VBAを使用せず、Excel関数だけでサジェストを実装する

Excelにはデータの入力規則を使用して、リストの中から値を選択させる機能があります。

この機能を使うことで打ち間違いを防止できるので非常に便利なのですが、大量のデータをリストの表示すると選ぶのが非常に面倒くさいです。

(大量のデータをリストにするくらいなら、自由入力にして想定外の入力値だったら条件付き書式で赤くするとかのほうがマシですね。)

そこでGoogleのサジェストみたいな感じで、入力された値に応じてリストの値が変わるようにすれば使いやすいのではないかと思いました。

早速作成をしてみたので実装内容について記載をしてみたいと思います。

VBAを使わずに関数だけで実装

ネット調べるとVBAでのサジェスト実装例は多く見つかりました。同じようなことを考えている人は多いようですね。

そこで僕はVBAを使用せずにExcel関数だけで実装する方法を検討してみました。

VBAを使うと、アンドゥ(CTRL+Z)が使えなくなるなどの弊害があるのでExcel関数のみで実行できるメリットは非常に大きいと思います。

Filter関数を使用しているので古いExcelでは使えないですが、一応以下の方法で実現をすることが出来ました。

前提として、下記のC列に記載されている値を選択肢としたサジェストのリストをA1セルに作成します。

まずC列の隣(B1セル)に以下の関数を設定します。

=UNIQUE(FILTER(C$1:$C$5,IFERROR(FIND(A1,C$1:$C$5)>0,0)))

↓結果

この式は、「C列の選択肢のうちA1セルに入力された値を含む文字列だけを返却する」という動きになります。

試しにA1に「ん」と入力すると

B列の値から「ん」が含まれない「ぶどう」と「いちご」の値が削除されました。

なのでB列の値をデータの入力規則に設定すれば、A1セルの値を含む文字列だけが表示されるリストになるので、サジェストっぽいリストの動きが実現できます。

A1セルのデータの入力規則に以下を設定します。

=OFFSET($B$1,,,COUNTA($B:$B))

A1に「ん」が入力された場合に、「ん」を含む文字列だけがリストの選択肢になります。

大量に使うならVBAで実装するしか無い

今回実装した方式ですと、1度Filter関数を使用した列を作成しないといけないのでサジェストの対象になるセルが大量にあった場合は使いにくいです。

その場合は、下記の記事で紹介しているドロップダウンリストを連動させる方法などで絞り込ませるのも良いかと思います。

データの入力規則に直接Filter関数を使った式を書けばいいだろうと思ったのですが、エラーとなってしまいました。

元の値はエラーと判断されます。続けますか?

おそらくですが、UniqueやFilterは最近追加された関数なのでデータの入力規則では使えるようになっていないのだと思います。


VBAでサジェストを作成する

せっかくなので、VBAを使用したサジェスト機能も実装してみました。

ただ、前述したとおりVBAが動くとアンドゥ(CTRL+Z)ができなくなるので使い勝手は激悪です。

まずは下記の関数を作成しました。選択範囲をループさせてワイルドカードで一致する場合はリストの選択肢に追加します。

Sub test(Target As Range)
    '選択肢の範囲を指定する
    Dim list_src As Range
    Set list_src = ThisWorkbook.Worksheets("Sheet2").Range("C1:C5")

    Dim fomula As String
    fomula = ""
    
    '選択肢の範囲ループさせる
    For Each c In list_src
        'セルの値が含まれた時に選択肢に追加
        If c.Value Like "*" & Target.Value & "*" Then
            If fomula = "" Then
                fomula = c.Value
            Else
                fomula = fomula & "," & c.Value
            End If
        End If
    Next c
    
    'リストを動的に設定する
    With Target.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:=fomula
        .ShowError = False
    End With
    
End Sub

次にワークシートのサジェストの対象セルが変更された場合に、上記の関数を呼び出すように設定します。

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If Target.Column = 1 And Target.Row = 1 Then
        Call test(Target)
    End If
End Sub

上記コードでセルの1,1(A1)が変更された際に、test関数が実行されA1の値に応じてリストの値が変更となります。

↑元の値が自動的に入力されている


コメントを残す

メールアドレスが公開されることはありません。

日本語が含まれない投稿は無視されますのでご注意ください。(スパム対策)