Excel - Contagem de itens correspondentes, comparando uma linha com uma coluna

Question

Estou tentando comparar a similaridade de uma série de conjuntos de dados ...

Eu tenho uma série de colunas de 20 valores (numeradas de 1 a 20) e linhas correspondentes de 20 valores (também numeradas de 1 a 20).

As colunas estão na parte superior e as linhas estão no lado de baixo de uma grade de matriz.

Gostaria de contar quantos dos valores nas colunas são iguais aos valores nas linhas (e na mesma posição correspondente).

A saída em uma grade 3 x 3 (em vez de 20 x 20) seria assim:

          Cols 
          A B C
          1 3 1
          2 2 3
          3 1 1
rows     ______
A 1 2 3 | 3 1 1
B 3 2 1 | 1 3 0
C 1 3 1 | 1 0 3

Qual função INDEX / MATCH / ARRAY pode fazer isso para mim, sem uma calcinação de 20 mega-ninhos, por ex. = SE (C7 = F3,1 + IF (... etc ...

    

Answer 1

A seguinte fórmula de matriz pode conseguir isso.

=SUM(IF(D$1:D$3=TRANSPOSE($A4:$C4), 1, 0))

Compara cada célula nos intervalos e adiciona 1 ao total de cada célula idêntica. A função TRANSPOSE é usada para converter as linhas em colunas para que possam ser comparadas.

Note que esta é uma fórmula de matriz, então deve ser digitada com Ctrl + Deslocar + Enter . Insira a fórmula na célula superior esquerda ( D4 na minha captura de tela), pressione Ctrl + Deslocamento + Enter e copie para as células restantes.