这篇文章介绍了一些Microsoft Excel比较常用的功能点和方法。
1. 数据透视表分析的前提
使用数据透视表,需要保证三个前提:
1.不存在缺字段情况;2.不存在合并单元格情况;3.同一字段的数据类型需要统一。
我们看对于下边的这个数据:
需要把上边几个城市合并了的单元格拆分。下边空的格依次填充好“北京”、“贵阳”、“杭州”。
- 先选中一个范围(不选中容易死机),再按 Ctrl + G 调出“定位”窗口,选择“定位条件”,选择“空值” ,确定,这样可以选中所有空格。
- 选择“北京”下的第一个空格,使用公式 = 上边空格,再按Ctrl +Enter,可以一次填充所有空格。
- 因为现在每一个单元格都是等于上一个单元格,需要重新复制,粘贴并只保留值。
判断一个单元格内的内容是文本还是数字,可以使用函数ISNUMBER(如果是返回True),如果需要将文本转换为数字,可以使用VALUE函数:
=ISNUMBER(D4)
=VALUE(D4)
也可以使用分列方式来实现将文本转为数字。选中要做分列的数据列,选择“数据” -> “分列” ,直接点击下一步,选择任何的分隔符即可,点击“下一步” -> 选择“常规”, -> 点击“完成”即可。 实际这个功能能将一列拆分为多个列(并可以制定拆分的列的格式)。
下面需要修改透视表的数据源,修改的方法如下:
点中我们的数据透视表(点击透视表的任何一个位置就行),选择“分析” -> "更改数据源"。重新选中数据透视表的数据区域即可。
2. 基础数据透视表和多维布局
创建数据透视表的第一种方式是, 选中一个空单元格,点击“插入” -> “数据透视表” ,再选择数据透视表的数据区域即可。
第二种方式使用“数据透视表向导”, 使用快捷键“ALT +D + P , (如果是笔记本,按住Fn+ alt ,点一下D ,再点一下P,就可以弹出新建透视表向导窗口)如果是Mac,使用COMMAND + ALT + P。
这样,就完成了数据透视表的创建,行、列、值、筛选标签可以任意拖动,非常灵活。
当数据透视表的数据很大时,可以勾上“延迟布局更新” , 勾上后,只有在点击“更新”后,透视表才更新数据,否则不随着拖动自动更新。
数据表的本质就是分类汇总,也就是SQL语句中的
SELECT X,SUM(Y) FROM Table GROUP BY X
3. 数据透视表选项配置
3.1 值字段设置
选择透视表的一个列,右键,选择“值字段设置”,这里可以一起修改这个列的汇总方式(例如从“计数”转为“求和”,并且可以设置数字的显示方式,例如千分位符号,保留几位小数等)。
3.2 数据透视表选项
选择透视表的一个列,右键,选择“数据透视表选项”, 这里有几个常用的配置,入下图:一般地、“更新时自动调整列宽”可以不勾选。在默认情况下,透视表是行标签折叠的方式来显示的,如下表,这样其实不友好,不是表格式的,看不起来不舒服。
这时候,可以选中透视表,然后选择“设计”->“报表布局” -> “以表格形式显示”。展示如下:
4. 数据透视表二维转一维
多重合并计算数据区域。 双击2个“总计”的交叉位置,就可以得到转一维的数据表。