Cómo usar fórmulas con respuestas de formularios de Google en hojas

Cuando las personas envían su formulario de Google, se inserta una nueva fila en la hoja de Google que almacena las respuestas del formulario. Esta fila de la hoja de cálculo contiene una columna de marca de tiempo, la fecha real en que se envió el formulario y las otras columnas de la hoja contienen todas las respuestas del usuario, una por columna.

Puede ampliar la hoja de Formularios de Google para incluir también campos de fórmula y los valores de las celdas se calculan automáticamente cada vez que el Formulario de Google agrega una nueva fila a la hoja. Por ejemplo:

• Puede tener una fórmula de numeración automática que asigne una ID de incremento automático pero secuencial a cada respuesta del formulario. Puede ser útil cuando utiliza Formularios de Google para la facturación.
• Para los formularios de pedido de los clientes, se puede escribir una fórmula en Hojas de cálculo de Google para calcular el monto total según la selección del artículo, el país (las tasas impositivas son diferentes) y la cantidad seleccionada en el formulario.
• Para los formularios de reserva de hotel, una fórmula puede calcular automáticamente el alquiler de la habitación en función de la fecha de entrada y salida completada por el cliente en el Formulario de Google.
• Para las pruebas, un maestro puede calcular automáticamente el puntaje final del estudiante haciendo coincidir los valores ingresados ​​en el formulario con las respuestas reales y asignando puntajes.
• Si un usuario ha realizado múltiples envíos de formularios, una fórmula puede ayudarlo a determinar la cantidad total de entradas realizadas por un usuario tan pronto como envía un formulario.

Fórmulas de hojas de cálculo de Google para formularios de Google

En esta guía paso a paso, aprenderá cómo agregar fórmulas a Hojas de cálculo de Google que están asociadas con Formularios de Google. Los valores de celda correspondientes en las filas de respuesta se calcularán automáticamente cuando se envíe una nueva respuesta.

Para comprender mejor lo que estamos tratando de lograr, abra este Formulario de Google y envíe una respuesta. A continuación, abra esta hoja de Google y encontrará su respuesta en una nueva fila. Las columnas FK se autocompletan mediante fórmulas.

Todos los ejemplos a continuación usarán la función ArrayFormula de Google Sheets, aunque algunos de estos ejemplos también se pueden escribir usando la función FILTER .

Respuestas de formulario de numeración automática con una identificación única

Abra la hoja de Google que almacena las respuestas del formulario, vaya a la primera columna vacía y copie y pegue la siguiente fórmula en la fila n.º 1 de la columna vacía.

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

La función ROW() devuelve el número de fila de la fila de respuesta actual. Devuelve 1 para la primera fila en la columna de la factura y, por lo tanto, establecemos el título de la columna en la primera fila. Para las filas subsiguientes, si la primera columna de la fila (generalmente la marca de tiempo) no está vacía, la identificación de la factura se genera automáticamente.

Los ID serán como 00001 , 00002 y así sucesivamente. Solo necesita colocar la fórmula en la primera fila de la columna y se completa automáticamente todas las demás filas de la columna.

La función IFERROR devuelve el primer argumento si no es un valor de error; de lo contrario, devuelve el segundo argumento si está presente o un espacio en blanco si el segundo argumento está ausente. Entonces, en este caso, 1/0 es un error y, por lo tanto, siempre devuelve un valor en blanco.

Fórmula de cálculo de fecha para formularios de Google

Su formulario de Google tiene dos campos de fecha: la fecha de entrada y la fecha de salida. Las tarifas de los hoteles pueden variar cada temporada por lo que tienes una tabla aparte en la Hoja de Google que mantiene el alquiler de la habitación por mes.

La columna C en la hoja de Google contiene las respuestas para la fecha de entrada, mientras que la columna D almacena las fechas de salida.

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

La fórmula usa VLOOKUP para obtener las tarifas de la habitación para la fecha de viaje especificada en la respuesta del formulario y luego calcula el alquiler de la habitación multiplicando el alquiler de la habitación por la duración de la estadía.

La misma fórmula también se puede escribir con IFS en lugar 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 el monto del impuesto basado en el valor de la factura

En este enfoque, usaremos la función FILTER y eso podría conducir a una fórmula menos complicada que usar la función IF . La desventaja es que debe escribir el título de la columna en la fila n. ° 1 y pegar las fórmulas en la fila n. ° 2 (debe existir una respuesta de formulario para que la fórmula funcione).

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

Aquí aplicamos un impuesto del 35 % al valor de la factura y esta fórmula debe agregarse en la fila n.º 2 de la columna titulada "Cantidad del impuesto", como se muestra en la captura de pantalla.

Asignar puntajes de cuestionarios en formularios de Google

¿Qué ciudad es conocida como la gran manzana? Esta es una pregunta de respuesta corta en Formularios de Google para que los estudiantes puedan dar respuestas como Nueva York, Ciudad de Nueva York, NYC y seguirán siendo correctas. El profesor tiene que asignar 10 puntos a la respuesta correcta.

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

En esta fórmula, estamos haciendo uso de la función IFS que es como una instrucción IF THEN en programación. Estamos usando REGEXMATCH para hacer coincidir valores como New York, New York, newyork de una sola vez usando expresiones regulares.

La función IFS devuelve un NA si ninguna de las condiciones es verdadera, por lo que agregamos una verificación de TRUE al final que siempre se evaluará como true si ninguna de las condiciones anteriores coincide y devuelve 0 .

Extraiga el primer nombre del encuestado del formulario

Si tiene un campo de formulario que le pide al usuario que complete su nombre completo, puede usar la función Hojas de cálculo de Google para extraer el nombre del nombre completo y usar ese campo para enviar correos electrónicos 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+]+")) ) )

Hemos usado el método RegexExtract aquí para buscar la cadena antes del primer espacio en el campo de nombre. La función PROPER pondrá en mayúscula la primera letra del nombre en caso de que el usuario haya ingresado su nombre en minúsculas.

Encuentre envíos de formularios de Google duplicados

Si su formulario de Google es una recopilación de direcciones de correo electrónico, puede usar ese campo para detectar rápidamente las respuestas que ha enviado el mismo usuario varias veces.

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

Suponiendo que la Columna B almacena las direcciones de correo electrónico de los encuestados del formulario, podemos usar la función COUNTIF para marcar rápidamente las entradas duplicadas en nuestra hoja de cálculo de respuestas. También puede usar formato condicional en Hojas para resaltar filas que son posibles entradas duplicadas.

Respuestas de formularios de correo electrónico con valores de Autocompletar

Puede utilizar Document Studio para enviar automáticamente un correo electrónico a los encuestados del formulario. El correo electrónico se envía después de que Google Sheet complete automáticamente los valores del formulario. La respuesta del formulario original y los valores calculados también se pueden incluir en el documento PDF generado.