常用Excel工作表函数

本页目录

为了加深对4.1节内容的理解，本节结合最常用的几个Excel工作表函数，结合实例展开进行介绍。[大谦Excel，dqexcel点com]

SUM函数

SUM函数主要用于对单元格中的值求和。它将被指定为参数的所有数字相加。每个参数可以是单元格、单元格区域、数组、常量、公式或另一个函数的结果。

SUM函数的语法格式为：

code.python 
SUM(number1,[number2],...)

其中，number1是必需参数，为相加的第一个数值参数。number2是可选参数，为相加的2～255个数值参数。

图4-4所示工作表中为各种食材的采购数据，试根据单价和重量数据计算采购食材的总费用。

Document Image

图4-4 求采购食材的总费用

【Excel】

在B8单元格中输入公式"=SUM(B2:B6*C2:C6)"，同时按下Ctrl, Shift和Enter键，在B8单元格中显示采购食材的总费用291。结果如图4-4所示。示例文件的存放路径为Samples\ch16\Excel函数\函数SUM.xlsx。

公式中首先将B2:B6单元格区域和C2:C6单元格区域中的对应数据相乘，得到各食材的采购费用，然后用SUM函数将所有费用相加，得到总费用。

【Excel VBA】

在Excel VBA中，可以直接调用Excel函数进行计算，也可以采用VBA的办法，通过循环进行计算。示例文件的存放路径为Samples\ch16\Excel VBA\函数SUM.xlsm。

过程Test用Evaluate函数直接调用公式进行计算，计算结果输出到B8单元格中。

code.vba 
Sub Test()
  Range("B8") = Evaluate("=SUM(B2:B6*C2:C6)")
End Sub

运行过程，在工作表的B8单元格中输出各食材的采购总费用291。

过程Test2也是用Evaluate函数调用公式进行计算，不同的是公式中使用的是SUMPRODUCT函数进行求和运算。

code.vba 
Sub Test2()
  Range("B8") = Evaluate("=SUMPRODUCT(B2:B6,C2:C6)")
End Sub

运行过程，在工作表的B8单元格中输出各食材的采购总费用291。

过程Test3用VBA的方法进行计算。使用For循环，求每种食材的采购费用并累加求和。

code.vba 
Sub Test3()
  Dim arr
  Dim sngSum As Single
  Dim intI As Integer

  arr = Range("B2:C6")  '将单价和重量数据保存到arr数组
  sngSum = 0#
  For intI = 1 To UBound(arr, 1)  '各食材费用累加求和
    sngSum = sngSum + arr(intI, 1) * arr(intI, 2)
  Next
  Range("B8") = sngSum  '输出总费用
End Sub

运行过程，在工作表的B8单元格中输出各食材的采购总费用291。

【Python】

用Python解决此问题，同样有直接调用Excel函数和使用for循环进行计算两种方法。示例文件的存放路径为Samples\ch16\Python\函数SUM.py。

下面的代码行首先导入xlwings和os两个包，然后获取本Python文件的当前路径。创建Excel应用，打开数据文件，获取工作表。

code.python 
import xlwings as xw  #导入xlwings
import os  #导入os
root = os.getcwd()  #获取当前路径
#创建Excel应用窗口，可见，不添加工作簿
app=xw.App(visible=True, add_book=False)
#打开数据文件，可写
bk=app.books.open(fullname=root+r"\函数SUM.xlsx",read_only=False)
sht=bk.sheets.active  #获取工作表

第一种方法使用xlwings的API使用方式调用Evaluate函数，使用公式计算总费用并将结果输出到工作表的B8单元格中。

code.python 
#方法一
#直接调用公式进行计算
sht.range("B8").value=app.api.Evaluate("=SUM(B2:B6*C2:C6)")

第二种方法用Python通过for循环对各食材采购费用进行累加求和得到总费用，然后将结果输出到工作表的B8单元格。

code.python 
#方法二
d=sht.range("B2:C6").value
sm=0.0
for i in range(5):
    sm+=d[i][0]*d[i][1]
sht.range("B8").value=sm

运行脚本，在工作表的B8单元格中输出采购总费用291。

IF函数

IF函数用于判断结构，在条件为真时返回一个值，条件为假时返回另一个值。 IF函数的语法格式为：

code.python 
IF(logical_test,value_if_true,value_if_false)

其中，logical_test表示逻辑判决表达式；value_if_true表示当判断条件为逻辑“真”（TRUE）时，显示该处给定的内容，如果忽略，返回TRUE；value_if_false表示当判断条件为逻辑“假”（FALSE）时，显示该处给定的内容，如果忽略，返回FALSE。

图4-5所示工作表中第1列为一组给定的成绩数据，试根据各成绩分数判断其及格或不及格，并将判断结果显示在第2列。

Document Image

图4-5 判断成绩及格或不及格

【Excel】

在B2单元格中输入公式"=IF(A2>=60,"及格","不及格")"，回车后在B2单元格中显示A2单元格中数据89对应的判断结果"及格"。单击B2单元格，双击它右下角的点向下复制填充公式并计算其他数据对应的判断结果。结果如图4-5中B列所示。示例文件的存放路径为Samples\ch16\Excel函数\函数IF-1.xlsx。

【Excel VBA】

在Excel中通过向下复制填充公式处理多行数据，需要手工操作，所以整个处理过程只能说是半自动化的。在Excel VBA和Python中，可以通过for循环自动处理每行数据。可以直接调用Excel函数进行计算，也可以采用VBA的办法进行计算。示例文件的存放路径为Samples\ch16\Excel VBA\函数IF-1.xlsm。

过程Test通过一个For循环，对第1列的每个成绩数据用Evaluate函数判断是否及格，然后将判断结果显示在其右边的单元格中。

code.vba 
Sub Test()
  Dim intI As Integer
  For intI = 2 To 6  '对每个成绩数据进行判断
    Cells(intI, 2) = _
        Evaluate("=IF(A" & intI & ">=60,""及格"",""不及格"")")
  Next
End Sub

运行过程，在工作表第2列输出各成绩数据的判断结果。

过程Test2使用IIf函数对各给定成绩进行判断。

code.vba 
Sub Test2()
  Dim intI As Integer
  Dim arr
  Dim strR As String
  arr = Range("A2:A6")  '将成绩数据保存到arr数组
  For intI = 1 To UBound(arr, 1)  '对每个数据进行判断
    strR = IIf(arr(intI, 1) < 60, "不及格", "及格")   'IIf函数
    Cells(intI + 1, 2) = strR  '输出判断结果
  Next
End Sub

运行过程，在工作表第2列输出各成绩数据的判断结果。

过程Test3在For循环中使用二分支的If判断结构进行判断。

code.vba 
Sub Test3()
  Dim intI As Integer
  Dim arr
  Dim strR As String
  arr = Range("A2:A6")  '将成绩数据保存到arr数组
  For intI = 1 To UBound(arr, 1)  '对每个数据进行判断
    If arr(intI, 1) < 60 Then  '二分支If判断结构
      Cells(intI + 1, 2) = "不及格"
    Else
      Cells(intI + 1, 2) = "及格"
    End If
  Next
End Sub

运行过程，在工作表第2列输出各成绩数据的判断结果。

【Python】

Python中可以直接调用Excel函数进行计算，也可以采用Python的办法进行计算。编写Python脚本，脚本文件的存放路径为Samples\ch16\Python\函数IF-1.py。

code.vba 
#前面代码省略，请参见Python文件
#......

第1种方法是用Evaluate函数直接调用公式进行计算。注意这里笔者测试发现不能在IF函数的参数处直接指定判断结果"及格"或"不及格"，而是返回数字1或0，然后根据该数字输出"及格"或"不及格"。

code.python 
#方法一
#直接调用公式进行计算
for i in range(5):
    rs=app.api.Evaluate("=IF(A"+str(i+2)+">=60,1,0)")
    if rs==1:
        sht.range("B"+str(i+2)).value="及格"
    else:
        sht.range("B"+str(i+2)).value="不及格"

第2种方法使用Python的三元操作表达式进行处理。

code.python 
#方法二
#使用三元操作表达式
for i in range(5):
    sht.range("B"+str(i+2)).value=\
        "及格" if sht.range("A"+str(i+2)).value>=60 else "不及格"

第3种方法使用Python的二分支判断结构进行处理。

code.python 
#方法三
#使用二分支判断结构
for i in range(5):
    if sht.range("A"+str(i+2)).value>=60:
        sht.range("B"+str(i+2)).value="及格"
    else:
        sht.range("B"+str(i+2)).value="不及格"

采用上面任何一种方法（同时注释掉另外两种方法），运行脚本，在工作表第2列输出各成绩对应的判断结果，如图4-5所示。

下面对给定的成绩数据作出更细致的等级判断，即将成绩分为不及格、中等、良好和优秀等多个等级。如图4-6所示。

Document Image

图4-6 判断成绩的等级

【Excel】

在B2单元格中输入公式"= IF(A2<60,"不及格",IF(A2<80,"中等",IF(A2<90,"良好","优秀")))"，回车后在B2单元格中显示左侧成绩89对应的等级"良好"。单击B2单元格，双击它右下角的点向下复制填充公式并判断其他成绩的等级。结果如图4-6中B列所示。示例文件的存放路径为Samples\ch16\Excel函数\函数IF-2.xlsx。

【Excel VBA】

示例文件的存放路径为Samples\ch16\Excel VBA\函数IF-2.xlsm。

过程Test通过一个For循环，对第1列的每个成绩数据用Evaluate函数使用公式判断成绩等级，然后将判断结果显示在其右边的单元格中。

code.vba 
Sub Test()
  Dim intI As Integer
  For intI = 2 To 6
    Cells(intI, 2) = _
      Evaluate("=IF(A" & intI & "<60,""不及格"",IF(A" & _
        intI & "<80,""中等"",IF(A" & intI & "<90,""良好"",""优秀"")))")
  Next
End Sub

运行过程，在工作表第2列输出各成绩数据的判断结果。

过程Test2使用多分支If判断结构对各给定成绩进行判断。

code.vba 
Sub Test2()
  Dim intI As Integer
  Dim arr
  arr = Range("A2:A6")
  For intI = 1 To UBound(arr, 1)
    If arr(intI, 1) < 60 Then
      Cells(intI + 1, 2) = "不及格"
    ElseIf arr(intI, 1) < 80 Then
      Cells(intI + 1, 2) = "中等"
    ElseIf arr(intI, 1) < 90 Then
      Cells(intI + 1, 2) = "良好"
    Else
      Cells(intI + 1, 2) = "优秀"
    End If
  Next
End Sub

运行过程，在工作表第2列输出各成绩数据的判断结果。

【Python】

编写Python脚本，脚本文件的存放路径为Samples\ch16\Python\函数IF-1.py。

code.vba 
#前面代码省略，请参见Python文件
#......

由于不能在IF函数的参数处直接指定判断结果为"不及格"或"中等"等字符串，在Python中调用公式进行处理不太方便。下面使用Python的多分支判断结构进行处理。

code.python 
#使用多分支判断结构
d=sht.range("A2:A6").value
for i in range(5):
    if sht.range("A"+str(i+2)).value<60:
        sht.range("B"+str(i+2)).value="不及格"
    elif sht.range("A"+str(i+2)).value<80:
        sht.range("B"+str(i+2)).value="中等"
    elif sht.range("A"+str(i+2)).value<90:
        sht.range("B"+str(i+2)).value="良好"
    else:
        sht.range("B"+str(i+2)).value="优秀"

运行脚本，在工作表第2列输出各成绩数据的判断结果。

LOOKUP函数

LOOKUP函数具有向量形式和数组形式两种语法形式。

向量是只含一行或一列的区域。LOOKUP的向量形式在单行区域或单列区域（称为“向量”）中查找值，然后返回第二个单行区域或单列区域中相同位置的值。语法格式为：

LOOKUP(lookup_value, lookup_vector, [result_vector])

其中，lookup_value表示LOOKUP在第一个向量中搜索的值，可以是数字、文本、逻辑值、名称或对值的引用；lookup_vector表示只包含一行或一列的区域，值可以是文本、数字或逻辑值；result_vector为可选参数，只包含一行或一列的区域，它必须与lookup_vector的大小相同。

LOOKUP的数组形式在数组的第一行或第一列中查找指定的值，并返回数组最后一行或最后一列内同一位置的值。语法格式为：

LOOKUP(lookup_value, array)

其中，lookup_value表示LOOKUP在数组中搜索的值，可以是数字、文本、逻辑值、名称或对值的引用；array表示包含要与lookup_value进行比较的文本、数字或逻辑值的单元格区域。

图4-7中工作表前5行给出了不同人员的编号、姓名、额度和名次数据，现要求根据该数据，对工作表中A8:A10单元格区域中指定的编号查询出各编号对应的姓名、额度和名次数据并显示在右侧各单元格中。

Document Image

图4-7 根据编号查询数据

【Excel】

在B8单元格中输入公式"= LOOKUP($A8,$A$2:B$5)"，其中$符号表示对应位置是固定的。公式在A2:B5范围内返回A8单元格的值对应的姓名。回车后在B8单元格中显示编号3对应的姓名王二。单击B8单元格，向右拖拉其右下角的点向右复制填充公式并获取编号3对应的额度和名次数据；再向下拖拉该点，向下复制填充公式获取其他指定编号对应的数据。结果如图4-7中阴影区域所示。示例文件的存放路径为Samples\ch16\Excel函数\函数LOOKUP.xlsx。

【Excel VBA】

示例文件的存放路径为Samples\ch16\Excel VBA\函数LOOKUP.xlsm。

过程Test通过一个两层嵌套的For循环，通过Evaluate函数调用LOOKUP函数查找A8:A10中各编号对应的数据并输出到指定的单元格。

code.vba 
Sub Test()
  Dim intI As Integer
  Dim intJ As Integer
  Dim strCol As String

  For intI = 8 To 10
    For intJ = 2 To 4
      If intJ = 2 Then strCol = "B"
      If intJ = 3 Then strCol = "C"
      If intJ = 4 Then strCol = "D"
      Cells(intI, intJ) = _
            Evaluate("=LOOKUP($A" & intI & ",$A$2:" & strCol & "$5)")
    Next
  Next
End Sub

运行过程，生成图4-7所示的查询结果。

过程Test2使用字典进行处理。构造字典中的键值对时将每个人员的编号作为键，将编号对应的姓名、额度和名次一起作为值。然后在指定位置输出指定编号（即键）对应的数据（即值）。

code.vba 
Sub Test2()
  Dim intI As Integer
  Dim arr
  Dim dicT As New Dictionary

  On Error Resume Next

  arr = Range("A2:D5")  '获取数据
  For intI = 1 To UBound(arr)  '构造字典
    '编号作为键，编号对应的数据作为值
    dicT(arr(intI, 1)) = Array(arr(intI, 2), arr(intI, 3), arr(intI, 4))
  Next
  For intI = 8 To Cells(7, "A").End(xlDown).Row   '输出指定编号对应的数据
    '输出结果
    Cells(intI, "B").Resize(1, 3) = dicT(Cells(intI, "A").Value2)
  Next
End Sub

运行过程，生成图4-7所示的查询结果。

【Python】

编写Python脚本，脚本文件的存放路径为Samples\ch16\Python\函数LOOKUP.py。

code.python 
#前面代码省略，请参见Python文件
#......
方法一直接调用公式进行数据查询。
#方法一
#直接调用公式进行查询
for i in range(8,11):
    for j in range(2,5):
        if j==2:col="B"
        if j==3:col="C"
        if j==4:col="D"
        sht.cells(i,j).value=\
            app.api.Evaluate("=LOOKUP($A"+str(i)+",$A$2:"+col+"$5)")

方法二使用字典实现数据查询。字典的构造和使用跟VBA的相同。

code.python 
#方法二
d=sht.range("A2:D5").value
dicT={}
for i in range(len(d)):  #遍历每行数据
    dicT[d[i][0]]=[d[i][1],d[i][2],d[i][3]]  #将编号作键，对应数据作值
for i in range(8,11):  #根据给定编号查询值
    sht.cells(i,"B").value=dicT[sht.cells(i,"A").value]

使用任意一种方法，运行过程，生成图4-7所示的查询结果。

VLOOKUP函数

VLOOKUP函数在表格或数值数组的首行查找指定的数值，并返回表格或数组当前行中指定列处的值。语法格式为：

VLOOKUP(lookup_value, table_array, col_index_num,[range_lookup])

其中，lookup_value表示要在表格或区域的第一列中搜索的值，可以是值或引用；table_array表示包含数据的单元格区域，可以使用对区域或区域名称的引用；col_index_num表示table_array参数中必须返回的匹配值的列号；range_lookup为可选参数，是一个逻辑值，指定希望VLOOKUP精确匹配还是近似匹配。

图4-8所示工作表中A-D列为采购到的各种食材的名称、重量、产地和单价，现在要求利用食材的重量和单价计算它们的费用。

Document Image

图4-8 计算各食材的采购费用

【Excel】

在E2单元格中输入公式"=VLOOKUP(A2,A$1:D$6,4,FALSE)*B2"，其中$符号表示对应位置是固定的。用VLOOKUP函数查找A1:D6单元格区域中第4列与A2单元格中食材名称对应的单价，然后用它乘以B2单元格中的重量值，结果显示在E2单元格中。

回车后在E2单元格中显示第1个食材的费用180。单击E2单元格，双击它右下角的点向下复制填充公式并计算其他食材的费用。结果如图4-8中E列所示。示例文件的存放路径为Samples\ch16\Excel函数\函数VLOOKUP.xlsx。

【Excel VBA】

示例文件的存放路径为Samples\ch16\Excel VBA\函数VLOOKUP.xlsm。

过程Test使用For循环，对于每种食材，通过Evaluate函数调用VLOOKUP函数查找食材对应的单价，乘以重量得到费用并输出结果到指定的单元格。

code.vba 
Sub Test()
  '直接调用公式
  Dim intI As Integer
  For intI = 2 To 6
    Cells(intI, 5) = Evaluate("=VLOOKUP(A" & intI _
        & ",A$1:D$6,4,FALSE)*B" & intI)
  Next
End Sub

运行过程，生成图4-8所示的计算结果。

过程Test2将工作表中A-D列数据作为给定参照数据，用A列的食材名称与参照数据中的第1列数据进行比较，得到食材对应的重量和单价，将它们相乘得到费用，然后输出到指定单元格。

code.vba 
Sub Test2()
  Dim intI As Integer
  Dim intJ As Integer
  Dim arr
  arr = Range("A2:D6")   '获取数据
  For intI = 2 To 6
    For intJ = 1 To UBound(arr)
      '如果目标食材在数据中存在，
      '则将对应行的价格和重量相乘，得到费用
      If Range("A" & intI) = arr(intJ, 1) Then
        Cells(intI, 5) = arr(intJ, 2) * arr(intJ, 4)
      End If
    Next
  Next
End Sub

运行过程，生成图4-8所示的计算结果。

【Python】

编写Python脚本，脚本文件的存放路径为Samples\ch16\Python\函数VLOOKUP.py。

code.python 
#前面代码省略，请参见Python文件
#......
方法一直接调用公式进行计算。
#方法一
#直接调用公式进行计算
for i in range(2,7):
    sht.cells(i,5).value=\
        app.api.Evaluate(“=VLOOKUP(A”+str(i)+”,A$1:D$6,4,FALSE)*B”+str(i))

方法二将工作表中A-D列数据作为给定参照数据，用A列的食材名称与参照数据中的第1列数据进行比较，得到食材对应的重量和单价，将它们相乘得到费用，然后输出到指定单元格。

code.python 
#方法二
d=sht.range("A2:D6").value
for i in range(2,7):  #遍历目标食材
    for j in range(len(d)):  #遍历每行数据
        #如果目标食材在数据中存在，
        #则将对应行的价格和重量相乘，得到费用
        if sht.cells(i,1).value==d[j][0]:
            sht.cells(i,5).value=d[j][1]*d[j][3]

使用上面任意一种方法，运行脚本，生成图4-8所示的计算结果。

CHOOSE函数

CHOOSE函数用于从给定的参数中返回指定的值。语法格式为：

CHOOSE(index_num, value1, [value2], ...)

其中，index_num表示指定所选定的值参数，必须为1～254之间的数字，或者为公式或对包含1～254之间某个数字的单元格的引用；value1, value2, ...，value1是必需的，后续值是可选的，这些值参数的个数介于1～254之间，函数CHOOSE基于index_num从这些值参数中选择一个数值或一项要执行的操作。

下面用CHOOSE函数实现学习成绩的多等级判断。如图4-9所示。

Document Image

图4-9 判断给定成绩的等级

【Excel】

在B2单元格中输入公式"=CHOOSE(IF(A2<60,1,IF(A2<80,2,IF(A2<90,3,4))),"不及格","中等","良好","优秀")"，回车后在B2单元格中显示A2单元格中成绩分数对应的等级"良好"。单击B2单元格，双击它右下角的点向下复制填充公式并计算成绩等级。结果如图4-9中B列所示。

公式中通过IF函数得到满足不同条件时的数字1-4，并用CHOOSE函数指定各数字对应的表示成绩等级的字符串，最后根据得到的数字返回字符串，输出到B列。示例文件的存放路径为Samples\ch16\Excel函数\函数CHOOSE.xlsx。

【Excel VBA】

示例文件的存放路径为Samples\ch16\Excel VBA\函数CHOOSE.xlsm。

过程Test使用For循环，对于每个成绩分数，通过Evaluate函数调用CHOOSE函数返回对应的等级并输出结果到指定的单元格。

code.vba 
Sub Test()
  Dim intI As Integer
  For intI = 2 To 6
    Cells(intI, 2) = _
      Evaluate("=CHOOSE(IF(A" & intI & _
          "<60,1,IF(A" & intI & "<80,2,IF(A" & intI _
          & "<90,3,4))),""不及格"",""中等"",""良好"",""优秀"")")
  Next
End Sub

运行过程，结果如图4-9中工作表B列所示。

【Python】

编写Python脚本，脚本文件的存放路径为Samples\ch16\Python\函数CHOOSE.py。[大谦Excel，dqexcel点com]

code.vba 
#前面代码省略，请参见Python文件
#......

Python中使用多分支判断结构来实现。

code.python 
#使用分支判断结构
d=sht.range("A2:A6").value
for i in range(5):
    if sht.range("A"+str(i+2)).value<60:
        sht.range("B"+str(i+2)).value="不及格"
    elif sht.range("A"+str(i+2)).value<80:
        sht.range("B"+str(i+2)).value="中等"
    elif sht.range("A"+str(i+2)).value<90:
        sht.range("B"+str(i+2)).value="良好"
    else:
        sht.range("B"+str(i+2)).value="优秀"

运行过程，结果如图4-9中工作表B列所示。