リスト入力を格段に使い易くする、UNIQUE関数とSORT関数

リスト入力は便利ですが "管理" が面倒でした

リスト入力を使えば「入力作業」の効率が上がる

　リスト入力の機能を使えば、"同じ データ を繰り返し入力"するような場合に効率が上がり、しかも入力ミスを防ぐことができます。

　　　

リスト入力の不満点

　とても便利な「リスト入力」の機能ですが、実際に使う際にはいくつかの不満点もありました。

1. リストに表示する「元データ」の作成に手間がかかる
2. 「リスト」に表示する内容の変更に手間がかかる
3. リストに表示される「行数」が増えると探すのが大変

Excelに追加された新しい関数 "UNIQUE関数"と"SORT関数"を使えば、「リスト入力」が使い易くなります

　リスト入力は、入力作業の効率は上がるものの、新たなデータを追加したり、プルダウンした"リスト"から必要なデータを選ぶのが大変でした。

　Excelに新たに追加された"UNIQUE関数"と"SORT関数"は、この関数が持つ画期的な「スピル特性」によりリスト入力の不満点が解消されます。

 
UNIQUE関数でリスト入力用の「元データ」が自動的に作成される

　スピル特性を持つUNIQUE関数を使うことにより、「元データ」が自動的に作成されるだけではなく、自動的に更新されるようになります。 

　　　

SORT関数で、「リスト」が昇順（降順）で並び替わる

　UNIQUE関数は上から順番に抽出するので、"抽出したリストの並び"は、引数にした範囲の並びが反映され、最期に追加したものは"リストの一番下"になってしまいます。

　UNIQUE関数にSORT関数を組み合わせ、ネスト（入れ子）構造にすることで、プルダウンリストの並びを昇順（降順）にすることができます。

　　　

1．リストに表示する「元データ」の"作成"に手間がかかる

　リスト入力に必要な「元データ」の作成は手間がかかる作業です。

　" 男・女 " や " 〇・✖" のような簡単な内容であれば手入力で構いませんが、"商品名"や"顧客名"のようにデータが多い場合は、手入力で「元データ」を作成するのは大変です。

　Excelの機能「重複の削除」の機能を使えば簡単に「元データ」を作成することができます。

　しかし、この重複の削除は"コマンド"なので、リストに"修正（変更）"があるたびに操作が必要でした。

　Excelに新しく追加された「UNIQUE関数」は、画期的な"スピル特性"を持つ関数です。

　このUNIQUE関数を使うことにより、従来はマクロを使わなければできなかった、リスト入力の「元データの自動生成（更新）」が可能になります。

「重複の削除」の機能を使って、「元データ」を作成する

　Excelのコマンド「重複の削除」は、選択範囲内の"重複しているデータを削除"してくれる便利な機能です。

　この機能を使えば、"既に入力されている表"を使って簡単に「元データ」を作成できます。

1. 重複しているデータを削除したい"範囲"を選択
2. 「データタブ」を選択
3. 「重複の削除」をクリック
4. 重複の削除ダイアログで「商品名」にチェックを入れる
5. 「OK」をクリック
6. 削除した内容を表すダイアログで「OK」をクリック

　　　

「UNIQUE関数」を使って、「元データ」を作成する

　「元データ」の作成は"重複の削除"の機能でもできますが、重複の削除は"コマンド"なので、元の「表」に変更があるたびにやり直しが必要でした。

　UNIQUE関数は、数式として入力しておけば「表」に変更があっても自動的に更新されるので、その都度やり直しの必要がありません。

 "E4" のセルに、UNIQUE関数を入力する

　　　

1. 引数「配列」に元データにしたいリストの範囲を選択
2. 「OK」をクリック　　　

　　　

　UNIQUE関数は "スピル特性" を持つ関数です。

　　"スピル" の意味は「溢れる（こぼれる）」で、条件に合致する「結果が複数ある場合」は"１つのセル"に数式を入力するだけで、数式がないセルにも"溢れる"ように結果が表示されます。

　今回の場合もUNIQUE関数を設定するのは"E4のセル"のみで、数式が入力されていない"E5～E13" の部分には「ゴースト」として結果表示されます。

　※ ゴーストが表示される部分に"データ"があると、エラーになります

2．「リスト」に表示する内容の"変更"に手間がかかる

　UNIQUE関数を使えば「表」のデータを変更（修正）すると「元データ」が自動更新されます。

　しかし、データを追加した場合には、UNIQUE関数の引数「配列」の範囲が変わるので、そのままでは"変更"が反映されません。

　この問題は"「表」をテーブル化"しておき、「構造化参照」を使って引数「配列」を設定することにより、「表」のデータを変更（追加）しても引数「配列」を変更する必要がなくなります。

構造化参照をつかえば、抽出する範囲の修正が不要になる

　UNIQUE関数を使うことにより、引数「配列」に指定した参照範囲から"自動的に重複していないデータを作成"することができます。

　しかし、このままではデータの変更は自動で更新されますが、データが追加された場合には引数「配列」の参照範囲を手作業で変更する必要があります。

　引数「配列」に"構造化参照"を使うことにより、データが追加されても自動で参照範囲を広げることができます。

「表」を"テーブル化"する

1. 「リスト」内のどこか１つのセルを選択する
2. 「挿入タブ」を選択
3. 「テーブル」をクリック
4. テーブルの作成ダイアログで、「テーブルに変換する･･･」の範囲が正しく選択されているのを確認
5. 「OK」をクリック

　　　

「テーブル」の"名前を変更"する

　作成した「テーブル」には、自動で「テーブル２」のように作成順に名前が付きますが、分かりやすくするために名前を変更します。

1. "テーブルのどこか１カ所を選択状態"にして、名前を編集する"テーブル"を選択します
2. 「テーブルデザイン」タブを選択します
3. テーブル名を「商品名」にします

　　　

UNIQUE関数の引数「配列」をテーブル名にする

　UNIQUE関数の引数「配列」を"テーブル名"にすることで「構造化参照」になり、リストの内容が追加されても、"引数" を修正する必要がなくなります。

1. 引数"配列"のボックスに、テーブル名の「商品名」を入力します
2. 「OK」をクリック

　　　

表を「テーブル化」して作業効率を上げる - Excelの機能を活用して、事務作業の省力化や経営分析をする

リストを追加すると、「元データ」に反映される

　商品名のリストの最後に「その他」を加えると、テーブル「商品名」の範囲が自動的に広がり、自動的にリスト入力の「元データ」に追加されます。

　　　

3．リストに表示される「行数」が増えると探すのが大変

 　UNIQUE関数は、引数「配列」の範囲を上から下へ検索するので、抽出結果も元の範囲の順番のままです。

　そのため追加されたものは「一番下」になってしまいます。

　そして、「リスト入力」のボックスに表示されるデータが多くなると、"規則性が無い"ので探すのが大変です。　

　　　

SORT関数を使って、リスト表示を「昇順」にする

　新しく追加された"スピル特性"を持つ「SORT関数」を使って、UNIQUE関数を囲み「ネスト（入れ子）構造）にすることで、UNIQUE関数で抽出した内容を "昇順" にすることができます。

　　　

作成した「元データ」を使って、リスト入力の設定をします

　リスト入力用の「元データ」がUNIQUE関数で作成されたので、リスト入力の設定をします。

　今回作成した「元データ」は、SORT関数（UNIQUE関数）が入力されているセルの「１つ」以外は"スピル特性"による「ゴースト」です。

　そのまま数式が入力されている"E4"のセルを「元データ」に指定しただけでは「ゴースト」部分が表示対象にならないので、リスト入力の設定時に"スピル範囲演算子「＃」"を使って「ゴースト部分」も「元データ」の範囲に指定します。

スピル範囲演算子"#"を使って、「元の値」の設定をする 

　スピル特性を持つ"UNIQUE関数で抽出されたデータ"は、数式が入力されているセル以外は、実体のない「ゴースト」として表示されています。

　スピル範囲演算子「＃」を使うことにより、「ゴースト」の部分を含む"スピル範囲全体"を参照することができます。

　　

1. "リスト入力"を設定するセルを選択
2. 「データ」タブを選択
3. 「データの入力規則」をクリック
4. データの入力規則ダイアログで「設定」タブを選択
5. 入力値の種類で「リスト」を選択
6. 元の値に数式 "＝E４＃" を入力
7. 「OK」をクリック

 　　　

"リスト入力"が設定されました　　

　"UNIQUE関数"、"SORT関数"、"構造化参照" を使うことにより、元の「表」が変更・追加・削除されると、それが「リスト」に反映されるようになりました。

　　　

　このようにExcelに新しく追加された、UNIQUE関数とSORT関数を使うことで、リスト入力に関する不満点が解消され、格段に使い易くなります。

　"リスト"の内容が自動更新されるのも、内容が並び替わるのも、新しい関数が持つ画期的な機能「スピル特性」により実現可能になりました。

UNIQUE関数を使って、リスト入力の「元の値」を自動更新する - Excelの機能を活用して、事務作業の省力化や経営分析をする

SORT関数とUNIQUE関数を使って、リスト入力の「元データ」を並べ替える - Excelの機能を活用して、事務作業の省力化や経営分析をする

"２つのリストを連動させる" と、リスト入力の効率が上がる 

　リスト入力の効率を上げる方法は他にもあります。

　２つのリスト（項目）のリスト入力を連動させることにより、１つ目のリスト（項目）で選んだ内容により、２つ目のリスト（項目）のリスト入力で表示字する内容を変えることができます。

「県」のリストと「市」のリスト（項目）を連動させる

　「県」の項目でリスト入力から"山口県"を選択すると、次の「市」の項目のリスト入力には"山口県内"の「市」しか表示されなくなります。

　　　

「部署名」と「担当者」のリスト（項目）を連動させる

　「部署」を選択すると、"その部署に属する"「担当者」だけがリストに表示させるようにすることができます。

　　　

「リスト入力」で、２つのフィールドを連動させて表示する（INDIRECT関数） - Excelの機能を活用して、事務作業の省力化や経営分析をする

k-ohmori9616.hatenablog.com