基于Excel的测量数据格式转换方法 - 图文-

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