Excel加权平均计算宏创建及使用

Excel加权平均计算函数的创建及使用
李光明
山东省地质科学实验研究院
加权平均品位计算是矿产勘查中的一项重要工作，使用Excel电子表格计算时，由于没有加权平均计算函数，实际操作起来比较麻烦。

为此，利用宏方法自定义了加权平均计算函数WeightedAverage()。

下面介绍其创建和使用方法。

1、创建加权平均计算函数WeightedAverage()
打开Excel工作簿，选择“工具/宏/visual Basic编辑器”(图1)，通过“插入/模块”命令添加一个模块(图2)。

图1 选择visual Basic编辑器图2 添加模块
在命令窗口添加如下代码(图3)：
Function WeightedAverage(Weight As Range, Value As Range) '加权平均计算。

Weight-权；value-值sWeight = Application.WorksheetFunction.Sum(Weight) '∑权，如∑样长、∑矿石量sWeightValue = Application.WorksheetFunction. SumProduct(Weight, Value)
'∑(权×值)，如∑(样长×mFe)、∑(矿石量×mFe) WeightedAverage = Round(sWeightValue / sWeight, 2) '平均值，如mFe，保留2位小数End Function
图3 在命令窗口添加代码
保存后，点击返回Excel视图。

2、利用WeightedAverage()进行品位加权平均计算
以矿床品位计算表(表1)为例，进行说明。

(1)进行各矿体类别品位计算
如图4所示，鼠标点击“E8”单元格，输入“=”号，点击左上角“函数”下拉列表中的“其它函数…”，出现“插入函数”列表框，选择“WeightedAverage”后“确定”，显示“函数参数”文本框。

在图4中，在Weight(权)文本框中，用鼠标下拉“D4:D7”、“E4:E7”，点击“确定”，“E8”单元格中便填入了TFe加权平均品位计算结果“31.65”。

为了简化mFe的计算操作，计算TFe时，Weight单元格区域的列采用了绝对地址(“$D4:$D7”)，这样在mFe计算时，可在表中直接拖动复制，不用再输入函数和选择单元格。

表1 矿床品位计算表续表1 矿床品位计算表
图4 函数参数输入
各矿体类别资源量求和，可使用Excel的“Sum”函数。

(2)矿体、矿床品位计算
矿体、矿床品位计算时，其类别品位是分散分布的，不便于使用WeightedAverage()计算。

为此，可使用Excel“数据”/“筛选”/“自动筛选”方法，将筛选结果复制后进行计算。

具体操作如下：
选取表格任一单元格，点击“数据”/“筛选”/“自动筛选”，各字段旁出现筛选箭头(图5)。

这时，点击“块段编号”旁的箭头，选中“类别”，则所有类别的计算结果被筛选
出来(图6)。

将“D8”至“F48”的数据(6行×3列)复制到表格下方，即可进行计算。

图5 自动筛选图图6 类别筛选结果图
对于按样长进行品位加权平均计算时，在“函数参数”文本框中选择样长和品位即可。

3、结论
本例提供了创建和使用自定义函数的方法，读者可据此自定义自己所需的其它函数。

本方法自定义的宏只对本工作簿有效。

若要对其它工作薄有效，应使用加载宏方法。