エクセルのデータ抽出

質問

複数の条件（重複も含む）に一致するデータを項目別に抽出したいのですが、関数で可能なのかどうかすら分からない状態です。顧客管理目的で、日付を入力すると項目別に表示させるようにすることはできますでしょうか？
図１をデータベースとして顧客データを入力し、図２を管理用として日付を入力すると各項目が表示される感じです。
ご教授の程、よろしくお願い致します。

回答No.12

kagakusuki · ベストアンサー

>顧客数が多く（今後も増えていく）エクセル自体が動作がかなり遅くなってしまいました。何か良い対策はありますでしょうか。

との事でしたので、大幅に見直しまして、関数内で同じ処理を重複して行っている様な箇所を減らすために、これらの共通の処理を行っている部分が行っている処理を、中間処理として一旦、Sheet3上の別のセル(作業セル)上で行って、その値をセルに書きだした上で、以前の関数において同じ処理を行っている箇所の所は、中間処理の結果が書き出されているセルを参照する様にしてみました。
(抽出方法自体も若干見直しています)
　尚、Sheet1及びSheet2のレイアウトはそのまま変えないものとします。

　まず、Sheet3のA1セルに次の関数を入力して下さい。

=ROW(Sheet1!$B$2)+1

　次に、Sheet3のA2セルに次の関数を入力して下さい。

=MAX(MATCH(CHAR(1),Sheet1!$B:$B,-1),$A$1)

　次に、Sheet3のA4セルに次の関数を入力して下さい。

=REPLACE(CELL("filename",Sheet1!$B$2),1,FIND("]",CELL("filename"),FIND(".xl",CELL("filename"))),)&"'!"

　次に、Sheet3のA6セルに次の関数を入力して下さい。

=IF(ISNUMBER(1/(Sheet2!$B$3&"/"&IF(AND(Sheet2!$D$3="",Sheet2!$F$3=""),1,Sheet2!$D$3)&IF(Sheet2!$F$3="","","/"&Sheet2!$F$3))),(Sheet2!$B$3&"/"&IF(AND(Sheet2!$D$3="",Sheet2!$F$3=""),1,Sheet2!$D$3)&IF(Sheet2!$F$3="","","/"&Sheet2!$F$3))+0,"")

　次に、Sheet3のA7セルに次の関数を入力して下さい。

=IF(ISNUMBER(1/(Sheet2!$B$3&"/"&IF(AND(Sheet2!$D$3="",Sheet2!$F$3=""),1,Sheet2!$D$3)&IF(Sheet2!$F$3="","","/"&Sheet2!$F$3))),DATE(Sheet2!$B$3+(Sheet2!$D$3=""),Sheet2!$D$3+(Sheet2!$F$3=""),(0&Sheet2!$F$3)+1),"")

　次に、Sheet3のC2セルに次の関数を入力して下さい。

=SUBSTITUTE(REPLACE(CELL("address",OFFSET(Sheet1!$B$2,1,ROWS($2:2))),1,FIND("$",CELL("address",Sheet1!$B$2),FIND(".xl",CELL("address",Sheet1!$B$2)))-1,),$A$1,)

　次に、Sheet3のD2セルに次の関数を入力して下さい。

=IF(OR(COUNT($A$1,$A$2)<2,ISERROR(INDIRECT("'"&$A$4&$C2&$A$1))),"","'"&$A$4&$C2)

　次に、Sheet3のE2セルに次の関数を入力して下さい。

=IF($D2="","",$D2&$A$1&":"&$C2)

　次に、Sheet3のF2セルに次の関数を入力して下さい。

=IF($E2="","",COUNTIFS(INDIRECT($E2&$A$2),">="&$A$6,INDIRECT($E2&$A$2),"<"&$A$7))

　次に、Sheet3のG2セルに次の関数を入力して下さい。

=IF(ISNUMBER($F2),IF($F2>0,MATCH(9E+307,INDIRECT($E2&$A$2))+$A$1-1,""),"")

　次に、Sheet3のB2セルに数値の0を入力して下さい。
　次に、Sheet3のB3セルに次の関数を入力して下さい。

=IF(ISNUMBER($G3),SUM($G$1:$G2),"")

　次に、Sheet3のA9セルに次の関数を入力して下さい。

=SUM($G:$G)

　次に、Sheet3のC2～G2のセル範囲をコピーして、Sheet3のC3～G3のセル範囲に貼り付けて下さい。
　次に、Sheet3のB3～G3のセル範囲をコピーして、同じ列の4行目以下に貼り付けて下さい。(記念日の入力欄の列数だけ在ればOK)

　次に、Sheet3のJ3セルに次の関数を入力して下さい。

=IF(ROWS($3:3)>$A$9,"",MATCH(ROWS($3:3)-1,$B:$B))

　次に、Sheet3のK3セルに次の関数を入力して下さい。

=IF(ISNUMBER($J3),ROWS($3:3)-INDEX($B:$B,$J3)+$A$1-1,"")

　次に、Sheet3のI2セルに数値の0を入力して下さい。
　次に、Sheet3のI3セルに次の関数を入力して下さい。

=IF(ISNUMBER($K3),IF(AND(INDIRECT(INDEX($D:$D,$J3)&$K3)>=$A$6,INDIRECT(INDEX($D:$D,$J3)&$K3)<$A$7),COUNTIFS(INDIRECT(INDEX($E:$E,$J3)&$A$2),"<"&INDIRECT(INDEX($D:$D,$J3)&$K3),INDIRECT(INDEX($E:$E,$J3)&$A$2),">="&$A$6)+COUNTIF(INDIRECT(INDEX($E:$E,$J3)&$K3),INDIRECT(INDEX($D:$D,$J3)&$K3))+($J3-ROW($B$1))*10000000,""),"")

　次に、Sheet3のI3～K3のセル範囲をコピーして、同じ列の4行目以下に貼り付けて下さい。(貼り付けた行数がSheet1の日付を入力するためのセルの個数を上回るまで)

　次に、Sheet2のA7セルに次の関数を入力して下さい。

=IF(OFFSET(A7,-ROWS($7:7)-1,)="","",IF(ISNUMBER(1/COUNTIF(Sheet3!$I:$I,MATCH(OFFSET(A7,-ROWS($7:7)-1,),Sheet1!$C$2:$H$2,0)*10000000+ROWS($7:7))),INDEX(Sheet1!$C:$H,VLOOKUP(MATCH(OFFSET(A7,-ROWS($7:7)-1,),Sheet1!$C$2:$H$2,0)*10000000+ROWS($7:7),Sheet3!$I:$K,3,FALSE),MATCH(OFFSET(A7,-ROWS($7:7)-1,),Sheet1!$C$2:$H$2,0)),""))

　次に、Sheet2のB7セルに次の関数を入力して下さい。

=IF(A7="","",INDEX(Sheet1!$B:$B,VLOOKUP(MATCH(OFFSET(A7,-ROWS($7:7)-1,),Sheet1!$C$2:$H$2,0)*10000000+ROWS($7:7),Sheet3!$I:$K,3,FALSE)))

　次に、Sheet2のC7セルに次の関数を入力して下さい。

=IF(A7="","",INDEX(Sheet1!$A:$A,VLOOKUP(MATCH(OFFSET(A7,-ROWS($7:7)-1,),Sheet1!$C$2:$H$2,0)*10000000+ROWS($7:7),Sheet3!$I:$K,3,FALSE)))

　次に、Sheet2のA7～C7のセル範囲をコピーして、Sheet2のA8～C13のセル範囲に貼り付けて下さい。
　次に、Sheet2のA5～C13のセル範囲をコピーして、同シートのE5～G13のセル範囲とI5～K13のセル範囲に貼り付けて下さい。
　次に、Sheet2の5～13行目全体をまとめてコピーして、Sheet2の16～24行目の行範囲に貼り付けて下さい。
　次に、Sheet2の18～24行目(16～24行目でも可)全体をまとめて選択してから、Excelの置換機能を利用して、

ROWS($7:

という部分を

ROWS($18:

に[すべて置換]して下さい。

　そして最後にSheet2の各記念日の表の一番上のセルである、A列、E列、I列の行番号7行目と18行目のセルに、各記念日の名称を入力して下さい。

　以上です。

　尚、Sheet1の記念日欄の列数を増やす際には、Sheet2とSheet3において

Sheet1!$C$2:$H$2
　↓
Sheet1!$C$2:$I$2

と

Sheet1!$C:$H
　↓
Sheet1!$C:$I

の置換を行うという方法か、或いはSheet1のC列とH列の間に列の挿入を行うという方法を用いて下さい。

　尚、今回の関数の場合、

$E$16
　↓
$I$16

や

$E18
　↓
$I18

の置き換えは不要です。

お礼

kagakusuki様
この度は、知識の浅い私の様々な質問に対して細部までご教示いただき、本当にありがとうございました！特に、年月日検索を年月検索でも対応可能な方法をお教えいただいたことは、本当に感謝の言葉しかありません。kagakusuki様含め、皆様方のお陰で、想像以上に完成度の高い帳票が完成しました。お教えいただいたものを全て理解するには、まだまだ時間がかかりますが、ひとつひとつ学びながら習得していきたいと思います。
この度は、貴重なお時間を割いて知恵をお貸しいただき、本当にありがとうございました！

回答No.1

bunjii

>図１をデータベースとして顧客データを入力し、図２を管理用として日付を入力すると各項目が表示される感じです。
辛うじて読める範囲の画像です。
行番号と列記号を当方の検証用に置き換えて作表してしてみました。
貼付画像のK5セルに次の数式を設定し、下へ必要数コピーします。
=SUMPRODUCT(($A$2:$A$11=I6)*($C$1:$G$1=I$3)*($C$2:$G$11=DATE($J$1,$K$1,$L$1))*$C$2:$G$11)
該当が無いときはシリアル値の0が返りますので、条件付き書式で0のとき文字の色を白にします。
但し、住所と名前の組み合わせは固定されているものとしてA列の住所のみで検索条件としています。
記念日の種別毎に日付の検索を行えるようにしてあります。
記念日Aの日付全体をコピーでクリップボードへ記憶させて、記念日B、記念日C、記念日D、記念日Eへ順次貼り付ければ貼付画像のようになります。

お礼

bunjii様
大変参考になりました。ただ今回は、Kagakusuki様から頂いた式を使用させて頂こうと思います。お忙しい時間を割いていただき、知恵をお貸し頂きまして本当にありがとうございました。

補足

bunjii様、検証までしていただき、ありがとうございます！
お教えいただいた内容なのですが、Sheet2の検索で各記念日で該当するものだけを抽出して上詰めで表示し、該当しないものは表示しないようにすることはできますでしょうか？例えば、Sheet2の記念日Aで該当者Aさん以外を表示させないようにや、記念日Dで該当者DさんとFさんを上詰めで表示して他を表示させないようにする感じです。
質問ばかりで申し訳ありません。よろしくお願い致します。

kagakusuki様、年月日単位で該当データを抽出できる形でご教授いただきありがとうございます！お教えいただいた通りに入力を進めてみたのですが、なかなか思うようにいきません。エクセルは一般的な知識くらいしかない者なので、ただただお教え頂いた通りに入力しています。ただ、これがとても重要だったら申し訳ありません。各記念日なのですが、ある基準日となる日付を別セルに入力して、そこから各記念日を数式にて算出しております。
例えば、基準日として「2015/3/9」と入れると、各記念日が自動で表示されるような形です。最初から記載しておけばよかったのですが、お教えいただいた数式の中にDATE関数のようなものが入っていたので、素人ながらもしかしたらと思ってしまいました。ご丁寧にお教えいただいたにも関わらず大切と思われる情報を記載していなかったこと、本当に申し訳なく思っています。もし、お許しいただけるようでしたら、尚のご教授いただければ大変ありがたく思います。考えてもいなかった、年月日別での検索方法は、とても理想的です。