Excel 関数 一度抽出された値を検索対象から外すには？

Excel 関数 一度抽出された値を検索対象から外すには？
Excelの関数について教えて頂きたいことがあります。例えばある検索範囲があり、vlookupか何かで対象の値を引っ張ってきたいのですが、検索値に対象となる値が複数ある場合、一度抽出された値を検索範囲から外し、次のvlookupではそれ以外の対象となる検索値から値を引っ張る、そしてそのまた次のvlookupでは既に抽出された2つの値を対象から外し検索値を探す、そしてそのまた・・・といったような関数の設定は可能でしょうか？
もう少し詳しく状況を説明しますと、日付の"年"と"月"を検索値とし、範囲に該当する値が複数ある時に(仮に検索値を"2010年3月"とし、検索範囲の中に2010年3月1日、2010年3月8日、2010年3月15日を=year()&"/"&month()で表示させた値がある場合)、=vlookup("2010年3月",A:B,2,false)でまず一つ目を抽出し、次のvlookupでの検索の時に一つ目を検索対象から外して再度検索させ、さらに次では一つ目と二つ目を検索対象外として検索させたい、と考えています。

どなたかこのような関数の組み方をご存知の方、教えて頂きたく思います。別にvlookupにはこだわりませんので、近い方法でもいいのでどうぞ宜しくお願いします。

No.1 ベストアンサー 回答者： KURUMITO 回答日時： 2010/04/03 10:46

3月のデータがたくさんあるときにVLOOKUP関数を使ってそのようなことをするには複雑な式になってしまうでしょうし、データがたくさんになれば対応ができません。

同じ月のデータがあるときにそれを抽出するためには次のようにすればよいでしょう。
例えばＡ列の2行目から下方に日付が、Ｂ列の2行目から下方には=IF(A2="","",YEAR(A2)&"/"&MONTH(A2))の式で下方にオートフィルドラッグコピーしたものが表示されているとします。また、Ｃ列には別の項目のデータが例えば氏名が入力されているとします。
作業列を使って対応するのが簡単です。
例えばＥ2セルには次の式を入力して下方にオートフィルドラッグします。

=IF(B2="","",B2&COUNTIF(B$2:B2,B2))

そこで例えば2010年3月のＣ列にあるデータを抽出したい場合にはVLOOKUP関数の代わりに次のような式を入力して、下方にオートフィルドラッグします。

=IF(COUNTIF($E:$E,"2010/3"&ROW(A1))=0,"",INDEX($C:$C,MATCH("2010/3"&ROW(A1),$E:$E,0)))

これで2010年3月のデータを連続して抽出し、表示させることができます。

No.5 回答者： tom04 回答日時： 2010/04/04 00:34

こんばんは！

外していたらごめんなさい。

↓の画像でとりあえず二段階で検索条件以外のものを表示させるようにしてみました。

E1セルの「3月」は3/1のシリアル値を入れて、
表示形式をユーザー定義から　m"月"としています。

まず、E2セルに
=IF(SUMPRODUCT(($A$2:$A$100<>"")*(MONTH($A$2:$A$100)<>MONTH($E$1)))<ROW(A1),"",INDEX(A$2:A$100,SMALL(IF(MONTH($A$2:$A$100)<>MONTH($E$1),ROW($A$1:$A$99)),ROW(A1))))

これは配列数式になってしまいますので、
この画面からE2セルにコピー＆ペーストしただけではエラーになると思います。
貼り付け後、F2キーを押す、又はE2セルをダブルクリック、又は数式バー内で一度クリックします。
編集可能になりますので　Shift＋Ctrl＋Enterキーで確定してください。
数式の前後の｛　｝マークが入り配列数式になります。
これを列方向と行方向にオートフィルでコピーします。

同様に第二段階の元データがこの表になりますので
H2セルに
=IF(SUMPRODUCT(($F$2:$F$100<>"")*($F$2:$F$100<>$H$1))<ROW(A1),"",INDEX(E$2:E$100,SMALL(IF($F$2:$F$100<>$H$1,ROW($A$1:$A$99)),ROW(A1))))

これも配列数式ですので、Shift＋Ctrl＋Enterキーで確定です。
この数式を列方向と行方向にオートフィルでコピーすると
画像のような感じになります。
尚、エラー処理はしていません。数式は最初のデータの100行目まで対応できるようにしていますが
データ量によって範囲指定の領域はアレンジしてみてください。

以上、長々と書きました。
参考になれば良いのですが
的外れなら読み流してくださいね。m(__)m

No.4 回答者： hallo-2007 回答日時： 2010/04/03 12:25

データの検索ではなく、条件にあったデータの抽出（一覧表の作成）といったことでしょうか。

もう少し、補足していただければ別案が出ると思います。
１、データは日付順に並んでいるのでしょうか？
２、同じ日付のデータがいくつもあるのか、日付はひとつづつなのか？
といったところです。
仮に、データが日付順でれば
データシートに（シート名　データ）
　A　　B
日付
2000/1/1
・・・
2010/3/1
2010/3/8
2010/3/15
・・・・
2010/4/2　と並んでいるとして
別シートの　A1に　2010　と年の数値、B1に　3　と月の数値を入れているとして
表示したいセルに
=INDEX(データ!A:A,MATCH(DATE(A$1,B$1,0.5),データ!A:A)+ROW(A1))
いれて下へコピィしてみてください。
参考ですのでエラー処理や空白の処理はしていません。
まずは式の意味を理解して応用してください。

No.3 回答者： web2525 回答日時： 2010/04/03 11:19

現在エクセルを操作できない環境なので考え方のみの回答になりますが

1）vlookupを利用する場合、検索範囲を絶対値で指定せずにoffset関数等を利用して検索範囲を狭めていく
・元データーにrow関数を利用して行番号を設置、vlookupで取得した時に行番号も一緒に取得、次の検索範囲は直前に取得した行番号+1行から下を検索範囲と指定

2）オートフィルターを利用して必要データーのみを抽出してコピーして貼り付け

3）マクロを作成
・２の操作をマクロ化するのが簡単です

No.2 回答者： KURUMITO 回答日時： 2010/04/03 10:59

先ほどはＣ列だけのデータを表示させる方法ですが例えばＣ列とＤ列のデータを表示させるのでしたら、最後の式は次のようになりますね。

=IF(COUNTIF($E:$E,"2010/3"&ROW(A1))=0,"",INDEX($C:$D,MATCH("2010/3"&ROW(A1),$E:$E,0),COLUMN(A1)))

この式をあるセルに入力した後で右横方向に1列だけオートフィルドラッグコピーしたのちに下方にもオートフィルドラッグコピーします。

この回答へのお礼

ご回答どうもありがとうございました。
Countifやindexとmatch関数の組み合わせの勉強になりました。これで作成したかったシートも出来そうです。ありがとうございました。

お礼日時：2010/04/04 00:46