Excel - Übereinstimmende Elemente zählen, eine Zeile mit einer Spalte vergleichen

Question

Ich versuche, die Ähnlichkeit einer Reihe von Datensätzen zu vergleichen ...

Ich habe eine Reihe von Spalten mit 20 Werten (nummeriert von 1-20) und entsprechende Zeilen mit 20 Werten (ebenfalls nummeriert von 1-20).

Die Spalten befinden sich oben und die Zeilen an der Seite eines Matrixrasters.

Ich möchte zählen, wie viele der Werte in den Spalten mit den Werten in den Zeilen übereinstimmen (und sich an der gleichen entsprechenden Position befinden).

Die Ausgabe auf einem 3 x 3-Raster (anstatt einem 20 x 20-Raster) würde wie folgt aussehen:

          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

Welche INDEX-/MATCH-/ARRAY-Funktion kann das für mich tun, ohne eine 20 Megabyte umfassende verschachtelte Berechnung, z. B. =IF(C7=F3,1+IF(... usw. …

Answer 1

Dies kann mit der folgenden Array-Formel erreicht werden.

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

Es vergleicht jede Zelle in den Bereichen und addiert 1 zur Gesamtsumme für jede identische Zelle. Die TRANSPOSEFunktion wird verwendet, um die Zeilen in Spalten umzuwandeln, damit sie verglichen werden können.

Beachten Sie, dass es sich um eine Matrixformel handelt und sie daher mit Ctrl+ Shift+ eingegeben werden muss Enter. Geben Sie die Formel in die obere linke Zelle ein ( D4in meinem Screenshot), drücken Sie Ctrl+ Shift+ Enterund kopieren Sie sie dann in die verbleibenden Zellen.