【PR】本記事にはアフィリエイトリンクが含まれます。
SUMやAVERAGEは使えるようになった。次は「条件で動きを変える」「他のシートからデータを引っ張る」をマスターしたい。本記事は5段階シリーズの Stage 3 です。結論として、IF関数とVLOOKUP/XLOOKUPの2つを覚えれば、Excelは「事務の道具」から「業務の武器」に変わります。
※ 前提知識として Stage 2(表と関数の基本) を済ませてください。次は Stage 4(ピボットテーブルとグラフ) に進みます。
事前準備:絶対参照「$」の使い方
Stage 3 では VLOOKUP や SUMIFS で範囲を「$D$2:$E$100」のように $ 付きで書きます。これは「セルをコピーしても範囲が動かない」という意味です。
D2:相対参照(コピー時にずれる)$D$2:絶対参照(コピー時に固定)$D2/D$2:複合参照(列だけ固定 / 行だけ固定)
キーボードショートカット F4 を押すと、相対参照と絶対参照を順番に切り替えられます。「数式をコピーしても範囲がズレない」ようにするための重要な仕組みです。詳しくは Stage 5 で解説しますが、本記事では「$ がついていたら『そこは固定』」と覚えれば進められます。
Stage 3 で覚えること
- IF関数で条件分岐(YES/NO)
- COUNTIF / SUMIF で「条件付き集計」
- VLOOKUP / XLOOKUP でデータ検索
- IFERROR でエラー対策
IF関数:「もし〜なら」を表現する
=IF(条件, 真の時の値, 偽の時の値)例:売上が¥1,000以上なら「達成」、未満なら「未達」
=IF(B2>=1000, "達成", "未達")B2 の値で「達成」または「未達」が自動で表示されます。営業の目標管理、テストの合否判定など、業務で頻出します。
応用:IF を入れ子にする(複数条件)
=IF(B2>=2000, "S", IF(B2>=1000, "A", IF(B2>=500, "B", "C")))これで4段階のランク付けができます。ただし、入れ子が深くなると読みにくくなるので、4階層以上は IFS 関数に移行するのが現代のスタイルです。
=IFS(B2>=2000, "S", B2>=1000, "A", B2>=500, "B", TRUE, "C")※ IFS関数はExcel 2019以降で使えます。それより古いバージョンでは IF のネストで対応してください。
COUNTIF / SUMIF:「条件に合うデータだけ集計」
COUNTIF:条件に合う件数を数える
=COUNTIF(B2:B100, "達成")「達成」と書かれたセルの数を返します。「営業目標を達成した人数」を数える時に使います。
SUMIF:条件に合うものだけ合計
=SUMIF(A2:A100, "りんご", B2:B100)A列が「りんご」の行だけ、対応するB列の値を合計します。商品別の売上集計などに使います。
SUMIFS:複数条件で合計
=SUMIFS(B2:B100, A2:A100, "りんご", C2:C100, "東京")「りんご × 東京」の組み合わせだけを合計します。SUMIF より新しい関数で、引数の順番が違うので注意してください(集計対象が最初、条件が後)。
VLOOKUP / XLOOKUP:データを引っ張る
「商品コードから商品名を取得」のような、別の表から値を持ってくる関数です。詳細は VLOOKUP完全マニュアル を参照してください。基本だけ示します。
VLOOKUP の基本
=VLOOKUP(検索値, 範囲, 列番号, FALSE)例:A2 の商品コードを D列で検索して、E列の商品名を返す
=VLOOKUP(A2, $D$2:$E$100, 2, FALSE)第4引数の FALSE は「完全一致で検索する」という意味です。TRUE を指定すると「近似一致」になり、業務ではほぼ使いません。VLOOKUP では必ず FALSE を書く、と覚えておけば事故が減ります。
XLOOKUP の基本(Excel 2021以降)
=XLOOKUP(A2, $D$2:$D$100, $E$2:$E$100, "該当なし")VLOOKUP より直感的で、見つからない時の値も指定できます。Microsoft 365 を使っているなら、新規はすべてXLOOKUP推奨です。
IFERROR でエラー対策
VLOOKUP で値が見つからないと「#N/A」エラーが出ます。これを「該当なし」と表示するには IFERROR でラップします。
=IFERROR(VLOOKUP(A2, $D$2:$E$100, 2, FALSE), "該当なし")エラー処理を慣習化しておくと、上司や同僚にシートを渡した時の信頼度が上がります。
練習問題(3ステップで)
STEP 1:IF関数だけの練習
A列に売上、B列に「達成/未達」を表示
=IF(A2>=1000, "達成", "未達")STEP 2:VLOOKUPだけの練習
G2:H10 にマスタ表(G列=コード、H列=商品名)を作ってから:
=VLOOKUP(A2, $G$2:$H$10, 2, FALSE)STEP 3:組み合わせの練習
STEP 1とSTEP 2が動くようになったら、ランク判定と商品名取得を1つの行で組み合わせます。
よくある詰まりポイント
| 症状 | 対処 |
|---|---|
| IFの条件式が動かない | 「>=1000」のような不等号、文字列は “達成” のようにダブルクォート |
| SUMIFが0を返す | 条件の表記揺れ(全角/半角・スペース)を確認 |
| VLOOKUPで#N/A | 検索値の前後にスペース、データ型の不一致 |
| セルをコピーすると範囲がズレる | $で絶対参照に |
次のステップ
関数の使い分けに慣れたら、Stage 4:ピボットテーブルとグラフ へ。データ集計・可視化のスキルを身につけると、業務報告書のクオリティが一気に上がります。
関連:
