Гледайте видео - Сравнете две колони в Excel за съвпадения и разлики
Единственото запитване, което получавам много, е - „как да сравня две колони в Excel?“.
Това може да се направи по много различни начини и методът за използване ще зависи от структурата на данните и това, което потребителят иска от нея.
Например, може да искате да сравните две колони и да намерите или маркирате всички съвпадащи точки от данни (които са в двете колони), или само разликите (където точка от данни е в една колона, а не в другата) и т.н.
Тъй като ме питат толкова много за това, реших да напиша този масивен урок с намерение да покрия повечето (ако не всички) възможни сценарии.
Ако намирате това за полезно, предайте го на други потребители на Excel.
Обърнете внимание, че техниките за сравняване на колони, показани в този урок, не са единствените.
Въз основа на вашия набор от данни може да се наложи да промените или коригирате метода. Основните принципи обаче ще останат същите.
Ако смятате, че може да се добави нещо към този урок, уведомете ме в секцията за коментари
Сравнете две колони за точно съвпадение на редовете
Това е най -простата форма за сравнение. В този случай трябва да направите сравнение по ред по ред и да определите кои редове имат същите данни и кои не.
Пример: Сравнете клетките в същия ред
По -долу е набор от данни, където трябва да проверя дали името в колона А е същото в колона В или не.
Ако има съвпадение, имам нужда от резултата като „ИСТИНА“, а ако не съвпада, тогава имам нужда от резултата като „НЕВЯРНО“.
Формулата по -долу ще направи това:
= A2 = B2
Пример: Сравнете клетките в същия ред (използвайки формулата IF)
Ако искате да получите по -описателен резултат, можете да използвате проста формула IF, за да върнете „Match“, когато имената са еднакви, и „Mismatch“, когато имената са различни.
= IF (A2 = B2, "Match", "Mismatch")
Забележка: В случай, че искате да направите сравнителния регистър чувствителен, използвайте следната формула IF:
= IF (ТОЧНО (A2, B2), "Съвпадение", "Несъответствие")
С горната формула „IBM“ и „ibm“ ще се считат за две различни имена и горната формула ще върне „Несъответствие“.
Пример: Маркирайте редове с съвпадащи данни
Ако искате да подчертаете редовете, които имат съвпадащи данни (вместо да получавате резултата в отделна колона), можете да направите това, като използвате условно форматиране.
Ето стъпките за това:
- Изберете целия набор от данни.
- Щракнете върху раздела „Начало“.
- В групата Стили щракнете върху опцията „Условно форматиране“.
- От падащото меню щракнете върху „Ново правило“.
- В диалоговия прозорец „Ново правило за форматиране“ щракнете върху „Използвайте формула, за да определите кои клетки да форматирате“.
- В полето за формула въведете формулата: = $ A1 = $ B1
- Щракнете върху бутона Форматиране и посочете формата, който искате да приложите към съответстващите клетки.
- Щракнете върху OK.
Това ще подчертае всички клетки, където имената са еднакви във всеки ред.
Сравнете две колони и маркиране на мачове
Ако искате да сравните две колони и да подчертаете съвпадащи данни, можете да използвате дублиращата се функционалност при условно форматиране.
Имайте предвид, че това е различно от това, което видяхме при сравняването на всеки ред. В този случай няма да правим сравнение по ред по ред.
Пример: Сравнете две колони и маркирайте съответстващи данни
Често ще получавате набори от данни, където има съвпадения, но те може да не са в един ред.
Нещо, както е показано по -долу:
Имайте предвид, че списъкът в колона А е по -голям от този в В. Също така някои имена има и в двата списъка, но не в един и същи ред (като IBM, Adobe, Walmart).
Ако искате да подчертаете всички съвпадащи имена на компании, можете да направите това, като използвате условно форматиране.
Ето стъпките за това:
- Изберете целия набор от данни.
- Щракнете върху раздела Начало.
- В групата Стили щракнете върху опцията „Условно форматиране“.
- Задръжте курсора на мишката върху опцията Highlight Cell Rules.
- Кликнете върху Дублирани стойности.
- В диалоговия прозорец Дублирани стойности се уверете, че е избрано „Дублирани“.
- Посочете форматирането.
- Щракнете върху OK.
Горните стъпки ще ви дадат резултат, както е показано по -долу.
Забележка: Условното форматиране на дублиращо се правило не е чувствително към регистъра. Така че „Apple“ и „apple“ се считат за еднакви и биха били подчертани като дубликати.
Пример: Сравнете две колони и маркирайте несъответстващи данни
В случай, че искате да подчертаете имената, които присъстват в единия списък, а не в другия, можете да използвате условното форматиране и за това.
- Изберете целия набор от данни.
- Щракнете върху раздела Начало.
- В групата Стили щракнете върху опцията „Условно форматиране“.
- Задръжте курсора на мишката върху опцията Highlight Cell Rules.
- Кликнете върху Дублирани стойности.
- В диалоговия прозорец Дублирани стойности се уверете, че е избрано „Уникално“.
- Посочете форматирането.
- Щракнете върху OK.
Това ще ви даде резултат, както е показано по -долу. Той подчертава всички клетки, които имат име, което не присъства в другия списък.
Сравнете две колони и намерете липсващи точки от данни
Ако искате да определите дали точка от данни от един списък присъства в другия списък, трябва да използвате формулите за търсене.
Да предположим, че имате набор от данни, както е показано по -долу и искате да идентифицирате компании, които присъстват в колона А, но не и в колона Б,
За да направя това, мога да използвам следната формула VLOOKUP.
= ГРЕШКА (VLOOKUP (A2, $ B $ 2: $ B $ 10,1,0))
Тази формула използва функцията VLOOKUP, за да провери дали името на фирма в A присъства в колона B или не. Ако е налице, то ще върне това име от колона B, в противен случай ще върне грешка #N/A.
Тези имена, които връщат грешката #N/A, са тези, които липсват в колона B.
Функцията ISERROR ще върне TRUE, ако има резултат VLOOKUP е грешка и FALSE, ако не е грешка.
Ако искате да получите списък с всички имена, където няма съвпадение, можете да филтрирате колоната с резултатите, за да получите всички клетки с TRUE.
Можете също да използвате функцията MATCH, за да направите същото;
= НЕ (ISNUMBER (МАТЧ (A2, $ B $ 2: $ B $ 10,0)))
Забележка: Лично аз предпочитам да използвам функцията Match (или комбинацията от INDEX/MATCH) вместо VLOOKUP. Намирам го за по -гъвкав и мощен. Можете да прочетете разликата между Vlookup и Index/Match тук.
Сравнете две колони и издърпайте съответстващите данни
Ако имате два набора от данни и искате да сравните елементи в един списък с другия и да извлечете съответстващата точка от данни, трябва да използвате формулите за търсене.
Пример: Издърпайте съответстващите данни (точно)
Например в списъка по -долу искам да извлека стойността на пазарната оценка за колона 2. За да направя това, трябва да потърся тази стойност в колона 1 и след това да извлека съответната пазарна стойност.
По -долу е формулата, която ще направи това:
= VLOOKUP (D2, $ A $ 2: $ B $ 14,2,0)
или
= ИНДЕКС ($ A $ 2: $ B $ 14, MATCH (D2, $ A $ 2: $ A $ 14,0), 2)
Пример: Издърпайте съответстващите данни (частично)
В случай, че получите набор от данни, където има малка разлика в имената в двете колони, използването на горепосочените формули за търсене няма да работи.
Тези формули за търсене се нуждаят от точно съвпадение, за да дадат правилния резултат. Във функцията VLOOKUP или MATCH има опция за приблизително съвпадение, но това не може да се използва тук.
Да предположим, че имате набор от данни, както е показано по -долу. Имайте предвид, че има имена, които не са пълни в колона 2 (като JPMorgan вместо JPMorgan Chase и Exxon вместо ExxonMobil).
В такъв случай можете да използвате частично търсене, като използвате заместващи знаци.
Следната формула ще даде правилния резултат в този случай:
= VLOOKUP ("*" & D2 & "*", $ A $ 2: $ B $ 14,2,0)
или
= ИНДЕКС ($ A $ 2: $ B $ 14, MATCH ("*" & D2 & "*", $ A $ 2: $ A $ 14,0), 2)
В горния пример звездичката (*) е заместващ знак, който може да представлява произволен брой знаци. Когато стойността за търсене е фланкирана с нея от двете страни, всяка стойност в колона 1, която съдържа стойността за търсене в колона 2, ще се счита за съвпадение.
Например * Exxon * ще съвпада с ExxonMobil (тъй като * може да представлява произволен брой знаци).
Може да ви харесат и следните съвети и уроци по Excel:
- Как да сравним два листа на Excel (за разлики)
- Как да маркирате празни клетки в Excel.
- Маркирайте ВСЕКИ друг ред в Excel.
- Разширен филтър на Excel: Пълно ръководство с примери.
- Маркирайте редове въз основа на стойност на клетката в Excel.
