Подивіться відео - порівняйте дві колонки в Excel на предмет збігів та відмінностей
Єдиний запит, який я отримую багато - "як порівняти два стовпці в Excel?".
Це можна зробити багатьма різними способами, і спосіб використання буде залежати від структури даних і того, що від неї хоче користувач.
Наприклад, ви можете порівняти два стовпці та знайти або виділити всі відповідні точки даних (які є в обох стовпцях), або лише відмінності (де точка даних знаходиться в одному стовпці, а не в іншому) тощо.
Оскільки мене так багато запитують про це, я вирішив написати цей масштабний підручник з наміром охопити більшість (якщо не всі) можливі сценарії.
Якщо вам це буде корисно, передайте його іншим користувачам Excel.
Зауважте, що методи порівняння стовпців, показані в цьому підручнику, не єдині.
Виходячи з вашого набору даних, вам може знадобитися змінити або відрегулювати метод. Однак основні принципи залишаться незмінними.
Якщо ви вважаєте, що до цього підручника можна додати щось, дайте мені знати в розділі коментарів
Порівняйте дві колонки для точного збігу рядків
Це найпростіша форма порівняння. У цьому випадку вам потрібно зробити порівняння рядків за рядком та визначити, які рядки мають однакові дані, а які - ні.
Приклад: Порівняйте клітинки в одному рядку
Нижче наведено набір даних, де мені потрібно перевірити, чи однакове ім’я у стовпці А у стовпці В чи ні.
Якщо є відповідність, мені потрібен результат як "TRUE", а якщо не відповідає, то мені потрібен результат як "FALSE".
Формула нижче зробить це:
= A2 = B2
Приклад: Порівняйте клітинки в одному рядку (за формулою IF)
Якщо ви хочете отримати більш описовий результат, ви можете використовувати просту формулу IF, щоб повернути “Match”, коли імена однакові, та “Mismatch”, коли імена різні.
= IF (A2 = B2, "Match", "Mismatch")
Примітка: Якщо ви хочете зробити регістр порівняння чутливим, скористайтеся такою формулою IF:
= IF (ТОЧНО (A2, B2), "Відповідність", "Невідповідність")
З наведеною вище формулою "IBM" та "ibm" вважатимуться двома різними назвами, а вищенаведена формула повертатиме "Невідповідність".
Приклад: виділіть рядки відповідними даними
Якщо ви хочете виділити рядки, які мають відповідні дані (замість того, щоб отримувати результат в окремому стовпці), ви можете зробити це за допомогою умовного форматування.
Нижче наведено кроки для цього.
- Виберіть весь набір даних.
- Перейдіть на вкладку «Домашня сторінка».
- У групі Стилі натисніть опцію «Умовне форматування».
- У спадному меню натисніть «Нове правило».
- У діалоговому вікні "Нове правило форматування" натисніть "Використовувати формулу, щоб визначити, які клітинки відформатувати".
- У поле формули введіть формулу: = $ A1 = $ B1
- Натисніть кнопку Формат і вкажіть формат, який потрібно застосувати до відповідних клітинок.
- Натисніть OK.
Це виділить усі клітинки, де імена однакові в кожному рядку.
Порівняйте дві колонки та збіги виділення
Якщо ви хочете порівняти два стовпці та виділити відповідні дані, ви можете скористатися функціями дублювання в умовному форматуванні.
Зауважте, що це відрізняється від того, що ми бачили при порівнянні кожного рядка. У цьому випадку ми не будемо проводити порівняння по рядках.
Приклад: Порівняйте дві колонки та виділіть відповідні дані
Часто ви отримуєте набори даних, де є збіги, але вони можуть бути не в одному рядку.
Щось, як показано нижче:
Зауважте, що список у стовпці А більший за список у В. Також деякі назви є в обох списках, але не в одному рядку (наприклад, IBM, Adobe, Walmart).
Якщо ви хочете виділити всі відповідні назви компаній, ви можете зробити це за допомогою умовного форматування.
Нижче наведено кроки для цього.
- Виберіть весь набір даних.
- Натисніть вкладку Домашня сторінка.
- У групі Стилі натисніть опцію «Умовне форматування».
- Наведіть курсор на параметр Виділити правила комірки.
- Натисніть «Повторювані значення».
- У діалоговому вікні «Повторювані значення» переконайтеся, що вибрано «Дублювати».
- Вкажіть форматування.
- Натисніть OK.
Наведені вище кроки дадуть вам результат, як показано нижче.
Примітка: Повторюване правило умовного форматування не враховує регістр. Тож «Яблуко» та «яблуко» вважаються однаковими і будуть виділені як дублікати.
Приклад: Порівняйте дві колонки та виділіть невідповідні дані
Якщо ви хочете виділити імена, які є в одному списку, а не в іншому, ви також можете використовувати умовне форматування для цього.
- Виберіть весь набір даних.
- Натисніть вкладку Домашня сторінка.
- У групі Стилі натисніть на опцію «Умовне форматування».
- Наведіть курсор на параметр Виділити правила комірки.
- Натисніть «Повторювані значення».
- У діалоговому вікні «Повторювані значення» переконайтеся, що вибрано «Унікальний».
- Вкажіть форматування.
- Натисніть 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.
