Excel で無効な数式をグラフに含めない方法

SOFTWARE REPORT

Microsoft Excel でグラフを作成したときに、セルの中で数式を使用していたりすると、その部分が "0" としてグラフにプロットされる場合があります。

ここではそんな "0" としてプロットされてしまういくつかの場面と、その回避方法について記します。

たとえば、このような表がそうです。

最後の「アイテム合計」の行が、次のように各アイテムの合計数を計算しています。

=SUM(B2:B5)

この数式を全ての列に設定すると、まだデータが収集されていない "4 月" 以降のセルについても "0" と計算されてしまいます。

これをそのままグラフにすると、当然のように 0 がグラフに反映されます。

ここで、表の見栄えを良くするためにデータが収集されていないセルは合計を表示しないようにしてみても、その数式が入ったセルは "0" としてグラフにプロットされてしまいます。

=IF(COUNT(B2:B5) > 0, SUM(B2:B5), "")

COUNT 関数を使って、計算対象のセルに値がひとつも設定されていない場合は空白セルにするようにすると、表自体には不要な "0" が表示されなくなってきれいになります。

ただ、見えなくなったセルにも関数が入っていると、そのセルは "0" としてグラフにプロットされます。

不要なセルをグラフに含めたくない場合は、そのセルの値が "#N/A" になるようにします。

#N/A という値は NA() という数式で設定することができるので、先ほどの計算式を調整して、データが収拾されていない場合の値をこの数式に置き換えます。

=IF(COUNT(B2:B5) > 0, SUM(B2:B5), NA())

このようにすると、表では、値が収拾されていないところが "#N/A" という表示になります。

慣れないと不思議に見えるかもしれませんが、#N/A という表示を見れば何らかの数式が入っていて、その値が無効なことが判るので、慣れてしまえばこちらの方が分りやすいと思います。

そしてこの #N/A はグラフに反映されないため、意図したとおりのグラフになりました。

ちょっとしたひと手間ですけど、数式を含むセルを正しくグラフ化するのにとても大事なポイントです。