合并Excel数据透视表中的列值 [英] Combining Column Values in an Excel Pivot Table

问题描述

为了简化堆积的条形图，我想总结一下我的数据透视表中多列的计数.例如，在此示例表中，我想将水果和蔬菜合并为一列，以便每个条形将包含三种颜色:一种用于肉类，一种用于谷物，另一种用于水果+蔬菜.

In order to simplify a stacked bar chart, I am looking to sum up the counts of multiple columns I have in my pivot table. For example, in this sample table, I would like to combine Fruits and Vegetables into one column, so that each bar will comprised of three colors: one for Meats, one for Grains, and one for Fruits+Vegetables.

数据透视表和堆叠图表

作为参考，这是数据源:

For reference, this is the data source:

数据源表

我尝试添加计算字段，但不确定是否可以使用公式"字段中一列的特定值.是否有一种方法可以从数据透视表创建类似的内容，或者我是否应该对此采取不同的方法?

I tried adding a Calculated Field, but I'm not sure if I can use specific values from a column in the Formulas field. Is there a way to create something like this from a pivot table, or should I be taking a different approach to this all together?

推荐答案

我正在使用Excel 2016，-可以肯定，它与旧版本相同，但前提是相同的.

I'm using Excel 2016, -- pretty sure it's the same with older versions, but the premise is the same.

在数据透视表中，选择数据透视表工具">分析"选项卡，然后选择字段，项目"，然后下拉至计算的字段".输入所生成字段的名称，以及要使用的公式:

In your pivot table, Select the Pivot Table Tools> Analyze tab, then "Fields, Items",then pull down to"Calculated fields". Enter a name for the generated field, and the formula you want to use:

在我的示例中，我从可用的数据透视表字段(基于数据表)中添加了字段Fruit和Vegi's.

In my example, I added the fields Fruit and Vegi's from my available pivot table fields (which is based on my data table).

然后选择计算字段，就像选择其他字段一样.

Then select the calculated field as you would any other..

并照常创建图表.

希望这会有所帮助.

这篇关于合并Excel数据透视表中的列值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！