Excel处理数据用到的一些公式和VBA脚本

相关文章推荐

唠叨的鸵鸟 · GPU显存释放：GPU显存被占用，但不显示进 ...· 1 年前 ·

强悍的酸菜鱼 · Unity3D命令行参数_unity ...· 1 年前 ·

谈吐大方的木瓜 · openLayer修改图标(image/ic ...· 1 年前 ·

俊秀的瀑布 · 富文本编辑器 quill.js ...· 1 年前 ·

沉着的钢笔 · Java语言程序设计— ...· 1 年前 ·

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相反

在一列中，填入其他一列需求名中的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
End Function

当EXCEL添加了VBA代码时，保存EXCEL得另存为 EXCEL启用宏的工作簿(*.xlsm)格式。