Как автоматически подсчитать новые ответы Формы в Таблице Google


Часто необходимо обработать значения, которые пользователь отправил через Форму. Например, проведение тестирования. Обычно, при появлении новых данных, формула растягивается мышью, еще говорят "протягивается".


Можно немного автоматизировать этот процесс для данных из Формы, используя формулу ARRAYFORMULA()

Для примера возьмем Форму Определятор настроения.
Условия определение настроения по количеству баллов:
  • 0 - Ужасное
  • 1 - Почти ужасное
  • 2 - Лучше, чем ничего
  • 3 - Среднее
  • 4 - Нормальное
  • 5 - Хорошее
  • 6 - Отличное
  • 7 - Превосходное!
Итоги будем считать на листе Таблицы "Результаты".
Имена респондентов, ячейка A2
=ARRAYFORMULA('Ответы на форму'!E2:E)
Результаты в баллах по первому вопросу, ячейка B2
=ARRAYFORMULA(IF('Ответы на форму'!B2:B="Да";1;0))
Результаты в баллах по второму вопросу, ячейка C2
= ARRAYFORMULA(IF('Ответы на форму'!C2:C="Просто замечательный";2;IF('Ответы на форму'!C2:C="Нормальный";1;0)))
Результаты в баллах по третьему вопросу, ячейка D2
=ARRAYFORMULA(IF('Ответы на форму'!D2:D = "Уже есть парочка!"; 4; IF('Ответы на форму'!D2:D = "Конечно!"; 3; IF('Ответы на форму'!D2:D = "Может быть, может быть"; 2;IF('Ответы на форму'!D2:D = "А стоит?"; 1;0)))))
Общий результат, ячейка E2
=ARRAYFORMULA(MMULT(B2:D;SIGN(TRANSPOSE(COLUMN(B2:D)))))
ARRAYFORMULA не будет считать сумму построчно, поэтому применяем такую формулу
Переводим значение результата в настроение, ячейка F2
=ARRAYFORMULA(IF(E2:E=7;"Превосходное!"; IF(E2:E=6;"Отличное";IF(E2:E=5;"Хорошее";IF(E2:E=4;"Нормальное";IF(E2:E=3;"Среднее";IF(E2:E=2;"Лучше, чем ничего";IF(E2:E=1;"Почти ужасное";"Ужасное"))))))))
Очень важно. Количество строк на листах должно совпадать, иначе не будет работать формула MMULT(). Решить эту проблему можно, ограничив диапазон в формулах. Например, с B2:B на B2:B1000.

Как видно, довольно хлопотно и неудобно. Задачу можно решить скриптом, что предпочтительнее. См. Тестирование с помощью Форм Google. Подсчет результатов через Google Apps Script

Популярные сообщения