エクセルで複数ファイルからコード条件で日付順抽出

質問

エクセルで複数ファイルからコード条件で日付順に抽出したいのですが、関数を使って可能でしょうか？
ファイル名と保持データ
(1)マスタ一覧：A列（コード）B列（名前）C列（住所）　
(2)契約履歴：A列（コード）B列（名前）C列（契約会社）D列（契約始期（日付））E列（契約終期（日付））
(3)コード別契約履歴：コード　名前　契約会社　契約始期（日付）　契約終期（日付）

(2)契約履歴には各人の契約履歴が1行に1契約ずつ入力されています。
(3)コード別契約履歴のコード入力セルにコードを入力すると、名前と住所、あと契約履歴を日付順に抽出される様にしたいです。（印刷用のレイアウト）

質問の内容が分かりにくいかもしれませんがよろしくお願いします。

回答No.3

KURUMITO · ベストアンサー

複数ファイルからとなっていますがマスタ一覧がシート1に、契約履歴はシート2にあるとして、シート3にコード別契約履歴を表示させることにします。
シート1のD列は作業列としてD2セルには次の式を入力して下方にオートフィルドラッグします。

=IF(A2="","",A2&B2)

シート2のF列は作業列としてF2セルには次の式を入力して下方にオートフィルドラッグします。

=IF(A2=Sheet3!B$1,D2,"")

同じくG列も作業列としてG2セルには次の式を入力して下方にオートフィルドラッグします。

=IF(F2="","",RANK(F2,F:F,1))

これらの作業が済んだ後でシート3ではA1セルにコードの文字を入力し、B1セルにはコードデータを入力します。
A2セルには名前、B2セルには住所、C2セルには契約始期、D2セルには契約終期の文字をそれぞれ入力します。
A3セルには次の式を入力して下方にオートフィルドラッグします。

=IF(ROW(A1)>MAX(Sheet2!G:G),"",INDEX(Sheet2!B:B,MATCH(ROW(A1),Sheet2!G:G,0)))

B3セルには次の式を入力して下方にオートフィルドラッグします。

=IF(A3="","",INDEX(Sheet1!C:C,MATCH(B$1&A3,Sheet1!D:D,0)))

C3セルには次の式を入力したのちにD3セルまでオートフィルドラッグしたのちに下方にもオートフィルドラッグします。

=IF($A3="","",INDEX(Sheet2!$D:$E,MATCH(ROW(A1),Sheet2!$G:$G,0),COLUMN(A1)))

これで契約始期の順に並べられます。

お礼

ご回答頂きありがとうございます。
教えて頂いた方法で解決しました。
ありがとうございました。

回答No.2

MackyNo1

訂正と補足です。

No1の回答では、G4セルだけに入力する数式が抜けていました。
最後の「&""」がない数式を入力してみてください。

=INDEX(B:B,SMALL(INDEX(($A$2:$A$20<>$H$1)*1000+ROW($A$2:$A$20),),ROW(A1)))

なお、日付欄はセルの書式設定で表示形式を日付にして下さい。

もし、上記の回答がご希望の操作と違う場合は、実際のデータの例と、抜き出した結果を具体的に例示してください。

お礼

ご回答頂きありがとうございます。
ご指摘頂いたエクセルのバージョンは2007です。

=INDEX(B:B,SMALL(INDEX(($A$2:$A$20<>$H$1)*1000+ROW($A$2:$A$20),),ROW(A1)))
教えて頂いた数式のセル参照ですが、
B:B＝契約履歴リストの名前列
$A$2:$A$20＝契約履歴リストのコード列の見出し以降の行
$H$1＝抽出したいコードの入力
であってますか？
上記の通り契約履歴リスト（別ファイル）を参照しました所、別のコードの名前が表示されます。
色々セル参照を変えてみましたが自分のスキル不足のせいでうまく出来ませんでした。
結果的にはKURUMITOさんに教えて頂いた方法で解決しました。
ありがとうございました。

回答No.1

MackyNo1

＞コード別契約履歴のコード入力セルにコードを入力すると、名前と住所、あと契約履歴を日付順に抽出される様にしたいです。（印刷用のレイアウト）

名前と住所はVLOOKUP関数でよいのでしょうか？
また「契約履歴」の意味が、契約履歴シートのコードが入力されている行全部を別シートに抽出したいということなら（この名前は別の名前でも対応できる）、以下のような関数で表示できます（添付画像参照；別シートの数式になっていませんが適宜参照範囲などを変更してください）。

G4セル(下方向にオートフィル）
=INDEX(B:B,SMALL(INDEX(($A$2:$A$20<>$H$1)*1000+ROW($A$2:$A$20),),ROW(A1)))&""

H4セル(右方向、下方向にオートフィル）
=IF(G4="","",INDEX(C:C,SMALL(INDEX(($A$2:$A$20<>$H$1)*1000+ROW($A$2:$A$20),),ROW(B1))))

上記の数式は計算負荷をできる限り少なくするために、G4セルとG5セルの数式を分けましたが、G4セルのコピーだけで対応するなら、以下の数式を右方向および下方向にオートフィルし、オプションからゼロ値を表示しない設定にすることで対応できます。

＃Officeソフトはバージョンによって使用できる機能や操作方法が大きく異なりますので、ご質問の際には必ずバージョンを明記するようにしましょう。