[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『マクロで積み上げグラフ』(らら)
下記のような表で棒グラフを作成しました。 (横に並べられなかったので、G列(空白列)から折り返して表示しております。) この表ですが、E列の粗利額をH〜P列の粗利率が該当するセルのところへ入力してあります。 この表をもとに、粗利率別に売上高をあらわす品番の積み上げ棒グラフを作成したところ 300行ぐらいデータがあり、系列をひとつずつ指定するのがとにかく大変です。 マクロで何とかなりますでしょうか?
2 B C D E F G 3 ユーザー 品番 売上高 粗利額 粗利率 4 りんご 45 8 17.0% 5 ばなな 1 800 20.0% 6 いちご -0 100 30.0% 7 めろん -311 200 40.0%
2 H I J K L M N O P 3 0%〜5% 5%〜10% 10%〜15% 15%〜20% 20%〜25% 25%〜35% 35%〜45% 45%〜55% 55%以上 4 8 5 800 6 100 7 200
項目軸(X) H〜Pの粗利率(0%〜5%、5%〜10%・・・) 数値軸(Y) 売上高(-200〜1200)
元データ (たとえばりんご) 系列 りんご 名前 =粗利額!$C$4 値 =粗利額!$H$4:$P$4 項目軸ラベルに使用 =粗利額!$H$3:$P$3
マクロは初心者でほとんどわかりませんが、 どなたかいい方法があればご教授よろしくおねがいします。
C3:C7 セルを選択して Ctrlキーを押したままH3:P7を選択すると 二つのセル範囲が選択出来ます。
この状態で積み上げ棒グラフを作ると ご説明の様に成りそうに思いますが。。。
ただ、H3:P7の範囲に有るのは粗利額ですよね?
(HANA)
早速ありがとうございます。
>ただ、H3:P7の範囲に有るのは粗利額ですよね? その通りです。 ここは関数で、それぞれの粗利額が表示されています。 CtrlキーでC3:C300 セルを選択、Ctrlキーを押したままH3:P300を選択すると "使用可能なデータ系列の数は1グラフあたり最大255個となります。" というメッセージがでました。 ここの系列タブで下記設定が必要なのですが
元データ (たとえばりんご) 系列 りんご 名前 =粗利額!$C$4 値 =粗利額!$H$4:$P$4 項目軸ラベルに使用 =粗利額!$H$3:$P$3
どうすればよろしいでしょうか?
どうするも何も、255個までしか設定できないのでは。。。? それとも、一つずつ追加して行ったら 256個以上設定出来るのですか?
或いは 「設定出来る所までで良いから、設定したい」 って事なんでしょうか。。。?
4行目からデータでしたら、258行目のセルまでグラフに出来る事になると思いますが。
(HANA)
データの中には同じ品番のものもありますが、 それを合算しても270件ほどあります。
ひとつずつ設定していけばできました。 ちなみにOffice2003です
>ひとつずつ設定していけばできました。 済みませんが、何処で設定していくのですか?
グラフウィザード - 2/4 - グラフ元のデータ の、 [データ範囲]タブで、255件目の範囲まで選択。 その後、[系列]タブで、256件目を追加するべく [追加(A)] を押すと 「使用可能なデータ系列の数は、1グラフあたり最大255個です」 と表示されて設定出来ません。
バージョンの違いなんでしょうか。。。 こちらは 2002で試して居ますが、ららさんは何をお使いですか? って書いてたら追記して下さってましたね。 と言う事で、2003のエクセルでも試して見ましたが同じメッセージが表示され 追加出来ませんでした。。。
詳しい手順を教えてもらえないですか?
また、実質問題として 270個の系列を持つグラフを描いてそれらは読みとれるのですか?
(HANA)
早速のご対応大変助かります。
まずHANAさんに謝らなければならないのですが、データの数はこちらの間違いで250行でした。 255を超えると、ご指摘どおり作業ができないと思います。
データは255以内に収めることを条件に、教えて頂いたCtrlキーを押しながら グラフウィザードを起動してみました。
C3:C250、H3:P250を選択 グラフウィザード1/4・・・縦棒ー積み上げ縦棒を選択 グラフウィザード2/4・・・行を選択 系列タブより・・・系列は品番名(C4)、名前=Sheet1!$C$4、値=Sheet1!$H$4:$P$4、項目軸ラベルに使用=Sheet1$H$3:$P$3
このグラフウィザード2/4の「値」と「項目軸ラベルに使用」が上記のように デフォルトになっておらず、せっせと手で修正いたしました。 毎月、このグラフを作成しなければならず これをひとつひとつ設定せずに簡単にできる方法はあるのでしょうか? 老眼の年寄りには酷な作業です・・・(らら)
えっと。。。 >上記のようにデフォルトになっておらず どうなっていますか?
グラフウィザード2/4 の [データ範囲]タブ の方に 系列 ●行(R) ○列(L) の切り替えが出来る所がありますよね。
切り替えると、上半分に表示されているイメージが変わると思います。
切り替えて、確認してみてもらえますか?
(HANA)
夜分ありがとうございます わたしもいろいろ思考錯誤しておりました。
■[データ範囲]タブ 行の場合
わたしが修正する前の「値」と「項目軸ラベルに使用」部分ですが 系列は品番名(りんご)、名前=Sheet1!$C$4、 値=(Sheet1!$D$4:$H$4,Sheet1!$M$4:$P$4)、項目軸ラベルに使用=(Sheet1!$D$3:$H$8,Sheet1!$M$3:$P$8)
■[データ範囲]タブ 列の場合
HANAさんのおっしゃるとおり、私も行と列を切り替えて試してみたのですが 列を選択すると、下の項目軸が粗利率ではなく品番になってしまいます。 無視してそのまま[系列タブ]へすすむと・・・
系列は、売上 45 1 -0 -311、 粗利額 8 800 100 200・・・
系列の <売上 45 1 -0 -311>を選択した場合 名前=Sheet1!$D$3:$D$8 値=Sheet1!$D$9:$D$250、項目軸ラベルに使用=Sheet1!$C$9:$C$250
とチンプンカンプン?な範囲が指定されてしまいました。
(らら)
>値=(Sheet1!$D$4:$H$4,Sheet1!$M$4:$P$4)、項目軸ラベルに使用=(Sheet1!$D$3:$H$8,Sheet1!$M$3:$P$8) これだと、 グラフにしないデータ範囲のD:G列がデータ範囲に設定されていて グラフにしたいデータ範囲のI:L列がデータ範囲に設定されていない 事に成りますよね。。。?
すると >データは255以内に収めることを条件に、教えて頂いたCtrlキーを押しながら >グラフウィザードを起動してみました。 > >C3:C250、H3:P250を選択 >グラフウィザード1/4・・・縦棒ー積み上げ縦棒を選択 >グラフウィザード2/4・・・行を選択
ウィザードを実行する前の選択時に、実際はこの様には成って居らず D:G列が含まれていて、I:L列が含まれていなかった のでは無いかと思いますが、どうでしょう?
広い範囲を選択するのも大変なので、10行目くらいまでの範囲で試してみて下さい。 C3:C10 と H3:P10 の範囲をコントロールキーを押しながら選択し グラフウィザードを起動。積み上げ棒グラフを選択。 ウィザード2/4 の [データ範囲]タブ のデータ範囲(D)には =Sheet1!$C$3:$C$10,Sheet1!$H$3:$P$10 と表示されると思いますが、どの様に成っていますか?
(HANA)
>これだと、 グラフにしないデータ範囲のD:G列がデータ範囲に設定されていて グラフにしたいデータ範囲のI:L列がデータ範囲に設定されていない 事に成りますよね。。。?
はい、データ範囲のD:H、M:P列が設定されていて、I:L列が設定されていません。 範囲を選択してからウィザードを起動していますが おっしゃるとおり、実際はウィザードの2/4[データ範囲]では、=Sheet1!$C$3:$H$250,Sheet1!$M$3:$P$250 となっていました。 気がつかなかったので、ビックリしてます。
検証用に表をつくりなおしました。(内容の数字はデタラメです) そうすると、ウィザード2/4 データ範囲(D)には正しい範囲が指定されていました。 =Sheet1!$C$3:$C$10,Sheet1!$H$3:$P$10 です。
C D E F H I J K L M N O P 3 品番 売上 粗利額 粗利率 0%〜5% 5%〜10% 10%〜15% 15%〜20% 20%〜25% 25%〜35% 35%〜45% 45%〜55% 55%以上 4 りんご 45 8 17.0% 7 5 バナナ 1 5 18.0% 5 6 みかん 3 10 30.0% 10 7 メロン 27 5 9.0% 5 8 いちご 0 15 34.0% 15 9 すいか 0 12 62.0% 12 10 トマト 8 6 4.0% 6
[系列タブ]をみてみたのですが、すいかとトマトの2つしか系列名にでてませんでした。 (・・・これもなぜなのか理由がわかりません)
系列 すいか (すいかとトマトしかない) 名前 =粗利額!$C$9 値 =粗利額!$H$9:$P$9 項目軸ラベルに使用 =粗利額!$H$3:$P$8 (つねに=粗利額!$H$3:$P$3が正解)
という具合です。 (らら)
変ですねぇ。なんで項目軸ラベルを5行分もとってしまうのですかね。。。 結合セルが有ったり、3行目と同じ背景色に成ってたり なんて事も無いですか?
試しに、 D:F列が無い状態にしてやってみるとどうですか?(範囲のデータを前詰にする) 3:4行目の範囲(項目ラベルとデータの1行分のみ)を選択した状態からやってみるとどうですか? 3行目のみ背景色を設定してみるとどうですか?
ちなみに >E列の粗利額をH〜P列の粗利率が該当するセルのところへ入力してあります。 これは、数式が入っていますよね? この数式を教えてもらっても良いですか?
こちらでもサンプルデータを作って確認しているのですが、同じ現象が出ないんですよね。。。 なるべく ららさんが作ったシートと同じ状況にしたいので データや書式設定等に関しても詳しく教えてもらえればと思います。
もちろん、 >検証用に表をつくりなおしました。(内容の数字はデタラメです) の様な表を作って説明してもらえれば良いので。。。
(HANA)
いろいろお手数おかけしてます。 データ範囲で、行と列を切り替えたりしてやってみましたが、 範囲が全部正解にはまることができませんでした。
> 結合セルが有ったり、3行目と同じ背景色に成ってたり なんて事も無いですか? 結合セルや色は使用しておらず、とってもシンプルな表です(^^)
>D:F列が無い状態にしてやってみるとどうですか?(範囲のデータを前詰にする) D:G列まで隙間がないように、数式の部分も値のみにしてやってみました。 結果は、あまりうまくいかなかったです。
データ範囲:=粗利額!$C$3:$L$10 系列:トマト(一番下) 名前:=粗利額!$C$10:$K$10 値:=粗利額!$L$10 項目軸ラベル=粗利額!$L$3:$L$9
> 3:4行目の範囲(項目ラベルとデータの1行分のみ)を選択した状態からやってみるとどうですか?
上記、D:G列を削除した表のままやってみました。 系列ですが、品番のあとに表のなかの粗利額がスペースをあけて入っています。 名前も最後L列まで入ってませんし、そのかわり項目軸ラベルがL3です
データ範囲:=粗利額!$C$3:$L$4 系列・・・りんご 8 名前:=粗利額!$C$4:$K$4 値:=粗利額!$L$4 項目軸ラベルに使用:=粗利額!$L$3
>3行目のみ背景色を設定してみるとどうですか? 項目の3行目だけ色をつけましたが、上記の2パターンとも同じ内容で結果は同じでした。
>E列の粗利額をH〜P列の粗利率が該当するセルのところへ入力してあります。 これは、数式が入っていますよね? はい、こんな式です。
4行目の場合(検証用ではない正式な表の列並びです) 0%〜5%(H4セル)・・・ =IF(F4<5%,E4,"") 5%〜10%(I4セル)・・・=IF(F4>5%,IF(F4<10%,E4,""),"") 10%〜15%(J4セル)・・・=IF(F4>10%,IF(F4<15%,E4,""),"") 15%〜20%(K4セル)・・・=IF($F4>15%,IF($F4<20%,$E4,""),"") 20%〜25%(L4セル)・・・=IF($F4>20%,IF($F4<25%,$E4,""),"") 25%〜35%(M4セル)・・・=IF($F4>25%,IF($F4<35%,$E4,""),"") 35%〜45%(N4セル)・・・=IF($F4>35%,IF($F4<45%,$E4,""),"") 45%〜55%(O4セル)・・・=IF($F4>45%,IF($F4<55%,$E4,""),"") 55%以上(P4セル) ・・・=IF($F4>56%,$E4,"")
>こちらでもサンプルデータを作って確認しているのですが、同じ現象が出ないんですよね。。。 そうなんですか・・・うーん、困りました。 ひとつひとつ手でやるのだけはなんとか…おねがいします。
教えてもらった数式を入れたら、同じ様に成りました!!
で。。。消極的な対策として「""」を返すのではなく「0」を返す事にして 表示形式・条件付き書式等で、「0」を非表示にするのでは駄目でしょうか。。。?
或いは、 一旦4行目を挿入して 3行目からの範囲を選択してグラフを作成。 すると、自動で3行目だけがタイトルとして認識される様に成るので 4行目の系列を削除。 でも出来そうです。
が・・・どうでしょう?
(HANA)
検証くださってありがとうございます。
関数がグラフを作るのにジャマ?するなんてことがあるんですね。 「""」が悪いんでしょうか? そこはまったく疑ってなかったので、とても驚いております。
>消極的な対策として「""」を返すのではなく「0」を返す事にして
見事にできました!
>一旦4行目を挿入して 3行目からの範囲を選択してグラフを作成。
これがうまくできません。 空白の4行目を挿入→C3:C10、H3:P10を選択→グラフウィザードを起動 の手順でよかったでしょうか?
(らら)
>「""」が悪いんでしょうか? これは、セルに何も表示されないのであいまいに認識してしまいますが 【文字】なんです。 グラフのデータに指定するものって【値】ですよね? で、項目に指定するものが【文字】なので 『ここは、データ行じゃなくて項目行なんだな』と エクセルが考えたのだと思います。
>空白の4行目を挿入→C3:C10、H3:P10を選択→グラフウィザードを起動 C4,H4:P4が空セルの状態(数式も入っていない。書式も無し)ですよね? こちらではこれでうまく行きましたが。。。
こうなると、バージョンの違いかもしれません。 空行を入れたパターンは 2007 でやったので。
C4に適当に項目を入れてみるとどうでしょう? それだけで、系列として認識してくれると良いですが。。。
それでも駄目な場合、H4:P4に「0」を入れてみるとどうでしょう?
(HANA)
グラフの表をつくるときは、こういうことも注意しなければならないのですね 大変勉強になりました 2007でも同じ現象がおきるのでしょうか。
>C4,H4:P4が空セルの状態(数式も入っていない。書式も無し)ですよね? この状態でもう一度やってみました。
C4セル・・・マンゴ と入力 H4:P4・・・0を入力 Ctrlキーを押しながらC3:C10、H3:P10を範囲選択 グラフウィザードを起動 [データ範囲]の行列がグレーで選択できない状態でした。 一度グラフウィザードをキャンセルして、範囲選択を解除した状態で もう一度グラフウィザードを起動 [データ範囲] 行を指定してから、 Ctrlキーを押しながらC3:C10、H3:P10を範囲選択
[系列]タブの系列がやはり品番だけ選択されず、[名前]を選択しなおさないとダメなようです。 名前・・・'='粗利額'!$C$5:$I$5 値・・・'='粗利額'!$H$4:$P$4 項目軸ラベルに使用・・・='粗利額'!$H$3:$P$3
こちらの方法は断念ですかね。 「""」を"0"にしての方法で検討してみます。
マクロの記録でマクロ作成してみました。 実際使うかどうかは別として、こちらも教えて頂いていいでしょうか? グラフエリアのサイズを大きくしたいのですが、うまくできませんでした。 HANAさんのようなプロの方から見れば、めちゃめちゃなコードでお恥ずかしいのですが…
Sub Graph_Test() Range("C3:C250,H3:P250").Select Charts.Add ActiveChart.ChartType = xlColumnStacked ActiveChart.SetSourceData Source:=Sheets("マクロテスト").Range("C3:C250,H3:P250"), _ PlotBy:=xlRows ActiveChart.Location Where:=xlLocationAsObject, Name:="マクロテスト" With ActiveChart .HasTitle = True .ChartTitle.Characters.Text = "グラフタイトル" .Axes(xlCategory, xlPrimary).HasTitle = True .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "粗利率" .Axes(xlValue, xlPrimary).HasTitle = True .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "(万円)" End With ActiveChart.HasLegend = False ActiveChart.Axes(xlCategory).Select With Selection.Border .Weight = xlHairline .LineStyle = xlAutomatic End With With Selection .MajorTickMark = xlInside .MinorTickMark = xlNone .TickLabelPosition = xlLow End With ActiveChart.Axes(xlValue).AxisTitle.Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .ReadingOrder = xlContext .Orientation = xlHorizontal End With Selection.Left = 38 Selection.Top = 23 ActiveChart.Axes(xlValue).Select ActiveChart.PlotArea.Select Selection.Left = 10 Selection.Top = 49 ActiveChart.PlotArea.Select With Selection.Border .ColorIndex = 16 .Weight = xlThin .LineStyle = xlContinuous End With Selection.Fill.TwoColorGradient Style:=msoGradientHorizontal, Variant:=2 With Selection .Fill.Visible = True .Fill.ForeColor.SchemeColor = 15 .Fill.BackColor.SchemeColor = 2 End With MsgBox "グラフ作成できました" End Sub
いまこれを実行して気がついたのですが、 マクロですと、関数を変更しなくてもグラフができました。 これまたなぜかわかりません…
2002でやってみました。 数式で「""」を返していた場合、上手く行きませんでした。 空行を挿入すると、上手く行きました。 空行無しで「0」を返す様にした場合、上手く行きました。 取り敢えず、結果報告です。(バージョンは違いますが。。。)
あと >マクロですと、関数を変更しなくてもグラフができました。 って事ですが、こちら(2002)では駄目でした。
>グラフエリアのサイズを大きくしたいのですが に関しては、マクロは得意ではないので、検索してみました。 [[20061108125704]] 『マクロでグラフを移動&サイズ変更したい』(ちろりん)
何処へ表示ですかね。。。 適当にやっておくので、良い様に変更してみて下さい。
'------ Sub コメントを入れてみた() Charts.Add ActiveChart.ChartType = xlColumnStacked ActiveChart.SetSourceData Source:= _ Sheets("マクロテスト").Range("C3:C250,H3:P250"), _ PlotBy:=xlRows ActiveChart.Location Where:=xlLocationAsObject, Name:="マクロテスト" With ActiveChart '−−−グラフタイトル−−− .HasTitle = True .ChartTitle.Characters.Text = "グラフタイトル"
'−−−X/項目軸−−− .Axes(xlCategory, xlPrimary).HasTitle = True .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "粗利率"
'−−−Y/数値軸−−− .Axes(xlValue, xlPrimary).HasTitle = True .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "(万円)"
'−−−凡例−−− .HasLegend = False
'===項目軸=== With .Axes(xlCategory) '−−−軸パターン−−− With .Border .Weight = xlHairline .LineStyle = xlAutomatic End With '−−−目盛線・補助目盛線−−− .MajorTickMark = xlInside .MinorTickMark = xlNone '−−−目盛ラベルの位置−−− .TickLabelPosition = xlLow End With
'===数値軸ラベル=== With .Axes(xlValue).AxisTitle '−−−ラベルの書式設定−−− .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .ReadingOrder = xlContext .Orientation = xlHorizontal '−−−ラベルの位置−−− .Left = 38 .Top = 23 End With
'===プロットエリア=== With .PlotArea '−−−輪郭パターン−−− With .Border .ColorIndex = 16 .Weight = xlThin .LineStyle = xlContinuous End With '−−−領域パターン−− With .Fill .TwoColorGradient Style:=msoGradientHorizontal, Variant:=2 .Visible = True .ForeColor.SchemeColor = 15 .BackColor.SchemeColor = 2 End With '−−−エリアの位置−−− .Left = 10 .Top = 49 End With
'===グラフの位置とサイズ=== With .Parent '−−−位置−−− .Top = Range("H3").Top .Left = Range("H3").Left '−−−サイズ−−− .Height = 300 .Width = 400 End With End With MsgBox "グラフ作成できました" End Sub '------
画面で一生懸命グラフを描いているのが見えるので Application.ScreenUpdating = False を入れて、画面の更新を停止しておいても良いかもしれません。
ちなみに、H列以降の表は グラフを描く為に作成しているのでしょうか? 横方向へ展開しなくても、区分を表示する列を作って ピボットグラフを使ってみても良いかもしれません。
(HANA)
遅くなりましたが、マクロありがとうございました。 大変わかりやすく書いて頂いて、編集しやすいです。
大きさですが、これは一体なんの単位なのでしょうか。 適当に入力をしていって、700で落ち着きました(^^) .Height = 300 .Width = 400
ピポットのご提案も助かります。 ご指摘の通り、グラフ作成のための表なのですが、 区分というのはたとえばどのようなものなのでしょうか? 作成してみようと思っています
>大きさですが、これは一体なんの単位なのでしょうか。 何の単位でしょうね。。。^^;
たとえば、高さを300、幅を400にあわせて グラフをA1セルから配置。 A1セルの行・列幅をマウスでドラッグして グラフのサイズにそろえると 行の高さが300と一致します。
高さを200に変更したグラフで確認しても、行の高さが200になって この値と一致している様です。
ですから、この値が何を意味しているのか 調べてみてください。
。。。どの値の事かわかりますか?。。。
>ピポットのご提案も助かります。 >区分というのはたとえばどのようなものなのでしょうか? たとえば、I3:J12の範囲に対応表を作成し G列に数式を埋めます。 [C] [D] [E] [F] [G] [H] [I] [J] [3] 品番 売上 粗利額 粗利率 区分 仕切 区分 [4] りんご 45 8 17.00% 15%〜20% 0% 0%〜 5% [5] バナナ 1 5 18.00% 15%〜20% 5% 5%〜10% [6] みかん 3 10 30.00% 25%〜35% 10% 10%〜15% [7] メロン 27 5 9.00% 5%〜10% 15% 15%〜20% [8] いちご 0 15 34.00% 25%〜35% 20% 20%〜25% [9] すいか 0 12 62.00% 55%以上 25% 25%〜35% [10] トマト 8 6 4.00% 0%〜 5% 35% 35%〜45% [11] 45% 45%〜55% [12] 55% 55%以上
G4セルに =IF(C4="","",VLOOKUP(F4,$I$4:$J$12,2,TRUE)) として、下にフィルドラッグ。
ピボットテーブル/ピボットグラフウィザード 1/3 ●Excelのリスト/データベース(M) ●ピボットグラフ(ピボットテーブル付き)(R) [ 次へ> ] ピボットテーブル/ピボットグラフウィザード 2/3 データ範囲を設定 [ 次へ> ] ピボットテーブル/ピボットグラフウィザード 3/3 [ レイアウト(L) ] 行(R) 区分 列(C) 品番 データ(D) 粗利額・・・「合計」 [ OK ] ピボットテーブルの作成先 ●既存のワークシート(E) ・・・ 適当なセルを指定 [ 完了(F)]
ピボットテーブルと、ピボットグラフが作成されます。
グラフを特定のシートに表示させたい場合は、 グラフ上で右クリック→場所(L) で ●オブジェクト(O) [ 表示したいシートを選択 ▼] してください。
品番が同じものは 勝手に合算してくれます。 G列に表示されていない区分のものはグラフになりませんので ダミーデータを入れておくのが安全かもしれません。
詳しくは、ピボットテーブル&ピボットグラフを調べてみてください。
(HANA)
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.