Как извлечь уникальные значения из нескольких столбцов в Excel?
Если вы часто работаете с наборами данных, распределёнными по нескольким столбцам в Excel, наверняка сталкивались с дублированием определённых значений — как внутри одного столбца, так и между разными. Во многих задачах отчётности и анализа данных критически важно выявить и извлечь все уникальные значения, то есть те, что встречаются ровно один раз во всём выделенном диапазоне, независимо от их расположения. Выполнение этой операции вручную не только утомительно, но и чревато ошибками, особенно при работе с объёмными или сложными таблицами. К счастью, Excel предлагает несколько эффективных способов для точного и быстрого извлечения таких уникальных значений.
В этом руководстве представлены несколько решений, которые можно выбрать в зависимости от вашей версии Excel и предпочтений: универсальные формулы, подходящие для всех версий; формулы динамических массивов — для недавних версий; KUTOOLS AI Aide — для быстрого получения простых результатов; сводная таблица — для наглядной консолидации данных; а также код VBA — для автоматизированного извлечения в сложных сценариях.
- Извлечение уникальных значений из нескольких столбцов с помощью формул
- Извлечение уникальных значений из нескольких столбцов с помощью KUTOOLS AI Aide
- Извлечение уникальных значений из нескольких столбцов с помощью Сводная таблица
- Извлечение уникальных значений из нескольких столбцов с помощью кода 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"),)&«»
- A2:C9 — это диапазон данных, из которого вы хотите извлечь уникальные значения.
- E1:E1 относится к ячейкам непосредственно над первой ячейкой вывода и необходим для отслеживания уже выведенных записей.
- $2:$9 — это диапазон строк с вашими данными, а $A:$C — диапазон столбцов. При необходимости скорректируйте их в соответствии со структурой вашего рабочего листа.
2. После ввода формулы вместо простого нажатия клавиши Enter одновременно нажмите Ctrl + Shift + Enter, чтобы подтвердить её как формулу массива. При правильном выполнении вокруг формулы в строке формул появятся фигурные скобки {}. Затем перетащите маркер заполнения из ячейки E2 вниз по столбцу. Продолжайте перетаскивание, пока не появятся пустые ячейки — это будет означать, что дополнительные уникальные значения для извлечения отсутствуют. Такая процедура гарантирует отображение всех уникальных значений в целевом столбце.
- $A$2:$C$9Указывает весь диапазон ячеек, в котором необходимо искать уникальные значения.
- 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обеспечивает включение только новых (ещё не извлечённых) значений.
- Если оба условия выполняются, соответствующий результат определяется вычислением на основе строки и столбца ячейки для генерации уникального индексного номера.
- Если хотя бы одно условие ложно, формула возвращает очень большое число ()7⁸), чтобы исключить случайный выбор.
- MIN(...)Определяет наименьший индексный номер, эффективно находя позицию следующего доступного уникального значения в данных.
- TEXT(...,"R0C00")Преобразует индекс в корректную ссылку на ячейку в стиле R1C1.
- INDIRECT(...)Преобразует созданную выше ссылку на ячейку в соответствующее значение из вашего диапазона данных.
- &""Принудительно обрабатывает результат формулы как текст, предотвращая неожиданные изменения форматирования.
Извлечение уникальных значений из нескольких столбцов с помощью формулы для Excel 365, Excel 2021 и более новых версий
Если вы используете Excel 365, Excel 2021 или более новую версию, вам доступны функции динамических массивов, которые обеспечивают более простой и интуитивно понятный способ извлечения уникальных значений из нескольких столбцов. Функции UNIQUE и TOCOL позволяют легко и быстро объединить данные из разных столбцов и удалить дубликаты за один шаг — особенно полезно при работе с постоянно обновляемыми или крупными наборами данных.
Чтобы использовать этот метод, просто выберите пустую ячейку (например,)E2или любое другое место, где должны появиться результаты), введите эту формулу и нажмите Enter:
=UNIQUE(TOCOL(A2:C9,1))После нажатия клавиши Enter все уникальные значения из диапазона A2:C9 автоматически заполнят ячейки под формулой. Эта функция особенно эффективна: результат динамически обновляется при изменении Ваших исходных данных, избавляя от необходимости выполнять ручное обновление.
- TOCOL(A2:C9,1): Преобразует диапазон значений из нескольких столбцов в один, автоматически удаляя пустые ячейки.
- UNIQUE(…): Извлекает каждое значение всего один раз, создавая чистый список без дубликатов.
Извлечение уникальных значений из нескольких столбцов с помощью KUTOOLS AI Aide
Если вы предпочитаете более простой подход и хотите минимизировать ручные усилия, KUTOOLS AI Aide в Kutools для Excel легко извлечёт уникальные значения из нескольких столбцов. Этот метод особенно удобен, если вы не знакомы с формулами или стремитесь избежать ошибок при их использовании. KUTOOLS AI Aide понимает ваши инструкции и автоматически обрабатывает данные — идеальное решение как для новичков, так и для тех, кто ищет быстрый результат всего за несколько кликов.
После установки нажмите KUTOOLS AI>AI Ассистент, чтобы открыть область «KUTOOLS AI Aide»:
- Введите свой запрос в чат-окно, например:«Извлечь уникальные значения из диапазона A2:C9, игнорируя пустые ячейки, и поместить результаты, начиная с E2:»
- Нажмите «Отправить» или клавишу Enter. После анализа запроса ИИ просто нажмите «Выполнить», чтобы запустить операцию — результаты мгновенно появятся на вашем рабочем листе именно там, где вы указали.
Совет: Это решение особенно полезно, если ваш рабочий процесс по извлечению данных меняется или если вы хотите задействовать функции обработки естественного языка. Обязательно проверьте параметры «Извлечь список» на наличие пустых ячеек, если исходные данные не полностью согласованы: пустые записи могут быть либо включены, либо отфильтрованы — в зависимости от деталей вашего запроса к ИИ.
Извлечение уникальных значений из нескольких столбцов с помощью Сводная таблица
Сводная таблица — ещё один удобный способ извлечения уникальных значений, особенно если вы предпочитаете визуальные инструменты и хотите не только собрать уникальные элементы воедино, но и дополнительно проанализировать их — например, подсчитать их количество. Этот метод прост и не требует формул. Однако он предполагает несколько шагов по настройке и небольшую перестройку данных, особенно если задействованные столбцы имеют разные заголовки.
Ниже приведён рекомендуемый процесс извлечения уникальных значений с использованием Сводная таблица:
1.Вставьте новый пустой столбец сразу слева от ваших данных. Например, если ваши данные начинаются со столбца B, вставьте новый столбец A. Такая настройка обеспечит корректное объединение диапазонов.
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 — и макрос запустится автоматически. Все уникальные значения появятся, начиная с указанной вами ячейки.
- Если при использовании формул появляются ошибки, такие как #ЗНАЧ! или #ПЕРЕПОЛН!, проверьте свои диапазоны и убедитесь, что область вывода свободна.
- Всегда проверяйте, не содержит ли ваш диапазон данных скрытых строк или объединённых ячеек — они могут повлиять на корректность извлечения уникальных значений.
- Формулы массивов и динамических массивов автоматически обновляются при изменении данных, в то время как решения на основе расширенного фильтра и сводных таблиц могут потребовать ручного обновления или повторного запуска.
- Для регулярных задач подумайте об автоматизации извлечения с помощью VBA — это обеспечит согласованность и ускорит процесс.
- Обязательно создайте резервную копию своих данных перед запуском любых массовых операций извлечения или автоматизации, особенно в сложных книгах.
Другие связанные статьи:
- Подсчёт количества уникальных и различных значений в списке
- Представьте, что у вас есть длинный список значений с некоторыми дубликатами, и вы хотите подсчитать либо количество уникальных значений (встречающихся только один раз), либо общее число различных значений в столбце — как на скриншоте слева. В этой статье описаны эффективные способы подсчёта уникальных и различных записей в Excel.
- Извлечение уникальных значений на основе критериев в Excel
- Предположим, вы хотите извлечь уникальные имена из столбца B, отфильтровав их по определённому условию в столбце A, — как раз такие результаты показаны на скриншоте. В этом руководстве вы узнаете, как применять критерии при получении уникальных значений.
- Разрешение только уникальных значений в Excel
- Если вы хотите разрешить ввод только уникальных записей в столбец рабочего листа и предотвратить дублирование значений, в этой статье представлены практические методы настройки правил уникальности в Excel.
- Суммирование уникальных значений на основе критериев в Excel
- Например, вам может понадобиться просуммировать только уникальные значения в столбце «Заказ», основываясь на именах из соседнего столбца, как показано на скриншоте. В этой статье рассматриваются способы комбинирования уникальных и условных вычислений.
Лучшие инструменты повышения продуктивности в Office
Раскройте весь потенциал Excel с помощью Kutools для Excel и ощутите эффективность как никогда раньше.Kutools для Excel предлагает более 300 расширенных функций для повышения продуктивности и Экономия времени.Нажмите здесь, чтобы получить нужную Вам функцию…
Office Tab добавляет в Office вкладки и значительно упрощает Вашу работу
- Включите редактирование и чтение во вкладках в Word, Excel, PowerPoint, Publisher, Access, Visio и Project.
- Открывайте и создавайте несколько документов во вкладках одного окна — вместо того чтобы использовать отдельные окна.
- Повышает вашу продуктивность на 50 % и экономит сотни кликов мышью каждый день!
Все надстройки Kutools — один установщик
Kutools for Office — набор надстроек для Excel, Word, Outlook и PowerPoint, а также Office Tab Pro, идеально подходящий командам, работающим с разными приложениями Office.
- Единый комплект— надстройки для Excel, Word, Outlook и PowerPoint + Office Tab Pro
- Один установщик, одна лицензия— настройка занимает считанные минуты (готово для MSI)
- Лучше работать вместе— оптимизированная продуктивность во всех приложениях Office
- 30-дневная полнофункциональная пробная версия— без регистрации и кредитной карты
- Лучшее соотношение цены и качества— экономия по сравнению с покупкой отдельных надстроек