スプレッドシートの上級関数を使いこなす方法｜XLOOKUP・ピボット・配列数式

スプレッドシートの上級関数で仕事が変わる

「SUMとVLOOKUPだけ使っている」「Excelはなんとなく使えるけど、もっと複雑な分析をしたい」という方に向けた記事です。

スプレッドシートの関数スキルを上げると、データ処理の時間が劇的に短縮され、上司や同僚に「データを見てスパッと答えられる人」として認知されるようになります。1つの関数を覚えるだけで数時間の作業が数分に短縮されることも珍しくありません。

今回はExcel・Google Sheetsで使える上級関数を、実際の業務でどう使うかを中心に解説します。

VLOOKUPからXLOOKUPへ：検索関数の進化

VLOOKUPの3つの限界

VLOOKUPは長年定番の関数でしたが、次の弱点があります：

左から右方向にしか検索できない（検索値より左の列を返せない）
見つからない場合に#N/Aエラーが出て見づらい
返せるのは1列のみ（複数列を一度に取得できない）

これらを全部解決したのがXLOOKUPです（Excel 2021以降・Google Sheets対応）。

VLOOKUPとXLOOKUPの違い

機能	VLOOKUP	XLOOKUP
検索方向	左から右のみ	左右どちらでも検索可能
見つからない場合の処理	`#N/A`エラー	任意のテキスト・数値を表示
複数列の返却	1列のみ	複数列を同時に返却可能
構文のわかりやすさ	やや複雑（列番号指定）	直感的（範囲指定）

XLOOKUPの基本構文と活用例

=XLOOKUP(検索値, 検索範囲, 返却範囲, [見つからない場合], [一致モード])

実用例①：商品コードから名前と価格を同時取得

=XLOOKUP(A2, 商品マスタ!A:A, 商品マスタ!B:C, "該当なし")

この1つの数式で、商品名と価格の両方が取得できます。

実用例②：社員番号から名前を左方向に検索

=XLOOKUP(B2, 社員一覧!C:C, 社員一覧!A:A, "社員不明")

VLOOKUPでは不可能だった「右の列にある検索値で、左の列を返す」が簡単にできます。

実用例③：見つからない場合のエラー処理

=XLOOKUP(A2, 商品マスタ!A:A, 商品マスタ!B:B, "登録なし")

見つからない場合に「登録なし」と表示されます。

SUMIFS・COUNTIFS・AVERAGEIFS：条件付き集計の核心

SUMIFSで複数条件の集計を自動化する

SUMIFSは「〇〇かつ〇〇の場合の合計」を計算する強力な関数です。

=SUMIFS(合計範囲, 条件範囲1, 条件1, 条件範囲2, 条件2, ...)

実用例①：東京支店の2024年1月売上合計

=SUMIFS(C:C, A:A, "東京支店", B:B, "2024/01")

実用例②：月間売上が10万円超の案件数をカウント

=COUNTIFS(A:A, "東京", C:C, ">100000")

実用例③：特定担当者の受注平均単価

=AVERAGEIFS(D:D, B:B, "鈴木一郎", C:C, "成約")

SUMPRODUCT：SUMIFSよりもさらに柔軟な集計

SUMPRODUCT関数は配列を掛け合わせて合計する関数で、SUMIFSよりも複雑な条件での集計に使えます。

=SUMPRODUCT((A2:A100="東京")*(B2:B100>100000)*C2:C100)

「東京で、かつ売上が10万円超の合計」を計算します。

ピボットテーブル：大量データ分析の最強ツール

ピボットテーブルで何ができるか

ピボットテーブルは関数ではなく機能ですが、関数と組み合わせることで大量データの多角的分析が可能になります。

ピボットテーブルが特に役立つシーン：

売上データを月別・商品別・担当者別に集計したい
どのカテゴリが伸びているかをビジュアルで確認したい
条件を変えながら様々な軸でデータを見たい

ピボットテーブルの作り方（Excel）

データ範囲を選択（必ずヘッダー行を含む）
「挿入」タブ → 「ピボットテーブル」をクリック
配置場所（新しいシートを推奨）を選択してOK
右パネルで「行・列・値・フィルター」に項目をドラッグ

月別×商品カテゴリ別の売上分析の例：

行：月（日付列を使用、月でグループ化）
列：商品カテゴリ
値：売上金額（合計）
フィルター：担当者名

これだけで、担当者ごとに月×カテゴリの売上マトリックスが完成します。

Google SheetsのピボットテーブルとQUERY関数

Google Sheetsにもピボットテーブルはありますが、さらに強力なのがQUERY関数です。

=QUERY(A:E, "SELECT A, SUM(D) GROUP BY A ORDER BY SUM(D) DESC")

「A列でグループ化して、D列の合計を降順で表示する」という意味です。SQLの知識があれば非常に柔軟な分析が可能です。

実用例：東京支店の上位10件の案件を取得

=QUERY(A:E, "SELECT A, B, D WHERE C='東京支店' ORDER BY D DESC LIMIT 10")

配列数式（ARRAYFORMULA / スピル）：一度の入力で全行に適用

Google SheetsのARRAYFORMULA

通常の数式はひとつのセルにしか結果が出ませんが、ARRAYFORMULAを使うと複数セルに一度に結果を展開できます。

通常の数式（1行ずつ設定が必要）：

C2: =A2*B2
C3: =A3*B3
...（100行あれば100回コピーペーストが必要）

ARRAYFORMULAを使った場合（1回の入力で全行に適用）：

=ARRAYFORMULA(A2:A100*B2:B100)

これだけで、C2〜C100まで一度に計算されます。新しい行が追加されても自動的に計算対象になります。

ExcelのスピルとDYNAMIC ARRAY関数

Excel 365以降では、多くの関数が自動的に複数セルに結果を展開（スピル）します。

FILTER関数（条件でデータを抽出）：

=FILTER(A2:C100, B2:B100="東京", "データなし")

東京のデータだけを自動抽出して一覧表示します。

SORT関数（自動ソート）：

=SORT(A2:C100, 3, -1)

3列目（C列）の値で降順ソートされた表を自動生成します。

TEXT関数と日付関数：表示形式を自在に操る

TEXT関数で数字を見やすく変換

=TEXT(A2, "yyyy年mm月dd日")
→ 例：2025年05月05日

=TEXT(B2, "#,##0円")
→ 例：1,500,000円

=TEXT(C2, "0.0%")
→ 例：15.3%

数値を文字列として特定の書式で表示したいときに使います。

業務でよく使う日付関数

関数	用途	例
`=TODAY()`	今日の日付	期限管理
`=NOW()`	現在の日時	タイムスタンプ
`=EOMONTH(TODAY(),0)`	今月末の日付	締め日管理
`=NETWORKDAYS(A2,B2)`	営業日数の計算	所要日数の計算
`=DATEDIF(A2,TODAY(),"Y")`	年齢・経過年数の計算	勤続年数管理
`=WORKDAY(TODAY(), 5)`	5営業日後の日付	納期計算

条件付き書式でデータを視覚化する

カスタム数式を使った高度な条件付き書式

例①：期限まで3日以内の行を赤くする

=$C2-TODAY()<=3

（C列に期限日が入っている場合）

例②：目標値を達成した行を緑で強調

=$B2>=$C2

（B列が実績、C列が目標の場合）

例③：重複するデータをオレンジで表示

=COUNTIF($A$2:$A$100, A2)>1

設定方法：

対象のセル範囲を選択
「条件付き書式」→「新しいルール」
「数式を使用して書式設定するセルを決定」を選択
数式を入力してフォントカラー・背景色を設定

IFERROR・IFSでエラー処理と分岐をスマートに

IFERRORでエラーを隠す

=IFERROR(VLOOKUP(A2, 一覧!A:B, 2, 0), "登録なし")

VLOOKUPがエラーになった場合に「登録なし」と表示します。

IFS関数で複数条件の分岐をシンプルに

ネストしたIF関数より読みやすいIFS関数：

=IFS(A2>=90, "S", A2>=80, "A", A2>=70, "B", A2>=60, "C", TRUE, "D")

入れ子になったIF文より格段に読みやすくなります。

まとめ

スプレッドシートの上級関数マスターへのロードマップ：

XLOOKUP：VLOOKUPを卒業して検索を自由自在に（最優先で覚える）
SUMIFS/COUNTIFS：複数条件での集計を自動化する
ピボットテーブル：大量データの多角的分析に活用
ARRAYFORMULA/スピル：一度の入力で全行に適用
条件付き書式のカスタム数式：データを視覚的にわかりやすくする

「知っているかどうか」だけで、仕事の速度が2〜5倍変わることがあります。今日の業務でよく使うデータから「SUMIFS」か「XLOOKUP」を1つ試してみてください。使えば使うほど「あの関数も試したい」という気持ちが湧いてきます。