Перейти до основного матеріалу

Як витягти унікальні значення з декількох стовпців у 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"),)&""
примітки: У наведеній вище формулі, A2: C9 вказує діапазон комірок, для яких потрібно витягти унікальні значення, E1: E1 - це перша комірка стовпця, куди потрібно помістити результат, $ 2: $ 9 підставки для рядків містять клітинки, які ви хочете використовувати, і $ A: $ C вказує, що стовпці містять клітинки, які ви хочете використовувати. Будь ласка, змініть їх на власні.

2. Потім натисніть Shift + Ctrl + Enter клавіші разом, а потім перетягніть маркер заповнення, щоб витягти унікальні значення, поки не з'являться порожні комірки. Дивіться знімок екрана:

Пояснення цієї формули:
  1. $ A $ 2: $ C $ 9: це вказує діапазон даних для перевірки, тобто комірки від A2 до C9.
  2. 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), яке служить заповнювачем.
  3. ХВ(...): знаходить найменше число, яке повертає функція IF вище, що відповідає розташуванню наступного унікального значення.
  4. ТЕКСТ(...,"R0C00"): перетворює це мінімальне число на адресу в стилі R1C1. Код формату R0C00 вказує на перетворення числа у формат посилання на комірку Excel.
  5. НЕПРЯМИЙ (...): використовує функцію INDIRECT для перетворення адреси стилю R1C1, згенерованої на попередньому кроці, назад у звичайне посилання на комірку стилю A1. Функція INDIRECT дозволяє посилатися на клітинку на основі вмісту текстового рядка.
  6. &"": Додавання &"" у кінці формули гарантує, що кінцевий результат розглядатиметься як текст, тому парні числа відображатимуться як текст.
 
Отримайте унікальні значення з кількох стовпців за допомогою формули для Excel 365

Excel 365 підтримує динамічні масиви, що значно полегшує вилучення унікальних значень із кількох стовпців:

Будь ласка, введіть або скопіюйте наведену нижче формулу в порожню комірку, де ви хочете розмістити результат, а потім натисніть Що натомість? Створіть віртуальну версію себе у ключ, щоб отримати всі унікальні значення одночасно. Перегляньте скріншот:

=UNIQUE(TOCOL(A2:C9,1))


Отримайте унікальні значення з кількох стовпців за допомогою Kutools AI Aide

Розкрийте силу Kutools AI Aide щоб безперешкодно витягувати унікальні значення з кількох стовпців у Excel. Лише кількома клацаннями миші цей інтелектуальний інструмент відсіює ваші дані, ідентифікуючи та перераховуючи унікальні записи в будь-якому вибраному діапазоні. Забудьте про складні формули чи код vba; відчуйте ефективність Kutools AI Aide і перетворите робочий процес Excel на більш продуктивний і безпомилковий досвід.

примітки: Щоб використовувати це Kutools AI Aide of Kutools для Excel, будь ласка завантажте та встановіть Kutools для Excel перший.

Після встановлення Kutools для Excel натисніть Kutools AI > А. І. помічник відкрити Kutools AI Aide панель:

  1. Введіть свою вимогу в поле чату та натисніть Відправити кнопку або натисніть Що натомість? Створіть віртуальну версію себе у ключ для відправки питання;
    "Видобути унікальні значення з діапазону A2:C9, ігноруючи порожні клітинки, і розмістити результати, починаючи з E2:"
  2. Після аналізу натисніть Виконати кнопку для запуску. 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?

Найкращі інструменти продуктивності офісу

🤖 Kutools AI Aide: Революціонізуйте аналіз даних на основі: Інтелектуальне виконання   |  Згенерувати код  |  Створення спеціальних формул  |  Аналізуйте дані та створюйте діаграми  |  Викликати функції Kutools...
Популярні функції: Знайдіть, виділіть або визначте дублікати   |  Видалити порожні рядки   |  Об’єднайте стовпці або клітинки без втрати даних   |   Раунд без Формули ...
Супер пошук: VLookup за кількома критеріями    Багатозначний VLookup  |   VLookup на кількох аркушах   |   Нечіткий пошук ....
Розширений розкривний список: Швидке створення випадаючого списку   |  Залежний спадний список   |  Виберіть розкривний список, що вибирається ....
Менеджер колонок: Додайте конкретну кількість стовпців  |  Перемістити стовпці  |  Перемкнути статус видимості прихованих стовпців  |  Порівняйте діапазони та стовпці ...
Особливості: Фокус сітки   |  Перегляд дизайну   |   Велика панель формул    Диспетчер робочих книг і аркушів   |  Бібліотека ресурсів (автотекст)   |  Вибір дати   |  Об’єднайте робочі аркуші   |  Шифрування/розшифрування клітинок    Надсилайте листи за списком   |  Супер фільтр   |   Спеціальний фільтр (фільтр жирний/курсив/закреслений...) ...
Топ-15 наборів інструментів12 текст Tools (додати текст, Видалити символи, ...)   |   50 + Графік типи (діаграма Ганта, ...)   |   40+ Практичний Формули (Розрахуйте вік на основі дня народження, ...)   |   19 вставка Tools (Вставте QR-код, Вставити зображення зі шляху, ...)   |   12 Перетворення Tools (Числа до слів, Валютна конверсія, ...)   |   7 Злиття та розділення Tools (Розширені комбіновані ряди, Розділені клітини, ...)   |   ... і більше

Покращуйте свої навички Excel за допомогою Kutools для Excel і відчуйте ефективність, як ніколи раніше. Kutools для Excel пропонує понад 300 додаткових функцій для підвищення продуктивності та економії часу.  Натисніть тут, щоб отримати функцію, яка вам найбільше потрібна...

Опис


Вкладка Office Передає інтерфейс із вкладками в Office і значно полегшує вашу роботу

  • Увімкніть редагування та читання на вкладках у Word, Excel, PowerPoint, Publisher, Access, Visio та Project.
  • Відкривайте та створюйте кілька документів на нових вкладках того самого вікна, а не в нових вікнах.
  • Збільшує вашу продуктивність на 50% та зменшує сотні клацань миші для вас щодня!
Comments (31)
Rated 5 out of 5 · 1 ratings
This comment was minimized by the moderator on the site
Thank you for this solution, however what if the columns are in separate excel sheet? or is in separate columns instead of a table?
This comment was minimized by the moderator on the site
Hello, Jon,
The methods in this article ar only works well for a range of data, if your data in separate columns, you should copy and paste them into one range first, and then apply the formula or VBA code.
Thank you!
This comment was minimized by the moderator on the site
Thank you for this great article.

For people who are using the array formular in non-English Excel there must be taken special care of the text format string: in your example: "R0C00".
For German this would translate to "Z0S00". However, "S" is a special character refering to seconds for time formating. This character needs to be escaped and therefore the correct format string for German Excel is "Z0\S00".

I hope this helps someone in the future :-)
This comment was minimized by the moderator on the site
Hello, Michael H.
Thanks for your kindly explanation.
Hope this can help others in the future.😄
This comment was minimized by the moderator on the site
Thank you for this great article.

For people who are using the array formular in non-English Excel there must be taken special care of the text format string: in your example: "R0C00".
For German this would translate to "Z0S00". However, "S" is a special character refering to seconds for time formating. This character needs to be escaped and therefore the correct format string for German Excel is "Z0\S00".

I hope this helps someone in the future :-)
This comment was minimized by the moderator on the site
Hello Sir! The VBA worked wonders, thank you very much for that! I was wondering, If I change the original data, is it possible to refresh the column with the unique values automatically?
Rated 5 out of 5
This comment was minimized by the moderator on the site
Hello Ioannis,

Glad to help. After you change the original data, the VBA can not refresh the result automatically. And the easiest way I can think of is to press Ctrl + Alt + F9 to refresh all results in worksheets in all open workbooks. Have a nice day.

Sincerely,
Mandy
This comment was minimized by the moderator on the site
The array formula at the top is working great when used with data in the same sheet, however when I try to use it to reference the same exact data from another sheet the formula returns nothing. I'm unable to figure out why. Is there a limitation with array functions that prevents you from referencing ranges in a different sheet?

Thanks for any insight you can provide.
This comment was minimized by the moderator on the site
Hello Erin,

Glad to help. The INDIRECT function in this formula is more complicated to use when referencing data in other worksheets. It is not recommended to use this feature when referencing ranges in different worksheets.

For example: Now the data is in Sheet1, I want to reference the content of cell C2 of Sheet1 in Sheet2. First, in any two cells in Sheet2, such as D1 and D2, enter Sheet1 and C2, respectively. At this point, enter the formula in the empty cell of Sheet2:
=INDIRECT("'"&D1&"'!"&D2), then the content of cell C2 in Sheet1 can be returned.

As you can see, it make things way more complex. Hope my explanation can help. Have a nice day.

Sincerely,
Mandy
This comment was minimized by the moderator on the site
Czy to żart?
This comment was minimized by the moderator on the site
can we create uniqdata function instead of macro?
This comment was minimized by the moderator on the site
Hi, İlhan,If you like a User Defined Function to create a formula for solving this problem, the below code may help you:After inserting the code, select a list of cells where you want to put the results. Then type this formula:=Uniques(A1:C4)  in the formula bar.Press Ctrl+Shift+Enter keys together. 


Function Uniques(ByVal inputRange As Range)
Dim inputArray As Variant
Dim myColl As New Collection
Dim xVal As Variant
Dim outArray() As Variant
On Error Resume Next
With inputRange
inputArray = Application.Intersect(.Cells, .Parent.UsedRange).Value
End With
On Error GoTo 0
On Error Resume Next
For Each xVal In inputArray
myColl.*** Item:=xVal, Key:=(CStr(xVal) & TypeName(xVal))
Next xVal
myColl.Remove "String"
On Error GoTo 0
ReDim outArray(1 To Application.Max(myColl.Count, Application.Caller.Cells.Count))
For xVal = 1 To UBound(outArray)
outArray(xVal) = vbNullString
Next xVal
For xVal = 1 To Application.Min(myColl.Count, Application.Caller.Cells.Count)
outArray(xVal) = myColl(xVal)
Next xVal
If Application.Caller.Columns.Count = 1 Then
Uniques = Application.Transpose(outArray)
Else
Uniques = outArray
End If
End Function
This comment was minimized by the moderator on the site
Thanks for the code. I'm using the VBA code of this page. Is there a way to add a sorting code after the unique values are extracted so it sorts it automatically?
This comment was minimized by the moderator on the site
Regarding the formula version, could you explain in more detail what this portion is doing? *100+COLUMN($A:$C),7^8)),"R0C00") Specifically, what are the *100, 7^8, and "R0C000" doing? I'm understanding everything else, but I can't figure out what these are for.
This comment was minimized by the moderator on the site
Little late for my response here but...
ROW($2:$9)*100 - this is multiplying the row number *100, so if it's in row 5, now the number is 500
COLUMN($A:$C) - this gets added to the row*100 number, so if it's row 5 col 2, then the number is 502.
7^8)), - this (I think) is to have a max value for the min statement from earlier.
"R0C00") - this formats the text based on the number. In the example, we had 502 so this gives R5C02 (row 5, col 02).

If you have a lot of columns but not many rows, then you could change it to ROW($2:$9)*1000+COLUMN($A:$C),7^8)),"R0C000")
This comment was minimized by the moderator on the site
i've adjusted to my sheet but am only returning the first value in the defined array... what am i missing?
This comment was minimized by the moderator on the site
Hello, Cody,
The above formula works well in my worksheet, could you give a screenshot of your data problem here?
Thank you!
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations