Excelで重複しないリストを自動で更新したい場面は多いでしょう。特定の条件に合うデータだけを抽出し、さらに並べ替える必要もあります。しかし、手作業での更新は手間がかかり、ミスも発生しやすいです。この記事では、Excelの動的配列関数であるUNIQUE、SORT、FILTERを連携させることで、これらの作業を自動化する実践例を解説します。
UNIQUE関数で重複を排除し、FILTER関数で条件に合うデータを抽出し、SORT関数で並べ替えることで、常に最新の状態のリストを簡単に作成できます。これにより、データ管理の効率が大幅に向上します。
ADVERTISEMENT
目次
UNIQUE・SORT・FILTER関数の概要と連携のメリット
Excelの動的配列関数は、複数のセルに結果を自動的に展開する画期的な機能です。UNIQUE関数は、指定した範囲から重複しない一意の値を返します。例えば、顧客リストから重複している氏名を除外し、ユニークな氏名だけを取得したい場合に役立ちます。
FILTER関数は、指定した条件に合致する行または列を抽出します。例えば、特定の地域に住む顧客、あるいは特定の商品を購入した顧客のリストを作成する際に使用します。SORT関数は、指定した範囲のデータを並べ替えます。昇順、降順、あるいは特定の列を基準にした並べ替えが可能です。
これらの3つの関数を連携させることで、重複がなく、特定の条件で絞り込まれ、さらに並べ替えられたリストを動的に作成できます。データが更新されるたびに、リストは自動的に再計算され、常に最新の状態が保たれます。これにより、手作業による更新作業が不要になり、ヒューマンエラーのリスクも低減します。
3関数連携による自動更新リスト作成の実践例
ここでは、営業担当者ごとの受注リストから、特定の商品を購入した担当者の一覧を重複なく抽出し、担当者名で昇順に並べ替える例を解説します。以下の表を元データとします。A1セルからE51セルにデータが入っていると想定してください。A列に「商品名」、B列に「担当者名」、C列に「受注日」、D列に「数量」、E列に「金額」が入っています。
1. UNIQUE関数で重複しない担当者名リストを作成する
まず、元データから重複しない担当者名の一覧を作成します。F1セルに以下の数式を入力してください。
- UNIQUE関数で担当者名を抽出
F1セルに「=UNIQUE(B2:B51)」と入力します。これにより、B2からB51の範囲にある担当者名から重複を除いたリストがF列に自動的に展開されます。
2. FILTER関数で特定の商品を購入した担当者のみを抽出する
次に、FILTER関数を使用して、特定の商品(例:「商品A」)を購入した担当者のみを抽出します。G1セルに以下の数式を入力してください。
- FILTER関数で「商品A」購入者のみを抽出
G1セルに「=FILTER(B2:B51, A2:A51=”商品A”)」と入力します。これにより、A列が「商品A」である行のB列(担当者名)のみがG列に抽出されます。
3. SORT関数で抽出した担当者名を並べ替える
最後に、FILTER関数で抽出した担当者名を、担当者名(B列)で昇順に並べ替えます。H1セルに以下の数式を入力してください。
- SORT関数で担当者名を昇順に並べ替え
H1セルに「=SORT(FILTER(B2:B51, A2:A51=”商品A”))」と入力します。これにより、FILTER関数で抽出された担当者名が、担当者名で昇順に並べ替えられてH列に表示されます。
4. 3関数を連携させて自動更新リストを作成する
これらの関数を連携させることで、さらに高度なリスト作成が可能です。例えば、「商品A」を購入した担当者の一覧を重複なく、かつ担当者名で昇順に並べ替えたリストを一度に作成します。I1セルに以下の数式を入力してください。
- UNIQUE, FILTER, SORT関数を連携
I1セルに「=SORT(UNIQUE(FILTER(B2:B51, A2:A51=”商品A”)))」と入力します。この数式は、まずFILTER関数で「商品A」を購入した担当者名を抽出し、次にUNIQUE関数で重複を排除し、最後にSORT関数で担当者名を昇順に並べ替えます。結果はI列に自動的に展開されます。
5. 条件をセル参照にして柔軟性を高める
数式内の「商品A」を直接入力するのではなく、セル参照にすることで、条件を変更したい場合に数式を編集する手間が省けます。例えば、J1セルに抽出したい商品名を入力し、I1セルの数式を以下のように変更します。
- セル参照で条件を指定
I1セルに「=SORT(UNIQUE(FILTER(B2:B51, A2:A51=J1)))」と入力します。J1セルに別の商品名(例:「商品B」)を入力すれば、その商品を購入した担当者のリストが自動的に更新されます。
動的配列関数使用時の注意点とトラブルシューティング
1. #SPILL!エラーが発生する場合
動的配列関数は、結果を展開するための十分なスペースがない場合に#SPILL!エラーを返します。これは、数式を入力したセルの下または右側に、既にデータが存在する場合に発生しやすいです。
対処法
- 展開先のセルをクリアする
数式を入力したセルから結果が展開される範囲(数式バーに表示されるスピル範囲)に、他のデータがないか確認してください。もしデータがあれば、そのデータを削除するか、別の場所に移動させてください。 - 数式を別の場所に移動する
結果を展開したい領域に十分な空きスペースがあることを確認し、数式を別のセルに入力し直してください。
2. 期待通りの結果が得られない場合
FILTER関数で条件を指定する際に、データ型やスペルミスがあると、期待通りの結果が得られないことがあります。例えば、数値と文字列の混在や、前後の空白などが原因で条件に合致しない場合があります。
対処法
- データ型を確認する
条件として指定している列(例:A列の商品名)と、条件の値(例:「商品A」)のデータ型が一致しているか確認してください。必要であれば、TRIM関数やVALUE関数などを使用してデータ形式を統一します。 - 空白文字の有無を確認する
データの前後に不要な空白が含まれていないか確認してください。TRIM関数を使って空白を除去してから比較すると良いでしょう。 - スペルミスを確認する
条件として指定している文字列(例:「商品A」)にスペルミスがないか、大文字・小文字が一致しているか確認してください。
3. Excelのバージョンによる制限
UNIQUE、SORT、FILTERといった動的配列関数は、Excel for Microsoft 365、Excel 2021以降で利用可能です。Excel 2019以前のバージョンではこれらの関数はサポートされていません。
対処法
- バージョンを確認する
お使いのExcelのバージョンが動的配列関数に対応しているか確認してください。「ファイル」>「アカウント」で確認できます。 - 代替手段を検討する
古いバージョンをご利用の場合は、Power QueryやVBA、あるいは従来の配列数式(Ctrl+Shift+Enterで確定)を使用して同様の処理を行う必要があります。
ADVERTISEMENT
UNIQUE・SORT・FILTER関数と他の関数との連携
UNIQUE、SORT、FILTER関数は、他のExcel関数とも組み合わせて使用することで、さらに強力なデータ分析ツールとなります。例えば、SUMIFS関数やCOUNTIFS関数と組み合わせることで、条件に合致するデータの合計値や件数を動的に取得できます。
1. SUMIFS関数との連携
「商品A」を購入した担当者ごとの合計金額を算出する例です。まず、J1セルに抽出したい商品名(例:「商品A」)を入力します。K1セルに以下の数式を入力します。
- UNIQUE, FILTER, SUMIFS関数を連携
K1セルに「=SUMIFS(E2:E51, A2:A51, J1, B2:B51, UNIQUE(FILTER(B2:B51, A2:A51=J1)))」と入力します。この数式は、J1セルで指定した商品を購入した担当者(UNIQUE+FILTERで取得)ごとに、E列の金額を合計します。結果はK列に展開されます。
2. COUNTIFS関数との連携
「商品A」を購入した担当者ごとの購入回数を算出する例です。L1セルに以下の数式を入力します。
- UNIQUE, FILTER, COUNTIFS関数を連携
L1セルに「=COUNTIFS(A2:A51, J1, B2:B51, UNIQUE(FILTER(B2:B51, A2:A51=J1)))」と入力します。この数式は、J1セルで指定した商品を購入した担当者(UNIQUE+FILTERで取得)ごとに、B列の担当者名が一致する件数をカウントします。結果はL列に展開されます。
3. XLOOKUP関数との連携
XLOOKUP関数は、動的配列関数ではありませんが、UNIQUE、SORT、FILTER関数と組み合わせて、より柔軟なデータ検索や集計を行うことができます。例えば、特定の商品を購入した担当者名リストから、その担当者の別の情報(例:受注日)を検索するようなケースです。
- XLOOKUP関数と連携した検索
例えば、I1セルに「=SORT(UNIQUE(FILTER(B2:B51, A2:A51=J1)))」で取得した担当者名リストがあります。M1セルに「=XLOOKUP(I1, B2:B51, C2:C51)」と入力すると、I1の担当者名に対応するC列の受注日(最新の受注日とは限らない点に注意)が返されます。より複雑な検索には、XLOOKUP関数の引数を調整したり、他の関数と組み合わせたりする必要があります。
まとめ
ExcelのUNIQUE、SORT、FILTER関数を連携させることで、重複を排除し、条件で絞り込み、並べ替えられたリストを自動更新できるようになりました。これにより、手作業によるデータ更新の手間とミスを大幅に削減できます。
今回解説した数式は、J1セルに商品名を入力するだけで、I列に該当する担当者リストが自動生成されます。さらに、SUMIFS関数やCOUNTIFS関数と組み合わせることで、集計作業も効率化できます。ぜひ、これらの動的配列関数を活用して、Excelでのデータ管理をよりスマートに進めてください。