Jak używać formuł z odpowiedziami na formularze Google w Arkuszach

Gdy ludzie przesyłają Twój formularz Google, w arkuszu Google wstawiany jest nowy wiersz, w którym przechowywane są odpowiedzi z formularza. Ten wiersz arkusza kalkulacyjnego zawiera kolumnę sygnatury czasowej, rzeczywistą datę przesłania formularza, a pozostałe kolumny w arkuszu zawierają wszystkie odpowiedzi użytkownika, po jednej na kolumnę.

Możesz rozszerzyć arkusz Formularzy Google, aby zawierał również pola z formułami, a wartości komórek są automatycznie obliczane za każdym razem, gdy do arkusza dodawany jest nowy wiersz przez Formularz Google. Na przykład:

• Możesz mieć formułę automatycznego numerowania, która przypisuje automatycznie zwiększający się, ale sekwencyjny identyfikator do każdej odpowiedzi na formularz. Może to być przydatne, gdy używasz Formularzy Google do fakturowania.
• W przypadku formularzy zamówień klientów w Arkuszach Google można napisać formułę, aby obliczyć łączną kwotę na podstawie wyboru pozycji, kraju (stawki podatkowe są różne) i ilości wybranej w formularzu.
• W przypadku formularzy rezerwacji hotelowych, formuła może automatycznie obliczyć czynsz za pokój na podstawie daty zameldowania i wymeldowania podanej przez klienta w Formularzu Google.
• W przypadku quizów nauczyciel może automatycznie obliczyć końcową ocenę ucznia, dopasowując wpisane w formularzu wartości do rzeczywistych odpowiedzi i przypisując wyniki.
• Jeśli użytkownik przesłał wiele formularzy, formuła może pomóc w określeniu całkowitej liczby wpisów dokonanych przez użytkownika zaraz po przesłaniu formularza.

Formuły Arkuszy Google dla Formularzy Google

W tym przewodniku krok po kroku dowiesz się, jak dodawać formuły do ​​Arkuszy Google, które są powiązane z Formularzami Google. Odpowiednie wartości komórek w wierszach odpowiedzi zostaną automatycznie obliczone po przesłaniu nowej odpowiedzi.

Aby lepiej zrozumieć, co staramy się osiągnąć, otwórz ten formularz Google i prześlij odpowiedź. Następnie otwórz ten Arkusz Google, a odpowiedź znajdziesz w nowym wierszu. Kolumny FK są wypełniane automatycznie za pomocą formuł.

Wszystkie poniższe przykłady wykorzystują funkcję ArrayFormula Arkuszy Google, chociaż niektóre z tych przykładów można również napisać za pomocą funkcji FILTER .

Automatyczne numerowanie odpowiedzi na formularze z unikalnym identyfikatorem

Otwórz Arkusz Google, w którym są przechowywane odpowiedzi z formularza, przejdź do pierwszej pustej kolumny i skopiuj i wklej następującą formułę w wierszu nr 1 pustej kolumny.

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

Funkcja ROW() zwraca numer bieżącego wiersza odpowiedzi. Zwraca 1 dla pierwszego wiersza w kolumnie faktury, dlatego ustawiamy tytuł kolumny w pierwszym wierszu. W przypadku kolejnych wierszy, jeśli pierwsza kolumna wiersza (zazwyczaj znacznik czasu) nie jest pusta, identyfikator faktury jest generowany automatycznie.

Identyfikatory będą takie jak 00001 , 00002 i tak dalej. Wystarczy umieścić formułę w pierwszym wierszu kolumny i automatycznie wypełnić wszystkie pozostałe wiersze w kolumnie.

Funkcja IFERROR zwraca pierwszy argument, jeśli nie jest to wartość błędu, w przeciwnym razie zwraca drugi argument, jeśli jest obecny, lub wartość pustą, jeśli drugi argument jest nieobecny. Więc w tym przypadku 1/0 jest błędem i dlatego zawsze zwraca pustą wartość.

Formuła obliczania dat dla formularzy Google

Twój formularz Google ma dwa pola daty – datę zameldowania i datę wymeldowania. Stawki hotelowe mogą się różnić w każdym sezonie, dlatego w Arkuszu Google znajduje się osobna tabela zawierająca miesięczny czynsz za pokój.

Kolumna C w Arkuszu Google zawiera odpowiedzi dotyczące daty zameldowania, podczas gdy kolumna D przechowuje daty wymeldowania.

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

Formuła korzysta z funkcji VLOOKUP , aby uzyskać stawki za pokój dla daty podróży określonej w odpowiedzi na formularz, a następnie oblicza czynsz za pokój, mnożąc czynsz za pokój przez czas pobytu.

Tę samą formułę można również zapisać za pomocą IFS zamiast funkcji 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 ) ) )

Oblicz kwotę podatku na podstawie wartości faktury

W tym podejściu użyjemy funkcji FILTER , co może prowadzić do mniej skomplikowanej formuły niż użycie funkcji IF . Minusem jest to, że musisz wpisać tytuł kolumny w wierszu 1 i wkleić formuły w wierszu 2 (aby formuła działała, powinna istnieć jedna odpowiedź z formularza).

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

Tutaj stosujemy podatek 35% do wartości faktury i tę formułę należy dodać w wierszu nr 2 kolumny zatytułowanej „Kwota podatku”, jak pokazano na zrzucie ekranu.

Przypisz wyniki quizów w Formularzach Google

Które miasto jest znane jako Wielkie Jabłko? To jest krótkie pytanie w Formularzach Google, więc uczniowie mogą udzielać odpowiedzi, takich jak Nowy Jork, Nowy Jork, Nowy Jork, i nadal będą one poprawne. Nauczyciel musi przyznać poprawną odpowiedź 10 punktów.

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

W tej formule korzystamy z funkcji IFS , która przypomina instrukcję IF THEN w programowaniu. Używamy REGEXMATCH do dopasowywania wartości takich jak New York, New York, newyork za jednym razem przy użyciu wyrażeń regularnych.

Funkcja IFS zwraca NA , jeśli żaden z warunków nie jest spełniony, więc na końcu dodajemy sprawdzenie TRUE , które zawsze zostanie ocenione jako true , jeśli żaden z poprzednich warunków nie zostanie spełniony i zwróci 0 .

Wyodrębnij imię respondenta formularza

Jeśli masz pole formularza, które prosi użytkownika o pełne imię i nazwisko, możesz użyć funkcji Arkuszy Google, aby wyodrębnić imię z pełnego imienia i nazwiska i użyć tego pola do wysyłania spersonalizowanych e-maili.

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

Użyliśmy tutaj metody RegexExtract , aby pobrać ciąg przed pierwszą spacją w polu nazwy. Funkcja PROPER zamieni pierwszą literę nazwy na wielką, jeśli użytkownik wpisał swoje imię małymi literami.

Znajdź zduplikowane przesłane formularze Google

Jeśli Twój formularz Google to adresy e-mail do zbierania, możesz użyć tego pola, aby szybko wykryć odpowiedzi, które zostały przesłane przez tego samego użytkownika wielokrotnie.

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

Zakładając, że kolumna B przechowuje adresy e-mail respondentów formularza, możemy użyć funkcji COUNTIF , aby szybko zaznaczyć zduplikowane wpisy w naszym arkuszu odpowiedzi. Możesz też użyć formatowania warunkowego w Arkuszach, aby wyróżnić wiersze, które mogą być zduplikowanymi pozycjami.

Odpowiedzi na formularze e-mail z wartościami autouzupełniania

Możesz użyć Document Studio, aby automatycznie wysłać wiadomość e-mail do respondentów formularza. Wiadomość e-mail jest wysyłana po automatycznym wypełnieniu wartości formuły przez Arkusz Google. Oryginalna odpowiedź formularza i obliczone wartości mogą być również zawarte w wygenerowanym dokumencie PDF.