​ExcelVBA小技教室

第四回 データを複合条件で抽出する

今回は、ひとつのユーザーフォーム上で複数の条件で「絞り込み検索」を行います。もうここまで来ればExcelがAccessと何ら変わらないデータベース機能を持っていると実感できるはずです。なお3.Userform1のプロシージャで、ComboBox1から3の値は変数にすることも可能です。ってゆか、ふつうはそうするかもしれませんね( ^ ^ ゞ。
そんなわけでサンプルファイルでは変数を用いてます。でも変数って使い過ぎると訳が分からなくなりますよね。何でもかんでも変数にするのも感心しないし、
特にσ(^_^・・・アルファベットの変数って嫌いだなぁ(笑)。

「絞り込み抽出」ボタンを押すと「絞り込み抽出」フォームが表示されます。

「男」「東京都」「会社員」の条件で「検索」ボタンを押してみます。

​データが抽出され、フォームには件数が表示されます。

1.ワークブックのプロシージャ

------------------------------------------------------------------------------------
Private Sub Workbook_Open()’ブックを開いた時のイベントプロシージャ。

   ActiveSheet.Unprotect’顧客名簿シートの保護を解除。
   Worksheets("顧客名簿").Range("R1").Formula = "=SUBTOTAL(3,A3:A65536)"’顧客名簿シートのR1セルに、「SUBTOTAL関数」を
   ’引数に「3」を選んで記入(抽出後の件数が表示される)。
   ActiveSheet.Protect’顧客名簿シートを保護。 

End Sub

------------------------------------------------------------------------------------

2.シートのイベントプロシージャ

------------------------------------------------------------------------------------
Sub ボタン3_Click()’ボタン3をクリックしたときのイベントプロシージャ。

   Unload UserForm1’UserForm1を初期化します。
   UserForm1.Show vbModeless’シート変更可能な状態でUserForm1を表示。

End Sub

------------------------------------------------------------------------------------

3.Userform1のプロシージャ

------------------------------------------------------------------------------------
Private Sub 検索_Click()’「検索」ボタンをクリックしたときのイベントプロシージャ。

   If ComboBox1.Value <> "" And ComboBox2.Value <> "" And ComboBox3.Value <> "" Then

’ComboBox1,2,3共空白でない場合。
      ActiveSheet.Unprotect’シートの保護を解除。
      ActiveSheet.Range("$A$2:$E$65536").AutoFilter Field:=3, Criteria1:=ComboBox1.Text

’ComboBox1のtext条件でField3を抽出。
      ActiveSheet.Range("$A$2:$E$65536").AutoFilter Field:=4, Criteria1:=ComboBox2.Text

’ComboBox2のtext条件でField4を抽出。
      ActiveSheet.Range("$A$2:$E$65536").AutoFilter Field:=5, Criteria1:=ComboBox3.Text

’ComboBox3のtext条件でField5を抽出。
      Label4.Caption = Range("R1").Value & "件"’Label4.CaptionにR1セルの値と文字列”件”を代入。
      ActiveSheet.Protect’シートを保護。

   ElseIf ComboBox1.Value <> "" And ComboBox2.Value <> "" And ComboBox3.Value = "" Then

’ComboBox1,2が空白でなく3が空白の場合。
      ActiveSheet.Unprotect’シートの保護を解除。
      ActiveSheet.Range("$A$2:$E$65536").AutoFilter Field:=3, Criteria1:=ComboBox1.Text

’ComboBox1のtext条件でField3を抽出。
      ActiveSheet.Range("$A$2:$E$65536").AutoFilter Field:=4, Criteria1:=ComboBox2.Text

’ComboBox2のtext条件でField4を抽出。
      Label4.Caption = Range("R1").Value & "件"’Label4.CaptionにR1セルの値と文字列”件”を代入。
      ActiveSheet.Protect’シートを保護。

   ElseIf ComboBox1.Value <> "" And ComboBox2.Value = "" And ComboBox3.Value = "" Then

’ComboBox1が空白でなく2,3が空白の場合。
      ActiveSheet.Unprotect’シートの保護を解除。
      ActiveSheet.Range("$A$2:$E$65536").AutoFilter Field:=3, Criteria1:=ComboBox1.Text

’ComboBox1のtext条件でField3を抽出。
      Label4.Caption = Range("R1").Value & "件"’Label4.CaptionにR1セルの値と文字列”件”を代入。
      ActiveSheet.Protect’シートを保護。

   ElseIf ComboBox1.Value = "" And ComboBox2.Value = "" And ComboBox3.Value = "" Then’ComboBox1,2,3が空白の場合。
      Label4.Caption = "条件が選択されていません。" ’Label4.Captionにメッセージを表示。
      ComboBox1.SetFocus’ComboBox1にカーソルを跳ばす。

   ElseIf ComboBox1.Value = "" And ComboBox2.Value <> "" And ComboBox3.Value <> "" Then
      ActiveSheet.Unprotect’シートの保護を解除。
      ActiveSheet.Range("$A$2:$E$65536").AutoFilter Field:=4, Criteria1:=ComboBox2.Text

’ComboBox2のtext条件でField4を抽出。
      ActiveSheet.Range("$A$2:$E$65536").AutoFilter Field:=5, Criteria1:=ComboBox3.Text

’ComboBox3のtext条件でField5を抽出。
      ActiveSheet.Protect’シートを保護。

   ElseIf ComboBox1.Value = "" And ComboBox2.Value = "" And ComboBox3.Value <> "" Then

’ComboBox1,2が空白で3が空白でない場合。
      ActiveSheet.Unprotect’シートの保護を解除。
      ActiveSheet.Range("$A$2:$E$65536").AutoFilter Field:=5, Criteria1:=ComboBox3.Text

’ComboBox3のtext条件でField5を抽出。
      Label4.Caption = Range("R1").Value & "件"’Label4.CaptionにR1セルの値と文字列”件”を代入。
      ActiveSheet.Protect’シートを保護。

   ElseIf ComboBox1.Value <> "" And ComboBox2.Value = "" And ComboBox3.Value <> "" Then

’ComboBox1,3が空白でなく2が空白の場合。
      ActiveSheet.Unprotect’シートの保護を解除。
      ActiveSheet.Range("$A$2:$E$65536").AutoFilter Field:=3, Criteria1:=ComboBox1.Text

’ComboBox1のtext条件でField3を抽出。
      ActiveSheet.Range("$A$2:$E$65536").AutoFilter Field:=5, Criteria1:=ComboBox3.Text

’ComboBox3のtext条件でField5を抽出。
      Label4.Caption = Range("R1").Value & "件"’Label4.CaptionにR1セルの値と文字列”件”を代入。
      ActiveSheet.Protect’シートを保護。

   End If

End Sub
------------------------------------------------------------------------------------
Private Sub 閉じる_Click()

   ActiveSheet.Unprotect’シートの保護を解除。
   ActiveSheet.Range("A1").Select’A1セルを選択。
   Selection.AutoFilter’フィルター状態を解除。
   ActiveSheet.Protect’シートを保護。
   Unload UserForm1’UserForm1を初期化。
   UserForm1.Hide’UserForm1を非表示に。

End Sub

------------------------------------------------------------------------------------

 

サンプルファイルはこちらからダウンロードすることが出来ます。

∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞

人事システム、不動産システムなら

データベースシステム開発  エクセル工房Inage