Функція для порівняння двох стовпців в excel. Як порівняти два стовпці в Excel - методи порівняння даних Excel
Інструкція
Скористайтеся вбудованою функцією порівняння осередків СЧЁТЕСЛІ, якщо треба порівняти текстові значення в осередках колонки таблиці зі зразком тексту і перерахувати всі співпадаючі значення. Почніть з заповнення колонки текстовими значеннями, а потім в іншій колонці клацніть осередок, в якій хочете бачити результат підрахунку, і введіть відповідну формулу. Наприклад, якщо перевіряються значення знаходяться в колонці A, а результат треба помістити в перший осередок колонки C, то її вміст має бути таким: \u003d СЧЁТЕСЛІ ($ A: $ A; "Виноград") Тут «Виноград» - строкове значення, з яким порівнюються значення всіх осередків колонки A. Можна не вказувати його у формулі, а помістити в окрему клітинку (Наприклад - в B1) і вставити в формулу відповідне посилання: \u003d СЧЁТЕСЛІ ($ A: $ A; B1)
використовуйте опції умовного форматування, Якщо треба візуально виділити в таблиці результат порівняння строкових змінних. Наприклад, якщо треба виділити в колонці A осередки, текст в яких збігається зі зразком в осередку B1, то почніть з виділення цієї колонки - клацніть її заголовок. Потім клацніть кнопку «Умовне форматування» в групі команд «Стилі» закладки «Головна» меню Excel. Перейдіть в розділ «Правила виділення осередків» і виберіть рядок «Так само». У віконці вкажіть клітинку-зразок (клацніть клітку B1) і виберіть у спадному списку варіант оформлення співпадаючих рядків. Потім натисніть кнопку «OK».
Використовуйте комбінацію вбудованих функцій ЯКЩО і СЦЕПИТЬ при необхідності порівняти зі зразком не одну текстову осередок, а кілька. Функція СЦЕПИТЬ з'єднує зазначені їй значення в одну строкову змінну. Наприклад, команда СЦЕПИТЬ (A1; "і"; B1) до рядка з осередку A1 додасть текст «і», а після нього помістить рядок з осередку B1. Сконструйовану таким способом рядок потім можна порівняти зі зразком за допомогою функції ЯКЩО. Коли порівнювати треба не один рядок зручніше дати власне ім'я осередку -образцу. Для цього клацніть її та зліва від рядки формул замість позначення комірки (наприклад, C1) наберіть її нове ім'я (наприклад, «зразок»). Потім клікніть ту клітинку, в якій повинен бути результат порівняння, і введіть формулу: ЯКЩО (СЦЕПИТЬ (A1; "і"; B1) \u003d зразок; 1; 0) Тут одиниця - це значення, яке буде містити осередок з формулою, якщо порівняння дасть позитивний результат, а нуль - для негативного результату. Розмножити цю формулу на всі рядки таблиці, які потрібно порівняти зі зразком дуже просто - наведіть курсор на правий нижній кут комірки і, коли курсор зміниться (стане чорним хрестиком), натисніть ліву кнопку миші і розтягніть цей осередок вниз до останньої порівнюєш рядки.
Якщо ви працюєте з табличними документами великого обсягу (багато даних / стовпців), дуже складно тримати на контролі достовірність / актуальність всієї інформації. Тому дуже часто потрібно проаналізувати два або більше стовпців в документі Ексель на предмет виявлення повторень. А якщо користувач не володіє інформацією про все функціонал програми, у нього може логічно запитати: як порівняти два стовпці в excel?
Відповідь вже давно придуманий розробниками даної програми, спочатку заклали в неї команди, які допомагають порівняти інфу. Взагалі, якщо вдаватися в глибини цього питання, можна відшукати близько десятка різноманітних способів, включаючи написання окремих макросів і формул. Але практика показує: досить знати три-чотири надійних способу, щоб протидіяти новим викликам потребами в порівнянні.
Як порівняти два стовпці в excel на збіги
Дані в Ексель звичайно порівнюються між рядками, між стовпцями, зі значенням, заданим, як еталон. Якщо є потреба порівняти стовпці, можна використовувати вбудований функціонал, а саме, дії «збігаючись» і «Якщо». І все, що вам потрібно - це Ексель не раніше сьомого року «випуску».
Починаємо з функції «збігаючись». Наприклад, порівнювані дані знаходяться в стовпцях, що мають адреси С3 і В3. Результат же порівняння потрібно помістити в клітинку, наприклад, D3. Ми клацаємо мишкою на цій клітці, входимо в директорію меню «формули», знаходимо рядок «бібліотека функцій», розкриваємо функції, поміщені в спадаючий список, знаходимо слово «текстовий» і клацаємо на «збігаючись».
Через мить на дисплеї ви побачите нову форму, де будуть всього два поля: «текст один», «текст два». У них потрібно забити, як раз, адреси порівнюваних стовпців (С3, В3), після клацнути на звичну клавішу «ОК». У підсумку, ви побачите результат зі словами «Істина» / «Брехня». В принципі, нічого особливо складного навіть для початківця користувача! Але це далеко не єдиний метод. Давайте розберемо функцію «Якщо».
Можливість порівняти два стовпці в excel на збіги з допомогою «Якщо» дозволяє вносити значення, які після здійснення операції відобразяться в результаті. Курсор встановлюється в клітинку, де буде здійснюватися введення, відкривається директорія меню «бібліотека функцій», у випадаючому списку вибирається рядок «логічні», в ньому ж першу позицію і буде займати команда «Якщо». Вибираємо її.
Далі вилітає форма аргументованого заповнення. «Лог_вираз» - це формулювання самої функції. У нашому випадку це порівняння двох колонок, тому вводимо «В3 \u003d С3» (або ваші адреси колонок). Далі поля «значеніе_еслі істина», «значеніе_еслі_ложь». Тут слід ввести дані (написи / слова / числа), які повинні відповідати позитивному / негативного результату. Після заповнення тиснемо, як водиться, «ок». Знайомимося з результатом.
Якщо потрібно виконати порядковий аналіз двох стовпчиків, в третій стовпчик поміщаємо будь-яку функцію, про які йшла мова вище ( «якщо», або «совпад»). Дія її поширюємо на всю висоту заповнених колонок. Далі, виділяємо третій стовпець, клацаємо вкладку «головна», шукаємо в з'явилася групі слово «стилі». Відкриються «правила виділення стовпців / осередків».
У них потрібно вибрати команду «дорівнює», потім клацнути по на одну колонку і натиснути на «Ентер». У підсумку, виходить «підфарбувати» стовпці, де є збігаються результати. І ви відразу будете бачити потрібну інфу. Далі в розборі теми «як порівняти значення двох стовпців в excel» перейдемо до такого методу, як умовне форматування в Ексель.
Ексель: умовне форматування
Форматування умовного типу дозволить вам не просто порівняти два різних стовпчика / ячейки / рядки, а й виділити заданим кольором (червоним) різні в них дані. Тобто ми шукаємо не збігом, а різниці. Щоб це отримати, діємо так. Виділяємо потрібні стовпчики, не чіпаючи їх назв, заходимо в директорію меню «головна», в ній знаходимо підрозділ «стилі».
У ньому буде рядок «умовне форматування». Натиснувши на неї, отримаємо список, де нам потрібен пункт-функція «створити правило». Наступний крок: в рядку «формат» потрібно вбити формулу \u003d $ А2<>$ В2. Ця формула допоможе Ексель зрозуміти, що саме нам потрібно, а саме, забарвити в червоний все значення стовпчика А, що не дорівнюють значенням стовпчика В. Трохи складніший спосіб застосування формул відноситься до участі таких конструкцій, як HLOOKUP / VLOOKUP. Ці формули відносяться до горизонтального / вертикального пошуку значень. Погляньмо на цей спосіб докладніше.
HLOOKUP і VLOOKUP
Ці дві формули дозволяють шукати дані по горизонталі / вертикалі. Тобто Н - це горизонталь, а V - вертикаль. Якщо дані, які потрібно порівняти, знаходяться в лівій колонці щодо тієї, де розташовані порівнювані значення, застосовуємо конструкцію VLOOKUP. Але якщо дані для порівняння знаходяться горизонтально у верхній частині сторінки від тієї колонки, де позначені еталонні значення, застосовуємо конструкцію HLOOKUP.
Щоб зрозуміти, як порівняти дані в двох шпальтах excel по вертикалі, слід використовувати таку повну формулу: Lookup_value, table_array, col_index_num, range_lookup.
Значення, яке потрібно відшукати, позначаємо, як «lookup_value». Колонки для пошуку забиваються, як «table array». Номер стовпчика слід вказати, як «сol_index_num». Причому це той стовпець, значення якого співпало, і яке потрібно повернути / виправити. Команда «range lookup» тут виступає, як додаткова. Вона може вказати, потрібно значення зробити точним, або наближеним.
Якщо цю команду не прописати, значення будуть повертатися за обома типами. Формула HLOOKUP повністю виглядає так: lookup_value, table_array, row_index_num, range_lookup. Робота з нею практично ідентична вищеописаної. Правда тут є виняток. Це індекс рядки, що визначає рядок, значення якої повинні бути повернуті. Якщо навчитися чітко застосовувати всі перераховані вище способи, стає ясно: немає більш зручною і універсальною програми для роботи з великою кількістю даних різних типів, ніж Ексель. Порівняти два стовпці в excel - це, однак, лише половина роботи. Адже з отриманими значеннями потрібно ще щось зробити. Тобто знайдені збіги ще потрібно якось обробити.
Спосіб обробки значень-дублікатів
Отже, є знайдені цифри в першому, припустимо, стовпці, повністю повторюються в другому стовпці. Зрозуміло, що вручну виправляти повторення - праця нереальний, віднімає багато дорогоцінного часу. Тому слід використовувати вже готову методику для автоматичного виправлення.
Щоб це спрацювало, потрібно спочатку дати імена стовпчиках, якщо їх немає. Курсор підганяємо до першій сходинці, натискаємо правою клавішею, в меню вибираємо «вставити». Припустимо, заголовками будуть «ім'я» і «дублікат». Далі потрібна директорія «дата», в ній - «фільтр». Тиснемо на малесеньку стрілочку біля «дубліката» і прибираємо всі «пташки» зі списку. Тепер тиснемо "ок", і повторювані значення стовпця А стають видимими.
Далі ми виділяємо ці осередки, адже нам потрібно було не просто порівняти текст в двох стовпчиках в excel, а й видалити дублікати. Виділили, натиснули правою кнопкою, вибрали «видалити рядок», натиснули «ок» і отримали таблицю без співпадаючих значень. Спосіб працює, якщо стовпчики знаходяться на одній сторінці, тобто є суміжними.
Таким чином ми з вами розібрали кілька способів порівняння двох стовпців в ексель. Я спеціально не став показувати вам скріншоти, тому як ви б в них заплуталися.
АЛЕ я приготував відмінне відео одного з найпопулярніших і простих способів порівнювання двох стовпців в документі і зараз пропоную вам з ним ознайомиться, щоб закріпити пройдений матеріал.
Якщо ж стаття для вас все ж була корисна, тоді поділіться в соц. мережах або поставте оцінку, натиснувши на ту кількість зірок, яке вважаєте за потрібне. Спасибі Вам, на сьогодні все, до швидких зустрічей.
© Олександр Іванов.
Уміння порівнювати два масиви даних в Excel часто пригождается для людей, що обробляють великі обсяги даних і працюють з величезними таблицями. Наприклад, порівняння може бути використано в, коректності занесення даних або внесення даних в таблицю в термін. У статті нижче описані кілька прийомів порівняння двох стовпців з даними в Excel.
Використання умовного оператора ЯКЩО
Метод використання умовного оператора ЯКЩО відрізняється тим, що для порівняння двох стовпців використовується тільки необхідна для порівняння частина, а не весь масив цілком. Нижче описані кроки по реалізації даного методу:
Додайте обидва стовпці для порівняння в колонках A і B робочого листа.
У комірці С2 введіть наступну формулу \u003d ЕСЛИ (ЕОШИБКА (ПОИСКПОЗ (A2; $ B $ 2: $ B $ 11; 0)); ""; A2) і простягніть її до комірки С11. Дана формула послідовно переглядає наявність кожного елемента з шпальти A в стовпці B і повертає значення елемента, якщо воно було знайдено в стовпці B.
Використання формули підстановки ВВР
Принцип роботи формули аналогічний попередній методиці, відмінність полягає в, замість ПОИСКПОЗ. Відмінною особливістю даного методу також є можливість порівняння двох горизонтальних масивів, використовуючи формулу ГПР.
Щоб порівняти два стовпці з даними, що знаходяться в стовпцях A і B (аналогічно попередньому способу), введіть наступну формулу \u003d ВПР (A2; $ B $ 2: $ B $ 11; 1; 0) в осередок С2 і простягніть її до комірки С11.
Дана формула переглядає кожен елемент з основного масиву в порівнюваних масиві і повертає його значення, якщо воно було знайдено в стовпці B. В іншому випадку Excelвернет помилку # Н / Д.
Використання макросу VBA
Використання макросів для порівняння двох стовпців дозволяє уніфікувати процес і скоротити час на підготовку даних. Рішення про те, який результат порівняння необхідно відобразити, повністю залежить від вашої фантазії і навичок володіння макросами. Нижче представлена \u200b\u200bметодика, опублікована на офіційному сайті Мікрософт.
1 | Sub Find_Matches () |
В даному коді змінної CompareRange присвоюється діапазон з порівнюваним масивом. Потім запускається цикл, який переглядає кожен елемент у виділеному діапазоні і порівнює його з кожним елементом порівнюваного діапазону. Якщо були знайдені елементи з однаковими значеннями, макрос заносить значення елемента в стовпець С.
Щоб використовувати макрос, поверніться на робочий лист, виділіть основний діапазон (в нашому випадку, це осередки A1: A11), натисніть клавіші Alt + F8. У діалоговому вікні виберіть макрос Find_Matchesі клацніть кнопку виконати.
Після виконання макросу, результат повинен бути наступним:
Використання надбудови Inquire
підсумок
Отже, ми розглянули кілька способів порівняння даних в Excel, які допоможуть вам вирішити деякі аналітичні завдання і спростять роботу в пошуку повтояющіхся (або унікальних) значень.
У нас є дві таблиці замовлень, скопійованих в один робочий лист. Необхідно виконати порівняння даних двох таблиць в Excel і перевірити, які позиції є першою таблиці, але немає в другій. Немає сенсу вручну порівнювати значення кожного осередку.
Порівняння двох стовпців на збіги в Excel
Як зробити порівняння значень в Excel двох стовпців? Для вирішення даного завдання рекомендуємо використовувати умовне форматування, яке швидко виділити кольором позиції, що знаходяться тільки в одному стовпці. Робочий лист з таблицями:
В першу чергу необхідно присвоїти імена обом таблицями. Завдяки цьому легше зрозуміти, які порівнюються діапазони осередків:
- Виберіть інструмент «ФОРМУЛИ» - «Певні імена» - «Присвоїти ім'я».
- У вікні в полі «Ім'я:» введіть значення - Табліца_1.
- Лівою клавішею мишки зробіть клацання по полю введення «Діапазон:» і виділіть діапазон: A2: A15. І натисніть ОК.
Для другого списку виконайте ті ж дії тільки ім'я надайте - Табліца_2. А діапазон вкажіть C2: C15 - відповідно.
Корисна порада! Імена діапазонів можна привласнювати швидше за допомогою поля імен. Воно знаходиться лівіше від рядка формул. Просто виділяйте діапазони осередків, а в поле імен вводите відповідне ім'я для діапазону і натисніть Enter.
Тепер скористаємося умовним форматуванням, щоб виконати порівняння двох списків в Excel. Нам потрібно отримати наступний результат:
Позиції, які є в Табліце_1, але немає в Таблціе_2 будуть відображатися зеленим кольором. У той же час позиції, що знаходяться в Табліце_2, але відсутні в Табліце_1, будуть підсвічені синім кольором.
Принцип порівняння даних двох стовпців в Excel
При визначенні умов для форматування осередків стовпців ми використовували функцію СЧЕТЕСЛІ. В даному прикладі ця функція перевіряє скільки разів зустрічається значення другого аргументу (наприклад, A2) в списку першого аргументу (наприклад, Табліца_2). Якщо кількість разів \u003d 0 в такому випадку формула повертає значення ІСТИНА. В такому випадку осередку присвоюється призначений для користувача формат, зазначений в параметрах умовного форматування. Посилання в другому аргументі відносна, значить по черзі будуть перевірятися всі осередки виділеного діапазону (наприклад, A2: A15). Друга формула діє аналогічно. Цей же принцип можна застосовувати для різних подібних завдань. Наприклад, для порівняння двох прайсів в Excel навіть