VBAの計算式で値を検出し別シートに貼り付ける

質問

例でA.xls sheet1に添付のデータがありB.xls sheet1のB列に検索値がありC列に記載のとおりの計算式が入力されています。関数入力はC2セルにの計算式を入力しそれを元にオートフィルでC3以降入力しているが膨大な計算式になりExcelを開いたりするのに処理に時間がかかりすぎる為セルに計算式をいれるのではなくVBAで計算式を入力しオートフィルみたいに連続した計算の値のみを入力したいのですがコードが解る方宜しくお願いします。

回答No.2

imogasi

#1です。
下記がピンと来ない場合は無視してください。
VBAで、マッチングという方法でやることを提案してみる。
予想では、骨子部分は１５行ぐらいのコード行数か。
ーー
・エクセル関数を使わない（シートに関数を設定しない）
・VBAのFindメソッドも使わない
・多分１０万行ぐらいなら、早く終わる、方法の提案。
VBAで関数式を作って、セルにセットするなんてVBAではふつう考えない。
＞VBAで計算式を入力しオートフィル
計算式を作る、式の複写を作る、のが結構むつかしいと思うが。
ＶＢＡ経験者はそういう発想をしないのではないか。
ーー
１日に１回まとめてする処理や１か月に１回まとめて処理などに最適。
オフラインバッチ処理というものです。レガシー的やり方ではあるが、応用が
広いものです．窓口でお客と対応しているときに検索するような処理には
適しません。
ーー
両ファイルとも、事前に目的のキーで、エクセルのソート機能を使って
ソートする必要がある。
　Ａファイル　　○順でソート
　Ｂファイル　　○順でソート
　　○はVLOOKUP関数の第１引数の列のデータ。
シートの処理最初行は（第２行から始まると仮定した場合）
　　ＡファイルＳｈｅｅｔ第２行目をポイント
　　ＢファイルＳｈｅｅｔ第２行目をポイント。
　　（ポイント（値）はそれぞれ専用の変数を設け、それに値を持ち、参照にはCells（ポインタ変数、列記号）で
セルの値を参照・取得することです。（初回のみＡ．Ｂファイルを両方読まないと比較できないので両方読む。両方読みモードをＯＮにする。）
ーー
（ｘ）Ａファイルのシートのデータ行のポイントしている行を読む。初回のみＢファイルも読む
Ａ，Ｂ両方読みモードＯＮなら、Ａ，Ｂ両方読みモードＯＦＦにして（ｙ）へ
でなければ、比較（ｃ）へ。
（ｙ）Ｂファイルのシートのポイント行を読む。
（ｃ）ＡファイルからのキーとＢファイルからのキーについて
両者のキーを比較する（ＩＦ文などで）。　
３つの場合がある
ーー
　　Ａ＝Ｂの場合（見つかった場合）
　　　　Ａファイルの行からほしいデータ列のデータを取得する
　　　　Ｃファイルに書き出す。キーやなど。
　　　　Ａ、Ｂ、Ｃのファイルで次行へポイントを進める（列番号を＋１）
A、Ｂ両方読みモードにして（ｘ）へ行く。
　　Ａ＜Ｂの場合（ＢはＡの現在行キーを飛び越えた）
　　　　Ａのポイント行を１つ進めて、
　　　　（ｘ）に行く
　　Ａ＞Ｂの場合（ＡはＢの現在行キーを飛び越えた。ＢはＡに見つからなかったということ。
　　　　　　その処理（見つからないという注記など）をする
　　　　Ｂのポイント行を１つ進めて、
　　　　（ｙ）に行く
ーー
　　AまたはBファイルのポイント行が最終行以下を指す様になったとき、終了に持ち込む手法。
　　　　　終わったフィルの方の最終行の次の行にＨＩＧＨＶＡＬＵＥをセット。
　　　　　比較（ｃ）に行って比較を続ける。
　　A=Bのルーチンの中にAのキーがHIGHVALUEか聞くステップを入れる。
http://www.gizcollabo.jp/vbtomo/log/archive/choshoqa_22071_0.html
等しければ本件全体処理終了。
　AとBの行を１回読む処理時間で終了するはず。事前処理にソート時間が加わるが。計算は全くしていない。行数に１加える部分だけ例外。
毎回画面処理をさせないため、Screenupdating＝ＦＡＬＳＥは入れること。

お礼

ありがとうございます。

回答No.1

imogasi

質問の表現が要領を得ず、内容がはっきりわからない。画像もぼやけてよくわからない。
ＶＬＯＯＫＵＰ関数を使っている点を、ＶＢＡでやるということか？。
データ数のオーダー（例１万、１０万、それ以上）など質問に書くべきだろう。
それなら第１引数に当たる値で第２引数のセル範囲の最左列を検索し、指定列のデータを取ってきて、どこかにセットすればよい、という内容か？。
そのためには、（１）ＶＢＡでもＶＬＯＯＫＵＰ、ＭＡＴＨ関数などが使えるが、（２）普通はＦｉｎｄ関数を使って、第１引数の値で第２引数の範囲の最左列を検索し（一番初めに見つかる行を見つけて）、その行の右列指定列のデータを取ってくればよい。
第１引数の値は複数行で見つかる場合はないのか（ユニークという）。これは質問に書いておくべき事項。
複数行で見つからないならＦｉｌｔｅｒでやることもないのだろう。
ただし（１）、（２）ともデータ数が多いと、検索に時間がかかり、関数より早いかどうかやってみないとわからない。
（３）Ａｃｃｅｓｓでの処理などに転向するのも検討しては。
（４）関数式をシートにＶＢＡでセットするというアイデアは馬鹿げているように思う。
（「＞VBAで計算式を入力し」のやり方。）
＞「膨大な計算式」は、（セル）数のことか、式の入力文字数が多いということか。
質問の表現からして、エクセルにどの程度慣れているのか、心配。ＶＢＡの経験があるのか。
ーー
関数を利用のアイデアを断ち切り、ＶＢＡのデータ処理（ＶＢＡの中に検索や計算などのプログラムを作る）に切り替えてやってみて、処理時間がガマンできるなら、その考えを取ればよい。
しかしＶＢＡのプログラムを組む力があるのか。質問の表現ぶりからして、プログラムを組む力は無いように見えるので、丸投げしてコードを教えろということか。

お礼

ありがとうございます。