Sheetsの参照がずれてしまう
Google Formsの回答を集計したり校正したりするとき、回答が蓄積されていくGoogle Sheetsの「フォームの回答1」シートを直接編集するのではなく、別のシートから解答を参照して複製したシートを作り、その上で作業することが多いと思われます。
その際に、単純な参照ではずれてしまうことがあります。
| A | B | C | |
|---|---|---|---|
| 1 | タイムスタンプ | メールアドレス | 好きな果物 |
| 2 | 2021/10/01 01:00:23 | taiyo@taiyo-hoge.com | メロン |
上のようなシートを複製する際に、以下のように参照してしまうと、回答が増える時に参照範囲がずれてしまいます。
| A | B | C | |
|---|---|---|---|
| 1 | =‘回答シート!A1‘ | =‘回答シート!B1‘ | =‘回答シート!C1‘ |
| 2 | =‘回答シート!A2‘ | =‘回答シート!B2‘ | =‘回答シート!C2‘ |
| 3 | =‘回答シート!A3‘ | =‘回答シート!B3‘ | =‘回答シート!C3‘ |
| 4 | =‘回答シート… | … | … |
ソリューション
まずソリューションを書きます。
Formsの回答を参照するシートには=‘フォームの回答1’!A1や= ‘フォームの回答1’! $A$1のように参照するのではなく、INDIRECT関数を用いて=INDIRECT('フォームの回答1'!A1)と書きましょう。
なぜINDIRECT関数を使うのか
Google Formsから書き込まれる回答は行を置き換えるのではなく、新たな行を作って挿入されているため、「$」による絶対参照を使っても、関数から参照する行はずれてしまいます（実際には行がずれているのではなく、参照範囲の上に回答が挿入されています）。
例えば冒頭の例で3行目に2件目の回答が追加されると、参照している関数は以下のようにずれてしまいます。
| 3 | 2021/10/01 01:00:24 | taiyo@taiyo-hoge.com | マロン |
|---|
| A | B | C | |
|---|---|---|---|
| 1 | =‘回答シート!A1‘ | =‘回答シート!B1‘ | =‘回答シート!C1‘ |
| 2 | =‘回答シート!A2‘ | =‘回答シート!B2‘ | =‘回答シート!C2‘ |
| 3 | =‘回答シート!A4‘ | =‘回答シート!B4‘ | =‘回答シート!C4‘ |
| 4 | =‘回答シート… | … | … |
これを回避するためにINDIRECT関数を用いてみました。参照すべきセルが文字列になるので、Google Sheetsが参照先を追跡することもありません。
| A | B | C | |
|---|---|---|---|
| 1 | =INDIRECT('フォームの回答1'!A1) | =INDIRECT('フォームの回答1'!B1) | =INDIRECT('フォームの回答1'!C1) |
| 2 | =INDIRECT('フォームの回答1'!A2) | =INDIRECT('フォームの回答1'!B2) | =INDIRECT('フォームの回答1'!C2) |
| 3 | =INDIRECT('フォームの回答1'!A3) | =INDIRECT('フォームの回答1'!B3) | =INDIRECT('フォームの回答1'!C3) |
| 4 | =INDIRECT('フォームの回答... | ... | ... |
CSVで大量に作っておけば安心して作業を行うことができるでしょう。Google SheetsのImportは部分置き換えにも対応していますしね。