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

Какая функция ИНДЕКС / ПОИСКПОЗ / МАССИВ может сделать это для меня, без 20-мега вложенных вычислений, например =ЕСЛИ(C7=F3,1+ЕСЛИ(... и т.д....

Answer 1

Это можно сделать с помощью следующей формулы массива.

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

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

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