データベース抽出とSUMPRODUCT関数

質問

データベース抽出とSUMPRODUCT関数

エクセル2007で以下のような表を作って
関数の勉強をしています。

下の画像の上段のシート（シート1）の表が検索フォームです。

下段のシート（シート2）が購入商品リストです。

シート1のA3セルに請求書NOを入力させることで、
B列からE列までにそれぞれのデータを表示させたいのですが
シート2に表示されているように、同じ購入者に複数の
請求書NOが付番されているために、購入者ごとに金額を上手く合計できませんでした。

sumproduct関数も使ってみたのですが、エラーが出てしまいました。

例えばAさんは、請求書が1と2がありますが、請求書は
1枚にしたいので、購入金額を合算して、検索表示したいのです。

加えて、請求書NOと購入商品も下の画像のように
一括にして表示することはできるのでしょうか。

初心者で申し訳ないですが、お願いします。

この投稿のマルチメディアは削除されているためご覧いただけません。

回答No.14

tom04 · ベストアンサー

追加質問について・・・

あるセルに「1～5」と表示させたい場合は
No.1に書いたように「購入者」が飛び飛びでは全く意味の成さないものになってしまいます。

質問をそのまま単純に受け止めて、必ず購入者は続いているという前提ですが、
お示しの数式では「MATCH」関数部分でエラーになります。

INDEX関数内のMATCH関数の「検査値」が「0」になっていますので、B列にない「0」を検査しています。
それでエラーになります。

無理やりですが、お考えになった数式を使ってみると
=B2&"～"&INDEX(B:B,MATCH(MAX(B:B),B:B,0))
（MATCH関数で「照合の型」を「-1」とすることはめったにありません。
通常は完全一致の「0」（FALSE）か、たまに「1」（TRUE）を使うことはありますけど・・・）
という感じでしょうか？

でも
=MIN(B:B)&"～"&MAX(B:B)
とか
=B2&"～"&MAX(B:B)
でも同じ結果になると思いますよ。

最初に書いたようにB列が連番の場合は有効ですが、あまりお勧めできる方法ではないと思います。

それから余談ですが、関数は直接手入力しているのでしょうか？
余計なお世話かもしれませんが、数式バーの左側にある「fx」のアイコンをクリックし
使いたい関数を選択すると「関数の引数ダイアログボックス」が出ますので、そこに一つずつ入力してみてはどうでしょうか？
そうすると関数の意味も理解できると思います。
慣れてくればこの画面を出さずに直接入力できるようになるはずです。
もし、この方法で数式を作っていらっしゃるのであればごめんなさいね。m(__)m

回答No.13

tom04

前回の数式の訂正です。

すでに入力間違いにお気づきだと思いますが・・・

=COUNTIF(J1:V11,"?*")
を
=COUNTIF(J1:IV1,"?*")
に訂正してください。

何度もごめんなさい。m(__)m

補足

何度もありがとうございました。

完ぺきです。

回答No.12

tom04

またまた・・・呼ばれて「ハクション大魔王」のように登場です。

最後の質問ですが・・・
通常、文字列のセル数を数える場合はCOUNTA関数で対応できますが、
空白でも数式が入っている場合はカウントしてしまいますので、
今回はそれが使用できないと思います。

そこで氏名数を表示したいセルに
（氏名がSheet2のJ1セル以降1行目に表示するようにしているとします。K列以降であれば列番号を変更してください。）

=COUNTIF(J1:V11,"?*")
としてみてはどうでしょうか？
上記の式はSheet2のセルに表示させる場合ですが
Sheet1であれば
=COUNTIF(Sheet2!J1:IV1,"?*")
のようになるかと思います。

尚、当方使用Excel2003の最終列はIV列になりますので、そこまで範囲指定しています。
実際はそれほど必要ないと思いますので、
列の範囲指定は適当に変更してみてください。

これで数式が入っていて、見た目は空白の場合にも対応できると思います。

お役に立ちましたかね？m(__)m

補足

なるほど、よくわかりました。

そうすると別件なのですが、
現在、請求NOがB列に連番で表示されています。

例えばNOが1、2、3、4、5と表示されている場合

請求書にNO「1～5」と表示したいとき
関数はどうなるのでしょうか。
=b2&"～"INDEX(B:B,MATCH(0,B:B,-1))

でもだめでした。
お願いします。

回答No.11

MackyNo1

＞できれば購入者ごとの商品と件数も表示できればうれしいのですが・・・

基本的にこのような集計をするなら、ピボットテーブルを利用することをお勧めします。
この機能を用いれば、自動的に重複のない購入者や購入商品のリストを自動的に作成してくれます（もちろん数式で重複のないリストを表示させることもできますが）。

例えば行ラベルに購入者、列ラベルとΣ値に購入商品をドラッグしてみてください。

また関数で集計して個数を表示させる場合も、配列数式を多くのセルに入力するのは、メモリーを消費するだけでなく再計算に時間がかかるなど問題が発生する可能性があり好ましくありません。

例えばSUMPRODUCT関数は典型的な配列数式ですが、他の関数で代用できる場合はそれを使う必要があります。

エクセル２００７ではCOUNTIFS関数で複数条件の個数の集計が簡単にできますので、こちらを利用するようにした方が良いと思います。

回答No.10

tom04

立て続けにおじゃまします。

前回のSheet2の表でJ列は必要ないと思います。
Sheet2のJ列すべてを削除してみてください。
K列以降が左側に移動してエラーになると思いますので
J2セルにもう一度↓の数式を入れて列・行方向にオートフィルでコピーしてみてください。
Sheet2のJ2セルに
=IF(OR($H2="",J$1=""),"",SUMPRODUCT(($B$2:$B$1000=J$1)*($C$2:$C$1000=$H2)))
です。

さて、次にSheet1の表の方ですが、↓の画像のようにしてみました。
Sheet1のA2セルに購入者の氏名を入力するとその人のデータが表示されます。
（もちろん請求NOは飛び飛びでもかまいません）

Sheet1のB2セルに
=IF(OR($A$2="",COUNTIF(Sheet2!$B$2:$B$1000,$A$2)<ROW(A1)),"",INDEX(Sheet2!$A$2:$D$1000,SMALL(IF(Sheet2!$B$2:$B$1000=$A$2,ROW($A$1:$A$999)),ROW(A1)),MATCH(B$1,Sheet2!$A$1:$D$1,0)))
これは配列数式ですので、Shift+Ctrlキーを押しながらEnterキーで確定です。
これをD2セルまでオートフィルでコピーし、B2～D2セルを範囲指定　→　D2セルのフィルハンドルで下へコピーします。

最後にE2セルに
=IF(A2="","",SUM(D:D))
という数式を入れています。

以上、親の仇のように顔を出してしまいましたが
参考になりますかね？m(__)m

補足

いろいろありがとうございます。

大変参考に、勉強になっています。

情報の洪水でおぼれそうですが（笑）。

少し整理してみます（笑）

また、よろしくお願いします。

エクセルは奥が深いです！

回答No.9

tom04

またまたお邪魔します。
交換日記状態ですが・・・

前回の補足に二点質問がありましたが
両方とも画像があったほうが理解しやすいと思いますので、
一つずつ投稿します。
（画像を二つ並べると小さくて見えにくいと思うので）

まず、
＞できれば購入者ごとの商品と件数も表示できればうれしいのですが・・・
についてです。

前回同様Sheet2に表示させるとします。表の配置も↓のようにした方が良いかもしれません。
せっかく前回商品名を重複なしに表示していますので、それを利用するようにしてみました。
画像では左側部分が途切れていますが、前回と同じSheet配置だと思ってください。
作業用の列をもう1列追加しています。
これで購入者を重複なしに表示させます。
（実は前回の商品を重複なしに表示させる方法とまったく同じで、今回は列方向にしているだけです）

作業列F2セルに
=IF(COUNTIF($B$2:B2,B2)=1,ROW(A1),"")
として下へコピー

J2セルは
=IF(H2="","",H2)
としてこれも下へコピー！（H列とまったく同じ表示になります。）

K1セルに
=IF(COUNT($F$2:$F$1000)<COLUMN(A1),"",INDEX($B$2:$B$1000,SMALL($F$2:$F$1000,COLUMN(A1))))
という数式をいれ、列方向（右方向）にコピー

最後にK2セルに
=IF(OR($J2="",K$1=""),"",SUMPRODUCT(($B$2:$B$1000=K$1)*($C$2:$C$1000=$J2)))
という数式をいれ、列・行方向にコピーすると
画像のような感じになります。
尚、オートフィルのコピーはデータが表示されなくても良いですので、
しっかり多めにコピーしておきます。
これでSheet2の元データが増えても自動で表示されます。

とりあえず一つ目はこれで良いでしょうか？

もう一つの質問に関してはこの後投稿しようと思いますが、
表の配置そのものを変更し、
氏名を入力するとその人のデータすべてを表示したほうが良いと思います。m(__)m

お礼

tom04さん、ありがとうございました。

最後に、K列から右に表示されている氏名だけをカウントする方法
はあるのでしょうか。

氏名の件数と請求書の枚数は
同じになると思うので、チェックしたいのです。

よろしくお願いします。

補足

ありがとうございました。
できました～。
完ぺきでした。

いろいろ助かりました～。
また、よろしくお願いします。

回答No.8

tom04

No.1です！
補足を読ませてもらいました。

＞購入商品の件数を、種類ごとにカウントしてどこかに表示することはできるのでしょうか・・・

とありましたので、再び顔を出しました。

1行が1件としています
↓の画像のようにSheet2に表示させるとして、
作業列を1列設けています。
作業列E2セルに
=IF(COUNTIF($C$2:C2,C2)=1,ROW(A1),"")
という数式をいれ、オートフィルでずぃ～～～！っと下へコピー

そして、G2セルに
=IF(COUNT($E$2:$E$1000)<ROW(A1),"",INDEX($C$2:$C$1000,SMALL($E$2:$E$1000,ROW(A1))))
H2セルに
=IF(G2="","",COUNTIF($C$2:$C$1000,G2))
（前回同様Sheet2の1000行目まで対応できる数式です）

という数式をいれ、G2・H2セルを範囲指定し、H2セルのフィルハンドルで下へコピーすると
画像のような感じになります。

尚、今回はすべての商品を表示するようにしていますが
ある特定の商品だけを表示したい場合は少し変わってきます。

以上、参考になればよいのですが・・・m(__)m

補足

ありがとうございます。
完ぺきです！
できれば購入者ごとの商品と件数も表示できれば
うれしいのですが。

あと、例えばAさんの請求書NOがとびとびになる時
（例えば1と3と5と7とかに入る場合）
請求書NOの表示がリストと合わず、困ったことになってしまいました。

tom04さんの言われたとおりです。

請求書NOの表示は最初から最後ではなく
氏名の分はすべてシート1にNO表示することは可能なのでしょうか。

注文ばかりですいません。
よろしくお願いします。

回答No.7

MackyNo1

私の例示したレイアウトの方が良いと思ったのであえて記載しませんでしたが、質問で最初に例示されたレイアウトの表のように請求書一括Noをセルに表示したいなら、私の添付画像のレイアウト（F6セルに請求書番号）で数式を作ると以下のようになります（請求書番号の最初の番号をF6セルに入力）。

=F6&"～"&MAX(INDEX((B2:B10=F2)*A2:A10,))

他のセルの値も、すでに提示した数式のセル位置を変更すれば、それぞれの値を簡単に表示できると思います。

回答No.6

imogasi

関数でやりたいらしいが、関数の複雑な式を考えるのも良いが、マクロの記録で出来る典型的な場合である。
それであえて説明してみる。
例データ
請求書No 購入者 購入商品 購入金額
1 A チョコ 2000
2 A アイス 1500
3 B ガム 3000
4 C クッキー 500
5 C チョコ 800
6 D アイス 1200
7 D グミ 1200
8 F グミ 300
9 E ガム 4000
10 E ガム 5000
ーー
準備
条件その他　F2:H15
購入者 殿　＜－－F2、H2に見出しとして入力
D 　　＜－－F3に式　＝G2、しょしきでフォント色を白色にいて見えなくする。
請求金額　　　　　　　＜－－F4F2、H2に見出しとして入力　G4に=SUM(H6:H10)
請求書No 購入商品 購入金額　＜－－F5:H5
ーーー
操作
ツールーマクロー新しいマクロの記録
Ctrl+のとことで　F
OK
データーフィルターフィルタオプションの設定
指定した範囲にチェック
リスト範囲　　$A$1:$D$11
検索条件範囲 $F$2:$F$3
抽出範囲 $F$5:$H$15
OK
ーーー
結果
購入者 D 殿
D
請求金額 2400
請求書No 購入商品 購入金額
6 アイス 1200
7 グミ 1200
ーーーー
G2セルを　A　に変えて、CTRL+SHIFT+F　を押す。
購入者 A 殿
A　　＜－－見えない
請求金額 3500
請求書No 購入商品 購入金額
1 チョコ 2000
2 アイス 1500
となる。
ーーー
自動印刷をするなら
標準モジュールに在る下記コードで、最後に１行印刷コードを追加
Sub Macro2()
'
' Macro2 Macro
'
' Keyboard Shortcut: Ctrl+Shift+F
Range("A1:D11").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
"F2:F3"), CopyToRange:=Range("F5:H13"), Unique:=False
Range("F2:H15").PrintOut　　＜－－－この１行を追加
End Sub

回答No.5

KURUMITO

回答Ｎｏ4です。
シート1のＢ2への式は次のように訂正してください。

=IF(AND(COLUMN()=2,ROW(A1)=1),$A$2&"～"&INDEX(Sheet2!$A:$A,MATCH($A$2+1,Sheet2!$E:$E,1)),IF(AND(COLUMN()=3,ROW(A1)=1),VLOOKUP($A$2,Sheet2!$A:$B,2,FALSE),IF(AND(COLUMN()=4,ROW(A1)<MATCH($A$2+1,Sheet2!$E:$E,1)),INDEX(Sheet2!$C:$C,MATCH($A$2+0.01*ROW(A1),Sheet2!$E:$E,0)),IF(AND(COLUMN()=5,ROW(A1)=1),SUM(INDEX(Sheet2!$D:$D,MATCH($A$2+0.01,Sheet2!$E:$E,0)):INDEX(Sheet2!$D:$D,MATCH($A$2+1,Sheet2!$E:$E,1))),""))))

回答No.4

KURUMITO

シート1にお望みのような表を作るためにはシート2に作業列を設けて対応します。
Ｅ2セルには次の式を入力して下方にオートフィルドラッグします。

=IF(A2="","",IF(B2<>B1,A2+0.01,E1+0.01))

シート1ではＡ2セルに請求書ＮＯを入力します。
Ｂ2セルには次の式を入力してＥ2セルまでオートフィルドラッグしたのちに下方にもオートフィルドラッグします。

=IF(AND(COLUMN()=2,ROW(A1)=1),$A$2&"～"&MOD(INDEX(Sheet2!$E:$E,MATCH($A$2+1,Sheet2!$E:$E,1)),1)*100,IF(AND(COLUMN()=3,ROW(A1)=1),VLOOKUP($A$2,Sheet2!$A:$B,2,FALSE),IF(AND(COLUMN()=4,ROW(A1)<MATCH($A$2+1,Sheet2!$E:$E,1)),INDEX(Sheet2!$C:$C,MATCH($A$2+0.01*ROW(A1),Sheet2!$E:$E,0)),IF(AND(COLUMN()=5,ROW(A1)=1),SUM(INDEX(Sheet2!$D:$D,MATCH($A$2+0.01,Sheet2!$E:$E,0)):INDEX(Sheet2!$D:$D,MATCH($A$2+1,Sheet2!$E:$E,1))),""))))

これでシート1にはお示しのようなデータが表示されます。