Как протянуть формулу при ответах Формы в Таблицах?

После того как новые ответы Формы попали в Таблицу, нужно их обсчитать. Основная трудность тут в том, что формулы автоматически не протягиваются. Постоянно приходится добавлять формулы к новым ответам. Решением может быть ARRAYFORMULA() - главный помощник расчетов в Таблицах Гугл.

Необходимо обработать значения, которые пользователь отправил через Форму, так чтобы не обновлять формулы при новых ответах.

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

Описание Формы

Рассмотрим на примере проведения тестирования для определения настроения. Эта Форма имеет всего четыре поля:

1. "Улыбались ли вы сегодня утром?"
2. "Сегодня отличный день?"
3. "Готовы к добрым поступкам?"
4. "Как вас зовут?"

Простой пример

Необходимо в Таблице против каждого ответа собрать строку вида: [Как вас зовут?]: [Улыбались ли вы сегодня утром?]; [Сегодня отличный день?]; [Готовы к добрым поступкам?], например, Саша: Да; Просто замечательный; А стоит?. Другими словами, получить ответ одной строкой.

Добавим формулу в крайнюю правую колонку листа Таблицы в верхнюю ячейку:

  
    =E1 & ": " & B1 & "; " & C1 & "; " & D1
  

Немного автоматизируем этот процесс для данных из Формы, используя формулу ARRAYFORMULA(). Обернем, все что есть в ARRAYFORMULA():

  
    =ARRAYFORMULA(E1 & ": " & B1 & "; " & C1 & "; " & D1)
  

Добавим указание диапазонам, на какой массив они должны указывать:

  
    =ARRAYFORMULA(E:E & ": " & B:B & "; " & C:C & "; " & D:D)
  

Будьте внимательны, эта формула вставляется в первую строку листа!

Все хорошо, но заголовок не впечатляет:

Далее вам нужно выбрать, либо вы смещаете формула на строку ниже, тогда поместите ее во вторую строку и укажите следующие диапазоны:

  
    =ARRAYFORMULA(E2:E & ": " & B2:B & "; " & C2:C & "; " & D2:D)
  

либо укажите заголовок непосредственно в формуле, используя конкатенацию диапазонов:

  
    ={
      "Ответ одной строкой";
      ARRAYFORMULA(E2:E & ": " & B2:B & "; " & C2:C & "; " & D2:D)
    }
  

в данном случае формула дожна остваться в первой строке, но все равно иметь указания на диапазоны на строку ниже.

Пример с расчетами итогов

Для примера возьмем опять Форму "Определятор настроения".

Задача состоит в том, чтобы перевести ответы пользователя в баллы, сложить эти баллы, и из полученной суммы определить настроение.

Условия определениz настроения по количеству баллов:

• 0 - Ужасное
• 1 - Почти ужасное
• 2 - Лучше, чем ничего
• 3 - Среднее
• 4 - Нормальное
• 5 - Хорошее
• 6 - Отличное
• 7 - Превосходное!

Итоги будем считать на листе Таблицы "Результаты".

Имена респондентов, ячейка A2

  
    =ARRAYFORMULA('Ответы на форму'!E2:E)
  

Результаты в баллах по первому вопросу, ячейка B2. Просто переводим "Да" в 1, остальное - 0:

  
    =ARRAYFORMULA(IF('Ответы на форму'!B2:B="Да";1;0))
  

Результаты в баллах по второму вопросу, ячейка C2. 2 балла за замечательный день, 1 - нормальный, остальное - 0:

  
    =ARRAYFORMULA(
      IF('Ответы на форму'!C2:C="Просто замечательный";
        2;
        IF('Ответы на форму'!C2:C="Нормальный";1;0)
      )
    )
  

Результаты в баллах по третьему вопросу, ячейка D2, соответственно добавляет 4, 3, 2,1 или 0 баллов

  
    =ARRAYFORMULA(
      IF('Ответы на форму'!D2:D = "Уже есть парочка!";4;
        IF('Ответы на форму'!D2:D = "Конечно!";3;
          IF('Ответы на форму'!D2:D = "Может быть, может быть";2;
            IF('Ответы на форму'!D2:D = "А стоит?"; 1;0)
    ))))
  

Подсчет общего результата может вас смутить, если вы недавно с Таблицами, но, поверьте, это довольно "простая" формула:

  
    =ARRAYFORMULA(
      MMULT(
        IF(ISNUMBER(B2:D);B2:D;0);
        TRANSPOSE(COLUMN(B2:D)^0))
    ))
  

Не беспокойтесь, вот действительно простая формула:

  
    =ARRAYFORMULA(B2:B + C2:C + D2:D)
  

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

Формула определения настроения:

  
    =ARRAYFORMULA(CHOOSE(
      E2:E+1;
      "Ужасное";
      "Почти ужасное";
      "Лучше, чем ничего";
      "Среднее";
      "Нормальное";
      "Хорошее";
      "Отличное";
      "Превосходное!"
    ))
  

Итоги

Обработка значений, передаваемых Формой, может быть упрощена, если использовать формулу ARRAYFORMULA()

Эта формула позволяет нетолько "протиягивать формулы вниз", но и вести рачеты по строкам.

Надеюсь, что это было понятно и полезно. Если у вас остались вопросы, то напишите мне, отправив форму обратной связи.

Ссылка на Таблицу "Определятор настроения".

Ссылка на Форму "Определятор настроения".