Excel。小計で集計した時に合計の隣に平均値も表示したいけど出来るの？【SUBTOTAL】

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を使っている可能性がありますので、
ご注意ください。

すべて置換ボタンをクリックします。

置換されたことを確認して、数式タブの『数式の表示』をオフにします。

どうですか。リクエスト通り、平均値を横に並べましたね。

このようにすれば、
小計での集計も使える可能性がアップしますので、
色々試してみてください。

Excel2010。小計で集計した結果を別のシートにコピーしたら？ 可視セルのコピー

Excel2010。小計で集計した結果を別のシートにコピーしたら？

可視セルのコピー

データタブのアウトラインにある。小計。
これを使うと、アイテムごとに集計を算出することが簡単に出来ますね。
この機能は、仕事でつかえるExcel講座や、マンツーマン講座等でも、ご紹介している機能ですね。
テーブルの集計行だと、どうしても一アイテムごとに条件を変えないといけませんけど、
小計なら簡単に算出できるので、こっちも合わせて講義内容に入れている訳です。

小計の使い方は、以前に紹介しておりますので、そちらを確認してもらうとして、
実は、この小計で算出したデータを別のシートにコピーしようとすると、うまくいかないんですね。
それをどうしたらいいのか？というのが今回の目的です。

意外とExcelもアチコチにトラップがあるんですね。

それでは、まず、下記のデータを店舗名を基準に金額の合計を集計してみます。

最初に基準となる列を並び替えないといけないんでしたね。
今回は店舗名ですので、店舗名を昇順に並び替えをしましょう。

並び替えの方法は、B1の店舗名をクリックしてデータタブの並び替えとフィルターにある、
昇順ボタンをクリックします。そうすると、店舗名順で並び替えが出来ますね。

これをやらないで、小計を行ってしまうと、とんでもないことになってしまうことも、
以前紹介しておりますので、そちらを参照してみてください。

また、B1をクリックしましたが、B列を範囲選択をしちゃいますと、
その選択範囲の中のみで並び替えが発生してしまい、
データがバラバラになってしまいますので、分かりやすいように、
今回は、B1の店舗名をクリックして昇順にしております。

並び替えが終わりましたら、データタブのアウトラインにある小計ボタンをクリックしましょう。

そうすると、集計の設定ダイアログボックスが表示されてきますね。

グループの基準は、店舗名ですね。
集計方法は、合計を選択します。
集計するフィールドは金額にチェックをつけましょう。
そうしたら、OKボタンをクリックしましょう。

左側に、アウトラインが設定されました。このアウトラインの2というボタンをクリックしてみましょう。折りたたまれて、店舗ごとの集計が表示されますね。

さて、これからが今回ご紹介したい事なのですが、このデータを別のシートにコピーしたいとします。
では、A1からD806の金額の総計までを範囲選択して、コピーボタンをおして、

別シートに移動して、貼り付けてみましょう。
すると、どうなったでしょうか…

これでいいのでしょうか？

どうなったのかというと、折りたたまれていたデータも一緒にコピーされちゃったんですね。
これが目的ではないんですね。集計されたデータのみを別シートにコピーしたいわけですね。
では、戻るボタンでシートのデータを消しておき、集計されているシートに戻りましょう。

ここで登場するテクニックが、可視セルのコピーというテクニックです。
簡単にいうと、今見えているデータだけをコピーしたい訳ですね。

見えている。つまり【可視】。をコピーするという訳です。

ではやっていきましょう。

まず、範囲選択をしましょう。
次に、ホームタブの検索と選択をクリックして、ジャンプをクリックしましょう。

そうすると、ジャンプのダイアログボックスが表示されます。

セル選択をクリックすると、今度は、選択オプションダイアログボックスが表示されますので、

可視セルを選択してOKボタンをクリックしましょう。
特に変わっておりませんが、

このまま、コピーをしていきます。
コピーボタンをクリックして、シートを切り替えていきましょう。
別のシートのA1をクリックして、貼り付けをしていきましょう。
するとどうでしょう。

うまくいきましたね。テーブルのフィルタやオートフィルタは大丈夫なのですが、
この小計機能だけは、この可視セルのコピーをしないとうまくいかないんですね。

Excel2010。SUMIF関数では大変なので、小計で集計。その１

Excel2010。SUMIF関数では大変なので、
小計で集計。その１

小計

Excel。ビッグデータが花盛りの昨今、仕事で、店舗ごとに集計したいんだけど、
というリクエストがあって、よくご質問に対応したり、講座でもご紹介しているものの中に、

小計という機能があります。

Excel2003では、集計なんですが、Excel2007以降では小計になっていますが、
ここでは、小計でいきたいと思います。

今回の、小計は、非常に便利なのですが、最初にある処理をしないで始めると、
結果が、【大惨事】になってしまいます。
その点も含めて、早速、ご紹介していきましょう。

まず、このようなデータがあります。データの件数は、284件です。

このデータを基に、店舗別に金額の合計を算出したいのが今回の目的です。
以前紹介した、SUMIF関数やDSUM関数を使ってもいいのですが、
それだと、店舗数分作業しないといけないわけです、店舗数が少なければいいでしょうけど、
多くなると、これらの関数を使用するのは、ちょっと効率が悪いわけですね。

そこで、登場するのがデータタブにある、小計というわけです。

操作自体は、簡単なのですが、先ほども書きましたように、最初が肝心なのです、
それは、

【合計する基準を並び替えすること】。

つまり、今回は、店舗で並び替える必要があるのです。ここがポイントになります。

それでは、B1の店舗のセルをクリックして、データタブの並び替えとフィルターにある、
昇順ボタンをクリックしましょう。
別に降順でも構いません。
ちなみに、昇順とは、小さい順。五十音順。A～Z順ですね。

すると、さいたま～宇都宮～と並んだのが確認できますね。

そうしましたら、A1のセルをクリックしてデータタブの小計のボタンをクリックします。
A1じゃなくても、この表の中のどのセルでもアクティブにしていればOKですが、
わかりやすく、左上のセルであるA1を使っております。

クリックすると、集計の設定ダイアログボックスが表示されます。
小計を押したのに、集計？わかりにくい気もしますが…

グループの基準は、今回並び替えたフィールドが、基準ですよね。ですので、店舗。
今回は、店舗ごとに集計したいわけですから。
集計方法は、今回は、合計。
集計するフィールドは、金額にチェックマークがついていることを確認します。
なお、この集計するフィールドは、Excelが勝手に判断しますので、
最初から最後まで、確認する癖をつけておきましょう。
でないと、関係ないフィールドの集計をしちゃいます。

あとは、そのままOKボタンをクリックします。コレで完成。

行番号の左側に1～3のアウトラインが表示されましたね。
このボタンをクリックするとデータが折り畳まれます。
では、今の3の状態は、全展開です。それでは、2をクリックしてみましょう。

おおっ、店舗ごとに集計されているのがわかりますね。
ちなみに、集計という文字が店舗のセルに表示されていますが、
作業上、邪魔な場合は、空白で置換しちゃえばいいでしょう。

それでは、今度は、1をクリックしてみましょう。

おおっ、今度は、全店舗集計ですね。
SUMIF関数やDSUM関数を使うよりも、楽に店舗ごとに集計ができましたね。

それでは、ここで、なぜ、最初に、

基準で並び替えないといけないのか

をやってみることにしましょう。

まず、データがあります。A1をクリックしておきます。

このまま、データタブの小計をクリックしたら、どうなるのでしょうか？
まず、集計の設定ダイアログボックスが表示されますので、先ほどと同じ設定をしましょう。

それでは、OKボタンをクリックしたらどうなったでしょうか？

あれれ？集計しているけど、一行毎に？？
実は、この小計。基準であるデータが同じものが続いている間は、
それがグループだと判断してくれて、集計してくれるというわけです。
ですので、並び替えをして、データをまとめておきませんと、
このようなことになってしまう訳ですね。

ということで、最初に基準で並び替えることが重要になるわけです。
余談ですが、データベース関係は、元に戻すボタンが使えなくなることがありますので、
元のデータを残しておくことをお勧めします。

それか、通し番号を振っておくといいでしょう。

Excel。3D集計（串刺し）をつかってみよう！

Excel。3D集計（串刺し）をつかってみよう！

研修や講義で人気が高い、3D集計（串刺し）。しかし、中々わかりづらいので、ご紹介。

条件として、同じセル番地に同種のデータが入っていることが必要。

第2四半期売上集計のB4には、7月～9月までの新宿店Aランチの合計金額を算出したい。

算出したい範囲を選択する。今回はB4:E8。なお、合計列行に数式を設定している場合は、B4:D7とします。

算出範囲の左上セル。今回はB4をクリックして、Σ(オートSUMボタン)をクリックする。

グループ作業の設定をします。
先頭シートをクリック後、最後のシートをShift+クリック。

自動的に先頭シートに移動しているので、そのシートの左上のセル、今回はB4をクリックする。
その後、再度、Σ(オートSUMボタン)をクリックする。

算出したいシートに自動的に戻り、算出されている。

担当者は、各店舗にExcelデータを渡すようにして、そのシートを使用するようにするべきですね。統合という方法もありますが、効率的でないので、一元管理が望ましいです。