ソニック脱・関数初心者｜Excelの最新関数で業務を変える
「VLOOKUPは使えるけど、もっと便利な方法はないの？」「同じ複雑な数式を、何度もコピペしている」「Excelの新関数があるらしいけど、よく分からない」
そんな悩み、ありませんか？
私自身、長らくVLOOKUPとIFの組み合わせで乗り切ってきました。でも、XLOOKUPやLAMBDAといった新関数を覚えてから、Excelの数式が劇的にシンプルになりました。
XLOOKUP・スピル・LAMBDAは、Excelの「次のレベル」。これらを使いこなせば、複雑だった数式が驚くほど簡潔になります。
この記事では、Excelの最新関数を、コピペで使えるサンプル付きで完全解説します。
こんな方におすすめ
- VLOOKUPは使えるが、次のステップに進みたい事務職の方
- 複雑な数式を何度も書いていて非効率を感じているバックオフィスの方
- Excelの最新関数（Microsoft 365）を実務で活用したい方
第1章｜なぜ最新関数を学ぶべきなのか
Excel関数は進化している
近年、Excel（特にMicrosoft 365）には強力な新関数が次々と追加されました。これらを知っているかどうかで、業務効率に大きな差が出ます。
- **XLOOKUP**：VLOOKUPの完全上位互換
- **スピル**：1つの数式で複数セルに結果を展開
- **FILTER / SORT / UNIQUE**：データ抽出・並べ替え・重複排除
- **LAMBDA**：自分だけのオリジナル関数を作る
この記事で扱う関数の一覧
| 関数 | できること |
|---|---|
| XLOOKUP | 柔軟な検索（左右どちらも・複数条件も） |
| FILTER | 条件に合う行を抽出 |
| SORT | データを並べ替え |
| UNIQUE | 重複を除いた一覧を作成 |
| LAMBDA | オリジナルの関数を定義 |
※これらの関数の多くはMicrosoft 365・Excel 2021以降で利用できます。古いバージョンでは使えない場合があります。
第2章｜XLOOKUPの発展テクニック
XLOOKUPの基本構文（復習）
=XLOOKUP(検索値, 検索範囲, 戻り範囲, [見つからない場合], [一致モード], [検索モード])発展1｜「見つからない場合」を指定
VLOOKUPでは#N/Aエラーになる場面も、XLOOKUPなら第4引数で対応できます。
=XLOOKUP(A2, 商品マスタ[商品コード], 商品マスタ[商品名], "該当なし")発展2｜左方向の検索
VLOOKUPは「右方向」しか検索できませんが、XLOOKUPは左右どちらも自由です。
=XLOOKUP(A2, 商品マスタ[商品名], 商品マスタ[商品コード])発展3｜複数列をまとめて取得
XLOOKUPは戻り範囲に複数列を指定すると、複数の値を一度に返せます（スピル）。
=XLOOKUP(A2, 商品マスタ[商品コード], 商品マスタ[[商品名]:[在庫]])発展4｜近似一致（価格帯・成績判定）
=XLOOKUP(B2, 基準表[下限点], 基準表[ランク], "対象外", -1)発展5｜複数条件の検索
検索値・検索範囲を「&」で連結すれば、複数条件の検索ができます。
=XLOOKUP(A2&B2, 売上[店舗]&売上[商品], 売上[金額])
第3章｜スピルとスピル範囲演算子
スピルとは
「スピル（spill）」とは、1つの数式の結果が複数のセルに自動で「あふれて」表示される機能です。
# 重複を除いた一覧が自動展開
=UNIQUE(B:B)スピル範囲演算子（#）
スピルした範囲を参照するには「#」を使います。
# スピル範囲全体を参照（#を使う）
=SUM(A1#)スピルを使うと、「数式を全行にコピー」する作業が不要になります。1つの数式で完結します。
第4章｜FILTER・SORT・UNIQUEの実務活用
FILTER｜条件で抽出
# 売上が100万以上の行を抽出
=FILTER(売上テーブル, 売上テーブル[金額]>=1000000)
# 東京店のデータだけ抽出
=FILTER(売上テーブル, 売上テーブル[店舗]="東京店")
# 複数条件（東京店 かつ 100万以上）
=FILTER(売上テーブル, (売上テーブル[店舗]="東京店")*(売上テーブル[金額]>=1000000))SORT｜並べ替え
# 売上を降順に並べ替え（第2引数=列番号、第3引数=-1で降順）
=SORT(売上テーブル, 4, -1)
# 抽出して並べ替え
=SORT(FILTER(売上テーブル, 売上テーブル[店舗]="東京店"), 4, -1)UNIQUE｜重複排除
# 店舗名の一覧（重複なし）
=UNIQUE(売上テーブル[店舗])
# 重複排除して並べ替え
=SORT(UNIQUE(売上テーブル[店舗]))3つを組み合わせた実務例
「東京店の売上トップ5を、重複なしで降順表示」のような複雑な処理も、関数の組み合わせで一発です。
# 東京店の売上を降順表示
=SORT(FILTER(売上テーブル[[商品]:[金額]], 売上テーブル[店舗]="東京店"), 2, -1)FILTER・SORT・UNIQUEは「元データが変わると結果も自動更新」される動的な関数。ダッシュボード作りにも最適です。
第5章｜LAMBDA関数｜オリジナル関数を作る
LAMBDAとは
LAMBDAは、**自分だけのオリジナル関数を定義できる**画期的な関数です。複雑な数式に名前を付けて、何度でも再利用できます。
LAMBDAの基本構文
=LAMBDA(引数1, 引数2, ..., 計算式)(実際の値)実例｜税込み価格を計算する関数
=LAMBDA(price, price * 1.1)(1000)
# 結果：1100名前付き関数として登録する手順
- 「数式」タブ →「名前の管理」
- 「新規作成」をクリック
- 名前：TAXIN（好きな関数名）
- 参照範囲：=LAMBDA(price, price * 1.1)
- 「OK」→ 以後 =TAXIN(1000) で使える
実用的なLAMBDA例
# 消費税（名前：TAX）
=LAMBDA(price, rate, price * rate)
# 使用：=TAX(1000, 0.1)
# 氏名整形（名前：FULLNAME）
=LAMBDA(sei, mei, sei & " " & mei)
# 使用：=FULLNAME("山田", "太郎")
# 達成率（名前：RATE）
=LAMBDA(actual, target, TEXT(actual/target, "0.0%"))
# 使用：=RATE(80, 100) → "80.0%"※LAMBDAで作った関数は、同じブック内で何度でも使えます。よく使う計算は関数化すると、数式がぐっと読みやすくなります。
第6章｜関数の組み合わせテクニック
XLOOKUP × IFERROR
=IFERROR(XLOOKUP(A2, マスタ[コード], マスタ[名前]), "未登録")XLOOKUP × FILTER
XLOOKUPで1件、FILTERで複数件。場面で使い分けます。
- **1件だけ取得** → XLOOKUP
- **該当する全件取得** → FILTER
TEXTJOINで連結
=TEXTJOIN(", ", TRUE, FILTER(商品[名前], 商品[店舗]="東京店"))SUMIFS × スピル
UNIQUEで作った一覧に対して、SUMIFSで集計すると、動的な集計表が完成します。
# E1: 店舗一覧
=UNIQUE(売上[店舗])
# F1: 各店舗の売上集計
=SUMIF(売上[店舗], E1#, 売上[金額])最新関数を組み合わせると、「元データを変えるだけで自動更新される集計表」が関数だけで作れます。
第7章｜実務例｜動的な集計ダッシュボードを関数だけで作る
シナリオ
売上データから「店舗別売上ランキング」を、ピボットテーブルを使わず、関数だけで自動更新される形で作る。
手順
- 店舗一覧を作る：=UNIQUE(売上[店舗])
- 各店舗の売上合計：=SUMIF(売上[店舗], スピル範囲#, 売上[金額])
- 売上で降順ソート：=SORT(集計範囲, 2, -1)
# 店舗別売上ランキング（1つの数式で完成）
=SORT(HSTACK(UNIQUE(売上[店舗]),SUMIF(売上[店舗],UNIQUE(売上[店舗]), 売上[金額])),2,-1)元データに新しい売上を追加すると、このランキングは**自動で更新**されます。ピボットテーブルの「更新」も不要です。
第8章｜つまずき対処＆まとめ
よくあるトラブル
トラブル1：#NAME? エラーが出る
原因：
使っているExcelのバージョンがその関数に対応していない。
対処：
XLOOKUP・FILTER・LAMBDAはMicrosoft 365・Excel 2021以降が必要。古い環境ではVLOOKUPなど従来関数で代替します。
トラブル2：#SPILL! エラーが出る
原因：
スピルの展開先に既にデータがあり、結果を表示できない。
対処：
スピルが展開する範囲（下や右のセル）を空にします。
トラブル3：LAMBDAが登録できない
対処：
「名前の管理」での参照範囲に、先頭の「=」を付け忘れていないか確認します。また、関数名に既存の関数名やスペースは使えません。
この記事のまとめ
- XLOOKUPはVLOOKUPの完全上位互換（左右検索・複数列・エラー対応）
- スピルで「全行にコピー」が不要に
- FILTER・SORT・UNIQUEで動的なデータ抽出
- LAMBDAでオリジナル関数を作って再利用
- 組み合わせれば、関数だけで自動更新ダッシュボードが作れる
FAQ
Q1. 古いExcelでも使えますか？
XLOOKUP・FILTER・LAMBDAなどはMicrosoft 365・Excel 2021以降が必要です。Excel 2019以前ではVLOOKUP・IFERROR・INDEX/MATCHで代替します。
Q2. VLOOKUPはもう使わなくていい？
新しい環境ならXLOOKUPを優先して構いません。ただし古いファイルや、他者と共有する環境では、互換性のためVLOOKUPが残っている場合もあります。両方知っておくのが安全です。
Q3. LAMBDAは実務でどう役立つ？
「毎回書く複雑な計算」を関数化することで、数式が読みやすくなり、ミスも減ります。税込計算・氏名整形・達成率計算など、定型計算に最適です。
Q4. スピルとテーブル機能、どちらを使うべき？
両方併用できます。テーブルで元データを管理し、スピル関数で集計・抽出する、という組み合わせが強力です。
Q5. これ以上の処理がしたい
数十万行の処理や、複雑な条件分岐が必要になったら、Pythonのpandasへステップアップしましょう。当ブログのPython業務自動化カテゴリで詳しく解説しています。
次にやるべき3つの行動
- **今すぐ**：自分のExcelバージョンを確認（365 or 2021以降か）
- **今日中**：VLOOKUPで書いている数式を1つ、XLOOKUPに書き換えてみる
- **今週中**：FILTER・SORT・UNIQUEで動的な抽出表を1つ作る
最新関数を使いこなせば、Excelの数式は驚くほどシンプルになります。脱・関数初心者の第一歩を、今日から。
最新関数で、Excelの世界が変わる
XLOOKUP・スピル・LAMBDA――これらの新関数は、事務職のExcelスキルを一段引き上げてくれます。
そして、関数で対応が難しくなったら、Pythonへ。Excelの最新関数を極めることは、Pythonへのステップアップにもつながります。
最新の解説記事は、新着記事から順次公開しています。X（旧Twitter）でも更新情報を発信していますので、ぜひフォローしてください。
この記事を書いた人
ソニック｜バックオフィス出身の業務効率化ブロガー。VLOOKUPとIFの組み合わせから、XLOOKUPやLAMBDAへと移行し、Excelの数式を劇的にシンプルにした実体験あり。現在のデータサイエンス業務でも、Excelの最新関数を前処理に活用中。リアルな実体験をもとにしたノウハウを発信中。
コメント