【Googleスプレッドシート】AGGREGATE関数の代替!エラー値を除いた集計

Googleスプレッドシートで集計作業をしていると、エラー値を含むセルがあるためにSUMやAVERAGEが正しく動作しないことがあります。ExcelのAGGREGATE関数ならエラーを無視できますが、GoogleスプレッドシートにはAGGREGATE関数が存在しません。この記事では、AGGREGATE関数の代わりとなる複数の方法を詳しく解説します。エラー値を除いた集計を実現するテクニックを身につけましょう。

AGGREGATE関数がGoogleスプレッドシートに無い理由と代替の考え方

AGGREGATE関数はExcel独自の関数であり、Googleスプレッドシートには実装されていません。この関数はエラー値や非表示行を除外しながらSUM、AVERAGE、COUNTなどの集計を実行できる便利な関数です。Googleスプレッドシートで同様の処理をするには、他の関数や機能を組み合わせる必要があります。代替方法を理解するための鍵は、「エラー値を事前に除去する」か「集計関数自体にエラー耐性を持たせる」かの2つです。本記事では具体例を交えながら、実践的な代替方法を紹介します。

代替方法1:IFERROR関数でエラー値を空白に変換する

最もシンプルな方法は、IFERROR関数を使ってエラー値を空白や0に変換してから集計することです。この方法は既存のSUMやAVERAGEをそのまま使えるため、手軽に導入できます。

手順

1. エラー値を含む列にIFERROR関数を適用する
   例えば、A列にエラー値が含まれている場合、隣のB列に「=IFERROR(A2, “”)」と入力します。これでエラーは空白に変換され、正常な値だけが残ります。
2. 変換後の範囲で集計する
   B列に対してSUMを適用します。空白セルは無視されるため、正しく集計できます。AVERAGEも同様に、空白セルはカウントされません。

注意点

この方法では、元のデータに手を加えることなく新しい列を作成します。データが頻繁に更新される場合は、毎回IFERRORを適用するための列を用意しておくと便利です。ただし、空白に変換した場合、AVERAGE関数は空白セルを無視して平均を計算するため、エラー行を除外したのと同じ結果になります。0に変換すると平均が変わってしまうので注意しましょう。

代替方法2:QUERY関数でエラー値を除外して集計する

QUERY関数はSQLライクな構文でデータを操作でき、エラー値を除外する条件を直接記述できます。集計関数も内包しているため、一度のクエリでエラー除去と集計を同時に行えます。

手順

1. QUERY関数の基本構文を記述する
   「=QUERY(A:B, “select avg(B) where A is not null and B is not null”)」のように、WHERE句でNULLやエラーを除外します。ただし、QUERYではエラー値自体を直接除外する条件はありません。代わりに、データにエラーが含まれている場合は事前にIFERRORで変換するか、または列全体をテキストとして扱う必要があります。
2. より確実な方法: 数値以外を除外する
   QUERYで数値のみを集計するには、「where B is not null and B <> ” and isnumeric(B)」のような条件は使えません。そのため、事前にIFERRORでエラーを空白にしておき、QUERYで空白を除外するのが現実的です。例えば、C列にIFERROR(A2,””)を用意し、「=QUERY(C:C, “select sum(C) where C is not null”)」とします。

注意点

QUERY関数は大文字小文字を区別しますが、エラー処理に関しては特別な関数はありません。大量データに対しては高速ですが、複雑な条件になると可読性が低下します。小規模データであれば十分実用的です。

代替方法3:FILTER関数とSUBTOTAL関数の組み合わせ

FILTER関数でエラー値を除外した動的な範囲を作成し、SUBTOTAL関数で集計します。SUBTOTALはAGGREGATEと同様に非表示行を無視するオプションがありますが、エラー値の除外はフィルタリングに頼ります。

手順

1. FILTER関数でエラー値を除外する
   「=FILTER(A:A, ISNUMBER(A:A))」と入力します。ISNUMBER関数はセルが数値の場合にTRUEを返すため、エラー値や空白が除外されます。
2. SUBTOTALで集計する
   FILTERで得られた範囲をSUBTOTALの引数に指定します。SUBTOTALの第1引数には集計の種類を指定します(例:SUMは9、AVERAGEは1)。例えば、「=SUBTOTAL(9, FILTER(A:A, ISNUMBER(A:A)))」でエラーを除いた合計を計算できます。

注意点

この方法はFILTERが配列を返すため、SUBTOTALが配列を受け取れるかがポイントです。SUBTOTALは通常の範囲を受け取りますが、FILTERの結果は配列として扱われ、SUBTOTALが対応している場合にのみ機能します。最新のGoogleスプレッドシートでは問題なく動作します。また、ISNUMBERは数値のみを抽出するため、日付や時刻も数値として扱われます。必要に応じて条件を調整しましょう。

代替方法4:ARRAYFORMULAとIFERRORの組み合わせ(配列内一括処理)

ARRAYFORMULAを使うと、複数のセルに対して一括でIFERRORを適用し、その結果を直接集計できます。数式が簡潔になり、補助列が不要になるメリットがあります。

手順

1. ARRAYFORMULAでIFERRORをラップする
   「=SUM(IFERROR(A:A, 0))」のように、配列数式としてIFERRORをSUMで囲みます。ARRAYFORMULAは自動的に適用されるため、明示的に書かなくても機能します。
2. エラー値を0として集計する場合
   「=SUM(IFERROR(A:A, 0))」はエラーを0に置き換えて合計します。平均を取る場合は「=AVERAGE(IFERROR(A:A, 0))」ですが、この場合元々のデータ数にエラー行を含めて平均を計算します。注意が必要です。
3. エラー値を無視する場合
   エラーセルを完全に無視するには、「=SUM(IF(ISNUMBER(A:A), A:A))」のようにISNUMBERで条件を指定します。ただし、これは配列数式になるため、Ctrl+Shift+Enterが必要な場合があります(Googleスプレッドシートでは自動配列のため不要)。

注意点

エラーを0に変換すると平均値が変わります。エラー行を除外したい場合は、ISNUMBERと組み合わせるか、FILTER+SUBTOTALを使う方が適切です。大量データではARRAYFORMULAの計算負荷が高くなることがあります。

各代替方法の比較

方法	エラー処理	補助列の必要性	計算速度	柔軟性
IFERROR変換+SUM	空白または0に変換	必要	高速	低い(変換方法が固定)
QUERY関数	NULL除去(事前変換必要)	場合による	高速	高い(SQL構文で条件自由)
FILTER+SUBTOTAL	ISNUMBERで数値のみ	不要	中速	中程度(条件変更に柔軟)
ARRAYFORMULA+ISNUMBER	数値のみ抽出	不要	中速	高い(配列演算で複雑な条件可)

よくある失敗とその対策

IFERRORで0に変換したら平均がおかしくなった

エラーを0に置き換えると、本来集計に含めるべきでないエラー行が0としてカウントされます。平均を取る場合は、エラー行を除外する必要があります。FILTER+SUBTOTALやIFERRORで空白にする方法を選びましょう。

QUERYのWHERE句でエラーが除外できない

QUERYはエラー値そのものを直接除外する構文をサポートしていません。事前にIFERRORで空白に変換してからQUERYを実行するか、代わりにFILTER関数を使うことを検討しましょう。

SUBTOTALでFILTERの結果が正しく処理されない

SUBTOTALは配列を引数に取れる場合と取れない場合があります。Googleスプレッドシートでは2020年以降のアップデートで対応しましたが、古い環境では正しく動かないことがあります。その場合は、SUBTOTALの代わりにSUMやAVERAGE単体で使うか、ARRAYFORMULAと組み合わせてください。

まとめ

GoogleスプレッドシートにAGGREGATE関数は存在しませんが、IFERROR、QUERY、FILTER、SUBTOTALなどの関数を組み合わせることで、エラー値を除いた集計を実現できます。シンプルな集計にはIFERROR変換、より複雑な条件にはQUERYやFILTERを選ぶとよいでしょう。データの特性や更新頻度に合わせて最適な方法を選びましょう。特に定期的なレポート作成では、FILTER+SUBTOTALの組み合わせがおすすめです。