折れ線グラフでデータがない所を0としない

ちょっとわかりにくいかもしれませんので、まず下の表と折れ線グラフを見てください。
e13-3.png
このグラフは5行目の限界利益率を折れ線グラフにしたものですが、3月に注目。売上高、変動原価が入っていない為に計算式で算出している5行目の限界利益率は#DIV/0!となっています。(G5の計算式は=G4/G2)
それ故に3月の折れ線グラフは0となって右肩下がりな印象を受けてしまいます。また、類似曲線を表示させた場合には0を含んだ類似曲線になってしまいます。
では折れ線グラフを2月までで切って3月を表示しないようにするにはどうしたら良いでしょうか。

いじったセルの宿命

上の例ではG5は#DIV/0!エラーになってます。0で割ることは出来ませんから。では#DIV/0!じゃなかったら大丈夫なのか?
いいえ、仮にIFで””(データ無)としたり0と表示させてもグラフは0と表示してしまいます。
=IF(G2=””,””,G4/G2)  でもダメ、同じグラフになります。
というのも、いくら””でデータ無し、空白ですとしても計算後の空白はデータがあるものとしてグラフは0認識してしまうのです。いじったセルの宿命です。何もいじっていないセルとは判定されません。
計算式を消してしまえばグラフで0と認識せずにデータ無しと認識されます。

じゃあいちいちセルを消す?

面倒です。><
ですので、いじったあとのセルでもデータ無しと認識させれば良いのです。
それは NA()関数を使います。
NA()関数は「エラー値#N/A(値が無効)」を返す関数です。これをIF文に絡ませて
=IF(G2=””,NA(),G4/G2)
としてしまえば、G2が空白だった場合は#N/Aと表示されます。そうなればグラフの線も消えます。
e13-4.png
NA()の代わりに直接#N/Aと打っても構いません。また、これを使うと類似曲線もその#N/A値は含まない類似曲線になるので実態に合った線が作成可能です。

でもエラー値で見栄えが悪いよ・・・

たしかにエラー値が表にあればそれだけで見栄えが悪くなってしまいます。でも#N/Aとしなければグラフの線は消えない・・・。
じゃあセルの文字色を背景色と同化させて無理やりエラーを見えなくしましょう。
そこで条件付き書式を使います。
e13-5.png
まずルールの種類を「指定の値を含むセルだけを書式設定」を選び、ルールの内容編集で「エラー」のセルのみを「書式設定」で文字色を背景色と同じにします。(この例では白文字にすればOK)
そうすればエラー#N/Aは表示されているものの、背景色と同じ文字色なので見えない。という事が実現できます。

Comment

  1. エクセル初心者 より:

    月間グラフで15日以降のデータがない所を0としない(表示しない)場合、#N/Aは正しく機能します。
    しかし、月間グラフで、平日のみ折れ線を表示し、土・日・祝日等の休日のみ折れ線を非表示にしたい場合、#N/Aはうまく機能しません。
    具体的には、NA()関数を使用して土・日曜日を#N/Aにしても、翌月曜日に数値を入れると、金曜日と月曜日を結んだグラフが描かれます。尚、 土・日曜日のマーカーは表示されません。
    小生は、休日は営業をしていないので、マーカーも折れ線グラフも表示したくありません。
    因みに、小生が作成しているグラフは、販売額を棒グラフで表し、目標に対する達成割合(%)を折れ線で表す2軸グラフです。
    土・日曜日について、棒グラフは0円で問題ないのですが、達成割合を表す折れ線グラフが表示されます。
    現在は、毎月達成割合の式をコピーした後、休日の式を手作業で削除しています。
    この作業を無くしたく、よろしくお願い致します。

  2. Isakat より:

    >エクセル初心者 さん
    確かにこの方法では間の抜けた折れ線グラフは作れません。
    といいますか、数式を含んだセルでは間の抜けた折れ線グラフは不可能かと思います。
    私でしたら以下のどちらかの方法を採ると思います。
    1.元々のデータテーブルに土日祝を作らない。
      日付データが自動で入ってしまうのでは難しいかもしれませんが、
      WORKDAY関数なんかも使えるかもしれません。
      WORKDAY関数は土日祝を無視して日付の加算が出来ます。
    2.エクセル初心者さんと同じように毎月手作業で消す。
      但し1日分ずつ消すのではなくジャンプ機能を使ってエラー値のみを一気に消す。
      マクロで処理登録すればボタン1つの作業ですし。

  3. 三上 盛 より:

    上記の表を作成、グラフを作成して
    =IF(D2=””,NA(),D4/D2)と
    入力したが線画消えない。
    なぜでしょうか、教えてください。
    Excel2013を使用しています。

    • isakat より:

      =IF(D2=””,NA(),D4/D2) と入力したセルの結果はどうなっていますか?
      #N/Aとなっていますか?

  4. ヒロシ イマダ より:

     対数グラフを多く作成した時、数値の更新の時その都度0、負数コメントにOKを返答しないと次に進めない。コメントを非表示にする方法がありますか。

コメントをどうぞ

メールアドレスが公開されることはありません。

このサイトはスパムを低減するために Akismet を使っています。コメントデータの処理方法の詳細はこちらをご覧ください

  • ▼お小遣い稼ぎしませんか?▼ 日々の生活にhappyをプラスする|ハピタス

    クレジットカード・FX口座を作るだけで簡単に3万円GETです。一切支出はありません。

テンプレート NA() SUM 置換 フロー図 SUMIFS VLOOKUP グラフ MATCH COLUMN ユーザー定義関数 HLOOKUP 日付 検索 図形 無料 XLSTART SUMIF 条件付き書式 変換 バーコード 作業効率UP TODAY コピペ INDEX ワイルドカード DATE COUNTIF 印刷 目標
  • 関西在住の30代サラリーマン。事務職で毎日Excelと戯れています。
    システム導入なんて資金が無い!Excelでなんとかやりたい零細中小の社長さん・事務員さんの力になればと思います。
    詳しいプロフィール


PAGE TOP ↑