エクセル：別ファイルからの抽出
- エクセルに別ファイルからデータを抽出する方法を教えてください。
- エクセルの特定の条件に合致するデータを別ファイルから抽出する方法を知りたいです。
- VBAや関数を使用して、エクセルの特定の条件に合致するデータを別ファイルから抽出する方法について教えてください。
- ベストアンサー
エクセル：別ファイルからの抽出
エクセル：別ファイルからの抽出 エクセルにて、 あるファイルに 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がなくても よいです。 恐縮ですが、お答え願えれば有難いです。
- namsan3
- お礼率60% (42/69)
- その他MS Office製品
- 回答数7
- ありがとう数9
- みんなの回答 （7）
- 専門家の回答
質問者が選んだベストアンサー
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
その他の回答 (6)
- MackyNo1
- ベストアンサー率53% (1521/2850)
>お教え頂いたやり方で、うまく表示されたのですが、セルの書式をyyyy/m に設定してもシリアル値でしか表示されないようです。 &""の部分がなければうまく表示されるのですが・・・。 失礼しました。 エラー処理（この場合は０を表示しない）を簡便化するために、「＆""」の数式を提示しましたが、確かに返す値がシリアル値（文字列以外）の時は「＆""」の部分を削除する必要があります。 簡便に対応するなら、B列については、IF関数でC列の値が空白の場合は空白を、それ以外の場合は「＆""」を除いた数式にしてください。
お礼
再度の回答ありがとうございました！ ＩＦ関数にて処理することにしました。
- MackyNo1
- ベストアンサー率53% (1521/2850)
＞ちなみに参照するセルが複数ある場合、 ＞例えば上記の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程度あるので そこが問題かとも思ったのですが、そうではないようで・・・。 また、元ファイル自体も入力ファイルから関数で集計されたファイルなので、 そのせいかとも思ったのですが、テキスト入力してみてもうまくいかず。。
- MackyNo1
- ベストアンサー率53% (1521/2850)
>試行してみたのですが、どうもうまく表示されないようです。。 よくみたら提示した数式が一部間違えていました（それでも２つ目のデータから表示されるはずです）。 数式の「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)))&""
- MackyNo1
- ベストアンサー率53% (1521/2850)
別ブックの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」 としてください（あるいは元のセルの書式のみコピー）。
お礼
ありがとうございました！ 試行してみたのですが、どうもうまく表示されないようです。。 私のやり方が間違っている気がするので、改めてやってみます。
- mitarashi
- ベストアンサー率59% (574/965)
フィルタオプションが良いのでは？ マクロの自動記録だと、どこから始めるかといった事が問題になりますが、 簡単なマクロなので、自分で書くつもりになれば、データが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
お礼
ありがとうございました。 おのサイトで質問するようになってから VBAの勉強の必要性を痛感しております。。 今回は関数で何とかすることになってしまったので、 別ファイルで勉強用にお教え頂いたVBAをやってみます！
- tom04
- ベストアンサー率49% (2537/5117)
こんばんは！ 一例です。 日付はシリアル値が入力してあり、表示形式だけが「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のいずれかに 該当する年月がある場合、という参照の仕方もできるのでしょうか？
関連するQ&A
- Excel 別ファイルからのデータ抽出
OfficeXP-Excelにて質問させて頂きます。 以下のような異なるファイルが2つあります。 ------------------- [ファイルA] A B C D E F G 1 No. 1 2 3 2 氏名 山田 田中 佐藤 3 送付日 受領日 送付日 受領日 送付日 受領日 4 2007年04月分 4/2 4/27 4/3 4/26 4/2 4/25 5 2007年05月分 5/1 5/30 5/1 5/31 5/2 5/30 ・ ・ ・ (1行目・2行目のB:C、D:E、F:Gは結合されています。) ------------------- [ファイルB-シート2007/5] A B C D 1 2007年05月分 2 ←空白行 3 No. 氏名 送付日 受領日 4 1 山田 5 2 田中 6 3 佐藤 ・ ・ ・ ------------------- [ファイルA]のA4以降の『2007年**月分』のデータ『送付日』と『受領日』を [ファイルB]のセル[A1(2007年**月分)]と『No.』が一致する『送付日(C4～)』『受領日(D4～)』に 関数などを用いて抽出したいのです。 1年分のデータを1つの表にまとめて格納している[ファイルA]に対し[ファイルB]は各月のデータをシート毎に分けてあります。 [ファイルB]で元になるシートを作成しておき、毎月その元シートをコピーし[A1]の日付だけを変更すれば自動的に必要なデータを引っ張ってくるように出来ますでしょうか？ 説明が解り辛ければ補足致しますので、何卒ご教示宜しくお願いします。
- ベストアンサー
- オフィス系ソフト
- 【EXCEL】シフト表から休みの日だけを抽出する方法をご教授ください。
いつも大変お世話になっています。 申し訳ありません。どなたかお力をお貸しください。 １ヶ月のシフト表から休みの日を抽出したいのです。 休みの日の記号は常に【X】です。（＝Xを抽出したいのです） 出勤日の記号は英数字で、最大5桁まであります。 【拡大図】 A B C D E F 1 No 氏名 2009/4/1 2009/4/2 2009/4/3 2009/4/4 2 123 山田 A B B B 3 124 佐藤 A B B X 4 125 田中 X X A A 5 128 清水 C B X A 6 025 上田 C B X A 【縮小図】 A B C DEFGHIHIJKLMNOPORSTU 1 NO 氏名 日日日日日日日日日・・・・・・ 2 123 山田 ABBBAXXAAAAAXXBBBBBXXAAAAAXXAA 3 124 佐藤 ABBXXAAAAAXXBBBBBXXAAAAAXXAABB 4 125 田中 BXXAAAAAXXBBBBBXXAAAAAXXAABBAA 5 128 清水 CBXABBBBXXXBBBBCCCCXAAAAAXXXAA 6 025 上田 CBXABBXBAAABBXBCXCCXAAAAAXXXAA 【やりたいこと】 ※No（＝社員No)、氏名、休みの日を別シートに表示したいのです。 ※オートフィルタを使って、日毎に手動でやることは避けたいです。 A B C 1 No 氏名 休日 2 123 山田 2009/4/6 3 123 山田 2009/4/7 4 123 山田 2009/4/13 5 123 山田 2009/4/14 6 123 山田 2009/4/20 7 123 山田 2009/4/21 8 123 山田 2009/4/27 9 123 山田 2009/4/28 10 124 佐藤 2009/4/4 11 124 佐藤 2009/4/5 12 124 佐藤 2009/4/11 13 124 佐藤 2009/4/12 ・ ・ ・ ・ 申し訳ありませんが、 どなたかご教示をお願いします。
- ベストアンサー
- オフィス系ソフト
- エクセル 複数ファイルから特定のデータを抽出
いつもこちらでお世話になっております。 先日も同じような質問をさせて頂いたのですが、 ファイルの構成が変わってきてしまい、再度質問させて頂きます。 （元データ） A B C Ｄ Ｅ No 月1 月2 月2 氏名 1 2010/11 2010/8 2010/7 佐藤 2 2010/10 2010/8 2010/7 山田 3 2011/12 2010/8 2010/7 田中 4 2010/10 2010/8 2010/7 田中 ・ ・ 上記のデータが１年度あたり ファイル1（4シート+一覧シート） ファイル2（4シート+一覧シート） ファイル3（4シート+一覧シート） ファイル4（4シート+一覧シート） ファイル5（4シート+一覧シート） ※上記Ａ列のＮｏは1シート内での連番。 のように構成され、それが６年度分（計30ファイル）あるという状態です。 別ファイルにて2010/10とセルに入力してやると、各ファイルのＢ～Ｄ列に2010/10 とあるデータだけ抽出して一覧にしてくれるようにしたいと考えております。 関数またはVBAにて処理する方法はありますでしょうか？ お知恵を拝借願えれば幸いです。
- ベストアンサー
- その他MS Office製品
- Excelについ教えてください！
会社で、アルバイトの勤務時間を処理しなくてはなりません。 下記のようなexcelのファイルが1か月単位であがってきます。 Ａ Ｂ Ｃ Ｄ １ 4/10 山田 16:00 退勤 ２ 4/10 佐藤 14:00 出勤 ３ 4/10 田中 14:00 退勤 ４ 4/10 山田 12:00 出勤 ５ 4/10 田中 9:00 出勤 ６ 4/9 田中 16:00 退勤 ７ 4/9 佐藤 15:00 退勤 これを、 Ａ Ｂ Ｃ Ｄ １ 出勤 退勤 ２ 4/9 ●● ●● ３ 4/10 ●● ●● ４ 4/11●● ●● このように別シートに個人毎に日報として抽出したいのですが、うまくいきません。 vlookupだと一つの条件しか指定できませんよね・・・？ 例えば、4/10の、且つ、山田の、且つ”出勤”の時間を（Ｂ３に）絞り出すなんてことはexcelの関数ではできないのでしょうか？ よろしくお願いします。
- 締切済み
- Windows Vista
- excelオートフィルタの検索条件をセルに入力したい
以下のようなexcelのリストがあるとします。 Ａ Ｂ Ｃ～ １ 佐藤・鈴木 Ａタイプ ２ 田中・山田 Ｂタイプ ３ 佐藤 Ａタイプ ４ 田中 Ｃタイプ ５ 山田・鈴木 Ｃタイプ Ａ列の"田"が含まれる行を抽出したい場合 オートフィルタをかけ、オプションの抽出条件の指定で "田"を含む、で検索すれば良い、というのはわかります。 ですが、この動作をもうちょっと簡単にできないかと思っています。 具体的には、以下のようにセルに入力して検索・抽出するとはできないでしょうか。 Ａ Ｂ Ｃ～ １ 田 ２ ３ ４ Ａ Ｂ Ｃ～ ６ 田中・山田 Ｂタイプ ８ 田中 Ｃタイプ ９ 山田・鈴木 Ｃタイプ （1、２行目＝検索用 ３行目＝空き ４行目以降＝リスト） 過去の質問で、同じようにセルに入力して抽出する方法を 聞いていた方がいらっしゃったので参考にしようと思ったのですが 方法がVBAを使ったもので、VBAの知識がまったく無いために さっぱり理解することができませんでした。 何か良い方法がありましたら教えていただけないでしょうか。
- ベストアンサー
- オフィス系ソフト
- エクセルのデータ並べ替え（抽出）の方法
エクセルのデータ並べ替え（抽出）の方法 を教えてください。 下記のようなデータがあるとします。 A B C D １ 田中 東京 千葉 福岡 ２ 山田 京都 滋賀 ３ 佐藤 奈良 青森 ＵＳＡ ４ 鈴木 カナダ 愛媛 Ａ列は名前、Ｂ列以降は文字列です。Ｂ列以降はC列までの行、D列までの行とさまざまです。重複セルはありません。 これを下記のように並べ替えたいです。 A B C D １東京 田中 ２千葉 田中 ３福岡 田中 ４京都 山田 ５滋賀 山田 ６奈良 佐藤 ７青森 佐藤 ８USA 佐藤 ９カナダ 鈴木 10愛媛 鈴木 こういうことは可能でしょうか？？？ 教えてください。 よろしくお願いします。
- ベストアンサー
- オフィス系ソフト
- エクセル 複数ファイルから特定のデータを一覧に
エクセル 複数ファイルから特定のデータを一覧に 先日こちらで質問をさせて頂いたのですが、うまく解決できず 、またその後独力でも解決できないので、再度質問させて頂きます。 前質問 http://okwave.jp/qa/q6309713.html （元データ） A B C Ｄ Ｅ No 月1 月2 月2 氏名 1 2010/11 2010/8 2010/7 佐藤 2 2010/10 2010/8 2010/7 山田 3 2011/12 2010/8 2010/7 田中 4 2010/10 2010/8 2010/7 田中 ・ ・ 上記のデータが１年度あたり ファイル1（4シート+一覧シート） ファイル2（4シート+一覧シート） ファイル3（4シート+一覧シート） ファイル4（4シート+一覧シート） ファイル5（4シート+一覧シート） ※上記Ａ列のＮｏは1シート内での連番。 のように構成され、それが６年度分（計30ファイル）あるという状態です。 参照用ファイルのセルＡ1に2010/10と入力してやると、各ファイルのＢ～Ｄ列のどこかに に2010/10とあるデータだけ抽出して一覧に（参照用ファイル同シート内） してくれるようにしたいと考えております。 関数またはVBAにて処理する方法はありますでしょうか？ お知恵を拝借願えれば幸いです。
- ベストアンサー
- その他MS Office製品
- データを別シートに抽出してリアルタイムで表示させたい
いろいろ調べてみたのですが、どのようにすればいいのかわからなかったので質問させていただきます。 Excel 2003において、下記のシート１のデータをシート２～４に曜日ごとに抽出したいと思っています。 その際、シート１に追加したら自動でシート２～４に反映されるようにもしたいと思っていますが、どのようにしたらよいでしょうか？ Excelが詳しくない者が扱うことが多いので、できればマクロは使わず、関数でできたらいいと思っているのですが、最悪マクロを使った方法でも構いませんのでご教授いただければ幸いです。 【シート１】 a b c d e f 1 月 火 水 名前 生年月日 年齢 2 ○ 佐藤 1980/1/1 29 3 ○ 田中 1981/1/1 28 4 ○ ○ 山田 1982/1/1 27 【シート２】（月曜に○のついている人の名前と年齢を表示） a b 1 名前 年齢 2 佐藤 29 3 山田 27 【シート３】（火曜に○のついている人の名前と年齢を表示） a b 1 名前 年齢 2 田中 28 【シート４】（水曜に○のついている人の名前と年齢を表示） a b 1 名前 年齢 2 山田 27
- ベストアンサー
- オフィス系ソフト
- エクセルでグループ分け
お知恵を貸してくださいよろしくお願いします。 Ａ：グループ名 Ｂ：性別 Ｃ：名前 が100人分横で入っています。 それを、グループごとに分けて、縦の一覧にしたいのです。 Ａ Ｂ Ｃ １ Ａ 男 佐藤 ２ Ｃ 女 山田 ３ Ｂ 女 田中 ４ Ａ 女 鈴木 ↓（別シートに） Ａグループ Ｂグループ Ｃグループ １ 男 佐藤 女 田中 女 山田 ２ 女 鈴木 ３ ４ このように抽出して、並び替えることを関数をつかってできないでしょうか。よろしくご教示ください。
- ベストアンサー
- オフィス系ソフト
お礼
再度の回答誠にありがとうございました。 画像もつけていただき、とても分かりやすいです。 お教え頂いた方法で試したところ、見事本チャンのファイルでも うまく表示されました。 ありがとうございました。