Come utilizzare le formule con le risposte ai moduli di Google nei fogli

Quando le persone inviano il tuo modulo Google, viene inserita una nuova riga nel foglio Google che memorizza le risposte del modulo. Questa riga del foglio di calcolo contiene una colonna Timestamp, la data effettiva di invio del modulo e le altre colonne del foglio contengono tutte le risposte dell'utente, una per colonna.

Puoi estendere il foglio di Google Forms per includere anche i campi formula e i valori delle celle vengono calcolati automaticamente ogni volta che una nuova riga viene aggiunta al foglio da Google Form. Per esempio:

• Puoi avere una formula di numerazione automatica che assegna un ID con incremento automatico ma sequenziale a ogni risposta del modulo. Può essere utile quando utilizzi Moduli Google per la fatturazione.
• Per i moduli d'ordine dei clienti, è possibile scrivere una formula in Fogli Google per calcolare l'importo totale in base alla selezione dell'articolo, al Paese (le aliquote fiscali sono diverse) e alla quantità selezionata nel modulo.
• Per i moduli di prenotazione alberghiera, una formula può calcolare automaticamente l'affitto della camera in base alla data di check-in e check-out compilata dal cliente nel modulo Google.
• Per i quiz, un docente può calcolare automaticamente il punteggio finale dello studente abbinando i valori inseriti nel modulo con le risposte effettive e assegnando punteggi.
• Se un utente ha inviato più moduli, una formula può aiutarti a determinare il numero totale di voci effettuate da un utente non appena invia un modulo.

Formule di Fogli Google per Moduli Google

In questa guida passo passo imparerai come aggiungere formule a Fogli Google associati a Moduli Google. I valori delle celle corrispondenti nelle righe della risposta verranno calcolati automaticamente quando viene inviata una nuova risposta.

Per avere una migliore comprensione di ciò che stiamo cercando di ottenere, apri questo modulo Google e invia una risposta. Quindi, apri questo foglio Google e troverai la tua risposta in una nuova riga. Le colonne FK vengono riempite automaticamente utilizzando formule.

Tutti gli esempi seguenti utilizzeranno la funzione ArrayFormula di Fogli Google, sebbene alcuni di questi esempi possano anche essere scritti utilizzando la funzione FILTER .

Numero automatico delle risposte ai moduli con un ID univoco

Apri il foglio Google che sta memorizzando le risposte del modulo, vai alla prima colonna vuota e copia e incolla la seguente formula nella riga n. 1 della colonna vuota.

 =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 funzione ROW() restituisce il numero di riga della riga di risposta corrente. Restituisce 1 per la prima riga nella colonna fattura e quindi impostiamo il titolo della colonna nella prima riga. Per le righe successive, se la prima colonna della riga (solitamente Timestamp) non è vuota, l'ID fattura viene generato automaticamente.

Gli ID saranno come 00001 , 00002 e così via. Devi solo inserire la formula nella prima riga della colonna e compila automaticamente tutte le altre righe nella colonna.

La funzione IFERROR restituisce il primo argomento se non è un valore di errore, altrimenti restituisce il secondo argomento se presente o uno spazio vuoto se il secondo argomento è assente. Quindi in questo caso 1/0 è un errore e quindi restituisce sempre un valore vuoto.

Formula di calcolo della data per Moduli Google

Il tuo modulo Google ha due campi di data: la data di check-in e la data di check-out. Le tariffe dell'hotel possono variare in ogni stagione, quindi hai una tabella separata nel Foglio Google che mantiene l'affitto mensile della camera.

La colonna C nel foglio Google contiene le risposte per la data di check-in mentre la colonna D memorizza le date di 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), "" ) ) )

La formula utilizza VLOOKUP per ottenere le tariffe delle camere per la data di viaggio specificata nella risposta del modulo e quindi calcola l'affitto della camera moltiplicando l'affitto della camera per la durata del soggiorno.

La stessa formula può essere scritta anche con IFS invece di 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 ) ) )

Calcola l'importo dell'imposta in base al valore della fattura

In questo approccio, utilizzeremo la funzione FILTER e ciò potrebbe portare a una formula meno complicata rispetto all'utilizzo della funzione IF . Lo svantaggio è che devi scrivere il titolo della colonna nella riga n. 1 e incollare le formule nella riga n. 2 (quindi dovrebbe esistere una risposta del modulo affinché la formula funzioni).

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

Qui applichiamo un'imposta del 35% al ​​valore della fattura e questa formula deve essere aggiunta nella riga n. 2 della colonna intitolata "Importo fiscale" come mostrato nello screenshot.

Assegna i punteggi dei quiz in Moduli Google

Quale città è conosciuta come la grande mela? Questa è una domanda a risposta breve in Moduli Google in modo che gli studenti possano fornire risposte come New York, New York City, New York e saranno comunque corrette. L'insegnante deve assegnare 10 punti alla risposta corretta.

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

In questa formula, stiamo facendo uso della funzione IFS che come un'istruzione IF THEN nella programmazione. Stiamo usando REGEXMATCH per abbinare valori come New York, New York, newyork in una volta sola usando espressioni regolari.

La funzione IFS restituisce un NA se nessuna delle condizioni è vera, quindi aggiungiamo un controllo TRUE alla fine che verrà sempre valutato come true se nessuna delle condizioni precedenti corrisponde e restituisce 0 .

Estrarre il nome del rispondente del modulo

Se hai un campo modulo che chiede all'utente di inserire il proprio nome completo, puoi utilizzare la funzione Fogli Google per estrarre il nome dal nome completo e utilizzare quel campo per inviare e-mail personalizzate.

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

Abbiamo usato il metodo RegexExtract qui per recuperare la stringa prima del primo spazio nel campo del nome. La funzione PROPER metterà in maiuscolo la prima lettera del nome nel caso in cui l'utente abbia inserito il proprio nome in minuscolo.

Trova invii di moduli Google duplicati

Se il tuo modulo Google è indirizzi email di raccolta, puoi utilizzare quel campo per rilevare rapidamente le risposte che sono state inviate dallo stesso utente più volte.

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

Supponendo che la colonna B memorizzi gli indirizzi e-mail degli intervistati del modulo, possiamo utilizzare la funzione COUNTIF per contrassegnare rapidamente le voci duplicate nel nostro foglio di calcolo delle risposte. Puoi anche utilizzare la formattazione condizionale in Fogli per evidenziare le righe che possono essere voci duplicate.

Risposte ai moduli e-mail con valori di riempimento automatico

Puoi utilizzare Document Studio per inviare automaticamente un'e-mail ai rispondenti del modulo. L'e-mail viene inviata dopo che i valori della formula sono stati compilati automaticamente dal foglio Google. La risposta del modulo originale ei valori calcolati possono anche essere inclusi nel documento PDF generato.