すべてのプロが知っておくべき必須スプレッドシート数式

スプレッドシートの財務データ

スプレッドシートは世界で最も広く使われているプログラミング環境です。何百万ものプロフェッショナルが、3年前のチュートリアルで半分だけ覚えた数式をもとに重大な意思決定を行っています。核となる数式をマスターすることで、スプレッドシートは単なる豪華な表から強力なデータ分析ツールへと生まれ変わります。

数式の基本構造

すべての数式は = で始まり、以下を参照できます：

セル参照: A1、B2:D10（相対参照）または $A$1、$B$2:$D$10（絶対参照）
名前付き範囲: =SUM(B2:B100) の代わりに =SUM(Revenue)
関数: SUM、VLOOKUP、IF、TEXT など
定数: 数値、引用符で囲んだテキスト、TRUE/FALSE

相対参照と絶対参照： 数式をコピーすると、相対参照はずれます。絶対参照（$ 付き）は固定されたままです。

=B2*C2       → 下にコピーすると =B3*C3、=B4*C4... となる
=B2*$C$1     → C1 は固定（例：税率）、B2 はずれる

検索数式

VLOOKUP

=VLOOKUP(lookup_value, table_array, col_index, [range_lookup])

表の先頭列で値を検索し、別の列の値を返します：

=VLOOKUP(A2, $E$2:$G$100, 2, FALSE)

A2 — 検索する値
$E$2:$G$100 — テーブル（$ でロック）
2 — テーブルの2列目を返す
FALSE — 完全一致（検索には常に FALSE を使用）

VLOOKUPの制限： 左から右方向にしか検索できず、列を挿入するとインデックス番号がずれます。

INDEX / MATCH（より優れた代替手段）

=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))

VLOOKUPより柔軟で、あらゆる方向に検索できます：

=INDEX($G$2:$G$100, MATCH(A2, $E$2:$E$100, 0))

E列がA2と一致する行のG列の値を返します。列を挿入しても壊れません。

XLOOKUP（モダンな Excel / Google Sheets）

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found])

両者の現代的な後継関数：

=XLOOKUP(A2, $E$2:$E$100, $G$2:$G$100, "Not found")

構文がシンプルで、見つからない場合も適切に処理でき、あらゆる方向に機能します。

条件付き数式

IF

=IF(condition, value_if_true, value_if_false)
=IF(B2>1000, "High", "Low")
=IF(C2="", "Missing", C2)

ネストされた IF（代わりに IFS を使用）

=IF(B2>=90,"A", IF(B2>=80,"B", IF(B2>=70,"C","F")))

-- IFS を使うとよりシンプルに：
=IFS(B2>=90,"A", B2>=80,"B", B2>=70,"C", TRUE,"F")

条件での AND / OR

=IF(AND(B2>100, C2="Active"), "Priority", "Normal")
=IF(OR(D2="VIP", E2>10000), "Discount", "")

集計数式

SUMIF / SUMIFS

条件に合う値を合計する：

=SUMIF(range, criteria, sum_range)
=SUMIF(B:B, "Electronics", C:C)   -- B = "Electronics" の行のCを合計

-- 複数条件：
=SUMIFS(C:C, B:B, "Electronics", D:D, ">2025-01-01")

COUNTIF / COUNTIFS

=COUNTIF(A:A, "Active")           -- A = "Active" の行数をカウント
=COUNTIF(B:B, ">1000")            -- B > 1000 の行数をカウント
=COUNTIFS(A:A, "Active", C:C, "US")  -- 両方の条件

AVERAGEIF

=AVERAGEIF(B:B, "Electronics", C:C)  -- B = "Electronics" のCの平均

テキスト数式

テキストの結合

=CONCATENATE(A2, " ", B2)          -- 旧構文
=A2 & " " & B2                     -- モダン構文（推奨）
=TEXTJOIN(", ", TRUE, A2:A10)      -- 区切り文字で結合、空白はスキップ

テキストの抽出

=LEFT(A2, 3)                       -- 先頭3文字
=RIGHT(A2, 4)                      -- 末尾4文字
=MID(A2, 3, 5)                     -- 3文字目から5文字
=LEN(A2)                           -- 文字数の合計
=FIND("@", A2)                     -- 文字列内の @ の位置

テキストのクリーニング

=TRIM(A2)                          -- 先頭・末尾のスペースを削除
=UPPER(A2) / =LOWER(A2)           -- 大文字・小文字に変換
=PROPER(A2)                        -- 先頭文字を大文字に
=SUBSTITUTE(A2, " ", "_")         -- スペースをアンダースコアに置換
=CLEAN(A2)                         -- 印刷できない文字を削除

日付と時刻の数式

=TODAY()                           -- 今日の日付
=NOW()                             -- 現在の日付と時刻
=YEAR(A2) / =MONTH(A2) / =DAY(A2) -- 各部分を抽出
=DATEDIF(A2, B2, "D")             -- 2つの日付の間の日数
=DATEDIF(A2, B2, "M")             -- 2つの日付の間の完全な月数
=DATEDIF(A2, B2, "Y")             -- 完全な年数（年齢計算に便利）
=NETWORKDAYS(A2, B2)              -- 2つの日付の間の営業日数（週末を除く）
=EDATE(A2, 3)                     -- A2 から3ヶ月後の日付
=EOMONTH(A2, 0)                   -- A2 の月の最終日

データの活用：実践的なパターン

UNIQUE で重複を削除（モダン）

=UNIQUE(A2:A100)                   -- ユニークな値のリスト

FILTER で動的配列を作成

=FILTER(A2:C100, B2:B100="Active")   -- B = "Active" の行
=FILTER(A2:C100, (B2:B100="Active")*(C2:C100>1000))  -- 両方の条件

SORT で動的に並べ替え

=SORT(A2:C100, 2, -1)              -- 2列目で降順に並べ替え

データの変換とエクスポート

スプレッドシートの作業が完了し、別のシステムにデータを移す必要がある場合：

Excel to CSV コンバーターで汎用的な CSV にエクスポート
CSV Merger で複数の CSV ファイルを結合
JSON to Excel で API 連携用の JSON に変換（双方向対応）

避けるべきよくある間違い

数式内に値をハードコードする — 代わりにセル参照を使用しましょう。=B2*0.08 は後で意味がわからなくなります。D1に「税率」とラベルをつけて =B2*$D$1 とすれば自己説明的になります。
コピー時に参照をロックしない — $ を忘れると、数式が間違ったセルを参照します。
TRUE（近似一致）で VLOOKUP を使う — データが並び替えられている必要があり、並び替えられていない場合は予想外の結果を返します。
循環参照 — 自分自身（またはそれを参照するセル）を参照する数式。Excel が警告を表示します。
データ型の混在 — 数値をテキストとして保存する（セル内で左揃えになる）と、SUM や並べ替えが機能しなくなります。

スプレッドシートのマスターは強力な武器です。上記の数式で実際の業務データ作業の90%をカバーできます。実際のデータセットで練習すれば、複雑な数式も自然と使いこなせるようになります。