如何用Excel打造资源储量估算系统-系列（2）

220gangge · 发表于 2021-5-25 14:39

本帖最后由 220gangge 于 2021-5-25 14:42 编辑

文字来源于公众号“地质进阶”（一个专注于从小白到高手的地质公众号），欢迎关注。1 资源储量估算系统创建

基于Excel软件以上特点，根据几何法（主要是地质块段法、开采块段法、断面法）资源储量估算所需参数（品位、厚度、面积、体重），利用Excel强大的函数功能，在各类别表格中编辑相应参数公式并进行跨表格关联，从而创建资源储量估算系统，然后结合使用辅助软件“Excel精灵”的批处理功能，对表格进行格式规范化，最终达到提交符合勘探阶段规范要求完整附表的目的。

1.1 勘探报告附表清单

根据《固体矿产勘查工作规范》（GB/T33444-2016）附录T.3.3，勘探阶段所附表格共有6大类，即测量成果表、探矿工程质量一览表、采样及样品测试结果表、矿石/夹石及顶底板围岩物理性能测量结果表、资源储量估算相关表和开采技术条件附表，根据银山矿区实际情况及表格之间依存关系，梳理为6类35个分表格，如（表 1）示。

表 1 银山勘探报告附表及表格间相互关系

1.2 资源储量估算表格链接关系

根据资源储量估算过程，梳理资源储量估算所需基分表、品位表、钻弯表、钻质表、厚度表、块段表和估算表等7类表格的链接关系如（图 2）示。

图 2 资源储量估算表格链接关系图

由上图可知，“基分表”是底层基础表格，其数据录入的准确性、规范性对后续工作起着极为关键的作用，故在使用前应进行必要的错误检查。工作中常见错误有：①数字型数据变为文本型，导致后续公式不能正确筛选和计算；②数字0和字母O的误录入；③小数点位的错位引起数据突变，如银山矿Cu、S、Au三元素相关性较高，如有一方出现较高值，而其它两方值较低，则需引起注意，须对应原始地质编录，核实后使用。

1.3 资源储量估算表格编制要求1.3.1 单工程样品基本分析结果表

据（图 3）所示格式填充、检查，表格命名为“基分”，适用于钻探、坑探、槽探等工程。该表功能是①存储基本分析结果数据；②根据工业指标进行初步单工程矿体圈定，为下一步编制单工程矿体平均品位计算表做准备。

图 3 单工程样品基本分析结果表样式

1.3.2 单工程矿体平均品位计算表

据（图 4）样式编制单工程矿体平均品位计算表，表格命名为“品位”。该表功能为①计算每个单工程的品位数据；②用于指导剖面图单工程圈矿和剖面、估算图单工程品位数据填充；③为编制“钻质表”和“块段表”做数据基础。

图 4 单工程矿体平均品位计算表样式

该表格在编制样式上，需注意三点：①两个单工程之间用空行隔开；②增加辅助列T“辅助隐藏”，计算“小计、平均”的个数和顺序，在T5单元格中输入参考公式“=IF(OR(C5="小计",C5="平均"),(C5&COUNTIF(C$5:C5,C5)),"")”，双击下拉充填；③M列黄色充填为该单工程的品位数据，用于粘贴至Cad剖面图、估算图中，省去人工输入与检查的麻烦，后续附表打印时，选中该列，通过F5→定位条件→公式→文本，批量选中删除。

在内容上，“孔号”、“矿体编号”为手工录入，“乘积”列直接录入代表长和品位的乘积公式，关键点在于如何实现C列“样号”输入后，后续D列“代表长（米）”至L列“S（%）”数据能够直接对应显示。

实现思路：D列至L列思路一致，现以在C5单元格输入“32”号样后得到D5单元格的“1.51”代表长为例讲解实现方法，E至L列参照编辑公式。该表中样号、代表长来源于“基分表”的“野外编号”和“代表长度”，观察基分表可知，每个样品的“野外编号”与“代表长度”在同行不同列（分别在C列和G列），那么在“品位表”C5中引用基分表32号样“='基分'!C36”（即32号样在基分表中的单元格地址）后，在D5中录入公式时，只需考虑读取出C5引用的地址“'基分'!C36”，然后单独把字母C替换为G即可。在D5中输入参考公式=INDIRECT(SUBSTITUTE(SUBSTITUTE(FORMULATEXT($C5),"C",SUBSTITUTE(ADDRESS(1,COLUMN(G$1),4),1,)),"=",""))”，其中FORMULATEXT函数（EXCEL HOME，2019）为Office2013新增函数。

由此，“基分”表和“品位”表链接在一起。

1.3.3 钻孔弯曲度计算表

钻孔弯曲度计算表为规范中未要求附，但在制图和后续钻探工程质量一览表（钻质表）和单项工程矿体厚度计算表（厚度表）中起很大作用，按（图 5）样式制表，表格命名为“钻弯”。

该表在样式上以空格隔开，分为上下两个部分；增加C列“辅助隐藏”，将A列工程编号和B列样品编号连接在一起，用于后续“钻质表”数据处理。

表格上半部分有两个功能，记录测点测斜结果（B、D、E列，用于钻质表）和绘制钻线剖面或平面投影图（O、P列）；下半部分有三个功能，①计算每个样品的底板孔深（F列，用于钻质表和厚度表）；②计算样品在估算图上的投影位置（P列）；③绘制带样轨的钻线（O、P列）。其中计算样品在储量图上的投影位置思路为：见矿质量点在储量图上位置由两个参数确定，偏离勘探线距离和该质量点的标高，由此在N19单元格（黄色充填）输入钻孔偏线数据，在R19单元格输入钻孔孔口标高，即可在P列计算出每个样品的投影位置。

图 5 钻孔弯曲度计算表样式

在内容上，除工程编号、测点深度、测斜结果为原始数据，需手工填写，其它列根据表格提示或相关规范编制相应公式，在此不赘述。编制好钻弯表，为钻质和厚度表奠定基础。

1.3.4 钻探工程质量一览表

按（图 6）所示格式编制钻探工程质量一览表，表格命名为“钻质”。该表功能主要为判定钻孔施工质量，并为下一步“厚度表”做数据准备。

表中开孔方位（C列）、开孔倾角（D列）、终孔井深（E列）、孔斜测量（J、K、L列）直接引用“钻弯表”数据，G和M-W列为原始数据，手工填写。关键点在F列“见矿井深（m）”、H列“矿体号”和I列“矿心采取率”数据的引用和显示。

图 6 钻探工程质量一览表样式

实现思路：F列“见矿井深（m）”数据引自“钻弯”，H列“矿体号”和I列“矿心采取率”数据引自“基分”表格。其中F列“见矿井深（m）”取值于每个钻探单工程中心质量点的井深，如图4“品位表”所示32和33号样的中心位于32号样的底板或33号样的顶板，由于钻弯表计算的是每个样品底板的井深（如2.3.3钻弯曲下半部分功能①），那么两样品的中心质量点为32号样，该样号值通过两样号的算术平均值32.5向下进位取得。观察图4可知，除第一个单工程，后续每个单工程都处于“平均”之间，且单工程的最小质量点和最大质量点分别处于上一个“平均”的底板和本单工程的顶板，可以考虑通过“平均”的位置定位到每个单工程的最小质量点和最大质量点，然后求取最小质量点和最大质量点的平均值，得到该单工程的中心质量点，再在“钻弯”表格中返回其井深值，即可得到“见矿井深（m）”。F6单元格直接引用见矿孔深，F7及以下单元格输入参照公式：“=IF(INDEX('品位'!$A$1

A$3236,MATCH("平均"&(ROW(B2)-COUNTIF($F$6:F6,"")),'品位'!$T$1:$T$3236,0))=B7,(VLOOKUP((INDEX('品位'!$A$1:$A$3236,MATCH("平均"&(ROW(B2)-COUNTIF($F$6:F6,"")),'品位'!$T$1:$T$3236,0))&ROUNDDOWN(AVERAGE(INDEX('品位'!$C$1:$C$3236,MATCH("平均"&(ROW(B1)-COUNTIF($F$6:F6,"")),'品位'!$T$1:$T$3236,0)+2),INDEX('品位'!$C$1:$C$3236,MATCH("平均"&(ROW(B2)-COUNTIF($F$6:F6,"")),'品位'!$T$1:$T$3236,0)-2)),0)),'钻弯'!$C$3:$F$3994,4,FALSE)),"")”。需要注意的是为了做到识别“平均”的唯一性，“品位表”的“辅助隐藏”T列将“小计”或“平均”进行了排序标识（如2.3.2说明），“钻弯”中“辅助隐藏”列将钻孔编号和样品号进行了绑定区分（如2.3.3说明）。

H列“矿体号”和I列“矿心采取率”数据在“品位表”中“小计”的左侧和右侧，通过小计的位置向左右偏移，可取得该两值。在H6、I6及以下单元格中分别输入参考公式：=IF(F6="","",INDEX('单工'!$B$1:$B$3236,MATCH("小计"&(ROW(A1)-COUNTIF($F5:F$6,"")),'品位'!$T$1:$T$3236,0)))、=IF(F6="","",INDEX('品位'!$F$1:$F$3236,MATCH("小计"&(ROW(B1)-COUNTIF($F$5:F6,"")),'品位'!$T$1:$T$3236,0)))。

需要注意的是，F、H和I列数据宜在“品位表”单工程圈矿终稿确定后再引用，避免过程中因“品位表”增删数据而“钻质”表格未及时更新导致单元格错误适配。

由此钻质、钻弯和品位表链接在一起。

1.3.5 单项工程矿体厚度计算表

按（图 7）所示格式编制单项工程矿体厚度计算表，表格命名为“厚度”。该表主要功能是计算单工程矿体真厚度、水平（铅直）厚度；真厚度与最小可采厚度对比，指导剖面图单工程正确圈矿。

表格中“工程编号”B列、“矿体编号”C列直接引用“钻质”表H列数据，“倾向b”、“矿体倾角α”列分别在中段图、剖面图中量取后手工录入，真厚度、水平厚度根据标注编制公式。关键点在于D列“样号”至G列“方位a”的引用和显示。

图 7 单项工程矿体厚度一览表样式

实现思路：D列“样号”数据源于“钻弯表”，可将“钻质”表中的“见矿井深”和“工程编号”绑定在一起后，以钻弯表中辅助列C为对接点，在“钻弯”表锁定唯一的样号。在D6单元格中输入参考公式：“=IF(C6="","",SUMPRODUCT(('钻弯'!$A$3:$A$3994='钻质'!$B6)*('钻弯'!$F$3:$F$3994='钻质'!$F6)*('钻弯'!$Q$3:$Q$3994<>"")*('钻弯'!$B$3:$B$3994)))”。

F列“钻孔穿矿倾角β”、G列“方位a”数据源于“钻弯表”，实现思路与D列一样，分别在F6、G6中输入参考公式：“=IF(E6="","",SUMPRODUCT(('钻弯'!$A$3:$A$3994='钻质'!$B6)*('钻弯'!$F$3:$F$3994='钻质'!$F6)*('钻弯'!$Q$3:$Q$3994<>"")*('钻弯'!$E$3:$E$3994)))”和=IF(F6="","",SUMPRODUCT(('钻弯'!$A$3:$A$3994='钻质'!$B6)*('钻弯'!$F$3:$F$3994='钻质'!$F6)*('钻弯'!$Q$3:$Q$3994<>"")*('钻弯'!$D$3:$D$3994)))。

E列“样长L（m）”数据源于“品位表”，在“小计”的右侧，读取小计位置并偏移后得到，在E6中输入参考公式：“=IF(OR(C6=0,C6=""),"",INDIRECT(ADDRESS(MATCH("小计"&COUNT($D$6

6),'品位'!$T$1:$T$4522,0),4,4,TRUE,"品位")))”。

“厚度”表每一行都是与“钻质”表一一对应的，宜在“钻质”表确定后再编制或根据“钻质”表的增删改查同步更新。

由此厚度表与钻质表、钻弯表和品位表链接在一起。

1.3.6 矿体块段平均厚度、平均品位计算表

依据（图 8）所示编制矿体块段平均厚度、平均品位计算表，表格命名为“块段”。该表的功能是计算每个块段的品位、厚度数据，并为下一步“估算表”各块段资源储量计算做准备。

图 8 矿体块段平均厚度、平均品位计算表样式

表格中A-D列、V和W列皆为手工输入，“乘积”P-U列可直接编入水平厚度与品位的乘积公式，关键点在于 “矿体编号”A列和“工程名称”D列输入后，I列“水平厚度（米）”到O列“S（%）”数据对应显示。

实现思路：I列“水平厚度（米）”数据源于“厚度表”；J-O列数据源于“品位表”，实现思路一致，公式参照录入。其中I列“水平厚度（米）”在“厚度表”根据矿体编号和工程名称确定惟一值，同样地J至O列根据矿体编号和工程名称在“品位表”中匹配惟一值，在I5和J5单元格中分别输入参考公式：=SUMPRODUCT(('厚度'!$B$6:$B$2400=$D5)*('厚度'!$C$6:$C$2400=$A5)*('厚度'!$M$6:$M$2400))；=SUMPRODUCT(('品位'!$A$5:$A$17002=$D5)*('品位'!$B$5:$B$17002=$A5)*('品位'!$C$5:$C$17002="平均")*('品位'!G$5:G$17002))。

为了便于后续工作，增加了“辅助列”，将面积、平均顺序编号，将块段编号和矿体编号引用至“平均”右侧。

由此块段表和品位表、厚度表链接在一起。

1.3.7 矿体地质块段法资源量估算结果表

依据（图 9）所示编制矿体地质块段法资源量估算结果表，表格命名为“估算”。该表的主要功能是汇总资源储量估算所需参数，计算各块段矿石量、金属量，为下一步“汇总表”数据累加做准备。

图 9 矿体地质块段法资源量估算结果表样式

表格中“矿量（t）”F列为面积、厚度、体重值的乘积，“资源量类别”C列为“块段编号”B列的一部分，“金属量”、“硫量”M-R列为矿石量与品位的乘积，直接录入公式解决，关键在于A列“矿体编号”至L列“S（%）”的引用和显示。

实现思路：A列“矿体编号”至L列“S（%）”皆源于“块段表”，观察可知，矿体编号、块段编号、厚度、品位都在“平均”的右侧，则以“平均”为基点，向右偏移，则可得到相应单元格的值。在A5单元格中录入参考公式：=VLOOKUP('块段'!$C$10&ROW(A1),'块段'!$F$5:$O$20111,3,FALSE) ，B5、E5和G5参照编制。

D列“面积（m2）”数据源于“块段表”中“探矿权面积”或“采矿权面积”，以“辅助列”中“面积”为起点向右偏移得到，在D5单元格中录入参考公式：=IF(VLOOKUP('块段'!$E$5&ROW(C1),'块段'!$F$5:$W$20111,17,FALSE)="",VLOOKUP('块段'!$E$5&ROW(C1),'块段'!$F$5:$W$20111,18,FALSE),VLOOKUP('块段'!$E$5&ROW(C1),'块段'!$F$5:$W$20111,17,FALSE))。

由此，估算表和块段表链接在一起。

1.3.8 矿体地质块段法资源量估算结果汇总表

依据（图 10）所示编制地质块段法资源量估算结果汇总表，表格命名为“汇总”。该表是资源储量估算的最后一步，主要功能是将各矿体块段按资源量类别分类统计，得到矿山最终的资源量数据。

图 10 矿体地质块段法资源量估算结果汇总表样式

表中“矿体编号”A列、“资源量类别”B列手工输入，“品位”D-I列编辑矿石量与金属量（硫量）的除法公式，关键点在于C列“矿量（t）”和J列“Pb（t）”至O列“S（万吨）”的引用和显示。

实现思路：C列和J-O列分别在“估算”表中统计后再在“汇总”表中引用，思路一致。以C列“矿量（t）”为例，利用SUMPRODUCT函数，根据矿体编号、资源量类别两个判定条件，可将“估算表”中全部符合条件的矿石量筛选并求和。为了简化公式输入长度，将“估算表”中“矿体编号”、“资源量类别”、“矿量（t）”、“金属量”等数据列定义为简洁的名称（快捷键CTRL+F3），如“资源量类别”定义为“类别”，然后在C5单元格中输入参考公式：=ROUND(SUMPRODUCT((矿体号=$A5)*(类别=$B5)*(铜品位>=0.4)*矿量)/1000,0)，即可统计出某一类别的矿石量，金属量、硫量的统计参考该公式计算，需要注意不同参数之间单位换算。

至此汇总表和块段表链接在一起，整个资源储量估算系统建立完成。

1.3.9 文本型附表规范整理

文本型附表是指表格中全部或部分不需要对外链接的部分，为了打印美观，宜将表格中有大量相同项的单元格合并，并按照页码拆分，如“基分”表中“样品名称”列，大量样品名称单调重复会使表格看上去繁冗累赘，合并相同项则使表格简洁清爽，但如果一一识别相同项，合并后再分页，会非常耗时耗力，此时使用“Excel精灵”插件→合并工具箱→合并列中相同值→让合并单元格合理分页的功能，瞬时高效完成。该插件同时集成了其它利于提高工作效率的功能，可熟悉使用。

2 结束语

本文通过录入基础数据，使用EXCEL软件函数功能，将资源储量估算所需的各个表格有效的链接在一起，搭建了完整的资源储量估算系统，达到了近乎全流程数据自动生成的目的，从而减少了数据二次检查时间和避免了人为输入数据引起错误现象的出现。结合辅助插件“Excel精灵”的批处理功能，可达到整个附表高效规范化提交的目的，总之二者结合极大地节省了地质技术人员报告附表的编制时间和精力。

同时需要指出的是，文中给出的参考公式经银山勘探项目实战有效，但不一定是最优解甚至较为繁复，当数据量较大时，公式运行会因计算机性能而出现卡顿现象，故本文旨在提供解题思路，以期达到抛砖引玉之能。

除此以外还可以使用EXCEL软件对录入的数据进行特高品位处理、品位（厚度）变化系数计算、有益（害）元素相关性分析、内外检超差检查等报告中需要数据的计算，在此不赘述。