Excel函数及Mapgis投影变换在柱状图绘制过程中的应用

刘宇潇

华东冶金地质勘查局综合地质大队，安徽马鞍山，243000

钻孔柱状图是地质勘查报告成果中的一个重要部分，而其绘制过程往往较为繁琐。尤其是水文地质及工程地质柱状图，其中包含岩心采取率及RQD采取率两项（图1）。要求将钻孔每一回次的岩心采取率及 RQD采取率用图表的形式表现出来。传统的绘制方法是使用计算器计算回次厚度按柱状图比例尺缩放后的长度，再用Mapgis中的“坐标输入线”来完成。这样一个回次一个回次地输入，消耗了大量的时间和精力，也影响了工作效率。下面介绍一种利用 Excel函数及Mapgis投影变换相结合的方法来实现水文地质及工程地质柱状图中岩心采取率及 RQD采取率部分的快速绘制。方法的总体思路是：将钻孔的回次厚度、岩心长度、完整岩心长度（即RQD）数据分别输入Excel表格，让Excel自动算出岩心采取率及RQD采取率，再编制公式换算出按柱状图比例尺缩放后落在Mapgis中的坐标，再将计算结果通过Mapgis中的投影变换功能导入Mapgis形成点文件，最后用点连线，完成绘制。下面将按两部分进行具体分述：

1 Excel函数的编制

Excel函数的编制很简单，但这里需要解决一个问题，就是要用函数来实现“四舍六入五化偶”这一数据修约规则。软件中自带的公式是没有这一规则的，所以需要用现有的公式来编制出一个相对较复杂的修约公式组。

关于在 Excel中实现“四舍六入五化偶”的方法，早有前人介绍过多种自己编制出的公式组合，但由于应用的领域不同、各人的逻辑思维方式亦不同，导致他人拿到公式组后理不清其中的逻辑关系，以致不知如何套用到自己需要处理的数据上。下面将以岩心采取率为例详细介绍本次工作所全新编制的修约公式组及其编制思路，以方便该公式组更好地被理解、传播和应用。

修约公式组的完整表达式为：

“=IF(OR(B1*100/A1-ROUNDDOWN(B1*10 0/A1,0)>0.5,AND(B1*100/A1-ROUNDDOWN(B1*100/A1,0)=0.5,MOD(ROUNDDOWN(B1*100/A1,0),2)=1)),ROUND(B1*100/A1,0),ROUNDDOWN(B1*100/A1,0))”。

公式组中共用到了5个公式，分别为：条件公式“IF”、或公式“OR”、并公式“AND”、余数公式“MOD”、向下舍入公式“ROUNDDOWN”和四舍五入公式“ROUND”。

其中：A1为回次厚度，B1为岩心长度。那么，B1*100/A1就是岩心采取率，单位为%。如用C1来代替B1*100/A1，则原公式组可简化为：

“=IF(OR(C1-ROUNDDOWN(C1,0)>0.5,AND(C1-ROUNDDOWN(C1,0)=0.5,MOD(ROUNDDO WN(C1,0),2)=1)),ROUND(C1,0),ROUNDDOWN(C1,0))”。

ROUNDDOWN(C1,0)和 ROUND(C1,0)中“0”是保留小数点后的位数，ROUNDDOWN(C1,0)公式的含义是将“C1”小数点后的数字全部舍去，只留整数，这里需注意它和“四舍五入”公式“ROUND”不同。ROUND(C1,0)的含义是按传统的“四舍五入”法则进行取整。

MOD(ROUNDDOWN(C1,0),2)是余数公式，括号中第一个数字是被除数，第二个数字是除数，输出的是余数，那么公式的含义就是将采取率向下舍入取整后除以2的余数。在数学中有一个常识，就是正奇数除以2，余数一定是1，正偶数除以2，余数一定是0。

公式组最外层是条件公式“IF”，它的基本形式是：

肌浆蛋白的提取参考Molina I等[18]的方法：准确称取5 g肉样，加入15 mL 0.2 mol/L，pH 6.5的PBS（甲液：NaH2PO4·2 H2O 31.21 g/L；乙液：Na2HPO4·12 H2O 71.64 g/L；甲：乙=1：2）混合均匀，在冰浴条件中4000 r/min匀浆10 min后，再10000 r/min 4 ℃离心20 min，上清液即为肌浆蛋白提取液。

IF(Logical_test,Value_if_true,Value_if_false)，Logical_test是任何可能被计算为true或false的数值或表达式；Value_if_true是Logical_test为true时的返回值；Value_if_false是Logical_test为false时的返回值。

在设置Logical_test时嵌入了一个或公式：

“OR(C1-ROUNDDOWN(C1,0)>0.5,AND(C1-ROUNDDOWN(C1,0)=0.5,MOD(ROUNDDOWN(C1,0),2)=1)”，包含了采取率与其本身向下舍入后的整数之差大于、等于0.5两种条件。而在等于0.5的条件中又嵌入了一个并公式：

AND(C1-ROUNDDOWN(C1,0)=0.5,MOD(R OUNDDOWN(C1,0),2)=1)。含义是采取率与其本身向下舍入后的整数之差等于0.5，并且采取率向下舍入后的整数除以2的余数为1。

了解了以上各个组成部分后，即可全面地解读修约公式组。其总体思路是：如果采取率与其本身向下舍入后的整数之差大于0.5、或等于0.5且采取率向下舍入后的整数除以2的余数为1时，按传统的“四舍五入”法则返回数值，否则将向下舍入，只保留整数。

如此一来，修约公式组即编制完毕，其他数据处理也就相对简单了。只需按柱状图的比例尺将回次厚度和采取率缩放，再与柱状图框架在Mapgis工程文件中的相对坐标进行加减，即可得出采取率与柱状图在 Mapgis工程文件中相匹配的相对坐标。

例如：柱状图比例尺是 1∶200，在 Mapgis工程文件中岩心采取率的起点是（300,500），RQD采取率的起点是（360,500），用50mm来表示采取率[0,100%]区间，如图2。

那么，岩心采取率每点横坐标即将采取率实际值除以2再加300，纵坐标则用500减去回次厚度按比例尺缩放后的值。其中回次厚度单位为米，按1:200的比例换算为软件中的毫米，数值上等于回次厚度乘以5，故计算公式为：

需要注意的是，本方法用的是“用点连线”，而在柱状图中每个回次采取率都是用矩形图表示的，因此，每个回次采取率都需要回次起点、回次终点两个坐标点来表示。例如第一个点（X1，Y1）表示第一回次的起点，第二个点（X2，Y2）表示第一回次的终点，X1应等于X2；第三个点（X3，Y3）表示第二回次的起点，则Y2应等于Y3。这样用点连出来的将是垂直转折的阶梯状图形。

通过计算和整理最后得出的数据如表1：

表1 采取率坐标换算统计表Table 1 Statistical list ofrecovery rate`s co-ordinates converting

2 投影变换

将换算后的各采取率点坐标，以两列为一组复制到txt文件中，保存（图3）。

打开Mapgis6.7主菜单，选择“实用服务”选项栏中的“投影变换”选项。在弹出的编辑窗口中，选择“投影转换”下拉菜单中的“用户文件投影转换”功能。弹出“用户数据点文件投影转换”窗口，选择“打开文件”，弹出对话框，从中找到保存后的txt文件，选择并打开。

在对话框左下角“设置用户文件选项”中选中“按指定分隔符”、“确定”。选择对话框中部“设置投影参数”中的“设置分隔符”。在弹出窗口的“分隔符号”栏中勾选Tab键，单击“属性名称所在行”下拉菜单，选中“X Y”、“确定”。

回到“用户数据点文件投影转换”窗口，在窗口下方设置X、Y坐标所在列：X位于1列，Y位于2列。勾选窗口右侧的“不需要投影”选项，此时其下方的“投影变换”选项变为“数据生成”，选择“数据生成”、“确定”。

在空白工作台中任意位置单击右键，选择“复位窗口”，弹出窗口中选择“NONAME0.WT”、“确定”，可以看到已经生成的点图元（图4）。

保存生成的点文件。回到Mapgis6.7主菜单，选择“图形处理”选项栏中的“输入编辑”选项。打开实现做好的水文地质及工程地质柱状图模板工程文件，将保存的点文件添加入工程文件。将点文件中的所有点图元选中，统改点属性，改成便于分辨的图形。

设置好线输入的缺省值，在“线编辑”选项栏中选择“用点连线”。光标依次对准每一个点图元，按F12，在弹出窗口中选择“靠近点”、“确定”。从而实现用点连线，最后“线文件提取弧段”、填充图案，完成采取率图表的绘制（图1）。

3 结语

使用Excel函数及Mapgis投影变换相结合的方法来绘制柱状图，是用机械地输入原始数据和一气呵成的用点连线来代替繁冗的计算和绘制，能有效地提高工作效率和图件的精准程度，同时也提高了从业人员对相关软件的操作技巧和软件本身的利用价值。无论是对于地质勘查行业由粗放生产型向精细研究型转变，还是对于专业软件自身的完善与升级，都具有较为可观的长远意义。

本文所介绍的仅是根据个人思维方式及工作习惯所总结出的制图方法，其中可能存在一些弊端和短板，还有待于后人的讨论和完善。