1）填充柄：鼠标左键下拉

2）利用自定义列表填充 文件>选项>高级>编辑自定义列表

3）填充柄：鼠标右键下拉

4）同时编辑多个单元格（连续和不连续） Ctrl+鼠标左键，Ctrl+Enter补全

5）文本记忆输入 右键>从下拉列表中选择

6）数据验证 数据>数据验证>验证条件>允许(序列)>来源(1.自定义内容选项用英文逗号隔开 2.内容可以是区域范围但只可以是一行或一列)

注：日期时间属于特殊的数字

通过自定义格式设置显示格式，增强数据的可读性：

选定要修改的区域>右键>设置单元格格式>自定义

1）设置单元格格式为文本，再输入数字

2）直接在常规格式下，先输入单引号(英文)，再输入数字

通过前面的方式将文本格式改为数字格式>选择需要修改的区域>右键选择设置单元格格式>日期>选择需要的类型

（2）利用 TEXT函数 + 自定义格式 实现，G2，G3......是单元格地址

8.快速求和&快速选择区域&冻结窗口

（1）鼠标选择需要计算的单元格区域

（3）冻结窗格，当需要冻结指定的行和指定的列时：例如冻结前两行跟前两列则选择第三行第三列的单元格然后冻结窗格

1）数据>分类汇总

2）复制可见单元格 F5>定位条件>可见单元格

2）类型转换 选择性粘贴>运算 要求：批量将文本型数字转为数值

（1）任意单元格输入1

（3）选择性粘贴>运算

（2）选择性粘贴>跳过空单元

步骤： a、C2输入1，选中C2:C3，双击填充柄

c、右键>插入>活动单元格下移

 d、选中A及以下单元格，复制

e、点击B2>再右键>选择性粘贴>跳过空单元

1）选项 选择单元格区域>查找和替换>选项>格式>从单元格中选择格式 查找：Ctrl+F 替换：Ctrl+H

注意：用完以后清除设置的格式，软件不会自动清除

查找和替换>选项>单元格匹配

当单元格内容与查找内容完全一样时才会进行匹配，而不是包含查找内容就匹配

1）填充柄>等差序列

6.数据验证&圈释无效数据

（1）公式>名称管理器

（2）数据验证>序列

要求：设置多行多列的下拉列表 a.定义名称：姓名=$A$1:$A$11 (用于下拉列表数据的单元格区域的一列) b.数据验证>序列>来源：=姓名 (设置下拉列表的引用值，“姓名”是变量名) c.名称管理>引用多行多列 (将引用的数据变量名的取值范围改为多行多列，即整个数据范围而不是单独的一列或一行)

注意：数据验证的序列不能引用多行多列，只能引用一行或一列，所以通过以上方式解决

（1）数据验证>设置需要的验证方式

（2）数据验证>圈释无效数据

如下图，给定一个规律，例如鼠标选中沈阳的单元格然后按Ctrl+E 就会自动向下提取并填充城市名称

2）数据>删除重复值

注意：自定义分隔符只能定义一个字符，如果分隔符是多个字符组成则分割后通过查找替换解决

2）选择性粘贴>转置(将行转成列，将列转成行)

3）快速填充(Ctrl+E)，将数据提取为想要的格式

选择结果显示要的区域>数据>合并计算>公式(按需求选择)>引用区域(要进行运算的数据区域，注意：如果显示结果的区域包含了标签内容则当选择计算数据区域的时候也要选择标签部分，反之则都不可以选择标签部分)>添加>选择第二个运算的数据区域(选取规则与前一个相同)>设置标签位置(如果没有选择标签则不需要打勾，如果选自责了标签则按规则打勾)>确定

3.追踪单元格&错误检查&公式求和

（1）选择一个单元格>公式>追踪引用单元格——查看单元格的值是由哪几个单元格求值得到的 （2）选择一个单元格>公式>追踪从属单元格——查看单元格的值有被哪几个单元格所使用 （3）公式>删除箭头 或者 直接保存(指向箭头就会消失)

选择单元格区域>公式>错误检查

用于查看复杂公式的求值步骤

（1）公式>公式求值

条件格式 要求：大于等于60且小于等于79，填充颜色为灰色；大于等于80，填充颜色为黄色

 注意：如果一个单元格同时符合多个规则则会遵循规则顺序靠前的规则，可以通过 条件格式>管理规则>对已有规则进行调整

（2）数据验证>序列 要求：下拉列表选择的省份，背景为红色

要求：总分小于80分的背景设为黄色

IF(条件判断，条件满足的结果，条件不满足的结果)

AND(条件1，条件2，... ) 只有所有条件同时成立时才为真

OR(条件1，条件2，... ) 只要有一个条件成立就为真

注：多条件计数可以写一个条件也可以写多个条件

SUMIF(求和的范围，筛选字段，筛选值)

注：多条件求和可以写一个条件也可以写多个条件

YEAR(时间值或只为时间的单元格) —— 提取时间中的年份

MONTH(时间值或只为时间的单元格) —— 提取时间中的月份

DAY(时间值或只为时间的单元格) —— 提取时间中的天的部分

EOMONTH(值为时间的单元格,偏移值) —— 获取指定日期的最后一天的日期

注：偏移值为0求当月的最后一天；向过去偏移用负数；向未来偏移用正数。

MID(单元格地址或字符串，提取的开始位置【包含】，提取几位)

TEXT(值，格式代码) 返回的是一个文本类型

2）提取出生日期：=TEXT(MID(B2,7,8),"")*1 将身份证含义为出生日期的片段提取出来并转换为日期格式

3）提取周岁年龄：=DATEDIF(D2,TODAY(),"y") 求当前日期与出生日期的时间差并提取年的部分

6.隔行求和&生成间断序号

=IF(B2<>"",COUNT($B$2:B2),"") 根据旁边的工号进行计数，如果旁边的工号是空的就赋值为空不进行累积计数的操作否则对工号进行累积计数操作并将计数的值赋值  

FREQUENCY(引用的数组的单元格地址范围，分组节点的单元格地址范围)

溢出数组行为: 一般的公式只返回一个值,一个值使用一个单元格存储;而复杂的公式可能返回多个值,多个值无法存储在一个单元格里面。Excel会自动存储至相邻的单元格,这种行为称为数组溢出行为,而这个复杂的组合公式称为动态数组公式

1）解决多重操作的问题,比如需要添加辅助列来解决统计需求的问题; 2）数组公式通常用于返回值存在多个的问题

（3）生成2行3列的常量数组，选中2行3列的单元格区域，输入：={1,2,3;4,5,6}

注意：ROW(1:10)表示第1行至第10行的单元格区域

3）数组公式——乘号和加号

2.两列之差的和&正数之和

（2）SUM+数组公式

3.身份证长度是否正确

要求：统计一车间男性工资之和

要求：统计一车间和二车间工资之和

要求：统计客户数量（去重）

要求：判断是否符合退休条件，男士：60岁及以上，女士：55岁及以上

1.查找与引用函数介绍

OFFSET(基准点单元格的引用地址，要偏移的行数，要偏移的列数[，偏移之后返回的单元格高度] [，偏移之后返回的单元格宽度])

5.MATCH函数——通过内容查找位置

要求：检查员工当月的公积金是否重复提交

注意：MATCH函数默认返回第一个查找到的数值

要求：计算两列数据相同的数目

思考：如何区分大小写来计算两列数据相同的数目？

EXACT函数(区分大小写)但是不支持一步完成需要增加辅助列

第二步：=SUM(D19:D28) 对上一步的结果值进行求和

6.INDEX函数——根据位置查找内容

第一步创建省份的下拉列表：点击A2单元格(省份下面的单元格)>数据>数据验证>来源(地区表的表头)

第二步以各省份创建以省份命名的名称：地区表>全选>F5(条件定位)>定位条件选择常量(只选中有内容的单元格)>公式>根据所选内容创建>按照需求选择创建名称的值的位置>

第三步根据省份创建城市的下拉列表：点击B2单元格(城市下面的单元格)>数据>数据验证>来源( INDIRECT(A2) )

要求：通过下拉列表，动态计算

第一步：选择季度以及各产品的值所在的单元格区域(不要选择合计)>公式>根据所选内容创建>名称选择在首行

选择数据表>插入>推荐图标>所有图标>饼图

2）复合饼图（子母饼图）

选择数据表>插入>推荐图标>所有图标>饼图>子母饼图

注：双击一块区域可移动绘图区，左边为第一绘图区，右边为第二绘图区

注：想要设置圆环的宽度: 在圆环上右键>选择设置数据系列格式>圆环图圆环大小

选择数据表>插入>推荐图标>所有图标>旭日图

2.折线图&堆积折线图&百分比堆积折线图

选择数据表>插入>推荐图标>所有图标>折线图

选择数据表>插入>推荐图标>所有图标>折线图>堆积折线图

选择数据表>插入>推荐图标>所有图标>折线图>堆积折线图

3.条形图&簇状条形图&堆积条形图&百分比堆积条形图

选择数据表>插入>推荐图标>所有图标>条形图>簇状条形图

选择数据表>插入>推荐图标>所有图标>条形图>堆积条形图

选择数据表>插入>推荐图标>所有图标>条形图>百分比堆积条形图

注：如果轴标签与图例的值相反，则点一下可视化图表，然后右键了，点击选择数据，最后点击切换行/列，确定。

用于衡量两个指标的相关性，正性相关（X增大Y也随着增大）和 负性相关（X增大Y随着减小）

先选择一个字段>按住Ctrl键选择另一个字段>插入>推荐的图表>所有图表>XY散点图

添加趋势线：点击图表>点击右上角的加号>勾选趋势线

显示趋势线公式：点击图表>点击右上角的加号>趋势线右边箭头>更多选项>下滑勾选显示公式；显示R平方值

添加数据标签：点击图表>点击右上角的加号>数据标签右边箭头>更多选项>勾选单元格中的值>选中需要引用的值的单元格区域>点击确定

5.气泡图(散点图的扩展)

需要三个字段：字段1决定X轴；字段2决定Y轴；字段3决定气泡的大小

选择一个字段>按住Ctrl键选择另外两个字段>插入>推荐的图标>所有图表>XY散点图>气泡图

如果X轴、Y轴和气泡的字段有问题：点击图表>右键>选择数据>点击图例项中的一个选项>编辑>对X轴、Y轴和气泡大小的决定字段进行修改>确定

调节气泡的总体大小：点击气泡>右键>设置数据系列格式>缩放气泡大小为

选择数据表>插入>推荐的图表>所有图表>雷达图

7.帕累托图(二八定律的分析)，又叫排列图

选择数据区域>插入>推荐的图表>所有图表>直方图>排列图

2）组合图实现帕累托图

让表按照值降序排列>为表添加一个累计百分比字段>选中表>插入>推荐的图表>所有图表>组合图>值用簇状柱形图>累计百分比用折线图（或带数据标记的折线图）>累计百分比勾选次坐标轴（次坐标轴指右边的轴，主坐标轴指左边的轴）>确定

为折线添加数据标签：点击折线>点击右上角加号>勾选数据标签

为折线上的单独某个点添加数据标签：点击折线>再点击某个点>右上角加号>勾选数据标签

8.树状图&直方图(频数统计)

选中数据区域>插入>推荐的图表>所有图表>树状图

选择一个数值字段>插入>推荐的图表>所有图表>直方图

设置分组节点：点击下方的坐标轴>右键>设置坐标轴格式>箱的宽度>填入需要设置的值

溢出箱：大于设定值的归为一组

下溢箱：小于设定值的归为一组

9.箱线图（集中趋势和离散程度）又称盒须图、箱型图

观测数据的集中趋势和离散程度

选中数据>插入>推荐的图表>所有图表>箱型图

上限值：Q3+1.5IQR，最大不超过最大值 下限值：Q1-1.5IQR，最小不低于最小值

1）Q3=QUARTILE.INC(数值单元格区域，3) QUARTILE.INC包含零；QUARTILE.EXC不含零，通常用包含零的。其中第二个参数值0为求最小值，1为求下四分位点(第25个百分位的值)，2为求中值，3为求上四分位点(第75个百分位的值)，4为求最大值

10.柱形图和折线图 & 散点图和折线图——组合图

按住Ctrl键选中需要的三个字段>插入>推荐的图表>所有图表>组合图>一个选择簇状柱形图一个选择折线图(或带数据标记的折线图)

按住Ctrl键选中地区、销售额、目标达成率三个字段>插入>推荐的图表>所有图表>组合图>销售额选择簇状柱形图、目标达成率选择带数据标记的折线图

选中数据表>插入>推荐的图表>所有图表>组合图>对数据字段分别按需求选择散点图或者折线图

选中数据表>插入>推荐的图表>所有图表>组合图>成绩选择散点图、及格线和优秀线选择折线图

知识点：组合图，簇状柱形图、系列重叠，填充、边框，粗细、间隙宽度

1）创建图表：选中数据表>插入>推荐的图表>所有图表>组合图>全部选择簇状柱形图分析哪几个数据用同一个轴，用主坐标轴和次坐标轴进行区别

2）对柱形进行重叠操作：点击一个柱形>右键>设置数据系列格式>系列重叠的值拉到100%

3）修改柱形的宽度：点击需要设置的一个柱形>右键>设置数据点格式>设置间隙宽度(值越大柱形越细，值越小柱形越粗)

4）修改柱形的填充颜色：选中需要设置的一个柱形>填充>选择需要的颜色

5）设置柱形的边框颜色：选中要设置的一个柱形>边框>选择需要的颜色>粗细>选择需要的边框宽度

旋风图又叫蝴蝶图或人口金字塔图

1）创建图表：选择数据区域(需要两个数值字段和一个名称字段)>插入>推荐的图表>所有图表>组合图>两个字段都选择簇状条形图>其中一个勾选次坐标轴

2）调节刻度：选中其中一个坐标轴>右键>设置坐标轴的格式>求出数据的最大值，并将其设置为刻度的最大值，其负值设置为刻度的最小值，使刻度对称(例如：7000，-7000)>点击另一个坐标轴设置其刻度与上一个保持一致>删除其中一个坐标轴>此时图标已经对称

3）逆序刻度值：选中一个坐标轴>右键>设置坐标轴格式>向下托，勾选逆序刻度值

4）移动名称坐标轴：点击坐标轴>右键>设置坐标轴格式>向下托，点击标签，标签位置设为低或者高按需求设置

5）设置坐标轴的负值：选中坐标轴>设置坐标轴的格式>数字>类别选择自定义>格式代码：[>0]0;[<0]0;>添加>此时坐标轴就没有负数了

13.矩阵图又叫四象限图

1）创建图表：选择两个数值字段>插入推荐图表>所有图表>XY散点图>散点图选择第二个

2）设置坐标轴：选择X坐标轴>右键>设置坐标轴的格式>设置刻度的最大值最小值>纵坐标轴交叉选中坐标轴值>设为X坐标轴字段的平均值>点击Y坐标轴>设置坐标轴刻度的最大最小值>横坐标轴交叉选中坐标轴值>设置为Y坐标轴字段的平均值

3）添加标签：选中图表>点击右上角的加号>数据标签>更多选项>勾选单元格中的值>选取单元格区域>确定

平均值： 满意度：5 利润率：7.5%

14.瀑布图(用于做对比分析)

选中数据区域>插入>推荐的图表>所有图表>瀑布图

15.漏斗图(流程转化分析)

选中名称字段和转化率字段>插入>推荐的图表>所有图表>漏斗图

要求：根据选取的月份创建显示当月各地区情况的图表

2）设置月份的下拉列表：

选中月份下的空白单元格>数据>数据验证>允许设为序列>来源选择原数据表中缘分的值区域>确定

3）根据月份获取每个区域的值：

4）创建图表：选中创建的数据表>插入>推荐的图表>簇状柱形图

1）设计表：选中数据表>插入>表格>确定

2）创建数据透视表：选中数据表>插入>数据透视表>可以选择新建工作表或者现有工作表>确定

3）设计数据透视表：选中数据透视表>右键>显示字段列表>根据需要设计行列和值

4）创建数据透视图：选中数据透视表中的数据>插入>推荐的图表>所有图表>选择需要的图表

5）删除字段按钮：选中数据透视图>数据透视图分析>字段按钮

注意：数据透视表可以连接数据透视图，但是有一些特殊的图并不支持，例如瀑布图

1）创建设计数据透视表

（1）创建数据透视表：选中数据表>插入>数据透视表>可以选择新建工作表或者现有工作表>确定

（2）设计数据透视表：选中数据透视表>右键>显示字段列表>根据需要设计行列和值

2）显示字段列表：点击数据透视表>右键>显示字段列表

3）数据透视表的分析与设计：点击数据透视表>数据透视表分析、设计

4）移动数据透视表字段：（1）鼠标拖放；（2）右键>移动

点击数据透视表>设计>报表布局

注意：表格形式的分类汇总只能显示在底部

2）对部门合并居中：选中数据透视表>右键>数据透视表选项>布局，勾选合并且居中排列带标签的单元格

3）去除加号/减号的按钮：选中数据透视表>数据透视表分析>+/-按钮  

1）数字格式：点击数据透视表的任何一个值>右键>值字段设置>数据格式  

 2）值字段设置：双击字段>自定义名称、计算类型

3）值显示方式：点击修改字段下的一个值>右键>值显示方式

1）右键>值汇总依据(计算类型/计算逻辑,如求和/最大值最小值/平均值等)

2）值字段设置>值字段汇总方式>计算类型

点击行标签>右键>字段设置>分类汇总和筛选>自定义

同比:今年本月与去年本月的对比 环比:本月与上月的对比 定比:1-12月固定与1月的对比

右键>值显示方式(数据透视计算后结果值的二次计算)>差异百分比

(1)环比增长率：差异百分比>月份>上一个

(2)同比增长率：差异百分比>年份>上一个

(3)定比增长率：差异百分比>月份>1

2）按某字段汇总&按某字段汇总的百分比&升序降序排列&总计百分比

（1）按某字段汇总：按指定字段方向累计计算

右键>值显示方式>按某一字段汇总

（2）按某字段汇总的百分比：按指定字段方向累计百分比计算

右键>值显示方式>按某字段汇总的百分比

升序排序：按计算的结果值升序排列生成序号 降序排序：按计算的结果值降序排列生成序号

右键>值显示方式>升序排序/降序排序

a.总计百分比：以总计为基数计算百分比

b.列汇总百分比：以列总计为基数计算百分比

c.行汇总百分比：以行总计为基数计算百分比

右键>值显示方式>父级汇总百分比

（1）差异：两个值相减差，比较值-基准值

右键>值显示方式>差异

（2）百分比：比较值/基准值

右键>值显示方式>百分比

（3）差异百分比：(比较值-基准值)/基准值

右键>值显示方式>差异百分比

指数计算逻辑：（单元格值总计的值）/(行汇总的值列汇总的值)

右键>值显示方式>指数

选中透视表>数据透视表分析>插入切片器>选择筛选的字段（可选择多个也可以选择单个）

选中透视表>数据透视表分析>插入日程表>选择字段

3）一个切片器控制多个透视表

多个透视表必须是由一个数据源创建的

（1）创建切片器：点击一个数据透视表>数据透视表分析>插入切片器>选择一个筛选字段>确定

（2）切片器连接多个报表：点击切片器>切片器>报表链接>选择要链接的数据透视表(可以多个)>确定

（1）鼠标选中数据透视表的行标签>右键>创建组合>选择要组合的字段>确定

（2）设置日期每隔固定天数为一组

鼠标选中数据透视表的行标签>右键>创建组合>只能选择“日”字段>天数设置需要固定分组的天数

鼠标选中数据透视表中需要分为一组的行标签>右键>创建组合

鼠标选中数据透视表的数值行标签>右键>创建组合>起始值，终止值，步长>确定

1）选中数据透视表>数据透视表分析>更改数据源

方法2： （1）选中数据表>Ctrl+T或者（插入）>创建表>命名，如：data （2）选中数据透视表>数据透视表分析>更改数据源>将 表/区域设置为创建的表的名称，如：data （3）选中数据透视表>右键>刷新

选中数据透视表>数据透视表分析>字段、项目和集>计算字段>给插入字段起名>按照需求填写公式>添加>确定

数据表中的表头叫字段，表头下面的值叫项

选中一个数据表中需要计算的项的名称单元格>数据透视表分析>字段、项目和集>计算项>起名>填写计算公式>添加>确定

=GETPIVOTDATA(要获取的数据透视表中的信息名称，透视表位置，筛选字段1，筛选值1 [，筛选字段2，筛选值2] )

例：根据年份、学期和年级快速获取数据透视表中对应的平均成绩

杜邦分析仪:指标拆解的分析方法

全部成本 = 销售成本 + 销售费用 + 管理费用 + 其他费用 净利润 = 销售收入 - 全部成本 + 其他利润 - 所得税 销售净利率 = 净利润 / 销售收入 其他流动资产 = 流动资产 - 现金有价证券 - 应收账款 - 存货 长期资产 = 资产总额 - 流动资产 资产周转率 = 销售收入 / 资产总额 资产净利率 = 销售净利率 * 资产周转率 权益乘数 = 1/(1 - 资产负债率) 权益净利率 = 资产净利率 * 权益乘数

通过选择性粘贴>带链接的图片

插入大括号：插入>插图