【Excel小技】グラフに、0の部分を表示しない方法【良く見せたい】

  • ガイド ( How to )
  • ありがとう数:0

はじめに

毎日エクセルを使っている皆さん、こんにちわ。


エクセルの困り事は、解決すると、つい人に教えたくなります。

そこで、これまで自分が困ったエクセル小技から

よく使うものを、解決テクとしてまとめます。


今回は、

「エクセルのグラフで、0を表示しない、非表示にする方法」

です。

STEP1エクセルのグラフで、0を表示したくない時って、どんな時?

まずは、こちらのグラフをご覧ください。

Photo by mine

見た感じ、みなさんの印象はどうでしょうか?


目標に肉薄する実績、

行ける、行けるのか?行かないとヤバイ!

今は1月、さぁ、2月3月がんばろうぜ!


みたいな状況を想像してください。

このグラフを見たら、

なんか2月と3月売れない気がしてきませんか?


「9月に1回目標超えたけど、1月もちょっと伸びてきたけど、

 でも9月のときみたいに、また下がるんでしょ?」


なんて声が聞こえてきそうで・・・。

だから、2月と3月の0を、できればグラフとしては

表示しないようにしたい。そう思いませんか?


グラフをうまく見せるテクニックは、とても大事です。

人身掌握という点で、とにかく視覚に訴えるものは、

とても重要なので、こういったものは、うまく演出したいものです。

そんなとき、#N/A


グラフの元データで、ゼロ0表示の部分を、
#N/Aエラーで出せば、グラフには反映されません。
Photo by mine

実際グラフを見るとわかりますが、

気分的に、2月3月、

なんだか行けそうな気がしてきませんか?


「#N/Aとか、不要でしょ?」

という人にも、このテクニックの重要性が

少し理解いただけたかと思います。

STEP2【演習】「0」を「#N/A」エラーにする

実践演習


それでは、具体的な演習です。


例1のようなデータを用意します。

これで、折れ線とか棒グラフを作成すると、

2月の部分がガクンと凹んでしまいます。


例1:元データ

1月:10

2月:0

3月:5

Photo by mine

このデータに対して、0の部分を#N/Aエラーが出るようにすると、

2月の部分が表示されず、

折れ線グラフとか棒グラフで見たときに、

凹みが無いような見た目になります。


例2:改善後データ

1月:10

2月:#N/A

3月:5


となれば、OKです。

Photo by mine

STEP3【注意点】「#N/A」がテキストでなく、エラーとして認識させる

最初このテクニックを始めて知った時、

自分で

1月:10  (⇒手打ちの10)

2月:#N/A (⇒0を「#N/A」という文字に置換した)

3月:5   (⇒手打ちの5)

としましたが、まったくグラフに反映されずに玉砕しました。(※)


#N/Aは計算式によってエクセルが出してくれる[#N/Aエラー]でないと

グラフに反映されません。


エクセルが出してくれる#N/Aとは、

セルに「=NA()」という、”数式”を入力します。

普段みなさん使い慣れている「=if(条件,真,偽)」とかの類と同じです。

”if”みたいな感じで、”NA”なんです。


方法としてはこんな感じです。

1月:10  (⇒手打ちの10)

2月:#N/A (⇒セルに「=NA()」と入力)

3月:5  (⇒手打ちの5)


最近のエクセルは、#N/Aと手打ちで入れても、OKらしい。

※最近の最新エクセルでは、#N/Aと手打ちで文字を入れても、

 自動的に、「#N/A(テキスト)」ではなく「#N/A(エラー)」として

 認識してくれるようです。でも、確実なのは、数式で「=NA()」!

STEP4【応用演習】元データが全部数式の時に、0なら#N/Aを出す

さきほどの元データが全部数式で出されてるとしましょう。

1月:10  (⇒例えば「A1*5」)

2月:0  (⇒例えば「A2*5」)

3月:5  (⇒例えば「A3*5」)

※例えば、上記の例では、

 このほかにセルA1に2、セルA2に0、セルA3に1、とかが入ってる。


この場合、

現在の数式を、if文で判定し、

結果が0ゼロなら#N/A、

結果が0以外ならそのまま計算結果を出す

ようにします。


=IF(現在の数式=0,NA(),現在の数式)

としてみてください。例えばこんな感じ。

1月:10  (⇒例えば「=if(A1*5=0,NA(),A1*5」)

2月:#N/A (⇒例えば「=if(A2*5=0,NA(),A2*5」)

3月:5  (⇒例えば「=if(A3*5=0,NA(),A3*5」)


このデータを下にグラフを作ると、

N/Aエラーはグラフに反映されない、

という仕組みになります。

STEP5【高等テク】データラベルの書式設定を使う方法も。さらにマクロ

データラベルの書式設定を使うという、

言葉だけ聞くと、一瞬何言ってるの?という

正直、ちょっと高等テクニックというか、使う人の限られるテクニック。

↓こちら↓

エクセルのグラフで、値0のときのみ、ラベルを非表示にしたい

エクセルで棒グラフ、円グラフを作成しています。
ラベルで値を表示するようにしていますが、値が0のときは、見た目的にうるさいので、表示しないようにしたいのですが、何か設定することができますか?
個別にひとつずつ消すこともできますが、面倒なので・・・
何か良い方法を教えてください。

棒グラフでしか確認していませんが。エクセル2000です。
グラフのラベルを選択して、データラベルの書式設定から表示形式を選択。ユーザー定義で
_ * #,##0_ ;_ * -#,##0_ ;"" ;_ @_
と入力すれば、ゼロ値は表示されなくなります。
一つ目のセミコロンまでは+値の表示形式、二つ目は-値、三つ目がゼロ値の表示形式で最後が文字の表示形式になります。ゼロはヌル(ブランク)表示です。

あとマクロ。これは、使える人向けですが、必要な人にはとても切実。

↓こちら↓

エクセルの折れ線グラフ、データが#DIV/0!の場合のグラフのみ非表示にする方法

エクセルの折れ線グラフ、データが#DIV/0!の場合、グラフのみ非表示にする方法が知りたいです。


月別生産高
月    1月  2月  3月 4月
A工場   10  空白 16 6
B工場   10  空白 14 18 
平均    10 #DIV/0! 15 12
の場合

で月と平均で折れ線グラフをつくると

1月の10から2月の0に向けてグラフが下落し、2月の0から3月の15に向けて急上昇します。
ここで2月の値は0ではなく、データそのものがないものとして
1月は10のところに点 2月は点なし 3月は15に点 3月から4月は線で結ばれ4月は点 というように2月の点とその両サイドの線を消してデータがない部分は不連続のグラフにしたいわけです。
当該グラフを選択後、ツール>オプション>グラフ>空白セルのプロットをしないようにすれば空白セルの部分のグラフが表示されないのはわかるのですが、平均などの場合、空白にはなりません。空白にするにIF関数=IF(平均=0,"",平均)つかっても、そこには関数があるため、見た目は空白でも実際は空白と認識されずにゼロのところにグラフがいってしまいます。

これを解決する方法わかる方、おしえてください。

数式によって、"" 、#DIV/0!、#N/A、等が返されている場合
そのセルをクリアするマクロを組んでみました。

Sub test折れ線()
  Dim mycht As Chart
  Dim erc As Range

  '数式を再セット
  For Each erc In Range("B2:B11")
    If erc.HasFormula Then
      Range("B2:B11").FormulaR1C1 = erc.FormulaR1C1
      Exit For
    End If
  Next
  'セルデータが、""とかエラー値の場合消去
  For Each erc In Range("B2:B11")
    If erc.Value = "" Or IsError(erc) Then
      erc.ClearContents
    End If
  Next
End Sub

こちらが一番多いケース。

↓↓↓

Excel2010グラフで0値を表示したくない

Excel2010で折れ線グラフを作りたいと思っています。
期間が1日から30日まであって、その日に相当する値を折れ線グラフで表示したいのですが、今日が18日だとすると19日以降がゼロでグラフができてしまいます。値があるところでグラフ表示を止めたいのですがどのようにすればいいのでしょうか?
よろしくお願いします。

ケース1)
グラフの元数字を「数式で計算している」場合は,いまの数式が例えば
=IF(条件,"",計算式)
のようになっているなら,それを
=IF(条件,NA(),計算式)
に改めます。

ケース1’)
数式を記入しているけど""にはしていない(けど明日以降のセルには数字が表示されていない)時も,
=IF(条件,NA(),計算式)
に計算式を改めます。



ケース2)
グラフの元数字を数式じゃなく手で記入していて,明日以降のセルには間違い無く全く空っぽ何にも記入して「いない」のにゼログラフが続く時は。

グラフを選択
グラフツールのデザインタブのデータの選択を開始
「非表示及び空白のセル」で空白セルの表示を空白にします

#参考
実は実際には折れ線グラフじゃなく,棒グラフと折れ線グラフの複合グラフを描いていて,教わったように出来ないケースもあります。
一度グラフを完全な折れ線グラフだけで作成し,操作してから,改めて棒と折れ線の複合にします。

そうそう、きっかけは、大半が、

1年分の月別データがあり、後半部分が全部ゼロだったりするケース。

グラフにすると、まるで急落しているようで、それが嫌なんですよね。

こちらの質問者にはとても親近感が湧きます。

↓こちら↓

エクセル折れ線グラフ、0を表示させない

エクセル2010です。
ある計算結果から折れ線グラフを作りたいのですが、未計算の月が0(ゼロ)になるのを、IF関数で空白になるようにしています。
しかしそのデータをグラフ化すると、やはり0と認識されるのか、折れ線が0に急降下してしまいます。

こういう場合、未計算の0の月を、グラフでは非表示にできないでしょうか?

※もちろんセルに何の記述も無ければグラフにも表示されませんが、全期間が自動計算されるように関数が入っていますから、何も記述しないというのはできません。

対処法をお分かりの方がいらっしゃいましたら、ご教授よろしくお願いします。

>ある計算結果から折れ線グラフを作りたい

方法1:
=IF(「ある計算結果」=0,NA(),「ある計算結果」)
という数式を並べておいて、グラフにします。


>未計算の月が0(ゼロ)になる

方法2:
=IF(不明の条件によりグラフに表示したい月までの月,計算結果,NA())
という数式を並べておいて、グラフにします。
たとえば「今月までは計算する(結果してゼロであっても)」、来月以降はまだ計算しないのでNAにする、といった判断をする場合です。

エクセルで系列名を表示しない、という

ニーズがあるかわかりませんが、そういう高等テクニックもあるみたいです。

↓こちら↓

エクセルの棒グラフで0の時、系列名を表示しない方法

エクセルで積み上げ棒グラフと折れ線グラフの2種類のグラフを作成しています。
折れ線グラフの方は問題ないのですが積み上げ棒グラフの系列名を表示させると
数値が0の場合も系列名が表示されて重なり合ってすごく見づらくなってしまっています。
ツールのオプションでプロット設定を変更しても何も変化ありません。
0の所は系列名を表示されないようにするにはどうしたらいいでしょうか?
どうぞ宜しくお願い致します。
エクセル2000を使用しています。

これを参考に、どうぞ!
積み上げ棒グラフで数値が0の系列名を非表示にするには?
http://www.efcit.co.jp/cgi-bin3/exqalounge.cgi?print+201001/10010003.txt

数字がすべてゼロの時、系列名そのものを表示しない、

(つまりはグラフにそもそもすべて出さない)

という高等テクニックもあるみたいです。

↓こちら↓

エクセルの棒グラフで0の時、系列名を表示しない方法

エクセルで積み上げ棒グラフと折れ線グラフの2種類のグラフを作成しています。
折れ線グラフの方は問題ないのですが積み上げ棒グラフの系列名を表示させると
数値が0の場合も系列名が表示されて重なり合ってすごく見づらくなってしまっています。
ツールのオプションでプロット設定を変更しても何も変化ありません。
0の所は系列名を表示されないようにするにはどうしたらいいでしょうか?
どうぞ宜しくお願い致します。
エクセル2000を使用しています。

データラベルとして値(要素の数値)を表示させ,その数字がゼロだったときには表示させない(後述参照)事はできます。
でも値ゼロの要素の「系列名」を表示させないという設定は,原則としてありません。

一般的な積み上げ棒グラフでは,積み上がった要素の1つずつに系列名をイチイチ再掲することは,あまりしないと思います。通常は「凡例」によって表現します。
ただ,積み上がった棒が一本しか無くて,その要素として系列名を表示させるようなことならあるかもしれませんね。


#参考
敢えて言うと,次の手順で出来ます。
1.グラフの元の表で,ゼロのセルに=NA()と記入する
 若しくは計算式の計算結果をグラフの元の表にしている場合は,計算式を
 =IF(計算結果=0,NA(),計算結果)
のように作成する

2.積み上げ棒グラフを,通常の2Dの(平面的な)のじゃなく,3D効果の付いた立体的な積み上げ棒グラフにする
 




#参考
データラベルで「値」を表示させる
データラベルの書式設定で表示形式でユーザー定義を選び
#
あるいは
G/標準;;;
などのように設定する。

STEP6最後に、大事な注意点。「グラフに、0部分の表示が必要な時もある」

最後に注意点があります。


あくまで、#N/Aエラーは、

「グラフに該当データが反映されていないだけ」

なので、

実績として「0」という数字を残す必要があるケースでは、

当然、グラフも0として出す必要があります。

こうした点には、注意が必要です。


エクセルが自動的に表示しているからといっても、
実績としての0は、ちゃんと表示が必要です。ご注意を!


「グラフに、0部分の表示が必要な時もある」

とは、どんな時でしょうか?

Photo by mine

もし、上記例が受注件数だとして、

2月の0件受注が正しいのに、

このグラフを、上司に出してしまったとしましょう。

どうなるでしょう?


上司「おー、がんばったね。2月は受注7~8件くらいあった感じ?」


あなた「そ、そうですね、えーとですね・・・」


(^^;)

まとめ

いかがでしたでしょうか。

それでは、エクセルで、今日も快適な良い一日を。

このユーザなら私の疑問や悩みを解決してくれそうと思ったら、
質問への回答をリクエストすることができます。

関連するタグ

特集


感謝でトクする時代へ!感謝経済に参加しよう!

ピックアップ

ページ先頭へ