2014/10/31

テーブルのすすめ 集計行

Excel 2007 以降の最も重要な機能追加である「テーブル機能」。
参照先範囲をテーブルにすることで、データの増減に対応することを紹介した(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 が使われている。これは、オートフィルターだけでなく、ユーザーが手動で行を非表示にした場合にも対応するためだ。

0 コメント:

コメントを投稿

Powered by Blogger.

自己紹介


PowerBI コミュニティ勉強会の 沼口 です。
https://powerbi.connpass.com/
最近の Excel は Office 365 のクラウドサービスと 連携する方向性が打ち出されています。この「Road to Cloud Office」ブログでは、Excel ユーザーの視点から Power BI Service や、Office 365 の活用方法を模索した結果をお伝えしています。
Microsoft MVP for Data Platform 2017-2018