纯干货一篇文章告诉你Excel常见函数用法.docx
- 文档编号:13585287
- 上传时间:2023-06-15
- 格式:DOCX
- 页数:18
- 大小:24.13KB
纯干货一篇文章告诉你Excel常见函数用法.docx
《纯干货一篇文章告诉你Excel常见函数用法.docx》由会员分享,可在线阅读,更多相关《纯干货一篇文章告诉你Excel常见函数用法.docx(18页珍藏版)》请在冰点文库上搜索。
纯干货一篇文章告诉你Excel常见函数用法
纯干货!
一篇文章告诉你Excel常见函数用法!
Word,PPT,Excel这三个Office软件是职场办公里最常用的三个软件,但是我发现头条上写PPT的教程多,Excel的少,即使有,也是零零散散。
因为Excel的系统庞大其实你如果耐心钻研下去的话,发现excel难度不亚于任何专业软件的难度。
总体来说,excel的常用功能包括以下五项:
本篇只讲函数。
因为函数用好了,也可以节省很多的时间。
我看到有很多职场新人只会用自动求和和求平均数,所以普及一下常用函数还是很有必要(其实也算不上普及,共同学习而已)。
很多人都会有这样一个概念,遇到问题再XX呗,我觉得那样是被动的,不系统的,如果提前了解一下到时候再XX也会有大概一个方向。
写完才发现,写了很长,高能预警,最后有彩蛋。
excel2016中函数共有400多个:
如图
较常用的是文本函数,逻辑函数,日期与时间函数,查找与引用函数,数学函数等,很多人一看到这些就头大,感觉太多了,没有头绪?
那么哪些函数是最常用的呢?
下面我打破这个顺序,按照类型讲一下。
为节省字数,我尽量多用图,并且把同一类型的对比着来讲,不会很散:
相对引用于绝对引用:
相对引用:
单元格或单元格区域的相对引用是指相对于包含公式的单元格的相对位置。
例如,单元格B2包含公式=A1;Excel将在距单元格B2上面一个单元格和左面一个单元格处的单元格中查找数值。
绝对引用:
1乘以单元格A2(=A1*A2)放到A4中,现在将公式复制到另一单元格中,则Excel将调整公式中的两个引用。
如果不希望这种引用发生改变,须在引用的'行号'和'列号'前加上美元符号($),这样就是单元格的绝对引用。
A4中输入公式如下:
=$A$1*$A$2复制A4中的公式到任何一个单元格其值都不会改变
数组:
关于这一部分,由于本篇所讲都是基本,不涉及到嵌套,具体用法可以去Excel一起来认识数组公式看一下。
基本函数:
1.逻辑值,and,or,not
1.逻辑值:
true,false
能产生或返回逻辑值的情况:
比较运算符
is类信息函数
and,or,not
2.与(and),或(or),非(not)
and:
所有条件为true,则返回true,,否则返回false
or:
其中之一条件为true,则返回true,否则返回false
not:
如果条件参数结果为true,则返回false,同理结果为false,则返回true
true:
正确-成立-是
false:
错误-不成立-否
true相当于1
false相当于0
AND、OR与*、+
现象推定:
=AND(TRUE,TRUE,TRUE,TRUE,TRUE,FALSE)=AND(1,1,1,1,1,0)
=1*1*1*1*1*0
=OR(FALSE,FALSE,FALSE,FALSE,FALSE,TRUE)=OR(0,0,0,0,0,1)
=0+0+0+0+0+1
总结规律:
AND可以用*来代替
OR可以用+来代替
这个类似于高中数学的逻辑或与非。
2.IF函数
=IF(条件,True,False)
If函数的简写模式:
结论:
如果参数未写,用逗号隔开则看做0
如果第三个参数未写,当返回结果时看做'FALSE'
3.IS类判断函数
正确则返回为true,错误返回fause
4.Min,Max函数
MIN(number1,number2,...)Number1,number2,...是要从中找出最大值的1到30个数字参数。
返回一组值中的最小值。
说明
可以将参数指定为数字、空白单元格、逻辑值或数字的文本表达式。
如果参数为错误值或不能转换成数字的文本,将产生错误。
如果参数是数组或引用,则函数MIN仅使用其中的数字,空白单元格,逻辑值、文本或错误值将被忽略。
如果逻辑值和文本字符串不能忽略,请使用MINA函数。
如果参数中不含数字,则函数MIN返回0。
5.SUM函数
SUM返回某一单元格区域中所有数字之和。
语法:
SUM(number1,number2,...)Number1,number2,...为1到30个需要求和的参数。
说明
直接键入到参数表中的数字、逻辑值及数字的文本表达式将被计算
如果参数为数组或引用,只有其中的数字将被计算。
数组或引用中的空白单元格、逻辑值、文本或错误值将被忽略。
如果参数为错误值或为不能转换成数字的文本,将会导致错误。
6.SUMPRODUCT函数
在给定的几组数组中,将数组间对应的元素相乘,并返回乘积之和。
SUMPRODUCT(array1,array2,array3,...)其相应元素需要进行相乘并求和。
说明
数组参数必须具有相同的维数,否则,函数SUMPRODUCT将返回错误值#VALUE!
。
函数SUMPRODUCT将非数值型的数组元素作为0处理。
7.Sumif函数
语法:
SUMIF(range,criteria,sum_range)
range:
为用于条件判断的单元格区域
criteria:
为确定哪些单元格将被相加求和的条件,其形式可以为数字、表达式或文本
sum_range:
求和的实际单元格,如果忽略了则对区域中的单元格求和
本例来举个例子:
备注:
花生为A15,160为B23
备注:
花生为A15,160为B23
8.COUNT、COUNTA、COUNTBLANK函数
9.Countif函数
COUNTIF(range,criteria)
range:
可以使用引用函数,criteria:
可以使用通配符,数组
Range为需要计算其中满足条件的单元格数目的单元格区域。
Criteria为确定哪些单元格将被计算在内的条件,其形式可以为数字、表达式或文本。
例如,条件可以表示为32、'32'、'>32'或'apples'。
日期函数篇
10.常用日期函数
返回某个月份最后一天的序列号,该月份与start_date相隔(之后或之后)指示的月份数。
使用函数EOMONTH可以计算正好在特定月份中最后一天到期的到期日。
额外小知识
输入当前系统日期:
ctrl+;
输入当前系统时间:
ctrl+shift+;
11.DATEVALUE、EDATE、WEEKDAY日期函数
12.DATEIF函数
datedif年数、月数、日数返回年数月数日数
=DATEDIF(起始日期,结束日期,返回单位)类似于
=DATEDIF($B16,TODAY(),'ym')
13.HOUR,MINUTE,SECOND,TIME函数
数学函数篇
14.Mod函数
MOD(number,divisor)
Number为被除数。
Divisor为除数。
返回两数相除的余数。
1.结果的正负号与除数相同。
2.余数的绝对值必定小于除数绝对值
15.INT,TRUNC函数
INT(number)将数字向下舍入到最接近的整数。
TRUNC(number,num_digits)将数字的小数部分截去,返回整数。
Number需要截尾取整的数字。
Num_digits用于指定取整精度的数字。
Num_digits的默认值为0。
总结:
TRUNC与INT的不同之处
1.TRUNC可以指定小数部分,INT不能
2.对负数的处理方式不同
16.ROUND系列函数
ROUND
ROUND(number,num_digits)返回某个数字按指定位数取整后的数字。
Number需要进行四舍五入的数字。
Num_digits指定的位数,按此位数进行四舍五入。
如果num_digits大于0,则四舍五入到指定的小数位。
如果num_digits等于0,则四舍五入到最接近的整数。
如果num_digits小于0,则在小数点左侧进行四舍五入。
ROUNDUP
ROUNDUP(number,num_digits)远离零值,向上舍入数字。
Number为需要向上舍入的任意实数。
Num_digits四舍五入后的数字的位数。
函数ROUNDUP和函数ROUND功能相似,不同之处在于函数ROUNDUP总是向上舍入数字。
如果num_digits大于0,则向上舍入到指定的小数位。
如果num_digits等于0,则向上舍入到最接近的整数。
如果num_digits小于0,则在小数点左侧向上进行舍入。
ROUNDDOWN
ROUNDDOWN(number,num_digits)靠近零值,向下(绝对值减小的方向)舍入数字。
Number为需要向下舍入的任意实数。
Num_digits四舍五入后的数字的位数。
函数ROUNDDOWN和函数ROUND功能相似,不同之处在于函数ROUNDDOWN总是向下舍入数字。
如果num_digits大于0,则向下舍入到指定的小数位。
如果num_digits等于0,则向下舍入到最接近的整数。
如果num_digits小于0,则在小数点左侧向下进行舍入。
总结:
我们发现rounddown与trunc取数方式完全一致,通常会用trunc来代替rounddown函数
17.CEILING和FLOOR函数
FLOOR:
向下舍入为最接近的指定基数的倍数=FLOOR(基数,倍数)
CEILING:
向上舍入为最接近的指定基数的倍数=CEILING(基数,倍数)
FLOOR类似于ROUNDDOWN
CEILING类似ROUNDUP
18.RAND、RANDBETWEEN函数(生成随机数)
RAND()
RAND括号中没有参数
返回大于等于0及小于1的随机数,每次计算工作表时都将返回一个新的数值。
RANDBETWEEN
返回位于两个指定数之间的一个随机数。
每次计算工作表时都将返回一个新的数值。
如果该函数不可用,并返回错误值#NAME?
,请安装并加载“分析工具库”加载宏。
操作方法
1.在“工具”菜单上,单击“加载宏”。
2.在“可用加载宏”列表中,选中“分析工具库”框,再单击“确定”。
3.如果必要,请遵循安装程序中的指示。
语法
RANDBETWEEN(bottom,top)
Bottom函数RANDBETWEEN将返回的最小整数。
Top函数RANDBETWEEN将返回的最大整数。
生成5到10之间的数
=RANDBETWEEN(5,10)
19.PRODUCT、POWER(脱字符^)函数
product(*)乘积=PRODUCT(4,5)相当于'*'
power(脱字符^)乘幂
POWER(number,power)返回给定数字的乘幂。
Number底数,可以为任意实数。
Power指数,底数按该指数次幂乘方。
可以用“^”运算符代替函数POWER来表示对底数乘方的幂次,例如5^2。
文本函数
在讲之前,先来普及一下字符与字节
字节:
字节(Byte):
字节是通过网络传输信息(或在硬盘或内存中存储信息)的单位。
字节是计算机信息技术用于计量存储容量和传输容量的一种计量单位
1B=8b
字符:
字符是指计算机中使用的字母、数字、字和符号,只是一个符号。
字符人们使用的记号,抽象意义上的一个符号。
'1','中','a','$','¥',……
注意:
当启用支持DBCS的语言的编辑并将其设置为默认语言时,有些文本类函数会将每个双字节字符按2计数,支持DBCS的语言包括日语、中文(简体)、中文(繁体)以及朝鲜语。
20.LEFTRIGHT函数
=LEFT(TEXT,Num_chars)
=RIGHT(TEXT,Num_chars)
其中:
TEXT必需。
包含要提取的字符的文本字符串。
Num_chars可选。
指定要由LEFT/RIGHT提取的字符的数量.
1.如果省略num_chars,则假设其值为1。
2.Num_chars必须大于或等于零。
3.如果num_chars大于文本长度,则LEFT返回全部文本。
21MID函数
=MID(text,start_num,num_chars)
text必需。
包含要提取字符的文本字符串。
start_num必需。
文本中要提取的第一个字符的位置。
num_chars必需。
指定希望MID从文本中返回字符的个数。
=MIDB(text,start_num,num_bytes)
必需。
指定希望MIDB从文本中返回字符的个数(字节数)
注意:
1.如果start_num大于文本长度,则MID返回空文本('')。
2.如果start_num小于文本长度,但start_num加上num_chars超过了文本的长度,则MID只返回至多直到文本末尾的字符。
3.如果start_num小于1,则MID返回错误值#VALUE!
。
4.如果num_chars是负数,则MID返回错误值#VALUE!
。
5.如果num_bytes是负数,则MIDB返回错误值#VALUE!
。
22.LEN函数
=LEN(text)
=LENB(text)
text必需。
要查找其长度的文本。
空格将作为字符进行计数。
左边是LEN,右边是LENB
左边是LEN,右边是LENB
23.Find函数
FIND(find_text,within_text,[start_num])
FINDB(find_text,within_text,[start_num])
三个参数的要求:
必需。
要查找的文本。
必需。
包含要查找文本的文本。
可选。
指定要从其开始搜索的字符。
within_text中的首字符是编号为1的字符。
如果省略start_num,则假设其值为1。
注意:
24.SEARCH函数
SEARCH(find_text,within_text,[start_num])
SEARCHB(find_text,within_text,[start_num])
三个参数说明:
必需。
要查找的文本。
必需。
要在其中搜索find_text参数的值的文本。
可选。
within_text参数中从之开始搜索的字符编号。
25.REPLACE函数
=REPLACE(old_text,start_num,num_chars,new_text)
=REPLACEB(old_text,start_num,Num_bytes,new_text)
参数说明
必需。
要替换其部分字符的文本。
必需。
要用new_text替换的old_text中字符的
必需。
new_text替换old_text中字符(字节)的个数。
必需。
将用于替换old_text中字符的文本。
26.SUBSTITUDE函数
=SUBSTITUTE(text,old_text,new_text,[instance_num])
参数说明:
必需。
需要替换其中字符的文本,或对含有文本(需要替换其中字符)的单元格的引用。
必需。
需要替换的旧文本。
必需。
用于替换old_text的文本。
可选。
用来指定要以new_text替换第几次出现的old_text。
注意:
如果指定了instance_num,则只有满足要求的old_text被替换;否则会将Text中出现的每一处old_text都更改为new_text。
REPLACE与SUBSTITUTE的区别:
1.如果需要在某一文本字符串中替换指定位置处的任意文本,请使用函数REPLACE。
2.如果需要在某一文本字符串中替换指定的文本,请使用函数SUBSTITUTE;
单文本替换还是建议用Ctrl+H查找替换
27.CHAR与CODE
=CHAR(number)
必需。
介于1到255之间用于指定所需字符的数字。
返回对应于数字代码的字符。
函数CHAR可将其他类型计算机文件中的代码转换为字符。
=CODE(text)
必需。
需要得到其第一个字符代码的文本
返回文本字符串中第一个字符的数字代码。
返回的代码对应于计算机当前使用的字符集。
此方法可快速输入A,B,C序列。
28.UPPER\LOWER\EXACT
=UPPER(text)
(text)必需。
需要转换成大写形式的文本。
Text可以为引用或文本字符串。
=LOWER(text)
(text)必需。
要转换为小写字母的文本。
函数LOWER不改变文本中的非字母的字符。
=EXACT(text1,text2)
参数说明:
必需。
第一个文本字符串。
必需。
第二个文本字符串。
该函数用于比较两个字符串:
如果它们完全相同,则返回TRUE;否则,返回FALSE。
函数EXACT区分大小写,但忽略格式上的差异。
利用EXACT函数可以测试在文档内输入的文本。
29.REPT函数
=REPT(text,number_times)
必需。
需要重复显示的文本
必需。
用于指定文本重复次数的正数。
注意:
1.如果number_times为0,则REPT返回''(空文本)。
2.如果number_times不是整数,则将被截尾取整。
3.REPT函数的结果不能大于32,767个字符,否则,REPT将返回错误值#VALUE!
。
30.TRIM函数
=TRIM(text)
必需。
需要删除其中空格的文本。
要想全部去除,查找替换空格。
31.TEXT函数
=TEXT(value,format_text)
必需。
数值、计算结果为数值的公式,或对包含数值的单元格的引用。
必需。
使用双引号括起来作为文本字符串的数字格式。
格式可以如下:
上图看不清点击这里
查找与引用函数
32.ROW与COLUMN
=ROW([reference])返回单元格的行号
=COLUMN([reference])返回单元格的列号
=ROWS(array)计划行数
33.VLOOKUP与HLOOKUP
vlookup非常常用,且不难,留给你自己探索。
34.LOOKUP
稍微有些复杂:
可参考下面两图:
35.CHOOSE函数
=CHOOSE(index_num,value1,value2,...)
Index_num必须为1到29之间的数字、或者是包含数字1到29的公式或单元格引用
函数CHOOSE基于index_num,从中选择一个数值或执行相应的操作。
参数可以为数字、单元格引用、已定义的名称、公式、函数或文本。
36.MATCH函数
=MATCH(lookup_value,lookup_array,match_type)
为需要在数据表中查找的数值。
可以为数值(数字、文本或逻辑值)或对数字、文本或逻辑值的单元格引用
可能包含所要查找的数值的连续单元格区域。
Lookup_array应为数组或数组引用
为数字-1、0或1。
Match-type指明MicrosoftExcel如何在lookup_array中查找lookup_value。
37.INDEX函数
=INDEX(array,row_num,column_num)
为单元格区域或数组常量
数组中某行的行序号,函数从该行返回数值。
如果省略row_num,则必须有column_num。
数组中某列的列序号,函数从该列返回数值。
如果省略column_num,则必须有row_num。
多与MATCH函数连用
38.OFFSET函数
=OFFSET(reference,rows,cols,height,width)
以指定的引用为参照系,通过给定偏移量得到新的引用。
返回的引用可以为一个单元格或单元格区域。
并可以指定返回的行数或列数。
注意:
如果省略height或width,则其高度或宽度与reference相同。
39.INDIRECT函数
返回由文本字符串指定的引用。
此函数立即对引用进行计算,并显示其内容。
当需要更改公式中单元格的引用,而不更改公式本身,请使用函数INDIRECT。
=INDIRECT(ref_text,a1)
此单元格可以包含A1-样式的引用、R1C1-样式的引用、定义为引用的名称或对文本字符串单元格的引用。
不是合法的单元格的引用,函数返回错误值。
为一逻辑值,指明包含在单元格ref_text中的引用的类型。
增补:
1.名称的含义
名称:
是一种特殊的公式,由用户自己定义,程序运行时存在于内存当中,通过其标识进行调用。
2.定义名称的方法
通过名称框
通过菜单
通过所选内容
3.名称命名的规则
名称命名可以是任意字符与数字的组合,不能以纯数字或以数字开头
不能以字母R、r、C、c命名,也不能是单元格名称
命名不超过255个字符
不区分大小写
4.选用名称的原因
5.名称的引用类别(包括5项)
多区域引用
常量引用
数组引用={1;2;3;4;5;6;7;8;9}
公式引用=SUM(D5:
E8)
名称修改
宏表函数
这部分略讲,因为我还不太会。
1.宏表函数概念:
早期低版本excel中使用的,现在已由VBA顶替它的功能,但仍可以在工作表中使用。
2.使用宏表函数注意事项:
A.不能在单元格中使用,要定义的名称'(菜单:
插入——名称——定义)
B.有的宏表函数不能自动更新,需结合易失性函数来辅助完成自动更新
=函数&T(NOW())适用文本
=函数+TODAY()*0适用数字………
=函数&T(RAND())适用文本=函数+NOW()*0适用数字
只要最后什么都没有就可以只要后结果为0都可以
C.宏表函数对公式的长度有限制
D.宏表函数运算速度较慢使用易失性函数后,会引发工作簿重新计算(now,today,rand)
3.常用函数
GET.CELL
GET.DOCUMENT
GET.WORKBOOK
EVALUATE
FILES
————————————————我是分割线——————————————————————
如果你能一口气看到这,说明你对函数已经掌握的相当可以了,
如果是一下拉到这的,我想你肯定看烦了。
是不是感觉还是太多了,本来想说一下最常用的,结果还是把基本所有的全讲了一遍,哎,谁让我啰嗦呢?
下面真正的干货来了!
企业中最常用的十个函数!
!
逻辑判断:
If函数(掌握If函数的嵌套使用方法)
多条件求和统计:
Sumifs函数(掌握函数参数中*与?
的用法)
多条件计数统计:
Countifs函数(掌握该函数的查询统计方法)
单元格内容拆分与组合:
文本日期函数(掌握日期的拆分方法)
专业的分类筛选统计:
Subtotal函数(掌握参数9和109的区别)
多表匹配及列表查询函数:
Vlookup函数(掌握0和1参数运用)
排名定位及二
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 干货 一篇 文章 告诉 Excel 常见 函数 用法
![提示](https://static.bingdoc.com/images/bang_tan.gif)