※本記事には広告・アフィリエイトリンクが含まれます。
行を隠したのに、合計が減らない……。
ExcelのSUBTOTAL関数なら、先頭に入れた数字を見てみて。
合計を求める「9」は、手動で非表示にした行も含めます。「109」は、その行を除外します。ただし、フィルターで条件から外れた行は、どちらも集計しません。
同じ「画面にない行」でも、隠れた理由で扱いが違うんです。ここを分けると、数字の変化を追いやすいよ！
SUBTOTALは、指定範囲の合計や平均などを求める関数です。ここでは合計だけに絞り、Microsoft 365・Excel 2024などのデスクトップ版で使う、縦に並んだ金額を例にします。
9と109は「手で隠した行を足すか」で選ぶ
フィルターは、見出しの矢印から条件に合う行だけを表示する機能。行番号を選んで手動で非表示にする操作とは区別します。
B2からB4までの金額を足すなら、数式は次の二つです。コロンの付いた B2:B4 は、B2〜B4の連続した範囲を表します。
| 数式 | 手動で非表示の行 | フィルターで外れた行 |
|---|---|---|
=SUBTOTAL(9,B2:B4) |
含む | 除外する |
=SUBTOTAL(109,B2:B4) |
除外する | 除外する |
先頭の9・109は、足す個数ではありません。どの方法で集計するかを指定する番号です。どちらも足し算で、手動の非表示をどう扱うかが違います。
「画面に出している明細だけを足したい」なら109が候補。一方、説明のために一時的に隠した明細も合計へ残すなら9、と目的から選ぼう。
3行の金額で、結果を順番に比べよう
以下は説明用の小さな例です。空のシートに、A1を「区分」、B1を「金額」として入力します。
| 行番号 | A列・区分 | B列・金額 |
|---|---|---|
| 2 | 対象 | 1000 |
| 3 | 対象外 | 2000 |
| 4 | 対象 | 3000 |
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に変える前に「この明細は合計から外してよい？」と一つ確かめると、意図まで揃えられます。
