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

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

Это руководство представляет несколько решений, которые вы можете использовать в зависимости от версии Excel и ваших предпочтений — таких как формулы, подходящие для всех версий, динамические массивы формул для последних версий, использование Kutools AI Ассистент для получения простых результатов, сводные таблицы для визуального объединения данных и VBA код для автоматизированного извлечения в сложных сценариях.

• Извлечение уникальных значений из нескольких столбцов с помощью формул
  • Массивная формула (все версии Excel)
  • Формула (Excel 365, Excel 2021 и более новые версии)
• Извлечение уникальных значений из нескольких столбцов с помощью Kutools AI Ассистент
• Извлечение уникальных значений из нескольких столбцов с помощью сводной таблицы
• Извлечение уникальных значений из нескольких столбцов с помощью кода VBA

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

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

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

Для совместимости со всеми версиями Excel использование формулы массива позволяет извлекать уникальные значения из нескольких столбцов — даже если ваш Excel не поддерживает динамические массивы. Этот подход использует комбинацию функций INDIRECT, TEXT, MIN, IF, COUNTIF, ROW и COLUMN, что делает его гибким для различных структур данных.

Предположим, что ваши данные находятся в диапазоне A2:C9. Чтобы извлечь уникальные значения, начиная с ячейки E2, следуйте указанной процедуре:

1. Щелкните ячейку 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. После ввода формулы вместо простого нажатия Enter нажмите Ctrl + Shift + Enter вместе, чтобы подтвердить ее как формулу массива. При правильном выполнении вокруг вашей формулы в строке формул появятся фигурные скобки {}. Затем перетащите маркер заполнения из ячейки E2 вниз по столбцу. Продолжайте перетаскивать до тех пор, пока не появятся пустые ячейки, что будет указывать на то, что больше нет уникальных значений для извлечения. Этот процесс гарантирует, что все уникальные значения будут отображены в целевом столбце.

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

Если вы используете Excel 365, Excel 2021 или более новую версию, у вас есть доступ к динамическим функциям массивов, которые обеспечивают более простой и интуитивно понятный способ извлечения уникальных значений из нескольких столбцов. Функции UNIQUE и TOCOL упрощают и ускоряют объединение данных из столбцов и удаление дубликатов за один шаг — особенно полезно для тех, кто работает с постоянно обновляемыми или большими наборами данных.

Чтобы использовать этот метод, просто выберите пустую ячейку (например, E2, или любую другую, где вы хотите видеть результаты), введите эту формулу и нажмите Enter:

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

После нажатия Enter все уникальные значения из диапазона A2:C9 автоматически появятся в ячейках ниже формулы. Эта функция особенно эффективна — вывод обновляется динамически при изменении исходных данных, экономя вам ручные шаги обновления.

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

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

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

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

Совет: Это решение очень полезно, если ваш рабочий процесс извлечения данных меняется или если вы хотите использовать возможности обработки естественного языка. Не забудьте дважды проверить извлеченный список на наличие пустых ячеек, если ваши исходные данные не полностью согласованы, так как пустые записи могут быть включены или отфильтрованы в зависимости от деталей вашего запроса AI.

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

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

Вот предлагаемый процесс для извлечения уникальных значений с использованием сводной таблицы:

1. Вставьте новый пустой столбец сразу слева от ваших данных. Например, вставьте новый столбец A, если ваши данные начинаются в столбце B. Эта корректировка помогает обеспечить правильное объединение диапазона.

2. Выберите любую ячейку в вашем наборе данных, нажмите Alt + D, затем быстро P, чтобы запустить мастер "Сводная таблица и сводная диаграмма". В первом шаге мастера выберите "Множественные диапазоны консолидации". Это позволит вам объединить значения из множества столбцов в одно обобщенное поле.

3. Нажмите Далее, затем выберите "Создать одну страницу поля для меня." Этот шаг организует все ваши данные как одну группу для более легкого извлечения уникальных значений.

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

5. В финальном шаге мастера выберите, куда вы хотите поместить сводную таблицу (новый лист или существующий лист), затем нажмите Готово, чтобы создать отчет сводной таблицы.

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

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

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

В случаях, когда вам нужно автоматизировать извлечение или работать с большими и нерегулярными наборами данных, использование кода VBA (Visual Basic for Applications) может предоставить быстрое и повторно используемое решение. Это идеально подходит для пользователей с базовым знакомством с редактором VBA Excel или для повторяющихся задач, где вы хотите минимизировать ручные операции. VBA также может обрабатывать большие объемы данных более эффективно, чем формулы массива.

1. Откройте редактор VBA, нажав 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 другой запрос спросит, куда вывести уникальные значения. Укажите верхнюю ячейку, где вы хотите видеть результаты (например, E2).

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

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