Excel。小計で集計した時に合計の隣に平均値も表示したいけど出来るの？
＜小計とSUBTOTAL関数＞
次の表がありまして。
ピボットテーブルではなくて、
小計機能を使って店舗ごとの売上高の合計と平均値を算出したいそうなのです。
しかも、わかりやすい表にしたいので、
合計のとなりに平均値も算出して表示したいそうなのです。
まず、小計を使ってみてどのようになるのか？
確認してみましょう。
小計を行う場合に、最初に項目の内容ごとにまとめませんと、
うまく小計機能が使えませんので、店舗名ごとに昇順で並び替えます。
そしてデータタブにある『小計』をクリックします。
集計の設定ダイアログボックスが表示されますので、
グループの基準は、『店舗名』
集計の方法は、『合計』
集計するフィールドは、売上高にチェックマーク
OKボタンをクリックすると、集計されます。
アウトラインの2をクリックすると、
折りたたまれて、各店舗の売上高集計が表示されますね。
さて、今回のリクエストは、
E列に平均値を算出できないか？というものなのです。
この小計に平均値を追加すると、次のように集計されます。
アウトラインを2にすると、より一層わかりにくくなります。
なので、横に並べたいわけですね。
出来ないわけではないのですが、
かなりの【力技】になりますが、ご紹介していきます。
まず、E列にD列の売上高をコピーして、
売上高平均と見出しを変更しておきます。
そして、店舗名を昇順で並び替えておきます。
まずは、通常通りに小計機能を使って集計していきます。
グループの基準は、『店舗名』
集計の方法は、『合計』
集計するフィールドは、売上高にチェックマーク
ここまで先程と同じですが、
忘れずに、売上高（平均値）にもチェックマークを付けます。
平均値を算出するのだから、
集計方法が合計でいいの？と思われるかもしれませんが、
構いませんので、OKボタンをクリックします。
E列にも合計値が算出されました。
アウトラインの2をクリックして、
店舗ごとの集計がわかるようにしておきます。
ここからが、【力技】。
小計は、そもそも、SUBTOTAL関数を使った機能なんですね。
E12である、上野の売上高（平均値）は、
=SUBTOTAL(9,E2:E11)
という数式が設定されているので、合計値を算出しているわけです。
なので、合計の設定を平均が算出される設定にすればいいわけです。
つまり、
=SUBTOTAL(1,E2:E11)
という数式に変更してあげればいい。9を1に修正すればいいわけです。
なるほど、簡単だと思うかもしれませんが、
問題になるのは、
今回のように3店舗ぐらいなら一つずつ数式を修正してもいいのですが、
数が多い場合。集計が大変です。
そこで、【置換】で9を1にしていきます。
売上高（平均値）の列を範囲選択します。
【数式も置換で変換できる！】
数式タブの『数式の表示』をクリックします。
すると、シートの計算式が設定されているセルは、その数式が表示されます。
ホームタブの置換ボタンをクリックして、
検索と置換ダイアログボックスを表示しましょう。
検索する文字列にTAL(9
置換後の文字列にTAL(1
SUBTOTALとしてもOKですが、
長いのでTALにしております。
ただ、9と1だと、
セル範囲でセル番地を9とか1を使っている可能性がありますので、
ご注意ください。
すべて置換ボタンをクリックします。
置換されたことを確認して、数式タブの『数式の表示』をオフにします。
どうですか。リクエスト通り、平均値を横に並べましたね。
このようにすれば、
小計での集計も使える可能性がアップしますので、
色々試してみてください。
