「最初に赤字になった月、どこだっけ？」——そのひと手間、関数で消せます
「先月まで黒字だったのに、最初に赤字へ転落した月はどこだ？」
月次の損益レポートを眺めながら、目で追って探した経験はありませんか。データが12行程度なら我慢できても、数年分・複数店舗分ともなると手動検索は現実的ではありません。
この記事では、B列に並んだ数値の中から「最初の負の値」を自動で取得するExcelの関数テクニックを5つ紹介します。使っているExcelのバージョンや取得したい情報の種類に応じて、最適な方法をすぐに選べるよう整理しました。記事を読み終えたあとには、手動検索ゼロで「最初の赤字月」を特定できるようになります。
まず：どの方法を使えばいい？ 30秒チェック
| 状況 | おすすめの方法 |
|---|---|
| Excel 365 / 2021を使っている | ① XLOOKUP（最もシンプル） |
| 負の値を一覧で加工したい | ② FILTER + TAKE |
| 月名と損益を同時に取得したい | ③ TAKE（複数列） |
| Excel 2019以前・旧環境でも動かしたい | ④ INDEX + MATCH |
| 「何行目か」を知りたい | ⑤ MIN + IF |
以下では、このシナリオをもとに各方法を解説します。
サンプルシナリオ
ある店舗の月次損益データが下表のとおりです。3月に初めて赤字へ転落しています。
| A列（月） | B列（損益） |
|---|---|
| 1月 | 120 |
| 2月 | 85 |
| 3月 | -30 |
| 4月 | 60 |
| 5月 | -15 |
| 6月 | 90 |
ゴール：B列で最初に登場する負の値（-30）と、その月名（3月）を取得する。
① XLOOKUP を使う（Excel 365 / 2021）――最短・最シンプル
=XLOOKUP(TRUE, B2:B7<0, B2:B7)
しくみ
B2:B7<0が{FALSE; FALSE; TRUE; FALSE; TRUE; FALSE}という論理値の配列を返す- XLOOKUPが先頭から順にスキャンし、最初に TRUE になった位置の値を返す
- デフォルトが「先頭から検索」なので、追加設定不要
月名（A列）を取得したい場合
=XLOOKUP(TRUE, B2:B7<0, A2:A7)
戻り範囲を変えるだけ。これ以上シンプルな書き方はありません。365環境ならまずこれ一択です。
② FILTER + TAKE を使う（Excel 365）――フィルタ派生での取得
=IFERROR(TAKE(FILTER(B2:B7, B2:B7<0), 1), "負の値なし")
しくみ
FILTER(B2:B7, B2:B7<0)で負の値だけの配列{-30; -15}を生成TAKE(..., 1)で先頭1件だけを切り出すIFERRORで負の値が存在しないケースに備える
FILTERだけでは複数の値が返るため、TAKEで絞り込む点がポイントです。「あとで負の値リスト全体も使いたい」という場面では、FILTERの中間結果をセルに保持しておくと使い回しがききます。
最後の負の値を取得したい場合
=TAKE(FILTER(B2:B7, B2:B7<0), -1)
TAKEの第2引数を -1 にするだけで末尾取得に切り替わります。
③ TAKE で複数列をまとめて取得する（Excel 365）――月名と損益を同時に
=TAKE(FILTER(A2:B7, B2:B7<0), 1)
しくみ
- FILTERの対象範囲を
A2:B7（月名＋損益の2列）に広げる - スピル機能によって「3月 ／ -30」が横に2列展開される
レポートに「最初の赤字月の月名と金額を並べて表示したい」というユースケースに直結します。②と③は用途で使い分けるもので、機能の重複はありません。
④ INDEX + MATCH を使う（全バージョン対応）――旧環境の鉄板
=INDEX(B2:B7, MATCH(TRUE, B2:B7<0, 0))
しくみ
MATCH(TRUE, B2:B7<0, 0)が条件配列の中から最初の TRUE の行番号（3）を返すINDEXがその行番号に対応する値（-30）を返す
⚠️ Excel 2019以前は Ctrl + Shift + Enter で配列数式として確定してください。Excel 365では通常のEnterでOKです。
月名を返す場合
=INDEX(A2:A7, MATCH(TRUE, B2:B7<0, 0))
新しい関数が使えない環境でも確実に動作します。社内共有ファイルや互換性が求められるシーンでは今でも第一候補です。
⑤ MIN + IF を使う――「何行目か」を知りたいとき
=MATCH(MIN(IF(B2:B7<0, ROW(B2:B7))), ROW(B2:B7), 0)
⚠️ Excel 2019以前は Ctrl + Shift + Enter で確定。
しくみ
IF(B2:B7<0, ROW(B2:B7))で負のセルだけの行番号配列を生成MINで最小の行番号（＝最初に登場した行）を取得MATCHでそれが範囲の何番目かを返す
「3行目（3月）から集計を始めたい」など、行位置そのものを後続の数式に渡したい場面で役立ちます。値の取得が目的なら④のほうがシンプルなため、⑤は「位置を知る」用途に限定して使いましょう。
5つの方法 比較まとめ
| 方法 | 対応バージョン | 記述のシンプルさ | 複数列取得 | 備考 |
|---|---|---|---|---|
| ① XLOOKUP | 365 / 2021 | ★★★ | △（戻り範囲変更で対応） | 最短・最推奨 |
| ② FILTER + TAKE | 365 | ★★☆ | △ | エラー対策が容易 |
| ③ TAKE（複数列） | 365 | ★★☆ | ✅ | 月名と値を同時取得 |
| ④ INDEX + MATCH | 全バージョン | ★★☆ | △ | 旧環境の定番 |
| ⑤ MIN + IF | 全バージョン | ★☆☆ | ❌ | 行位置取得に特化 |
まとめ
「最初の負の値を探す」という一見地味な操作も、関数を使えば一瞬で解決できます。
- Excel 365 / 2021 → まず ① XLOOKUP を使う
- 月名と損益を同時に出したい → ③ TAKE（複数列） が便利
- 旧バージョンも考慮 → ④ INDEX + MATCH が安定
- 行位置が必要 → ⑤ MIN + IF を使い分ける
どの関数を選ぶかに迷ったら、冒頭の30秒チェック表に戻ってください。あなたのExcel環境と目的に合った方法がすぐに見つかるはずです。
数式をコピーしたらまずサンプルデータで動作確認し、自分のシートに合わせてセル範囲を書き換えてみてください。「使えた！」と感じたら、ぜひブックマークやシェアをお願いします🙏
