Перейти к основному содержанию

Как извлечь уникальные значения из нескольких столбцов в Excel?

Предположим, у вас есть несколько столбцов с несколькими значениями, некоторые значения повторяются в одном или другом столбце. И теперь вы хотите найти значения, которые присутствуют в любом столбце только один раз. Есть ли какие-нибудь уловки для извлечения уникальных значений из нескольких столбцов в Excel?


Извлекайте уникальные значения из нескольких столбцов с помощью формул

В этом разделе будут рассмотрены две формулы: одна с использованием формулы массива, подходящей для всех версий Excel, а другая с использованием формулы динамического массива специально для Excel 365.

Извлекайте уникальные значения из нескольких столбцов с помощью формулы массива для всех версий Excel.

Для пользователей любой версии Excel формулы массива могут стать мощным инструментом для извлечения уникальных значений из нескольких столбцов. Вот как вы можете это сделать:

1. Предполагая, что ваши значения находятся в диапазоне A2: C9, введите следующую формулу в ячейку 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 + Ввод вместе, а затем перетащите маркер заполнения, чтобы извлечь уникальные значения, пока не появятся пустые ячейки. Смотрите скриншот:

Объяснение этой формулы:
  1. 2 австралийский доллар: 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), функция ЕСЛИ вычисляет уникальное число на основе своей строки и столбца (ROW($2:$9)*100+COLUMN($A: $С)).
    • Если условия не выполняются, функция возвращает большое число (7^8), которое служит заполнителем.
  3. МИН(...): находит наименьшее число, возвращаемое функцией ЕСЛИ, указанной выше, соответствующее местоположению следующего уникального значения.
  4. ТЕКСТ(...,"R0C00"): Преобразует это минимальное число в адрес в стиле R1C1. Код формата R0C00 указывает на преобразование числа в формат ссылки на ячейку Excel.
  5. КОСВЕННЫЙ(...): использует функцию ДВССЫЛ для преобразования адреса в стиле R1C1, сгенерированного на предыдущем шаге, обратно в обычную ссылку на ячейку в стиле A1. Функция ДВССЫЛ позволяет ссылаться на ячейку на основе содержимого текстовой строки.
  6. &"": добавление &"" в конец формулы гарантирует, что окончательный вывод будет обработан как текст, поэтому четные числа будут отображаться как текст.
 
Извлекайте уникальные значения из нескольких столбцов с помощью формулы для Excel 365

Excel 365 поддерживает динамические массивы, что значительно упрощает извлечение уникальных значений из нескольких столбцов:

Введите или скопируйте следующую формулу в пустую ячейку, в которую вы хотите поместить результат, а затем нажмите кнопку Enter ключ для получения всех уникальных значений одновременно. Смотрите скриншот:

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


Извлекайте уникальные значения из нескольких столбцов с помощью Kutools AI Aide

Познай силу Kutools AI Помощник для беспрепятственного извлечения уникальных значений из нескольких столбцов Excel. Всего за несколько кликов этот интеллектуальный инструмент анализирует ваши данные, определяя и перечисляя уникальные записи в любом выбранном диапазоне. Забудьте о сложных формулах или коде VBA; Оцените эффективность Kutools AI Помощник и превратите рабочий процесс Excel в более продуктивный и безошибочный.

Внимание: Чтобы использовать это Kutools AI Помощник of Kutools for Excel, Пожалуйста, скачайте и установите Kutools для Excel первый.

После установки Kutools for Excel, нажмите Кутулс ИИ > AI-помощник для открытия Kutools AI Помощник панель:

  1. Введите свое требование в окно чата и нажмите Отправить или нажмите Enter ключ для отправки вопроса;
    «Извлеките уникальные значения из диапазона A2:C9, игнорируя пустые ячейки, и поместите результаты, начиная с E2:»
  2. После анализа нажмите Выполнить кнопка для запуска. Kutools AI Aide обработает ваш запрос с использованием AI и вернет результаты в указанную ячейку непосредственно в Excel.


Извлекайте уникальные значения из нескольких столбцов с помощью сводной таблицы

Если вы знакомы со сводной таблицей, вы можете легко извлечь уникальные значения из нескольких столбцов, выполнив следующие действия:

1. Сначала вставьте один новый пустой столбец слева от ваших данных, в этом примере я вставлю столбец A рядом с исходными данными.

2. Щелкните одну ячейку в ваших данных и нажмите Alt + D клавиши, затем нажмите P ключ немедленно открыть Мастер сводных таблиц и диаграмм, выберите Несколько диапазонов консолидации в мастере step1 см. снимок экрана:

3. Затем нажмите Следующая кнопку, проверьте Создайте для меня одностраничное поле вариант на шаге 2 мастера, см. снимок экрана:

4. Продолжайте нажимать Следующая нажмите кнопку, щелкните, чтобы выбрать диапазон данных, который включает левый новый столбец ячеек, затем щелкните Добавить кнопку, чтобы добавить диапазон данных в Все диапазоны список, см. снимок экрана:

5. После выбора диапазона данных продолжайте нажимать Следующая, на шаге 3 мастера выберите, где вы хотите разместить отчет сводной таблицы, как хотите.

6. Наконец, нажмите Завершить чтобы завершить работу мастера, и на текущем листе была создана сводная таблица, затем снимите отметки со всех полей в Выберите поля для добавления в отчет раздел, см. снимок экрана:

7. Затем проверьте поле Значение или перетащите Значение в Ряды label, теперь вы получите уникальные значения из нескольких столбцов следующим образом:


Извлечение уникальных значений из нескольких столбцов с кодом VBA

С помощью следующего кода VBA вы также можете извлечь уникальные значения из нескольких столбцов.

1. Удерживайте ALT + F11 ключи, и он открывает Окно Microsoft Visual Basic для приложений.

2. Нажмите Вставить > Модулии вставьте следующий код в окно модуля.

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
  • Например, у меня есть диапазон данных, который содержит столбцы Name и Order, теперь для суммирования только уникальных значений в столбце Order на основе столбца Name, как показано на следующем снимке экрана. Как быстро и легко решить эту задачу в Excel?

Лучшие инструменты для офисной работы

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

Улучшите свои навыки работы с Excel с помощью Kutools for Excel и почувствуйте эффективность, как никогда раньше. Kutools for Excel предлагает более 300 расширенных функций для повышения производительности и экономии времени.  Нажмите здесь, чтобы получить функцию, которая вам нужна больше всего...

Описание


Вкладка Office: интерфейс с вкладками в Office и упрощение работы

  • Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint, Издатель, доступ, Visio и проект.
  • Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
  • Повышает вашу продуктивность на 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