Excelで売上や貯金の推移を管理していると、「目標金額にいつ到達するのか」を自動表示したいことがあります。
たとえば、次のような予測表があるケースです。
| 年月 | 予想残高 |
|---|---|
| 2026/10 | 4,500円 |
| 2026/11 | 4,800円 |
| 2026/12 | 5,100円 |
別のセルへ目標金額の「5,000」を入力すると、指定金額以上になる最初の年月である「2026年12月」を表示できます。
この記事では、XLOOKUP関数を使って、条件を満たす最初の日付を取得する方法を解説します。
※この方法は、Microsoft 365、Excel 2021、Excel 2024など、XLOOKUP関数を使用できるExcelが対象です。
指定金額に到達する最初の年月を表示する
今回は、A列を年月、B列を予想残高、E2セルを目標金額、F2セルを到達予定月として使用します。
F2セルへ、次の数式を入力してください。
=XLOOKUP(TRUE,B2:B100>=E2,A2:A100,"未到達")Enterキーを押すと、B列の予想残高がE2セルの目標金額以上になる最初の行から、A列の年月が取得されます。
今回の例では、5,000円以上になる最初の行は5,100円の「2026年12月」です。
そのため、F2セルには「2026年12月」と表示されます。
指定金額に到達するデータがない場合は、「未到達」と表示されます。
【注意】
予想残高と目標金額は、文字列ではなく数値で入力してください。
「円」は直接入力せず、セルの表示形式で付けると、数式で正しく比較できます。
👉XLOOKUP関数で、特定の文字を含むデータを検索したい場合は、以下の記事で部分一致の方法を解説しています。
日付は古い順に並べておく
XLOOKUP関数は、表の上から順番に検索し、最初に条件を満たした行を返します。
目標金額へ初めて到達する年月を調べる場合は、A列の日付を古い順に並べておきましょう。
日付の順番が入れ替わっていると、実際とは異なる年月が表示されることがあります。
また、年月は文字列ではなく、Excelが日付として認識できる値で入力してください。
年月の表示形式を変更する
結果が「2026/12/1」のように表示される場合は、セルの表示形式を変更します。
■年月を「2026年12月」と表示する方法
1.結果を表示するセルを選択
↓
2.Ctrl＋1キーを押す
↓
3.［表示形式］から［ユーザー定義］を選択し、［種類］へ「yyyy年m月」と入力
（もしくは[日付]で、[種類]から[20212年3月]を選択）
↓
4.［OK］をクリックします。
これで、「2026年12月」の形式で表示できます。
売上や貯金などにも応用できる
今回の数式は、次のような管理表にも応用できます。
・売上が目標金額へ到達する月
・貯金が指定金額を超える予定日
・累計販売数が目標へ到達する日
・在庫量が指定数以上になる時期
日付の列と数値の列があれば、同じ方法で条件を満たす最初の日付を取得できます。
👉すでにある予測表から到達時期を探すのではなく、目標達成に必要な金額や数量を逆算したい場合は、ゴールシークを使用します。
まとめ
Excelで指定金額に到達する最初の日付を表示する場合は、次のような数式を使用します。
=XLOOKUP(TRUE,B2:B100>=E2,A2:A100,"未到達")この数式を使えば、売上や貯金、予想残高などが目標金額以上になる最初の年月を自動表示できます。
正しい到達時期を取得するため、日付は古い順に並べておきましょう。
コメント