大体でIT

-ちょっと使えるネタを紹介-

大体でIT

Excel VBAを使って、条件を絞り込んで値を抽出するには、部分一致で値を抽出して、ForとIfを使って値を検索するとできます。大量データから一意の値で検索するとなると大変です。なので、条件を絞りこんで値を検索できるとかなり便利です。条件を絞りこんで値を検索する方法について、マスターしていきましょう。

はじめに

この記事では、条件を絞りこんで値を検索する方法について、ご紹介します。

手順としては、次のようになります。

  • 部分一致で値を抽出
  • 抽出した値から検索値を入力
  • データベースの値を取得

大量のデータベースから値を検索する場合、一意の値を使って検索するとなると大変です。

なので、条件を絞りこんで検索できるようになると、かなり便利です。

データベースから値を絞り込んで検索する方法について、マスターしていきましょう。

では、条件を絞りこんで値を検索する方法について、解説していきます。

この記事を読むメリット

  • 条件を絞りこんで値を検索することができるようになります。

本記事の内容を動画にて解説しております。

本記事の内容を動画にて解説しております。

VBAコードの作成手順について、動画で確認できます。

目次から見たい項目へ移動すると便利ですよ。

目次

条件を絞り込んで検索したい

条件を絞り込んで検索してみます。

やりたい内容

やりたい内容は、条件を絞りこんで検索したいということになります。

条件を絞りこんで検索したい

条件を絞り込んで検索したいです

検索するデータベースは、こんな感じになっています。

データベースの値

データベースから値を検索します

では、条件を絞り込んで、検索してみます。

まずは、絞りこむ条件を入力して、値を抽出します。

条件を絞りこむ

絞りこむ値を入力します。

絞りこむ値を入力します

データベースから値を抽出できます。

データベースから値を抽出できます

データベースから値を抽出できました。

次に、絞り込んだ値から検索値を入力します。

絞りこんだ値から選択する

抽出した値からプルダウンリストで選択できます。

抽出した値からプルダウンリストで選択できます

検索する値を選択してみます。

検索する値を選択してみます

これで、データベースから値を検索できます。

データベースから値を検索できる

データベースから値を検索できます

データベースから値を検索できました。

こちらのVBAコードを作成する手順について、解説していきます。

VBAコード作成

条件を絞りこんで値を検索するVBAコードを、ポイントごとに作成していきます。

フォームを作成

まずは、検索用のフォームを作成します。

検索用フォームを作成

検索用のフォームを作成します

こちらの検索用フォームを使って、条件を絞り込んで検索していきます。

部分一致で値を抽出

部分一致で値を抽出するVBAコードです。

Sub TEST1()
  
  '抽出リストをクリア
  Sheets("検索").Range("F2:F1000").Clear
  
  j = 1
  For i = 2 To Sheets("DB").Cells(Rows.Count, "A").End(xlUp).Row
    '部分一致で検索
    If InStr(Sheets("DB").Cells(i, "A"), Sheets("検索").Range("B3")) > 0 Then
      j = j + 1
      '「商品」を抽出
      Sheets("検索").Cells(j, "F") = Sheets("DB").Cells(i, "A")
    End If
  Next
  
End Sub

絞りこむ条件を入力しておきます。

絞りこむ値を入力する

絞りこむ条件を入力します

実行すると、データベースから部分一致で値を抽出できます。

部分一致で値を検索できる

データベースから部分一致で値を抽出できます

データベースから部分一致で値を抽出できました。

プルダウンリストを設定

抽出したリストを、プルダウンリストから選択できるように設定していきます。

入力規則のリストに、OFFSET関数とCOUNTA関数を入力することでできます。

入力規則を設定

入力規則を設定します。

入力規則を設定します

リストを設定して、OFFSETとCOUNTA関数の数式を入力します。

「=OFFSET($F$2,,,COUNTA($F:$F)-1)」というように入力をします。

リストを設定して、OFFSETとCOUNTA関数の数式を入力します

OFFSET関数は、「OFFSET(基準セル,行の移動,列の移動,行数,列数)」というように入力をします。

COUNTA関数は、指定したセル範囲に入力されている数をカウントできます。

これで、抽出した値から、プルダウンリストで選択できるようになります。

抽出した値から選択できる

抽出した値から、プルダウンリストで選択できるようになります

抽出した値から、プルダウンリストで選択できるようになりました。

Changeイベントを作成

Changeイベントを作成して、抽出する値を入力したタイミングで、絞りこみできるようにします。

次のVBAコードを、「検索」シートの「シートモジュール」に記載をします。

Private Sub Worksheet_Change(ByVal Target As Range)
  
  If Target.Address(False, False) = "B3" Then
    Call TEST1 '部分一致で、「商品」を抽出
  End If
  
End Sub

絞りこむ値を入力してみます。

絞りこむ値を入力

絞りこむ値を入力します

入力したタイミングで、データベースから値を部分一致することができます。

入力したタイミングで値を抽出できる

入力したタイミングで、データベースから値を部分一致することができます

入力したタイミングで、データベースから値を部分一致することができました。

値を検索する

選択した検索値を、データベースから検索するVBAコードです。

Sub TEST2()
  
  For i = 2 To Sheets("DB").Cells(Rows.Count, "A").End(xlUp).Row
    '検索する「商品」と一致するかを確認
    If Sheets("DB").Cells(i, "A") = Sheets("検索").Range("D3") Then
      Sheets("検索").Range("B5") = Sheets("DB").Cells(i, "B") '価格
      Sheets("検索").Range("B6") = Sheets("DB").Cells(i, "C") '残り数量
      Sheets("検索").Range("B7") = Sheets("DB").Cells(i, "D") '必要数量
      Sheets("検索").Range("A9") = Sheets("DB").Cells(i, "E") '備考
    End If
  Next
  
End Sub

検索する値をプルダウンリストから選択しておきます。

検索値を選択

検索する値をプルダウンリストから選択します

実行すると、データベースから値を検索できます。

値を検索できる

データベースから値を検索できます

データベースから値を検索できました。

Changeイベントを作成

Changeイベントを作成して、検索値を選択したタイミングで、値を検索できるようにします。

先ほどと同じように、「検索」シートの「シートモジュール」に記載をします。

Private Sub Worksheet_Change(ByVal Target As Range)
  
  If Target.Address(False, False) = "B3" Then
    Call TEST1 '部分一致で、「商品」を抽出
  End If
  
  If Target.Address(False, False) = "D3" Then
    Call TEST2 '「商品」を検索
  End If
  
End Sub

プルダウンリストから検索する値を選択します。

検索値を入力

プルダウンリストから検索する値を選択します

選択したタイミングで、値を検索することができます。

選択したタイミングで値を検索できる

選択したタイミングで、値を検索することができます

選択したタイミングで、値を検索することができました。

というような感じで、条件を絞りこんで値を検索することができます。

おわりに

この記事では、条件を絞りこんで値を検索する方法について、ご紹介しました。

手順としては、次のようになります。

  • 部分一致で値を抽出
  • 抽出した値から検索値を入力
  • データベースの値を取得

大量のデータベースから値を検索する場合、一意の値を使って検索するとなると大変です。

なので、条件を絞りこんで検索できるようになると、かなり便利です。

データベースから値を絞り込んで検索する方法について、マスターしていきましょう。

参考になればと思います。最後までご覧くださいまして、ありがとうございました。

関連する記事から探す

カテゴリから探す

カテゴリから見たい項目を探すと便利ですよ。

サイト内を検索する

↓キーワードを入力する

アーカイブから探す