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

常用函数: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:D9 C7:E10)中间用空格隔开3.乘方运算=554.整行、列 7:7 7:10 A:A A: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,”100,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=3Eg：=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的基础概念和一些日常碰到的问题。例一这里就是一个vlookup函数的最简单的雏形。通过李四名字进行查找到相应的成绩。这里我先解释一下这个函数=VLOOKUP(E2,A2:B4,2,FALSE)E2代表了李四，就是用户查找内容的条件，A2:B2代表的是需要查找的区域，2代表的是该区域的第二列，就是成绩列，FALSE是代表了精确查找，和TRUE（模糊查找）相对，但是大多数情况下都是用FALSE，TRUE要使用的时候情况比较特殊。vlookup的四个参数，第一：单元格。第二：区域(条件所在列必须是所属区域第一列）。第三：数字，必须是正数（不能超出你区域的总列数）。第四，一般用false或0。这里如果明白了，先恭喜你，你已经踏入了这个函数的门了。例二：例一只是引入门，我会在例二部分，充分的讲解该函数的使用和注意事项。表格有了变化，大家注意一下，现在问一个问题，如果我要查找李四的年龄怎么办，1，2，3，考虑完了，就是把函数中的列数选中为“3”，这里我要说一些注意事项。1、这里的“3”不是指该表格的第三列，而是选取范围的第三列，该表格的选取范围是B2：E6，年龄就是为第三列。2、“李四”是作为函数的检索条件，“李四”所在的列，必须是在该函数的第一列，如果B2：E6我改成A2：E6会是什么结果呢？如图是的，它出错了。3、因为所在列必须是第一列，所以也就只能提取右边的数值，不能提取左边的，提取可以用index+match或是if（1,0），不过这个不在这个教程内阐述。4、如果一列里有出现多个“李四”的情况下，默认只提取第一个。5、vlookup查找时注意查找值与查找区域的第一列的格式设置必须一致。 如果不一致也会出错，如图：我这里使用学号进行检索姓名，但是A列和H2的格式不同，也无法得出结果。6、如果要下拉vlookup公式，在第二参数里面加上“$”符号。没加“$”，下拉后，第二参数的区域就变动了。所以需要加“$”把你要查找的区域固定住。如图：一个是没有加“$”的截图，一个是加了“$”的截图。注意看函数的位置，如果