So verwenden Sie Formeln mit Google-Formularantworten in Tabellen

Wenn Personen Ihr Google-Formular einreichen, wird eine neue Zeile in das Google-Blatt eingefügt, in der die Formularantworten gespeichert werden. Diese Tabellenzeile enthält eine Timestamp-Spalte, das tatsächliche Datum, an dem das Formular gesendet wurde, und die anderen Spalten im Blatt enthalten alle Antworten des Benutzers, eine pro Spalte.

Sie können das Google Forms-Blatt erweitern, um auch Formelfelder einzubeziehen, und die Zellenwerte werden automatisch berechnet, wenn dem Blatt durch das Google-Formular eine neue Zeile hinzugefügt wird. Zum Beispiel:

• Sie können eine Formel mit automatischer Nummerierung haben, die jeder Formularantwort eine automatisch inkrementierende, aber fortlaufende ID zuweist. Dies kann nützlich sein, wenn Sie Google Formulare für die Rechnungsstellung verwenden.
• Für Kundenbestellformulare kann eine Formel in Google Sheets geschrieben werden, um den Gesamtbetrag basierend auf der Artikelauswahl, dem Land (Steuersätze sind unterschiedlich) und der im Formular ausgewählten Menge zu berechnen.
• Für Hotelreservierungsformulare kann eine Formel die Zimmermiete automatisch berechnen, basierend auf dem Check-in- und Check-out-Datum, das vom Kunden in das Google-Formular eingetragen wird.
• Bei Quizzen kann ein Lehrer automatisch die Endpunktzahl des Schülers berechnen, indem er die in das Formular eingegebenen Werte mit den tatsächlichen Antworten abgleicht und Punktzahlen zuweist.
• Wenn ein Benutzer mehrere Formulare gesendet hat, kann Ihnen eine Formel dabei helfen, die Gesamtzahl der Einträge zu ermitteln, die von einem Benutzer gemacht wurden, sobald er ein Formular gesendet hat.

Google Sheets-Formeln für Google Forms

In dieser Schritt-für-Schritt-Anleitung erfahren Sie, wie Sie Formeln zu Google Tabellen hinzufügen, die mit Google Forms verknüpft sind. Die entsprechenden Zellenwerte in den Antwortzeilen werden automatisch berechnet, wenn eine neue Antwort gesendet wird.

Um besser zu verstehen, was wir zu erreichen versuchen, öffnen Sie dieses Google-Formular und senden Sie eine Antwort. Öffnen Sie als Nächstes dieses Google Sheet und Sie finden Ihre Antwort in einer neuen Zeile. Die Spalten FK werden mit Formeln automatisch gefüllt.

Alle folgenden Beispiele verwenden die ArrayFormula-Funktion von Google Sheets, obwohl einige dieser Beispiele auch mit der FILTER Funktion geschrieben werden können.

Automatische Nummerierung von Formularantworten mit einer eindeutigen ID

Öffnen Sie das Google-Blatt, das Formularantworten speichert, gehen Sie zur ersten leeren Spalte und kopieren Sie die folgende Formel in die Zeile #1 der leeren Spalte.

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

Die Funktion ROW() gibt die Zeilennummer der aktuellen Antwortzeile zurück. Es gibt 1 für die erste Zeile in der Rechnungsspalte zurück und somit setzen wir den Spaltentitel in der ersten Zeile. Wenn für nachfolgende Zeilen die erste Spalte der Zeile (normalerweise Timestamp) nicht leer ist, wird die Rechnungs-ID automatisch generiert.

Die IDs lauten wie 00001 , 00002 und so weiter. Sie müssen die Formel nur in der ersten Zeile der Spalte platzieren und alle anderen Zeilen in der Spalte werden automatisch ausgefüllt.

Die Funktion IFERROR gibt das erste Argument zurück, wenn es kein Fehlerwert ist, andernfalls gibt sie das zweite Argument zurück, falls vorhanden, oder ein Leerzeichen, wenn das zweite Argument nicht vorhanden ist. In diesem Fall ist 1/0 also ein Fehler und liefert daher immer einen leeren Wert.

Datumsberechnungsformel für Google Formulare

Ihr Google-Formular hat zwei Datumsfelder – das Check-in-Datum und das Check-out-Datum. Die Hotelpreise können jede Saison variieren, daher haben Sie eine separate Tabelle im Google Sheet, die die Zimmermiete pro Monat enthält.

Die Spalte C im Google Sheet enthält die Antworten für das Check-in-Datum, während die Spalte D die Check-out-Daten speichert.

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

Die Formel verwendet VLOOKUP , um die Zimmerpreise für das in der Formularantwort angegebene Reisedatum abzurufen, und berechnet dann die Zimmermiete, indem sie die Zimmermiete mit der Aufenthaltsdauer multipliziert.

Dieselbe Formel kann auch mit IFS statt VLOOKUP geschrieben werden

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

Berechnen Sie den Steuerbetrag basierend auf dem Rechnungswert

Bei diesem Ansatz verwenden wir die FILTER Funktion, was zu einer weniger komplizierten Formel führen könnte als die Verwendung der IF -Funktion. Der Nachteil ist, dass Sie den Spaltentitel in Zeile 1 schreiben und die Formeln in Zeile 2 einfügen müssen (daher sollte eine Formularantwort vorhanden sein, damit die Formel funktioniert).

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

Hier wenden wir 35 % Steuern auf den Rechnungswert an und diese Formel sollte in Zeile 2 der Spalte mit dem Titel „Steuerbetrag“ hinzugefügt werden, wie im Screenshot gezeigt.

Weisen Sie Quizergebnisse in Google Formulare zu

Welche Stadt ist als Big Apple bekannt? Dies ist eine Kurzantwortfrage in Google Formulare, sodass Schüler Antworten wie „New York“, „New York City“, „NYC“ geben können, die trotzdem richtig sind. Der Lehrer muss der richtigen Antwort 10 Punkte zuordnen.

 =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 dieser Formel verwenden wir die IFS -Funktion, die wie eine IF THEN -Anweisung in der Programmierung ist. Wir verwenden REGEXMATCH , um Werte wie New York, New York, newyork in einem Rutsch mit regulären Ausdrücken abzugleichen.

Die IFS -Funktion gibt NA zurück, wenn keine der Bedingungen wahr ist, also fügen wir am Ende eine TRUE -Prüfung hinzu, die immer als true ausgewertet wird, wenn keine der vorherigen Bedingungen zutrifft, und 0 zurückgibt.

Extrahieren Sie den Vornamen des Formularteilnehmers

Wenn Sie ein Formularfeld haben, das den Benutzer auffordert, seinen vollständigen Namen zu vervollständigen, können Sie die Google Sheets-Funktion verwenden, um den Vornamen aus dem vollständigen Namen zu extrahieren und dieses Feld zum Senden personalisierter E-Mails zu verwenden.

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

Wir haben hier die RegexExtract Methode verwendet, um die Zeichenfolge vor dem ersten Leerzeichen im Namensfeld abzurufen. Die Funktion PROPER den ersten Buchstaben des Namens groß, falls der Benutzer seinen Namen in Kleinbuchstaben eingegeben hat.

Finden Sie doppelte Google-Formularübermittlungen

Wenn es sich bei Ihrem Google-Formular um eine Sammlung von E-Mail-Adressen handelt, können Sie dieses Feld verwenden, um schnell Antworten zu erkennen, die von demselben Benutzer mehrmals gesendet wurden.

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

Angenommen, die Spalte B speichert die E-Mail-Adressen der Formularbefragten, können wir die COUNTIF Funktion verwenden, um schnell doppelte Einträge in unserer Antworttabelle zu markieren. Sie können auch die bedingte Formatierung in Tabellen verwenden, um Zeilen hervorzuheben, bei denen es sich möglicherweise um doppelte Einträge handelt.

E-Mail-Formularantworten mit AutoFill-Werten

Sie können Document Studio verwenden, um automatisch eine E-Mail an die Formularteilnehmer zu senden. Die E-Mail wird gesendet, nachdem die Formularwerte von Google Sheet automatisch ausgefüllt wurden. Die ursprüngliche Formularantwort und die berechneten Werte können auch in das generierte PDF-Dokument aufgenommen werden.