CDA学习笔记--EXCEL篇

常用快捷键

键盘快捷键	功能
CTRL+A	1.选定整张工作表（光标在工作表数据区域时） 2.输入公式（光标在工作表编辑栏内时）
CTRL+B	字体加粗切换
CTRL+SHIFT+箭头	将选定区域扩展到与活动单元格在同一列或同一行的最后一个非空单元格
CTRL+ENTER	多个单元格输入相同内容
TAB	光标横向移动输入数据
ALT+ENTER	在单元格中换行
F4	1.重复前一次操作 2.绝对引用和相对引用的切换
SHIFT+SPACE	选定整行
Ctrl+F	查找
Ctrl+H	替换
Ctrl+G F5	定位

详细快捷键大全见：https://www.oh100.com/kaoshi/bangong/540895.html

基本操作

多个窗口查看工作表

视图--》新建窗口

冻结窗口

视图--》冻结窗口

1.想要冻结多行，将光标移到下一行的行标上，点击冻结
2.想要冻结多行+多列，将光标移到（列标+1行标+1）的单元格，点击冻结

保护特定单元格数据

1.全选单元格--》右键--》设置单元格格式--》保护--》取消勾选锁定
2.选中想要保护的单元格--》右键--》设置单元格格式--》保护--》勾选锁定
3，审阅--》保护工作表--》设置密码

自定义序列设置

文件--》选项--》高级--》常规--》自定义序列

条件格式标识重点信息

例一：如何快速标识单科成绩90份以上或60分一下的单元格？
答：开始--》条件格式--》突出显示单元格格式--》大于/小于

例二：如何快速标识平均成绩前三名的单元格？
答：开始--》条件格式--》突出显示单元格格式--》其他规则--》仅对排名考前或靠后的数值设置格式

单元格增加批注两种方式

方法一：选中单元格--》右键--》添加批注
方法二：选中单元格--》数据--》数据验证--》数据验证--》输入信息

工作表创建超链接

插入任意图标--》右键--》编辑超链接--》本文档中的位置--》键入单元格引用

修改文本型数据

问题：数据导入或者录入时，有文本型字符造成无法汇总
方法一：选择性黏贴——乘或除
方法二：运用函数Value
方法三：表格出现的浮动选项直接修改

数据验证

录入数据验证：某一列单价最低为70，最高单价为420
答：数据--》数据验证--》允许（整数）：数据（介于）：最小值（70）：最大值（420）

设置下拉菜单
答：选中列--》数据验证--》允许（序列）：来源（选中表格中输入好的序列）

分类汇总

数据--》分类汇总--》分类字段：汇总方式：选定汇总项

高级筛选功能多条件自定义筛选数据

例一：如何筛选出东南地区1月份销售明细？

例二：一次筛选出东南地区的所有销售明细，以及所有地区的1月份的销售明细？

多个工作表数据快速汇总

数据–》数据工具–》合并计算

透视表

背景资料：大华公司在重庆的渝北区和江北区共有九家门店，表格为各个区域和门店的2018年定额明细
给出的字段：销售区域；门店；订购日期；订单号；订购额
透视表创建方法：插入–》数据透视表–》选定表格区域：放置数据透视表位置

例一：每个门店的订购是额多少

例二：特定地区的订购额

例三：查看某期间(月、季度)门店的业绩
数据透视表分析–》选项–》显示–》经典透视表布局

例四：特定订购额的明细
双击想要查看的特定订购额，进行跳转
例五：按订购金额3%计算，每个门店应得多少提成
在例一的基础上，点击：数据透视表分析–》字段、项目和集–》计算字段

常用函数应用

IF

IF(判断的条件,符合条件时的结果,不符合条件时的结果)
对满足条件的数据进行处理,条件满足则输出结果1,不满足则输出结果2
例：如果数值大于100，输出大于100，否则输出小于等于100
if(单元格>100,“大于100”,“小于等于100”)

SUM

SUMIF(想要判断条件的单元格,判断的条件,进行累加的单元格)
对报表范围中符合指定条件的值求和
例：计算公司2月的销售额
sumif(月份列,“2月”,销售额列)
sumifs(进行累加的单元格，条件1的单元格,判断的条件,条件2的单元格,判断的条件)
快速对多条件单元格求和
例：计算公司A产品2月的销售额
sumifs(销售额列,月份列,“2月”,产品列,“A”)
SUMPRODUCT(array1,[array2], [array3], …)
Array1：必需。其相应元素需要进行相乘并求和的第一个数组参数。
Array2, array3,…：可选。 2 到 255 个数组参数，其相应元素需要进行相乘并求和。
例：根据各产品单价和销售量快速统计总销售额
SUMPRODUCT(销售数量列,销售单价列)

COUNT

count(value1,value2, …)
对给定数据集合或者单元格区域中数据的个数进行计数
参数：
value1 是必需参数。 要计算其中数字的个数的第一项、单元格引用或区域。
value2, … 为可选参数。 要计算其中数字的个数的其他项、单元格引用或区域，最多可包含 255 个。
COUNTA(value1, [value2], …)
返回参数列表中非空的单元格个数
参数：
COUNTA 函数语法具有下列参数：
value1必需参数，表示要计数的值的第一个参数。
value2, …可选参数，表示要计数的值的其他参数，最多可包含 255 个参数。
countif（range，criteria）
对指定区域中符合指定条件的单元格计数的一个函数
参数：
range 要计算其中非空单元格数目的区域
criteria 以数字、表达式或文本形式定义的条件
countifs(criteria_range1,criteria1,criteria_range2,criteria2,…)
计算多个区域中满足给定条件的单元格的个数
参数：
criteria_range1为第一个需要计算其中满足某个条件的单元格数目的单元格区域（简称条件区域），criteria1为第一个区域中将被计算在内的条件（简称条件），其形式可以为数字、表达式或文本。例如，条件可以表示为 48、“48”、“>48” 、 “广州” 或 A3；
同理，criteria_range2为第二个条件区域，criteria2为第二个条件，依次类推。最终结果为多个区域中满足所有条件的单元格个数。

IFERROR

IFERROR(value,value_if_error)
如果公式的计算结果为错误，则返回您指定的值；否则将返回公式的结果
例：想要让分母为零的结果默认为1000
IFERROR(0/16,1000)

ROUND

ROUND(需要处理的数字,按此位数进行标准四舍五入)
ROUND(78.888888,2)–>78.89
ROUNDDOWN(需要处理的数字,按此位数强制保留)
ROUND(78.888888,2)–>78.88
ROUNDIP(需要处理的数字,按此位数强制进位)
ROUND(78.88111,2)–>78.89

SUBTOTAL

SUBTOTAL(function_num,ref1,ref2, …)
Function_num 为 1 到 11（包含隐藏值）或 101 到 111（忽略隐藏值）之间的数字，指定使用何种函数在列表中进行分类汇总计算。
ref1,ref2为进行汇总数据的单元格区域

Function_num
1 到 11（包含隐藏值	1	AVERAGE（算术平均值）
	2	COUNT（数值个数）
	3	COUNTA（非真空单元格数量）
	4	MAX（最大值）
	5	MIN（最小值）
	6	PRODUCT（括号内所有数据的乘积）
	7	STDEV（估算样本的标准偏差）
	8	STDEVP（返回整个样本总体的标准偏差）
	9	SUM（求和）
	10	VAR（计算基于给定样本的方差）
	11	VARP（计算基于整个样本总体的方差）
101 到 111（忽略隐藏值）	101	AVERAGE（算术平均值）
	102	COUNT 数字的个数
	103	COUNTA 非空的个数
	104	MAX 最大值
	105	MIN（最小值）
	106	PRODUCT乘积
	107	STDEV（估算样本的标准偏差）
	108	STDEVP（返回整个样本总体的标准偏差）
	109	SUM（求和）
	110	VAR（计算基于给定样本的方差）
	111	VARP（计算基于整个样本总体的方差）

特点
1.如果在 ref1, ref2,… 中有其他的分类汇总（嵌套分类汇总），将忽略这些嵌套分类汇总，以避免重复计算。也就是在数据区域中有SUBTOTAL获得的结果将被忽略！
2.当 function_num 为从 1 到 11 的常数时，SUBTOTAL 函数将包括通过“格式”菜单的“行”子菜单下面的“隐藏”命令所隐藏的行中的值。当您要分类汇总列表中的隐藏和非隐藏值时，请使用这些常数。当 function_num 为从 101 到 111 的常数时，SUBTOTAL 函数将忽略通过“格式”菜单的“行”子菜单下面的“隐藏”命令所隐藏的行中的值。当您只分类汇总列表中的非隐藏数字时，使用这些常数。 但不论使用什么 function_num 值，SUBTOTAL 函数都会忽略任何不包括在筛选结果中的行。 而SUBTOTAL 函数不适用于数据行或水平区域。隐藏某一列不影响分类汇总。但是隐藏分类汇总的垂直区域中的某一行就会对其产生影响。
3.可以代替上面说的11种函数，当有上面说的两种特点情况时，就可以使用SUBTOTAL来完成。

截取字符类（mid,left,right）

MID (要提取的字符串或者单元格引用, 文字的第一个开始位置,向右截取的字符长度 )
返回文本字符串中从指定位置开始的指定数目的字符
例：从身份证提取出生年月
mid(身份证号,7,8)
LEFT(要提取的字符串或单元格引用，截取的长度)
函数执行成功时返回string字符串左边n个字符，发生错误时返回空字符串（“”）。如果任何参数的值为NULL，Left()函数返回NULL。如果n的值大于string字符串的长度，那么Left()函数返回整个string字符串，但并不增加其它字符。
例：从身份证号提取当事人户口所在地
left(身份证号,6)
RIGHT(要提取的字符串或单元格引用，截取的长度)
从字符串右端取指定个数字符
例：从“上海市浦东新区”中提取“浦东新区”
right(上海市浦东新区,4)

lookup类

LOOKUP(查找对象，查找区域，结果区域)
第一参数：查找值
第二参数：查找的数据区域，必须是一行或者一列
第三参数：返回结果的区域，必须是一行或者一列
注意事项
1.第二参数中的值必须升序排序否则可能找不到结果
2.如果找不到准确的结果，lookup会返回小于或等于查找值的最大值。
3.如果查找值小于查找区域的最小值，lookup则会返回 #N/A 错误。
4.第二与第三参数必须一一对应
例：通过姓名来查找成绩
LOOKUP(姓名,姓名列,成绩列)
VLOOKUP(查找对象，查找范围，范围中结果的列号，是否模糊匹配)
按列查找（纵向查找），找出与查找对象相匹配的其他列的值。
例：找学生编号为B3的成绩
VLOOKUP(“B3”,从编号到成绩的完整区域,成绩所在第几列的数字,FALSE)
HLOOKUP(查找对象，查找范围，范围中结果的行号，是否模糊匹配)
在表格或数值数组的首行查找指定的数值，并返回表格或数组中指定行的同一列的数值

MATCH()与INDEX()

match(需要在数据表中查找的数值,更排索的单元格区域,是否模糊匹配)
返回指定数值在指定数组区域中的位置
例：一列有1月，2月，3月，4月，5月，6月，想要精准查找查找4月在本列的位置
match(“4月”,单元格区域,1)
index(单元格区域，行序号，列序号)
返回表或区域中的值或值的引用
混合使用

时间函数

NOW()
生成一个日期加时间的时刻，并且在其余单元格输入数据后，这个时刻会自动刷新，也就是说它是变动的，不是固定的。
DATE(年份,月份,天数）
构建一个指定的日期（注意：不能生成1900年1月1日之前的日期）
YEAR(需要提取的日期)、MONTH(需要提取的日期)、DAY(需要提取的日期）
提取日期中的年、月、日
EOMONTH（日期，参数类型）
返回指定月份之前或者之后的最后一天的日期

参数
0	返回当月的最后一天的日期
1	返回下个月的最后一天的日期
2	返回下下个月的最后一天的日期
-1	返回上个月的最后一天的日期

DATEDIF（开始日期，结束日期，计算类型）
计算两个日期之间的差值

计算类型
Y	计算年份的差值
M	计算月份差值
D	计算天数差值
MD	计算同月间隔天数。 忽略日期中的月份和年份
YD	计算同年间隔天数。忽略日期中的年份
YM	计算间隔月数。忽略日期中年份

WORKDAY(开始日期, days, [需要从工作日历中排除的一个或多个日期])
返回在某日期（起始日期）之前或之后、与该日期相隔指定工作日的某一日期的日期值

NUMBERSTRING

NUMBERSTRING(VALUE,TYPE)
将小写数字转换成中文大写数字

结果类型
1	NumberString(1234567890,1) 返回结果：一十二亿三千四百五十六万七千八百九十
2	NumberString(1234567890,2) 返回结果：壹拾贰亿叁仟肆佰伍拾陆万柒仟捌佰玖拾
3	NumberString(1234567890,3) 返回结果：一二三四五六七八九〇

N(可用作长公式注释)

N（参数）
将参数转换为数值型

错误值

#####!

原因：如果单元格所含的数字、日期或时间比单元格宽，或者单元格的日期时间公式产生了一个负值，就会产生#####!错误。
解决方法：如果单元格所含的数字、日期或时间比单元格宽，可以通过拖动列表之间的宽度来修改列宽。

#VALUE!

原因：在需要数字或逻辑值时输入了文本，Excel不能将文本转换为正确的数据类型。
解决方法：确认公式或函数所需的运算符或参数正确，并且公式引用的单元格中包含有效的数值。例如：如果单元格A1包含一个数字，单元格A2包含文本"学籍"，则公式"=A1+A2"将返回错误值#VALUE!。

#DIV/0!

原因：在公式中，除数使用了指向空单元格或包含零值单元格的单元格引用(在Excel中如果运算对象是空白单元格，Excel将此空值当作零值)。
解决方法：修改单元格引用，或者在用作除数的单元格中输入不为零的值。

#NAME?

原因：在公式中使用了Excel不能识别的文本时或在公式中输入文本时没有使用双引号。产生错误值#NAME?。
解决方法：将公式中的文本括在双引号中。

#N/A

原因：当在函数或公式中没有可用数值时，将产生错误值#N/A。
解决方法：可以用IFERROR函数将其消除。

#REF!

原因：删除了由其他公式引用的单元格，或将移动单元格粘贴到由其他公式引用的单元格中。
解决方法：重新设置新的引用。

#NUM!

原因：在需要数字参数的函数中使用了不能接受的参数。
解决方法：确认函数中使用的参数类型正确无误。

#NULL!

原因：使用了不正确的区域运算符或不正确的单元格引用。
解决方法：使用正确的区域运算符或正确的单元格引用。

基本数据处理方法

处理重复数据

识别重复行方法
1.使用公式COUNTIF(),对想要去重的列进行计数，计数结果为1表示没有重复项，排序关键字段后使用公式if函数用于区分重复项和非重复项
2.利用数据透视表
去重方法
数据->排序和筛选->高级，识别重复行后删除
注意:需按照业务逻辑选择保留或删除重复行

处理缺失值

查找缺失值
1.使用ctrl+F
2.使用定位条件（Ctrl+G)
3.利用排序筛选
处理缺失值;
1.使用0替换数值类缺失值
2.使用平均值替换数值类缺失值
3.删除含有缺失值的记录或不对此类记录进行操作
4.暂时保留缺失值行，在有必要时再进行处理

转换变量类型

转换类型:
1.设置格式
2.使用分列功能
3.使用公式补全信息后转换
常需要相互转换的数据类型有:文本型、日期型、数值型

数据标准化

Min-Max标准化：新数据=（原数据-极小值)/(极大值-极小值)
使用标准分进行标准化：标准分=(原始分-平均分)/标准差
利用交叉表设置权重:
1.纵向和横向对比，横向重要则为1，纵向重要为0
2.横向加总
3.每个阶段合计值/合计总值100%
加权平均值=变量1变量1的权重+…变量n*变量n的权重

Power Pivot

Power Pivot是一款加强版的数据透视工具。它不仅拥有比传统数据透视表更加强大灵活的计算分析能力，还可以导入并关联多种不同数据源的大量数据，并在内存中创建自己的多维数据模型（多个表关联在一起的数据集合）
Power Pivot特点:
1．搭建多维数据分析环境
2．突破数据行数限制
3．简洁的操作界面功能
4．强大的自定义功能

多维数据模型

在咨询公司以及BI工具厂商的介绍性资料中又被称为“立方体（Cube）”，在这些资料中常以一个立体正方形的形式出现。多维数据集可以从多角度用数据全面映射某种业务的实际状况。
搭建方法:
1.明确表与表之间用于匹配的关键字段
2．在关系图视图模式下选中某个表的关键字段拖拽向另一个表的关键字段

注意事项:
1.用于连接两表的两个关键字段中，至少有一个关键字段是没有重复值的
2．关键字段中有重复值的表为主表，没有重复值的表为附表，在数据透视表中，只有当行列
标签来自附表时，附表才能提供值字段
3.两表关联时必然有一个主表和一个附表（如果两个关键字段都没有重复值，连接时先选中
的表为主表)

DAX表达式

1. DAX表达式的结果应用在数据透视表中
2. DAX表达式的结果作用于整列或者表中所有行3.还需注意以下几点:
   a.表名用“''”引用。
   b.字段名（列名）用“[]”引用。
   c．要注意函数表达式中参数的数据类型，尤其是要将“表”与“数值”正确区分。
   d.与Excel公式相同，除了直接在编辑器的公式区域输入公式外还可以通过单击公式编辑栏前的“fx”图标启动“插入函数”对话框，在对话框中选择需要的函数使用。
   e．表达式中的函数名不分大小写，比如“ALL”函数也可以记作“all”。
   相关参考网站:
   https://msdn.microsoft.com/zh-cn/library/ee634365(v=sql.105). aspx
   https://msdn.microsoft.com/zh-cn/library/vs/alm/hh272053

问题:用数据透视表求出每名销售人员手中高赢单率低风险商机金额占总商机金额的百分比
(高赢单率:赢单率=0.75/低风险:有无拖欠还款情况=“无”)
制作步骤:
1.关联商机记录与商机相关企业信息表
2．使用switch函数将嬴单率中的数值描述替换为文字描述（0.15=>低、0.25=>低、0.5=>中、0.75=>高）

=SWITCH('商机记录'[赢单率],0.15,"低",0.25,"低",0.5,"中",0.75,"高","-")

1.使用related函数将商机相关企业信息表中的有无拖欠还款情况字段内容合并到商机记录表中来

=RELATED('商机相关企业信息'[有无拖欠还款情况])

2．使用calculate与filter函数的嵌套函数创建高赢单率低风险商机金额加总值

高赢单率低风险商机金额加总值:=CALCULATE(sum('商机记录'[金额（$M）]),FILTER('商机记录','商机记录'[赢单率2]="高"),FILTER('商机记录','商机记录'[计算列 1]="无"))

商机总金额:=sum('商机记录'[金额（$M）])

3．用高赢单率低风险商机金额加总值/商机金额加总值求出高赢单率低风险金额百分比

高赢单率低风险金额百分比:=[高赢单率低风险商机金额加总值]/[商机总金额]

2．创建行标签为销售人员，值为高赢单率低风险商机金额百分比的数据透视表

创建KPI

创建KPI方法
1．在编辑器下方公式区域内用公式为需要创建KPI的字段指定汇总规则
2.点击“创建KPI”在“关键绩效指标（KPI)”对话框中设定KPI规则
3．定义KPI目标值的方法有“度量值”及“绝对值”两种方法，“度量值”是以两种汇总值之间的百分比为判断依据，而“绝对值”是以一个汇总规则与某个绝对数值之间的大小关系为判断依据
问题1:使用度量值规则创建平均销售金额与城市维度总平均金额的KPI(最小阈值100%，最大阈值120%），并在行标签为国家与城市的嵌套字段透视表中使用。
提示:筛选器函数ALL可以用来返回计算汇总值的数据范围，可以翻译成“所有XX的…”,比如CALCULATE(AVERAGE(‘表1’[销售金额]), all(‘表1’[城市]))函数的意思就是返回所有城市的销售金额的平均值
问题2:使用绝对值规则创建销量与销量目标绝对值数字之间的KPI（目标值10000，最小阙值1000，最大阙值4000),并在行标签为产品类别与产品子类别的嵌套字段透视表中使用