De 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))
A fórmula pressupõe que o intervalo de dados é A2: A10. Você precisará ajustar as referências para a célula A2 e o intervalo A2: A10 para ajustar seus dados.
Esta é uma fórmula de matriz e deve ser inserida com a combinação de teclas Controle - Shift - Enter .
Examinando mais de perto a expressão, percebe-se que ela depende do uso de matrizes em combinação com quatro funções do Excel. Estes são explicados abaixo. Para fins de ilustração, a discussão se concentrará no exemplo a seguir com um total de 9 valores nas células A2: A10, com apenas 5 visíveis após a filtragem.
1.MATCH("~" & A2:A10, A2:A10 & "", 0)
A função MATCH é normalmente usada para procurar um valor em outro intervalo de valores e, se o valor for encontrado, para retornar a posição da linha onde o valor correspondente é encontrado.
Quando uma matriz é usada como o valor de pesquisa e o intervalo de pesquisa, MATCH retorna uma matriz que mostra a posição da linha do primeiro valor correspondente, se houver, para cada um dos valores no intervalo.
Se o intervalo incluir valores duplicados, a mesma posição de linha será retornada para cada valor duplicado. Usando os dados de exemplo, a função MATCH produziria:
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}
Com um pouco de trabalho, esse resultado pode ser usado para obter uma contagem de quantas vezes cada valor ocorre na matriz original: por exemplo, observe que a posição da linha 3 para o valor 67 aparece três vezes na matriz resultante. , correspondendo às três ocorrências desse valor no array de entrada. *
2. SUBTOTAL(3, OFFSET(A2, ROW(A2:A10)-ROW(2),,1)
A dificuldade particular desta questão é como distinguir valores que aparecem na lista filtrada daqueles que não aparecem. A função SUBTOTAL aplicada a um intervalo inclui apenas linhas visíveis em seu resultado, mas não, mesmo quando inserida como uma fórmula de matriz, fornece informações sobre se as células individuais em seu intervalo de entrada estão visíveis ou não.
No entanto, a função OFFSET , como usada aqui, retorna uma matriz de células em um formulário tal que SUBTOTAL produz um resultado de matriz. Credenciado para Laurent Longre, essa formulação gera uma matriz de 1s para células visíveis e 0s para células ocultas .
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}
Com este resultado, torna-se possível contar em quaisquer cálculos subsequentes apenas os valores no intervalo visíveis após a filtragem. **
3. FREQUENCY(IF(SUBTOTAL(3,OFFSET(...)),IF(A2:A10<>"",MATCH(...))),
ROW(A2:A10)-ROW(A2)+1)
A função FREQUENCY usa dois argumentos, uma matriz de valores de dados e uma matriz bin , e retorna uma matriz que mostra quantos dos valores da faixa estão dentro de cada caixa. Por exemplo, se os valores no intervalo do intervalo fossem (1,5, 2,5, 3,5), FREQUENCY retornaria uma contagem desses valores de dados que eram menores ou iguais a 1,5, maiores que 1,5 e menores ou iguais a 2,5, etc.
Na fórmula, a matriz de dados é expressa por meio de uma combinação de instruções IF e as expressões SUBTOTAL/OFFSET e MATCH . O que isso realmente se resume é um elemento a elemento AND das matrizes produzidas por essas expressões.
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
O array bin produzido pela expressão ROW(A2:A10)-ROW(A2)+1 é simplesmente os valores de 1 a 9, correspondentes às possíveis posições de linha no intervalo A2: 10.
BIN ARRAY: {1, 2, 3, 4, 5, 6, 7, 8, 9}
FREQUENCIES: {0, 2, 2, 0, 0, 0, 0, 1, 0}
As freqüências giram com o valor de 2 vezes o valor 98 na faixa filtrada, o 2 vezes 67 mostra, e a 1 hora 56 mostra.
Como o objetivo é contar o número de valores distintos no intervalo filtrado, o final entre IF - IF(FREQUENCY(...), 1) - converte os valores diferentes de zero na matriz de frequência em 1:
DISTINCT VALUES ARRAY: {F, 1, 1, F, F, F, F, 1, 0}
Somando esse array produzindo a resposta final de 3 para este exemplo.
* O til (~) na expressão MATCH é incluído para escapar de caracteres como '+' e '>' Isso produziria um valor de erro se eles fossem o primeiro caractere nos valores correspondentes (porque o Excel os trataria como operadores em vez de caracteres).
** O primeiro argumento para a função SUBTOTAL , o valor 3, diz à função para retornar uma contagem das células visíveis. O valor mais visto, 9, produz a soma das células visíveis.