エクセル２００７のピボットテーブルで質問です

質問

よろしくお願いしますm(_ _)m

エクセルで下記のようなデータを作成する機会があるのですが

　いつ　　料理１　　料理２　　飲み物１　飲み物２
(1)朝食　牛丼　　　サラダ　　　コーラ　　お茶
(2)朝食　牛丼　　　豚汁　　　　お茶　　　水
(3)夕食　・・・・・・・・

本当は下記のようなテーブルを作成しグラフを作りたいのですが
・レポートフィルタ→いつ（朝食でフィルタかける）
・列ラベル→飲み物１と飲み物２（コーラ、お茶、水）
・行ラベル→料理１と料理２（牛丼、サラダ、豚汁）
・値→データの個数（料理１と料理２）

　　　　　コーラ　お茶　水　総計
牛丼　　　１　　　　２　　１　　４
サラダ　　１　　　　１　　０　　２
豚汁　　　０　　　　１　　１　　２
総計　　　２　　　　４　　２　　８

うまくいきません。

例えば、列ラベルに飲み物１と飲み物２をドラッグすると、
・「コーラ」「お茶」「お茶」「水」と表示される。
・飲み物１と飲み物２の総計を表示してくれない。
※SUMで総計を出すと、項目が増えた時対応できずピボットグラフの作成に
毎回手間がかかる。

なんとか、飲み物１、飲み物２のように２つ以上の行列データをまとめる方法は
ないでしょうか？

わかりづらくて申し訳ないのですがよろしくお願いいたします。

回答No.2

KURUMITO

料理の種類や飲み物の種類などが追加されたりしても自動的にお望みの表ができるようにするためには、次のように作業列を作って対応することが必要です。
元の表はシート1に有ってA2セルから下方には朝食、夕食などの文字が入力されるとします。
B1セルには料理1の文字が、C1セルには料理2、D1セルには飲み物1、E1セルには飲み物2の文字列が入力されており、下方には各データが入力されているとします。
作業列としてG1セルには料理と入力し、G2セルには次の式を入力して下方にドラッグコピーします。

=IF(ROW(A1)<=COUNTA(B:B)-1,INDEX(B:B,ROW(A1)+1),IF(ROW(A1)<=COUNTA(B:B)+COUNTA(C:C)-2,INDEX(C:C,ROW(A1)-COUNTA(B:B)+2),""))

H2セルには次の式を入力して下方にドラッグコピーします。

=IF(G2="","",IF(COUNTIF(G$2:G2,G2)=1,MAX(H$1:H1)+1,""))

I1セルには飲み物と文字を入力し、I2セルには次の式を入力して下方にドラッグコピーします。

=IF(I2="","",IF(COUNTIF(I$2:I2,I2)=1,MAX(J$1:J1)+1,""))

K1セルには組み合わせと文字を入力し、K2セルには次の式を入力して下方にドラッグコピーします。

=IF(MOD(ROW(A1),4)=1,INDEX(B:B,ROUNDUP(ROW(A1)/4,0)+1)&INDEX(D:D,ROUNDUP(ROW(A1)/4,0)+1),IF(MOD(ROW(A1),4)=2,INDEX(B:B,ROUNDUP(ROW(A1)/4,0)+1)&INDEX(E:E,ROUNDUP(ROW(A1)/4,0)+1),IF(MOD(ROW(A1),4)=3,INDEX(C:C,ROUNDUP(ROW(A1)/4,0)+1)&INDEX(D:D,ROUNDUP(ROW(A1)/4,0)+1),IF(MOD(ROW(A1),4)=0,INDEX(C:C,ROUNDUP(ROW(A1)/4,0)+1)&INDEX(E:E,ROUNDUP(ROW(A1)/4,0)+1),""))))

これで作業列の作業は終了です。
お望みの表をシート2に作るとしてA2セルには次の式を入力して下方にドラッグコピーします。

=IF(ROW(A1)<=MAX(Sheet1!H:H),INDEX(Sheet1!G:G,MATCH(ROW(A1),Sheet1!H:H,0)),IF(ROW(A1)=MAX(Sheet1!H:H)+1,"総計",""))

B1セルには次の式を入力して右横方向にドラッグコピーします。

=IF(COLUMN(A1)<=MAX(Sheet1!$J:$J),INDEX(Sheet1!$I:$I,MATCH(COLUMN(A1),Sheet1!$J:$J,0)),IF(COLUMN(A1)=MAX(Sheet1!$J:$J)+1,"総計",""))

B2セルには次の式を入力して右横方向にドラッグコピーしたのちに下方にもドラッグコピーします。

=IF(OR(B$1="",$A2=""),"",IF(AND(B$1<>"総計",$A2<>"総計"),COUNTIF(Sheet1!$K:$K,$A2&B$1),IF($A2="総計",SUM(B$1:B1),IF(B$1="総計",SUM($A2:A2),""))))

これでお望みの表が完成します。
料理の種類や飲み物の種類、データの増加にも自動的に対応して表示が変化します。

回答No.1

keithin

＞飲み物１、飲み物２のように２つ以上の行列データをまとめる

ピボットテーブルレポートでは、そういう事は出来ません。全く不可能です。

＞方法はないでしょうか？

ピボットテーブルレポートを使って集計するには、例えば次のようにちょっと工夫すれば簡単に出来ます。

例：
今例えばＡ列に食事でＡ２：Ａ１０ぐらいにデータが記入されているとする
Ｂ列に食事１がＢ２：Ｂ１０にあるとする
Ｃ列に食事２がＣ２：Ｃ１０にあるとする
Ｄ、Ｅ列も同様に。

Ａ２：Ａ１０をコピー
Ａ２１に形式を選んで「リンク貼り付け」
Ｃ２：Ｃ１０をコピー
Ｂ２１に形式を選んでリンク貼り付け
Ｅ２：Ｅ１０をコピー
Ｄ２１に形式を選んでリンク貼り付け

これで食事２が食事１の下に統合されました。飲み物２も飲み物１の下に一列に統合されました。

Ａ：Ｅ列を列選択してふつーにピボットテーブルレポートを作成を開始
種別をページ
食事１を行
飲み物１を列
食事１を追加でデータ
に配置
仕上げに行と列のフィールド一覧から、ゼロや空白の表示を除外すれば完成です。

言わずもがなですが実際にはコピーもリンク貼り付け先も、実際のデータが入ってくる範囲を見越して大き目に取っておいて作成し、毎回イチイチ同じ操作を繰り返すみたいな非効率なマネをしなくて済むように準備します。