[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『積み上げ面のグラフでゼロは非表示したい』（amaryllis）
エクセルのグラフで折れ線グラフはデータのない部分を｢=NA()｣にすれば、その部分は非表示になる。
つまり、例えば土日で会社は休日で月〜金はデータがあって金〜月の間の土日は｢=NA()｣にしておけば、グラフはゼロで表示せず金の次に月で結ぶ感じの折れ線グラフになりますが、積み上げ面のグラフだと｢=NA()｣や｢0｣や空白どれでもゼロで表示されてしまい困っています。
インターネットでくまなく調べましたが見つかりませんでした。
方法がありますでしょうか？
＜ 使用 Excel：Excel2010、使用 OS：Windows7 ＞
毎月変わるといちいち非表示する事になるのでは？と、思ってますし、うちの会社は土日でも、表示する事になっています。
なので、他の方法をお願いします。
水上さん、回答をありがとうございます。
「自動もしくは日付軸をテキスト軸
にしてやればいいんじゃないの？」
↑
どういうことか？詳しく教えて頂けますでしょうか？
「金曜を垂直に０にしたいなら方法はあります。」
↑
どういうことですか？
また方法もお願いします。
（amaryllis） 2018/11/21(水) 13:31
このやり方で良いならば、後はマクロでも使って条件を確認しつつ、対象列を順次非表示にしてしまうとか。 For文で回しつつ、If文で判定し、非表示にするなら列の .Hidden プロパティを True にするだけですよ。
（???） 2018/11/21(水) 15:16
(1)A1:D19 を選択して、積み上げ面グラフ作成
(2)E2: =IF(WEEKDAY(A2)=7,A1,IF(AND(WEEKDAY(A2)=1,A3<>""),A3,A2))で下へコピー
(3)データーソースの選択 画面で 横（項目）軸ラベル の編集で、A2:A19 を E2:E19 に変更
A B C D 1 系列１ 系列２ 系列３ 2 11月1日 木 24 18 18 3 11月2日 金 13 20 12 4 11月3日 土 5 11月4日 日 6 11月5日 月 14 24 23 7 11月6日 火 21 12 12 8 11月7日 水 12 21 10 9 11月8日 木 21 22 20 10 11月9日 金 17 13 14 11 11月10日 土 12 11月11日 日 13 11月12日 月 24 24 13 14 11月13日 火 24 22 22 15 11月14日 水 23 22 23 16 11月15日 木 20 30 40 17 11月16日 金 10 20 12 18 11月17日 土 19 11月18日 日
（きまぐれ） 2018/11/21(水) 16:20
きまぐれさんが回答した通りにやってみたら、出来ました。
おかげで助かりました。
ありがとうございます。
あとは、土日に空いている部分を金と月に線で結びと色付けをマクロでは可能ですか？
Private Sub Worksheet_～()という自動実行マクロで結べばいいなあと思っており、グラフは幾つかがあるので、
ActiveSheet.ChartObjects(1)のようにグラフ指定で線を結ぶマクロが出来ますでしょうか？
もし、不可能でしたら、ここで質問を締め切っても構いません。
（amaryllis） 2018/11/22(木) 13:23
で、定時前にその方法を確実にやろうと思ったら、問題が起きました。
きまぐれさんが回答した通りでやったのは、｢2018/11/1｣のようにして、オートフィルする方法でした。
でも、会社で使用しているのは、｢2018/11/1｣のようにして、オートフィルするものではないです。
横の表(横でも、きまぐれさんが回答した通りの方法で面グラフはOKと確認済み)になっていて、
1日のセルが
=DATA(年のセル,月のセル,1)
2日〜28日までのセルは
=1日前のセル+1
29日〜31日までのセルは
=IF(DAY(28日のセル)>DAY(28日のセル+1),"",28日のセル+1)〜=IF(DAY(28日のセル)>DAY(28日のセル+3),"",28日のセル+3)
になっており、その為、土日の部分がゼロ表示になってしまっている。
今月は30日までで31日はないから関数で""にしている。
会社で使っているものは毎月の初日に雛形として作成してあるのを使う形になっています。
そんなわけで、｢2018/11/01｣のように変えれないので、それに合った方法がありますでしょうか？
（amaryllis） 2018/11/22(木) 21:37
（マナ） 2018/11/22(木) 22:48
これをマクロで実行してはどうでしょうか。
値が見えると邪魔なら、条件付き書式で、土日は文字色を白にしてしまうとよいです。
ただ、グラフの両端は補間できません…
（マナ） 2018/11/22(木) 23:21
https://1drv.ms/u/s!AhqCef4yWVJZpzWFh_adaddHFWB8
↑
こんな感じになってます。
（amaryllis） 2018/11/23(金) 10:42
（マナ） 2018/11/23(金) 12:19
本当は、金曜日と月曜日は線で結びたいが、「グラフの両端は補間できません…」と回答されており、そこは仕方ないですので、空白でいいです。
（amaryllis） 2018/11/23(金) 13:10
目盛線を使用していなければですが
白色の長方形の図形を上に重ねるのは問題ですか
（マナ） 2018/11/23(金) 14:17
はい。図形を重ねたら、目盛線も隠れてしまい、みっともないです。
いっそのこと、１日が日曜日なら、２日からのグラフではだめでしょうか。
それなら可能です。
（マナ） 2018/11/23(金) 15:37
う〜ん
難しいですね。
うちは、決めれないです。
うちの会社のある部の課長や工長の他に現場の組長たちが見るために共有ファイル(ネットワーク)の中にそのファイルを入れている。
その職制からクレームを言われたらマズイです。
職制たちは1日〜31日の1ヵ月全て表示する考えなので、２日からのグラフではダメですね。
ということは、不可能ですね？
（amaryllis） 2018/11/23(金) 22:32
不可能かどうか知りませんが
わたしには、できません。
できても面倒なのでそこまでしません。
必要なら手作業で見た目を調整します。
それほど手間でもないと思います。
目盛線が一部とぎれてしまうのが妥協できるなら、
以下を試してください。
きまぐれ さんのデータを縦横入れ替えて動作確認しています。
土日部分のセルに仮データを入れてしまうので
条件付き書式で見えないようにしてください。
Option Explicit
Sub test()
Dim tbl As Range
Dim r As Range, c As Range
Dim c1 As Range, c2 As Range
Dim d As Double
Dim n1 As Long, n2 As Long, n As Long
Dim cht As Chart, pla As PlotArea
Dim L As Double, T As Double, W As Double, H As Double
Dim shp As Shape
Set tbl = Cells(1).CurrentRegion '★実際のデータ範囲？
For Each r In Intersect(tbl, tbl.Offset(1, 1)).Rows
Set c1 = Nothing
For Each c In r.Cells
If c.Value <> "" Then
If c1 Is Nothing Then
Set c1 = c
Else
Set c2 = c
d = (c2.Value - c1.Value) / (c2.Column - c1.Column)
Do
Set c1 = c1.Offset(, 1)
If c1.Column = c2.Column Then Exit Do
c1.Value = c1.Offset(, -1).Value + d
c1.Font.ColorIndex = 3 '★実際は条件付き書式で白文字にする
Loop
End If
End If
Next
Next
With tbl.Rows(2)
n1 = Day(.Find("*", .Cells(1), SearchDirection:=xlNext).Offset(-1).Value)
n2 = Day(.Find("*", .Cells(1), SearchDirection:=xlPrevious).Offset(-1).Value)
End With
Set cht = ActiveSheet.ChartObjects(1).Chart
On Error Resume Next
cht.Shapes("gakko1").Delete
cht.Shapes("gakko2").Delete
On Error GoTo 0
With cht.Axes(xlCategory)
n = .MaximumScale - .MinimumScale
End With
Set pla = cht.PlotArea
d = pla.InsideWidth / n
L = pla.InsideLeft + d * (n1 - 2) + 1
T = pla.InsideTop + 1
W = d - 1
H = pla.InsideHeight - 2
If n1 > 1 Then
Set shp = cht.Shapes.AddShape(msoShapeRectangle, L, T, W, H)
shp.Fill.ForeColor.RGB = vbWhite
shp.Line.Visible = msoFalse
shp.Name = "gakko1"
End If
If n > n2 Then
L = L + d * (n2 - 1)
Set shp = cht.Shapes.AddShape(msoShapeRectangle, L, T, W, H)
shp.Fill.ForeColor.RGB = vbWhite
shp.Line.Visible = msoFalse
shp.Name = "gakko2"
End If
End Sub
ところで、今までは、他の人も含めてどうしていたのでしょうか。
できない場合はどうするのでしょうか。
（マナ） 2018/11/23(金) 23:46
できました。
Sub test2()
Dim tbl As Range
Dim r As Range, c As Range
Dim c1 As Range, c2 As Range
Dim d As Double
Dim s As Long, n As Long
Dim cht As Chart
Dim ser As Series
Set tbl = Cells(1).CurrentRegion
For Each r In Intersect(tbl, tbl.Offset(1, 1)).Rows
Set c1 = Nothing
For Each c In r.Cells
If c.Value <> "" Then
If c1 Is Nothing Then
Set c1 = c
Else
Set c2 = c
d = (c2.Value - c1.Value) / (c2.Column - c1.Column)
Do
Set c1 = c1.Offset(, 1)
If c1.Column = c2.Column Then Exit Do
c1.Value = c1.Offset(, -1).Value + d
Loop
End If
End If
Next
Next
Set cht = ActiveSheet.ChartObjects(1).Chart
With cht.Axes(xlCategory)
.MaximumScale = WorksheetFunction.EoMonth(tbl.Rows(1).Cells(2).Value2, 0)
.MinimumScale = tbl.Rows(1).Cells(2).Value2
End With
With tbl.Rows(2)
s = .Find("*", .Cells(1), SearchDirection:=xlNext).Column
n = .Find("*", .Cells(1), SearchDirection:=xlPrevious).Column - s + 1
End With
For Each ser In cht.SeriesCollection
ser.XValues = Range(Split(ser.Formula, ",")(1)).EntireRow.Cells(s).Resize(, n)
ser.Values = Range(Split(ser.Formula, ",")(2)).EntireRow.Cells(s).Resize(, n)
Next
End Sub
（マナ） 2018/11/24(土) 09:32
https://1drv.ms/u/s!AhqCef4yWVJZpzfKUZeSg2B2VsVB
因みに小数点を使います。
（amaryllis） 2018/11/26(月) 15:36
（マナ） 2018/11/26(月) 18:43
2018/11/21(水) 16:20 の、きまぐれ さんように
実際のレイアウトを、セル番地がわかる形式で説明お願いします。
（マナ） 2018/11/26(月) 18:51
今度は、
'MaximumScale'メソッドに失敗しました:'Axes'オブジェクト
↑
こういうエラーがでて
.MaximumScale = WorksheetFunction.EoMonth(tbl.Rows(1).Cells(2).Value2, 0)
のところで止まりました。
.MaximumScale = WorksheetFunction.EoMonth(tbl.Rows(1).Cells(2).Value2, 0)は、
初めて見るマクロです。
（amaryllis） 2018/11/26(月) 21:20
（マナ） 2018/11/26(月) 21:46
で、EoMonth(tbl.Rows(1).Cells(2).Value2, 0)は、
どういう流れで実行されるのですか？
試しに
Set tbl Cells(1).CurrentRegion
Range(tbl, tbl).CurrentRegion.Select
にすると選択範囲されたセルが確認出来ました。
つまり、ローカル(マクロを打ち込む側の下)を見ますと、
tblのColumnの値は1でした。
今回のエラーですが、
tblのCurrentArrayは、「該当するセルが見つかりません。」となっています。
（amaryllis） 2018/11/27(火) 10:45
（マナ） 2018/11/27(火) 18:33
勉強になりました。
「'MaximumScale'メソッドに失敗しました:'Axes'オブジェクト」のエラーが直りました。
正常に実行されました。
そこで、本格的にマクロを組みたいと思ってますが、
今回の場合は、A1セルからの表でしたが、これから組むのは
例えば、D50セルからの表でマクロ実行したいし、複数の表があって、それも含めて実行させたい(表が幾つかがあって、一部に2～3つを実行させたい)。
その場合は、どれを変えればいいですか？
もう1つは、シートが3つあって、
シート1は、全てのグラフ(データテーブル付き)
シート2は、今回の表でデータが沢山入っています(1年分のデータと今月の表でグラフ表示の為のデータがある)。
シート3は、今回のマクロ実行の為の表(シート2の表から2～3つの表をここに式で引き継ぐ)
そんな感じになっています。
そこで、マクロ実行したシート3のグラフをシート1のデータテーブル付きのグラフを最前面に被せて表示させておこうと思っています。
データテーブル付きのグラフの元のデータがマクロ実行されると土日までデータとして、表示されてしまうからです。
シート3を非表示にしてシート1のままでマクロ実行させたいので、
With Sheet("シート3")
・
・
・
End With
↑
これを今回のマクロを入れたい。
その場合は、
Cells()…を
.Cells()…に変えれば良いことは知ってますが、今回のマクロの場合は、とこをどのように入れればいいですか？
（amaryllis） 2018/11/28(水) 13:29
＞Set tbl = Cells(1).CurrentRegion
↓
Set tbl = Sheets("シート3").Cells(1).CurrentRegion
シート1にグラフがあるなら
＞Set cht = ActiveSheet.ChartObjects(1).Chart
↓
＞Set cht = Sheets("シート1").ChartObjects(1).Chart
のようにします。
＞例えば、D50セルからの表でマクロ実行したい
Set tbl = Sheets("シート3").Range("D50",Sheets("シート3").Range("D50").End(xldown)).Resize(,32)
のようにするとよいです。
（マナ） 2018/11/28(水) 18:35
Sub グラフ3個で実行()
Dim tbl As Range
Dim cht As Chart
Set tbl = ここにデータ範囲を指定
Set cht = ここにグラフを指定
Call test3(tbl, cht)
Set tbl = ここにデータ範囲を変更
Set cht = ここにグラフを変更
Call rest3(tbl, cht)
'
Set tbl = ここにデータ範囲を変更
Set cht = ここにグラフを変更
Call rest3(tbl, cht)
End Sub
Private Sub test3(tbl As Range, cht As Chart)
Dim r As Range, c As Range
Dim c1 As Range, c2 As Range
Dim d As Double
Dim s As Long, n As Long
Dim ser As Series
For Each r In Intersect(tbl, tbl.Offset(1, 1)).Rows
Set c1 = Nothing
For Each c In r.Cells
If c.Value <> "" Then
If c1 Is Nothing Then
Set c1 = c
Else
Set c2 = c
d = (c2.Value - c1.Value) / (c2.Column - c1.Column)
Do
Set c1 = c1.Offset(, 1)
If c1.Column = c2.Column Then Exit Do
c1.Value = c1.Offset(, -1).Value + d
Loop
End If
End If
Next
Next
With cht.Axes(xlCategory)
.MaximumScale = WorksheetFunction.EoMonth(tbl.Rows(1).Cells(2).Value2, 0)
.MinimumScale = tbl.Rows(1).Cells(2).Value2
End With
With tbl.Rows(2)
s = .Find("*", .Cells(1), SearchDirection:=xlNext).Column
n = .Find("*", .Cells(1), SearchDirection:=xlPrevious).Column - s + 1
End With
For Each ser In cht.SeriesCollection
ser.XValues = Range(Split(ser.Formula, ",")(1)).EntireRow.Cells(s).Resize(, n)
ser.Values = Range(Split(ser.Formula, ",")(2)).EntireRow.Cells(s).Resize(, n)
Next
End Sub
（マナ） 2018/11/28(水) 18:43
こういう方法で出来るんですね。
明日、やってみます。
その前に質問ですが、
＞3個のグラフで同じことをしたいなら
マナさんが回答して下さった中に
Call test3(tbl, cht)
・
・
Call rest3(tbl, cht)
・
・
Call rest3(tbl, cht)
↑
｢test3｣と｢rest3｣があって｢test3｣3つではなく、｢test3｣と｢rest3｣でよろしいでしょうか
もう1つ、私が｢2～3つの表を…｣と言いましたが、
2つで実行させる場合は、
Set tbl = ここにデータ範囲を指定
Set cht = ここにグラフを指定
Call test3(tbl, cht)
↑
この様なマクロを2つでよろしいかな？
その場合、他のマクロの変更点はないですか？
（amaryllis） 2018/11/28(水) 20:56
タイプミスです。
＞2つで実行させる場合は、
それでOKです。
（マナ） 2018/11/28(水) 21:53
はい。
Callしているマクロ（Sub グラフ3個で実行）と
同じ場所にある必要があります。
（マナ） 2018/11/28(水) 22:30
まず、
＞Set tbl = Sheets("シート3").Cells(1).CurrentRegion
＞Set cht = Sheets("シート1").ChartObjects(1).Chart
↑
これだけを変えて、実行してみる方法で、やったら要望通りに出来ました。
次に
Set tbl = ここにデータ範囲を指定
Set cht = ここにグラフを指定
Call test3(tbl, cht)
これと、
Private Sub test3(tbl As Range, cht As Chart)〜End Sub
に変えて実行してみました。
そうしたら、途中で、「'MaximumScale'メソッドに失敗しました:'Axes'オブジェクト」の
エラーが出て、
.MaximumScale = WorksheetFunction.EoMonth(tbl.Rows(1).Cells(2).Value2, 0)
というところで止まりました。
1つたげのグラフで実行させるマクロでは、問題なく出来ましたが、
Private Sub test3(tbl As Range, cht As Chart)〜End Subにするとなぜかが分かりませんが、止まってしまったので、
仕方なく、1つのグラフで実行するマクロを3作り、
Sub グラフをまとめて実行()
Call 1つめのグラフ
Call 2つめのグラフ
Call 3つめのグラフ
End Sub
にしたら、全て実行完了になりました。
実にいうと気になっていたのは、
With tbl.Rows(2)
s = .Find("*", .Cells(1), SearchDirection:=xlNext).Column
n = .Find("*", .Cells(1), SearchDirection:=xlPrevious).Column - s + 1
End With
For Each ser In cht.SeriesCollection
ser.XValues = Range(Split(ser.Formula, ",")(1)).EntireRow.Cells(s).Resize(, n)
ser.Values = Range(Split(ser.Formula, ",")(2)).EntireRow.Cells(s).Resize(, n)
Next
↑
変化がなく、マクロ実行が完了した様に感じるんです。
一体、どんなマクロですか？
（amaryllis） 2018/11/29(木) 21:06
これはどうでしたか？
Sub グラフ1個で実行()
Dim tbl As Range
Dim cht As Chart
Set tbl = Sheets("シート3").Cells(1).CurrentRegion
Set cht = Sheets("シート1").ChartObjects(1).Chart
Call test3(tbl, cht)
End Sub
（マナ） 2018/11/29(木) 22:16
A B C D E F G H I J K 1 11/1 11/2 11/3 11/4 11/5 11/6 11/7 11/8 11/9 11/10 2 系列１ 13 14 12 21 17 3 系列２ 20 24 21 22 13
マクロでやっていることは
１）途中の空白セルは、仮データで埋める ２）グラフの軸は、11/1から11/30にする ３）データ範囲は、11/2から11/9までにする
＞一体、どんなマクロですか？
３）の部分に相当します。
11/2は、2行目で左側から、検索（Find)して、最初のデータセルの列にあります。
11/9は、2行目で右側から、検索（Find)して、最初のデータセルの列にあります。
これを、マクロにしたのが
＞s = .Find("*", .Cells(1), SearchDirection:=xlNext).Column
＞n = .Find("*", .Cells(1), SearchDirection:=xlPrevious).Column - s + 1
sは、11/2の列を求めています。 nは、11/9の列を求めて、sから何列目かを計算しています。 上記例では、s=3、n=8になります。 で、グラフのデータ範囲は、C列から8セル分（J列まで）となります。
そして、求めた、sとnを使って、系列データ範囲を設定しているのが
以下の部分です。
＞ser.XValues = Range(Split(ser.Formula, ",")(1)).EntireRow.Cells(s).Resize(, n) ＞ser.Values = Range(Split(ser.Formula, ",")(2)).EntireRow.Cells(s).Resize(, n)
なぜ、これでできるのかまでは、複雑すぎて説明できません。
（マナ） 2018/11/29(木) 23:31
＞グラフ1個で実行
↑
そこは、まだ、チェックしてない。
明日、やってみます。
分かったら、返事します。
With tbl.Rows(2)～End
For Each ser In cht.SeriesCollection～Nextについての説明をありがとうございます。
（amaryllis） 2018/11/30(金) 05:27
Sub グラフ1個で実行()でやったら、やはりエラーが出ました。 「'MaximumScale'メソッドに失敗しました:'Axes'オブジェクト」です。 （amaryllis） 2018/11/30(金) 08:15
＞Set tbl = Sheets("シート3").Cells(1).CurrentRegion
↓
Set tbl = Sheets("シート3").Range("〇〇:〇〇")
＞Set cht = Sheets("シート1").ChartObjects(1).Chart
↓
Set cht = Sheets("シート1").ChartObjects("実際の名前").Chart
（マナ） 2018/11/30(金) 18:27
＞Set cht = Sheets("シート1").ChartObjects("実際の名前").Chart
↓
.ChartObjects(1).Chartを.ChartObjects("実際の名前").Chartに変えてあります。
↑
私が、今朝にエラーが出ますと言う前に変えています。
Set tbl = Sheets("シート3").Range("〇〇:〇〇")は、来週の月曜日にやってみます。
（amaryllis） 2018/11/30(金) 23:35
範囲の１行目は日付ですよ。
で、２個めのセルが、11月1日とか、12月1日のはず。
（マナ） 2018/12/02(日) 23:20
例えば、表がD50セルからの場合は、
Set tbl = Sheets("シート3").Range("D50:〇〇")
↑
○○の部分はどのセル？
「11月1日とか、12月1日」と、言われたならば、
Range("D50:E50")で良い?
これだと、「式が一致しません」と出ます。
（amaryllis） 2018/12/03(月) 13:09
手作業で、グラフを作った時に選んだデータ範囲と同じです。
（マナ） 2018/12/03(月) 20:43
＞手作業で、グラフを作った時に選んだデータ範囲と同じです。
その通りで、やってみましたが、相変わらず
「'MaximumScale'メソッドに失敗しました:'Axes'オブジェクト」になっています。
方法がなければ、
Sub グラフをまとめて実行()
Call 1つめのグラフ
Call 2つめのグラフ
Call 3つめのグラフ
End Sub
↑
この方法でいきます。
今まで、色々と回答して下さってありがとうございました。
（amaryllis） 2018/12/05(水) 13:08
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.