У меня есть ведомость материалов, которую я хочу отфильтровать по нескольким столбцам, а затем подсчитать количество уникальных значений в столбце Номер детали. Это количество должно обновляться, когда я изменяю критерии фильтра в других столбцах. Как я могу это сделать? Спасибо!
1 ответ
5
От MrExcel.com:
=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(A2,ROW(A2:A10)-ROW(A2),,1)),
IF(A2:A10<>"",MATCH("~"&A2:A10,A2:A10&"",0))),ROW(A2:A10)-ROW(A2)+1),1))
Формула предполагает, что диапазон данных равен A2:A10. Вам нужно будет отрегулировать ссылки для ячейки A2 и диапазона A2:A10 в соответствии с вашими данными.
Это формула массива и должна вводиться с помощью комбинации клавиш Control - Shift - Enter .
Если присмотреться к выражению более внимательно, можно увидеть, что оно зависит от использования массивов в сочетании с четырьмя функциями Excel. Это объясняется ниже. В целях иллюстрации обсуждение будет сосредоточено на следующем примере с 9 значениями в ячейках A2:A10 и только 5 видимыми после фильтрации.
1. MATCH("~" & A2:A10, A2:A10 & "", 0)
Функция MATCH обычно используется для поиска значения в другом диапазоне значений и, если оно найдено, для возврата позиции строки, в которой найдено совпадающее значение.
Когда массив используется как в качестве значения поиска, так и диапазона поиска, MATCH возвращает массив, который показывает позицию строки первого соответствующего значения, если оно есть, для каждого из значений в диапазоне.
Если диапазон включает повторяющиеся значения, то же самое положение строки возвращается для каждого повторяющегося значения. Используя данные примера, функция MATCH выдаст:
MATCH(A2:A10, A2:A10, 0)}
-->MATCH({11, 98, 67, , 37, 67, 98, 56, 67},{11, 98, 67, ,37, 67, 98, 56, 67},0)
--> { 1, 2 , 3, 4, 5, 3, 2, 8, 3}
Немного поработав, этот результат можно использовать для подсчета того, сколько раз каждое значение встречается в исходном массиве: например, обратите внимание, что позиция 3 строки для значения 67 отображается в массиве результатов три раза, что соответствует три вхождения этого значения во входном массиве.*
2. SUBTOTAL(3, OFFSET(A2, ROW(A2:A10)-ROW(2),,1)
Особая трудность в этом вопросе состоит в том, как отличить значения, отображаемые в отфильтрованном списке, от значений, которые не отображаются. Функция SUBTOTAL примененная к диапазону, включает в свой результат только видимые строки, но не предоставляет информацию о том, являются ли отдельные ячейки в своем входном диапазоне видимыми или нет, даже если они введены как формула массива.
Однако функция OFFSET , как здесь используется, возвращает массив ячеек в такой форме, что SUBTOTAL создает результат массива. Эта формулировка, зачисленная Лорану Лонгре, дает массив 1 для видимых ячеек и 0 для скрытых ячеек.
SUBTOTAL(3, OFFSET(A2, ROW(A2:A10) - ROW(A2), , 1))
--> SUBTOTAL(3, OFFSET(A2, {2, 3,..., 10} - {2}, , 1))
--> SUBTOTAL(3, OFFSET(A2, {0, 1,..., 8}, , 1))
--> SUBTOTAL(3, ({A2}, {A3},...,{A9}))
--> {0, 1, 1, 0, 0, 0, 1, 1, 1}
Благодаря этому результату становится возможным подсчитывать в любых последующих вычислениях только те значения в диапазоне, которые видны после фильтрации.**
3. FREQUENCY(IF(SUBTOTAL(3,OFFSET(...)),IF(A2:A10<>"",MATCH(...))),
ROW(A2:A10)-ROW(A2)+1)
Функция FREQUENCY принимает два аргумента, массив значений данных и массив bin , и возвращает массив, который показывает, сколько значений в диапазоне попало в каждый bin. Например, если значения в диапазоне ячеек были (1,5, 2,5, 3,5), FREQUENCY вернет счетчик тех значений данных, которые меньше или равны 1,5, больше 1,5 и меньше или равны 2,5 и т.д.
В формуле массив данных выражается через комбинацию операторов IF и SUBTOTAL/OFFSET и MATCH . То, к чему это действительно сводится, является поэлементным AND для массивов, созданных этими выражениями.
SUBTOTAL/OFFSET ARRAY: {0, 1, 1, 0, 0, 0, 1, 1, 1} [visible vs. hidden]
MATCH ARRAY: {1, 2, 3, F, 5, 3, 2, 8, 3} [row position of 1st match]
RESULT ARRAY: {F, 2, 3, F, F, F, 2, 8, 3} [row position of 1st match
in visible cells]
'F' = FALSE
Массив bin, созданный выражением ROW(A2:A10)-ROW(A2)+1 представляет собой просто значения от 1 до 9, соответствующие возможным позициям строк в диапазоне A2:10.
BIN ARRAY: {1, 2, 3, 4, 5, 6, 7, 8, 9}
FREQUENCIES: {0, 2, 2, 0, 0, 0, 0, 1, 0}
Частоты соответствуют 2-кратному значению 98, отображаемому в отфильтрованном диапазоне, 2-кратному 67-му и 1-му 56-му.
Поскольку целью является подсчет количества различных значений в отфильтрованном диапазоне, заключительный включающий IF - IF(FREQUENCY(...), 1) - преобразует ненулевые значения в массиве частот в 1:
DISTINCT VALUES ARRAY: {F, 1, 1, F, F, F, F, 1, 0}
Суммирование этого массива дает окончательный ответ 3 для этого примера.
* Тильда (~) в выражении MATCH включена для экранирования таких символов, как '+' и '>', которые могли бы выдавать значение ошибки, если бы они были первыми символами в сопоставляемых значениях (потому что Excel скорее воспринимал бы их как операторы чем персонажи).
** Первый аргумент функции SUBTOTAL , значение 3, сообщает функции, чтобы она возвращала счетчик видимых ячеек.Значение, наиболее часто встречающееся, 9, дает сумму видимых ячеек.