参照先範囲をテーブルにすることで、データの増減に対応することを紹介しました（VLOOKUP, 入力規則）が、今回は「集計行」を取り上げてみます。
Excel で表を作り、その最終行になんらかの計算結果を表示するものを出すことは多いです。
ここで「田中だけの合計」や「商品Aだけの合計」といった絞り込みを行う場合、Excel に慣れ親しんだユーザーはピボットテーブルを使うと思いますが、そこまでしなくても良い場合は「オートフィルター」を使うと思います。表のセルのどれかをアクティブセルにして [データ] タブの [フィルター] を押せば、オートフィルターが表の1行目に設定されます。
このオートフィルターで「担当 田中」で絞り込むと、合計の行が消えます。合計行はデータの範囲に隣接しているため、データ行として扱われるためです。無理やり担当の列が空白の合計行を出すために「空白のセル」を選択すると最終行は表示されますが、今度は合計の計算が元のままなことに気が付きます。
この計算結果が変わらないのも 2003 までの鉄板ネタのひとつで、オートフィルターは該当しない行を「高さ 0 で非表示」にしているだけであり、SUM 関数は指定範囲内の非表示の行の数値も計算してしまうため、表示されている行だけの計算をしません。この時は SUBTOTAL 関数を使え、といえるでしょう。
SUBTOTAL 関数 (Excel 2003 Office Online の解説）
SUBTOTAL 関数（Excel 2007 Office Online の解説）
解説を見て分かるように、合計なのか平均なのか、といったオプション指定を数値で行います。おおよそ 使うのは 3（COUNTA) と 9(SUM) ですが、関数を直接入力すれば数式オートコンプリートが使える数値とその意味を表示してくれるので問題ありません。（繰り返しますが、関数の挿入 ダイアログボックスは使ってはいけません。このような支援を受けられないからです）
ですが、絞り込みによって最終行が消えてしまうのはいただけません。空白を選択したくないケースもあるでしょう。
そこでテーブルの「集計行」の出番です。上記の問題をすべて解決してくれます。
一連の操作を以下で確認してください。
上手の操作を注意深く見ると、集計行で使われている関数は SUBTOTAL 関数ですが、集計方式で 9 ではなく 109 が使われています。これは、オートフィルターだけでなく、ユーザーが手動で行を非表示にした場合にも対応するためです。
テーブル関連の投稿
[テーブルとVLOOKUP] https://road2cloudoffice.blogspot.jp/2014/10/vlookup.html
[テーブルと入力規則] https://road2cloudoffice.blogspot.jp/2014/10/blog-post.html
[テーブルと集計行] https://road2cloudoffice.blogspot.jp/2014/10/blog-post_31.html
[テーブルと構造化参照] https://road2cloudoffice.blogspot.jp/2014/11/blog-post.html
[テーブルとピボット] https://road2cloudoffice.blogspot.jp/2014/11/blog-post_7.html
[テーブルとVBA] https://road2cloudoffice.blogspot.jp/2014/11/blog-post_7.html
0 コメント:
コメントを投稿