Excel常用函数

相对引用和绝对引用

相对引用

随着鼠标的拖动，单元格的位置会发生改变（一个数字不动，行列前加$）

绝对引用

随着鼠标的拖动，单元格的位置不会发生改变（不发生位置改变的单元格是被$绝对引用的）

‘四则运算’

求和：sum

平均值：average

最大值：max

最小值：min

求个数

只能数值：count

任意值：counta

带条件的计数countif
countif(范围，条件)

带多条件的计数countifs
countifs(条件范围1，条件1；条件范围2，条件2，，，)

求和

带条件的求和sumif
sumif(范围，条件，{求和范围)

带多条件的求和sumifs
sumifs(求和的范围，条件1的范围，条件1，[条件范围2]，[条件2]…)

排序

排序rank
rank(要排的第一个数值，要排的范围,[升降序])

逻辑函数

if函数
判断是否满足一个条件，满足的话则返回一个值，不满足则返回另外一个值
if：判断条件“T”“F”

iferror
如果表达式是一个错误，则返回自定义值，否则返回表达式自身的值
iferror：（a/b,自定义）

and
必须满足所有参数，才会返回true，有一个参数不满足，则直接返回false

or
只要有一个参数符合，则返回true；全部参数都不符合，则返回false

取余数

mod
判断除法后是否有余数

左右中提取

left
从左边提取若干字符
left:(文本范围，提取数量）

right
从右边提取若干字符
right:（文本范围，提取数量）

mid
从中间指定的位置开始提取
mid：(文本范围，开始提取的位数，一共提取几位数)

合并

符号
&

concatenate
concatenate：(文本1，文本2…)

替换

substitute
substitute：(文本范围，被替换的文本，新的替换文本)

replace
replace：(文本范围，从第几个字符开始，共替换几个字符，新的文本)

文本转化

text（文本范围，格式）
例子：[红色][>0]↑0;[绿色][<0]↓0 [红色][=1]√;[绿色][=0]×

文本查找

find
查找文本，在哪个文本区域
search
（查找文本，在哪个文本区域），可通配符做迷糊查找

转换

upper
（要转换的文本），小写转大写
lower
（要转换的文本），大写转小写

重复

rept
要重复的文本，重复的次数

求文本长度

len
文本

查找与引用函数

vlookup（查找列）
0表示精确匹配，1表示模糊匹配

案例一 ：
用谁去找、匹配对象范围、返回第几列、匹配方式（0表示精确匹配，1表示模糊匹配）
工资=VLOOKUP(F3,$A$2:$C$11,3,0)

案例二 ：
反向查找的固定公式用法：=VLOOKUP(检索关键字，IF({1,0},检索关键字所在列，查找值所在列)，2，0)
班级=VLOOKUP(G17,IF({1,0},$B$16:$B$22,$A$16:$A$22),2,0)

案例三：
多条件查找的固定公式用法：=VLOOKUP(关键字1&关键字2，IF({1,0},序列1&序列2，查找值所在列)，2，0)
工资 =VLOOKUP(E27&F27,IF({1,0},表3[工号]&表3[姓名],表3[工资]),2,0)

案例四：
返回多列的固定公式用法：=VLOOKUP(混合引用关键字，查找范围，COLUMN(xx)，0)，返回第几列就开始引用第几列的单元格即可
电脑销售额=VLOOKUP($F45,表3_5,COLUMN(表3_5[[#标题],[电脑销售额]]),0)

Hlookup（查找行）
搜索表区域首行满足条件的元素，确定待检索单元格在区域中的行序号，再进一步返回选定单元格的值

indirect
类似于间接引用
例子:五大洲
返回文本字符串所指定的引用

#match——>index
match
match返回符合特定值特定顺序的项在数组中的相对应位置，所选择的区域必须要有一定的序列（需要有排序）
找坐标的
需要有序列
index
index函数（选定的区域，行坐标，列坐标）
在给定的单元格区域中，返回特定行列交叉处单元格的值
找坐标值

时间函数

Ctrl+1 快捷键设置单元格格式

Power Pivot

不重复计数
	Distinctcount(文本范围）
	
关系函数
	RELATED
		（从‘一’端提取/引用‘多’端的列值（类似于vlookup)
	RELATEDTABLE
		（从‘多’端提取/引用‘一’端的相关的表格）
		
	多行数据不能单独存放于一个单元表中错误的=relatedtable('大单定义')输出提示 该表达式引用多列，多列不能转换为标量值
正确的为=countrowsrelatedtable('大单定义'） countrows（表的意思）

CALCULATE
	类似于分组依据
	参数（度量值，筛选条件...）
	筛选条件不受自身切片器的影响
	
DIVIDE安全除法
	分子，分母

Power Pivot案例分析

渗透率:=divide([已购买客户数量],[客户总数])
平均客户购买数量:=DIVIDE([金额的总和],[已购买客户数量])
平均订单购买金额:=DIVIDE([金额 的总和],[订单数量])
2014年销售总额:=CALCULATE([销售总金额],‘时间表’[年份]=“2014年”)
2014年销售额完成率:=DIVIDE([2014年销售总额],[销售总金额])
2015年任务额完成率:=DIVIDE([2015年销售总额],[任务额 的总和])
年度增长率YOY:=DIVIDE([2015年销售额完成率]-[2014年销售额完成率],[2014年销售额完成率])
日期函数:=date(2015,月份，几号）