Excel应用技巧.doc

EXCEL应用技巧u编辑与操作3l如何在Excel中快速输入大写中文数字3l如何在Excel中实现多个工作表的页眉和页脚同时设置3l如何在Excel中实现单元格文字随时换行3l如何在Excel中快速插入空白行3l如何在Excel中消除0值3l如何在Excel中批量转换日期格式3l如何在Excel中快速选定“空白”和“数据”单元格4l如何在Excel中防止误改4l如何在Excel中快速隐藏4l如何在Excel中用条件格式为单元格自动加边框4l如何在Excel中隔行调整行高5l如何在Excel中只选中包含文本的单元格8l如何在Excel中巧用右键拖移实现快速复制10l如何利用宏给Excel工作簿文档自动添加密码14l如何用单元格数据作为Excel工作簿名称15l如何在Excel中实现多条件求和18l如何让隐藏的Excel工作表别人无法取消隐藏22l如何快速找到所需要的Excel函数25l如何利用Excel记录单快速录入表格数据27l如何对Excel作数据校验27l如何在Excel单元格中多行与一行并存29l如何在同一Excel单元格中混用文本与数值数据31l如何急救受损的Excel文件32lExcel公式应用常见错误及处理34n#DIV/0! 错误34n#N/A 错误35n#NAME?错误37n#NUM! 错误39n#VALUE错误39n#REF!错误41n#NULL!错误42l如何实现Excel表格数据行列巧互换43uExcel鲜为人知的秘技44l建立分类下拉列表填充项44l建立“常用文档”新菜单44l让不同类型数据用不同颜色显示45l制作“专业符号”工具栏46l用“视面管理器”保存多个打印页面47l让数据按需排列47l把数据彻底隐藏起来48l让中、英文输入法智能化地出现48l让“自动更正”输入统一的文本48l在Excel中自定义函数49l表头下面衬张图片49l用连字符“&”来合并文本49l快速打印学生成绩条50l同时查看不同工作表中多个单元格内的数据50l为单元格快速画边框51l控制特定单元格输入文本的长度51l成组填充多张表格的固定单元格52l改变文本的大小写53l提取字符串中的特定字符54l把基数词转换成序数词54l用特殊符号补齐位数55l创建文本直方图56l计算单元格中的总字数57l关于欧元的转换57l给表格做个超级搜索引擎58lExcel工作表大纲的建立60l插入“图示”61l熟悉Excel的“从文本到语音”62lExcel中“摄影”功能的妙用64l在多张表格间实现公用数据的链接和引用65l“驯服”Excel的剪贴板窗口66l利用公式审核工具查看数据出处66l巧用Excel 的“智能鼠标”67lExcel “监视”窗口的应用67在使用EXCEL的过程中，我们通常会遇到一些小问题，看似简单，一时半会儿又研究不通解决不了，很让人头痛。现在信息系统部从编辑与操作、宏和公式、函数等几个方面给大家整理了一些处理方法，带大家找找解决这些问题的思路。u 编辑与操作l 如何在Excel中快速输入大写中文数字将光标移至需要输入大写数字的单元格中。利用数字小键盘在单元格中输入相应的小写数字(如12345)。右击该单元格，点击“设置单元格格式”，从弹出的“单元格格式”对话框中选择“数字”选项；然后从“类型”列表框中选择“中文大写数字”选项。最后单击“确定”按钮，这时输入的12345就自动变成“壹万贰仟叁佰肆拾伍”。l 如何在Excel中实现多个工作表的页眉和页脚同时设置我们有时要把一个Excel文件中的多个工作表设置成同样的页眉和页脚，分别对一张张工作表去设置感觉很烦琐。如果用下面的方法就可以一次将多个工作表中的页眉和页脚同时设置好：把鼠标移到工作表的名称处(如果没有给每张表取名的话，Excel自动设置的名称就是Sheet1、Sheet2、Sheet3等等)，然后点右键，在弹出的菜单中选择“选择全部工作表”的菜单项，这时再进行页眉和页脚设置就是针对全部工作表了。l 如何在Excel中实现单元格文字随时换行在Excel中，我们有时需要在一个单元格中分成几行显示文字等内容。那么实现的方法一般是通过选中格式菜单中的“单元格”下“对齐”的“自动换行”复选项，单击“确定”即可，这种方法使用起来不是特别随心所欲，需要一步步地操作。还有一种方法是：当你需要重起一行输入内容的时候，只要按住Alt键的同时按下回车键就可以了，这种方法又快又方便。l 如何在Excel中快速插入空白行如果想在某一行上面插入几行空白行，可以用鼠标拖动自此行开始选择相应的行数，然后单击右键，选择插入。如果在每一行上面均插入一空白行，按住Ctrl键，依次单击要插入新行的行标按钮，单击右键，选择插入即可。l 如何在Excel中消除0值有Excel中当单元格计算结果为0时，默认会显示0，这看起来显然有点碍眼。如果你想显示0时，显示为空白，可以试试下面的方法。打开“工具选项视图”，取消“0值”复选项前的，确定后，当前工作表中的值为0的单元格将全部显示成空白。l 如何在Excel中批量转换日期格式以前在Excel中输入职工出生时间时，为了简单都输入成“yymmdd”形式，但上级部门一律要求输入成“yyyy-mm-dd”格式，那么一千多名职工出生时间肯定不能每个手工转化。最快速的方法是：先选定要转化的区域。点击“数据分列”，出现“文本分列向导”对话框。勾选“固定宽度”，连续两次点击“下一步”按钮，在步骤三对话框的“列数据格式”中，选择“日期”，并选定“YMD”形式，按下“完成”按钮，以前的文本即转化成了需要的日期了。l 如何在Excel中快速选定“空白”和“数据”单元格在Excel中，经常要选定空白单元格，逐个选定比较麻烦，如果使用下面的方法就方便多了：打开“编辑定位”，在“定位”窗口中，按下“定位条件”按钮；选择“定位条件”中的“空值”，再按“确定”，空白单元格即被全部选定。如果要选定只含数据的单元格，在上面方法的“定位条件”窗口中，选择“常量”，再点“确定”，含有数据的单元格全部选定。l 如何在Excel中防止误改在包含多个工作表的工作薄中，为了防止误修改，我们常常采取将行(列)隐藏或者设置编辑区域的方法，但是如果要防止整个工作表的误修改怎么办呢?单击“格式工作表隐藏”，将当前的工作表隐藏，这样操作者连表格都看不到，误操作就无从谈起了。要重新显示该表格，只须单击“格式工作表取消隐藏”。要注意的是：如果设置了工作表保护，则不能进行隐藏操作。l 如何在Excel中快速隐藏在打印工作表时，我们有时需要把某些行或者列隐藏起来，可是用菜单命令或调整行号(列标)分界线的方法比较麻烦，介绍一个简单方法：在英文状态下，按“Ctrl+9”或“Ctrl+0”组合键，就可以快速隐藏光标所在的行或列。l 如何在Excel中用条件格式为单元格自动加边框Excel有许多“自动”的功能，如能合理使用，便会效率倍增。经过试验，本人找到一种利用条件格式为Excel单元格自动添加边框的方法，可谓“所键之处，行即成表”。下面是具体的步骤：1.在首行中选择要显示框线的区域，如本例中的A1:D1。2.执行“格式”“条件格式”，打开“条件格式”对话框。单击打开“条件1”下拉列表，单击选择“公式”，在随后的框中输入下面的公式“=OR($A1,$B1,$C1,$D1)”，意即只要A1、B1、C1或D1中有一个单元格内存在数据，将自动给这四个单元格添加外框线。注意：公式中的单元格引用为混合引用，如改为相对引用则效果不同，朋友们可以一试。3.单击“格式”按钮，打开“单元格格式”对话框，切换到“边框”选项卡，为符合条件的单元格指定外边框。如图1。4.选择A1:D1区域，复制，选择AD列，粘贴(如果A1:D1中已输入数据，可执行“编辑”“选择性粘贴”“格式”)，这样就把第2步中设置的格式赋予了AD列的所有单元格。如图2。在这四列中任意一个单元格中输入数据(包括空格)，此行(AD列)各单元格将会自动添加框线。如图3。提示：对于已经显示条件格式所设置框线的单元格，仍然允许在“单元格格式”对话框的“边框”选项卡中设置其框线，但外边框不能显示(使用工具栏上的“边框”按钮进行设置也是如此，除非删除条件区域内的全部数据)，只能显示斜线。l 如何在Excel中隔行调整行高要求把一份Excel表格的偶数行行高调整一下。这份表格可是有上百行的，逐一调整行高显然是不科学的。如下方法可实现：一、直接定位法先在表格的最后增加一个辅助列。在该列的第一行的单元格中输入数字“1”，然后在第二行的单元格中输入公式“=1/0”，回车后会得到一个“#DIV/0!”的错误提示。现在选中这两个单元格，将鼠标定位于选区右下角的填充句柄，按下鼠标右键，向下拖动至最后一行。松开鼠标后，在弹出的快捷菜单中选择“复制单元格”的命令。好了，现在该列的奇数行均是数字1，而偶数行则都是“#DIV/0!”的错误提示了，如图1所示。点击菜单命令“编辑定位”，在打开的“定位”对话框中点击“定位条件”按钮，然后在打开的“定位条件”对话框中，选中“公式”单选项，并取消选择除“错误”以外的其它复选项，如图2所示。确定后，就可以看到，所有的错误提示单元格均处于被选中状态。现在我们所需要做的，只是点击菜单命令“格式行行高”，然后在打开的“行高”对话框中设置新的行高的值就可以了，如图3所示。行高调整完成后，记得将辅助列删除。二、筛选法也是先增加一个辅助列。然后在该列的第一个单元格输入数字“0”，第二行的单元中输入“1”。选中这两个单元格，然后按下右键后向下拖动填充句柄，并在弹出的快捷菜单中选择“复制单元格”命令。现在点击菜单命令“数据筛选自动筛选”。点击辅助列第一个单元格的下拉按钮，在列表中选择“1”，如图4所示。单击后，则可将数值为“1”的单元格筛选出来。选中该列所有数值为1的单元格，点击菜单命令“格式行行高”，设置需要的行高。最后，别忘了，再次点击菜单命令“数据筛选自动筛选”，取消“自动筛选”前的对勾，使全部数据正常显示出来。三、选择性粘贴法相比之下，这种方法说简单多了。先选中第二行，调整其行距至合适。然后点击左侧行号，选中第一行和第二行，点击“复制”按钮。然后点击左侧行号，选中其余各行。再点击菜单命令“编辑选择性粘贴”，打开“选择性粘贴”对话框。点击其中的“格式”单选项，如图5所示。这样，就可以得到需要的效果了。此法固然简单，但它只适合于各行的格式完全一致的情况。如果某行中有合并单元格或者行与行的格式并不完全一致，那么此法就不太好用了。四、格式刷法如果选择性粘贴法能用的话，那么，格式刷当然也能用。如同上法，先调整好第一行和第二行，选中它们，点击“格式刷”按钮。鼠标变成小刷子形状时，点击左侧行号至需要的选区。这样，就可以获得调整行高的目的。需要注意的是，各行的格式应该完全一致。此外，必须点击左侧行号选中整行操作，否则，行高是不会调整的。l 如何在Excel中只选中包含文本的单元格在一个Excel工作表中，通常会包含许多类型的数据，诸如文本、数值、货币、日期、百分比等等，而有时会需要从这些不同类型的数据中只选中某种类型的数据，例如文本，然后对其进行删除、填充、锁定或修改格式等操作。本文要介绍的就是如何在Excel中实现只选中包含文本的单元格。具体操作步骤如下。方法一：使用“定位条件”1.按F5键，或选择菜单命令“编辑|定位”(也可按快捷键Ctrl+G)，打开如图1所示的“定位”对话框。图12.在“定位”对话框中，单击“定位条件”按钮。3.在“定位条件”对话框中，选择“常量”，如图2所示，然后只选中“文本”复选框，选中后单击“确定”按钮即可。同理，如果要只选择工作表中的数字，也可以用上述同样的方法。图2方法二：使用条件格式使用“条件格式”可以一次性改变特定类型数据的格式，可以改变的格式包括字体样式、下划线、删除线和颜色。例如，我们意图将工作表中所有的文本都改为红色，通常的做法是先选中这些文本，然后再改变其颜色，而使用“条件格式”会很快完成这一操作。1.在工作表中选择包含数据的区域。2.选择菜单命令“格式|条件格式”。3.在“条件格式”对话框中，从“条件1”下方选择“公式”，然后在右方输入框中输入=Istext(A1)，如图3所示。图34.单击“条件格式”对话框中的“格式.”按钮，打开“单元格格式”对话框，然后将颜色设置为红色，如图4所示。图45.单击“确定”按钮回到“条件格式”对话框，然后再单击“确定”按钮，关闭对话框即可。使用这种方法，当以后再次向该区域中添加文本时，文本也会自动变为红色。l 如何在Excel中巧用右键拖移实现快速复制在Excel工作表中，我们经常会将一个单元格或区域中的数据复制到另一位置。实现复制Excel表格数据的方法有许多种，最基本的是采用“编辑”菜单或鼠标右键中的复制/粘贴命令，或者使用快捷键Ctrl+C和Ctrl+V。其实，还有一种鲜为人知的方法，让我们可以用最省时省力的操作来实现数据的快速复制。下面通过实例向大家介绍这一技巧。1.例如，我们要将如图1所示的A13:E21中的内容复制到A1:E9处。首先选中A13:E21。图12.移动鼠标指针到选中区域的黑色边框处，直到鼠标指针变为如图2所示的形状。图23.这时按下鼠标右键拖动鼠标，当移动到A1:E9时，松开右键，出现如图3所示菜单，单击“链接此处”。图3这样就实现了所选内容的快速复制，结果如图4所示。图4举一反三：细心的读者一定会注意到，如图3所示的右键菜单中还有许多其它命令，都是跟复制和移动有关的，以后如果要实现这些相关操作，都可以用以上所介绍的方法来实现。l 如何利用宏给Excel工作簿文档自动添加密码在Excel中在给工作簿文档添加密码时，需要通过选项一个一个的设置，比较麻烦。下面，我们利用一个自动运行的宏，让软件自动给文档添加密码。1、启动Excel，执行“工具宏Visual Basic 编辑器”命令，进入VBA编辑状态(如图1)。2、在左侧的“工程资源管理器”窗口中，选中“VBAproject(PERSONAL.XLS)”(个人宏工作簿)选项。3、执行“插入模块”命令，插入一个模块(模块1)。4、将下述代码输入到右侧的代码编辑窗口中：Sub Auto_close()ActiveWorkbook.Password = 123456ActiveWorkbook.SaveEnd Sub退出VBA编辑状态。注意：这是一个退出Excel时自动运行的宏，其宏名称(Auto_close)不能修改。5、以后在退出Excel时，软件自动为当前工作簿添加上密码(123456，可以根据需要修改)，并保存文档。l 如何用单元格数据作为Excel工作簿名称在Excel中，通常用Book1、Book2作为工作簿名称。能不能让Excel采用我们选定的某个单元格中的数据做为工作簿名称来保存文档呢?答案是肯定的。1、启动Excel，执行“工具宏Visual Basic 编辑器”命令，进入VBA编辑状态(如图1)。2、在左侧的“工程资源管理器”窗口中，选中“VBAproject(PERSONAL.XLS)”(个人宏工作簿)选项。3、执行“插入模块”命令，插入一个模块(模块1)。4、将下述代码输入到右侧的代码编辑窗口中：Sub baocun()lj = InputBox(请输入文档保存路径)ActiveWorkbook.SaveAs Filename:=lj & ActiveCell.Value & .xlsEnd Sub退出VBA编辑状态。5、以后要保存某个工作簿文档时，先选中作为名称的字符所在的单元格(参见图2)，然后执行“工具宏宏”命令，打开“宏”对话框(如图3)。6、选中刚才编辑的宏(PERSONAL.XLS!baocun)，单击“执行”按钮，系统弹出如图4所示的对话框。7、输入保存文档的路径(如“E:office技巧”)，单击“确定”按钮。文档保存成功(参见图5)。注意：如果不需要保存路径，文档将被保存到“我的文档”文件夹中。l 如何在Excel中实现多条件求和在平时的工作中经常会遇到多条件求和的问题。如图1所示各产品的销售业绩工作表，我们希望分别求出“东北区”和“华北区”两部门各类产品的销售业绩，或者在同一部门中的不同组也要求出各产品的销售业绩。在Excel中，我们可以有三种方法实现这些要求。一、分类汇总法首先选中A1:E7全部单元格，点击菜单命令“数据排序”，打开“排序”对话框。设置“主要关键字”和“次要关键字”分别为“部门”、“组别”，如图2所示。确定后可将表格按部门及组别进行排序。然后将鼠标定位于数据区任一位置，点击菜单命令“数据分类汇总”，打开“分类汇总”对话框。在“分类字段”下拉列表中选择“部门”，“汇总方式”下拉列表中选择“求和”，然后在“选定汇总项”的下拉列表中选中“A产品”、“B产品”、“C产品”复选项，并选中下方的“汇总结果显示在数据下方”复选项，如图3所示。确定后，可以看到，东北区和华北区的三种产品的销售业绩均列在了各区数据的下方。再点击菜单命令“数据分类汇总”，在打开的“分类汇总”对话框中，设置“分类字段”为“组别”，其它设置仍如图3所示。注意一定不能勾选“替换当前分类汇总”复选项。确定后，就可以在区汇总的结果下方得到按组别汇总的结果了。如图4所示。二、输入公式法上面的方法固然简单，但需要事先排序，如果因为某种原因不能进行排序的操作的话，那么我们还可以利用Excel函数和公式直接进行多条件求和。比如我们要对东北区A产品的销售业绩求和。那么可以点击C8单元格，输入如下公式:=SUMIF($A$2:$A$7,=东北区,C$2:C$7)。回车后，即可得到汇总数据。选中C8单元格后，拖动其填充句柄向右复制公式至E8单元格，可以直接得到B产品和C产品的汇总数据。而如果把上面公式中的“东北区”替换为“华北区”，那么就可以得到华北区各汇总数据了。如果要统计“东北区”中“辽宁”的A产品业绩汇总，那么可以在C10单元格中输入如下公式:=SUM(IF($A$2:$A$7=东北区,IF($B$2:$B$7=辽宁,Sheet1!C$2:C$7)。然后按下“Ctrl+Shift+Enter”键，则可看到公式最外层加了一对大括号(不可手工输入此括号)，同时，我们所需要的东北区辽宁组的A产品业绩和也在当前单元格得到了，如图5所示。拖动C10单元格的填充句柄向右复制公式至E10单元格，可以得到其它产品的业绩和。把公式中的“东北区”、“辽宁”换成其它部门或组别，就可以得到相应的业绩和了。三、分析工具法在EXCEL中还可以使用“多条件求和向导”来方便地完成此项任务。不过，默认情况下EXCEL并没有安装此项功能。我们得点击菜单命令“工具加载宏”，在打开的对话框中选择“多条件求和向导”复选项，如图6所示。准备好Office 2003的安装光盘，按提示进行操作，很快就可以安装完成。完成后在“工具”菜单中会新增添“向导条件求和”命令。先选取原始表格中A1:E7全部单元格，点击“向导条件求和”命令，会弹出条件求和的向导对话框，在第一步中已经会自动添加了需要求和计算的区域，如图7所示。点击“下一步”，在此步骤中添加求和的条件和求和的对象。如图8所示。在“求和列”下拉列表中选择要求和的数据所在列，而在 “条件列”中指定要求和数据应满足的条件。设置好后，点击“添加条件”将其添加到条件列表中。条件列可多次设置，以满足多条件求和。点击“下一步”后设置好结果的显示方式，然后在第四步中按提示指定存放结果的单元格位置，点击“完成”就可以得到结果了。如果要对多列数据按同一条件进行求和，那么需要重复上述操作。l 如何让隐藏的Excel工作表别人无法取消隐藏在Excel中，通常隐藏工作表的操作方法如下:把需要隐藏的工作表激活成当前工作表，执行一下“格式工作表隐藏”命令，即可将其隐藏起来。这样隐藏的工作表，通过执行“格式工作表取消隐藏”命令，打开“取消隐藏”对话框(如图1)，选中需要显示出来的工作表名称，单击一下“确定”按钮即可将其显示出来。今天，我给大家介绍一种隐藏工作表的方法，通过这种方法隐藏的工作表，别人显示不出来。1、启动Excel，打开相应的工作簿文档。2、按下Alt+F11组合键进入VBA编辑状态(如图2)。3、按下F4功能键，展开“属性”窗口(参见图3)。4、选中相应工作簿中需要隐藏的工作表(如“Sheet3(PPT)”)，然后在下面的属性窗口中，找到“Visible”选项，单击其右侧的下拉按钮，在随后出现的下拉列表中，选择 “0-xlSheetVeryHidden”选项。注意:每个工作簿文档中，至少要有一个工作表不被隐藏。5、再执行“工具VBAProject属性”命令，打开“VBAProject-工程属性”对话框(如图3)。6、切换到“保护”标签下，选中“查看时锁定工程”选项，并输入密码，确定返回(参见图3)。7、退出VBA编辑状态，保存一下工作簿文档，隐藏实现。经过这样的设置以后，我们发现“格式工作表取消隐藏”命令是灰色的，无法执行;如果想通过VBA编辑窗口修改属性，发现需要提供密码(如图4)，不知道密码就无法取消隐藏了。l 如何快速找到所需要的Excel函数面对众多的Excel函数，想必没有几位朋友可以把它们记得清清楚楚吧。哪天真正要用到这些函数时，您又该怎么办呢？也许有的朋友会翻阅相关的书籍，有的朋友会查询Excel的随机帮助。可我在使用Excel函数时几乎很少需要查这查那的，我会让Excel自己帮我把需要的函数找出来。想知道我是怎么操作的吗？(以下操作技巧已在微软Office 2003版本上测试通过)操作步骤如下:1. 打开Excel 软件2. 执行“插入”菜单“函数”命令，弹出如图1所示的窗口图13. 在图1窗口中，不知大家注意到箭头标注的那个区域没有，这其实就是一个小型的函数搜索器，在这里我们可以像搜索引擎那样输入需要查找的函数描述，然后点击“转到”按钮等待查询结果即可。如图2所示图2【小提示】 此处输入的函数描述要求尽量简明，最好能用两个字代替，比如“统计”、“排序”、“筛选”等等，否则Excel会提示“请重新表述您的问题”而拒绝为您进行搜索4. 怎么样？结果很快就出来了吧。要是您还是觉得Excel所推荐的函数过多而拿不准主意用哪个时，还可以在图2的函数列表中依次点击每个函数的名字，下面就会显示出关于它的简单解释以及使用方法，或者直接点击窗口最下面的“有关该函数的帮助”链接来查阅这个函数的详细说明l 如何利用Excel记录单快速录入表格数据在一个列数很多的Excel表格中输入数据时，来回拉动滚动条，既麻烦又容易错行，非常不方便。这时，我们可以利用记录单来输入：选中数据区域的任意一个单元格，执行“数据记录单”命令，打开“记录单”窗体(如图)，单击其中的“新建”按钮，然后在相应的单元格中输入数据，输入完一条记录后，按下“Enter”键或“下一条”按钮，进入下一条记录的输入状态。注意：在输入时，请按“Tab”键移动鼠标，不能按“Enter”键移动鼠标!l 如何对Excel作数据校验在用Excel录入完大量数据后，不可避免地会产生许多错误。通常多数朋友都是一手拿着原始数据，一手指着计算机屏幕，手工进行数据校验，这效率不高还累人。为此，特向大家介绍两种轻松且高效的数据校验方法。公式审核法有时，我们录入的数据是要符合一定条件的，例如，对于学生成绩表而言，其中的数据通常是0100之间的某个数值。但是，在录入时由于错误按键或重复按键等原因，我们可能会录入超出此范围的数据。那么，对于这样的无效数据，我们如何快速将它们“揪”出并予以更正呢？让我们用公式审核法来解决这类问题 吧。下面笔者以一张学生成绩表为例进行介绍。首先，打开一张学生成绩表。然后，选中数据区域(注意:这里的数据区域不包括标题行或列)，单击“数据有效性”命令，进入“数据有效性”对话框。在“设置”选项卡中，将“允许”项设为“小数”，“数据”项设为“介于”，并在“最小值”和“最大值”中分别输入“0”和“100”(见图)，最后单击“确定”退出。接着，执行“视图工具栏公式审核”命令，打开“公式审核”工具栏。好了，现在直接单击“圈释无效数据”按钮即可。此时，我们就可看到表中的所有无效数据立刻被红圈给圈识出来了(见图)。根据红圈标记的标志，对错误数据进行修正。修正后红圈标记会自动消失。这样，无效数据就轻松地被更正过来了。语音校验法使用Excel的“文本到语音”功能，将Excel工作表中的数据读出来给我们听，这样就可让耳朵和眼睛并行工作，以轻松实现数据的校验。具体操作如下：在Excel工作表中选定需要校验的数据，执行“工具语音文本到语音”命令，打开“文本到语音”工具栏(见图)。然后，根据自己的需要，单击工具栏上的“按行”或“按列”按钮来设定朗读顺序。接着，准备好原始数据，再单击“朗读单元格”按钮即可开始数据校验。不过，有些用户可能会发现自己的计算机只能朗读数字或英文单词，而不能朗读中文。这该怎么办？别急!此时，你还需要做点小小的设置。打开控制面板，双击其中的“语音”项。在“文本语音转换”选项卡中将“语音选 择”由“Microsoft Sam”改为“Microsoft Simplified Chinese”即可。再试试，是不是中文也可以读出来了？当然，也有用户可能会问，能不能在录入时就通过语音来校验数据呀？行，没问题!单击工具栏上的“按回车键开始朗读”。此时，当你在单元格中录入数据并回车后，Excel就会立即将数据读出来了。l 如何在Excel单元格中多行与一行并存在EXCEL中，经常会碰到在一个单元格中多行与一行同时并存的情况(见图1)，该如何处理呢？在图1的红圈处，左侧的“合计人民币(大写)”分成两行，而与之在同一格的 “仟 佰 拾 元 角 分”却是一行而已。我们可以利用文本框来处理。首先，单击绘图工具栏的文本框按钮，见图2红圈处:然后在单元格的左上角单击一下，输入 “合计人民”。注意是单击一下，而不是拖动鼠标，如果拖动鼠标，就会出现边框。效果见下图:采取同样的办法做出 “币(大写)”文本框，将鼠标移到文本框的边缘，按住鼠标即可拖动文本框到合适的位置。效果见下图:采取同样的办法做出 “仟 佰 拾 元 角 分”，将鼠标移到文本框的边缘，按住鼠标左键拖动该文本框到单元格右边合适的位置。效果见下图:至此，即可完成同一单元格多行与一行并存的处理。推而广之，如果在同一单元里多行与多行文字并存，也可以使用此办法进行处理。l 如何在同一Excel单元格中混用文本与数值数据在Excel中，有时我们需要在同一单元格中既显示文本，又显示数值。可以通过以下这些公式技巧来将文本与数字混合显示在同一单元格中。技巧之一例如，假设A6单元格包含数值1234，我们可以在另一个单元格(如D5)中输入以下公式：=总数:&A6则在D5单元格中就会显示出：“总数：1234”，如图1所示。图1在本例中，符号&所起的作用是将文本“总数”与A6单元格中的内容连接在一起。对这样一个包含公式的单元格应用数值格式是不起作用的，因为单元格中包含文本而不是数值。技巧之二如果在公式中巧妙地使用TEXT函数，也可以实现文本与数值同时显示在一个单元格中。例如，我们可以在另一个单元格(如D6)中输入以下公式：=总数: &TEXT(A6,$#,#0.00)这样在D6中就会显示为：“总数: $1,234.00”，如图2所示。图2技巧之三下面是一个使用NOW函数实现同一单元格同时显示文本与日期时间型数值的例子。=本报告打印于&TEXT(NOW(),yyyy-mm-d h:mm AM/PM)则输入完成后显示为：“本报告打印于2006-04-19 4:21 PM”，如图3所示。图3l 如何急救受损的Excel文件小心、小心、再小心，但还是避免不了Excel文件被损坏，那你是将受损文件弃之不顾呢，还是想办法急救呢？如果属于后一种的话，你将从下面的内容中得到惊喜。1、转换格式法这种方法就是将受损的Excel工作簿重新保存，并将保存格式选为SYLK格式；一般情况下，大家要是可以打开受损Excel文件，只是不能对文件进行各种编辑和打印操作的话，那么笔者建议大家首先尝试这种方法，来将受损的Excel工作簿转换为SYLK格式来保存，通过这种方法可筛选出文档中的损坏部分。2、直接修复法最新版本的Excel具有直接修复受损文件的功能，大家可以利用Excel新增的“打开并修复”命令，来直接检查并修复Excel文件中的错误，只要单击该命令，Excel就会打开一个修复对话框，单击该对话框中的修复按钮就可以了。这种方法常常适合用常规方法无法打开受损文件的情况。3、偷梁换柱法遇到无法打开受损Excel文件时，大家可以尝试使用Word程序来打开Excel文件，这种方法是利用Word直接读取Excel文件功能实现的，它通常适用于Excel文件头没有损坏的情况，下面是具体的操作步骤：(1)运行Word程序，在出现的文件打开对话框中选择需要打开的Excel文件；(2)要是首次运用Word程序打开Excel文件的话，大家可能会看到“Microsoft Word无法导入指定的格式。这项功能目前尚未安装，是否现在安装？”的提示信息，此时大家可插入Microsoft Office安装盘，来完成该功能的安装任务；(3)接着Word程序会提示大家，是选择整个工作簿还是某个工作表，大家可以根据要恢复的文件的类型来选择；(4)一旦将受损文件打开后，可以先将文件中损坏的数据删除，再将鼠标移动到表格中，并在菜单栏中依次执行“表格”/“转换”/“表格转换成文字”命令；(5)在随后出现的对话框中选择制表符为文字分隔符，来将表格内容转为文本内容；(6)在Word菜单栏中依次执行“文件”/“另存为”命令，将转换获得的文本内容保存为纯文本格式文件；(7)运行Excel程序，来执行“文件”/“打开”命令，在弹出的文件对话框中将文字类型选择为“文本文件”或“所有文件”，这样就能打开刚保存的文本文件了；(8)随后大家会看到一个文本导入向导设置框，大家只要根据提示就能顺利打开该文件，这样大家就会发现该工作表内容与原工作表完全一样，不同的是表格中所有的公式都需重新设置，还有部分文字、数字格式丢失了。4、自动修复法倘若Excel程序运行出现故障而导致文件受损的话，大家就可以使用这种修复方法了。一旦在编辑文件的过程中，Excel程序停止响应的话，大家可以强制关闭程序；要是由于突然断电导致文件受损的话，大家可以重新启动计算机并运行Excel，这样Excel会自动弹出“文档恢复”窗口，并在该窗口中列出了程序发生意外原因时Excel 已自动恢复的所有文件。大家可以用鼠标选择每个要保留的文件，并单击指定文件名旁的箭头，再按下面的步骤来操作文件：(1)想要重新编辑受损的文件的话，可以直接单击“打开”命令来编辑；(2)想要将受损文件保存的话，可以单击“另存为”，在出现的文件保存对话框中输入文件的具体名称；程序在缺省状态下，将文件保存在以前的文件夹中；(3)想要查看文件受损修复信息的话，可以直接单击“显示修复”命令；(4)完成了对所有要保留的文件相关操作后，大家可以单击“文档恢复”任务窗格中的“关闭”按钮；Excel程序在缺省状态下是不会启用自动修复功能的，因此大家希望Excel在发生以外情况下能自动恢复文件的话，还必须按照下面的步骤来打开自动恢复功能：(1)在菜单栏中依次执行“工具”/“选项”命令，来打开选项设置框；(2)在该设置框中单击“保存”标签，并在随后打开的标签页面中将“禁用自动恢复”复选框取消；(3)选中该标签页面中的“保存自动恢复信息，每隔X分钟”复选项，并输入指定Excel程序保存自动恢复文件的频率；(4)完成设置后，单击“确定”按钮退出设置对话框。l Excel公式应用常见错误及处理n #DIV/0! 错误 常见原因：如果公式返回的错误值为“#DIV/0!”，这是因为在公式中有除数为零，或者有除数为空白的单元格(Excel把空白单元格也当作0)。 处理方法：把除数改为非零的数值，或者用IF函数进行控制。具体方法请参见下面的实例。 具体实例：如图1的所示的工作表，我们利用公式根据总价格和数量计算单价，在D2单元格中输入的公式为“=B2/C2”，把公式复制到D6单元格后，可以看到在D4、D5和D6单元格中返回了“#DIV/0!”错误值，原因是它们的除数为零或是空白单元格。 假设我们知道“鼠标”的数量为“6”，则在C4单元格中输入“6”，错误就会消失(如图2)。 假设我们暂时不知道“录音机”和“刻录机”的数量，又不希望D5、D6单元格中显示错误值，这时可以用IF函数进行控制。在D2单元格中输入公式“=IF(ISERROR(B2/C2),B2/C2)”，并复制到D6单元格。可以看到，D5和D6的错误值消失了，这是因为IF函数起了作用。整个公式的含义为：如果B2/C2返回错误的值，则返回一个空字符串，否则显示计算结果。 说明：其中ISERROR(value)函数的作用为检测参数value的值是否为错误值，如果是，函数返回值TRUE，反之返回值FALSE.。 n #N/A 错误 常见原因：如果公式返回的错误值为“#N/A”，这常常是因为在公式使用查找功能的函数(VLOOKUP、HLOOKUP、LOOKUP等)时，找不到匹配的值。 处理方法：检查被查找的值，使之的确存在于查找的数据表中的第一列。 具体实例：在如图4所示的工作表中，我们希望通过在A10单元格中输入学号，来查找该名同学的英语成绩。B10单元格中的公式为“=VLOOKUP(A10,A2:E6,5,FALSE)”，我们在A10中输入了学号“107”由于这个学号，由于在A2:A6中并没有和它匹配的值，因此出现了“#N/A”错误。 如果要修正这个错误，则可以在A10单元格中输入一个A2:A6中存在的学号，如“102”，这时错误值就不见了(如图5)。 说明一：关于公式“=VLOOKUP(A10,A2:E6,5,FALSE)”中VLOOKUP的第四个参数，若为FALSE，则表示一定要求完全匹配lookup_value的值；若为TRUE，则表示如果找不到完全匹配lookup_value的值，就使用小于等于 lookup_value 的最大值。 说明二：出现“#N/A”错误的原因还有其他一些，选中出现错误值的B10单元格后，会出现一个智能标记，单击这个标记，在弹出的菜单中选择“关于此错误的帮助”(如图6)，就会得到这个错误的详细分析(如图7)，通过这些原因和解决方法建议，我们就可以逐步去修正错误，这对其他的错误也适用。 n #NAME?错误 常见原因：如果公式返回的错误值为“#NAME?”，这常常是因为在公式中使用了Excel无法识别的文本，例如函数的名称拼写错误，使用了没有被定义的区域或单元格名称，引用文本时没有加引号等。 处理方法：根据具体的公式，逐步分析出现该错误的可能，并加以改正，具体方法参见下面的实例。 具体实例：如图8所示的工作表，我们想求出A1:A3区域的平均数，在B4单元格输入的公式为“=avenge(A1:A3)”，回车后出现了“#NAME?”错误(如图8)，这是因为函数“average”错误地拼写成了“avenge”，Excel无法识别，因此出错。把函数名称拼写正确即可修正错误。 选中C4单元格，输入公式“=AVERAGE(data)”，回车后也出现了“#NAME?”错误(如图9)。这是因为在这个公式中，我们使用了区域名称data，但是这个名称还没有被定义，所以出错。 改正的方法为：选中“A1:A3”单元格区域，再选择菜单“名称定义”命令，打开“定义名称”对话框，在文本框中输入名称“data”单击“确定”按钮(如图10)。 返回Excel编辑窗口后，可以看到错误不见了(如图11)。 选中D4单元格，输入公式“=IF(A1=12,这个数等于12,这个数不等于12)”，回车后出现“#NAME?”错误(如12)，原因是引用文本时没有添加引号。 修改的方法为：对引用的文本添加上引号，特别注意是英文状态下的引号。于是将公式改为“=IF(A1=12,这个数等于12,这个数不等于12)”(如图13)。 n #NUM! 错误 常见原因：如果公式返回的错误值为“#NUM!”，这常常是因为如下几种原因：当公式需要数字型参数时，我们却给了它一个非数字型参数；给了公式一个无效的参数；公式返回的值太大或者太小。 处理方法：根据公式的具体情况，逐一分析可能的原因并修正。 具体实例：在如图14所示的工作表中，我们要求数字的平方根，在B2中输入公式“=SQRT(A2)”并复制到B4单元格，由于A4中的数字为“16”，不能对负数开平方，这是个无效的参数，因此出现了“#NUM!”错误。修改的方法为把负数改为正数即可。 n #VALUE错误 常见原因：如果公式返回的错误值为“#VALUE”，这常常是因为如下几种原因：文本类型的数据参与了数值运算，函数参数的数值类型不正确；函数的参数本应该是单一值，却提供了一个区域作为参数；输入一个数组公式时，忘记按CtrlShiftEnter键。 处理方法：更正相关的数据类型或参数类型；提供正确的参数；输入数组公式时，记得使用CtrlShiftEnter键确定。 具体实例：如图15的工作表，A2单元格中的“壹佰”是文本类型的，如果在B2中输入公式“=A2*2”，就把文本参与了数值运算，因此出错。改正方法为把文本改为数值即可。 图16中，在A8输入公式“=SQRT(A5:A7)”，对于函数SQRT，它的参数必须为单一的参数，不能为区域，因此出错。改正方法为修改参数为单一的参数即可。 如图17的工作表，如果要想用数组公式直接求出总价值，可以在E8单元格中输入公式“=SUM(C3:C7*D3:D7)”，注意其中的花括号不是手工输入的，而是当输入完成后按下CtrlShiftEnter键后，Excel自动添加的。如果输入后直接用Enter键确定，则会出现 “#VALUE”错误。 修改的方法为：选中E8单元格后激活公式栏，按下CtrlShiftEnter键即可，这时可以看到Excel自动添加了花括号(如图18)。 n #REF!错误 常见原因：如果公式返回的错误值为“#REF!”，这常常是因为公式中使用了无效的单元格引用。通常如下这些操作会导致公式引用无效的单元格：删除了被公式引用的单元格；把公式复制到含有引用自身的单元格中。 处理方法：避免导致引用无效的操作，如果已经出现错误，先撤销，然后用正确的方法操作。 具体实例：如图19的工作表，我们利用公式将代表日期的数字转换为日期，在B2中输入了公式“=DATE(LEFT(A2,4),MID(A2,5,2),RIGHT(A2,2)”并复制到B4单元格。 这时如果把A2:A4单元格删除，则会出现“#REF!”错误(如图20)，这是因为删除了公式中引用的单元格。 先执行“撤消 删除”命令，然后复制B2:B4单元格区域到A2:A4，也会出现“#REF!”错误(如图21)，这是因为把公式复制到了含有引用自身的单元格中。 由于这时已经不能撤销，所以我们先把A2:A4中的数据删除，然后设置单元格格式为“常规”，在A2:A4中输入如图19所示的数据。 为了得到转换好的日期数据，正确的操作方法为：先把B2:B4复制到一个恰当的地方，如D2:D4，粘贴的时候执行选择性粘贴，把“数值”粘贴过去。这时D2:D4中的数据就和A列及B列数据“脱离关系”了，再对它们执行删除操作就不会出错了(如图22)。 说明：要得到图22的效果，需要设置D2:D4的格式为“日期”。 n #NULL!错误 导致原因：如果公式返回的错误值为“#NULL!”，这常常是因为使用了不正确的区域运算符或引用的单元格区域的交集为空。 处理方法：改正区域运算符使之正确；更改引用使之相交。 具体实例：如图23所示的工作表中，如果希望对A1:A10和C1:C10单元格区域求和，在C11单元格中输入公式“=SUM(A1:A10 C1:C10)”，回车后出现了“#NULL!”错误，这是因为公式中引用了不相交的两个区域，应该使用联合运算符，即逗号 (,)。 改正的方法为：在公式中的两个不连续的区域之间添加逗号，改正后的效果为图24。 关于Excel公式常见错误的处理方法就介绍到这里。文中选用的实例都是平时出现最多的情况，请大家注意体会。l 如何实现Excel表格数据行列巧互换一张Excel报表，行是项目栏、列是单位栏，现在想使整张表格反转，使行是单位栏、列为项目栏，且其中的数据也随之变动。也就是想让Excel表格数据的行列互换，该怎么做呢？可先选中需要交换的数据单元格区域，执行“复制”操作。然后选中能粘贴下数据的空白区域的左上角第一