[[20170422165939]]　『別ブックの値で一致したものを行ごと抽出』（あっきー）　ページの最後に飛ぶ

『別ブックの値で一致したものを行ごと抽出』（あっきー）

初めまして。
マクロ初心者です。
いつも参考にさせていただいております。

今回は、過去の記事にありました「別ブックの全シートから条件に合った値を抽出」という記事に似たものになってしまうが、ご教示いただけるとたすかります。知識不足で、同様に単一セルへの反映はできたのですが、行の抽出ができなくて、質問を上げさせていただきました。

ケース
ファイル1、ファイル2、ファイル3があり、ファイル2、3には下記のようにデータが入っています。

ファイル2
　A B C D F　… AZ

  1   2    3   4   5      52
　1   2    3   4   5      52
  a   b    c   d   e      az

ファイル3
　A B C D F　… AZ

  1   2    3   4   5a      52
  a   b    c   d   e1     az

基本一致と判断するのは、Ａ列に含まれる値の想定です。
しかし、A列が一致していても、その他の列においては、変動するため、その情報においてもそれぞれの行として、Aのファイルに抽出したいと思っています。

ファイル1においては、A列にファイル2，3と共通の値が含まれています。

Vlookupでのやり方を考えたのですが、行だけで10万近くあるレコード数で動作しなくなることが多々あったので…

わかりにくい説明で大変恐縮ですが、よろしくお願いいたします。

＜使用 Excel：Excel2013、使用 OS：Windows7 ＞

 肝心のファイル１のイメージが見えませんが、アップされた　ファイル２、ファイル３の場合、
 ファイル１は、どうなってほしいか、それを例示してもらえませんか？

 それと、「ファイル」ですね？　「シート」ではなく。

 で、この 10万件処理を、何で行いたいですか？

 VLOOKUP以外の、でも「数式」で？
 それとも？

（ β） 2017/04/22(土) 17:26

説明不足で申し訳ありません。

ファイル1はA列の値は入力してある想定です。
A列(条件値)にB列以降のものを下記のように表示されたいです。

A B C D E … AZ
1 2 3 4 5 52
1 2 3 4 5 52
1 2 3 4 5a 52
a b c d e az
a b c d e1 az

それぞれは「ファイル」です。

処理をマクロで行いたいと考えています。

（あっきー） 2017/04/22(土) 17:44

 > ファイル1はA列の値は入力してある想定です。

 ほんとですか？

 ということは　ファイル1のA列には　抽出したい文字列　たとえば 1 と a だけが

 1
 a

 といったようにセットされているんですね？

 まさか

 と記入されているんじゃないですね。

 ところで、これらファイルのシートですけど、先頭にタイトル行はありますか？
 あるとすれば、３つのファイル、同じタイトル行ですか？

（β） 2017/04/22(土) 18:09

 1
 a
のようにファイル1の検索値は、それぞれ1つずつです。
それぞれのファイルに「1」が3つ、「a」が2つとかに分かれています。

先頭にタイトル行はついています。
3つともすべて同じタイトル行です。

（あっきー） 2017/04/22(土) 18:18

２つのブックから２条件に合致する行を抽出ですか。
手作業で、オートフィルターとコピペを繰り返すのが簡単ではないですか。

（マナ） 2017/04/22(土) 19:48

並べ替えも必要でしたか？
だとしても、これだけの作業です。
何か勘違いしていますか。

１）ブック２で、１に等しい　or aに等しい　でオートフィルタ
２）抽出行をブック１にコピペ
３）ブック３で、１に等しい　or aに等しい　でオートフィルタ
４）抽出行をブック１にコピペ
５）ブック１で、A列並び替え

（マナ） 2017/04/22(土) 20:01

マナさんアドバイスありがとうございます。

事実、フィルター、コピペの繰り返しは私もやっていました。

実際あまり時間に猶予がないのが事実なので、作業効率の向上を図ってみたく、今回相談させていただいきました。

やり方として、コピペの繰り返しがベストチョイスであれば、その方向性で進んでいくしかないのかなとも思っています。

（あっきー） 2017/04/22(土) 20:04

今のやり方で何に困っているのか。
どこに時間がかかっているのか不明なのです。

実際は、抽出条件が沢山あるとか、
ブックの数も２個じゃないとか。

シートレイアウトの件も含めて、
もっと実際の条件に近いもので説明していただかないと
手作業でよいのでは？
となります。

（マナ） 2017/04/22(土) 20:13

マナさん

説明が下手で申し訳ございません。

補足させていただきます。

上記にて、2つのブック内の情報を１つのブックにというのは、今回の集計においての根幹を進めるにおいて質問を上げさせていただきました。

ブック総数は約40。
一ブックの行数は、10万件ほど。
10万件は前述のように、A列以降の列に部分部分で違う値が含まれています。
　A B C D F　… AZ

  1   2    3   4   5       52
　1   2    3   4   5a      52
A列の条件「1」に関わる行が2列ないし3列以上になってきます。
抽出条件は、約2万件ほどあり、フィルターに時間を要する。

なかなか、うまく説明しきれない部分が多々あると思いますが、とりあえず補足させていただきます。
（あっきー） 2017/04/22(土) 21:02

 私からは、処理としては　集約シートの A列を、右のほうの離れた未使用列にコピペした上で、そこを検索条件として
 フィルターオプションで抽出する方法を提案しようと思っていました。
 ファイル3からの抽出時は、ファイル2からの抽出データの下にタイトル行をコピーして、そこに抽出した上で
 そのタイトル行を削除。

 もちろん、何らかの理由で自動処理が必須だということであれば、こういった操作をマクロ記録して組み立てればいいわけで
 いずれにしても、処理部品としてはオートフィルターないしはフィルターオプションでしょうね。

 ところで【抽出条件が2万件！】というのは本当ですか？
 集約シートのA列に抽出文字列が2万行かかれているということですか？

 もし、そうであっれも、いずれにしてもフィルター処理でしょうね。

（ β） 2017/04/22(土) 21:06

＞抽出条件は、約2万件ほどあり、フィルターに時間を要する。

＞事実、フィルター、コピペの繰り返しは私もやっていました。

本当でしょうか？不可能に思えるのですが。
やはり、わたしが何か勘違いしているようです。

（マナ） 2017/04/22(土) 21:13

ところで【抽出条件が2万件！】というのは本当ですか？集約シートのA列に抽出文字列が2万行かかれているということですか？

約ですが、2万件ほどあります。
A列に2万行あるということになります。

その2万行の中身が、わかれるので総数が4～5倍ほどになってきてしまうんです。

最終的にはフィルター処理になってくるんでしょうが、そこに至るまえに
ファイル2と3の中で条件「1」に一致するすべての行を作らなければいけないと思い、今回の質問を上げさせていただいた次第になります。
（あっきー） 2017/04/22(土) 21:16

マナさん

本当です。
実際数日かけても、全体のうち1割も満たしていないのが現状です。
なので、この作業効率を向上させたいという気持ちです。
（あっきー） 2017/04/22(土) 21:20

 よくわかりません。

 たとえば　A列が 1 のもの　ということなら、A列が 1 という１つの条件だけでいいのですけど？
 それがフィルターです。

 もちろん、A列が 1 であり、かつ　C列が a か b 、 F列が　xyz のもの　ということであれば
 それなの条件指定が必要ですが・・・？？？？？

 >そこに至るまえに ファイル2と3の中で条件「1」に一致するすべての行を作らなければいけないと

 この意味を、具体的に教えていただけませんか？

（β） 2017/04/22(土) 21:21

まだ勘違いしているかもしれませんが。

フィルタオプションで、数式＝Countif(2万件の条件のセル範囲, A2）>0
で抽出すればお望みの結果ですか？

それを40ブックでについて実行しコピペするなら、手作業でもできそうですが。

（マナ） 2017/04/22(土) 21:44

なかなか私のやっていきたいことが、文章化できていないので、皆様にご迷惑をおかけして申し訳ございません。
【例】
ブック1
1行目　A列　商品名「１」
2行目　A列　商品名「２」
3行目　A列　商品名「３」

ブック2
1行目　A列から、商品名「１」、商品コード「1-a]、価格650…
2行目　A列から、商品名「２」、商品コード「2-a]、価格750…
3行目　A列から、商品名「３」、商品コード「3-a]、価格600…
4行目　A列から、商品名「１」、商品コード「1-b]、価格850…

ブック3
1行目　A列から、商品名「１」、商品コード「1-c]、価格950…
2行目　A列から、商品名「２」、商品コード「2-b]、価格750…

ブック1のA列を【条件値】として、ブック2,3の条件値に一致する行を抽出
ブック1
1行目　A列から　商品名「１」、商品コード「1-a]、価格650…
2行目　A列から、商品名「１」、商品コード「1-b]、価格850…
3行目　A列から、商品名「１」、商品コード「1-c]、価格950…
4行目　A列から　商品名「２」、商品コード「2-a]、価格750…
5行目　A列から、商品名「２」、商品コード「2-b]、価格750…
6行目　A列から　商品名「３」、商品コード「3-a]、価格600…

このようなものを作成できる可能性があるのかどうかというのが私ではわからなくて...

（あっきー） 2017/04/22(土) 21:45

 これがフィルター機能の本筋です。

 抽出元ブックに対して、Ａ列が　１　か　２　か　３　だよと条件を与えれば
 それぞれのブックから、さくっと　指定のものが抽出されますよ。

 それを　フィルターオプションでやるか、オートフィルターでやるか、
 また、操作でやるかマクロ化しておいてマクロ実行でやるか、私はこだわりませんが
 いずれにしても　フィルター処理です。

 なぜ　ブック1 の 1 という条件で、各ブックを眺めて、他の列の値を取り出して
 それも含めて抽出条件にしたいのか、どうしても、わかりません。

 もう一度、各ブックのA列が　１　か　２　か　３　のもの　だけでは、なぜだめなんですか？？？

（β） 2017/04/22(土) 21:54

すこし頭の整理をつけてから、また返答させていただきます。

確かに自分自身フィルターでいいという気持ちもあるんですが、少しでもフィルターからコピペというのを作業が機械的に行ってくれたらと思い今回、質問させていただいた次第です。

みなさまの時間を割くようなことをしてしまい、申し訳ございませんでした。

また、何かありましたらご相談にのってください。

（あっきー） 2017/04/22(土) 22:09

もうみてないかもしれませんが、
βさんのおっしゃるフィルタオプションでできそうな気がしますが…？

各ファイルの1行目はタイトル行がついているとして

データ→並べ替えとフィルタ内の詳細設定

選択した範囲(O)　選択
リスト範囲(L)　：ファイル2のリスト範囲
検索条件範囲(C)　：ファイル1の記入してある範囲A列
抽出範囲(T)　：ファイル1なりなんなりの右の方の開いてるスペースのセル1個選択

ファイル3～40についても同様に実施後、最終的に重複するものを削除とか
（tata） 2017/04/23(日) 00:01

あら、そうでうね。
わたしの数式云々は、はずかしので、削除したい…

（マナ） 2017/04/23(日) 00:21

あっきーさん

あっきーさんが何に困っているのか、回答者にはよくわからないのですが、

＞実際数日かけても、全体のうち1割も満たしていないのが現状
ということは、もしかして、オートフィルタの☑を手作業で2万件照らし合わせて☑つけてたとか？

（tata） 2017/04/23(日) 01:35

 こんにちわ。

 抽出条件が2万件もあるならフィルターオプションではExcelが固まるんじゃないですか？
 オートフィルターも全ての条件を1つづつ抽出では膨大な時間がかかりますね。

 こう言う要件では、予め全てのシートのデータを抽出条件キー(今回の要件では商品名)で
 昇順にソートしておいて、配列で処理するしかないですね。

 もしくは上記方法よりは若干遅いですけどDictionaryを使えば予めソートしておく必要はありません。

 でもコードを書く前に以下の質問にお答えください。
 1,ブックは全て同じフォルダ内に存在するんですか？
 2,フォルダ内に目的のブック以外の余計なブックは存在しますか？
 3,マクロブックに転記すれば良いんですか？
 4,各ブックは対象データシート以外に、余計なシートはありますか？
 5,無条件で全てのブックの条件に合うデータを転記すれば良いですか？
 6,抽出条件は、今回実行する時には全てのブックで同じ条件を使用するんですか？
 7,条件専用のブックは無駄なので、結果用のブックに条件シートと言う風にしてはどうでしょうか？

（sy） 2017/04/23(日) 11:48

 >抽出条件が2万件もあるならフィルターオプションではExcelが固まるんじゃないですか？

 実際のところは　質問者さんに聞かなければ、わからないことですけど、私は勝手に　条件が 2万件もあるとは
 思っていません。

 条件は 1,2,a,x,・・・　とせいぜい10種類ぐらい？
 なぜかはかわりませんが、たとえば　その 1 で、ブック2 の他の列の値の組み合わせを取り出して、その組み合わせの結果を
 抽出条件といっておられるのかなと。

（β） 2017/04/23(日) 11:53

 >その組み合わせの結果を抽出条件といっておられるのかなと。
 抽出結果の件数が2万件と言う事ですか？

 でも（あっきー） 2017/04/22(土) 21:16 で
 >A列に2万行あるということになります。
 と言ってるので実際に2万行あるのでは無いでしょうか？
 まぁ商品名が2万データもあるって私もちょっと想像つかないですし、
 条件の値が重複してるんじゃないかとかの疑問はありますけどね。

（sy） 2017/04/23(日) 12:06

こんにちは。

＞抽出条件が2万件もあるならフィルターオプションではExcelが固まるんじゃないですか？

そうですね。固まらないかな、と私も思って、一応投稿前にダミー試してみたんですが、
rand()で発生させた、
52列×10万行
から、
2万行分の検索条件
で実施すると、1～2分かかってた感じでした。

パソコンのスペックによっては苦しいかもしれませんが、
現実的な範囲内かな、ダメでも検索条件5000行ずつとかで行けるかな、と思います。
（tata） 2017/04/23(日) 12:28

 私のPCで2万件の条件で3列10万行のデータを抽出すると、7000件くらい抽出したあたりで固まりますねぇ。

 PCに寄らずに抽出出来る条件の件数は5000件くらいじゃないかな？

 何れにしても抽出条件が数千件を超えると抽出に時間がかかりすぎるので、
 配列処理なら2万件の条件でも1秒かかるかくらいなので、フィルター関連は非効率ですね。

（sy） 2017/04/23(日) 12:39

コメント返信：

[ 一覧(最新更新順) ]

[[20170422165939]] 『別ブックの値で一致したものを行ごと抽出』（あっきー） ページの最後に飛ぶ

[[20170422165939]]　『別ブックの値で一致したものを行ごと抽出』（あっきー）　ページの最後に飛ぶ