Cum să utilizați formule cu răspunsuri la formulare Google în Foi de calcul

Când oamenii vă trimit Formularul Google, în foaia Google este inserat un rând nou care stochează răspunsurile formularului. Acest rând de foaie de calcul conține o coloană Timestamp, data reală la care a fost trimis formularul, iar celelalte coloane din foaie conțin toate răspunsurile utilizatorului, câte unul pe coloană.

Puteți extinde foaia Formulare Google pentru a include și câmpuri de formulă, iar valorile celulelor sunt calculate automat ori de câte ori un rând nou este adăugat în foaie de Formularul Google. De exemplu:

• Puteți avea o formulă de numărare automată care atribuie un ID cu incrementare automată, dar secvențial fiecărui răspuns de formular. Poate fi util atunci când utilizați Formulare Google pentru facturare.
• Pentru formularele de comandă ale clienților, în Google Sheets se poate scrie o formulă pentru a calcula suma totală pe baza selecției articolului, a țării (cotele de impozitare sunt diferite) și a cantității selectate în formular.
• Pentru formularele de rezervare la hotel, o formulă poate calcula automat chiria camerei pe baza datei de check-in și check-out completate de client în Formularul Google.
• Pentru chestionare, un profesor poate calcula automat punctajul final al elevului prin potrivirea valorilor introduse în formular cu răspunsurile efective și atribuirea punctajelor.
• Dacă un utilizator a trimis mai multe formulare, o formulă vă poate ajuta să determinați numărul total de intrări făcute de un utilizator de îndată ce trimit un formular.

Formule Google Sheets pentru Formulare Google

În acest ghid pas cu pas, veți învăța cum să adăugați formule în Foi de calcul Google care sunt asociate cu Formulare Google. Valorile celulelor corespunzătoare din rândurile de răspuns vor fi calculate automat atunci când este trimis un răspuns nou.

Pentru a înțelege mai bine ceea ce încercăm să realizăm, deschideți acest formular Google și trimiteți un răspuns. Apoi, deschideți această foaie Google și veți găsi răspunsul dvs. într-un rând nou. Coloanele FK sunt completate automat folosind formule.

Toate exemplele de mai jos vor folosi funcția ArrayFormula din Google Sheets, deși unele dintre aceste exemple pot fi scrise și folosind funcția FILTER .

Numărarea automată a răspunsurilor la formular cu un ID unic

Deschideți foaia Google care stochează răspunsurile formularului, mergeți la prima coloană goală și copiați și inserați următoarea formulă în rândul #1 al coloanei goale.

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

Funcția ROW() returnează numărul rândului rândului de răspuns curent. Returnează 1 pentru primul rând din coloana Facturii și astfel setăm titlul coloanei în primul rând. Pentru rândurile următoare, dacă prima coloană a rândului (de obicei marca temporală) nu este goală, ID-ul facturii este generat automat.

ID-urile vor fi ca 00001 , 00002 și așa mai departe. Trebuie doar să plasați formula pe primul rând al coloanei și completează automat toate celelalte rânduri din coloană.

Funcția IFERROR returnează primul argument dacă nu este o valoare de eroare, în caz contrar returnează al doilea argument dacă este prezent sau un gol dacă al doilea argument este absent. Deci, în acest caz, 1/0 este o eroare și, prin urmare, returnează întotdeauna o valoare goală.

Formula de calcul a datei pentru Formulare Google

Formularul dvs. Google are două câmpuri pentru dată - data de check-in și data de check-out. Tarifele hoteliere pot varia în fiecare sezon, așa că aveți un tabel separat în Google Sheet care menține chiria camerei pe lună.

Coloana C din foaia Google conține răspunsurile pentru data de check-in, în timp ce coloana D stochează datele 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), "" ) ) )

Formula folosește VLOOKUP pentru a obține tarifele camerei pentru data călătoriei specificată în răspunsul formularului și apoi calculează chiria camerei înmulțind chiria camerei cu durata șederii.

Aceeași formulă poate fi scrisă și cu IFS în loc 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 ) ) )

Calculați valoarea taxei pe baza valorii facturii

În această abordare, vom folosi funcția FILTER și asta ar putea duce la o formulă mai puțin complicată decât utilizarea funcției IF . Dezavantajul este că trebuie să scrieți titlul coloanei în rândul #1 și să lipiți formulele în rândul #2 (deci ar trebui să existe un răspuns de formular pentru ca formula să funcționeze).

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

Aici aplicăm un impozit de 35% la valoarea facturii și această formulă ar trebui adăugată în rândul #2 al coloanei intitulate „Suma taxei”, așa cum se arată în captură de ecran.

Atribuiți scoruri la test în Formulare Google

Care oraș este cunoscut drept mărul cel mare? Aceasta este o întrebare cu răspuns scurt în Formulare Google, astfel încât studenții să poată da răspunsuri precum New York, New York City, NYC și vor fi în continuare corecte. Profesorul trebuie să atribuie 10 puncte răspunsului corect.

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

În această formulă, folosim funcția IFS care place o instrucțiune IF THEN în programare. Folosim REGEXMATCH pentru a potrivi valori precum New York, New York, newyork dintr-o singură mișcare folosind expresii regulate.

Funcția IFS returnează un NA dacă niciuna dintre condiții nu este adevărată, așa că adăugăm o verificare TRUE la sfârșit care va fi întotdeauna evaluată la true dacă niciuna dintre condițiile anterioare nu se potrivește și returnează 0 .

Extrageți prenumele respondentului formularului

Dacă aveți un câmp de formular care cere utilizatorului să-și completeze numele complet, puteți utiliza funcția Foi de calcul Google pentru a extrage prenumele din numele complet și utilizați acel câmp pentru a trimite e-mailuri personalizate.

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

Am folosit metoda RegexExtract aici pentru a prelua șirul înainte de primul spațiu din câmpul de nume. Funcția PROPER va scrie cu majuscule prima literă a numelui în cazul în care utilizatorul și-a introdus numele cu litere mici.

Găsiți Trimiteri de formulare Google duplicat

Dacă formularul dvs. Google reprezintă adrese de e-mail de colectare, puteți utiliza acel câmp pentru a detecta rapid răspunsurile care au fost trimise de același utilizator de mai multe ori.

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

Presupunând că coloana B stochează adresele de e-mail ale respondenților formularului, putem folosi funcția COUNTIF pentru a marca rapid intrările duplicate în foaia noastră de răspunsuri. De asemenea, puteți utiliza formatarea condiționată în Foi de calcul pentru a evidenția rândurile care sunt posibile intrări duplicate.

Răspunsuri la formulare de e-mail cu valori de completare automată

Puteți utiliza Document Studio pentru a trimite automat un e-mail respondenților formularului. E-mailul este trimis după ce valorile formularului sunt completate automat de Google Sheet. Răspunsul formularului original și valorile calculate pot fi incluse și în documentul PDF generat.