EXCEL 如何制作瀑布图,对于有些数据做图表非常适合用瀑布图,比如生活开支......
Excel计算有颜色的行数量(多种颜色分别统计)
我们通常会以填充背景颜色的方式,将不同类型的对象进行标注区分。
但是当我们要统计不同类型对象的数量时,尤其是颜色种类很多、且数据存在与不同的工作表sheet时,要想计算出有某种颜色的行数量并非是一个简单的事情。
具体的解决方法,请看敏丝下方图解。
工具/原料
- Office配置
方法/步骤
本例中,我们要在"汇总"sheet中计算多个城市标注不同颜色的行的数量,并最终进行合计,得出各类型客户的数量。
由于我们要用到宏表函数,所以,我们首先要将工作薄另存为"Excel 启用宏的工作薄(*.xlsm)"。
鼠标左键单击"汇总sheet"的A2单元格后,在公式菜单下找到名称管理器并单击打开(或使用快捷键Ctrl F3也可打开,下图1)。
新建1个名称,名称设置为"颜色",引用位置输入"=GET.CELL(63,!B2) NOW()*0"(下图2)。
接下来,在"汇总sheet"的A2单元格输入"=颜色"(下图1),或输入"="号后按F3快捷键调出名称,双击该名称输入/单击后点击确认(下图2),然后按Enter键完成公式输入,输入完成后将公式向下填充。
在各个城市(Sheet)的A列位置,插入辅助列。
在辅助列,以和上一步同样的方式,输入"=颜色",并向下填充公式。
在"汇总"Sheet城市名称下输入公式:
=COUNTIFS(INDIRECT(D$1&"!A:A"),$A2)
在合计列,使用SUM函数将各个城市的计算结果进行求和汇总。
由于后面的列可能会新增城市数据,因此,将SUM函数放在前面的列位置最妥当(SUM函数对后面的单元格求和的区域可以多选一些)。
此时,如果我们在各个城市(Sheet)对行更改标注颜色时,"汇总"(sheet)里的函数公式不能自动更新。
所以,我们再次新建一个名称,名称名设置为"工作表名",引用位置设置为"=GET.WORKBOOK(1)&T(RAND())"(下图1)。
名称建好后,如果我们更改行的标注颜色,汇总(Sheet)的函数公式便能自动更新了。(下图2)
需要注意的是:
使用INDIRECT函数进行间接引用时,汇总Sheet的名称一定要与各个城市(Sheet)的名称一致,各个城市(sheet)名称中不能存在空格,否则公式不能匹配,无法计算出结果。
常见的查询错误,我们还可以参考以下经验:
30Excel查询错误怎么办(VLOOKUP、真空假空等)
如果我们要在各个城市(Sheet)中插入新的行(有标注颜色),需要及时在辅助列填充好公式,以更新函数公式计算结果。
如果要将填充颜色的单元格替换为数字(下图1转下图2),请参考下方百度经验:
计算填充某种颜色的单元格数量,也适用此方法。
17Excel填充颜色单元格替换成数字(含高清视频)
如对您有帮助,请投票或点赞鼓励一下敏丝,谢谢~
注意事项
- 各个城市(Sheet)后面的日期需要增加数据(可能会插入列,或挪动公式位置),会影响到数据的计算,因此,辅助列放在A列最妥当。
以上方法由办公区教程网编辑摘抄自百度经验可供大家参考!