扫码加入数据分析学习群

文档：excel.md 链接：http://note.youdao.com/noteshare?id=fcacb94da58291761788766226b43cdf&sub=778AE0B6885A4A598B4669CDEB380DD7

一、 基础知识

• 工作簿：扩展名xls、xlsx、xlsm、保护
• 工作表：插入、复制、移动、重命名、保护
• 单元格：单元格属性、单元格地址、单元格区域地址 2^20*214

二、工作表加工

1、数据加工

• 文本型数字转换数值型数字：
  6种方式：*1，/1，+0，-0，--，value()
• 快速输入数据

1. 直接拖拽填充柄（只有数字按原数字填充，文字+数字下拉时数字增加1）
2. Ctrl+填充柄（默认步长1）
3. 输入两个值-填充柄（默认等差填充）
4. 右键按住填充柄下拉-【序列】等差、等比、日期、年、月、日等
5. 选择单元格区域-【数据】-【数据验证】-序列，序列选项之间用英文分号隔开

• 组合键快速选择

1. 选中连续区域：SHIFT+CTRL+↑↓←→
2. 定位边缘单元格：CTRL+↑↓←→
3. ALT+=  汇总上方数据列，选中要小计的单元格，若不连续的，辅助Ctrl键

• 查找替换筛选

1. Ctrl+F查找和替换:*代替任意多个字符，? 代表一个字符
2. 快速筛选：SHIFT+CTRL+L
3. 使用“查找”选中某特定格式的单元格
4. 查找替换单元格，选中"单元格匹配"，不影响其他单元格的相同相同内容
5. 替换号时，查找为“~”
6. 利用辅助列产生相关数据的排序实现隔行插入1空行
7. 数据选项卡-高级筛选-筛选区域和条件区域

• 复制粘贴

1. 选择性粘贴为数值、格式、图片链接、转置
2. 使用选择性粘贴方法实现“金额”数据列数据增加25%。
3. 借助辅助列，选择性粘贴跳过空单元格，将两列数据合为一列

• 定位条件

1. Ctrl+G定位条件
2. ctrl+erter输入相同的值
3. 使用“定位条件”选中某特定条件的单元格
4. 选中空值、可见单元格、公式产生的错误值、对象（比如表中的图片、插入的形状等）

2、格式加工

• 条件格式：数据条、色阶

1. 使用公式求所选区域进行格式设置时，选中的单元格与区域的左上角单元格形成对应，然后向下向右填充，需注意尽量不要选中标题行，公式中行或列的引用方式要搞清楚
2. 图标格式中，尽量不使用百分比的选项，使用数值选项，将数据中的百分比转化为数值，图标会更直观明了

• 打印设置：打印界面-页面设置-设置标题行或重复列
• 保护功能

工作簿保护：结构，表示对工作表的编辑限制；
工作表保护：对单元格编辑的限制；
部分保护（部分单元格可编辑）：取消可编辑单元格的“锁定”复选框，然后设置保护工作表

• 冻结窗格：视图选项卡——拆分冻结窗格
• 自定义数据格式中，aaa可将星期数字1，2...7对应转换为标准星期数日、一、二...六
• 0“瓶” ：将单元格设置为该格式，对其输入2时，单元格显示为2瓶，且右对齐，但其本质仍未数值格式，该单元格可参与计算
• @“部门” ：@为输入的文本，输出的仍为文本格式
• 要注意单元格的显示和实际内容是有差别的，判别单元格时，应以实际内容为准进行判别
• 表格的合并计算：数据——合并计算

三、公式函数

• 相对绝对引用F4切换
• 文本连接 &

1、文本函数

• MID() 取子串
• LEFT() 从左取子串
• RIGHT() 从右取子串
• LEN() 文本长度
• TEXT() 数字转化文本格式
• REPT 文本重复
• REPLACE 替换特定位置处的文本
• SUBSTITUTE 替换文本
• Find() 一个字符串在另一个字符串的起始位置

2、逻辑函数

• IF,AND,OR,NOT的结合使用

3、数学函数

• INT() 取整
• MOD() 求余数

MOD求余函数，IF+MOD统计闰年或平年：同为正或者同为负：余数=被除数-整商×除数一个为正一个为负：余数=除数×（整商+1）-被除数

• ROUND() 四舍五入
• ABS() 取绝对值
• SQRT() 算术平方根
• RAND() 产生随机数
• RANDBETWEEN()

1. 返回位于两个指定数之间的一个随机整数。每次计算工作表时都将返回一个新的随机整数。
2. 每次被引用时，都会生成新的随机数

4、统计函数

• MAX() 求最大
• MIN() 求最小
• SUM() 求和
• SUMIF() 条件求和
• SUMIFS() 多条件求和
• COUNT() 数值计数
• COUNTA() 计数
• AVERAGE() 求平均
• AVERAGEIF() 条件平均
• AVERAGEIFS() 多条件平均
• COUNTIF() 条件计数
• COUNTIFS() 多条件计数
• FREQUENCY() 求数据分布频率

1. 用以分段计数，=frequency(数据源、分段点)
2. 数组公式，先选中返回值得单元格区域，再按ctrl+shift+enter确认输入公式

分段点返回结果释义a5x<=ab10>a且<=bc6>b且<=c-23>c

• RANK() 排名次
• 条件求和

1. sumif(条件区域,求和条件,[求和区域])
2. sumifs( 求和区域，条件区域，求和条件，...... )
3. =SUM(条件一×条件二×……条件N×求和区域)，Ctrl+Shift+Enter结束公式输入
4. =SUMPRODUCT(条件一×条件二×……条件N×求和区域)，Enter结束公式输入
5. 遇到多条件是或关系的情况时，可采用sumif相加的方法

• 条件计数

1. =countif(条件一×条件二×……条件N)，最后，Ctrl+Shift+Enter结束公式输入
2. =SUPRRODUCT(条件一×条件二×……条件N)，Enter结束公式输入
3. countifs(条件区域1，计数条件1，条件区域2，计数条件2，......),每一个附加的区域都必须与条件区域1具有相同的行数和列数,这些区域无需彼此相邻。

• sum函数求的区域参数中，连续区域用冒号，不连续区域用逗号连接，取其交集作为选用区域
• 求和条件和计数条件中，>和<之类的符号都要写在引号中
• sumproduct函数

1. 主要用来求几组数据的乘积之和；
2. =sumproduct(区域)：只设置一个参数，作用与sum相同；
3. =sumproduct(区域1，区域2)：设置两个参数，相同位置求乘积再求和；
4. 设置多个参数，计算相同位置的乘积再求和
5. 参数中的文本、逻辑值等非数值数据，函数将其当做0处理；
6. 参数包含的数据个数、行列数必须相同，否则会报错
7. =sumproduct（条件1，条件2...求和区域）用以条件求和
8. =sumproduct（条件1，条件2...）用以条件计数

5、日期与时间函数

• YEAR() 求年
• MONTH() 求月
• DAY() 求日
• TODAY() 当前日期 2020/5/19
• NOW() 当前日期和时间 2020/5/19 16:07
• today（）和now()函数无参数
• DATE() 输出标准日期格式 2011/11/21
• EDATE() 指定日期前后月份的日期
• WORKDAY 计算工作日
• NETWORKDAYS() 开始日期和结束日期之间工作日数值
• DATEDIF(日期1，日期2，格式)

Y 相差年数
M 相差总月数
YM 一年内相差月数
D 相差总天数
YD 一年内相差天数
MD 一月内相差天数
1900/1/1：标准日期格式是以斜线进行连接的

• networkdays()：返回两个日期之间的完整工作日数，若有特殊的节假日，还需手动设置将其去掉

应使用 DATE函数输入日期，或者将日期作为其他公式或函数的结果输入。例如，使用函数 DATE(2012,5,23) 输入 2012 年 5 月 23 日。 如果日期以文本形式输入，则会出现问题。

• eomonth（）：获取指定日期当月（0）、前月（负数）、后月（正数）的最后一天
• weekday()第二参数默认为1，1-7对应星期日到星期六；常用的为2时，1-7对应星期一到星期日
• weekday在配合使用excel其他功能，并且是不能编写公式函数的功能，比如，自定格式，参数二必须为1，否则会出错

公式输入输出自定义格式aaa的显示=WEEKDAY(DATE(2019,11,1),1)6五=WEEKDAY(DATE(2019,11,1),2)5四

6、查找引用函数

• VLOOKUP() 垂直方向查找

查找列必须位于查找区域首列
参数四为0或省略，默认模糊查找，返回<或=查找值的最大值

• OFFSET() 计算偏移量

常用做动态图表的制作

• MATCH(值，区域，格式)

1. 1 或省略：查找<或= lookup_value 的最大值。查找区域参数中的值必须以升序排序
2. 0：查找=lookup_value的第一个值。查找区域参数中的值可按任何顺序排列。
3. -1:查找>或= lookup_value 的最大值。查找区域参数中的值必须以降序排序
4. 查找区域只能是一列数或一行数

• INDEX(区域，行，列)

1. 若区域只有一行或一列，可只设置两个参数
2. 与match搭配使用实现查找只不在查找区域首列的逆向查找
3. 若区域为单元格引用，则返回值也为单元格引用；只有档区域为飞单元格引用的常量数组时，返回值才是常量数组

• ROW() 引用行的数据
• COLUMN() 引用列的数据

7、其他函数

• HYPERLINK(超链接地址，【显示字段】)
• INDIRECT函数功能：将文本“A1”转换为单元格引用A1，作用范围：

设置二级组合下拉框
先设置一级和二级下拉框的名称
二级下拉框数据验证处用indirect函数对对应一级下拉框的数据进行引用

四、 数据透视表

• 数据透视表内的数据可修改数据类型
• 定位到数据透视表，在其相应选项卡下可设置数据透视表的布局
• 数据透视表—设计—报表布局-以大纲形式显示：可将行列标签显示为选中字段的名称
• 选中整个数据透视表才可移动或删除
• 日期分组显示，取消组合，则为按日显示；反之组合即可
• 更改汇总依据值显示方式：求和、求平均等
• 遇到按月份排序可直接将10，11，12选中进行拖拽即可
• 插入计算字段、计算项：数据透视表-分析-字段、项目和集-计算字段、计算项
• 插入切片器：数据透视表-分析-切片器，切片器工具——切片器设置——自定义列表；文件-选项-高级-自定义列表（2019版本默认升序排列）
• 动态数据透视表：数据源选取借助offset函数，将数据源变为动态型

数据透视表的值显示方式

• 行百分比：每行最后一列为100%
• 列百分比：每列最后一行为100%
• 总计百分比：以右下角总计为基准数进行百分比汇总
• 父行汇总：每一字段的值/最近的上一级的汇总值，最后一行为100%
• 父列汇总：每一字段的值/最近的上一级的汇总值，最后一列为100%
• 父级汇总：只对最后等级的字段进行百分比计算，其他有细分类别的字段都为100%
• 百分比：可选择以某个字段为基准进行百分比汇总
• 按某一字段汇总、按某一字段汇总的百分比：可用来做累计求和、累计百分比
• 差异、差异百分比：用同类别的某一项做基准进行比较；差异百分比选择为“上一个”时，可用作同环比求值
• 指数（重要性权重）规则：（单元格值*总计）/（行总计列总计）

五、图表

• 左和下为主坐标轴，右和上为次坐标轴
• 辅助列，N/A不显示在图表中
• 雷达图维度不超过10，类别不超过4，2个最佳
• 饼图：原始数据一定按顺排列，避免图表显示混乱
• 不建议使用三维图表
• 图表中选不到对应系列时，可通过设置数据格式—系列选项选中
• 删除次坐标轴，主坐标轴会合并两坐标轴的信息，显示两者的最大值；若同时显示，需保证两者最大最小值一致

1、常见图表——对比类

• 柱形图反映一段时间内数据的变化，或者不同项目之间的对比。
• 条形图是显示各个项目之间的对比，其分类轴设置在横轴上。条形图符合人眼上下扫描的阅读方式，对比效果更直观
• 折线图反映一段时间内，统计内容的变化趋势，往往结合时间维度使用。
• 雷达图反映指定维度下类别间的差异，为不影响图表内容的表达，维度控制在10个内，类别控制在4个内（2个为最佳）

2、常见图表——构成类

• 饼图和圆环图反映组成数据系列的项目在项目总和中所占的比例。
• 旭日图与树状图反映组成数据系列间的层级关系以及系类内项目在项目总和中所占的比例。
• 树状图：

结构比较，用于展现多层级下数据间的比例分布情况； 轴的值为文本；
不同颜色代表不同图例；
矩形面积代表数据大小；
是饼图的升级版，比饼图更加直观的展现更多维度层级下的的信息

• 旭日图：

结构比较，用于展示多层级间数据的对比关系 注：轴的值为文本 旭日图是由多层圆环嵌套而成， 不同层级圆环代表不同层级数据维度 旭日图用扇区大小显示数据间的占比多少是饼图的升级版，比饼图更加直观的展现更多维度层级下的的信息

3、常见图表——分布类

• 散点图反映数据在两个维度下的相关性，而气泡图在此基础上增加了一个维度用气泡大小表示。
• 散点图的表示方式：两列数值的相关性
• 气泡的大小代表一个维度

4、常见图表——统计类

• 直方图
  右键坐标轴选项可设置：箱宽度、箱数、溢出箱、下溢箱
• 箱线图

1. 主要包含六个数据节点，将一组数据从大到小排列，分别计算出他的上边缘，上四分位数Q3，中位数Q2，下四分位数Q1，下边缘，异常值。
2. 中位数：将数列按从小到大排列；若为奇数个数，则中位数为第（n+1）/2个数；若为偶数个数，则中位数为第n/2和（n+1）/2的平均值
3. 上四分位数：将数列按从小到大排列，上四分位数为第（n+1）/4个数；
4. 下四分位数：将数列按从小到大排列，下四位数为第3（n+1）/4个数；
5. 四分距：IQR=上四分位数-下四分位数
6. 上边缘：上边缘=上四分位数+1.5IQR

上边缘为数列中存在的数值；若计算得出的上边缘不在数列中，选择小于且离上边缘最近的数值作为上边缘

1. 下边缘：下边缘=下四分位数-1.5IQR

下边缘为数列中存在的数值，若计算得出的下边缘不在数列中，选择大于且离下边缘最近的数值作为下边缘

5、常见图表衍生应用

子弹图

1. 以堆积柱状图为基础
2. 外框架显示的系列，设置次坐标轴，选择为无填充，添加边框颜色、调整间距

子母饼图

1. 以饼图为基础，选中数据确定在第一或第二绘图区
2. 色系、标签、第二绘图区的图表类型都可更改
3. 当在子母饼图中，需以子饼图为100%，显示子饼图内的百分比时:可手动添加辅助列，单独算出子饼图中的百分比（母饼图正常算），然后右键——设置数据标签格式——标签选项，勾选“单元格中的值”，选择手动添加的辅助列即可地区
   4、展示二级分类的一级标签会显示为其他，最后在更改，若提前更改，后续的标签操作对其都不起作用

地区销售额辅助列北方¥143,40431.16%南方¥26,6605.79%西南¥143,74231.24%杭州¥17,06011.66%南京¥38,78426.50%上海row 1 col 261.84%

对比图

1. 以堆积条形图为基础
2. 设置坐标轴区域（-a,a），其中a为绝对值最大的刻度值
3. 设置次坐标轴
4. 逆序刻度值
5. 主坐标轴在数字下，选择自定义格式，输入0;0，取消负值的红色显示
6. 主纵坐标轴标签格式下选择低，则位于图标最左列

6、图表制作雷区

1. 绘图区长宽比例不当
2. 坐标轴刻度值大小不合适，绘图区留白太多
3. 图标信息不完整：标题，坐标轴标题、图例，字体大小等
4. 减量不要选择饱和度高的颜色，尽量一个色系，同一份报告中要掌握好整体的颜色基调