зміст
Класична ситуація: у вас є два списки, які потрібно об'єднати в один. Причому в початкових списках можуть бути як унікальні елементи, так і збігаються (як між списками, так і всередині), але на виході потрібно отримати список без дублікатів (повторів):
Традиційно розглянемо кілька способів вирішення такої поширеної проблеми – від примітивних «в лоб» до більш складних, але елегантних.
Спосіб 1: Видалення дублікатів
Вирішити проблему можна найпростішим способом – вручну скопіювати елементи обох списків в один, а потім застосувати інструмент до отриманого набору. Видалити дублікати із вкладки дані (Дані — видалити дублікати):
Звісно, цей спосіб не спрацює, якщо дані у вихідних списках часто змінюються – доведеться повторювати всю процедуру після кожної зміни заново.
Спосіб 1а. зведена таблиця
Цей спосіб, по суті, є логічним продовженням попереднього. Якщо списки не дуже великі і максимальна кількість елементів в них відома заздалегідь (наприклад, не більше 10), то можна об'єднати дві таблиці в одну прямими посиланнями, додати стовпець з одиницями праворуч і на основі отриманої таблиці побудувати зведену таблицю:
Як відомо, зведена таблиця ігнорує повторення, тому на виході ми отримаємо зведений список без дублікатів. Допоміжний стовпець з 1 потрібен тільки тому, що Excel може будувати зведені таблиці, що містять принаймні два стовпці.
Коли вихідні списки буде змінено, нові дані потраплять до об’єднаної таблиці через прямі посилання, але зведену таблицю доведеться оновлювати вручну (клацніть правою кнопкою миші – Update & Save). Якщо вам не потрібен перерахунок на ходу, то краще скористатися іншими варіантами.
Спосіб 2: формула масиву
Розв’язати задачу можна за допомогою формул. У цьому випадку перерахунок і оновлення результатів буде відбуватися автоматично і миттєво, відразу після зміни вихідних списків. Для зручності та стислості назвемо наші списки. Список 1 и Список 2використання Менеджер імен таб формула (Формули — Менеджер імен — Створити):
Після іменування потрібна нам формула буде виглядати так:
На перший погляд це виглядає моторошно, але, насправді, все не так страшно. Дозвольте мені розгорнути цю формулу на кілька рядків за допомогою комбінації клавіш Alt+Enter і зробити відступ пробілами, як ми це зробили, наприклад, тут:
Логіка тут така:
- Формула INDEX(List1;MATCH(0;COUNTIF($E$1:E1;List1); 0) вибирає всі унікальні елементи з першого списку. Як тільки вони закінчуються, починає видавати помилку #N/A:
- Формула INDEX(List2;MATCH(0;COUNTIF($E$1:E1;List2); 0)) витягує унікальні елементи з другого списку таким же чином.
- Вкладені одна в одну дві функції IFERROR реалізують виведення спочатку унікальних зі списку-1, а потім зі списку-2 по черзі.
Зауважте, що це формула масиву, тобто після введення її потрібно ввести в клітинку, яка не є звичайною Що натомість? Створіть віртуальну версію себе у , але за допомогою комбінації клавіш Ctrl+Shift+ Що натомість? Створіть віртуальну версію себе у а потім скопіюйте (перетягніть) униз до дочірніх клітинок із полем.
В англійській версії Excel ця формула виглядає так:
=ЯКЩОПОМИЛКА(ЯКЩОПОМИЛКА(ІНДЕКС(Список1, MATCH(0, COUNTIF($E$1:E1, List1), 0)), INDEX(Список2, MATCH(0, COUNTIF($E$1:E1, List2), 0)) ), “”)
Мінусом такого підходу є те, що формули масивів помітно сповільнюють роботу з файлом, якщо вихідні таблиці мають велику (кілька сотень і більше) кількість елементів.
Спосіб 3. Power Query
Якщо ваші вихідні списки містять велику кількість елементів, наприклад, кілька сотень або тисяч, то замість повільної формули масиву краще використовувати принципово інший підхід, а саме інструменти надбудови Power Query. Ця надбудова вбудована в Excel 2016 за замовчуванням. Якщо у вас Excel 2010 або 2013, ви можете завантажити та встановити його окремо (безкоштовно).
Алгоритм дій наступний:
- Відкрийте окрему вкладку встановленого доповнення PowerQuery (якщо у вас Excel 2010-2013) або просто перейдіть на вкладку дані (якщо у вас Excel 2016).
- Виберіть перший список і натисніть кнопку З таблиці/діапазону (З діапазону/таблиці). Коли нас запитують про створення «розумного столу» з нашого списку, ми погоджуємося:
- Відкриється вікно редактора запитів, де ви побачите завантажені дані та назву запиту Таблиця 1 (ви можете змінити його на свій, якщо хочете).
- Двічі клацніть заголовок таблиці (слово Список 1) і перейменуйте його на будь-яке інше (наприклад Люди). Як саме назвати не важливо, але придумане ім'я потрібно запам'ятати, т. к. його потрібно буде використовувати знову пізніше під час імпорту другої таблиці. Об’єднання двох таблиць у майбутньому працюватиме, лише якщо їхні заголовки стовпців збігаються.
- Розгорніть спадний список у верхньому лівому куті закрити і завантажити І вибирай Закрийте та завантажте в… (Закрити&Завантажити до…):
- У наступному діалоговому вікні (воно може виглядати дещо інакше – не лякайтеся) виберіть Просто створіть зв'язок (тільки створити з'єднання):
- Повторюємо всю процедуру (пункти 2-6) для другого списку. Перейменовуючи заголовок стовпця, важливо використовувати те саме ім’я (Люди), що й у попередньому запиті.
- У вікні Excel на вкладці дані або на вкладці PowerQuery Вибирати Отримати дані – Об’єднати запити – Додати (Отримати дані — Об’єднати запити — Додати):
- У діалоговому вікні, що з’явиться, виберіть наші запити з випадаючих списків:
- В результаті ми отримаємо новий запит, де два списки будуть підключені один під одним. Залишилося видалити дублікати кнопкою Видалити рядки – видалити дублікати (Видалити рядки — видалити дублікати):
- Готовий запит можна перейменувати в правій частині панелі параметрів, давши йому розумне ім’я (насправді це буде назва таблиці результатів), і все можна завантажити на аркуш за допомогою команди закрити і завантажити (Закрити та завантажити):
Надалі, при будь-яких змінах або доповненнях до вихідних списків, для оновлення таблиці результатів достатньо буде просто клацнути правою кнопкою миші.
- Як зібрати кілька таблиць із різних файлів за допомогою Power Query
- Вилучення унікальних елементів зі списку
- Як порівняти два списки один з одним на збіги та відмінності