この記事で分かること
- UNIQUE 関数と SORT 関数を組み合わせてマスターリストを自動生成する方法
- ドロップダウンリストの元データとして使う手順
- 元データに追加・削除があってもリストが自動更新される仕組みの作り方
- FILTER と組み合わせた条件付きリスト・連動ドロップダウンの作り方
「ドロップダウンリストの選択肢を手動で管理している」「データが増えるたびにリストを更新し忘れる」という方に向けて書いています。
どんな場面で使うか
- 売上データから担当者一覧・部署一覧・商品カテゴリ一覧を自動生成したい
- 入力フォームのドロップダウンリストを元データから自動更新させたい
- 重複を除いた正規のリストをメンテ不要で維持したい
- 連動ドロップダウン（1段目で選んだ内容に応じて2段目が変わる）を数式だけで作りたい
基本説明
この記事では、次のような「売上データ」シートを例に使います（約90行あるうちの先頭部分です）。
| A: 日付 | B: 担当者 | C: エリア | D: 商品名 | E: カテゴリ | F: 数量 | G: 売上金額 |
|---|---|---|---|---|---|---|
| 2025/01/06 | 田中 | 東京 | 商品A | 電子機器 | 10 | 50000 |
| 2025/01/07 | 佐藤 | 大阪 | 商品B | 文具 | 5 | 15000 |
| 2025/01/08 | 鈴木 | 名古屋 | 商品C | 食品 | 8 | 24000 |
| … |
手動管理との違い
従来のドロップダウンリストの元データは、別シートに手動でリストを作っておく方法が一般的でした。
問題点:
- 新しい担当者や部署が増えたときに手動でリストに追加する必要がある
- 追加し忘れるとドロップダウンに出てこなくなる
- リストと実データが乖離しやすい
=SORT(UNIQUE(範囲)) を使うと、元データを参照してリストを自動生成できます。元データに追加・削除があれば、リストも自動で更新されます。
手順
基本の組み合わせ
担当者列から重複なし・昇順のリストを生成する:
=SORT(UNIQUE(B2:B100))UNIQUE(B2:B100)→ B列の値から重複を除いたリストを返すSORT(...)→ それを昇順に並べ替える
結果は数式を入力したセルから縦方向にスピルして展開されます。置き場所はデータ範囲（A〜G列）の外側にします。
I2 セルに入力した場合の結果イメージ:
| I列（I2から下に展開） |
|---|
| 伊藤 |
| 佐藤 |
| 山田 |
| 田中 |
| 鈴木 |
サンプルデータには5人の担当者が延べ90行以上登場しますが、重複が除かれて5件だけになります。 （漢字の並び順は、データの入力方法＝ふりがな情報の有無によって変わることがあります）
ドロップダウンリストの元データとして使う
スピルで展開されたリストを入力規則のリストに指定する場合は、スピル範囲を参照します。
スピル範囲の参照方法:
スピルで展開された範囲全体を参照するには、起点のセルに # をつけます（スピル演算子）。
例: I2 から始まるスピル範囲全体を指定する場合:
=$I$2#先ほどの例なら、=$I$2# と書くだけで I2〜I6 に展開された5件全体を参照できます。担当者が6人に増えれば、参照範囲も自動で I7 まで広がります。
入力規則の設定手順:
- ドロップダウンを設定したいセルを選択
- 「データ」タブ →「データの入力規則」
- 入力値の種類: 「リスト」
- 元の値:
=$I$2#（UNIQUE+SORT を入力したセルの参照 +#）
これで元データに新しい担当者を追加すると、ドロップダウンにも自動で追加されます。
条件付きのリスト生成
特定の条件に合う行からだけリストを生成したい場合は FILTER と組み合わせます。
「エリアが東京の担当者だけ」のリストを作る:
=SORT(UNIQUE(FILTER(B2:B100, C2:C100="東京", "担当者なし")))FILTER(...)→ C列（エリア）が「東京」の行の、B列（担当者）の値を抽出UNIQUE(...)→ 重複を除くSORT(...)→ 昇順に並べ替える
サンプルデータでは、結果は「伊藤」「田中」の2件になります。
サンプル例
連動ドロップダウンを数式で作る
1段目で「エリア」を選ぶと、2段目に「そのエリアの担当者だけ」が表示される連動ドロップダウンです。
前提（セルはすべてデータ範囲 A〜G列の外側に置きます）:
- ここまでと同じ「売上データ」シート（B列: 担当者、C列: エリア）を使う
- I1 セルに1段目のエリアドロップダウン
- I2 セルに2段目の担当者ドロップダウン（I1の値によって変わる）
- K2・L2 セルをリストの置き場所にする
1段目（エリア）のリスト生成（K2 セルに入力）:
=SORT(UNIQUE(C2:C100))このスピル範囲 =$K$2# を I1 の入力規則（元の値）に指定します。
2段目（担当者）のリスト生成（L2 セルに入力）:
=SORT(UNIQUE(FILTER(B2:B100, C2:C100=I1, "担当者なし")))I1 の値（選ばれたエリア）で FILTER をかけてから UNIQUE + SORT します。このスピル範囲 =$L$2# を I2 の入力規則に指定します。
I1 でエリアを選ぶたびに、I2 の選択肢がそのエリアの担当者だけに自動で更新されます。
動作イメージ:
| I1で選んだエリア | I2の選択肢 |
|---|---|
| 東京 | 伊藤、田中 |
| 大阪 | 佐藤 |
| 名古屋 | 鈴木 |
| 福岡 | 山田 |
大文字・小文字の混在を統一してからリストを作る
これは担当者コードや品番など英数字データの場合の話です（サンプルのような漢字の氏名には影響しません）。UNIQUE は大文字・小文字を区別します（「TOKYO」と「tokyo」は別物）。UPPER・LOWER・PROPER で統一してから UNIQUE を使う方法もあります。
=SORT(UNIQUE(UPPER(B2:B100)))よくあるエラー
#SPILL! エラーが出る
SORT・UNIQUE はスピル関数です。結果が展開される先のセルに値が入っているとこのエラーが出ます。スピル先のセルを空にしてください。
ドロップダウンにリストが反映されない
スピル演算子（#）を使わずに固定セル範囲（$I$2:$I$20 など）で指定している場合、リストが増えても増えた分が表示されないことがあります。=$I$2# のように # を使って指定してください。
#CALC! エラーが出る
FILTER の第3引数を省略しており、条件に合う行が0件のときに起きます。第3引数に「”なし”」などを指定しておきます。
「名前が無効」エラーが出る
UNIQUE・SORT・FILTER は Microsoft 365 または Excel 2021 以降が必要です。それ以前のバージョンでは対応していません。
まとめ
=SORT(UNIQUE(範囲))で重複なし・昇順のリストが自動生成できる- スピル範囲全体を参照するには起点セルに
#をつける（=$I$2#） - ドロップダウンの元の値に
=$I$2#を指定すると、元データの変更が自動でドロップダウンに反映される - リストやドロップダウンの置き場所はデータ範囲の外側（I列以降など）にする
- FILTER と組み合わせると「条件付きの動的リスト」や「連動ドロップダウン」も作れる
手動でリストを管理する手間がなくなり、追加・削除の反映漏れを防げます。
対応バージョン: Microsoft 365 または Excel 2021 以降
関連記事
- 【Excel 関数】SORT・UNIQUE・FILTERで変わるリスト管理 — スピル関数入門 — SORT・UNIQUE・FILTER の基本をまとめた入門記事
- 【Excel】動的なドロップダウンリストを作成する方法 — データの入力規則を使ったドロップダウンの基本
- 【Excel】連動プルダウン（2段階ドロップダウン）を作る方法｜INDIRECT関数で簡単設定 — INDIRECT を使った連動ドロップダウンの別アプローチ
サンプルファイル
記事中で使用しているサンプルデータはこちらからダウンロードできます。
