スプレッドシートのGoogleフォームの回答で数式を使用する方法

公開: 2022-06-16

ユーザーがGoogleフォームを送信すると、フォームの回答を保存している新しい行がGoogleスプレッドシートに挿入されます。このスプレッドシートの行には、タイムスタンプ列、フォームが送信された実際の日付が含まれ、シートの他の列には、列ごとに1つずつ、すべてのユーザーの回答が含まれます。

Googleフォームシートを拡張して数式フィールドも含めることができ、Googleフォームによってシートに新しい行が追加されるたびにセルの値が自動的に計算されます。例えば：

すべてのフォーム応答に自動インクリメントであるが順次IDを割り当てる自動番号式を使用できます。請求にGoogleフォームを使用している場合に便利です。
顧客注文フォームの場合、Googleスプレッドシートに数式を記述して、アイテムの選択、国（税率は異なります）、およびフォームで選択された数量に基づいて合計金額を計算できます。
ホテル予約フォームの場合、数式を使用すると、Googleフォームに顧客が入力したチェックイン日とチェックアウト日に基づいて部屋の家賃を自動的に計算できます。
クイズの場合、教師はフォームに入力された値を実際の回答と照合してスコアを割り当てることにより、生徒の最終スコアを自動的に計算できます。
ユーザーが複数のフォームを送信した場合、数式は、ユーザーがフォームを送信するとすぐにユーザーが行ったエントリの総数を決定するのに役立ちます。

Autofill Google Sheets Formulas

Googleフォーム用のGoogleスプレッドシートの数式

このステップバイステップガイドでは、Googleフォームに関連付けられているGoogleスプレッドシートに数式を追加する方法を学習します。応答行の対応するセル値は、新しい応答が送信されたときに自動的に計算されます。

私たちが達成しようとしていることをよりよく理解するには、このGoogleフォームを開いて回答を送信してください。次に、このGoogleスプレッドシートを開くと、新しい行に回答が表示されます。列FKは、数式を使用して自動入力されます。

以下のすべての例では、GoogleスプレッドシートのArrayFormula関数を使用しますが、これらの例の一部はFILTER関数を使用して記述することもできます。

一意のIDを使用したフォーム応答の自動番号付け

フォームの回答を保存しているGoogleスプレッドシートを開き、最初の空の列に移動して、空の列の行1に次の数式をコピーして貼り付けます。

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

ROW()関数は、現在の応答行の行番号を返します。請求書列の最初の行に1が返されるため、最初の行に列のタイトルを設定します。後続の行では、行の最初の列（通常はタイムスタンプ）が空でない場合、請求書IDが自動生成されます。

IDは00001などのように00002ます。数式を列の最初の行に配置するだけで、列の他のすべての行に自動入力されます。

IFERROR関数は、エラー値でない場合は最初の引数を返し、そうでない場合は2番目の引数を返します。2番目の引数がない場合は空白を返します。したがって、この場合、 1/0はエラーであるため、常に空白の値を返します。

Googleフォームの日付計算式

Googleフォームには、チェックイン日とチェックアウト日という2つの日付フィールドがあります。ホテルの料金は季節ごとに異なる場合があるため、Googleスプレッドシートには、1か月あたりの部屋の家賃を管理する別の表があります。

Google Sheets Date Formula

Googleスプレッドシートの列Cにはチェックイン日の回答が保持され、D列にはチェックアウト日が格納されます。

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

数式はVLOOKUPを使用して、フォームの応答で指定された旅行日の宿泊料金を取得し、部屋の家賃に滞在期間を掛けて部屋の家賃を計算します。

VLOOKUPの代わりにIFSを使用して同じ式を記述することもできます

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

請求書の値に基づいて税額を計算する

このアプローチでは、 FILTER関数を使用します。これにより、 IF関数を使用するよりも複雑な式が得られない可能性があります。欠点は、行＃1に列のタイトルを記述し、行＃2に数式を貼り付ける必要があることです（したがって、数式が機能するには1つのフォーム応答が存在する必要があります）。

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

ここでは、請求書の値に35％の税金を適用します。この数式は、スクリーンショットに示すように、「TaxAmount」というタイトルの列の2行目に追加する必要があります。

Googleフォームでクイズスコアを割り当てる

ビッグアップルとして知られている都市はどれですか？これはGoogleフォームの簡単な質問なので、生徒はニューヨーク、ニューヨーク市、ニューヨークなどの回答をすることができますが、それでも正解です。教師は正解に10ポイントを割り当てる必要があります。

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

この式では、プログラミングでIF THENステートメントのようなIFS関数を使用しています。正規表現を使用してNew York, New York, newyork値を一度に照合するためにREGEXMATCHを使用しています。

IFS関数は、いずれの条件も真でない場合はNAを返すため、最後にTRUEチェックを追加します。これは、前の条件のいずれも一致せず、 0を返す場合に常にtrueと評価されます。

フォーム回答者の名を抽出します

ユーザーにフルネーム全体を入力するよう求めるフォームフィールドがある場合は、Googleスプレッドシート機能を使用してフルネームから名を抽出し、そのフィールドを使用してパーソナライズされたメールを送信できます。

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

ここでは、 RegexExtractメソッドを使用して、名前フィールドの最初のスペースの前にある文字列をフェッチしました。 PROPER関数は、ユーザーが名前を小文字で入力した場合に備えて、名前の最初の文字を大文字にします。

重複するGoogleフォームの送信を検索

Googleフォームがコレクションのメールアドレスである場合、そのフィールドを使用して、同じユーザーが複数回送信した応答をすばやく検出できます。

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

列Bにフォームの回答者の電子メールアドレスが保存されていると仮定すると、 COUNTIF関数を使用して、回答スプレッドシートの重複エントリをすばやくマークできます。スプレッドシートで条件付き書式を使用して、重複エントリの可能性がある行を強調表示することもできます。

AutoFill値を使用した電子メールフォームの応答

Document Studioを使用して、フォームの回答者に電子メールを自動的に送信できます。メールは、フォーミュラの値がGoogleスプレッドシートによって自動入力された後に送信されます。元のフォームの応答と計算された値は、生成されたPDFドキュメントに含めることもできます。