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

【#文档大全网# 导语】以下是®文档大全网的小编为您整理的《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”后“确定”，显示“函数参数”文本框。

1


在图4中，在Weight(权)文本框中，用鼠标下拉“D4:D7”、“E4:E7”，点击“确定”，“E8”单元格中便填入了TFe加权平均品位计算结果“31.65”。为了简化mFe的计算操作，计算TFe时，Weight单元格区域的列采用了绝对地址(“$D4:$D7”)，这样在mFe计算时，可在表中直接拖动复制，不用再输入函数和选择单元格。

表1 矿床品位计算表

矿体 编号

资源量 类别

块段 编号 332-1 332-2 332-3 332-4 类别 333-1 333-2 333-3 333-4 333-5 333-6 333-7 333-8 333-9 333-10 类别 332-5 332-6 332-7 类别 333-11 333-12 333-13 333-14

资源量 (万 t)

217.7 224.9 126.4 164.3

169.6 117.5 71.8 91.2 88.5 31.2 65.1 49.8 75.7 275.9

14.4 70.7 70.7

47.9 16.3 37.9 31.7

品 位(%) TFe mFe 31.51 21.17 30.61 20.96 32.24 20.94 32.82 20.84 32.55 20.75 31.58 21.04 31.40 21.39 30.34 20.59 30.89 21.35 30.51 20.35 33.30 21.75 32.76 21.54 33.00 20.59 32.29 20.75 32.97 18.63 31.24 18.13 31.10 18.40 32.63 16.80 32.72 19.47 33.02 18.46 30.92 18.53

矿体

编号

续表1 矿床品位计算表

资源量 类别

块段 编号 333-15 333-16 333-17 333-18 333-19 333-20 333-21 类别 332-8 332-9 类别 333-22 333-23 333-24 333-25 333-26 333-27 333-28 类别

资源量 (万 t) 62.0 33.1 65.2 89.1 50.2 24.4 78.3

47.7 47.7

2.6 27.2 18.4 39.2 12.3 29.8 2.6



品 位(%) TFe mFe 32.24 18.36 30.34 17.38 31.16 18.49 31.77 18.63 32.52 17.98 31.77 19.33 32.86 20.33 34.69 25.35 35.15 26.23 34.68 25.89 33.46 23.25 34.42 24.04 34.29 25.22 35.31 25.49 34.88 26.51 34.31 24.97

(332)

II

(333)

I

(333)

矿体



(332)

III

(333)

矿体 (332)

II

(333)

矿床

矿体

(332) (333) 矿床





图4 函数参数输入

各矿体类别资源量求和，可使用Excel的“Sum”函数。 (2)矿体、矿床品位计算

矿体、矿床品位计算时，其类别品位是分散分布的，不便于使用WeightedAverage()计算。为此，可使用Excel“数据”/“筛选”/“自动筛选”方法，将筛选结果复制后进行计算。具体操作如下：

选取表格任一单元格，点击“数据”/“筛选”/“自动筛选”，各字段旁出现筛选箭头(图5)。这时，点击“块段编号”旁的箭头，选中“类别”，则所有类别的计算结果被筛选



2


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





图5 自动筛选图

图6 类别筛选结果图

对于按样长进行品位加权平均计算时，在“函数参数”文本框中选择样长和品位即可。 3、结论

本例提供了创建和使用自定义函数的方法，读者可据此自定义自己所需的其它函数。 本方法自定义的宏只对本工作簿有效。若要对其它工作薄有效，应使用加载宏方法。

3

本文来源：https://www.wddqxz.cn/0fd62a1184c24028915f804d2b160b4e767f812e.html