Excel函数学会50%，就很强大！Excel函数，不是数学概念里的方程式，它是Excel表格里的超级计算器，机械重复的人工计算，函数一条公式就能解决。vlookup大家应该知道，而Excel 有哪些和 vlookup 一样重要的函数或功能呢？做为多年的数据分析师，带大家系统的了解下什么是函数，什么是公式，函数与分类，常用函数有哪些，点赞收藏，相信你肯定会用到！01什么是函数它是由Excel内部预先定义并按照特定的顺序、结构来执行计算、分析等数据处理任务的功能模块。因此，函数被人们称为“特殊公式”，与公式一样，Excel函数的最终返回结果为值。函数只有唯一的名称且不区分大小写，它决定了函数的功能和用途。举个简单的例子——处理表格时，如何把名字首个字母全部变成大写？不懂函数的你，是不是一个个手动修改？如果你知道Proper这个函数，就不会一个个修改了。输入函数公式，3秒搞定！02什么是公式什么是Excel的公式？公式就是由用户自行设计并结合常量数据、单元格引用、运算符等元素进行数据处理和计算的算式。用户使用公式是为了有目的地计算结果，因此Excel的公式必须(且只能)返回值。公式的结构：=(C2+D2)*5从公式结构来看，构成公式的元素通常包括等号、常量、引用和运算符等元素。其中，=号是不可或缺的。但在实际应用中，公式还可以使用数组、Excel函数或名称(命名公式)来进行运算。公式中的运算符符号说明实例-算数运算符：负号=8*-5=-40%算数运算符：百分号=60*50%=30^算数运算符：乘幂=3^2=9*和/算数运算符：乘和除=3*2/4=1.5+和-算数运算符：加和减=3+2-5=0=,<>,>,<,>=,<=比较运算符：等于、不等于、大于、小于、大于等于、小于等于=(A1=A2)判断A1与A2相等=(B1<>“ABC”) 判断B1不等于“ABC”＆文本运算符：连接文本=“Excel”＆“Home”=“ExcelHome”:区域运算符：冒号=SUM(A1:A10)空格交叉运算符：单个空格=SUM(A1:B5 A4:D9)相当于=SUM(A4:B5),联合运算符：逗号=RANK(A1(A1:A10,C1:C10))通常情况下，Excel按照从左向右的顺序进行公式运算，当公式中使用多个运算符时，Excel将根据各运算符的优先级进行运算，对于同一级次的运算符，则按自左而右的顺序运算。具体的优先顺序如下表：运算符优先级序号符号1: 空格 ，2-3%4^5*和/6+和-7＆8=,<>,>,<,>=,<=在使用Excel公式进行计算时，可能会因为某种原因无法得到正确结果，而返回一个错误值。常见的错误值及其含义如下表所示。错误值类型含义#####当使用了负的日期或负的时间时，出现错误#VALUE!当使用的参数或操作数类型错误时，出现错误#DIV/O!当数字被零(0)除时，出现错误#NAME?当Excel未识别公式中的文本时，出现错误#N/A当数值对函数或公式不可用时，出现错误#REF!当单元格引用无效时，出现错误#NUM!公式或函数中使用无效数字值时，出现错误#NULL!当指定并不相交的两个区域的交点时，出现错误当公式的结果返回错误值时，应该及时地查找错误原因，并修改公式来解决问题。03函数与分类Excel函数通常是由函数名称、左括号、参数、半角逗号和右括号构成。函数公式结构：=IF(A1>0,”正数”，IF(A1<0,负数,””))对于函数的参数来说，可以由数值、日期和文本等元素组成，也可以使用常量、数组、单元格引用或其他函数。当函数的参数也是函数时，Excel称之为函数的嵌套。函数一共有11类，分别是数据库函数、日期与时间函数、工程函数、财务函数、信息函数、逻辑函数、查询和引用函数、数学和三角函数、统计函数、文本函数以及用户自定义函数。整理一个非常全面的函数手册，需要的朋友关注我私信领取 。04常用函数本文内容为目录式的，介绍每个函数是做什么的、遇到某个问题可以用哪个函数解决等，具体使用方法各位可以自行百度学习。对于函数，不用死记硬背，只需要知道应该选取什么类别的函数，以及需要哪些参数怎么用就行了！比如选取字段，用LEFT／RIGHT／MID函数......其他细节交给万能的百度吧！下面根据不同的运用场景，对这些常用的必备函数进行分类介绍。1、关联匹配类需要的数据不在同一个Excel表或同一个Excel表不同sheet中，数据太多copy起来麻烦还容易出错，如何整合呢？下面这些函数就是用于多表关联或者行列比对时的场景，而且表格越复杂，用起来越爽！01.VLOOKUP功能：用于查找首列满足条件的元素。语法：=VLOOKUP (lookup_value,table_array, col_index_num, [range_lookup])*备注：[ ]内为可选参数，其余为必需参数，下文同理。=VLOOKUP (要查找的项、要查找位置、区域中包含要返回的值的列号、返回近似匹配或精确匹配 - 指示为 1/TRUE 或 0/FALSE) 。例：查询姓名是F5单元格中的员工是什么职务。02.HLOOKUP功能：在表格的首行或数值数组中搜索值，然后返回表格或数组中指定行的所在列中的值。HLOOKUP中的H代表“行”。语法：=HLOOKUP(lookup_value,table_array, row_index_num, [range_lookup])例：=HLOOKUP("车轴",A1:C4, 2, TRUE) 在首行查找车轴，并返回同列（列A）中第2行的值。LOOKUP和HLOOKUP区别：当比较值位于数据表格的首行时，如果要向下查看指定的行数，则可使用HLOOKUP。当比较值位于所需查找的数据的左边一列时，则可使用VLOOKUP。03.INDEX功能：返回表格或区域中的值或值的引用。语法：=INDEX(array,row_num, [column_num])例：= INDEX(B2:D11,3,3)位于区域A2:B3中第三行和第三列交叉处的数值。04.MATCH功能：用于返回指定内容在指定区域（某行或者某列）的位置。语法：=MATCH(lookup_value,lookup_array, [match_type])例：=MATCH(41,B2:B5,0) 单元格区域B2:B5中值41的位置。match_type：1或省略：MATCH 查找小于或等于lookup_value的最大值。0：MATCH查找完全等于lookup_value的第一个值。-1：MATCH查找大于或等于lookup_value的最小值。05.RANK功能：求某一个数值在某一区域内一组数值中的排名。语法：=RANK(number,ref,[order])例：=RANK(A3,A2:A6,1) A3在上表中A2:A6的排位排名方式：0是降，1是升序，默认为006.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/ SEARCH09.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(A2>1,A2<100) 如果A2大于1并且小于100，则显示TRUE；否则显示FALSE。25.OR功能：逻辑判断，相当于“或”。语法：只要参数有一个True，则返回Ture，经常用于多条件判断。例：=OR(A2>1,A2<100) 如果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.INT／ROUND功能：ROUND 函数将数字四舍五入到指定的位数。语法：=ROUND(A1, 2)例：=ROUND(2.15, 1)将2.15四舍五入到一个小数位功能：INT将数字向下舍入到最接近的整数。语法:=INT(8.9) 将 8.9 向下舍入到最接近的整数。5、时间序列类专门用于处理时间格式以及转换。37.TODAY功能：返回当前日期的序列号。语法：=TODAY()li'z=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")忽略日期中的年份，一段时期内的天数附：函数参数或其他常用单词Sheet(Worksheet)工作表Workbook工作簿Cell单元格TRUE真FALSE假Logical_test逻辑判断式Value值Value if true如果为真Value if false如果为假Logical逻辑Value if error如果错误Function num函数编号Number数字Ref (reference)涉及的内容Range范围Criteria标准Sum range求和的范围Digits数字Divisor除数Lookup查找Lookup value查找的值Array数组、区域、阵列Col index num (Col=Column)索引的列号Num (Number)数字的缩写Type类型Text文本Num chars字符数量}

专栏/EXCEL函数公式有哪些？EXCEL函数公式大全介绍，电脑新手值得收藏2023年02月06日 09:36--浏览 ·
--喜欢 ·
--评论EXCEL中的函数隐藏了有很多，但是很多人却不记得具体的函数公式，运用函数可以简便我们日常的工作，下面电脑百科林老师就来给大家介绍一下EXCEL函数公式大全，还不知道的同志赶紧来看看吧！1、文本函数2、统计函数3、数学函数4、日期与时间函数5、逻辑函数6、查找与引用函数—《END》—目录------2}

2022年08月25日 10:03--浏览 ·
--喜欢 ·
--评论大家好，我是小良。01. 单条件求和公式：F2=SUMIF(A:A,E2,C:C)说明：SUMIF函数的基本用法02. 忽略错误值求和如下图所示，B列数据中有部分错误值，使用以下公式，可以对B列数据进行求和。=SUMIF(C2:C12,"<9e307")SUMIF函数省略求和区域时，会自动对求和区域进行汇总。9e307，就是9*10^37，是一个非常大的数值。本例的求和条件使用"<9e307"，就是对条件区域中，小于这个最大值的所有数值进行汇总，并且SUMIF函数会自动忽略错误值。SUMIF函数的条件区域使用A:E的整列引用，求和区域使用B:F的整列引用，如果A:E等于指定的店铺，就对B:F对应的内容进行求和。03. 使用通配符求和除了按精确条件进行求和汇总，还可以根据部分关键字进行汇总。如下图，要根据D列的商品名称关键字，来统计对应的销售数量。=SUMIF($A$2:$A$11,"*"&D2&"*",$B$2:$B$11)公式中的求和条件使用"*"&D2&"*"，也就是在D列商品名称前后各连接上一个星号*。星号在这里的作用是通配符，就是A列中只要包含关键字，就会对B列对应的数量进行汇总。04. 截取字符串中任一段的公式公式:B1=TRIM(MID(SUBSTITUTE($A1," ",REPT(" ",20)),20,20))说明:公式是利用强插N个空字符的方式进行截取05. 根据出生年月计算年龄=DATEDIF(A2,TODAY(),"y")TODAY函数返回系统当前的日期。DATEDIF函数以A2的出生年月作为开始日期，以系统日期作为结束日期，第三参数使用Y，表示计算两个日期之间的整年数。06. 判断销售额是否达标IF函数是最常用的判断类函数之一，能完成非此即彼的判断。常规用法是：IF(判断的条件,符合条件时的结果,不符合条件时的结果)如下图，考核得分的标准为9分，要判断B列的考核成绩是否合格。=IF(B4>=9,"合格","不合格")07. 多条件判断销售状况IF函数除了能进行简单的一次性判断，而且还可以对同一个条件进行多重判断。如下图，要按照不同的区间范围，判断B列的成绩是否合格。低于9分是不合格，达到9分是合格，达到9.5分是优秀。=IF(B5>=9.5,"优秀",IF(B5>=9,"合格","不合格"))公式先判断B5大于9.5的判断条件是否成立，如果符合条件就返回指定的内容“优秀”。如果条件不成立，就继续判断下一个条件，看看B5>=9的条件是否成立，如果成立就返回指定的内容“合格”。如果条件仍然不成立，就返回指定的内容“不合格”。使用IF函数进行多个区间的判断时，特别要注意：可以从最高开始，向最低依次判断，也就是先判断如果比最高的条件还要高，是什么结果。也可以从最低开始向最高依次判断，也就是先判断如果比最低的条件还要低，是什么结果。08. 使用VLOOKUP函数查询数据VLOOKUP函数一直是大众情人般的存在，函数的语法为：VLOOKUP(要找谁,在哪儿找,返回第几列的内容,精确找还是近似找)如下图，要查询F5单元格中的员工姓名是什么职务。=VLOOKUP($F$5,$B$1:$D$10,2,0)使用该函数时，需要注意以下几点：1. 第4参数一般用0（或FASLE）以精确匹配方式进行查找。2. 第3参数中的列号，不能理解为工作表中实际的列号，而是指定返回值在查找范围中的第几列。3. 如果查找值与数据区域关键字的数据类型不一致，会返回错误值#N/A。4. 查找值必须位于查询区域中的第一列。}