ExcelのSUBTOTAL関数の9についてお探しですね。

広告

ExcelのSUBTOTAL関数「9」って何？109との違いも分かりやすく解説

Excelで「=SUBTOTAL(9,B2:B10)」という数式を見て、「なんでSUMじゃないの？」「9って何？」と思ったことはありませんか？SUBTOTAL関数は、合計や平均、個数などいろんな集計を番号で指定できる、ちょっと変わった関数です。

特に「9」と「109」はどちらも合計を出す番号なんですが、非表示の行をどう扱うかが違うので、使い分けを間違えると思ってた数字と違う結果になることも。

この記事では、SUBTOTALの「9」が何を意味するのか、番号の一覧、109との違い、実際の使い分けまで分かりやすく説明します。

1. SUBTOTAL関数の「9」って何？合計を求める番号です

ExcelのSUBTOTAL関数で使う「9」は、指定した範囲の合計を計算するための番号です。

例えば「=SUBTOTAL(9,B2:B10)」と入力すると、B2からB10までの数字を全部足してくれます。

パッと見は「=SUM(B2:B10)」と同じような結果になりますが、SUBTOTALはフィルターをかけた表や小計がある表で本領を発揮します。

ただの合計ならSUMで十分ですが、絞り込んだ後の表示されてる行だけを集計したいときや、小計を含む表で二重に計算しちゃうのを防ぎたいときには、SUBTOTALがすごく便利なんです。

SUBTOTAL関数の基本的な書き方は「=SUBTOTAL(集計方法,範囲1,[範囲2],…)」です。

最初の「集計方法」のところに、合計なら9、平均なら1、個数なら2みたいに番号を指定します。

つまり「9」は関数の名前じゃなくて、「SUMと同じ合計の処理をしてね」っていう指示番号なんです。

Excelに慣れてないと、この番号で指定するのが分かりにくいかもしれませんが、「SUBTOTALっていう万能な集計関数に、どの集計をさせるかを番号で伝える」って考えると分かりやすいですよ。

例えば売上表で、部署ごとにフィルターをかけながら合計を確認したい場面を考えてみましょう。

SUM関数だと、フィルターで一部の行を隠しても、隠れた行も含めて合計されちゃいます。

でもSUBTOTAL関数で「=SUBTOTAL(9,売上範囲)」って設定しておけば、フィルターで表示されてる行だけを対象に合計が自動で計算し直されるんです。

この性質があるから、SUBTOTALの「9」は、ただの合計じゃなくて「フィルターに連動できる合計」って覚えておくと実際に使うときに便利です。

2. SUBTOTAL関数の番号一覧｜1〜11と101〜111の対応表

SUBTOTAL関数では、集計方法を1〜11か101〜111の番号で指定します。

1〜11と101〜111は、どっちも平均・個数・最大値・最小値・合計なんかの集計をする番号なんですが、手動で非表示にした行を含めるかどうかが違います。

まずは、どの番号がどの集計方法なのかを押さえておきましょう。

特に普段よく使うのは、平均の1か101、数値の個数の2か102、データの個数の3か103、最大値の4か104、最小値の5か105、合計の9か109です。

| 非表示行を含む | 非表示行を除外 | 集計方法 | 同じような関数 |
|—:|—:|—|—|
| 1 | 101 | 平均を求める | AVERAGE |
| 2 | 102 | 数値の個数を数える | COUNT |
| 3 | 103 | 空白以外のデータ数を数える | COUNTA |
| 4 | 104 | 最大値を求める | MAX |
| 5 | 105 | 最小値を求める | MIN |
| 6 | 106 | 積を求める | PRODUCT |
| 7 | 107 | 標本の標準偏差を求める | STDEV.S |
| 8 | 108 | 母集団の標準偏差を求める | STDEV.P |
| 9 | 109 | 合計を求める | SUM |
| 10 | 110 | 標本の分散を求める | VAR.S |
| 11 | 111 | 母集団の分散を求める | VAR.P |

この表を見ると分かるように、「9」と「109」はどっちも合計を求める番号です。

違いは集計方法そのものじゃなくて、手動で非表示にした行を計算に入れるかどうか。

ちなみに、フィルターで非表示になった行は、1〜11でも101〜111でも、SUBTOTALでは基本的に集計の対象から外されます。

ここがSUM関数との大きな違いです。

SUMはフィルターで隠れた行も合計しちゃいますが、SUBTOTALはフィルター結果に合わせて表示されてる行だけを集計できるんです。

あと、SUBTOTAL関数には大事な特徴があって、集計範囲の中に別のSUBTOTAL関数があると、そのSUBTOTALの結果を自動で無視して二重計算を防いでくれます。

例えば部署ごとの小計をSUBTOTALで出して、さらに全体の合計もSUBTOTALで出す場合、全体合計の範囲に小計行が入ってても、小計行を重複して足さないように処理してくれるんです。

売上表や経費一覧みたいに、小計と総計が混ざってる表では、この性質を知ってるだけで集計ミスをぐっと減らせますよ。

3. 「9」と「109」の違い｜手動で非表示にした行を含めるか除外するか

SUBTOTAL関数の「9」と「109」の違いは、手動で非表示にした行を合計に含めるかどうかです。

「=SUBTOTAL(9,範囲)」は、フィルターで非表示になった行は除外しますが、行番号を右クリックして「非表示」にした行や、グループ化で折りたたんだ行は合計に含めます。

一方、「=SUBTOTAL(109,範囲)」は、フィルターで非表示になった行だけじゃなく、手動で非表示にした行も合計から除外します。

つまり、表示されてる行だけをしっかり合計したいなら109を選ぶのが基本です。

具体例で考えると分かりやすいです。

B2:B10に売上金額があって、B5:B7の行を手動で非表示にしたとします。

このとき「=SUBTOTAL(9,B2:B10)」だと、非表示にしたB5:B7の金額も合計に含まれます。

でも「=SUBTOTAL(109,B2:B10)」だと、非表示にしたB5:B7の金額は合計されません。

画面に見えてるデータだけを集計したいと思ってる場合、「9」を使うと思ってたより大きい合計になることがあるので注意が必要です。

ただ、フィルターで行が非表示になった場合は、「9」でも「109」でも表示されてる行だけが集計されます。

ここがちょっとややこしいところ。

フィルターだけを使う表なら、9と109の結果は同じになることが多いので、どっちでも問題ないように見えます。

でも、途中で行を手動で非表示にしたり、アウトラインのグループ化で一部を折りたたんだりする可能性がある表では、109を使った方が思った通りの集計になりやすいです。

特に他の人と共有するファイルだと、誰かが手動で行を隠すこともあるので、表示行だけの合計を重視するなら109を選ぶと安全ですよ。

ただし、いつも109が正解ってわけじゃありません。

例えば、明細行の一部を作業上の理由で一時的に非表示にしてるだけで、合計には含めたい場合は9の方が適してます。

逆に、非表示にした行を「集計対象外」として扱いたいなら109が適してます。

大事なのは、非表示の行を「見えないだけのデータ」と考えるのか、「集計から外したいデータ」と考えるのかを先に決めること。

この判断をしてから9と109を使い分けると、集計結果の意味がはっきりします。

4. 実際の使い分けと注意点｜SUBTOTALの「9」はどんな場面で使うべき？

実際の仕事でSUBTOTAL関数の「9」を使うべき代表的な場面は、フィルターで絞り込んだ結果に合わせて合計を変えたいときです。

売上一覧から特定の担当者だけを表示したり、月別・地域別・商品カテゴリ別に絞り込んだりする表では、SUM関数よりSUBTOTAL関数の方が便利。

SUMは常に範囲全体を合計しちゃいますが、SUBTOTAL(9,範囲)ならフィルター後の表示行だけを集計してくれます。

だから、同じ表をいろんな条件で確認する営業管理表、在庫表、経費一覧、アンケート集計なんかで役立ちます。

あと、小計行と総計行が混ざってる表でもSUBTOTAL関数は便利です。

部署ごとの小計をSUBTOTALで計算して、最後に全体合計もSUBTOTALで出せば、範囲内にある小計行を自動で無視してくれます。

これをSUM関数でやると、明細の合計に加えて小計行まで足しちゃって、二重に計算することがあります。

特に、複数のグループごとに小計を置いた表では、総計にSUMを使うよりSUBTOTALを使った方が安全。

「小計も総計もSUBTOTALで統一する」って覚えておくと、表の構造が変わったときにもミスを防ぎやすくなります。

9と109の選び方は、こんな感じで整理できます。

フィルターだけで絞り込む表なら9でも109でも表示行だけが合計されますが、手動非表示やグループ化を使う可能性があるなら109を優先するといいです。

一方で、手動で非表示にした行も正式な集計対象として残したいなら9を使います。

迷ったときは、「今見えてる行だけを合計したいのか」「非表示でもデータとして含めたいのか」を基準にしてみてください。

– **フィルター結果に連動した合計を出したい**：SUBTOTAL(9,範囲)またはSUBTOTAL(109,範囲)
– **手動で非表示にした行も合計したい**：SUBTOTAL(9,範囲)
– **手動で非表示にした行を合計したくない**：SUBTOTAL(109,範囲)

注意点として、SUBTOTAL関数は縦方向のリスト集計で使うことを前提に考えると分かりやすい関数です。

行を非表示にしたときは9と109の違いが出ますが、横方向の範囲で列を非表示にした場合には、期待通りに除外されないことがあります。

例えば「=SUBTOTAL(109,B2:G2)」みたいに横一列を集計して列を非表示にしても、行の非表示と同じ感覚では扱えません。

列方向の表示・非表示を厳密に反映したい場合は、別の設計や関数を検討する必要があります。

さらに、エラー値を除外したい、中央値や順位を求めたいなど、SUBTOTALより細かい制御が必要な場合はAGGREGATE関数が候補になります。

AGGREGATE関数は、非表示行やエラー値を無視する設定をもっと細かく指定できます。

ただ、SUBTOTAL関数に比べるとちょっと読み解きにくいので、共有ファイルでは関数の意味が分かるようにメモを入れると親切です。

まずはSUBTOTALの9と109を正しく使い分けて、必要に応じてAGGREGATE関数やピボットテーブルを検討する流れが実際の仕事では扱いやすいでしょう。

まとめ

Excel SUBTOTAL関数の「9」は、合計を求めるための番号です。

ただし、SUM関数と同じ単純な合計じゃなくて、フィルターに連動して、小計の二重計算も避けられる点に価値があります。

そして「109」は、9と同じく合計を求める番号だけど、手動で非表示にした行も除外できる点が違います。

フィルター中心なら9、表示されてる行だけをしっかり集計したいなら109、って考えると使い分けがしやすくなります。

表の見た目じゃなくて、「どの行を集計対象にしたいのか」を基準に選ぶことが、正確な集計への近道です。

広告