本帖最后由 faliming7 于 2014-4-11 20:18 编辑
一、 数字不能计算 先来看一个现象。 A1:A6单元格分别录入8元、9元、5元、4元、3元、2元。 B1单元格输入公式“=LEFT(A1)”并向下复制公式至B6单元格。 B7单元格输入公式“=SUM(B1:B6)”，结果怎么会是“0”？ 图一 这么简单的数字，口算都能算出来，EXCEL竟然会算错？ 二、 数字的两种类型 实际上在EXCEL中，数字有两种类型。就象是双胞胎，长一个样子，但不是一个人。一个是文本，喜欢语文，一个是数值，喜欢数学。喜欢数学的才能正常计算。 双胞胎不好识别，EXCEL怕我们搞混了，会给点提示。 比如文本型数字自然左对齐，数值型数字自然右对齐。 图二 当我们把公式内容固定下来，变成值的时候（复制――选择性粘贴――值），EXCEL又会出现提示，单元格左上角会出现一个绿色小三角（错误指示器），选中单元格，旁边会出现一个感叹号，点击这个提示，第一条就是“以文本形式存储的数字”。 图三 有的人有洁癖，不喜欢这些“碍眼”的小玩意，会手动选择“忽略错误”。 图四 每一次出现提示，都手动“忽略错误”毕竟麻烦，有人干脆取消“错误检查”（文件――选项――公式――允许后台错误检查）。 图五 可是这么一来，双胞胎就没那么好识别了。 如果手动选择了“忽略错误”又后悔了，可以选择“重置”（文件――选项――公式――重新设置忽略错误） 图六 有人可能发现，自己也出现类似不能计算的现象。但并不是如上述所说的公式结果，而是自己手动录入的数字。 不说EXCEL会自动识别录入的数字为数值吗，为什么还会不能计算呢？ 那是因为单元格已经被提前设置成了“文本”，只是你没意识到而已。 图七 这有可能是别人设置的，也可能是自己设置的又忘记了，最可能的就是自己设置过文本，但又将原数据删除了。 三、 数字类型与格式 也就是说，提前设置文本可以让EXCEL将数字识别为文本。 对于双胞胎，不光我们会搞混，EXCEL也怕搞混，它时刻在提醒我们，所以我们在录入的时候也要时刻提醒它。提前设置文本（设置单元格格式――数字――文本）就是告诉EXCEL，下面我要录入的数字是弟弟――“文本”。 这种提前告之，也可以简化程序，录入时在数字的前面加一个单引号。 图八 但是，当我们已经录入完毕，再设置为文本时，情况会有所不同。 后设置为文本时，EXCEL依然可以计算这些数字，这是为什么呢？ 图九 因为提前“设置文本”可以理解为“定义文本”（单元格数字格式显示的依然是“常规”），这是告诉EXCEL，出生的会是谁，是弟弟文本，还是哥哥数值。 图十 而之后“设置文本”只是“设置格式”。对于格式，我们可以理解为“衣服”。 哥哥数值已经出生了，这时候你再给它穿件“文本格式”的“外衣”，不会影响其本来属性，是谁还是谁，喜欢数学的，不会穿件文艺青年的外衣就喜欢语文。 所以千万不要想通过事后设置文本格式来改变数字属性。 四、 文本转换为数值 那么已经“定义”成文本的数字如何才能快速转换为数值呢？ 1、 通过智能指示器转换 当数字以文本形式出现时，EXCEL不仅会给出提示，也会随提示提供快速修改机会。选择带有绿色小三角的单元格，点击旁边出现的按钮，选择“转换为数字”。 图十一 2、 选择性粘贴 如果已经在错误指示器中忽略了错误，那么绿色小三角提示就不会出现。这时也没必要到“选项”里面去“重新设置忽略错误”。只要选择任意一个空单元格，复制（CTRL+C），再选择需要转换为数值的区域，“选择性粘贴――运算――加（减）”即可。 图十二 3、 数据分列 如果工作表内容不便于选择空单元格复制，可以选择需要转换为数值的列，“数据――分列――下一步――下一步”。但分列每次只能针对一列操作。 图十三 五、 数值转换为文本 相对于文本转数值的多样性方法，数值转文本只能用“数据――分列――下一步――下一步――文本”。当然这里所谈论的技巧都不包括函数方法。 图十四 六、 身份证号码的录入 正因为EXCEL会自动识别录入的数字为数值，而它所能识别的数值长度最长只能达到15位。16位以上的数字，已经超出它的“脑容量”，EXCEL无法记录。因此我们如果在单元格中直接录入18位的身份证号码，后三位会不予记录，显示为0。 图十五 所以录入身份证号码这样的长数字时，应该提前通知EXCEL。提前“设置单元格为文本”或在录入前输入一个单引号。 图十六 我经常在想，既然EXCEL可以自动识别录入的数字为数值，那能不能再智能一点，当录入超过15位的数字后，自动识别为文本呢？ 经常会有人提问，我提前没有设置为文本，也没输入单引号，录入了半天才发现，身份证号码后面三位都变成0了，怎么找回尾巴来。 我们只能遗憾地告诉他“吸取教训吧”。因为EXCEL根本没记住那三位，你让她上哪找？ 也有人说，我提前没设置成文本，录入完毕发现错了，我改还不行吗？我把单元格格式设置成“文本”。 在本文第三部分已经说明了，之后的设置与之前设置是两个概念。之后再设置成“文本”，只相当于换件外衣，EXCEL没记住的尾巴依然不会出现。 七、 身份证号码的替换 即使对以上知识已经相当熟悉，也只能确保自己不出错。 我们有可能看到别人不标准的方式，想改也没那么容易。 1、查找替换法 如A1:A6单元格，原内容在身份证号前面录入了一个“号”字。我们想除掉这个多余的字，首先想到的可能会是“查找――替换”。 可是查找“号”，替换留空时，发现身份证号后三位全都变成了0。即使单元格格式提前设置为“文本”，也无济于事。 图十七 实际上，这里查找“号”，替换栏要输入一个单引号才行。 图十八 2、内容重排法 3、项目符号法 如果是从别的软件导过来的身份证号码，比如从WORD中粘贴一列身份证号码，同样会遇到尾巴变0的情况。即使提前将单元格格式设置为文本，也依然解决不了问题。 可以在WORD中选择身份证号码列，点工具栏的“项目符号”，批量添加一个符号。 图十九 复制后粘贴入EXCEL，发现WORD中的项目符号在EXCEL中显示不同，不去管他。 复制一个这样的“符号”，查找替换，查找栏CTRL+V（粘贴），替换栏输入一个单引号，全部替换即可。 图二十 替换后可能会发现如图所示的奇怪字符，那是因为字体格式的原因，只要将字体改为“宋体”即可。 图二十一
如果看不到图就看不明白的话，请移步博客
| 
IP卡
狗仔卡
发表于 2014-4-9 16:59
提升卡
置顶卡
沉默卡
喧嚣卡
变色卡
抢沙发
千斤顶
显身卡
发表于 2014-4-9 17:37
发表于 2014-4-9 17:59
