エクセルで複数条件の集計

質問

前に同じような質問があるか探しましたが、ビビッとくるのが探せないので質問します。
エクセルでの複数条件を満たす数値の集計を行いたいのですが、例えば日付、取引先、
品種、数量、単価、金額、とあります。そのデータを取引先別に単価が３０円以下の
ものを全て品種別に集計したい場合どのようにしたらいいでしょうか？

●データ（データ数は2000-3000位）
　　Ａ　　　Ｂ　　Ｃ　　Ｄ　　Ｅ　　Ｆ
１　日付　取引先　品種　数量　単価　金額
２ *****　○　　ピンク　 10 　　50 　　500
３ *****　○　　レッド 　10 　　30 　　300
４ *****　△　　レッド　 10 　　30　 　300
５ *****　□　　ピンク　 10 　　30 　　300
６ *****　□　　ブルー　 10 　　50 　　500
７ *****　○　　ブルー　 10 　　28　 　280
８ *****　△　　レッド 　10 　　50 　　500
９ *****　○　　レッド 　10　 　26 　　260

●作りたい集計表
取引先　○　　　　　　　　　　　△　　　　　　　　・・・
品種　　数量　単価　金額　　数量　単価　金額　・・・
ピンク
レッド 　２０ 　　２８ 　５６０　 １０　 ３０　 ３００・・・
ブルー　１０　　２８　　２８０　　　　　　　　　　　　・・・

という感じです。ちなみにエクセル2000を利用してます。
これまではロータスを使って問い合わせテーブルで３０円以下を抽出したあとデータ
ベース関数を利用し集計してました。とにかく分かり易く教えていただきたいです。
参考ＨＰなどがあったらそちらも教えていただきたいです。よろしくお願いします。
ちなみにアクセスは持ってません。マクロも簡単なものをロータスで扱ったことが
ある程度です。エクセルが主流の世の中ですので、これを機に勉強したいと思います。
こんな私ですが誰か教えてください。よろしくお願いします。

回答No.3

toshihiko_ooishi · ベストアンサー

一昨日にも、似たような質問に答えたばかりです。

　この質問内容の場合は、
　データシートの名前をSheet1、
　作りたい集計表を仮にSheet2とし、内容を
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
　　　Ａ　　　Ｂ　　　Ｃ　　　Ｄ
１　　取引先　○
２　　品種　　数量　　単価　　金額
３　　ピンク　１０　　５０　　５００
４　　レッド　２０　　２８　　５６０
５　　ブルー　１０　　２８　　２８０　　　　　　　　　　　　

＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
とすると、数量のセルを(仮にB3とします)

=SUM((Sheet1!$B:$B=$B$1)*(Sheet1!$C:$C=$A3)*(Sheet1!$E:$E=<30)*Sheet1!$D:$D)

と入力し、Ctrl+Shift+Enterとします。

同様に、金額のセル(仮にD3とします)

=SUM((Sheet1!$B:$B=$B$1)*(Sheet1!$C:$C=$A3)*(Sheet1!$E:$E=<30)*Sheet1!$F:$F)

と入力し、Ctrl+Shift+Enterとします。
これをそれぞれ下方向へコピーすればよいです。

　単価の欄はVLOOKUPで拾うか、手入力かと。

参考URL：

http://oshiete1.goo.ne.jp/kotaeru.php3?q=462097

お礼

ご回答ありがとうございます。
この方法で挑戦してみます。ありがとうございました。

回答No.5

noname#3166

状況が良く分からないので、また補足があればお願いします。

一応、私なりの提案です。

ピボットテーブルを作る時に、完了する前にレイアウトを選び、ページのところに取引先、行のところに品種と単価、データのところに数量と金額をもってきます。

出来たテーブルの上で右クリック→レポートの書式設定→レポート5あたりを選べばだいたいご希望の集計表になるのではないでしょうか？

あとは、取引先を選び、単価で31以上のチェックをはずしてみたら、どうでしょうか？

ここまで、1分かからないと思います。
もっと良い方法があると思いますが、妥協案？です。

すみません・・・。

お礼

ご回答ありがとうございます。
確かにピボットテーブルを使うと希望通りの集計ができるのですが、自分好みの書式に変更してから、再計算（だったかな？）をしたら見事に最初の書式に戻ってしまいひどいショックを受けたことがあるんです。（笑）
とりあえず、もっと勉強して頑張ってみたいと思います。楽するためにも!!!（笑）

回答No.4

noname#3166

一応確認ですが、品種が同じでも単価は同じではないんですよね？
例えば、レッドにも単価30や単価26があるみたいですが・・・。

とすれば、作りたい集計表の品種の欄はレッドの欄が何種類もあるということですか？

補足をお願いします。

お礼

上の補足の追加です。
単価が３０円以下の物を品種ごとに数量、金額それぞれの合計を出して、集計表の単価はあとで割り返します。
よろしくお願いします。

補足

はい、その通りです。単価は色々あり同じではありません。都度変わるものです。
作りたい集計表はエクセルの番地で言うところのA1-A50という感じです。各取引先毎に
数量、単価、金額とある形です。取引先が横、品種が縦と言えばいいのでしょうか・・。
よろしくお願いします。

回答No.2

utauta

「データ」→「フィルタ」→「オートフィルタ」
とやると、多分１行目の各マス右側に▼が出ると思います。
「単価」のマスの▼をクリック。
３行目「（オプション）」をクリック。
抽出条件の指定：というのが出てきますので、
左上のマスに「３０」右上のマスを「より小さい」にしてＯＫにすると、
単価３０円以下のものが出てきます。

個人的にはこれが簡単だと思うのですが、、

お礼

ご回答ありがとうございます。
確かにオートフィルタを使うと３０円以下のものだけを表示することは可能ですが、
私が最終的に行いたい集計にはならないので困ってます。
仮にそのデータを利用したとしてDSUMでも用いて集計した場合、エクセルだと別位置に
条件設定する必要があるので、品種が５０種ほどあり、取引先は１０－２０件だとかなりの
条件設定を行うことになると思うのですが、もしそれ以外でオートフィルタを使った上で、
最終的にどのような方法で集計したらやりやすいかよろしければお教えいただけませんか？
もし、私の言っている方法が間違っていたらすみません。
よろしくお願いします。

回答No.1

maruru01

こんにちは。maruru01です。

ピボットテーブルを使用すると、様々な集計が出来ます。
質問にある集計データも作成出来ます。
ただ、表の体裁はちょっと違いますが。
ピボットテーブルの作成は、
メニューの[データ]→[ピボットテーブルとピボットグラフ レポート...]をクリックすると、ウィザードが起動します。
詳細は、ヘルプを見るか検索して下さい。
思い通りの表の体裁にしたいなら、やはりVBAでしょう。

お礼

早々にありがとうございます。
確かに以前ピポットテーブルを使って望みどおりのものができたのですが、自分好みの書式
（セルの幅を広げたりとか）にして再計算（だったかな？）をしたら書式が全部元に
戻ってしまい泣きたくなってしまったんですよ。その書式を固定することとかはできるん
ですかね？毎月使うものなので毎月書式をわざわざ変えてというやり方は辛いんですよ。
できれば、データを更新すると同時に望み通りの結果＆書式になっていてくれたら嬉しい
と思うのですが、何か良い方法はありますかね？よろしくお願いします。