Excel在儲量地質(zhì)報告附表排版中的應(yīng)用

李光明

(山東省地質(zhì)科學(xué)研究院，山東 濟(jì)南 250013)

附表是儲量地質(zhì)報告的重要組成部分。生成格式統(tǒng)一、規(guī)范的附表，是儲量地質(zhì)報告編寫中的一項重要工作。目前生成附表的方法，一是在Excel附表的基礎(chǔ)上形成Word附表，然后進(jìn)行排版；二是對Excel附表直接進(jìn)行排版，并且排版過程都是人工完成的，過程繁瑣，效率低下，難以做到規(guī)范、統(tǒng)一。為了解決以上問題，該文介紹一種在Excel 2010環(huán)境下，利用程序方式，快速高效地進(jìn)行儲量地質(zhì)報告附表排版的方法。

1 排版要求

附表排版包括頁面設(shè)置、表格格式設(shè)置和關(guān)鍵字欄目合并。

1.1 頁面設(shè)置

頁面布局包括紙張規(guī)格、紙張方向、頁邊距、居中方式、頁眉頁腳。

紙張規(guī)格為A4(297mm×210mm)，方向為橫向。根據(jù)《黨政機(jī)關(guān)公文格式》(GB/T 9704—2012)規(guī)定，標(biāo)準(zhǔn)頁邊距設(shè)置見圖1，即左、右邊界寬度為36mm，上、下邊界寬度為27mm。居中方式為水平居中，頁眉、頁腳距上、下端距離為20mm。

圖1 頁邊距設(shè)置/mm

1.2 表格格式

頂端標(biāo)題行：1～3行，包括表題1行、欄目2行。表題行行高20磅，字號16磅；欄目行行高15.25磅，字號9磅。

數(shù)據(jù)區(qū)(表身)：行高15.25磅，字號9磅(表寬超過頁寬時，字號自動縮小)，每頁包括25行。

頁碼：包括2套頁碼，即在附表冊中的總頁碼和所屬附表的頁次和頁數(shù)。如圖2中附表3，該表共5頁，其第1頁在附表冊中的總頁碼為“- 3 -”(右下角)，自身頁碼為“第1頁 共 5 頁”(見右上角)。頁碼設(shè)于頁腳和頁眉中。

責(zé)任人、日期：設(shè)于頁腳中。

單元格邊框線型：默認(rèn)的線型較粗，為此將其改為細(xì)線。

1.3 關(guān)鍵字欄目合并

為了便于閱讀和使用，附表中勘查線編號、工程編號、礦體編號、品位等關(guān)鍵字欄目常需要進(jìn)行合并。合并時不允許跨越上一級欄目和頁。

圖3為勘查線編號、工程編號、礦體編號欄目合并前、后對比圖。

圖2 表格格式示例

圖3 關(guān)鍵字欄目合并前、后對比圖

2 方法原理

(1)數(shù)據(jù)區(qū)行數(shù)=(第1個水平分頁符的行號-1)-標(biāo)題行行數(shù)，即(PageSetup.HPageBreaks(1).Location.Row-1)-標(biāo)題行行數(shù)。圖3中為(29-1)-3=25(行)。

(2)每個附表的頁數(shù)：利用水平分頁符(HPageBreaks.Count)計算頁數(shù)。如果某個附表的最后1頁滿頁(25行)，頁數(shù)即等于HPageBreaks.Count，否則等于HpageBreaks.Count+1。

(3)每一個附表在全部附表中的起始頁碼，等于前繼附表累計頁數(shù)+1。如：該文中附表1為1頁，附表2為1頁，附表3為5頁，則附表4的起始頁碼為(1+1+5)+1=8，即附表4的PageSetup.FirstPageNumber等于8。

在附表冊中的總頁碼(&P)注記在右頁腳中，因此，應(yīng)將其賦值給PageSetup.RightFooter。

(4)每個附表自身頁碼“第 頁 共 頁”中，前者等于其頁碼(&P)-(PageSetup. FirstPageNumber-1)，后者等于該表的頁數(shù)(&N)。其注記在右頁眉中，因此，賦值給PageSetup.RightHeader即可。

(5)使表格不超寬：利用垂直分頁符判斷。若PageSetup.VPageBreaks.Count>0，表示表格超寬，這時縮小字號，直至表格壓縮在1頁中。如字號過小，可適當(dāng)修改左、右邊距。

3 排版程序

3.1 程序代碼

包括頁面和格式設(shè)置程序pPageSetup()、關(guān)鍵字欄目合并程序pColumnMerge()。

3.1.1 頁面和格式設(shè)置程序

頁面和格式設(shè)置程序如下：

SubpPageSetup()'參數(shù)設(shè)置nPageWidth=29.7'A4頁面寬度(橫向)nLeftMargin=3.6'左邊距(cm)nRightMargin=3.6'右邊距nTopMargin=2.7'上邊距nBottomMargin=2.7'下邊距nHeaderMargin=2'頁眉nFooterMargin=2'頁腳nCaptionRowHeight=20'標(biāo)題行行高(磅,7.05mm)nCaptionFontSize=16'標(biāo)題字號(磅,三號,5.64mm)nWordRowHeight=15.25'數(shù)據(jù)行行高cMaker="智標(biāo)仁"'制表人(應(yīng)根據(jù)情況修改制表人及日期)cExaminer="簡姹"'檢查人(應(yīng)根據(jù)情況修改檢查人及日期)cAuditor="沈河"'審核人(應(yīng)根據(jù)情況修改審核人及日期)MakeDate="2017年7月29日"'制表日期ExaminationDate="2017年7月30日"'檢查日期AuditDate="2017年7月31日"'審核日期Application.ScreenUpdating=False'屏幕刷新關(guān)閉Application.PrintCommunication=True'2010版啟用與打印機(jī)通信,否則無法正常執(zhí)行頁眉、頁腳的設(shè)置代碼'Application.PrintCommunication=False'2003版關(guān)閉與打印機(jī)通信,提高代碼運行效率'刪除原目次頁內(nèi)容Worksheets("目次頁").SelectWithSheets("目次頁") nLines=.UsedRange.Rows.Count .Rows("4:"&nLines).DeleteShift:=xlUp .Rows("4:104").RowHeight=20 .Rows("4:104").Font.Size=10EndWithnSheets=Worksheets.Count'工作表個數(shù)Forsh=4TonSheets'前3個是封面、題名頁和目次頁,不是表 Worksheets(sh).Select WithActiveSheet nWordFontSize=9'數(shù)據(jù)字號(小五號,3.15mm) With.PageSetup .PrintTitleRows="$1:$3"'標(biāo)題行 .Orientation=xlLandscape'橫向打印模式(xlPortrait縱向打印模式) .PaperSize=xlPaperA4'A4紙 .LeftMargin=Application.CentimetersToPoints(nLeftMargin)'左邊距 .RightMargin=Application.CentimetersToPoints(nRightMargin)'右邊距 .TopMargin=Application.CentimetersToPoints(nTopMargin)'上邊距 .BottomMargin=Application.CentimetersToPoints(nBottomMargin)'下邊距 .HeaderMargin=Application.CentimetersToPoints(nHeaderMargin)'頁眉 .FooterMargin=Application.CentimetersToPoints(nFooterMargin)'頁腳 .CenterHorizontally=True'水平居中 .CenterVertically=False'垂直不居中 EndWith .Range("A4").Select ActiveWindow.FreezePanes=True'凍結(jié)1～3行 nLines=.UsedRange.Rows.Count'表的行數(shù) nColumns=.UsedRange.Columns.Count'表的列數(shù)--數(shù)值 cColumns=IIf(nColumns<=26,Chr(64+nColumns),"A"&Chr(64+nColumns-26))'表的列數(shù)--字符,如:A、AA .Rows("1:1").RowHeight=nCaptionRowHeight'標(biāo)題行行高 .Rows("1:1").Font.Size=nCaptionFontSize'標(biāo)題行字號 .Rows("2:"&nLines).RowHeight=nWordRowHeight'欄目行、表身行高 .Rows("2:"&nLines).Font.Size=nWordFontSize .Columns("A:"&cColumns).EntireColumn.AutoFit'列自適應(yīng)寬度 '若表格寬度超過1頁,減小字號,使表格保持在1頁中 wPage1=nPageWidth-nLeftMargin-nRightMargin'頁面理論寬度(cm) wPage2=Application.CentimetersToPoints(wPage1)'頁面理論寬度(磅) nTimes=8 DoWhile.VPageBreaks.Count>0'若表格寬度超過1頁,則縮小 wSheet=.UsedRange.Columns.Width'表格實際寬度(磅) nWordFontSize=Round(nTimes*wPage2/wSheet,1)'計算字號(如字號過小,可適當(dāng)修改左、右邊距) .Rows("2:"&nLines).Font.Size=nWordFontSize'設(shè)置字號 .Columns("A:"&cColumns).EntireColumn.AutoFit'列自適應(yīng)寬度 nTimes=nTimes-0.5'可酌情修改,如將0.5改為0.25 Loop

該程序同時生成目次頁。

3.1.2 關(guān)鍵字欄目合并程序

關(guān)鍵字欄目合并程序如下：

SubpColumnMerge()'基本分析結(jié)果表關(guān)鍵字欄目合并'B列勘查線剖面編號、C列工程編號、D列礦體編號 nRows=ActiveSheet.UsedRange.Rows.Count'表的行數(shù) i=4'表中數(shù)據(jù)行的首行號 Application.DisplayAlerts=False'信息警告關(guān)閉 nRowsPerPage=(ActiveSheet.HPageBreaks(1).Location.Row-1)-(i-1)'每頁數(shù)據(jù)的行數(shù) DoWhilei<=nRows'表 nPageRowNo=1'每頁的初始行號 DoWhilenPageRowNo<=nRowsPerPageAndi<=nRows'頁 pmRowNo1=i'剖面的起始行號 pmbh=Cells(i,"B")'剖面編號 DoWhileCells(i,"B")=pmbhAndnPageRowNo<=nRowsPerPageAndi<=nRows '剖面 gcRowNo1=i'工程的起始行號 gcbh=Cells(i,"C")'工程編號 DoWhilegcbh=Cells(i,"C")AndnPageRowNo<=nRowsPerPageAndi<=nRows'工程 ktRowNo1=i'礦體的起始行號 ktbh=Cells(i,"D")'礦體編號 DoWhilektbh=Cells(i,"D")Andgcbh=Cells(i,"C")And_ nPageRowNo<=nRowsPerPageAndi<=nRows'礦體 nPageRowNo=nPageRowNo+1'頁的行號增1 i=i+1'表的行號增1 Loop ktRowNo2=i-1'礦體的終止行號 If""<>ktbhThen'非礦體不合并 Range(Cells(ktRowNo1,"D"),Cells(ktRowNo2,"D")).Merge'礦體編號合并 EndIf Loop Range(Cells(gcRowNo1,"C"),Cells(ktRowNo2,"C")).Merge'工程合并 Loop Range(Cells(pmRowNo1,"B"),Cells(ktRowNo2,"B")).Merge'剖面合并 LoopLoopApplication.DisplayAlerts=True'信息警告開啟EndSub

該程序中，B列為勘查線編號、C列為工程編號、D列為礦體編號。若用戶附表中的列號和程序中的不同，應(yīng)對程序作相應(yīng)修改。

3.2 程序操作

3.2.1 程序輸入

分別將程序pPageSetup()、pColumnMerge()輸入到【封面】、【基本分析】模塊代碼窗口中。以pPageSetup()為例，方法如下：

將鼠標(biāo)置于【封面】標(biāo)簽之上，點擊右鍵，彈出快捷菜單(圖4)，單擊【查看代碼】，顯示【封面】模塊代碼窗口(圖5)，將程序代碼輸入其中。

圖4 工作表標(biāo)簽快捷菜單

圖5 代碼窗口

3.2.2 程序運行

分別在【封面】、【基本分析】模塊代碼窗口中，將光標(biāo)停留在代碼范圍內(nèi)，按功能鍵【F5】或運行按鈕(圖5)即可運行。注意應(yīng)先運行pPageSetup()，再運行pColumnMerge()。

頁面和格式設(shè)置程序執(zhí)行完成后，可通過【打印預(yù)覽】觀看效果。目次頁和欄目合并結(jié)果，可直接瀏覽。

3.2.3 注意事項

(1)操作前，應(yīng)將原始附表進(jìn)行備份。

(2)程序執(zhí)行前，應(yīng)在3.1節(jié)程序中修改制表人、檢查人和審核人及日期。若各附表的責(zé)任人不同，應(yīng)在排版后修改。修改方法如下：

在圖6【頁面布局】選項卡【頁面設(shè)置】功能區(qū)中單擊【打印標(biāo)題】，顯示圖7【頁面設(shè)置】對話框，單擊【頁眉/頁腳】/【頁腳】，可進(jìn)入修改。

圖6 【打印標(biāo)題】功能組

圖7 【頁面設(shè)置】對話框

(3)頁面參數(shù)若需要調(diào)整，用戶可在3.1節(jié)程序中進(jìn)行修改，或在圖7【頁邊距】中進(jìn)行修改。

(4)【封面】【題名頁】【目次頁】應(yīng)置于工作簿開始位置(圖4)，附表的順序應(yīng)根據(jù)規(guī)范的規(guī)定進(jìn)行排列?！灸看雾摗繕?biāo)簽名稱不可以更改，其他標(biāo)簽名稱可由用戶自由命名。

(5)若排版后表格較窄，用戶可人工進(jìn)行調(diào)整，包括調(diào)整左、右頁邊距，以達(dá)到美觀的效果。

(6)將pCellMerge()程序拷貝到其他工作表模塊代碼窗口中，對關(guān)鍵字欄目列號作相應(yīng)修改(或增減)，即可實現(xiàn)對其他附表關(guān)鍵字欄目的合并操作。

4 結(jié)語

該程序在多個儲量地質(zhì)報告中進(jìn)行了成功應(yīng)用，實現(xiàn)了快速排版和版式的規(guī)范、統(tǒng)一。希望該程序能給地質(zhì)同仁編制地質(zhì)報告附表提供預(yù)期的幫助。

[1] 伍遠(yuǎn)高.Excel VBA開發(fā)技術(shù)大全[M].北京：清華大學(xué)出版社，2009.

[2] 韓家國等編著.Excel VBA從入門到精通[M].北京：化學(xué)工業(yè)出版社，2010.

[3] 伍遠(yuǎn)高等編著.Excel VBA編程實戰(zhàn)寶典[M].北京：清華大學(xué)出版社，2014.

[4] 馮宇,蔣向東,于立紅主編.Excel 2010電子表格制作項目教程[M]. 上海：上?？茖W(xué)普及出版社,2015.

[5] Excel Home編著.Excel 2013應(yīng)用大全[M]. 北京：人民郵電出版社,2015.

[6] Excel Home編著.Excel 2010 VBA實戰(zhàn)技巧精粹[M].北京：人民郵電出版社，2015.

[7] 劉玉紅,王攀登.Excel 2016高效辦公[M]. 北京：清華大學(xué)出版社,2017.

[8] 固體礦產(chǎn)勘查報告格式規(guī)定(DZ/T 0131-1994)[S].

[9] 固體礦產(chǎn)勘查地質(zhì)資料綜合整理綜合研究技術(shù)要求(DZ/T 0079-2015)[S].

[10] 固體礦產(chǎn)勘查工作規(guī)范(GB/T 33444-2016)[S].