原创 我是小花椒 Excel学习世界 2022-06-12 11:00 发表于上海
用过数据透视表的人都知道,数据透视表的值区域只能进行计算,而不能返回值字段的内容。
就这点来说,确实是 Power Query 更强大,PQ 的透视功能中有个选项叫“不要聚合”,选择这个选项就不会对值字段进行计算,而是返回字段值,甚至还能返回文本内容。
说回数据透视表,值区域的汇总公式默认情况下是求和,但是如果值字段中有文本,那么就会默认变成计数。
这下问题来了,如果值字段既有数字,又有文本,但还是想让数据透视表默认求和汇总,应该怎么设置?
案例:
下图 1 为各班学生的各科考试成绩,宋大莲因病未能参加考试,所以她的成绩为“缺考”。
请用数据透视表将各个学生的考试成绩汇总成二维表,效果如下图 2 所示。
正常情况下这样制作数据透视表:
1. 选中数据透视表的任意单元格 --> 选择菜单栏的“插入”-->“数据透视表”
2. 在弹出的对话框中选择“现有工作表”--> 选择需要创建数据透视表的位置 --> 点击“确定”
3. 在右侧的“数据透视表字段”区域,将“班级”和“姓名”拖动到“行”区域,“科目”拖动到“列”区域,“成绩”拖动到“值”区域。
现在数据透视表中的各科成绩的汇总方式是计数,这是因为数据源的成绩列中有文本格式的“缺考”单元格,因此数据透视表默认为计数。
4. 在右侧的“数据透视表字段”区域中,选中“值”字段中的“成绩”--> 右键单击 --> 在弹出的菜单中选择“值字段设置”
5. 在弹出的对话框中将计算类型修改为“求和”--> 点击“确定”
数据透视表的汇总方式现在就变成了求和,文本字段变成了 0。
手动修改计算方式虽然管用, 但是如果字段多的话,一个个改就非常低效。
所以接下来就是今天要教的技巧:即使包含文本,数据透视表的汇总方式也默认为求和。
1. 在确保第一行的成绩为数值的前提下,选中标题行和第一个数据行 --> 选择菜单栏的“插入”-->“数据透视表”
2. 在弹出的对话框中选择“现有工作表”及需要创建的位置 --> 点击“确定”
3. 同样将“班级”和“姓名”拖动到“行”区域,“科目”拖动到“列”区域,“成绩”拖动到“值”区域。
4. 选中数据透视表的任意区域 --> 选择菜单栏的“分析”-->“更改数据源”-->“更改数据源”
5. 在弹出的对话框中选择整个数据表区域 --> 点击“确定”
修改了数据源区域以后,数据透视表的数据字段就完整了,而汇总方式仍然是求和,这正是我们要的效果。
所以在字段多的情况下,今后就不用一个个手工修改汇总方式了,用这方法甚方便。
转发、在看也是爱!
本站仅提供存储服务,所有内容均由用户发布,如发现有害或侵权内容,请
点击举报。