GEN MUTO'S HOMEPAGE  エクセル大事典  エクセルVBAを極める

エクセルExcel大事典 エクセル大事典TOPページへ
 ■ エクセルVBA中級編−VBAでいろんなテクニック
  様々な検索方法をマスターしよう!
 
■ 様々な検索方法をマスターしよう!

業務システムを開発する上で、必須機能となるのがデータの検索です。
データーベース利用を前提にするなら、SQL文で検索すれば事足りるのですが、ExcelVBAの場合、ワークシート上のデータから検索したいこともよくある話です。

今回は、代表的な検索方法から、ちょっと変ったものまでいくつかご紹介したいと思います。
開発内容に応じて、使い分けをしてもらえればと思います。

■Findメソッドによる検索

エクセル検索の代表的なものは、このFindメソッドによる検索でしょう。ワークシート上から欲しいデータをあっという間に検索します。
(例)
  Public Sub test()
    Debug.Print Columns("a").Find(What:="excel").Offset(0, 1)
  End Sub
この例では、アクティブシートのA列を検索し、"excel"の文字列が見つかったセルの右隣りのセルの値を出力します。

ただしこの例では、検索値が見つからなかった場合にエラーが発生します。検索値が見つからなかった場合、FindメソッドはNothingを返します。NothingはRangeオブジェクトではないため、Offsetプロパティで値を取得することができません。

検索値が見つからなくてもエラーが発生しないようにするには、
(例)
  Public Sub test()
    Dim MyRange As Range
    Set MyRange = Columns("a").Find(What:="excel")
    If MyRange Is Nothing Then
      Debug.Print "Not Found"
    Else
      Debug.Print MyRange.Offset(0, 1)
    End If
  End Sub
このようにします。
Findメソッドの返り値をオブジェクト変数にセットし、セットしたオブジェクト変数がNothingかどうかを判定させ、処理を分岐させるようにします。

Findメソッドには、たくさんの引数を指定できます。指定可能な引数は以下の通りです。
引数 定数 内容 省略
What   検索するデータを指定します 不可
After   検索を開始するセルを指定します
省略すると検索範囲の左上端から検索します
LookIn    
  xlFormulas 数式を検索対象に指定します  
xlValues 値を検索対象に指定します  
xlComents コメント文を検索対象に指定します  
LookAt     
  xlPart 一部が一致するセルを検索します  
xlWhole 全てが一致するセルを検索します  
SearchOrder    
  xlByRows 列方向に検索します  
xlByColumns 行方向に検索します  
SearchDirection    
  xlNext 順方向に検索します(規定値)   
xlPrevious 逆方向に検索します  
MatchCase    
  True 大文字と小文字を区別します  
False 大文字と小文字を区別しません(規定値)  
MatchByte    
  True  半角と全角を区別します  
False 半角と全角を区別しない(規定値)   
SearchFormat    
  True 検索に書式を含めます  
False 検索に書式を含めません  

LookIn、LookAt、SearchOrder、MatchByteの引数を省略した場合、前回検索したときのプロパティが使用されます。
SearchFormat引数はExcel2002以降で使用可能です。

■Vlookup関数による検索

ワークシート関数のVlookup関数ですが、当然VBAからでも利用できます。
(例)
  Public Sub test()
    Debug.Print WorksheetFunction.VLookup("excel", Range("A:B"), 2, False)
  End Sub

この例では、アクティブシートのA列を検索し、"excel"の文字列が見つかったセルの右隣りのセルの値を出力します。
またこの例では、検索値が見つからなかった場合に実行時エラーが発生します。

検索値が見つからなくても実行時エラーが発生しないようにするには、
(例)
  Public Sub test()
    Dim MyVariant As Variant
    MyVariant = Application.VLookup("excel", Range("A:B"), 2, False)
    If IsError(MyVariant) Then
      Debug.Print "Not Found"
    Else
      Debug.Print MyVariant
    End If
  End Sub
このようにします。
Application.WorksheetFunction.VLookupをApplication.VLookupにすることで、エラー値を取得することが可能になります。(WorksheetFunctionプロパティを使用する場合はエラートラップが必要になります)

■セル範囲を配列に取得して検索します

セル範囲をいったん配列に格納してから検索させてみましょう。
(例)
  Public Sub test()
    Dim MyVariant As Variant
    Dim i As Long
    MyVariant = Range("A:B")
    For i = 1 To UBound(MyVariant)
      If MyVariant(i, 1) = "excel" Then
        Debug.Print MyVariant(i, 2)
        Exit Sub
      End If
    Next i
    Debug.Print "Not Found"
  End Sub
この例では、アクティブシートのA列を検索し、"excel"の文字列が見つかったセルの右隣りのセルの値を出力します。
またこの例では、検索値が見つからなかった場合"Not Found"を出力します。

■ADOレコードセットを取得して検索します
ADOのレコードセットオブジェクトには、Findメソッドが用意されています。
ワークシートをレコードセットに格納したのち、Findメソッドで検索させてみます。
※ 参照設定で「Microsoft ActiveX Data ObjectX.X Library」にチェックを入れる必要があります。(X.Xはバージョン名) 
(例)
  Public Sub test()
    Dim CN As ADODB.Connection
    Dim RS As ADODB.Recordset
    Dim SQL As String
    Set CN = New ADODB.Connection
      CN.Provider = "Microsoft.Jet.OLEDB.4.0"
      CN.Properties("Extended Properties") = "Excel 8.0"
      CN.Open ThisWorkbook.FullName
    SQL = "SELECT * FROM [" & ActiveSheet.Name & "$]"
    Set RS = New ADODB.Recordset
      RS.Open SQL, CN, adOpenStatic, adLockReadOnly
      RS.Find RS.Fields(0) & "='excel'"
    If RS.EOF Then
      Debug.Print "Not Found"
    Else
      Debug.Print RS.Fields(1)
    End If
  End Sub
この例では、アクティブシートのA列を検索し、"excel"の文字列が見つかったセルの右隣りのセルの値を出力します。
またこの例では、検索値が見つからなかった場合"Not Found"を出力します。
速度は遅いですがこの方法の場合、閉じているブックに対して検索がかけられるというメリットがあります。
※ その場合、ThisWorkbook.FullNameの部分を対象のブックのフルパスに、ActiveSheet.Nameの部分を検索対象のシート名に変更する必要があります。

フィールド名がある(シートの1行目に項目名がある)場合、Select文のWhere句を用いてデータを検索することも可能です。
(例)
  Public Sub test()
    Dim CN As ADODB.Connection
    Dim RS As ADODB.Recordset
    Dim SQL As String
    Set CN = New ADODB.Connection
      CN.Provider = "Microsoft.Jet.OLEDB.4.0"
      CN.Properties("Extended Properties") = "Excel 8.0"
      CN.Open ThisWorkbook.Path & "\test.xls"
    SQL = "SELECT * FROM [Sheet1$] WHERE DATA='excel'"
    Set RS = New ADODB.Recordset
      RS.Open SQL, CN, adOpenStatic, adLockReadOnly
    If RS.EOF Then
      Debug.Print "Not Found"
    Else
      Debug.Print RS.Fields("NO")
    End If
  End Sub
この例では、マクロを実行するブックと同じフォルダにあるtest.xlsブックのワークシート"Sheet1"から、項目名"DATA"の列を検索し、"excel"の文字列が見つかったとき、同じ行の"NO"の列の値を出力します。
また、この場合もtest.xlsブックが開いている必要はありません。

それではせっかくですので、最後に各方法による検索速度を比較してみましょう。
速度比較は、ワークシート上にある5万件のデータから検索する処理を10回繰り返した時間で比較しています。



検索方法(単位ミリ秒) 計測1回目 計測2回目 計測3回目
Findメソッドによる検索 125 125 140
Vlookup関数による検索 31 47 47
配列を使用した検索 203 219 218
ADOのFindメソッドによる検索 2562 2532 2532
SQLのWHERE句による検索 532 541 547
※ADOによる検索は両方とも、閉じたブックに対して行った結果です。

こうして比較してみると、Vlookup関数とFindメソッドを利用した検索の速さが際立ちます。並び替え(Sort)のときも触れましたが、Excel自体が持つ検索機能がいかに優れているかがよくわかる結果となりました。

ADOによる検索も速度は遅いものの閉じたブックに対して有効であるなど、それぞれに長所があります。
開発するシステムに応じて、適材適所、使い分けてください。

>> 次の話にすすむ


サイト内検索ができます

Microsoft Most Valuable Professional

Microsoft MVP Excel
武藤 玄 プロフィール
 

本を執筆しました
ストーリーで学ぶ Excel VBAと
業務改善のポイントがわかる本

Webでは書ききれなかった技術的
解説や描き下ろしイラスト満載
初版限定公式模擬問プレゼント
 

VBAエキスパート公式サイトにて、
連載コラムを執筆しています
毎月10日更新、「やってみよう!
Excel/Access VBAで業務改善

 

本を執筆しました
VBAエキスパート公式テキスト
Access VBA ベーシック
Access VBA スタンダード
VBAエキスパート新試験の
合格を目指す方は必読です
 

本を執筆しました
Excel VBA ゲーム作成入門
Excel VBAの究極テクニックを
惜しげもなく大公開!


TOPページ


■ エクセルVBA超入門

 ・9ステップで"Hello World!"
  からオブジェクト指向まで!
  1."Hello World!"を表示しよう
  2.基本はデータの入出力
  3.繰り返し処理
  4.シートで簡易データベース
  5.名前をつけよう
  6.構造化プログラムに挑戦
  7.構造体を使ってみよう
  8.フォームを使ってみよう
  9.オブジェクト指向に挑戦

■ エクセルVBA中級編

 ・VBAでいろんなテクニック
  変数・関数のスコープ
  引数の参照方法を明示
  配列の中身を一気に複写
  識別子がよいコードを作る
  データ定義型をもっと知ろう
  定数と列挙型を活用しよう
  エラー処理の重要性1
  エラー処理の重要性2
  いろいろな条件分岐
  いろいろな繰り返し処理
  演算子について知ろう
  文字列操作 連結 変換 置換
  文字列操作 検索 比較 書式
  日付操作関数を知ろう
  数値操作・評価・その他関数
  並び替えソートをマスター
  いろいろな検索をマスター
  VBEditorを使いこなそう
  マクロの記録を活用しよう1
  マクロの記録を活用しよう2
  VBAからVBEを操作しよう1
  VBAからVBEを操作しよう2
  VBAからVBEを操作しよう3
 ・ワークシートを使いこなす
  セルの参照をマスターしよう
  セルの選択をマスターしよう
  セルの操作をマスターしよう
  セルの書式設定をマスターしよう
  行・列の操作をマスターしよう
  シートの参照をマスターしよう
  シートの操作をマスターしよう
  ブックの操作をマスターしよう
  ウィンドウの操作をマスターしよう
  イベントプロシージャを活用しよう
  ワークシートを印刷しよう1
  ワークシートを印刷しよう2
  音声(読み上げ)機能を操作しよう
  グラフを操作しよう
 ・ユーザーフォームを使いこなす
  ユーザーフォームを使おう
  UserFormsオブジェクト
  Labelコントロール
  TextBoxコントロール
  ComboBoxコントロール
  ListBoxコントロール
  CheckBoxコントロール
  OptionButtonコントロール
  ToggleButtonコントロール
  CommandButtonコントロール
  ScrollBarコントロール
  SpinButtonコントロール
 ・VBA関数の一覧
  Aから始まる関数の一覧
  Cから始まる関数の一覧
  Dから始まる関数の一覧
  Eから始まる関数の一覧
  Fから始まる関数の一覧
  Gから始まる関数の一覧
 
■ エクセルVBAを極める
 VBAスーパーテクニック
 
 ・データベースへ接続してみよう!
  CSVファイルへの接続
  ワークシートへの接続
  mdbファイルへの接続
  SQL Serverへの接続
  ADOレコードセットの操作1
  ADOレコードセットの操作2
 ・ファイルを操作してみよう!
  FileSystemObjectの操作
  Drive File Folderの操作
  TextStream ダイアログ
  ステートメントでファイル操作1
  ステートメントでファイル操作2
 ・他アプリと連携してみよう!
  ActiveXオートメーションの操作
 ・ゲーム技でマクロを超える
  エクセルで音を鳴らす1
  エクセルで音を鳴らす2  
  スクリーン座標を取得
  様々なイベント取得
  リボンUIを制御しよう1
  リボンUIを制御しよう2
  シート上でアニメを動かそう
 
■ 一歩上行くエクセル
 基本操作の完全マスター!

 
 ・あなたはいったい
  エクセルで何をしたいのか?
  エクセルで業務効率化
  エクセル関数を使いこなす
  エクセルの便利技
  エクセルは最高の帳票ツール
 ・エクセル基本操作上級編!
  セル選択、コメント、シリアル値
  オート機能を使いこなそう!
  参照、条件付書式、入力規則
  関数をもっと理解しよう!
  配列数式を有効利用しよう!
  データの正規化をしよう!
  ショートカットキーを利用しよう1
  ショートカットキーを利用しよう2
 ・世間の誤ったエクセルの
  べからず集
  エクセルで文章を書くべからず
  エクセルで図形を描くべからず
  エクセルでシステムをつくるな
 
■ エクセル関連その他

 ・エクセルコミニュティにあなたも
  参加しませんか?
  Excel関連リンク・コミニュティ
 ・VBの理解がVBAをマスターする
  最短距離!
  VB関連の老舗・大家サイト
 ・エクセルExcel大事典掲示板
 ・プライバシーポリシー

2000アイテムを超えるラベルを販売
送料無料、即納対応の優良ショップ
今買っているラベルより確実に安い!
OAラベルの販売専科グラフトラベル




エクセルExcel大事典ははリンクフリーです エクセル大事典Excel大事典
お問い合わせはこちら gengengen@yahoo.co.jp

Copyright© Gen Muto 武藤 玄 All Rights Reserved,