Excel工作表函数概述

本页目录

本节简单介绍Excel工作表函数，并结合简单实例分多种情况介绍在Excel, VBA和Python中如何使用Excel工作表函数。[大谦Excel，dqexcel点com]

Excel工作表函数简介

Excel的工作表函数是Excel中很重要的内容，可以作为函数库使用，也可以看作是一门公式语言。

Excel工作表函数目前一共有300多个，其中不仅有数据类型、运算符、流程控制、函数等跟语言有关的函数，也有VBA没有的数据分析、财务、工程和统计等方面的比较专业的函数。

所以，熟练掌握Excel工作表函数，可以帮助我们更快更好地完成数据处理任务。

Excel使用工作表函数

图4-1所示工作表中A列给定了5个数据，现在要求计算它们的均值并显示在C1单元格。在C1单元格输入公式"=AVERAGE(A1:A5)"，回车，得到给定数据的均值4.8并显示在C1单元格中。结果如图4-1中所示。示例文件的存放路径为Samples\ch16\Excel函数\均值.xlsx。

Document Image

图4-1 计算给定数据的均值

下面计算图4-1所示工作表中A列数据与它们均值的离差。各数据的离差等于各数据减去它们的均值。在B1单元格中输入公式"=A1-AVERAGE($A$1:$A$5)"，其中$符号表示对应位置是固定的，用AVERAGE函数计算均值。回车后在B1单元格中显示第1个数据2与均值4.8之间的离差-2.8。单击B1单元格，双击它右下角的点向下复制填充公式并计算其他数据的离差。结果如图4-2中B列所示。示例文件的存放路径为Samples\ch16\Excel函数\离差.xlsx。

Document Image

图4-2 求给定数据与其均值的离差

仍然使用图4-1所示工作表中A列的数据，计算各数据的最大平方值，显示在C1单元格中。在C1单元格中输入公式"=MAX(A2:A5*A2:A5)"，采用数组运算进行计算。A2:A5*A2:A5分别计算A2至A5各单元格中值的平方值，MAX函数返回各平方值的最大值。在C1单元格中输入公式后，同时按下Ctrl, Shift和回车键，在C1单元格中显示8的平方值64，它是各平方值中最大的。结果如图4-3所示。示例文件的存放路径为Samples\ch16\Excel函数\最大平方值.xlsx。

Document Image

图4-3 求一组数据的最大平方值

Excel VBA使用工作表函数

4.1.2小节介绍了几个在Excel中使用工作表函数的实例，它们在操作上有一定的代表性。下面使用Excel VBA来完成相同的任务。使用Excel VBA来完成，可以直接调用工作表函数，也可以使用VBA自己的方法来实现。

在Excel VBA中使用Application对象的WorksheetFunction属性可以调用Excel的工作表函数。对于图4-1所示工作表中A列给定的5个数据，下面的代码计算它们的均值并将结果显示在C1单元格。示例文件的存放路径为Samples\ch16\Excel VBA\均值.xlsm。

code.vba 
Sub Test()
  Cells(1, 3) = Application.WorksheetFunction.Average(Range("A1:A5"))
End Sub

代码使用工作表函数Average计算给定数据的均值。注意函数的参数需要使用VBA指定的引用方式进行设置。

下面的VBA代码计算图4-1所示工作表中A列数据与它们均值的离差。首先求出所有数据的均值，然后计算每个数据与均值的差，显示在第2列。示例文件的存放路径为Samples\ch16\Excel VBA\离差.xlsm。

code.vba 
Sub Test()
  Dim intI As Integer
  Dim sngMean As Single
  '均值
  sngMean = Application.WorksheetFunction.Average(Range("A1:A5"))
  '离差=数据-均值
  For intI = 1 To 5
    Cells(intI, 2) = Cells(intI, 1) - sngMean
  Next
End Sub

也可以使用Application对象的Evaluate方法，直接调用4.1.2小节中求离差的公式进行计算，如下面代码中所示(Application可以省略)。如果说4.1.2小节中需要通过向下复制公式求其他数据的离差是半自动化操作，则这里利用VBA的For循环结构可以实现真正的自动化计算。示例文件的存放路径为Samples\ch16\Excel VBA\离差.xlsm。

code.vba 
Sub Test2()
  Dim intI As Integer
  For intI = 1 To 5
    '直接调用公式进行计算
    Cells(intI, 2) = Evaluate("=A" & intI & "-AVERAGE($A$1:$A$5)")
  Next
End Sub

4.1.2小节中使用公式"=MAX(A1:A5*A1:A5)"计算了给定数据的最大平方值，其中A1:A5*A1:A5是工作表函数中特定的计算格式，可以通过引用单元格直接实现数组运算。在Excel VBA中没有这样的使用方式，但可以用Evaluate方法直接调用公式。示例文件的存放路径为Samples\ch16\Excel VBA\最大平方值.xlsm。

code.vba 
Sub Test2()
  Cells(1, 3) = Evaluate("=MAX(A1:A5*A1:A5)")
End Sub

如果不使用Evaluate方法，在Excel VBA中需要用For循环计算每个数据的平方值并通过比较获取最大平方值。示例文件的存放路径为Samples\ch16\Excel VBA\最大平方值.xlsm。

code.vba 
Sub Test()
  Dim intI As Integer
  Dim sngV As Single
  Dim sngMax As Single
  sngMax = 0
  For intI = 1 To 5
    sngV = Range("A" & intI)   '给定的数据
    If sngMax < sngV * sngV Then  '获取最大平方值
      sngMax = sngV * sngV
    End If
  Next
  Cells(1, 3) = sngMax
End Sub

Python使用工作表函数

Python的xlwings包因为使用与Excel VBA相同的对象模型，所以也可以通过Excel应用对象的WorksheetFunction属性使用工作表函数，或者用Excel应用对象的Evaluate方法直接使用公式。

对于图4-1所示工作表中A列给定的5个数据，下面的代码计算它们的均值并将结果显示在C1单元格。在Python中，首先要导入xlwings包和os包，创建Excel应用，打开数据文件，获取工作表；然后使用工作表函数Average计算数据的均值。脚本文件的存放路径为Samples\ch16\Python\均值.py。

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"\均值.xlsx",read_only=False)
sht=bk.sheets.active  #获取工作表
#调用Average函数计算均值
sht.api.Cells(1,3).Value=\
    app.api.WorksheetFunction.Average(sht.api.Range('A1:A5'))

计算数据与其均值的离差时采用了两种方法。第1种方法用工作表函数计算均值后通过一个for循环计算各数据与均值的差；第2种方法使用Evaluate方法直接调用公式进行计算。下面代码中两种方法，使用其中一种时可将另外一种注释掉再运行。脚本文件的存放路径为Samples\ch16\Python\离差.py。

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"\离差.xlsx",read_only=False)
sht=bk.sheets.active  #获取工作表
#方法一
#计算均值
m=app.api.WorksheetFunction.Average(sht.api.Range('A1:A5'))
#计算离差
for i in range(5):
    sht.api.Cells(i+1,2).Value=sht.api.Cells(i+1,1).Value-m

#方法二
#直接调用公式进行计算
for i in range(5):
    sht.api.Cells(i+1,2).Value=\
        app.api.Evaluate("=A"+str(i+1)+"-AVERAGE($A$1:$A$5)")

下面的代码用两种方法计算给定数据的最大平方值，使用其中一种方法时同样将另外一种注释掉再运行。脚本文件的存放路径为Samples\ch16\Python\最大平方值.py。[大谦Excel，dqexcel点com]

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"\最大平方值.xlsx",read_only=False)
sht=bk.sheets.active  #获取工作表

#方法一
#直接调用公式进行计算
sht.api.Cells(1,3).Value=app.api.Evaluate("=MAX(A1:A5*A1:A5)")
#方法二
max=0.0
#计算最大平方值
for i in range(5):
    v=sht.api.Cells(i+1,1).Value
    if max<v*v:max=v*v
sht.api.Cells(1,3).Value=max