エクセルで指定した日付の期間の特定の条件にマッチしたデータを別のシート

質問

エクセルで指定した日付の期間の特定の条件にマッチしたデータを別のシートに表示して集計を出すことは可能でしょうか？
ご存知の方がいましたらよろしご教授お願いします。

Sheet1に
日付 顧客名 契約料 営業 対応者　ポイントA 訪問者　ポイントB
1月1日 A社 10,000 伊藤 鈴木 10 鈴木 20
1月5日 B社 20,000 高橋 田中 10 "空白"
1月25日 C社 20,000 伊藤 鈴木 10 吉田 20
2月2日 D社 10,000 伊藤 吉田 10 鈴木 20
2月10日 E社 20,000 高橋 鈴木 10 "空白"
2月18日 F社 10,000 高橋 "空白" 吉田 20
2月25日 G社 20,000 伊藤 吉田 10 吉田 20
3月18日 H社 30,000 高橋 "空白" 鈴木 20
3月28日 I社 10,000 伊藤 林 10 "空白"
4月5日 J社 50,000 高橋 鈴木 10 鈴木 20
というデータがあります。

Sheet2で
日付の期間・対応者・訪問者を任意で入力するとそれを条件として一致した項目を表示しその期間内の対応件数およびポイントの集計を自動で計算させたいです。
うまく説明できないためイメージ画像を添付いたします。
よろしくお願いいたします。

うまく説明できないので画像を添付させていただきます。

回答No.7

tom04 · ベストアンサー

No.1・5・6です！
またまたお邪魔します。
もう一度補足を読ませていただきました。

もしかしてこんな感じで良いのですかね？
今回も前回の表をそのまま使わせてもらいます。

Sheet2の方は変更しないで、Sheet1の作業列の数式だけを操作すれば対応できると思います。

Sheet1の作業列I2セルを
=IF(AND(COUNTBLANK(Sheet2!$A$2:$B$2),Sheet2!$C$2=""),"",IF(Sheet2!$C$2="全員",IF(AND(A2>=Sheet2!$A$2,A2<=Sheet2!$B$2),ROW(A1),""),IF(AND(A2>=Sheet2!$A$2,A2<=Sheet2!$B$2,E2=Sheet2!$C$2),ROW(A1),"")))

J2セルを
=IF(AND(COUNTBLANK(Sheet2!$A$2:$B$2),Sheet2!$I$2=""),"",IF(Sheet2!$I$2="全員",IF(AND(A2>=Sheet2!$A$2,A2<=Sheet2!$B$2),ROW(A1),""),IF(AND(A2>=Sheet2!$A$2,A2<=Sheet2!$B$2,G2=Sheet2!$I$2),ROW(A1),"")))

としてオートフィルで下へずぃ～～～！っとコピーしてみてください。

これで「対応者」・「訪問者」の欄が空白の場合はSheet1の「対応者」・「訪問者」の列が空白のデータのみが表示されると思います。
そして、このセルに「全員」と入力すればその期間内のデータ全てが表示されるはずです。

尚、今回はSheet1に空白があればSheet2に「0」が表示されますので
No.5で書いたように、「ゼロ値」のチェックを外しておいてください。

ここまでできればSheet2の5行目の数式はもっと簡単にできますが
あまりに手を付け過ぎても余計にややこしくなるので、前回の数式そのままにしておきます。
（前回の数式でも問題なく表示されるはずです）

以上、参考になれば良いのですが
今回も的外れならごめんなさいね。m(__)m

お礼

今回もご丁寧なご回答をいただきありがとうございます。

何度もお手間を取らせ申し訳ございません。

補足

何度もご回答を頂ありがとうございます。

私のイメージ通りになりました。
私の説明不足によりご迷惑をおかけしました。

「Sheet2の5行目の数式はもっと簡単にできる」とのことですが参考までに教えていただけますか？
よろしくお願いいたします。

回答No.9

tom04

何度もごめんなさい。

前々回の投稿で間違いがありました。

＞Sheet2の5行目の数式はもっと簡単にできる

の件は5行目ではなく、2行目の間違いでした。
5行目はあの程度の数式は必要になってきます。

親の仇のように、これでもかっ！というくらい顔を出してしまいました。
どうも失礼しました。m(__)m

お礼

ご回答ありがとうございます。

回答No.8

tom04

こんばんは！
交換日記状態ですが・・・

再び補足の件での投稿です。

これまでの表の配置をそのまま使わせてもらいます。

Sheet2のD2セルは
=COUNT(Sheet1!I:I)

E2セルは
=SUM(D5:D1000)

J2セルは
=COUNT(Sheet1!J:J)

K2セルは
=SUM(L5:L1000)

E2・K2セルに範囲指定は1000行目までにしていますが、これ以上のデータはない！という行まで指定しても構いません。
当方使用のExcel2003では最終行が65536行目になりますので
=SUM(D5:D65536)
のようにしてもOKです。

データがない場合は通常「0」が表示されますが、前回の回答で「ゼロ値」のチェックを外す設定をしていれば
「0」も表示されないはずです。

以上、何度も失礼しました。m(__)m

お礼

ご回答いただきありがとうございます。

回答No.6

tom04

No.1・5です！
たびたびごめんなさい！

投稿した後で気づきました。

Sheet2の訪問者が空白の場合はSheet1の訪問者が空白の行を表示させてはいけないのですよね？

Sheet1の作業列J2セルの数式をもう一度変更してください。

J2セルは
=IF(AND(COUNTBLANK(Sheet2!$A$2:$B$2),Sheet2!$I$2=""),"",IF(Sheet2!$I$2="",IF(AND(A2>=Sheet2!$A$2,A2<=Sheet2!$B$2,G2<>""),ROW(A1),""),IF(AND(A2>=Sheet2!$A$2,A2<=Sheet2!$B$2,G2=Sheet2!$I$2),ROW(A1),"")))

ではどうでしょうか？

これで前回の最後部分・・・「ゼロ値」云々というのは無視してもらっても良いと思います。

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

補足

非常に丁寧なご回答をいただきありがとうございます。
あれこれとやっているうちに頭が混乱して・・・
それでは対応者や訪問者がいない場合の集計は・・・！！
私のやりたいことがかなり矛盾していることに今気づきました。

冷静に整理しますと
Sheet2の対応者(C2)及び訪問者(I2)が何も入力されていないときは空白の行を表示

Sheet2の対応者(C2)及び訪問者(I2)に"全員"と入力すると指定期間の全ての行を表示

こんな感じです。

ご面倒をおかけして申し訳ございませんがどうか見捨てずにお付き合いください。
よろしくお願いいたします。

回答No.5

tom04

No.1です！
補足の件について・・・

Sheet2の対応者・訪問者の欄が空白の場合はA2・B2セルに入力した期間のデータすべてを表示したいということですね？

前回の表をそのまま使わせてもらいます。
Sheet1の作業列の数式を変えてみてください。

作業列I2セルは
=IF(COUNTBLANK(Sheet2!$A$2:$C$2)=3,"",IF(Sheet2!$C$2="",IF(AND(A2>=Sheet2!$A$2,A2<=Sheet2!$B$2),ROW(A1),""),IF(AND(A2>=Sheet2!$A$2,A2<=Sheet2!$B$2,E2=Sheet2!$C$2),ROW(A1),"")))

J2セルは
=IF(AND(COUNTBLANK(Sheet2!$A$2:$B$2),Sheet2!$I$2=""),"",IF(Sheet2!$I$2="",IF(AND(A2>=Sheet2!$A$2,A2<=Sheet2!$B$2),ROW(A1),""),IF(AND(A2>=Sheet2!$A$2,A2<=Sheet2!$B$2,G2=Sheet2!$I$2),ROW(A1),"")))

としてI2・J2セルを反指定し、J2セルのフィルハンドルで下へずぃ～～～！っとコピー！

そして、Sheet2のD2・E2・J2・K2の数式も変わってきます。

D2セルは
=IF(COUNTBLANK(A2:B2),"",COUNT(Sheet1!I2:I1000))

E2セルは
=IF(D2="","",SUMIF(Sheet1!$I$2:$I$1000,">0",Sheet1!F2:F1000))

J2セルは
=IF(AND(COUNTBLANK(A2:B2),I2=""),"",COUNT(Sheet1!J2:J1000))

K2セルは
=IF(J2="","",SUMIF(Sheet1!J2:J1000,">"&0,Sheet1!H2:H1000))

としてみてください。

尚、Sheet2の5行目の数式は前回そのまま、オートフィルのコピー方法も前回そのままでOKだと思います。

それから今回は、訪問者の欄が空白の場合は期間内のデータすべてが表示されますので、
Sheet1の訪問者の欄が空白の場合は「0」が表示されると思います。

それを回避するために、当方使用のExcel2003の場合ですが
メニュー　→　ツール　→　オプション　→　「表示タブ」　→　「ゼロ値」のチェックを外しておいてください。
これで「0」が表示されなくなるはずです。

そして、期間の長さによりますがSheet2に表示されるデータ量が多くなる可能性がありますので、
5行目の数式はかなり下までコピーしておいた方が良いかもしれません。

以上、こんなんで良かったですかね？m(__)m

お礼

ありがとうございます。

補足

非常に丁寧なご回答をいただきありがとうございます。
あれこれとやっているうちに頭が混乱して・・・
それでは対応者や訪問者がいない場合の集計は・・・！！
私のやりたいことがかなり矛盾していることに今気づきました。

冷静に整理しますと
Sheet2の対応者(C2)及び訪問者(I2)が何も入力されていないときは空白の行を表示

Sheet2の対応者(C2)及び訪問者(I2)に"全員"と入力すると指定期間の全ての行を表示

こんな感じです。

ご面倒をおかけして申し訳ございませんがどうか見捨てずにお付き合いください。
よろしくお願いいたします。

回答No.4

MackyNo1

参考までに。

例示のレイアウトのように、表示データのある部分だけを罫線で囲みたいなら、A5セルからデータ表示範囲を選択し、条件付き書式で「数式が」にして（エクセル２００７なら「数式を使用して・・・・」を選択）、「=A5<>""」と入力して「書式」ボタンから罫線を設定します。

お礼

教えていただいた設定をするとすっきりと見やすくなりますね。
ありがとうございました。

回答No.3

MackyNo1

エクセルのバージョンが記載されていませんが、ご使用のエクセルが２００７なら２行目の数値を計算するには、SUMIFS関数やCOUNTIF関数を利用するのがお勧めです。

それ以前のバージョンの場合は、すでに回答があるようにSUMPRODUCT関数を使う必要があります。

A5セル以下のデータ表示セルには、以下のような数式を入力しCtrl+Shift+Enterで確定し、下方向にオートフィルします。

A5セル(セルの書式を日付にする）
IF(B5="","",INDEX(Sheet1!A:A,SMALL(IF((Sheet1!$A$2:$A$100>=$A$2)*(Sheet1!$A$2:$A$100<=$B$2)*(Sheet1!$E$2:$E$100=$C$2),ROW($A$2:$A$100),1000),ROW(A1))))

B5セル
=INDEX(Sheet1!B:B,SMALL(IF((Sheet1!$A$2:$A$100>=$A$2)*(Sheet1!$A$2:$A$100<=$B$2)*(Sheet1!$E$2:$E$100=$C$2),ROW($A$2:$A$100),1000),ROW(A1)))&""

C5セル
=INDEX(Sheet1!E:E,SMALL(IF((Sheet1!$A$2:$A$100>=$A$2)*(Sheet1!$A$2:$A$100<=$B$2)*(Sheet1!$E$2:$E$100=$C$2),ROW($A$2:$A$100),1000),ROW(A1)))&""

D5セル
=INDEX(Sheet1!F:F,SMALL(IF((Sheet1!$A$2:$A$100>=$A$2)*(Sheet1!$A$2:$A$100<=$B$2)*(Sheet1!$E$2:$E$100=$C$2),ROW($A$2:$A$100),1000),ROW(A1)))&""

同様にG5セルから右のセルには、以下のように参照セルを変更した数式にします。

G2セル
=IF(H5="","",INDEX(Sheet1!A:A,SMALL(IF((Sheet1!$A$2:$A$100>=$A$2)*(Sheet1!$A$2:$A$100<=$B$2)*(Sheet1!$G$2:$G$100=$I$2),ROW($A$2:$A$100),1000),ROW(A1))))

お礼

参考になりました。
ありがとうございます。

回答No.2

noname#204879

範囲 Sheet1!A1:H11 に例えば dbase という名前(範囲名)を付けておきます。
貴方が「名前」の意味を理解できないときは、以下の説明中の dbase は Sheet1!$A$1:$H$11 と読み替えてください。

Sheet2!D2: =SUMPRODUCT((Sheet1!$A2:$A11>=$A2)*(Sheet1!$A2:$A11<=$B2)*(Sheet1!$E2:$E11=$C2))
Sheet2!E2: =SUMPRODUCT((Sheet1!$A2:$A11>=$A2)*(Sheet1!$A2:$A11<=$B2)*(Sheet1!$E2:$E11=$C2)*(Sheet1!F2:F11))
Sheet2!J2: =SUMPRODUCT((Sheet1!$A2:$A11>=$A2)*(Sheet1!$A2:$A11<=$B2)*(Sheet1!$G2:$G11=$I2))
Sheet2!K2: =SUMPRODUCT((Sheet1!$A2:$A11>=$A2)*(Sheet1!$A2:$A11<=$B2)*(Sheet1!$G2:$G11=$I2)*(Sheet1!H2:H11))
Sheet2!E4: 空白セルのままにしておく
Sheet2!E5: =AND(Sheet1!$A2>=$A$2,Sheet1!$A2<=$B$2,Sheet1!E2=C$2)
　　　　　 （お示しの例では、此処は FALSE と表示されます）
Sheet2!K4: 空白セルのままにしておく
Sheet2!K5: =AND(Sheet1!$A2>=$A$2,Sheet1!$A2<=$B$2,Sheet1!G2=I$2)
　　　　　 （お示しの例では、此処は FALSE と表示されます）

指定の条件に一致した対応者（または、訪問者）に関する項目を表示させるには、[フィルタオプションの設定]を使用します。具体的な手順は割愛するとして、此処では当該設定メニューに入力すべき範囲項目を示します。添付図参照。

　　　　　　　　　対応者の場合　　　　　訪問者の場合
　　　　　　　　　－－－－－－－－　　　－－－－－－－－
抽出先　　　　　　指定した範囲　　　　　指定した範囲
リスト範囲　　　　dbase　　　　　　　　 dbase
検索条件範囲　　　Sheet2!$E$4:$E$5　　　Sheet2!$K$4:$K$5
抽出範囲　　　　　Sheet2!$A$4:$D$4　　　Sheet2!$G$4:$J$4

お礼

画像まで付けていただきありがとうございます。

とてもわかりやすく参考になりました。

回答No.1

tom04

こんばんは！
一例です。
少し長くなりますが、
小さくて見にくいかもしれませんが、↓の画像で説明すると・・・
（Sheet1の1000行目まで対応できるようにしています）

Sheet1に作業用の列を2列設けています。
作業列I2セルに
=IF(AND(A2>=Sheet2!$A$2,A2<=Sheet2!$B$2,E2=Sheet2!$C$2),ROW(A1),"")

J2セルに
=IF(AND(A2>=Sheet2!$A$2,A2<=Sheet2!$B$2,G2=Sheet2!$I$2),ROW(A1),"")

という数式を入れ、オートフィルでずぃ～～～！っと下へコピーします。
最初に書いたように1000行位までコピーしておいても構いません。

次にSheet2のそれぞれのセルの数式を羅列しておきます。

D2セル
=IF(COUNTBLANK(A2:C2),"",SUMPRODUCT((Sheet1!A2:A1000>=A2)*(Sheet1!A2:A1000<=B2)*(Sheet1!E2:E1000=C2)))

E2セル
=IF(COUNTBLANK(A2:C2),"",SUMPRODUCT((Sheet1!A2:A1000>=A2)*(Sheet1!A2:A1000<=B2)*(Sheet1!E2:E1000=C2)*(Sheet1!F2:F1000)))

J2セル
=IF(I2="","",SUMPRODUCT((Sheet1!A2:A1000>=A2)*(Sheet1!A2:A1000<=B2)*(Sheet1!G2:G1000=I2)))

K2セル
=IF(I2="","",SUMPRODUCT((Sheet1!A2:A1000>=A2)*(Sheet1!A2:A1000<=B2)*(Sheet1!G2:G1000=I2)*(Sheet1!H2:H1000)))

A5セル
=IF(COUNT(Sheet1!$I$2:$I$1000)<ROW(A1),"",INDEX(Sheet1!A$2:A$1000,SMALL(Sheet1!$I$2:$I$1000,ROW(A1))))
として隣のB5セルまでオートフィルでコピー

C5セル
=IF(COUNT(Sheet1!$I$2:$I$1000)<ROW(A1),"",INDEX(Sheet1!E$2:E$1000,SMALL(Sheet1!$I$2:$I$1000,ROW(A1))))
として隣のD5セルまでオートフィルでコピー
A5～D5セルを範囲指定し、D5セルのフィルハンドルで下へコピー

I5セル
=IF(COUNT(Sheet1!$J$2:$J$1000)<ROW(A1),"",INDEX(Sheet1!A$2:A$1000,SMALL(Sheet1!$J$2:$J$1000,ROW(A1))))
として隣のJ5セルまでコピー

K5セル
=IF(COUNT(Sheet1!$J$2:$J$1000)<ROW(A1),"",INDEX(Sheet1!G$2:G$1000,SMALL(Sheet1!$J$2:$J$1000,ROW(A1))))
として隣のL5セルまでコピー
最後にI5～L5セルを範囲指定し、L5セルのフィルハンドルで下へコピーすると
画像のような感じになります。

尚、Sheet2の日付列の表示形式は当然、日付にしておいてください。

以上、長々と書きましたが参考になれば幸いです。m(__)m

お礼

早々にとてもご丁寧にご回答いただきありがとうございました。
教えていただいた方法で私のイメージ通りにできました。

本当にありがとうございました。

補足

丁寧なご回答ありがとうございました。
いろいろと試しているともっと使いやすくと欲が出てしまいました。

Sheet2の対応者(C2)及び訪問者(I2)に何も入力していない場合には指定した期間内の行が表示され、
件数とポイントの集計をだす。
（条件が何も入力されていない＝すべて）

期間条件だけでも集計が出せたほうが便利だと思いまして・・・

ご面倒ですがご教授いただければと思います。