Anteriormente, aprendemos como contar valores exclusivos em um intervalo. Também aprendemos como extrair valores exclusivos de um intervalo. Neste artigo, aprenderemos como contar o valor único na faixa com a condição no Excel.
Fórmula Genérica
{= SUM (- (FREQUENCY (IF (condition, MATCH (range, range, 0)), ROW (range) -ROW (firstCell in range) +1)> 0))}
É uma fórmula de matriz, use CTRL + SHIFT + ENTER
Doença : Os critérios pelos quais você deseja obter valores exclusivos.
Faixa : intervalo no qual você deseja obter valores únicos.
primeira célula no alcance: É a referência da primeira célula em faixa. Se o intervalo for A2: A10, será A2.
Exemplo:
Aqui eu tenho esses dados de nomes. As classes correspondentes são mencionadas na coluna adjacente. Precisamos contar nomes exclusivos em cada classe.
Usando a fórmula genérica acima, escreva esta fórmula em E2
{= SOMA (- (FREQUÊNCIA (SE (B2: B19 = "Classe 1", CORRESPONDÊNCIA (A2: A19, A2: A19,0)), LINHA (A2: A19) -ROW (A2) +1)> 0 ))}
A fórmula acima retorna o valor exclusivo no intervalo do excel A2: A19 na condição de B2: B19 = "Classe 1".
Para obter valores exclusivos em classes diferentes, altere os critérios. Codificamos aqui, mas você também pode fornecer a referência da célula. Use intervalos nomeados ou referência absoluta para intervalos, se você não quiser que eles mudem.
Como funciona?
Vamos decompô-lo por dentro.
E SE(B2: B19 = "Classe 1",PARTIDA(A2: A19, A2: A19,0))
B2: B19 = "Classe 1": Esta parte retornará uma matriz de verdadeiro e falso. VERDADEIRO para cada partida.
{VERDADEIRO; FALSO; VERDADEIRO; FALSO; VERDADEIRO; VERDADEIRO; FALSO….}
PARTIDA(A2: A19, A2: A19,0): esta parte retornará a primeira localização de cada valor no intervalo A2: A19 de acordo com a propriedade da MATCH.
{1;2;1;4;5;4;1;8;9;1;2;1;4;5;4;1;8;9}.
Agora, para cada valor TRUE obteremos a posição e para false obteremos FALSE. Portanto, para toda a instrução IF, obteremos
{1; FALSE; 1; FALSE; 5; 4; FALSE; FALSE; FALSE; FALSE; 2; FALSE; FALSE; 5; FALSE; 1; 8; FALSE}.
Em seguida, passamos para a parte da frequência.
FREQUÊNCIA(E SE(B2: B19 = "Classe 1",PARTIDA(A2: A19, A2: A19,0)),FILEIRA(A2: A19) -FILEIRA(A2) +1)
ROW (A2: A19): Isso retorna o número da linha de cada célula no intervalo A2: A19.
{2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19}
ROW (A2: A19) -ROW (A2): Agora subtraímos o número da primeira linha de cada número de linha. Isso retorna uma matriz de número de série começando em 0.
{0;1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17}
Como queremos um número de série começando em 1, adicionamos 1 a ele.
ROW (A2: A19) -ROW (A2) +1. Isso nos dá uma matriz de número de série começando em 1.
{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18}
Isso nos ajudará a obter uma condição única de contagem.
Agora temos: FREQUÊNCIA({1; FALSE; 1; FALSE; 5; 4; FALSE; FALSE; FALSE; FALSE; 2; FALSE; FALSE; 5; FALSE; 1; 8; FALSE},{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18})
Isso retorna a frequência de cada número na matriz fornecida. {3; 1; 0; 1; 2; 0; 0; 1; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0}
Aqui, cada número positivo indica a ocorrência de valor único quando os critérios são atendidos. Precisamos contar valores maiores que 0 nesta matriz. Para isso, verificamos por> 0. Isso retornará TRUE e FALSE. Convertemos verdadeiro falso usando - (operador binário duplo).
SOMA(--({3;1;0;1;2;0;0;1;0;0;0;0;0;0;0;0;0;0;0})>0) isso se traduz em SOMA ({1; 1; 0; 1; 1; 0; 0; 1; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0})
E, finalmente, obtemos a contagem única de nomes no intervalo de critérios como 5.
Eu sei que é um pouco complexo de entender, mas você verifica na opção de avaliação de fórmula.
Para contar valores únicos com vários critérios, podemos usar a lógica booleana:
Conte o valor exclusivo com vários critérios com e lógica
{= SUM (- (FREQUENCY (IF (condition1 * Condition2, MATCH (range, range, 0)), ROW (range) -ROW (firstCell in range) +1)> 0))}
A fórmula genérica acima pode contar valores únicos em várias condições e quando todas elas forem verdadeiras.
Conte o valor exclusivo com vários critérios com ou lógica
{= SUM (- (FREQUENCY (IF (condition1 + Condition2, MATCH (range, range, 0)), ROW (range) -ROW (firstCell in range) +1)> 0))}
Esta fórmula genérica pode ser usada para contar valores únicos com a lógica Or. Isso significa que contará se alguma das condições for verdadeira.
Então, sim pessoal, é assim que você conta valores únicos em um intervalo em várias condições. Isso é um pouco complexo, mas é rápido. Depois de começar a usá-lo, você saberá como funciona.
Se você tiver alguma dúvida em relação a este artigo sobre a fórmula do Excel, deixe-me saber na seção de comentários abaixo.
⇬ Fazer download do arquivo:
Como contar valores únicos no Excel com critériosFórmula do Excel para extrair valores únicos de uma lista
Contar valores únicos no Excel
Artigos populares:
A função VLOOKUP no Excel
CONT.SE no Excel 2016
Como usar a função SUMIF no Excel
