エクセルで折れ線グラフや散布図を作成すると、元データに空白セルがある箇所で線が途切れたり、意図しない空白が表示されたりすることがあります。
売上、気温、在庫数、アクセス数の推移などでは、未入力の期間だけを飛ばして線をつなぎたい場面も多いでしょう。
この記事では、グラフの非表示および空白セルの設定を使って、空白を表示しない方法と、空白部分を線でつなぐ方法を詳しく解説します。
空白セルがあるグラフでは、データの意味に合わせて表示方法を選ぶことが重要です。
空白を残す、ゼロとして扱う、前後の値を線でつなぐという3つの設定を使い分けましょう。
なお、数式の結果が空文字列になっているセルと、完全に未入力のセルでは、グラフ上の見え方が異なる場合があります。
データの集計方法も確認しながら、読みやすく誤解のないグラフに整えていきましょう。
エクセルのグラフで空白を線でつなぐ設定
それではまず、空白セルを含むグラフを線でつなぐ基本設定について解説していきます。
| 月 | 売上 |
|---|---|
| 4月 | 120 |
| 5月 | 空白 |
| 6月 | 180 |
グラフ要素を選択する操作
まず、作成済みの折れ線グラフをクリックして、グラフ全体を選択します。
枠線とハンドルが表示された状態で、グラフ内の折れ線やプロットエリアを右クリックしましょう。
データ系列そのものではなく、グラフ全体を選択した状態で操作することがポイントです。
右クリックメニューに目的の項目が見つからない場合は、グラフの余白部分をクリックして選択し直してください。
グラフが小さいと選択しにくいため、いったん大きく表示してから操作すると項目を見つけやすくなります。
この設定は棒グラフよりも、値の連続性を表したい折れ線グラフや散布図で特に役立ちます。
データの選択画面を開く手順
グラフを選択したら、上部リボンの「グラフのデザイン」タブを開き、「データの選択」をクリックします。
「データソースの選択」ダイアログが表示され、系列名、系列値、横軸ラベルの参照範囲を確認できます。
ここではデータ範囲を変更しなくても問題ありませんが、対象の系列が正しい列を参照しているか確認しておきましょう。
月が横軸、売上が縦軸になる例では、見出しを除いたA2からB4が基本の対象範囲です。
途中に空白があるからといって、空白行を削除する必要はありません。
元データを残したまま表示方法だけを変えられる点が、この設定の便利なところです。
非表示および空白セルの設定
「データソースの選択」ダイアログ左下にある「非表示および空白セル」をクリックします。
表示された画面には、空白セルの表示方法として「空白」「ゼロ」「データポイントを線で結ぶ」が並びます。
空白を飛ばして前後のデータを結びたい場合は、データポイントを線で結ぶを選択してください。
その後、「OK」を押してダイアログを閉じると、4月の120と6月の180が一本の線としてつながります。
線でつなぐ設定は、途中の値が未収集である一方、時系列の流れを途切れさせたくない場合に向いています。
反対に、休業や計測停止など、値が存在しない事実を見せたい場合は空白表示を選ぶほうが適切です。
【操作のポイント】「非表示および空白セル」は、グラフを右クリックするより「グラフのデザイン」タブから開くと見つけやすい項目です。
空白セルを表示しないためのデータ整理
続いては、設定を変えても空白が残る場合に確認したい元データの扱いを確認していきます。
| 日付 | 実績 | グラフ用 |
|---|---|---|
| 9月1日 | 50 | 50 |
| 9月2日 | 空白 | 空白 |
| 9月3日 | 68 | 68 |
本当に空白のセルを確認する方法
見た目が空白でも、セル内にスペース、アポストロフィ、数式、非表示文字が入っていることがあります。
該当セルを選択して数式バーを見て、何も表示されないかを確認してください。
スペースが入力されている場合、エクセルは完全な空白セルとは判定しないことがあります。
不要な文字をDeleteキーで消去し、完全に未入力の状態にすると、空白セル設定が反映されやすくなります。
数値が入っていたセルを削除しただけでは表示が更新されない場合もあるため、グラフを一度クリックして再計算状態を確認しましょう。
空文字列を返す数式の扱い
IF関数などで条件に合わないときに空欄を返す場合、一般的には次のような数式を使います。
=IF(B2=””,””,B2)
この数式では、B2が空白なら見た目上は何も表示されず、B2に値があればその値を返します。
ただし、“”は完全な未入力ではなく、空文字列という数式の結果です。
グラフの種類やExcelのバージョンによっては、空文字列がゼロに近い扱いになることもあるため、実際のグラフで確認してください。
意図しないゼロ表示が出る場合は、グラフ用の補助列を用意し、NA関数を使う方法も検討できます。
グラフ用の補助列を作る方法
元データを直接変更したくない場合は、グラフ専用の補助列を作る方法が安全です。
たとえばB列に実績があり、C列をグラフ用にする場合、C2には次の数式を入力します。
=IF(B2=””,NA(),B2)
1行目をヘッダーとした場合、C2に入力した数式を下方向へオートフィルしてコピーしましょう。
NA関数はグラフで描画しない値として扱われやすく、未入力部分だけを視覚的に除外したいときに便利です。
ただし、エラー値を他の集計式で参照すると計算結果に影響するため、集計用列とグラフ用列は分けて管理するのが安心です。
【操作のポイント】空白に見えるセルでも数式が入っていれば挙動が変わるため、数式バーと表示形式を両方確認しましょう。
グラフの種類別に確認したい設定
続いては、折れ線グラフ、散布図、棒グラフごとの空白セルの見え方を確認していきます。
| グラフの種類 | 空白を線でつなぐ効果 |
|---|---|
| 折れ線グラフ | 時系列の推移を連続的に見せやすい |
| 散布図 | X値とY値の組み合わせを確認する必要がある |
| 棒グラフ | 棒が出ない表示になりやすい |
折れ線グラフにおける表示の確認
折れ線グラフは、日付、月、週などの順番に沿って数値の変化を見るためのグラフです。
空白セルを線で結ぶと、欠測期間をまたいで推移を見やすくできます。
一方で、その期間に実際の数値があったように見える可能性もあります。
線で結ばれた区間は推定値ではないことを、注記やデータラベルで伝えると誤解を減らせます。
散布図におけるX値とY値の対応
散布図では、横軸のX値と縦軸のY値が一組になってデータポイントを構成します。
たとえばA列に日付、B列に気温がある場合、片方だけが空白の行は正しく描画できません。
空白を線で結ぶ設定を行っても、X値またはY値の欠損によって期待どおりにつながらないことがあります。
その場合は、系列の編集画面でXの値とYの値の参照範囲が同じ行数になっているか確認しましょう。
棒グラフと縦棒グラフの見え方
棒グラフや縦棒グラフでは、空白セルを線でつなぐという考え方は基本的に使いません。
空白のカテゴリでは棒を表示しない、またはゼロの高さの棒として扱う表示になります。
売上が未報告なのか、売上がゼロなのかで意味は大きく異なります。
ゼロと空白を同じ扱いにしないことが、報告用グラフを正確に作る基本です。
【操作のポイント】空白をつなぐ設定は主に折れ線グラフ向けです。グラフの種類を変えた後は表示結果を必ず確認してください。
B
罫線
データの選択
➤
| A | B | C | |
|---|---|---|---|
| 1 | 月 | 売上 | グラフ用 |
| 2 | 4月 | 120 | 120 |
| 3 | 5月 | 空白 | 空白 |
| 4 | 6月 | 180 | 180 |
ゼロ表示と空白表示の使い分け
続いては、ゼロとして表示する場合と空白として扱う場合の判断基準を確認していきます。
| データの状態 | 適した表示 |
|---|---|
| 売上が発生しなかった | ゼロ |
| まだ報告されていない | 空白 |
| 途中の計測が欠けた | 空白または線で結ぶ |
実績がゼロである場合
販売数が0件、問い合わせが0件など、実際に結果がゼロだった場合は0を入力します。
ゼロを空白に置き換えると、未集計なのか実績ゼロなのかが区別できなくなります。
グラフの「ゼロ」設定は、空白セルをゼロ扱いにする機能であり、元データに0が入力されるわけではありません。
業務上の意味を優先して、実際のゼロは数値の0で入力することが大切です。
まだ確定していない数値の場合
月末前で集計が終わっていない、入力担当者からデータが届いていない場合は、空白のままにするのが自然です。
このケースをゼロ表示にすると、売上が急減したように見えるおそれがあります。
ダッシュボードや会議資料では、空白期間の上に「集計中」と記載する方法も有効です。
グラフだけで判断する人にも状況が伝わるよう、表題や注釈を加えましょう。
平均値と合計値への影響
グラフ上で空白を線で結んでも、SUM関数やAVERAGE関数の計算結果が自動で補間されるわけではありません。
たとえばB2からB10の平均を求める式は、次のように入力します。
=AVERAGE(B2:B10)
AVERAGE関数は通常、空白セルを分母に含めませんが、0は平均の対象に含めます。
グラフの表示設定と関数の集計ルールは別物として理解しておくと、数値の不一致を防げます。
【操作のポイント】空白をゼロに見せる設定を選ぶ前に、その空白が「実績なし」なのか「情報なし」なのかを確認しましょう。
数式とオートフィルを使ったグラフ用データ
続いては、元データを保護しながらグラフ表示を整える補助列と数式の使い方を確認していきます。
| A列 | B列 | C列 |
|---|---|---|
| 日付 | 実績 | グラフ用数値 |
| 9月1日 | 50 | =IF(B2=””,NA(),B2) |
NA関数を使う数式
グラフから欠損値を除外したい場合、C2に次の数式を入力します。
=IF(B2=””,NA(),B2)
B2が空白ならNA関数によるエラー値を返し、B2に数値があればその数値を返す仕組みです。
グラフはNAエラーを描画対象外として処理することが多く、不要なゼロ点を避けられます。
ただし、セルには#N/Aが表示されるため、見せる表とグラフ用の表を分ける運用に向いています。
先頭セルからオートフィルする操作
1行目にヘッダーがある表では、C2に数式を入力したあと、セル右下のフィルハンドルを下へドラッグします。
隣のB列に連続したデータがある場合は、フィルハンドルをダブルクリックして最終行までコピーする方法も便利です。
C2だけに数式を入れ、以降はオートフィルで相対参照を自動調整すると入力ミスを減らせます。
C3ではB3、C4ではB4を参照する式に自動で変わるため、各行へ個別に式を書き直す必要はありません。
グラフの参照列を切り替える操作
補助列を作成したら、グラフを選択して「グラフのデザイン」から「データの選択」を開きます。
系列を選んで「編集」をクリックし、系列値を元のB列からグラフ用のC列へ変更してください。
横軸ラベルは日付が入ったA列を参照したままにします。
グラフだけに補助列を参照させることで、元の実績データと集計式を変更せずに表示を整えられます。
補助列は「表示専用」の列です。
元データ、集計用データ、グラフ用データを役割別に分けると、ブックの保守性が高まります。
【操作のポイント】数式を入れるのはデータ開始行のC2です。見出し行のC1には「グラフ用数値」など用途が分かる名前を付けましょう。
複数系列と非表示データの確認
続いては、複数の系列やフィルターを使うグラフで空白が発生する場合を確認していきます。
| 月 | 東京 | 大阪 |
|---|---|---|
| 4月 | 120 | 110 |
| 5月 | 空白 | 130 |
| 6月 | 160 | 145 |
系列ごとに空白の意味を確認する方法
東京と大阪のように複数系列がある場合、空白セルの設定はグラフ全体に影響します。
一方の系列だけが未入力でも、設定によってはその系列だけが線で結ばれます。
地域別、商品別、担当者別のデータでは、空白の理由が系列ごとに異なることもあります。
同じ設定で表示してよいデータかを系列単位で判断することが必要です。
行列の非表示データを表示する設定
フィルターで行を隠したときや列を非表示にしたとき、グラフにデータが出なくなることがあります。
「データソースの選択」画面の「非表示および空白セル」には、「非表示の行と列のデータを表示する」というチェック項目があります。
非表示にしたデータもグラフに表示したい場合は、このチェックを有効にしてください。
ただし、レポート上で意図的に除外したデータまでグラフに現れる可能性があるため、設定後に凡例と系列の線を確認しましょう。
ピボットグラフでの空白表示
ピボットグラフでは、元になるピボットテーブルの空白項目やフィルター条件がグラフにも反映されます。
通常のグラフと同じ感覚で補助列を参照させることは難しいため、ピボットテーブル側の設定を先に確認してください。
空白という項目名が凡例や軸に表示される場合は、フィールドのフィルターから空白を外せます。
値が存在しない期間をどう見せるかは、ピボットテーブルの集計方法も含めて設計する必要があります。
複数系列では、すべての線を無条件でつなぐと比較を誤ることがあります。
データの欠損理由が異なる系列は、別グラフや補足表で示す方法も検討しましょう。
【操作のポイント】フィルターで消えたデータと空白セルは別の要因です。「非表示の行と列のデータを表示する」のチェック状態も確認してください。
まとめ エクセルのグラフで線でつなぐ・空白を表示しない設定方法
エクセルのグラフで空白を表示しない、または線でつなぐには、「データの選択」から「非表示および空白セル」を開く方法が基本です。
途中の欠損をまたいで推移を見せたいときは、「データポイントを線で結ぶ」を選択しましょう。
実績がないことを表す場合はゼロを入力し、まだ確定していないデータや未収集のデータは空白として扱うのが適切です。
空白、ゼロ、欠損は同じ意味ではありません。
グラフの見た目だけで判断せず、元データが持つ業務上の意味に合わせて設定を選びましょう。
数式による空文字列で期待どおりに表示できないときは、NA関数を使ったグラフ用の補助列も有効です。
元データを残しながらグラフ専用の列を作れば、集計作業と見た目の調整を両立できます。
正しい設定で空白セルを扱い、見やすく信頼できるエクセルグラフを作成していきましょう。
