Excel VBA質問箱 IV

当質問箱は、有志のボランティア精神のおかげで成り立っています。
問題が解決したら、必ずお礼をしましょうね。
本サイトの基本方針をまとめました。こちら をご一読ください。

投稿種別の選択が必要です。ご注意ください。
迷惑投稿防止のため、URLの入力を制限しています。ご了承ください。

  新規投稿 ┃ツリー表示 ┃一覧表示 ┃トピック表示 ┃検索 ┃設定 ┃過去ログ ┃ホーム  
7723 / 13644 ﾂﾘｰ ←次へ　｜　前へ→
▼ 【37107】Ｌｉｋｅ関数を使った処理を高速で行うには？ カド 06/4/21(金) 20:11 質問[未読]
【37108】Re:Ｌｉｋｅ関数を使った処理を高速で行... neptune 06/4/21(金) 20:52 発言[未読]
【37109】Re:Ｌｉｋｅ関数を使った処理を高速で行... カド 06/4/21(金) 22:25 発言[未読]
【37110】Re:Ｌｉｋｅ関数を使った処理を高速で行... neptune 06/4/21(金) 23:09 回答[未読]
【37111】Re:Ｌｉｋｅ関数を使った処理を高速で行... カド 06/4/21(金) 23:53 発言[未読]
【37112】Re:Ｌｉｋｅ関数を使った処理を高速で行... Ned 06/4/22(土) 1:24 発言[未読]
【37114】Re:Ｌｉｋｅ関数を使った処理を高速で行... Ned 06/4/22(土) 2:47 発言[未読]
【37116】Re:Ｌｉｋｅ関数を使った処理を高速で行... カド 06/4/22(土) 7:36 お礼[未読]
【37123】Re:Ｌｉｋｅ関数を使った処理を高速で行... Ned 06/4/22(土) 16:09 発言[未読]
【37248】Re:Ｌｉｋｅ関数を使った処理を高速で行... カド 06/4/26(水) 21:43 お礼[未読]
【37115】Re:Ｌｉｋｅ関数を使った処理を高速で行... カド 06/4/22(土) 7:31 発言[未読]
【37107】Ｌｉｋｅ関数を使った処理を高速で行うに...
質問  カド  - 06/4/21(金) 20:11 -
引用なし
パスワード
   アクティブセルに検索元データ
アクティブセルから３つ左に検索先データ
アクティブセルから２つ左に取得したい値があります。

以下のようなコードを書いたのですが、データ量が数万個あると
処理速度が数分程度掛かってしまいます。

なんとかもっと高速に処理したいのですが、なんとかならないでしょうか？


Sub データ取得_あいまい()

　　Dim AAA(1 To 50000, 1 To 1)
　　Dim a As Single, b As Single
　　
　　Set ACELL = Range(ActiveCell.Offset(, -3), ActiveCell.Offset(, -3).End(xlDown))
　　Set CCELL = Range(ActiveCell, ActiveCell.End(xlDown))
　　
　　a = Timer
　　
　　Set r = ActiveCell

　　AA = ACELL.Count
　　CC = CCELL.Count
　　
　　For i3 = 1 To CC
　　
　　　　For i1 = 1 To AA
　　　　　　Ma = CCELL.Cells(i3) Like ACELL.Cells(i1)

　　　　　　If Ma Then
　　　　　　　　AAA(i3, 1) = ACELL.Cells(i1).Offset(, 1)
　　　　　　　　Exit For
　　　　　　End If
　　　　Next i1

　　Next i3
　　
　　r.Offset(, 1).Resize(50000).Value = AAA
　　
　　b = Timer
　　MsgBox b - a

End Sub
【37108】Re:Ｌｉｋｅ関数を使った処理を高速で行...
発言  neptune  - 06/4/21(金) 20:52 -
引用なし
パスワード
   ▼カド さん：

>なんとかもっと高速に処理したいのですが、なんとかならないでしょうか？
今のやり方よりは速くなるんじゃないですか？
１．私自身はやったことは無いんですが、Findメソッドでやってみたらどうでしょう？
　全部なめるよりは速いと思うんですが？
２．どんなデータか不明なのでなんともいえませんが、先にIndexを作成しておく。
　　Index作成にロジックなど手間は掛かるが、非常に早いはず。
３．DAO、ADOなどの使用を考える。
４．同じ全部なめるにしても、なめ方を考える。
　　「2分検索」や「検索アルゴリズム」でググルと沢山、文献がある
　　
【37109】Re:Ｌｉｋｅ関数を使った処理を高速で行...
発言  カド  - 06/4/21(金) 22:25 -
引用なし
パスワード
   ▼neptune さん 回答ありがとうございます。
現在の私の知識では以下のような考えです。
これから頂いたアドバイスについて調べてみますが、すでに解決策が
お分かりの方がみえましたら、回答をお願いいたします。

****************************************************

検索先は以下のような感じです。

Ａ２？３＊
Ａ？１＊
Ｂ＊
・
・
・

１．こういうあいまいな検索はLikeを使うものだと教えて頂いたのですが、
Findでも出来るのでしょうか？

２．Indexの作成も残念ながら知識がなくピンと来ません。
専用関数などあるのでしょうか？

３．DAO,ADOも言葉は聞いたことがありますが、具体的には分かりません。
エクセルのＶＢＡだけで出来ることなのでしょうか？

４．この辺りはやれそうですが、そこまで凝る必要があるなら諦めます。
過去の経験上、ロジックに凝っても結局はエクセルに準備された専用メソッドに
負けてしまう気がします。（私見なので定かではないですが）
【37110】Re:Ｌｉｋｅ関数を使った処理を高速で行...
回答  neptune  - 06/4/21(金) 23:09 -
引用なし
パスワード
   ▼カド さん：
>これから頂いたアドバイスについて調べてみますが、すでに解決策が
>お分かりの方がみえましたら、回答をお願いいたします。
恐らくこの問題は既出の質問だと思います。
このサイト、若しくは他のExcelVBAの掲示板で検索するとヒットすると思います。

>検索先は以下のような感じです。
>
>Ａ２？３＊
>Ａ？１＊
>Ｂ＊
このままのデータですか？

>１．こういうあいまいな検索はLikeを使うものだと教えて頂いたのですが、
>Findでも出来るのでしょうか？
できます。これが一番簡単で、速いかもしれません。
Helpを見てください。確か、xlPartという引数を指定できる箇所が
あったと思います。

>２．Indexの作成も残念ながら知識がなくピンと来ません。
>専用関数などあるのでしょうか？
ありません。例えば１はどこにあるという情報を自分で作成するわけです。
例えばHelpも、このIndexを持っています。（HTMLHelp）
だから、全文検索でも非常に早く出来ます。

>３．DAO,ADOも言葉は聞いたことがありますが、具体的には分かりません。
>エクセルのＶＢＡだけで出来ることなのでしょうか？
できます。諸条件によりますが、Excelのフィルター機能と
同等の速さと考えてもいいと思います。フィルターと比べて安定した
処理速度を持ちます。
>４．この辺りはやれそうですが、そこまで凝る必要があるなら諦めます。
>過去の経験上、ロジックに凝っても結局はエクセルに準備された専用メソッドに
>負けてしまう気がします。（私見なので定かではないですが）
恐らくその通りと思います。しかし、現在よりかなり速くは
なると思います。
【37111】Re:Ｌｉｋｅ関数を使った処理を高速で行...
発言  カド  - 06/4/21(金) 23:53 -
引用なし
パスワード
   ▼neptune さん　回答ありがとうございます。

改めて言いますと、知りたいことはこれです。
下記のようにｎ列に品番が並んでいて、その品番のパターンに応じて
右の列に識別記号を書き込みたいのです。

頭が”Ａ”の場合は”あ”
頭が”Ａ”で次に”１”が来たら”い”
以下下記のとおりです。

このような場合、一般的には文字を一つづつ取り出して、ｉｆ文で判定する
しかないと思うのですが、なにか他に気の利いた方法はあるのでしょうか？

＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿
注：＊は任意の文字


ｎ列　　　　　　　　（ｎ＋１）列

Ａ＊＊＊＊　　　　　　　あ
Ａ１＊＊＊　　　　　　　い
Ａ１＊０＊　　　　　　　う
＊＊＊＊＊　　　　　　　え
　　・　　　　　　　　　・
　　・　　　　　　　　　・
　　・　　　　　　　　　・
http://www.vbalab.net/vbaqa/c-board.cgi?cmd=ntr;tree=37041;id=excel


>恐らくこの問題は既出の質問だと思います。

http://www.vbalab.net/vbaqa/c-board.cgi?cmd=ntr;tree=36857;id=excel


>Helpを見てください。確か、xlPartという引数を指定できる箇所が
>あったと思います。


http://www.vbalab.net/vbaqa/c-board.cgi?cmd=ntr;tree=36857;id=excel

多分これだと思うのですが、A?で検索すると、AAもAAAもヒットしてしまいます。
ヒットするのがAAだけにしたいのですが。

Indexについてはこんな感じだと思うのですが、残念ながら今は使いこなす
知識がありません。

http://www.vbalab.net/vbaqa/c-board.cgi?cmd=ntr;tree=36749;id=excel
【37112】Re:Ｌｉｋｅ関数を使った処理を高速で行...
発言  Ned  - 06/4/22(土) 1:24 -
引用なし
パスワード
   こんにちは。
n列のパターンは何パターンあるのですか？
それでどれくらい時間かかってます？

また、例題でいくと、順番は
A1?0?
A1???
A????
?????
のような気がしますが？
>条件がきつい順で比較
と Blueさんは書かれてますね。

何となく、Replaceメソッドが使えるような気がしてますが、それもパターン数しだいでしょうか。
もしかして、RegExp（正規表現）オブジェクトが使えるのかもしれませんが、ちょっと詳しくないです。^ ^;
【37114】Re:Ｌｉｋｅ関数を使った処理を高速で行...
発言  Ned  - 06/4/22(土) 2:47 -
引用なし
パスワード
   失礼。Replaceメソッドは使い物にならなかったですね^ ^;
なので現在のコードをそのまま配列処理に変更したらどうですか？多少は変わると思いますが。

Dim a As Single, b As Single
Dim ACELL, CCELL, AAA
Dim i3 As Long, i1 As Long
With ActiveCell.Offset(, -3)
　　ACELL = Range(.Cells, .End(xlDown).Offset(, 1)).Value
End With
With Range(ActiveCell, ActiveCell.End(xlDown))
　　CCELL = .Value
　　ReDim AAA(1 To UBound(CCELL), 1 To 1)
　　a = Timer
　　For i3 = 1 To UBound(CCELL)
　　　　For i1 = 1 To UBound(ACELL)
　　　　　　If CCELL(i3, 1) Like ACELL(i1, 1) Then
　　　　　　　　AAA(i3, 1) = ACELL(i1, 2)
　　　　　　　　Exit For
　　　　　　End If
　　　　Next i1
　　Next i3
　　With .Offset(, 1)
　　　　.ClearContents
　　　　.Value = AAA
　　End With
End With
b = Timer
Debug.Print b - a
【37115】Re:Ｌｉｋｅ関数を使った処理を高速で行...
発言  カド  - 06/4/22(土) 7:31 -
引用なし
パスワード
   ▼Ned さん：
>こんにちは。
>n列のパターンは何パターンあるのですか？
>それでどれくらい時間かかってます？
>
>また、例題でいくと、順番は
>A1?0?
>A1???
>A????
>?????
>のような気がしますが？
>>条件がきつい順で比較
>と Blueさんは書かれてますね。

失礼しました。
順番は手を抜いて適当に書いてます。
おっしゃるとおり条件の厳しい順位並べる必要がありますね。
【37116】Re:Ｌｉｋｅ関数を使った処理を高速で行...
お礼  カド  - 06/4/22(土) 7:36 -
引用なし
パスワード
   ▼Ned さん　ありがとうございます。
最近の一連の質問でみなさんに色々教えていただいたコードを
利用して、私としては配列にして処理したつもりだったのですが、
実際は配列になってなかったみたいですね。

検索先が２００個、検索元が３．５万個にて
私のコードで３．５分、Nedさんのコードで５秒でした。

これなら問題ありません。またまた、教えていただいたコードを
雛型に追加したいと思います。
【37123】Re:Ｌｉｋｅ関数を使った処理を高速で行...
発言  Ned  - 06/4/22(土) 16:09 -
引用なし
パスワード
   ▼カド さん：
こんにちは。
>私のコードで３．５分、Nedさんのコードで５秒でした。
そんなに変わりましたか。とりあえず良かったですね^ ^
再度チューニングすると

Dim a As Single, b As Single
Dim ACELL, CCELL, vc
Dim i3 As Long, i1 As Long
Dim r As Range
Dim AAA() As String '書き出し用配列をString型に。

a = Timer
Set r = ActiveCell
With r.Offset(, -3)
　　ACELL = Range(.Cells, .End(xlDown).Offset(, 1)).Value
End With
With Range(r, r.End(xlDown))
　　CCELL = .Value
　　ReDim AAA(1 To UBound(CCELL), 1 To 1)
　　For Each vc In CCELL
　　　　i3 = i3 + 1
　　　　For i1 = 1 To UBound(ACELL)
　　　　　　If vc Like ACELL(i1, 1) Then
　　　　　　　　AAA(i3, 1) = ACELL(i1, 2)
　　　　　　　　Exit For
　　　　　　End If
　　　　Next i1
　　Next vc
　　With .Offset(, 1)
　　　　.ClearContents
　　　　.Value = AAA
　　End With
End With
b = Timer
Set r = Nothing
Debug.Print b - a

な感じでしょうか。

先日から高速化についてのご質問が多いようなので、参考サイトをご紹介しておきます。
http://www.officetanaka.net/excel/vba/speed/index.htm
http://www.bcap.co.jp/hanafusa/VBHLP/sonota_no1.htm
定番化してますので、既知であればごめんなさい。
【37248】Re:Ｌｉｋｅ関数を使った処理を高速で行...
お礼  カド  - 06/4/26(水) 21:43 -
引用なし
パスワード
   ▼Ned さん　ありがとうございます。
確かにまた処理速度が上がりました。
高速化は知識としては知っていましたが、これまではそこまでの
必要性が無く、残念ながら使いこなすスキルは身に付いていません。
いづれにしても、今後はNedさんのコードをベースに改良して使っていくことに
なりそうです。
  新規投稿 ┃ツリー表示 ┃一覧表示 ┃トピック表示 ┃検索 ┃設定 ┃過去ログ ┃ホーム  
7723 / 13644 ﾂﾘｰ ←次へ　｜　前へ→
ページ：  ┃  記事番号：
2610219
(SS)C-BOARD v3.8 is Free