Excel - подсчет соответствующих элементов, сравнение строки со столбцом

Question

Я пытаюсь сравнить сходство ряда наборов данных ...

У меня есть серия столбцов из 20 значений (пронумерованных 1-20) и соответствующих строк из 20 значений (также пронумерованных 1-20).

Столбцы расположены вдоль верхней части, а строки - вниз по сторонам матричной сетки.

Я хотел бы посчитать, сколько значений в столбцах совпадают со значениями в строках (и в той же соответствующей позиции).

Вывод на 3 x 3 (а не на сетку 20 x 20) будет выглядеть так:

          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

Какая функция INDEX / MATCH / ARRAY может сделать это для меня, без 20-мегапиксельного вычисления, например = IF(C7 = F3,1+IF(... и т.д.)

Answer 1

Следующая формула массива может сделать это.

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

Он сравнивает каждую ячейку в диапазонах и добавляет 1 к общему количеству для каждой идентичной ячейки. Функция TRANSPOSE используется для преобразования строк в столбцы, чтобы их можно было сравнивать.

Обратите внимание, что это формула массива, поэтому ее нужно вводить с помощью Ctrl+Shift+Enter. Введите формулу в верхнюю левую ячейку (D4 на моем скриншоте), нажмите Ctrl+Shift+Enter, а затем скопируйте в оставшиеся ячейки.