Как фильтр уникальных записей в Excel
В этой статье мы узнаем, как фильтровать уникальные записи в Excel.
Ранее в Excel 2016 и более ранних версиях мы использовали сложные формулы для связывания: / counting-how-to-count-unique-values-in-excel-with-conditions-2 [извлекать уникальные значения из диапазона]. В онлайн-версии Excel 365 MS предоставляет новую функцию динамического массива UNIQUE, которая просто возвращает уникальные объекты из заданного списка.
Синтаксис УНИКАЛЬНОЙ функции
|
=UNIQUE(array,[by_col],[exactly_once]) |
Массив: массив, из которого вы хотите извлечь уникальные значения:
[by_col]: установите TRUE (1), если массив горизонтальный. По умолчанию для вертикальных данных это ЛОЖЬ.
[only_once]: установите TRUE (1), если вы хотите извлекать значения, которые встречаются только один раз в массиве. По умолчанию FALSE (0) извлекает все уникальные значения.
Приведем несколько примеров, чтобы понять, как работает эта УНИКАЛЬНАЯ функция.
Пример УНИКАЛЬНОЙ функции Пример 1: извлечение уникальных чисел из списка с помощью УНИКАЛЬНОЙ функции Excel 365.
Вот пример данных. В диапазоне A2: A11 у меня есть несколько чисел, которые содержат повторяющиеся значения. Я хочу получить уникальные значения из этого диапазона. Я просто буду использовать УНИКАЛЬНУЮ функцию Excel 365.
|
=UNIQUE(A2:A11) |
Эта формула просто возвращает все уникальные значения из диапазона.
Как только вы нажимаете кнопку ввода, результирующий массив уникальных значений распределяется по ячейкам. Это динамический массив, доступный только для Excel 365 в Интернете.
Пример 2: Извлечь значения, которые встречаются в диапазоне только один раз.
В приведенном выше примере мы получили уникальные значения из диапазона. Если значение встречается один, два или более раз, мы получаем только одну его позицию. Но если мы хотим извлечь значения, которые встречаются в диапазоне только один раз (уникальные значения в диапазоне), то формула будет иметь вид:
|
=UNIQUE(A2:A11,1) |
Здесь мы устанавливаем для переменной even_once значение TRUE. Это возвращает значение, уникальное в самом диапазоне / массиве.
Пример 3: поиск уникальных значений в горизонтальных данных
В двух приведенных выше примерах массив был вертикальным и имел числовые значения. Что, если у нас есть нечисловые значения и горизонтальные данные? Что ж, не волнуйтесь. Функция excel UNIQUE работает с любыми типами значений, будь то числовые, текстовые, логические и т. Д. И мы также можем использовать ее для горизонтальной настройки данных.
Здесь у меня есть имена в ячейках соседних столбцов. Я хочу занести все имена без повторов в ячейки ниже.
Формула будет такой:
|
=UNIQUE(C2:K2,,1) |
Он возвращает имена после удаления всех дубликатов из массива в столбцах.
Для Excel 2010 — 2016
Чтобы извлечь список уникальных значений из списка, мы будем использовать INDEX, MATCH и COUNTIF. Я также буду использовать ЕСЛИОШИБКА, просто чтобы получить чистые результаты, это необязательно.
И да, это будет формула массива … Итак, давайте сделаем это … Общая формула извлекает уникальные значения в Excel
|
\{=INDEX(ref_list,MATCH(0,COUNTIF*(expanding_ouput_range,ref_list),0))} |
Ref_list: список, из которого вы хотите извлечь уникальные значения. Expanding_ouput_range: Теперь это очень важно. Это диапазон, в котором вы хотите видеть извлеченный список. Этот диапазон должен иметь отдельный заголовок, который не является частью списка, и ваша формула будет под заголовком (если заголовок находится в E1, то формула будет в E2). Расширение означает, что при перетаскивании формулы она должна расширяться за пределы диапазона вывода. Для этого вам нужно указать заголовок как $ E $ 1: E1 (мой заголовок находится в E1). Когда я потащу его вниз, он расширится. В E2 это будет $ E $ 1: E1. В E3 это будет $ E $ 1: E2. В E2 это будет $ E $ 1: E3 и т. Д. * Теперь рассмотрим пример. Это прояснит.
Извлечение уникальных значений Пример Excel Итак, вот у меня список клиентов в столбце A в диапазоне A2: A16. Теперь в столбце E я хочу получать уникальные значения только от клиентов. Теперь этот диапазон A2: A16 также может увеличиваться, поэтому я хочу, чтобы моя формула извлекала любое новое имя клиента из списка при каждом увеличении списка.
Хорошо, теперь, чтобы получить уникальные значения из столбца A, запишите эту формулу в ячейку E2 и нажмите CTRL + SHIFT + ENTER, чтобы сделать ее формулой массива. *
|
\{=INDEX(A$2:A16,MATCH(0,COUNTIF($E$1:E1,$A$2:A16),0))} |
A $ 2: A16: Я ожидаю, что этот список будет расширяться, и в нем могут появиться новые уникальные значения, которые я хотел бы извлечь. Вот почему я оставил его открытым снизу, не являясь абсолютной ссылкой на A16. Это позволит ему расширяться всякий раз, когда вы копируете формулу ниже.
Итак, мы знаем `ссылка: / поиск-формулы-получение-сопоставления-значений-из-не-соседнего-списка [как работают функции ИНДЕКС и ПОИСКПОЗ]». Основная часть здесь:
COUNTIF ($ E $ 1: E1, $ A $ 2: A16): эта формула вернет массив единиц и нулей. Каждый раз, когда значение в диапазоне $ E $ 1: E1 обнаруживается в списке критериев $ A $ 2: A16, значение преобразуется в 1 в его позиции в диапазоне $ A $ 2: A16.
Теперь, используя функцию MATCH, мы ищем значения 0. Match вернет позицию первого 0, найденного в массиве, возвращаемом функцией COUNTIF. Затем ИНДЕКС будет искать в A $ 2: A16 значение, найденное по индексу, возвращаемому функцией ПОИСКПОЗ.
Это может быть немного сложно для понимания, но это работает. 0 в конце списка означает, что уникальных значений больше нет. Если вы не видите 0 в конце, скопируйте формулу в ячейки ниже.
Теперь, чтобы избежать #NA, вы можете использовать функцию ЕСЛИОШИБКА в excel.
|
\{=IFERROR(INDEX($A$2:A16,MATCH(0,COUNTIF(E$1:$E1,$A$2:A16),0)),»»)} |
Примечание: не вставляйте фигурные скобки вручную, используйте Ctrl + Shift + Enter, чтобы получить результат.
Надеюсь, эта статья о том, как отфильтровать уникальные записи в Excel в Excel, носит пояснительный характер. Дополнительные статьи об извлечении значений и связанных формул Excel можно найти здесь. Если вам понравились наши блоги, поделитесь ими с друзьями на Facebook. А также вы можете подписаться на нас в Twitter и Facebook. Мы будем рады услышать от вас, дайте нам знать, как мы можем улучшить, дополнить или усовершенствовать нашу работу и сделать ее лучше для вас. Напишите нам на [email protected].
Статьи по теме:
link: / lookup-formulas-excel-formula-to-extract-unique-values-from-a-list [Формула Excel для извлечения уникальных значений из списка] | Чтобы найти все уникальные значения из списка в Excel 2016 и более ранних версиях, мы используем эту простую формулу.
link: / custom-functions-in-vba-extract-unique-values-in-excel-using-one-function [UNIQUE Function для Excel 2016] | Excel 2016 не предоставляет УНИКАЛЬНУЮ функцию, но мы можем ее создать. Эта функция UDF UNIQUE возвращает все уникальные значения из списка.
link: / counting-how-to-count-unique-values-in-excel-with-conditions-2 [Как подсчитывать уникальные значения в Excel с помощью критериев] | Для подсчета уникальных значений в Excel с помощью критериев мы используем комбинацию функций. Функция ЧАСТОТА является ядром этой формулы
Популярные статьи:
link: / tips-if-condition-in-excel [Как использовать функцию ЕСЛИ в Excel]: оператор IF в Excel проверяет условие и возвращает конкретное значение, если условие истинно, или другое конкретное значение, если ЛОЖЬ .
link: / formulas-and-functions-Introduction-of-vlookup-function [Как использовать функцию VLOOKUP в Excel]: это одна из наиболее часто используемых и популярных функций Excel, которая используется для поиска значений из разных диапазонов и листы.
link: / excel-formula-and-function-excel-sumif-function [Как использовать функцию СУММЕСЛИ в Excel]: Это еще одна важная функция панели инструментов. Это поможет вам суммировать значения для конкретных условий.
link: / tips-countif-in-microsoft-excel [Как использовать функцию СЧЁТЕСЛИ в Excel]: Подсчет значений с условиями с помощью этой удивительной функции. Вам не нужно фильтровать данные для подсчета определенных значений. Функция Countif важна для подготовки вашей приборной панели.