Excel使⽤技巧整理
1.if函数
=if(条件，是则结果，否则结果)
2. SUBTOTAL函数
函数名称：SUBTOTAL
主要功能：返回列表或数据库中的分类汇总。
使⽤格式：SUBTOTAL(function_num, ref1, ref2, ...)
参数说明：Function_num为1到11（包含隐藏值）或101到111（忽略隐藏值）之间的数字，⽤来指定使⽤什么函数在列表中进⾏分类汇总计算（如图6）；ref1, ref2,……
代表要进⾏分类汇总区域或引⽤，不超过29个。
3.查函数
=IF(ISERROR(FIND("东芝",A2,1)),"",A2)
如果在A2单元格不到“东芝”两字（出错），就置空，如果到，就返回结果A2
=IF(ISERROR(FIND("集束",S2,1)),"0","集束天线")
=IF(ISERROR(FIND("射灯",S2,1)),"0","射灯天线")
=IF(ISERROR(FIND("普通",S2,1)),"0","普通天线")
=IF(ISERROR(FIND("双频",S2,1)),"0","普通天线")
=IF(ISERROR(FIND("双频",S2,1)),"0","普通天线")
=IF(ISERROR(FIND("排⽓管",S2,1)),"0","美化天线")
=IF(ISERROR(FIND("有机房",T2,1)),"0","有机房")
=IF(ISERROR(FIND("1台空调",T2,1)),"0","1台空调")
=IF(ISERROR(FIND("⾼频开关电源",T2,1)),"0","⾼频开关电源")
4.函数整理
sum（数字1，数字2，数字3...）
count（数字1，数字2，数字3...）
max（数字1，数字2，数字3...）
min（数字1，数字2，数字3...）
averge（数字1，数字2，数字3...）
right（字符串，右取指数）从右往左取数
left（字符串，左取指数）从左往右取数
460⽀=460 =left(D25,LEN(D25)-1)
5.vlookup(查值，查范围，返回值所在的列数，False)
=IF(ISERROR(Vlookup(A3,’1⽉’!$A$1:$B$6,2,0)),0,Vlookup(A3,’1⽉’!$A$1:$B6,2,0))
查A3在“1⽉”表中的A1:B:6区域的值，如果不到为0，到则返回Vlookup(A3,’1⽉’!$A$1:$B6,2,0))
VLOOKUP函数的反向查
⼀般情况下，VLOOKUP函数只能从左向右查。但如果需要从右向右查，则需要把区域进⾏“乾坤⼤挪移”，把列的位置⽤数组互换⼀下。
公式：=VLOOKUP(A9,IF({1,0},B2:B5,A2:A5),2,0)
公式剖析：
1、这⾥其实不是VLOOKUP可以实现从右⾄右的查，⽽是利⽤IF函数的数组效应把两列换位重新组合后，再按正常的从左⾄右查。
2、IF({1,0},B2:B5,A2:A5)这是本公式中最重要的组成部分。在EXCEL函数中使⽤数组时（前提时该函数
的参数⽀持数组），返回的结果也会是⼀个数组。这⾥1和0不是实际意义上的数字，⽽是1相当于TRUE，0相当于FALSE。
当为1时，它会返回IF的第⼆个参数（B列），为0时返回第⼆个参数（A列）。根据数组运算返回数组，所以使⽤IF后的结果返回⼀个数组（⾮单元格区域）：{"张⼀","A001";"赵三","A002";"杨五","A003";"孙⼆","A004"}
1、如何避免出现错误值。
EXCEL2003 在VLOOKUP查不到，就#N/A的错误值，我们可以利⽤错误处理函数把错误值转换成0或空值。
即：=IF(ISERROR(VLOOKUP（参数略）),"",VLOOKUP（参数略））
EXCEL2007，EXCEL2010中提供了⼀个新函数IFERROR，处理起来⽐EXCEL2003简单多了。
=IFERROR（VLOOKUP（），"")
Index+match
=INDEX(数据源!A:A,MATCH(查询2!A2，数据源!B:B,0))
要“查询2！A2”的值，先在数据源!B:B出第⼏列，然后在数据源!A:A出第⼏⾏。
逆向查数据，跟vlookup相反。
通配符的使⽤：
*代表0到多个字符
代表1个字符
错误值7+2：
#DIV/0! 除以0出现的错误
#NAME? 没有定义，函数名写错，误操作
#VALUE ⽤错参数类型，如⽂本+数字
#NULL! 单元格没有交集
#N/A! 函数不到
#NUM! ⽆效的数字
excel 字符串转数组#REF! 引⽤错误
>### 格式错误，单元格列宽较⼩
⾝份证格式先设置⽂本格式，后输⼊数据
⽇期的录⼊：
当前⽇期Ctrl+；
年内⽇期9/6
=IF(OR(B2>=25000,C2>=15000),"优秀员⼯","")
如果B2单元格中的数值⼤于或等于25000，或者C2单元格中的数值⼤于或等于15000，则判断并显⽰为优秀员⼯，如果两个条件中没有⼀个符合，则不作显⽰（""表⽰空⽩）。
=INDEX($H$1:$Q$1,MATCH(1,COUNTIF(F3,"*"&$H$2:$Q$2&"*"),))
countif部分到F3在H2:Q2中哪⾥存在
Match部分出存在的那个单元格是在H2:Q2中的哪⼀列
Index部分提取出结果
6.rank(排名值，排名范围，排名顺序)
排名顺序：0代表降序，1代表升序
www.doczj/doc/c01005b355270722192ef7bf.html nge/small(数值，位次) 最⼤最⼩值，排次
9.phonetic() 合并单元格内容
10.提取号码，如“学习学习0000-10101学习学习”提取出“0000-10101”
=MID(B2,FIND(“-“,B2,)-4,10)
11.根据⾝份证号码求男⼥性别：
=IF(LEN(B3)=18,IF(MOD(MID(B3,17,1),2),"男","⼥"),IF(MOD(RIGHT(B3,1),2),"男","⼥"))
10. ISERROR函数
函数名称：ISERROR
主要功能：⽤于测试函数式返回的数值是否有错。如果有错，该函数返回TRUE，反之返回FALSE。
使⽤格式：ISERROR(value)
参数说明：Value表⽰需要测试的值或表达式。
应⽤举例：输⼊公式：=ISERROR(A35/B35)，确认以后，如果B35单元格为空或“0”，
则A35/B35出现错误，此时前述函数返回TRUE结果，反之返回FALSE。
特别提醒：此函数通常与IF函数配套使⽤，如果将上述公式修改为：=IF(ISERROR
(A35/B35),"",A35/B35)，如果B35为空或“0”，则相应的单元格显⽰为空，反之显⽰
A35/B35的结果。
11. LEN函数
函数名称：LEN
主要功能：统计⽂本字符串中字符数⽬。
使⽤格式：LEN(text)
参数说明：text表⽰要统计的⽂本字符串。
应⽤举例：假定A41单元格中保存了“我今年28岁”的字符串，我们在C40单元格中输
⼊公式：=LEN(A40)，确认后即显⽰出统计结果“6”。
特别提醒：LEN要统计时，⽆论中全⾓字符，还是半⾓字符，每个字符均计为“1”；
与之相对应的⼀个函数——LENB，在统计时半⾓字符计为“1”，全⾓字符计为“2”。
如果单元格中存在中⽂和英⽂，那么LENB-LEN就是等于汉字个数。
12. MID函数
函数名称：MID
主要功能：从⼀个⽂本字符串的指定位置开始，截取指定数⽬的字符。
使⽤格式：MID(text,start_num,num_chars)
参数说明：text代表⼀个⽂本字符串；start_num表⽰指定的起始位置；num_chars表⽰要截取的数⽬。
应⽤举例：假定A47单元格中保存了“我喜欢天极⽹”的字符串，我们在C47单元格中输⼊公式：=MID(A47,4,3)，确认后即显⽰
出“天极⽹”的字符。
特别提醒：公式中各参数间，要⽤英⽂状态下的逗号“,”隔开。
13. VALUE函数
函数名称：VALUE
主要功能：将⼀个代表数值的⽂本型字符串转换为数值型。
使⽤格式：VALUE(text)
参数说明：text代表需要转换⽂本型字符串数值。
应⽤举例：如果B74单元格中是通过LEFT等函数截取的⽂本型字符串，我们在C74单元格中输⼊公式：=VALUE(B74)，确认后，即可将其转换为数值型。
特别提醒：如果⽂本型数值不经过上述转换，在⽤函数处理这些数值时，常常返回错误。14. TEXT函数
函数名称：TEXT
主要功能：根据指定的数值格式将相应的数字转换为⽂本形式。
使⽤格式：TEXT(value,format_text)
参数说明：value代表需要转换的数值或引⽤的单元格；format_text为指定⽂字形式
的数字格式。
应⽤举例：如果B68单元格中保存有数值1280.45，我们在C68单元格中输⼊公式：
=TEXT(B68, "$0.00")，确认后显⽰为“$1280.45”。
特别提醒：format_text参数可以根据“单元格格式”对话框“数字”标签中的类型进⾏确定。MATCH函数
函数名称：MATCH
主要功能：返回在指定⽅式下与指定数值匹配的数组中元素的相应位置。值所在的⾏
使⽤格式：MATCH(lookup_value,lookup_array,match_type)
参数说明：Lookup_value代表需要在数据表中查的数值；
Lookup_array表⽰可能包含所要查的数值的连续单元格区域；
Match_type表⽰查⽅式的值（-1、0或1）。
如果match_type为-1，查⼤于或等于lookup_value的最⼩数值，Lookup_array 必须按降序排列；
如果match_type为1，查⼩于或等于lookup_value 的最⼤数值，Lookup_array 必须按升序排列；
如果match_type为0，查等于lookup_value 的第⼀个数值，Lookup_array 可以按任何顺序排列；如果省略match_type，则默认为1。
应⽤举例：如图4所⽰，在F2单元格中输⼊公式：=MATCH(E2,B1:B11,0)，确认后则返回查的结果“9”。
15. INDEX函数
函数名称：INDEX
主要功能：返回列表或数组中的元素值，此元素由⾏序号和列序号的索引值进⾏确定。
值所在的列
使⽤格式：INDEX(array,row_num,column_num)
参数说明：Array代表单元格区域或数组常量；Row_num表⽰指定的⾏序号（如果省略
row_num，则必须有column_num）；Column_num表⽰指定的列序号（如果省略column_num，则必须有row_num）。
应⽤举例：如图3所⽰，在F8单元格中输⼊公式：=INDEX(A1:D11,4,3)，确认后则显⽰
出A1⾄D11单元格区域中，第4⾏和第3列交叉处的单元格（即C4）中的内容。
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
EXCEL数组公式入门教程
下一篇 »
发表评论