HOME > Q&A > Excel （一般機能）

Excel （一般機能）
Excelの一般機能に関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 10 Pro : Excel 2019)
外部ファイル参照のVLOOKUPを使ったファイル
投稿日時: 23/07/20 10:52:42
投稿者: ラングドシャ

よろしくお願いいたします。
 
500行でB〜W列までVLOOKUP関数が入っているブックがあります。
既に関数がある行は問題ありませんが、それ以上に増やしたい際
最終行をコピーし関数貼り付けをすると、VLOOKUP関数に使っている外部ファイルを
リンクしています〜　というメッセージがステータスバーに表示されまま
ずっと考え中の状態となってしまい貼り付けが終わりません。
 
外部ファイルを開いていると貼り付けはすんなり終わります。
 
元々、VLOOKUP関数の完全一致で指定した式にしていたのですが、これが原因で
遅いのだと思い、近似値を2回使って高速化するという方法に切り替えました。
ですが、貼り付け作業は考え中になって止まってしまう状態に変わりは
ありませんでした。
 
ブックの状態としては
・シートの保護をかけています。
・トラストセンターの外部コンテンツののブックリンクのセキュリティ設定は
「全てのブックリンクの自動更新を有効にする」にチェックしています。
・その他、外部ファイルはネットワーク上にあります。
 
外部ファイルを開いていなくても、関数コピー貼り付けが問題なく完了する方法は
ありませんでしょうか？
よろしくお願いいたします。
投稿日時: 23/07/20 11:02:24
投稿者: ラングドシャ

追記です
 

引用:
ブックの状態としては
・シートの保護をかけています。

としましたが、貼り付け時には保護解除しています。[/quote]
回答
投稿日時: 23/07/20 17:22:03
投稿者: Suzu

 22列 (B列〜W列) x 500行 = 11,000 の セル全てに対し VLOOKUPを行っているのですよね？
 
それを、ネットワーク上のファイルから参照する。
VLOOKUPなので、原理的には、1個1個参照し、それを、11000回繰り返す。
 
遅くなって当然かと。。
 
 
1. 少なくとも、VLOOKUPを使うより、MATCH＆INDEX関数を使った方が速いと言われています。そちらを試す
2. ブックの自動更新を 手動にする
3. PowerQuery を使う
4. VBA を 使い、対象データファイルを裏で開き、値を貼り付ける処理を行う
5. Excel ではなく、Accessを検討する
 
の順番で、検討でしょうか。
 
 
2021や、365であれば、スピルとかも使えるので、別のアプローチもありそうですけどね。
投稿日時: 23/07/21 11:40:26
投稿者: ラングドシャ

Suzu様
 
さまざまなご提案ありがとうございます。
既に関数が入っている500行に対しては問題ないのです。すぐに結果が表示されます。
それ以上に1行でもコピーすると、リンクしていますのメッセージが出て止まってしまいます。
コピー件数が少ないと、一応は完了するのですが･･･
関数アリの500行は問題ないのに、コピーするだけで？？？こんなに時間がかかるもの？？
と、ちょっと不思議な感じです。
 

引用:
1. 少なくとも、VLOOKUPを使うより、MATCH＆INDEX関数を使った方が速いと言われています。そちらを試す

こちらは試してみました。こちらも、同様の現象でした。
 
引用:
2. ブックの自動更新を 手動にする
3. PowerQuery を使う
4. VBA を 使い、対象データファイルを裏で開き、値を貼り付ける処理を行う
5. Excel ではなく、Accessを検討する

このなかから、4番を使ってみようと思い、早速ためしてみました。
非表示で開くように設定してみたところ、うまく動作しコピーも問題なくできました。
これで様子をみてみます。
パワークエリも興味があるのでやってみたいのですが、自分が使い慣れていないので完成までに時間がかかりそうであるため、ゆくゆく試してみます。
 
色々な方法があることが分かり勉強になりました。
いつもありがとうございます。