Як витягти унікальні значення з декількох стовпців у Excel?
Припустимо, у вас є кілька стовпців з кількома значеннями, деякі значення повторюються в одному стовпці або в іншому стовпці. І тепер ви хочете знайти значення, які є в кожному стовпці лише один раз. Чи існують якісь швидкі підказки для вилучення унікальних значень із декількох стовпців у Excel?
Отримайте унікальні значення з кількох стовпців за допомогою формул
У цьому розділі розглядатимуться дві формули: одна використовує формулу масиву, яка підходить для всіх версій Excel, а інша використовує формулу динамічного масиву спеціально для Excel 365.
Отримайте унікальні значення з кількох стовпців за допомогою формули масиву для всіх версій Excel
Для користувачів будь-якої версії Excel формули масиву можуть бути потужним інструментом для отримання унікальних значень у кількох стовпцях. Ось як ви можете це зробити:
1. Припускаючи ваші значення в діапазоні А2: С9, введіть наступну формулу в клітинку E2:
=INDIRECT(TEXT(MIN(IF(($A$2:$C$9<>"")*(COUNTIF($E$1:E1,$A$2:$C$9)=0),ROW($2:$9)*100+COLUMN($A:$C),7^8)),"R0C00"),)&""
2. Потім натисніть Shift + Ctrl + Enter клавіші разом, а потім перетягніть маркер заповнення, щоб витягти унікальні значення, поки не з'являться порожні комірки. Дивіться знімок екрана:
- $ A $ 2: $ C $ 9: це вказує діапазон даних для перевірки, тобто комірки від A2 до C9.
- IF(($A$2:$C$9<>"")*(COUNTIF($E$1:E1,$A$2:$C$9)=0), ROW($2:$9)*100+COLUMN($A:$C), 7^8):
- $A$2:$C$9<>"" перевіряє, чи клітинки в діапазоні не порожні.
- COUNTIF($E$1:E1,$A$2:$C$9)=0 визначає, чи значення цих клітинок ще не були перераховані в діапазоні клітинок від E1 до E1.
- Якщо виконуються обидві умови (тобто значення не порожнє та ще не зазначено в стовпці E), функція IF обчислює унікальне число на основі свого рядка та стовпця (ROW($2:$9)*100+COLUMN($A: $C)).
- Якщо умови не виконуються, функція повертає велике число (7^8), яке служить заповнювачем.
- ХВ(...): знаходить найменше число, яке повертає функція IF вище, що відповідає розташуванню наступного унікального значення.
- ТЕКСТ(...,"R0C00"): перетворює це мінімальне число на адресу в стилі R1C1. Код формату R0C00 вказує на перетворення числа у формат посилання на комірку Excel.
- НЕПРЯМИЙ (...): використовує функцію INDIRECT для перетворення адреси стилю R1C1, згенерованої на попередньому кроці, назад у звичайне посилання на комірку стилю A1. Функція INDIRECT дозволяє посилатися на клітинку на основі вмісту текстового рядка.
- &"": Додавання &"" у кінці формули гарантує, що кінцевий результат розглядатиметься як текст, тому парні числа відображатимуться як текст.
Отримайте унікальні значення з кількох стовпців за допомогою формули для Excel 365
Excel 365 підтримує динамічні масиви, що значно полегшує вилучення унікальних значень із кількох стовпців:
Будь ласка, введіть або скопіюйте наведену нижче формулу в порожню комірку, де ви хочете розмістити результат, а потім натисніть Що натомість? Створіть віртуальну версію себе у ключ, щоб отримати всі унікальні значення одночасно. Перегляньте скріншот:
=UNIQUE(TOCOL(A2:C9,1))
Отримайте унікальні значення з кількох стовпців за допомогою Kutools AI Aide
Розкрийте силу Kutools AI Aide щоб безперешкодно витягувати унікальні значення з кількох стовпців у Excel. Лише кількома клацаннями миші цей інтелектуальний інструмент відсіює ваші дані, ідентифікуючи та перераховуючи унікальні записи в будь-якому вибраному діапазоні. Забудьте про складні формули чи код vba; відчуйте ефективність Kutools AI Aide і перетворите робочий процес Excel на більш продуктивний і безпомилковий досвід.
Після встановлення Kutools для Excel натисніть Kutools AI > А. І. помічник відкрити Kutools AI Aide панель:
- Введіть свою вимогу в поле чату та натисніть Відправити кнопку або натисніть
Що натомість? Створіть віртуальну версію себе у
ключ для відправки питання;
"Видобути унікальні значення з діапазону A2:C9, ігноруючи порожні клітинки, і розмістити результати, починаючи з E2:" - Після аналізу натисніть Виконати кнопку для запуску. Kutools AI Aide обробить ваш запит за допомогою штучного інтелекту та поверне результати у вказану клітинку безпосередньо в Excel.
Витягніть унікальні значення з декількох стовпців за допомогою зведеної таблиці
Якщо ви знайомі зі зведеною таблицею, ви можете легко витягти унікальні значення з кількох стовпців, виконавши такі дії:
1. Спочатку вставте один новий порожній стовпець ліворуч від ваших даних, у цьому прикладі я вставлю стовпець A поруч із вихідними даними.
2. Клацніть одну клітинку у ваших даних і натисніть Alt + D клавіші, потім натисніть P негайно відкрити Майстер зведеної таблиці та зведеної діаграмивиберіть Кілька діапазонів консолідації у майстрі step1 див. скріншот:
3. Потім натисніть МАЙБУТНІ кнопку, перевірити Створіть для мене одне поле сторінки параметр у майстрі step2, див. скріншот:
4. Продовжуйте натискати МАЙБУТНІ натисніть, щоб вибрати діапазон даних, який включає лівий новий стовпець комірок, а потім натисніть додавати , щоб додати діапазон даних до Всі діапазони вікно списку, див. знімок екрана:
5. Вибравши діапазон даних, продовжуйте клацнути МАЙБУТНІ, у кроці 3 майстра виберіть, куди ви хочете додати звіт зведеної таблиці, як вам подобається.
6. Нарешті клацніть обробка для завершення роботи майстра, а на поточному аркуші створена зведена таблиця, а потім зніміть усі поля з Виберіть поля, які потрібно додати до звіту розділ, див. знімок екрана:
7. Потім перевірте поле значення або перетягніть значення в ряди ярлик, тепер ви отримаєте унікальні значення з декількох стовпців наступним чином:
Витягніть унікальні значення з декількох стовпців за допомогою коду VBA
За допомогою наступного коду VBA ви також можете витягти унікальні значення з декількох стовпців.
1. Утримуйте клавішу ALT + F11 і відкриває Вікно Microsoft Visual Basic для програм.
2. Клацання Insert > Модуліта вставте наступний код у вікно модуля.
VBA: Витяг унікальних значень із кількох стовпців
Sub Uniquedata()
'Updateby Extendoffice
Dim rng As Range
Dim InputRng As Range, OutRng As Range
Set dt = CreateObject("Scripting.Dictionary")
xTitleId = "KutoolsforExcel"
Set InputRng = Application.Selection
Set InputRng = Application.InputBox("Range :", xTitleId, InputRng.Address, Type:=8)
Set OutRng = Application.InputBox("Out put to (single cell):", xTitleId, Type:=8)
For Each rng In InputRng
If rng.Value <> "" Then
dt(rng.Value) = ""
End If
Next
OutRng.Range("A1").Resize(dt.Count) = Application.WorksheetFunction.Transpose(dt.Keys)
End Sub
3. Потім натисніть F5 для запуску цього коду, і з’явиться вікно із запитом, щоб нагадати вам про вибір діапазону даних, який ви хочете використовувати. Дивіться знімок екрана:
4. А потім клацніть OK, з’явиться інше підказне вікно, яке дозволить вам вибрати місце для розміщення результату, див. знімок екрана:
5. Натисніть OK щоб закрити це діалогове вікно, і всі унікальні значення були витягнуті відразу.
Більше відносних статей:
- Підрахуйте кількість унікальних та чітких значень зі списку
- Припустимо, у вас довгий список значень з деякими повторюваними елементами, тепер ви хочете підрахувати кількість унікальних значень (значення, які відображаються у списку лише один раз) або різних значень (усі різні значення у списку, це означає унікальні значення + 1-е повторюване значення) у стовпці, як показано на екрані ліворуч. У цій статті я розповім про те, як боротися з цією роботою в Excel.
- Витяг унікальних значень на основі критеріїв у Excel
- Припустимо, у вас є такий діапазон даних, для якого ви хочете перерахувати лише унікальні імена стовпця B на основі конкретного критерію стовпця A, щоб отримати результат, як показано нижче. Як ви могли швидко та легко впоратися з цим завданням у Excel?
- Дозволити лише унікальні значення в Excel
- Якщо ви хочете зберегти лише унікальні значення, що вводяться в стовпець робочого аркуша, і запобігти дублікатам, ця стаття запропонує кілька швидких прийомів для вирішення цього завдання.
- Сума унікальних значень на основі критеріїв у Excel
- Наприклад, у мене є діапазон даних, який містить стовпці Ім'я та Порядок, тепер для підсумовування лише унікальних значень у стовпці Замовлення на основі стовпця Ім'я, як показано на наступному знімку екрана. Як швидко та легко вирішити це завдання в Excel?
Найкращі інструменти продуктивності офісу
Покращуйте свої навички Excel за допомогою Kutools для Excel і відчуйте ефективність, як ніколи раніше. Kutools для Excel пропонує понад 300 додаткових функцій для підвищення продуктивності та економії часу. Натисніть тут, щоб отримати функцію, яка вам найбільше потрібна...
Вкладка Office Передає інтерфейс із вкладками в Office і значно полегшує вашу роботу
- Увімкніть редагування та читання на вкладках у Word, Excel, PowerPoint, Publisher, Access, Visio та Project.
- Відкривайте та створюйте кілька документів на нових вкладках того самого вікна, а не в нових вікнах.
- Збільшує вашу продуктивність на 50% та зменшує сотні клацань миші для вас щодня!