Перейти к содержимому

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

Author: Xiaoyang Last Modified: 2025-06-05
A screenshot of an Excel dataset containing multiple columns with some repeated values

Предположим, у вас есть несколько столбцов с множеством значений, где некоторые значения повторяются в пределах одного столбца или в разных столбцах. Теперь вам нужно определить значения, которые являются уникальными для всех столбцов. Существуют ли быстрые методы для извлечения этих уникальных значений из нескольких столбцов в 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 указывает столбцы, содержащие ячейки, которые вы хотите использовать. Пожалуйста, измените их на свои собственные.

A screenshot showing how to use an array formula to extract unique values in Excel

2. Затем нажмите клавиши Shift + Ctrl + Enter вместе, а затем перетащите маркер заполнения для извлечения уникальных значений до появления пустых ячеек. Смотрите скриншот:

A screenshot showing unique values extracted using the array formula in Excel

Объяснение этой формулы:
  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. MIN(...): Находит наименьшее число, возвращаемое функцией IF выше, соответствующее местоположению следующего уникального значения.
  4. TEXT(...,"R0C00"): Преобразует это минимальное число в адрес стиля R1C1. Форматный код R0C00 указывает преобразование числа в формат ссылки на ячейку Excel.
  5. INDIRECT(...): Использует функцию INDIRECT для преобразования адреса стиля R1C1, созданного на предыдущем шаге, обратно в обычную ссылку на ячейку стиля A1. Функция INDIRECT позволяет ссылаться на ячейки на основе содержимого текстовой строки.
  6. &"": Добавление &"" в конце формулы гарантирует, что окончательный вывод будет рассматриваться как текст, так что даже числа будут отображаться как текст.
 
Извлечение уникальных значений из нескольких столбцов с помощью формулы для Excel 365, Excel 2021 и более новых версий

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

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

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

A screenshot showing the UNIQUE function in Excel extracting unique values from multiple columns


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

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

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

После установки Kutools для Excel, пожалуйста, нажмите "Kutools AI" > "AI Ассистент", чтобы открыть панель "Kutools AI Ассистент":

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

A GIF demonstrating how Kutools AI Aide extracts unique values from multiple columns in Excel

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

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

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

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

A screenshot showing the addition of a blank column before using a Pivot Table in Excel

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

A screenshot of the PivotTable and PivotChart Wizard with 'Multiple consolidation ranges' selected

3. Затем нажмите кнопку Далее, отметьте опцию "Создать одно поле страницы для меня" на шаге 2 мастера, см. скриншот:

A screenshot showing 'Create a single page field for me' selected in the Pivot Table Wizard

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

A screenshot of the Pivot Table Wizard with data range selection

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

A screenshot showing where to place the Pivot Table report in Excel

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

A screenshot of a created Pivot Table in Excel for unique value extraction

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

A screenshot showing unique values extracted using a Pivot Table in Excel


Извлечение уникальных значений из нескольких столбцов с помощью кода 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 для запуска этого кода, и появится всплывающее окно, напоминающее вам выбрать диапазон данных, который вы хотите использовать. Смотрите скриншот:

A screenshot of the VBA prompt for selecting a data range in Excel

4. Затем нажмите OK, появится другое всплывающее окно, позволяющее выбрать место для размещения результата, см. скриншот:

A screenshot of the VBA prompt for selecting the output cell in Excel

5. Нажмите OK, чтобы закрыть это диалоговое окно, и все уникальные значения будут извлечены одновременно.

A screenshot showing unique values extracted using VBA in Excel


Больше связанных статей:

  • Подсчитайте количество уникальных и различных значений из списка
  • Предположим, у вас есть длинный список значений с некоторыми повторяющимися элементами, и вы хотите подсчитать количество уникальных значений (значения, которые появляются в списке только один раз) или различных значений (все разные значения в списке, то есть уникальные значения + первые повторяющиеся значения) в столбце, как показано на левом скриншоте. В этой статье я расскажу, как справиться с этой задачей в Excel.
  • Извлечение уникальных значений на основе критериев в Excel
  • Предположим, у вас есть следующий диапазон данных, из которого вы хотите перечислить только уникальные имена столбца B на основе конкретного критерия столбца A, чтобы получить результат, как показано на скриншоте ниже. Как можно справиться с этой задачей в Excel быстро и легко?
  • Разрешить только уникальные значения в Excel
  • Если вы хотите сохранить только уникальные значения при вводе в столбец рабочего листа и предотвратить дубликаты, эта статья представит несколько быстрых приемов для вас, чтобы справиться с этой задачей.
  • Суммирование уникальных значений на основе критериев в Excel
  • Например, у меня есть диапазон данных, который содержит столбцы Имя и Заказ, теперь, чтобы суммировать только уникальные значения в столбце Заказ на основе столбца Имя, как показано на следующем скриншоте. Как быстро и легко решить эту задачу в Excel?

Лучшие инструменты для повышения продуктивности работы с Office

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

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


Office Tab добавляет вкладки в Office и делает вашу работу намного проще

  • Включите редактирование и чтение с вкладками в Word, Excel, PowerPoint, Publisher, Access, Visio и Project.
  • Открывайте и создавайте несколько документов во вкладках одного окна, а не в отдельных окнах.
  • Увеличьте свою продуктивность на50% и сократите сотни лишних кликов мышью каждый день!