ExcelでXLOOKUP関数を使っていると、
・重複データの「最後の値」を取得したい
・下から検索して一番下のデータを取りたい
という場面がありませんか？
そこで使われるのが、search_mode = -1 を指定する方法です。
しかし実務では、「最後の値」と「最新の値」という点を理解しておかないと、思わぬミスにつながります。
この記事では、
・XLOOKUP関数で最後の値を取得する方法
・なぜ下から検索できるのか
・最後の値と最新データの違い
・本当に最新値を取得する正しい方法
を、実務者目線で解説します。
👉他にXLOOKUP関数の活用方法を解説した記事はこちら！こちらも合わせてチェックしてみてください。
XLOOKUPで最後の値を取得する方法（-1の使い方）
XLOOKUP関数で最後の値を取得するには、第6引数「検索モード」 に -1 を指定します。
=XLOOKUP(検索値, 検索範囲, 戻り範囲,,0,-1)第6引数の意味
XLOOKUP関数の第6引数である「検索モード」とは、検索する方向を指定することができます。
こちらは省略可能な引数で、省略時は「1」（上から検索）が既定値となっています。
| 検索モード | 意味 |
|---|---|
| 1 | 上から検索（既定値） |
| -1 | 下から検索 |
つまり 「-1」 を指定すると、検索範囲を下から上へ検索することができます。
これにより、重複データがある場合でも「一番下の一致した値」を取得できます。
※そのほかに「2」と「-2」の検索モードがありますが、特殊なケースのみ使用するため、「1」か「-1」だけ把握できておけばOKです。
例：重複データの最後の値を取得
以下の表を使って、XLOOKUP関数で最後のデータを取得してみます。
今回は商品Aの最後の値にある売上額を取得します。
商品Aの最後の値にある売上額を取得する場合、以下のように数式を入力します。
=XLOOKUP("商品A",A2:A12,B2:B12,,0,-1)すると、以下のように最後の値を取得することができました。
XLOOKUP関数は一致が見つかった時点で検索を終了します。
「-1」を指定すると下から上へ検索するため、最初に見つかった一致が「一番下の一致」となります。
注意：最後の値＝最新の値とは限らない
実務で重要なのはここです。
先ほどのデータから商品Aの最新売上データを取りたいと考えたとき、最後の値を取得するだけでは最新データが取得できていません。
つまり、最後の値＝最新の値とならないケースが存在します。
先ほどの表だと、最新の値は2月7日ですが、最後の値では2月3日を検索しており、最新データが拾えていないことが確認できます。
最後の値を拾いたい＝最新の値を拾いたい、ということで使用されている方も多いのではないかと思いますが、検索モード=-1では、単純に最後の値を拾うだけとなります。
つまり、
・「-1」は「最後の行」を取得する
・しかし、「最新日付」を保証するものではない
ということです。
実務で起きるトラブル例
業務データでは、次のようなことが普通に起きます。
★トラブル例ポイント
⚠️並び替えが行われる
⚠️CSV取込で順番が変わる
⚠️古いデータを後から追記する
⚠️手入力で行順が崩れる
この状態で「最後の値」を基準にすると、誤った最新データを取得するリスクがあります。
そのため、最後の値を検索するのではなく、別の手段で最新の値を検索する必要があります。
最新データを取得する方法
では、最新のデータを取得するにはどうすればよいのか。
それは、行番号ではなく「日付の最大値」を基準にすることで解決できます。
方法①：MAXIFS＋XLOOKUP（最も安定）
最大値を拾うには、MAXIFS関数を使用します。
MAXIFS関数は、条件に合うデータで一番大きい数字（ここでは日付）を見つける関数です。
先ほどの商品Aから、最新の売上額（＝日付が一番大きい日）を拾うには、以下のように入力します。
=XLOOKUP(MAXIFS(B2:B12, A2:A12, "商品A"),B2:B12,C2:C12)これで、商品Aの最新の売上額を検索することができました。
流れとしては、
・MAXIFS関数で、対象商品の最大日付を取得
・その日付をキーとして売上額を取得
しています。
この方法であれば、
・並び順がバラバラでもOK
・後からデータを追記しても対応可能
・本当に最新の値を取得できる
ので、実務面でもこちらの方法が安全に使用できます。
方法②：FILTER＋SORTで明示的に最新を取得
FILTER関数とSORT関数の組み合わせで、最新の日付（＝日付の最大値）を拾うこともできます。
・FILTER関数→選んだ条件に合うデータのみ抽出する関数
・SORT関数→順番通りに並び替える関数
となります。
日付の最大値を拾うには、以下のように入力します。
=INDEX(SORT(FILTER(A2:C12, A2:A12="商品A"),2,-1),1,3)この方法でも、商品Aの最新の売上額を検索することができました。
流れとしては、
・条件に一致する行を抽出し、
・日付で降順に並び替え、
・先頭行を取得する
ことで、最新の日付を取得しています。
複数件の最新データを扱う場合にも応用できます。
👉FILTER関数については以下記事で詳しく解説しております。合わせてチェックしてみてください。
「最後の値」と「最新の値」の使い分け
ここまで解説してきた内容をまとめた表となります。
必要な条件に応じて、適切な方法を選択することが重要です。
| 目的 | 推奨方法 |
|---|---|
| 単純に一番下の値を取得 | search_mode = -1 |
| 最新日付の値を取得 | MAXIFS基準 |
| 分析・一覧処理 | FILTER＋SORT |
重要なのは、
・「最後」と「最新」は別物
と理解することで、データの拾い間違いも減らすことができます。
まとめ｜「最後」と「最新」を混同しないことが重要
XLOOKUP関数で最後の値を取得するには、
・search_mode = -1
を指定することで実現することができます。
しかし実務では、最後の値=最新の値と想定して検索しているケースもあると思います。
・最後の値なのか
・最新の値なのか
目的を明確にすることが重要です。
最新データを正しく取得したい場合は、日付の最大値を基準にする設計をおすすめします。
XLOOKUP関数を“使える”から、XLOOKUP関数を“正しく設計できる”へ。
この視点を持つだけで、Excelの実務力は一段上がります。
ぜひ実際の業務でも活用してみてください。
最後まで読んでいただき、ありがとうございました。
コメント