【Excel】UNIQUE関数とSORT関数を組み合わせてリストを自動整理する方法

UNIQUE関数とSORT関数の基本機能

UNIQUE関数とSORT関数は、どちらもExcelの動的配列関数です。これらの関数は、結果を必要とするセル範囲に自動的に「スピル」させます。つまり、数式を1つのセルに入力するだけで、結果が複数のセルに展開されるのです。

UNIQUE関数は、指定した範囲から重複する値を除いた、ユニークな値のリストを返します。例えば、顧客リストから重複する名前を取り除きたい場合などに役立ちます。

一方、SORT関数は、配列または範囲を並べ替えることができます。昇順(小さい順、AからZ)または降順(大きい順、ZからA)を指定して、データを整理することが可能です。

これらの関数はExcel for Microsoft 365で利用可能です。Excel 2019以前のバージョンでは利用できないため注意が必要です。

UNIQUE関数とSORT関数を組み合わせるメリット

UNIQUE関数とSORT関数を組み合わせることで、データ管理におけるいくつかの課題を解決できます。

まず、データソースが更新されても、作成されるリストは常に最新の状態に保たれます。新しいデータが追加されたり、既存のデータが変更されたりしても、数式は自動的に再計算され、重複のないソート済みのリストが生成されます。

次に、手作業によるミスを減らせます。重複の削除や並べ替えを手動で行う場合、誤ってデータを削除したり、並べ替え順序を間違えたりする可能性があります。これらの関数を使えば、そのようなヒューマンエラーのリスクを大幅に低減できます。

さらに、作業時間の短縮につながります。手作業で行っていた定型的な作業が自動化されるため、他の重要な業務に集中する時間を確保できます。特に、頻繁に更新されるリストや、大量のデータを取り扱う場合に、その効果は顕著です。

UNIQUE関数とSORT関数を組み合わせた数式の入力手順

ここでは、実際にUNIQUE関数とSORT関数を組み合わせて、重複を排除し、昇順に並べ替えたリストを作成する手順を説明します。

例として、A列に商品名が入力されており、重複する商品名が含まれているとします。このA列の商品名を、重複なく、かつ50音順(昇順)で表示するリストを作成します。

1. リストを表示したいセルを選択する
   結果を表示したい任意のセルを選択します。ここでは、例えばE1セルを選択します。
2. 数式を入力する
   選択したセル(E1セル)に、以下の数式を入力します。
   

   =SORT(UNIQUE(A1:A100))

   この数式では、「A1:A100」が元データが含まれる範囲です。実際のデータ範囲に合わせて適宜変更してください。

3. Enterキーを押す
   数式を入力したらEnterキーを押します。

数式を入力すると、E1セルから下に、A列の重複しない商品名が昇順(50音順)で自動的に表示されます。これがスピル機能です。

もし、降順(ZからA)で並べ替えたい場合は、SORT関数の第2引数に「-1」を指定します。例えば、商品名を降順で並べ替えたい場合は、以下のようになります。

1. 降順で並べ替える数式を入力する
   E1セルに以下の数式を入力します。
   

   =SORT(UNIQUE(A1:A100),-1)

この数式は、UNIQUE関数で重複を排除した結果を、SORT関数で降順に並べ替えます。

応用:複数列のデータを対象にする方法

UNIQUE関数とSORT関数は、単一の列だけでなく、複数列のデータに対しても利用できます。

例えば、A列に商品カテゴリ、B列に商品名が入っており、商品カテゴリごとに重複しない商品名のリストを作成し、それをさらに商品名で並べ替えたい場合などを想定します。

この場合、UNIQUE関数に複数列の範囲を指定すると、その範囲全体で重複しない行を抽出します。しかし、通常は特定の列(例:商品名)を基準に重複を判断したい場合が多いでしょう。

特定の列を基準に重複を排除し、その結果を並べ替えるには、工夫が必要です。ここでは、A列とB列のデータがあり、B列(商品名)を基準に重複を排除し、その結果をB列の昇順で並べ替える方法を説明します。

1. 補助列を作成する(推奨)
   B列の商品名が重複しているかどうかを判定するための補助列を作成します。例えばC列に以下の数式を入力します。
   

   =IF(COUNTIF(B$1:B1,B1)>1,TRUE,FALSE)

   この数式は、B1セルと同じ値がB列の1行目から1行目までにいくつあるかを数え、1より大きい場合は「TRUE」(重複あり)、そうでなければ「FALSE」(重複なし)を返します。この数式をデータ範囲全体にコピーします。

2. UNIQUE関数とFILTER関数を組み合わせる
   次に、補助列(C列)で「FALSE」となっている行のみを抽出し、さらにB列で並べ替えるための数式を作成します。結果を表示したいセル(例えばE1セル)に以下の数式を入力します。
   

   =SORT(FILTER(A1:B100,C1:C100=FALSE),2,1)

   この数式は以下の処理を行っています。

   • FILTER(A1:B100,C1:C100=FALSE):A1:B100の範囲から、C1:C100がFALSEである行(つまり、B列で重複していない行)を抽出します。
   • SORT(...,2,1):抽出された結果を、2列目(B列)を基準に、昇順(1)で並べ替えます。
3. Enterキーを押す
   数式を入力したらEnterキーを押します。

これにより、A列とB列のデータから、B列(商品名)で重複しない行が抽出され、B列の昇順で並べ替えられたリストがE1セルからスピルされます。

なお、Excel for Microsoft 365では、UNIQUE関数に「match_mode」引数や「exact_mode」引数などを指定することで、より柔軟な重複判定が可能ですが、複数列の組み合わせで複雑な条件を指定する場合は、FILTER関数と組み合わせるのが一般的です。

UNIQUE関数とSORT関数の注意点と制限事項

UNIQUE関数とSORT関数は非常に便利ですが、いくつか注意すべき点や制限事項があります。

Excelのバージョンによる互換性

これらの関数は動的配列関数であり、Excel for Microsoft 365のサブスクリプション版で利用可能です。Excel 2019、2016、またはそれ以前のバージョンでは、これらの関数はサポートされていません。これらのバージョンでファイルを開くと、数式はエラーになるか、正しく計算されない可能性があります。互換性のあるファイル形式(.xlsx)で保存しても、動的配列関数の機能は失われるため、注意が必要です。

スピル範囲の競合

UNIQUE関数やSORT関数は、結果を複数のセルに展開(スピル)します。もし、数式を入力したセルの下や右側に、他のデータが入力されている場合、スピル範囲が競合し、「#SPILL!」エラーが発生します。このエラーが発生した場合は、スピル範囲を妨げているデータを削除するか、数式を入力するセルを変更する必要があります。

大文字・小文字の区別

UNIQUE関数は、デフォルトでは大文字と小文字を区別しません。例えば、「Apple」と「apple」は同じ値として扱われ、どちらか一方が重複とみなされます。もし大文字・小文字を区別して重複を判定したい場合は、UNIQUE関数に「exact_mode:=TRUE」という引数を追加する必要があります。例えば、A1:A100の範囲で大文字・小文字を区別して重複を排除したい場合は、以下の数式を使用します。

=UNIQUE(A1:A100,FALSE,TRUE)

この場合、3番目の引数「TRUE」が大文字・小文字を区別することを意味します。この結果をSORT関数で並べ替える場合は、以下のように記述します。

=SORT(UNIQUE(A1:A100,FALSE,TRUE))

空のセルやエラー値の扱い

UNIQUE関数は、空のセルも1つの値として扱います。もし、データ範囲に空のセルが複数含まれている場合、結果リストにも1つの空のセルが表示されます。エラー値(#N/A, #VALUE!など)も同様に扱われます。これらの値を除外したい場合は、FILTER関数などを組み合わせて、事前にエラー値や空のセルを除外する処理を追加する必要があります。

パフォーマンスへの影響

非常に大量のデータ(数十万行以上)に対してUNIQUE関数やSORT関数を使用すると、Excelの計算に時間がかかる場合があります。パフォーマンスに問題が生じる場合は、データの範囲を絞る、Power Queryなどの別のツールを検討するなどの対策が必要です。

UNIQUE関数とSORT関数の比較

UNIQUE関数とSORT関数は、それぞれ単独でも強力な機能を持っていますが、組み合わせることでさらにその効果を発揮します。ここでは、それぞれの機能と、組み合わせた場合のメリットを比較します。

項目	UNIQUE関数単独	SORT関数単独	UNIQUE関数とSORT関数の組み合わせ
主な機能	重複を排除し、一意の値のリストを生成する	データを昇順または降順に並べ替える	重複を排除したリストを、自動的に並べ替える
データソースの更新への対応	重複のないリストは作成されるが、並び順は元のまま	データソースの変更は反映されない(手動再実行が必要)	データソースの変更が自動的に反映され、常に整理された状態を維持する
手作業の削減効果	重複削除の手間が省ける	並べ替えの手間が省ける	重複削除と並べ替えの両方にかかる手間が省ける
利用シーン例	ユニークな項目リストの作成、ドロップダウンリストの元データ作成	データの一覧表示、ランキング作成、データ分析前の準備	常に最新かつ整理された状態を保ちたいマスターリスト、レポート用データ
Excelバージョン	Microsoft 365	Microsoft 365, Excel 2019, 2021	Microsoft 365

表からもわかるように、UNIQUE関数とSORT関数を組み合わせることで、データソースの変更に自動で追従し、常に整理された状態のリストを維持できる点が最大のメリットです。

SORT関数はExcel 2019や2021でも利用可能ですが、UNIQUE関数はMicrosoft 365限定の機能です。そのため、これらの関数を組み合わせて使用する場合は、Excel for Microsoft 365が必要です。

まとめ

UNIQUE関数とSORT関数を組み合わせることで、Excelでのリスト整理作業を劇的に効率化できます。

重複のないユニークなリストを自動で作成し、さらにそれを常に最新かつ指定した順序で並べ替えることが可能になります。

このテクニックをマスターすれば、データ管理の手間が大幅に削減され、より高度な分析や業務に時間を割くことができるでしょう。

まずは簡単なデータで試してみて、ぜひあなたの業務でも活用してください。さらに応用として、FILTER関数と組み合わせることで、条件付きでリストを整理することも可能です。

📊

Excelトラブル完全解決データベースこの記事以外にも、様々なエラー解決策をまとめています。困った時の逆引きに活用してください。

🏆

超解決 Excel検定 あなたのExcel実務能力を3分で測定！【1級・2級・3級】