一、基本操作
1.保护设置
| 基本操作 |
|---|
| 1. 保护工作簿：文件>信息>保护工作簿>用密码进行加密（需要密码才能打开Excel文件） |
| 2. 保护工作簿结构：审阅>保护工作簿（防止对工作簿结构进行更改，如插入/删除/重命名/移动或复制/隐藏工作表等） |
| 3. 工作表保护：审阅>保护工作表（当前工作表的所有单元格均无法操作） |
| 4. 单元格保护：审阅>允许编辑区域>保护工作表 |
2.快速输入数据
1）填充柄：鼠标左键下拉
2）利用自定义列表填充 文件>选项>高级>编辑自定义列表
3）填充柄：鼠标右键下拉
4）同时编辑多个单元格（连续和不连续） Ctrl+鼠标左键，Ctrl+Enter补全
5）文本记忆输入 右键>从下拉列表中选择
6）数据验证 数据>数据验证>验证条件>允许(序列)>来源(1.自定义内容选项用英文逗号隔开 2.内容可以是区域范围但只可以是一行或一列)
3.导入数据
| 导入外部数据 |
|---|
| 数据选项卡 -> 从文本/CSV |
| 数据选项卡 -> 获取数据 -> 自文件 -> 从文本/CSV |
4.数据类型
| 常见数据类型 | 实例 |
|---|---|
| 文本 | 姓名、性别、住址、商品名称 |
| 数字 | 100，0.1，1e5… |
| 逻辑值 | TRUE, FALSE |
| 错误值 | #VALUE!、#DIV/0!、#NAME!、#N/A、#REF!、#NULL! |
注：日期时间属于特殊的数字
| 错误值 | 实例 |
|---|---|
| #VALUE! | 文本与数字运算 |
| #DIV/0! | 两数相除，分母为零 |
| #NAME! | 公式名称错误 |
| #N/A | 查找值不存在 |
| #REF! | 所引用单元格被删除 |
| #NULL! | 两数组无交集 |
| #NUM! | 使用无效数值 |
| ### | 单元格宽度不足 |
5.自定义格式
通过自定义格式设置显示格式，增强数据的可读性：
选定要修改的区域>右键>设置单元格格式>自定义
6.文本行数据
如果单元格输入的数字超过12位，则以科学记数显示 例如：41654654644646400 ------> 4.165E+16
解决方法：
1）设置单元格格式为文本，再输入数字
2）直接在常规格式下，先输入单引号(英文)，再输入数字
| 文本型数字转为数值（也适用于日期时间） |
|---|
| 使用6个公式之一来转换（A1为单元格地址） |
| 1、=A1*1 |
| 2、=A1/1 |
| 3、=A1+0 |
| 4、=A1-0 |
| 5、=--A1 |
| 6、=VALUE(A1) |
| 逻辑值转为数字（A1为单元格地址） |
|---|
| 1、=A1*1 |
| 2、=A1/1 |
| 3、=A1+0 |
| 4、=A1-0 |
| 5、=--A1 |
| 6、=N(A1) |
7.日期标准化
1）文本行的标准格式
通过前面的方式将文本格式改为数字格式>选择需要修改的区域>右键选择设置单元格格式>日期>选择需要的类型
2）不标准格式
（1）查找替换
| 不标准格式1 | 使用查找替换 |
|---|---|
| 2019年9月1日 | 2019-09-01 |
| 2016年8月10日 | 2016-08-10 |
| 2020.10.10 | 2020-10-10 |
| 2018.7.19 | 2018-07-19 |
| 2011.12.19 | 2011-12-19 |
（2）利用 TEXT函数 + 自定义格式 实现，G2，G3......是单元格地址
| 不标准格式2 | TEXT+自定义格式 |
|---|---|
| 100102 | =TEXT(G2,"2000-00-00")+0 |
| 190919 | =TEXT(G3,"2000-00-00")+0 |
| 201008 | =TEXT(G4,"2000-00-00")+0 |
| 171108 | =TEXT(G5,"2000-00-00")+0 |
| 160708 | =TEXT(G6,"2000-00-00")+0 |
（3）选择区域>数据>分裂>下一步>下一步>选择日期>完成
| 不标准格式3 | 数据>分列 |
|---|---|
| 2019年10月1日 | 2019-10-01 |
| 2019年10月2日 | 2019-10-02 |
| 20191003 | 2019-10-03 |
| 20191004 | 2019-10-04 |
| 20191005 | 2019-10-05 |
8.快速求和&快速选择区域&冻结窗口
1）知识点：ALT + = 快速求和
（1）鼠标选择需要计算的单元格区域
（2）ALT+=
2）快速选择区域
| 操作 | 功能 |
|---|---|
| Ctrl+方向键 | 快速移动到边界单元格 |
| Shift+方向键 | 当前所选的单元格区域，按指定方向扩充/缩小一行或一列 |
| Ctrl+Shift+方向键 | 当前所选的单元格区域，按指定方向扩充/缩小至边界单元格 |
3）冻结窗格
视图>冻结窗格
（1）冻结首行
（2）冻结首列
（3）冻结窗格，当需要冻结指定的行和指定的列时：例如冻结前两行跟前两列则选择第三行第三列的单元格然后冻结窗格
二、Excel数据处理
1.定位条件
1）空值
F5>定位条件>空值>输入指定值>按Ctrl+Enter实现批量修改
2）错误
F5>定位条件>公式>错误 >删除(按delete键批量清空)或修改错误值
2.分类汇总
1）数据>分类汇总
2）复制可见单元格 F5>定位条件>可见单元格
3）组合 数据>组合 创建组合：Shift+Alt+向右键 取消组合：Shift+Alt+向左键
3.选择性粘贴
1）运算
选择单元格区域(或单元格)>Ctrl+C>选择单元格区域>右键>选择性粘贴>运算
2）类型转换 选择性粘贴>运算 要求：批量将文本型数字转为数值
（1）任意单元格输入1
（2）复制该单元格
（3）选择性粘贴>运算
3）跳过空单元格
（1）F5>定位条件>常量
（2）选择性粘贴>跳过空单元
步骤： a、C2输入1，选中C2:C3，双击填充柄
b、选中A:C列，F5>定位条件>常量
c、右键>插入>活动单元格下移
d、选中A及以下单元格，复制
e、点击B2>再右键>选择性粘贴>跳过空单元
4.查找和替换
1）选项 选择单元格区域>查找和替换>选项>格式>从单元格中选择格式 查找：Ctrl+F 替换：Ctrl+H
注意：用完以后清除设置的格式，软件不会自动清除
2）单元格匹配
查找和替换>选项>单元格匹配
当单元格内容与查找内容完全一样时才会进行匹配，而不是包含查找内容就匹配
5.隔行插入空行
1）填充柄>等差序列
2）开始>排序和筛选(Ctrl+Shift+L)>按照等差序列升序
6.数据验证&圈释无效数据
1）数据验证
（1）公式>名称管理器
（2）数据验证>序列
要求：设置多行多列的下拉列表 a.定义名称：姓名=$A$1:$A$11 (用于下拉列表数据的单元格区域的一列) b.数据验证>序列>来源：=姓名 (设置下拉列表的引用值，“姓名”是变量名) c.名称管理>引用多行多列 (将引用的数据变量名的取值范围改为多行多列，即整个数据范围而不是单独的一列或一行)
注意：数据验证的序列不能引用多行多列，只能引用一行或一列，所以通过以上方式解决
2）圈释无效数据
（1）数据验证>设置需要的验证方式
（2）数据验证>圈释无效数据
7.快速填充
快速填充，Ctrl+E
如下图，给定一个规律，例如鼠标选中沈阳的单元格然后按Ctrl+E 就会自动向下提取并填充城市名称
| 省份城市 | 城市 | 省份 | 城市省份 |
|---|---|---|---|
| 辽宁省沈阳市 | 沈阳 | 辽宁 | 沈阳辽宁 |
| 吉林省长春市 | |||
| 江苏省南京市 | |||
| 浙江省杭州市 | |||
| 安徽省合肥市 | |||
| 福建省福州市 | |||
| 江西省南昌市 | |||
| 山东省济南市 | |||
| 河南省郑州市 | |||
| 湖北省武汉市 |
8.分列&删除重复值
1）数据>分列
2）数据>删除重复值
注意：自定义分隔符只能定义一个字符，如果分隔符是多个字符组成则分割后通过查找替换解决
9.分列-转置
1）数据>分列
2）选择性粘贴>转置(将行转成列，将列转成行)
3）快速填充(Ctrl+E)，将数据提取为想要的格式
10.合并计算
选择结果显示要的区域>数据>合并计算>公式(按需求选择)>引用区域(要进行运算的数据区域，注意：如果显示结果的区域包含了标签内容则当选择计算数据区域的时候也要选择标签部分，反之则都不可以选择标签部分)>添加>选择第二个运算的数据区域(选取规则与前一个相同)>设置标签位置(如果没有选择标签则不需要打勾，如果选自责了标签则按规则打勾)>确定
三、Excel基本公式
1.单元格引用
| 引用方式及实例 |
|---|
| 相对引用(相对位置)：A1 |
| 绝对引用(绝对位置)：$A$1 |
| 混合引用(相对引用和绝对引用结合使用)：$A1、A$1 |
2.运算符 
| 优先级 | 运算符 | 类型 |
|---|---|---|
| 1 | : | 区域运算符 |
| 2 | 空格 | 交叉运算符 |
| 3 | , | 联合运算符 |
| 4 | - | 负 |
| 5 | % | 百分比 |
| 6 | ^ | 乘幂 |
| 7 | *、/ | 乘法、除法 |
| 8 | +、- | 加法、减法 |
| 9 | & | 连接 |
| 10 | =、<、>、<=、>=、<> | 比较运算符 |
| 注意:可通过小括号调整执行顺序 |
3.追踪单元格&错误检查&公式求和
1）追踪单元格
（1）选择一个单元格>公式>追踪引用单元格——查看单元格的值是由哪几个单元格求值得到的 （2）选择一个单元格>公式>追踪从属单元格——查看单元格的值有被哪几个单元格所使用 （3）公式>删除箭头 或者 直接保存(指向箭头就会消失)
2）检查错误
选择单元格区域>公式>错误检查
3）公式求和
用于查看复杂公式的求值步骤
（1）公式>公式求值
（2）F9
四、Excel条件格式
1.条件格式1
条件格式 要求：大于等于60且小于等于79，填充颜色为灰色；大于等于80，填充颜色为黄色
注意：如果一个单元格同时符合多个规则则会遵循规则顺序靠前的规则，可以通过 条件格式>管理规则>对已有规则进行调整
2.条件格式2
（1）条件格式
（2）数据验证>序列 要求：下拉列表选择的省份，背景为红色
3.条件格式3
条件格式
要求：总分小于80分的背景设为黄色
五、Excel常用函数
1.IF + AND + OR + MOD
IF(条件判断，条件满足的结果，条件不满足的结果)
AND(条件1，条件2，... ) 只有所有条件同时成立时才为真
OR(条件1，条件2，... ) 只要有一个条件成立就为真
MOD(值1，值2) 值1对值2求余
例如：
=IF(AND(E2>=60,F2>=60,G2>=60),"通过","不通过")
=IF(MOD(A2,4)=0,"闰年","平年")
2.COUNTIF(单条件计数)&COUNTIFS(多条件计数)、SUMIF(单条件求和)&SUMIFS(多条件求和)
1）COUNTIF&COUNTIFS
COUNTIF(条件范围，筛选值)
COUNTIFS(条件范围1，筛选值1[，条件范围2] [，筛选值2] ... )
注：多条件计数可以写一个条件也可以写多个条件
2）SUMIF$SUMIFS
SUMIF(求和的范围，筛选字段，筛选值)
SUMIFS(求和的范围，筛选字段1，筛选值1 [, 筛选字段2] [，筛选值2] ... )
注：多条件求和可以写一个条件也可以写多个条件
3.DATEDIF时间差函数
| 起始日期 | 结束日期 | 时间差 | 公式 | 参数 | 说明 | |
|---|---|---|---|---|---|---|
| 2010-08-01 | 2012-02-04 | 1 | =DATEDIF(A2,B2,"Y") | Y | 相差年数 | 几年 |
| 2010-08-01 | 2012-02-04 | 18 | =DATEDIF(A3,B3,"M") | M | 相差月数 | 几月 |
| 2010-08-01 | 2012-02-04 | 552 | =DATEDIF(A5,B5,"D") | D | 相差天数 | 几天 |
| 2010-08-01 | 2012-02-04 | 6 | =DATEDIF(A4,B4,"YM") | YM | 一年内相差月数 | 几年几月 |
| 2010-08-01 | 2012-02-04 | 187 | =DATEDIF(A6,B6,"YD") | YD | 一年内相差天数 | 几年几天 |
| 2010-08-01 | 2012-02-04 | 3 | =DATEDIF(A7,B7,"MD") | MD | 一月内相差天数 | 几年几月几天 |
4.提取日期信息
1）YEAR/MONTH/DAY
YEAR(时间值或只为时间的单元格) —— 提取时间中的年份
MONTH(时间值或只为时间的单元格) —— 提取时间中的月份
DAY(时间值或只为时间的单元格) —— 提取时间中的天的部分
2）EOMONTH
EOMONTH(值为时间的单元格,偏移值) —— 获取指定日期的最后一天的日期
注：偏移值为0求当月的最后一天；向过去偏移用负数；向未来偏移用正数。
3）ROUNDUP 向上取整
ROUNDUP(数值，小数位)
5.提取身份信息
MID(单元格地址或字符串，提取的开始位置【包含】，提取几位)
TEXT(值，格式代码) 返回的是一个文本类型
TODAY() 获取当前日期
1）提取性别：=IF(MOD(MID(B2,17,1),2)=1,"男","女") 将身份证的倒数第二位提取出来，奇数为男偶数为女
2）提取出生日期：=TEXT(MID(B2,7,8),"0000-00-00")*1 将身份证含义为出生日期的片段提取出来并转换为日期格式
3）提取周岁年龄：=DATEDIF(D2,TODAY(),"y") 求当前日期与出生日期的时间差并提取年的部分
6.隔行求和&生成间断序号
1）隔行求和
SUM+填充柄
2）生成间断序号
COUNT() 对数值进行计数
COUNTA() 对非空值进行计数
=IF(B2<>"",COUNT($B$2:B2),"") 根据旁边的工号进行计数，如果旁边的工号是空的就赋值为空不进行累积计数的操作否则对工号进行累积计数操作并将计数的值赋值 
7.FREQUENCY频数统计函数(数组公式)
FREQUENCY(引用的数组的单元格地址范围，分组节点的单元格地址范围)
注意： 21前版本调用数组公式:ctrl+shift+enter
溢出数组行为: 一般的公式只返回一个值,一个值使用一个单元格存储;而复杂的公式可能返回多个值,多个值无法存储在一个单元格里面。Excel会自动存储至相邻的单元格,这种行为称为数组溢出行为,而这个复杂的组合公式称为动态数组公式
六、数组公式
1.数组公式:
1）解决多重操作的问题,比如需要添加辅助列来解决统计需求的问题; 2）数组公式通常用于返回值存在多个的问题
例：
（1）=SUM(B2:B10*C2:C10) 两个区域的对应单元格相乘后求和
（2）计算大于零的数值之和：=SUM((A3:A7>0)*A3:A7)
（3）生成2行3列的常量数组，选中2行3列的单元格区域，输入：={1,2,3;4,5,6}
（4）计算1到10的自然数求和。公式：=SUM(ROW(1:10)) ROW函数获取行号，ROW(1，10)获取第1行至第10行的行号
注意：ROW(1:10)表示第1行至第10行的单元格区域
（5）求两个数组进行相乘后的总和。=SUM({1,2}*{10;20;30})
={1,2}*{10;20;30}，运算过程推导：
3）数组公式——乘号和加号
2.两列之差的和&正数之和
1）两列之差的和
=SUM(B2:B11-A2:A11)
2）正数之和
（1）IF+SUM+数组公式
=SUM(IF(A2:A17>0,A2:A17,0))
（2）SUM+数组公式
=SUM((A2:A17>0)*A2:A17)
3.身份证长度是否正确
1）常规方式：=IF(OR(LEN(B2)=15,LEN(B2)=18),"正确","错误") LEN函数获取文本长度
2）数组方式1——判断两次，一次判断一列数据：=IF((LEN(B2:B11)=15)+(LEN(B2:B11)=18),"正确","错误")
3）数组方式2——每个单元格单独判断两次：=IF(OR(LEN(B2)={15;18}),"正确","错误")
4.多条件求和
| A | B | C | D |
|---|---|---|---|
| 姓名 | 部门 | 性别 | 工资 |
| 赵大年 | 一车间 | 男 | 2400 |
| 钱英姿 | 二车间 | 女 | 1700 |
| 孙军 | 一车间 | 男 | 2000 |
| 赵芳芳 | 一车间 | 女 | 1700 |
| 钱三金 | 三车间 | 男 | 1900 |
| 孙纹 | 一车间 | 女 | 1800 |
| 赵一曼 | 二车间 | 女 | 2000 |
| 钱芬芳 | 三车间 | 女 | 1700 |
| 孙大胜 | 三车间 | 男 | 2300 |
1）SUM/*/数组公式
要求：统计一车间男性工资之和
=SUM((B2:B10="一车间")*(C2:C10="男")*(D2:D10))
2）SUM/SUMIFS/数组公式
要求：统计一车间和二车间工资之和
=SUM(((B2:B10="一车间")+(B2:B10="二车间"))*(D2:D10))
=SUM(SUMIFS(D2:D10,B2:B10,I5:I6)) I5和I6分别为：一车间、二车间
5.非重复值计数
| 订单ID | 订单日期 | 客户ID | 客户姓名 |
|---|---|---|---|
| A0001 | 2021-04-27 | 14485 | 曾惠 |
| A0002 | 2021-06-15 | 10165 | 许安 |
| A0003 | 2021-06-15 | 10165 | 许安 |
| A0004 | 2021-12-09 | 17170 | 宋良 |
| A0005 | 2020-05-31 | 15730 | 万兰 |
| A0006 | 2019-10-27 | 18325 | 俞明 |
| A0007 | 2019-10-27 | 18325 | 俞明 |
| A0008 | 2019-10-27 | 18325 | 俞明 |
| A0009 | 2019-10-27 | 18325 | 俞明 |
| A0010 | 2019-10-27 | 18325 | 俞明 |
| A0011 | 2018-12-22 | 21700 | 谢雯 |
| A0012 | 2021-06-01 | 19585 | 康青 |
| A0013 | 2019-06-05 | 10885 | 赵婵 |
| A0014 | 2019-06-05 | 10885 | 赵婵 |
| A0015 | 2019-06-05 | 10885 | 赵婵 |
| A0016 | 2020-11-22 | 20965 | 刘斯 |
| A0017 | 2020-11-22 | 20965 | 刘斯 |
| A0018 | 2021-10-02 | 14050 | 白鹄 |
| A0019 | 2021-10-02 | 14050 | 白鹄 |
| A0020 | 2021-10-02 | 14050 | 白鹄 |
| A0021 | 2021-10-02 | 14050 | 白鹄 |
| A0022 | 2021-10-02 | 14050 | 白鹄 |
| A0023 | 2021-10-02 | 14050 | 白鹄 |
| A0024 | 2021-10-02 | 14050 | 白鹄 |
| A0025 | 2020-06-07 | 10600 | 贾彩 |
| A0026 | 2020-06-07 | 10600 | 贾彩 |
| A0027 | 2020-06-07 | 10600 | 贾彩 |
| A0028 | 2020-06-07 | 10600 | 贾彩 |
| A0029 | 2020-06-07 | 10600 | 贾彩 |
| A0030 | 2020-06-07 | 10600 | 贾彩 |
| A0031 | 2020-06-07 | 10600 | 贾彩 |
| A0032 | 2021-12-12 | 15910 | 马丽 |
| A0033 | 2021-09-28 | 12310 | 宋栋 |
| A0034 | 2021-09-28 | 12310 | 宋栋 |
| A0035 | 2020-11-19 | 13495 | 巩虢 |
| A0036 | 2020-02-28 | 20575 | 常松 |
| A0037 | 2020-09-03 | 16450 | 田黎 |
| A0038 | 2020-09-17 | 17815 | 谭乐 |
| A0039 | 2020-09-17 | 17815 | 谭乐 |
| A0040 | 2020-09-17 | 17815 | 谭乐 |
| A0041 | 2020-07-02 | 11875 | 徐岱 |
| A0042 | 2020-07-26 | 14815 | 武杰 |
| A0043 | 2020-12-24 | 15700 | 吕兰 |
| A0044 | 2018-05-17 | 21385 | 唐婉 |
| A0045 | 2018-05-17 | 21385 | 唐婉 |
| A0046 | 2020-07-16 | 12505 | 葛毅 |
| A0047 | 2020-06-16 | 21265 | 邹涛 |
| A0048 | 2020-06-16 | 21265 | 邹涛 |
| A0049 | 2020-06-16 | 21265 | 邹涛 |
| A0050 | 2021-06-19 | 15985 | 薛磊 |
SUM/COUNTIFS/数组公式
要求：统计客户数量（去重）
=SUM(1/COUNTIFS(C2:C51,C2:C51))
6.判断是否退休
| 姓名 | 性别 | 年龄 |
|---|---|---|
| 赵 | 男 | 55 |
| 钱 | 女 | 46 |
| 孙 | 女 | 57 |
| 李 | 男 | 53 |
| 周 | 男 | 65 |
| 吴 | 女 | 53 |
| 郑 | 女 | 59 |
| 王 | 男 | 46 |
| 冯 | 男 | 60 |
| 陈 | 女 | 60 |
要求：判断是否符合退休条件，男士：60岁及以上，女士：55岁及以上
1）IF/AND/OR
=IF(OR(AND(B3="男",C3>=60),AND(B3="女",C3>=55)),"是","否")
2）IF/*/+
=IF((B3="男")*(C3>=60)+(B3="女")*(C3>=55),"是","否")
3）IF/*/+/数组公式
=IF((B3:B12="男")*(C3:C12>=60)+(B3:B12="女")*(C3:C12>=55),"是","否")
7.三列之和
| 品牌 | 周一 | 周二 | 周三 |
|---|---|---|---|
| 西门子 | 39 | 75 | 38 |
| 长虹 | 85 | 34 | 17 |
| Philip | 10 | 71 | 80 |
| TCL | 90 | 53 | 47 |
| 西门子 | 75 | 58 | 55 |
| 西门子 | 13 | 87 | 60 |
| 长虹 | 78 | 50 | 75 |
| TCL | 17 | 22 | 30 |
| 长虹 | 50 | 58 | 73 |
| Philip | 33 | 98 | 15 |
| TCL | 82 | 17 | 10 |
要求：统计三天之和
IF/SUM/数组公式
| 品牌 | 三列之和 |
|---|---|
| 西门子 | =SUM(IF($A$2:$A$12=G6,$B$2:$D$12,0)) |
| 长虹 | =SUM(IF($A$2:$A$12=G7,$B$2:$D$12,0)) |
| Philip | =SUM(IF($A$2:$A$12=G8,$B$2:$D$12,0)) |
| TCL | =SUM(IF($A$2:$A$12=G9,$B$2:$D$12,0)) |
七、Excel查找引用函数
1.查找与引用函数介绍
| 函数名称 | 功能 |
|---|---|
| VLOOKUP | 垂直方向查找 |
| HLOOKUP | 水平方向查找 |
| OFFSET | 偏移 |
| MATCH | 通过内容查找位置 |
| INDEX | 通过行号列号查找内容 |
| ROW | 返回一个引用的行号 |
| COLUMN | 返回一个引用的列号 |
| INDIRECT | 返回文本字符串所指定的引用 |
| CHOOSE | 根据给定的索引值，从参数串中选出相应值或操作 |
| FIND() | 返回一个字符串在另一个字符串中出现的起始位置（区分大小写） |
2.VLOOKUP函数
3.OFFSET
OFFSET(基准点单元格的引用地址，要偏移的行数，要偏移的列数[，偏移之后返回的单元格高度] [，偏移之后返回的单元格宽度])
公式：=OFFSET(A1,5,2,3,3)
结果如下表：
| 员工姓名 | 性别 | 年龄 |
|---|---|---|
| 张大 | 男 | 50 |
| 刘二 | 女 | 60 |
| 田七 | 男 | 50 |
4.OFFSET累计求和
SUM/OFFSET/ROW
| 日期 | 数量 | 累计求和(SUM+OFFSET) |
|---|---|---|
| 2008-01-01 | 293 | 293（=SUM(OFFSET($B$2,0,0,ROW()-1,1))） |
| 2008-01-02 | 192 | 485 |
| 2008-01-03 | 449 | 934 |
| 2008-01-04 | 290 | 1224 |
| 2008-01-05 | 254 | 1478 |
| 2008-01-06 | 361 | 1839 |
| 2008-01-07 | 439 | 2278 |
| 2008-01-08 | 337 | 2615 |
| 2008-01-09 | 200 | 2815 |
| 2008-01-10 | 123 | 2938 |
| 2008-01-11 | 371 | 3309 |
5.MATCH函数——通过内容查找位置
| 查找位置-行号： | |
|---|---|
| 1 | |
| 2 | |
| 3 | |
| 4 | |
| 5 | |
| 6 | |
| 7 | 100 |
| 8 | |
| 9 | |
| 10 |
查找位置-列号：
| 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 |
|---|---|---|---|---|---|---|---|
| 200 |
| 要求 | 查找值 | 位置 |
|---|---|---|
| 查找100在B2:B11单元格区域的位置 | 100 | 7（=MATCH(K6,B2:B11,0)） |
| 查找200在A15:H15单元格区域的位置 | 200 | 6（=MATCH(K7,A15:H15,0)） |
1）MATCH判断重复
IF/MATCH/ROW
要求：检查员工当月的公积金是否重复提交
| 工号 | 姓名 | 部门 | 工资 | 公积金 |
|---|---|---|---|---|
| A01048 | 王巍 | 技术支持部 | 3100 | 465 |
| A02267 | 刘洋 | 企划部 | 2900 | 435 |
| A02267 | 刘洋 | 企划部 | 2900 | 435 |
| A01048 | 王巍 | 技术支持部 | 3100 | 465 |
| A05241 | 夏远 | 开发部 | 3600 | 540 |
| A07546 | 阮清 | 推广部 | 2200 | 330 |
| A08084 | 林仁 | 综合部 | 3500 | 525 |
| A01048 | 王巍 | 技术支持部 | 3100 | 465 |
=IF(MATCH(A2,$A$2:$A$9,0)=ROW()-1,"","重复提交") ROW()-1是因为含有表头需要将表头减掉
注意：MATCH函数默认返回第一个查找到的数值
2）MATCH相同数目
MATCH(不区分大小写)/COUNT/数组公式
要求：计算两列数据相同的数目
| 数据1 | 数据2 |
|---|---|
| left1 | rand |
| right | Bug |
| mid | thanKs |
| round | right |
| rand | mid |
| Bug | r0und |
| thanks | monny |
| monny | text |
| test | left |
| After | after |
公式：=COUNT(MATCH(A2:A11,B2:B11,0))
思考：如何区分大小写来计算两列数据相同的数目？
EXACT函数(区分大小写)但是不支持一步完成需要增加辅助列
第一步：=OR(EXACT(A19,$B$19:$B$28))*1 两列数据进行匹配如果有重复值就返回 1 否则返回 0
第二步：=SUM(D19:D28) 对上一步的结果值进行求和
6.INDEX函数——根据位置查找内容
INDEX(array,row_num, [column_num])，根据位置查找内容
7.INDEX + MATCH & VLOOKUP + MATCH
| 规格 \ 型号 | 101 | 201 | 301 |
|---|---|---|---|
| A0110 | 78 | 87 | 76 |
| A0111 | 80 | 97 | 84 |
| A0112 | 91 | 75 | 64 |
| A0113 | 88 | 86 | 68 |
| A0114 | 93 | 99 | 83 |
| B1120 | 89 | 69 | 79 |
| B1121 | 91 | 70 | 69 |
| B1122 | 77 | 91 | 81 |
| B1123 | 98 | 77 | 74 |
| 型号 | A0114 |
|---|---|
| 规格 | 201 |
| 价格(INDEX+MATCH) | 99（=INDEX(B2:D10,MATCH(G6,A2:A10,0),MATCH(G7,B1:D1,0))） |
| 价格(VLOOKUP+MATCH) | 99（=VLOOKUP(G6,A2:D10,MATCH(G7,A1:D1,0),0)） |
8.INDIRECT二级下拉列表
| 知识点： |
|---|
| 1、数据>数据验证 |
| 2、F5>定位条件>常量 |
| 3、公式>根据所选内容创建名称 |
| 4、INDIRECT(A2) |
例如：
| 省份 | 城市 |
|---|---|
| 上海市 | 松江区 |
地区表：
| 北京市 | 天津市 | 重庆市 | 上海市 | 河北省 | 山西省 | 内蒙古自治区 | 辽宁省 | 吉林省 | 黑龙江省 | 江苏省 | 浙江省 | 安徽省 | 福建省 | 江西省 | 山东省 | 河南省 | 湖北省 | 湖南省 | 广东省 | 广西省 | 海南省 | 四川省 | 贵州省 | 云南省 | 西藏自治区 | 陕西省 | 甘肃省 | 青海省 | 宁夏回族自治区 | 新疆维吾尔自治区 |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 东城区 | 和平区 | 万州区 | 黄浦区 | 石家庄市 | 武安市 | 呼和浩特市 | 沈阳市 | 长春市 | 哈尔滨市 | 南京市 | 杭州市 | 合肥市 | 福州市 | 南昌市 | 济南市 | 郑州市 | 武汉市 | 长沙市 | 广州市 | 南宁市 | 海口市 | 成都市 | 贵阳市 | 昆明市 | 拉萨市 | 西安市 | 兰州市 | 西宁市 | 银川市 | 乌鲁木齐市 |
| 东城区 | 河东区 | 涪陵区 | 南市区 | 唐山市 | 太原市 | 包头市 | 大连市 | 吉林市 | 齐齐哈尔 | 无锡市 | 宁波市 | 芜湖市 | 厦门市 | 景德镇市 | 青岛市 | 开封市 | 黄石市 | 株洲市 | 韶关市 | 柳州市 | 三亚市 | 自贡市 | 六盘水市 | 东川市 | 昌都地区 | 铜川市 | 嘉峪关市 | 海东地区 | 石嘴山市 | 克拉玛依市 |
| 崇文区 | 河西区 | 渝中区 | 卢湾区 | 秦皇岛市 | 大同市 | 乌海市 | 鞍山市 | 四平市 | 鸡西市 | 徐州市 | 温州市 | 蚌埠市 | 莆田市 | 萍乡市 | 淄博市 | 洛阳市 | 十堰市 | 湘潭市 | 深圳市 | 桂林市 | 五指山市 | 攀枝花市 | 遵义市 | 曲靖市 | 山南地区 | 宝鸡市 | 金昌市 | 果洛藏族自治州 | 吴忠市 | 石河子市 |
| 宣武区 | 南开区 | 大渡口区 | 徐汇区 | 邯郸市 | 阳泉市 | 赤峰市 | 抚顺市 | 辽源市 | 鹤岗市 | 常州市 | 嘉兴市 | 淮南市 | 三明市 | 九江市 | 枣庄市 | 平顶山市 | 沙市市 | 衡阳市 | 珠海市 | 梧州市 | 文昌市 | 泸州市 | 安顺市 | 玉溪市 | 日喀则地 | 咸阳市 | 白银市 | 海北藏族自治州 | 固原市 | 吐鲁番地区 |
| 朝阳区 | 河北区 | 江北区 | 长宁区 | 邢台市 | 长治市 | 通辽市 | 本溪市 | 通化市 | 双鸭山市 | 苏州市 | 湖州市 | 马鞍山市 | 泉州市 | 新余市 | 东营市 | 安阳市 | 宜昌市 | 邵阳市 | 汕头市 | 岑溪市 | 琼海市 | 德阳市 | 保山市 | 那曲地区 | 渭南市 | 天水市 | 海南藏族自治州 | 银南地区 | 哈密地区 | |
| 丰台区 | 红桥区 | 沙坪坝区 | 静安区 | 保定市 | 晋城市 | 丹东市 | 白山市 | 大庆市 | 南通市 | 绍兴市 | 淮北市 | 漳州市 | 鹰潭市 | 烟台市 | 鹤壁市 | 襄樊市 | 岳阳市 | 佛山市 | 北海市 | 万宁市 | 绵阳市 | 昭通市 | 阿里地区 | 延安市 | 武威市 | 海西蒙古族藏族自治州 | 固原地区 | 昌吉回族自治州 | ||
| 石景山区 | 塘沽区 | 九龙坡区 | 普陀区 | 张家口市 | 朔州市 | 锦州市 | 松原市 | 伊春市 | 连云港市 | 金华市 | 铜陵市 | 南平市 | 赣州市 | 潍坊市 | 新乡市 | 鄂州市 | 常德市 | 江门市 | 防城港市 | 儋州市 | 广元市 | 林芝地区 | 汉中市 | 河南蒙古族自治县 | 伊犁哈萨克自治州 | |||||
| 海淀区 | 汉沽区 | 南岸区 | 闸北区 | 承德市 | 晋中市 | 营口市 | 白城市 | 佳木斯市 | 淮安市 | 衢州市 | 安庆市 | 龙岩市 | 吉安市 | 济宁市 | 焦作市 | 荆门市 | 张家界市 | 湛江市 | 东兴市 | 东方市 | 遂宁市 | 江孜地区 | 榆林市 | 黄南藏族自治州 | 塔城地区 | |||||
| 门头沟区 | 大港区 | 北碚区 | 虹口区 | 沧州市 | 运城市 | 阜新市 | 公主岭市 | 七台河市 | 盐城市 | 舟山市 | 黄山市 | 宁德市 | 宜春市 | 泰安市 | 濮阳市 | 孝感市 | 益阳市 | 茂名市 | 钦州市 | 内江市 | 安康市 | 玉树藏族自治州 | 阿勒泰地区 | |||||||
| 房山区 | 东丽区 | 万盛区 | 杨浦区 | 廊坊市 | 忻州市 | 辽阳市 | 梅河口市 | 牡丹江市 | 扬州市 | 台州市 | 滁州市 | 永安市 | 抚州市 | 威海市 | 许昌市 | 荆州市 | 郴州市 | 海口市 | 贵港市 | 乐山市 | 商洛市 | 博尔塔拉蒙古自治州 | ||||||||
| 通州区 | 西青区 | 双桥区 | 吴淞区 | 衡水市 | 临汾市 | 盘锦市 | 集安市 | 黑河市 | 镇江市 | 丽水市 | 阜阳市 | 石狮市 | 上饶市 | 日照市 | 漯河市 | 黄冈市 | 永州市 | 肇庆市 | 桂平市 | 南充市 | 巴音郭楞蒙古自治州 | |||||||||
| 顺义区 | 津南区 | 渝北区 | 闵行区 | 古交市 | 铁岭市 | 桦甸市 | 绥化市 | 泰州市 | 余姚市 | 宿州市 | 莱芜市 | 三门峡市 | 咸宁市 | 怀化市 | 惠州市 | 玉林市 | 万县市 | 阿克苏地区 | ||||||||||||
| 昌平区 | 北辰区 | 巴南区 | 宝山区 | 朝阳市 | 九台山市 | 大兴安岭市 | 宿迁市 | 海宁市 | 巢湖市 | 临沂市 | 南阳市 | 随州市 | 娄底市 | 梅州市 | 北流市 | 眉山市 | 克孜勒苏柯尔克孜自治州 | |||||||||||||
| 大兴区 | 武清区 | 黔江区 | 嘉定区 | 葫芦岛市 | 绥芬河市 | 仪征市 | 兰溪市 | 六安市 | 德州市 | 商丘市 | 湘西土家族自治州 | 汕尾市 | 百色市 | 宜宾市 | 喀什地区 | |||||||||||||||
| 昌平区 | 宝坻区 | 长寿区 | 浦东新区 | 阿城市 | 常熟市 | 瑞安市 | 亳州市 | 聊城市 | 信阳市 | 醴陵市 | 河源市 | 贺州市 | 广安市 | 和田地区 | ||||||||||||||||
| 顺义区 | 宁河县 | 金山区 | 同江市 | 张家港市 | 萧山市 | 池州市 | 滨州市 | 周口市 | 湘乡市 | 阳江市 | 河池市 | 达州市 | 伊犁哈萨克自治州 | |||||||||||||||||
| 通县 | 武清 | 松江区 | 富锦市 | 江阴市 | 江山市 | 宣城市 | 菏泽市 | 驻马店市 | 耒阳市 | 清远市 | 宜州市 | 雅安市 | 伊犁地区 | |||||||||||||||||
| 大兴县 | 静海县 | 青浦区 | 铁力市 | 丹阳市 | 义乌市 | 青州市 | 义马市 | 汨罗市 | 东莞市 | 来宾市 | 巴中市 | 塔城地区 | ||||||||||||||||||
| 房山 | 宝坻县 | 南汇区 | 密山市 | 东台市 | 东阳市 | 龙口市 | 汝州市 | 津市市 | 中山市 | 合山市 | 资阳市 | 阿勒泰地区 | ||||||||||||||||||
| 平谷县 | 蓟县 | 奉贤区 | 兴化市 | 慈溪市 | 曲阜市 | 济源市 | 三亚市 | 崇左市 | 广汉市 | |||||||||||||||||||||
| 怀柔县 | 宜兴市 | 奉化市 | 新泰市 | 禹州市 | 潮州市 | 凭祥市 | 江油市 | |||||||||||||||||||||||
| 密云县 | 诸暨市 | 胶州市 | 卫辉市 | 揭阳市 | 都江堰市 | |||||||||||||||||||||||||
| 延庆县 | 诸城市 | 辉县市 | 云浮市 | |||||||||||||||||||||||||||
| 莱阳市 | ||||||||||||||||||||||||||||||
| 莱州市 | ||||||||||||||||||||||||||||||
| 滕州市 | ||||||||||||||||||||||||||||||
| 文登市 | ||||||||||||||||||||||||||||||
| 荣成市 |
第一步创建省份的下拉列表：点击A2单元格(省份下面的单元格)>数据>数据验证>来源(地区表的表头)
第二步以各省份创建以省份命名的名称：地区表>全选>F5(条件定位)>定位条件选择常量(只选中有内容的单元格)>公式>根据所选内容创建>按照需求选择创建名称的值的位置>
第三步根据省份创建城市的下拉列表：点击B2单元格(城市下面的单元格)>数据>数据验证>来源( INDIRECT(A2) )
9.INDIRECT动态计算
| 知识点： |
|---|
| 1、数据>数据验证 |
| 2、公式>根据所选内容创建名称 |
| 3、SUM+INDIRECT |
例如：
要求：通过下拉列表，动态计算
| 季度 \ 产品 | 第一季 | 第二季 | 第三季 | 第四季 |
|---|---|---|---|---|
| 产品A | 147 | 133 | 149 | 106 |
| 产品B | 74 | 122 | 110 | 82 |
| 产品C | 87 | 89 | 80 | 70 |
| 产品D | 59 | 116 | 82 | 138 |
| 产品E | 124 | 80 | 90 | 136 |
| 产品F | 91 | 85 | 135 | 77 |
| 合计 | 582 | 625 | 646 | 609 |
| 季度 | 第三季（以季度为选项的下拉列表） |
|---|---|
| 合计 | 646（=SUM(INDIRECT(H7))） |
第一步：选择季度以及各产品的值所在的单元格区域(不要选择合计)>公式>根据所选内容创建>名称选择在首行
第二步：求值单元格内=SUM(INDIRECT(H7))
八、Excel可视化大全
1.饼图&复合饼图&圆环图&旭日图
1）饼图
选择数据表>插入>推荐图标>所有图标>饼图
例：
2）复合饼图（子母饼图）
选择数据表>插入>推荐图标>所有图标>饼图>子母饼图
例：
注：双击一块区域可移动绘图区，左边为第一绘图区，右边为第二绘图区
3）圆环图
选择数据表>插入>推荐图标>所有图标>饼图>圆环图
例：
注：想要设置圆环的宽度: 在圆环上右键>选择设置数据系列格式>圆环图圆环大小
4）旭日图
选择数据表>插入>推荐图标>所有图标>旭日图
例：
2.折线图&堆积折线图&百分比堆积折线图
主要用于趋势分析
1）折线图
选择数据表>插入>推荐图标>所有图标>折线图
例：
2）堆积折线图
选择数据表>插入>推荐图标>所有图标>折线图>堆积折线图
例：
3）百分比堆积折线图
选择数据表>插入>推荐图标>所有图标>折线图>堆积折线图
例：
3.条形图&簇状条形图&堆积条形图&百分比堆积条形图
1）簇状条形图
选择数据表>插入>推荐图标>所有图标>条形图>簇状条形图
例：
2）堆积条形图
选择数据表>插入>推荐图标>所有图标>条形图>堆积条形图
例：
3）百分比堆积条形图
选择数据表>插入>推荐图标>所有图标>条形图>百分比堆积条形图
注：如果轴标签与图例的值相反，则点一下可视化图表，然后右键了，点击选择数据，最后点击切换行/列，确定。
例：
4.散点图
用于衡量两个指标的相关性，正性相关（X增大Y也随着增大）和 负性相关（X增大Y随着减小）
先选择一个字段>按住Ctrl键选择另一个字段>插入>推荐的图表>所有图表>XY散点图
添加趋势线：点击图表>点击右上角的加号>勾选趋势线
显示趋势线公式：点击图表>点击右上角的加号>趋势线右边箭头>更多选项>下滑勾选显示公式；显示R平方值
添加数据标签：点击图表>点击右上角的加号>数据标签右边箭头>更多选项>勾选单元格中的值>选中需要引用的值的单元格区域>点击确定
例：
| 用户 | 年龄 | 身份特质 | 行为偏好 | 履约能力 | 人脉关系 | 信用历史 | 信用分 |
|---|---|---|---|---|---|---|---|
| 小明 | 32 | 89 | 90 | 88 | 92 | 95 | 771 |
| 小花 | 28 | 92 | 80 | 92 | 94 | 87 | 751 |
| 小丽 | 23 | 86 | 78 | 94 | 78 | 86 | 738 |
| 小刚 | 24 | 83 | 81 | 83 | 78 | 94 | 726 |
| 小红 | 21 | 85 | 87 | 80 | 81 | 85 | 725 |
5.气泡图(散点图的扩展)
需要三个字段：字段1决定X轴；字段2决定Y轴；字段3决定气泡的大小
选择一个字段>按住Ctrl键选择另外两个字段>插入>推荐的图标>所有图表>XY散点图>气泡图
如果X轴、Y轴和气泡的字段有问题：点击图表>右键>选择数据>点击图例项中的一个选项>编辑>对X轴、Y轴和气泡大小的决定字段进行修改>确定
调节气泡的总体大小：点击气泡>右键>设置数据系列格式>缩放气泡大小为
例：
| 用户 | 年龄 | 身份特质 | 行为偏好 | 履约能力 | 人脉关系 | 信用历史 | 信用分 |
|---|---|---|---|---|---|---|---|
| 小明 | 32 | 89 | 90 | 88 | 92 | 95 | 771 |
| 小花 | 28 | 92 | 80 | 92 | 94 | 87 | 751 |
| 小丽 | 23 | 86 | 78 | 94 | 78 | 86 | 738 |
| 小刚 | 24 | 83 | 81 | 83 | 78 | 94 | 726 |
| 小红 | 21 | 85 | 87 | 80 | 81 | 50 | 725 |
6.雷达图
选择数据表>插入>推荐的图表>所有图表>雷达图
例：
| 用户 | 身份特质 | 行为偏好 | 履约能力 | 人脉关系 | 信用历史 |
|---|---|---|---|---|---|
| 小明 | 89 | 90 | 88 | 92 | 95 |
| 小花 | 92 | 80 | 92 | 94 | 87 |
7.帕累托图(二八定律的分析)，又叫排列图
1）直接实现
选择数据区域>插入>推荐的图表>所有图表>直方图>排列图
例：
| 城市 | 销售额 |
|---|---|
| 北京 | ¥67,906 |
| 成都 | ¥82,084 |
| 大连 | ¥11,450 |
| 广州 | ¥16,776 |
| 杭州 | ¥17,060 |
| 昆明 | ¥23,912 |
| 南京 | ¥38,784 |
| 上海 | ¥90,512 |
| 深圳 | ¥9,884 |
| 沈阳 | ¥51,614 |
| 天津 | ¥12,434 |
| 重庆 | ¥37,746 |
2）组合图实现帕累托图
让表按照值降序排列>为表添加一个累计百分比字段>选中表>插入>推荐的图表>所有图表>组合图>值用簇状柱形图>累计百分比用折线图（或带数据标记的折线图）>累计百分比勾选次坐标轴（次坐标轴指右边的轴，主坐标轴指左边的轴）>确定
为折线添加数据标签：点击折线>点击右上角加号>勾选数据标签
为折线上的单独某个点添加数据标签：点击折线>再点击某个点>右上角加号>勾选数据标签
例：
| 城市 | 销售额 | 累计百分比 |
|---|---|---|
| 上海 | ¥90,512 | 20% |
| 成都 | ¥82,084 | 38% |
| 北京 | ¥67,906 | 52% |
| 沈阳 | ¥51,614 | 63% |
| 南京 | ¥38,784 | 72% |
| 重庆 | ¥37,746 | 80% |
| 昆明 | ¥23,912 | 85% |
| 杭州 | ¥17,060 | 89% |
| 广州 | ¥16,776 | 93% |
| 天津 | ¥12,434 | 95% |
| 大连 | ¥11,450 | 98% |
| 深圳 | ¥9,884 | 100% |
8.树状图&直方图(频数统计)
1）树状图
选中数据区域>插入>推荐的图表>所有图表>树状图
例：
2）直方图
主要用于做频数统计
只需要一个指标
选择一个数值字段>插入>推荐的图表>所有图表>直方图
设置分组节点：点击下方的坐标轴>右键>设置坐标轴格式>箱的宽度>填入需要设置的值
溢出箱：大于设定值的归为一组
下溢箱：小于设定值的归为一组
例：
9.箱线图（集中趋势和离散程度）又称盒须图、箱型图
观测数据的集中趋势和离散程度
选中数据>插入>推荐的图表>所有图表>箱型图
IQR:四分位距,IQR=Q3-Q1
上限值：Q3+1.5IQR，最大不超过最大值 下限值：Q1-1.5IQR，最小不低于最小值
求分位数：
1）Q3=QUARTILE.INC(数值单元格区域，3) QUARTILE.INC包含零；QUARTILE.EXC不含零，通常用包含零的。其中第二个参数值0为求最小值，1为求下四分位点(第25个百分位的值)，2为求中值，3为求上四分位点(第75个百分位的值)，4为求最大值
2）如果是求第70%的值：=PERCENTILE.INC(数值单元格区域，0.7) PERCENTILE.INC包含零；PERCENTILE.EXC不含零，通常使用包含零的。第二个参数可以取0~1之间。
例：
10.柱形图和折线图 & 散点图和折线图——组合图
1）柱形图和折线图
按住Ctrl键选中需要的三个字段>插入>推荐的图表>所有图表>组合图>一个选择簇状柱形图一个选择折线图(或带数据标记的折线图)
例：
| 地区 | 销售额 | 目标值 | 目标达成率 |
|---|---|---|---|
| 北方 | ¥143,404 | ¥120,000 | 119.50% |
| 江浙沪 | ¥146,356 | ¥150,000 | 97.57% |
| 南方 | ¥26,660 | ¥30,000 | 88.87% |
| 西南 | ¥143,742 | ¥150,000 | 95.83% |
按住Ctrl键选中地区、销售额、目标达成率三个字段>插入>推荐的图表>所有图表>组合图>销售额选择簇状柱形图、目标达成率选择带数据标记的折线图
2）散点图和折线图
选中数据表>插入>推荐的图表>所有图表>组合图>对数据字段分别按需求选择散点图或者折线图
例：
| 姓名 | 成绩 | 及格线 | 优秀线 |
|---|---|---|---|
| 张一 | 55 | 60 | 85 |
| 王二 | 88 | 60 | 85 |
| 陈三 | 62 | 60 | 85 |
| 李四 | 78 | 60 | 85 |
| 五毛 | 99 | 60 | 85 |
| 胡六 | 45 | 60 | 85 |
选中数据表>插入>推荐的图表>所有图表>组合图>成绩选择散点图、及格线和优秀线选择折线图
11.子弹图
知识点：组合图，簇状柱形图、系列重叠，填充、边框，粗细、间隙宽度
1）创建图表：选中数据表>插入>推荐的图表>所有图表>组合图>全部选择簇状柱形图分析哪几个数据用同一个轴，用主坐标轴和次坐标轴进行区别
2）对柱形进行重叠操作：点击一个柱形>右键>设置数据系列格式>系列重叠的值拉到100%
3）修改柱形的宽度：点击需要设置的一个柱形>右键>设置数据点格式>设置间隙宽度(值越大柱形越细，值越小柱形越粗)
4）修改柱形的填充颜色：选中需要设置的一个柱形>填充>选择需要的颜色
5）设置柱形的边框颜色：选中要设置的一个柱形>边框>选择需要的颜色>粗细>选择需要的边框宽度
例如：
| 地区 | 销售额 | 平均值 | 目标值 |
|---|---|---|---|
| 北方 | ¥143,404 | ¥115,041 | ¥120,000 |
| 江浙沪 | ¥146,356 | ¥115,041 | ¥150,000 |
| 南方 | ¥26,660 | ¥115,041 | ¥30,000 |
| 西南 | ¥143,742 | ¥115,041 | ¥150,000 |
12.旋风图
旋风图又叫蝴蝶图或人口金字塔图
1）创建图表：选择数据区域(需要两个数值字段和一个名称字段)>插入>推荐的图表>所有图表>组合图>两个字段都选择簇状条形图>其中一个勾选次坐标轴
2）调节刻度：选中其中一个坐标轴>右键>设置坐标轴的格式>求出数据的最大值，并将其设置为刻度的最大值，其负值设置为刻度的最小值，使刻度对称(例如：7000，-7000)>点击另一个坐标轴设置其刻度与上一个保持一致>删除其中一个坐标轴>此时图标已经对称
3）逆序刻度值：选中一个坐标轴>右键>设置坐标轴格式>向下托，勾选逆序刻度值
4）移动名称坐标轴：点击坐标轴>右键>设置坐标轴格式>向下托，点击标签，标签位置设为低或者高按需求设置
5）设置坐标轴的负值：选中坐标轴>设置坐标轴的格式>数字>类别选择自定义>格式代码：[>0]0;[<0]0;>添加>此时坐标轴就没有负数了
例：
| 门店 | A产品 | B产品 |
|---|---|---|
| 1分店 | ¥ 7,000.00 | ¥ 5,600.00 |
| 2分店 | ¥ 5,270.00 | ¥ 3,400.00 |
| 3分店 | ¥ 2,430.00 | ¥ 231.00 |
| 4分店 | ¥ 3,533.00 | ¥ 1,230.00 |
| 5分店 | ¥ 300.00 | ¥ 603.00 |
| 6分店 | ¥ 3,200.00 | ¥ 4,500.00 |
| 7分店 | ¥ 420.00 | ¥ 1,000.00 |
13.矩阵图又叫四象限图
1）创建图表：选择两个数值字段>插入推荐图表>所有图表>XY散点图>散点图选择第二个
2）设置坐标轴：选择X坐标轴>右键>设置坐标轴的格式>设置刻度的最大值最小值>纵坐标轴交叉选中坐标轴值>设为X坐标轴字段的平均值>点击Y坐标轴>设置坐标轴刻度的最大最小值>横坐标轴交叉选中坐标轴值>设置为Y坐标轴字段的平均值
3）添加标签：选中图表>点击右上角的加号>数据标签>更多选项>勾选单元格中的值>选取单元格区域>确定
例：
| 产品 | 满意度 | 利润率 |
|---|---|---|
| 桌子 | 4 | 1.0% |
| 信封 | 2 | 8.5% |
| 装订机 | 7 | 4.0% |
| 收纳具 | 9 | 10.1% |
| 设备 | 1 | 4.0% |
| 椅子 | 10 | 12.0% |
| 纸张 | 8 | 9.4% |
| 书架 | 2 | 13.0% |
| 复印机 | 1 | 9.4% |
| 配件 | 6 | 4.0% |
平均值： 满意度：5 利润率：7.5%
14.瀑布图(用于做对比分析)
选中数据区域>插入>推荐的图表>所有图表>瀑布图
例如：
| 城市 | 销售占比 |
|---|---|
| 上海 | 19.67% |
| 成都 | 17.84% |
| 北京 | 14.76% |
| 沈阳 | 11.22% |
| 南京 | 8.43% |
| 重庆 | 8.20% |
| 昆明 | 5.20% |
| 杭州 | 3.71% |
| 广州 | 3.65% |
| 天津 | 2.70% |
| 大连 | 2.49% |
| 深圳 | 2.15% |
15.漏斗图(流程转化分析)
选中名称字段和转化率字段>插入>推荐的图表>所有图表>漏斗图
例如：
| 阶段 | 数量 | 全流程转化率 |
|---|---|---|
| 首页访问数 | 10000 | 100% |
| 产品页访问数 | 8000 | 80% |
| 放入购物车数 | 6000 | 60% |
| 生成订单数 | 4000 | 40% |
| 支付成功数 | 2000 | 20% |
| 交易成功数 | 1000 | 10% |
漏斗图原理：
九、动态图表和数据透视图
1.动态图表
例如：
| 月份 | 北方 | 江浙沪 | 南方 | 西南 |
|---|---|---|---|---|
| 2016年1月 | ¥21,016 | ¥27,400 | ¥4,528 | ¥39,074 |
| 2016年2月 | ¥27,440 | ¥22,052 | ¥4,158 | ¥27,250 |
| 2016年3月 | ¥34,666 | ¥34,048 | ¥5,770 | ¥25,634 |
| 2016年4月 | ¥31,866 | ¥23,490 | ¥4,338 | ¥22,546 |
| 2016年5月 | ¥28,416 | ¥39,366 | ¥7,866 | ¥29,238 |
要求：根据选取的月份创建显示当月各地区情况的图表
1）创建数据表：
| 月份 | 北方 | 江浙沪 | 南方 | 西南 |
|---|---|---|---|---|
2）设置月份的下拉列表：
选中月份下的空白单元格>数据>数据验证>允许设为序列>来源选择原数据表中缘分的值区域>确定
3）根据月份获取每个区域的值：
（1）OFFSET + MATCH：=OFFSET(A1,MATCH(A9,A2:A6,0),1,1,4)
（2）VLOOKUP + MATCH：=VLOOKUP($A$10,$A$2:$E$6,MATCH(B8,$A$1:$E$1,0),0)
| 月份 | 北方 | 江浙沪 | 南方 | 西南 |
|---|---|---|---|---|
| 2016年1月 | ¥21,016 | ¥27,400 | ¥4,528 | ¥39,074 |
4）创建图表：选中创建的数据表>插入>推荐的图表>簇状柱形图
2.数据透视图
1）设计表：选中数据表>插入>表格>确定
2）创建数据透视表：选中数据表>插入>数据透视表>可以选择新建工作表或者现有工作表>确定
3）设计数据透视表：选中数据透视表>右键>显示字段列表>根据需要设计行列和值
4）创建数据透视图：选中数据透视表中的数据>插入>推荐的图表>所有图表>选择需要的图表
5）删除字段按钮：选中数据透视图>数据透视图分析>字段按钮
注意：数据透视表可以连接数据透视图，但是有一些特殊的图并不支持，例如瀑布图
十、数据透视表
1.基本操作
1）创建设计数据透视表
（1）创建数据透视表：选中数据表>插入>数据透视表>可以选择新建工作表或者现有工作表>确定
（2）设计数据透视表：选中数据透视表>右键>显示字段列表>根据需要设计行列和值
2）显示字段列表：点击数据透视表>右键>显示字段列表
3）数据透视表的分析与设计：点击数据透视表>数据透视表分析、设计
4）移动数据透视表字段：（1）鼠标拖放；（2）右键>移动
2.报表布局
点击数据透视表>设计>报表布局
1）主要的布局形式：
注意：表格形式的分类汇总只能显示在底部
2）对部门合并居中：选中数据透视表>右键>数据透视表选项>布局，勾选合并且居中排列带标签的单元格
3）去除加号/减号的按钮：选中数据透视表>数据透视表分析>+/-按钮
3.值字段设置
1）数字格式：点击数据透视表的任何一个值>右键>值字段设置>数据格式 
2）值字段设置：双击字段>自定义名称、计算类型
3）值显示方式：点击修改字段下的一个值>右键>值显示方式
4.值汇总依据
1）右键>值汇总依据(计算类型/计算逻辑,如求和/最大值最小值/平均值等)
2）值字段设置>值字段汇总方式>计算类型
5.分类汇总
点击行标签>右键>字段设置>分类汇总和筛选>自定义
6.值显示方式
1）环比&同比&定比
同比:今年本月与去年本月的对比 环比:本月与上月的对比 定比:1-12月固定与1月的对比
右键>值显示方式(数据透视计算后结果值的二次计算)>差异百分比
(1)环比增长率：差异百分比>月份>上一个
(2)同比增长率：差异百分比>年份>上一个
(3)定比增长率：差异百分比>月份>1
2）按某字段汇总&按某字段汇总的百分比&升序降序排列&总计百分比
（1）按某字段汇总：按指定字段方向累计计算
右键>值显示方式>按某一字段汇总
（2）按某字段汇总的百分比：按指定字段方向累计百分比计算
右键>值显示方式>按某字段汇总的百分比
（3）升序降序排列
升序排序：按计算的结果值升序排列生成序号 降序排序：按计算的结果值降序排列生成序号
右键>值显示方式>升序排序/降序排序
（4）总计百分比
a.总计百分比：以总计为基数计算百分比
b.列汇总百分比：以列总计为基数计算百分比
c.行汇总百分比：以行总计为基数计算百分比
d.父行汇总百分比：
e.父列汇总百分比
f.父级汇总百分比：
右键>值显示方式>父级汇总百分比
3）差异&百分比&差异百分比&指数
（1）差异：两个值相减差，比较值-基准值
右键>值显示方式>差异
（2）百分比：比较值/基准值
右键>值显示方式>百分比
（3）差异百分比：(比较值-基准值)/基准值
右键>值显示方式>差异百分比
（4）指数
指数计算逻辑：（单元格值 * 总计的值）/ (行汇总的值 * 列汇总的值)
右键>值显示方式>指数
7.切片器与日程表
1）切片器
选中透视表>数据透视表分析>插入切片器>选择筛选的字段（可选择多个也可以选择单个）
2）日程表
选中透视表>数据透视表分析>插入日程表>选择字段
3）一个切片器控制多个透视表
多个透视表必须是由一个数据源创建的
（1）创建切片器：点击一个数据透视表>数据透视表分析>插入切片器>选择一个筛选字段>确定
（2）切片器连接多个报表：点击切片器>切片器>报表链接>选择要链接的数据透视表(可以多个)>确定
8.创建组合
1）针对日期
（1）鼠标选中数据透视表的行标签>右键>创建组合>选择要组合的字段>确定
（2）设置日期每隔固定天数为一组
鼠标选中数据透视表的行标签>右键>创建组合>只能选择“日”字段>天数设置需要固定分组的天数
2）针对文本
鼠标选中数据透视表中需要分为一组的行标签>右键>创建组合
3）针对数值
鼠标选中数据透视表的数值行标签>右键>创建组合>起始值，终止值，步长>确定
9.动态更新数据源
1）选中数据透视表>数据透视表分析>更改数据源
2）当向原有数据表尾部追加数据时，数据透视表中的数据不会更新 解决方法： 方法1：（不推荐） （1）公式>定义名称 data：OFFSET($A$1,0,0,COUNTA($A:$A),COUNTA($1:$1)) （2）分析>更改数据源>data （3）右键>刷新
方法2： （1）选中数据表>Ctrl+T或者（插入）>创建表>命名，如：data （2）选中数据透视表>数据透视表分析>更改数据源>将 表/区域设置为创建的表的名称，如：data （3）选中数据透视表>右键>刷新
10.计算字段&计算项
1）计算字段
选中数据透视表>数据透视表分析>字段、项目和集>计算字段>给插入字段起名>按照需求填写公式>添加>确定
修改和删除步骤近似
2）计算项
数据表中的表头叫字段，表头下面的值叫项
选中一个数据表中需要计算的项的名称单元格>数据透视表分析>字段、项目和集>计算项>起名>填写计算公式>添加>确定
删除和修改的步骤近似
11.GETPIVOTDATA函数
知识点：GETPIVOTDATA，快速统计汇总
=GETPIVOTDATA(要获取的数据透视表中的信息名称，透视表位置，筛选字段1，筛选值1 [，筛选字段2，筛选值2] )
例：根据年份、学期和年级快速获取数据透视表中对应的平均成绩
| 年份 | 2011 |
|---|---|
| 学期 | 秋季学期 |
| 年级 | 一年级 |
| 平均成绩 | 67.3（=GETPIVOTDATA("平均成绩",$H$10,"年份",I4,"学期",I5,"年级",I6)） |
十一、杜邦分析仪
杜邦分析仪:指标拆解的分析方法
基础数据：
| 月份 | 1月 | 2月 | 3月 | 4月 | 5月 | 6月 |
|---|---|---|---|---|---|---|
| 销售收入 | 5,000.0 | 5,050.0 | 5,100.0 | 5,150.0 | 5,200.0 | 5,250.0 |
| 销售成本 | 4,400.0 | 4,393.5 | 4,641.0 | 4,789.5 | 4,758.0 | 4,830.0 |
| 销售毛利 | 600.0 | 656.5 | 459.0 | 360.5 | 442.0 | 420.0 |
| 净利润 | 250.0 | 277.8 | 183.6 | 257.5 | 286.0 | 189.0 |
| 费用总额 | 350.0 | 378.8 | 275.4 | 103.0 | 156.0 | 231.0 |
| 其中：广告费 | ||||||
| 销售费用 | 250.0 | 277.8 | 173.4 | - | 52.0 | 126.0 |
| 管理费用 | 100.0 | 101.0 | 102.0 | 103.0 | 104.0 | 105.0 |
| 资产总额 | 32,000.0 | 32,500.0 | 33,000.0 | 33,500.0 | 34,000.0 | 34,500.0 |
| 负债总额 | 20,000.0 | 20,500.0 | 21,000.0 | 21,500.0 | 22,000.0 | 22,500.0 |
| 销售净利润率 | 5.0% | 5.5% | 3.6% | 5.0% | 5.5% | 3.6% |
| 销售毛利率 | 12.0% | 13.0% | 9.0% | 7.0% | 8.5% | 8.0% |
| 费用率 | 7.0% | 7.5% | 5.4% | 2.0% | 3.0% | 4.4% |
| 其中：媒体费用率 | 0.0% | 0.0% | 0.0% | 0.0% | 0.0% | 0.0% |
| 营业费用率 | 5.0% | 5.5% | 3.4% | 0.0% | 1.0% | 2.4% |
| 管理费用率 | 2.0% | 2.0% | 2.0% | 2.0% | 2.0% | 2.0% |
| 资产负债率 | 70.0% | 80.0% | 85.0% | 83.0% | 81.0% | 79.0% |
| 其他费用 | ||||||
| 其他利润 | ||||||
| 所得税 | ||||||
| 流动资产 | 18,000.0 | 19,500.0 | 21,000.0 | 22,500.0 | 24,000.0 | 25,500.0 |
| 现金有价证券 | 8,000.0 | 8,500.0 | 9,000.0 | 9,500.0 | 10,000.0 | 10,500.0 |
| 应收账款 | 3,000.0 | 3,500.0 | 4,000.0 | 4,500.0 | 5,000.0 | 5,500.0 |
| 存货 | 7,000.0 | 7,500.0 | 8,000.0 | 8,500.0 | 9,000.0 | 9,500.0 |
全部成本 = 销售成本 + 销售费用 + 管理费用 + 其他费用 净利润 = 销售收入 - 全部成本 + 其他利润 - 所得税 销售净利率 = 净利润 / 销售收入 其他流动资产 = 流动资产 - 现金有价证券 - 应收账款 - 存货 长期资产 = 资产总额 - 流动资产 资产周转率 = 销售收入 / 资产总额 资产净利率 = 销售净利率 * 资产周转率 权益乘数 = 1/(1 - 资产负债率) 权益净利率 = 资产净利率 * 权益乘数
1）整理基础指标
| 指标 | 增长率 | 2月 | 1月 |
|---|---|---|---|
| 销售成本 | -0.1%（=IFERROR(L2/M2-1,0)） | 4394（=INDEX($B$2:$G$30,MATCH($J2,$A$2:$A$30,0),MATCH(L$1,$B$1:$G$1,0))） | 4400（=INDEX($B$2:$G$30,MATCH($J2,$A$2:$A$30,0),MATCH(M$1,$B$1:$G$1,0))） |
| 销售费用 | 11.1% | 277.8 | 250 |
| 管理费用 | 1.0% | 101 | 100 |
| 其他费用 | 0.0% | 0 | 0 |
| 销售收入 | 1.0% | 5050 | 5000 |
| 其他利润 | 0.0% | 0 | 0 |
| 所得税 | 0.0% | 0 | 0 |
| 现金有价证券 | 6.3% | 8500 | 8000 |
| 应收账款 | 16.7% | 3500 | 3000 |
| 存货 | 7.1% | 7500 | 7000 |
| 资产总额 | 1.6% | 32500 | 32000 |
| 流动资产 | 8.3% | 19500 | 18000 |
| 资产负债率 | 14.3% | 0.8 | 0.7 |
2）整理结果指标
| 全部成本 | 0.5% | 4772 | 4750 |
|---|---|---|---|
| 净利润 | 11.1%（=IFERROR(L16/M16-1,0)） | 277.8 | 250 |
| 销售净利率 | 10.0% | 5.5% | 5.0% |
| 其他流动资产 | 0.0% | 0 | 0 |
| 长期资产 | -7.1% | 13000 | 14000 |
| 资产周转率 | -0.6% | 15.5% | 15.6% |
| 资产净利率 | 9.4% | 0.9% | 0.8% |
| 权益乘数 | 50.0% | 5.00 | 3.33 |
| 权益净利率 | 64.1% | 4.3% | 2.6% |
3）创建结构图
通过选择性粘贴>带链接的图片
插入大括号：插入>插图
