いまさら聞けないExcelの使い方講座
【Excel】XLOOKUP関数では無理？ 販売数が同じ複数の製品名をまとめて取り出すテク
2025年3月12日 06:55
ランキングに該当するデータをすべて取り出す
以前の記事で、LARGE関数を利用したトップ3を求める定番ワザを紹介しました。
トップ3のデータを取り出す例でしたが、実際の業務ではデータとあわせて、対応する値（以下のサンプルでは製品名）も取り出したいことがあるでしょう。
例えば「25」に対応する「製品B」を自動的に取り出すには、どうしたらいいでしょうか？ もちろん手入力はしません。第3位の「製品C」「製品D」のように、同順位の製品が存在する場合もありますよね。今回は、LARGE関数とあわせて覚えておきたいテクニックを紹介します。
XLOOKUP関数では同順位の値は取り出せない
トップ3を取り出すために使っているLARGE関数について、簡単におさらいしておきましょう。セルA11～A13には、第1～3位を指定するために「1」「2」「3」と数値が入力してあります。セルB3～B7を対象として、トップ1を調べるためにセルA11の「1」を参照しています。
LARGE関数の詳しい使い方は、こちらの記事を参考にしてください。
同様に2、3位のデータを取り出したとして、それぞれに対応する値をXLOOKUP関数で探してみましょう。なお、VLOOKUP関数は“検索値を探す対象列が左端に用意しておく”制約があるので使えません。
第3位の製品名を求めるために入力した数式は「=XLOOKUP(B13,$B$3:$B$7,$A$3:$A$7)」です。セルB13の「21」を検索値として、セルB3～B7を探し、対応する値をセルA3～A7から取り出す意味になります。
セルB3～B7を対象にして、先頭から「21」を探すと「製品C」となります。同順位の「製品D」は取り出せませんよね。オプションの引数［検索モード］に末尾から探す「-1」と指定しては？と考えた人もいるかもしれませんが、同順位が3つ以上ある場合には対応できません。
FILTER関数ですべて取り出してTRANSPOSE関数で縦横を入れ替える
重複するデータに該当する値を“すべて”取り出したい場合は、FILTER関数を使います。Excel 2024/2021、Microsoft 365のExcel利用可能です。構文は以下の通り。引数［配列］と［含む］を2つで動作します。
前述の例で重複していた「21」を引数［含む］の条件として、数式を作成すると以下のようになります。［配列］はセルA3～A7、セルB3～B7のうちセルB13と同じ値という条件になります。
=FILTER($A$3:$A$7,$B$3:$B$7=B13)
取り出した「製品C」「製品D」を横に並べたいので、表の縦横を入れ替えられるTRANSPOSE関数を組み合わせます。数式の先頭に「TRANSPOSE(」と追記して、末尾に「)」を追加するだけです。
3つの重複がある場合の結果も確認してみましょう。例えば、製品Eのデータ「21」に書き換えると、「製品E」も横に並びます。
