SUBTOTAL 関数は、リスト (一覧形式の表、テーブル) のデータの集計値を返す関数です。
「SUBTOTAL 関数を使う 1」の続きです。

構文：＝SUBTOTAL(集計方法，セル範囲)

「(合計なのか、平均なのか、個数なのかなど) 指定した方法で」「この列 (セル範囲) を計算してね」と指定しますが、集計方法には大きく分けて 2 つの数字を指定することができ、これは、非表示のセルを計算に含むかどうかで選びます。(この辺はその 1 でご覧ください。)

SUBTOTAL 関数では非表示になっているセルを計算に含めるかどうかを制御できることをご紹介しましたが、今回は手動の設定による「非表示」ではない、もう 1 つの「非表示」と SUBTOTAL 関数の組み合わせについてご紹介します。

 

範囲内に小計が含まれるリストでの計算

非表示のお話の前に。

SUBTOTAL関数で指定しているセル範囲の中に、SUBTOTAL関数による数式のセルが含まれる場合は、このセルの値は計算から除外されます。

たとえば、下図の点線で囲まれているセル (E19、E24、E26、E30、E33、E36) には、それぞれ SUBTOTAL関数を使ってエリアごとの金額の小計を算出する数式が作成されています。

セル E37 の数式で「E11:E36」をセル範囲として指定していますが (とびとびで 1 つずつセルを指定していませんが)、点線で囲まれているセルの値は計算から除外されています。

これも SUBTOTAL 関数の特徴の 1 つです。

01.png

 

 

グループ化と組み合わせて使う

ワークシートの行や列をグループ化すると、アウトラインによって明細などを折りたためます。たとえば、行をグループ化するとこんな感じ↓。

間に小計が含まれるようなリストで、明細部分を折りたたんで利用できます。

02.png

「明細が折りたたまれている」というのは「明細が非表示になっている」ということです。

もともと、SUBTOTAL 関数による数式では、範囲内にある SUBTOTAL 関数の数式のセルは計算から除外されますが、集計方法として「9」(非表示も含んで合計する) を指定すると、非表示になっている (折りたたまれている) セルは計算に含まれるため、下図では、ピンク色のセルの値が合計されています。

SUBTOTAL 関数で計算に含む / 含まないを制御する「非表示」とは、手動による非表示だけでなく、グループ化によって設定されたアウトラインによって折りたたまれているセルも該当する、ということです。

SUBTOTAL 関数はグループ化によるアウトラインの設定と、とても相性がいいです。
03.png



 

行のグループ化とアウトラインの設定

本題ではないけれど簡単に行のグループ化の設定方法を書いておきます。

ここまでも使ってきたリストですが、下図のピンク色の部分を折りたためるようにグループ化を設定してみます。やり方は、1 つずつグループを設定するか、大きなグループを作って小計部分をグループから除外します。

04.png



■　1 つずつグループ化

  1. 1 つ目のグループとしたい行を選択して、リボンの [データ] タブの
    [アウトライン] グループの [グループ化] をクリックします。

    いっぺんに全部のグループは作れないので、1 つずつグループ化していきます。
    05.png
     
  2. 選択している行がグループ化され、アウトラインが表示されます。
    06.png
     
  3. 同じようにほかの行もグループ化の設定をします。

    今回の例ではは A からF までの 6 つのエリアがあるので、グループ化の作業を 6 回やりました。
    07.png
     

■　グループの解除を使ってグループを分割

今回のような間に小計が含まれていて、この行はグループに含まないようなとき、グループの解除機能を使って小計部分をグループから除外することで結果的に複数のグループを作ることもできます。

  1. グループ化するすべての行を選択して、リボンの [データ] タブの [アウトライン] グループの [グループ化] をクリックします。

    今回の場合は、11 行目から 36 行目までを選択して 1 つのグループにしています。
    08.png
     
  2. 選択した行がグループ化されたら、グループから除外したい行を選択して、リボンの [データ] タブの [アウトライン] グループの [グループ解除] をクリックします。
    09.png
     
  3. 選択した行の位置でグループ化が解除されます。
    言い換えると、この行を境に大きな 1 つのグループが 2 つのグループに分かれました。
    10.png
     
  4. 同じように、ほかの小計行を選択してグループ化を解除して、グループを分けます。

    今回は赤枠の行でグループ化の解除を実行しました。
    11.png


ちなみに、すべてのグループ化を解除してアウトラインの設定をクリアするには、リボンの [データ] タブの [アウトライン] グループの [グループ解除] の▼をクリックして、[アウトラインのクリア] をクリックしてください。

と、ここまで書いておいてなんですが、小計行の作成とグループ化とアウトラインの設定をまとめて実行できる機能があるんです。それについては こちら でどうぞ。

今回のようなリストの場合はそもそもこっちの機能を使ったほうがよいような気もしますが、SUBTOTAL 関数の解説用にあえて機能は使いませんでした。

 

SUBTOTAL 関数はいくつかの特徴がある関数です。非表示の制御ができるとか、範囲に SUBTOTAL 関数の数式がある場合は計算から除外してくれるとか。

いずれにしても、関数だけ知っていてもだめってことですね。ほかの操作や設定と組み合わせてこそ、だと思いますし、そこにその関数を使う理由があるのだと思います。


石田 かのこ