Excel图表 - 类别和子类别分组

时间:2022-10-15 07:37:19

I seldom if ever use excel and have no deep understanding of graphs and graphing-related functions. Having said that...

我很少使用excel并且对图形和图形相关函数没有深刻的理解。话说回来...

I have dozens of rows of data, composed by 4 columns

我有几十行数据,由4列组成

  • column 1 = amount/price (in numbers)
  • 第1列=金额/价格(以数字表示)
  • column 2 = description (the what in text)
  • 第2列=描述(文中的内容)
  • column 3 = category (in text)
  • 第3列=类别(在文本中)
  • column 4 = subcategory (in text)
  • 第4列=子类别(在文本中)

I want to make a bar graph of my rows of data so that, the end result looks like this:

我想制作一个我的数据行的条形图,以便最终结果如下所示:

X axis - categories Y axis - amount/price

X轴 - 类别Y轴 - 数量/价格

The trick here is for categories NOT to repeat themselves. For example, if our data is something like...

这里的诀窍是不重复自己的类别。例如,如果我们的数据类似......

  • 100 | boat purchase | boats | 3 engine boat
  • 100 |船购买船| 3发动机船
  • 200 | boat purchase | boats | 2 engine boat
  • 200 |船购买船| 2发动机船
  • 500 | plane purchase | planes | 4 engine plane
  • 500 |飞机购买|飞机| 4引擎飞机
  • 900 | car purchase | cars | 1 engine car
  • 900 |汽车购买|汽车| 1引擎车

Then there should only be ONE instance of boats, planes and cars in my graph, under which all associated data would be summed up.

然后在我的图表中应该只有一个船只,飞机和汽车的实例,在这个实例中,所有相关数据将被总结。

Last but not least, I have seen graphs where, these unique-not-repeated categories, instead of just being one single 'bar' so to speak, are composed of smaller bars. In this case, I want these smaller bars to be the sub categories, so that the end result would look like this:

最后但并非最不重要的是,我已经看到图表,这些独特的不重复的类别,而不是仅仅是一个单一的“酒吧”可以说,由较小的条组成。在这种情况下,我希望这些较小的条形成为子类别,因此最终结果将如下所示:

Excel图表 - 类别和子类别分组

In that sample image, I first present a 'basic, classic' graph where blue, yellow and red each represent a unique, different category. Right below it is what I want, a 'breakdown' of each category by subcategory where blue/yellow/red each represent an imaginary 3 different subcategories per category.

在该示例图像中,我首先呈现一个“基本的,经典的”图表,其中蓝色,黄色和红色各自代表一个独特的,不同的类别。正下方是我想要的,按子类别划分每个类别的“细分”,其中蓝色/黄色/红色各自代表每个类别虚构的3个不同子类别。

This means subcategories will repeat themselves for each category, but categories themselves will not.

这意味着子类别将为每个类别重复,但类别本身不会。

For clarification, I currently only have 3 main categories and 6 or so sub-categories, but this could change in the future, hence the desire to have this in an automatic/dynamic fashion

为了澄清,我目前只有3个主要类别和6个左右的子类别,但这可能会在未来发生变化,因此希望以自动/动态方式实现这一目标

Kind regards G.Campos

亲切的问候G.Campos

EDIT: new image:

编辑:新形象:

Excel图表 - 类别和子类别分组

2 个解决方案

#1


11  

Here i my take on it. Unfortunately I can't post the screenshots as I don't have enough posts.

我在这里接受它。不幸的是我无法发布截图,因为我没有足够的帖子。

One solution is to use pivot charts put Amount in "Values", Category in "Row Lables", and SubCategory in "Column Labels".

一种解决方案是使用数值图表将Amount放入“值”,“行标签”中的类别和“列标签”中的子类别。

I uploaded relevant images on a free image upload service.

我在免费图片上传服务上传了相关图片。

This is our source data:

这是我们的源数据:

Amount  Decription  Category    SubCategory
100     boat purchase   boats   3 engine boat
200     boat purchase   boats   2 engine boat
500     plane purchase  planes  4 engine plane
900     car purchase    cars    1 engine car
450     boat purchase   boats   2 engine boat
110     plane purchase  planes  4 engine plane
550     car purchase    cars    1 engine car
230     car purchase    cars    2 engine car
450     car purchase    cars    5 engine car

This is the desired graph (Edit: This has ghost bars):

这是所需的图形(编辑:这有鬼条):

http://imageshack.us/photo/my-images/849/pivot.gif/

http://imageshack.us/photo/my-images/849/pivot.gif/

I just read the comment about no ghost graphs. This might be what you are looking for:

我刚读了关于没有幽灵图的评论。这可能是您正在寻找的:

http://imageshack.us/photo/my-images/266/pivotnoghost.gif/

http://imageshack.us/photo/my-images/266/pivotnoghost.gif/

Just googled and found something very similar here:

谷歌搜索,发现一些非常相似的东西:

peltiertech.com/WordPress/using-pivot-table-data-for-a-chart-with-a-dual-category-axis/

peltiertech.com/WordPress/using-pivot-table-data-for-a-chart-with-a-dual-category-axis/

You need to add http:// ( I can't have more than two hyperlinks due to low number of posts)

您需要添加http://(由于帖子数量较少,我不能有两个以上的超链接)

#2


2  

I am not sure this will get you exactly where you want but I find in general in excel it is easiest to summarize your graph data on a separate tab.

我不确定这会让你准确到达你想要的位置,但我总体上发现在excel中,最简单的方法是在单独的选项卡上总结你的图形数据。

For sample data like this

对于像这样的样本数据

Excel图表 - 类别和子类别分组

you would create a 2nd tab in the sheet that appears something like

你会在工作表中创建第二个选项卡,看起来像

Excel图表 - 类别和子类别分组

the totals are calculated by using the sumif formula

总计是使用sumif公式计算的

=SUMIF(Data!C:C,Summary!A2,Data!A:A)

For the Category totals

对于类别总计

and

=SUMIF(Data!D:D,Summary!E2,Data!A:A)

For the sub category totals (Assuming sub-categories are mutally exclusive). Now that that data is summarized, highlight the cells and insert a column chart for the following charts.

对于子类别总计(假设子类别是互斥的)。现在总结了该数据,突出显示单元格并插入以下图表的柱形图。

Excel图表 - 类别和子类别分组

Adding new categories and/or sub categories will require you to add lines to the summary data, and then add series to the charts. You could use a vba macro to automate that task but I suspect that is overkill since your dataset is "dozens" rather than "thousands"

添加新类别和/或子类别将要求您向摘要数据添加行,然后向图表添加系列。您可以使用vba宏来自动执行该任务,但我怀疑这是过度的,因为您的数据集是“数十”而不是“数千”

#1


11  

Here i my take on it. Unfortunately I can't post the screenshots as I don't have enough posts.

我在这里接受它。不幸的是我无法发布截图,因为我没有足够的帖子。

One solution is to use pivot charts put Amount in "Values", Category in "Row Lables", and SubCategory in "Column Labels".

一种解决方案是使用数值图表将Amount放入“值”,“行标签”中的类别和“列标签”中的子类别。

I uploaded relevant images on a free image upload service.

我在免费图片上传服务上传了相关图片。

This is our source data:

这是我们的源数据:

Amount  Decription  Category    SubCategory
100     boat purchase   boats   3 engine boat
200     boat purchase   boats   2 engine boat
500     plane purchase  planes  4 engine plane
900     car purchase    cars    1 engine car
450     boat purchase   boats   2 engine boat
110     plane purchase  planes  4 engine plane
550     car purchase    cars    1 engine car
230     car purchase    cars    2 engine car
450     car purchase    cars    5 engine car

This is the desired graph (Edit: This has ghost bars):

这是所需的图形(编辑:这有鬼条):

http://imageshack.us/photo/my-images/849/pivot.gif/

http://imageshack.us/photo/my-images/849/pivot.gif/

I just read the comment about no ghost graphs. This might be what you are looking for:

我刚读了关于没有幽灵图的评论。这可能是您正在寻找的:

http://imageshack.us/photo/my-images/266/pivotnoghost.gif/

http://imageshack.us/photo/my-images/266/pivotnoghost.gif/

Just googled and found something very similar here:

谷歌搜索,发现一些非常相似的东西:

peltiertech.com/WordPress/using-pivot-table-data-for-a-chart-with-a-dual-category-axis/

peltiertech.com/WordPress/using-pivot-table-data-for-a-chart-with-a-dual-category-axis/

You need to add http:// ( I can't have more than two hyperlinks due to low number of posts)

您需要添加http://(由于帖子数量较少,我不能有两个以上的超链接)

#2


2  

I am not sure this will get you exactly where you want but I find in general in excel it is easiest to summarize your graph data on a separate tab.

我不确定这会让你准确到达你想要的位置,但我总体上发现在excel中,最简单的方法是在单独的选项卡上总结你的图形数据。

For sample data like this

对于像这样的样本数据

Excel图表 - 类别和子类别分组

you would create a 2nd tab in the sheet that appears something like

你会在工作表中创建第二个选项卡,看起来像

Excel图表 - 类别和子类别分组

the totals are calculated by using the sumif formula

总计是使用sumif公式计算的

=SUMIF(Data!C:C,Summary!A2,Data!A:A)

For the Category totals

对于类别总计

and

=SUMIF(Data!D:D,Summary!E2,Data!A:A)

For the sub category totals (Assuming sub-categories are mutally exclusive). Now that that data is summarized, highlight the cells and insert a column chart for the following charts.

对于子类别总计(假设子类别是互斥的)。现在总结了该数据,突出显示单元格并插入以下图表的柱形图。

Excel图表 - 类别和子类别分组

Adding new categories and/or sub categories will require you to add lines to the summary data, and then add series to the charts. You could use a vba macro to automate that task but I suspect that is overkill since your dataset is "dozens" rather than "thousands"

添加新类别和/或子类别将要求您向摘要数据添加行,然后向图表添加系列。您可以使用vba宏来自动执行该任务,但我怀疑这是过度的,因为您的数据集是“数十”而不是“数千”