Googleフォームをスプレッドシートで集計する時に関数がずれる

スプレッドシートがうまくできなくて困っている女性
スプレッドシートがうまくできなくて困っている女性

Googleフォームで複数の人から入力してもらったのをGoogleスプレッドシートで集計するのは普通にありますが注意点があります。

今回はその注意点と解決する方法について解説しています。

Googleフォームをスプレッドシートで集計するとの問題点

たとえば友人達とプロジェクトをやっていて物品を購入するときに立て替えた購入費を集計して精算することを考えます。

購入品の申請フォーム
購入品の申請フォーム

入力項目を減らすことと単純ミスを減らすために合計金額は集計するスプレッドシートで計算するので上記のフォームには合計金額を入力するフォームはありません。

こちらのフォームと連携するスプレッドシートを作成すると以下の図のA列~F列が自動的に作成されます。

集計シート
集計シート

G列には合計金額を計算して表示する列として後から追加しています。数式は以下の様にしています。

=E2*F2

以下、数式をコピーしています。

ここで先ほどのフォームから1つ回答を送ってみます。

フォームからの入力により関数がずれる
フォームからの入力により関数がずれる

上記のように先ほどG2に入れていた数式が一つ下のG3に移動しています。G2には何も数式が入っていない状態になっています。

これはスプレッドシートがフォームを受け取る時に、上書きではなく、行を挿入してフォームのデータを書き込むという動作をするためです。

挿入という動作のために関数がずれることが問題となります。

Googleフォームのデータが挿入されても問題ない解決方法

Googleフォームを受け取った後に数式を入力

Googleフォームを受け取った後にG列に数式を入れていくやり方です。精算する直前にやってしまえばいいのですが一番面倒くさくてエレガントではない解決策です。

ArrayFormula関数を使う方法

1つだけフォームからのデータを受け取った後に「ArrayFormula」関数を埋め込むやり方です。最初にサンプルとしてGoogleフォームからテストデータを送るという名目でいいかと思います。

ArrayFormula関数を使った例
ArrayFormula関数を使った例

上記のようにサンプルとして受け取ったフォームデータの小計に以下の数式を入力します。

=ArrayFormula(E2:E*F2:F)

以降のフォームからのデータは3行目以下に挿入されますので関数がずれることはありません。

集計を別シートにしてINDEX関数を使う方法

若干手間がかかりますが最適なやり方は集計を別シートにして、フォームデータを「INDEX」関数を使って取得する方法です。

このやり方のメリットは以下の通りです。

  1. フォーム回答を受け取るシートには全く触らないのでデータを誤って上書きしたり削除したりする危険性が減る
  2. サンプルデータが不要
別シートでINDEX関数で集計
別シートでINDEX関数で集計

式としてはA1に

=INDEX('フォームの回答 1'!A1:F)

G2に

=ArrayFormula(E2:E*F2:F)

を入れています。

ポイントはINDEX関数をA1に入れて列の見出しも関数で作らせることです。これにより最初のフォーム回答が2行目に入っても数式に影響を受けないことです。

手動で列の見出しを記入して、A2にINDEX関数を下記の様に入力すると最初のフォーム回答が2行目に入ると数式が変化してしまい期待した結果になりません。

=INDEX('フォームの回答 1'!A2:F)
↓最初のフォーム回答が2行目に入ると以下へ変化
=INDEX('フォームの回答 1'!A3:F)

まとめ

Googleフォームのフォーム回答はスプレッドシートへの書き込みは挿入で行われるということは頭に入れておく必要があります。

また、フォームの回答の下に手動でデータを追加した場合、次にフォームデータが送信された場合は最後のフォーム回答行の下に挿入されることになります。つまり直前のフォームデータの行と手動で追加した行の間に挿入されることになります。

なので、フォーム回答のシートには手動でのデータ追加はやらない方が混乱しなくて良いです。

コメント