- 自动函数:
公式–>自动求和(求和、平均值、计数、最大值、最小值)
自动函数特点:可以自动识别当前单元格左侧或上边的连续区域。
Excel中只有一个函数有快捷键:sum函数(求和)–>Alt+等号(=)
注意:
条件反射:写公式、函数必须以等号=开头。
当公式处于编辑状态时,不要用鼠标点击其他地方,如果要确定公式结果就按Enter键,取消则按ESC键。
技巧:
定位行(或列)特殊的空格位置,定位快捷键(F5)。
例:
当你拿到上图excel的时候应该怎么处理更加快捷完成任务?步骤:选中“实发工资”,按快捷键F5定位,点击定位条件——空值——确定。则会出现以下只选中小计空格图片
根据自动函数特点,此时选择自动函数求和或者快捷键Alt+等号(=)即可完成任务。
-
Rank函数(排序函数):对一组数据进行排序。
函数格式:rank(对谁排序,排序区域)——西式排名 ;
横拖字母变、竖拖数字变——未来,写完公式,一定用这句话来检查一下。
将单元格的公式显示出来:Ctrl+~;
如何让字母或数字不变? “$"——给钱就听话;快捷键F4(可以多次按)。 -
iF函数:如果条件成立,返回结果1,否则返回结果2。
函数格式:IF(条件,结果1,结果2)。
if嵌套使用:
结果个数决定IF的个数,有N个结果,就有N-1个IF -
And函数与Or函数:格式AND(条件1,条件2…),需要满足所有条件才为true;OR(条件1,条件2…)满足其中一个条件为true,条件都为false时,则结果返回false。
-
Count函数:计算一片区域中的数字个数。
count格式:count(区域)
countif(区域,条件):通常,区域需要锁定。
条件分两种情况:
1)条件直接是一个值或一个单元格引用,写上即可;
2)条件是一个不等式:需要用一对半角的""将条件括起来。
counta(区域):计算区域中非空单元格的个数。
countifs(条件区域1,条件1,条件区域2,条件2…):统计一组给定条件所指定的单元格数。 -
Sumif函数:在“条件区域”找到满足“条件”的数据,将对应的“求和区域”数据加起来。
sumif函数格式:SUMIF(条件区域,条件,求和区域)。
注意:
1)当“条件区域”和“求和区域”相同时,可以省略“求和区域”。
2)sumif函数的条件区域和求和区域起点、终点是一一对应。
这两个区域的大小一定要相同,并且起点也要相同。
多条件求和:SUMIFS(求和区域,区域1,条件1,区域2,条件2,…)
思考:可以不可以在J5单元格写一个公式,右拉、下拉,完成所有的数据查询。
J5处代码 :=SUMIF($A$2:$A$23,$H5,IF(J$4="基础薪金",$E$2:$E$23,$F$2:$F$23))
- Vlookup函数:搜索表区域 首列 满足条件的元素,确定待检索单元格在区域中的行序号,再进一步返回选定单元格的值。默认情况在,表是以升序排序的。
白话文讲就是:我们要找“A”的ASCLL码,首先根据“A”的特点从ASCLL码表中找到“A”,然后才会找到“A”对应的ASCLL码——65。
Vlookup函数格式:Vlookup(查找目标,查找区域,返回第几列,0)0——近似查找;1——模糊查找
总结:
1)“查找区域”必须同时包含要找的区域和要返回的区域;
2)“返回第几列”是指“选中范围的第几列”;
3)“查找区域”内,查找的数据必须被选为最左列;
Vlookup只能根据左边返回右边;从左到右返回,叫顺向查找;从右到左返回,叫逆向查找。
例:根据右边工号从左边数据中查找满足右边工号的人的姓名?
在N4处输入代码:=VLOOKUP(M4,$A$2:$B$23,2,0)
,在下拉即可完成所有查找。
注意“查找区域”——A2:B23,我将工号和姓名都包含在查找区域中,才能在“返回第几列”中写上2。
- Match、Index函数
Match函数:返回在“查找区域”内符合“查找对象”条件的对应数组顺序;
Match函数格式:Match(查找对象,查找区域,0);
Index函数:从“数据区域”中返回第N个单元格的值;
Index函数格式:Index(数据区域,n);
利用Match、Index函数完成题一、题二?利用提供的工号或者姓名填写空格。
N4处代码:`=INDEX($B 2 : 2: 2:B 23 , M A T C H ( 23,MATCH( 23,MATCH(M4,$A 2 : 2: 2:A$23,0))Q4处代码:`=INDEX($A 2 : 2: 2:A 23 , M A T C H ( 23,MATCH( 23,MATCH(P4,$B 2 : 2: 2:B$23,0))
两处代码填写后下拉,即可完成所有单元格的查找与填写。
> 总结:在这个例子中Match函数的作用相当于在ASCLL表中找到了“A”,而Index函数相当于将“A”转化为ASCLL码“65”。
Vlookup=Match+Index,Match+Index结合使用可能比单独使用Vlookup更加实用。因为Vlookup函数有一个限制条件:Vlookup只能根据左边返回右边。
- 文本截取、文本信息函数
文本截取函数:left & right & mid
left函数:从“字符串”左端提取“n”位字符;
left函数格式:left(字符串,n);
right函数:从“字符串”右端提取“n”位字符;
right函数格式:right(字符串,n);
mid函数: 从“字符串”的定义“起始位”开始提取“n”位字符;
mid函数格式:mid(字符串,起始位,n);
文本信息函数:Len & Lenb & Find
Len函数:计算“字符串”长度,不区分中英文输入,一律按照1位计算;
Len函数格式:Len(字符串);
Lenb函数:计算“字符串”长度,区分中英文输入,中文按照2位计算,英文按照1位计算;
Lenb函数格式:Lenb(字符串);
Find函数:计算“目标字符”在“字符串”中出现的位置,返回一个整数。
Find函数格式:Find(目标字符,字符串); - 文本替换、文本重复函数
文本替换函数:substitute & replace
substitute函数:将“目标单元格”里的“源文本”替换成“新文本”;n代表替换第几个,省略表示全部。
substitute函数格式:Substitute(目标单元格,源文本,新文本,n);
replace函数:将“目标文本”里的“起点位”开始的连续n位替换成“新文本”。
replace函数格式:replace(目标文本,起点位,n,新文本);
文本重复函数:
repet函数:将“文本”重复n次;
repet函数格式:repet(文本,n);
11. Text函数
1)占位符的使用
将一些数值以什么样的格式显示;text(数值,格式);
2)设置日期格式
3)设置条件格式
TEXT(数据。“【条件1】显示格式1;【条件2】显示格式2;否则显示格式;差异显示格式”)
-
日期函数
注意:在使用edate/eomonth函数时,要将目标单元格格式设置为日期格式; -
Offset函数
offset函数:返回对单元格或单元格区域中指定行数和列数的区域的引用。从“偏离起点”开始,通过行、列偏移量定位需要的起点单元格,再通过行数与列数来决定单元格区域。
offset函数格式: offset(偏离起点,行偏移量,列偏移量,行数,列数);
偏离起点: 光标从哪儿开始偏移;
行偏移量: 光标从起点偏移i行;
列偏移量: 光标从起点偏移i列;
行数: 以光标的新位置开始算起m行;
列数: 以光标的新位置开始算起n列;
-
Indirect函数
Indirect函数:返回文本字符串所指定的引用。
Indirect函数格式:Indirect(文本字符串,格式);格式——true(A1样式引用)/false(R1C1样式引用)
-
SUBTOTAL函数
subtotal函数:返回列表或数据库中的分类汇总;
subtotal函数格式:subtotal(函数序列,数据范围);
函数列表:
更多推荐
初识excel函数
发布评论