Подсчет парных комбинаций из множества опций в Excel

Question

У меня есть большой набор данных студентов и классов, которые они взяли. Каждый ученик прошел от 12 до 18 из примерно 80 доступных занятий. Используя Excel (2013), я хотел бы выяснить, сколько учеников взяли оба из них. Я представляю таблицу с 80 классами в виде строк и столбцов, а затем для каждого пересечения я вижу количество учеников, принявших эту комбинацию.

Данные поступают в виде файла Excel с одной строкой на каждого учащегося в классе:

Student  Class
Smith    E101
Jones    E101
Parker   E101
Brown    E102
Green    E102
Smith    E201
Jones    E202
Parker   E201
Brown    E202
Green    E203
...

Предполагаемый результат:

      E101  E102  E201  E202  E203  ...
E101        0     2     1     0
E102  0           0     1     1    
E201  2     0           0     0
E202  1     1     0           0
E203  0     1     0     0
...

(Очевидно, мне нужна только диагональ половины вышеприведенного, поскольку другая половина отражает его.)

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

        E101  E102  E201  E202  E203  ...
Smith   1           1           
Jones   1                 1         
Parker  1           1          
Brown         1           1     
Green         1                 1
...

Но затем я застрял на том, как, с минимальным ручным вмешательством, достичь желаемого результата.

Кто-нибудь может предложить способ достижения результата, который мне нужен в Excel? Я провел довольно обширный поиск, но ничего не нашел.

Или я должен искать другое программное обеспечение?

Answer 1

Это довольно просто сделать в 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() предназначена для того, чтобы гарантировать, что диагональные ячейки будут пустыми.