王增輝,伊飛

(1.山東省地質(zhì)調(diào)查院，山東 濟(jì)南 250014；2.山東省礦業(yè)權(quán)儲(chǔ)備交易中心，山東 濟(jì)南 250014)

?

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

王增輝1,伊飛2

(1.山東省地質(zhì)調(diào)查院，山東 濟(jì)南 250014；2.山東省礦業(yè)權(quán)儲(chǔ)備交易中心，山東 濟(jì)南 250014)

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

地球化學(xué)；數(shù)據(jù)處理；Excel；VBA；直方圖；MapGIS明碼

0 引言

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

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

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

1 方法

1.1 數(shù)據(jù)與參數(shù)

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

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 算術(shù)等間距制圖

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

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

'繪制柱狀圖線

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

區(qū)文件結(jié)構(gòu)相對(duì)較復(fù)雜，包括文件頭、弧段、節(jié)點(diǎn)、區(qū)共4個(gè)部分[16]，其中區(qū)面積值并不影響成圖，不需進(jìn)行計(jì)算，可給定任意值代替。寫入?yún)^(qū)的代碼如下：

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

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

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 '弧段參數(shù)

Write #ifilenum, i + 1, i + 1 '前后節(jié)點(diǎn)號(hào)

Write #ifilenum, 0, i + 1 '左右區(qū)號(hào)

Write #ifilenum, 5 '點(diǎn)數(shù)

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

'繪制節(jié)點(diǎn)

Write #ifilenum, zs + 1 '節(jié)點(diǎn)數(shù)

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

'繪制區(qū)

Write #ifilenum, zs '區(qū)數(shù)

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 對(duì)數(shù)等間距制圖

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

mean = WorksheetFunction.Average(myRange1)

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

zd=0.1

1.5 成圖效果

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

圖2 算術(shù)等間距直方圖

圖3 對(duì)數(shù)等間距直方圖

2 VBA在數(shù)據(jù)處理中的應(yīng)用

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

3 結(jié)語

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

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

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

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

[3] 劉肅敬,王文武,王生志.Excel在地質(zhì)數(shù)據(jù)處理中的應(yīng)用[J].化工礦產(chǎn)地質(zhì),2006,28(2):113-116.

[4] 路遠(yuǎn)發(fā).GeoKit:一個(gè)用VBA構(gòu)建的地球化學(xué)工具軟件包[J].地球化學(xué),2004,33(5):459-464.

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

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

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

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

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

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

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

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

[13] 鐘華.利用VB開發(fā)的MapGIS明碼數(shù)據(jù)格式文件生成系統(tǒng)[J].地質(zhì)學(xué)刊,2001,25(3):166-169.

[14] 中國(guó)地質(zhì)調(diào)查局.多目標(biāo)區(qū)域地球化學(xué)調(diào)查規(guī)范(1：250000，DD2005-01)[S].2005:23.

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

[16] 孫國(guó)慶,溫迎慶.基于MapGIS明碼文件實(shí)現(xiàn)區(qū)域自動(dòng)填充的實(shí)踐[J].西部探礦工程,2007,19(7):97-98.

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

[18] 鐘理韜.MapGIS明碼文件的獲取和在坐標(biāo)轉(zhuǎn)換中的應(yīng)用研究[J].安徽農(nóng)業(yè)科學(xué),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；編輯：王敏 作者簡(jiǎn)介：王增輝(1980—)，男，高級(jí)工程師，主要從事農(nóng)業(yè)地質(zhì)、環(huán)境地球化學(xué)研究；E-mail：269304558@qq.com

王增輝,伊飛.使用VBA編程制作MapGIS格式地球化學(xué)直方圖的方法研究[J].山東國(guó)土資源，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