意外と知らない？Google スプレッドシート関数の落とし穴　～集計作業を効率化する4つの関数と、見落としがちな注意点～

Google スプレッドシートには豊富な関数が用意されており、簡単な計算から複雑なデータ処理まで、幅広い業務をサポートしてくれます。
データ集計や分析に欠かせないツールとして、日々の業務に活用している方も多いのではないでしょうか？

そんな便利なGoogle スプレッドシートですが、関数の特性を正しく理解せずに使うと意図しない結果を招くことがあります。特に、大量のデータを扱う場合や、複数人でファイルを共有・編集する場合は、ちょっとした設定ミスが思わぬトラブルにつながる恐れがあります。

そこで本記事では、Google スプレッドシートの関数のなかでも特に注意が必要な4種に焦点を当て、「意外と知らない落とし穴とその対策」 について詳しく解説します。ぜひ本記事を参考に、スプレッドシート関数をより安全かつ効率的に使いこなしてください。

※本記事内のショートカットキーなどの表記は、いずれもWindows版Google ChromeでGoogle スプレッドシートを使用した場合のものです。

1. ARRAYFORMULA関数：数式を自動展開！　ただし組み合わせには要注意

ARRAYFORMULA関数とは？

ARRAYFORMULA関数は、1つのセルに入力した数式を、指定した範囲の複数のセルに自動的に展開（適用）する関数です。

通常、数式を複数行や列に適用する際は、コピー＆ペースト操作などを用いて数式を複製する必要があります。しかし、ARRAYFORMULA関数を使えば、1つのセルに数式を入力するだけで自動的に範囲全体へ数式を適用できます。

複数の行に対して同じ計算を繰り返し行う場合や、新しい行が追加された際に自動的に計算を適用したい場合に使うことで、シートの更新作業の手間を大幅に減らせるでしょう。

例えば、A列に単価・B列に数量が入力されているシートがあり、C列で各行の合計金額を計算したいケースがあるとします。

通常はC1セルに =A1 * B1 と入力してそれを下へコピーしますが、ARRAYFORMULA関数を使えばC1セルに下記のように入力するだけで済みます。

=ARRAYFORMULA(A:A * B:B)

これで、A列とB列の各行同士をかけ合わせた結果が、C列に一括で表示されます。

ARRAYFORMULA関数の使用例

ARRAYFORMULA関数の注意点：他の関数との相性

ARRAYFORMULA関数はとても便利ですが、すべての関数と自由に組み合わせられるわけではありません。単純に組み合わせるだけでは期待通りに動作せず、意図しない結果になることがあるので注意しましょう。

例1：SUM関数との組み合わせ

A1:C10の範囲に対して各行ごとの合計を計算したい場合、以下のような数式を入力しても期待する結果は得られません。

=ARRAYFORMULA(SUM(A1:C10))

この数式では、A1:C10範囲全体の合計が計算されてしまいます。

これは、SUM関数がもともと引数として範囲を受け取るため、SUM関数の本来の処理が優先されてしまい、ARRAYFORMULA関数の効果が発揮されないためです。

同様の理由で、他にもAVERAGE（範囲の数値の平均を返す）やCONCATENATE（範囲の要素を結合する）、COUNT（範囲の数値を数える）などの「範囲」をまとめて扱う機能を持つ関数とは相性が悪いので、注意が必要です。

ARRAYFORMULA関数とSUM関数の組み合わせ

例2：INDIRECT関数との組み合わせ

INDIRECT関数との組み合わせにも注意が必要です。

例えば、A1:A10の範囲に参照先のシート名が入力されており、それぞれのシートの値をまとめて表示したい場合、以下のような数式を入力してもA1の参照先しか取得されません。

=ARRAYFORMULA(INDIRECT(A1:A10))

このように、そもそもARRAYFORMULAとの組み合わせに対応していない関数も存在しています。

ARRAYFORMULA関数とINDIRECT関数の組み合わせ

例3：XLOOKUP関数との組み合わせ

ARRAYFORMULA関数と組み合わせることで、単体で使用する場合とは異なる挙動を示す関数も存在します。

XLOOKUP関数を使用すると、単一の値だけでなく、範囲（1列または1行）を戻り値として返すことができます。例えば、データベースのシートからIDが一致する行のデータをまとめて取得する、といった使い方ができます。

しかし、ARRAYFORMULA関数と組み合わせると、XLOOKUP関数の戻り値が単一の値に限定されてしまいます。範囲を戻り値として扱いたい場合は、後述するMAP関数やBYROW関数などの代替手段の検討が必要です。

ARRAYFORMULA関数とXLOOKUP関数の組み合わせ

対処方法

1. 関数を使用しない計算で代用

ARRAYFORMULA関数内の数式が単純な合計や結合などのシンプルな内容の場合、関数を使わず「+」や「&」などの基本的な演算子を用いることもできます。

SUM関数の例の場合、3列程度の範囲であれば、SUM関数を使わずに「+」でそのまま計算する方法でも対応が可能です。

=ARRAYFORMULA(A:A + B:B + C:C)

ただし、この場合、計算が必要な列数が多くなればなるほど記述が煩雑になってしまうので、多数の列を処理する必要がある場合は次にご紹介する方法も検討すると良いでしょう。

2. MAP関数、BYROW関数などを活用

Google スプレッドシートには、ARRAYFORMULA関数以外にもMAP関数やBYROW関数などの、より複雑な配列処理を行うための関数も用意されています。

例えば、SUM関数を使って行単位の合計を計算したい場合は、BYROW関数を使うとシンプルに実現できます。

=BYROW(A1:C10, LAMBDA(row, SUM(row)))

3. ARRAYFORMULA関数を使わずにコピー＆ペーストで対処

あえてARRAYFORMULA関数を使わず、通常の数式をコピー＆ペーストする方法に戻ってみるのも一つの手です。

ARRAYFORMULA関数と比べると手作業感はありますが、関数の仕様を気にせず簡単に導入でき、数式自体もシンプルになります。

コピー＆ペーストの際は、以下のようなショートカットキーを活用すると効率的です。

• Ctrl + D： 選択したセルの内容を下方向にコピー
• Ctrl + R： 選択したセルの内容を右方向にコピー
• F4（または Ctrl + Y、Ctrl + Shift + Z）： 直前の操作を繰り返す

2. IMPORTRANGE関数：別ファイルからデータを取得！　ただし使いすぎには要注意

IMPORTRANGE関数とは？

IMPORTRANGE関数は、別のGoogle スプレッドシートからデータを参照・取得できる関数です。複数のファイルに分散しているデータを集約したり、他部署が管理しているデータを参照したりする際に重宝します。

例えば、「売上データ_2024年」というファイルにある「年間売上」シートから、A列とB列のデータを読み込みたい場合、次のように記述します。

=IMPORTRANGE("「売上データ_2024年」のスプレッドシートURL", "年間売上!A:B")

IMPORTRANGE関数の使用例

IMPORTRANGE関数の注意点1：直接読み込めないファイル形式

IMPORTRANGE関数では、.csvや.xlsxのような他形式のファイルをそのまま取り込むことはできません。

Google スプレッドシート以外の形式で保存されたファイルを参照したい場合、まずはファイルをGoogle スプレッドシート形式に変換する必要があります。

ファイルのスプレッドシートへの変換方法

• CSVファイルの場合
  1. Google ドライブ上で対象ファイルを右クリック
  2. 「アプリで開く」→「Google スプレッドシート」を選択
• Excelファイルの場合

1. Google スプレッドシートで該当ファイルを開く
2. 「ファイル」→「Google スプレッドシートとして保存」を選択

大量のファイルを一度に変換する場合は、GASを利用して自動化することも可能です。

いずれの方法で変換した場合でも、変換後は元のファイルとは別のファイルになる（URLが変わる）点にも留意してください。

IMPORTRANGE関数の注意点2：大量のデータを扱うとパフォーマンスが低下

IMPORTRANGE関数で大量のデータを読み込むと、スプレッドシート全体の動作が重くなりがちです。特に、複数のIMPORTRANGE関数を同一シート内で使用している場合や、参照元のスプレッドシート自体が重い場合は注意が必要です。

対処方法

1. 参照元でデータ量を絞ったシートを作成

IMPORTRANGE関数で参照するスプレッドシート側で、参照専用のデータ量を絞ったシートを作成しておきます。例えば、必要な列だけを抽出したり、集計済みのデータのみを抽出したりすることで、読み込むデータ量を削減できます。

2. GASでデータを転記

IMPORTRANGE関数を使わず、GASでデータの転記処理を行うことも有効です。GASで転記すると、IMPORTRANGE関数とは異なり、転記後は値の再読み込みが発生しないため、シートを軽量化できます。ただし、リアルタイム性はIMPORTRANGE関数に劣るため、定期的にGASを実行する仕組みづくりなどの工夫も必要になります。

補足：空白セルの「""」と「 」の違い

IF関数の結果やIFERROR関数のエラー値、XLOOKUP関数の結果が見つからなかった場合の値などで 「空白」 を返したい場合、「""」（ダブルクォーテーション2つ）と「 」（何も入力しない）のふた通りの方法があります。

• 「""」
  • 例：IF(条件, "", "")
  • 「空の文字列」 が存在することになります。
  • セルには何も表示されませんが、「長さ0の文字列」というデータが存在します。
• 「 」
  • 例：IF(条件, , )
  • 「値が存在しない」 状態です。
  • セルは完全に空で、データは何も存在しません。

この違いは、ISBLANK関数で判別できます。

ISBLANK関数の結果が FALSE になる場合は、指定したセルやデータに「空の文字列」が入っており、TRUE になる場合は「値が存在しない」状態です。

• A1セルに =""（空の文字列）を入力、A2セルは何も入力しない場合
  • ISBLANK(A1) → FALSE （空の文字列が存在するため、空白ではない）
  • ISBLANK(A2) → TRUE （値が存在しないため、空白）

ISBLANK関数での「””」と「 」の判別

通常の使い方では、この二つの違いはあまり気にならないかもしれません。

しかし、以下のようなケースではこの違いによる影響が出ることがあります。

• ARRAYFORMULA関数との組み合わせ： 例えば、「A列が空白なら何も表示しない」という数式で「""」を使うと、シートの最下行まで空の文字列が存在することになり、不要なデータが増えてしまう可能性があります。
• IMPORTRANGE関数やINDIRECT関数でのデータ処理： データの取得範囲や集計結果に影響を与える場合があります。
• GASでのデータ取得： データ範囲の判定（Sheet.getLastRow() や Sheet.getDataRange() など）に影響を与える場合があります。
• スプレッドシートのセル数上限： 「""」はデータとしてセル数を消費するため、大量に使うと上限に達しやすくなります。

明確に空白セルを「""」（空の文字列）として扱いたい理由がない限り、基本的には「 」（何も入力しない）を使う方が無難です。

特に、ARRAYFORMULA関数と組み合わせる場合や、大量のデータを扱う場合は、「 」を使うようにしましょう。

3. INDIRECT関数：参照先を動的に変更！　ただしデータの更新に問題も？

INDIRECT関数とは？

INDIRECT関数は、文字列で指定されたセルや範囲を「実際の参照」に変換する関数です。

参照先を直接指定するだけではなく、セルに入力された文字列や数式の結果も参照先として指定できるため、参照先を柔軟に切り替えたり自動生成したりできる点が特徴です。

例として、月ごとにシートが追加されるスプレッドシートで、特定の月のデータを集計したいケースを考えてみましょう。

A1セルに「2025-04」のような年月が入力されており、その月のシート名が「2025-04」である場合、INDIRECT関数を使うと、A1セルの日付をもとに対象月のシートからデータを取得できます。

=INDIRECT(TEXT(A1, "yyyy-mm") & "!A1:C1")

この数式によって以下の処理が行われ、数式内にシート名を直接記述しなくても、日付から対象月のシートを指定することができます。

1. TEXT(A1, "yyyy-mm") でA1セルの日付を「2025-04」形式の文字列に変換
2. & "!A1:C1" でシート名とセル範囲を結合
3. INDIRECT(...) で文字列が実際の参照に変換され、「2025-04」シートのA1:C1のデータを取得

INDIRECT関数の使用例

INDIRECT関数の注意点：参照先の更新が遅れることがある

INDIRECT関数を使う上で注意したいのが、参照先のシートが変更された際の更新タイミングの遅延です。

例えば、=INDIRECT(A1 & "!A:C") という数式で、A1セルに入力された名前のシートを参照しているとします。

INDIRECT関数を入力した時点で、対象の名前のシートが存在しない状態だと、参照先が存在しないため #REF! エラーが表示されます。

これ自体は想定通りの挙動ですが、その後対象の名前のシートを新規に追加しても、INDIRECT関数が再計算されずエラー状態が続いてしまう場合があります。

この問題はシートや関数の組み合わせ、操作手順によって発生しない場合もありますが、特に大規模なシートや複雑な数式を扱う際は発生しやすいので注意が必要です。

対処方法

1. INDIRECT関数を含むセルをコピー＆ペーストし直す

INDIRECT関数の更新の遅延が発生した場合、最も簡単な対処法は、INDIRECT関数を含むセルを Ctrl + C → Ctrl + V で同じ場所に貼り付け直す方法です。この操作を行うことで、INDIRECT関数が再計算され、最新の状態に更新されることがあります。

削除してやり直し（Delete/Backspace → Ctrl + Z）では再計算されない可能性があるので、コピー＆ペーストをお試しください。

2. GASでデータを転記

IMPORTRANGE関数と同様に、シート参照が動的に変わるような処理をGASでまとめて行う方法もあります。

リアルタイム性は少し下がりますが、関数の再計算や読み込み速度にまつわるトラブルを回避できるメリットもあります。

3. TODAY関数やNOW関数と組み合わせて強制再計算

INDIRECT関数を含む数式に、TODAY関数やNOW関数など、定期的に再計算される関数を組み込むことで、INDIRECT関数を強制的に再計算させる方法もあります。

=IF(NOW() > 0, INDIRECT("Sheet1!A1"), )

この例では、IF(NOW() > 0, ...) とすることで、NOW関数の再計算に合わせてINDIRECT関数の再計算も促すことができます。

ただし、この方法は再計算が頻発することでシートが非常に重くなる可能性があるため、注意が必要です。

極力、上述した他のいずれかの方法で対処し、どうしても必要な場合のみ使うことをお勧めします。

4. QUERY関数：データ抽出・集計の万能選手！　ただし扱うデータには要注意

QUERY関数とは？

QUERY関数は、Google Visualization API Query Languageという言語を使って、スプレッドシートのデータに対して抽出、並べ替え、集計などの高度なデータ操作が行える関数です。スプレッドシートのピボットテーブルやフィルター機能を関数で実現できるようなイメージ、と考えると分かりやすいでしょう。

例えば、日付ごとの商品名・単価・販売数量が入力されたデータがあり、商品ごとの合計金額を計算したい場合、QUERY関数を使うと以下のように記述できます。

=QUERY(A:D, "select B, C * sum(D) where A is not null group by B, C label C * sum(D) '合計金額'")

この数式では、以下のような処理が行われています。

1. A:D で対象範囲を指定
2. クエリで以下の処理を実行

• select B, C * sum(D)：B列（商品名）と、C列（単価）にD列（販売数量）を掛けた金額を取得
• where A is not null：A列が空白の行を除外
• group by B, C：商品ごと（B列の商品名とC列の単価）に計算結果をグループ化
• label C * sum(D) '合計金額'：C列×D列の合計の計算結果の列名を変更

QUERY関数の使用例

QUERY関数の注意点1：列の指定方法が状況によって変わる

QUERY関数では、列の指定方法に注意が必要です。

QUERY関数のデータ範囲としてどのような範囲を指定したかによって、列の指定の記述方法が変化します。

• 同じスプレッドシート内の範囲を対象とする場合：select A, B のように列名（アルファベット） で指定
• 別の関数で処理した範囲などを対象とする場合：select Col1, Col2 のように列番号（Col1, Col2, ...） で指定

QUERY関数の列指定方法の違い

この仕様を利用すれば、たとえば "select Col" & A1 & "..." のように参照する列を動的に変更することも可能ですが、慣れないうちは混乱しやすいので注意しましょう。

QUERY関数の注意点2：データ型が混在すると一部のデータが除外される

QUERY関数は、同じ列に複数のデータ型（数値や文字列など）が混在している場合、少数派のデータ型が排除されるという動作をします。

例として、以下のようなデータがある列をQUERY関数のデータ範囲に指定した場合を考えてみましょう。

110, 120, 130, "140円", "１５０", 160, "170", 180, 190, 200

このデータに対してQUERY関数を実行すると、"140円", "１５０", "170" は文字列と判断され、結果から除外されてしまいます。

文字列と判定されるケース

• "140円"： 「円」という文字が含まれているため、文字列として扱われます。
  • セル内に直接「円」が入力されておらず、表示形式で「#,##0円」のように指定されている場合は数値として扱われます。
• "１５０"： 全角数字は文字列として扱われます。
• "170"： 半角数字でも、表示形式が「書式なしテキスト」になっている場合は、文字列として扱われます。

QUERY関数のデータ型不一致によるデータ除外

対処方法

1. データ型をチェックする

QUERY関数での集計時にデータ型の問題を避けるためには、データをQUERY関数にかける前に、元データに数値以外の値が含まれていないか確認しておくことが重要です。

例えば、ISNUMBER関数を使うことで、数値以外の値が混在していないか検証することができます。

さらにそこへCOUNTIF関数を組み合わせ、ISNUMBER関数の判定結果に FALSE が何個あるかを数えることで、問題のあるデータの数を把握できます。

空白セルも許容したい場合は、=OR(ISNUMBER(A1), ISBLANK(A1)) として空白もチェックするか、TYPE関数を使うことで確認できます（TYPE関数は数値・空白セルともに戻り値が「1」となるので、数値と空白をまとめて判定できます）。

これらの方法で問題のあるデータが見つかったら、該当の値を修正してデータの整合性を確保しましょう。

2. データ型を統一する

データ型を確認するほかに、QUERY関数の対象範囲として、=ARRAYFORMULA(TO_TEXT(範囲)) のように、TO_TEXT関数で範囲内のデータをすべて文字列化する方法もあります。

=QUERY(ARRAYFORMULA(TO_TEXT(A:C)), "select Col1, Col2 where Col3 is not null")

この方法では、QUERY関数はすべてのデータを文字列として扱うため、データ型の不一致は発生しません。

ただし、以下のようなデメリットもあるため、QUERY関数で数値の計算や集計を扱いたい場合は注意が必要です。

• 数値が文字列になる： QUERY関数内で数値の集計を行う場合や、QUERY関数の結果を他の関数で参照する場合は、文字列から数値への再変換が必要です。
• 意図しない文字列も扱われる： 本来数値であるべき箇所に意図しない文字列が入力されていても、そのまま扱われてしまいます。不正なデータが紛れ込まないか注意が必要です。

3. QUERY関数を使わずに他の方法で代用する

QUERY関数にこだわらず、他の関数で実現できないかも検討しましょう。

QUERT関数はこれ一つで多種多様な処理をまとめて行える半面、クエリの記述が複雑になりやすいという弱点もあります。

例えば以下に挙げるような特定の目的のためだけにQUERY関数を使っているのであれば、それに適した関数を使用したほうがシンプルに実現でき、メンテナンス性も高くなりやすいです。

• 空白データの除外 → FILTER関数で代替
  • QUERY関数：=QUERY(A:B, "select * where A is not null")
  • FILTER関数：=FILTER(A:B, A:A <> "")
• グループ化して集計 → UNIQUE関数と条件付き集計関数（SUMIF関数など）で代替
  • QUERY関数：=QUERY(A:B, "select A, sum(B) group by A")
  • UNIQUE関数 + SUMIF関数：
    • =UNIQUE(A:A) （A列の重複しないリストを作成）
    • =SUMIF(A:A, <UNIQUEの結果>, B:B) （SUMIF関数で、UNIQUE関数の結果を検索条件として、B列の合計を計算）

まとめ：スプレッドシート関数の落とし穴に注意し、効率的なデータ集計を！

本記事では、Google スプレッドシート関数のうちARRAYFORMULA関数・IMPORTRANGE関数・INDIRECT関数・QUERY関数の4種を取り上げ、それぞれの特徴と落とし穴、対策方法を紹介しました。

Google スプレッドシートを使いこなせば、集計や分析に費やす時間を大幅に減らせます。しかし、今回挙げたような注意点を把握していないと、思わぬエラーや集計ミスに悩まされるかもしれません。

ぜひ本記事の内容を活用していただき、スプレッドシートの機能を最大限に活かしてみてください。