シーゴの Excel 研究室

タイトル変更しました (旧称:今日を乗り切るExcel研究所)

入力したセルの値をドロップダウンリストに表示したい

手打ちによるデータ入力を効率化するためドロップダウンリストの活用方法を検討します。また、入力済みデータからドロップダウンリストを作成するマクロを公開します。

セルへのデータ入力を効率化したい

手作業で入力作業を進めていると、何度も同じ語句を入力することが多くて、すぐにやっていられなくなります。

作業の効率化のためには、なんとか先に入力した既存データを利用したいところです。

そのための機能の一つにオートコンプリートがあるのですが、これが日本語入力とは相性が悪いようで、無効に設定している人も多いようです。

候補表示のタイミングをユーザがコントロールできればもっと使い勝手のいいものになったと思いますが・・・。

ここでは代わりにドロップダウンリストを使った入力効率化を考えてみます。

標準のドロップダウンリストを使う

「これ前に入力したな」と思ったら、とりあずAlt + ↓(下矢印)ショートカットを押してみます。

ドロップダウンリストが表示され、その列ですでに入力されている語句が選択できるようになっているかと思います。

f:id:shego:20170501191503p:plain

これは右クリックメニューにある「ドロップダウン リストの選択」の機能でもあるのですが、Alt + ↓ ですぐに呼べます。

しかし、必ずしも期待通りにはならず、項目に過不足があったり、空のリストになってしまうこともあります。

f:id:shego:20170501191717p:plain

ドロップダウンリストが項目値を集める条件はセルの位置関係や値、型、セルの書式などが影響し、かなり複雑なようです。

とりあえずやってみて慣れるしかしかありませんが、普通に上の行から順に入力している限り、入力セルの上の既存データが期待通りにリストアップされるようです。

ただ、「日付」や「数値」がドロップダウンリストの対象外になっているのはなんとも残念なことです。

f:id:shego:20170501191608p:plain

「データの入力規制」のドロップダウンリストを使う

標準のドロップダウンの代わりに、「データの入力規制」のドロップダウンリストを入力に応用する方法もよく見ます。

こちらのリストは、マウスによるプルダウンのするための▼が表示されて入力規制であることがわかります。

マウス操作の代わりにAlt + ↓ショートカットも使えます。

f:id:shego:20170501210911p:plain

入力規制のドロップダウンリストを設定するには、リスト項目定義を別に用意し管理しなければならなりません。

入力規制の機能からすれば当然のことなのですが、今いまの入力効率のためだけにその仕込みの手間はちょっと気が重いです。

入力項目が固定されているのならそれでもいいのですが、標準のドロップダウンのように、入力済みデータから簡単にリストを作ることはできないのでしょうか。

手作業でドロップダウンリストを登録するには

「データの入力規制」のドロップダウンリストに既存データを登録する方法を考えてみます。

まず思いつくのは、同所的にドロップダウンにしたい範囲そのものを、そのリスト項目の設定範囲に指定してしまうことです。

やってみると、確かに既存入力データがドロップダウンのリスト項目に表示されるのですが、重複データがそのまま重複したリストになるので効率化には使えません。

結局、別にリストを用意するしかなさそうなので、その方向で考えます。

[データの入力規制]を表示するショートカットは AltDL (Data List) です。

【手順例】

  1. 入力済みのセル範囲をコピーし、シートの端の余白か別シートに貼り付けて「リスト項目」とします
  2. リスト項目の重複を削除します
    • キー操作: AltAM
  3. リスト項目をソートします(必要なら)
    • キー操作: AltHSS
  4. ドロップダウンリストを使いたい入力範囲を選択した状態で、[データの入力規制]ダイアログを開きます
    • キー操作: AltDL
      f:id:shego:20170507221929p:plain:w200 f:id:shego:20170507222148p:plain:w200
      1. [設定]タブの[入力値の種類]で、[リスト]を選択します
      2. [設定]タブの[元の値]で、リスト項目の範囲を選択します
      3. [エラー メッセージ]タブの[無効なデータが入力されたらエラー メッセージを表示する]で、チェックを外します(キー入力も可能にするため)
      4. [OK]ボタンを押します
  5. 入力中の列のセルにドロップダウンリストが追加されます

後から入力した新規データは自動反映されないので、それもリスト項目に追加するには再度上記手順を行います。

データのコピー&ペーストではなく、数式で関数など駆使してデータ抽出できれば、動的にリストを更新できるような気がするのですが、それは今後の課題として出来たらまた更新したいと思います。

ドロップダウンリストの解除は`、再び「データの入力規制」ダイアログを開き、「同じ入力規制が設定されたすべて乗せるに変更を適用する」チェックを入れたうえで、「すべてクリア」ボタンを押します。

 



選択範囲のセル値をもとにドロップダウンリストを設定するマクロ

同所的に既存データをドロップダウンリストに登録するマクロを作成してみました。

外部のリスト定義を必要とはしません。

セルを範囲選択してからこのマクロ VBA を実行すると、セル範囲の値をリスト項目にしたドロップダウンリストを、選択範囲のすべてのセルに設定します。

セルの値には、テキストのほか、日付や数値も使用可能です。

f:id:shego:20170503000520p:plain

リストにない新規の値を入力してもドロップダウンリストには自動反映されないので、必要ならマクロを再実行してください。

ドロップダウンリストを解除するマクロも用意しました。

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

【使い方】

  1. 標準 VBA モジュールに上記 VBA ソースをコピー&ペーストします
  2. ドロップダウンリストを表示したいセル範囲を選択します
    • あらかじめ、いくつかのセルに値が入っているものとします
  3. マクロを実行します
  4. 選択範囲の全セルに、範囲内の値からなるドロップダウンリストが登録されます

Windows 10 の Excel 2013 で動作確認をしました。