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

Обновлено: 05.07.2024

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

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

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

УНИКАЛЬНАЯ функция Excel

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

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

Синтаксис функции Excel UNIQUE следующий:

Массив (обязательно) — диапазон или массив, из которого возвращаются уникальные значения.

By_col (необязательно) — логическое значение, указывающее, как сравнивать данные:

TRUE — данные сравниваются по столбцам.
FALSE или опущено (по умолчанию) — данные сравниваются по строкам.

Exactly_once (необязательно) — логическое значение, определяющее, какие значения считаются уникальными:

TRUE — возвращает значения, встречающиеся только один раз, что является уникальным в базе данных.
FALSE или опущено (по умолчанию) — возвращает все отдельные (различные) значения в диапазоне или массиве.

Примечание. В настоящее время функция UNIQUE доступна только в Excel для Microsoft 365 и Excel 2021. Excel 2019, 2016 и более ранние версии не поддерживают формулы динамического массива, поэтому функция UNIQUE недоступна в этих версиях.

Основная УНИКАЛЬНАЯ формула в Excel

Ниже представлена формула уникальных значений Excel в простейшей форме.

Цель – извлечь список уникальных имен из диапазона B2:B10. Для этого в D2 вводим следующую формулу:

Обратите внимание, что 2-й и 3-й аргументы опущены, потому что значения по умолчанию прекрасно работают в нашем случае — мы сравниваем строки друг с другом и хотим вернуть все разные имена в диапазоне.

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

Если ваши данные находятся в столбцах от B2 до I2, установите для второго аргумента значение TRUE, чтобы сравнить столбцы друг с другом:

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

УНИКАЛЬНАЯ функция Excel — советы и примечания

UNIQUE — это новая функция, и, как и другие функции динамического массива, она имеет несколько особенностей, о которых вам следует знать:

Как найти уникальные значения в Excel — примеры формул

В приведенных ниже примерах показано практическое использование функции UNIQUE в Excel. Основная идея состоит в том, чтобы извлечь уникальные значения или удалить дубликаты, в зависимости от вашей точки зрения, самым простым способом.

Извлечение уникальных значений, которые встречаются только один раз

Чтобы получить список значений, которые встречаются в указанном диапазоне ровно один раз, установите для третьего аргумента UNIQUE значение TRUE.

Например, чтобы выбрать имена из списка победителей один раз, используйте следующую формулу:

Где B2:B10 — исходный диапазон, а второй аргумент (by_col) — FALSE или опущен, поскольку наши данные организованы в строки.

Найти различные значения, которые встречаются более одного раза

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

Например, чтобы извлечь разные имена, встречающиеся в B2:B10 более одного раза, можно использовать следующую формулу:

=УНИКАЛЬНЫЙ(ФИЛЬТР(B2:B10, СЧЁТЕСЛИ(B2:B10, B2:B10)>1))

Как работает эта формула:

В основе формулы лежит функция ФИЛЬТР, которая отфильтровывает повторяющиеся записи на основе количества вхождений, возвращаемого функцией СЧЁТЕСЛИ. В нашем случае результатом COUNTIF является следующий массив счетчиков:

Операция сравнения (>1) изменяет указанный выше массив на значения TRUE и FALSE, где TRUE представляет элементы, встречающиеся более одного раза:

Этот массив передается функции FILTER в качестве аргумента include, указывающего функции, какие значения включить в результирующий массив:

Как вы можете заметить, сохраняются только значения, соответствующие TRUE.

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

Совет. Аналогичным образом можно отфильтровать уникальные значения, встречающиеся более двух раз (>2), более трех раз (>3) и т. д. Для этого просто измените число в логическом сравнении.

Поиск уникальных значений в нескольких столбцах (уникальных строках)

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

Например, чтобы вернуть уникальные Имя (столбец A) и Фамилию (столбец B) победителей, мы вводим эту формулу в E2:

Нажатие клавиши Enter приводит к следующим результатам:

Чтобы получить уникальные строки, то есть записи с уникальной комбинацией значений в столбцах A, B и C, используйте следующую формулу:

Удивительно просто, не так ли? :)

Получить список уникальных значений, отсортированных в алфавитном порядке

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

С введением функций динамического массива эта проблема исчезла! Что вам нужно сделать, так это просто деформировать функцию SORT вокруг обычной формулы UNIQUE, например:

Например, чтобы извлечь уникальные значения из столбцов от A до C и упорядочить результаты от A до Z, используйте следующую формулу:

По сравнению с приведенным выше примером вывод намного проще для восприятия и работы. Например, мы можем ясно видеть, что Эндрю и Дэвид были победителями в двух разных видах спорта.

Совет. В этом примере мы отсортировали значения в 1-м столбце от А до Я. Это значения по умолчанию для функции СОРТИРОВКА, поэтому необязательные аргументы sort_index и sort_order опущены. Если вы хотите отсортировать результаты по какому-либо другому столбцу или в другом порядке (от Я до А или от большего к меньшему), задайте 2-й и 3-й аргументы, как описано в руководстве по функции СОРТИРОВКА.

Найти уникальные значения в нескольких столбцах и объединить их в одну ячейку

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

Для этого вместо ссылки на весь диапазон используйте амперсанд (&), чтобы объединить столбцы и поместить нужный разделитель между ними.

Например, мы объединяем имена в формате A2:A10 и фамилии в формате B2:B10, разделяя значения пробелом (""):

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

Получить список уникальных значений на основе критериев

Чтобы извлечь уникальные значения с условием, используйте функции Excel UNIQUE и FILTER вместе:

Функция ФИЛЬТР ограничивает данные только значениями, которые соответствуют условию.
Функция UNIQUE удаляет дубликаты из отфильтрованного списка.

Вот общая версия формулы отфильтрованных уникальных значений:

Для этого примера давайте получим список победителей в определенном виде спорта. Для начала вводим интересующий нас вид спорта в какую-нибудь ячейку, скажем F1. Затем используйте приведенную ниже формулу, чтобы получить уникальные имена:

Где A2:B10 — диапазон для поиска уникальных значений, а C2:C10 — диапазон для проверки критериев.

Фильтрация уникальных значений по нескольким критериям

Чтобы отфильтровать уникальные значения с двумя или более условиями, используйте выражения, подобные показанным ниже, для построения требуемых критериев для функции ФИЛЬТР:

Результатом формулы является список уникальных записей, для которых все заданные условия ИСТИННЫ. В Excel это называется логикой И.

Чтобы увидеть формулу в действии, давайте получим список уникальных победителей в спорте в G1 (критерий 1) и в возрасте младше в G2 (критерий 2).

С исходным диапазоном в A2:B10, спортом в C2:C10 (criteria_range 1) и возрастом в D2:D10 (criteria_range 2), формула принимает следующий вид:

=УНИКАЛЬНЫЙ(ФИЛЬТР(A2:B10, (C2:C10=G1) * (D2:D10

И возвращает именно те результаты, которые мы ищем:

Как работает эта формула:

Вот подробное объяснение логики формулы:

В аргументе include функции FILTER вы указываете две или более пар диапазон/критерий. Результатом каждого логического выражения является массив значений ИСТИНА и ЛОЖЬ. Умножение массивов приводит логические значения к числам и создает массив из 1 и 0. Поскольку умножение на ноль всегда дает ноль, только записи, удовлетворяющие всем условиям, имеют 1 в конечном массиве. Функция FILTER отфильтровывает элементы, соответствующие 0, и передает результаты в UNIQUE.

Фильтрация уникальных значений по нескольким критериям ИЛИ

Чтобы получить список уникальных значений на основе нескольких критериев ИЛИ, т. е. когда этот ИЛИ тот критерий равен ИСТИНА, сложите логические выражения вместо их умножения:

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

=УНИКАЛЬНЫЙ(ФИЛЬТР(A2:B10, (C2:C10="Футбол") + (C2:C10="Хоккей")))

При необходимости вы, конечно, можете ввести критерии в отдельные ячейки и ссылаться на эти ячейки, как показано ниже:

=УНИКАЛЬНЫЙ(ФИЛЬТР(A2:B10, (C2:C10=G1) + (C2:C10=G2)))

Как работает эта формула:

Как и при тестировании нескольких критериев И, вы помещаете несколько логических выражений в аргумент include функции ФИЛЬТР, каждое из которых возвращает массив значений ИСТИНА и ЛОЖЬ. Когда эти массивы складываются, элементы, для которых один или несколько критериев имеют значение ИСТИНА, будут иметь 1, а элементы, для которых все критерии имеют значение ЛОЖЬ, будут иметь 0. В результате любая запись, удовлетворяющая одному из условий, попадает в список. массив, который передается в UNIQUE.

Получить уникальные значения в Excel, игнорируя пробелы

Если вы работаете с набором данных, который содержит пробелы, список уникальных значений, полученный с помощью обычной формулы, скорее всего, будет иметь пустую ячейку и/или нулевое значение. Это происходит потому, что функция Excel UNIQUE предназначена для возврата всех уникальных значений в диапазоне, включая пробелы. Таким образом, если в вашем исходном диапазоне есть как нули, так и пустые ячейки, уникальный список будет содержать 2 нуля, один из которых представляет собой пустую ячейку, а другой — само нулевое значение. Кроме того, если исходные данные содержат пустые строки, возвращаемые какой-либо формулой, список уникальных идентификаторов также будет включать пустую строку (""), которая визуально выглядит как пустая ячейка:

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

Отфильтруйте пустые ячейки и пустые строки с помощью функции ФИЛЬТР.
Используйте функцию UNIQUE, чтобы ограничить результаты только уникальными значениями.

В общем виде формула выглядит следующим образом:

В этом примере формула в D2 выглядит следующим образом:

В результате Excel возвращает список уникальных имен без пустых ячеек:

Поиск уникальных значений в определенных столбцах

Иногда может потребоваться извлечь уникальные значения из двух или более столбцов, которые не являются смежными друг с другом. Иногда вам также может понадобиться изменить порядок столбцов в результирующем списке. Обе задачи можно выполнить с помощью функции ВЫБОР.

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

=УНИКАЛЬНЫЙ(ВЫБРАТЬ(, C2:C10, A2:A10))

И получите следующий результат:

Как работает эта формула:

Функция ВЫБОР возвращает двумерный массив значений из указанных столбцов. В нашем случае он также меняет порядок столбцов.

Из приведенного выше массива функция UNIQUE возвращает список уникальных записей.

Поиск уникальных значений и обработка ошибок

Чтобы этого не произошло, просто заключите формулу в функцию ЕСЛИОШИБКА.

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

=ЕСЛИОШИБКА(УНИКАЛЬНЫЙ(ФИЛЬТР(A2:B10, (C2:C10=G1) * (D2:D10)

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

=ЕСЛИОШИБКА(УНИКАЛЬНЫЙ(ФИЛЬТР(A2:B10, (C2:C10=G1) * (D2:D10)

Функция Excel UNIQUE не работает

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

Происходит, если вы используете УНИКАЛЬНУЮ формулу в версии Excel, где эта функция не поддерживается.

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

Происходит, если одна или несколько ячеек в диапазоне заполнения не полностью пусты.

Вот как найти уникальные значения в Excel. Я благодарю вас за чтение и надеюсь увидеть вас в нашем блоге на следующей неделе!

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

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

Совет. Чтобы быстро получить уникальные значения в последней версии Excel 365, поддерживающей динамические массивы, используйте функцию UNIQUE, как описано в приведенном выше руководстве.

Как получить уникальные значения в Excel

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

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

Формула уникальных значений массива (заполняется нажатием Ctrl + Shift + Enter ):

=ЕСЛИОШИБКА(ИНДЕКС($A$2:$A$10, СОВПАДЕНИЕ(0, СЧЁТЕСЛИ($B$1:B1,$A$2:$A$10) + (СЧЁТЕСЛИ($A$2:$A$10, $A $2:$A$10)<>1), 0)), "")

Формула обычных уникальных значений (заполняется нажатием Enter):

=ЕСЛИОШИБКА(ИНДЕКС($A$2:$A$10, СОВПАДЕНИЕ(0,ИНДЕКС(СЧЁТЕСЛИ($B$1:B1, $A$2:$A$10)+(СЧЁТЕСЛИ($A$2:$A$10, $A$2:$A$10)<>1),0,0), 0)), "")

В приведенных выше формулах используются следующие ссылки:

A2:A10 — исходный список.
B1 — верхняя ячейка уникального списка минус 1. В этом примере мы начинаем уникальный список с ячейки B2, поэтому в формулу мы подставляем ячейку B1 (B2-1=B1). Если ваш уникальный список начинается, скажем, с ячейки C3, измените $B$1:B1 на $C$2:C2.

Примечание.Поскольку формула ссылается на ячейку над первой ячейкой уникального списка, которая обычно является заголовком столбца (B1 в этом примере), убедитесь, что заголовок имеет уникальное имя, которое не встречается больше нигде в столбце.

В этом примере мы извлекаем уникальные имена из столбца A (точнее, из диапазона A2:A20), а следующий снимок экрана демонстрирует формулу массива в действии:

Подробное объяснение логики формулы представлено в отдельном разделе, и вот как использовать формулу для извлечения уникальных значений на листах Excel:

Измените одну из формул в соответствии с вашим набором данных.
Введите формулу в первую ячейку уникального списка (в данном примере – B2).
Если вы используете формулу массива, нажмите Ctrl + Shift + Enter . Если вы выбрали обычную формулу, нажмите клавишу Enter, как обычно. насколько это необходимо, перетащив маркер заполнения. Поскольку обе формулы уникальных значений инкапсулированы в функцию ЕСЛИОШИБКА, вы можете скопировать формулу до конца таблицы, и она не будет загромождать ваши данные какими-либо ошибками, независимо от того, как мало уникальных значений было извлечено.

Как получить различные значения в Excel (уникальные + 1 повторяющееся вхождение)

Как вы, возможно, уже догадались из заголовка этого раздела, различные значения в Excel — это все разные значения в списке, т. е. уникальные значения и первые экземпляры повторяющихся значений. Например:

Чтобы получить отдельный список в Excel, используйте следующие формулы.

Отдельная формула массива (требуется нажатие Ctrl + Shift + Enter ):

=ЕСЛИОШИБКА(ИНДЕКС($A$2:$A$10, СОВПАДЕНИЕ(0, СЧЁТЕСЛИ($B$1:B1, $A$2:$A$10), 0)), "")

Обычная отдельная формула:

=ЕСЛИОШИБКА(ИНДЕКС($A$2:$A$10, СОВПАДЕНИЕ(0, ИНДЕКС(СЧЁТЕСЛИ($B$1:B1, $A$2:$A$10), 0, 0), 0)), "" )

A2:A10 — это исходный список.
B1 – это ячейка над первой ячейкой в списке отдельных элементов. В этом примере отдельный список начинается с ячейки B2 (это первая ячейка, в которую вы вводите формулу), поэтому вы ссылаетесь на ячейку B1.

Извлекать отдельные значения в столбце, игнорируя пустые ячейки

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

Формула массива для извлечения различных значений, исключая пробелы:

=ЕСЛИОШИБКА(ИНДЕКС($A$2:$A$10, СОВПАДЕНИЕ(0, СЧЁТЕСЛИ($B$1:B1, $A$2:$A$10&"") + ЕСЛИ($A$2:$A$10=) "",1,0), 0)), "")

Получить список различных текстовых значений без учета чисел и пробелов

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

=ЕСЛИОШИБКА(ИНДЕКС($A$2:$A$10, СОВПАДЕНИЕ(0, СЧЁТЕСЛИ($B$1:B1, $A$2:$A$10&"") + ЕСЛИ(ISTEXT($A$2:$A) $10)=ЛОЖЬ,1,0), 0)), "")

Напоминаем, что в приведенных выше формулах A2:A10 — это исходный список, а B1 — это ячейка прямо над первой ячейкой отдельного списка.

На следующем снимке экрана показаны результаты обеих формул:

Как извлечь отдельные значения с учетом регистра в Excel

При работе с данными, чувствительными к регистру, такими как пароли, имена пользователей или имена файлов, может потребоваться получить список отдельных значений, чувствительных к регистру. Для этого используйте следующую формулу массива, где A2:A10 — исходный список, а B1 — ячейка над первой ячейкой отдельного списка:

Формула массива для получения отдельных значений с учетом регистра (требуется нажатие Ctrl + Shift + Enter )

=ЕСЛИОШИБКА(ИНДЕКС($A$2:$A$10, СОВПАДЕНИЕ(0, ЧАСТОТА(ЕСЛИ(ТОЧНО($A$2:$A$10,ТРАНСП($B$1:B1))), СОВПАДЕНИЕ(СТРОКА($A $2:$A$10), СТРОКА($A$2:$A$10)), ""), ПОИСКПОЗ(СТРОКА($A$2:$A$10), СТРОКА($A$2:$A$10))), 0 )), "")

Как работает уникальная / отличительная формула

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

Само собой разумеется, что формулы для извлечения уникальных и различных значений в Excel не являются ни тривиальными, ни простыми.Но присмотревшись, можно заметить, что все формулы основаны на одном и том же подходе — использовании функций ИНДЕКС/ПОИСКПОЗ в сочетании с функциями СЧЁТЕСЛИ или СЧЁТЕСЛИ + ЕСЛИ.

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

=ЕСЛИОШИБКА(ИНДЕКС($A$2:$A$10, СОВПАДЕНИЕ(0, СЧЁТЕСЛИ($B$1:B1, $A$2:$A$10), 0)), "")

А теперь давайте разберем основную часть нашей формулы:

В этом примере СЧЁТЕСЛИ($B$1:B1, $A$2:$A$10) возвращает массив из 1 и 0 в зависимости от того, было ли какое-либо из значений исходного списка ($A$2:$A$10) появляется где-то в отдельном списке ($B$1:B1). Если значение найдено, формула возвращает 1, иначе - 0.

В частности, в ячейке B2 СЧЁТЕСЛИ($B$1:B1, $A$2:$A$10) принимает вид:

ПОИСКПОЗ(0,СЧЁТЕСЛИ($B$1:B1, $A$2:$A$10), 0)

В этом примере ИНДЕКС($A$2:$A$10, 1)

и возвращает "Ронни".

Когда формула копируется вниз по столбцу, список отдельных элементов ($B$1:B1) расширяется, поскольку вторая ссылка на ячейку (B1) является относительной ссылкой, которая изменяется в зависимости от относительного положения ячейки, в которую перемещается формула.

Итак, при копировании в ячейку B3 СЧЁТЕСЛИ($B$1:B1, $A$2:$A$10) изменяется на СЧЁТЕСЛИ($B$1:B2, $A$2:$A$10) и становится:< /p>

потому что один "Ронни" находится в диапазоне $B$1:B2.

Затем ПОИСКПОЗ(0,0;0;0;0;0;0;0;0>,0) возвращает 2, поскольку 2 – это относительное положение первого 0 в массиве.

Совет. Чтобы лучше понять логику формулы, вы можете выбрать разные части формулы в строке формул и нажать F9, чтобы увидеть, что оценивает выбранная часть:

Если у вас все еще есть трудности с вычислением формулы, вы можете просмотреть следующий учебник для подробного объяснения того, как работает связь ИНДЕКС/ПОИСКПОЗ: ИНДЕКС и ПОИСКПОЗ как лучшая альтернатива ВПР Excel.

Как уже упоминалось, другие формулы, обсуждаемые в этом руководстве, основаны на той же логике с небольшими изменениями:

Формула уникальных значений — содержит еще одну функцию СЧЁТЕСЛИ, которая исключает из уникального списка все элементы, встречающиеся в исходном списке более одного раза: СЧЁТЕСЛИ($A$2:$A$10, $A$2:$A$10)<> 1 .

Формула уникальных значений, игнорирующая пробелы. Здесь вы добавляете функцию ЕСЛИ, которая предотвращает добавление пустых ячеек в список уникальных значений: IF($A$2:$A$13="",1,0) .

Формула отдельных текстовых значений без учета чисел. Функция ISTEXT используется для проверки того, является ли значение текстом, а функция IF используется для исключения всех других типов значений, включая пустые ячейки: IF(ISTEXT($A$2:$A$13) =ЛОЖЬ,1,0).

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

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

Выберите столбец данных, из которого вы хотите извлечь отдельные значения.
Перейдите на вкладку Данные в группу Сортировка и фильтрация и нажмите кнопку "Дополнительно":
В диалоговом окне Расширенный фильтр выберите следующие параметры:
- Отметьте переключатель Копировать в другое место.
- Убедитесь, что в поле Список диапазонов правильно отображается исходный диапазон.
- В поле Копировать в введите самую верхнюю ячейку целевого диапазона. Имейте в виду, что вы можете копировать отфильтрованные данные только на активный лист.
- Выберите Только уникальные записи

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

Извлечение уникальных и различных строк с помощью Duplicate Remover

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

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

А теперь давайте посмотрим на инструмент для удаления дубликатов в действии.

Предположим, у вас есть сводная таблица, созданная путем объединения данных из нескольких других таблиц. Очевидно, что эта сводная таблица содержит много повторяющихся строк, и ваша задача состоит в том, чтобы извлечь уникальные строки, которые встречаются в таблице только один раз, или отдельные строки, включая уникальные и 1-е повторяющиеся вхождения. В любом случае, с надстройкой Duplicate Remover эта работа выполняется за 5 быстрых шагов.

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

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

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

Поскольку мы извлекаем уникальные строки, выберите Копировать в другое место, а затем укажите, куда именно вы хотите их скопировать — на активный лист (выберите параметр Пользовательское расположение, и укажите верхнюю ячейку целевого диапазона), новый лист или новую книгу.

Понравился этот быстрый и простой способ получить список уникальных значений или строк в Excel? Если это так, я рекомендую вам загрузить ознакомительную версию ниже и попробовать ее. Duplicate Remover, а также все другие инструменты для экономии времени, которые у нас есть, включены в Ultimate Suite for Excel.

Если у меня есть столбец со значениями, и я хочу узнать, какие уникальные значения в нем содержатся (не сколько, а фактические уникальные значения), как мне это сделать?

В SQL Server я бы сделал что-то вроде

7 ответов 7

Проще, чем вы думаете:

Откройте меню ленты данных.
Нажмите кнопку «Дополнительно» в разделе Сортировка и фильтрация.
Заполните диалоговое окно, скопировав результаты в другое место и установив флажок "Только уникальные записи".

При копировании данных на другой лист необходимо инициировать диалоговое окно «Дополнительно» с листа, на который вы хотите скопировать, в противном случае вы получите сообщение об ошибке «Можно копировать только отфильтрованные данные на активный лист».

Excel 2007 и 2010:
используйте пункт меню "Удалить дубликаты" под заголовком "Данные".

Excel 2003:
Простой способ:

Убедитесь, что у ваших данных есть заголовок.
Данные --> Фильтр --> Расширенный фильтр
Проверить только уникальные записи
Выберите Копировать в другое место.
Нажмите "ОК".

Напишите макрос со следующим кодом:

Это дает вам отдельный список. Вы можете сначала скопировать список на другой лист.

Спасибо! Это доказательство того, что Stack Exchange намного лучше любого другого источника технической информации. Все остальные результаты Google бесполезны и не имеют рейтинга. Также интересно, как выживают биржи экспертов

Или (простой грубый способ):

и скопируйте вниз. Он просто копирует первое вхождение каждого значения (в той строке, в которой оно встречается).

Как и во всех формулах Excel, вам может понадобиться использовать точку с запятой ( ; ) вместо запятой ( , ) в зависимости от региональных настроек вашей системы.

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

Недостаток удаления дубликатов заключается в том, что вы теряете данные. Несмотря на силу Ctrl + z, вы можете не захотеть делать это по разным причинам — например, у вас могут быть выбраны фильтры, из-за которых довольно сложно удалить дубликаты, чтобы получить ваш отдельный список значений.

Другой метод, не упомянутый выше, — использование сводной таблицы.

Допустим, это ваша таблица, и вы хотите получить отдельный список платформ.

Вставьте сводную таблицу с таблицей в качестве источника данных.

Теперь выберите столбец "Платформы" в качестве ярлыков строк.

Вуаля! Список различных значений.

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

Чтобы извлечь из списка или столбца только уникальные значения, можно использовать формулу массива на основе ИНДЕКС, ПОИСКПОЗ и СЧЁТЕСЛИ. В показанном примере формула в D5, скопированная вниз, выглядит следующим образом:

где "список" — это именованный диапазон B5:B11.

Примечание. Это формула массива, которую необходимо вводить с помощью клавиш Ctrl + Shift + Enter.

Основой этой формулы является базовый поиск с помощью ИНДЕКС:

Другими словами, задайте INDEX список и номер строки, и INDEX получит значение для добавления в уникальный список.

Здесь СЧЁТЕСЛИ подсчитывает, сколько раз элементы, уже находящиеся в уникальном списке, появляются в основном списке, используя расширяющуюся ссылку для диапазона, $D$4:D4.

Расширяющаяся ссылка является абсолютной с одной стороны и относительной с другой. В этом случае по мере копирования формулы ссылка будет расширяться, чтобы включить больше строк в уникальный список.

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

Важно! Убедитесь, что заголовок уникального списка не отображается в основном списке.

Для критериев в СЧЁТЕСЛИ мы используем сам основной список. При задании нескольких критериев функция СЧЁТЕСЛИ возвращает несколько результатов в виде массива. В каждой новой строке у нас есть другой массив, подобный этому:

Примечание. СЧЁТЕСЛИ обрабатывает несколько критериев с отношением "ИЛИ" (т. е. СЧЁТЕСЛИ (диапазон, ) подсчитывает красный, синий или зелёный цвет.

Теперь у нас есть массивы, необходимые для поиска позиций (номеров строк). Для этого мы используем MATCH, настроенный на точное совпадение, чтобы найти нулевые значения. Если мы поместим массивы, созданные СЧЁТЕСЛИ выше, в ПОИСКПОЗ, вот что мы получим:

ПОИСКПОЗ находит элементы, ища число, равное нулю (т. е. ищет элементы, которые еще не отображаются в уникальном списке). Это работает, потому что ПОИСКПОЗ всегда возвращает первое совпадение при наличии дубликатов.

Наконец, позиции передаются в ИНДЕКС как номера строк, и ИНДЕКС возвращает имя в этой позиции.

Версия без массива с ПРОСМОТР

Вы можете создать формулу без массива для извлечения уникальных элементов с помощью гибкой функции ПРОСМОТР:

Формула аналогична приведенной выше формуле ПОИСКПОЗ ИНДЕКС, но ПРОСМОТР может обрабатывать операции с массивами изначально.

Извлечь элементы, которые появляются только один раз

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

Единственным дополнением является второе выражение COUNTIF:

Здесь СЧЁТЕСЛИ возвращает массив количества элементов, например:

которые сравниваются с 1, в результате чего получается массив значений ИСТИНА/ЛОЖЬ:

которые действуют как «фильтр», чтобы ограничить вывод элементами, которые встречаются только один раз в исходных данных.

УНИКАЛЬНАЯ функция в Excel 365

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

Читайте также: