EXCEL最有用的函数公式用法大全

常用函数:1、VLOOKUP（查找目标，查找范围，返回值的列数，精确OR模糊查找)2、LEFT就是提取该数值的前几位，LEFT(H2，2)就是H2的前2位。3、IFIF4、四舍五入:=ROUND(B5,2)=ROUND(SUM(G12:G14),3)’2,3为保留小数的位数’5、直接舍去:=ROUNDDOWN(B5,2)=ROUNDDOWN(SUM(G12:G14),3)’2,3为保留小数的位数’6、COLUMN(B1)返回值17、COUNTIF：求得分大于等于80分小于90分的学生数=COUNTIF(B2:B20,=80)-COUNTIF(B2:B20,=90)8、sumif=sumif(range，criteria，sum_range)Sumif（条件区域，求和条件，实际求和区域），第二个求和条件参数在第一个条件区域里。6、access。1、excel单元格之中同时按键盘上的Alt和Enter组合键，这时光标就在同一个单元格内切换到下一行5、单元格的“绝对引用”、“混合引用”和“相对引用”：绝对引用：绝对地址=$A$1复制后永远引用相同单元格地址混合引用相对引用:绝对列=$A1绝对行=A$1数学和三角函数1.=IF(A10,”增加”,”减少”)2.交集运算=SUM(B7:D9C7:E10)’中间用空格隔开’3.乘方运算=5^54.整行、列7:77:10A:AA:Ceg:=SUM(7:7)=SUM(A:C)5.1.四舍五入:=ROUND(B5,2)=ROUND(SUM(G12:G14),3)’2,3为保留小数的位数’5.2.直接舍去:=ROUNDDOWN(B5,2)=ROUNDDOWN(SUM(G12:G14),3)’2,3为保留小数的位数’6.三维引用:多张工作表同一单元格区域=SUM(SHEET1:SHEET3!B7)=AVERAGE(SHEET1:SHEET3!B7)7.绝对地址=$A$1’复制后永远引用相同单元格地址’混合引用绝对地址:绝对列=$A1绝对行=A$18.保护单元格区域内容：菜单栏→工具→保护→允许用户编辑区域9.单元格编辑栏修改单元格内容，或按F2键修改10.阿拉伯数字转成罗马数字，不能为负数，且必须小于3999=ROMAN(number,form)’数字,格式’=ROMAN(499,0)=ROMAN(499,1)=ROMAN(499,2)=ROMAN(499,3)=ROMAN(499,4)0’经典的’1’较简洁的’2’较简洁的’3’较简洁的’4’简化的’TURE为经典的FALSE为简化的11.所有参数相乘=PRODUCT(4,5)=PRODUCT(A1:A3,C1C3)12.产生随机数字=RANDBETWEEN(BOTTOM,TOP)=RANDBETWEEN(1,100)13.=SUBTOTAL(function_num,ref1.ref2,…ref29)Function_Num的设置值所应用的函数1AVERAGE2COUNT3COUNTA4MAX5MIN6PRODUCT7STDEV8STDEVP9SUM10VAR11VARPVLOOKUP（查找目标，查找范围，返回值的列数，精确OR模糊查找)14.对满足条件的单元格求和=SUMIF(range,criteria,sum_range)=SUMIF(区域,条件,要求和区域)range要进行筛选的单元格区域criteria设置筛选的”条件”(条件可以是文字)sum_range实际求和的相对应单元格区域ABCD1123100245620037893004SUMIF函数公式结果51=SUMIF(A1:A3,”600”,C1:C3)30062=SUMIF(A1:A3,”400”,C1:C3)50073=SUMIF(A1:A3,”100”,C1:C3)60084=SUMIF(A1:A3,”400”,C1:C3)100日期与时间函数1.=DATE(year,month,day)year1900～9999month若值大于12，则会将12的余数设置为月份，15=3月day若日数大于所指定月份的日数，则余数为该月份日数eg：=DATE(2002,1,35)即为2002年2月4日右键可在【设置单元格格式】选择日期类型2.计算2个日期之间的相差天数=DATE(2003,3,8)-DATE(2002,6,5)3.系统时间=NOW()系统日期=TODAY()4.日期所对应的星期数值=WEEKDAY(serial_number,return_type)=WEEKDAY(序列号,返回值类型)serial_number可用DATE()函数输入return_type若为1或默认，则1代表周日，7代表周六若为2，则1代表周一，7代表周日若为3，则0代表周一，6代表周日eg:=WEEKDAY(A1)=WEEKDAY(A1,2)=WEEKDAY(A1,3)5.日期所对应的该星期为一年中的第几周=WEEKNUM(serial_number,return_type)=WEEKNUM(序列号,返回值类型)serial_number可用DATE()函数输入return_type1,一周是从周日开始计算2,一周是从周一开始计算eg:=WEEKNUM(A1,1)=WEEKNUM(A1,2)信息函数1.计算指定区域空白单元格的数目=COUNTBLANK(range)2.测试值是否为数字=ISNUMBER(value)3.测试值是否为偶数=ISEVEN(value)4.测试值是否为奇数=ISODD(value)5.当前目录或文件夹的路径名称=INFO(“directory”)逻辑函数1.AND()所有的参数都为TURE时，结果才是TURE=AND(logicall1,logicall2,…,logicall30)’logicall1～30’eg:=AND(TRUE,FALSE)结果为FALSE=AND(TRUE,TRUE)结果为TRUE=AND(A1,A2)结果为FALSE=AND(A1,A1)结果为TRUE=IF(AND(A1100,A490),”测试成功”,”测试失败”)2.对参数求反=NOT(logicall)Logicall为TRUE时结果为FALSE;Logicall为FALSE时结果为TRUEeg:=NOT(FALSE)结果为TRUE=NOT(TRUE)结果为FALSE=NOT(1+1=2)结果为FALSE3.OR()其中一个参数为TURE时，结果就为TURE=OR(TRUE,FALSE)结果为TRUE=OR(FALSE,FALSE)结果为FALSE=OR(1+1=1,2+2=5)结果为FALSE=IF(OR(),””,””)文本函数1.将数字转换为泰文=BAHTTEXT(NUMBER)=BAHTTEXT(A1)2.将数字四舍五入到指定的小数位数=FIXED(number,decimals,no_commas)=FIXED(数字,小数点位数,逻辑值)number要被进行四舍五入的数字decimals指定小数点位数，默认值为2no_comma默认值为FALSE或省略，则返回的文本中包含有逗点9，876.10，若为TRUE则无逗点9876.10=FIXED(-1234.567,-1,TRUE)结果为-1230decimals正数表示小数点后的位数，负数表示小数点左边位数。3.将文本字符串中英文单词的第1个字母改为大写=PROPER(text)text只能为单元格引用，英文须写在单元格。***=LOWER(text)大写字母全部改小写VLOOKUP函数1.VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)lookup_value目前新文档查找目标table_array查找范围(绝对地址$$)要包含查找目标及目标参数col_index_num返回值的列数，是查找范围的第几列，非原表列数range_lookup精确OR模糊查找。0或FALSE表示精确查找；1或TRUE表示模糊查找切勿遗漏第四参数，若遗漏则默认值为模糊查找。eg：B13=VLOOKUP(A13,$B$2:$D$8,3,0)或B13=VLOOKUP(A13,$B:$D,3,0)★目标表变动大可扩大预留空白区域或整列引用选定公式内查找范围按F4插入绝对引用$符号★“跨表引用”查找范围目标表可以更改名称和所在工作簿位置★“跨簿引用”目标表被删除后，数据→编辑链接→断开链接★★★快速修改第三参数（返回值列数）：方法1：另一空白行对应处写对应序数，然后第三参数引用单元格。方法2：第三参数用“column”函数（函数意义“列”转换为“数字”）A=1，B=2，C=3……Eg：=VLOOKUP(A13,$B$2:$D$8,COLUMN(C1),0)Eg：=IF(A13=””,””,VLOOKUP(A13,$B$2:$D$8,3,0))避免出现#N/A★★如果返回值为#N/A报错，而不想显示#N/A而显示空白或其他Eg：=IF(ISNA(VLOOKUP(A13,$B$2:$D$8,3,0),””,VLOOKUP(A13,$B$2:$D$8,3,0))数据有效性→序列→选定区域创建单元格下拉菜单数据有效性跨表引用备案表选定引用区域→公式→定义名称；操作表数据有效性→序列→来源输入“=公式名称”★★★复合查找=IF（VLOOKUP=0,VLOOKUP返回值其他列，VLOOKUP）=IF（VLOOKUP(A13,$B$2:$D$8,3,0)=0,VLOOKUP(A13,$B$2:$D$8,4,0),VLOOKUP(A13,$B$2:$D$8,3,0))★★★重复数据“双列/多列结合查找”，“&”合并两列/多列成为“辅助列”，以“辅助列”为查找标的。VLOOKUP的基础概念和一些日常碰到的问题。例一imgsrc==474data-rawheight=126class=origin_imagezh-lightbox-thumbwidth=474data-original=这里就是一个vlookup函数的最简单的雏形。通过李四名字进行查找到相应的成绩。这里我先解释一下这个函数=VLOOKUP(E2,A2:B4,2,FALSE)E2代表了李四，就是用户查找内容的条件，A2:B2代表的是需要查找的区域，2代表的是该区域的第二列，就是成绩列，FALSE是代表了精确查找，和TRUE（模糊查找）相对，但是大多数情况下都是用FALSE，TRUE要使用的时候情况比较特殊。vlookup的四个参数，第一：单元格。第二：区域(条件所在列必须是所属区域第一列）。第三：数字，必须是正数（不能超出你区域的总列数）。第四，一般用false或0。这里如果明白了，先恭喜你，你已经踏入了这个函数的门了。例二：例一只是引入门，我会在例二部分，充分的讲解该函数的使用和注意事项。imgsrc==670data-rawheight=154class=origin_imagezh-lightbox-thumbwidth=670data-original=表格有了变化，大家注意一下，现在问一个问题，如果我要查找李四的年龄怎么办，1，2，3，考虑完了，就是把函数中的列数选中为“3”，这里我要说一些注意事项。1、这里的“3”不是指该表格的第三列，而是选取范围的