Несъответствие в Excel с 5 лесни и практични метода


Когато работите с големи данни в Excel, най-вероятно ще трябва да контрастирате в Excel. Диагностицирането и коригирането на тези малки грешки изглежда необходимо, за да се предостави точен отчет в Excel. Excel представя различни инструменти за подпомагане на разрешаването на конфликти. В тази статия от списание Faradars ние преподаваме контраст с редица от най-практичните методи. Тези методи включват филтриране, условно форматиране и използване на разширени формули. Използвайки тези методи, можете да подобрите и ускорите работата си с данни. Накрая въвеждаме метод за предотвратяване на несъответствия в данните.

Разбиране на несъответствията в данните в Excel

Наличието на несъответствия в редовете и колоните на Excel може да е резултат от човешка грешка. Като дублирани данни или нередност във форматирането на данните. Например в таблицата по-долу полът на Уолтър Уайт и Уейн Сина е неправилно написан като „Маел“ вместо „Мъж“. Разбира се, този проблем може да бъде коригиран един по един, докато преглеждате данните, но когато сме изправени пред голямо количество данни в нива и колони, това не изглежда логично. За да коригирате тези несъответствия в Excel, можете да използвате следните функции:

Видео обучение за използване на Excel в счетоводството и управлението в извънкласни часове

щракнете

  • Филтриране: Бързо намиране на несъответствия в колони с данни с помощта на филтри.
  • „Условно форматиране“ (Условно форматиране): Маркирайте клетки, които не следват даденото условие, като използвате правила за форматиране.
  • Разширени функции на Excel: Използвайте функции като функцията MATCH и Willocup, за да намерите това несъответствие между данните.

Като разберете функционалността на тези функции, можете лесно да боравите с големи количества данни в Excel. Първо, преди да въведем тези методи, ще представим най-простия метод, който е ръчен преглед и промяна на данните.

Ръчно съпоставяне на данни в Excel

Ръчното несъответствие в Excel обикновено може да бъде лесен и бърз начин за намиране на несъответствия. Този процес несъмнено изисква много прецизност и обемът на данните не трябва да бъде твърде голям. По-долу са някои съвети, които ще помогнат при ръчната дискретизация:

Обучително видео за Excel Microsoft Excel 2021 в извънкласна програма

щракнете

  • Разберете данните пред вас. Запознайте се какво има в редовете и колоните и форматирането на всеки от тях.
  • Докато превъртате през данните, обърнете внимание на съдържанието на всяка клетка. Можете да използвате инструменти за оцветяване и подчертаване на клетки, които имат грешки.
  • За да увеличите скоростта, опитайте се да сравните данните, които разглеждате, с други данни, така че несъответствието да може да бъде открито бързо.
  • Съберете намерените несъответствия в списък. Този списък може да бъде добра справка за отстраняване на неизправности по-късно.
  • Бъдете търпеливи, докато откривате несъответствия. Ръчното намиране на несъответствия в Excel отнема много време, но анализирането на несъответствията помага да се разкрият потенциални проблеми.

Използвайте филтър за контраст

Използването на филтър е един от най-лесните начини за контрастиране в Excel. Например в изображението по-долу ние изолираме грешни данни в колоната „Пол“ с помощта на филтър. Първо щракнете върху бутона „Сортиране и филтриране“ в раздела „Начало“.

Кликнете върху изображението, за да го видите в по-голям размер.

Изберете „Филтър“.

Кликнете върху изображението, за да го видите в по-голям размер.

Отворете падащото меню над колоните.

Кликнете върху изображението, за да го видите в по-голям размер.

В този пример избираме горната част на колоната за пол и премахваме правилните данни (Мъж) от избраното състояние.

Кликнете върху изображението, за да го видите в по-голям размер.

Сега Excel показва само редовете на таблицата, където Male е написано неправилно.

Кликнете върху изображението, за да го видите в по-голям размер.

След това отидете в раздела Начало на секцията „Намиране и избор“ и изберете опцията „Замяна“.

Кликнете върху изображението, за да го видите в по-голям размер.

Напишете грешната стойност пред „Намери какво“ и правилната стойност пред „Замени с“, след което щракнете върху „Замени всички“.

Ще изскочи прозорец, за да ви уведоми, че замяната е направена. Натиснете OK.

Как да научите контраст в Excel?

Кликнете върху изображението, за да видите видео поредицата от уроци за Excel.

За да контрастирате в Excel, трябва да сте запознати с концепции като писане на формули, използване на конвенционални функции като IF и VLOOKUP и създаване на условни правила в Excel. След като се запознаете с основните понятия, можете да преминете към по-напредналите стъпки на контраста в Excel. Например, трябва да използвате функции INDEX-MATCH и обобщени таблици, които бяха споменати по-рано в списание Faradars. Освен това трябва да се възползвате от помощта на нови инструменти и техники за управление и анализ на данни, за да улесните управлението на големи данни.

И накрая, като разглеждате и използвате видеоклиповете за извънкласно обучение на Excel, можете да подобрите уменията си в Excel и да се запознаете напълно с неговите усъвършенствани концепции. Предлагаме ви да видите и прегледате образователните видеоклипове, дадени в курса по същите теми от връзките по-долу.

  • Научаване как да използвате функции и формули в Excel
  • Преподаване на практически трикове в Excel

В допълнение към горните курсове, може също да искате да разширите знанията си в Excel и поради тази причина ви предлагаме да гледате видео поредицата от извънкласно обучение по Excel, което е дадено на връзката по-долу:

  • Обучително видео за Excel от въведение до напреднали

Изключения при използване на условно форматиране

Друг метод, използван за контрастиране на нива и колони в Excel, е условното форматиране. При този метод можете да форматирате данните, като зададете различни условия. В тази статия обясняваме способността на този ефективен метод, използвайки прост пример.

Учебно видео за Excel – инструменти на Excel в извънкласните програми

щракнете

Изберете желаните данни, които включват желаните редове и колони. В този пример избираме колона A и колона B, включително името на компанията и името на държавата. След това кликваме върху „Условно форматиране“ в раздела Начало.

Кликнете върху изображението, за да го видите в по-голям размер.

Изберете вида на залога и след това самия залог. В този пример първо избираме „Маркиране на правила за клетки“ и след това „Дублирани стойности“.

Кликнете върху изображението, за да го видите в по-голям размер.

Сега трябва да изберем дали дублиращи се стойности или уникални стойности ще бъдат маркирани. Избираме желания цвят и шрифт за форматиране на клетки. След това кликваме върху Ok.

Кликнете върху изображението, за да го видите в по-голям размер.

Виждаме, че според избора, който направихме, всички дублирани стойности бяха маркирани с тъмночервен текст и светлочервен фон.

Несъответствие при използване на разширени функции в Excel

В четвъртия метод ние изследваме използването на различни функции, комбинацията от функции, за да открием несъответствия в Excel.

Видео за това как да използвате функции и формули в Excel Excel в извънкласните часове

щракнете

IF и IS функции

За да сравним клетките и да намерим разликата между тях, можем да използваме функциите IF и IS в комбинация, например използваме следната формула, за да сравним клетки A2 и B2.

=IF(A2=B2,"MATCH","NOT MATCHED")

Записваме формулата в клетка C2. Като плъзнете малкия квадрат в долната част на клетката до края на таблицата, повтаряме формулата. Ако съответните клетки в колона A и B са равни една на друга, се показва „Съвпадение“, в противен случай се показва „Не съвпада“.

Комбинирайте функцията IF с функцията IS, като ISNUMBER или ISTEXT, за да изследвате различни типове данни. Например следната формула връща „Число“. Ако колона B2 има числова стойност, в противен случай се връща „NaN“.

=IF(ISNUMBER(B2), "Number", "NaN")

Използване на формули VLOOKUP, HLOOKUP и XLOOKUP

WilloCup с функции за търсене в колона и OchloCup с функции за търсене на ред са ефективни за сравняване на стойности в различни редове и колони. Можете да видите аргумента на функцията willocup в следната формула:

=VLOOKUP(lookup_value, table_array, col_index_num, (range_lookup)) 

Аргументът на функцията echlocap е както следва:

=HLOOKUP(lookup_value, table_array, row_index_num, (range_lookup))
  • търсена_стойностСтойностите, които искате да търсите в таблицата.
  • масив_таблицаИграта на клетки във втората таблица, с която искате да сравните.
  • номер_индекс_колонаНомерът на записа или колоната, от която искате да върнете данни.
  • (диапазон_търсене)Този аргумент приема False за търсене с точно съвпадение и True за приблизително търсене.

В тази статия, използвайки VLOOKUP в колона E, сравняваме имената на служителите в колона A и D, така че доходите им да бъдат извлечени от колона B.

Кликнете върху изображението, за да го видите в по-голям размер.

Функцията Xlocap е по-ранна версия на Willocap и Ochlocap, която е налична в Excel 2021 и по-нови версии. Аргументът на тази функция е както следва:

=XLOOKUP(lookup_value, lookup_array, return_array,(if_not_found))
  • търсена_стойностСумата, която се опитвате да намерите.
  • търсене_масивМасив от клетки, в който търсите желаната стойност.
  • върнат_масивМасивът, от който искате да върнете стойност.
  • (ако_не_намерено)Текстът, който искате да върнете, ако функцията не успее да намери желаната стойност.

В изображението по-долу изпълняваме предишния пример с помощта на тази функция:

Следвайте MATCH

С функцията MATCH можете да сравните два списъка с данни за несъответствия. Функцията MATCH е полезна за намиране на конкретни данни в списък с данни и връща позицията на тези данни в списъка.

=MATCH(lookup_value, lookup_array, (match_type))

Например, на снимката по-долу позицията на “Дини” се намира в списъка, който е на четвъртия ред от списъка.

Кликнете върху изображението, за да го видите в по-голям размер.

Използване на разширения в Excel за контраст

Excel предлага разнообразие от добавки в допълнение към своите инструменти по подразбиране, за да ви помогне с несъответствия в Excel и проверка на несъответствия.

Помислете за приставката Spreadsheet Inquire. Този плъгин ви помага да сравните два файла на Excel и да подчертаете разликите им. Следвайте стъпките по-долу, за да активирате този плъгин:

  1. Отидете в раздела Файл.
  2. Щракнете върху Опции, след което отидете в секцията Добавки.
  3. В секцията „Управление“ изберете „COM добавки“ и след това щракнете върху „Отиди“.
  4. Изберете Spreadsheet Inquire, след което щракнете върху Ok.
  5. След активиране отидете в раздела Запитване и използвайте командата “Сравнение на файлове”.

Друг полезен инструмент за намиране на аномалии е Analysis ToolPak. Този плъгин предоставя разширени статистически функции заедно с разширени инструменти за проверка на данни, които могат да се използват за проверка за несъответствия. За да активирате този инструмент, следвайте същите стъпки като предишния инструмент и активирайте опцията Анализ в секцията Управление.

Като цяло Excel предлага разнообразие от добавки, които да ви помогнат да намерите и управлявате несъответствията във вашите данни. За да подобрите качеството на вашите данни, е по-добре да сте запознати с тези инструменти и функции.

Избягване на несъответствия в данните с валидиране на данни

Можете да създадете правила за валидиране на данни, за да предотвратите несъответствия в данните. За да направите това, следвайте стъпките по-долу:

Учебно видео за Excel – инструменти на Excel в извънкласните програми

щракнете

Изберете клетките, към които искате да приложите правилото. Броят може да бъде една клетка, няколко клетки или цяла колона.

Отидете в раздела „Данни“ и щракнете върху „Проверка на данни“.

Кликнете върху изображението, за да го видите в по-голям размер.

В прозореца за проверка на данните отидете на раздела „Настройки“. В този раздел можете да определите желаните от вас условия.

В Excel има различни видове условия за валидиране. Дадохме някои от тези условия в продължението на тази статия от списание Faradars:

  • Цяло число: Позволява само въвеждане на цели числа в определен диапазон.
  • Десетичен знак: Приема десетични числа само в определени игри.
  • списък: Приема само данни, които са в предварително дефинирания списък.
  • Дата: Приема само дати в определен диапазон.
  • време: Приема само време в определен диапазон.
  • Дължина на текста: Ограничението налага ограничение върху синтетичните дължини.
  • Персонализиран: Създайте желаното от вас състояние, като използвате различни съществуващи условия.

След като изберете желаното условие, задайте необходимите параметри. Например, ако се използва цяло число, трябва да посочите минимум и максимум за интервала, така че данните от таблицата да бъдат включени в този интервал.

В допълнение към условието за валидиране можете да зададете персонализирани съобщения за грешки и т.н., така че например да се показва съобщение, ако данните не отговарят на приложеното от вас условие. На свой ред можете да направите данните по-лесни за разбиране от публиката. За да направите това, отидете на разделите „Въведено съобщение“ и „Предупреждение за грешка“ в прозореца за проверка на данните, за да въведете желаните съобщения.

С помощта на Data Validation можете значително да намалите възможността за грешки и несъответствия във вашите данни и да увеличите достоверността на вашия Excel файл.

Писане на противоречия в Excel с 5 лесни и практични метода за първи път в списание Фарадарс. се появи.