В условиях дистанционного обучения преподаватели столкнулись с проблемой дистанционного контроля обученности учащихся. "Дистант" закончился, но сделанные наработки продолжают приносить пользу и дальше.

Одним из простейших способов организовать тестирование через Интернет является использование сервиса Google Forms. Чтобы превратить простой список вопросов в тест с проверкой ответов и баллами, необходимо войти в настройки Формы и включить режим «Quiz».

Ответы можно просматривать в интерфейсе самой Формы во вкладке «Responses». Кроме того, ответы можно выгрузить в таблицу Google Sheet. Таблица выглядит следующим образом:

Интерес представляет столбец Score. В нем в виде дроби представлена информация о набранных баллах и максимальном количестве баллов за тест. Сложность состоит в том, что физически в ячейке записано только число набранных баллов, а строка "/ 2" является частью Custom Number Format. Чрезвычайно удобная функция IMPORTRANGE(), позволяющая вставить заданный диапазон на другой лист или даже в другую таблицу, успешно копирует этот формат для каждой ячейки. А вот функция QUERY() - нет. Информация о максимальном количестве баллов за тест в некоторых случаях теряется.

Итак, пусть у нас есть три Формы: Test 1, Test 2 и Test 3. Первым вопросом в тестах идет "Full Name". По этому полю мы будем идентифицировать учащихся. Для трех тестов есть три таблицы с ответами: Test 1 (Responses), Test 2 (Responses), Test 3 (Responses). Первые три столбца в каждой таблице одинаковые: Timestamp, Score, Full Name. Далее идут ответы на вопросы теста, которые нам не понадобятся.

Создадим новый документ Googe Sheet. Назовем его, например, Grade Book. Нам понадобится по одному листу на каждый тест (T1, T2, T3), лист Source, чтобы собрать всё вместе, и лист Grade Book для сводной таблицы.

Листы T1, T2, T3...

Импортируем на листы T1, T2, T3 первые три столбца из таблиц ответов. Для этого в ячейку A1 на каждом листе вставим формулу:

=IMPORTRANGE(
"https://docs.google.com/spreadsheets/d/1dee7GYwj1NgZfDNZJgLMVOcWRmPnvSAvg3KJ0ahqkmI",
"Form Responses 1!A2:C"
)

Где "https://..." - это URL таблицы "Test X (Responses)", который можно скопировать из адресной строки браузера, "Form Responses 1" - название Листа с результатами теста, "A2:C" - диапазон ячеек, который мы хотим скопировать (заголовок игнорируем).

Теперь нужно разделить значения из колонки "Score" на два значения: количество набранных баллов и максимальное количество баллов в тесте. Для этого в ячейку D1 поместим формулу:

=ArrayFormula(split(B1:B, "/"))

Теперь в колонке D хранится количество баллов за тест, а в колонке E - максимальное количество баллов за тест.

Лист Source

Лист Source будет чем-то вроде таблицы базы данных, в которую мы соберем ответы на все тесты добавив ещё один столбец - идентификатор теста. Затем уже можно будет пересчитать баллы в оценки и немого причесать поле Full Name.

В ячейку A1 на Листе "Source" вставим формулу:

=QUERY({
QUERY('T1'!A1:E, "SELECT 'T1', A, B, C, D, E WHERE A IS NOT NULL LABEL 'T1' ''");
QUERY('T2'!A1:E, "SELECT 'T2', A, B, C, D, E WHERE A IS NOT NULL LABEL 'T2' ''");
QUERY('T3'!A1:E, "SELECT 'T3', A, B, C, D, E WHERE A IS NOT NULL LABEL 'T3' ''")
}, "SELECT * ")

Где T1, T2, T3 - названия Листов, куда мы импортировали данные из таблиц ответов, SELECT 'T1'... - это необходимо, чтобы добавить в каждую строку идентификатор теста.

В ячейку G1 на листе "Source" добавим формулу для пересчета баллов в оценки по пятибалльной шкале:

=ArrayFormula(E1:E/F1:F*5)

А в ячейку H1 добавим формулу, чтобы вырезать из Full Name только первое слово. По опыту студенты пишут свою фамилию каждый раз одинаково, а дальше пишут то имя, то имя и отчество, то инициалы. Чтобы связать несколько ответов одного студента вместе в моём случае оказалось достаточно вырезать фамилию.

=ArrayFormula(INDEX(SPLIT(D1:D, " "), 0, 1))

Лист "Source" будет выглядеть следующим образом:

Лист Grade Book

В ячейку A1 на листе "Grade Book" вставим следующую формулу:

=QUERY(
Source!A1:H,
"SELECT H, MAX(G) WHERE C IS NOT NULL GROUP BY H PIVOT A"
)

Где Source - лист, с которого брать данные, MAX(G) - максимальная оценка из всех попыток каждого студента сдать тест, PIVOT(A) задает столбец для колонок сводной таблицы, в нашем случае - идентификатор теста.

Вот и готова таблица с оценками:

Данные будут обновляться автоматически после каждого нового ответа.

PS: В русской версии Google Docs необходимо использовать точку с запятой в качестве разделителя аргументов в функциях, так как запятая является десятичным разделителем.