Excelで「部署名」や「対応状況」の列を作ったら、入力する人ごとに「営業部」「営業」「営業 部」とバラバラになって、集計のときに頭を抱えた…という経験はありませんか。
そこで出番になるのが、セルの横に▼を出して選ぶだけにするプルダウン(ドロップダウンリスト)です。プルダウンは[データ]タブの[データの入力規則]で[入力値の種類]を[リスト]にし、[元の値]に項目を入れれば作れて、▼が出ないときはまず同じ画面の[ドロップダウン リストから選択する]のチェックを確かめます。
足したはずの項目が出てこないのは、たいてい[元の値]の範囲の外に書き足したとき。コピーしたら▼が消えたなら、貼り付け方を疑います。
プルダウンを作る前に決めておくこと
作り方は2通りあります。
項目が「済・未」「○・×」のように少なく、まず増えないなら、項目を直接打ち込む方法で十分です。部署名や商品名のように、あとから増えたり変わったりしそうなら、シートのどこかに一覧を書いておき、そのセル範囲を指定する方法が向いています。
私は最初、何でも直接入力で作っていました。項目が10個を超えたあたりで、カンマ区切りの長い文字列を直すのが面倒になり、結局セル範囲に作り直しています。迷ったらセル範囲で作っておくと、あとで困りにくいと思います。
項目を直接入力してプルダウンを作る手順(Windows版)
プルダウンを付けたいセルを選んでから始めます。列ごと付けたいときは、列番号をクリックして列全体を選んでもかまいません。
- プルダウンを付けたいセルを選ぶ
- リボンの[データ]タブ →[データの入力規則]をクリックする(新しいExcelでは[データ検証]と表示されることもあります)
- [設定]タブの[入力値の種類]で[リスト]を選ぶ
- [元の値]に、項目を半角のカンマで区切って入力する(例:
未着手,対応中,完了) - [ドロップダウン リストから選択する]にチェックが入っているのを見る
- [OK]をクリックする
カンマの後ろにスペースを入れると、そのスペースまで項目の一部になります。Microsoftの案内でも「はい,いいえ,おそらく」のように、カンマのあとを詰めて書く形になっていますね。
[空白を無視する]は、セルを空のままにしておくことを許すかどうかの設定です。空欄を認めたくない表なら外しておきます。
参考:セルにデータの入力規則を適用する(Microsoft サポート)
セル範囲を元にしてプルダウンを作る手順
先に、選択肢をシートの1列に縦に並べて書いておきます。見出しの行は入れず、項目だけの範囲にするのがコツです。
- 空いている列に、選択肢を1セルに1つずつ縦に入力する(例:H2〜H6)
- プルダウンを付けたいセルを選ぶ
- [データ]タブ →[データの入力規則]を開く
- [入力値の種類]で[リスト]を選ぶ
- [元の値]の欄をクリックし、シート上で選択肢のセル(H2〜H6)をドラッグで選ぶ
- [OK]をクリックする
[元の値]には =$H$2:$H$6 のように、$の付いた絶対参照で入ります。ここが $ なしになっていると、あとでコピーしたときに参照先がずれる原因になるので、手で書き換えた場合は気をつけたいところ。
Mac版のExcelで画面名が違うところ
Mac版も[データ]タブから[データの入力規則](版によっては[検証])を開くのは同じです。ただ、Microsoftの手順ではWindowsの[入力値の種類]が[許可]、[リスト]が[一覧]、[元の値]が[ソース]という名前で出てきます。
名前が違うだけで、やることは変わりません。[許可]で[一覧]を選び、[ソース]にカンマ区切りで項目を入れるか、セル範囲を選んで[OK]です。
参考:ドロップダウン リストを作成する(Microsoft サポート)
あとから選択肢を増やす・消す方法
どう作ったかで、直す場所が変わります。
直接入力で作った場合
- プルダウンのあるセルを選ぶ
- [データ]タブ →[データの入力規則]を開く
- [元の値]のカンマ区切りの文字に、項目を足すか消す
- [同じ入力規則が設定されたすべてのセルに変更を適用する]にチェックを入れる
- [OK]をクリックする
4つ目のチェックを入れ忘れると、選んでいたセルだけが新しい選択肢になり、ほかのセルは古いままになります。地味ですが、一番外しやすいチェックですね。
セル範囲で作った場合
範囲の途中に行を挿入して項目を足したなら、[元の値]の範囲も一緒に広がるので、それだけで反映されます。
困るのは、範囲のすぐ下に書き足したとき。[元の値]が =$H$2:$H$6 のままなので、H7に足した項目は▼を押しても出てきません。この場合は[データの入力規則]を開き、[元の値]を選び直して H2〜H7 に広げます。
項目を消すときは、一覧のセルを消してから上に詰めます。中身だけ消すと、プルダウンの中に空白の行が残ります。
増えるたびに範囲を直すのが面倒なら、テーブルにする
選択肢の一覧をテーブルにしておくと、一覧の最後に項目を書き足すだけでプルダウンにも反映されます!(Microsoftの案内でも、テーブルなら追加・削除で自動的に更新されると書かれています)。
- 選択肢の一覧のどこかのセルを選ぶ
- [挿入]タブ →[テーブル]をクリックする
- [先頭行をテーブルの見出しとして使用する]は、一覧に見出しがあればチェックを入れる
- [OK]をクリックする
- プルダウンの[元の値]を、テーブルの項目のセル(見出しを除く)で選び直す
なお、選択肢の文字を変えても、すでに入力済みのセルの値までは置き換わりません。「営業部」を「第一営業部」に変えたら、入力済みのセルは自分で直す必要があります。
参考:ドロップダウン リストの項目を追加または削除する(Microsoft サポート)
選択肢を別シートに置く方法
入力用のシートに一覧が見えていると、誰かが一覧のほうを書き換えてしまうことがあります。一覧は「リスト」などの名前の別シートにまとめておくと安心です。
- 新しいシートを追加し、名前を「リスト」にする
- 「リスト」シートのA列に、選択肢を縦に入力する(例:A2〜A6)
- 入力用のシートに戻り、プルダウンを付けたいセルを選ぶ
- [データ]タブ →[データの入力規則]を開き、[入力値の種類]で[リスト]を選ぶ
- [元の値]の欄をクリックしてから「リスト」シートのタブを押し、A2〜A6 を選ぶ
- [OK]をクリックする
[元の値]は =リスト!$A$2:$A$6 の形になります。
範囲に名前を付けておく手もあります。[数式]タブ →[名前の定義]で一覧の範囲に「部署一覧」のような名前を付ければ、[元の値]は =部署一覧 と書くだけ。項目が増えたら[数式]タブ →[名前の管理](名前マネージャー)で範囲を広げれば、その名前を使っている全部のプルダウンにまとめて効きますよ!
一覧のシートは、右クリックの[非表示]で隠しておいても、プルダウンはそのまま使えます。
プルダウンを解除する方法
プルダウンをやめたいときは、セルの中身を消すのではなく、入力規則のほうを消します。
- プルダウンを外したいセルを選ぶ(離れたセルは Ctrl キーを押しながらクリック)
- [データ]タブ →[データの入力規則]を開く
- [設定]タブの左下にある[すべてクリア]をクリックする
- [OK]をクリックする
すでに選んで入っている値は消えずに残ります。値ごと消したいなら、そのあとで Delete キーを押してください。
どのセルにプルダウンが付いているか分からないシートなら、[ホーム]タブ →[検索と選択]→[条件を選択してジャンプ]で[データの入力規則]と[すべて]を選ぶと、入力規則のあるセルがまとめて選ばれます。そのまま上の手順で[すべてクリア]すれば、シート中のプルダウンを一度に外せます。
参考:ドロップダウン リストを削除する(Microsoft サポート)
▼が出ない・リストが表示されないとき
最初に1つ。▼はセルを選んだときだけ右に出るもので、選んでいないセルには表示されません。故障ではないので、まずはそのセルをクリックしてみましょう。
選んでも出ないなら、次の順に確かめます。
- そのセルで[データ]タブ →[データの入力規則]を開き、[入力値の種類]が[リスト]になっているか見る
- 同じ画面の[ドロップダウン リストから選択する]にチェックが入っているか見る
- [データの入力規則]がグレーで押せないなら、[校閲]タブに[シート保護の解除]が出ていないか見る(シートが保護されていると、入力規則を変えられません)
- セルに文字を入力している途中なら、Enter キーか Esc キーで入力を終える
- [ファイル]→[オプション]→[詳細設定]の[次のブックで作業するときの表示設定]にある[オブジェクトの表示]が[なし]になっていたら、[すべて]に戻す
2つ目のチェックが外れていると、入力規則そのものは生きているのに▼だけが出ません。Microsoftのヘルプにも、このチェックが無いとセルの横に矢印が表示されないとはっきり書かれています。
5つ目は、図形やグラフを隠す設定にプルダウンの▼も巻き込まれる、というもの。ほかの人から受け取ったファイルでだけ起きるときに疑ってみてください。
▼は出るのに、開いたリストの文字が途中で切れているなら、それは列の幅の問題です。リストの幅はセルの幅で決まるので、列を広げれば全部読めるようになります。
参考:データの入力規則に関する詳細(Microsoft サポート)
追加した項目がプルダウンに出てこないとき
一覧に項目を足したのに▼を押しても出てこない、というのは、ほとんどが[元の値]の範囲の外に書き足したケースです。
- プルダウンのセルで[データの入力規則]を開く
- [元の値]に書かれている範囲(例:
=$H$2:$H$6)を読む - 足した項目のセルがその範囲に入っているかを見る
- 入っていなければ、範囲を選び直して[同じ入力規則が設定されたすべてのセルに変更を適用する]にチェックを入れ、[OK]を押す
名前を付けた範囲を使っているなら、[元の値]には =部署一覧 としか出てこないので、範囲は[数式]タブ →[名前の管理]で確かめます。
範囲に入っているのに出てこないなら、一覧のどこかに空白のセルが挟まっていないか、別のファイルの一覧を見ていないかも疑ってみてください。似た症状でも、フィルターの一覧に項目が出てこないのは原因が別です。
コピーした先でプルダウンが効かないとき
これは私がやった失敗です。名簿の表で、プルダウンのある列に別の行の内容をコピーして貼り付けたら、その行だけ▼が出なくなっていました。
原因は貼り付け方でした。普通に貼り付けると、コピー元のセルの書式や入力規則ごと上書きされるので、コピー元にプルダウンが無ければ貼り付け先のプルダウンも消えます。値を入れたいだけなら、次の手順で貼り付けます。
- コピー元のセルを選んで Ctrl+C でコピーする
- 貼り付け先のセルを選び、Ctrl+Alt+V で[形式を選択して貼り付け]を開く
- [値]を選んで[OK]をクリックする
逆に、プルダウンを別のセルに広げたいときは、プルダウンのあるセルをコピーし、同じ画面で[入力規則](新しいExcelでは[検証])を選んで貼り付けます。中身の値は動かさず、プルダウンだけを写せます。
なお、コピーや貼り付けで入った値は、リストに無い文字でも入力規則のエラーにはなりません。Microsoftのヘルプにも、コピーやオートフィルで入ったデータにはメッセージが出ないとあります。リストの外の値が混ざっていないかを確かめたいときは、[データ]タブ →[データの入力規則]の右の▼ →[無効なデータを円で囲む]で、リストに無い値を赤い丸で囲めます。
まとめ
プルダウンのトラブルは、作り直すより先に、セルを選んで[データの入力規則]を開けば原因がだいたい見えてきます。
▼が出ないなら[ドロップダウン リストから選択する]のチェック、項目が出てこないなら[元の値]の範囲、コピーした先で消えたなら貼り付け方を見る、という順番です。
あとから選択肢が増えそうなら、選択肢を並べた一覧をテーブルにしておくと、一覧の最後に書き足すだけで、範囲を直さなくてもプルダウンに出てくるんです。プルダウンを外すなら[データの入力規則]の画面の左下にある[すべてクリア]で、すでに選んで入っている値は消えずに残ります。
貼り付けで消えたプルダウンは、入力規則のあるセルをコピーして[形式を選択して貼り付け]の[入力規則]で貼れば戻せます。