エクセルのデータ抽出

質問

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

回答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.13

Nouble

済みません
エクセルオンラインが
セッションの関係で編集不能
落ちまくるもので
確認、作成が立ちゆいません

もう少し頑張っては見ますが
取り敢えず
作成した基本形だけあげます
http://1drv.ms/1HsGpNf

一致したものだけを拾い出し
小数点以下が何列目か
小数点以上が何行目か
を、示します

このデータをもとにoffset関数等を使うと
さらっと出てくるもの
と、思います

が
エクセルオンラインが、…

=
LARGE(
　INDEX(
　　INDIRECT(
　　　"sheet1!$D$6:$H”&(
　　　　SUMPRODUCT(
　　　　　NOT(
　　　　　　ISBLANK(
　　　　　　　Sheet1!$D:$D
　　　　　　)
　　　　　)+0
　　　　)-1+$T$20
　　　)
　　)=
　　DATE(
　　　$J$3,$L$3,$N$3
　　),,
　)*
　(
　　(
　　　COLUMN(
　　　　INDIRECT(
　　　　　"sheet1!$D$6:$H”&(
　　　　　　SUMPRODUCT(
　　　　　　　NOT(
　　　　　　　　ISBLANK(
　　　　　　　　　Sheet1!$D:$D
　　　　　　　　)
　　　　　　　)+0
　　　　　　)-1+
　　　　　　ROW(
　　　　　　　Sheet1!$B$5
　　　　　　)
　　　　　)
　　　　)
　　　)-
　　　COLUMN(
　　　　Sheet1!$C$5
　　　)
　　)/10+(
　　　ROW(
　　　　INDIRECT(
　　　　　"sheet1!$D$6:$H”&(
　　　　　　SUMPRODUCT(
　　　　　　　NOT(
　　　　　　　　ISBLANK(
　　　　　　　　　Sheet1!$D:$D
　　　　　　　　)
　　　　　　　)+0
　　　　　　)-1+
　　　　　　ROW(
　　　　　　　Sheet1!$B$5
　　　　　　)
　　　　　)
　　　　)
　　　)-
　　　ROW(
　　　　Sheet1!$C$5
　　　)
　　)
　),
　ROW(
　　INDIRECT(
　　　"$A$1:$A$"&
　　　SUMPRODUCT(
　　　　NOT(
　　　　　ISBLANK(
　　　　　　Sheet1!$D:$D
　　　　　)
　　　　)+0
　　　)-1
　　)
　)
)

参考URL：

http://1drv.ms/1HsGpNf

回答No.11

MackyNo1

>検索日付なのですが、ここを年と月で検索し、該当するデータを全て表示させることはできますでしょうか？

最初の質問内容（添付画像のデータ）と条件が違いますね。

このようなご質問では、条件が異なると全く使用する関数が異なる（最適な関数が異なる）など、質問の条件を変更するのは好ましくありません。

また、今回のようなケースでは、使用する数式が複雑になりますので、例示のレイアウトや条件は変更しないのが基本です。

ちなみに、他の回答者の皆さんが提示されている数式は、数式のメンテナンスがしにくい煩雑な数式になっていると思いますので、もう少しメンテナンスしやすい数式を参考までに提示します。

たとえば、添付画像で提示されたレイアウトの場合（K3セルに「年」M3セルに「月」、I5セルに「記念日」、Sheet1のA2セル以下1000行目までに「住所」が入力されている場合）、以下の式を入力し下方向にオートフィルコピーすると該当年月の住所データが表示できます。

=INDEX(Sheet1!A:A,SMALL(INDEX((INDEX(TEXT(Sheet1!$C$2:$F$1000,"yyyymm"),,MATCH($I$5,Sheet1!$C$1:$G$1,0))<>$K$3&$M$3)*10000+ROW($2:$1000),),ROW(1:1)))&""

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

一般に上記のようなデータ範囲の広い配列数式を多くのセルに入力すると、再計算に時間がかかりシートの動きが重くなります。
また、計算速度は幾分改善されることが多いのですが、計算用の補助列に多数の関数（たとえば1万行）を入力すると、メモリーを大量に消費し、エクセルがハングアップするなどのデメリットがあります。

したがって、このようなケースでは、たとえば表示用シートの記念日欄をドロップダウンリストにして選択できる状態にしておき、このセルの値を変更して、該当データを抽出する方法がお勧めです。

どうしても、すべての記念日のデータを一括して表示したい場合は、その他の列には計算負荷の少ない数式(VLOOKUP関数とINDEX関数）を利用することになります。

今回のケースでしたら、たとえば住所（や名前など）は同じものがないという条件なら、計算負荷の少ない関数で上記の数式で得た「住所」から「名前」や「日付」を引っ張ってくることが可能です。

＃もし上記の回答を試されるつもりがあるなら、元データのシートのレイアウト（セル番地）と表示データのレイアウトを提示していただければ、具体的な数式を提示します。

お礼

MackyNo1様
この度は、質問内容が当初と離れてしまったことで不快な思いをさせてしまい、本当に申し訳ありませんでした。そんな中で、様々な知恵をお貸しいただき、本当に感謝しております。今回は、当初の質問に対する皆様方のご教示の中で、検索方法が自分の想像していなかった年月検索というベストな提案をいただいたことから、知識の浅い私の自分勝手でこのような形となってしまいました。今後は、こういったことのないよう、マナーを覚えながら皆様への質問をさせていただこうと思います。
この度は、本当にありがとうございました。

回答No.10

bunjii

回答No.9の修正版です。
元データをSheet1、抽出データをSheet2として貼付画像のようにしてみました。
また、数式にOFFSET関数が使われていましたので、デバック時に中間値を確認できないため関数の使い方を変更しました。
Sheet2!A5=IFERROR(INDEX(Sheet1!$A$1:$H$11,LARGE(INDEX((Sheet1!$C$2:$H$11>=DATE($B$1,$C$1,1))*(Sheet1!$C$2:$H$11<=EOMONTH(DATE($B$1,$C$1,1),0))*ROW(Sheet1!J$2:J$11)*(Sheet1!$C$1:$H$1=A$3),0),1+SUM(INDEX((Sheet1!$C$2:$H$11>=DATE($B$1,$C$1,1))*(Sheet1!$C$2:$H$11<=EOMONTH(DATE($B$1,$C$1,1),0))*(Sheet1!$C$1:$H$1=A$3),0))-ROWS(A$5:A5)),1),"")&""

Sheet2!B5=IFERROR(INDEX(Sheet1!$A$1:$H$11,LARGE(INDEX((Sheet1!$C$2:$H$11>=DATE($B$1,$C$1,1))*(Sheet1!$C$2:$H$11<=EOMONTH(DATE($B$1,$C$1,1),0))*ROW(Sheet1!K$2:K$11)*(Sheet1!$C$1:$H$1=A$3),0),1+SUM(INDEX((Sheet1!$C$2:$H$11>=DATE($B$1,$C$1,1))*(Sheet1!$C$2:$H$11<=EOMONTH(DATE($B$1,$C$1,1),0))*(Sheet1!$C$1:$H$1=A$3),0))-ROWS(B$5:B5)),2),"")&""

Sheet2!C5=IFERROR(INDEX(Sheet1!$A$1:$H$11,LARGE(INDEX((Sheet1!$C$2:$H$11>=DATE($B$1,$C$1,1))*(Sheet1!$C$2:$H$11<=EOMONTH(DATE($B$1,$C$1,1),0))*ROW(Sheet1!J$2:J$11)*(Sheet1!$C$1:$H$1=A$3),0),1+SUM(INDEX((Sheet1!$C$2:$H$11>=DATE($B$1,$C$1,1))*(Sheet1!$C$2:$H$11<=EOMONTH(DATE($B$1,$C$1,1),0))*(Sheet1!$C$1:$H$1=A$3),0))-ROWS(A$5:A5)),MATCH(A$3,Sheet1!$A$1:$H$1,0)),"")

元データの行数が増えるときは10行目と11行目に必要行数を挿入してください。
また、記念日の種類が増えて列数が増えるときはG列とH列の間に必要列数を追加してください。
上記のようにすれば元データの行と列が増えても数式は追従するはずです。

お礼

bunjii様
シート別でお教えいただき、本当にありがとうございます！
数式の知識もほとんどない私にとっては、学ばせていただくところばかりでした。
お教えいただいた内容を全て理解するにはまだまだ勉強が必要ですが、ひとつひとつ理解しながら使わせていただきたく思います。当初の質問から離れた内容についても、事細かにご教示いただき、本当にありがとうございました。ベストアンサーを２つ選べるのならば、間違いなくbunjii様にもというところですが、今回は年月別抽出という私の想像もしていなかったベストな検索方法をご提案いただいたという観点から、kagakusuki様をベストアンサーに選ばせていただきました。この度は、貴重なお時間を割いて、知恵をお貸しいただきまして本当にありがとうございました！

回答No.9

bunjii

>検索日付なのですが、ここを年と月で検索し、該当するデータを全て表示させることはできますでしょうか？
できますが数式を理解できるか否かで応用の可否が決まります。
改めて画像を貼付しました。（解像度が心配です）
I5=INDEX($A:$A,IFERROR(SUMPRODUCT(LARGE(($C$2:$G$11>=DATE($J$1,$K$1,1))*($C$2:$G$11<=EOMONTH(DATE($J$1,$K$1,1),0))*ROW(I$2:I$11)*($C$1:$G$1=I$3),COUNTIFS(OFFSET($A$1,1,MATCH(I$3,$A$1:$G$1,0)-1,ROW(A10)),">"&DATE($J$1,$K$1,0),OFFSET($A$1,1,MATCH(I$3,$A$1:$G$1,0)-1,ROW(A10)),"<="&EOMONTH(DATE($J$1,$K$1,1),0))-ROW(A1)+1)),ROW(I$12)),1)&""

J5=INDEX($B:$B,IFERROR(SUMPRODUCT(LARGE(($C$2:$G$11>=DATE($J$1,$K$1,1))*($C$2:$G$11<=EOMONTH(DATE($J$1,$K$1,1),0))*ROW(I$2:I$11)*($C$1:$G$1=I$3),COUNTIFS(OFFSET($A$1,1,MATCH(I$3,$A$1:$G$1,0)-1,ROW(A10)),">"&DATE($J$1,$K$1,0),OFFSET($A$1,1,MATCH(I$3,$A$1:$G$1,0)-1,ROW(A10)),"<="&EOMONTH(DATE($J$1,$K$1,1),0))-ROW(A1)+1)),ROW(I$12)),1)&""

K5=INDEX(OFFSET($A$1,0,MATCH(I$3,$A$1:$G$1,0)-1,ROW(A$12)),IFERROR(SUMPRODUCT(LARGE(($C$2:$G$11>=DATE($J$1,$K$1,1))*($C$2:$G$11<=EOMONTH(DATE($J$1,$K$1,1),0))*ROW(I$2:I$11)*($C$1:$G$1=I$3),COUNTIFS(OFFSET($A$1,1,MATCH(I$3,$A$1:$G$1,0)-1,ROW(A10)),">"&DATE($J$1,$K$1,0),OFFSET($A$1,1,MATCH(I$3,$A$1:$G$1,0)-1,ROW(A10)),"<="&EOMONTH(DATE($J$1,$K$1,1),0))-ROW(A1)+1)),ROW(I$12)),1)

K5セルは条件付き書式で値が0のとき文字の色を白にしました。

I5、J5、K5をそれぞれ下へコピーします。
I5:K11をセットで右側の空いているセルへコピーします。
コピー先の行位置が上下すると正しい値が抽出ません。

回答No.8

kagakusuki

>記念日Eの横にもうひとつ記念日Fというのを作成したのですが、うまくデータを反映させることができません。数式的には記念日Eで使用しているE18、F18、G18をコピーで使用してE16をI16に置き換えて使ってみました。Sheet3のAB列を何か変更しなくてはいけませんでしょうか？

　他には、Sheet3のA3セルに入力する関数中の

Sheet1!$C$2:$G$2

となっている箇所を

Sheet1!$C$2:$H$2

に変える必要がありますし、

Sheet1!$C:$G

となっている箇所を

Sheet1!$C:$H

に変える必要もあります。
　それと、Sheet1のリストに含まれているセルの数が増えた事に合わせて、Sheet3の作業列の行数も増やさなくてはなりません。
　Sheet3のA列はA3から数えて「記念日の入力欄の列の数」以上の数のセルに関数を入力(コピー&ペースト)しておく必要があります。
　Sheet3のB列はB3から数えて「『記念日の入力欄ごとの日付が入力されている最終行』の『(実際にデータが入力されている最初の行である3行目から数えた)行番号』の合計数」以上の数のセルに関数を入力(コピー&ペースト)しておく必要があります。

　Sheet2の関数に関しては、記念日Eの抽出結果を表示させる表をコピー&ペーストした後、E16をI16に置き換える以外にも、

$E18
　↓
$I18

という置き換えと

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

という置き換えを行う必要があります。

　それで、一々関数の内容を目で確認して上記の様な置き換えをしていたのでは手間が掛かりますから、もし、元データであるSheet1のリストに、「『本件で御質問内容で触れておられるSheet2』とは別のファイルやSheetで、リンクや参照等が行われている」とか、「決まりがあるため元データのリストを手を加える事が出来ない」といった様な何らかの理由から、Sheet1に手を加える事が出来ない、という場合には使えない方法ではありますが、Sheet1のG列全体を選択した後、選択範囲を右クリックすると現れる[挿入]を用いて、Sheet1のリストの列数を増やしてから、位置がずれてしまった「記念日E」のデータをコピー&ペーストで新たなG列に貼り付けてしまった方が簡単です。(H列に残された「記念日E」のデータは後で消去しておいて下さい。データを移す際に[切り取り]で行う事は止めておいた方が良いです)

　そして、

$E$16
　↓
$I$16

と

$E18
　↓
$I18

の置き換えに関しては、本件のExcel　bookのレイアウトでは、"偶々"、Sheet3に入力されている関数中には、いずれかのシート上のE列のセルを直接参照している様な箇所は、上記の置き換えなければならないもの以外には存在しておりませんので、「記念日E」のリストをコピーして隣に貼り付けた後で、コピーされた新たな記念日のリスト全体を範囲選択してから、Excelの[置換]を利用して、

$E
↓
$I

という置換を行えば、要修正個所を一辺に置き換える事が出来ます。
　尚、もしSheet1に手を加える事が出来ない場合には、Sheet3のA～B列や、Sheet2全体に対して、同様にExcelの[置換]を利用して、

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

や

Sheet1!$C:$G
　↓
Sheet1!$C:$H

という置き換えを一気に行ってしまうという手もあります。

補足

kagakusuki様
細部までお気遣いいただき、本当にありがとうございます。
お教え頂いた通りに作成したらできました！
ただ、これはしょうがない事だとは思うのですが、顧客数が多く（今後も増えていく）エクセル自体が動作がかなり遅くなってしまいました。何か良い対策はありますでしょうか。
ただ、重くなることは最初から予想していましたので、少しでも軽くできる対策などがもしあれば、毎回の質問で申し訳ありません、ご教授いただくことはできますでしょうか？
宜しくお願い致します。

回答No.7

bunjii

回答No.1の貼付画像の下の表について住所、名前、日付を検索日付にマッチしたものだけを抽出するには次のように変更すれば良いでしょう。
I5=INDEX($A:$A,IFERROR(SUMPRODUCT(LARGE(($C$2:$G$11=DATE($J$1,$K$1,$L$1))*ROW(I$2:I$11)*($C$1:$G$1=I$3),COUNTIFS(OFFSET($A$1,1,MATCH(I$3,$A$1:$G$1,0)-1,ROW(A10)),DATE($J$1,$K$1,$L$1))-ROW(A1)+1)),ROW(I$12)),1)&""

J5=INDEX($B:$B,IFERROR(SUMPRODUCT(LARGE(($C$2:$G$11=DATE($J$1,$K$1,$L$1))*ROW(I$2:I$11)*($C$1:$G$1=I$3),COUNTIFS(OFFSET($A$1,1,MATCH(I$3,$A$1:$G$1,0)-1,ROW(A10)),DATE($J$1,$K$1,$L$1))-ROW(A1)+1)),ROW(I$12)),1)&""

K5=INDEX(OFFSET($A$1,0,MATCH(I$3,$A$1:$G$1,0)-1,12),IFERROR(SUMPRODUCT(LARGE(($C$2:$G$11=DATE($J$1,$K$1,$L$1))*ROW(I$2:I$11)*($C$1:$G$1=I$3),COUNTIFS(OFFSET($A$1,1,MATCH(I$3,$A$1:$G$1,0)-1,ROW(A10)),DATE($J$1,$K$1,$L$1))-ROW(A1)+1)),ROW(I$12)),1)

I5とJ5を選択して下へ必要数コピーします。
K5はL5と結合していますので単独で下へ必要数コピーします。

I3:L11が記念日Aの抽出用になり、この範囲を他のセル範囲へコピペして記念日D等に訂正すれば目的の表になります。

補足

bunjii様、分かりやすい内容でお教えいただき、本当にありがとうございます！
最初の補足で合わせて伺っておけばよかったのですが、申し訳ありません。
検索日付なのですが、ここを年と月で検索し、該当するデータを全て表示させることはできますでしょうか？例えば、2015年3月を検索値にした場合、2015年3月1～31日の間で記念日Aを迎える方を上詰めで全て表示するといった形です。回答No.7でお教えいただいた内容を生かしながら、年月日検索を年月検索にできれば、当初想像していた通りの管理表になります。何度もお願いばかりで申し訳ありません。もしよろしければ、お力をお貸しいただけませんでしょうか。よろしくお願い致しいます。

回答No.5

MackyNo1

ご希望のデータを表示するには、かなり複雑な配列数式を使用する必要があります。

元データがSheet1のA1セル以下にあり、抽出対象の日付が表示シートのK3セルに年、M3セルに月、O3セルに日がそれぞれ入力されているなら（提示されているレイアウトなら）、たとえばI7セルに以下の数式を入力して右方向に1つ、下方向に適当数オートフィルすれば該当の住所と名前が表示されます。

=INDEX(Sheet1!A:A,SMALL(INDEX((INDEX(Sheet1!$C$2:$F$1000,,MATCH($I$5,Sheet1!$C$1:$G$1,0))<>DATE($K$3,$M$3,$O$3))*10000+ROW($2:$1000),),ROW(1:1)))&""

上記の数式は、数式を煩雑化させないために、I5セルの記念日の値を絶対参照していますので、他の記念日を表示する場合はこのセル書き換える必要がありますが、数式や入力方法を少し工夫すれば同じ数式ですべての記念日に対応させることが可能です。

ちなみに、K7セル以下の日付表示欄が不要のような気もするのですが、検索日を表示したいなら以下の式を入力することになります。

=IF(I7="","",DATE($K$3,$M$3,$O$3))

補足

MackyNo1様
大変参考になりました。ただ、今回はkagakusukiさまより頂いた式を使わせて頂こうと思います。貴重なお時間を割いて、知恵をお貸し下さいまして、本当にありがとうございました。

回答No.4

kagakusuki

回答No.3の続きです。

　同様にSheet2のC18セルには

=IF(ISNUMBER($A18),OFFSET(Sheet1!$A$2,MATCH(COUNTIF(OFFSET(Sheet1!$B$2,1,MATCH($A$16,Sheet1!$C$2:$G$2,0),Sheet3!$B$1+0),"<"&$A$18)+ROWS($18:18)+MATCH($A$16,Sheet1!$C$2:$G$2,0)*10000000,Sheet3!$B:$B,0)-ROW(Sheet3!$B$2)-OFFSET(Sheet3!$A$2,MATCH($A$16,Sheet1!$C$2:$G$2,0)-1,),)&"","")

Sheet2のG18セルには

=IF(ISNUMBER($E18),OFFSET(Sheet1!$A$2,MATCH(COUNTIF(OFFSET(Sheet1!$B$2,1,MATCH($E$16,Sheet1!$C$2:$G$2,0),Sheet3!$B$1+0),"<"&$E$18)+ROWS($18:18)+MATCH($E$16,Sheet1!$C$2:$G$2,0)*10000000,Sheet3!$B:$B,0)-ROW(Sheet3!$B$2)-OFFSET(Sheet3!$A$2,MATCH($E$16,Sheet1!$C$2:$G$2,0)-1,),)&"","")

と入力して下さい。
　次に、Sheet2の7行目をコピーして、Sheet2の8行目～13行目に貼り付けて下さい。
　次に、Sheet2の18行目をコピーして、Sheet2の19行目～24行目に貼り付けて下さい。

　以上です。

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

回答No.3

kagakusuki

回答No.2の続きです。

　同様にSheet2のA18セルには

=IF(AND(ISNUMBER(1/($B$3&"/"&IF(AND($D$3="",$F$3=""),1,$D$3)&IF($F$3="","","/"&$F$3))/COUNTIF(Sheet1!$C$2:$G$2,A$5)),A$5<>""),IF(ROWS($7:7)>COUNTIFS(OFFSET(INDEX(Sheet1!$C$2:$G$2,MATCH(A$5,Sheet1!$C$2:$G$2,0)),,,ROWS(Sheet1!$A:$A)-ROW(Sheet1!$A$2)),">="&$B$3&"/"&IF(AND($D$3="",$F$3=""),1,$D$3)&IF($F$3="","","/"&$F$3),OFFSET(INDEX(Sheet1!$C$2:$G$2,MATCH(A$5,Sheet1!$C$2:$G$2,0)),,,ROWS(Sheet1!$A:$A)-ROW(Sheet1!$A$2)),"<"&DATE($B$3+($D$3=""),$D$3+($F$3=""),(0&$F$3)+1)),"",SMALL(OFFSET(INDEX(Sheet1!$C$2:$G$2,MATCH(A$5,Sheet1!$C$2:$G$2,0)),,,ROWS(Sheet1!$A:$A)-ROW(Sheet1!$A$2)),COUNTIF(OFFSET(INDEX(Sheet1!$C$2:$G$2,MATCH(A$5,Sheet1!$C$2:$G$2,0)),,,ROWS(Sheet1!$A:$A)-ROW(Sheet1!$A$2)),"<"&$B$3&"/"&IF(AND($D$3="",$F$3=""),1,$D$3)&IF($F$3="","","/"&$F$3))+ROWS($7:7))),"")

Sheet2のE18セルには

=IF(AND(ISNUMBER(1/($B$3&"/"&IF(AND($D$3="",$F$3=""),1,$D$3)&IF($F$3="","","/"&$F$3))/COUNTIF(Sheet1!$C$2:$G$2,E$5)),E$5<>""),IF(ROWS($7:7)>COUNTIFS(OFFSET(INDEX(Sheet1!$C$2:$G$2,MATCH(E$5,Sheet1!$C$2:$G$2,0)),,,ROWS(Sheet1!$A:$A)-ROW(Sheet1!$A$2)),">="&$B$3&"/"&IF(AND($D$3="",$F$3=""),1,$D$3)&IF($F$3="","","/"&$F$3),OFFSET(INDEX(Sheet1!$C$2:$G$2,MATCH(E$5,Sheet1!$C$2:$G$2,0)),,,ROWS(Sheet1!$A:$A)-ROW(Sheet1!$A$2)),"<"&DATE($B$3+($D$3=""),$D$3+($F$3=""),(0&$F$3)+1)),"",SMALL(OFFSET(INDEX(Sheet1!$C$2:$G$2,MATCH(E$5,Sheet1!$C$2:$G$2,0)),,,ROWS(Sheet1!$A:$A)-ROW(Sheet1!$A$2)),COUNTIF(OFFSET(INDEX(Sheet1!$C$2:$G$2,MATCH(E$5,Sheet1!$C$2:$G$2,0)),,,ROWS(Sheet1!$A:$A)-ROW(Sheet1!$A$2)),"<"&$B$3&"/"&IF(AND($D$3="",$F$3=""),1,$D$3)&IF($F$3="","","/"&$F$3))+ROWS($7:7))),"")

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

=IF(ISNUMBER($A7),OFFSET(Sheet1!$B$2,MATCH(COUNTIF(OFFSET(Sheet1!$B$2,1,MATCH($A$5,Sheet1!$C$2:$G$2,0),Sheet3!$B$1+0),"<"&$A$7)+ROWS($7:7)+MATCH($A$5,Sheet1!$C$2:$G$2,0)*10000000,Sheet3!$B:$B,0)-ROW(Sheet3!$B$2)-OFFSET(Sheet3!$A$2,MATCH($A$5,Sheet1!$C$2:$G$2,0)-1,),)&"","")

Sheet2のF7セルに

=IF(ISNUMBER($E7),OFFSET(Sheet1!$B$2,MATCH(COUNTIF(OFFSET(Sheet1!$B$2,1,MATCH($E$5,Sheet1!$C$2:$G$2,0),Sheet3!$B$1+0),"<"&$E$7)+ROWS($7:7)+MATCH($E$5,Sheet1!$C$2:$G$2,0)*10000000,Sheet3!$B:$B,0)-ROW(Sheet3!$B$2)-OFFSET(Sheet3!$A$2,MATCH($E$5,Sheet1!$C$2:$G$2,0)-1,),)&"","")

Sheet2のJ7セルに

=IF(ISNUMBER($I7),OFFSET(Sheet1!$B$2,MATCH(COUNTIF(OFFSET(Sheet1!$B$2,1,MATCH($I$5,Sheet1!$C$2:$G$2,0),Sheet3!$B$1+0),"<"&$I$7)+ROWS($7:7)+MATCH($I$5,Sheet1!$C$2:$G$2,0)*10000000,Sheet3!$B:$B,0)-ROW(Sheet3!$B$2)-OFFSET(Sheet3!$A$2,MATCH($I$5,Sheet1!$C$2:$G$2,0)-1,),)&"","")

Sheet2のB18セルに

=IF(ISNUMBER($A18),OFFSET(Sheet1!$B$2,MATCH(COUNTIF(OFFSET(Sheet1!$B$2,1,MATCH($A$16,Sheet1!$C$2:$G$2,0),Sheet3!$B$1+0),"<"&$A$18)+ROWS($18:18)+MATCH($A$16,Sheet1!$C$2:$G$2,0)*10000000,Sheet3!$B:$B,0)-ROW(Sheet3!$B$2)-OFFSET(Sheet3!$A$2,MATCH($A$16,Sheet1!$C$2:$G$2,0)-1,),)&"","")

Sheet2のF18セルに

=IF(ISNUMBER($E18),OFFSET(Sheet1!$B$2,MATCH(COUNTIF(OFFSET(Sheet1!$B$2,1,MATCH($E$16,Sheet1!$C$2:$G$2,0),Sheet3!$B$1+0),"<"&$E$18)+ROWS($18:18)+MATCH($E$16,Sheet1!$C$2:$G$2,0)*10000000,Sheet3!$B:$B,0)-ROW(Sheet3!$B$2)-OFFSET(Sheet3!$A$2,MATCH($E$16,Sheet1!$C$2:$G$2,0)-1,),)&"","")

Sheet2のC7セルに

=IF(ISNUMBER($A7),OFFSET(Sheet1!$A$2,MATCH(COUNTIF(OFFSET(Sheet1!$B$2,1,MATCH($A$5,Sheet1!$C$2:$G$2,0),Sheet3!$B$1+0),"<"&$A$7)+ROWS($7:7)+MATCH($A$5,Sheet1!$C$2:$G$2,0)*10000000,Sheet3!$B:$B,0)-ROW(Sheet3!$B$2)-OFFSET(Sheet3!$A$2,MATCH($A$5,Sheet1!$C$2:$G$2,0)-1,),)&"","")

Sheet2のG7セルに

=IF(ISNUMBER($E7),OFFSET(Sheet1!$A$2,MATCH(COUNTIF(OFFSET(Sheet1!$B$2,1,MATCH($E$5,Sheet1!$C$2:$G$2,0),Sheet3!$B$1+0),"<"&$E$7)+ROWS($7:7)+MATCH($E$5,Sheet1!$C$2:$G$2,0)*10000000,Sheet3!$B:$B,0)-ROW(Sheet3!$B$2)-OFFSET(Sheet3!$A$2,MATCH($E$5,Sheet1!$C$2:$G$2,0)-1,),)&"","")

※そろそろ回答欄に入力可能な文字数を超えそうですので、残りは又後で回答致します。

回答No.2

kagakusuki

Sheet2の3行目に年月日を入力するとその指定した日のデータのみを抽出し、
年月のみを入力し、日の入力欄を空欄にした場合には、指定した月の1日～月末の期間に含まれているデータのみを抽出し、
年のみを入力し、月や日の入力欄を空欄にした場合には、指定した年の1日～年末の期間に含まれているデータのみを抽出する様にする方法です。

　今仮に、Sheet1のA2セルに「住所」、B2セルに「名前」、C2セルに「記念日A」、D2セルに「記念日B」、E2セルに「記念日C」、F2セルに「記念日D」、G2セルに「記念日E」と入力されていて、
Sheet2のA3セルに「検索日付」、C3セルに「年」、E3セルに「月」、G3セルに「日」、
A6セルに「記念日A」、E6セルに「記念日B」、I6セルに「記念日C」、A16セルに「記念日D」、E16セルに「記念日E」と入力されていて、
Sheet3のA列とB列を作業列として使用するものとします。

　まず、Sheet3のA2セルとB2セルに

0

という数値を入力して下さい。

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

=IF(ROWS($3:3)>COLUMNS(Sheet1!$C$2:$G$2),"",IF(COUNT(INDEX(Sheet1!$C:$G,,ROWS($3:3))),A2+MAX(0,MATCH(9E+307,INDEX(Sheet1!$C:$G,,ROWS($3:3)))-ROW(Sheet1!$C$2:$G$2)),""))

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

=MAX($A:$A)&""

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

=IF(ROWS($3:3)>$B$1+0,"",IF(ISNUMBER(1/DAY(OFFSET(Sheet1!$B$2,ROWS($3:3)-VLOOKUP(ROWS($3:3)-1,$A:$A,1),MATCH(ROWS($3:3)-1,$A:$A)-ROW($A$2)+1))),COUNTIF(OFFSET(Sheet1!$B$2,1,MATCH(ROWS($3:3)-1,$A:$A)-ROW($A$2)+1,$B$1+0),"<"&OFFSET(Sheet1!$B$2,ROWS($3:3)-VLOOKUP(ROWS($3:3)-1,$A:$A,1),MATCH(ROWS($3:3)-1,$A:$A)-ROW($A$2)+1))+COUNTIF(OFFSET(Sheet1!$B$2,1,MATCH(ROWS($3:3)-1,$A:$A)-ROW($A$2)+1,ROWS($3:3)-VLOOKUP(ROWS($3:3)-1,$A:$A,1)),OFFSET(Sheet1!$B$2,ROWS($3:3)-VLOOKUP(ROWS($3:3)-1,$A:$A,1),MATCH(ROWS($3:3)-1,$A:$A)-ROW($A$2)+1))+(MATCH(ROWS($3:3)-1,$A:$A)-ROW($A$2)+1)*10000000,""))

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

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

=IF(AND(ISNUMBER(1/($B$3&"/"&IF(AND($D$3="",$F$3=""),1,$D$3)&IF($F$3="","","/"&$F$3))/COUNTIF(Sheet1!$C$2:$G$2,A$5)),A$5<>""),IF(ROWS($7:7)>COUNTIFS(OFFSET(INDEX(Sheet1!$C$2:$G$2,MATCH(A$5,Sheet1!$C$2:$G$2,0)),,,ROWS(Sheet1!$A:$A)-ROW(Sheet1!$A$2)),">="&$B$3&"/"&IF(AND($D$3="",$F$3=""),1,$D$3)&IF($F$3="","","/"&$F$3),OFFSET(INDEX(Sheet1!$C$2:$G$2,MATCH(A$5,Sheet1!$C$2:$G$2,0)),,,ROWS(Sheet1!$A:$A)-ROW(Sheet1!$A$2)),"<"&DATE($B$3+($D$3=""),$D$3+($F$3=""),(0&$F$3)+1)),"",SMALL(OFFSET(INDEX(Sheet1!$C$2:$G$2,MATCH(A$5,Sheet1!$C$2:$G$2,0)),,,ROWS(Sheet1!$A:$A)-ROW(Sheet1!$A$2)),COUNTIF(OFFSET(INDEX(Sheet1!$C$2:$G$2,MATCH(A$5,Sheet1!$C$2:$G$2,0)),,,ROWS(Sheet1!$A:$A)-ROW(Sheet1!$A$2)),"<"&$B$3&"/"&IF(AND($D$3="",$F$3=""),1,$D$3)&IF($F$3="","","/"&$F$3))+ROWS($7:7))),"")

　同様にSheet2のE7セルには

=IF(AND(ISNUMBER(1/($B$3&"/"&IF(AND($D$3="",$F$3=""),1,$D$3)&IF($F$3="","","/"&$F$3))/COUNTIF(Sheet1!$C$2:$G$2,E$5)),E$5<>""),IF(ROWS($7:7)>COUNTIFS(OFFSET(INDEX(Sheet1!$C$2:$G$2,MATCH(E$5,Sheet1!$C$2:$G$2,0)),,,ROWS(Sheet1!$A:$A)-ROW(Sheet1!$A$2)),">="&$B$3&"/"&IF(AND($D$3="",$F$3=""),1,$D$3)&IF($F$3="","","/"&$F$3),OFFSET(INDEX(Sheet1!$C$2:$G$2,MATCH(E$5,Sheet1!$C$2:$G$2,0)),,,ROWS(Sheet1!$A:$A)-ROW(Sheet1!$A$2)),"<"&DATE($B$3+($D$3=""),$D$3+($F$3=""),(0&$F$3)+1)),"",SMALL(OFFSET(INDEX(Sheet1!$C$2:$G$2,MATCH(E$5,Sheet1!$C$2:$G$2,0)),,,ROWS(Sheet1!$A:$A)-ROW(Sheet1!$A$2)),COUNTIF(OFFSET(INDEX(Sheet1!$C$2:$G$2,MATCH(E$5,Sheet1!$C$2:$G$2,0)),,,ROWS(Sheet1!$A:$A)-ROW(Sheet1!$A$2)),"<"&$B$3&"/"&IF(AND($D$3="",$F$3=""),1,$D$3)&IF($F$3="","","/"&$F$3))+ROWS($7:7))),"")

　Sheet2のI7セルには

=IF(AND(ISNUMBER(1/($B$3&"/"&IF(AND($D$3="",$F$3=""),1,$D$3)&IF($F$3="","","/"&$F$3))/COUNTIF(Sheet1!$C$2:$G$2,I$5)),I$5<>""),IF(ROWS($7:7)>COUNTIFS(OFFSET(INDEX(Sheet1!$C$2:$G$2,MATCH(I$5,Sheet1!$C$2:$G$2,0)),,,ROWS(Sheet1!$A:$A)-ROW(Sheet1!$A$2)),">="&$B$3&"/"&IF(AND($D$3="",$F$3=""),1,$D$3)&IF($F$3="","","/"&$F$3),OFFSET(INDEX(Sheet1!$C$2:$G$2,MATCH(I$5,Sheet1!$C$2:$G$2,0)),,,ROWS(Sheet1!$A:$A)-ROW(Sheet1!$A$2)),"<"&DATE($B$3+($D$3=""),$D$3+($F$3=""),(0&$F$3)+1)),"",SMALL(OFFSET(INDEX(Sheet1!$C$2:$G$2,MATCH(I$5,Sheet1!$C$2:$G$2,0)),,,ROWS(Sheet1!$A:$A)-ROW(Sheet1!$A$2)),COUNTIF(OFFSET(INDEX(Sheet1!$C$2:$G$2,MATCH(I$5,Sheet1!$C$2:$G$2,0)),,,ROWS(Sheet1!$A:$A)-ROW(Sheet1!$A$2)),"<"&$B$3&"/"&IF(AND($D$3="",$F$3=""),1,$D$3)&IF($F$3="","","/"&$F$3))+ROWS($7:7))),"")

と入力して下さい。

※そろそろ回答欄に入力可能な文字数を超えそうですので、残りは又後で回答致します。

補足

kagakusuki様

再度、添付していただいた画像の通りに表を作成し直し、教えていただいた通りに入力したら、できました！！
本当にありがとうございます！

何度も申し訳ありませんが、もしよろしかったら、ご教授いただけませんでしょうか？
記念日Eの横にもうひとつ記念日Fというのを作成したのですが、うまくデータを反映させることができません。数式的には記念日Eで使用しているE18、F18、G18をコピーで使用してE16をI16に置き換えて使ってみました。Sheet3のAB列を何か変更しなくてはいけませんでしょうか？厚かましいお願いで申し訳ありません。よろしくお願い致します。