常用Excel公式用法实例.doc

常用Excel 公式用法实例一、 数值处理1、 取绝对值n ABS（Number） 简单取绝对值=ABS（A4） 加减乘除嵌套=ABS（B2-B3） IF组合=IF（B2C2，“进步”，“退步”）&ABS（B2-C2）&“分”2、 取整n 格式取整：四舍五入n INT（Number）：小于此数最接近的整数n TRUNC（Number）：截去小数部分n ROUND（Number，num_chars）：指定小数位数的四舍五入二、 判断1、 IF函数n IF(logical_test, value_if_true, value_if_false) 简单比对与嵌套比对B2 = IF（A260，“不及格”，“及格“）C2 = IF（A260，“不及格”，IF（A270，“及格”，IF（A280，“良好”，“优秀”）2、 IFERROR函数n IFERROR(value, value_if_error) Value_if_error：错误值特定显示#N/A、#VALUE!、#REF!、 #DIV/0!、 #NUM!、 #NAME? 、#NULL!空格=IFERROR（A2/B2，“”） 0 =IFERROR（A2/B2，0）三、 统计1. 数字统计n COUNT(value1, value2, .)：数字个数统计n COUNTA(value1, value2, .)：单元格使用个数统计n COUNTBLANK（Range）：空白格个数统计=COUNT(A2 : B9) 5=COUNTA(A2 : C9) 18=COUNTBLANK（A2:C9） 62. 单区域条件统计n COUNTIF(range, criteria) 准确条件统计=COUNTIF（A1:A10，“高级”） 多条件统计=COUNTIF（A2:A14，“75”）- COUNTIF（A2:A14，“60”） 返回包含值12的单元格数量 =COUNTIF（DATA,12） 返回包含负值的单元格数量 =COUNTIF（DATA，0） 返回不等于0的单元格数量 =COUNTIF（DATA，0） 返回大于5的单元格数量 =COUNTIF（DATA，5） 返回等于单元格A1中内容的单元格数量 =COUNTIF（DATA，A1） 返回大于单元格A1中内容的单元格数量 =COUNTIF（DATA，&A1） 返回包含文本内容的单元格数量 =COUNTIF（DATA,*） 返回包含三个字符内容的单元格数量 =COUNTIF（DATA，?） 返回包含单词GOOD(不分大小写)内容的单元格数量 =COUNTIF（DATA,GOOD） 返回在文本中任何位置包含单词GOOD字符内容的单元格数量 =COUNTIF（DATA,*GOOD*） 返回包含以单词AB(不分大小写)开头内容的单元格数量 =COUNTIF（DATA,AB*） 返回包含以单词AB(不分大小写)结尾内容的单元格数量 =COUNTIF（DATA,*AB） 返回包含当前日期的单元格数量 =COUNTIF（DATA，TODAY()） 返回大于平均值的单元格数量 =COUNTIF（DATA，&AVERAGE(DATA) 返回平均值上面超过三个标准误差的值的单元格数量 =COUNTIF（DATA,&AVERAGE(DATA)+STDEV(DATA)*3) 返回包含值为3或-3的单元格数量 =COUNTIF（DATA，3）+COUNIF（DATA，-3） 返回包含值;逻辑值为TRUE的单元格数量 =COUNTIF（DATA，TRUE） 统计区域中不为空的单元格个数（数值、文本、空格都算）（上述第3条：文本也算不等于0，空格不算） =Countif(DATA,) 只统计文本单元格数量，不统计数值和空格（上述第7条统计含空格） =COUNTIF(DATA,80000”）四、 求和1、 公式n SUM(number1,number2,.)：指定参数的所有数字相加n SUMIF(range, criteria, sum_range)：（条件区域，求和条件，实际求和区域）区域中符合指定条件的值求和n SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2, .)：区域中满足多个条件的单元格求和n SUMPRODUCT(array1, array2, array3, .)：几组数组中,将数组间对应的元素相乘,并返回乘积之和n SUMSQ(number1, number2, .)：参数的平方和n SUMX2MY2(array_x, array_y)：数值的平方差之和n SUMX2PY2(array_x, array_y)：数值的平方和之和n SUMXMY2(array_x, array_y)：数值之差的平方和2、 应用2.1、SUM 多表相同位置求和=SUM(sheet1:sheet19,B2)2.2、SUMIF（条件区域，求和条件，实际求和区域） 单条件求和1=SUMIF(A3:A14,D3,B3:B14) 单条件模糊求和2=SUMIF（A2:A4, “*A*”,C2:C4）42=SUMIF（A2:A4, “A*”,C2:C4） 24=SUMIF（A2:A4, “*A”,C2:C4） 18注：引号（”）需使用英文半角符号 单条件求和3-1 （无第三参数）=SUMIF（D2:D8,”=95”） 单条件求和3-2=SUMIF（D2:D8,”=95”,F2:F8）2.3、SUMIFS（求和区域,条件1区域,条件1,条件2区域,条件2,条件3区域,条件3） 多条件求和=SUMIFS（C2:C8,B2:B8,”男”）=SUMIFS（F2:F8,C2:C8,”=90”,D2:D8,”=90”）=90分之和2.3、SUMPRODUCT 两数组相乘求和=SUMPRODUCT（A1:A10）*（B1:B10）Note：A1*B1+A2*B2+A10*B10 符合数组1条件的数组2的和=SUMPRODUCT（A1:A10=4）*（B1:B10）Note：若数组1中数值为4，则（A1:A10=4）=1（True），否则为0（False） 0*11+1*12+0*13+1*14+0*10=43 与日期组合求和n 特定日期产量查询=SUMPRODUCT（A2:A63=DATE（H2,I2,J2）*（B2:B63）Note：统计日期为本日（Date（H2,I2,J2）的量产数据n 特定月份的产量统计=SUMPRODUCT（YEAR（A2:A63）=H2）*（MONTH（A2:A63）=I2）*（A2:A63=DATE（H2,I2,J2）*（B2:B63）Note：这就有一个较为复杂的逻辑界定。其一，我们统计本月的数据，就要用条件MONTH(A2:A63)=I2)。其二，我们仅有上面条件不足以统计出正确数据，因为必须要考虑到历史查询情况，就是说，查询日为10日，但是10-31日是有数据的，因此还必须加上如些条件)（A2:A63=DATE(H2,I2,J2)，就是当月数据还要小于查询日。其三，有些时候，数据中有一年以上的数据，所以仅有上面两个条件还不行，如查询本月2月，就可能把去年2月的数据也统入其中了，还得加上条件(YEAR(A2:A63)=H2)，既“年”等于XX年。五、 查找与引用1、 公式LOOKUP(lookup_value, lookup_vector, result_vector)：向量形式LOOKUP(lookup_value, array)：数组形式VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)：纵向查找HLOOKUP(lookup_value, table_array, row_index_num, range_lookup)：横向查找INDEX(reference, row_num, column_num, area_num)：返回特定位置的内容INDEX(array, row_num, column_num)：数组形式MATCH(lookup_value, lookup_array, match_type)：返回指定内容所在的相对位置FIND(find_text, within_text, start_num)：返回指定单元格某字符在文本串中位置的值2、 应用2.1、LOOKUP 按条件查找=LOOKUP（D2,B2:B15,C2:C15）Note：从B2：B15列里面找D2，并返回C2：C15相对应行的值（数值经常需按升序排列）2.2、VLOOKUP 最简运用=VLOOKUP（”C”,A2:B6,2,1）（查找值，查找区域，返回所查找值所在列号，匹配条件）Note：匹配条件：【1/True】-模糊查找 【0/False】-精确查找 跨表纵向查找n Sheet名+！（英文状态下）=引用该表下的数据 如：Y1！，sheet1！A1：A10n 加“$”表示绝对引用 如：$A$1:$B$6：绝对引用（A1:B6）的区域 绝对引用快捷键：F4（windows） Command+T（Mac）B1=VLOOKUP（Y1!A6,Y1!$A:$C,3,0）Note：在Y2 B1单元格中精确查找表Y1中WillisChoi的产品编号2.3、HLOOKUP 跨表横向查找B1=HLOOKUP(sheet2!$A1,sheet1!$A$1:$D$2,2,0)Note：在sheet2 B1单元格中精确查找表sheet1中张三对应的数值2.4、INDEX 特定位置查找E2=INDEX（D2：F11, 3, 3）Note：返回区域（D2:F11）中第三行第三列的值 特定位置查找E2=INDEX（B2：C11, E2：F11）, 2, 2, 2）Note：区域1（B2:C11），区域2（E2:F11） 返回两个区域中区域2（2）的第二行第二列（2,2）的值2.5、MATCH（目标值，查找区域，0/1/-1）【0:精确; 1:=】 运用实例E2=MATCH(C2,B2:D2,0)Note：查找5月份在查找区域（B2:D2）的位置2.6、INDEX与MATCH组合使用 运用实例G4=INDEX(A1:D10,MATCH(F2,A1:A10,0),MATCH(F4,A1:D1,0)Note：MATCH(F2,A1:A10,0)=3（行） MATCH(F4,A1:D1,0)=4（列） 对应的INDEX函数=INDEX(A1:D10,3,4) ,所得到的即D3单元格数值210 2.7、FIND（要查找的文本，文本所在的单元格，从第几个字符开始查找【默认1】） 运用实例B3=FIND（”i”，A1，1）Note：返回“i”在A1单元格中处于第几个字符 与LEFT函数组合B3=LEFT（A2，FIND（”，A2，1）-1）Note：返回“”之前的所有字符六、 字符串处理1、 公式LEFT(text, num_chars)：从单元格左边第一个字符开始截取指定长度RIGHT(text,num_chars)：从单元格右边第一个字符开始截取指定长度MID(text, start_num, num_chars)：从单元格指定位置开始从左至右截取指定长度LEN(text)：返回字符个数LOWER(text)：字符串转换成小写UPPER(text)：字符串转换成大写PROPER(text)：字符串首字母转换成大写，其余小写PHONETIC(reference)：所选单元格字符合并REPLACE(old_text, start_num, num_chars, new_text)：新字符替换某段字符串2、 运用2.1、LEFT 截取前部分=LEFT（A1,3）Note：取A1单元格字符串的前三位 LEFT&FIND组合=LEFT（A1,FIND（“-“,A1）-1）Note：截取A列中“-”前的字符2.2、RIGHT 截取后部分=RIGHT（A2,3）Note：提取A2单元格后三位字符2.3、MID 从任何位置截取字符串=MID（A1,1,4）Note：截取A1单元格中从第1为开始的4个字符 MID&FIND组合=MID（A2，FIND（“省”，A2）+1，FIND（“市”，A2）-FIND（“省”，A2）Note：从“省”之后开始截取，长