【スプレッドシート】AGGREGATE関数のようにエラーを無視して集計する方法

Excelで「非表示の行」や「エラー値」を無視した集計に便利なAGGREGATE関数ですが、Googleスプレッドシートには同じ機能を持つ関数がありません。そこでこの記事では、いくつかの関数を組み合わせてAGGREGATE関数と同じような集計を行う方法を解説します。

はじめに

ExcelのAGGREGATE関数は、「非表示の行」や「エラー値」を無視した集計（オプション3）に便利ですが、GoogleスプレッドシートにはAGGREGATE関数がありません。

例：=AGGREGATE(9,3,B2:B5)

AGGREGATE関数の引数：(集計コード, オプション, 範囲 1, [範囲 2], …)

エラーを無視して集計したい場合、基本的に元のエラー値の方を、「空白」や「0」に変換しておくことが推奨されます。

たとえば、VLOOKUP関数を使って「商品名」から「価格」を抽出する数式で、エラーを「空白」にしたい場合、次のようにIFERROR関数を組み合わせます。

例：=IFERROR(VLOOKUP(A2,$H$2:$I$5,2,FALSE),"")

このように計算の元となるデータからエラーを取り除いておくことで、そのままSUM関数で合計したり、SUBTOTAL関数で非表示の行を含まない集計をしたりできます。

SUBTOTAL関数の引数：(関数コード, 範囲1, [範囲2, …])

計算元のデータにエラー値がある状態で、エラーを無視した集計をしたい場合、SUM関数とIFERROR関数を組み合わせます。

たとえば、エラー値を含むB列の範囲をSUM関数で合計したい場合、数式は次のようになります。

例：=SUM(IFERROR(B2:B5,""))

ただし、この方法は「非表示の行を含まない、エラーを無視した集計」には対応していません。

「エラーのセル」と「非表示のセル」を両方無視して集計したい場合、フィルター機能を使うのが最も簡単な方法です。

フィルター作成と集計の手順

フィルターを適用する: データ範囲を選択し、「データ」→「フィルタを作成」を選びます。
フィルターで条件を絞り込む: フィルターアイコンを開き、「値でフィルタ」を使ってエラー値（#N/Aなど）のチェックを外して絞り込みます。
SUBTOTAL関数で集計する: 表示されているセルだけがSUBTOTAL関数の集計対象になります。例：B列の合計を出す =SUBTOTAL(109, B2:B5)

ポイント： 109は「非表示の行を含まない合計」のコードです。フィルターでエラのセルを非表示にすることで、SUBTOTAL関数がそれらの行を無視して計算します。

「非表示のセル」と「エラーのセル」を両方無視した集計を、数式だけで完結させたい場合は、作業列（例：D列）を作成することで実現します。

ステップ1：非表示行とエラー値を除外する「作業列」を作る

まず次のような数式を入力し、データがある行まで下へオートフィルします。

例：=IFERROR(SUBTOTAL(109,B2),0)

この数式では、SUBTOTAL関数を使って、表示されている行の値だけを抽出し、もし、SUBTOTALが返す値がエラーだった場合、IFERROR関数でエラー値を0に変換します。

ステップ2：作業列の値を合計する

次に、ステップ1で作成した作業列（D列）の値を合計します。

例：=SUM(D2:D5)

ポイント：SUM関数は非表示の行も合計しますが、作業列（D列）がすでに非表示の行の値を0に変換しているため、0として計算されます。

ExcelのAGGREGATE関数のような機能を持つ関数がない場合でも、複数の関数を組み合わせることで、「エラーと非表示の行を除いた集計」を実現できます。