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

image

Чтобы извлечь список уникальных значений из списка, мы будем использовать link: / lookup-formulas-excel-index-function [INDEX], link: / lookup-formulas-excel-match-function [MATCH]

и link: / tips-countif-in-microsoft-excel [COUNTIF].

Я также буду использовать link: / logic-formulas-excel-iferror-function [ЕСЛИОШИБКА], просто чтобы получить чистые результаты, это необязательно.

И да, это будет формула массива …​ Итак, давайте сделаем это …​ ===== Общая формула для извлечения уникальных значений в Excel

{=INDEX(ref_list,MATCH(0,COUNTIF(expanding_output_range,ref_list),0))}

Ref_list: список, из которого вы хотите извлечь уникальные значения link: / excel-range-name-expanding-links-in-excel [Expanding_output_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 также может увеличиваться, поэтому я хочу, чтобы моя формула извлекала любое новое имя клиента из списка при каждом увеличении списка.

image

Хорошо, теперь, чтобы получить уникальные значения из столбца A, запишите эту формулу в ячейку E2 и нажмите CTRL + SHIFT + ENTER, чтобы сделать ее формулой массива.

{=INDEX($A$2:A16,MATCH(0,COUNTIF($E$1:E1,$A$2:A16),0))}

image

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)),"")}

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

Скачать файл: