При порівнянні кількох зіставних об'єктів у Excel таблицях, дані часто організують по стовпцях, щоб було зручно порівнювати характеристики цих об'єктів рядково. Наприклад, моделі автомобілів, телефони, експериментальні та контрольні групи, ряд магазинів торгової мережі та ін. При великій кількості рядків візуальний аналіз не може бути достовірним. Функції ВПР, ІНДЕКС, ПОШУКПОЗ (VLOOKUP, INDEX, MATCH) зручні для порівняння даних по осередках і не дають загальної картини. А як з'ясувати, наскільки загалом стовпці схожі між собою? Чи ідентичні стовпці?
Надбудова «Зіставити стовпці» дозволяє зіставити стовпці та побачити загальну картину:
- Порівняти два і більше стовпців один з одним
- Порівняти стовпці з еталонними значеннями
- Обчислити точний відсоток відповідності
- Подати результат у наочній зведеній таблиці
Мова відео: англійська. Субтитри: російська, англійська. (Увага: відео може не відображати останні оновлення. Використовуйте інструкцію нижче.)
Додати «Співставити стовпці» в Excel 2016, 2013, 2010, 2007
Підходить для: Microsoft Excel 2016 – 2007, desktop Office 365 (32-біт та 64-біт).
Як працювати з надбудовою:
Як порівняти два стовпці і більше один з одним та обчислити відсоток відповідності
Розглянемо приклад розробки товару. Припустимо, вам необхідно порівняти між собою кілька готових прототипів і з'ясувати, наскільки вони схожі, відмінні і навіть ідентичні.
- Натисніть кнопку «Суставити стовпці» на панелі XLTools > Виберіть «Суставити стовпці між собою».
- Натисніть кнопку ОК >
Порада:
Виберіть зведену таблицюрезультату > Натисніть піктограму Експрес-аналізу > Застосуйте «Колірну шкалу».
Прочитання результату: прототипи Тип 1 і Тип 3 практично ідентичні, показник відповідності на 99% свідчить, що 99% їх параметрів у рядках збігаються. Тип 2 і Тип 4 схожі найменше - їх параметри збігаються лише на 30%.
Як порівняти стовпці з еталонними значеннями та обчислити ступінь відповідності
Розглянемо приклад розробки товару. Припустимо, вам необхідно порівняти кілька готових прототипів із певним цільовим стандартом, а також розрахувати ступінь відповідності прототипів цим стандартам.
- Виберіть стовпці для порівняння.
Наприклад, стовпці з даними прототипів. - Натисніть кнопку «Суставити стовпці» на панелі XLTools.
- Виберіть «Суставити з діапазоном еталонних стовпців» > Виберіть стовпці еталонних значень.
Наприклад, стовпці зі стандартами. - Позначте "Стовпці містять заголовки", якщо це так.
- Позначте «Показувати відсоток відповідності», щоб рівень відповідності відображався у відсотках.
В іншому випадку результат відобразиться як 1 (повна відповідність) або 0 (немає відповідності). - Вкажіть, куди слід помістити результат: на новий або існуючий лист.
- Натисніть ОК > Готово, результат представлений у таблиці.
Порада:щоб було простіше інтерпретувати результат, застосуйте до нього умовне форматування:
Виберіть зведену таблицю результату > Натисніть піктограму Експрес-аналізу > Застосуйте «Колірну шкалу».
Читання результату: прототип Тип 2 на 99% відповідає Стандарту 2, тобто. 99% їхніх параметрів у рядках збігаються. Продукт 5 найближчий до Стандарту 3 - 96% їх параметрів ідентичні. У той же час Продукт 4 далекий від відповідності якомусь із трьох стандартів. Тепер можна дійти невтішного висновку, наскільки кожен із прототипів відхиляється від цільових еталонних значень.
У вирішенні яких завдань допоможе надбудова «Співставити стовпці»
Надбудова рядково сканує комірки та обчислює відсоток однакових значень у стовпцях. XLTools «Сіставити стовпці» не підходить для звичайного порівняння значень у осередках — вона не призначена для пошуку дублікатів чи унікальних значень.
Надбудова зіставити стовпці має інше призначення. Її головне завдання — з'ясувати, наскільки, загалом, набори даних (стовпці) схожі чи відмінні. Надбудова допомагає з аналізом великого обсягу даних, коли вам потрібно подивитися ширше, на макрорівні, напр. відповісти на такі запитання:
- Наскільки схожі показники експериментальних груп
- Наскільки схожі результати експериментальних та контрольних груп
- Наскільки схожі/відмінні кілька товарів однієї категорії
- Наскільки показники KPI співробітників близькі до планових показників
- Наскільки схожі між собою показники кількох магазинів роздрібної мережі тощо.
Інструкція
Скористайтеся вбудованою функцією порівняння осередків РАХУНКИ, якщо треба порівняти текстові значення в осередках колонки таблиці зі зразком тексту і перерахувати всі відповідні значення. Почніть із заповнення колонки текстовими значеннями, а потім в іншій колонці клацніть комірку, в якій хочете бачити результат підрахунку, та введіть відповідну формулу . Наприклад, якщо перевірені значення знаходяться в колонці A, а результат треба помістити в першу комірку колонки C, то її вміст повинен бути таким: = РАХУНКИ ($A: $ A; "Виноград") порівнюються значення всіх осередків колонки A. Можна не вказувати його у формулі , а помістити в окрему комірку (наприклад - в B1) і вставити у формулу відповідне посилання:=РАХУНКИ($A:$A;B1)
Використовуйте опції умовного форматування, Якщо треба візуально виділити в таблиці результат порівняння рядкових змінних. Наприклад, якщо треба виділити в колонці A комірки, текст у яких збігається зі зразком у комірці B1, то почніть із виділення цієї колонки - клацніть її заголовок. Потім натисніть кнопку «Умовне форматування» у групі команд «Стилі» закладки «Головна» меню Excel. Перейдіть до розділу «Правила виділення осередків» та виберіть рядок «Рівне». У вікні, що відкрилося, вкажіть комірку-зразок (клацніть клітинку B1) і виберіть у випадаючому списку варіант оформлення збігаються рядків. Потім натисніть кнопку "OK".
Використовуйте комбінацію вбудованих функцій ЯКЩО і ЗЧЕПИТИ при необхідності порівняти зі зразком не одну текстову комірку, а кілька. Функція СЧЕПИТИ з'єднує вказані їй значення одну строкову змінну. Наприклад, команда СЧЕПИТИ(A1;" і ";B1) до рядка з комірки A1 додасть текст "і", а після нього помістить рядок з комірки B1. Сконструйований таким способом рядок можна порівняти зі зразком за допомогою функції ЯКЩО. Якщо порівнювати треба не один рядок зручніше дати власне ім'я осередку-зразку. Для цього клацніть її і зліва від рядкиформул замість позначення комірки (наприклад, C1) наберіть її нове ім'я (наприклад, зразок). Потім клацніть ту комірку, в якій має бути результат порівняння, і введіть формулу:ЯКІ(СЧЕПИТИ(A1;" і ";B1)=зразок;1;0)Тут одиниця - це значення, яке міститиме комірка з формулою, якщо порівняння дасть позитивний результат, а нуль – для негативного результату. Розмножити цю формулу на все рядкитаблиці, які потрібно порівняти зі зразком дуже просто - наведіть курсор на правий нижній кут комірки і, коли курсор зміниться (стане чорним хрестиком), натисніть ліву кнопку миші і розтягніть цю комірку вниз до останньої порівнюваної рядки.
Це розділ із книги: Майкл Гірвін. Ctrl+Shift+Enter. Освоєння формул масиву Excel.
Вибірки, що ґрунтуються на одному або кількох умовах.Ряд функцій Excelвикористовують оператори порівняння. Наприклад, СУМІСЛІ, СУМІСЛІМН, РАХУНКИ, РАХУНОК, РАХУНКИ, РОЗЗНАЧИЛИ і РОЗНАЧЛИМН. Ці функції здійснюють вибірки з урахуванням однієї чи кількох умов (критеріїв). Проблема в тому, що ці функції можуть лише складати, підраховувати кількість і знаходити середню. А якщо ви хочете накласти умови на пошук, наприклад максимального значення або стандартного відхилення? У цих випадках, оскільки немає вбудованої функції, ви повинні винайти формулу масиву. Нерідко це з використанням оператора порівняння масивів. Перший приклад у цьому розділі показує, як розрахувати мінімальне значення за однієї умови.
Скористайтеся функцією ЯКЩО, щоб вибрати елементи масиву, що відповідають умові. На рис. 4.1 у лівій таблиці присутні стовпець із назвами міст та стовпець з часом. Потрібно знайти мінімальний час для кожного міста і помістити це значення у відповідну комірку правої таблиці. Умова для вибірки – назва міста. Якщо ви використовуєте функцію МІН, то зможете знайти мінімальне значення стовпця В. Але як ви оберете тільки ті числа, що відносяться тільки до Окленду? І як вам скопіювати формули вниз колонкою? Оскільки в Excel немає вбудованої функції МІНЕСЛІ, вам необхідно написати оригінальну формулу, що поєднує функції ЯКЩО та МІН.
Мал. 4.1. Мета формули: вибрати мінімальний час для кожного міста
Завантажити нотатку у форматі або у форматі
Як показано на рис. 4.2, вам слід розпочати введення формули в комірку E3 з функції МІН. Але ж ви не можете помістити в аргумент число1всі значення стовпця B!? Ви хочете відібрати лише ті значення, які стосуються Окленду.
Як показано на рис. 4.3, на наступному етапі введіть функцію ЯКЩО як аргумент число1для МІН. Ви вклали ЯКЩО всередину МІН.
Розмістивши курсор у місці введення аргументу лог_виразфункції ЯКЩО (рис. 4.4), ви виділяєте діапазон із назвами міст А3:А8, а потім натискаєте F4, щоб зробити посилання на комірки абсолютними (докладніше див., наприклад, ). Потім ви набираєте порівняльний оператор – знак рівності. Нарешті, ви виділіть комірку ліворуч від формули – D3, залишаючи посилання на неї відносною. Сформульована умова дозволить вибрати тільки Окленд при перегляді діапазону А3:А8.
Мал. 4.4. Створіть оператор масиву в аргументі лог_виразфункції ЯКЩО
Отже, ви створили оператора масиву за допомогою оператора порівняння. У будь-який момент обробки масиву оператор масиву є оператором порівняння, тому результатом його роботи буде масив, що складається зі значень ІСТИНА і БРЕХНЯ. Щоб переконатися в цьому, виділіть масив (для цього клацніть у підказці на аргумент лог_вираз) та натисніть F9 (рис. 4.5). Зазвичай ви використовуєте один аргумент лог_вираз,повертає або ІСТИНУ, або БРЕХНЯ; тут же результуючий масив поверне кілька значень ІСТИНИ та ЛЖИ, так що функція МІН вибере мінімальну кількість тільки для тих міст, які відповідають значенню ІСТИНА.
Мал. 4.5. Щоб побачити масив, що складається зі значень ІСТИНА і брехня, клацніть у підказці на аргумент лог_виразта натисніть F9
Ми маємо дві таблиці замовлень, скопійованих в один робочий лист. Необхідно виконати порівняння даних двох таблиць в Excel і перевірити, які позиції є першою таблицею, але немає в другій. Немає сенсу вручну порівнювати значення кожного осередку.
Порівняння двох стовпців на збіги в Excel
Як зробити порівняння значень у Excel двох стовпців? Для вирішення цієї задачі рекомендуємо використовувати умовне форматування, яке швидко виділити кольором позиції, що знаходяться лише в одному стовпчику. Робочий лист із таблицями:
Насамперед необхідно присвоїти імена обом таблицям. Завдяки цьому легше зрозуміти, які порівнюються діапазони осередків:
- Виберіть інструмент «ФОРМУЛИ»-«Визначені імена»-«Присвоїти ім'я».
- У вікні, що з'явилося в полі «Ім'я:» введіть значення – Таблица_1.
- Клацніть лівою кнопкою мишки на полі введення «Діапазон:» і виділіть діапазон: A2:A15. І натисніть OK.
Для другого списку виконайте ті ж дії тільки назву присвойте – Таблица_2. А діапазон вкажіть C2: C15 відповідно.
Корисна порада! Імена діапазонів можна надавати швидше за допомогою поля імен. Воно знаходиться ліворуч від рядка формул. Просто виділяйте діапазони осередків, а в полі імен введіть відповідне ім'я для діапазону та натисніть Enter.
Тепер скористаємося умовним форматуванням, щоб порівняти два списки в Excel. Нам потрібно отримати наступний результат:
Позиції, які є в Таблиці_1, але немає в Таблиці_2, будуть відображатися зеленим кольором. У той же час, позиції, що знаходяться в Таблиці_2, але відсутні в Таблиці_1, будуть підсвічені синім кольором.
Принцип порівняння даних двох стовпців в Excel
При визначенні умов для форматування осередків стовпців ми використовували функцію РАХУНКИ. У даному прикладіця функція перевіряє, скільки разів зустрічається значення другого аргументу (наприклад, A2) у списку першого аргументу (наприклад, Таблица_2). Якщо кількість разів = 0 у такому разі формула повертає значення ІСТИНА. У такому випадку осередку присвоюється формат користувача, зазначений у параметрах умовного форматування. Посилання у другому аргументі відносне, отже по черзі будуть перевірені всі осередки виділеного діапазону (наприклад, A2:A15). Друга формула діє аналогічно. Цей принцип можна застосовувати для різних подібних завдань. Наприклад, для порівняння двох прайсів в Excel навіть
