ExcelのVBAで効率的プログラムについて

質問

表のようなデーターからオーダー列の値「23」と、記号列の値「A」を、アンドで検索して数値列の値「5384」を検索したいのですが、どんな方法があるでしょうか
現在はAutoFilterを使っていますが、処理時間が掛かり過ぎて実用的ではありません

出来るだけ処理時間が短く、シンプルなプログラムがあれば教えて頂きたいです
よろしくお願いします

回答No.2

keithin · ベストアンサー

オートフィルタを使っているという事は、必ず一意に絞り込める保証があるなら、ワークシートのどこかてきとーな場所にcriteria領域を(一時的に・または恒常的に)準備してＤＧＥＴ関数を使った方がシンプルで高速です。
実は複数ヒットする可能性があるなら、同様にcriteria領域を仮に用意してAdvancedFilterを使ったほうが高速です。

今いったいどんなマクロで「処理が遅い」のか判りませんが、application.calculationをxlcalculationmanualにしてからオートフィルタで絞り込んでみると、改善するかも？しれません。

作成例：
application.screenupdating = false
application.calculation = xlcalculationmanual
range("A:C").autofilter field:=1, criteria1:=23
range("A:C").autofilter field:=2, criteria1:="A"
set res = range("C65536").end(xlup)
activesheet.autofiltermode = false
application.calculation = xlcalculationautomatic
application.screenupdating = true

if res.row = 1 then
　msgbox "NOT FOUND"
else
　msgbox res.value
end if

お礼

大変にありがとうございます
少し私のレベルを越えて難解な所も多いですが、調べて参考にさせて頂きます
出来れば「回答３の補足項目」を見て頂き、更にアドバイスして頂ける所があれば
是非お願いします

回答No.5

tsubuyuki

回答４さまがおっしゃる通り、質問がまるっきり変わってますね。

まず、
> [23,A,が-2384]、[23,F,が+2384]ですが、
こうなる理屈がさっぱりわかりません。
続いて、
> 時には表Aの[23,A]の5384と[24,A]の5373が合計され、
> 表Bに[28,M]の10757と表示されている事もあります
これを判断する条件が一切不明です。
この辺りはなんらかの人的な判断が必要なのではないですか？

私の理解力が劣っているだけかもしれませんが、
人にたいして説明しきれない処理をプログラミング出来るはず無いと思いませんか？

なので、すいませんが
> そのため３０回近くAutoFilterを繰り返さなければなりません。
> もっと簡単に出来る知恵があればよいのですが・・・
> どんなヒントでも構いませので宜しく願いします
この部分のヒントだけ、
３０回近く処理を繰り返すように、Do～LoopまたはFor～Nextを使って書いてやればＯＫ。
あとはご自身で調べるなり、他の回答がつくまで待つなり、質問しなおすなり、
なんとか解決の糸口をつかんでくださいませ。

回答No.4

keithin

人の回答に寄せられた補足を横取りして回答するような、そんな泥棒紛いの無礼なマネはしたくありません。
それに「実は」と言いながら、元のご相談とはまるで無関係なご質問にすり替わってます。一回このご相談は解決で閉じてから、改めて本当にヤリタイ事をキチンと詳しく情報提供して投稿し直してみたらどうですか。

一応ご参考までに。
どんなデータからどんな理屈でどんな結果が欲しいのか、さっぱり不明です。
それにご利用のエクセルのバージョンも不明のご相談ですが、エクセル２００７以降を使い、SUMIFS関数で計算させるのが一番簡単で手っ取り早いんじゃないかなと思います。
理解不明な時はキチンと正しい状況説明を添えて、仕切り直してご相談を投稿し直して下さい。

お礼

要領を掴めずわかりにくい事を書いて申し訳ありません
皆さんからアドバイス頂いた内容から自分なりにトライしてみます
いろいろ勉強になりました

回答No.3

tsubuyuki

どのように「時間がかかる」んでしょう？
・行数が多すぎて、オートフィルタのレスポンスが遅い
・オートフィルタの条件を指定するのに手間がかかる（＝面倒）

とりあえず、５万件のダミーデータで確認しましたが、
フィルタ処理自体は１秒未満で終わりました。
特別高性能なパソコンは使用していません。
いわゆる、ミドルスペックくらいのものです。

なので、「時間が・・」は後者と断定して進めさせていただきます。
（異論がございましたら、補足くださいませ。）

準備として、
・Ａ２セルに"オーダー"の検索値（今回は「23」）
・Ｂ２セルに"記号"の検索値（今回は「A」）
・４行目に項目名（"オーダー"・"記号"・"数値"などの文字列）の行
・５行目以降にデータ行
をご用意ください。

で、コードは以下。

Sub Macro1()
　　With Range("A4:C" & Range("A65535").End(xlUp).Row)
　　　　.AutoFilter
　　　　.AutoFilter Field:=1, Criteria1:=Range("A2")
　　　　.AutoFilter Field:=2, Criteria1:=Range("B2")
　　End With
End Sub

あとはボタンにでも登録しておいて、
Ａ２・Ｂ２セルを打ち変えてお試しくださいませ。

「列がもっとある」なら、コード中の"A4:C"の「Ｃ」を適宜、
項目行を４行目以外にするなら、"A4:C"の「４」を適宜、
それぞれ打ち変えてくださいませ。

補足

大変ありがとうございます、実は下記のような表A,Bの[オーダー列][記号列]のどこか１個所または数か所が違う表を比較・検索し数値の増減を調べています。そのため表Bの項目を上から順に変数に代入、表Aを順にAutoFilterし、Filterされた合計値が表Bの数値と同じかどうか比較しています
そのため３０回近くAutoFilterを繰り返さなければなりません。もっと簡単に出来る知恵があればよいのですが・・・どんなヒントでも構いませので宜しく願いします
表A　　　　　　　　　　　　表B
オーダー 記号 数値 　　　　オーダー 記号 数値
21 A 5367 　　　　21 A 5367
25 B 5380 　　　　25 B 5380
25 E 5369 　　　　25 E 5369
22 D 5370 　　　　22 D 5370
21 C 5371 　　　　21 C 5371
23 A 5384 　　　　23 F 5384
24 A 5373 　　　　24 A 5373

因みにこの表で導き出したい答えは、[23,A,が-2384]、[23,F,が+2384]ですが、時には表Aの[23,A]の5384と[24,A]の5373が合計され、表Bに[28,M]の10757と表示されている事もあります

回答No.1

あいあい わーるど（@IIWorld）

ご回答します。

VBAと記載していますが、関数で十分可能です。

１．表の左側欄外に、オーダーと記号を連結した列を作成する
２．VLOOKUP関数を利用して、検索する。
=VLOOKUP("23A",a2..D100,4,false)

上記の関数のパラメータは多少誤りがあるかもしれません。
ヘルプとかで研究してみてください。

よろしくお願いします。

補足

ありがとうございます
私の理解が浅いのか、どうやってオーダーと記号を連結した文字列で、VLOOKUPをかけるのでしょうか
エラーになるように思うのですが、
何か方法があるのであれば、宜しくお願いします

また、それは検索項目が複数ある場合にも活用出来るでしょうか