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

Предположим, у вас есть несколько столбцов с множеством значений, где некоторые значения повторяются в пределах одного столбца или в разных столбцах. Теперь вам нужно определить значения, которые являются уникальными для всех столбцов. Существуют ли быстрые методы для извлечения этих уникальных значений из нескольких столбцов в Excel? Давайте рассмотрим несколько эффективных решений.
- Извлечение уникальных значений из нескольких столбцов с помощью формул
- Извлечение уникальных значений из нескольких столбцов с помощью Kutools AI Ассистент
- Извлечение уникальных значений из нескольких столбцов с помощью сводной таблицы
- Извлечение уникальных значений из нескольких столбцов с помощью кода VBA
Извлечение уникальных значений из нескольких столбцов с помощью формул
В этом разделе будут рассмотрены две формулы: одна использует массивную формулу, подходящую для всех версий 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"),)&""
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), которое служит заполнителем.
- MIN(...): Находит наименьшее число, возвращаемое функцией IF выше, соответствующее местоположению следующего уникального значения.
- TEXT(...,"R0C00"): Преобразует это минимальное число в адрес стиля R1C1. Форматный код R0C00 указывает преобразование числа в формат ссылки на ячейку Excel.
- INDIRECT(...): Использует функцию INDIRECT для преобразования адреса стиля R1C1, созданного на предыдущем шаге, обратно в обычную ссылку на ячейку стиля A1. Функция INDIRECT позволяет ссылаться на ячейки на основе содержимого текстовой строки.
- &"": Добавление &"" в конце формулы гарантирует, что окончательный вывод будет рассматриваться как текст, так что даже числа будут отображаться как текст.
Извлечение уникальных значений из нескольких столбцов с помощью формулы для Excel 365, Excel 2021 и более новых версий
Excel 365, Excel 2021 и более новые версии поддерживают динамические массивы, что значительно упрощает извлечение уникальных значений из нескольких столбцов:
Пожалуйста, введите или скопируйте следующую формулу в пустую ячейку, куда вы хотите поместить результат, а затем нажмите клавишу Enter, чтобы сразу получить все уникальные значения. Смотрите скриншот:
=UNIQUE(TOCOL(A2:C9,1))
Извлечение уникальных значений из нескольких столбцов с помощью Kutools AI Ассистент
Раскройте мощь "Kutools AI Ассистент", чтобы легко извлекать уникальные значения из нескольких столбцов в Excel. С несколькими кликами этот интеллектуальный инструмент просматривает ваши данные, выявляя и перечисляя уникальные записи в любом выбранном диапазоне. Забудьте о сложных формулах или коде VBA; воспользуйтесь эффективностью "Kutools AI Ассистент" и превратите ваш рабочий процесс в Excel в более продуктивный и безошибочный опыт.
После установки Kutools для Excel, пожалуйста, нажмите "Kutools AI" > "AI Ассистент", чтобы открыть панель "Kutools AI Ассистент":
- Введите ваш запрос в чат-окно и нажмите кнопку "Отправить" или клавишу Enter, чтобы отправить вопрос;
"Извлечь уникальные значения из диапазона A2:C9, игнорируя пустые ячейки, и разместить результаты начиная с E2:" - После анализа нажмите кнопку "Выполнить", чтобы запустить. Kutools AI Ассистент обработает ваш запрос с использованием ИИ и вернет результаты прямо в указанную ячейку в Excel.
Извлечение уникальных значений из нескольких столбцов с помощью сводной таблицы
Если вы знакомы со сводной таблицей, вы можете легко извлечь уникальные значения из нескольких столбцов, выполнив следующие шаги:
1. Сначала, пожалуйста, вставьте один новый пустой столбец слева от ваших данных, в этом примере я вставлю столбец A рядом с исходными данными.
2. Щелкните одну ячейку в ваших данных и нажмите клавиши "Alt+D", затем немедленно нажмите клавишу "P", чтобы открыть "Мастер сводных таблиц и сводных диаграмм", выберите "Несколько диапазонов консолидации" на шаге 1 мастера, см. скриншот:
3. Затем нажмите кнопку Далее, отметьте опцию "Создать одно поле страницы для меня" на шаге 2 мастера, см. скриншот:
4. Продолжайте нажимать кнопку "Далее", щелкните, чтобы выбрать диапазон данных, включая новый левый столбец ячеек, затем нажмите кнопку "Добавить", чтобы добавить диапазон данных в список "Все диапазоны", см. скриншот:
5. После выбора диапазона данных продолжайте нажимать "Далее", на шаге 3 мастера выберите, где вы хотите разместить отчет сводной таблицы по своему усмотрению.
6. Наконец, нажмите "Завершить", чтобы завершить мастер, и сводная таблица будет создана в текущем листе. Затем снимите все поля из раздела Выбор полей для добавления в отчет, см. скриншот:
7. Затем отметьте поле Значение или перетащите Значение к метке "Строки", теперь вы получите уникальные значения из нескольких столбцов следующим образом:
Извлечение уникальных значений из нескольких столбцов с помощью кода VBA
С помощью следующего кода VBA вы также можете извлечь уникальные значения из нескольких столбцов.
1. Удерживайте клавиши "ALT + F11", и откроется окно "Microsoft Visual Basic for Applications".
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, чтобы закрыть это диалоговое окно, и все уникальные значения будут извлечены одновременно.
Больше связанных статей:
- Подсчитайте количество уникальных и различных значений из списка
- Предположим, у вас есть длинный список значений с некоторыми повторяющимися элементами, и вы хотите подсчитать количество уникальных значений (значения, которые появляются в списке только один раз) или различных значений (все разные значения в списке, то есть уникальные значения + первые повторяющиеся значения) в столбце, как показано на левом скриншоте. В этой статье я расскажу, как справиться с этой задачей в Excel.
- Извлечение уникальных значений на основе критериев в Excel
- Предположим, у вас есть следующий диапазон данных, из которого вы хотите перечислить только уникальные имена столбца B на основе конкретного критерия столбца A, чтобы получить результат, как показано на скриншоте ниже. Как можно справиться с этой задачей в Excel быстро и легко?
- Разрешить только уникальные значения в Excel
- Если вы хотите сохранить только уникальные значения при вводе в столбец рабочего листа и предотвратить дубликаты, эта статья представит несколько быстрых приемов для вас, чтобы справиться с этой задачей.
- Суммирование уникальных значений на основе критериев в Excel
- Например, у меня есть диапазон данных, который содержит столбцы Имя и Заказ, теперь, чтобы суммировать только уникальные значения в столбце Заказ на основе столбца Имя, как показано на следующем скриншоте. Как быстро и легко решить эту задачу в Excel?
Лучшие инструменты для повышения продуктивности работы с Office
Ускорьте работу в Excel с Kutools для Excel и ощутите новую степень эффективности. Kutools для Excel предлагает более300 расширенных функций для повышения продуктивности и экономии времени. Нажмите здесь, чтобы выбрать нужную вам функцию...
Office Tab добавляет вкладки в Office и делает вашу работу намного проще
- Включите редактирование и чтение с вкладками в Word, Excel, PowerPoint, Publisher, Access, Visio и Project.
- Открывайте и создавайте несколько документов во вкладках одного окна, а не в отдельных окнах.
- Увеличьте свою продуктивность на50% и сократите сотни лишних кликов мышью каждый день!