Digamos que você queira contar itens exclusivos de uma lista, mas com uma diferença. E digamos que você esteja trabalhando com esta planilha:
A coluna D conta o número de linhas em cada uma das seções da coluna B e a coluna C conta o número de seções exclusivas com base nos primeiros cinco caracteres da coluna A para essa seção. As células B2: B11 contêm ARG e você pode contar oito itens exclusivos nos primeiros cinco caracteres de A2: A11 porque A7: A9 cada uma contém 11158, portanto, as duas duplicatas não são contadas. Da mesma forma, o 5 em D12 informa que há cinco linhas para BRD, mas dentro das linhas 12:16, há três itens exclusivos dos primeiros cinco caracteres, já que 11145 é repetido e 11173 é repetido.
Mas como você diz ao Excel para fazer isso? E que fórmula você poderia usar em C2 que poderia ser copiada para C12 e C17?
A fórmula de contagem simples em D2,, =COUNTIF(B:B,B2)conta o número de vezes que B2 (ARG) existe na coluna B.
Você usa uma coluna auxiliar para isolar os primeiros cinco caracteres da coluna A, como nesta figura:
Em seguida, você precisa indicar de alguma forma que, para ARG, você está interessado apenas nas células F2: F11 para encontrar o número de itens exclusivos. Em geral, você encontraria esse valor usando a fórmula de matriz mostrada nesta figura:
Você usa a célula C3 temporariamente apenas para mostrar a fórmula; você pode ver que não está presente em C3 nas figuras anteriores. (Você aprenderá em breve como essa fórmula funciona.)
Então, qual é a fórmula em C2, C12 e C17? A resposta surpreendente (e legal) é mostrada nesta figura:
Uau! Como é que isso funciona?
Dê uma olhada na resposta nos nomes definidos nesta figura:
É a mesma fórmula de uma figura anterior, mas em vez de usar o intervalo F2: F11, ele usa um intervalo denominado Rg. Além disso, a fórmula era uma fórmula de matriz, mas as fórmulas nomeadas são tratadas como se fossem fórmulas de matriz! Isto é, =Answernão é inserido com Ctrl + Shift + Enter, mas simplesmente inserido como de costume.
Então, como Rg é definido? Se a célula C1 for selecionada (o que é uma etapa importante para a compreensão desse truque), ela será definida como nesta figura:
Isso é =OFFSET(Loan_Details!$F$1,MATCH(Loan_Details!$B1,Loan_Details!$B:$B,0)-1,0,COUNTIF(Loan_Details!$B:$B,Loan_Details!$B1),1).
Loan_Details é o nome da planilha, mas você pode olhar para esta fórmula sem o nome longo da planilha. Uma maneira fácil de fazer isso é nomear temporariamente a planilha com algo simples, como x, e depois olhar novamente para o nome definido:
Esta fórmula é mais fácil de ler!
Você pode ver que esta fórmula corresponde a $ B1 (observe a referência relativa à linha atual) com relação a toda a coluna B e subtrai 1. Você subtrai 1 porque está usando OFFSET de F1. Agora que você sabe sobre a fórmula de C, dê uma olhada na fórmula de C2:
A MATCH($B2,$B:$B,0)parte da fórmula é 2, então a fórmula (sem a referência ao nome da planilha) é:
=OFFSET($F$1,2-1,0,COUNTIF($B:$B,$B2),1)
ou:
=OFFSET($F$1,1,0,COUNTIF($B:$B,$B2),1)
ou:
=OFFSET($F$1,1,0,10,1)
Porque COUNTIF($B:$B,$B2)é 10, existem 10 ARGs. Este é o intervalo F2: F11. Na verdade, se a célula C2 for selecionada e você pressionar F5 para ir para Rg, verá o seguinte:
Se a célula inicial fosse C12, pressionar F5 para ir para Rg produz isso:
Agora, com a resposta definida como =SUM(1/COUNTIF(rg,rg)), está tudo feito!
Vamos examinar mais de perto como essa fórmula funciona, usando um exemplo muito mais simples. Normalmente, a sintaxe para CONT.SE é =COUNTIF(range,criteria), como =COUNTIF(C1:C10, "b")nesta figura:
Isso daria 2 como o número de bs no intervalo. Mas a passagem do próprio intervalo como critério usa cada item do intervalo como critério. Se você destacar esta parte da fórmula:
e pressione F9, você verá:
Cada item do intervalo é avaliado, e essa série de números significa que há um a e há dois b's, três c's e quatro d's. Esses números são divididos em 1, dando 1, ½, ½, ⅓, ⅓, ⅓, ¼, ¼, ¼, ¼, como você pode ver aqui:
Então você tem 2 metades, 3 terços, 4 quartos e 1 inteiro, e somando-os resulta em 4. Se um item fosse repetido 7 vezes, você teria 7 sétimos e assim por diante. Muito legal! (Tiramos o chapéu para David Hager por descobrir / inventar esta fórmula.)
mas espere um minuto. Da forma como está, você só precisa inserir essa fórmula em C2, C12 e C17. Não seria melhor se você pudesse inseri-lo em C2 e preencher e mostrá-lo apenas nas células corretas? Na verdade, você pode fazer isso. Você pode modificar a fórmula em C2 para ser =IF(B1B2,Answer,""), e quando você preencher isso, ela fará o trabalho:
Mas por que parar aqui? Por que não transformar a fórmula em uma fórmula nomeada, conforme mostrado aqui:
Para que isso funcione, a célula C2 deve ser a célula ativa (ou a fórmula teria que ser diferente). Agora você pode substituir as fórmulas da coluna C por =Answer2:
Você pode ver que C3 tem =Answer2, assim como todas as células na coluna C. Por que não continuar com isso na coluna D? A fórmula em D2, após também aplicar a comparação com B1 e B2, é mostrada aqui:
Portanto, se você mantiver a célula D2 selecionada e definir outra fórmula, diga Resposta3:
então você pode entrar =Answer3na célula D2 e preencher:
Esta é a parte superior da planilha, com as fórmulas sendo mostradas, seguidas pela mesma captura de tela com os valores mostrando:
Quando outras pessoas tentam descobrir isso, elas podem coçar a cabeça no início!
Este artigo convidado é do Excel MVP Bob Umlas. É do livro More Excel Outside the Box. Para ver os outros tópicos do livro, clique aqui.
