[ SOFTWARE REPORT ]

Microsoft Office Excel 2013

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

2013/10/09 Tomohiro Kumagai

□ 数式で計算された空白セルやエラーのセルをグラフに含めない

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

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

数式が 0 になってグラフに反映される場合はそのまま 0 がプロットされる

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

計算式の 0 がグラフに含まれる例

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

=SUM(B2:B5)

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

 

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

数式が計算した不要な 0 がグラフに含まれてしまうパターン

数式で不要な 0 を空白セルに変えた場合も 0 がプロットされる

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

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

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

関数を調整することで、データが収集されていないセルを空白にすることはできますが、これだとグラフには 0 として表示されます。

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

表には表示がなくても、数式があれば、グラフには "0" がプロットされます。

不要な値を #N/A にすれば、そのセルはグラフにプロットされない

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

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

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

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

データが収集されていない場合に NA() を返すことによって、そのセルを無効な値として指定することができます。

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

 

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

#N/A を活用することで、意図したとおりのグラフを作ることができました。

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


 

カスタム検索

copyright © Tomohiro Kumagai @ EasyStyle G.K.
contact me: please from mail-form page.