Как использовать формулы с ответами формы Google в таблицах

Когда люди отправляют вашу форму Google, в таблицу Google вставляется новая строка, в которой хранятся ответы формы. Эта строка электронной таблицы содержит столбец Timestamp, фактическую дату отправки формы, а другие столбцы таблицы содержат все ответы пользователя, по одному на столбец.

Вы можете расширить лист Google Forms, включив в него поля формул, и значения ячеек автоматически рассчитываются всякий раз, когда новая строка добавляется на лист с помощью формы Google. Например:

• У вас может быть формула автоматической нумерации, которая присваивает автоматически увеличивающийся, но последовательный идентификатор каждому ответу формы. Это может быть полезно, когда вы используете Google Forms для выставления счетов.
• Для форм заказов клиентов в Google Sheets можно написать формулу для расчета общей суммы на основе выбора товара, страны (налоговые ставки разные) и количества, выбранного в форме.
• Для форм бронирования отелей формула может автоматически рассчитать стоимость аренды номера на основе даты заезда и выезда, введенной клиентом в форме Google.
• Для викторин учитель может автоматически рассчитать окончательный балл ученика, сопоставив значения, введенные в форму, с фактическими ответами и присвоив баллы.
• Если пользователи отправили несколько форм, формула может помочь вам определить общее количество записей, сделанных пользователем, как только он отправит форму.

Формулы Google Sheets для Google Forms

В этом пошаговом руководстве вы узнаете, как добавлять формулы в Google Sheets, связанные с Google Forms. Соответствующие значения ячеек в строках ответов будут автоматически рассчитаны при отправке нового ответа.

Чтобы лучше понять, чего мы пытаемся достичь, откройте эту форму Google и отправьте ответ. Затем откройте этот Google Sheet, и вы найдете свой ответ в новой строке. Столбцы FK заполняются автоматически по формулам.

Во всех приведенных ниже примерах будет использоваться функция ArrayFormula Google Sheets, хотя некоторые из этих примеров также могут быть написаны с использованием функции FILTER .

Автоматическая нумерация ответов формы с уникальным идентификатором

Откройте Google Sheet, в котором хранятся ответы формы, перейдите к первому пустому столбцу и скопируйте и вставьте следующую формулу в строку № 1 пустого столбца.

 =ArrayFormula( IFS( ROW(A:A)=1, "Invoice ID", LEN(A:A)=0, IFERROR(1/0), LEN(A:A)>0, LEFT(CONCAT(REPT("0",5), ROW(A:A) -1),6) ) )

Функция ROW() возвращает номер строки текущей строки ответа. Он возвращает 1 для первой строки в столбце счета, и поэтому мы устанавливаем заголовок столбца в первой строке. Для последующих строк, если первый столбец строки (обычно Timestamp) не пуст, идентификатор счета создается автоматически.

Идентификаторы будут вроде 00001 , 00002 и так далее. Вам нужно только поместить формулу в первую строку столбца, и она автоматически заполнит все остальные строки в столбце.

Функция IFERROR возвращает первый аргумент, если он не является значением ошибки, в противном случае возвращает второй аргумент, если он присутствует, или пробел, если второй аргумент отсутствует. Таким образом, в этом случае 1/0 является ошибкой и поэтому всегда возвращает пустое значение.

Формула расчета даты для Google Forms

В вашей форме Google есть два поля даты: дата заезда и дата выезда. Цены на отели могут меняться в зависимости от сезона, поэтому у вас есть отдельная таблица в Google Sheet, в которой указана арендная плата за номер в месяц.

Столбец C в Google Sheet содержит ответы для даты регистрации, а столбец D хранит даты выезда.

 =ArrayFormula( IF(ROW(A:A) = 1, "Room Rent", IF(NOT(ISBLANK(A:A)), (D:D - C:C) * VLOOKUP(MONTH(D:D), 'Room Rates'!$B$2:$C$13,2, TRUE), "" ) ) )

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

Эту же формулу можно записать с помощью IFS вместо VLOOKUP .

 =ArrayFormula( IF(ROW(A:A) = 1, "Room Rent", IFS(ISBLANK(C:C), "", MONTH(C:C) < 2, 299, MONTH(C:C) < 5, 499, MONTH(C:C) < 9, 699, TRUE, 199 ) ) )

Рассчитать сумму налога на основе суммы счета

В этом подходе мы будем использовать функцию FILTER , и это может привести к менее сложной формуле, чем использование функции IF . Недостатком является то, что вам нужно написать заголовок столбца в строке № 1 и вставить формулы в строку № 2 (поэтому для работы формулы должен существовать один ответ формы).

 =ArrayFormula(FILTER(E2:E, E2:E<>"")*1.35)

Здесь мы применяем 35% налога к стоимости счета, и эту формулу следует добавить в строку № 2 столбца под названием «Сумма налога», как показано на снимке экрана.

Назначьте баллы за викторину в Google Forms

Какой город называют большим яблоком? Это вопрос с кратким ответом в Google Forms, поэтому учащиеся могут давать ответы типа Нью-Йорк, Нью-Йорк, Нью-Йорк, и они все равно будут правильными. Учитель должен поставить 10 баллов за правильный ответ.

 =ArrayFormula( IF(ROW(A:A) = 1, "Quiz Score", IFS( ISBLANK(A:A), "", REGEXMATCH(LOWER({B:B}), "new\s?york"), 10, {B:B} = "NYC", 10, TRUE, 0 ) ) )

В этой формуле мы используем функцию IFS , аналогичную оператору IF THEN в программировании. Мы используем REGEXMATCH для сопоставления таких значений, как New York, New York, newyork за один раз с использованием регулярных выражений.

Функция IFS возвращает NA , если ни одно из условий не выполняется, поэтому мы добавляем в конце проверку TRUE , которая всегда будет оцениваться как true , если ни одно из предыдущих условий не соответствует, и возвращает 0 .

Извлечение имени респондента из формы

Если у вас есть поле формы, в котором пользователю предлагается ввести полное имя, вы можете использовать функцию Google Sheets, чтобы извлечь имя из полного имени и использовать это поле для отправки персонализированных электронных писем.

 =ArrayFormula( IFS( ROW(A:A)=1, "First Name", LEN(A:A)=0, IFERROR(1/0), LEN(A:A)>0, PROPER(REGEXEXTRACT(B:B, "^[^\s+]+")) ) )

Здесь мы использовали метод RegexExtract , чтобы получить строку перед первым пробелом в поле имени. Функция PROPER сделает первую букву имени заглавной, если пользователь ввел свое имя строчными буквами.

Найдите дубликаты отправленных форм Google

Если ваша форма Google представляет собой набор адресов электронной почты, вы можете использовать это поле для быстрого обнаружения ответов, отправленных одним и тем же пользователем несколько раз.

 =ArrayFormula( IFS( ROW(A:A)=1, "Is Duplicate Entry?", LEN(A:A)=0, IFERROR(1/0), LEN(A:A)>0, IF(COUNTIF(B:B, B:B) > 1, "YES", "") ) )

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

Ответы формы электронной почты со значениями автозаполнения

Вы можете использовать Document Studio для автоматической отправки электронной почты респондентам формы. Электронное письмо отправляется после того, как формульные значения автоматически заполняются таблицей Google. Исходный ответ формы и рассчитанные значения также могут быть включены в сгенерированный документ PDF.