常用Excel公式用法实例.docx
- 文档编号:4940550
- 上传时间:2022-12-11
- 格式:DOCX
- 页数:18
- 大小:2.76MB
常用Excel公式用法实例.docx
《常用Excel公式用法实例.docx》由会员分享,可在线阅读,更多相关《常用Excel公式用法实例.docx(18页珍藏版)》请在冰豆网上搜索。
常用Excel公式用法实例
《常用Excel公式用法实例》
一、数值处理
1、取绝对值
⏹ABS(Number)
Ø简单取绝对值
=ABS(A4)
Ø加减乘除嵌套
=ABS(B2-B3)
ØIF组合
=IF(B2 2、取整 ⏹格式取整: 四舍五入 ⏹INT(Number): 小于此数最接近的整数 ⏹TRUNC(Number): <截整函数>截去小数部分 ⏹ROUND(Number,[num_chars]): 指定小数位数的四舍五入 二、判断 3、IF函数 ⏹IF(logical_test,[value_if_true],[value_if_false]) Ø简单比对与嵌套比对 B2=IF(A2<60,“不及格”,“及格“) C2=IF(A2<60,“不及格”,IF(A2<70,“及格”,IF(A2<80,“良好”,“优秀”) 4、IFERROR函数 ⏹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.数字统计 ⏹COUNT(value1,[value2],...): 数字个数统计 ⏹COUNTA(value1,[value2],...): 单元格使用个数统计 ⏹COUNTBLANK(Range): 空白格个数统计 =COUNT(A2: B9)5 =COUNTA(A2: C9)18 =COUNTBLANK(A2: C9)6 2.单区域条件统计 ⏹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,"><") 3.多区域条件统计 ⏹COUNTIFS(criteria_range1,criteria1,*criteria_range2,criteria2+...) =COUNTIFS(A2: A21,“XS*”,C2: C21,“>80000”) 四、求和 1、公式 ⏹SUM(number1,[number2],...)): 指定参数的所有数字相加 ⏹SUMIF(range,criteria,[sum_range]): (条件区域,求和条件,实际求和区域)区域中符合指定条件的值求和 ⏹SUMIFS(sum_range,criteria_range1,criteria1,[criteria_range2,criteria2],...): 区域中满足多个条件的单元格求和 ⏹SUMPRODUCT(array1,[array2],[array3],...): 几组数组中,将数组间对应的元素相乘,并返回乘积之和 ⏹SUMSQ(number1,[number2],...): 参数的平方和 ⏹SUMX2MY2(array_x,array_y): 数值的平方差之和 ⏹SUMX2PY2(array_x,array_y): 数值的平方和之和 ⏹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 Ø与日期组合求和 ⏹特定日期产量查询 =SUMPRODUCT((A2: A63=DATE(H2,I2,J2))*(B2: B63)) Note: 统计日期为本日(Date(H2,I2,J2))的量产数据 ⏹特定月份的产量统计 =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】-精确查找 Ø跨表纵向查找 ⏹Sheet名+! (英文状态下)=引用该表下的数据如: Y1! ,sheet1! A1: A10 ⏹加“$”表示绝对引用如: $A$1: $B$6: 绝对引用(A1: B6)的区域 ✓绝对引用快捷键: F4(windows)Command+T(Mac) B1=VLOOKUP(Y1! A6,Y1! $A: $C,3,0) Note: 在Y2B1单元格中精确查找表Y1中WillisChoi的产品编号 2.3、HLOOKUP Ø跨表横向查找 B1=HLOOKUP(sheet2! $A1,sheet1! $A$1: $D$2,2,0) Note: 在sheet2B1单元格中精确查找表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: <=;-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: 从“省”之后开始截取,长度为“省~市” 2.4、LEN Ø计算字符串的长度 =LEN(A1) Note: 返回单元格A1的字符串长度为5 ØLEN与RIGHT的组合 =LEN(A1) Note: 2.5、PHONETIC Ø字符型单元格合并(数字不能合并) =PHONETIC(A2: A7) Note: 将A2到A7按顺序合并 2.6、REPLACE(要替换的字符串,开始位置,替换个数,新的文本) Ø用法 =REPLACE(A2,8,4,”****”) Note: 用“****”取代第8位开始的4个字符 =REPLACE(A1,3,2,)/(A1,3,2,””) Note: 将A1单元格第3位开始的2个字符替换为空
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 常用 Excel 公式 用法 实例