Excel (VBA)

Excel VBAに関するフォーラムです。
  • 掲示板への投稿には会員登録(無料)が必要です。会員登録がまだの方はこちら
  • 掲示板ご利用上のお願い」に反するご記入はご遠慮ください。
  • Q&A掲示板の使い方はこちらをご覧ください
トピックに返信
質問

 
(Windows 10全般 : Excel 2016)
転記元の範囲が連続ではない場合に、別シートへ高速に転記する方法はありますでしょうか。
投稿日時: 21/08/30 12:46:11
投稿者: むぎまき

シート1のB列の値をシート2・3・4から探し、
一致した値があった場合、シート1のO列から一列ずつ飛び飛びでZ列までの値を、
該当のシートの同列に転記したいです。
 
データが約20000行ありますのと参照セル範囲が多く、WorksheetfunctionのVlookupですと
かなり時間がかかってしまいました。
配列に入れて一括で転記する方法が早いとのことですが、
知識不足で恐縮ですが、よく理解できませんでした。
転記元範囲がの行・列が飛び飛びのため、やはり一つずつセルを見に行くしかないのでしょうか。
もしよい方法がございましたら、ご教授いただきたく存じます。
 
下記のように転記したいです。
シート1
B列   O列 Q列 S列 … Z列
11111  あ  い  う   お
22222  か  き  く   こ
33333  さ  し  す   そ
 
シート2           |シート3           |シート4
B列  O列 Q列 S列 … Z列 |B列  O列 Q列 S列 … Z列 |B列  O列 Q列 S列 … Z列 
11111 あ  い  う   お  |22222 か  き  く   こ  |33333 さ  し  す   そ
 

回答
投稿日時: 21/08/30 13:54:41
投稿者: WinArrow
投稿者のウェブサイトに移動

転送元シート側の指定セル(O〜Z)の間の列は空白と考えてよいならば、
1列おきと考えずにO〜Zまでの範囲で複写すればよいでしょう。
空白でなくても、複写後に不要列をクリアする方法もあります。
  
  
確認
キーとなる「値」は、転送元シートのどこかに存在すると考えてよいですか?
キーとなる「値」は、転送元シート内では一意(重複しない)でしょうか?
  
Worksheet関数のCOUNTIFとVLOOKUPの組合せで対応できると思いますが・・・・

回答
投稿日時: 21/08/30 14:16:38
投稿者: Suzu

方法の前に、前提条件の確認をさせてください。
 

引用:
データが約20000行あります

これは、シート1 の行が 20000行 あると言うことでしょうか。
それとも、シート2〜4 のデータが 20000行 あるのでしょうか?
 
引用:
配列に入れて一括で転記する方法が早いとのことですが、

転記との事ですが、あくまでも、速いのは、『一括貼り付け』なのですよ。
セルの範囲を選択後コピー コピー先 を選択し 張り付ける これと同じ事をやる場合の話です。
引用:
O列から一列ずつ飛び飛びでZ列までの値を、
該当のシートの同列に転記したいです。

との事ですが、P列、R列 ・・ に 既に値が入っており
その値を残したい場合には、その値を 一旦、配列側に渡し、
 求めた O、Q・・列の値と一緒に貼り付けを行う必要があります。
 
また、今回遅いのは、貼り付け部分も改善の余地はあると思いますが
大部分は、VLOOKUP関数部分だと思います。
 
コードの所々に、Timer関数等で、処理時間を算出し どの処理に時間を要しているのか確認してみてください。
 
 
対策としては、PowerQuery を使用すれば 必要なデータを得られると思います。
 
Office TANAKA さんの
【VLOOKUP関数と同じことをする】
http://officetanaka.net/excel/function/GetAndTransform/03.htm
 
が参考になるかと。

回答
投稿日時: 21/08/30 14:17:42
投稿者: 素人眼鏡

1点気になったので

引用:
一致した値があった場合、シート1のO列から一列ずつ飛び飛びでZ列までの値を、
基点をO列から1列飛びで行くとY列を参照するので、Z列は参照しないのですが、
何か条件の間違いや見落としはないですか。
 
WinArrowさん提示の方法でやるなら実態データに合わせてクリアする列を選ぶと思うので、
影響ないでしょうけど、他の方法をするときの影響が気になります。
引用:
転送元シート側の指定セル(O〜Z)の間の列は空白と考えてよいならば、
 1列おきと考えずにO〜Zまでの範囲で複写すればよいでしょう。
 空白でなくても、複写後に不要列をクリアする方法もあります。

投稿日時: 21/08/30 14:31:53
投稿者: むぎまき

WinArrow様
ご返信ありがとうございます。
 
転送元シート(シート1)についてですが、
・O〜Z列の間の列には、転記したくない不要なデータがございます。
・キーとなる値は、記載した例ですとB列の数字5桁です。
 また、転送元(シート1)内、転送先(シート2、3、4)内でのキーの重複はございません。
 
シート1 (転送元)
 B列   O列 Q列 S列 … Z列
11111  あ  い  う   お
22222  か  き  く   こ
33333  さ  し  す   そ

投稿日時: 21/08/30 14:45:00
投稿者: むぎまき

素人眼鏡様
ご返信ありがとうございます。
 
後出しになってしまい大変申し訳ございません。
実際のデータでシート1(転記元)で転記したい列は、O〜AH列となり、
必要な列 O・P・R・S・U・V・X・Z・AB・AD・AF・AH
不要な列 Q・T・W・Y・AA・AC・AE・AG
なのです。こちらを転記先の各シートのO〜Z列に転記したい次第です。
本当に申し訳ございません。

投稿日時: 21/08/30 14:49:18
投稿者: むぎまき

Suzu様
ご返信ありがとうございます。
 
シート1の行が約20000行ございます。
(シート2〜4のデータは多くて2000弱です)
Timer関数での時間計測確認してみます。
 
PowerQuery使用したことがありませんでした。
教えていただいたURL拝見いたします。

回答
投稿日時: 21/08/30 15:50:30
投稿者: simple

各シートに見出し行(ダミー)を追加(*)したうえで、
フィルタオプションを使ったらどうですか?
マクロにすることも勿論可能です。
たぶん10行かからずに書けると思います。
(*)Sheet2〜4には必要な列の見出しだけを書き、それを「抽出範囲」に指定します。
フィルタオプションはかなり高速のはずです。

回答
投稿日時: 21/08/30 16:18:18
投稿者: WinArrow
投稿者のウェブサイトに移動

各シートのレイアウトの説明で、
私の提案は、難しいと感じました。
 
作業用シートを提案します。
 
シート1、2,3,4を無条件に作業シートに複写(できれが「値複写」)未意義や合体します。
作業シートを転送先シートのレイアウトに合わせる形で列を整理します(不要列の削除、または、クリア)
この作業は手作業でも、マクロでもできますよね・・・・

トピックに返信