Excelの入力規則のリスト機能を使用すると毎回同じ項目を選ぶことができて便利なんですが、このリスト、項目数が多くなってくると選ぶのにスクロールする必要があったりして意外に面倒になってくることがあります。
そんなときにリストに表示される項目を頭文字などのサーチキーで絞り込むことはできないか色々調べてマクロを作ってみました。
今回はExcelで経費帳や現金出納帳を作成しているケースを前提としています。
Excelの入力規則のリストをサーチキーで絞り込むマクロ
Excelの入力規則のリストは便利なんですが項目数が多く（大体10個以上とか）なると目的の項目を選ぶためにスクロールする必要がでてくるので面倒になってきます。
こんな感じで。
ですが、サーチキーで絞り込むことができればリストに表示される項目を少なくすることができるので、選ぶ面倒が少しは減ると思います。
ということでまずはイメージをつかんでもらうために動画を用意しました。
A列～D列にかけて経費帳とか現金出納帳のようなものを準備して、そのうちのC列の入力規則を設定しており、リストから勘定科目を選択するようにしています。
C列のどこかのセル、例えばC6セルでサーチキーを入力して確定するとF列とG列に作成した勘定科目のリストからサーチキーに対応した勘定科目だけがリストに表示されるようになることを確認していただければと思います。
マクロのコード
今回考えたマクロはこんな感じになります。まずは標準モジュールに下記のマクロを記載します。
（標準モジュールのModule1に記載しています。）
Sub siborikomi()
'■Ｃ列に入力されたサーチキー（アルファベット）で勘定科目を絞り込み
'■入力規則に表示させるマクロ （サジェスト機能）
'■変数宣言
Dim listbox As Variant
Dim i As Long
Dim lastrow As Long
'■最終行の取得 マスタを作成している場所を参照する
lastrow = Cells(Rows.Count, 6).End(xlUp).Row
'■処理実行部分
'■アクティブセル（Ｃ列のセル）に入力されたアルファベットのサーチキーでＦ列からInstr関数で該当する勘定科目を検索する
'■もし該当する勘定科目があれば変数「listbox」に格納する
'■マスタに登録された勘定科目の数だけ繰り返す。（lastrowで登録された勘定科目の数を取得する)
For i = 2 To lastrow
If Range("f" & i).Value <> "" Then
If InStr(Range("g" & i).Value, ActiveCell.Value) = 1 Then
listbox = listbox + Range("f" & i).Value + ","
End If
End If
Next i
'■変数「listbox」に何らかの勘定科目が格納されている場合に、入力規則をＣ列に設定する。
If Len(listbox) > 0 Then
Range("c:c").Validation.Delete '■入力規則をクリアする ※必須。これがないとエラーが発生する。
Range("c:c").Validation.Add Type:=xlValidateList, Formula1:=listbox '■絞り込んだ勘定科目で入力規則をセットする
Range("c:c").Validation.IMEMode = xlIMEModeOff '■サーチキーを使いやすくするために日本語入力をオフにする
Range("c:c").Validation.ShowError = False '■入力規則にないデータ（勘定科目）を入力してもエラーにならないようにする
End If
End Sub
次に、sheet1のモジュール（今回の場合はシート１に「勘定科目絞込」という名前をつけています。）を選択して、「worksheet」を選択して「change」にしたうえでコード記載欄に下記のマクロを記載します。
Private Sub Worksheet_Change(ByVal Target As Range)
If ActiveCell.Column = 3 Then
Call siborikomi
End If
End Sub
これで準備は完成になります。
サーチキーでリストの項目を絞り込むマクロの内容を解説
自分で作ったマクロのくせに完全に内容を理解していませんが、わかる範囲でコードの内容を説明しておきます。
おおまかな流れとしては下記のように考えました。
- C列のどこかのセル（アクティブセル）に入力されたサーチキーをG列の２番目からＧ列の最後の行まで探す。
- G列を検索してサーチキーに一致するデータがあればその左にある勘定科目のデータを変数「listbox」に格納していく。
- 「listbox」になんらかのデータがあれば（検索した結果何か一つでもヒットしたら）それを入力規則のリストに設定する。
- C列に入力する際には入力モードを「オフ」に設定することでひらがな入力をしないようにする。
- 入力規則のリストに登録がない勘定科目でもセルに入力ができるようにしておく。
まずは変数宣言をしておきます。今回はサーチキーで検索して検索にヒットした勘定科目を格納するための変数として「listbox」という変数を宣言しました。
変数「i」については特に意味はありません。
変数「lastrow」については読んで字のごとく、データの最終行の番号を取得するための変数として使用します。今回はF列の最終行を取得して格納しています。
この部分ですね↓
で、Instr関数を使ってアクティブセルであるC列のどこかのセル（例えばC６セルとか）に入力されたアルファベットのサーチキーがG列に記載されたサーチキーに含まれているかどうかを判定させます。
Instr関数の使い方はネットで調べると丁寧な解説記事が腐るほどありますので割愛します。
（自分もよくわかってません。。）
判定の結果一致したら変数「listbox」にサーチキーに一致したG列の勘定科目を格納します。勘定科目を格納するのと同時に文字列である「，（カンマ）」も一緒に変数「listbox」に格納します。
この記載をしておかないとリストがうまく反映されないみたいです。
この一連の作業をfor~nextステートメントを使って繰り返します。
繰り返す回数は変数「lastrow」に格納した回数、すなわちF列の最終行（F列に記載された勘定科目）の数だけ繰り返します。
この部分になります。↓
繰り返しの作業が終わったら変数「listbox」の中に格納された文字数を判定し、文字数が０文字以上なら、すなわち変数「listbox」になにか一つでも勘定科目が格納されていたらその結果を入力規則のリストに設定します。
「validation」というものをつかうと入力規則をマクロで扱うことができるようです。
この部分になります。
意味合い的にはコードの48行目で前に設定されていた入力規則をクリア（delete）して、
コードの50行目で新たに入力規則をリストで設定する。
そのリストは変数「listbox」の中のデータでリストを作成する。
具体的には手作業で入力規則を設定するときにでてくるこの画面の設定と同じことをやっています↓
次にコードの52行目でC列全体の入力モードを変更し、ひらがな入力できないように設定。
この画面の設定と同じことをやっています↓
最後にコード54行目で、もしリストにない科目をC列のセルに入力した場合にエラーメッセージがでないように設定しています。
この画面の設定と同じ作業になります↓
以上がざっくりとした説明になります。
サーチキーの使いかた
このマクロですが、使い方に少しクセがあるというか、入力の際のコツがあります。
それはサーチキーを使用するセル、今回の場合はC列のセルに入力したサーチキーを確定させるときにenterキーで確定させるのではなくて、ctrlキーを押しながらenterキーを押して確定させる必要があります。
というのも、enterキーでC列のセルに入力した内容を確定させるとセルの選択箇所が下に１つずれてしまうため、サーチキーで絞り込んだ勘定科目をリストから選択するためにわざわざ一つ上のセルを選択しなおす必要がでてきます。
ctrlキーを押しながらenterキーを押して確定させることでマクロが実行されて入力規則のリストが絞り込まれます。それと同時に下のセルに移動することを防ぐことができるのでこのやり方に慣れる必要があります。
まとめ
今回はExcelの入力規則のリストをサーチキーで絞り込むことを記事にしました。
絞り込んでいるようにみせかけていますが、実際には毎回入力規則のリストを作り直して毎回設定しなおしているといったほうが表現としては正しい気がします。
今回は勘定科目のリストを作成してサーチキーで絞り込むようにしましたが、勘定科目の部分を摘要でよく使うリストなどに置き換えて使ってもいいのかなと思います。
あと、F列とG列の勘定科目のマスタとサーチキーについては出納帳のデータを入力するシートに作成せず、別のシートで管理するほうが効率的になるかもしれません。このあたりは好みで調整していただければよいかと思います。
リストの項目が増えすぎて困っている人は試してみていただければと思います。
機能を一部改良した記事もあります。気になるかたはこちらをご覧ください↓
【本日の近況報告】
タレントの方が書いた本をたまに読むことがありまして、
くりーむしちゅーの上田さんが書いた本を最近読みましたが面白くて一気に読み切ってしまいました。
上田さんが番組でトークしている感じで文章が進んでいくのでサクサク読めます。
読みながら思わず笑ってしまいました。
くりーむしちゅーのオールナイトニッポンまたやってくれないかな～。
【本日の１曲】
The Isley Brothers / Respectable
シーナアンドザロケッツのレモンティーって曲の元ネタというかオリジナルがヤードバーズのTrain Kept On Rollinだということを知ってからヤードバーズを聴いてたんですが、ライブ音源でrespectableという曲をやってまして。
これがすごくカッコよくて最初はヤードバーズがオリジナルだと思ってたんですが調べてみるとどうやら違うことが判明。
オリジナルはshoutで有名なこの人たちだということが判明しました。
ローリングストーンズにも同じ名前の曲があって最初はこっちがオリジナルかと思いました。（よくきくと全然曲の感じが違う。。）
で、オリジナルを聴いてみたらこっちもカッコよくて。１９６０年代の曲とは思えない。。。
この曲はヤードバーズのほかにもアウトサイダーズってバンドもカバーしてるみたいですね。
