■文中のタイトル

「コピぺ de 関数」FILE_08

前回の記事では平均値や合計を計算する方法をご紹介しました。

今回はさらに便利な関数、SUBTOTAL関数をご紹介します。

この関数の凄いところは、引数で集計方法を選ぶことができて、選んだ引数に連動して結果が変わります。

引数を変えるだけで、合計、平均、積、最大値、最小値、データの個数などが調べられます。

また、フィルター機能と合わせて使うともっと便利に使えます！！

今回はその方法を詳しく書いていきたいと思います。

試しに結果を入れて体感してみましょう。

便利な集計関数

では早速ですが、試す用のデータを用意したので下記のデータをコピーして自身のExcelのA1セルに貼り付けてください。

氏名 国語 算数 社会
赤井 80 50 99
安室 50 70 40
乱馬 40 60 いい目をしているな

まずはこちらの数式をA6にコピペしてみましょう。

=SUBTOTAL(1,B2:B4)

すると、赤井、安室、乱馬3名の国語の平均点が表示されます。

この関数の凄いところは、添付した数式の”1”が入っている箇所の引数を変えることによって様々な結果を表示させることができることです。

引数の一覧はこちらです。

関数の意味 関数 引数の番号
平均値 AVERAGE 1
数値の個数（数値のみカウントする） COUNT 2
データの個数（何か入って入ればカウントする） COUNTA 3
最大値 MAX 4
最小値 MIN 5
積（掛け算） PRODUCT 6
不偏標準偏差 STDEV 7
標本標準偏差 STDEVP 8
合計 SUM 9
不偏分散 VAR 10
標準分散 VARP 11

統計に関する仕事でない限り、標準偏差、標準分散などは使わないと思います。

数値の個数とデータの個数ですが、少しだけ意味合いが違うのでご注意ください。

違いを実感するためには、下記の数式をB6にコピペしてみてください。

=SUBTOTAL(2,D2:D4)

すると結果は2となります。D4セルの文字列はカウントされないということです。

次にこちらの数式をC6にコピペしてみてください。

=SUBTOTAL(3,D2:D4)

すると今度は3になります。文字列も含めカウントしているのがわかります。

ちなみに、どちらも空欄の場合はカウントしませんのでご注意を！

フィルター機能でさらに便利に使える！

SUBTOTAL関数はさらに便利な使い方ができるので、こちらも実践してみたいと思います。

まずは自身のExcelのA1〜D1を選択し、データタブの「フィルター」をクリックします。

※画像はMacですので、Windowsは若干ボタンの配置等が異なると思います。

クリックすると、こちらの画像のように三角ボタンがA1〜D1セルにつくと思います。

この三角ボタンをフィルター機能と言いますが、この三角を押してデータを絞ったり、特定の条件のデータだけを見ることができます。

そして、SUBTOTAL関数がフィルター機能に連動して動くということです。

試しにA1セルの三角ボタンをクリックして、「乱馬」と除いてみましょう。

すると、先ほどまでA6には「57」、C6には「3」と入っていた結果が、自動的に「65」と「2」に変わりました。

※B6はもともと「数値の個数」だけカウントしていたので、乱馬の社会の点数枠に入っていた「いい目をしているな」が無くなっても結果は変化しません。

このように、SUBTOTAL関数は、引数を変えて合計、平均、データの個数を算出できるだけではなく、フィルター機能と一緒に使うことでデータを様々な角度から分析することができます。

Excelで色々と分析をする方は使える関数だと思いますので、ぜひ使い方をマスターしてみてください。

今回使った関数

SUBTOTAL関数・・・表示データされているデータを使って色んな集計ができる

次回は、リスト内の条件を満たす行の値を合計する方法（DSUM関数）をご紹介します。