使用VBA編程制作MapGIS格式地球化學直方圖的方法研究

王增輝,伊飛

(1.山東省地質調查院，山東 濟南 250014；2.山東省礦業權儲備交易中心，山東 濟南 250014)

?

使用VBA編程制作MapGIS格式地球化學直方圖的方法研究

王增輝1,伊飛2

(1.山東省地質調查院，山東 濟南 250014；2.山東省礦業權儲備交易中心，山東 濟南 250014)

Excel和MapGIS是地球化學數據處理和制圖的常用軟件，根據數據表手工計算和制作MapGIS格式圖件的過程通常比較繁瑣，而MapGIS明碼格式為數據表和圖件的直接交互提供了可能。使用VBA編程可以迅速、批量地實現基于Excel數據表的地球化學專題圖件的生成，該文以地球化學數據直方圖的制作為例，在Excel中通過VBA編程完成多元素地球化學數據直方圖的制作。該方法首先在Excel中使用VBA編程完成制圖所需的關鍵統計值和參數的計算，之后根據MapGIS明碼格式的規則編程實現了點、線、區文件的批量生成，實現了數據表和圖件的直接交互，并且各種參數均可在代碼層修改，代碼完善后生成的圖件可不進行任何后期人工修改，制圖效率較高，制圖自由度高，操作簡便。VBA編程制圖具有靈活、易用和直觀的優點，可以大幅減少數據處理和制圖過程中的繁瑣操作，配合專業軟件使用，在數據預處理、統計、分析等過程中均具有較高的應用價值。

地球化學；數據處理；Excel；VBA；直方圖；MapGIS明碼

0 引言

數據處理和制圖是地球化學工作的基礎，目前開展的大面積地球化學調查和局部大比例尺地球化學調查工作往往涉及大量樣品和數據，數據處理和制圖工作如果完全由人工操作則工作量會非常龐大，因此，通常需要借助一些專業數據處理軟件進行。目前，常用的化探數據處理軟件數據處理流程通常包括：數據的預處理、數據導入、參數導入、成圖等幾個過程，操作相對較為復雜，數據準備工作繁瑣，并且多數化探數據處理軟件不是免費的，需要購買才能使用。

Excel是目前大多采用的基本數據處理工具，可以實現部分統計和計算功能[1-3]，VBA是Visual Basic的一種宏語言，是在其桌面應用程序中執行通用的自動化(OLE)任務的編程語言，能用來擴展Microsoft Office軟件功能。VBA在地球化學數據處理中有著廣泛的應用[4]，以往研究中借助在Excel的VBA編程可以實現多種處理功能[5-6]，也可以實現由數據表到圖件的直接轉化[7-8]，對簡化工作流程，提高工作效率大有幫助[9-11]。MapGIS軟件是地質行業常用的圖件制作軟件，其提供的明碼(ASCⅡ碼)格式為數據和圖件交互提供了可能[12-13]，這里以VBA編程實現地球化學直方圖為例，探討該方法在地球化學數據處理中的應用。

數據直方圖是常規地球化學圖的重要組成部分，用以反映數據分布情況，通常制圖中涉及多種元素一系列統計值的計算和制圖。制作過程如果借助常用的化探數據處理軟件完成，圖件的優化過程通常較為繁瑣，并且只能修改軟件給出的參數值，成圖的位置、大小、字體等眾多參數通常不可自定義，因此自由度受到制約，圖面效果也會受到影響，造成后續圖件修改工作量較大。這里以數據直方圖的制作為例，在Excel中通過VBA編程，實現批量地球化學數據直接生成MapGIS明碼格式圖件，這種方法實現了數據表和圖件的直接交互，并且各種參數均可在代碼層修改，代碼完善后可不進行任何后期人工修改，制圖效率較高。此外，由于VBA編程可直接調用Excel的函數方法，不需要編寫一般的函數代碼，因此編程效率也比較高。

1 方法

1.1 數據與參數

地球化學數據原始表格第一行為元素名稱，以下每行代表1件樣品的元素分析值，不相關屬性列放到表前面，參與制圖的元素列放到后面，VBA編程流程見圖1。具體如下：首先判斷數據表存放路徑下是否存在“直方圖”文件夾，沒有則新建該文件夾用以存放生成的直方圖文件；其次定義變量(也可在使用前定義)，主要包括：元素起始列號“s”、元素終止列號“e”、元素數“yss”、坐標軸原點坐標“x0”和“y0”。需運行前指定值的參數有：繪圖區寬“w”、繪圖區高“h”、分組數“zs”，在運行時根據每個元素有效(非空值)樣品數“yps”參數進行計算；其后根據規范要求，大部分滿足對數正態分布的元素含量使用對數等間隔組距，少部分常量元素需使用算術等間隔組距[14]。采用哪種類型可以根據經驗指定或根據數據分布形式判斷，這里根據數據列(myRange1)均值附近(0.9～1.1倍含量均值)數據集中程度確定使用對數等間距還是算術等間距，即如果該區域樣品數占比大于70%或指標名稱為“pH值”則選用算術等間距制圖，低于70%則選用對數等間距制圖，判斷語句代碼如下：

If (WorksheetFunction.CountIf(myRange1, ">" & WorksheetFunction.Average(myRange1) * 0.9) - WorksheetFunction.CountIf(myRange1, ">" & WorksheetFunction.Average(myRange1) * 1.1)) / WorksheetFunction.Count(myRange1) > 0.7 Or InStr(Sheet1.Cells(1, k), "pH") > 0 Then…

圖1 直方圖制作VBA編程流程圖

1.2 算術等間距制圖

首先需計算合理的分組原點值“z0”和組距“zd”，通常為了美觀將臨近均值的組放置在圖中央，標注的分組值和間距通常取“1，2，5，10，20，50，……”等整值，這里采用均值除以某一常數后取整再乘以該常數的倒數得到中間分組值，再由中間分組值減去5倍組距作為分組原點值“z0”。組距因不同元素數量級和離散程度差異較大，不宜通過計算得到，這里根據經驗值給定。根據相關規范pH值分組原點值和組距分別指定為4.5和0.5。之后根據上述z0和zd值統計各分組的樣品數量并寫入數據表后，代碼如下：

For i = 1 To zs

Sheet1.Cells(1, k + yss + 1) = "組" & Sheet1.Cells(1, k)

Sheet1.Cells(i + 1, k + yss + 1) = WorksheetFunction.CountIf(myRange1, ">=" & z0 + (i - 1) * zd) - WorksheetFunction.CountIf(myRange1, ">=" & z0 + i * zd)

Next i

1.3 MapGIS明碼文件生成

按照MapGIS明碼文件格式規則，點、線、區文件格式均需要首先寫入文件頭[15]，點、線、區文件頭分別為“WMAP9022”、“WMAP9021”、“WMAP9023”，各文件的其他結構嚴格按照MapGIS明碼文件格式進行寫入，規律的點、線、弧段如坐標軸標注、刻度等信息寫入使用For…next…語句編寫以精簡代碼，元素單位標注需按照元素名稱進行判斷，如“pH值”不標注單位，氧化物等常量指標標注“%”，金“標注”為“ng·g-1”，其他指標標注為“μg·g-1”。柱狀線根據各分組樣品數進行計算和標注，累計頻率曲線按照1%的間隔進行取值、計算和繪制。代碼如下：

'繪制柱狀圖線

For i = 0 To zs-1

Write #ifilenum, 1, 0, 1, 0.1, 10#, 10#, 0, 0, 0

Write #ifilenum, 4

Write #ifilenum, x0 + i * w / zs, y0

Write #ifilenum, x0 + i * w / zs, y0 + 2 * h * Sheet1.Cells(i + 2, k + yss + 1) / yps

Write #ifilenum, x0 + (i + 1) * w / zs, y0 + 2 * h * Sheet1.Cells(i + 2, k + yss + 1) / yps

Write #ifilenum, x0 + (i + 1) * w / zs, y0

Write #ifilenum, 5, w / zs + 2 * h * Sheet1.Cells(i + 2, k + yss + 1) / yps

Next i

'繪制累積頻率曲線

Write #ifilenum, 1, 0, 1, 0.1, 10, 10, 0, 0, 0

Write #ifilenum, 100

For i = 0 To 99

n = WorksheetFunction.CountIf(myRange1, "<=" & z0 + zs * zd * i * 0.01)

Write #ifilenum, x0 + w * i * 0.01, y0 + h * n / yps

Next i

Write #ifilenum, 6, 75

區文件結構相對較復雜，包括文件頭、弧段、節點、區共4個部分[16]，其中區面積值并不影響成圖，不需進行計算，可給定任意值代替。寫入區的代碼如下：

sfilename = Sheet1.Cells(1, k).Text

sfilename = ActiveWorkbook.Path & "直方圖” & ActiveSheet.Name & sfilename & ".wap" '定義區文件保存全名

ifilenum = FreeFile()

Open sfilename For Output As #ifilenum

Print #ifilenum, "WMAP9023"

Print #ifilenum, zs

'Print #ifilenum, zs - WorksheetFunction.CountIf(Sheet1.Range(Sheet1.Cells(2, k), Sheet1.Cells(1 + zs, k), 0))

'繪制柱狀圖弧段

For i = 0 To zs - 1

Write #ifilenum, 1, 0, 1, 0.1, 10#, 10#, 0, 0, 0 '弧段參數

Write #ifilenum, i + 1, i + 1 '前后節點號

Write #ifilenum, 0, i + 1 '左右區號

Write #ifilenum, 5 '點數

Write #ifilenum, x0 + i * w / zs, y0

Write #ifilenum, x0 + i * w / zs, y0 + 2 * h * Sheet1.Cells(i + 2, k + yss + 1) / yps

Write #ifilenum, x0 + (i + 1) * w / zs, y0 + 2 * h * Sheet1.Cells(i + 2, k + yss + 1) / yps

Write #ifilenum, x0 + (i + 1) * w / zs, y0

Write #ifilenum, x0 + i * w / zs, y0

Write #ifilenum, i + 1, w * 2 / zs + 2 * h * Sheet1.Cells(i + 2, k + yss + 1) / yps

Next i

'繪制節點

Write #ifilenum, zs + 1 '節點數

For i = 0 To zs - 1

Write #ifilenum, x0 + i * w / zs, y0

Print #ifilenum, "1N"

Write #ifilenum, i + 1

Write #ifilenum, 0 - i - 1

Next i

'繪制區

Write #ifilenum, zs '區數

For i = 0 To zs - 1

Write #ifilenum, 86, 0, 0#, 0#, 0, 0, 0, 0, 1, 0.494315, 8.802109

Write #ifilenum, 2

Write #ifilenum, i + 1

Write #ifilenum, 0

Next i

Close #ifilenum

1.4 對數等間距制圖

對數等間距制圖與算術等間距制圖代碼類似，二者主要區別在于統計樣品數量和分組原點值的計算方法，涉及樣品數量統計的計算均要按照以10為底數、以橫坐標值為指數的指數次冪乘方值進行計算，即調用POWER(number，Power)函數返回含量值。根據《區域地球化學勘查規范(DZ/T 0167-2006)》及《多目標區域地球化學調查規范(DD2005-01)》要求，對數間隔取正值時百分位為7，負值的百分位為3，組距z0按規定賦為0.1，代碼如下：

mean = WorksheetFunction.Average(myRange1)

z0 = WorksheetFunction.Round(WorksheetFunction.Log10(mean), 1) - 0.53

zd=0.1

1.5 成圖效果

VBA程序運行后將對每一元素生成一套MapGIS明碼文件“*.wat、*.wal和*.wap”，經MapGIS自帶的轉換軟件可轉為MapGIS專用的二進制文件“*.wt、*.wl和*.wp”以添加到標準地球化學圖上。對數等間距制圖與算術等間距制圖成圖效果見圖2和圖3，分組數均為14組，所有標注點、線、區均為自動生成，其參數均可在代碼層中統一修改以滿足最終地球化學圖需要。需要注意的是，對于部分峰值較高的元素，其柱狀圖最高點可能超出繪圖區域，這是應根據實際適當擴大“頻率”坐標軸取值范圍。

圖2 算術等間距直方圖

圖3 對數等間距直方圖

2 VBA在數據處理中的應用

在Excel中使用VBA編程處理數據優點較多，不僅可以方便地利用工作表存取數據，簡化操作，而且編程中可以直接調用Excel自帶的函數方法進行計算，不必單獨編寫函數功能代碼，易于實現并且降低了自編程的風險。VBA編程可以處理很多地球化學數據處理過程中的繁瑣操作，可以方便地實現MapGIS格式柱狀圖、剖面圖甚至表格的批量生成。不僅如此，VBA編程還可以實現由Excel表向任何明碼文件的批量轉制和編輯，如：文本文件(*.TXT)、離散點高程數據(*.DET)、通用GPS交換數據(*GPX)等，配合多種專業軟件使用，極大地簡化工作流程，提高工作效率[17-18]。

3 結語

地球化學元素和指標種類較多，一個工區范圍內各元素含量數量級相差可達幾倍甚至數萬倍不等，數據離散程度也不一致，因此繪制直方圖的種類和計算組間距等的方法很難做到完全一致，較簡單的辦法是編程前根據經驗在代碼層中對不同元素進行判別、分類，或計算前在表格中插入繪圖類型、分組原點值、分組數、單位等信息在編程過程中供直接調用，使成圖效果也能夠因“元素”而異。

MapGIS自有的點、線、區文件格式(*.wt、*.wl、*.wp)為二進制文件，結構相對較復雜，不易通過VBA編程的方式生成和修改，并且MapGIS自帶的文件轉換功能不夠完善，批量轉換明碼文件容易出錯，更好的轉換方法有待進一步研究。根據以往研究，建議對于復雜地球化學圖件的生成和編輯或要求生成圖件格式可直接供MapGIS軟件使用的，借助MapGIS二次開發功能來實現比較適宜。

[1] 春乃芽.如何利用Excel處理化探數據[J].物探化探計算技術,2006,28(3):272-276.

[2] 劉蕾.Excel與MapGIS結合在地質制圖中的應用[J].山東國土資源,2014,30(4):100-102.

[3] 劉肅敬,王文武,王生志.Excel在地質數據處理中的應用[J].化工礦產地質,2006,28(2):113-116.

[4] 路遠發.GeoKit:一個用VBA構建的地球化學工具軟件包[J].地球化學,2004,33(5):459-464.

[5] 郝光前,周立新.淺談VBA在工作中的應用[J].山東國土資源,2014,30(8).80-84.

[6] 葛建華,徐國棟,朱華平.Excel圖形化處理地球化學數據的應用研究[J].物探化探計算技術.2016，(1):120-124.

[7] 王芳,李波,劉慧鵬.利用Excel VBA自動繪制物化探平剖圖[J].云南地質,2010,29(2):231-234.

[8] 鐘家均.基于VBA的地震勘探綜合信息圖件制作方法及實現[J].物探化探計算技術,2007,29(4):307-312.

[9] 孫懷文,齊孔讓,孟煥梅.運用Excel及VBA語言快速智能地處理土工試驗數據[J].山東國土資源,2010,26(4):28-30.

[10] 唐朝永,易慧.VBA和MapGIS在化探異常解釋評價中的應用[J].物探與化探,2008,32(4):392-396.

[11] 鄭偉安,鄭驥飛,石佳桂,等.基于Visual Basic的Geoway—CASS數據轉換程序[J].山東國土資源,2016,32(4).78-81

[12] 段青梅,龍文華,丁天才,等.基于MapGIS明碼文件的繪圖轉換系統開發及應用[J].物探與化探,2005,29(1):50-52.

[13] 鐘華.利用VB開發的MapGIS明碼數據格式文件生成系統[J].地質學刊,2001,25(3):166-169.

[14] 中國地質調查局.多目標區域地球化學調查規范(1：250000，DD2005-01)[S].2005:23.

[15] 張印廷，何苗，張曉棠，等.MapGIS明碼文件的研究與應用[J].科技資訊,2009,16(2):9-12.

[16] 孫國慶,溫迎慶.基于MapGIS明碼文件實現區域自動填充的實踐[J].西部探礦工程,2007,19(7):97-98.

[17] 郭衍游,辛忠雷.Excel VBA在化探數據處理中的應用——以變異函數的計算為例[J].西藏地質,2002，(2):92-97.

[18] 鐘理韜.MapGIS明碼文件的獲取和在坐標轉換中的應用研究[J].安徽農業科學,2014(7):2171-2173.

Research on the Method of Making MapgGIS Format Geochemical Histogram by Using VBA Programming

WANG Zenghui1, YI Fei2

(Shandong Geological Surveying Institute, Shandong Jinan 250014, China; 2. Shandong Provincial Mining Right Reserve Trading Center, Shandong Jinan 250014, China)

Excel and MapGIS are two commonly used softwares in geochemical data processing and mapping. The manual process of data calculation and the MapGIS format map making are usually tedious. However, the MapGIS clear file format makes it possible for the direct interaction between geochemical data tables and geochemical maps. With VBA programming, geochemical maps based on data tables can be generated quickly and massively. Taking the making of geochemical histograms as an example, geochemical histogram of multi-elements are completed by using VBA programming in the Excel. Firstly, calculate the key statistics and parameters which should be needed in the mapping process by useing VBA program in the Excel, then generates the points, lines and area files according to the rules of MapGIS clear format by using a VBA program. This method can realize the direct interaction between the data tables and maps, and all parameters used in the histogram maps can be modified in the code layer. These maps can be used without any manual modification with optimized codes, the drawing efficiency and the degree of freedom are rather high, and the operation is very simple. VBA programming drawing is flexible, intuitive and easy to use, which can greatly reduce the tedious operation of data processing and drawing process. It has high application value in data preprocessing and statistical analysis when cooperating with other professional softwares.

Geochemistry; data processing; Excel; VBA; histogram, MapGIS format

2017-01-04；

2017-04-12；編輯：王敏 作者簡介：王增輝(1980—)，男，高級工程師，主要從事農業地質、環境地球化學研究；E-mail：269304558@qq.com

王增輝,伊飛.使用VBA編程制作MapGIS格式地球化學直方圖的方法研究[J].山東國土資源，2017,33(7)：77-81.WANG Zenghui, YI Fei .Research on the Method of Making MapgGIS Format Geochemical Histogram by Using VBA Programming[J].Shandong Land and Resources, 2017,33(7)：77-81.

TP302.4

B