Высокооплачиваемый американский аналитик несколько лет назад рассказал мне о способе, как в Excel можно только при помощи формул отфильтровать и отсортировать список, оставив только уникальные значения. В этой статье я поделюсь этим способом с вами.
Надо заметить, что все, что написано ниже касется версий Excel, не относящихся к подписке Microsoft 365, так как в подписной версии появилась новые функции UNIQUE и SORT, работающие с динамическими массивами и позволяющие решить задачу в два счета.
В Google Таблицах тоже есть такие же функции UNIQUE и SORT - появились они еще раньше, чем в Excel. Ну а нам, пользователям старых версий Excel остается только этот способ.
В примере ниже я использую функции INDEX (ИНДЕКС), MATCH (ПОИСКПОЗ), COUNTIF (СЧЁТЕСЛИ), IFERROR (ЕСЛИОШИБКА) которые позволяют работать с массивами. Здесь и далее при первом использовании я указываю название функции на английском языке первым, название на русском в скобках, при повторном упоминании я буду использовать английское название. В конце статьи я выложу получившийся файл для свободного скачивания. А вот и сама формула (для начала простой вариант, без сортировки):
=IFERROR(INDEX($A$2:$A$18;MATCH(0;COUNTIF($C$1:$C1;$A$2:$A$18);0));"")
Пара моментов, которые важно понимать:
- Это формула массива, о чем говорят фигурные скобки вокруг формулы. Вводить их вручную бесполезно, нужно в строке с формулой нажать Ctrl + Shift + Enter.
- Исходные значения не должны содержать пустых строк (мы это поправим чуть позже)
- Формула не может располагаться в первой строке (только начиная со второй), а ячейке над формулой должен быть уникальный текст, не содержащийся в списке значений.
Разберем, как работает эта формула:
- Обратим внимание, что так как первый аргумент в COUNTIF содержит диапазон, не фиксированный с помощью знака "$", для каждой новой ячейки списка в колонке C формула немного отличается от предыдущей, захватывая на одну ячейку больше сверху.
- Функция COUNTIF возвращает на выходе число ячеек из аргумента 1, для которых выполняется требование аргумента 2. Поскольку у нас в качестве аргумента 2 используется массив, то и результат этой функции тоже массив. На иллюстрации выше он отображен в колонке E.
- Функция MATCH ищет позицию первого аргумента в массиве, заданном вторым аргументом. Третий аргумент указывает как искать (сверху вниз, снизу вверх, или приблизительный поиск при сортированном списке). В тех случаях, когда первый аргумент не найден, функция возвращает ошибку "N/A". В нашем случае первый аргумент "0". Второй аргумент - результат функции COUNTIF, третий аргумент тоже "0", означающий что искать нужно сверху вниз. Результат функции MATCH отображен на иллюстрации в колонке F.
- Функция INDEX извлекает из массива, указанного в первом аргументе, значение на позиции, указанной вторым аргументом.
- Наконец, последняя функция, IFERROR позволяет убрать из выдачи ошибки "N/A", заменив их на значение второго аргумента - в нашем случае это пустая строка.
Объем статьи получился уже достаточно большой, поэтому продолжение про сортировку я напишу отдельно.
UPD: продолжение доступно по ссылке https://zen.yandex.ru/media/id/5f24070dd98a994308ce5e45/tolko-pri-pomosci-formul-spisok-unikalnyh-znachenii-i-sortirovka-5f355126a7d25b5b72636944
Попробуйте сами: по ссылке доступен файл Excel со всеми формулами.