[[20210622160511]]　『VBAでのデータラベル表示について』（sky）　ページの最後に飛ぶ

『VBAでのデータラベル表示について』（sky）

お力を貸してください。
現在、下記の表（本来の表だと行数が多いのでイメージ）からVBAで複数のグラフを作ろうとしています。
　

	[A]	|[B]	|[C]	|[D]	|[E]	|[F]
[ 1]（大内訳1）	|	|	|	|	|10000
[ 2]		|Aさん	|3000	|0.3	|〇100%	|
[ 3]		|Bさん	|2000	|0.2	|	|
[ 4]		|Cさん	|1000	|0.1	|	|
[ 5]		|	|0	|	|	|
――――――――――――――――――――――――――――――――
[ 6]		|Dさん	|3000	|0.3	|	|
[ 7]		|Eさん	|1000	|0.1	|	|
[ 8]		|	|0	|	|	|
――――――――――――――――――――――――――――――――
[ 9]（大内訳2）	|	|	|	|	|
[10]		|Fさん	|
　・
　・
　・
　
A・B・FとC・D・Eはそれぞれ別シートからコピーしたもので、C行には計算式（C3なら=F2*D3）、EにはD列の大内訳1つごとにF行と比較して数値が100%一致するかをif関数（=IF(SUM(D3:D8)=1,"〇 "&SUM(D3:D8)*100&"%","× "&SUM(D3:D8)*100&"%")）で見ています。
またA・B・Fをコピーしたシートは本来2～8行目の部分は存在せず、コピーしてから空白行を行数決め打ちで増やしています。
　
ここから現在、大内訳の全体（1～8行目）を円グラフ、大内訳を更に区分けしたもの（小内訳A（1～5列目）と小内訳B（6～8行目））を100%積み上げ横棒で作成しています。
大内訳は毎回変わりますが大体10個程度と決め打ちしました。
　
ここからが問題になりますが、小内訳の中の人数も毎回変動があり、どうしても空白行ができてしまいます。
そして空白行ができてしまうと分類名のデータラベルがうまく出てくれません。
空白行を削除しようとしても、グラフの範囲自体も決め打ちで作成してしまったためグラフ範囲がずれてしまいます。
このVBAを作った後、グラフ作成に慣れていない人に渡す必要があるので手動で直すと言った手が使えません。
グラフに分類名のデータラベルを表示させるにはどうすればよいでしょうか。
　
　
　
以下、作成したvbaになります。
上のイメージ表と範囲行が違いますがご了承ください。

Sub グラフ作成()

      Worksheets("グラフ用").Activate

'既にあるグラフを削除

    Dim i As Long
    With ActiveSheet
        For i = .ChartObjects.Count To 1 Step -1
            .ChartObjects(i).Delete
        Next i
    End With

'グラフ作成1---------------------------------------

  With ActiveSheet.Shapes.AddChart.Chart
    'ドーナツグラフ追加_凡例はグラフの上
    .ChartType = xlDoughnut
    .SetSourceData Range("b3:c19")
    .SetElement (msoElementDataLabelCallout)
    .HasLegend = False

  End With

  With ActiveSheet.Shapes.AddChart.Chart
    '100% 積み上げ横棒グラフ追加
    .ChartType = xlBarStacked100
    .SetSourceData Range("b3:c12")
    .ChartColor = 14

  Select Case .PlotBy
  Case xlRows
    .PlotBy = xlColumns
  Case xlColumns
    .PlotBy = xlRows
End Select

  End With

  With ActiveSheet.Shapes.AddChart.Chart
    '100% 積み上げ横棒グラフ追加
    .ChartType = xlBarStacked100
    .SetSourceData Range("b13:c19")
    .ChartColor = 17

  Select Case .PlotBy
  Case xlRows
    .PlotBy = xlColumns
  Case xlColumns
    .PlotBy = xlRows
End Select

  End With

'グラフ作成2---------------------------------------

  With ActiveSheet.Shapes.AddChart.Chart
    'ドーナツグラフ追加
    .ChartType = xlDoughnut
    .SetSourceData Range("b21:c37")
    .SetElement (msoElementDataLabelCallout)
    .HasLegend = False

  End With

  With ActiveSheet.Shapes.AddChart.Chart
    '100% 積み上げ横棒グラフ追加
    .ChartType = xlBarStacked100
    .SetSourceData Range("b21:c30")
    .ChartColor = 14

  Select Case .PlotBy
  Case xlRows
    .PlotBy = xlColumns
  Case xlColumns
    .PlotBy = xlRows
End Select

  End With

  With ActiveSheet.Shapes.AddChart.Chart
    '100% 積み上げ横棒グラフ追加
    .ChartType = xlBarStacked100
    .SetSourceData Range("b31:c37")
    .ChartColor = 17

  Select Case .PlotBy
  Case xlRows
    .PlotBy = xlColumns
  Case xlColumns
    .PlotBy = xlRows
End Select

  End With

（以下繰り返しなので割愛）

＜使用 Excel：Excel2016、使用 OS：Windows10 ＞

追記して、現在の運用方法として

1　別のマクロでA・B・FとC・D・Eにそれぞれ別のシートから列を貼り付けて3行目～19行目（イメージで言う2行目～8行目）の空白行を増やす。以降の大内訳も同様に17行増やす
2　3行目～19行目のB列に名前、D列に数値の比率を手入力
3　今回提示したマクロを使ってグラフを3種類作る

と言った動きを考えています。
この時にBに空欄セルがある状態でグラフを出力するとB列の名前が出ず、データソースでも項目軸ラベルがなしになっています。
Bが全部埋まった状態だと項目軸ラベルでBが選択されるので、空欄があってもこの状態を目指したいです。
（sky） 2021/06/23(水) 08:16

別の質問掲示板にも質問されているようですが、こちらでコメントします。
　
問題の所在がよく理解できません。
空白行があることが問題を引き起こしているのですか？
だったら、空白行を検索して、注意喚起すればそれで完了なんですか？
　
何が実現できたらゴールなんですか？
それを他人が分かるように説明してもらえますか？
　
以下は質問を拝見した、とりとめのない感想です。
　
提示されたコードでは6つのグラフが描画されますね。
そのうち、次のセル範囲を指定した３つがワンセットになるんですかね。
・"b3:c19"
・"b3:c12"
・"b13:c19"
　
それで、空白行を無くすようにしたとして、上記の3つのセル範囲は、
どのような理屈で決まるのですか？
つまり、12行目と13行目になにか境界があるようですが、
その境界は何によって判断できるんですか？
　
そのあたりをきちんと説明されないので、問題がぼやけている印象です。

（γ） 2021/06/28(月) 11:06

＞γさま
他サイト（http://www.vbalab.sakura.ne.jp/vbaqa/c-board.cgi?cmd=one;no=81849;id=excel）ともども閲覧いただきありがとうございます。
ゴールとしましては「B列に空白セルがあっても項目軸のラベル名がそれぞれのグラフに表示されるようにしたい」となります。
またその際、できる限り空白行は消さずに残したいと思います。

現在のVBAでは例えば「b3：c19」範囲で「b3：b5」「b13：b19」に名称がある状態でグラフを作ると"b3:c19"の円グラフおよび"b13:c19"の100%横グラフではグラフの項目ラベルに値が入り名称がグラフの上で表示されますが、"b3:c12"の100%横グラフでは項目ラベルが未選択の状態となり名称が表示されません。
これをb列の名称に空白セルが発生しても項目ラベルが表示されるようにしたい（上の例だと"b3:c12"の100%横グラフも他2つのようにラベル名が表示されて欲しい）と考えています。

グラフのワンセットとしては「"b3:c12"（小内訳A）＋"b13:c19"（小内訳B）＝"b3:c19"（大内訳）」となります。
それぞれのセル範囲は『最大でもこれくらいの行があれば足りるだろう』と言う大まかな決め打ちで決定しており、それをそのままグラフ範囲に適用させています。
12行目と13行目の境界と言われると説明が難しいのですが『小内訳Aは最大でも10人程度、小内訳Bは最大でも7人程度、その上で全員分のグラフも必要なので大内訳は合計17人分になる』と言った説明で伝わるでしょうか。

分かりにくい説明で申し訳ありません。他に不明点があれば指摘してもらえると幸いです。
（sky） 2021/06/28(月) 12:02

 横棒グラフのデータラベルの追加部分は下記のような感じじゃないですか？
     Dim sr As Series
     With ActiveSheet.Shapes.AddChart.Chart
         '100% 積み上げ横棒グラフ追加
         .ChartType = xlBarStacked100
         .SetSourceData Range("b3:c12")
         .ChartColor = 14
         Select Case .PlotBy
         Case xlRows
             .PlotBy = xlColumns
         Case xlColumns
             .PlotBy = xlRows
         End Select

         For Each sr In .FullSeriesCollection
             sr.ApplyDataLabels
             With sr.DataLabels
                 .ShowSeriesName = True
                 .ShowValue = False
             End With
         Next
     End With

 ほかのところは特に問題ではないようなので、いらぬ心配だったようです。
 私はこれで失礼します。
（γ） 2021/06/28(月) 13:36

＞γさま

再度返答いただきありがとうございます。
それぞれのグラフに For Each～部分を追加して動かしてみましたが、項目軸ラベルではなく系列名が表示されました。
しかしラベルの構文を入れ込む方法はとても参考になりました。ここから色々と弄って試してみようと思います。

返答いただきありがとうございました。
（sky） 2021/06/29(火) 09:25

コメント返信：

[ 一覧(最新更新順) ]

[[20210622160511]] 『VBAでのデータラベル表示について』（sky） ページの最後に飛ぶ

[[20210622160511]]　『VBAでのデータラベル表示について』（sky）　ページの最後に飛ぶ