Excel (一般機能)

Excelの一般機能に関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 8.1 Pro : Excel 2010)
グラフ中の#N/Aは線で結ばない
投稿日時: 18/01/12 21:56:02
投稿者: tako552101

お世話になります。
 
折れ線グラフを作成しているのですが、入力基のセル参照ならばデータが空白の場合、その間は線でつながりません。
しかし、分け合って計算セルを参照しているため、IFなどで=""とすると0とみなして、下部に固まってグラフが美しく仕上がりません。
#N/Aを入れるとデータ範囲の前、後に関してはその場で線は消えますが、途中はデータ有りから#N/Aを飛ばして次のデータ有りまで、線でつながれてしまい見にくくなります。
 
下記データのような場合、例えば項目1列なら、横軸の2/1地点から折れ線が表示されてOKですが、項目7列の場合は1/15〜2/12が線で結ばれてしまいます。データがなし(#N/A)の場合は線でつなぎたくないのですが、何かいい方法ありますでしょうか。マクロで#N/A箇所を強引に消去するなどの荒業以外でご指導いただけると大変助かります。
 

日付	項目1	項目2	項目3	項目4	項目5	項目6	項目7
1/12	#N/A	#N/A	5	#N/A	#N/A	7	8
1/14	#N/A	#N/A	3	#N/A	#N/A	8	8
1/15	#N/A	#N/A	6	#N/A	#N/A	8	8
1/18	#N/A	#N/A	5	8	6	#N/A	#N/A
1/20	#N/A	#N/A	4	6	6	#N/A	#N/A
1/24	#N/A	#N/A	4	7	6	#N/A	#N/A
2/1	5	#N/A	5	#N/A	#N/A	#N/A	#N/A
2/2	5	#N/A	8	#N/A	#N/A	#N/A	#N/A
2/9	4	#N/A	6	#N/A	#N/A	#N/A	#N/A
2/10	6	#N/A	4	#N/A	#N/A	#N/A	#N/A
2/12	6	#N/A	4	#N/A	#N/A	#N/A	7
2/13	3	#N/A	4	#N/A	#N/A	#N/A	7
2/14	5	#N/A	6	#N/A	#N/A	#N/A	9
2/16	4	#N/A	5	#N/A	#N/A	#N/A	8
2/19	4	#N/A	6	#N/A	#N/A	#N/A	9
2/21	5	#N/A	6	#N/A	#N/A	#N/A	8

回答
投稿日時: 18/01/13 00:22:57
投稿者: もこな2

荒業かわかりませんけど、0でなくてブランクだったら線でつながれなかったかも。
 
あらかじめバックアップとってから エラーになってる部分をクリアして意図的にブランクにしてみてください。
 
これで線が思ったように切れてくれるなら、IFERROR関数組み込んで、セルの値がエラー値ではなくブランクになるようにしてあげればいけそうな気がします。(未テストなのでうまくいかなくてもご容赦ください

回答
投稿日時: 18/01/13 10:00:53
投稿者: んなっと

引用:
入力基のセル参照ならばデータが空白の場合、その間は線でつながりません。
しかし、分け合って計算セルを参照しているため

そうなんですよね。
元のデータから直接グラフを書くなら、空白セルをつながらないようにする設定は簡単にできます。
しかし数式で参照してからグラフを書く場合、「つながらないようにする」のは、""でも#N/Aでもうまくいきません。
「つなぐようにする」なら#N/Aにするテクニックが有名なのですが...
 
テーブル」に変換してグラフを書き、
別シートからの追加データは一番下に「コピペ」して追加していくのが手軽ではないでしょうか。
自動的にグラフ範囲も変更されます。
 
 
なお、同じ構成の複数シートを結合するのは、PowerQuery(2010の場合ダウンロードしてインストールが必要)を使う方法もあります。
今回のようなグラフを書く場合であれば上のテーブル→コピペで十分だと思いますが、
結合前の複数シートのデータが頻繁に変わる場合はおすすめです。
以下、例えば現在のブックが D:\Office\Book1.xlsx で、以下の3つのシートを結合するとします。
 
1月 シート
 
   A   B   C   D   E   F   G   H
1 日付 項目1 項目2 項目3 項目4 項目5 項目6 項目7
2 1/12          5          7   8
3 1/14          3          8   8
4 1/15          6          8   8
5 1/18          5   8   6       
6 1/20          4   6   6       
7 1/24          4   7   6       
 
2月 シート
 
    A   B   C   D   E   F   G   H
 1 日付 項目1 項目2 項目3 項目4 項目5 項目6 項目7
 2  2/1   5       5              
 3  2/2   5       8              
 4  2/9   4       6              
 5 2/10   6       4              
 6 2/12   6       4              7
 7 2/13   3       4              7
 8 2/14   5       6              9
 9 2/16   4       5              8
10 2/19   4       6              9
11 2/21   5       6              8
 
3月 シート
...
 
 
1.PowerQuery
→ファイルから
→Excelから
→D:\Office\Book1.xlsx (自分自身)選択
→OK
2.表示されるナビゲーターで
 複数のアイテムの選択 にチェックを入れる
→1月、2月、3月チェック
→読み込み
→新しくできたシートのうち、1月のデータが読み込まれているものを選択し、テーブル化された部分を選択
→[テーブルツール][クエリ][追加]
→3つ以上のテーブル
→「追加」で右に1月、2月、3月テーブルを追加してOK
3.表示されるクエリエディターで結合されていることを確認
→閉じて読み込む
 
これで新シートにAppend1クエリが読み込まれ、空白セルは空白セルのまま複数シートが結合できます。
 
      A   B   C   D   E   F   G   H
 1    日付 項目1 項目2 項目3 項目4 項目5 項目6 項目7
 2 2018/1/12          5          7   8
 3 2018/1/14          3          8   8
 4 2018/1/15          6          8   8
 5 2018/1/18          5   8   6       
 6 2018/1/20          4   6   6       
 7 2018/1/24          4   7   6       
 8  2018/2/1   5       5              
 9  2018/2/2   5       8              
10  2018/2/9   4       6              
11 2018/2/10   6       4              
12 2018/2/12   6       4              7
13 2018/2/13   3       4              7
14 2018/2/14   5       6              9
15 2018/2/16   4       5              8
16 2018/2/19   4       6              9
17 2018/2/21   5       6              8
...
 
このテーブルでグラフを書きましょう。
元のデータが変わったら、ブックを上書き保存してから
[テーブルツール][クエリ][最新の情報に更新]でグラフも更新されます。
 
 
新たに4月シートを追加するときは
 
4.PowerQuery
→ファイルから
→Excelから
→D:\Office\Book1.xlsx (自分自身)選択
→OK
5.表示されるナビゲーターで
→4月チェック
→読み込み
6.上の3.で結合してできたAppend1クエリのテーブルを選択
→[テーブルツール][クエリ][クエリの編集]
7.表示されるクエリエディターで[クエリの追加]
→4月を追加してOK
→閉じて読み込む
 
グラフが更新されています。

回答
投稿日時: 18/01/13 10:48:07
投稿者: 細雪

tako552101 さんの引用:
マクロで#N/A箇所を強引に消去するなどの荒業以外でご指導いただけると大変助かります。

 
通常ではできないことを実現するなら強引に行くしかないと思うのですが。
素直に「マクロは出来ないのでマクロ以外で」と言ってのけるほうが好印象ですよ。
 
 
さておき、以下参考までに。
・グラフにしたい範囲を選択
・ジャンプ機能を起動(Ctrl+G または F5単独押下)
・「セル選択」をクリック
・「数式」を選択し、「エラー値」のみチェックが入っている状態にしてOKボタン
  ※エラー値が返っているセルだけ選択された状態になります。
・Deleteキーで選択セル(エラー値)を消去
これでエラーが返っていたセルが綺麗に消えます。
人によってはコレも「強引」と言われるのかもしれません。
 
で、コレをあえてマクロでこなすなら
    Selection.CurrentRegion.SpecialCells(xlCellTypeFormulas, 16).ClearContents
  ※グラフにしたい範囲の任意のセルにフォーカスがある状態で起動。
のたった1行で完了です。
コレを強引と言うか?は個人の判断です。
ちなみに私は、上記は至極真っ当であると思ってますよ。
「エラー値だったら消去、を範囲内全セルについて繰り返す」という書き方をしていると
めんどくさいことしてるねぇ・・と苦笑するところですけどね。

回答
投稿日時: 18/01/13 15:11:08
投稿者: んなっと

もうひとつ PowerQuery を使う方法です。
 
現在のブックが D:\Office\Book1.xlsx で、#N/Aのあるシートを「抽出」シートとします。
 
抽出 シート
 
    A   B   C   D   E   F   G   H
 1 日付 項目1 項目2 項目3 項目4 項目5 項目6 項目7
 2 1/12  #N/A  #N/A   5  #N/A  #N/A   7   8
 3 1/14  #N/A  #N/A   3  #N/A  #N/A   8   8
 4 1/15  #N/A  #N/A   6  #N/A  #N/A   8   8
 5 1/18  #N/A  #N/A  #N/A   8   6  #N/A  #N/A
 6 1/20  #N/A  #N/A  #N/A   6   6  #N/A  #N/A
 7 1/24  #N/A  #N/A  #N/A   7   6  #N/A  #N/A
 8  2/1   5  #N/A   5  #N/A  #N/A  #N/A  #N/A
 9  2/2   5  #N/A   8  #N/A  #N/A  #N/A  #N/A
10  2/9   4  #N/A   6  #N/A  #N/A  #N/A  #N/A
11 2/10   6  #N/A  #N/A  #N/A   6  #N/A  #N/A
12 2/12  #N/A  #N/A  #N/A  #N/A   6  #N/A   7
 
1.PowerQuery
 →ファイルから
→Excelから
→D:\Office\Book1.xlsx (自分自身)選択
→OK
 2.表示されるナビゲーターで
→「抽出」シート選択
→読み込み
 
これで#N/Aエラーがないシートができるので、こちらでグラフを書きます。
 
      A   B   C   D   E   F   G   H
 1    日付 項目1 項目2 項目3 項目4 項目5 項目6 項目7
 2 2018/1/12          5          7   8
 3 2018/1/14          3          8   8
 4 2018/1/15          6          8   8
 5 2018/1/18              8   6       
 6 2018/1/20              6   6       
 7 2018/1/24              7   6       
 8  2018/2/1   5       5              
 9  2018/2/2   5       8              
10  2018/2/9   4       6              
11 2018/2/10   6              6       
12 2018/2/12                 6       7
 
元のデータが変わったら、ブックを上書き保存してから
[テーブルツール][クエリ][最新の情報に更新]でグラフも更新されます。

回答
投稿日時: 18/01/13 15:25:06
投稿者: んなっと

第2の方法の注意点は、抽出シートが以下のような不正な形式にならないことです。
 
    A   B   C   D   E   F   G   H
 1 日付 項目1 項目2 項目3 項目4 項目5 項目6 項目7
 2 1/12  #N/A  #N/A   5  #N/A  #N/A   7   8
 3 1/14  #N/A  #N/A   3  #N/A  #N/A   8   8
 4 1/15  #N/A  #N/A   6  #N/A  #N/A   8   8
 5 1/18  #N/A  #N/A  #N/A   8   6  #N/A  #N/A
 6 1/20  #N/A  #N/A  #N/A   6   6  #N/A  #N/A
 7 1/24  #N/A  #N/A  #N/A   7   6  #N/A  #N/A
 8  2/1   5  #N/A   5  #N/A  #N/A  #N/A  #N/A
 9  2/2   5  #N/A   8  #N/A  #N/A  #N/A  #N/A
10  2/9   4  #N/A   6  #N/A  #N/A  #N/A  #N/A
11 2/10   6  #N/A  #N/A  #N/A   6  #N/A  #N/A
12 2/12  #N/A  #N/A  #N/A  #N/A   6  #N/A   7
13 2/13  #N/A  #N/A  #N/A  #N/A   6  #N/A   7
14     #N/A  #N/A  #N/A  #N/A  #N/A  #N/A  #N/A
15     #N/A  #N/A  #N/A  #N/A  #N/A  #N/A  #N/A
16     #N/A  #N/A  #N/A  #N/A  #N/A  #N/A  #N/A
 
 
数式を少しだけ工夫して
A2
=IF(Sheet2!A2="","",Sheet2!A2)
下方向・↓
B2
=IF($A2="","",IF(Sheet2!B2="",NA(),Sheet2!B2))
下方向・↓
 
    A   B   C   D   E   F   G   H
 1 日付 項目1 項目2 項目3 項目4 項目5 項目6 項目7
 2 1/12  #N/A  #N/A   5  #N/A  #N/A   7   8
 3 1/14  #N/A  #N/A   3  #N/A  #N/A   8   8
 4 1/15  #N/A  #N/A   6  #N/A  #N/A   8   8
 5 1/18  #N/A  #N/A  #N/A   8   6  #N/A  #N/A
 6 1/20  #N/A  #N/A  #N/A   6   6  #N/A  #N/A
 7 1/24  #N/A  #N/A  #N/A   7   6  #N/A  #N/A
 8  2/1   5  #N/A   5  #N/A  #N/A  #N/A  #N/A
 9  2/2   5  #N/A   8  #N/A  #N/A  #N/A  #N/A
10  2/9   4  #N/A   6  #N/A  #N/A  #N/A  #N/A
11 2/10   6  #N/A  #N/A  #N/A   6  #N/A  #N/A
12 2/12  #N/A  #N/A  #N/A  #N/A   6  #N/A   7
13 2/13  #N/A  #N/A  #N/A  #N/A   6  #N/A   7
14                            
15                            
16                            

回答
投稿日時: 18/01/14 08:23:10
投稿者: んなっと

最初の複数シートを結合する方法は、状況が当てはまらなければ無視してください。
分かれたセル範囲を数式で参照しているのではなく、
分け合って「計算」セルを参照しているということなので、おそらく違いますね。

投稿日時: 18/01/14 09:52:39
投稿者: tako552101

皆さんありがとうございます。
 
グラフはあまり使ったことが無いのでわからないことが多いのですが、結果、データなし部分は通常なら線でつながってしまう「仕様」ということですね。
 
いずれ、このままでいくか、線なしにするためデータのコピペをマクロで行うかなど、検討して改めて質問させていただきます。
 
言葉足らずの部分もあったと思いますが、丁寧にご回答頂いて感謝します。本当にありがとうございました。
 

投稿日時: 18/01/16 09:25:15
投稿者: tako552101

閉じ忘れました。
ありがとうございました。