Перекрестная таблица всех возможных комбинаций столбцов в Excel или SPSS

Question

Для исследования у меня есть следующий набор данных в Excel (упрощенный для целей иллюстрации):

Article   | Boolean1    Boolean2    Boolean3    Boolean4|
--------------------------------------------------------|
1         | Yes         No          Yes         No      |
2         | No          Yes         No          Yes     |
3         | Yes         No          No          Yes     |
4         | Yes         Yes         No          Yes     |
5         | Yes         No          No          No      |

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

             | Boolean2 No   Boolean2 Yes | Boolean3 No   Boolean3 Yes |
-----------------------------------------------------------------------|
Boolean1 No  |          0               1 |           1              0 |
Boolean1 Yes |          3               1 |           3              1 |
-----------------------------------------------------------------------|
Boolean4 No  |          2               0 |           1              3 |
Boolean4 Yes |          1               2 |           1              0 |

Однако мне нужны все комбинации в такой таблице, поэтому в данном случае boolean1-boolean2, boolean1-boolean3, boolean1-boolean4, boolean2-boolean3, boolean2-boolean4 и boolean3-boolean4 (1-1, 2-2 и т.д. Я могу отфильтровать себя).

Теперь это можно сделать с помощью «Кросс-таблиц» на всех комбинациях в SPSS или создания сводной таблицы в Excel для каждой возможной комбинации; однако, поскольку у меня 144 статьи и 29 переменных (все логические), копирование на отдельную таблицу заняло бы много времени. Поскольку сводные таблицы «складывают» переменные, размещение всех переменных в тех же строках / столбцах в сводной таблице также не имеет смысла. Ответ, который был ближе всего к тому, что я искал, был этот ответ ; однако, это не сработало для меня в Excel 2016.

Есть ли способ создать гигантскую таблицу, содержащую все возможные кросс-таблицы, более эффективно, чем копирование вручную на новый или существующий рабочий лист в Excel или, возможно, с помощью другого инструмента, такого как SPSS?

Answer 1

Я бы сделал это с COUNTIFS .

Сначала создайте в таблице заголовки строк и столбцов, что довольно просто, особенно если переменные пронумерованы, как в вашем примере (Boolean1, Boolean2, ...), потому что вы просто используете автозаполнение. В противном случае вам нужно выполнить некоторые манипуляции / извлечение строк, чтобы получить хорошую таблицу:

             |   Boolean2        Boolean2 |    Boolean3       Boolean3 |
             |         No             Yes |         No             Yes |
-----------------------------------------------------------------------|
Boolean1 No  |          X                 |                            |
Boolean1 Yes |                            |                            |
-----------------------------------------------------------------------|
Boolean4 No  |                            |                            |
Boolean4 Yes |                            |                            |

Вы также должны определить свой набор данных как таблицу Excel (Ctrl-L) со строками заголовка (я использовал Table1 в качестве имени по умолчанию).

И в X (при условии, что это ячейка C3) вы кладете формулу:
=COUNTIFS(INDIRECT("Table1["& C$1 &"]");C$2;(INDIRECT("Table1["& $A3 &"]"));$B3)
Который вы, конечно, можете автозаполнить по всей области.

Table1[Boolean2] ссылается на этот столбец в вашем наборе данных (INDIRECT просто избегает необходимости жестко кодировать ссылку на столбец, и вы можете использовать значение в ячейке C1). Таким образом, общая формула подсчитывает случаи, когда Table1[Boolean2] равен "Нет", а Table1[Boolean1] также равен "Нет" (используя два условия в COUNTIFS).

Выходные данные будут полностью симметричными, поэтому в зависимости от того, как вы хотите рассчитать индекс Жакара, вы должны брать только выход ниже диагонали (отбрасывая дубликаты (N, M) и (M, N) и тривиальное (N, N) те). Если вы хотите получить чистый вывод сразу, вы, конечно, можете добавить дополнительный IF чтобы вычислить только число, когда индекс переменной в строке больше, чем индекс переменной в столбце. Возможно, вы также можете сделать это в той же формуле, но для наглядности я бы просто добавил дополнительную строку и столбец с порядковым номером переменной (от 1 до 29) и протестировал их.