Excel関数は4つから　SUM・IF・COUNTIF・XLOOKUPの基本

Excelの数式は、セルに計算手順を書き、入力値から結果を返す仕組みだ。関数はその計算手順を用途別に定めたもので、初めから多くを覚えるより、合計、条件分岐、条件付き集計、検索という四つの仕事に分けると選びやすい。

結果が表示された事実と、その結果が正しいことは別である。式を入れる前に対象の列と行を決め、入力後は参照範囲、データ形式、共有相手のExcelバージョンを順に確かめる必要がある。

数式の骨格──等号、関数名、引数、セル範囲

Microsoftの日本語版サポートは、Excelの数式が等号で始まり、関数名と括弧内の引数を入力する構造を示している。たとえば、=SUM(A1:A2)では、SUMが関数名、A1:A2が処理対象を示す引数である。

A1は一つのセル、A1:A10はA列の1行目から10行目までの連続範囲を指す。離れた二つの範囲を渡す式では、A1:A10,C1:C10のようにコンマで区切る。入力前に「どの列を読むか」「何行目まで含めるか」「結果をどのセルへ置くか」を決めておくと、式の目的と範囲を照合しやすい。

列ごとのデータ形式も先にそろえる。数字に見えても文字列として保存された値、前後に空白が付いた名称、表記の異なる日付が混じると、合計や条件判定が意図とずれる。原則は1行に1件、1列に1種類の項目であり、小計行や見出しを明細の途中へ入れない表ほど式を追いやすい。

SUM──連続した数値を合計

MicrosoftのSUM関数資料は、単一の値、セル参照、セル範囲、それらの組み合わせを引数に取り、=SUM(A2:A10)や=SUM(A2:A10,C2:C10)の形で合計すると説明している。B2からB10までの金額を合計するなら、式は=SUM(B2:B10)となる。

=B2+B3+B4+B5とセルを一つずつ並べる式は、明細が増えたときに追加行を外しやすい。連続した明細なら=SUM(B2:B5)と範囲で示す方が対象を読み取りやすい。ただし、範囲内に月別小計や前月繰越が入れば二重計上になるため、結果と数件の明細を手作業で突き合わせる。

IF──条件に応じて二つの結果を分岐

MicrosoftのIF関数資料は、IF(論理式, 値が真の場合, 値が偽の場合)という構文を示し、条件の成立時と不成立時に別の値を返すとしている。B2が予算、C2が実績なら、=IF(C2>B2,"予算超過","予算内")で二つの表示を分けられる。式の中で返す文字列は二重引用符で囲み、何も表示しない場合は""を指定する。

最初の練習では、条件を一つに絞る。出勤表の状態欄が「完了」なら1、それ以外なら0を返す式を作り、返した数値をSUMで合計すれば、条件と集計を別々に点検できる。複数の判定を一度に入れる前に、境界となる値、空白セル、表記揺れで結果がどう変わるかを確かめる。

COUNTIF──一つの条件に合うセルを数える

MicrosoftのCOUNTIF資料は、構文をCOUNTIF(範囲, 検索条件)とし、数値、式、セル参照、文字列を検索条件に使う例を掲載している。A2:A100に受注状況が入っている場合、「入金済み」の件数は=COUNTIF(A2:A100,"入金済み")で数える。条件をD1へ置くなら、=COUNTIF(A2:A100,D1)となる。

「1000より大きい」は">1000"、「取消済み以外」は"<>取消済み"のように条件を記す。支店と受注状況の両方を条件にする場合はCOUNTIFSを使う。MicrosoftのCOUNTIFS資料は、追加する各条件範囲について、最初の条件範囲と同じ行数・列数を指定する必要があると明記している。範囲の開始行か終了行が一つずれると、別の明細同士を対応させる式になる。

XLOOKUP──検索値と同じ行からデータを返す

MicrosoftのXLOOKUP資料は、検索値、検索範囲、戻り配列を必須の引数とし、検索列の左右どちらにある列からも、同じ行に対応する値を返す仕組みを示している。商品番号から価格を探す式なら、=XLOOKUP(E2,A2:A100,C2:C100)のE2が検索値、A2:A100が検索範囲、C2:C100が戻り配列となる。

MicrosoftのXLOOKUP資料によると、一致モードを省略した場合は完全一致が既定であり、一致する値がなく、見つからない場合の引数も省くと#N/Aが返る。報告書で未登録を明示するなら、=XLOOKUP(E2,A2:A100,C2:C100,"未登録")と書く。この表示は「対象データが見つからない」状態を示すもので、数式の入力ミスや無効な参照を同じ文言で隠さない。

四つの関数──作業の目的から選択

関数名から考え始めず、出したい結果を一文にする。「一列の金額を合計する」ならSUM、「基準を超えた行に表示を付ける」ならIF、「特定の状態を数える」ならCOUNTIF、「番号に対応する名称を取り出す」ならXLOOKUPである。入力範囲と出力先も同じ時点で決める。

作業	最初に使う関数	入力前の確認
金額、時間、数量を合計する	SUM	明細の開始行と終了行、小計の有無
基準や状態で二つの結果に分ける	IF	判定条件、成立時と不成立時の戻り値
一つの条件に合う件数を数える	COUNTIF	条件の表記と対象範囲のデータ形式
番号から名称、価格、担当部署を探す	XLOOKUP	検索範囲と戻り配列の行対応

検算──値が出ても範囲とデータへ戻る

確認は、関数名と括弧、引数の区切り、参照範囲、元データ、表示形式の順に進める。Microsoftの数式エラー資料は、#N/A、#NAME?、#REF!、#VALUE!などを挙げ、関数や構文、セル参照、データ型によって原因が異なると説明している。関数名の誤記なら#NAME?、削除したセルへの参照なら#REF!というように、表示された種類から調べる場所を絞る。

互換性は式の外側にある確認項目だ。MicrosoftのXLOOKUP資料は、同関数をExcel 2016とExcel 2019では利用できないと明記する一方、新しい版で作成されたXLOOKUP入りのブックを旧版で扱う場面がありうるとしている。共有するブックへXLOOKUPを入れる前に、相手が使う製品名とバージョンを尋ねる。日本国内の利用者をバージョン別に集計したMicrosoftの公開資料は、本稿の執筆時点で確認できていないため、普及率から相手の環境を推測するのは避ける。

練習──10行の受注表で一つずつ検証

受注番号、日付、支店、状況、金額の5列を用意し、2行目から10行目までに架空の明細を入れる。最初に=SUM(E2:E10)で金額を合計し、次に=IF(E2>1000,"高額","通常")で区分する。続いて=COUNTIF(C2:C10,"東京")で指定支店の件数を数え、別シートの価格表から=XLOOKUP(A2,価格表!A:A,価格表!B:B,"未登録")で価格を返す。

各段階で明細を一件だけ変え、結果が予想どおり動くかを見る。金額を文字列へ変える、支店名の末尾へ空白を入れる、検索表から番号を一つ消すといった変更も、形式不一致や未登録を見つける練習になる。最後に元の値へ戻し、式と期待値を並べて記録する。

受け渡し前の確認表には、等号、関数名、括弧、範囲の始点と終点、条件文字列、空白、戻り値、共有先のバージョンを並べる。別シートや別ブックを参照する場合は、シート名、ファイル名、保存場所も加える。本文の例は整形済みの小さな表を前提としており、外部接続や共同編集で起きる更新競合は別に検証が要る。

よくある質問

Excel関数は何から覚えるべきか
等号、関数名、引数、セル範囲の関係を確認し、連続範囲を扱うSUMから始める。次に作業の目的へ合わせてIF、COUNTIF、XLOOKUPを加え、各式を元データと照合する。

数値が表示されたのに結果が誤るのはなぜか
式は指定された範囲と入力値を使って計算する。範囲から明細が漏れた場合、文字列の数値や余分な空白が混じった場合、小計まで含めた場合は、エラー表示がなくても意図と異なる値になりうる。

Excel 2016やExcel 2019でXLOOKUPは使えるか
Microsoftの日本語版サポートは、XLOOKUPをExcel 2016とExcel 2019では利用できないとしている。新しい版で作ったブックを渡す前に、受け取る側のExcel環境を個別に確認する。