Excel函数公式大全, Excel函数有多少?很少有人知道!这里边肖为大家整理了:数据分析常用的43个函数分析,绝对干货!赶紧收藏起来!
什么是函数?
它是由Excel预定义的功能模块,按照特定的顺序和结构执行数据处理任务,如计算和分析。
因此,函数被称为特殊公式。与公式一样,Excel函数的最终返回结果是一个值。
一个函数有唯一的名字,不区分大小写,这决定了它的功能和用途。
举一个简单的例子,说明在处理表单时如何大写一个名字的所有首字母。
如果不了解功能,是不是一个一个手动修改?如果你知道函数本身,你就不会一个一个地修改它。输入函数公式,3秒就搞定了!
什么是公式?公式是用户自己设计的公式,结合常量数据、单元格引用、运算符等元素进行数据处理和计算。
用户有目的地使用公式计算结果,因此Excel公式必须(且只能)返回值。
公式结构:=(C2D2)* 5从公式结构来看,构成公式的元素通常包括等号、常数、引用和运算符。其中=号是必不可少的。
但在实际应用中,公式也可以通过数组、Excel函数或名称(命名公式)进行操作。
通常,Excel从左到右执行公式运算。当公式中使用多个运算符时,Excel会根据每个运算符的优先级进行运算,对于同一级别的运算符,会从左到右进行运算。
具体优先顺序如下:
使用Excel公式计算时,可能因为某些原因得不到正确的结果,返回错误值。
下表显示了常见的错误值及其含义。
当公式结果返回错误值时,要及时找到错误原因,修改公式解决问题。
03函数和分类Excel函数通常由函数名、左括号、参数、逗号和右括号组成。
函数公式结构:=IF(A10,正数,IF(A10,负数,))对于函数的参数,可以由数值、日期、文本等元素组成,也可以用作常数、数组、单元格引用或其他函数。
当函数的参数也是函数时,Excel称之为函数嵌套。
共有11种类型的函数,分别是数据库函数、日期和时间函数、工程函数、金融函数、信息函数、逻辑函数、查询和引用函数、数学和三角函数、统计函数、文本函数和用户自定义函数。
04常用函数本文是一个目录,介绍每个函数的作用,哪个函数可以用来解决某个问题等。可以自己学习使用。
对于函数,不用死记硬背,只需要知道应该选择什么样的函数,需要什么参数,如何使用就可以了!
例如,选择字段并使用左/右/中功能.其他细节留给万能的百度吧!
下面,根据不同的应用场景,对这些常用的必备功能进行分类介绍。
1.关联匹配类所需的数据不在同一个Excel表中,也不在同一个Excel表的不同工作表中。数据太多抄起来麻烦,容易出错。如何整合它们?
以下函数用于多表关联或行列比较,表越复杂越好用!
01.VLOOKUP
函数:用于查找满足第一列条件的元素。
Syntax:=VLOOKUP (lookup value, table array, column index number, [range lookup])
*注意:[]中的参数是可选的,其余是必选的。下面也是如此。=VLOOKUP(要搜索的项目、要搜索的位置、包含要返回的值的范围中的列号,返回近似匹配或精确匹配-表示1/TRUE或0/FALSE)。
示例:名为F5的员工的职位是什么?
02.HLOOKUP
函数:在表或值数组的第一行中搜索值,然后返回表或数组中指定行的列中的值。
HLOOKUP中的h代表线。
Syntax:=HLOOKUP (lookup value, table array, row index number, [range lookup])
示例:=HLOOKUP (axle,A1:C4,2,TRUE)查找第一行中的轴,并返回同一列(A列)中第二行的值。
LOOKUP和HLOOKUP的区别:当比较值在数据表的第一行时,如果要向下查看指定的行数,可以使用HLOOKUP。
当比较值位于要搜索的数据的左列时,可以使用VLOOKUP。
03.INDEX
函数:返回表格或区域中的值或对值的引用。
Syntax:=INDEX (array, number of rows, [number of columns])
示例:=INDEX(B2:D11,3,3)是位于区域A2:B3的第三行和第三列的交点处的值。
04.MATCH
函数:用于返回指定区域(一行或一列)中指定内容的位置。
Syntax:=MATCH (find value, find array, [match type])
示例:=MATCH(41,B2:B5,0)B2:B5单元格区域中值41的位置。
Matching type:
1或省略:MATCH查找小于或等于lookup_value的最大值。
0: Match查找与lookup_value完全相等的第一个值。
-1: Match查找大于或等于lookup_value的最小值。
05.RANK
功能:求某个数值在某个区域的一组数值中的排名。
Syntax:=RANK (number, reference, [order])
例:=RANK(A3,A2:A6,1) A3上表中,A2:A6: 0的排序方式为降序,1为升序,默认为0。
06.ROW
函数:返回引用的行号。
语法:=ROW([reference])
例:=ROW() 公式所在行的行号
07.COLUMN
功能:返回单元格所在的列。
语法=COLUMN(reference)
例:=COLUMN (D10) 返回4,因为D列是第四列。
08.OFFSET
功能:返回对单元格或单元格区域中指定行数和列数的区域的引用。返回的引用可以是单个单元格或单元格区域。可以指定要返回的行数和列数。
语法:=OFFSET(reference, rows, cols,[height], [width])
例:=OFFSET(D3,3,-2,1,1)显示单元格B6中的值,其中3为下方三行,-2为左方两行,1为行高和列宽。
2、清洗处理类数据处理之前,需要对提取的数据进行初步清洗,如清除字符串空格,合并单元格、替换、截取字符串、查找字符串出现的位置等。
截取字符串:使用MID /LEFT/RIGHT
替换单元格中内容:SUBSTITUTE /REPLACE
合并单元格:使用CONCATENATE
清除字符串空格:使用TRIM/LTRIM/RTRIM
查找文本在单元格中的位置:FIND/SEARCH
09.MID
功能:从中间截取字符串
语法:=MID(text,start_num, num_chars)
例:=MID(A2,1,5) 从A2内字符串中第1个字符开始,返回5个字符。
根据身份证号码提取年月。
10.LEFT
功能:从左截取字符串。
语法:=LEFT(text,[num_chars])
例:=LEFT(A2,4) 第一个字符串中的前四个字符。
11.RIGHT
功能:从右截取字符串。
语法:=RIGHT(text,[num_chars])
例:=RIGHT(A2,5)第一个字符串的最后5个字符
12. SUBSTITUTE
功能:在文本字符串中用new_text替换old_text。
语法:=SUBSTITUTE(text,old_text, new_text, [instance_num])
例:=SUBSTITUTE(A2, 销售, 成本)将销售替换为成本(成本数据)替换部分电话号码。
13.REPLACE
功能:替换掉单元格的字符串。
语法:=REPLACE(old_text,start_num, num_chars, new_text)
例:=REPLACE(A2,6,5,*) 在A2中,从第六个字符(f)开始使用单个字符*替换五个字符。
REPLACE和SUBSTITUTE区别:两个函数很接近,不同在于REPLACE根据位置实现替换,需要提供从第几位开始替换,替换几位,替换后的新的文本;
而SUBSTITUTE根据文本内容替换,需要提供替换的旧文本和新文本,以及替换第几个旧文本等。
因此REPLACE实现固定位置的文本替换,SUBSTITUTE实现固定文本替换。
14.CONCATENATE
功能:将两个或多个文本字符串联接为一个字符串。
语法:=CONCATENATE(text1,[text2],)
合并单元格中的内容,还有另一种合并方式是,需要合并的内容过多时,CONCATENATE效率更快。
例:=CONCATENATE(B2, , C2) 联接三部分内容:单元格B2中的字符串、空格字符以及单元格C2中的值。
15.TRIM
功能:除了单词之间的单个空格之外,移除文本中的所有空格。
语法:=TRIM(text)
Text为要去掉空格的文本。
例:=TRIM(First Quarter Earnings ) 从公式的文本中移除前导空格和尾随空格。
16.LTRIM
功能:从字符串左侧删除空格或其他预定义字符。
语法:=LTRIM (string, [charlist])
17.RTRIM
功能:从字符串右侧删除空格或其他预定义字符。
语法:=LTRIM(string, [charlist])
18.FIND
功能:查找文本位置
语法:=FIND(find_text,within_text, [start_num])
例:=FIND(M,A2) 单元格A2中第一个M的位置
19.SEARCH
功能:返回一个指定字符或文本字符串在字符串中第一次出现的位置,从左到右查找。
语法:=SEARCH(find_text,within_text,[start_num])
例:=SEARCH(e,A2,6) 单元格A2中的字符串中,从第6个位置起,第一个e的位置。
FIND和SEARCH区别:这两个函数功能几乎相同,实现查找字符所在的位置,区别在于FIND函数精确查找,区分大小写;SEARCH函数模糊查找,不区分大小写。
20.LEN
功能:返回文本字符串中的字符个数。
语法:=LEN(text)
例:=LEN(A1) A1单元格字符串的长度
21. LENB
功能:返回文本字符串中用于代表字符的字节数。
语法:=LENB(text)
例:=LEN(A1)A1单元格字符串的字节数。
3、逻辑运算类逻辑,顾名思义,不赘述,直接上函数。
22.IF
功能:使用逻辑函数IF 函数时,如果条件为真,该函数将返回一个值;如果条件为假,函数将返回另一个值。
语法:=IF(Logical,Value_if_true,Value_if_false)
如果指定条件的计算结果为true,IF函数将返回某个值;如果该条件的计算结果为false,则返回另一个值。
23.COUNTIF
功能:用于统计满足某个条件的单元格的数量;例如,统计特定城市在客户列表中出现的次数。
语法:=COUNTIF(单元格1: 单元格2 ,条件)
统计特定店铺在列表中出现的次数。
24.AND
功能:逻辑判断,相当于并。
语法:全部参数为True,则返回True,经常用于多条件判断。
例:=AND(A21,A2100) 如果A2大于1并且小于100,则显示TRUE;否则显示FALSE。
25.OR
功能:逻辑判断,相当于或。
语法:只要参数有一个True,则返回Ture,经常用于多条件判断。
例:=OR(A21,A2100) 如果A2大于1或者小于100,则显示TRUE;否则显示FALSE。
4、计算统计类在利用Excel表格统计数据时,常常需要使用各种Excel自带的公式,也是最常使用的一类。(对于这些,Excel自带快捷功能)
26.MIN
功能:找到某区域中的最小值。
语法:=MIN(number1, [number2],)
例:=MIN(D2:D11) 区域D2:D11中的最小数。
27.MAX
功能:找到某区域中的最大值。
语法:=MAX(number1, [number2],)
例:=MAX(A2:A6) 区域A2:A6中的最大值。
28.AVERAGE
功能:计算某区域中的平均值。
语法:=AVERAGE(number1, [number2],)
例:=AVERAGE(D2:D11) 单元格区域D2到D11中数字的平均值。
29.COUNT
功能:计算含有数字的单元格的个数。
语法:=COUNT(value1, [value2],)
例:=COUNT(A2:A7) 计算单元格区域A2到A7中包含数字的单元格的个数。
30.COUNTIFS
功能:统计一组给定条件所指定的单元格数。
语法:COUNTIFS(criteria_range1,criteria1, [criteria_range2, criteria2],)
例:=COUNTIFS(A2:A7,6,A2:A7,1)计算1和6之间(不包括1和6)有几个数包含在单元格A2到A7中。
31.SUM
功能:计算单元格区域中所有数值的和。
语法:=SUM(单元格1:单元格2)
例:=SUM(A2:A10) 将单元格A2:10中的值加在一起。
32.SUMIF
功能:求满足条件的单元格和。
语法:=SUMIF(range,criteria, [sum_range])
例:=SUMIF(A2:A7,水果,C2:C7) 水果类别下所有食物的销售额之和。
32.SUMIFS
功能:对一组满足条件指定的单元格求和。
语法:=SUMIFS(sum_range,criteria_range1, criteria1, [criteria_range2, criteria2],)
例:=SUMIFS(A2:A9, B2:B9,=香*, C2:C9, 卢宁) 计算以香开头并由卢宁售出的产品的总量。
33.SUMPRODUCT
功能:返回相应的数组或区域乘积的和。
语法:=SUMPRODUCT (array1, [array2], [array3],)
例:=SUMPRODUCT(Table1!A1:Table1!A100,Table2!B1:Table2!B100) 计算表格1的A1到A100与表格2的B1到B100的乘积和,
即A1*B1+A2*B2+A3*B3+
34.STDEV
功能:基于样本估算标准偏差。
语法:STDEV(number1,[number2],)
例:=STDEV(D2:D17) 列的标准偏差
35.SUBSTOTAL
功能:返回列表或数据库中的分类汇总。
语法:=SUBTOTAL(function_num,ref1,[ref2],)
例:=SUBTOTAL(9,A2:A5)使用9作为第一个参数,算出的单元格A2:A5中分类汇总的值之和。
http://36.INTROUND
功能:ROUND 函数将数字四舍五入到指定的位数。
语法:=ROUND(A1, 2)
例:=ROUND(2.15, 1)将2.15四舍五入到一个小数位
功能:INT将数字向下舍入到最接近的整数。
语法:=INT(8.9) 将8.9 向下舍入到最接近的整数。
5、时间序列类专门用于处理时间格式以及转换。
37.TODAY
功能:返回当前日期的序列号。
语法:=TODAY()
liz=TODAY()+5返回当前日期加5天。例如,如果当前日期为1/1/2012,此公式会返回1/6/2012。
38.NOW
功能:返回当前日期和时间的序列号。
语法:=Now()
=NOW()+7 返回7天后的日期和时间。
39.YEAR
功能:返回对应于某个日期的年份。
语法:=YEAR(serial_number)
=YEAR(A3) 单元格A3中日期的年份
40.MONTH
功能:返回日期中的月份。
语法:=MONTH(serial_number)
=MONTH(A2) 单元格A2中日期的月份
41.DAY
功能:返回以序列数表示的某日期的天数。
语法:=DAY(serial_number)
=DAY(A2) 单元格A2中日期的天数
42.WEEKDAY
功能:返回对应于某个日期的一周中的第几天。默认情况下,天数是1(星期日)到7(星期六)范围内的整数。
语法:=WEEKDAY(serial_number,[return_type])
=WEEKDAY(A2) 1(星期日)到7(星期六)一周中的第几天
=WEEKDAY(A2, 2) 1(星期一)到7(星期日)一周中的第几天。
43.DATEDIF
功能:计算两个日期之间相隔的天数、月数或年数。
语法:=DATEDIF(start_date,end_date,unit)
=DATEDIF(Start_date,End_date,Y)一段时期内的年数
=DATEDIF(Start_date,End_date,D)一段时期内的天数
=DATEDIF(Start_date,End_date,YD)忽略日期中的年份,一段时期内的天数
附:
Excel函数公式大全,以上就是本文为您收集整理的Excel函数公式大全最新内容,希望能帮到您!更多相关内容欢迎关注。