Как да сравните два списъка в Excel

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

Сравняване на два списъка в Excel

Разбира се, можете да сравните два списъка ръчно. Но ще отнеме много време. Excel има свой собствен интелигентен инструментариум, който ще ви позволи да сравнявате данни не само бързо, но и да получавате информация, която не е толкова лесно да получите с очите си. Да предположим, че имаме две колони с координати A и B. Някои стойности се повтарят в тях.

Формулиране на проблема

Така че трябва да сравним тези колони. Процедурата за сравняване на два документа е следната:

1. Ако уникалните клетки на всеки от тези списъци са еднакви и общият брой на уникалните клетки е еднакъв и клетките са еднакви, тогава тези списъци могат да се считат за еднакви. Редът, в който са подредени стойностите в този списък, няма толкова голямо значение.
2. Можем да говорим за частично съвпадение на списъците, ако самите уникални стойности са еднакви, но броят на повторенията е различен. Следователно такива списъци могат да съдържат различен брой елементи.
3. Фактът, че двата списъка не съвпадат, се обозначава с различен набор от уникални стойности.

Всички тези три условия едновременно са условията на нашия проблем.

Решението на проблема

Нека генерираме два динамични диапазона, за да улесним сравняването на списъци. Всеки от тях ще съответства на всеки от списъците.

За да сравните два списъка, направете следното:

1. В отделна колона създаваме списък с уникални стойности, които са специфични и за двата списъка. За това използваме формулата: ЕСЛИОШИБКА(ЕСЛИОШИБКА( ИНДЕКС(Список1;ПОИСКПОЗ(0;СЧЁТЕСЛИ($D$4:D4;Список1);0)); ИНДЕКС(Список2;ПОИСКПОЗ(0;СЧЁТЕСЛИ($D$4:D4;Список2);0))); «»). Самата формула трябва да бъде написана като формула за масив.
2. Нека определим колко пъти се среща всяка уникална стойност в масива от данни. Ето формулите за това: =COUNTI(Списък1,D5) и =COUNTI(Списък2,D5).
3. Ако както броят на повторенията, така и броят на уникалните стойности са еднакви във всички списъци, които са включени в тези диапазони, тогава функцията връща стойността 0. Това показва, че съвпадението е XNUMX%. В този случай заглавията на тези списъци ще придобият зелен фон.
4. Ако цялото уникално съдържание е и в двата списъка, тогава се връща от формули =СЧЁТЕСЛИМН($D$5:$D$34;»*?»;E5:E34;0) и =СЧЁТЕСЛИМН($D$5:$D$34;»*?»;F5:F34;0) стойността ще бъде нула. Ако E1 не съдържа нула, но такава стойност се съдържа в клетки E2 и F2, тогава в този случай диапазоните ще бъдат разпознати като съвпадащи, но само частично. В този случай заглавията на съответните списъци ще станат оранжеви.
5. И ако една от формулите, описани по-горе, върне ненулева стойност, списъците няма да съвпадат напълно.

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

Пример за тестване

В нашата версия на таблицата има три типа списъци от всеки тип, описан по-горе. Има частично и пълно съвпадение, както и несъвпадение.

За да сравним данни, използваме диапазона A5:B19, в който последователно вмъкваме тези двойки списъци. За това какъв ще бъде резултатът от сравнението, ще разберем по цвета на оригиналните списъци. Ако са напълно различни, тогава ще бъде червен фон. Ако част от данните са същите, тогава жълто. В случай на пълна идентичност, съответните заглавия ще бъдат зелени. Как да направите цвят в зависимост от това какъв е резултатът? Това изисква условно форматиране.

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

Нека опишем още два метода за намиране на разлики, в зависимост от това дали списъците са синхронни или не.

Вариант 1. Синхронни списъци

Това е лесен вариант. Да предположим, че имаме такива списъци.

За да определите колко пъти стойностите не са се сближили, можете да използвате формулата: =SUMPRODUCT(—(A2:A20<>B2:B20)). Ако получим 0 като резултат, това означава, че двата списъка са еднакви.

Вариант 2: Разбъркани списъци

Ако списъците не са идентични в реда на обектите, които съдържат, трябва да приложите функция като условно форматиране и оцветяване на дублирани стойности. Или използвайте функцията COUNTIF, чрез който определяме колко пъти елемент от един списък се среща във втория.

Как да сравним 2 колони ред по ред

Когато сравняваме две колони, често трябва да сравним информация, която е в различни редове. За да направим това, операторът ще ни помогне АКО. Нека да разгледаме как работи на практика. За целта представяме няколко илюстративни ситуации.

Пример. Как да сравним 2 колони за съвпадения и разлики в един ред

За да анализираме дали стойностите, които са в един и същи ред, но различни колони, са еднакви, ние пишем функцията IF. Формулата се вмъква във всеки ред, поставен в спомагателната колона, където ще се показват резултатите от обработката на данните. Но изобщо не е необходимо да го предписвате във всеки ред, просто го копирайте в останалите клетки на тази колона или използвайте маркера за автоматично попълване.

Трябва да запишем такава формула, за да разберем дали стойностите в двете колони са еднакви или не: =АКО(A2=B2, “Съвпадение”, “”). Логиката на тази функция е много проста: тя сравнява стойностите в клетки A2 и B2 и ако са еднакви, показва стойността „Съвпада“. Ако данните са различни, не връща никаква стойност. Можете също да проверите клетките, за да видите дали има съвпадение между тях. В този случай използваната формула е: =АКО(A2<>B2, “Не съвпада”, “”). Принципът е същият, първо се извършва проверката. Ако се окаже, че клетките отговарят на критерия, тогава се показва стойността „Не съвпада“.

Също така е възможно да използвате следната формула в полето за формула, за да покажете както „Съвпадение“, ако стойностите са еднакви, така и „Не съвпадат“, ако са различни: =АКО(A2=B2; „Съвпадение“, „Не съвпадение“). Можете също да използвате оператора за неравенство вместо оператора за равенство. Само редът на стойностите, които ще бъдат показани в този случай, ще бъде малко по-различен: =АКО(A2<>B2, „Не съвпада“, „Съвпада“). След като използвате първата версия на формулата, резултатът ще бъде както следва.

Тази вариация на формулата е без значение за главни и малки букви. Следователно, ако стойностите в една колона се различават от другите само по това, че са написани с главни букви, тогава програмата няма да забележи тази разлика. За да направите сравнението чувствително към главни и малки букви, трябва да използвате функцията в критериите ТОЧНО. Останалите аргументи остават непроменени: =АКО(ТОЧНО(A2,B2), “Съвпадение”, “Уникално”).

Как да сравнявате няколко колони за съвпадения в един ред

Възможно е да се анализират стойностите в списъците според цял набор от критерии:

1. Намерете онези редове, които имат еднакви стойности навсякъде.
2. Намерете тези редове, където има съвпадения само в два списъка.

Нека да разгледаме няколко примера как да процедираме във всеки от тези случаи.

Пример. Как да намерите съвпадения в един ред в множество колони на таблица

Да предположим, че имаме поредица от колони, които съдържат необходимата ни информация. Изправени сме пред задачата да определим онези редове, в които стойностите са еднакви. За да направите това, трябва да използвате следната формула: =АКО(И(A2=B2,A2=C2), “съвпадение”, ” “).

Ако има твърде много колони в таблицата, тогава просто трябва да я използвате заедно с функцията IF оператор COUNTIF: =АКО(COUNTIF($A2:$C2,$A2)=3;”съвпадение”;” “). Числото, използвано в тази формула, показва броя на колоните за проверка. Ако се различава, тогава трябва да напишете толкова, колкото е вярно за вашата ситуация.

Пример. Как да намерите съвпадения в един ред във всеки 2 колони на таблица

Да кажем, че трябва да проверим дали стойностите в един ред съвпадат в две колони от тези в таблицата. За да направите това, трябва да използвате функцията като условие OR, където последователно напишете равенството на всяка от колоните с другата. Ето един пример.

Използваме тази формула: =ЕСЛИ(ИЛИ(A2=B2;B2=C2;A2=C2);”Совпадают”;” “). Може да има ситуация, когато в таблицата има много колони. В този случай формулата ще бъде огромна и може да отнеме много време, за да изберете всички необходими комбинации. За да разрешите този проблем, трябва да използвате функцията COUNTIF: =IF(COUNTIF(B2:D2,A2)+COUNTIF(C2:D2,B2)+(C2=D2)=0; „Уникален низ“; „Неуникален низ“)

Виждаме, че общо имаме две функции COUNTIF. С първия последователно определяме колко колони имат сходство с A2, а с втория проверяваме броя на сходствата със стойността на B2. Ако в резултат на изчисление по тази формула получим нулева стойност, това означава, че всички редове в тази колона са уникални, ако са повече, има прилики. Следователно, ако в резултат на изчисление по две формули и добавяне на крайните резултати получим нулева стойност, тогава се връща текстовата стойност „Уникален низ“, ако това число е по-голямо, се записва, че този низ не е уникален.

Как да сравним 2 колони в Excel за съвпадения

Сега да вземем пример. Да кажем, че имаме таблица с две колони. Трябва да проверите дали съвпадат. За да направите това, трябва да приложите формулата, където също ще се използва функцията IF, и операторът COUNTIF: =IF(COUNTIF($B:$B,$A5)=0, „Няма съвпадения в колона B“, „Има съвпадения в колона B“)

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

Как да сравните 2 колони в Excel за съвпадения и да маркирате с цвят

За да улесните визуалното идентифициране на съответстващите колони, можете да ги маркирате с цвят. За да направите това, трябва да използвате функцията "Условно форматиране". Да видим на практика.

Намиране и маркиране на съвпадения по цвят в множество колони

За да определите съвпаденията и да ги маркирате, първо трябва да изберете диапазона от данни, в който ще се извърши проверката, и след това да отворите елемента „Условно форматиране“ в раздела „Начало“. Там изберете „Дублирани стойности“ като правило за избор на клетки.

След това ще се появи нов диалогов прозорец, в който в левия изскачащ списък намираме опцията „Повтаряне“, а в десния списък избираме цвета, който ще се използва за селекцията. След като щракнете върху бутона „OK“, ще бъде избран фонът на всички клетки с прилики. След това просто сравнете колоните на око.

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

Техниката за проверка дали низовете съвпадат е малко по-различна. Първо, трябва да създадем допълнителна колона и там ще използваме комбинираните стойности с помощта на оператора &. За да направите това, трябва да напишете формула от формата: =A2&B2&C2&D2.

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

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