Excel中数值与字符混合型数据的自动运算实现

朱宁贤

（山东政法学院　济南　250014）



Excel中数值与字符混合型数据的自动运算实现

朱宁贤

（山东政法学院济南250014）

摘要Excel在处理数据方面能力很强大，但它也有一套规则和标准规范，即一个单元格只有一个属性。学生在学习excel时，只是掌握了利用函数和公式的自动运算功能，但如果函数与公式中的数据带有单位，excel就会改变数据的本质属性，从而导致运算的失败。很多学生对此感到困惑不解。尤其是带有不同长度的单位时，更是给数据的处理带来较大的困难。如何才能解决带用单位的excel数据运算呢？本文结合教学中积累的经验，通过设置通用格式设置、显示格式设置、函数转换数据、函数提取数据等方式，从不同的途径探讨了固定长度单位、任意单位的数据运算方法，解决人们实际生活中经常遇到的实际问题。

关键词Excel运算混合数据函数变换

一、问题背景

Excel具有强大的数据处理功能，广泛应用在金融、办公等领域。人们经常利用它的公式和函数进行数据自动运算，大大提高了办公效率。在Excel教学中，学生只是掌握了利用函数和公式的自动运算功能，但如果要处理的数据带有单位，Excel就会出现“#VALUE”错误提示，如图1所示，学生对此困惑不解。结合多年的教学和生活中常遇到的问题，我们探讨了产生此问题的原因：Excel的“一格一属性”的规则，即一格单元格中只能有一种数据类型，如果数据和字符等不同数据混合在一起时，Excel自动将数据类型转换为字符型，而字符型数据在运用公式和函数计算时，就必然出现错误提示。这就是带有单位的Excel数据不能进行运算的原因。如何才能解决这一问题？

按照多年的教学经验，并结合日常中常遇到的应用情景，我们按照使用单位的不同，将数据分成长度固定且内容相同、长度固定但内容不同、长度和内容均不相同等情况，采用不同的解决策略，实现各种情况下的带有单位的Excel运算问题。

二、长度固定且内容相同时

解决方法：采用通用格式方法。在需要运算的单元格单击鼠标右键，选择“设置单元格属性”，在弹出的对话框中，选择“数据”选项卡，找到其中的“G/通用格式”，直接输入“元”（注意：输入时不要带用引号），然后确定。关闭“设置单元格属性”对话框返回Excel主程序后，再输入单价的数值，系统会自动带上单位，如图3所示，但其数据类型依然为数值型（右对齐），所以可以直接计算结果。采用这种自定义格式，只不过是改变了单元格的显示形式，而没有改变单元格存储的内容。

解决方法2：采用left（）字符函数进行截取。对于图1所示的内容，将两个单元格直接相乘修改为函数“=LEFT （A2，LEN（A1）-1）*B1”，如果想显示出单位来，可以采用通用格式来显示（参照2.1方法），如图4所示。

三、长度固定但内容不同时

利用2.1的方法2，还有一个好处，即可以处理不同内容单位的数据运算，如图5所示，只需要对B2也采取函数截取即可以。

如果单位内容存在不同的数据时，解决方法是函数转换的同时进行单位转换。

在表格当中有两个单位，分别是元、角，如何统一还原位数并且计算？带单位的数字是没法进行数据运算的，只能将其进行拆分后，即由一列改为数字与单位两列，这种情况下，首先要对单位，数字进行统一，比如单位中有元，也有角还有分时，就要按一个单位进行统一，比如均按元进行统一。同时在单位进行统一时，数字部分也要伴随转换。如原2角需转换成0.2元，这样转换后，就可以进行数字部分的运算，同时在运算结果后再将单位添加。当然在实际应用中，最好还是使用相同的单位才好。

四、长度和内容均不相同时

1、数据有固定的分隔特征时

解决方法时：采用分列处理。如果数据和字符之间存在有固定的分隔符，可以采用数据列分隔的方法。如图7可以采用引入列的方式，首先使excel表符合一格一属性的要求，然后在进行计算。

方式是先将数量列分解为数值和单位列，操作步骤为“数据”|“分列”，选择“空格”，将原有表分割为两列，如图8所示，然后就可以进行计算，如图9。

2、没有固定的分隔标志

解决方法：采用lookup（）函数处理。但如果没有固定的分隔符，只能采用函数lookup（）来处理，将其中的数据读取出来，如图所示。公式含义说明：分别提取数量单元格的左边1位、2位、3位……直到1000位，组成一个包含1000个元素的内存数组。再用“--”将其转换成数值，最后通过LOOKUP函数从这个数组中提取最大值，即目标数量。该值乘以单价即金额。

利用lookup函数，不仅能够处理字符在右侧的数据，而且结合right（）、mid（）函数，也可以处理字符在左侧或者字符在两侧的函数。

通过上述方法，可以将带有任何不同单位的数据进行运算处理，尤其是最后一种方法，可以在不用对已有数据表进行额外处理，仅借助于lookup（）函数，就可以进行直接的运算处理，大大增强了excel的数据处理能力。

注：上述解决方法在win8+office2013环境中实现通过。

参考文献

［1］文杰书院.excel2010公式函数图表与数据分析［M］.清华大学出版社.2013.03.

［2］IT部落窝.Excel提取文本和数字荟萃［OL］. http: //www.ittribalwo.com/article/909.html.

Operatedautom Atically of Character and Number's Data Type in Excel

Zhu Ningxian
（Shandong University of Political Science and Law 250014）

AbstractExcel hasadvanced capabilities to process data, but it also has a set of rules and standards, known as a cell is only one attribute.When students learn Excel,they only study some automatic operation of functions and formulas. If there are some units in them, excel will change the essential attribute of data, which leads to the failure of the operation.Many students feel confused about this.Especially when the units have different length, it is more difficult to deal with it. How to solve the data with units in Excel?Based on the author's teaching experience, this paperhas four settings for data, i.e.general format、Display format、convertedand extracted function, discuss the way to deal with the fixed and unfixed length dataunit, solve practical problems that people often meet in their life.

KeywordsExceloperate Mixeddat a Functiontransform

中图分类号TP317.3

文献标识码B

文章编号160314-7226

作者简介

朱宁贤（1971～），男，山东政法学院副教授，计算机应用硕士；主要研究方向：数据库原理与多媒体应用。