Excelでよく使う関数には、合計を求める「SUM」、条件によって結果を変える「IF」、条件に合うデータを集計する「COUNTIF」「SUMIFS」、表から情報を探す「XLOOKUP」などがあります。
ここでは、仕事で役立つExcel関数40個を、基本の書き方・数式例・計算結果付きの一覧表にまとめました。引数に何を入力すればよいかを確認でき、似た関数の使い分けや実務での活用方法も分かる構成です。
Excelでよく使う関数40個の一覧表
関数は、やりたい作業に合わせて探すと選びやすくなります。40個の関数を5つのグループに分けました。
| 分類 | 関数の数 | 主な用途 |
|---|---|---|
| 合計・平均・件数 | 11個 | 売上集計、件数のカウント |
| 条件判定・四捨五入 | 8個 | 合否判定、金額の端数処理 |
| 検索・抽出 | 5個 | 商品検索、名簿の照合 |
| 文字列の加工 | 8個 | コードの抽出、文字の置換 |
| 日付の計算 | 8個 | 月末日、納期、営業日数 |
| 合計 | 40個 |
初めて関数を覚えるなら、まずは下記のものがおすすめです。
- SUM
- IF
- COUNTIF
- SUMIFS
- XLOOKUP(またはVLOOKUP)
エラー処理に使うIFERRORも覚えておくと便利ですが、使用前にエラーの原因を確認することが基本です。
数式例で使用するサンプルデータ
関数を実際に試せるよう、次の売上表を使用します。
| 商品コード | 商品名 | 担当者 | 数量 | 売上 | 売上日 |
|---|---|---|---|---|---|
| A01 | ノート | 田中 | 3 | 900 | 2026/11/02 |
| A02 | ペン | 佐藤 | 5 | 750 | 2026/11/05 |
| A03 | ファイル | 田中 | 2 | 1,200 | 2026/11/10 |
| A04 | ノート | 佐藤 | 4 | 1,200 | 2026/11/15 |
| A05 | ペン | 田中 | 1 | 150 | 2026/10/29 |
自分のExcelで試したい場合は、以下のデータをコピーしてA1セルに貼り付けてください。タブ区切りなので、A〜F列に分かれて入力できます。
商品コード 商品名 担当者 数量 売上 売上日
A01 ノート 田中 3 900 2026/11/02
A02 ペン 佐藤 5 750 2026/11/05
A03 ファイル 田中 2 1200 2026/11/10
A04 ノート 佐藤 4 1200 2026/11/15
A05 ペン 田中 1 150 2026/10/29
1行目が見出し、2〜6行目がデータです。日付の計算を試す際は、F列がExcelの日付データとして認識されていることを確認してください。
一覧表の「基本の書き方」には、各引数に指定する内容を日本語で記載しています。例えば、=SUM(数値1,[数値2],…)の[ ]は省略可能な引数、…は追加できる引数を示します。
数式は=から始め、複数の引数を,で区切ります。E2:E6はE2からE6までのセル範囲、"田中"は文字列の指定です。
1. 合計・平均・件数を求める関数11個
売上集計、勤怠管理、アンケート集計などで使う関数です。
| 関数・用途 | 基本の書き方 | 数式例 | 結果 |
|---|---|---|---|
| SUM:合計 | =SUM(数値1,[数値2],…) | =SUM(E2:E6) | 4,200 |
| AVERAGE:平均 | =AVERAGE(数値1,[数値2],…) | =AVERAGE(E2:E6) | 840 |
| MAX:最大値 | =MAX(数値1,[数値2],…) | =MAX(E2:E6) | 1,200 |
| MIN:最小値 | =MIN(数値1,[数値2],…) | =MIN(E2:E6) | 150 |
| COUNT:数値セルの件数 | =COUNT(値1,[値2],…) | =COUNT(D2:D6) | 5 |
| COUNTA:空白以外の件数 | =COUNTA(値1,[値2],…) | =COUNTA(B2:B6) | 5 |
| COUNTIF:1条件の件数 | =COUNTIF(条件範囲,条件) | =COUNTIF(C2:C6,"田中") | 3 |
| COUNTIFS:複数条件の件数 | =COUNTIFS(条件範囲1,条件1,[条件範囲2,条件2],…) | =COUNTIFS(C2:C6,"田中",D2:D6,">=2") | 2 |
| SUMIF:1条件の合計 | =SUMIF(条件範囲,条件,[合計範囲]) | =SUMIF(B2:B6,"ノート",E2:E6) | 2,100 |
| SUMIFS:複数条件の合計 | =SUMIFS(合計範囲,条件範囲1,条件1,[条件範囲2,条件2],…) | =SUMIFS(E2:E6,C2:C6,"田中",B2:B6,"ノート") | 900 |
| SUBTOTAL:小計・集計 | =SUBTOTAL(集計番号,範囲1,[範囲2],…) | =SUBTOTAL(109,E2:E6) | 4,200 |
SUM、AVERAGE、MAX、MINなどには、数値そのものやセル参照、セル範囲を指定できます。
COUNTIFやSUMIFの「条件」には、特定の文字列や数値、比較条件を指定します。例えば、"田中"なら田中と一致するセル、">=2"なら2以上の数値が入ったセルが対象になります。
SUBTOTALは、Excelのフィルターでデータを絞り込んだときに便利です。最初の引数によって、手動で非表示にした行の扱いが変わります。
| 数式 | 集計対象 |
|---|---|
=SUBTOTAL(9,E2:E6) | フィルターで隠れた行を除外し、手動非表示の行は含む |
=SUBTOTAL(109,E2:E6) | フィルターと手動非表示の両方を除外する |
表の計算結果4,200は、すべての行が表示されている場合の値です。担当者が田中さんの行だけをフィルターで表示すると、どちらも2,250になります。
集計番号の詳細は、Microsoft公式のSUBTOTAL関数の解説で確認できます。
2. 条件判定・四捨五入に使う関数8個
条件によって表示内容を切り替えたり、計算結果の端数を処理したりする関数です。
| 関数・用途 | 基本の書き方 | 数式例 | 結果 |
|---|---|---|---|
| IF:条件判定 | =IF(条件,成立時の値,[不成立時の値]) | =IF(D2>=3,"対象","対象外") | 対象 |
| IFS:複数条件の判定 | =IFS(条件1,結果1,[条件2,結果2],…) | =IFS(E2>=1200,"高",E2>=800,"中",TRUE,"低") | 中 |
| AND:すべての条件 | =AND(条件1,[条件2],…) | =AND(D2>=3,E2>=800) | TRUE |
| OR:いずれかの条件 | =OR(条件1,[条件2],…) | =OR(C2="佐藤",D2>=3) | TRUE |
| IFERROR:エラー処理 | =IFERROR(計算式,エラー時の値) | =IFERROR(VLOOKUP("A99",A2:E6,2,FALSE),"該当なし") | 該当なし |
| ROUND:四捨五入 | =ROUND(数値,桁数) | =ROUND(1234.567,1) | 1,234.6 |
| ROUNDUP:切り上げ | =ROUNDUP(数値,桁数) | =ROUNDUP(1234.1,0) | 1,235 |
| ROUNDDOWN:切り捨て | =ROUNDDOWN(数値,桁数) | =ROUNDDOWN(1234.9,0) | 1,234 |
ANDはすべての条件を満たした場合、ORはいずれかの条件を満たした場合にTRUEを返します。
IF関数と組み合わせれば、複数の条件に応じて表示内容を変更できます。
=IF(AND(D2>=3,E2>=800),"対象","対象外")
この式は、数量が3以上、かつ売上が800以上の場合に「対象」と表示します。
IFSは上から順に条件を判定し、最初に満たした条件の結果を返します。数式例の最後にあるTRUE,"低"は、それ以前の条件に当てはまらなかった場合に「低」を返す指定です。
ROUND系の関数では、第2引数で丸める位置を指定します。
| 桁数 | 処理する位置 | ROUNDの計算結果 |
|---|---|---|
| 2 | 小数第2位まで残す | =ROUND(1234.567,2) → 1,234.57 |
| 1 | 小数第1位まで残す | =ROUND(1234.567,1) → 1,234.6 |
| 0 | 整数にする | =ROUND(1234.567,0) → 1,235 |
| -1 | 10単位で丸める | =ROUND(1234.567,-1) → 1,230 |
| -2 | 100単位で丸める | =ROUND(1234.567,-2) → 1,200 |
ROUNDUPは、切り上げ対象の端数があれば0から遠ざかる方向に切り上げます。ROUNDDOWNは、0に近づく方向に切り捨てます。
3. 表から検索・抽出する関数5個
商品コードから商品名を調べる、社員番号に対応する氏名を取得する、条件に合うデータだけを抜き出すといった作業に使います。
| 関数・用途 | 基本の書き方 | 数式例 | 結果 |
|---|---|---|---|
| XLOOKUP:データ検索 | =XLOOKUP(検索値,検索範囲,戻り範囲,[見つからない場合],[一致モード],[検索モード]) | =XLOOKUP("A03",A2:A6,B2:B6,"なし") | ファイル |
| VLOOKUP:表の左端列から検索 | =VLOOKUP(検索値,表範囲,列番号,[検索方法]) | =VLOOKUP("A03",A2:E6,2,FALSE) | ファイル |
| FILTER:条件に合う行を抽出 | =FILTER(抽出範囲,条件,[該当なしの表示]) | =FILTER(A2:F6,C2:C6="田中","なし") | 田中さんの3行 |
| UNIQUE:重複を除いた一覧 | =UNIQUE(範囲,[列を比較],[1回だけ出現する値に限定]) | =UNIQUE(B2:B6) | 3種類の商品名 |
| SORT:データの並べ替え | =SORT(範囲,[基準列],[順序],[並べ替え方向]) | =SORT(A2:F6,5,-1) | 売上の高い順 |
FILTERの例では、田中さんが担当したA01・A03・A05の3行を抽出します。UNIQUEは「ノート」「ペン」「ファイル」の3種類を返します。
SORTの第2引数は基準となる列番号、第3引数は並べ替え順序です。1が昇順、-1が降順なので、表の例では5列目の売上を高い順に並べ替えます。
FILTER・UNIQUE・SORTは、複数セルに結果を表示する関数です。 例えば、FILTERの式をA10に入力すると、A10:F12に結果が展開されます。UNIQUEはH12、SORTはA20など、それぞれ十分な空きがある場所で試してください。
対応するExcelのバージョンにも違いがあります。
| 関数 | 主な対応バージョン |
|---|---|
| XLOOKUP | Microsoft 365、Excel 2021・2024 |
| VLOOKUP | Microsoft 365、Excel 2024および旧バージョン |
| FILTER | Microsoft 365、Excel 2021・2024 |
| UNIQUE | Microsoft 365、Excel 2021・2024 |
| SORT | Microsoft 365、Excel 2021・2024 |
XLOOKUP・FILTER・UNIQUE・SORTは、Excel 2019以前の買い切り版では使用できません。
なお、関数が使えるバージョンと、Microsoftのサポート対象期間は別の情報です。Office 2021のサポートは2026年10月13日に終了しています。詳しくはMicrosoft公式のサポート終了案内で確認できます。
4. 文字列を取り出す・加工する関数8個
商品コードの一部を抜き出す、余分な空白を削除する、複数のセルの内容をつなげるといった作業に役立ちます。
| 関数・用途 | 基本の書き方 | 数式例 | 結果 |
|---|---|---|---|
| LEFT:左から取り出す | =LEFT(文字列,[文字数]) | =LEFT(A2,1) | A |
| RIGHT:右から取り出す | =RIGHT(文字列,[文字数]) | =RIGHT(A2,2) | 01 |
| MID:途中から取り出す | =MID(文字列,開始位置,文字数) | =MID(A2,2,2) | 01 |
| LEN:文字数を数える | =LEN(文字列) | =LEN(B2) | 3 |
| TRIM:余分な空白を整理 | =TRIM(文字列) | =TRIM(" ノート ") | ノート |
| SUBSTITUTE:文字を置換 | =SUBSTITUTE(文字列,検索文字,置換文字,[置換番号]) | =SUBSTITUTE("A-01","-","") | A01 |
| TEXT:表示形式を指定 | =TEXT(値,表示形式) | =TEXT(E5,"#,##0") | 1,200(文字列) |
| TEXTJOIN:文字列を結合 | =TEXTJOIN(区切り文字,空白を無視,文字列1,[文字列2],…) | =TEXTJOIN("、",TRUE,B2:B4) | ノート、ペン、ファイル |
LEFTとRIGHTは、取り出す文字数を省略すると1文字を返します。
MIDの開始位置は1文字目を1として数えます。A2に「A01」が入力されている場合、=MID(A2,2,2)は2文字目から2文字を取り出し、「01」を返します。
SUBSTITUTEの第4引数は、同じ文字が複数登場するとき、何番目を置き換えるかを指定するものです。省略すると、指定した文字をすべて置き換えます。
TRIMは、文字列の先頭・末尾にある余分な半角スペースを削除し、単語間に連続する半角スペースを1つに整えます。
全角スペースを確実に処理したい場合は、次のようにSUBSTITUTEと組み合わせます。
=TRIM(SUBSTITUTE(A2," "," "))
引用符の中の最初の空白は全角スペース1文字、次の空白は半角スペース1文字です。全角を半角に置き換えたうえで、余分な半角スペースを整理します。
また、Webページからコピーした文字列には、通常の半角スペースと異なる「改行しないスペース」が含まれることがあります。これは文字コード160の空白で、TRIMだけでは取り除けません。
その場合は、次の式が使えます。
=TRIM(SUBSTITUTE(A2,CHAR(160)," "))
詳しくはMicrosoft公式のTRIM関数の説明で確認できます。
TEXTは数値や日付を指定した書式の文字列に変換します。数値の1200を「1,200」と表示するほか、日本語環境のExcelでは次のように曜日を表示することもできます。
=TEXT(F2,"aaa")
F2が2026年11月2日なら「月」を返します。書式記号や表示結果は言語設定によって異なる場合があります。
TEXTで変換した結果は文字列なので、後続の計算で数値を使う場合は、元のセルを参照するか、セルの表示形式を利用するとよいでしょう。
TEXTJOINでは、第2引数のTRUEが「空白セルを無視する」という指定です。2つのセルを単純につなぐだけなら、=A2&B2と書く方法もあります。
TEXTJOINはMicrosoft 365およびExcel 2019以降で使用できます。
5. 日付を計算・管理する関数8個
請求書の日付、月次資料、納期管理などで使います。
| 関数・用途 | 基本の書き方 | 数式例 | 結果 |
|---|---|---|---|
| TODAY:今日の日付 | =TODAY() | =TODAY() | 今日の日付 |
| NOW:現在日時 | =NOW() | =NOW() | 現在の日時 |
| DATE:日付を作成 | =DATE(年,月,日) | =DATE(2026,11,17) | 2026/11/17 |
| YEAR:年を取り出す | =YEAR(日付) | =YEAR(F2) | 2026 |
| MONTH:月を取り出す | =MONTH(日付) | =MONTH(F2) | 11 |
| EOMONTH:月末日 | =EOMONTH(開始日,前後の月数) | =EOMONTH(F2,0) | 2026/11/30 |
| NETWORKDAYS:営業日数 | =NETWORKDAYS(開始日,終了日,[休日範囲]) | =NETWORKDAYS(DATE(2026,11,9),DATE(2026,11,13)) | 5日 |
| WORKDAY:指定営業日後の日付 | =WORKDAY(開始日,営業日数,[休日範囲]) | =WORKDAY(DATE(2026,11,9),5) | 2026/11/16 |
※日付の結果が数値で表示された場合は、セルの表示形式を「日付」に変更してください。
TODAYとNOWは引数を指定しないため、括弧の中は空欄です。Excelの再計算時に更新されるため、請求書の発行日など変更したくない日付は、直接入力する方法が確実です。
EOMONTHの第2引数には、開始日から何か月前後の月末日を求めるかを指定します。0なら当月末、1なら翌月末、-1なら前月末です。
NETWORKDAYSは開始日と終了日を含む期間の営業日数を求めます。WORKDAYは開始日から指定した営業日数だけ進めた日付を返します。
どちらも標準では土日を除外しますが、日本の祝日は自動で除外されません。祝日や会社の休業日を考慮したい場合は、休日の日付を別の範囲に入力し、第3引数で指定します。
例えば、L2:L10に休日を入力した場合は、次のように書きます。
=NETWORKDAYS(F2,F3,L2:L10)
詳しい指定方法はMicrosoft公式のNETWORKDAYS関数の解説で確認できます。
よく使う関数の引数を詳しく解説
一覧表の基本構文を理解したら、使用頻度の高い関数を自分の表に合わせて書き換えてみましょう。
IF関数は条件・成立時・不成立時を指定する
IF関数の基本形は次のとおりです。
=IF(条件,成立した場合,成立しない場合)
例えば、数量が3個以上なら「対象」、それ以外なら「対象外」と表示する場合です。
=IF(D2>=3,"対象","対象外")
| 引数 | 指定内容 | 意味 |
|---|---|---|
| 第1引数 | D2>=3 | D2の数量が3以上か判定 |
| 第2引数 | "対象" | 条件を満たした場合の表示 |
| 第3引数 | "対象外" | 条件を満たさなかった場合の表示 |
サンプルのD2は3なので、結果は「対象」です。
判定する数量を5以上に変更したいなら、第1引数をD2>=5に変更します。「対象」という表示を「合格」に変えたい場合は、第2引数を書き換えます。
COUNTIFは範囲と条件を指定する
COUNTIFは、指定した範囲から条件に一致するセルの数を求めます。
例えば、=COUNTIF(C2:C6,"田中")なら、担当者が入力されたC2:C6から「田中」の件数を数えます。
数量が3以上の件数を数えるなら、範囲と条件を変更します。
=COUNTIF(D2:D6,">=3")
比較する数値をセルから取得する場合は、次のように指定できます。
=COUNTIF(D2:D6,">="&I1)
I1に3を入力すると、3以上の件数を数えます。
COUNTIFの条件は1つの引数なので、比較演算子>=を文字列として指定し、&でI1の値とつなげています。
この書き方を覚えると、「70点以上」「指定金額未満」など、基準値を変更できる集計表を作れます。
SUMIFSは合計範囲と条件範囲を対応させる
SUMIFSは、複数の条件をすべて満たすデータだけを合計します。
田中さんが担当したノートの売上を合計する場合は、次の数式です。
=SUMIFS(E2:E6,C2:C6,"田中",B2:B6,"ノート")
| 引数 | 指定内容 | 意味 |
|---|---|---|
| 第1引数 | E2:E6 | 合計する売上金額 |
| 第2引数 | C2:C6 | 担当者を調べる範囲 |
| 第3引数 | "田中" | 担当者の条件 |
| 第4引数 | B2:B6 | 商品名を調べる範囲 |
| 第5引数 | "ノート" | 商品名の条件 |
「担当者が田中」「商品名がノート」の両方を満たす行だけを合計するため、結果は900円です。
佐藤さんが担当したノートの売上を調べたいなら、第3引数の「田中」を「佐藤」に変更します。
SUMIFとSUMIFSは、引数の順序も異なります。
| 関数 | 引数の順序 |
|---|---|
| SUMIF | 条件範囲 → 条件 → 合計範囲 |
| SUMIFS | 合計範囲 → 条件範囲1 → 条件1 → … |
また、SUMIFSの合計範囲と条件範囲は、行数・列数を揃えて指定します。例えば、合計範囲がE2:E6なら、条件範囲もC2:C6やB2:B6のように対応する5行を指定します。
範囲の大きさが違うと#VALUE!エラーになることがあります。大きさが同じでも、C2:C6とD3:D7のように開始行がずれていると、異なる行のデータを組み合わせて判定してしまいます。
XLOOKUPは検索する場所と取得する場所を分ける
XLOOKUPは、指定した値を探し、対応するデータを取得する関数です。
商品コード「A03」から商品名を取得する場合は、次のように書きます。
=XLOOKUP("A03",A2:A6,B2:B6,"該当なし")
| 引数 | 指定内容 | 意味 |
|---|---|---|
| 第1引数 | "A03" | 探したい商品コード |
| 第2引数 | A2:A6 | 商品コードを探す範囲 |
| 第3引数 | B2:B6 | 一致した行から取得する商品名の範囲 |
| 第4引数 | "該当なし" | 見つからなかった場合の表示 |
結果は「ファイル」です。
商品名の代わりに売上金額を取得したいなら、第3引数をE2:E6に変更します。
XLOOKUPでは、検索値が複数の行に存在する場合、初期設定では最初に一致したデータを返します。商品名や氏名は重複することがあるため、商品コードや社員番号など、一意の識別番号を使うと確実です。
一致モードや検索モードの詳細は、Microsoft公式のXLOOKUP関数の解説で確認できます。
XLOOKUPとVLOOKUPはどちらを使う?
Microsoft 365やExcel 2024を使用しているなら、基本的にはXLOOKUPがおすすめです。
| 比較項目 | XLOOKUP | VLOOKUP |
|---|---|---|
| 検索する列 | 任意の列 | 表範囲の左端列 |
| 取得する方向 | 左右どちらも可能 | 検索列より右側のみ |
| 取得列の指定 | セル範囲 | 列番号 |
| 完全一致 | 初期設定 | FALSEを指定 |
| 列を挿入した場合 | 参照範囲が追従しやすい | 列番号のずれに注意 |
| 古いExcelでの利用 | Excel 2019以前は非対応 | 幅広い旧バージョンに対応 |
VLOOKUPは、検索範囲の左端列から値を探し、指定した列番号のデータを返します。
例えば、次の式はA2:E6の左端にあるA列から「A03」を検索し、その範囲の2列目にある商品名を取得します。
=VLOOKUP("A03",A2:E6,2,FALSE)
最後のFALSEは完全一致で検索する指定です。省略すると近似一致になり、検索列が昇順に並んでいることを前提とした検索になります。商品コードや社員番号の照合ではFALSEを指定すると確実です。
一方、近似一致は「70点以上ならB評価、80点以上ならA評価」のように、数値の区分表から該当する値を探す場合に使えます。
VLOOKUPは取得列を数字で指定するため、表の途中に列を挿入すると意図しない列の値を返すことがあります。XLOOKUPは検索範囲と取得範囲を別々に指定でき、こうした問題を避けやすくなります。
XLOOKUPが使えない環境で検索列より左側の値を取得したい場合は、INDEXとMATCHを組み合わせる方法もあります。
=INDEX(A2:A6,MATCH("ノート",B2:B6,0))
この式は、B列で「ノート」が最初に見つかった行に対応するA列の値「A01」を返します。
Excel関数を組み合わせた実務例
関数を組み合わせると、月次の売上集計なども効率化できます。
SUMIFSとEOMONTHで月別売上を集計する
サンプルの売上表から、2026年11月の売上だけを合計してみます。
まず、G列に集計対象の月を入力します。
| G列:対象月 | H列:売上合計 |
|---|---|
| 2026/11/1 | |
| 2026/10/1 |
G1に「対象月」、H1に「売上合計」を入力し、G2とG3にそれぞれの日付を入力してください。
次に、H2セルに以下の数式を入力します。
=SUMIFS($E$2:$E$6,$F$2:$F$6,">="&(EOMONTH(G2,-1)+1),$F$2:$F$6,"<"&(EOMONTH(G2,0)+1))
計算結果:4,050円
サンプル表の全売上は4,200円ですが、10月29日の150円を除いた11月分は4,050円です。
この式は、G2の日付を含む月の初日から、翌月1日の直前までに売上日が含まれるデータを合計します。
| 指定内容 | 意味 |
|---|---|
$E$2:$E$6 | 合計する売上金額 |
$F$2:$F$6 | 売上日が入力された範囲 |
EOMONTH(G2,-1)+1 | G2が属する月の初日 |
EOMONTH(G2,0)+1 | G2が属する月の翌月1日 |
例えば、G2に2026/11/15を入力した場合も、11月1日から30日までの売上を集計できます。
前月末の翌日が当月1日になる仕組みを利用しているため、対象月の日付が1日以外でも正しく計算できます。また、翌月1日より前という条件を使っているため、売上日に時刻が含まれていても月末日の取引まで集計できます。
絶対参照を使ってほかの月にもコピーする
H2の数式をH3にコピーすると、G2の参照は自動的にG3に変わります。一方、$E$2:$E$6や$F$2:$F$6は絶対参照になっているため、コピーしても範囲が変わりません。
その結果、次のように集計できます。
| 対象月 | 売上合計 |
|---|---|
| 2026年11月 | 4,050円 |
| 2026年10月 | 150円 |
G列の日付は、セルの表示形式をユーザー定義のyyyy"年"m"月"にすると、「2026年11月」のように表示できます。元の日付データは維持されるため、集計にもそのまま使えます。
実際の業務では、データが追加されても集計範囲が自動で広がるように、元の売上表をExcelのテーブルに変換しておく方法も便利です。
ただし、FILTER・UNIQUE・SORTなど、複数セルに結果を展開する数式はExcelテーブルの内部では使用できません。元データをテーブル化する場合も、これらの数式はテーブルの外側に入力します。
Excel関数で計算結果が合わない・エラーが出るとき
数式が正しくても、参照先のデータによって計算結果が合わないことがあります。特に、数値や日付が文字列として保存されている場合や、条件範囲がずれている場合は確認が必要です。
| 症状 | 考えられる原因 | 確認・対処方法 |
|---|---|---|
| SUMの合計が少ない | 数値が文字列として保存されている | COUNTとCOUNTAで数値セルの件数を比較する |
| SUMIFSで一部が集計されない | 条件に余分な空白がある | TRIMやSUBSTITUTEで整える |
| COUNTIFSで値が合わない | 条件範囲の開始行がずれている | 同じ行同士を比較しているか確認する |
COUNTIFSで#VALUE!が出る | 条件範囲の行数・列数が異なる | 各条件範囲の大きさを揃える |
| 日付の集計が合わない | 日付が文字列として保存されている | Excelの日付データとして認識されているか確認する |
| 検索結果が見つからない | 検索値の空白やデータ型が異なる | 検索値と検索範囲を照合する |
SUMの合計が少ないときはCOUNTとCOUNTAで調べる
例えば、売上欄のE2:E6には5つの数値が入力されているはずなのに、SUMで合計すると一部が抜けている場合です。
次の2つの式を入力してみます。
=COUNT(E2:E6)
=COUNTA(E2:E6)
サンプルデータでは、どちらも5になります。
しかし、COUNTAが5でCOUNTが4なら、5セルにデータがあっても、そのうち数値として認識されているセルが4つしかないことが分かります。
WebページやCSVファイルから取り込んだデータでは、数値が文字列として保存されている場合があります。セルに緑色のエラーマークが表示されていれば、メニューから「数値に変換する」を選べることがあります。
また、=VALUE(E2)を使えば、数値として解釈できる文字列を数値に変換できます。
金額は数値として保存し、円記号や桁区切りはセルの表示形式で設定すると、後の計算に使いやすくなります。
COUNTIFSは範囲の大きさと開始行を確認する
COUNTIFSでは、複数の条件範囲を指定します。
例えば、次の数式は担当者が佐藤さんで、数量が4以上の行を数えます。
=COUNTIFS(C2:C6,"佐藤",D2:D6,">=4")
結果は2件です。
一方、次のように2つ目の条件範囲だけ開始行がずれていると、異なる行の担当者と数量を組み合わせて判定してしまいます。
=COUNTIFS(C2:C6,"佐藤",D3:D7,">=4")
この式の結果は0件です。条件範囲の大きさはどちらも5行なのでエラーになりませんが、比較するデータの行がずれています。
また、条件範囲の大きさ自体が異なると#VALUE!エラーになります。
複数条件の関数を使用する際は、各範囲の開始行と終了行、行数・列数を確認すると誤集計を防げます。
よくあるエラー表示と対処法
関数が動かないときは、表示されているエラーから原因を絞り込めます。
| エラー表示 | 主な原因 | 確認すること |
|---|---|---|
#N/A | 検索値が見つからない | 検索値、余分な空白、データ型 |
#VALUE! | 不適切な値やデータ型、範囲の指定 | 引数、参照先の値、範囲の大きさ |
#DIV/0! | 0や空白セルで割っている | 割る数が0になっていないか |
#REF! | 参照先のセルが無効になっている | 行・列の削除や参照範囲 |
#NAME? | 関数名などが認識されない | 関数名、スペル、対応バージョン |
#SPILL! | 複数の結果を展開できない | 出力先の既存データ、結合セル、テーブル内の数式 |
FILTER、UNIQUE、SORTなどは、計算結果が周囲の複数セルに自動で広がります。これを「スピル」と呼びます。
例えば、FILTERで3行×6列のデータを抽出する場合、数式を入力したセルから3行×6列分の空き領域が必要です。その範囲に既存データや結合セルがあると、#SPILL!になることがあります。
エラーが出たセルを選択し、展開先の範囲を確認してください。対処方法はMicrosoft公式のスピルエラーの修正ガイドでも確認できます。
IFERRORでエラーを処理するときの注意点
IFERRORを使うと、計算結果がエラーになった場合に「該当なし」などの文字列を表示できます。
ただし、検索値が見つからない場合の#N/Aだけでなく、参照範囲の指定ミスなどによるエラーも処理対象になります。
検索で#N/Aが発生した場合だけを処理したいなら、IFNA関数を使う方法があります。
=IFNA(VLOOKUP("A99",A2:E6,2,FALSE),"該当なし")
この式では、商品コード「A99」が見つからないため「該当なし」を返します。
XLOOKUPを使う場合は、第4引数で見つからなかったときの表示を指定できます。
数式を作成する際は、まず検索や計算が正常に動作することを確認し、その後で必要なエラー処理を加えると、原因を見落としにくくなります。
