前回は、Excelの入力規則について解説しました。
入力規則を設定することで表示されるプルダウンリストは、マウスのみでデータを選ぶことができるので、ミスの防止や効率アップを図ることができます。
入力規則を使うと便利さを実感できますが、項目が増えるとマウスで選ぶのに時間がかかるという問題に必ず直面します。
当然ですが、便利なものは長期間にわたって使われることになります。長期間使われることになると、新しい項目をリストに追加しないといけなくなります。
数項目程度ならば大きな問題はありませんが、項目が増えすぎると入力するムダを無くすために作られた入力規則が、項目を探すムダを生みます。
その問題は、プルダウンリストを2段階で連動させることで解決できます。
プルダウンリストを連動することが出来れば、項目を選ぶのに大幅に時間を削減できます。
もくじ
この記事を書いている私は、社内でExcel業務の改善を10年以上行ってきましたが、日々追加される新しい機能について情報を取り入れ、現在も学習を続けています。
そんな私が、この記事を読んで頂いている方の問題が解決できるように適切な機能を選んで解説していきます。
今回は、まだ実用的ではないけれども、今後の発展が望める新しい機能についても触れていきます。
Excelのプルダウンリストを連動させるメリット
Excelのプルダウンリストを連動させために行うことは、1行のデータに2つの入力規則を設置します。
例えば、1つ目は分類、2つ目は商品名などです。
商品名のみで入力規則を設定した場合でも、商品名が10個以下程度のうちは使いにくさを感じることはありません。
ところが、商品名が増えれば増えるほど使いにくくなり、項目を探すムダを生みます。
探すムダを少なくするために、商品名の前に分類という入力規則を設定します。
2つのリストから項目を選ばないといけませんが、それぞれの項目を選ぶ時間が減ります。
結果、連動させない場合に比べ、項目を探す時間が少なくなります。
折れ線グラフで商品数と入力時間を表現するとこのような感じです。
Excelのプルダウンリストを連動させるための前準備
入力規則を連動させるためには、商品マスタの表以外に、連動させるためのリストを別に設定する必要があります。
ここで、シートの左側にデータを入力し溜めるための表を、右側に商品マスタを設置しています。
これからB列とC列に入力規則を設置していきます。
連動させるためには、分類と商品CDを整理し直した表を作ります。新しく設定した表に名前(分類名)をつけます。
これで前準備は完了です。
Excelのプルダウンリストを連動させるための具体的方法
いよいよプルダウンリストを連動させるために、入力規則の設定を行います。
設定方法は、以下の2つの方法が主に使われます。
- INDIRECT関数
- 複数の関数を組み合わせる
Excelの関数に慣れていない人は、まずはINDIRECT関数をマスターするとよいです。
Excelの関数を使いこなしている人は、複数の関数の組み合わせに挑戦してみましょう。
例として、VLOOKUP関数を卒業して、INDEXとMATCHを使っているような方です。
INDIRECT関数を使う
INDIRECT関数を使う場合は、前もって範囲に名前をつけておきましょう。
1つ目の分類名(B列)は、元の値に別表の見出し行を指定します。
B列で、分類名のプルダウンリストが完成しました。
次にC列の入力規則の設定を行い、プルダウンリストを連動させます。
元の値は、INDIRECT関数でB列のセルを指定します。ここで絶対参照を外すのを忘れないでください。
これで入力規則の設定は完了しています。
動作確認を行います。
果物を選択し、C列のプルダウンリストを表示させます。果物を指定している5商品が選択されています。
次に、野菜を選択すると、2商品のみが表示されました。
複数の関数を組み合わせる
名前を設定せずに、複数の関数を組み合わせて連動させる方法があります。
以下の関数を使うことになります
- OFFSET関数(範囲と始点を決める)
- MATCH関数(1つ目のプルダウンリストから値を受け取る)
- COUNTA(セルの数を決める)
この方法では、連動させるための表については、INDIRECT関数の構成とは行列を入れ替えるようにします。
1つ目のプルダウンリストで使用する式です。
ここではOFFSET関数とCOUNTA関数のみを用います。
2つ目のプルダウンリストにも式を入力します。
式が長くて入らなかったので、こちらに表示しました。
=OFFSET($R$1,MATCH(B2,OFFSET($R$1,0,0,COUNTA($R:$R),1),0)-1,1,1,5)
3つの関数を組み合わせています。
これで、INDIRECT関数と同じようにプルダウンリストを連動させることができます。
マスタ表から連動用の表を手入力しない方法
連動用のリストを手入力で作成せずに設定する方法があります。この２つの方法は、初めに解説した方法に比べ難易度が上がりますが、興味のある方は挑戦してみましょう。
AGGREGATE関数を使う
AGGREGATE関数を使って、リストを自動で作成する方法です。
例えば、商品マスタに新しい分類「精肉」を追加した場合でも、上の数式をコピペするだけで自動で追加することができます。
詳細は、こちらのYouTube動画で解説してありますので、よかったら参考にしてください。
Excel: Find Multiple Matches & Dependent Drop Down List (Unsorted Tabular Data Set)
スピルを使う
Microsoft365を契約している方は、スピルという機能を使うこともできます。
スピルを使ってプルダウンリストも設定できるようです。
データを溜めるための入力表には、まだ実用できる段階ではないですが、これから注目される関数です。
こちらのサイトで解説していますので、よかったらどうぞご覧ください。
【Excel】3つ以上のリストを連動させる| パパセンセイ365
まとめ
Excelでプルダウンリストを連動させれば、更に作業効率を上げることができます。
いくつかの方法がありますが、一般的に最も使われるのはINDIRECT関数を使う方法です。
他の方法については若干難易度が上がりますが、興味がある方は試す価値ありです。
次回はテーブルについて触れていきます。
テーブルは大変便利な機能で、これからデータ入力や分析を行う予定のある方は必ずマスターしておくべき機能です。
本日も最後までありがとうございました。