Excelで関数を入れたら、セルに #NUM! とだけ表示された。そんな場面で手が止まっていませんか。
IRRで投資の利回りを出そうとしたとき、DATEDIFで勤続年数を数えたとき。数式を見直しても、どこが悪いのか見当がつかないと困りますよね。
#NUM! エラーは「その数値では計算できない」というサインです。原因は3つの系統に分かれるので、使っている関数から逆引きすれば直し方はすぐ決まります。
関数別の早見表と、#NUM! を再現する最小の数式・直した数式の Before/After を並べました。自分の状況に近い行から読んでください。
Excelの#NUM!エラーとは?関数・症状別の早見表
#NUM! は、数式や関数が数値の計算を最後まで終えられないときにExcelが返すエラーです。文字列が混ざって型が合わない #VALUE! とは違い、渡しているのは数値なのに計算が成り立たない状態を指します。
Microsoftの公式ヘルプが原因に挙げるのは、結果が大きすぎる・小さすぎる場合。もう1つは、IRRやRATEのような反復する関数が答えを見つけられない場合です。
各関数のヘルプには、受け付けない引数を渡したときの #NUM! も個別に書かれています。
#NUM!が出る3つの系統
#NUM! の原因は、次の3つのどれかに当てはまります。
- 系統① 計算結果がExcelの扱える数値の範囲を超えた(べき乗などで巨大な数ができた)
- 系統② 引数が関数の定義外(負の数の平方根、開始日が終了日より後など)
- 系統③ 反復計算が収束しない(IRR・RATE・XIRRが答えにたどり着けない)
系統が分かれば、確認する場所も絞り込めます。①は途中の計算の大きさ、②は引数の値、③はキャッシュフローの符号と推定値。
症状別早見表(関数・状況 → 原因系統 → 直し方)
| 使っている関数・状況 | 原因の系統 | 直し方 |
|---|---|---|
| べき乗や掛け算で巨大な数を作っている | ① 範囲超え | 割ってからべき乗するなど計算の順番を変える |
| SQRTに負の数を渡している | ② 定義外 | IFで負の数を除く・ABSで符号を外す |
| LOG・LNに0や負の数を渡している | ② 定義外 | IFで0以下の値を除く |
| GAMMAに0や負の整数を渡している | ② 定義外 | 引数の値を確認する |
| DATEの年が0未満、または10000以上 | ② 定義外 | 年の入力ミスを直す |
| DATEDIFの開始日が終了日より後・終了日が空欄 | ② 定義外 | 日付の順番を直す・空欄をIFで除く |
| DEC2BIN・BIN2DECなど進数変換 | ② 定義外 | 扱える範囲・桁数に収める |
| LARGEの順位がデータ件数より大きい | ② 定義外 | 順位を件数以下にする |
| IRR・XIRRの値がすべてプラス(またはマイナス) | ③ 収束しない | 投資額をマイナスで入れる |
IRRの符号は正しいのに #NUM! が出る | ③ 収束しない | 推定値(第2引数)を指定する |
| RATEの借入額と返済額が同じ符号 | ③ 収束しない | 返済額をマイナスにする |
| XIRRに最初の日付より前の日付がある | ③ 反復系の関数(日付の条件) | 日付の入力ミスを直す |
早見表の「直し方」だけで解決しない場合は、系統ごとの節で再現式とBefore/Afterを確認してください。
系統① 計算結果がExcelの数値範囲を超えている
Excelが数式で扱える値には上限と下限があり、公式の「Excelの仕様と制限」に載っています。数式で処理できる値は、およそ ±1.7976931348623158E+308 まで。
セルに直接入力できる最大値は 9.99999999999999E+307 で、こちらとは数字が異なります。公式の #NUM! のヘルプは、結果を -1×10^307 〜 1×10^307 の範囲に収めるよう案内しています。
大きすぎる値は計算の順番を変えて範囲に収める
見落としやすいのは、最終的な答えは小さいのに、途中の値が上限を超えるケースです。次の数式は、答えが1.13程度にしかならない計算。
=1000^120/999^120
ところが分子の1000の120乗は10の360乗で、Excelの上限を超えます。分母も同じく上限超え。割り算に進む前に計算が成り立たず、結果は #NUM! です。
割ってからべき乗するように順番を変えると、途中の値が範囲に収まります。
=(1000/999)^120
結果は約1.1276。単位を「円」から「億円」に変える、比を先に計算するなど、途中の桁を小さく保つ工夫が対処の基本です。
小さすぎる値は途中で0になっていないか確かめる
公式ヘルプは、結果が小さすぎる場合も #NUM! の原因として挙げています。一方で、正の最小値(2.2251E-308)を下回る値は0として扱われる、という第三者の報告もあります。公式には明記されていない点に注意。
極端に小さい値を扱う計算で #NUM! や想定外の0が出たら、単位を変えて桁を戻すのが無難です。結果が0になった場合はエラーが出ないぶん気づきにくいので、途中の値を別のセルに出して確かめましょう。
系統② 引数が関数の定義外になっている
関数には、受け付ける引数の範囲が決まっています。範囲の外の値を渡すと、計算そのものが定義されていないため #NUM! が返ります。
SQRT・LOG・LN・GAMMAに0や負の数を渡している
平方根や対数は、負の数に答えがない計算(対数は0も同じ)。次の数式は、一般にいずれも #NUM! になります。
=SQRT(-16)
=LN(0)
=LOG(-10)
A2の値が負になりうる場合は、IFで先に判定してから関数に渡します。
=IF(A2<0,"負の値",SQRT(A2))
=IF(A2>0,LN(A2),"")
符号に意味がなく大きさだけ欲しいなら、=SQRT(ABS(A2)) で符号を外す方法もあります。A2が-16なら結果は4。ただし元の値の意味が変わるので、使う場面は選びましょう。
GAMMAは、0や負の整数で #NUM! を返します。公式ヘルプの例では =GAMMA(-2) が #NUM!。負でも整数でない =GAMMA(-3.75) は、0.268と計算できます。
各関数の使い方はSQRT関数・LOG関数・LN関数・GAMMA関数の記事で解説しています。
DATEの年が範囲外・DATEDIFの開始日が終了日より後
DATE関数が #NUM! を返すのは、年が0未満か10000以上のときだけ。年を「20255」と打ち間違えると、次のように #NUM! になります。
Before: =DATE(20255,4,1)
After : =DATE(2025,4,1)
月や日が範囲外でも #NUM! にはなりません。=DATE(2008,1,35) は2008/2/4、=DATE(2008,-3,2) は2007/9/2と、前後の月に繰り越されます。
注意したいのは、年に0〜1899を入れた場合。1900が足されるため、和暦の「7」を入れると1907年になり、エラーが出ないまま日付がずれます。
DATEDIFは、開始日が終了日より後だと #NUM! を返します。B2に入社日の2020/4/1、C2に基準日の2025/3/31が入っている表で考えましょう。
Before: =DATEDIF(C2,B2,"Y")
After : =DATEDIF(B2,C2,"Y")
引数の順番を入れ替えると、結果は4(満4年)です。
順番が正しくても、終了日のセルが空欄だと #NUM! になります。空欄は日付のシリアル値0と同じ扱いになり、開始日より前の日付とみなされるためです。基準日が未入力の行は、IFで計算を飛ばしておきます。
=IF(C3="","",DATEDIF(B3,C3,"Y"))
関数の基本はDATE関数とDATEDIF関数の記事をどうぞ。
DEC2BIN・BIN2DECの桁あふれ
10進数を2進数に変えるDEC2BINが扱えるのは、-512〜511の整数です。範囲外の数や、変換結果の桁数が第2引数(桁数)より多い場合は #NUM! になります。
Before: =DEC2BIN(600)
Before: =DEC2BIN(5,2)
After : =DEC2BIN(5,3)
5は2進数で「101」の3桁なので、桁数に2を指定すると足りません。3にすれば「101」が返ります。桁数に0や負の数を指定した場合も、同じく #NUM!。
逆向きのBIN2DECは、10文字(10ビット)を超える値や、0と1以外を含む値で #NUM! を返します。=BIN2DEC("102") は、2進数として成り立たないためエラー。
入力値が範囲に収まるか分からない表では、先に範囲を判定しておきましょう。
=IF(AND(A2>=-512,A2<=511),DEC2BIN(A2),"範囲外")
詳しくはDEC2BIN関数・BIN2DEC関数の記事にまとめています。
LARGE・SMALLの順位の指定を確かめる
LARGEは、順位(第2引数)が0以下か、データの件数より大きいと #NUM! を返します。B2:B6に売上が5件しかないのに6位を求めると、次のようにエラーです。
Before: =LARGE(B2:B6,6)
After : =IF(COUNT(B2:B6)>=6,LARGE(B2:B6,6),"")
月の途中で件数がまだ少ない表を「上位5位」の形で作っておくと、データが揃うまで下位の行に #NUM! が並びます。COUNTで件数を数えて、足りない間は空白にしておきましょう。
SMALLも引数の構造は同じなので、順位の指定を同じように確認してください。LARGEの使い方はLARGE関数の記事で解説しています。
系統③ IRR・RATE・XIRRの反復計算が収束しない
IRR・RATE・XIRRは、答えを一発で計算できない関数です。推定値から出発して計算を繰り返し、答えに近づけていきます。決められた回数で答えにたどり着けないと、#NUM! が返る仕組み。
| 関数 | 試す回数の上限 | 求める精度 | 推定値の既定 |
|---|---|---|---|
| IRR | 20回 | 0.00001% | 10% |
| RATE | 20回 | 0.0000001 | 10% |
| XIRR | 100回 | 0.000001% | 10% |
回数・精度・既定値は、各関数のMicrosoft公式ヘルプに書かれている値です。
IRR:符号を確認してから推定値を変える
IRRで #NUM! が出たら、まずキャッシュフローの符号を確認します。公式ヘルプによると、IRRの値には正と負の値が1つ以上ずつ必要です。
100万円を投資して、4年間で毎年30万円を回収する例で考えてみましょう。B2に投資額をプラスのまま入れると、値がすべてプラスになり #NUM! が返ります。
| 行 | A(年) | B(Before) | B(After) |
|---|---|---|---|
| 2 | 0年目 | 1,000,000 | -1,000,000 |
| 3 | 1年目 | 300,000 | 300,000 |
| 4 | 2年目 | 300,000 | 300,000 |
| 5 | 3年目 | 300,000 | 300,000 |
| 6 | 4年目 | 300,000 | 300,000 |
=IRR(B2:B6)
出ていくお金(投資額)をマイナスにしたAfterの値なら、結果は約7.71%(小数第2位まで表示)。1段階目の修正はここまで。
符号が正しくても #NUM! が出る場合は、2段階目として推定値(第2引数)を指定します。利回りが既定の10%から大きく離れていると、20回の計算で届かないことがあるためです。推定値なしで #NUM! になるかどうかは、数値の組み合わせ次第です。
推定値の入れ方の例として、B2:B4が -100・300・300 のキャッシュフローを見てみましょう。利回りは約279.13%と極端に高くなります。このデータは推定値なしでも計算できる可能性があり、#NUM! の再現例ではありません。答えが10%から大きく離れるときは、答えに近い値を推定値に入れて、計算の出発点を近づけます。
=IRR(B2:B4,2)
推定値の2は200%の意味です。結果は約279.13%。
正負が2回以上入れ替わるキャッシュフローは答えが複数ありうるので注意しましょう。-100・230・-132 なら10%と20%のどちらも成り立ち、推定値で返る値が変わることがあります。
IRRの構文はIRR関数の記事で確認できます。
RATE:借入額と返済額の符号が同じになっている
RATEでつまずきやすいのが、借入額と返済額を両方プラスで入れる書き方。300万円を借りて、毎月9万円を36回で返す例で比べます。
Before: =RATE(36,90000,3000000)
After : =RATE(36,-90000,3000000)
Beforeは受け取る額と払う額が同じ符号のため、つり合う利率が存在せず #NUM! が返ります。返済額をマイナスにするのが一般的な書き方です。
Afterの結果は月利で約0.42%。年利で見たいときは、=RATE(36,-90000,3000000)*12 で約5.06%になります。
符号を直しても #NUM! が出るなら、6番目の引数に推定値を入れてみてください。=RATE(36,-90000,3000000,0,0,0.01) は月利1%から計算を始める指定です。詳しくはRATE関数の記事をどうぞ。
XIRR:最初の日付より前の日付が混ざっている
XIRRは日付つきのキャッシュフローから利回りを求めます。IRRと同じく正と負の値が1つ以上ずつ必要で、ほかにも #NUM! の条件が2つあります。
- 2つ目以降の日付に、最初の日付より前のものがある
- 値と日付の個数が違う
日付の並び自体は順不同でかまいません。問題になるのは、最初の日付より前の日付だけ。次の表では、C3の年を2026と打つところを2024と間違えています。
| 行 | B(値) | C(Before) | C(After) |
|---|---|---|---|
| 2 | -1,000,000 | 2025/4/1 | 2025/4/1 |
| 3 | 600,000 | 2024/4/1 | 2026/4/1 |
| 4 | 600,000 | 2027/4/1 | 2027/4/1 |
=XIRR(B2:B4,C2:C4)
Beforeは #NUM!。C3を直したAfterなら、結果は約13.07%。日付の入れ方はXIRR関数の記事で解説しています。
「反復計算を行う」の設定との関係
公式の #NUM! のヘルプは、IRRやRATEが答えを見つけられないときの対処に反復計算の設定を挙げています。[ファイル]→[オプション]→[数式]で「反復計算を行う」をオンにする方法です。あわせて「最大反復回数」と「変化の最大値」も指定します。
ただし、この設定がIRR・RATE・XIRRの内部の計算回数に効くかどうか、公式の根拠は確認できませんでした。各関数のヘルプでは、回数は上の表のとおり固定。
そのため、まず試したいのは符号の確認と推定値の指定です。反復計算の設定は本来、循環参照を意図して計算させるときに使うもの。仕組みは循環参照エラーの原因と直し方で解説しています。
#NUM!の原因を特定する手順とIFERRORで隠す前の注意
どの系統か見当がつかないときは、確認する順番を決めておくと迷いません。
#NUM!診断フロー(関数名 → 引数の符号・範囲 → 反復)
#NUM!が出たセルの関数名を確認し、冒頭の症状別早見表で系統を決める- 参照しているセルの値を見る。負の数・0・空欄・日付の前後関係を確かめる
- べき乗や掛け算があるなら、途中の値を別のセルに出して大きさを確かめる
- IRR・RATE・XIRRなら、符号を確認してから推定値を指定する
- 参照先のセル自体が
#NUM!になっていないか見る
つい見落としがちなのが5つ目。参照先のエラーはそのまま引き継がれるので、元のセルを直さない限り消えません。合計の列に出た #NUM! も、たどると1つのセルが原因ということがあります。
ERROR.TYPEで#NUM!を判別する
表のあちこちにエラーがあるときは、ERROR.TYPEで種類を見分けられます。#NUM! に対してERROR.TYPEが返す値は6。
=IF(ISERROR(D2),IF(ERROR.TYPE(D2)=6,"#NUM!:引数と符号を確認","別のエラー"),"")
ERROR.TYPEはエラーでないセルに使うと #N/A を返すため、ISERRORで先に判定しています。ほかのエラーの番号はERROR.TYPE関数の記事で一覧にしました。
IFERRORで隠すのは原因を直した後
#NUM! が見苦しいからと、いきなりIFERRORで消すのはおすすめしません。IRRの符号ミスのように、直すべき間違いまで見えなくなるからです。
=IFERROR(IRR(B2:B6),"要確認")
IFERRORは #NUM! 以外のエラーもまとめて置き換えます。使うのは原因を直したうえで、未入力の行など #NUM! が出ても困らない場面に限りましょう。表示を空白にせず「要確認」のような文字にしておくと、エラーの存在には気づけます。
IFERRORの書き方はIFERROR関数の記事を参考にしてください。
よくある質問(FAQ)とまとめ
Q. #NUM!と#VALUE!の違いは?
#VALUE! は、「未定」のような文字列を計算に使うなど、データの型が合っていないときのエラーです。#NUM! は、数値として受け取れたうえで計算が成り立たないときに出ます。
DATEDIFなら、日付として読めない文字列だと #VALUE!、開始日が終了日より後だと #NUM!。#VALUE! 側の直し方はExcel #VALUE!エラーの直し方にまとめています。
Q. Excelのバージョンによって#NUM!の出方は変わる?
この記事で扱った関数の公式ヘルプを見た範囲では、バージョンごとの条件の違いは記載されていませんでした。ただし差がないと断定できる根拠もありません。手元の結果が記事と違う場合は、その関数の公式ヘルプも確認してください。
Q. #NUM!のセルを含む合計までエラーになるのは?
SUMなどで範囲に #NUM! のセルが含まれると、合計の結果もエラーになります。診断フローの5つ目のとおり、元の #NUM! を直すのが先決です。
まとめ
#NUM!は「その数値では計算できない」というサイン。原因は範囲超え・引数が定義外・反復が収束しないの3系統- 範囲超えは、割ってからべき乗するなど途中の値を小さく保つ
- 引数の定義外は、負の数・0・空欄・日付の前後・順位や桁数の範囲を確認する
- IRR・RATE・XIRRは、符号の確認 → 推定値の指定の順に試す
- IFERRORで隠すのは原因を直した後。種類の判別にはERROR.TYPE(
#NUM!は6)
IRRやRATEの符号の考え方から押さえ直したい方は、IRR関数とRATE関数の記事もあわせて読んでみてください。
ほかのエラーが出たときは、#DIV/0!エラー・#N/Aエラー・#REF!エラー・#NAME?エラー・#SPILL!エラーの記事も参考にしてください。
