Как подсчитать в Excel количество уникальных значений в массиве

Вопрос типовой. Приём решения тоже - объединение функции расчёта и функции ЕСЛИ. Достаточно уметь вкладывать условные функции в функции подсчёта, например:

=СУММ(ЕСЛИ(A1:A10>0; A1:A10;0))

- сумма положительных значений из диапазона A1:A10

=ПРОИЗВЕД(ЕСЛИ(A1:A10>0;ЕСЛИ(A1:A10<=1;A1:A10;1);1))

- произведение значений из диапазона A1:A10, попадающих в интервал ]0,1]

Формула, где в условии участвует диапазон, завершается нажатием комбинации клавиш Ctrl+Shift+Enter.

Теперь наш подсчёт количества различных значений в диапазоне-столбце.

=СУММ(ЕСЛИ(ЧАСТОТА(Диапазон;Диапазон)>0;1;0))

- если данные - только числовые.

=СУММ(ЕСЛИ(ЧАСТОТА(ПОИСКПОЗ(Диапазон;Диапазон;0),ПОИСКПОЗ(Диапазон;Диапазон;0))>0;1;0))

- работает и со строками.

Третий аргумент функции ПОИСКПОЗ (равный здесь 0) на самом деле обозначает тип поиска:

• Если тип_сопоставления равен 1, то функция ПОИСКПОЗ находит наибольшее значение, которое меньше либо равно, чем искомое_значение. Просматриваемый_массив должен быть упорядочен по возрастанию: ..., -2, -1, 0, 1, 2, ..., A-Z, ЛОЖЬ, ИСТИНА.
• Если тип_сопоставления равен 0, то функция ПОИСКПОЗ находит первое значение, которое в точности равно аргументу искомое_значение. Просматриваемый_массив может быть не упорядочен.
• Если тип_сопоставления равен -1, то функция ПОИСКПОЗ находит наименьшее значение, которое больше либо равно чем искомое_значение. Просматриваемый_массив должен быть упорядочен по убыванию: ИСТИНА, ЛОЖЬ, Z-A, ..., 2, 1, 0, -1, -2, ..., и так далее.

Как для всех формул массива, после ввода нажимается Ctrl+Shift+Enter.

Пустых ячеек эти формулы "не понимают", лист должен быть заполнен без пропусков. Регистры при сопоставлении текстов не различаются. Если не настроены российские региональные стандарты, разделитель аргументов ; в формулах, возможно, придётся заменить на запятые.

При указании диапазонов в виде целых столбцов (A:A) функция ПОИСКПОЗ, скорее всего, откажется работать, а вот при указании ограниченных дипазонов всё будет нормально. Скажем, этой формулой я пользовался сегодня:

=СУММ(ЕСЛИ(ЧАСТОТА(ПОИСКПОЗ(Лист1!$A$2:$A$6585;Лист1!$A$2:$A$6585;1);
ПОИСКПОЗ(Лист1!$A$2:$A$6585;Лист1!$A$2:$A$6585;1))>0;1;0))

Обратите внимание, что для нормальной работы ПОИСКПОЗ лист сортируется по столбцу, содержащему указанный диапазон (меню Данные, Сортировка).

 на сайт

06.02.2010, 22:00 [17212 просмотров]