Excel。テーブルの集計行のようなものをテーブルを使用しないで作ってほしい
＜IF関数とSUBTOTAL関数と入力規則＞
Excelで通常に用意させているものを使えばいいのに、わざわざというか、
こういうものを作ることはできませんか？
というリクエストもありまして、当然現場では必要なものなのでしょう。ということで、今回はそのリクエストの中から、
【テーブルの集計行をテーブルを使わないで作るにはどうしたらいいでしょうか？】
というもの。素直に、テーブルにすればいいのですが、そうしたくないわけですね。
そして、テーブルと同じ機能、すなわち、行を非表示したら、
それに連動して計算結果も変えたいというわけですね。
そこで、次のような表があります。
F2に計算方法が表示して、それに合わせて、その結果をG2に表示するというもの、
当然行を非常にしてみると、
このように、それに合わせて、計算結果も連動して変わるという表を作ってみましょう。
まず、F2には、入力規則のリストを設定しておきます。
ドロップダウンリストから選択できるようにするわけですね。
設定方法は簡単ですね。
データの入力規則ダイアログボックスの設定タブの入力値の種類にはリスト。
元の値には、
合計,平均,最大値,最小値,件数
と入力しております。
次に、G2にSUBTOTAL関数を設定したいのですが、
集計方法の番号を求めておくと便利ですので、
H2にIF関数のネストで、それぞれの計算方法に合致する数値を求めるようにします。
H2には、
=IF(F2="合計",109,IF(F2="平均",101,IF(F2="最大値",104,IF(F2="最小値",105,103))))
という数式を設定しております。これは、
合計が、109。平均は、101。最大値は104。最小値が105。
そして、件数は103という集計方法の数値が割り振りされているのです。
なお、100番台は、データを非表示にしてもそれに連動して計算結果を算出してくれますので、
今回は100番台を使っております。
そして、メインとなるのが、G2のSUBTOTAL関数とことになりますので、G2をクリックして、SUBTOTAL関数ダイアログボックスを表示しましょう。
以前ご紹介したように、SUBTOTAL関数は手入力すると集計方法が一覧で表示されますので、
わかりやすいと書きましたが、今回は、H2を参考にしたいだけなので、
ダイアログボックスを使ってみます。
集計方法には、H2。
参照1には、D3:D25。
あとは、OKボタンをクリックしましょう。
これで、完成しました。H列を見せたくない場合には、非表示など設定してください。
では、F2の計算方法を変えてみましょう。
ちゃんと、結果も変わりましたね。
当然データの一部を非表示にしても、
SUBTOTAL関数を使っておりますので、連動して結果が変わりましたね。
このような表を作ってみると、SUBTOTAL関数の動きもわかるようになりますので、
機会がありましたら、作ってみると面白いですよ。
