SUMIFS関数がなぜうまくいかない?#VALUE!エラーの原因
ここでは、ExcelのSUMIFS関数がうまくいかず、#VALUE!エラーになってしまう原因2つと対処法を解説します。
特に気づきにくい原因もありますので、SUMIFS関数を使いこなせるようになるために押さえておいた方がいいと思います。
1.閉じた他のファイルを参照している
SUMIFS関数が#VALUE!エラーになる原因の一つ目は「引数で閉じた他のエクセルファイルを参照している」ことです。
1-1.まずは例を確認
まずは、例を見ながら原因を確認してみましょう。
上の表は、Book1という名前のファイルにある「廃棄一覧表」です。
商品名や廃棄の有無、仕入れ額が入力されています。
この表をもとに別のファイル「Book2」で、「商品A廃棄の仕入額合計」をSUMIFS関数で合計すると…。
これは、SUMIFS関数の引数でBook2を参照しているからです。
1-2.原因を解説
なぜ、引数で他のファイルを参照するとエラーになるのでしょうか?
なぜなら、SUMIFS関数は「閉じているファイルの値をとることができない」からです。
もちろん、エクセル関数のすべてがそうではありません。閉じていてもしっかりと値を返してくれる関数はたくさんあります。
しかし、SUMIFS関数はそうではないのです。
つまり、実質SUMIFS関数では他のファイルを参照してはいけないのです。
1-3.数式作成時になぜ気づかないのか?
しかし、SUMIFS関数を使って数式を作っている時にはエラーになりませんよね。
実は、数式を組んだ直後には他ブックの参照元を閉じても即座にエラーにはならないのです。
なぜなら、他のファイルの値がメモリに残っているからです。
そして、閉じた後に再計算をかけたり一度数式が入ったブックを閉じて再度開くと、値がクリアされるため、エラーになるのです。
1-4.【対処法1】SUM関数とIF関数を組み合わせて使う
対処法の一つ目は「他のファイル参照が可能な関数を使う」ことです。
「SUM関数とIF関数との組み合わせ+配列数式」 で、SUMIFS関数と同じことができます。
数式の形の比較をしてみます。
条件が2つの場合、SUMIFS関数は、次の通りです。
対して、SUM関数+IF関数+配列数式の場合は次のようになります。
ポイントは、以下の2つです。
- ① 条件をカッコで囲み、「*」でつなげる
- ② 配列数式にする
特に②は、数式入力後に「CtrlキーとShiftキーを押しながらEnterキーを押す」ことで、配列数式になります。
例での数式で見比べてみると、SUMIFS関数の場合は次のようになります。
対して、SUM関数+IF関数+配列数式の場合は以下の通りです。
1-5.【対処方法2】作業用シートに参照元をコピーする
対処法の二つ目は「Book2の作業用シートにBook1の表をコピー」することです。
どういうことかというと、次の通りです。
Book2に新しいシートを作成し、表などの体裁を整えた後、値は「=」を使ってBook1の値を参照させるのです。
つまり、「Book2のシート2とBook1のシート1をリンクさせる」のです。
例えば、Book2のセルA2に入っている数式は、Book1のセルA2を見ています。
後は、このシートに対してSUMIFS関数で数式を作ればいいのです。
これだと、Book1が閉じていてもSUMIFS関数が#VALUE!エラーになることはありません。
2.検索条件毎の行範囲が違う
SUMIFS関数が#VALUE!エラーになる原因の二つ目は「引数の行範囲が違っている」ことです。
2-1.まずは例を確認
まずは、例を見ながら原因を確認してみましょう。
上の表は、先ほどと同じものです。
ただ、今度は同じシートの中に結果を表示させたい表があります。
そして、「商品A廃棄の仕入額合計」をSUMIFS関数で合計しようとしたのですが…。
#VALUE!エラーになってしまいました。
これは、SUMIFS関数の「引数の行範囲が違う」ことが原因です。
2-2.原因を解説
「引数の行範囲が違う」とは、どういうことでしょうか?
それは、数式をクリックしてみると一目瞭然です。
例だと、赤枠が「検索範囲1」、紫枠が「検索範囲2」です。
紫枠のほうが、一行少ないのが分かります。
ダイアログを見ても、やはり行範囲が違うことが分かります。
また、合計対象範囲も同じです。
このように、引数同士の行範囲が違うと、SUMIFS関数はエラーになってしまうのです。
2-2.【対処法】行範囲を合わせる
この原因の場合、対処法は簡単です。
「引数の行範囲を合わせればいい」のです。
例でいうと、合計条件範囲と条件範囲のすべてを「2行目から7行目」にします。
これで、#VALUE!エラーは解消されます。
以上、参考になれば幸いです。
【口コミ・評判のいいExcel本の紹介】
SUMIFS関数に関連したページは、次の通りです。。
- SUMIFS関数 各ページへのリンクはこちら
- ◎基本的な使い方
- ◎合計対象範囲を複数列にする方法
- ◎別シートや別ブックに表示させる方法
- ◎月ごとに集計をする方法
- ◎#VALUE!エラーになる2つの原因と対処法