Общая формула
(=INDEX(list,MATCH(0,COUNTIF(uniques,list),0)))
Резюме
Чтобы извлечь только уникальные значения из списка или столбца, можно использовать формулу массива на основе ИНДЕКС, ПОИСКПОЗ и СЧЁТЕСЛИ. В показанном примере формула в D5, скопированная ниже, имеет следующий вид:
(=INDEX(list,MATCH(0,COUNTIF($D$4:D4,list),0)))
где «список» - именованный диапазон B5: B11.
Примечание: это формула массива, и ее нужно вводить с помощью Ctrl + Shift + Enter.
Объяснение
Ядро этой формулы - это базовый поиск с помощью INDEX:
=INDEX(list,row)
Другими словами, дайте INDEX список и номер строки, и INDEX получит значение для добавления в уникальный список.
Тяжелая работа заключается в том, чтобы вычислить номер СТРОКИ, чтобы получить ИНДЕКС, чтобы мы получали только уникальные значения. Это делается с помощью ПОИСКПОЗ и СЧЁТЕСЛИ, и главный трюк здесь:
COUNTIF($D$4:D4,list)
Здесь COUNTIF подсчитывает, сколько раз элементы, уже находящиеся в уникальном списке, появляются в главном списке, используя расширяющуюся ссылку для диапазона, $ D $ 4: D4.
Расширяющаяся ссылка с одной стороны абсолютна, а с другой - относительна. В этом случае по мере копирования формулы ссылка будет расширяться, чтобы включить больше строк в уникальный список.
Обратите внимание, что ссылка начинается с D4, на одну строку выше первой уникальной записи в уникальном списке. Это сделано намеренно - мы хотим подсчитать элементы * уже * в уникальном списке, и мы не можем включить текущую ячейку без создания круговой ссылки. Итак, начнем с строки выше.
Важно: убедитесь, что заголовок уникального списка не отображается в главном списке.
Для критериев в COUNTIF мы используем сам главный список. Если задано несколько критериев, СЧЁТЕСЛИ вернет несколько результатов в массиве. В каждой новой строке у нас есть другой массив, например:
(0;0;0;0;0;0;0) // row 5 (1;0;0;0;1;0;0) // row 6 (1;1;0;0;1;0;1) // row 7 (1;1;1;1;1;0;1) // row 8
Примечание. СЧЁТЕСЛИ обрабатывает несколько критериев с отношением «ИЛИ» (т. Е. СЧЁТЕСЛИ (диапазон, («красный», «синий», «зеленый»)) подсчитывает красный, синий или зеленый цвет.
Теперь у нас есть массивы, которые нам нужны для поиска позиций (номеров строк). Для этого мы используем ПОИСКПОЗ, настроенное на точное совпадение, чтобы найти нулевые значения. Если мы поместим массивы, созданные с помощью COUNTIF выше, в MATCH, мы получим следующее:
MATCH(0,(0;0;0;0;0;0;0),0) // 1 (Joe) MATCH(0,(1;0;0;0;1;0;0),0) // 2 (Bob) MATCH(0,(1;1;0;0;1;0;1),0) // 3 (Sue) MATCH(0,(1;1;1;1;1;0;1),0) // 6 (Aya)
ПОИСКПОЗ обнаруживает элементы, ища счетчик, равный нулю (то есть ищет элементы, которые еще не появляются в уникальном списке). Это работает, потому что ПОИСКПОЗ всегда возвращает первое совпадение при наличии дубликатов.
Наконец, позиции вводятся в ИНДЕКС как номера строк, и ИНДЕКС возвращает имя в этой позиции.
Версия без массива с LOOKUP
Вы можете создать формулу без массива для извлечения уникальных элементов, используя гибкую функцию ПРОСМОТР:
=LOOKUP(2,1/(COUNTIF($D$4:D4,list)=0),list)
Конструкция формулы аналогична приведенной выше формуле ИНДЕКС ПОИСКПОЗ, но ПРОСМОТР может обрабатывать операцию с массивом изначально.
- СЧЁТЕСЛИ возвращает количество каждого значения из «списка» в расширяющемся диапазоне $ D $ 4: D4
- Сравнение с нулем создает массив значений ИСТИНА и ЛОЖЬ.
- Число 1 делится на массив, образуя массив из единиц и ошибок # DIV / 0.
- Этот массив становится lookup_vector внутри LOOKUP
- Значение поиска 2 больше, чем любые значения в lookup_vector
- ПРОСМОТР будет соответствовать последнему значению без ошибок в массиве поиска
- ПРОСМОТР возвращает соответствующее значение в result_vector, именованном диапазоне "list"
Извлеките элементы, которые появляются только один раз
Приведенную выше формулу ПРОСМОТР легко расширить с помощью булевой логики. Чтобы извлечь список уникальных элементов, которые появляются в исходных данных только один раз, вы можете использовать следующую формулу:
=LOOKUP(2,1/((COUNTIF($D$4:D4,list)=0)*(COUNTIF(list,list)=1)),list)
Единственное дополнение - это второе выражение COUNTIF:
COUNTIF(list,list)=1
Здесь СЧЁТЕСЛИ возвращает массив количества элементов, подобный этому:
(2;2;2;2;2;1;2)
которые сравниваются с 1, в результате получается массив значений ИСТИНА / ЛОЖЬ:
(FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE)
которые действуют как «фильтр», чтобы ограничить вывод элементами, которые встречаются в исходных данных только один раз.
