エクセル関数を使って月別・商品別売上表を作成したい

質問

１列目売上月、２列目商品名、３列目売上金額からなるデータベースを作成し、このデータベースを基に月別、商品別の売上表を作成する場合、通常はピボットテーブルを使いますが、都度ピボットテーブル更新手続きを省くため、あらかじめ縦方向に売上月、横方向に商品名を入れた表を作成しておき、テーブルにレコードが入力された時点で即、この表に入力結果を反映させたい場合、セルにどのような関数を入力すればよろしいでしょうか。　配列により全セルに関数を一発で入力できるようなことができればうれしいのですが。

回答No.2

tom04 · ベストアンサー

こんばんは！
一例です。
本来であれば行方向に商品名・列方向に月の方が良いと思うのですが、
（月別の場合は12列で済み、商品名の方がかなりの数になると想像できますので・・・）
敢えてご希望通りに↓の画像のように表を作ってみました。
Sheet1のデータをSheet2に集計するようにしています。

Sheet1に作業用の列を設けています。（商品を重複なしにSheet2に表示させるため）

Sheet1の作業列D2セルに
=IF(COUNTIF($B$2:B2,B2)=1,ROW(),"")
という数式を入れオートフィルでずぃ～～～！っと下へコピーしておきます。

Sheet2のA列は月をあらかじめ入力
B1セルに
=IF(COUNT(Sheet1!$D:$D)<COLUMN(A1),"",INDEX(Sheet1!$B:$B,SMALL(Sheet1!$D:$D,COLUMN(A1))))
として右方向へオートフィルでコピー！

最後にB2セルに
=IF(B$1<>"",SUMPRODUCT((Sheet1!$A$2:$A$1000=$A2)*(Sheet1!$B$2:$B$1000=B$1)*(Sheet1!$C$2:$C$1000)),"")
という数式を入れ列方向と行方向にオートフィルでコピーすると画像のような感じになります。

尚、「0」が目障りであれば当方使用のExcel2003の場合ですが
メニュー　→　ツール　→　オプション　→　「表示」タブで　「ゼロ値」のチェックを外しておきます。

参考になれば良いのですが・・・m(__)m

お礼

tom04さん、いつもお世話になります。画面まで新設に例示してくださり、
ありがとうございます。勉強のため、やってみます。

回答No.5

imogasi

この質問の最大の欠点は、月別、商品別を作りたいとのことだが、明細表なのか集計（合計）表なのかはっきりしないこと。
回答も分裂してませんか。それは質問のせいです。ピボットは合計表を作れても、明細表は出来なかったように思う。
エクセルは合計は得意でSUMIFやSUMIFSやSUMPRODUCTで出せちゃう。
＞横方向に商品名を入れた表を作成しておき
ということは条件付き合計計数だけで良いのかな。
しかし明細を残したままで「条件（例えば月指定）による明細の抜き出し」は式が複雑になって、エクセル関数は適してない。
いつもエクセルをよく知らない質問者が、何か良い方法（関数）があると思って３日に位階ぐらい質問するが。
Googleで「imogasi方式」で照会すれば過去のこの質問コーナーの質問例が出て、回答にはタイプがあることが判る。複雑なのはINDEX－SMALLその他を組み合わせる式が出るが、これでよければ使えば良い。
それと条件（例えば月数字や商品名）の一覧は意外に関数では作りにくい。（例えば表に出てきている商品のダブリなく漏れの無い商品名の１セット）
ーー
こういう仕事がらみのことをエクセルでやるなら、VBAを勉強する必要があるというのが私の持論です。
あるいは、データベース系のソフトを使うとか。
難しいことを、十分　　勉強する前に、早くからやりすぎると思う。
＝＝＝＝
集計表で良いなら
例データ
A,BC列
数字 商品名 計数
3 aa 10
3 bb 20
3 cc 30
3 aa 5
4 bb 20
4 cc 30
4 aa 50
4 aa 15
4 cc 5
・・
結果　F1:I3　説明をやさしくするため同一シートでやっている
ー aa bb cc
3 15 20 30
4 65 20 35
・・・
式
G2　の式　=SUMPRODUCT(($A$2:$A$11=$F2)*($B$2:$B$11=G$1)*($C$2:$C$11))
＄を付ける場所に注意
右方向に式を複写
G2:I2を範囲指定して下方向に＋ハンドルを引っ張る
別シートに結果を持っていった場合はSheet1！を付けるkとを注すれば良いだけ。
上記は簡単のため、月を単なる数字でやったが、実際は文字列、日付シリアル値（エクセルの年月日）など色々アル
のでSUNPRODUCT関数の作り方に注意。
本当はこれらを実例でもあげて質問すべきなんだ。エクセルの経験が少ないから、そういうことに気がつかないのだろう。
２００７以後ならSUMIFSが使える。質問にエクセルバージョンも書いてない。

お礼

imogasiさん、ありがとうございます。
ご指摘の通り、質問の焦点がぼやけています。ごめんなさい。
合計表を作成したいことです。ご教示の点大変勉強になりました。詳細なご説明ありがとうございました。

回答No.4

KURUMITO

シート1に元の表が有ってシート2にびピボットテーブルで作ったと同じような表を関数を使って表示する。そのためにはシート1で入力される月や商品名もシート2に自動的に表示され売上金額の合計も表示されるようにすることが必要ですね。そのような場合には関数も複雑になりがちですが計算に負担の掛けない方法としてはできるだけ簡単な分かり易い関数を使って対応することです。シート1では作業列を用意することです。
例えばシート1ではA1セルに売上月、B1セルに商品名、C1セルに売上金額とそれぞれ文字列が入力されており下方にデータが入力されているとします。
D2セルには次の式を入力してE2セルまでオートフィルドラッグしたのちに下方にもオートフィルドラッグします。

=IF(A2="","",IF(COUNTIF(A$2:A2,A2)=1,MAX(D$1:D1)+1,""))

F2セルには次の式を入力して下方にオートフィルドラッグします。

=A2&B2

その後にシート2では例えばA2セルに次の式を入力して右横方向にオートフィルドラッグしたのちに下方向にもオートフィルドラッグします。

=IF(COLUMN(A1)=1,IF(OR(ROW(A1)=1,ROW(A1)>MAX(Sheet1!$D:$D)+1),"",INDEX(Sheet1!$A:$A,MATCH(ROW(A1)-1,Sheet1!$D:$D,0))),IF(COLUMN(A1)>MAX(Sheet1!$E:$E)+1,"",IF(ROW(A1)=1,INDEX(Sheet1!$B:$B,MATCH(COLUMN(A1)-1,Sheet1!$E:$E,0)),IF($A2="","",SUMIF(Sheet1!$F:$F,$A2&A$2,Sheet1!$C:$C)))))

お礼

KURUMITOさん、面白い方法ですね。なるほどです。関数の組み合わせで複雑な仕事ができることはとても興味があります。感謝です。

回答No.3

keithin

ご利用のエクセルのバージョンがご質問に明記されていませんので，お薦めとしてはExcel2007以降を使い
=SUMIFS(Sheet1!C:C,Sheet1!A:A,$A2,Sheet1!B:B,B$1)
のように集計させます。

＃具体的なデータの姿が例示されていませんので，「売上月」は期間（１日から月末日までのような）ではなくイコールで判定できるモノとします

やっぱり2003までしか使えませんというのなら
=SUMPRODUCT((Sheet1!$A$1:$A$1000=$A2)*(Sheet1!$B$1:$B$1000=B$1), Sheet1!$C$1:$C$1000)
などのようになりますが，途端に重くなります。

＃言っても聞く耳はないということでしょうが，ピボットテーブルレポートの使用をお薦めします。
　更新ボタンをぽちっとするのがそんなにイヤなら，ブックを開く際に自動更新とかもできます。
　マクロを使って更新させるとかもやればできますが。

お礼

keithinさん、ありがとうございます。Excel2010ですので。今回はやはりピボットテーブルが自然のようですから、自動更新でやってみます。ありがとうございます。

回答No.1

asobi17

あらかじめ
ピポットテーブルで
縦と横を定めて、

元のデータが追加されるたびに、
ピポットも自動的に更新されるようにしたい！

という解釈でよろしいでしょうか？

その場合なら、

データ→データ更新

をすればいいと思います。

ただ元データを更新すると、
ピポットで、
指定している範囲を飛び出る場合があります。

その場合は
http://www11.plala.or.jp/koma_Excel/contents3/mame3030/mame303001.html

こちらを参考にください。

お礼

asobi17さん、ピボットテーブルの更新の件、問題点も含めてご教示ありがとうございます。とくに問題点について勉強させていただきました。