エクセルの入力を省きたい
カンタンに集計できるようにしたい
そんなときは、プルダウンリスト(ドロップダウンリスト)が便利です。プルダウンリストとは、セルに入れ込む文字を選択できるようにする機能のことです。
この機能を使えば、いちいちエクセルに文字を入力しないで済むようになり、資料の誤記が激減します
さらには、変な入力がされなくなるので、関数やマクロでの計算が一気にラクになります。
この記事では、あなたがエクセル知識ゼロでも、
・プルダウンリストを設定・編集
・選択肢に応じて、セルが自動で色付け
・リストの選択肢が自動で追加
・不要なプルダウンリストの解除・削除
・2段階の連動リスト(複数選択型)
・プルダウンリストのリストだけをコピー
これらの解説はもちろん、さらにドロップダウンリストを使いやすくするコツも合わせてお伝えします。ぜひ、最後まで読んでみてくださいね。
- プルダウンリストの作成する｜手動の設定からラクラク自動化まで
- 連動するリストで複数選択できる方法(2段階リスト)
- プルダウンリストの編集する
- プルダウンリストをコピーする｜全てコピー,部分的にコピー
- プルダウンリストの設定を解除・削除する
- 選択肢に連動して色を変更する｜条件付き書式かマクロ
- 使えるプルダウンリストを作るときの5つのコツ
- さいごに
プルダウンリストの作成する｜手動の設定からラクラク自動化まで
まずは、プルダウンリストの作成方法を紹介します。作り方は、いくつかありますので、あなたの目的に応じて、使い分けることをオススメします。
方法1｜リストを手軽に設定したい1｜直接入力
もっともシンプルな方法を紹介します。これは、選択肢の数が固定の場合に使うと便利です。
メリット
・難しい操作が不要
・1分あれば、すぐに設定できる
デメリット
・選択肢が途中で増えたり、減ったりすると、再設定が必要
作成方法
ここでは、「リンゴ、オレンジ、キウイ」の3つを選択できるプルダウンリストを作成します
[2] タブ「データ」→「データの入力規制」→データの入力規制(V)とクリック
「データの入力規制」のダイアログが出る
[3] 入力値の数値(A)から「リスト」を選択する
[4] 元の値(S)に リンゴ,オレンジ,キウイ と入力し、OKをクリック
選択したセルにプルダウンリストが作成される
もし、選択肢を増やしたいときは、「,」で区切って選択肢を記入します。具体的には、方法7で詳しく紹介していますので、読み進めていってください。
詳しい解説は動画をご覧ください(無音なので、音量調節は不要です)
これをつかえば、1分程度ですぐに、リストを作成することができます。
ただ、カンタンに設定できる一方で、リストの変更が起きたときは、メンテナンスが必要になります。
方法2｜リストを手軽に設定したい2｜範囲指定で作成
メジャーな方法です。選択肢の数が固定の場合に使うと便利です。
メリット
・範囲指定なので、設定がカンタン
・手順通りにやれば、すぐに設定できる
デメリット
・選択肢が途中で増えたり、減ったりすると、再設定が必要
作成方法
「リンゴ、オレンジ、キウイ」の3つを選択できるプルダウンリストを作成します。（ここでは、選択肢用のシートを別に作成しています。）
[3] タブ「データ」→「データの入力規制」→データの入力規制(V)とクリック
「データの入力規制」のダイアログが出る
[4] 入力値の数値(A)から「リスト」を選択する
[5] シート範囲のアイコンをクリック
[6] 「設定」シートのリストにしたい範囲を選択して、OKをクリック
選択したセルにプルダウンリストが作成される
詳しい解説は動画をご覧ください(無音なので、音量調節は不要です)
設定方法がカンタンなので、すぐにできるようになります。ただ方法1と同じで、リストの選択肢を増減したいときは、メンテナンスが必要です。
選択肢が増減しても、困らない方法を試したい方は、次の方法がオススメです。
方法3｜選択肢を編集なしで、自動増減させたい｜名前の管理
リストは作りたいけど、リストの選択肢が追加になるたびに、新しく作る必要がある…というのでは、面倒くさいですよね。
ここでは、リストの選択肢が自動で増えるプルダウンリストの作り方を紹介します。
メリット
・選択肢が途中で増えたり、減ったりしても、自動で調整される
デメリット
・慣れるまで、設定の作業が複雑(エクセルが苦手な人にとって)
・エクセル関数を利用
作成方法
「リンゴ、オレンジ、キウイ」の3つを選択できるプルダウンリストを作成します
リストの設定後、スイカを付け加えて、4つを選択できるプルダウンリストにメンテナンスするところまで説明します。
[2] 「数式」→「名前の管理」を選択
「名前の管理」のダイアログ出現
[3] 新規作成(N)名称をクリック
[4] 名前(N)を記入(ここでは「果物」)
[5] 参照範囲(R) =OFFSET(設定!$A$2,0,0,COUNTA(設定!$A:$A)-1,1) と入力し、OK
[6] 「名前の管理」のダイアログを閉じる
[7] リストを入れ込みたいセルを選択
[8] タブ「データ」→「データの入力規制」→データの入力規制(V)とクリック
「データの入力規制」のダイアログが出る
[9] 入力値の数値(A)から「リスト」を選択する
[10] 元の値(S)に =果物 と入力し、OKをクリック
選択したセルにプルダウンリストが作成される [11] 「設定」シートにスイカを追加すると、リストにスイカが自動で追加
リストに自動で追加される
詳しい解説は動画をご覧ください(無音なので、音量調節は不要です)
補足します。
[5] 参照範囲® =OFFSET(設定!$A$2,0,0,COUNTA(設定!$A:$A)-1,1) と入力 エクセルのワークシート関数のOffset関数を利用しています。
設定!$A$2 →「設定」シートのA2を基準に($は絶対参照を意味)
0 → 下に0行移動
0 → 右に0列移動
COUNTA(設定!$A:$A)-1 → 設定シートのA列に文字を含んだセルが4つ、そこから-1する。つまり、ここでは3の高さ
1 → 1の幅
という意味で、
要するに、設定シートのA2からA4までを参照する。という意味です。
設定シートのA列に「スイカ」を追記すると、offset関数がスイカを検出して参照範囲がA2からA5に変化します。これがメカニズムです。
果物以外のプルダウンリストを作りたい場合は、設定シートのB列に新しく選択肢を作成し、作成手順[1]～[10]を行います。
ただし[5]参照範囲® =OFFSET(設定!$B$2,0,0,COUNTA(設定!$B:$B)-1,1) と入力しましょう。
ここまでくれば、プルダウンリストを新しく作りたい場合は、C,Dと変えていけばいいことが分かるでしょう。
[9] 元の値(S)に =果物 と入力し、OKをクリック
ここでは、名前の管理で設定した名前が入ります。もし、新しく名前の管理で設定した場合、果物ではなく新しく設定した名前が入ります。
この方法を使えば、仕事がいっきにラクになりますので、ぜひ実践してみてください
方法4｜自動増減型リストをカンタンに設定したい｜マクロ(VBA)で自動化
カンタンに言うと、方法3をマクロで自動化した方法です。
メリット
・選択肢が途中で増えたり、減ったりしても、自動で調整される
・マクロで自動で設定されるので、10秒あれば設定できる
デメリット
・マクロ(VBA)なので、知識がないとアレンジできない
作成方法
方法3と同様、「リンゴ、オレンジ、キウイ」の3つを選択できるプルダウンリストを作成します。
設定後、スイカを付け加えて、4つを選択できるプルダウンリストにメンテナンスするところまで説明します。
[2] VBEを起動して、標準モジュールをクリックし、Module1を作成
[3] 生成されたModule1にマクロ(※1)をコピペ
[4] もう一度、標準モジュールをクリックし、Module2を生成
[5] 生成されたModule2にマクロ(※2)をコピペ
[6] リスト化したい列の先頭セルを選択する
[7] マクロ(※1)を実行
実行すると、果物が名前の管理に設定される
[8] プルダウンリストを入れ込みたいセルを選択する
[9] マクロ(※2)を実行
[10] 実行すると、テキストボックスが出現するので、[7]で登録した名前(ここでは果物)を入力
選択したセルにドロップダウンリストが作成される
[11] リストにすいかを追加
リストに自動で追加される
マクロ※1とマクロ※2のコードを紹介しますので、コピペして使ってください。
VBAに興味のある方へ、少し解説しておきます。
マクロ※1は選択しているセルの列をベースに「名前の管理」を新規作成します。選択しているセルが「名前の管理」の名前になります。
マクロ※2は選択しているセルにプルダウンリストを設定します。リストは、「名前の管理」の名称から引用します。テキストボックスは"「名前の管理」の名称"を入れ込むためのものです。
マクロ※1(「名前の管理」を行うマクロです)
注意：リスト化したい行の先頭セルを選択した状態で、VBAコードを起動すること
Sub dropdownlist_setname() Dim ws1 As Worksheet Dim cmax, i, j, n As Long Dim d, hantei As Date Dim namae, sh_name, chunk As String Set ws1 = ActiveSheet sh_name = ws1.Name i = ActiveCell.Row j = ActiveCell.Column cmax = ws1.Range("A65536").Offset(0, j - 1).End(xlUp).Row namae = ActiveCell.value chunk = "=OFFSET(" & sh_name & "!R" & i + 1 & "C" & j & ",0,0,COUNTA(" & sh_name & "!C" & j & ")-1,1)" ActiveWorkbook.Names.Add Name:=namae, RefersToR1C1:=chunk MsgBox namae & " を名前の管理を設定しました" End Sub
マクロ※2(プルダウンリストを設定します)
注意：このマクロを起動すると、記入画面が出ます。そこにマクロ※1で登録した名前を記入すること
Sub dropdownlist_setlist() Dim str As String str = InputBox("プルダウンリストの名前を入れる") With Selection.Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= xlBetween, Formula1:="=" & str .IgnoreBlank = True .InCellDropdown = True .InputTitle = "" .ErrorTitle = "" .InputMessage = "" .ErrorMessage = "" .IMEMode = xlIMEModeNoControl .ShowInput = True .ShowError = True End With End Sub
詳しい解説は動画をご覧ください(無音なので、音量調節は不要です)
さて、マクロでカンタンに設定できるようになりましたが、さらにカンタンな操作で、プルダウンリストを設定できるように工夫できます。
これを実施すれば、10秒でプルダウンリストを設定できます。その方法とは、マクロにショートカットキーを割り当てるというモノです。
マクロのショートカットキー化は、こちらの記事で詳しく紹介していますので、ぜひ合わせて読んでみて下さい。
これで、範囲の自動調整機能付きドロップダウンリストが、10秒で作成できるようになります。
方法5(おまけ)|マクロ(VBA)でリストを設定する
方法4とは、ちがうカタチでマクロを利用します。作成する方法を紹介します。
ただし情報として、紹介しますが、あまり使い物になりません。興味があれば、見てみるとよいでしょう。
作成方法
方法3と同様、「リンゴ、オレンジ、キウイ」の3つを選択できるプルダウンリストを作成します。
[2] マクロ※3の起動
リストを入れ込みたいセルを選択した状態で、起動すること
マクロ※3
Sub dropdownlist_setlist() With Selection.Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= xlBetween, Formula1:="=リンゴ,キウイ,オレンジ" .IgnoreBlank = True .InCellDropdown = True .InputTitle = "" .ErrorTitle = "" .InputMessage = "" .ErrorMessage = "" .IMEMode = xlIMEModeNoControl .ShowInput = True .ShowError = True End With End Sub
なお、このコードでは、リストの選択肢を自動で増減しません
コードの中の=リンゴ,キウイ,オレンジに , で追加すれば、リストの選択肢を増やせます。
変更前
=リンゴ,キウイ,オレンジ
変更後
=リンゴ,キウイ,オレンジ,スイカ
と変更して、マクロを起動すれば、選択肢が4つに増えたプルダウンリストを作成できます。
連動するリストで複数選択できる方法(2段階リスト)
2段階で設定するプルダウンリストを聞いたことはありますか？
たとえば、最初のプルダウンリストで果物を選択すると、それに応じて、次のプルダウンリストが「果物」のカテゴリーから選択できるようになるリストです。
方法6｜複数リスト連動型(2段階連動リスト)
ここでは、作業者の使い勝手をグンと上げる2段階で設定するドロップダウンリストの作成方法を紹介します。
メリット
・選択を制限できるので、誤記が激減
・選択を制限できるので、関数やマクロによる計算漏れが激減
デメリット
・設定が少し複雑
・エクセル関数を利用するので、エクセル関数の使い方を知っておくこと
作成方法
ここでは、以下のようなプルダウンを作ります。
第1段階のリスト：果物、スイーツ、お菓子
第2段階のリストは
果物：リンゴ、オレンジ、キウイ
スイーツ：ケーキ、プリン、マカロン
お菓子：クッキー、チョコレート、ポッキー
[2] 「数式」→「名前の管理」を選択
「名前の管理」のダイアログ出現
[3] 「名前の管理」を4つ作成
・カテゴリー：果物、スイーツ、お菓子
・果物：リンゴ、オレンジ、キウイ
・スイーツ：ケーキ、プリン、マカロン
・お菓子：クッキー、チョコレート、ポッキー
ここでは、参照範囲をマウスで選択する方法で、4つを設定します(詳しくは動画で)
[4] 「名前の管理」のダイアログを閉じる
[5] 第1段階のプルダウンリストを入れ込みたいセルを選択
[6] タブ「データ」→「データの入力規制」→データの入力規制(V)とクリック
「データの入力規制」のダイアログが出る
[7] 入力値の数値(A)から「リスト」を選択する
[8] 元の値(S)に =カテゴリー と入力し、OKをクリック
選択したセルに第1段階プルダウンリストが作成される
[9] 第2段階のプルダウンリストを入れ込みたいセルを選択
[10] タブ「データ」→「データの入力規制」→データの入力規制(V)とクリック
「データの入力規制」のダイアログが出る
[11] 入力値の数値(A)から「リスト」を選択する
[12] 元の値(S)に =indirect(D3) と入力し、OKをクリック
※ここでは、第1段階のプルダウンリストが設定されているセルB2を選択
選択したセル第2段階のプルダウンリストをセルに設定
[13] コピペして、プルダウンリストをほかのセルにも適用
詳しい解説は動画をご覧ください(無音なので、音量調節は不要です)
この2段階の連動リストは、都道府県の選択などに役立ちます。第１段階のリストで「関東」を選択して、２段階目のリストで、「東京」と選ぶカンジです。 この方法を使えば、ラクに作成できますので、ぜひ実践してみてください
プルダウンリストの編集する
せっかくプルダウンリストを設定したのはいいけれど
・リストの選択肢が増えてしまった…
・選択肢に間違いがあったので、修正したい…
・リストの選択肢の数を減らしたい…
そんな場合には、プルダウンリストの編集が必要。ここでは、プルダウンリストの編集方法を紹介します。
方法7｜データの入力規制を再設定・編集
方法1,方法2で紹介したやり方で、プルダウンリストを作成すると、データの入力規制を再設定・編集する必要があります。
では、選択肢「リンゴ,キウイ,オレンジ」に「スイカ」を追加する場合を考えます。
方法1の場合設定したリストの再設定・編集する方法
[2] タブ「データ」→「データの入力規制」→データの入力規制(V)とクリック
「データの入力規制」のダイアログが出る
元の値(S)に リンゴ,オレンジ,キウイ と記入されているので
[3] 元の値(S)に リンゴ,オレンジ,キウイ,スイカ と変更して、OKをクリック
選択したセルにプルダウンリストが再設定・編集される
方法2の場合設定したリストの再設定・編集する方法
[3] タブ「データ」→「データの入力規制」→データの入力規制(V)とクリック
「データの入力規制」のダイアログが出る
元の値(S)に =設定!$2$:$4$ と記入されているので
[4] リストの記載 =設定!$2$:$4$ を =設定!$2$:$5>$に変更、OKをクリック
選択したセルにプルダウンリストが再設定・編集される
これで、選択肢が4つに増えたプルダウンリストに変更できます。
方法8｜シートを編集する
方法3,方法4で紹介したやり方で、プルダウンリストを作成した場合、「設定」シートの記載を編集するだけです。
・リストの選択肢を減らしたいなら、記載を削除
・リストの選択肢の文言を修正したいなら、記載を変更
というカンジです。
詳しい解説は動画をご覧ください(無音なので、音量調節は不要です)
プルダウンリストをコピーする｜全てコピー,部分的にコピー
リストをセルに設定したはいいけど、ほかのセルにも同じ設定を行いたい！そんなときの使える方法を紹介します。
方法9｜セルの線や色を含めて、全てコピーする
プルダウンリストを含めてコピペすればOKなので
[2] ショートカットキー[Ctrl + C]でコピー
[3] 新しく選択肢を設定したいセルを移動する
[4] ショートカットキー[Ctrl + V]で貼り付け
でセルをコピぺしてしまえばOKです。
ただし、コピーしたセルの線や色も含めて全てコピーしてしまいます。気にしないなら、これでOKです。
プルダウンリストだけを部分的にコピーしたい場合は、次のやり方を試してみてください。
方法10｜プルダウンリストだけを、部分的にコピーしたい
ショートカットキー[Ctrl + Alt + V]を使います。
具体的には
[2] ショートカットキー[Ctrl + C]でコピー
[3] 新しく選択肢を設定したいセルを移動する
[4] ショートカットキー[Ctrl + Alt + V]で貼り付け
[5] 「形式を選択して貼り付け」が表示される
[6] 入力規制(N)を選択してOK
これでプルダウンリストを部分的にコピーできます。コピー元の線や色は、コピーされません。
プルダウンリストの設定を解除・削除する
リストを解除・削除したい！そんなときの方法を紹介します。方法11だけを覚えておけば、問題ありません
方法11｜空白のセルをコピペする
空白のセルをコピペすればOKなので
[2] ショートカットキー[Ctrl + C]でコピー
[3] プルダウンリストを解除・削除したいセルに移動
[4] ショートカットキー[Ctrl + V]で貼り付け
これで、プルダウンリストを解除・削除できます。
ただし、セルの線や色も含めて全て解除・削除されてしまいます。もし、プルダウンリストだけを削除したい場合は、次のやり方を試してみてください。
方法12｜入力規制をリセット
セル内のプルダウンリストだけを解除・削除します。セルの線や色はそのままです。
[2] タブ「データ」→「データの入力規制」→データの入力規制(V)とクリック
「データの入力規制」のダイアログが出る
[3] すべてクリア(C)を選択してOK
これでセルのプルダウンリストだけを部分的に削除できます。
選択肢に連動して色を変更する｜条件付き書式かマクロ
プルダウンリストで選択肢Aを選んだら、同時にセルの色が変わるように設定したい！そんなときの方法を紹介します。
これがデキるようになれば、表の見やすさが一気に改善されて、間違いを減らせるので、便利ですよ！
方法13｜条件付き書式で、選択肢に応じて色を設定
条件付き書式をシートに入れ込んでおくと、選択肢に応じて、色を設定できます。
ただし、データの量が500行～1000行を超えてくると、エクセルが重くなってしまいます。データが大きい場合は、方法14で紹介するマクロでの自動化をオススメします。
作成方法
「リンゴ」を選択したら赤、「スイカ」を選択したら緑になるように設定します
文字列のダイアログが出現 [2] リンゴと入力して、濃い赤の文字～を入力
[3] スイカと入力して、濃い緑の文字～を入力
[4] ホーム→条件付き書式→ルールの管理(R)をクリック
条件付き書式ルールの管理 のダイアログが出現 [5] 適用先を列全体にする(ここではD列)
詳しい解説は動画をご覧ください(無音なので、音量調節は不要です)
方法14｜VBA(イベントプロシージャ)を利用する
選択肢に応じて、色を設定する方法は、これがもっともオススメです。
データの量が500行～1000行を超えても、エクセルはあまり重くなりません。ただし、VBA(イベントプロシージャ)と聞くだけで、「ちょっと…」となる方も少ないはず。
ここでは、コピペで今スグに使えるように説明していきますので、ご安心ください。
設定方法
「リンゴ」を選択したら赤、「スイカ」を選択したら緑になるように設定します
[2] Sheet1(プルダウンリスト作成)にマクロ(※4)をコピペ
マクロ※4
Private Sub Worksheet_Change(ByVal Target As Range) If Target.Value = "リンゴ" Then Target.Interior.ColorIndex = 3 ElseIf Target.Value = "スイカ" Then Target.Interior.ColorIndex = 4 Else Target.Interior.ColorIndex = xlNone End If End Sub
色は4や6の数字で設定します。「vba colorindex 色設定」というカンジで検索すれば、どの数字がどの色を設定しているか調べることができます。
その数字をいれてやれば、あなたの好みの色でセルを塗りつぶすことができます。
方法15｜マクロのショートカットキーを設定する
この方法は、手動でやる方法です。選択肢を変更したら、連動して色がつくというものではありません。
選択肢に連動して、色がつく方法は、方法13や方法14で紹介しました。しかし、エクセルを確認するときに、手動で色を付ければ、十分という場合がけっこうあります
また、実際にプルダウンリストを運用してみると、自動での色付けは、3つくらいで十分です。4つ以上の設定をつけると、メンドウになってきます。
正直、プルダウンリストを使って人間からの経験を述べさせていただくと、自分の好みの色をマクロのショートカットキー設定しておく方がよっぽど便利です。ですので、あえて紹介させていただきました。
やりかたは、こちらの記事で紹介していますので、興味があれば読んでみてください。
使えるプルダウンリストを作るときの5つのコツ
ここからは、ドロップダウンリストを早く使いこなす方法を紹介します。
コツ1｜設定のシートをつくる
「プルダウンリスト」を使うシートとプルダウンリスト用の選択肢を設定シートを分ける。これがオススメです。
詳しくいうと、下の画像のように、シートを分けましょう。
分ける理由は、
・選択肢が増減しても、すぐに修正できる
・どの選択肢が、どこにあるかが一目で分かる
仕事でも、作業と管理は分けますよね？それと同じで、エクセルもデータ作業用と管理用シートを分けると、グッと仕事がラクになります。
コツ2｜プルダウンの選択肢は、[Alt+↓]を使う
ドロップダウンリストのいいところは、いちいち書く必要がなくなるところです。
しかし、選択肢のセルをマウスでクリックしていると、時間がかかってしまいますよね・・・
せっかく、選択肢を書く必要がなくなったのに、選ぶのに時間がかかる・・・なんてことは避けたいところ。
そこで、紹介したいのが、ショートカットキー[Alt + ↓]でドロップダウンリストを操作することです。
Altを押しながら↓を押すと、マウスなしでプルダウンリストを開けるようになります。ぜひ使いこなしてみてください
コツ3｜エクセルシートの移動は、[Ctrl+PageUp]や[Ctrl+PageDown]で
方法3を使って、プルダウンリストを作成すると、シートの移動を頻繁に行います。 そんなとき、いちいちマウスを使っているとメンドウです。
ですので、エクセルシートの移動は、ショートカットキー[Ctrl+PageUp]や[Ctrl+PageDown]と覚えてしまいましょう！
おどろくほどエクセル仕事が早くなりますので、ぜひ使いこなしてみてください
コツ4｜ヘッダーの名称と「名前の管理」の名称を合わせる
オススメしている方法3の方法でプルダウンリストを作成すると、
選択肢を自動増減してくれて、仕事が一気にラクになりますが、「名前の管理」という作業が発生します。
この「名前の管理」という作業、設定するときはいいのですが、設定後、「名前の管理」で設定した名称を忘れてしまい、再設定に時間がかかることがあります。
ですので、視覚的にわかりやすくするため、「設定」シートのヘッダー と 「名前の管理」の名称を一致させることをオススメします。かなりラクになります。
コツ5｜日付の入力はCtrl + ; でやってもらう
入力規制で、日付を規制をしたい場合、
・2017.3.24
・3/24
・2017年3月24日
など、入力者によっていろんな形式で記載されます。
しかし、日付を入力する場合は、プルダウンリストをわざわざ使う必要はありません。
覚えておいておいていただきたいのが、日付けの入力は、ショートカットキー[Ctrl + ;]です。
このショートカットキーで入力してもらえば、2017/3/24という形式に統一されます。
あとあと、エクセルでデータ計算するには、2017/3/24の形式がもっとも便利ですので、ショートカットキー[Ctrl + ;]で入力してもらうようにルール化することをオススメします。
さいごに
いかがだったでしょうか？プルダウンを使いこなせば、かなり仕事がラクになります。この記事があなたの一助になれば、幸いです。
