手打ちによるデータ入力を効率化するためドロップダウンリストの活用方法を検討します。また、入力済みデータからドロップダウンリストを作成するマクロを公開します。
セルへのデータ入力を効率化したい
手作業で入力作業を進めていると、何度も同じ語句を入力することが多くて、すぐにやっていられなくなります。
作業の効率化のためには、なんとか先に入力した既存データを利用したいところです。
そのための機能の一つにオートコンプリートがあるのですが、これが日本語入力とは相性が悪いようで、無効に設定している人も多いようです。
候補表示のタイミングをユーザがコントロールできればもっと使い勝手のいいものになったと思いますが・・・。
ここでは代わりにドロップダウンリストを使った入力効率化を考えてみます。
標準のドロップダウンリストを使う
「これ前に入力したな」と思ったら、とりあずAlt + ↓(下矢印)
ショートカットを押してみます。
ドロップダウンリストが表示され、その列ですでに入力されている語句が選択できるようになっているかと思います。
これは右クリックメニューにある「ドロップダウン リストの選択」の機能でもあるのですが、Alt + ↓
ですぐに呼べます。
しかし、必ずしも期待通りにはならず、項目に過不足があったり、空のリストになってしまうこともあります。
ドロップダウンリストが項目値を集める条件はセルの位置関係や値、型、セルの書式などが影響し、かなり複雑なようです。
とりあえずやってみて慣れるしかしかありませんが、普通に上の行から順に入力している限り、入力セルの上の既存データが期待通りにリストアップされるようです。
ただ、「日付」や「数値」がドロップダウンリストの対象外になっているのはなんとも残念なことです。
「データの入力規制」のドロップダウンリストを使う
標準のドロップダウンの代わりに、「データの入力規制」のドロップダウンリストを入力に応用する方法もよく見ます。
こちらのリストは、マウスによるプルダウンのするための▼が表示されて入力規制であることがわかります。
マウス操作の代わりにAlt + ↓
ショートカットも使えます。
入力規制のドロップダウンリストを設定するには、リスト項目定義を別に用意し管理しなければならなりません。
入力規制の機能からすれば当然のことなのですが、今いまの入力効率のためだけにその仕込みの手間はちょっと気が重いです。
入力項目が固定されているのならそれでもいいのですが、標準のドロップダウンのように、入力済みデータから簡単にリストを作ることはできないのでしょうか。
手作業でドロップダウンリストを登録するには
「データの入力規制」のドロップダウンリストに既存データを登録する方法を考えてみます。
まず思いつくのは、同所的にドロップダウンにしたい範囲そのものを、そのリスト項目の設定範囲に指定してしまうことです。
やってみると、確かに既存入力データがドロップダウンのリスト項目に表示されるのですが、重複データがそのまま重複したリストになるので効率化には使えません。
結局、別にリストを用意するしかなさそうなので、その方向で考えます。
[データの入力規制]を表示するショートカットは Alt
⇒ D
⇒ L
(Data List) です。
【手順例】
- 入力済みのセル範囲をコピーし、シートの端の余白か別シートに貼り付けて「リスト項目」とします
- リスト項目の重複を削除します
- キー操作:
Alt
⇒A
⇒M
- キー操作:
- リスト項目をソートします(必要なら)
- キー操作:
Alt
⇒H
⇒S
⇒S
- キー操作:
- ドロップダウンリストを使いたい入力範囲を選択した状態で、[データの入力規制]ダイアログを開きます
- キー操作:
Alt
⇒D
⇒L
- [設定]タブの[入力値の種類]で、[リスト]を選択します
- [設定]タブの[元の値]で、リスト項目の範囲を選択します
- [エラー メッセージ]タブの[無効なデータが入力されたらエラー メッセージを表示する]で、チェックを外します(キー入力も可能にするため)
- [OK]ボタンを押します
- キー操作:
- 入力中の列のセルにドロップダウンリストが追加されます
後から入力した新規データは自動反映されないので、それもリスト項目に追加するには再度上記手順を行います。
データのコピー&ペーストではなく、数式で関数など駆使してデータ抽出できれば、動的にリストを更新できるような気がするのですが、それは今後の課題として出来たらまた更新したいと思います。
ドロップダウンリストの解除は`、再び「データの入力規制」ダイアログを開き、「同じ入力規制が設定されたすべて乗せるに変更を適用する」チェックを入れたうえで、「すべてクリア」ボタンを押します。
選択範囲のセル値をもとにドロップダウンリストを設定するマクロ
同所的に既存データをドロップダウンリストに登録するマクロを作成してみました。
外部のリスト定義を必要とはしません。
セルを範囲選択してからこのマクロ VBA を実行すると、セル範囲の値をリスト項目にしたドロップダウンリストを、選択範囲のすべてのセルに設定します。
セルの値には、テキストのほか、日付や数値も使用可能です。
リストにない新規の値を入力してもドロップダウンリストには自動反映されないので、必要ならマクロを再実行してください。
ドロップダウンリストを解除するマクロも用意しました。
Option Explicit ' 選択範囲の値をリスト項目にしたドロップダウンを設定します Sub ドロップダウンリストの設定_選択範囲のリスト化() Dim txt As String Dim list As Collection Set list = New Collection Dim c As Range For Each c In Selection.Cells If Not IsEmpty(c) Then txt = c.Text txt = IIf(IsNumeric(c.Value) And Not IsDate(txt), c.Value, txt) putListItem list, txt End If Next Dim strList As String strList = joinListItems(list) With Selection.Validation .Delete .Add Type:=xlValidateList, Formula1:=strList .IgnoreBlank = True .InCellDropdown = True .ShowError = False End With End Sub ' 選択セルと同じドロップダウンリストを持つ全てのセルで解除します Sub ドロップダウンリストの解除() If ActiveCell.Validation.Type = xlValidateList Then On Error Resume Next ActiveCell.SpecialCells(xlCellTypeSameValidation).Validation.Delete On Error GoTo 0 End If End Sub Private Sub putListItem(ByRef list As Collection, newItem As String) Dim listItem As Variant For Each listItem In list Select Case StrComp(listItem, newItem, vbTextCompare) Case 0 ' 既存 Exit Sub Case 1 ' 新規挿入 list.Add item:=newItem, Key:=newItem, Before:=listItem Exit Sub End Select Next ' 新規追加 list.Add item:=newItem, Key:=newItem End Sub Private Function joinListItems(ByRef list As Collection) As String Const DELIM = "," Dim listItem As Variant joinListItems = DELIM ' 日付が変換されるのを防ぐ For Each listItem In list joinListItems = joinListItems & listItem & DELIM Next End Function
【使い方】
- 標準 VBA モジュールに上記 VBA ソースをコピー&ペーストします
- ドロップダウンリストを表示したいセル範囲を選択します
- あらかじめ、いくつかのセルに値が入っているものとします
- マクロを実行します
- 選択範囲の全セルに、範囲内の値からなるドロップダウンリストが登録されます
Windows 10 の Excel 2013 で動作確認をしました。