ひとつの関数で複数の計算をまかなえる謎関数、Subtotalのご紹介です。ここではSumとSubtotalの違いについて説明しますが、これがわかるとこの関数の存在意義がわかるかと思います。
Subtotal関数の使い方
指定した計算方法で範囲を集計
Subtotal(集計方法,範囲)
Subtotal(集計方法,範囲)
![subtotal1](https://sunagitsune.com/wp-content/uploads/2020/04/subtotal1.png)
こんな感じの表があるものとします(数字の意味は特に考えてません)。
![subtotal2](https://sunagitsune.com/wp-content/uploads/2020/04/subtotal2.png)
数字の小計を求めたいので、=sum(C1:C7)と入力します。
ちゃんと数字の合計が表示されました。
ちゃんと数字の合計が表示されました。
![subtotal3](https://sunagitsune.com/wp-content/uploads/2020/04/subtotal3.png)
今度は、Subtotal関数で小計を求めて見ます。=subtotal(と入力すると、何やらプルダウンが表示されます。
![subtotal4](https://sunagitsune.com/wp-content/uploads/2020/04/subtotal4.png)
この1,2,3,4..と数字が割り振られているのが集計方法で、Subtotal関数では他にも最大値とか平均とか、色々な計算をこなすことができます。今回は加算をしたいので、「Sum」と書かれた9をダブルクリックします(または9,と入力)
![subtotal5](https://sunagitsune.com/wp-content/uploads/2020/04/subtotal5.png)
あとはSum関数同様範囲を指定し、=SUBTOTAL(9,C1:C7)となった状態でEnterを押します。
![subtotal6](https://sunagitsune.com/wp-content/uploads/2020/04/subtotal6.png)
無事に計算が実行されました。
さて、この関数はSumとどう違うのでしょう? わざわざ覚えて使う意味あるんでしょうか?
さて、この関数はSumとどう違うのでしょう? わざわざ覚えて使う意味あるんでしょうか?
Subtotalと他の関数の違い①非表示項目を集計しない
![subtotal7](https://sunagitsune.com/wp-content/uploads/2020/04/subtotal7.png)
表に対し、挿入タブ→テーブルでテーブル化をしてみます。
![subtotal8](https://sunagitsune.com/wp-content/uploads/2020/04/subtotal8.png)
さらにフィルターをかけ、東京の項目だけが表示されるようにしてみます。
![subtotal9](https://sunagitsune.com/wp-content/uploads/2020/04/subtotal9.png)
小計は先ほど打ちこんだSubtotal関数のままですが、表示された項目だけが合計されていることがわかります。
![subtotal10](https://sunagitsune.com/wp-content/uploads/2020/04/subtotal10.png)
しかしこれをSum関数に打ちなおすと、非表示の項目も合計値に含まれていることがわかります。
Subtotalと他の関数の違い②小計を計算結果に含めない
![subtotal11](https://sunagitsune.com/wp-content/uploads/2020/04/subtotal11.png)
テーブル化を解除し、今度は最初から県と県の間で小計を求めておきます。
計算はsum関数で行います。
計算はsum関数で行います。
![subtotal12](https://sunagitsune.com/wp-content/uploads/2020/04/subtotal12.png)
全ての小計を打ちこみ、これを一番下の合計で合算します。
=sum(C1:C10)のように入れると、小計も合計に含まれ、数字が実際のものより大きくなります。
なので=sum(C4,C7,C10)のようにして、小計を合計するといったやりかたが一般的です。しかし47都道府県全てとなると、かなり視認性の悪い式となります。
一か所間違えただけで直すのも大変ですし、何かの拍子にズレたらまたひとつずつ打ちなおしです。
=sum(C1:C10)のように入れると、小計も合計に含まれ、数字が実際のものより大きくなります。
なので=sum(C4,C7,C10)のようにして、小計を合計するといったやりかたが一般的です。しかし47都道府県全てとなると、かなり視認性の悪い式となります。
一か所間違えただけで直すのも大変ですし、何かの拍子にズレたらまたひとつずつ打ちなおしです。
![subtotal13](https://sunagitsune.com/wp-content/uploads/2020/04/subtotal13.png)
それは少々スマートではないので、今度は小計のSum関数をSubtotal関数に打ちなおしていきます。
![subtotal14](https://sunagitsune.com/wp-content/uploads/2020/04/subtotal14.png)
合計もSubtotal関数で求めます。小計も含め、=subtotal(9,C1:C10)と入力します。
![subtotal15](https://sunagitsune.com/wp-content/uploads/2020/04/subtotal15.png)
小計を抜いた数値=正しい数値が集計されました。
Subtotal関数の参照範囲にSubtotal関数がある場合、これは無視されます。
Sum関数でSubtotal関数を含んだ範囲を含んだ場合、その逆の場合は普通に間違えた結果が出ます。
Subtotal関数の参照範囲にSubtotal関数がある場合、これは無視されます。
Sum関数でSubtotal関数を含んだ範囲を含んだ場合、その逆の場合は普通に間違えた結果が出ます。
このように、最初から集計を見越した作りになっているのがSubtotal関数の特徴です(ちなみにSubtotal=「小計」という意味)。
集計方法がたくさんあるのも、ひとつずつ同様の機能を持ったSubsum関数とか、Subaverage関数とかそういうのを作りたくなかったんだろうな……と類推します。
その他の集計方法
![subtotal16](https://sunagitsune.com/wp-content/uploads/2020/04/subtotal16.png)
集計値に「1」を指定すると、Average(平均)を求めます。
![subtotal22](https://sunagitsune.com/wp-content/uploads/2020/04/subtotal22.png)
「2」と「3」はCount(数値個数)およびCountA(データ個数)、
![subtotal17](https://sunagitsune.com/wp-content/uploads/2020/04/subtotal17.png)
「4」はMax(最大値)、
![subtotal18](https://sunagitsune.com/wp-content/uploads/2020/04/subtotal18.png)
「5」はMin(最小値)、
![subtotal19](https://sunagitsune.com/wp-content/uploads/2020/04/subtotal19.png)
「6」はProduct(積)
![subtotal20](https://sunagitsune.com/wp-content/uploads/2020/04/subtotal20.png)
「7」と「8」はStdev(不偏標準偏差)およびStdevP(標準偏差)、
![subtotal21](https://sunagitsune.com/wp-content/uploads/2020/04/subtotal21.png)
「10」と「11」はVar(不偏分散)およびVarP(分散)となります。
他に101~もありますが、ちょっと特殊なのでまたの機会に。
Subtotal関数の使い方については以上です。
![](https://sunagitsune.com/wp-content/uploads/2018/10/gui-2311261_1280.png)
やりたいことから方法を探すエクセル(Excel)操作・関数・VBA(マクロ)逆引きまとめ
逆引き(やりたいことから探す)Excel記事まとめ
![](https://sunagitsune.com/wp-content/uploads/2018/12/mathematics-1509559_1280-160x90.jpg)
関数・演算子・メソッド・プロパティ名から探すExcel/VBA(マクロ)使い方・組み合わせ方まとめ
こちらはExcelやメソッドの諸機能を、機能の名称から探せるまとめ記事です。
コメント