ExcelのSUBTOTAL、9と109は何が違う？非表示にした行の合計を見よう

※本記事には広告・アフィリエイトリンクが含まれます。

行を隠したのに、合計が減らない……。
ExcelのSUBTOTAL関数なら、先頭に入れた数字を見てみて。

合計を求める「9」は、手動で非表示にした行も含めます。「109」は、その行を除外します。ただし、フィルターで条件から外れた行は、どちらも集計しません。

同じ「画面にない行」でも、隠れた理由で扱いが違うんです。ここを分けると、数字の変化を追いやすいよ！

SUBTOTALは、指定範囲の合計や平均などを求める関数です。ここでは合計だけに絞り、Microsoft 365・Excel 2024などのデスクトップ版で使う、縦に並んだ金額を例にします。

9と109は「手で隠した行を足すか」で選ぶ

フィルターは、見出しの矢印から条件に合う行だけを表示する機能。行番号を選んで手動で非表示にする操作とは区別します。

B2からB4までの金額を足すなら、数式は次の二つです。コロンの付いた B2:B4 は、B2〜B4の連続した範囲を表します。

先頭の9・109は、足す個数ではありません。どの方法で集計するかを指定する番号です。どちらも足し算で、手動の非表示をどう扱うかが違います。

「画面に出している明細だけを足したい」なら109が候補。一方、説明のために一時的に隠した明細も合計へ残すなら9、と目的から選ぼう。

3行の金額で、結果を順番に比べよう

以下は説明用の小さな例です。空のシートに、A1を「区分」、B1を「金額」として入力します。

B6に =SUBTOTAL(9,B2:B4)、B7に =SUBTOTAL(109,B2:B4) を入力。全部の行を表示している状態では、どちらも6000になります。

次にA1:B4を選び、「データ」→「フィルター」。区分の矢印で「対象」だけを選んで確定します。2000の行が条件から外れるので、両方とも4000です。

あれ、109にしてもしなくても同じ？
うん、この段階では同じ！ フィルターで外れた行は、両方とも数えないからです。

さらに4行目の行番号を選び、「ホーム」→「書式」→「非表示/再表示」→「行を表示しない」で隠します。すると9の式は4000のまま、109の式は1000に。4行目の3000を数えるかどうかが、ここで分かれます。

横方向の合計には、この説明をそのまま使わないでね

SUBTOTALは縦方向の範囲の集計を前提としています。横に並ぶ B2:G2 を指定し、途中の列を非表示にしても、109だからその列が除外される、とはなりません。

関数の使い分けを紙で引ける資料が欲しい場合は、関数の基礎知識・数学／三角関数などを収録する『できるポケット 時短の王道 Excel関数全事典 改訂4版』も候補です。対応版や目次を見て選んでね。

Excel関数全事典 改訂4版・ISBN9784295021186を楽天市場で見る

手元の集計が合わないときは、まず先頭の番号と、行を隠した方法へ。109に変える前に「この明細は合計から外してよい？」と一つ確かめると、意図まで揃えられます。

出典：Microsoft・SUBTOTAL関数、Microsoft・範囲のフィルター、インプレス・書籍案内