エクセルのピボットテーブルを使った集計を教えてください。



注文者 品名1 品名2 品名3
A みかん みかん バナナ
B りんご みかん
A みかん バナナ バナナ
A バナナ りんご みかん
C りんご みかん


これを
  品名 みかん りんご バナナ
注文者
A  4  1  4
B  1  1  0
C  1  1  0

このように集計したいのですが
出来ますでしょうか?
お願いします。


回答の条件
  • 1人2回まで
  • 登録:2007/04/05 21:43:49
  • 終了:2007/04/09 20:40:05

回答(2件)

id:rikuzai No.1

りくっち回答回数1366ベストアンサー獲得回数1412007/04/08 20:14:11

ポイント35pt

少し遅くなってすみません。

では関数を使わずピボットテーブルと、もう一つ「統合」という機能を使ってご希望の集計を実現しようと思います。


まず、表に対するピボットテーブルを別シートに作成し、空の状態のままこのピボットテーブルシートを品目のカラム数(例示の場合なら3)と同じになるようシートコピーします。

(該当シートのシートタブの上で右クリック→「移動またはコピー」でできます)


まずピボットテーブルに

行範囲=注文者

列範囲=品目1

データ範囲=品目1/データの個数

を追加します。すると、↓のように集計されます。

注文者 バナナ みかん りんご
A 1 2
B 1
C 1

この後、コピーしたシートに品目2、品目3を同じように集計し、各品目カラムごとのピボット集計を実行します。


次にこの三つのピボットテーブルの集計結果を「統合」で一つにまとめます。

集計結果を算出したシートのセルを選択してから、

ツールバーの「データ」→「統合」をクリックします。

統合の設定というダイアログがひらきますので、

集計の方法を「合計」とし、下の統合元範囲にさっきの三つの集計結果を追加していきます。

範囲の追加は「統合元範囲」の右横にあるボタンを押して、直に選択してから「追加」ボタンを押して決定します。

例示のデータで、各ピボット集計シート名を「品目1」「品目2」「品目3」とした場合

品目1!$A$4:$D$7

品目2!$A$4:$D$7

品目3!$A$4:$D$7

となると思います。

範囲の追加が終わったら、左下の「統合の基準」の上端行、左端列の両方にチェックを入れ、OKをクリックします。


すると自動的に↓の集計結果が選択していたセル以下に表示されます。

バナナ みかん りんご (空白)
A 4 4 1
B 1 1
C 1 1

この時(空白)列ができるのを防ぐことはできません。


以上ご参考まで。

id:zuguimo

ありがとうございます。

統合は思いつきませんでした。

やはり直接集計するのはは無理ですね。

2007/04/08 23:05:41
id:aside No.2

aside回答回数339ベストアンサー獲得回数312007/04/09 12:35:47

ポイント40pt

注文者-A!セル、注文者C空白-D6セルとすると

ピボットテーブル作成ウィザードで最初に、「複数のワークシート範囲」を選択。

ページフィールドの作成は自動で、範囲はA1-D6範囲を選択します、そして完了。

作成されたテーブルの右上はデフォルトで「列」になっているのですが、右クリックで「表示しない」

を選び消します。

代わりに「値」をドラッグしていれると「バナナ、みかん、りんご、(空白)」の列が表示されるはず。

「(空白)」の列を右クリックで「表示しない」を選び、消すことで、目的のテーブルっぽいのが、、。

id:zuguimo

ありがとうございます。

何が起こったのかわからないのが少し怖いですが出来ました。助かりました。

2007/04/09 20:38:19
  • id:rikuzai
    一回関数で表を作り直さないと厳しいと思いますが、
    工程にワンクッション入れるのは可能ですか?
  • id:zuguimo
    例えば、E列に=countif(b2:d2,"みかん")などとして
    みかん列で集計という方法は、望みません。
    データが10000単位ですのであまり関数を使うと重くなりすぎますので
    これが解消されればOKですが、出来れば直接集計する方法があれば教えてください。
  • id:rikuzai
    >やはり直接集計するのはは無理ですね。
    そうですね…一番簡単なのは、
    A列を更に下に二回コピーして、品目2、品目3のデータをB列にコピーして、A:B列でピボット集計するのが一番確実ですが、
    1万行以上あって、カラムも3以上などになってくるようなら厳しいでしょうし。

    今回例示した集計方法がちょっといいところは、
    ピボットを作る前にデータ範囲に名前をつけて、
    その名前でデータ範囲を指定してテーブルを作れば、
    データ量が変わっても名前で指定しなおせば全てのピボットに反映されますし、
    統合を実行する時、品目数や注文者数が変わらないのであれば、
    (ピボットテーブルの集計結果の範囲が変わらない)
    「統合元とリンクする」にチェックをしておけばピボットの値が変われば統合データも自動更新されるしくみとなります。

この質問への反応(ブックマークコメント)

「あの人に答えてほしい」「この質問はあの人が答えられそう」というときに、回答リクエストを送ってみてましょう。

これ以上回答リクエストを送信することはできません。制限について

絞り込み :
はてなココの「ともだち」を表示します。
回答リクエストを送信したユーザーはいません