1、基本函数1. Vlookup：（查找值，查找的区域，返回位置（列），查找方式)查找方式：精确查找：false，0，空(有逗号）。模糊查找：true，1，为空（无逗号）-区域必须为升序例：vlookup(a30,e$30:g$33,3,) vlookup(a30,e$30:g$33,3) vlookup（c20，0,无,2,15天，5,”20天,10,30天，2）注：vlookup默认都是向右查找，利用if能让vlookup向左查找，但只支持两列例：vlookup（A22，IF(1,0,e10:e18,d10:d18),2,0)2. Count：计算一个区域中包含有数据的单元格有几个；count
2、（a12:v22）,； COUNT(1/(A13:C16=张无名):区域内出现”张无名”个数，数组函数（alt+ctr+anter） Counta： 计算单元格非空值的个数 Countblank：求空值个数 Countif：用来计算区域中满足给定条件的单元格的个数；例如：大于等于2000的人数：COUNTIF(D12:D19,=2000)；1500-2000的人数：SUM(COUNTIF(D12:D19,=1500,=2000)*1,-1)姓为张的人数：COUNTIF(A12:A19,张*)求出不重复总个数：SUM(1/COUNTIF(A24:A32,A24:A32)，（数组）；求不重复总个数
3、（含有空格）：SUM(IF(A37:A45=,1/COUNTIF(A37:A45,A37:A45)；将表中重复记录“*”表示出来：if（countif(A$20:A$29,A20)1,*,);求出每个部门人数：countif（c$5:c$19,c6),c6表示其中一个部门统计区域类100的数量：countif（k2:k5，“100”）3. sumproduct函数：SUMPRODUCT(条件1)*(条件2)* (条件3) *(条件n)*某区域)：汇总同时满足条件1、条件2到条件n的记录指定区域的汇总金额 SUMPRODUCT(条件1)*(条件2)*(条件3)* (条件n)：统计同时满足条件1、
4、条件2到条件n的记录的个数。 SUMPRODUCT(A11:A13*C11:C13)+(B11:B13*D11:D13)计算数组乘积的和； SUMPRODUCT(B20:B24=男)*(C20:C24=中级) SUMPRODUCT(A43:A51=花生,梨子)*B43:B51)：花生和梨子总数量男业务员工资总额：SUMPRODUCT(B41:B48=男)*(C41:C48=业务员)*D41:D48)4. Sumif：根据指定条件对若干单元格求和。SUMIF(用于条件判断范围,满足条件,求和的实 际单元 例如：SUMIF(A15:A23,花生,B15:B23)； SUMIF(A29:A37,10
5、0)=SUMPRODUCT(A29:A37100)*A29:A37)大于150和小于50的总数量：SUMIF(B43:B51,150,=2000)-SUMIF(D29:D36,=5000)=SUMPRODUCT(D29:D36=2000)*(D29:D36=1500)，SUMPRODUCT(A20:A28=张三 )*(B20:B28=25)：满足张三且=25个数6. if函数：如果大于2000小于3000评为差，大于3000小于5000评为中，大于5000评为优；=IF(x5000,优,if(x3000,中,if(x2000,差,极差)7最小值：min（k2:k5)，最大值：max（k2:k5
6、)8. ABS（a2）：在a2单元格输入正负数都取绝对值9. AVERAGE(B7:D7,F7:H7,7,8)，确认后，即可求出B7至D7区域、F7至H7区域中的数值和7、8的平均值。10. CONCATENATE ：合并函数；CONCATENATE(A14,B14,.com)，确认后，即可将A14单元格中字符、B14单元格中的字符和.com连接成一个整体，显示在C14单元格中。11 int：将数值向下取整为最接近的整数；INT(18.89)，确认后显示出18,如果输入的公式为=INT(-18.89)，则返回结果为-1912. 假定A38单元格中保存了“我喜欢天极网”的字符串，我们在C38单元
7、格中输入公式：=LEFT(A38,3)，如果省略3，则默认假设为1，如果大于文本长度，则默认全部，确认后即显示出“我喜欢”的字符。LEFT(G26)&IF(H26=女,小姐,先生)=赵小姐13. LEN函数:统计文本字符串中字符数目。假定A41单元格中保存了“我今年28岁”的字符串，我们在C40单元格中输入公式：=LEN(A40)，确认后即显示出统计结果“6”。 Lenb:计算文本字节长度，一个汉字两个字节。例：546502445开心号RIGHT(A18,LENB(A18)-LEN(A18)=开心号14. 输入公式：=NOW()，确认后即刻显示出当前系统日期和时间。如果系统日期和时间发生了改变
8、，只要按一下F9功能键，即可让其随之改变。today():提取系统日期；输入当前系统日期：ctrl+； 输入当前系统时间：ctrl+shift+；今天是今年的第几天：today（)-1-1+1; 算员工工龄：year(today()-year(b5),b5:出生日期，结果需要进行日期和数 字转换ctrl+shift+ IF(MONTH(C34)=30)*100 要求：性别=男,且年龄=30岁，补助金额100元？，例题：=IF(AND(O17=160,O17未考),优秀,)；16. 数组：用“,”(逗号)把列数组元素分开；用“;”(分号)把行数组元素分开，=2,4,9*2=2;4;9*2。大于8
9、0的数字求和？：=SUM(IF(A26:B2880,A26:B28,)，注意：内存数组须以ctrl+shift+enter 求金额总和：sum（b4:b9*c4:c9),sum(if(c4:c980,1); 求语文和数学同时为优的个数：count（if（a14:a23&b14:b23=优优），ctr+shift+enter 金额大于2000的平均分？：sum（if(b30:b352000,b30:b35)/count(if(b30:b352000,b30:b35)其他：*在单元格中打钩，按住Alt键不放，再输入小数字键盘上的数字41420，松开Alt键就可以了。在网页：x ALT+41409
10、ALT+41420*怎么样才能不复制这些隐藏单元格呢？ 开始-选择与查找-定位条件-可见单元格- 又称减负运算, 是将文本，罗辑值或日期值转换为数值, 相当于value.Ctrl+l 缩短字间距Substitute数学函数1.mod（number，divisor），number/divisor,值返回余数到最接近的整数，2. int ：将数字向下舍入int（2.66）=23. trunc：将数字的小数部分截去，返回整数。4. round(number,num_digits),返回某个数字指定位数取整后的数字。如果 num_digits 大于 0，则四舍五入到指定的小数位；如果 num_digi
11、ts 等于 0，则四舍五入到最接近的整数；如果 num_digits 小于 0，则在小数点左侧进行四舍五入。 roundup 向上舍入，rounddown 向下舍入5 product（a5:b5),相当于*，6 power（number，power）：number底数，power 指数，次方7 large：返回数据集中的第k个最大值。例如：large（a1：c4，4） a1至c4区域中第四大的值文本函数1. Mid ：从字符串中返回指定数目的字符。 Mid(text,start_num,num_charts) ,要提取文本字符串，要提取的第一个字符，希望mid从文本中返回字符的个数。 例如：m
12、id（12345,2,2）=232. find：（查找文本，文本所在文本，指定从其开始搜索，默认1）例如：a1=我爱函数， find（”爱”,a1）=2；A28=Q78QWE，MID(A28,FIND(Q,A28,FIND(Q,A28)+1)+1,LEN(A28)=weCOUNT(FIND(B48,$B$38:$B$45)统计b48中内容出现的次数A55=jim把衣服洗了，LEFT(A55,MIN(FIND(把,被,A55&把被)-1)=把衣服洗了3. substitute: (text,old_text,new_text,instance_num); text必需，需要替换其中字符的文本，含
13、有文本的单元格引用；old_text：必需，需要替换的旧文本，instance_num：用于替换old_text的文本。增补函数1. small（array,k）: 返回数据集中第 k 个最小值。使用此函数可以返回数据集中特定位置 上的数值。查找函数1. row(refereence):返回当前函数所在的行号。例如：row(F7:F15)=7column(refereence)：返回当前函数所在的列号 例如：column(f:h)=6rows(array):计算行数，如：rows(a2:a22)=21,row(2:9)=8columns(array):计算列数2. Vlookup进阶(vloo
14、kup第三参数，序列号的数组运用)例如：计算全年工作总额（2到13列为每个月），sum(vlookup(a17,a6:m14,row(2:13),):数组函数。找出没有做漏做工资表的员工：if(isna(vlookup(a22,m$23:m$33,1,),”x”,”),isna:判断是否为空的函数多条件查询：通过vlookup函数实现多条件或双条件查找并返回值，那么只需要加上If(1,0)就可以实现，例如：VLOOKUP(F40&G40,IF(1,0,A39:A47&B39:B47,C39:C47),2,)，要查询单元格：f40+g40，A39:A47&B39:B47,C39:C47：表示查找
15、范围,数组函数3. Hlookup（查找的值，查找的区域，返回的行号，查找方式）：HLOOKUP(C4,A8:M17,5,)= HLOOKUP(C4,A8:M17,row(5:5),)4. Lookup(lookup_value,lookup_vector,result_vector):lookup_value:查找的值，可以为数字、文本、逻辑值或包含数值的名称或应用,数值必须升序排列，-2、-1、1、2一行或一列的区域。可以为文本、数字或逻辑值result_vector：只包含一行或一行的区域，其大小必须与lookup_vector相同例如：LOOKUP(E15,A15:A20,C15:C2
16、0)，C15:C20：查找值LOOKUP(9E+307,H14:H19,J14:J19)：9e+307最大的不可能出现的数，因而返回的是第n行中最末一列的数值数组用法：LOOKUP ( lookup_value , array ) lookup_value：在数组中所要查找的数值。可以为数字、文本、逻辑值或包含数值的名称或引用。Array：包含文本、数字或逻辑值的单元格区域，它的值用于与 lookup_value 进行比较。例如：LOOKUP(E34,A34:D39)，被查找的区域要升序。5. Index(array,row_num,column_num),array:单元格区域或数组常量，row_num:数组中某行 序号。Column_num:数组中某列的序列号， 如：index（a1:f11,2,3）; INDEX(B29:F35,MATCH(J28,A29:A35,),MATCH(J29,B28:F28,);INDEX($A10:$A20,A43) a43=1INDEX($A10:$F10,0,A50)
