いまさら聞けないExcelの使い方講座

【Excel】計算式は正しいはずなのに「#VALUE!」エラーが！エクセルでセルに文字列を含めたまま計算するワザ

トップスタジオ

2019年4月19日 06:55

セルに文字列が入力されていると「#VALUE!」エラーになる！

　Excelで数式を作成していると、さまざまなエラーに遭遇することがありますよね。「計算式は正しいはずなのに、突然エラーが出て戸惑った……」という経験のある読者は意外と多いのではないでしょうか。

　次の「給与支給表」（①）の例を見てください。この表では、「給与」欄の金額と「諸手当」欄の金額を足して「総支給額」を計算しています（例えば、セルD3には「給与＋諸手当」という意味の「=B3+D3」という数式が入力されています）。この数式を下のセルにコピーしたところ、セルD6に「#VALUE!」というエラーが表示されてしまいました（②）。

　「#VALUE!」エラーは、数値を指定すべきセルに文字列が入力されている時に発生するエラーです。今回の場合、セルD6には数式をコピーしたときに「=B6+C6」という数式が入力されたのですが、セルC6に「なし」という文字列が入力されているために正しく計算が行われず、「#VALUE!」エラーが発生しています。

　セルC6の値を「0」に修正すればこのエラーは発生しなくなるのですが、実際の業務では、「0」という数値ではなく、「なし」とか「検討中」のような文字列を入れておきたい時ってありませんか。

　そこで今回は、セルに入力された文字列を「0」とみなしたり、文字列が計算の対象外になる関数を使ったりして「#VALUE!」エラーを回避する方法を解説します。

テクニック1：文字列を「0」とみなして計算する

　1つ目は、セルに入力された文字列を「0」とみなして計算するという方法です。冒頭でも触れたように、セルD6に「#VALUE!」エラー（①）が表示されているのは、このセルに入力されている数式「=B6+C6」で参照しているセルC6に文字列が入力されているからでしたね。そこで、セルC6の「なし」という文字列を「0」とみなして、正しく計算できるようにしましょう。まず［ファイル］タブ（②）をクリックします。

　［情報］画面が表示されるので、左側のメニューの一番下にある［オプション］（③）をクリックします。

　［Excelのオプション］画面が表示されるので、［詳細設定］（④）をクリックして画面を下にスクロール（⑤）します。画面の下のほうにある［計算方式を変更する］にチェックマークを付けます（⑥）。

　チェックマークをONにしたら、［OK］（⑦）をクリックしてダイアログボックスを閉じます。

　すると、先ほどセルD6に表示されていた「#VALUE!」というエラーは消え（⑧）、セルC6の文字列を「0」とみなした計算結果が表示されていますね。

テクニック2：「〇＋〇」のような足し算ではなくSUM関数を使って計算する

　冒頭の例では、D列の「総支給額」は「給与＋諸手当」という足し算で求めていますよね。ここで解説する2つ目のテクニックは、この「給与＋諸手当」という足し算をSUM関数で行うというものです。SUM関数は、引数となるセルやセル範囲に文字列が入っていても無視して計算を行ってくれるので、今回のような例でもエラーが発生することはありません。

　実際にやってみる前に、前項で行った設定を元に戻しておきます。また、最初の例でエラーが発生したのはセルD6だけですが、D列の数式はすべてSUM関数を使ったものに書き換えます。セル範囲D3:D6のデータはすべて削除しておきましょう（①）。

　では、D列にSUM関数を使った数式を入力していきます。セルD6に「=SUM(B3:C3)」（②）と入力して、［Enter］キーを押します。

　計算結果が表示されました（③）。オートフィルでこの数式をほかのセルにもコピーします（④）。

　セルD6に「#VALUE!」エラーは表示されなくなりましたね。

エラーインジケータが表示されたら…

　ここで気付いた読者もいると思いますが、セルD3などのセルの左上隅に緑色の三角形（①）が表示されていますね。これは「エラーインジケータ」と呼ばれ、何かのエラーが発生している可能性を示しています。

　エラーインジケータが表示されているセル（ここではセルD3）をクリックすると、セルの左横に「!」マークのアイコン（②）が表示されるので、クリックしてみます。するとメニューが表示され、一番上に「数式は隣接したセルを使用していません」（③）と表示されます。これは、「SUM関数の引数に隣のA列のセルが含まれていないですがよろしいですか？」というExcelからの問いかけです。今回の例では、A列は社員番号なので、もちろん計算には含めません。したがって、このエラーは無視しても大丈夫です。

　先ほどのメニューから［エラーを無視する］（④）をクリックすると、エラーインジケータは消えます。エラーインジケータが表示されている他のセルにも同様の操作をしておきましょう。

　これで、SUM関数を使った「給与支給表」が完成しましたね（⑤）。