Общая формула
=UNIQUE(FILTER(data,COUNTIF(data,data)>n))
Резюме
Чтобы извлечь список уникальных значений из набора данных, отфильтрованных по количеству или появлению, вы можете использовать UNIQUE с FILTER и применить критерии с помощью функции COUNTIF. В показанном примере формула в D5:
=UNIQUE(FILTER(data,COUNTIF(data,data)>1))
который выводит 3 уникальных значения, которые появляются более одного раза в названном диапазоне «данные» (B5: B16).
Примечание. В этом примере мы извлекаем уникальный список значений, которые встречаются более одного раза. Другими словами, мы создаем список дубликатов :) Язык несколько запутанный.
Объяснение
В этом примере функция UNIQUE используется вместе с функцией FILTER. Здесь вы можете увидеть более простой пример.
Уловка в этом случае состоит в том, чтобы применить критерии к функции ФИЛЬТР, чтобы разрешить только значения, основанные на количестве появлений. Работая изнутри, это делается с помощью СЧЁТЕСЛИ и функции ФИЛЬТР:
FILTER(data,COUNTIF(data,data)>1)
Результатом СЧЁТЕСЛИ является массив таких значений:
(3;1;3;3;2;1;1;3;1;2;3;3)
которые проверяются логическим сравнением> 1 для получения массива или значений ИСТИНА / ЛОЖЬ:
(TRUE;FALSE;TRUE;TRUE;TRUE;FALSE;FALSE;TRUE;FALSE;TRUE;TRUE;TRUE)
Обратите внимание, что ИСТИНА соответствует значениям в данных, которые встречаются более одного раза. Этот массив возвращается в FILTER в качестве аргумента включения , используемого для фильтрации данных. В результате FILTER возвращает другой массив:
("red";"green";"green";"blue";"red";"blue";"red";"green")
Этот массив возвращается непосредственно в функцию UNIQUE как аргумент массива . Обратите внимание на 12 исходных значений, только 8 сохранились.
Затем UNIQUE удаляет дубликаты и возвращает окончательный массив:
("red";"green";"blue")
Если значения в B5: B16 меняются, выходные данные обновляются немедленно.
Счетчик> 2
Формула в F5, в которой перечислены цвета, встречающиеся как минимум 2 раза в исходных данных, выглядит так:
=UNIQUE(FILTER(data,COUNTIF(data,data)>2))
Диапазон динамического источника
Поскольку данные (B5: B15) являются обычным именованным диапазоном, его размер не изменится при добавлении или удалении данных. Чтобы использовать динамический диапазон, размер которого будет автоматически изменяться при необходимости, вы можете использовать таблицу Excel или создать динамический именованный диапазон с помощью формулы.
