Excel技巧

快捷键

  • Excel的快捷键很多,以下主要能提高效率:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
Crtl+方向键,对单元格光标快速移动,移动到数据边缘(空格位置)。

Crtl+Shift+方向键,对单元格快读框选,选择到数据边缘(空格位置)。

Ctrrl+空格键,选定整列。

Shift+空格键,选定整行。

Ctrl+A,选择整张表内容。

Alt+Enter,换行。

Ctrl+Enter,以当前单元格为始,往下填充数据和函数。

Ctrl+S,快读保存,你懂的。

Ctrl+Z,撤回当前操作。

格式转换

  • Excel的格式及转换很容易忽略,但格式会如影随形伴随数据分析者的一切场景,是后续SQL和Python数据类型的基础。
  • 通常我们将Excel格式分为数值、文本、时间。
  • 数值:常见整数型 Int和小数/浮点型 Float。两者的界限很模糊。在SQL和Python中,则会牵扯的复杂,涉及运算效率,计算精度等。
  • 文本:分为中文和英文,存储字节,字符长度不同。中文很容易遇到编码问题,尤其是Python2。Win和Mac环境也有差异。大家遇到的乱码一般都属于中文编码错误。
  • 时间:格式在Excel中可以和数值直接互换,也能用加减法进行天数换算。时间格式有不同表达。例如2016年11月11日,2016/11/11,2016-11-11等。当数据源多就会变得混乱。我们可以用自定义格式规范时间。
  • 这里了解一下时间格式的概念,列举是一些较通用的范例(不同编程语言还是有差异的):
1
2
3
4
5
6
7
8
9
10
11
12
13
YYYY代表通配的四位数年格式

MM代表通配的两位数月格式

DD代表通配的两位数日格式

HH代表通配的的两位数小时(24小时)格式

hh代表通配的两位数小(12小时制)格式

mm代表通配的两位数分格式

ss代表通配的两位数秒格式

数组

  • 数组很多人都不会用到,甚至不知道有这个功能。依旧是数据分析越往后用到越多,它类似R语言的Array和Python的List。
  • 数组由多个元素组成。普通函数的计算结果是一个值,数组类函数的计算结果返回多个值。
  • 数组用大括号表示,当函数中使用到数组,应该用Ctrl+Shift+Enter输入,不然会报错。
  • 先看数组的最基础使用。选择A1:D1区域,输入={1,2,3,4}。记住是大括号。然后Ctrl+Shift+Enter。我们发现数组里的四个值被分别传到四个单元格中,这是数组的独有用法。

分列

  • Excel可以将多个单元格的内容合并,但是不擅长拆分。分列功能可以将某一列按照特定规则拆分。常常用来进行数据清洗。
北京市海定区 北京市 海定区
北京市昌平区 北京市 昌平区
北京市大兴区 北京市 大兴区
北京市通州区 北京市 通州区
北京市石景山区 北京市 石景山区
北京市丰台区 北京市 丰台区
北京市顺义区 北京市 顺义区
北京市朝阳区 北京市 朝阳区
北京市东城区 北京市 东城区
  • SQL和Python中类似split()切分函数。

数据透视表

  • 数据透视表是非常强大的功能,当初学会时惊为天人。
  • 数据透视表的主要功能是将数据聚合,按照各子段进行sum( ),count( )的运算。
  • 将原始数据和汇总计算数据分离。
  • 数据透视表的核心思想是聚合运算,将字段名相同的数据聚合起来,所谓数以类分。
  • 列和行的设置,则是按不同轴向展现数据。简单说,你想要什么结构的报表,就用什么样的拖拽方式。

删除重复项

  • 一种数据清洗和检验的快速方式。想要验证某一列有多少个唯一值,或者数据清洗,都可以使用。
  • 功能类似SQL中的distinct,Python中的set集合。

自定义下来菜单(数据验证)

  • 数据验证是一种约束,针对单元格限制其输入,也就是让其只能固定几个值。下拉菜单是一种高阶应用,通过允许下拉箭头即可。
  • 数据->数据验证->设置验证条件->设置来源