Como usar fórmulas com respostas de formulários do Google no Planilhas

Quando as pessoas enviam seu Formulário Google, uma nova linha é inserida na Planilha Google que armazena as respostas do formulário. Essa linha da planilha contém uma coluna Timestamp, a data real em que o formulário foi enviado e as outras colunas da planilha contêm todas as respostas do usuário, uma por coluna.

Você pode estender a planilha do Google Forms para incluir também campos de fórmula e os valores das células são calculados automaticamente sempre que uma nova linha é adicionada à planilha pelo Google Form. Por exemplo:

• Você pode ter uma fórmula de numeração automática que atribui um ID sequencial, mas de incremento automático, a cada resposta de formulário. Pode ser útil quando você estiver usando o Google Forms para faturamento.
• Para formulários de pedidos de clientes, uma fórmula pode ser escrita no Planilhas Google para calcular o valor total com base na seleção do item, no país (as taxas de imposto são diferentes) e na quantidade selecionada no formulário.
• Para formulários de reserva de hotel, uma fórmula pode calcular automaticamente o aluguel do quarto com base na data de check-in e check-out preenchida pelo cliente no Formulário Google.
• Para questionários, um professor pode calcular automaticamente a pontuação final do aluno comparando os valores inseridos no formulário com as respostas reais e atribuindo pontuações.
• Se um usuário tiver feito vários envios de formulário, uma fórmula pode ajudá-lo a determinar o número total de entradas feitas por um usuário assim que ele enviar um formulário.

Fórmulas do Planilhas Google para Formulários Google

Neste guia passo a passo, você aprenderá a adicionar fórmulas ao Planilhas Google associadas ao Formulários Google. Os valores das células correspondentes nas linhas de resposta serão calculados automaticamente quando uma nova resposta for enviada.

Para entender melhor o que estamos tentando alcançar, abra este Formulário do Google e envie uma resposta. Em seguida, abra esta Planilha Google e você encontrará sua resposta em uma nova linha. As colunas FK são preenchidas automaticamente usando fórmulas.

Todos os exemplos abaixo usarão a função ArrayFormula do Planilhas Google, embora alguns desses exemplos também possam ser escritos usando a função FILTER .

Respostas de formulário de numeração automática com um ID exclusivo

Abra a Planilha Google que está armazenando as respostas do formulário, vá para a primeira coluna vazia e copie e cole a seguinte fórmula na linha 1 da coluna vazia.

 =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) ) )

A função ROW() retorna o número da linha da linha de resposta atual. Ele retorna 1 para a primeira linha na Coluna da Fatura e, assim, definimos o título da coluna na primeira linha. Para as linhas subsequentes, se a primeira coluna da linha (geralmente Timestamp) não estiver vazia, o ID da fatura será gerado automaticamente.

Os IDs serão como 00001 , 00002 e assim por diante. Você só precisa colocar a fórmula na primeira linha da coluna e preencher automaticamente todas as outras linhas da coluna.

A função IFERROR retorna o primeiro argumento se não for um valor de erro, caso contrário, retorna o segundo argumento se estiver presente ou um espaço em branco se o segundo argumento estiver ausente. Portanto, neste caso, 1/0 é um erro e, portanto, sempre retorna um valor em branco.

Fórmula de cálculo de data para o Google Forms

Seu Formulário Google tem dois campos de data - a data de check-in e a data de check-out. As tarifas do hotel podem variar a cada temporada, então você tem uma tabela separada na Planilha Google que mantém o aluguel do quarto por mês.

A coluna C na Planilha Google contém as respostas para a data de check-in enquanto a coluna D armazena as datas de check-out.

 =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), "" ) ) )

As fórmulas usam VLOOKUP para obter as tarifas do quarto para a data da viagem especificada na resposta do formulário e, em seguida, calcula o aluguel do quarto multiplicando o aluguel do quarto pela duração da estadia.

A mesma fórmula também pode ser escrita com IFS em vez de 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 ) ) )

Calcular o valor do imposto com base no valor da fatura

Nesta abordagem, usaremos a função FILTER e isso pode levar a uma fórmula menos complicada do que usar a função IF . A desvantagem é que você precisa escrever o título da coluna na linha 1 e colar as fórmulas na linha 2 (assim, uma resposta de formulário deve existir para que a fórmula funcione).

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

Aqui aplicamos 35% de imposto ao valor da fatura e esta fórmula deve ser adicionada na linha nº 2 da coluna intitulada “Valor do imposto”, conforme mostrado na captura de tela.

Atribuir pontuações de questionários no Google Forms

Qual cidade é conhecida como a grande maçã? Esta é uma pergunta de resposta curta no Formulários Google para que os alunos possam dar respostas como Nova York, Nova York, NYC e ainda assim estarão corretas. O professor deve atribuir 10 pontos à resposta correta.

 =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 ) ) )

Nesta fórmula, estamos fazendo uso da função IFS que gosta de uma instrução IF THEN na programação. Estamos usando REGEXMATCH para corresponder valores como New York, New York, newyork de uma só vez usando expressões regulares.

A função IFS retorna um NA se nenhuma das condições for verdadeira, então adicionamos uma verificação TRUE no final que sempre será avaliada como true se nenhuma das condições anteriores corresponder e retornar 0 .

Extraia o nome do respondente do formulário

Se você tiver um campo de formulário que solicite ao usuário o nome completo, você pode usar a função Planilhas Google para extrair o primeiro nome do nome completo e usar esse campo para enviar e-mails personalizados.

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

Usamos o método RegexExtract aqui para buscar a string antes do primeiro espaço no campo de nome. A função PROPER irá capitalizar a primeira letra do nome caso o usuário tenha digitado seu nome em minúsculas.

Localizar envios duplicados de formulários do Google

Se o seu Formulário Google for de endereços de e-mail de coleção, você poderá usar esse campo para detectar rapidamente as respostas que foram enviadas pelo mesmo usuário várias vezes.

 =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", "") ) )

Supondo que a Coluna B esteja armazenando os endereços de e-mail dos respondentes do formulário, podemos usar a função COUNTIF para marcar rapidamente entradas duplicadas em nossa planilha de respostas. Você também pode usar a formatação condicional no Planilhas para destacar as linhas que são possíveis entradas duplicadas.

Respostas de formulário de e-mail com valores de preenchimento automático

Você pode usar o Document Studio para enviar automaticamente um e-mail aos respondentes do formulário. O e-mail é enviado depois que os valores do formulário são preenchidos automaticamente pela Planilha Google. A resposta do formulário original e os valores calculados também podem ser incluídos no documento PDF gerado.