如何仅通过使用excel vba进行编程来创建堆积的柱形图？ [英] How do I create a stacked column chart purely through programming with excel vba?

问题描述

我试图在VBA中创建一个堆叠的柱状图，其中有一个条形图，每个条形图都显示了该图上子类别的细分（请参见图片的右侧）。对于普通的条形图，我要做的就是创建一个新系列并设置Values和XValues属性。我不确定如何告知子类别的值。

I am trying to create a stacked column chart in VBA where there is a bar graph and each bar graph shows the breakdown of subcategories on the graph (See right side of picture). For the normal bar graph all I had to do was create a new series and set the Values and XValues properties. I'm not sure how to tell it about the values for the subcategories.

_{（来源： msecnd.net ）}

_{(source: msecnd.net)}

推荐答案

要创建堆叠的列效果，您需要为列中的每个细分创建一个序列。例如，假设我们有4个动物园。假设他们位于迈阿密，亚特兰大，纽约和拉斯维加斯。另外，我们有4种动物，即狮子，老虎，熊和海豹。我们想要一个堆叠的列，显示每只动物的数量以及动物的总数。

To create the stacked column effect, you need to create a series for each subdivision in the column. For example, say we had 4 zoos. Say they are located in Miami, Atlanta, New York, and Las Vegas. Also, we have 4 animals, Lions, Tigers, Bears, and Seals. And we want a stacked column showing the number of each animal and also the total number of animals.

首先，将图表类型设置为xl3DColumnStacked这样。

First, set the chart type to xl3DColumnStacked like this.

ActiveChart.ChartType = xl3DColumnStacked

然后我们将为每个动物创建一个序列。

We would then create a series for each animal.

ActiveChart.SeriesCollection.NewSeries

然后将Values属性设置为动物的值数组，并将XValues属性设置为城市的名称数组。因此，如果第一个系列是针对Lions的，则在迈阿密，亚特兰大，纽约，拉斯维加斯的狮子数量组成一个数组，并在其中包含字符串 Miami， Atlanta等组成一个数组。cityNames数组将在每个数组中重复使用系列，但是lionCounts显然每次都会被适当的数组替换。例如，tigerCounts，bearCounts等。

Then set the Values property to the array of values for the animal and the XValues property to the array of names for the cities. So if the first series was for Lions, make an array with the # of lions at Miami, Atlanta, NY, Las Vegas and an array containing the strings "Miami","Atlanta", etc. The cityNames array will be reused for each Series but the lionCounts will obviously be replaced each time with the appropriate array. i.e. tigerCounts, bearCounts, etc.

ActiveChart.SeriesCollection(1).Values = lionCounts
ActiveChart.SeriesCollection(1).XValues = cityNames

这应该是直接使用您的值创建堆积列的全部操作程序而不是单元格引用。

That should be all it takes to create a stacked column using values straight from your program instead of cell references.

这篇关于如何仅通过使用excel vba进行编程来创建堆积的柱形图？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！