Googleスプレッドシート・ARRAYFORMULA
ARRAYFORMULAの「配列結果を展開できません」を直す方法
ARRAYFORMULAを入れたのに #REF! が出て、計算結果が表示されない。そんなときは、エラーに書かれた「上書きされるセル」を確認してください。そこに必要なデータがあれば別の場所へ移し、出力先を空けると、数式を書き直さずに直せます。
#REF!の説明を開き、邪魔になっているセルを特定する
練習シートをコピーしたら、「エラーを直す」タブを開きます。A列に品目、B列に単価、C列に数量、D列に小計、E列にメモがある表です。D2の式で小計をまとめて計算するはずが、#REF! になっています。
まずD2の「#REF!」にマウスを重ねて、エラーの説明を読みます。この表では「D4 のデータを上書きするため、配列結果は展開されませんでした。」と表示されます。つまり、D2の数式が下へ結果を広げようとしているのに、D4に別のデータが入っている状態です。
#REF! はほかの参照エラーでも表示されます。「配列結果」「上書き」と書かれているかを見てから対処を決めます。
D4のメモをE4に移し、D4の手入力だけを消す
次にD4をクリックして数式バーを確認します。練習シートでは「要確認」と直接入力されています。この文字がD2のARRAYFORMULAの展開を止めています。
- D4の「要確認」をコピーし、メモ欄のE4へ貼り付けます。残す必要のあるデータを先に退避します。
- D4だけを選んでDeleteキーを押し、手入力した文字を消します。4行目全体やD列全体は削除しません。
- D2の
#REF!が消え、D4に小計 750 が表示されたら復旧です。D4は空白のままではなく、配列数式の結果で埋まります。
数式のあるD2は変更しません。すでに入力されている式が、空いたD4へ結果を自動で表示します。
小計がD2:D7へ戻ったら、数式はそのままでよい
「完成例」タブでは、D4のメモをE4に移した状態を確認できます。小計はコピー用紙2,320、封筒2,200、付箋750、ボールペン960、ファイル480、配送箱760と表示されています。
D2に入っている式は次のとおりです。直った場合は入力し直す必要はありませんが、仕組みを確かめたいときはコピーして使えます。
D2にあるARRAYFORMULAの式
=ARRAYFORMULA(IF(A2:A7="","",B2:B7*C2:C7))A2:A7 は品目が入る範囲で、品目が空欄なら小計も空欄にします。B2:B7 は単価、C2:C7 は数量です。この2つの範囲を掛けた結果を、D2から6行分表示します。数式を入力するのはD2だけで、D3:D7へコピーする必要はありません。
自分の表で列の場所が違えば、品目・単価・数量の列記号を変えます。データが100行目まであれば、3つの範囲の終端をそれぞれ 100 にそろえ、結果を表示する列の先頭セルに式を置いてください。
まだ展開できないときは、次の上書き先を確認する
D4の文字を消してもエラーが続くなら、もう一度数式セルの吹き出しを開いてください。ほかのセルにも手入力の値や別の数式が残っていないか確認します。見た目が空欄でも、スペースだけが入っている場合があります。選択したセルの数式バーで確かめ、必要な内容は別の場所へ移してから表示先を空けます。
IFERROR で #REF! の表示を隠しても、上書き先が塞がった状態は直りません。既存データを移せない場合は、別の空いた列やタブへ配列数式の出力場所を変える方法もあります。
同じ種類のエラーは、複数セルに結果を表示する UNIQUE関数 や FILTER関数 でも起こります。関数自体の仕様は、Google公式のARRAYFORMULAヘルプで確認できます。