Как в Экселе найти уникальные значения

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

Конечно, вы можете использовать инструмент удаления дубликатов, но если список динамически обновляется, лучше применять рассмотренные ниже практики, т.к. они будут автоматически «подхватывать» изменения.

Опишу пример из жизни: есть таблица имён для ребёнка, куда записали предложения все члены семьи. Нам нужно убрать повторы из этого перечня:

Алгоритм поиска таков:

1. Первым уникальным значением будем считать заголовок второй таблицы. Это необходимо, чтобы дальнейшая логика срабатывала
2. Посчитаем, сколько раз уже найденные уникальные значения встречаются в общем перечне. Получим для каждой строчки массивы с количеством элементов, равным размеру исходного списка. В нашем примере это 9 элементов. Для первой строки результат должен выглядеть так: {0;0;0;0;0;0;0;0;0}. То есть, ни одно из уникальных имён не найден в исходнике
3. В этом массиве нулями будут помечены те имена, которые еще не были добавлены в список. Добавляем к уникальным то имя, которому соответствует ближайший к началу нолик.
4. Переходим к следующей строке уникального списка и проделываем всё заново, начиная со второго пункта. При этом, массив уже будет выглядеть так: {1;0;0;0;0;0;0;0;0}
5. Останавливаемся, когда все уникальные имена будут выведены

Надеюсь, принцип ясен. Реализовываем:

1. 1. Для подсчёта вхождений уникальных значений в изначальный перечень, применим такую формулу массива: {=СЧЁТЕСЛИ($D$1:D1;список)}

      Обратите внимание на 2 момента здесь:

      • Интервал $D$1:D1 означает, что первая ячейка $D$1 будет оставаться неизменной, а последняя (D1) – спускаться ниже, когда вы копируете формулу вниз. Таким образом, заданный диапазон всегда будет охватывать все найденные уникальные имена
      • Формула массива СЧЁТЕСЛИ принимает в качестве критерия диапазон, а значит, будет считать количество совпадений уникальных имён с каждой из ячеек интервала-критерия. Таким образом, мы получим массив, о котором говорили выше
   2. Теперь в полученном массиве ищем первое нулевое значение с помощью функции ПОИСКПОЗ: {=ПОИСКПОЗ(0;СЧЁТЕСЛИ($D$1:D1;список);0)}

      Результатом выполнения этого шага будет индекс найденного уникального значения в полном списке

   3. Используем функцию ИНДЕКС для вывода найденного уникального значения в ячейку. Её порядковым номером будет значение, которое вернула функция ПОИСКПОЗ: {=ИНДЕКС(список;ПОИСКПОЗ(0;СЧЁТЕСЛИ($D$1:D1;список);0))}

      

   4. Скопируем эту формулу в оставшиеся ячейки и получим все уникальные значения. Но видим в последних двух строчках ошибку #Н/Д. Это из-за того, что все уникальные значения найдены, новые уже не находятся.

      

   5. Используем функцию перехвата ошибки ЕСНД, чтобы вывести пустую строку («») вместо текста ошибки:

      {=ЕСНД(ИНДЕКС(список;ПОИСКПОЗ(0;СЧЁТЕСЛИ($D$1:D1;список);0));«»)}

      

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