エクセル：別ファイルからの抽出

エクセル：別ファイルからの抽出

エクセルにて、

あるファイルに

A　　B　　　　C
No　月　　　　氏名　　　
1　2010/11　　佐藤
2　2010/10　　山田
3　2011/12　　田中
4　2010/10　　田中
・
・

とあり、それを別ファイルで
2010/10とセルに入力してやると、B列の2010/10だけ抽出して
一覧にしてくれるようにしたいと考えております。
関数またはVBAにて処理する方法はありますでしょうか？

A　　B　　　　C
No　月　　　　氏名　　　
2　2010/10　　山田
4　2010/10　　田中
・
・

※別ファイルにおいては順番は関係ないので、Noがなくても
よいです。

恐縮ですが、お答え願えれば有難いです。

No.6 ベストアンサー 回答者： tom04 回答日時： 2010/10/27 20:45

No.1です！

補足を読ませてもらいました。
もう一度画像をアップしてみます。

Book1のSheet1の作業列の数式を変えてみました。
F2セルの数式を
=IF(COUNTBLANK([Book2]Sheet1!A$1:A$2),"",IF(SUMPRODUCT((YEAR(B2:D2)=[Book2]Sheet1!A$1)*(MONTH(B2:D2)=[Book2]Sheet1!A$2)*(COLUMN(A$1:C$1))),ROW(A1),""))
としてオートフィルでずぃ～～～！っと下へコピーしておいてください。

そして、Book2の表も変えています。
条件に一致する人の「月1」～「月3」も表示するようにしてみました。

Book2のSheet1のA5セルに
=IF(COUNT([Book1]Sheet1!$F$2:$F$1000)<ROW(A1),"",INDEX([Book1]Sheet1!$A$2:$E$1000,SMALL([Book1]Sheet1!$F$2:$F$1000,ROW(A1)),MATCH(A$4,[Book1]Sheet1!$A$1:$E$1,0)))
という数式を入れ、列方向と行方向にオートフィルでコピーします。
尚、Book1の空白セルの場合は「1900/1」と表示されると思いますので、
当方使用のExcel2003の場合ですが
メニュー　→　ツール　→　オプション　→　「表示タブ」の「ゼロ値」のチェックを外しておきます。

尚、Book1の行内に一致するセルが一つでもあれば他の月があってもその月も表示されてしまいますので、
希望通りでなければごめんなさいね。m(__)m

この回答へのお礼

再度の回答誠にありがとうございました。
画像もつけていただき、とても分かりやすいです。

お教え頂いた方法で試したところ、見事本チャンのファイルでも
うまく表示されました。

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

お礼日時：2010/10/29 13:04

No.7 回答者： MackyNo1 回答日時： 2010/10/28 00:17

>お教え頂いたやり方で、うまく表示されたのですが、セルの書式をyyyy/m

に設定してもシリアル値でしか表示されないようです。
&""の部分がなければうまく表示されるのですが・・・。

失礼しました。
エラー処理（この場合は０を表示しない）を簡便化するために、「＆""」の数式を提示しましたが、確かに返す値がシリアル値（文字列以外）の時は「＆""」の部分を削除する必要があります。

簡便に対応するなら、B列については、IF関数でC列の値が空白の場合は空白を、それ以外の場合は「＆""」を除いた数式にしてください。

この回答へのお礼

再度の回答ありがとうございました！

ＩＦ関数にて処理することにしました。

お礼日時：2010/10/29 15:11

No.5 回答者： MackyNo1 回答日時： 2010/10/27 16:56

＞ちなみに参照するセルが複数ある場合、

＞例えば上記のsheet1のB2、C2、D2のいずれかに
＞該当する年月がある場合、という参照の仕方もできるのでしょうか？

私の提示した数式なら数式範囲を変更するだけでOKです。

例えば、数式を簡略化するために同じシートのE1セルに入力されている値を検索値として、B2:D10セルの範囲でB～D列に検索値が含まれている行のA列のデータを抽出する場合で説明すると、以下の式になります。

=INDEX(A:A,SMALL(INDEX(($B$2:$D$10<>$E$1)*1000+ROW($B$2:$D$10),),ROW(A1)))&""

適宜、参照するブックと数式のセル範囲を変更してみてください。

この回答へのお礼

補足の質問にまでお答え頂きありがとうございました。

仰るように範囲指定うまくできました！

ところが、例示のようなものではなく本チャンのファイルに
入力してみたところうまく表示されないようです。

元ファイル（質問で言うところのBOOK１）の行数が500程度あるので
そこが問題かとも思ったのですが、そうではないようで・・・。
また、元ファイル自体も入力ファイルから関数で集計されたファイルなので、
そのせいかとも思ったのですが、テキスト入力してみてもうまくいかず。。

お礼日時：2010/10/27 18:37

No.4 回答者： MackyNo1 回答日時： 2010/10/27 16:34

>試行してみたのですが、どうもうまく表示されないようです。

。

よくみたら提示した数式が一部間違えていました（それでも２つ目のデータから表示されるはずです）。
数式の「ROW(A2)」の部分を「ROW(A1)」に訂正してください。

基本的に2つのブックを立ち上げておき、提示した数式を（ブック名とシート名の部分を適宜変更して）そのまま入力すれば、2つ目からの該当データが表示されているはずですので確認してみてください。

この回答へのお礼

重ねての回答ありがとうございます。
ちなみに質問ついでにひとつお答え頂ければ幸いです。

お教え頂いたやり方で、うまく表示されたのですが、セルの書式をyyyy/m
に設定してもシリアル値でしか表示されないようです。
&""の部分がなければうまく表示されるのですが・・・。


=INDEX([Book1.xls]Sheet1!A:A,SMALL(INDEX(([Book1.xlsx]Sheet1!$B$2:$B$10<>$E$1)*1000+ROW($B$2:$B$10),),ROW(A1)))&""

お礼日時：2010/10/27 17:34

No.3 回答者： MackyNo1 回答日時： 2010/10/27 09:22

別ブックのE1セルに「2010/10」と抽出したい日付が入力されているなら、以下の式をA2セルに入力して右方向に３つおよび下方向にオートフィルしてください。

=INDEX([Book1.xls]Sheet1!A:A,SMALL(INDEX(([Book1.xlsx]Sheet1!$B$2:$B$10<>$E$1)*1000+ROW($B$2:$B$10),),ROW(A2)))&""

日付がシリアル値で入力されている場合は、日付列のセルの書式をユーザー定義で「yyyy/m」 としてください（あるいは元のセルの書式のみコピー）。

この回答へのお礼

ありがとうございました！
試行してみたのですが、どうもうまく表示されないようです。。

私のやり方が間違っている気がするので、改めてやってみます。

お礼日時：2010/10/27 14:45

No.2 回答者： mitarashi 回答日時： 2010/10/26 23:46

フィルタオプションが良いのでは？

マクロの自動記録だと、どこから始めるかといった事が問題になりますが、
簡単なマクロなので、自分で書くつもりになれば、データがBook1、抽出条件がBook2、抽出先がBook3なんて事も可能です。ブックはいずれも開いている必要があります。

Sub test()
Workbooks("Book1.xls").Sheets("Sheet1").Range("A1:C50").AdvancedFilter Action _
:=xlFilterCopy, CriteriaRange:=Workbooks("Book2.xls").Sheets("Sheet3").Range("A1:D2"), CopyToRange:=Workbooks("Book3.xls").Sheets("Sheet1").Range("A1"), _
Unique:=False
End Sub

参考URL：http://www.eurus.dti.ne.jp/~yoneyama/Excel/filte …

この回答へのお礼

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

おのサイトで質問するようになってから
VBAの勉強の必要性を痛感しております。。

今回は関数で何とかすることになってしまったので、
別ファイルで勉強用にお教え頂いたVBAをやってみます！

お礼日時：2010/10/27 14:47

No.1 回答者： tom04 回答日時： 2010/10/26 22:12

こんばんは！

一例です。

日付はシリアル値が入力してあり、表示形式だけが「yyyy/mm」になっているとしています。

Book2のSheet1に年と月の数値を入力するとそのデータが表示されるようにしています。
Book1のSheet1に作業用の列を設けています。

作業列D2セルに
=IF(COUNTBLANK([Book2]Sheet1!$A$1:$A$2),"",IF(AND(YEAR(B2)=[Book2]Sheet1!$A$1,MONTH(B2)=[Book2]Sheet1!$A$2),ROW(),""))
という数式を入れ、オートフィルでずぃ～～～！っと下へコピー！

そしてBook2のA5セルに
=IF(COUNT([Book1]Sheet1!$D:$D)<ROW(A1),"",INDEX([Book1]Sheet1!A:A,SMALL([Book1]Sheet1!$D:$D,ROW(A1))))
という数式を入れ、列方向と行方向にオートフィルでコピーすると
画像のような感じになります。

尚、日付の列の表示形式はユーザー定義から
yyyy/mm　としています。

参考になれば良いのですが・・・m(__)m

この回答への補足

ちなみに参照するセルが複数ある場合、
例えば上記のsheet1のB2、C2、D2のいずれかに
該当する年月がある場合、という参照の仕方もできるのでしょうか？

補足日時：2010/10/27 14:42

この回答へのお礼

回答ありがとうございました！
このやり方でできました。

ありがとうございます。

お礼日時：2010/10/27 14:39