常用Excel公式用法实例文档格式.docx
- 文档编号:17893948
- 上传时间:2022-12-11
- 格式:DOCX
- 页数:18
- 大小:2.76MB
常用Excel公式用法实例文档格式.docx
《常用Excel公式用法实例文档格式.docx》由会员分享,可在线阅读,更多相关《常用Excel公式用法实例文档格式.docx(18页珍藏版)》请在冰豆网上搜索。
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:
60”)
返回包含值12的单元格数量
=COUNTIF(DATA,12)
返回包含负值的单元格数量
=COUNTIF(DATA,"
0"
)
返回不等于0的单元格数量
>
返回大于5的单元格数量
5"
返回等于单元格A1中内容的单元格数量
=COUNTIF(DATA,A1)
返回大于单元格A1中内容的单元格数量
'
&
A1)
返回包含文本内容的单元格数量
=COUNTIF(DATA,'
*'
返回包含三个字符内容的单元格数量
=COUNTIF(DATA,'
?
返回包含单词"
GOOD"
(不分大小写)内容的单元格数量
GOOD'
返回在文本中任何位置包含单词"
字符内容的单元格数量
=COUNTIF(DATA,"
*GOOD*"
返回包含以单词"
AB"
(不分大小写)开头内容的单元格数量
AB*"
(不分大小写)结尾内容的单元格数量
*AB"
返回包含当前日期的单元格数量
=COUNTIF(DATA,TODAY())
返回大于平均值的单元格数量
"
AVERAGE(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
A4,“A*”,C2:
C4)24
A4,“*A”,C2:
C4)18
注:
引号(””)需使用英文半角符号
单条件求和3-1(无第三参数)
=SUMIF(D2:
D8,”>
=95”)
单条件求和3-2
=95”,F2:
F8)
2.3、SUMIFS(求和区域,条件1区域,条件1,条件2区域,条件2,条件3区域,条件3)
多条件求和
=SUMIFS(C2:
C8,B2:
B8,”男”)<
男生语文成绩之和>
=SUMIFS(F2:
F8,C2:
C8,”>
=90”,D2:
=90”)<
语文和数学成绩均>
=90分之和>
2.3、SUMPRODUCT
两数组相乘求和
=SUMPRODUCT((A1:
A10)*(B1:
B10))
Note:
A1*B1+A2*B2+……+A10*B10
符合数组1条件的数组2的和
A10=4)*(B1:
若数组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))
统计日期为本日(Date(H2,I2,J2))的量产数据
⏹特定月份的产量统计
=SUMPRODUCT((YEAR(A2:
A63)=H2)*(MONTH(A2:
A63)=I2)*(A2:
A63<
=DATE(H2,I2,J2))*(B2:
这就有一个较为复杂的逻辑界定。
其一,我们统计本月的数据,就要用条件MONTH(A2:
A63)=I2)。
其二,我们仅有上面条件不足以统计出正确数据,因为必须要考虑到历史查询情况,就是说,查询日为10日,但是10-31日是有数据的,因此还必须加上如些条件)(A2:
=DATE(H2,I2,J2)),就是当月数据还要小于查询日。
其三,有些时候,数据中有一年以上的数据,所以仅有上面两个条件还不行,如查询本月2月,就可能把去年2月的数据也统入其中了,还得加上条件(YEAR(A2:
A63)=H2),既“年”等于XX年。
五、查找与引用
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.1、LOOKUP
按条件查找
=LOOKUP(D2,B2:
B15,C2:
C15)
从B2:
B15列里面找D2,并返回C2:
C15相对应行的值(数值经常需按升序排列)
2.2、VLOOKUP
最简运用
=VLOOKUP(”C”,A2:
B6,2,1)(查找值,查找区域,返回所查找值所在列号,匹配条件)
匹配条件:
【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)
在Y2B1单元格中精确查找表Y1中WillisChoi的产品编号
2.3、HLOOKUP
跨表横向查找
B1=HLOOKUP(sheet2!
$A1,sheet1!
$D$2,2,0)
在sheet2B1单元格中精确查找表sheet1中张三对应的数值
2.4、INDEX
特定位置查找
E2=INDEX(D2:
F11,3,3)
返回区域(D2:
F11)中第三行第三列的值
E2=INDEX((B2:
C11,E2:
F11),2,2,2)
区域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)
查找5月份在查找区域(B2:
D2)的位置
2.6、INDEX与MATCH组合使用
G4=INDEX(A1:
D10,MATCH(F2,A1:
A10,0),MATCH(F4,A1:
D1,0))
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)
返回“i”在A1单元格中处于第几个字符
与LEFT函数组合
B3=LEFT(A2,FIND(”@”,A2,1)-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)
截取A列中“-”前的字符
2.2、RIGHT
截取后部分
=RIGHT(A2,3)
提取A2单元格后三位字符
2.3、MID
从任何位置截取字符串
=MID(A1,1,4)
截取A1单元格中从第1为开始的4个字符
MID&
=MID(A2,FIND(“省”,A2)+1,FIND(“市”,A2)-FIND(“省”,A2))
从“省”之后开始截取,长度为“省~市”
2.4、LEN
计算字符串的长度
=LEN(A1)
返回单元格A1的字符串长度为5
LEN与RIGHT的组合
2.5、PHONETIC
字符型单元格合并(数字不能合并)
=PHONETIC(A2:
A7)
将A2到A7按顺序合并
2.6、REPLACE(要替换的字符串,开始位置,替换个数,新的文本)
用法
=REPLACE(A2,8,4,”****”)
用“****”取代第8位开始的4个字符
=REPLACE(A1,3,2,)/(A1,3,2,””)
将A1单元格第3位开始的2个字符替换为空
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 常用 Excel 公式 用法 实例