Загальна формула
=UNIQUE(FILTER(data,COUNTIF(data,data)>n))
Резюме
Щоб вилучити список унікальних значень із набору даних, відфільтрованих за рахунком або випадком, ви можете використовувати UNIQUE з FILTER та застосувати критерії за допомогою функції COUNTIF. У наведеному прикладі формула в D5 має вигляд:
=UNIQUE(FILTER(data,COUNTIF(data,data)>1))
який виводить 3 унікальних значення, які з'являються більше одного разу в названому діапазоні "дані" (B5: B16).
Примітка: У цьому прикладі ми витягуємо унікальний список значень, які з’являються більше одного разу. Іншими словами, ми створюємо список дублікатів :) Мова дещо заплутана.
Пояснення
У цьому прикладі використовується функція UNIQUE разом із функцією FILTER. Більш базовий приклад ви можете побачити тут.
Фокус у цьому випадку полягає у застосуванні критеріїв до функції ФІЛЬТР, щоб дозволити лише значення на основі кількості випадків. Працюючи зсередини, це робиться за допомогою функції COUNTIF і функції ФІЛЬТР тут:
FILTER(data,COUNTIF(data,data)>1)
Результат від COUNTIF - це масив таких підрахунків:
(3;1;3;3;2;1;1;3;1;2;3;3)
які перевіряються за допомогою логічного порівняння> 1, щоб отримати масив або значення TRUE / FALSE:
(TRUE;FALSE;TRUE;TRUE;TRUE;FALSE;FALSE;TRUE;FALSE;TRUE;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 або створити динамічний іменований діапазон із формулою.
