Google 検索の「サジェスト機能」を， Excel のシート上にVBAで作る　（セルの内容に応じて入力規則を動的に変える方法）

バッチ・マクロのTOPへ

「山」と入力したとき，「山」で始まる入力候補だけをドロップダウンで表示させたい

「入力規則」の使いづらさ

マクロで作ってみる

（１）辞書となるシートを用意

（２）入力規則を動的に切り替えるコード

（３）シートを開いた時に処理を自動的に始めるコード

（４）使い方

補足

補足２

「山」と入力したとき，「山」で始まる入力候補だけをドロップダウンで表示させたい

Googleの検索窓には，検索候補のサジェスト機能がある。

１文字「山」と入力したら，「山田」「山川」などの候補をドロップダウンで表示。

同じく「石」と入力したら，「石田」「石川」などの候補をドロップダウンで表示。

最初の一文字だけ入力すればよい。　楽だし，ミスタイプもない。

これと同じことを，Excelでも実現してみる。

↑上：セルの入力内容にサジェストするようなドロップダウンを表示

（「石」で始まる関係ない候補は表示しない）

下：入力候補のリスト

「入力規則」の使いづらさ

一応，Excelの「入力規則」の機能を使えば，セルに特定のデータしか入力できないよう制限できる。

例えば Excel2007 なら，

    [データ]タブ→データの入力規則→入力値の種類→リスト

で「石川,山田」と設定しておけば，「石川」と「山田」のどちらかしか入力できないようになる。

この際，セル入力時にはドロップダウンが現れ，「石川」「山田」のどちらかを選択できる。

でも，候補の数が増えてくると，これはドロップダウンが巨大化して使いにくい。

検索のヒントを与えるサジェスチョン機能が無いのだ。

Excel/VBAクリニック，セルのイベント・ドリブンで自動入力
http://itpro.nikkeibp.co.jp/article/C...
どれでも選択できるのがリストの利点かつ欠点

マクロで作ってみる

下記では，Excel シート上でのサジェスト機能をマクロによって実現してみる。

（１）辞書となるシートを用意

まず，冒頭の画像にあるように，特定のシートに「入力候補」を列挙しておく。

ここではシート名を「名前リスト」とする。

このシートがデータベースの役割をする。

（２）入力規則を動的に切り替えるコード

シート編集時に入力候補を表示させたいセルは，シートの１列目であるとする。

Excelのマクロメニューから Visual Basic Editor を起動し，Module1 に下記のコードを追加。

' セルの入力候補を設定
Sub make_dropdown()
    
    ' アクティブセルの値が辞書に載っているか検索
    Dim listSheet As String
    listSheet = "名前リスト" ' 検索対象シート
    Dim strDictionary As String
    strDictionary = "B3:B7" ' 検索対象範囲
    Dim FoundCell As Variant
    matchKey = ActiveCell.Value
    Set FoundCell = Worksheets(listSheet).Range(strDictionary).Find(matchKey)
    
    ' 検索結果が空の場合終了
    If FoundCell Is Nothing Then Exit Sub
    
    ' 検索結果を回す
    Dim strFormula As String ' 入力規則
    strFormula = ""
    Dim firstAddress As String ' 最初の結果のアドレス
    firstAddress = FoundCell.Address
    Do
        ' 辞書から入力候補を収集
        lngY = FoundCell.Cells.Row
        intX = FoundCell.Cells.Column
        matchWord = Worksheets(listSheet).Cells(lngY, intX).Value
        
        ' 登録条件：
        ' 語頭にマッチしているか
        ' VB(A)にはWhile文のContinueが無い
        If InStr(matchWord, matchKey) = 1 Then
            strFormula = strFormula & matchWord & ","
        End If
        
        ' 次の入力候補へ
        Set FoundCell = Worksheets(listSheet).Range(strDictionary).FindNext(FoundCell)
    Loop While (Not FoundCell Is Nothing) And (firstAddress <> FoundCell.Address)
    
    ' 入力候補をセット
    If Len(strFormula) > 0 Then
        ActiveCell.Validation.Delete
        ActiveCell.Validation.Add Type:=xlValidateList, Formula1:=strFormula
        ActiveCell.Validation.ShowError = False
    End If

End Sub

「検索対象範囲」の部分は，状況に合わせて変えておくこと。

コードの概説：

make_dropdown 中では，現在アクティブなセルの入力内容をキーとして，先ほどのデータベースとなるシートで検索をかける。

ここでは語頭マッチの制限を設け，「山」というキーに対して「石山」をヒットさせないようにしている。（外して正規表現を使ったりすることも可能）

検索結果が存在する場合，アクティブなセルの入力規則（Validation）を作り直す。

ShowError = Falseのオプションを入れることによって，入力規則で設定した値以外の入力があったときに，警告のアラートを表示させないようにしている。

（３）シートを開いた時に処理を自動的に始めるコード

前項のコードが，シートが更新されたイベントのタイミングで毎回実行されるようにしよう。

ThisWorkBook のモジュールに下記のコードを追加。

' セル内容変更時に入力規則を更新
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If ActiveCell.Column = 1 Then
        make_dropdown
    End If
End Sub

（４）使い方

さっそく使ってみる。

名前リストを準備して，xlsファイルを開きなおす。

適当なセルに文字列を入力したら，その入力文字列を確定するために Enter キーではなく，Ctrl + Enter を押す。

そうすれば文字列を確定した時にカーソルが移動しない。

その瞬間，冒頭の画像のように，セルに入力規則が自動的に生成される。

Alt + 下キーを押せば，マウスを使わずに入力規則を表示できる。（キャンセルはEsc）

こうするとセルの入力内容にマッチした候補だけが表示されるし，あとからセルを書き換えればそのつど候補が変化する。

補足

上記の記事は，下記の質問へのご回答として執筆させて頂きました。

http://q.hatena.ne.jp/1227532139

EXCELについての質問です。
日報に人の名前を入力するのですが、入力を簡単に、漢字間違いを防ぐために、
以下のようなことは可能でしょうか。

あいうえお順に並んだ名前のリストがあって、ex)池田,稲岡,川上,柳沢,山田,山本
別のシートのセルに最初の数文字を入れるとドロップダウンなどで絞り込んだ候補が表示され、
選択して入力できる。(例えば、"山"と入力すると、山田,山本が候補として表示され、"山田"まで入力すると山田に絞り込める。
あるいは"や"と入力し、柳沢,山田,山本が候補表示され、"やま"と入力すると山田,山本が候補になる。)

以下の条件があります。
・名前を入力するセルの場所は決まっている
・日報は最終的に印刷して使用
・リストにある名前の件数は200人程度
・リストはときどき追加される
・リストは同ブック内、別シートにある
・入力規則からのリスト入力だと件数が多すぎて効率が悪い

マクロを使わないやり方がよいですが、
無理であればマクロを書いていただけるとありがたいです。

上記の方法が無理なら、覚えきれないほどのデータを
間違いなく、効率よく入力する方法があれば教えてください。

補足２

255文字の文字数制限をなくし，改良版を作って下さった方がいる。

エクセルでグーグルサジェストっぽい入力をする
http://suugleblog.blogspot.jp/2012/02...