関数テクニック 総合ガイド
検索・抽出・整形・集計を、VBAなしで再現性高く回すための関数体系をまとめています。
🧩 読者の課題
- 検索条件が増えるたびに式が壊れる。
- 抽出や並べ替えを毎回手作業でやっている。
- Excel 2016/2019 と 2021/2024/Microsoft 365 で使える式を切り分けたい。
- 複数条件検索・文字分割・重複除去を標準化したい。
✅ このカテゴリで解決できること
- XLOOKUP / XMATCH による堅牢な検索設計。
- FILTER / UNIQUE / SORT 系で抽出作業を自動化。
- LET / IFERROR で長い式の可読性と保守性を向上。
- 旧式関数(INDEX+MATCH 等)でレガシー環境も対応。
📐 関数テクニック
モダンExcel関数から旧バージョン対応の王道手法まで。関数ごとの対応差も含めて整理しています。
GROUPBYについて: Microsoft公式一覧では「Microsoft 365」表記です。数式バーで候補に表示される環境ではGROUPBYの記事を、表示されない環境ではSUMIFSの代替記事を利用してください。
※ Lvは一般的なExcel技能の共通基準ではなく、このカテゴリ内で必要になる関数の組み合わせや設計判断を含む相対的な目安です。
1. モダンExcel関数(Excel 2021・2024・Microsoft 365中心/関数ごとに差あり)
🔍 検索・抽出・配列系
-
XLOOKUP 基本編
Excel 2021 / 2024 / Microsoft 365 で使える検索関数の決定版。VLOOKUPの弱点を克服。
-
XLOOKUP 応用編
第3引数(戻り配列)に複数列の範囲を指定するだけで、ヒットした行の複数列を横並びに一気に展開できます。
-
XLOOKUP ネスト(多条件検索)
「店舗」と「商品名」など、複数条件の組み合わせが完全一致する行を探し出します。
-
検索列2列を1つの検索値で探す方法
Aシートの東京001を、マスタ表の東京列+001列に一致。表を増やさず使うメリットとケース別の式も整理します。
-
XLOOKUP比較編:VLOOKUP・INDEX+MATCHとの使い分け
VLOOKUPの弱点、INDEX+MATCHの強み、XLOOKUPの便利さを比較して使い分けます。
-
XMATCH関数
リストの中から特定の値を探し、それが上から何番目にあるかを安全・確実に取得します。
-
CHOOSECOLS関数 — 離れた列を自在に抽出
指定した配列から、必要な列番号だけを選んでスピル展開する関数です。
-
TOROW・WRAPROWS・CHOOSECOLSで複数行データを1行に整形
複数行に分かれた1件分のデータを横一列にし、必要な列だけの明細表へ組み直します。
-
FILTER関数
条件に合うデータを複数件まとめて動的抽出します。抽出作業の自動化に非常に強力です。
-
UNIQUE関数
重複を削除して一意のリストを作る基本、スピル、複数列の判定方法を解説します。
-
UNIQUE×FILTERで条件付きリストを作る
条件に合うデータだけを抽出し、その結果から重複のない一覧を作ります。
-
UNIQUE×FILTER×SORTで条件付きリストを並び替える
条件で絞り、重複を消し、SORTであいうえお順に並べる3関数ネストを丁寧に解説します。
-
SORT / SORTBY関数
抽出結果や一覧を、条件に応じて自動で並べ替えます。
📊 集計・分析系
-
GROUPBY関数で月別集計|社員番号別の集計も解説
年月や社員番号の組み合わせを自動で見つけ、ピボットテーブルのような集計を1つの数式で展開します。未対応環境向けのSUMIFS代替記事も用意しています。
🧠 論理・条件分岐系
-
LET関数で可読性を上げる
長大で読めない数式を変数名で整理し、同じ計算の繰り返しを減らして速度改善にもつなげます。
-
IFS関数
条件が多段階あるとき、IF関数の深いネストを避けて読みやすく分岐できます。
-
LET + IF の複雑ロジック
複雑なIF条件や結果値をLET関数の変数で整理し、見やすく・保守しやすくします。
-
LET深掘り②:SORT・SORTBYの重複計算を減らす
ソート結果を変数化して再利用し、INDEX・SEQUENCEで上位件数を抽出します。Excel 2021対応、該当0件の分岐付き。
-
SWITCH関数
特定の値に対して決まった結果を返す処理を、IFよりすっきり書けます。
🔤 文字列操作系
-
TEXTSPLIT
カンマ区切りや区切り文字つきの文字列を、複数セルに一気に分割します。
-
TEXTJOIN
複数セルの文字列を、指定した区切り文字でまとめます。空白セルを無視する設定も可能です。
-
CONCAT vs CONCATENATE vs「&」徹底比較
Excelで文字列を結合する方法を比較し、実務でどれを使うべきか整理します。
-
TEXTBEFORE / TEXTAFTER関数
文字列から、特定の区切り文字の前後だけを抜き出します。
📅 日付・その他
-
SEQUENCE で日付リスト自動生成
連番、日付、月初、時間割を1セルからスピル生成する方法を図解します。
2. 旧式の関数(Excel 2016以前から使える王道手法)
📊 集計・集計表系
- COUNTIFS・SUMIFS:複数条件の件数と合計
複数条件の件数・合計・集計表を作る基本を解説します。基本式はExcel 2016以降、MAXIFS・MINIFSの補足は2019以降。
- ROUND関数:四捨五入・切り上げ・切り捨てと消費税の端数処理
ROUND・ROUNDUP・ROUNDDOWNの桁数の指定、消費税の端数処理、表示形式との違い、合計が合わないときの直し方を解説します。
- SUBTOTAL関数:フィルターで絞り込んだ行だけ合計・件数を出す
フィルターで絞り込んだ行だけを集計します。9と109の違い、絞り込んでも崩れない連番、条件との組み合わせ、AGGREGATEとの使い分けを解説します。
-
SUMIFS関数で月別集計|社員番号別の集計も解説
GROUPBYが使えない環境でも、月初日とEDATEを使って月別・社員番号別の合計を安全に作れます。
-
EDATE・EOMONTH・SUMIFSで月別集計
月初・月末・翌月初日の作り方と、時刻入り日付も漏らさないSUMIFS条件を詳しく解説します。
🔍 検索・抽出系
-
INDEX + MATCH(Excel 2016以降対応)
検索キーより左側にある列のデータを取得したい場合など、旧バージョンで強力な検索手法です。
-
VLOOKUP完全ガイド — 基本から限界まで
VLOOKUPの基本構文、よくあるエラー、列番号の落とし穴、使ってはいけない場面まで整理します。
-
INDEX + MATCH + 結合キー列
複数条件の組み合わせで検索したいとき、旧Excelでも安定して実装できます。
-
A列とB列を結合して検索する方法:作業列なしのINDEX+MATCH
A列とB列を数式の中で結合し、1つのセルの値をMATCHで探します。作業列なしの手順と、作業列との負荷の違いを説明します。
-
行の一番右にある最終列を求める方法:途中に空白があっても使える数式
1行の中で一番右に入力があるセルの位置を、MATCHとINDEXで求めます。最終列の値、次の空き列、COUNTAとの違いも説明します。
-
行の中で最初に見つけたセルの右隣を取り出す方法:空白・入力・特定の文字
1行を左から右へ探し、最初の入力・空白・特定の文字が見つかったセルの右隣を、INDEXとMATCHで取り出します。
-
INDEX:INDEXで可変範囲を作る:SUMとMATCHで使う参照の仕組み
INDEXが返すセル参照を「:」でつなぎ、集計範囲や検索範囲を数式で切り替えます。相対位置の変換と、逆転・未一致・重複の動作も確認します。
-
INDEX&INDEXと複合検索:作業列なしの検索と45万行の実測
複合キー、複数条件、複数列の一括取得を作業列なしで書く方法と、Ctrl+Shift+Enterが必要な条件を整理します。45万行の実測で作業列方式とも比較します。
-
複数条件に合う行だけ連番を振る方法
COUNTIFSで連番の土台を作り、必要なときだけIFで対象外を空白にする考え方を解説します。
🔥 検索・抽出【複合関数編】
-
① 旧ExcelでFILTER関数を代用|ROW・SMALL・INDEX
まずはこちら。ROW・SMALL・INDEXの動きを図で追い、旧Excelの複数抽出を土台から理解します。
-
② 【FILTER代替】4つの複数抽出方式を比較
基本を理解した次はこちら。COUNTIF、ROW、AGGREGATE、上行参照方式のメリットと速度を比較します。
-
住所→郵便番号 逆引き(最長一致検索)
郵便番号簿からINDEX+MATCH+MAX+LEFT+LENで最長一致検索を行う高度な関数テクニック。
🧠 論理・文字列・その他
-
IFERROR vs IFNA の使い分け
#N/Aだけを処理したいのか、他のエラーもまとめて処理したいのかで使い分けます。
-
ERROR.TYPE による分岐
セルのエラー種類によって表示メッセージや処理を変えたい場合に使います。
-
SUBSTITUTE 多段ネスト
複数の文字置換を一気に済ませたいときの定番テクニックです。
-
PHONETIC関数でフリガナを取り出す:カタカナ・ひらがな・半角への変換
氏名からフリガナを取り出し、全角カタカナ・半角カナ・ひらがなにそろえます。貼り付けた名簿で漢字のまま返るときの対処と、読みの確認まで。
-
MID / MIDB関数 — 文字数やバイト数で文字列を抽出する
文字列の途中から指定した分だけテキストを抜き出す関数です。
-
和暦を西暦日付へ変換|DATEVALUE関数
昭・平・令付きの和暦文字列を、DATEVALUEで日付シリアル値へ変換します。
-
西暦8桁YYYYMMDDを日付へ変換|DATE関数
19781215のような西暦8桁を、年・月・日に分解して日付シリアル値へ変換します。
-
TEXT関数による和暦変換
日付を令和表記や省略形式に変換する表示テクニックです。
-
DATEDIFの注意点
年齢や勤続年数の計算で便利ですが、仕様に注意が必要な関数です。
-
【要注意】揮発性関数とは?Excelが重くなるのを防ぐ
INDIRECT・OFFSETなどの揮発性関数による再計算負荷を理解し、重いブックを防ぎます。
🛣️ 推奨学習ルート
- 1. XLOOKUP 基本編
まずは検索の新しい標準を理解。
- 2. FILTER関数
条件抽出の自動化を導入。
- 3. LET関数
複雑な式を保守可能な形へ。
- 4. INDEX + MATCH
旧バージョン環境の実務解。
- 5. XLOOKUP 応用編
複数列返却と高度設定で中級へ。
❓ FAQ
Q. Excel 2016でも学習できますか?
A. はい。旧式関数セクションを用意しており、INDEX+MATCHやCOUNTIFS系の代替ルートを併記しています。
Q. XLOOKUPが使えない環境ではどうしますか?
A. INDEX+MATCH と IFERROR の組み合わせで同等ロジックを構築できます。カテゴリ内に比較記事を用意しています。
Q. 関数だけで自動化のどこまで可能ですか?
A. 検索、抽出、整形、重複排除、分類、一部の集計まで可能です。外部入出力の自動化は Power Query や VBA 領域です。
Q. 難易度タグはどう見ればいいですか?
A. Lv.1〜2は導入、Lv.3〜5は実務定着、Lv.6以上は複数技術の組み合わせを想定しています。
Q. 即戦力タグと応用例タグの違いは?
A. 即戦力は導入直後に効果が出る手法、応用例は複数の機能を組み合わせる手法を示します。
🔗 関連カテゴリへ
- 🔄 パワークエリ総合
取り込みから更新運用までを一気通貫。
- 🏭 COBOL帳票処理総合
固定長・不規則帳票をExcelで実務化。
- ⚡ QR Automation Lab総合
VBA不要の入力自動化と閉域転送。
- 🧰 現場実務ラボ総合
速度・印刷・UI改善の実戦ノウハウ。
- 🔓 制限解除ラボ総合
VBA可環境で最後の5%を補完。