Excel: Contagem de duplicatas apenas uma vez, com base no terceiro critério

Question

Então eu tenho uma planilha de excel que lista (entre outras coisas) organizações e o ano em que elas estavam ativas. Algumas organizações estavam ativas mais de uma vez por ano. Parece assim:

YEAR -- ORGANIZATION

2015 --- Orga A
2015 --- Orga A
2015 --- Orga B
2014 --- Orga C
2014 --- Orga C
2013 --- Orga D
2013 --- Orga D

Estou tentando descobrir uma fórmula que me proporcione o número de organizações exclusivas ativas por ano (no exemplo: 2 em 2015, 1 em 2014 1 em 2013).

O que eu tentei: Eu criei uma nova coluna em C e inseri essa fórmula em cada linha:

=COUNTIF($B$2:B*X*,B*X*)=1

X = 2 (B2), na primeira linha, e que sobe 1 por linha (por exemplo, B2, B3, B4) Isso irá mostrar se é a primeira vez que a organização está ativa (como SIM / NÃO) .

Com isso, posso criar uma fórmula COUNTIF simples que contará o número que uma organização estava ativa. O problema, porém, é que isso se refere a se a organização estava ativa em todos os anos mais de uma vez, não por ano.

Escrever a função mencionada acima para cada ano não é uma opção, já que a planilha deve ser uma espécie de "autossustentável", já que é uma lista contínua que precisará acomodar os anos futuros.

Eu sinto que isso poderia ser resolvido com uma fórmula de matriz e uma função IF, mas não tenho certeza de como ... Qualquer entrada seria muito apreciada! (:

    

Answer 1

Eu achei muito estranho que ninguém respondesse a isso ... Bem, vou tentar então.

Ok, se você não gosta de tabelas dinâmicas, posso sugerir algumas soluções que não fazem uso delas.

PRIMEIRA MANEIRA

Os pré-requisitos são que você tenha a lista de todos os nomes de organização possíveis, bem como uma lista de todos os anos que você deseja verificar em relação ao número de organizações exclusivas ativas.

Noexemploquemostrei,coloqueisualistanacolunaA(intervaloA1:A7),alistadepossíveisnomesdeorganizaçõesnointervaloC2:C5eosanosde2013a2015emD1toF1células.

Agoraquevocêtemisso,vocêpodeobteroqueprecisasaberseguindoestasetapas:

1. emD2put=IF(IFERROR(MATCH(CONCATENATE(D$1," --- ",$C2),$A$1:$A$7,0),0)>0,1,0) copie a fórmula até F5 . Isso criará uma grade de 0s e 1s com 1, significando que a organização estava ativa no ano de referência e 0 significando que não era.

2. Então, tudo o que você precisa fazer é simplesmente obter as somas da linha 6 de D para F (basta colocar =SUM(D2:D5) em D6 e copiar a fórmula até F6 ).

Caso contrário, se você não precisa ou não quer a grade, você pode usar uma fórmula CSE / array (pode encontrar alguns links para saber mais sobre eles em outra das minhas respostas ).

No exemplo, em D7 , escrevi =SUM(IF(IFERROR(MATCH(CONCATENATE(D$1," --- ",$C2:$C5),$A$1:$A$7,0),0)>0,1,0)) , depois pressionei ctrl + desloque + digite e copiei a fórmula até% código%. (Apenas note que os passos 1. e 2. não são necessários para obter os resultados desejados neste caso.)

SEGUNDA VIA

Por fim, se você não conseguir obter uma lista de todas as organizações com facilidade, supondo que só possa compilar a lista dos anos em que está interessado em verificar, uma possível solução pode ser a seguinte:

1. Selecione todo o seu intervalo de dados (no meu exemplo é F7 ) e vá para "Dados" e selecione "Remover duplicados".

Nesteexemplo,apenasquatrolinhasdevempermanecereelasserãocombinaçõesexclusivasdeA1:A7( um exemplo de como Remover Duplicatas funcionam ) então agora podemos simplesmente "contar" o número de vezes que um ano aparece no intervalo de dados das linhas restantes. O número de linhas que permanecerão após a remoção de duplicatas não é realmente importante, pois assumiremos a coluna A inteira como intervalo de dados.

2. Então, depois de ter removido duplicatas, o que resta a fazer é usar a fórmula (por exemplo, em year --- organization name e, em seguida, copiando para D8 ) F8 (apenas note que o asterisco na fórmula é um curinga do Excel, pode encontrar alguns links para saber mais sobre eles em a mesma outra resposta que eu mencionei antes )

e é isso.