Это довольно просто сделать в Excel с помощью формулы, которая работает с вашей сводной таблицей.
С двумя таблицами, настроенными так
введите следующую формулу J2и ctrl-enter / copy-paste / fill-down & right / auto-fill в остальные ячейки таблицы:
= IF( J$1=$I2, "", COUNTIFS( INDEX($A$1:$F$6,ROW($A$1:$F$6),MATCH(J$1,$A$1:$F$1,0)):INDEX($A$1:$F$6,1+ROWS($A$1:$F$6)-ROW($A$1:$F$6),MATCH(J$1,$A$1:$F$1,0)), 1, INDEX($A$1:$F$6,ROW($A$1:$F$6),MATCH($I2,$A$1:$F$1,0)):INDEX($A$1:$F$6,1+ROWS($A$1:$F$6)-ROW($A$1:$F$6),MATCH($I2,$A$1:$F$1,0)), 1 ) )
Объяснение:
Первым аргументом COUNTIFS()функции является динамически генерируемый столбец сводной таблицы, соответствующий заголовку столбца выходной таблицы. Немного легче понять, если мы посмотрим на промежуточные оцениваемые шаги (для ячейки L2):
INDEX($A$1:$F$6,ROW($A$1:$F$6),MATCH(L$1,$A$1:$F$1,0)):INDEX($A$1:$F$6,1+ROWS($A$1:$F$6)-ROW($A$1:$F$6),MATCH(L$1,$A$1:$F$1,0))
→ INDEX($A$1:$F$6,1,MATCH("E201",$A$1:$F$1,0)):INDEX($A$1:$F$6,6,MATCH("E201",$A$1:$F$1,0))
→ INDEX($A$1:$F$6,1,4):INDEX($A$1:$F$6,6,4)
→$D$1:$D$6
(Обратите внимание, что вторыми аргументами каждого из них INDEX()являются только полностью динамические начальная и конечная строки, соответственно, сводной таблицы.)
Аналогично для третьего аргумента COUNTIFS()функции, но на этот раз динамически генерируемый столбец сводной таблицы соответствует заголовку строки выходной таблицы. Для клетки L2это оценивается как $B$1:$B$6.
Таким образом, COUNTIFS()функция L2становится
COUNTIFS($D$1:$D$6,1,$B$1:$B$6,1) который является стандартным способом подсчета количества строк (студентов), где оба столбца содержат 1(т.е. студент был зачислен в оба класса).
Инкапсулирующая IF()функция предназначена для того, чтобы гарантировать, что диагональные ячейки не заполнены.