【Excel】適用開始日つきマスタから基準日時点の最新行を引く式

適用開始日つきマスタから基準日時点の最新行を引く式を表した、XLOOKUPとINDEX・MATCH・MAXIFSの数式入りアイキャッチ 実務設計

改定のたびに行を足していく単価マスタから、基準日の時点で有効な1行だけを引きたい。適用開始日が昇順に並んでいれば、近似一致のINDEXとMATCHで足ります。ただ、行を足すうちに並びが崩れたり、同じ開始日が2行入ったりすると、エラーにならずに別の単価が返ることがあります。

この記事では、適用開始日つきのマスタから基準日時点の最新行を引く式を7つ並べて、同じ表で結果を比べました。並びが崩れたとき、開始日が重複したとき、基準日より前の行が無いとき、範囲に空行が入ったときで、式ごとに結果が分かれます。

改定履歴を別のテーブルに分けてキーで引く設計は、【Excel】手入力と自動計算を共存させる上書き優先列の設計でも扱っています。

追記型と非追記型の分け方

マスタの項目は、2種類に分けると扱いやすくなります。

  • 追記型：単価や料率のように、改定のたびに行を足す。古い行を残すので、過去の基準日で計算し直しても、当時の値を引ける
  • 非追記型：コードや名称のように、変わったら上書きする。1コードに1行

この分け方は運用上の考え方で、Excelの動作として検証した内容ではありません。この記事は、追記型のマスタから1行を引く式を扱います。

基準日時点の最新行を引く式を7つ比べる

次の表を、テーブル名tblRateにしました。列見出しはStartDate（適用開始日）とRate（単価）で、基準日はセルE1に入れます。

StartDateRate
2024/04/011000
2025/04/011100
2026/04/011250

比べた式は、次の7つです。A〜Gの記号は、この記事の中だけの呼び名です。

A  =INDEX(tblRate[Rate],MATCH($E$1,tblRate[StartDate],1))
B  =LOOKUP($E$1,tblRate[StartDate],tblRate[Rate])
C  =LOOKUP(2,1/(tblRate[StartDate]<=$E$1),tblRate[Rate])
D  =INDEX(tblRate[Rate],MATCH(MAXIFS(tblRate[StartDate],tblRate[StartDate],"<="&$E$1),tblRate[StartDate],0))
E  =INDEX(tblRate[Rate],MATCH(AGGREGATE(14,6,tblRate[StartDate]/(tblRate[StartDate]<=$E$1),1),tblRate[StartDate],0))
F  =XLOOKUP(TRUE,(tblRate[StartDate]<=$E$1),tblRate[Rate],"",0,-1)
G  =XLOOKUP($E$1,tblRate[StartDate],tblRate[Rate],"",-1)

Fは、開始日が基準日以下の行のうち、いちばん下の行を返す書き方です。Gは、基準日と同じ日付の行か、無ければ次に小さい日付の行を返します。

昇順で重複がなければ、どの式も同じ結果になる

サンプルの並び（昇順・重複なし）では、基準日を2024/04/01、2025/03/31、2025/04/01、2026/04/01、2026/10/24にして、7つの式すべてが同じ単価（1000、1000、1100、1250、1250）を返しました。違いが出るのは、ここから外れたときです。

並びが崩れたとき：エラーにならずに値が変わる

開始日が2024/04/01、2026/04/01、2025/04/01の順に並んだ3行で、同じ7つの式を3つの基準日で比べました。

並び順が不揃いの3行のマスタに対し、7つの式を3つの基準日で比べたExcelの表。基準日2026/10/24では、MAXIFS・AGGREGATE・XLOOKUP(基準日,…,-1)が正解の1250で、他の4つは1100になっている
再現用の最小コードの実行結果（開始日が不揃いの3行）
  • 基準日2025/06/30（正解は1100）：AとBだけが1000で誤り
  • 基準日2026/10/24（正解は1250）：A・B・C・Fが1100で誤り。D・E・Gは、どちらの基準日でも正解

行の並びを、2025/04/01、2026/04/01、2024/04/01の順に変えて、基準日を2025/06/30にすると、A・Bは1100で正解、C・Fは1000で誤りになりました。式によって、合う並びと外れる並びが違います。エラーは出ず、正解になる並びもあるため、気づきにくい壊れ方です。

8行の別の並びでは、基準日を17通りに変えて、VBAで求めた正解と比べました。

式誤った件数（17通り中）
A INDEX/MATCH（近似一致）11
B LOOKUP11
C LOOKUP(2,1/…)12
D MAXIFS0
E AGGREGATE0
F XLOOKUP(TRUE,…,-1)12
G XLOOKUP(基準日,…,-1)0

近似一致のA・Bは、昇順に並んでいることを前提にした検索です。C・Fは、条件に合う行のうち、いちばん下の行を返します。下の行ほど開始日が新しいとは限らないため、並びが崩れると、いちばん新しい行にならないことがあります。

並びを保証できないマスタでは、D・E・Gを使います。A・B・C・Fを使うなら、並びが崩れていないかを、別の式で見張ります。

並びと重複を検知する式

開始日が3行（A2:A4）のときの、並び替わっている箇所の数と、同じ日付を持つ行の数です。どちらも0なら、問題ありません。

=SUMPRODUCT(--($A$3:$A$4<$A$2:$A$3))
=SUMPRODUCT(--(COUNTIF($A$2:$A$4,$A$2:$A$4)>1))

1つ目は、昇順の並びなら0、2025/04/01、2026/04/01、2024/04/01の並びでは1でした。2つ目は、重複がなければ0、同じ日付が2行あるときは、その2行が数えられて2でした（範囲は行数に合わせます）。

同じ開始日が2行あるとき

2025/04/01の行が2行（上が1100、下が1150）あるとき、基準日を2025/06/30にすると、次のように分かれました。

  • A・B・C・F：1150（下の行）
  • D・E・G：1100（上の行）

どちらが正しいかは、運用で決まります。重複は入力ミスとして扱い、上の検知式で見つけるのが安全です。

基準日より前の行が無いとき

基準日を2024/03/31（最初の開始日の前日）にすると、式ごとに結果の種類が違いました。

式結果
A・B・C・D#N/A
E AGGREGATE#NUM!
F・G（第4引数に””）空白（””）

Dは、MAXIFSが条件に合う行が無いときに0を返すため（MAXIFS関数の記載）、MATCHで0を探して#N/Aになります。Fの第4引数を省略すると、#N/Aが返りました。

IFNAで包むと、A・B・C・Dの#N/Aだけを消せます。Eの#NUM!はIFNAでは消えず、IFERRORで消えました。ただし、IFERRORは他のエラーも隠します。使い分けは、【Excel関数】#N/A・#VALUE!・#REF!が出たら最初に確認で扱っています。

範囲に空行が含まれるとき

データが2〜4行目にあるのに、範囲を6行目までにしてあると、基準日を2026/10/24にしたとき、CとFだけが1250ではなく0を返しました。A・B・D・E・Gは1250です。

空白のセルは、比較の中で0として扱われます。開始日<=基準日が、空行でもTRUEになるためです。範囲A2:A6に対するSUMPRODUCT(--(A2:A6<=基準日))は、データ3行と空行2行で5でした。C・Fは、条件に合ういちばん下の行（空行）を返してしまいます。

テーブルにすれば、行を足しても式を直さなくていい

tblRateのようにテーブルにして、構造化参照で書くと、行を足したときに範囲が自動で広がります。7つの式すべてで、2026/10/01の行（1300）を足した後、基準日2026/09/30は1250、2026/10/24は1300になりました。範囲のずれや空行の混入も、テーブルなら起きません。

XLOOKUPの検索値は、TRUEか–で渡す

Fのように、条件の配列に対して検索するときは、検索値をTRUEにします。1を渡すと、配列がTRUE/FALSEのままなので一致せず、第4引数の""が返りました（エラーにはなりません）。--(…)で数値にすれば、1で正しく引けます。

=XLOOKUP(TRUE,(tblRate[StartDate]<=$E$1),tblRate[Rate],"",0,-1)     → 1250
=XLOOKUP(1,(tblRate[StartDate]<=$E$1),tblRate[Rate],"",0,-1)        → 空白（一致なし扱い）
=XLOOKUP(1,--(tblRate[StartDate]<=$E$1),tblRate[Rate],"",0,-1)      → 1250

商品ごとのマスタ：キーで絞ってから最新行を引く

商品コードなどのキーごとに履歴があるときは、キーだけで引くと、いちばん古い行が返ります。次の5行で、キーAをINDEXとMATCHの完全一致、VLOOKUPの完全一致で引くと、どちらも500でした。

KeyStartDateRate
A2024/04/01500
B2024/04/01300
A2025/04/01550
B2025/10/01320
A2026/04/01600

キーで絞ってから、最新の開始日の行を引きます。列Aがキー、列Bが適用開始日、列Cが単価で、セルE1にキー、セルE2に基準日を入れ、範囲は2〜6行目にします。

=INDEX($C$2:$C$6,MATCH(1,INDEX(($A$2:$A$6=$E$1)*($B$2:$B$6=MAXIFS($B$2:$B$6,$A$2:$A$6,$E$1,$B$2:$B$6,"<="&$E$2)),0),0))

=INDEX($C$2:$C$6,MATCH(1,INDEX(($A$2:$A$6=$E$1)*($B$2:$B$6=AGGREGATE(14,6,$B$2:$B$6/(($A$2:$A$6=$E$1)*($B$2:$B$6<=$E$2)),1)),0),0))

上がMAXIFS版、下がAGGREGATE版です。同じ5行を日付順でない並びにして、キーと基準日の6通りで比べました。MAXIFS版とAGGREGATE版は、6通りとも正解でした。LOOKUP(2,1/…)で書いた式と、XLOOKUP(1,…,-1)で書いた式は、キーAで基準日2026/04/01（正解は600）のとき550、キーBで基準日2025/11/01（正解は320）のとき300になり、2通りで誤りでした。

キーが無いときや、そのキーに基準日以前の行が無いときは、MAXIFS版もAGGREGATE版も#N/Aでした。AGGREGATE版は、キーを使わない式（E）では#NUM!でしたが、キーで絞る式では#N/Aになります。

どのExcelで使えるか

XLOOKUP関数は、Excel 2021とMicrosoft 365で使えます（Excel 2016・2019では使えません）。MAXIFSは、Excel 2019以降です。どちらも公式ページの記載です。

この記事の検証は、Microsoft 365版のExcel（Windows、日本語）で行いました。365として認識されていない状態のExcelで同じ表を試したときは、MAXIFSもXLOOKUPも#NAME?になり、A・B・C・Eは昇順の表で同じ結果でした。MAXIFSとXLOOKUPが使えない環境では、Eを使います。Mac・他のバージョンは、手元で検証できていません。

まとめ

  • 追記型のマスタは、並びが崩れる前提で式を選ぶ。365・2021ならG、2019以降ならD、MAXIFSが使えない環境ならE
  • XLOOKUP(TRUE,…,-1)やLOOKUPは、昇順で、重複がなく、空行を含まない範囲でだけ正しい
  • 基準日より前の行が無いときは、#N/A・#NUM!・空白に分かれる。IFNAとIFERRORは、結果を見て使い分ける
  • テーブルにして、並びと重複を検知する式を置いておく

コメント

タイトルとURLをコピーしました