「リスト入力」を使いこなす（その３）

「リスト入力」を使いこなせば、入力効率が上がります

（その２）では、「リスト入力」をさらに使いやすくする方法を紹介します

　とても便利な「リスト入力」ですが、"１度に表示される行数は「８行」" までです。

　元データの数が多い場合はリストをスクロールさせてプルダウンリストの中から必要なデータを探すのが大変になります。

　　

※ 「プルダウンリスト」に表示される "データ" の行数は "マクロ" を使えば変更できますが、とても複雑で現実的ではありません

「プルダウンリスト」の表示方法を変更する

　「SORT関数」や「INDIRECT関数」を使って「プルダウンリスト」の "表示方法を変更" することで、から必要なデータを選択するのが簡単になります。

１．"プルダウンリスト" の内容を並べ替える（SORTする）

　「UNIQUE関数」と「SORT関数」を組み合わせることで、「プルダウンリストの内容を並べ替える」ことができます。

　「UNIQUE関数」の"元データの数式" に「SORT関数」を使うことで、"元データ" を並べ替える（SORTする）ことができます。

　並べ替えることで "データが探しやすくなる" だけではなく、通常「最後に登録したDATAは、リストの最後に表示される」ことがなくなります。

　「使いたい」から登録したのに、そのデータが最後に表示されてしまいスクロールさせて探すのは不合理です。

「プルダウンリスト」の表示は、「元データ」と同じ並びになる

　UNIQUE関数とSORT関数を組み合わせることで、UNIQUE関数で作成した「元データ」が "並び変わった状態" にすることができます。

　　

リスト入力を格段に使い易くする、UNIQUE関数とSORT関数 - 事務作業の省力化や資料作成に役立つ、Excelの使い方を紹介

「元データ」の範囲を、"テーブル化"すればデータが増えても修正不要

　「UNIQUE関数」の、引数 "配列" を「テーブル名（テーブル化されている範囲に付けた名前）」にすることで、「元データ」にデータを追加（削除）しても数式を変更する必要がなくなります。

1. 「元データ」のどこか１カ所を選択（アクティブセルを置く）します
2. 「挿入」タブを選択
3. 「テーブル」をクリック
4. 「テーブルの作成」ダイアログで、「元データ」の範囲が正しく選択されているのを確認（※「先頭行をテーブルの見出しとして使用する」にチェックを入れる）
5. 「OK」をクリック

　　

分かりやすくするために、「テーブル名」を変更

　数式を分かりやすくするために、"テーブル名" を「元データ」に変更します。

1. 「元データ」の範囲に、アクティブセルを置きます
2. 「テーブルデザイン」タブを選択
3. テーブル名のボックスに「元データ」と入力

　　

「関数の挿入」ダイアログを使って、「UNIQUE関数」を入力

　「関数の挿入」ダイアログは、面倒なので敬遠される場合もありますが慣れるととても便利な機能です。

　関数を検索する方法は「分類」が分かれば簡単に探すことができますが、慣れるまでは「分類」が少しわかりにくい（※ 理解しずらいものがある）かもしれません。

　名称が分かっているときは、"関数の分類" を「すべて表示」にし、探している関数の１文字目（英文字）を入力すると、その文字から始まる関数が表示されるので素早く探すことができます。

「数式」を簡単に入力するテクニック（その１） - 事務作業の省力化や資料作成に役立つ、Excelの使い方を紹介

1. 数式（関数）を入力する部分にカーソル（アクティブセル）を置く
2. 「 fx 」ボタンを押して、"関数の挿入ダイアログ" を開く
3. 関数の分類で「すべて表示」を選択し、英文字の「 U 」を入力
4. "U" から始まる関数が表示されるので「UNIQUE関数」を選択
5. 「OK」をクリック

　　

「名前の貼り付け」ダイアログを使う

　UNIQUE関数の引数「配列」に「テーブル名」を使う際、「名前の貼り付け」ダイアログを使えば、間違えることなく簡単に入力することができます。

※ うまくいかない場合は、いったんダイアログを終了させてやり直すとうまくいく場合があります

1. 引数「配列」のボックスに、カーソルを置いて、ファンクションキーの『 f3』を押す
2. 「名前の貼り付け」ダイアログが開くので、リストの中から「元データ」を選択
3. 「OK」をクリック　　

　　

「UNIQUE関数」の "スピル特性" で、「元データ」が表示された

　並べ替えた（SORTした）「元データ」を表示するセルに「UNIQUE関数」のスピル機能で "重複しないデータ" が表示されます

　※ 数式（関数）を入力したセルの下には、"充分な空白" を空けます

　　

「SORT関数」で、元データを"並べ替える"

　SORT関数を組み合わせることで、UNIQUE関数で抽出された "内容" を並べ替えることができます。

　　

　　

リスト入力の"元データ"の指定には「スピル範囲演算子『＃』」を使う

　リスト入力の「元データ」に、UNIQUE関数で作成したデータを使う場合は注意が必要です。

　UNIQUE関数で作成したデータは、 "数式（UNIQUE関数）が入力されたセル" 以外は "ゴースト" として表示されているので、データ数が増・減します。

　データ数が増減する範囲を「L3:L18」のようにセル番地で指定してしまうと、データ数が増減するたびに数式を修正する必要があり、UNIQUE関数を使った意味がありません。

　データ数が増減する「ゴースト」を範囲指定するためには「スピル範囲演算子『 ＃ 』」を使うことでデータの増減に対応するので「スピル特性」を生かすことができます。

元データの範囲指定に、"スピル範囲演算子『＃』"を使う

　UNIQUE関数を入力した「セル番地『 $L$3』」の後に、「スピル範囲演算子『 ＃ 』」を付けることで「ゴーストの範囲が自動的に選択」され、データの数が増減に合わせて「元データ」の範囲が増減します。

　　

２．"プルダウンリスト" が、自動的に絞り込まれる（２段階表示）ようにする

　リスト入力の「元の値」に、INDIRECT関数を使うことにより "２つの項目を関連付け" １つ目の項目に入力した内容に対応して、２つ目の項目のリスト入力で表示される内容を絞り込むことができます。

「地域」に入力した内容に応じて「県名」の "プルダウンリストの内容を変える" 

　"地域の項目" に「関西」を入力すると、"県名の項目" のプルダウンリストの内容が［関西地域］だけになります。

　　

項目「地域」の内容で、「元の値」の範囲が変わる仕組み

　INDIREC関数により、地域の項目（『 J3 』）に入力されている文字により、リスト入力の「元の値」の参照範囲が変化します。

　① 「地域」の項目に入力されている内容が、INDIREC関数で「引数」になる

　② セル番地『 J３ 』には「文字列『関西』」が入力されている

　③ 文字列『関西』は範囲 "E3:E12" に付けた「名前」なので、その範囲が引数になる

　　

INDIRECT関数で、「範囲に付けた名前」を"引数"にする

　INDIRECT関数は、『 B2:H2 』のセル範囲に付けた「地域」と言う『名前』を引数にすることができます。

INDIREC関数の"引数"に「名前」を使う

　「 =INDIRECT(地域) 」を、他の関数の引数に使えば『 B2:H2 』と認識されます。

　　

「地域に入力された文字」が、「県名の元の値」になる

　「地域」の項目に入力されている「文字」が、INDIRECT関数により「県名」の項目のリスト入力の「元の値」の「範囲」になります。

　　

「地域」のそれぞれの範囲に「名前」を付ける

　地域別のデータの、それぞれの "範囲" に名前を付けます。

　１つ１つ名前を付けていくのは面倒ですが、「選択範囲から作成」のコマンドを使うことで、１度の操作で全ての地域に名前を付けることができます。

　　

「選択範囲から作成」で、範囲に名前を付ける

　"範囲" に名前を付ける方法は複数ありますが、「選択範囲から作成」のコマンドを使えば、一度に複数の範囲に名前を付けることができます。

1. 「表全体」を範囲選択します（※ 項目名も含める）
2. 「数式」タブを選択
3. 「選択範囲から作成」のコマンドを選択
4. 「選択範囲から名前...」のダイアログで、「上端行」にチェックを入れる
5. 「OK」をクリック

　　

項目「地域」の内容によって、「県名」のリストの内容が変わる

　リスト入力の「元の値」に、INDIREC関数を使うことにより「地域」と「県名」の項目が関連付けられ、リスト入力が２段階になりました。

k-ohmori9616.hatenablog.com