Как подсчитать уникальные значения из повторяющегося списка в приоритете с помощью ячейки - электронные таблицы Google

У меня есть таблица с четырьмя столбцами:

question_id user_id unique_question_ids # of unique_user_ids
X 11 X ? (=2)
X 12 Y ? (=3)
X 12
X 12
Y 13
Y 14
Y 15

Первые две колонки - это вопросы и их соответствующие пользователи и включают повторы обоих.

Цель заключается в следующем: я хочу подсчитать количество уникальных пользователей для каждого уникального вопроса.

Я начал с первого поиска уникальных_кустей, которые я нашел, используя функцию UNIQUE. В этом перечислены, какие вопросы уникальны в столбце unique_question_ids (т.е. X, Y)

Теперь я хочу подсчитать количество уникальных пользователей, которые имеют каждый уникальный вопрос? Другая проблема заключается в том, что я не знаю, где начинаются X и Y, однако они все еще отсортированы по порядку (реальная таблица очень велика).

Как мне это сделать? Я думаю, что могу использовать COUNTIFS, но это не учитывает уникальные значения. Я также думаю об использовании функции, которая вернет диапазон, где X или Y находится в столбце question_id, а затем подсчитывается в следующем столбце (т.е. user_id) для уникальных значений. Но я не могу найти функцию, которая возвращает диапазон ячеек значения в столбце. Я также делаю это в Google Spreadsheets.

Любые мысли или идеи будут оценены, спасибо

1 ответ

Предполагая, что ваши данные начинаются в ячейке A1, вы можете использовать эту формулу в ячейке C1:

=ARRAYFORMULA(QUERY(UNIQUE(A2:B8),"SELECT Col1, COUNT(Col2) GROUP BY Col1 LABEL Col1 'unique_question', COUNT(Col2) 'unique_users'",-1))

Это в основном SQL-запрос с использованием уникальных значений из unique(A2:B8) и подсчета значений из второго столбца на основе первых значений столбца.

Пример Google Spreadsheet

Некоторое объяснение:

Уникальные данные таблицы после прохождения через UNIQUE() следующим образом:

question_id user_id
X 11
X 12
Y 13
Y 14
Y 15

Язык SQL (специфичный для GoogleSpreadsheet) выглядит следующим образом:

SELECT -- From the data, Col1, -- select column 1 (unique question_id) COUNT(Col2) -- select the count of column 2 (unique user_id)
GROUP BY Col1 -- group by the first column *
LABEL Col1 'unique_question', -- label the first column as 'unique_question' COUNT(Col2) 'unique_users' -- label the second column as 'unique_users'
  • Когда вы применяете агрегатную функцию к столбцу, например, COUNT() является агрегированной функцией, вы должны использовать GROUP BY для других столбцов, чтобы решить, что с ними должно случиться.

Например, если вы используете это по приведенным выше данным:

SELECT COUNT(Col2)

В итоге вы получите 5 (это одна строка), потому что она подсчитывает все строки в данных таблицы. Если вы пытаетесь:

SELECT Col1, COUNT(Col2)

Вы получите 5 строк для Col1 и 1 строки для результата функции, что недопустимо. Итак, вам нужна GROUP BY чтобы сказать, что все подобные значения в Col1 должны занимать одну строку, так что у вас есть одна строка для X и одна для Y а затем счетчик будет адаптироваться к этой группировке, посчитав все X отдельно от Y,

licensed under cc by-sa 3.0 with attribution.