Список уникальных элементов

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

Исходные данные

Пусть у нас будет такой список:

Теперь в ячейку D3 вставим следующую формулу:

=ИНДЕКС(B3:B15;ПОИСКПОЗ(0;СЧЁТЕСЛИ($D$2:D2;B3:B15);0))

Важное замечание! Мы вводим формулу, работающую с массивом. Поэтому после ввода формулы надо нажать сочетание клавиш Ctrl+Shift+Enter.

На картинке хорошо видно что введенная формула находится между символом { и }. Этим легко отличать формулы для работы с массивом от обычных формул.

Еще важное замечание, вводя формулу работы с массивом необходимо именно завершать ввод нажатием комбинации клавиш Ctrl+Shift+Enter. Если при вводе формулы набрать ее между символом { и }, Excel воспримет такие данные как обычную строку.

После ввода формулы в ячейку D3 растянем формулу до ячейки D15. И увидим интересный результат.

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

Еще одно замечание. Обратите внимание на исходную таблицу в ячейке B7 — значение «Сергей», а в ячейке В8 «Сергеи». Так же и в результате мы имеем два значения. Excel воспринял два этих значения как разные (ячейки D4 и D7), что вполне логично, так как есть отличие в одной букве. Для демонстрации того что в результате у нас получается то что хотели. И что не одно значение не пропало в исходной таблице в ячейке B15 – значение «Анатолий». В таблице результатов это же значение так же стоит последним. Таким образом, в таблице результатов у нас все уникальные значения из исходной таблицы.

Как это работает

Начнем изнутри, как видит эту формулу Excel. Функция «СЧЕТЕСЛИ» считает, сколько раз текущий элемент встречается в исходном списке. Особенность работы этой функции и используется в формуле. Дело в том, что функция просматривает массив и для ячеек, критерий поиска у которых совпадает с условием принимается результат логическая истина (1), иначе логическое отрицание 0. Получается когда функция просматривает исходный диапазон в поиске значения «Сергей», для нее диапазон выглядит как – истинно, ложно, ложно, истинно, ложно, ложно, ложно,  ложно, ложно, ложно, ложно, ложно. Или если быть точнее 1, 0, 0, 1, 0, 0, 0,  0, 0, 0, 0, 0.

Именно поэтому следующая функция «ПОИСКПОЗ» ищет значение 0 (первый параметр) в массиве который дает «СЧЕТЕСЛИ» выбирая точное соответствие, значение 0 (третий параметр). Так мы получаем позицию первого элемента, которого еще нет в наборе результатов.

Ну и последняя функция «ИНДЕКС» просто выбирает из исходного массива элемент с позицией указанно функцией «ПОИСКПОЗ».

В заключении

У формулы есть один серьёзный недостаток, список уникальных элементов указан в примере для первого элемента в ячейке D3 как $D$2:D2. Связанно это с тем, что необходимо создать расширенную ссылку на список, исключая ссылку на самого себя. Таким образом вынуждены ссылаться на ячейку выше текущей. Отсюда вывод, эту формулу нельзя ввести в первую строку, начинать можно только со второй.