【Googleフォーム】回答をスプレッドシートに保存する場合に関数がズレる原因と対処法

この記事では、Googleフォームの回答データをスプレッドシートに保存している場合に、回答データに対して設定していた関数がズレてしまう原因とその対処法について説明します。

回答データに対して設定した関数がズレてしまう

例として、下記のように人数と金額だけ入力する簡単な「会食申請」のフォームを考えましょう。

ここで、仮に会食は1人当たり5,000円までというルールがあった場合を考えてください。

このスプレッドシートに対し、下記のように「1人当たり単価」を計算する関数を設定すれば検証が楽になりそうです。（数式入力後、Ctrl+Enterで下にコピーしました。）

この状態で新しい回答が入ってくると、A8～D8に回答が表示され、E8セルの単価が計算される・・・と思いきや、実はこれではうまくいきません。

なんと上記のように、関数が下にズレてしまい計算ができなくなってしまいます。こうなってしまう原因と対処法を、以下で説明していきます。

ズレの原因は回答が「行ごと挿入される」から

上記のような問題が起こる理由は、Googleフォームの回答がスプレッドシートに保存されるとき、行が丸ごと挿入されるためです。分かりやすいように、先ほどのスプレッドシートに色を付けて再試行します。

このように、8行目に行が丸ごと挿入されていることがわかります。関数がズレてしまう原因はこのためでした。

では、どのようにすれば関数がズレないようにできるのでしょうか。

Googleフォームの回答に対する関数をずらさない方法

結論：別シートを用意してINDEX関数を使う

結論から申し上げると、まず回答を表示させる別シートを用意し、INDEX関数を使って回答を引っ張ってくることで解決できます。

具体的な手順を下記に記載しますので試してみてください。

手順①　表示用の別シートを用意する。

まずは回答表示用の別シートを用意します。新しくシートを追加し、こんな感じで適当にレイアウトを整えてください。

この時、どこかに2から始まる連番の列を用意してください。後ほど「元の回答シートの2行目からデータを引っ張る」という使い方をします。

手順②　INDEX関数を用いて回答データを取り込む

続いて、INDEX関数を用いて回答データを取り込みます。

例えばINDEX(A:A,2)のように書くと、「A列の2番目の値を引っ張ってくる」という意味になります。この2番目を指定するために用意したのが先ほどの2から始まる連番の列です。

ここではコピペができるように絶対参照と相対参照を活用しつつ以下のように入力します。入力後、サジェストに従って下段まで計算式をコピーします。

氏名、人数、支払金額についても同様の数式で引っ張ってこれるため、申請日時の関数をコピペすればOKです。

手順③　表示用のシートに関数を入力する

ここまでできたら、1人当たり単価の列に普通に計算式を入力すればOKです。これで、新しい回答データが入ってきても関数がズレなくなります。

ついでに、2から始まる連番はもう使わないため非表示にしておきましょう。

実際に上手くいくか試してみる

以上の方法で、新しい回答が入っても関数がズレずに計算できるはずです。それでは、実際にいくつか回答を入力して確かめてみましょう。

このように、新たに回答が記録されても関数がズレずに正しく計算できています。

まとめ

Googleフォームの回答データをスプレッドシートに保存する際、関数がズレてしまう原因は回答時に行が丸ごと挿入されているからでした。

これを回避する手段としては、まず回答表示用の別シートを用意し、INDEX関数を用いて元シートから回答データを引っ張ってくるという対策が有効です。

INDEX関数は「この列の上からN番目のデータ」のような指定の仕方をするため、Ｎにあたる数値を参照するための列を用意することが必要です。