いまさら聞けないExcelの使い方講座

【Excel】XLOOKUP関数で販売リストから全種類の商品を商品番号順に並べて取り出すテク

  • 今井 孝

2025年8月6日 06:55

UNIQUE関数にSORT関数を組み込むだけでOK

UNIQUE関数を使うならSORT関数も組み合わせよう

　同じ顧客が購入を繰り返す注文履歴や、取扱商品の販売履歴を管理する販売データなどでは、表内に同一の商品番号などのコードが繰り返し記録されていることが多いでしょう。例えば、販売履歴が月ごとに集計されていて、このデータを元に商品ごとの売れ行きを分析する場合は、重複を取り除いた商品名を調べることになります。

例えば、今月に販売した商品を調べるには？

　商品名をコピペして［重複の削除］の機能を利用しても構いませんが、一意（ユニーク）なデータを取り出したいならUNIQUE関数が便利です。引数もセル範囲を指定するだけなので手軽に利用できます。

UNIQUE関数を利用して一意（ユニーク）な商品名を取り出しました。セルH2には「=UNIQUE(C2:C31)」と入力してあります（①）

　しかし、データを分析する場合、重複を取り除くことだけが目的ではありません。商品番号をキーとして処理するほうが都合の良いことが多いでしょう。今回はUNIQUE関数とSORT関数の組み合わせ、XLOOKUP関数を使ったデータの抽出のテクニックを紹介します。

SORT関数を組み合わせる

　まず、SORT関数の構文を紹介します。1つの列を昇順で並べ替えるなら、最初の［範囲］を指定するだけです。UNIQUE関数の引数としてSORT関数を指定すると、［範囲］に指定したセル範囲を昇順で並べ替えた結果から一意のデータが取り出されることになります。ここでは、商品番号（セルB2～B31）を対象にUNIQUE関数とSORT関数を組み合わせてみます。

SORT関数の構文。引数［範囲］を［基準］の列、または行を基準として［順序］（1：昇順、-1：降順）の順に並べ替えます。［基準］の省略時は［範囲］の1列（行）目と見なされます。［データの並び］は「TRUE」で行方向（右方向）、「FALSE」で列方向（下方向）となります（省略時は「FALSE」）
セルH2に「=UNIQUE(SORT(B2:B31))」（②）と入力した結果です。引数［範囲］に指定したセル範囲が昇順に並び、UNIQUE関数で一意のデータだけが取り出されてます

XLOOKUP関数で参照する

　H列に取り出した商品番号をキーとして、対応する商品名をXLOOKUP関数で取り出してみましょう。セルH2の結果はスピルで表示されているので、引数［検索値］に「H2#」のように指定すると、まとめて結果を取り出せます。

　「#」はスピル演算子と呼ばれ、「H2#」は”セルH2のスピルの結果”を指します。結果として、今月に販売された一意の商品名を商品番号順で取り出すことができます。

セルI2に「=XLOOKUP(H2#」と入力すると（③）、スピルで表示されるセルH2に入力された数式の結果（④）が対象になっていることがわかります
続けて、「,B:B,C:C)」（⑤）と指定します。
商品番号に対応する商品名をまとめて取り出せました

　なお、セルH2の数式をXLOOKUP関数に組み込んでも構いません。商品番号順などに整えた一意のデータは、集計やレポート作成の土台として役立ちます。ぜひ活用してみてください。 

=XLOOKUP(UNIQUE(SORT(B2:B31)),B:B,C:C)
「=XLOOKUP(UNIQUE(SORT(B2:B31)),B:B,C:C)」と入力しても（⑥）同じ結果になります