ピボットで複数の区切りでグループ化する方法

質問

販売日時、商品名、価格　のデータが蓄積されており、
下記のような区切りで売れた商品の個数分布の集計を定期的に行っています。

0
1-500
501-1000
1001-3000
3001-5000
5001-10000
10001-30000
30001-50000
50001-100000
100001-150000
150001-200000
200001-300000
300001-

今はエクセルのピボットテーブルを使って、
行ラベルに価格、
値に商品名(個数)、価格(合計)　と割り当てて、行ラベルをグループ化して集計していますが、
グループ化の単位が規則性がないため、
「先頭の値：1、単位：500」
「先頭の値：1001、単位：2000」
「先頭の値：5001、単位：5000」
・
・
・
と何度もグループ化し直しているのですが、
一度にこの区分で集計する方法はありますでしょうか？
実現できるならピボットテーブルを使わなくても構いません。(VBAでも可)
よろしくお願いいたします。

回答No.3

nishi6 · ベストアンサー

＞「C列全て」という表現はできないでしょうか。

前回解答の添付図のセルJ1に

　="$C$2:$C$"&COUNTA(B:B)

と入力します。データの加減があるとこれが変化します。

で、このセルに名前を付けます。分かりやすくするためです。

　セルJ1を選択し、名前ボックス（画面の左上の方です）に「データ範囲」と入力してEnterとします。「」はいりません。実際のデータ数は表題の分1件少ないんですが、「データ数を表す範囲」程度に理解してください。特別に意味のある用語ではなく、勝手に付けた「セル範囲名」です

次に、セルI3の数式を、

　=SUMPRODUCT((G3<=INDIRECT(データ範囲))*(INDIRECT(データ範囲)<=H3))

セルJ3の数式を、

　=SUMPRODUCT((G3<=INDIRECT(データ範囲))*(INDIRECT(データ範囲)<=H3)*(INDIRECT(データ範囲)))

のように置き換えてください。これをランク分コピーします。これで完了です。
「INDIRECT(データ範囲)」を1回入力すればコピーして貼り付けられます。

Indirect関数は文字列を算式に使えるようにする関数です。

これでデータを入力すれば自動的に合計されるでしょう。所要時間「0秒」で集計終了？！

お礼

なるほど！
いくらでもやりようはあるもんですね。
大抵解決できてしまうんですねぇ
ありがとうございました！

回答No.2

keithin

元データに１列追記し、価格がＣ列だとして
Ｄ１：
区分
Ｄ２：
=IF(C2="","",LOOKUP(C2,{0,1,501,1001,3001},{"0","0-0.5","0.5-1","1-3","3-"}))
以下コピー

といった具合でグループを算出、ふつーにピボット処理してしまったほうが早くてトータルで納得いくのが出来ると思います。
勿論グループ分け表を別途用意して、ふつーにVLOOKUPとかで算出しても全然ＯＫです。

お礼

ありがとうございます。できました！
自己流でif文を何度も繰り返して同様の事を行っていたのですが、
こんなにシンプルにできる方法があるなんて驚きました。

今までの変わらない運用で、よりスマートな方法ですね。

回答No.1

nishi6

データは添付図のA、B、C列のようになっていると想定しました。

まず、集計するランクの表を作ります。
最初と最後は0と9,999,999を追記しています。

データにランクを追加します。添付図では「ランク最初」と「ランク最後」を追加していますが、どちらか一方でいいです。選択してください。

添付図のD2: =VLOOKUP(C2,$G$3:$H$15,1)
添付図のE2: =VLOOKUP(C2,$G$3:$H$15,2)

とします。全データにコピーします。

後はピボットの行ラベルに「ランク最初」か「ランク最後」を選択します。

ランク別の集計は不要になると思われます。

または、直接計算（こちらがおすすめ）するには、添付図の

セルI3: =SUMPRODUCT((G3<=$C$2:$C$41)*($C$2:$C$41<=H3))

セルJ3: =SUMPRODUCT((G3<=$C$2:$C$41)*($C$2:$C$41<=H3)*($C$2:$C$41))

としてコピーすればピボットは不要になるでしょう。（C41は添付図での一番下の行です）
こちらはD、E列は不要です。

ご参考に。

お礼

ありがとうございます。できました！
ピボット不要でダイレクトに算出できる方法に感動しました。

レコード数は毎回増え続けるので、本当はC列全部という指定（C:C）のようにしたかったのですが、
エラーが出てしまったので、（$C:$999999）の指定で回避しました。
「C列全て」という表現はできないでしょうか。