首页 > 代码库 > Excel处理数据用到的一些公式和VBA脚本
Excel处理数据用到的一些公式和VBA脚本
最近工作中用到EXCEL统计处理一些数据,正好之前有自学了一段时间的EXCEL,这次正好用上。为了加深印象,以后方便翻阅,就记录下来。这篇会不断补充。
IF 多条件判断返回值
IF(logical_test, [value_if_true], [value_if_false])
示例: 小于500,且未到期的,返回“补款”
IF(AND(A2<500,B2="未到期"),"补款","")
职称为工程师或高工的,返回“满足”
IF(OR(C3="工程师", C3="高工"),"满足","")
说明:两个条件同时成立用AND,任一个成立用OR函数。
IFS 多场景判断返回值
IFS(logical_test1,value_if_true1, logical_test2, value_if_true2, ...)
示例:
在一列中,根据不同的判断条件,给出符合条件的值
IFERROR 把公式产生的错误值显示为自定义值
IFERROR(value, value_if_error) 第一个参数为需要判断的计算式,第二个参数为当第一个参数出错时要返回的值。
示例:成绩低于60,不及格
IFERROR(A2>=60,"不及格")
COUNTIF 单条件计数
COUNTIF(range, criteria)
示例:统计E列工资大于6000的人员数
COUNTIF(E:E, ">6000")
COUNTIF(E:E, ">"&M9) 假设M9单元格内容为6000
COUNTIFS 多条件计数
COUNTIFS(criteria_range1, criteria1, criteria_range2, criteria2, ...)
示例:统计属于销售部门,工资大于6000的员工人数
COUNTIFS(D:D, "销售", E:E, ">6000")
SUMIF 按条件在查找区域进行查找,并返回查找区域对应的数据区域中数值的和
SUMIF(range, criteria, [sum_range])
示例:当不指定SUM_RANGE时,将直接对RANGE区域求和
SUMIF(C:C, ">1000"), 对大于1000的单元格求和
SUMIF(B:B, "果汁", C:C), 对B列为果汁对应的C列销量求和
SUMIFS 多条件组合的数据求和
SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2, ...)
示例: 统计生产部门,男性工资的总和
SUMIFS(K:K, D:D, "生产", C:C, "男")
AVERAGEIF, AVERAGEIFS和SUMIF, SUMIFS类似
VLOOKUP, HLOOKUP 搜索用户查找范围中首列(或首行)中满足条件的数据,并根据指定的列号(行号)返回对应的值。VLOOKUP对列数据按行进行查找,HLOOKUP对行数据按列查找
VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])
[range_lookup]默认值为1或TRUE,使用模糊匹配方式进行查找。如果设置成0或FALSE,使用精确查找,同时支持无序查找。
示例:查找员工号为2267的员工姓名
VLOOKUP("2267", A1:D4, 2)
LEN 返回文本串的字符个数
LEN(text)
SUBSTITUTE 将目标文本中指定的字符串替换为新的字符串
SUBSTITUTE(text, old_text, new_text, instance_num)
示例:
将第二个狐替换成虎
SUBSTITUTE("狐假狐威", "狐", "虎", 2)
计算单元格中MANUAL字符的个数
(LEN(J13)-LEN(SUBSTITUTE(J13,"MANUAL",)))/LEN("MANUAL")
INDEX 根据给定的一个范围(区域引用或数组)中指定的行号和列号来返回一个值,如果源数据是区域,则返回单元格引用;如果源范围是数组,则返回数组中的某个值。
INDEX(reference, row_num, [column_num], [area_num])
INDEX(array, row_num, [column_num])
示例:从左侧的数据表隔行提取,生成新的工作表
E4:INDEX($C$3:$C$8, ROW(A2)*2-1)
F4:INDEX($C$3:$C$8,ROW(A2)*2)
SMALL 取指定的第N小的值
SMALL(array, K)
示例:
取第一小的值
SMALL({10,15,20},1.5)等同于SMALLSMALL({10,15,20},1),结果为10
LARGE 取指定的第N大的值,与SMALL相反
VBA
示例:
在一列中,填入其他一列需求名中的ID号,ID号会是重复的,但在另外一个OtherSheet中已经有B列对应所有的ID号。先模糊匹配上ID号,再返回ID号为单元格值。
E17=VaildReq(OtherSheet!B:B,B17)
VBA代码:
Function VaildReq(rng1 As Range, s As String) As String Dim Arr1 Dim r As Long r = rng1.End(xlDown).Row - rng1.Row + 1 Arr1 = rng1.Resize(r, 1) Dim i As Long For i = 1 To UBound(Arr1) If InStr(s, Arr1(i, 1)) Then If VaildReq = "" Then VaildReq = Arr1(i, 1) Else VaildReq = s End If Next End Function
当EXCEL添加了VBA代码时,保存EXCEL得另存为 EXCEL启用宏的工作簿(*.xlsm)格式。
Excel处理数据用到的一些公式和VBA脚本