表格怎么自动计算占比(按分类汇总算每个占比)
表格怎么自动计算占比
在excel表格中怎么计算数据在总量所占的比例,并用百分比格式显示?
这是一个非常基础excel知识,使用的公式也相当简单,不过对excel新手非常友好。
我们介绍两个场景,以下面数据表为例,场景1:在表1中,已知产品1销量和所有产品总销量,要计算产品1销量占总销量的比例;场景2:表2数据包含了所有产品销量,要计算产品1销量的占比。
既然是两个场景,那我们分别来介绍不同场景的公式使用。
场景1,表1中已知产品1销量和总销量,而销量除以总销量就是该销量的占比,因此可以输入基础运算公式:
=A5/B5
这是一个基础运算公式,只使用了一个除法符号“/”,但它们得到的结果默认是数值格式,如上图所见,是包含了多个小数位的数字。
因此如果要显示为百分比,还需要进行单元格格式的设置。
设置如上图所示,有两种方式,第一个是直接按下快捷键Ctrl+1,进入单元格格式设置界面,并点击“数字--百分比--小数位数选择为2”,即可显示保留2位小数位的百分比。
第二个方式是点击上方功能区数字区块中的“%”符号,并点击右侧箭头所指的“向左箭头”图标,点击依次则小数位向左移动一位,点击两次则得到保留2个小数位的百分比。
以上是第一个场景的公式。接下来继续第二个场景的占比百分比计算。
回到表格,表2的数据是包含了所有产品的明细销量,它没有总销量,但我们都知道,将所有产品销量相加,就能得到总销量。
因此它相当于进行两步计算,所以我们可以利用sum函数先求和产品总销量,再将产品1销量除以总销量即可。
可以输入公式为:
=B10/SUM(B10:B15)
这个公式应用了基础运算和函数的组合,这里大家可以注意一个地方,函数表达式在公式中是作为一个整体的,进行加减乘除运算时不需要添加括号来引用,但如果是一个基础运算表达式和另一个数字进行加减乘除运算,则要添加左右括号来引用。
比如这个示例,“=500/(500+6210+3200+1420+1205+3985)”,其中“500+6210+3200+1420+1205+3985”就是一个基础运算表达式,在于另一组数字相计算时,切记使用左右括号来引用,否则会得到错误结果!
按分类汇总算每个占比
大名鼎鼎的数据透视表听说过吧?其中有一项特别牛的功能——计算分类汇总的百分比。
在Excel 2010即以上版本中,右键单击数据透视表中值区域的任意单元格,在[值显示方式]选项中,我们可以看到下图所示的可选计算方式。
这一功能极大地方便了百分比的计算,同时提供了足够多的计算方式可供选择。这里面的大部分计算方式非常直观,无需过多说明就可以理解。
但父行汇总百分比、父列汇总百分比、父级汇总百分比这三项就不那么好从字面上直观的理解,甚至容易误解,所谓的父行、父列、父级究竟是指什么呢?
接下来咱们就解析一下父行汇总百分比、父列汇总百分比、父级汇总百分比的计算方式,进而能够正确理解和应用。
要理解百分比的计算方式,我们只需百分比的分子和分母。这里我们举一个例子进行讲解。
下面这个数据透视表中,在行字段上对“区域”、“销售地区”、“品名”三个字段进行分类汇总“销售金额”。
在列字段上有“销售人员”一个字段进行分类汇总“销售金额”。
此时[值显示方式]为“无计算”,即简单的求和汇总。
下面我们首先选择[父行汇总百分比],结果如下图。
为便于理解,我们暂且忽略列字段,假设只有白露一名销售人员。
可以看到,每一个行字段分类下的“销售金额”总和比上它上一级行字段分类下的“销售金额”总和,得出该字段的汇总百分比。
即 “品名”/“销售地区”,“销售地区”/“区域”,“区域”/“总计” 。
上面的描述有些抽象,如果看不懂也没关系,具体到实例,以D5单元格为例,是由按摩椅(“品名”)的总销售金额比上北京(“销售地区”)的总销售金额计算得出。
往上一级,D10单元格,是由北京(“销售地区”)的总销售金额比上华北(“区域”)的总销售金额计算得出。再往上一级,也是**一层,D17单元格,华北(“区域”)的总销售金额比上“总计”计算得出。
看到这里,您大概已经了解了“父行”的含义了,简单的讲可以描述为“上一级行字段”。
同理“父列汇总百分比”只需把行字段换成列字段即可,在此不再赘述。下面我们来看“父级的汇总百分比”,个人认为“父级”比“父行”和“父列”还要更抽象一些,毕竟“行”和“列”在Excel中是我们经常接触的对象,而这个“级”又如何理解呢?
继续看这个例子,我们试着将[值显示方式]设置为“父级的汇总百分比”,与上面的“父行汇总百分比”立即显示结果不同,此时Excel会弹出一个对话框要求选择“基本字段”。
“基本字段”的意思就是计算百分比时作为分母的字段,出现在选项中的既有行字段,也有列字段,如果我们选择的“基本字段”是一个行字段,则类似父行汇总百分比,在行字段之间计算百分比,不同点在于各级行字段均会比上我们选择的“基本字段”而不是依次比上上一级行字段。
选择列字段同理。下面我们结合实例来讲解,下图是将“销售地区”选为“基本字段”的父级汇总百分比结果:
由于“销售地区”是行字段,我们仍然可以忽略列字段只看D列。
D5单元格,是由按摩椅(“品名”)的总销售金额比上北京(“基本字段”即“销售地区”)的总销售金额计算得出,与“父行汇总百分比”结果相同。
而D10单元格则出现了差异,因为这一级是由北京(“销售地区”)的总销售金额比上北京(“基本字段”即“销售地区”)的总销售金额计算得出,所以为100%。
再往上一级D17,出现了空值,这是因为“区域”/“销售地区”(即“基本字段”)>100%,所以不做计算,为空。
以上,我们可以看到,不论那一级字段,分母始终是我们选定的“基本字段”,而与“父级”无关,这也是单看字面意思容易误解的原因。下图是将“区域”选为“基本字段”的父级汇总百分比结果,进一步佐证了上面的解释:
希望看到这里,您已经对父行汇总百分比、父列汇总百分比、父级汇总百分比的计算方式已经有了深入的理解。