今回は、Excelの動的配列のUNIQUEとSORTで一覧を整理する方法を紹介します。
UNIQUEとSORTでできること
Excelで名簿、商品一覧、部署一覧、担当者一覧などを扱うとき、重複を取り除いた一覧を作りたい場面があります。さらに、その一覧を昇順や五十音順で並べたい場合もあります。
Excel 動的配列 UNIQUE
SORTを使うと、元データから重複しない値を取り出し、並べ替えた一覧を自動で作れます。元データが増えたときも、範囲やテーブルを適切に指定していれば結果が更新されます。
たとえば、売上一覧から担当者名の一覧を作り、プルダウンの候補に使うことができます。部署名や商品カテゴリの一覧を作る場合にも便利です。
動的配列の基本
動的配列関数は、1つのセルに入力した数式の結果が複数セルへ広がる仕組みです。この広がる範囲をスピル範囲と呼びます。UNIQUEやSORTの結果も、必要な行数だけ自動で表示されます。
- 結果が複数セルに広がる
- 元データが変わると結果も変わる
- 出力先に値があるとエラーになる
- 結果範囲に直接入力しない
- テーブルと組み合わせると更新しやすい
出力先の下や右に別のデータがあると、結果を表示できません。UNIQUEやSORTを使う場所には、あらかじめ余白を確保します。
元データはテーブル化する
元データをExcelテーブルにしておくと、行が増えたときに数式の参照範囲が自動で広がります。担当者列や部署列をテーブルの列名で参照できるため、式の意味も分かりやすくなります。
通常のセル範囲を使う場合は、追加データが範囲外にならないように注意します。定期的に更新する一覧では、テーブル化しておくほうが管理しやすくなります。
UNIQUEで重複しない一覧を作る
UNIQUE関数は、指定した範囲から重複しない値を取り出します。名簿の部署列から部署一覧を作る、売上一覧の商品カテゴリを取り出す、といった使い方ができます。
- 元データの列を確認する
- 一覧を表示したい空きセルを選ぶ
- UNIQUE関数で対象列を指定する
- 重複しない値が表示されるか確認する
- 空白が混ざっていないか見る
- 必要に応じてSORT関数と組み合わせる
元データに空白が含まれていると、結果にも空白が出ることがあります。プルダウン候補に使う場合は、空白を除外する方法も検討します。
表記ゆれに注意する
UNIQUEは、表記が違う値を別のものとして扱います。「営業部」と「営業」、「東京」と「東京都」のように表記が混ざると、別々の候補として表示されます。
一覧を作る前に、元データの表記をそろえます。入力規則やマスター表を使って、部署名やカテゴリ名を選択式にすると表記ゆれを減らせます。
SORTで並べ替える
SORT関数は、指定した範囲を並べ替えます。UNIQUEで取り出した一覧をSORTで包むと、重複しない一覧を並べ替えて表示できます。
- 部署一覧を五十音順にする
- 商品カテゴリを昇順にする
- 担当者名を並べ替える
- コード一覧を番号順にする
並び順が整っていると、プルダウン候補や確認用一覧として使いやすくなります。コードと名称をセットで並べたい場合は、複数列を対象にする方法もあります。
独自の並び順が必要な場合
部署の表示順や商品カテゴリの優先順など、単純な昇順ではない並び順が必要なことがあります。その場合は、並び順を示す番号列をマスター表に持たせる方法が扱いやすくなります。
SORTだけで無理に対応するより、並び順用の列を作ると後から変更しやすくなります。
プルダウン候補に使う
UNIQUEとSORTで作った一覧は、入力規則の候補として使えます。元データから候補一覧を自動で作れば、新しい部署や担当者が追加されたときにも候補を更新しやすくなります。
ただし、スピル範囲を入力規則に指定する場合は、出力先の範囲が安定していることが大切です。候補一覧用のシートを作り、そこにUNIQUEとSORTの結果を表示すると管理しやすくなります。
- 候補一覧用シートを用意する
- UNIQUEとSORTで一覧を作る
- 空白や表記ゆれを確認する
- 入力規則で候補範囲を指定する
- 元データ更新後に候補を確認する
候補一覧を作る場所に手入力のメモを置くと、スピル範囲と重なることがあります。候補一覧シートは、関数の出力専用にすると安全です。
エラーを避けるポイント
動的配列では、出力先の範囲に値があるとスピルエラーが出ます。結果が何行になるかは元データによって変わるため、周囲に十分な空白を取ります。
- 出力先の下を空ける
- 結果範囲に直接入力しない
- 元データの表記をそろえる
- 空白を除外する処理を検討する
- テーブル参照で範囲を管理する
動的配列の結果は便利ですが、手入力の表と混ぜると管理が難しくなります。自動で作る一覧と手で管理する一覧は、役割を分けます。
複数列の一覧を作る場合
UNIQUEは1列だけでなく、複数列の組み合わせにも使えます。たとえば、部署名と担当者名の組み合わせ、商品カテゴリと商品名の組み合わせを重複なしで取り出したい場合に便利です。
複数列を扱う場合は、どの列の組み合わせを1つの候補として扱うのかを決めます。部署だけなら1列で十分ですが、部署と担当者をセットで管理したい場合は2列を対象にします。SORTと組み合わせると、部署順、担当者順の一覧も作れます。
更新時の確認ポイント
元データが増えたときは、候補一覧が正しく広がっているか確認します。テーブル参照を使っていれば更新されやすいですが、出力先の下に別データがあるとスピルエラーになります。
共有ブックで使う場合は、候補一覧の近くに手入力しないルールを決めると安全です。候補一覧用シートを保護する方法もあります。
まとめ
ExcelのUNIQUEとSORTを使うと、元データから重複しない一覧を作り、並べ替えて表示できます。部署一覧、担当者一覧、商品カテゴリ一覧など、候補リストや確認用一覧に向いています。
元データはテーブル化しておくと、行が増えたときにも対応しやすくなります。表記ゆれや空白があると結果に影響するため、入力規則やマスター表で整えることも大切です。
Excel 動的配列 UNIQUE
SORTは、一覧作成を自動化するための便利な組み合わせです。スピル範囲を空け、候補一覧用の場所を分けておくことで、日常のデータ管理に使いやすくなります。