☰

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

tag-icon • tag-icon microsoft-excel microsoft-excel-2013 microsoft-access statistics
Подсчет парных комбинаций из множества вариантов в Excel

У меня есть большой набор данных о студентах и ​​курсах, которые они посещали. Каждый студент посещал от 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? Я провел довольно обширный поиск, но ничего не нашел.

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

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

Связанный контент