前回、企業が入社テストとして出題した「Excel問題」のその6をやってみました。
今回は、フィルターを使った集計に対する関数の使い方です。
それでは一緒にやっていきましょう。
問題7を確認
今回の表には、すでにフィルターが適用されています。
これまでは、決まった範囲に対してセルの値を合計したり、検索したりという処理を行ってきました。
しかし今回は、フィルターをかけるので表の範囲が一定ではありません。
フィルターがかけられた後に、範囲が流動的になる表のデータを合計したり、件数を数えたりという操作は「SUBTOTAL関数」でできるようになっています。
「SUBTOTAL関数」の集計方法に注意
では早速、「上場区分件数」のデータを「SUBTOTAL関数」で取得してみたいと思います。
国内上場だけの件数を数える
「セルC4」をクリックして、関数の挿入ボタン(fx)をクリックし、「SUBTOTAL」を選択します。
最初の引数の「集計方法」には、表に対してどのような集計を行うかを数値で設定します。
集計方法には、以下のような機能が用意されています。
| 集計方法(非表示の行を含む) | 集計方法(非表示の行を無視する) | 機能 |
|---|---|---|
| 1 | 101 | AVERAGE |
| 2 | 102 | COUNT |
| 3 | 103 | COUNTA |
| 4 | 104 | MAX |
| 5 | 105 | MIN |
| 6 | 106 | PRODUCT |
| 7 | 107 | STDEV |
| 8 | 108 | STDEVP |
| 9 | 109 | SUM |
| 10 | 110 | VAR |
| 11 | 111 | VARP |
集計の機能は全部で11あります。
101～111は、非表示の行を無視して集計してくれます。
つまりフィルターをかけて、データを絞った後の表において、非表示となった行は集計対象とはならないわけですね。
まずは「上場区分件数」を数えるので、「count」機能を選択します。
上の表で「非表示の行を無視する」方の数値なので、「102」を指定します。
次の引数の「参照1」には、集計したい表の範囲を入力します。
ここでは、表の範囲をタイトルから選択するため「セルB6からセルD55」までを設定します。
入力できたら画面を確認してみましょう。
フィルターがかかっておらず、すべての件数が表示されている場合は、範囲が7行目から55行目までデータがすべて表示されています。
したがって結果として「49」と表示されます。
では、問題の通り「国内上場」でフィルターをかけてみましょう。
OKボタンをクリックすると、「セルC4」の上場区分件数が「23」となります。
フィルターによって非表示となった行は「SUBTOTAL関数」の「集計方法」引数に「102」を指定したため、カウントされません。
では、次に業種別の従業員数の合計を表示してみましょう。
業種別従業員数の合計
次に「国内上場」のフィルターがかかった表から、「業種大名称」でさらにフィルターをかけてみます。
「業務大名称」列のフィルターで、「金融」だけを選択します。
上のようなイメージですね。
フィルター後の表から従業員数の合計を「セルF4」に表示します。
それでは一旦フィルターをすべてクリアしましょう(フィルターのオフではありませんのでご注意ください)。
フィルターのクリアは、「Altキー」を押したまま、Aキーを1回、Cキーを1回押すショートカットキーが便利です。
先ほどの「集計方法」の表から、今度は「集計方法(非表示の行を無視する)」の「SUM」を使います。
それでは「セルF4」をクリックして、「SUBTOTAL関数」を設定していきます。
「集計方法」は109を指定し、参照1に表のタイトルからすべて範囲選択します。
109は、リスト内の表示されている行だけの合計を行ってくれます。
「国内上場」で「金融」の件数と従業員数の合計が表示されるように設定できました。
他の業種もフィルターをかけて動作を確認してみてください。
数値データが複数列あったら
先ほどの表で数値データを持っているのは、「従業員数」しかありませんでした。
そのため数値の合計を指定すれば、「SUBTOTAL関数」は唯一数値データを持っている「従業員数」の合計を計算してくれました。
では、数値データを持っている列が複数あったら「SUBTOTAL関数」の引数、「参照」にはどのような範囲を指定すればいいでしょうか。
例として先ほどの表に「支店数」という名前の列を加えてみます。
そして引数の「参照」に「支店数列」(E列)まで含めたパターンと含めないパターンを並べてみました。
「参照」に支店数列まで含めると、「SUBTOTAL関数」は両方の列の合計を表示してしまいます。
そのため同じように、フィルターをかけた後の「支店数」だけを合計させたい時には、「SUBTOTAL関数」の「参照」には「支店数列」(E列)だけを設定するようにします。
つまり先ほどの「従業員数」の合計を表示する時も、「SUBTOTAL関数」の「参照」引数には、「D6:D55」でも良かったわけです。
上の例で言えば、「従業員数」と「支店数」という数値データに関連性のない複数の列だったので、「D6:D55」のように単独列だけを「参照」引数に指定しても問題はありません。
もし「従業員数列」が「社員数」という列と「アルバイト・パート数」という複数列に分かれていたら、という場合で考えてみましょう。
その場合は、「参照」引数にどちらの列も含まれるように範囲を設定すれば、全従業員の合計数が分かりますし、「社員数」だけを範囲に設定してアルバイトやパートの人を除いた従業員数を数えるのも簡単にできます。
「参照」引数は最大で254個設定できます。
まぁ、そこまで指定する機会もそうはないでしょう。
ただ今回の入社問題のように、「SUBTOTAL関数」を使う場合、1つの表をそのまま範囲指定する時もあれば、自分の欲しい情報を細かく範囲指定する時もあります。
そういった柔軟性が「SUBTOTAL関数」の使いやすいところなのかもしれませんね。
問題7まとめ
今回は「SUBTOTAL関数」を使った集計を操作してみました。
それではおさらいをしておきましょう。
- 「SUBTOTAL関数」で非表示となる列の集計方法を指定できるか
- 「SUBTOTAL関数」の「参照」引数に状況に応じたデータ範囲を指定できるか
今回の内容は、エクセルをノンコードで使う場合でも、だいぶシステマチックな動作を感じられる操作だったのではないでしょうか。
次回はラストですね。問題8に進んでいきます。