Excelで大量プルダウンリストを階層的に絞り込む方法とVBA活用例

Excelで30行程度のプルダウンリストがあり、営業部・開発部など大まかに分類して複数段階で絞り込みたい場合、標準機能だけでは操作が煩雑になることがあります。この記事では、フォームリストやINDIRECT関数だけでは対応が難しいケースで、VBAを活用して複数行のプルダウンリストを効率的に絞り込む方法をご紹介します。

標準機能での限界
VBAを使った動的プルダウン
サンプルコード
使用方法
まとめ

標準機能での限界

通常、Excelのデータ検証で作成するプルダウンリストは、1つのセルに対して値を返す設計です。INDIRECT関数を組み合わせて階層化する方法もありますが、複数行で同じ操作を行う場合は設定が複雑になり、手間が増えます。

VBAを使った動的プルダウン

VBAを活用すると、選択肢の内容を動的に変更し、複数行で適用することが可能です。例えば、部署を選択するとその部署に属する取引先リストだけが表示されるようにできます。

サンプルコード

以下は簡単な例です。A列が部署、B列が取引先の入力セルだとします。

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Dept As String
  If Not Intersect(Target, Range("A2:A31")) Is Nothing Then
    Dept = Target.Value
    With Target.Offset(0, 1).Validation
      .Delete
      .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
      xlBetween, Formula1:=GetClientList(Dept)
      .IgnoreBlank = True
      .InCellDropdown = True
    End With
  End If
End Sub

Function GetClientList(Dept As String) As String
  Select Case Dept
    Case "営業部": GetClientList = "取引先A,取引先B,取引先C"
    Case "開発部": GetClientList = "取引先X,取引先Y,取引先Z"
    Case Else: GetClientList = ""
  End Select
End Function