1、文章編號:1672-8262(200806-137-04中圖分類號:P209文獻標識碼:B 基于Excel 的測量數據格式轉換方法譙生有13,鄒文雄2,趙立學23收稿日期:20080503作者簡介:譙生有(1975,男,高級工程師,主要從事精密工程測量技術研究與管理。(11中鐵一局集團第五工程有限公司,陜西寶雞721006;21濟南市歷下區城市建設綜合開發公司,山東濟南250013摘要:以常用辦公軟件Excel 電子表格為工具,提出了基于Excel 電子表格轉換數據格式的整列變換法、行號取余篩選法、序列置換公式法,通過對S V300數據格式與CASS 數據格式的相互轉換,介紹了應用Excel

2、對文本數據格式進行轉換的基本步驟和方法。關鍵詞:Excel;測量數據;格式轉換1引言隨著計算機應用的普及和測繪作業自動化程度的不斷提高,測量數據普遍應用測繪軟件進行計算和處理,而測繪軟件門類繁多,不同軟件存儲數據的格式各異,在實際工作中,經常會遇到不同測量軟件之間的數據交叉使用的情況,由于軟件之間存儲數據的具體格式不同,必須要對數據格式進行轉換,使之成為相應軟件能夠識別和調用的數據格式,才能實現不同軟件之間的數據交叉使用。一般而言,測量數據都以純文本形式存儲,因此,測量軟件存儲的數據文件可以通過W indo ws 的一個附件“記事本”來打開進行編輯。不同數據格式的轉換實質就是對相應的文本型數據

3、格式進行編輯和修改,而文本的編輯只有選中、剪貼、復制、粘貼、查找、替換等幾個非常較簡單的工具,很難靠這幾個簡單的編輯命令對文本數據格式進行批量轉換。由于Excel 電子表格可以直接導入和導出文本數據,因此,應用Excel 豐富的內部函數和靈活的數據處理功能可以實現文本型數據格式的快速、批量轉換。2測量數據的一般格式就一般情況來說,測量數據主要有兩種格式。一種為橫向陣列數據,一種為縱向陣列數據1。211橫向陣列數據數據文件為一個矩陣,數據之間用逗號分隔,固定列數的一行數據為一個數據單元,該單元包含了某個元素的若干列綜合數據信息。例如S V300成圖測繪軟件的數據格式就是橫向陣列數據,格式如下:點

4、號,點名,代碼,Y 坐標,X 坐標,高程點號,點名,代碼,Y 坐標,X 坐標,高程212縱向陣列數據數據文件為一列,數據之間以行分隔,固定行數的數據組成一個數據單元,該單元包含了某個元素的綜合數據信息。例如CASS 成圖測繪軟件的數據格式就是縱向陣列數據,格式如下:總點數點名代碼Y 坐標X 坐標高程3將數據文件導入Excel將數據文件導入Excel 有3種方式,第一種是先打開Excel,在Excel 中通過“導入外部數據”的方式打開數據文件,第二種是通過選擇文件的打開方式將數據文件導入Excel,第三種是利用Excel “打開”菜單導入數據文件,3種方式各有特點,可以根據情況選擇使用。311利

5、用Excel “導入外部數據”導入數據文件先打開Excel,新建一個工作簿,如新建一個工作簿Book1,單擊“數據”下拉菜單,將鼠標移至“導入外部數據”,單擊“導入數據”或在鍵盤上輸入字母“D ”,打開“選取數據源”對話框,將文件類型選為“所有文件”,在“查找范圍”提示框內找到數據文件,雙擊數據文件,進入“文本導入向導-3步驟之1”對話框,原始數據類型選“分隔符號”,單擊“下一步”進入“文本導入向導-3步驟之2”對話框,分隔符號勾選“Tab鍵”、“分號”、“逗號”、“空格”,如果不清楚數據文件的具體分隔符號,可以把這幾個符號都勾選上,單擊“下一步”進入“文本導入向導-3步驟之3”對話框,列數據

6、格式選“常規”,單擊“完成”,進入“導入數據”對話框,數據的放置位置選“現有工作表”,單擊“確定”,數據文件就成功導入到工作簿Book1,各行數據按照分隔符分隔成各列。312利用Excel“打開”菜單導入數據文件先打開Excel,新建一個工作簿,如新建一個工作簿Book1,單擊“文件”下拉菜單,再單擊“打開”選項,彈出打開對話框,選擇文件類型選為“所有文件”,找到數據文件后雙擊該文件的圖標,進入“文本導入向導-3步驟之1”對話框,如前所述根據提示依次完成數據文件的導入。313利用數據文件的打開方式導入數據文件選中數據文件后單擊右鍵,將鼠標移至“打開方式”,單擊Excel圖標,數據文件就導入到E

7、xcel;對于文件類型為非文本的數據文件,直接雙擊數據文件,出現一個W indows對話框,選“從列表中選擇程序”,單擊“確定”,出現“打開方式”對話框,直接單擊Excel圖標(或從列表中選擇并單擊Excel圖標,數據文件就導入到Excel,默認工作簿名稱與數據文件的文件名相同。該方法導入數據的優點是簡單快捷,缺點是數據導入到Excel后,每行數據為一列,不能根據分隔符自動隔開成各列。這種方式適合單列數據的導入,對于多列數據,數據導入后不便于應用Excel的特點進行對數據進行編輯。4整列變換法進行數據格式轉換W indows“記事本”無法對數據文件的整列進行編輯,將數據文件導入Excel后,每

8、行數據的列字段分別對應于Excel的不同列,應用Excel的“插入”、“刪除”、“復制”、“剪貼”、“粘貼”等命令,使得對整行整列數據的編輯變得簡單快捷。例如,在GPS測量中,控制點坐標信息數據通常為橫向陣列數據,格式如下:點名,X坐標,Y坐標,高程,緯度,經度點名,X坐標,Y坐標,高程,緯度,經度將上述數據格式與S V300成圖測繪軟件數據格式進行對比,要將該坐標信息數據轉換為S V300成圖測繪軟件的數據格式,應在“點名”列前追加“點號”列,在“點名”列后追加“代碼”列,對坐標信息數據的“X坐標”列和“Y坐標”列進行交換,最后刪除“緯度”、“經度”列。以將GPS控制點坐標信息數據轉換為S

9、V300成圖測繪軟件數據格式為例,介紹整列變換法的轉換步驟和應用。411將數據文件導入到Excel 數據導入Excel后格式如圖1所示。圖1數據文件導入到Excel412對數據列進行變換如圖1,在A列(點名前插入一個空列作為點號列,插入空列后,X坐標列變為C列;然后在C列前插入一個空列作為代碼列,X坐標列變為D列;將D列(X坐標“剪貼”到C列(作為代碼列的空列,然后將E列數據(Y坐標“復制”到D列,最后將臨時存放在C列的X坐標“剪貼”到E列,刪除G列(緯度和H 列(經度。413填充點號用拖動鼠標自動填充的方法在空列A中填入點號,假設點號為1001、1002,經過對Excel各列的一 系列操作,

10、就完成了數據格式的編輯,如圖2所示。圖2轉換成S V300數據格式414保存數據文件用“另存為”方式保存數據文件,將“保存類型”選為“CS V(逗號分隔”,文件擴展名改為.txt。5行號取余篩選法進行數據格式的轉換511行號取余篩選法進行數據格式轉換的方法有些數據格式的轉換表現為對有某種倍數規律的特定數據行進行編輯,在Excel中先將每個數據行用行號標識出來,然后用Excel 的余函數計算結果表示出這種倍數規律,最后應用Excel 的篩選功能實現對特定行的編輯,本文稱這種方法為行號取余篩選法。通過將縱向陣列數據轉換為橫向陣列數據來介紹行號取余篩選法進行數據格式轉換的方法和技巧。512縱向陣列數

11、據轉換為橫向陣列數據 以CASS 數據格式轉換為S V300數據格式為例,說明行號取余篩選法轉換數據格式的步驟。(1將數據文件導入到Excel第一行數據為總點數,從第二行開始為數據單元。(2為數據單元填充行號在A 列前插入一個空列作為數據單元的行號列,在A2單元格填入行號1,A3單元格填入行號2,同時選中A2、A3單元格,將鼠標移動至A3單元格右下角,當鼠標指針變為實心十字時,按下鼠標左鍵向下拖拉至最后一行,每行數據的行號將自動填充在A 列中。(3行號取余將C 列作為余數列,在C2單元格中輸入公式“=MOD (A2,5”,按回車鍵,將鼠標移動至C2單元格右下角,當鼠標指針變為實心十字時,按下鼠

12、標左鍵向下拖拉至最后一行,即將A 列中相應數據行的行號取5的余數,將余數存放在相應的C 列中。由于本例中,一個數據單元為5行,因此除數為5,假設一個數據單元為n 行,那么在C2列中應輸入公式“=MOD (A2,n ”。如圖3所示。 圖3行號取余(4篩選點名、代碼、Y 坐標、X 坐標、高程從圖3可以看出,所有點名對應的行號被5取余數后,余數均為1,代碼對應的余數均為2,Y 坐標對應的余數均為3,X 坐標對應的余數均為4,高程對應的余數均為0,通過自動篩選功能可以非常方便地把各項數據分別顯示出來。同時選中A 、B 、C 三列,單擊“數據”下拉菜單進行自動篩選,單擊C1單元格的三角符號,選顯示行值等

13、于1,則B 列中只顯示所有的點名,如圖4,全選B 列,將B 列數據“復制”到Sheet2工作簿中的B 列,單擊C1單元格的三角符號,選顯示行值等于2,則B 列中只顯示所有點的代碼,將B 列數據復制代“復制”到Sheet2工作簿中的C 列,依次篩選Y 坐標、X 坐標、高程,并將篩選結果分別“復制”到Sheet2工作簿中的D 列、E 列、F 列。圖4點名篩選(5完成數據轉換保存數據文件刪除Sheet2工作簿中的第一行,用自動填充的方法在A 列填充點號,本例代碼項為空,格式如圖2。單擊“文件”下拉菜單,用“另存為”方式將Sheet2工作簿保存為文本文件。行號取余篩選法與需要轉換的數據元素本身無關,彌

14、補了文獻1需要使轉換元素本身滿足一定的邏輯關系才能進行篩選的缺點。6序列置換公式法進行數據格式的轉換611序列置換公式法進行數據格式轉換的原理在Excel 中轉換數據格式時,兩種格式的數據在Excel 單元格中的對應關系可以用一個序列來表示,應用Excel 自動填充功能產生這種序列,然后把序列置換為Excel 公式實現數據格式的轉換,本文稱這種方法為序列置換公式法。通過將橫向陣列數據轉換為縱向陣列數據來介紹行號取余篩選法進行數據格式轉換的方法和技巧。612橫向陣列數據轉換為縱向陣列數據以S V300數據格式轉換為CASS 數據格式為例,說明序列置換公式法轉換數據格式的步驟。(1將數據文件導入到

15、Excel導入數據后格式同圖2。(2統計總點數nCASS 數據比S V300數據多一個總點數,可通過查看數據末尾行的行號進行統計。(3刪除點號列CASS 數據比S V300數據少一個點號列,在Excel中導入數據后直接刪除點號列,點號列是數據表中的第一列。(4歸納CASS 數據與S V300數據在Excel 表格中的對應規律 刪除點號列后,將CASS 數據與S V300數據放在一個表格中,如圖5,A 、B 、C 、D 、E 列為S V300數據,G 列為CASS 數據,用空列F 列將兩種數據隔開,可以看出G1單元格的數據等于A1單元格,G2單元格的數據等于B1單元格,G5單元格的數據等于E1單

16、元格,G6單元格的數據等于A2單元格,以此類推得到如下對應規律:G1=A1、G2=B1、G3=C1、G4=D1、G5=E1,G6=A2、G7=B2、G8=C2、G9=D2、G10=E2,G11=A3、G12=B3、G13=C3、G14=D3、G15=E3, 圖5CASS 數據與S V300數據對照(5填充表達對應規律的序列如果直接在G 列單元格用拖拉鼠標自動填充公式,則G11單元格得到的數據是A11,G12得到的數據是B11不能得到正確的轉換結果,但是可以用自動填充獲得滿足上述對應規律的序列。首先在G1單元格輸入“:=A1”,G2單元格輸入“:=B1”,G3單元格輸入“:=C1”,G4單元格輸

17、入“:=D1”,G5單元格輸入“:=E1”,為將序列文本區別于公式表達式,在“=”前鍵入“:”,然后選中G1至G5單元格,鼠標移至G5單元格右下方,按住實心十字向下拖拉鼠標產生序列,如圖6。本例中一個點的CASS 數據占5行,如果總點數為n,則填充序列至第“5n ”行。圖6產生對應規律的序列(6將序列置換為公式利用查找與替換命令將全部“:=”替換為“=”,就將A 、B 、C 、D 、E 列S V300數據轉換為G 列的C ASS 數據。(7完成數據轉換保存數據文件將G 列數據復制到sheet2工作簿,由于本例代碼為空,故轉換后的值為0,如果要讓代碼行保持為空,可利用行號取余篩選法將代碼行篩選出

18、來然后刪除單元格中的“0”即可。在第一行前插入空行,然后在A1單元格填入總點數n,將sheet2工作簿保存為文本格式文件。7結語整列變換法可以對某個整列數據進行編輯,行號取余篩選法可以對行號滿足某一函數規律的行進行編輯,序列置換公式法可以對滿足某種映射規律的單元格進行運算,將這3種方法進行延伸或混合使用,可以實現各種測量數據格式的復雜轉換,豐富了編輯文本數據格式的思路,為Excel 電子表格在測量數據處理中發揮更大的作用提供了借鑒。參考文獻1何征坤.應用Excel 批量處理測量數據.城市勘測,2006(4:P49Survey Dat a For mat Conversi on M ethod Based on ExcelQ iao ShengYou 1,Z ou W enXi ong 2,Zhao L i Xue2(11The Fifth Engineering Co .,L td .Of China Rail w ay First Gr oup,Baoji 7210