EXCEL应用技巧大全

第一篇  绪论：最佳Excel学习方法           1 技巧1  成为Excel高手的捷径                    2 技巧2  使用Excel联机帮助系统      5 技巧3  通过互联网搜索学习资源和解题方法      8 技巧4  在新闻组或BBS中学习      10 第二篇  Excel基本功能           13 第1章  操作Excel 14 技巧5  设置不同的Excel启动效果      14 技巧6  Excel启动时自动打开指定的工作簿      16 技巧7  实用的Excel选项设置      18 技巧8  更新Excel到最新状态      20 技巧9  Excel 2000中的赛车游戏      24 技巧10  解决双击XLS文件无法打开的问题      26 第2章  工具栏和菜单      29 技巧11  让Excel始终显示完整的菜单      29 技巧12  自定义菜单和工具栏      30 技巧13  共享自定义工具栏      32 技巧14  备份自定义工具栏和菜单      33 第3章  操作工作簿      34 技巧15  显示工作簿的完整路径      34 技巧16  自定义默认工作簿      35 技巧17  预览工作簿      36 技巧18  多用途的文档属性      38 技巧19  查看最后打开文件的人      41 技巧20  使用工作区文件      42 技巧21  一次关闭多个文件的技巧      43 技巧22  简繁转换不求人      44 技巧23  保护Excel文件      46 技巧24  为工作簿减肥      48 技巧25  修复受损的Excel文件      54 技巧26  Excel文件转化为PDF文档      55 第4章  操作工作表      57 技巧27  选取单元格区域的高招      57 技巧28  轻松选择“特殊”区域      59 技巧29  快速填充所有空白单元格      61 技巧30  改变撤消的步数      63 技巧31  省心省力的重复操作      64 技巧32  快速切换工作表      65 技巧33  重置工作表中的已使用范围      66 技巧34  改变工作表标签颜色与字号      67 技巧35  更改Excel默认的行列标签的颜色      68 技巧36  隐藏秘密数据      69 技巧37  快速插入多个单元格      71 技巧38  快速插入多个行或列      71 技巧39  隔行插入的绝招      72 技巧40  在非空行之间自动插入空行      74 技巧41  快速删除所有空行      75 技巧42  快速改变行列的次序      77 技巧43  快速设置最适合的列宽      78 技巧44  限定工作表中的可用范围      78 技巧45  彻底隐藏工作表                                             81 技巧46  自定义默认工作表      83 第5章  数据处理      85 技巧47  输入数据后向不同方向移动单元格指针      85 技巧48  在单元格区域中移动      85 技巧49  方便查看标题的“冻结窗格”功能      86 技巧50  多窗口协同作业      87 技巧51  定义自己的序列      88 技巧52  自动填充的威力      89 技巧53  巧用右键和双击填充      93 技巧54  输入分数的方法      94 技巧55  使用语音校验输入准确性      94 技巧56  控制自动超链接      98 技巧57  取消现有的超链接      99 技巧58  提取超链接信息      101 技巧59  输入特殊字符      103 技巧60  输入身份证号码      105 技巧61  自动更正的妙用      106 技巧62  为文本添加拼音      107 技巧63  替换掉所有单元格中的换行符      108 技巧64  模糊查找数据      109 技巧65  查找替换单元格格式      110 技巧66  神奇的选择性粘贴      112 技巧67  Excel的摄影功能      114 第6章  单元格格式      117 技巧68  奇妙的自定义数字格式      117 技巧69  随心所欲设置日期格式      123 技巧70  自定义数字格式的经典应用      125 技巧71  把自定义数字格式的显示值保存下来      127 技巧72  合并单元格的同时保留所有数值      128 技巧73  为同一个单元格里的文本设置不同格式      130 技巧74  制作斜线表头      131 技巧75  单元格里的文字换行      134 技巧76  工作表背景图片的妙用      136 第7章  名称的奥妙      140 技巧77  定义名称的3种方法      140 技巧78  名称命名的为与不为      141 技巧79  在名称中使用常量与函数      142 技巧80  名称的作用范围      144 技巧81  编辑名称引用      145 技巧82  创建动态名称      146 技巧83  图片自动更新      148 技巧84  快速选择定义名称的区域      153 技巧85  创建名称表格      153 技巧86  以图形方式查看名称      154 技巧87  快速删除所有名称      155 第8章  活用条件格式      156 技巧88  美丽的数据区域      156 技巧89  永恒的间隔底纹      159 技巧90  快速比较不同区域的数值（一）      161 技巧91  快速比较不同区域的数值（二）      162 技巧92  自动醒目的小计      164 技巧93  在工作日历中突显周休日      166 技巧94  突显活动单元格的坐标      167 技巧95  生日提醒      169 技巧96  特殊数值提醒      171 技巧97  标记重复值      172 技巧98  标记含有公式的单元格      173 技巧99  标记匹配目标数值的单元格      175 技巧100  用8种颜色区分不同数值      176 技巧101  无限条件的条件格式      178 第9章  数据有效性绝技      180 技巧102  在单元格中创建下拉列表      180 技巧103  另类的批注      181 技巧104  快速切换有效性的来源      183 技巧105  动态源的数据有效性      184 技巧106  只能录入某种特征的数据      184 技巧107  限制重复值的录入      185 技巧108  杜绝负数库存      187 技巧109  强制序时录入      188 技巧110  多级选择录入      190 第10章  打印      193 技巧111  控制需要打印的内容      193 技巧112  重复打印顶端标题行和左侧标题行      194 技巧113  在同一页上打印不连续的区域      195 技巧114  控制打印输出的比例      196 技巧115  克隆工作表的页面设置      198 技巧116  在报表的每一页上打印公司Logo    199 技巧117  在页眉或页脚增加分隔线      201 技巧118  Excel的双面打印      203 技巧119  在Excel中定时打印      204 技巧120  虚拟打印      205 第三篇  数据分析                             209 第11章  排序与筛选      210 技巧121  对超过3列的数据排序      210 技巧122  按照特定的顺序排序      211 技巧123  按笔划排序      213 技巧124  按行来排序      215 技巧125  按字符数量排序      217 技巧126  按颜色排序或筛选      218 技巧127  随机排序      220 技巧128  排序字母与数字的混合内容      221 技巧129  返回排序前的表格      222 技巧130  解决常见的排序故障      222 技巧131  在受保护的工作表中使用自动筛选      225 第12章  分类汇总与数据透视      228 技巧132  分类汇总结果的复制      228 技巧133  多字段分类汇总      231 技巧134  多层次的数据浏览      232 技巧135  在受保护的工作表中调整分级显示视图      234 技巧136  用二维表创建数据透视表      235 技巧137  组合数据透视表内的日期项      238 技巧138  快速统计重复项目      240 技巧139  在数据透视表中添加计算项      242 技巧140  自动刷新数据透视表      244 第四篇  函数导读                             247 第13章  函数介绍      248 技巧141  慧眼识函数      248 技巧142  函数公式的输入和编辑      249 技巧143  函数工具提示      251 技巧144  函数公式查错与监视      252 技巧145  分步查看公式计算结果      254 技巧146  保护和隐藏工作表中的公式      256 第14章  函数基础      258 技巧147  单元格的引用方法      258 技巧148  快速切换引用类型      259 技巧149  数据类型区分及转换      260 技巧150  逻辑关系判断      262 技巧151  运算符号      264 技巧152  函数参数的处理技巧      267 技巧153  函数公式的限制与突破      269 技巧154  函数的易失性      271 第15章  数组公式入门      272 技巧155  理解数组      272 技巧156  理解多重计算及数组公式      274 技巧157  多单元格数组公式      275 技巧158  数组的转置和变换      276 技巧159  为何不能用AND、OR替代*、+   277 第五篇  函数技巧                             279 第16章  信息处理      280 技巧160  屏蔽公式返回的错误值      280 技巧161  取得单元格信息      280 技巧162  取得当前工作表表名      281 技巧163  转换数值      281 第17章  文本处理      283 技巧164  字符转换技巧三则      283 技巧165  重复文本技巧二则      284 技巧166  字符串比较及清理垃圾字符      285 技巧167  替换字符      286 技巧168  查找字符      287 技巧169  计算字符出现的次数      288 技巧170  提取字符串      290 技巧171  文本合并符号的妙用      293 技巧172  重复记录自动加编号      295 技巧173  格式化数值      296 技巧174  解读身份证编码信息      297 技巧175  身份证位数的变换      299 技巧176  金额数字分列      300 技巧177  数字转换成英文序数      301 技巧178  人民币金额大写公式      302 第18章  日期与时间计算      304 技巧179  根据生日计算农历生肖年份      304 技巧180  解读日期格式代码      305 技巧181  日期与数字格式的互换      306 技巧182  英文日期的转换      308 技巧183  将中文日期文本转为日期值      309 技巧184  计算指定间隔的日期      310 技巧185  月度、季度相关日期计算      312 技巧186  隐秘函数DATEDIF    314 技巧187  星期的相关计算      316 技巧188  工作日的相关计算      318 技巧189  解读时间格式代码      320 技巧190  将角度显示为度分秒以及相关转换计算      321 第19章  数学与三角计算      322 技巧191  常用数值舍入计算      322 技巧192  按人民币面额估算备钞数      323 技巧193  余数的妙用      324 技巧194  数值的修约      326 技巧195  产生规定范围内的随机数      328 技巧196  求解多元一次方程      329 技巧197  求出成绩最高分      330 第20章  统计求和      332 技巧198  设置目标数据的上、下限      332 技巧199  统计选定区域数据个数      332 技巧200  认识COUNTIF函数      334 技巧201  单字段多条件计数      335 技巧202  动态统计及格人数      335 技巧203  认识SUMIF函数      336 技巧204  单字段多条件求和      338 技巧205  使用通配符模糊求和      339 技巧206  日计账中的余额累计      340 技巧207  数据表实现动态小计      341 技巧208  SUM数组公式计数与求和      342 技巧209  SUM函数与SUMPRODUCT函数的区别      344 技巧210  部门评价等级转换      346 技巧211  分级累进求和      347 技巧212  自动筛选、隐藏、组合下的统计      349 技巧213  取得各科成绩的最高分      351 技巧214  统计不及格人数      352 技巧215  指定条件下求平均值      352 技巧216  对称剔除极值求平均值      353 技巧217  认识FREQUENCY函数      354 技巧218  统计不重复值的数量      356 技巧219  单个区域排名      358 技巧220  多个区域排名      359 技巧221  中国式排名      360 技巧222  求第n大的不重复值      360 第21章  查找与引用      362 技巧223  根据首行（列）查找记录      362 技巧224  多条件区间判断取值      363 技巧225  确定查找值是否存在      365 技巧226  根据行列条件返回结果      366 技巧227  返回引用的单元格地址      366 技巧228  逆向查询数据      367 技巧229  模糊查找数据      368 技巧230  返回字符串中连续数值      369 技巧231  定位最后非空单元格      370 技巧232  生成垂直、水平序列      371 技巧233  理解OFFSET函数      372 技巧234  批量生成工资条      374 技巧235  建立超链接      375 技巧236  自动跳转到未输入的单元格      376 技巧237  统计指定月份的销量汇总      376 技巧238  取得目标汉字的拼音首字母      377 技巧239  根据双列条件进行查找      379 技巧240  返回最后一条数据记录      380 技巧241  按单条件筛选记录      381 技巧242  按多条件筛选记录      382 技巧243  对数据进行排序      383 技巧244  多关键字排名应用      384 技巧245  返回单列中的唯一值列表      385 技巧246  返回双列中的唯一值列表      386 技巧247  返回数据区域中的唯一值列表      388 第22章  宏表函数      390 技巧248  认识宏表函数      390 技巧249  取得工作表名      391 技巧250  取指定路径下所有文件名      393 技巧251  取单元格属性值      393 技巧252  取得表达式      395 技巧253  算式和计算结果      396 第23章  逻辑判断      398 技巧254  养成良好的逻辑分析习惯      398 技巧255  学会逆向思维      399 第24章  函数公式的优化      401 技巧256  去除冗余判断      401 技巧257  使用动态引用      402 技巧258  使用辅助列和普通公式      404 技巧259  使用“高效函数”      405 第六篇  函数高级应用                407 第25章  多维引用      408 技巧260  多维引用的工作原理      408 技巧261  三维引用取不重复数据      409 技巧262  跨多表查询      411 技巧263  跨多表汇总      413 技巧264  条件筛选下求不重复值      414 技巧265  求出各项目极值的合计数      415 第26章  内存数组      418 技巧266  数组变换技巧三则      418 技巧267  N/T函数生成内存数组      420 技巧268  空行自动填满数据的内存数组      422 技巧269  生成除当前工作表外的工作表名称      423 技巧270  对文本进行排序      424 技巧271  MMULT函数应用      428 第27章  循环引用      433 技巧272  认识循环引用      433 技巧273  记录单元格操作时间      434 技巧274  产生不变随机数      434 技巧275  求固定总和的组合      435 技巧276  有记忆功能的单元格      437 第七篇  图表与图形                      439 第28章  图表通用技巧      440 技巧277  去除分类轴上的空白日期      440 技巧278  折线图中处理空单元格的三种样式      441 技巧279  让图表自动适应窗口大小      443 技巧280  快速设置图表字体      445 技巧281  隐藏接近于零的数据标签      446 技巧282  在图表中处理负值      448 技巧283  快速添加图表数据系列      449 技巧284  在图表中使用透明填充色      451 技巧285  背景透明的图表      453 技巧286  在图表中使用QQ图片      454 技巧287  制作完全静态的图表      457 技巧288  控制数据标志的显示位置      459 技巧289  快速统一多个图表大小      461 技巧290  使条形图分类轴的标签与源数据列显示顺序一致      463 技巧291  自定义图表      465 技巧292  图表公式SERIES的使用技巧      466 技巧293  在图表中使用对数刻度      469 技巧294  制作瀑布图      470 技巧295  美化三维图表      472 技巧296  快速制作组合图表      476 技巧297  随单元格自动变化的图表标题      478 技巧298  图表背景——横向分割      479 技巧299  图表背景——纵向分割      482 技巧300  图表背景——四象限图表      483 技巧301  复合饼图      486 技巧302  利用误差线制作数据点交叉线      488 技巧303  N合一图表      490 技巧304  在图表中画直线      493 技巧305  始终突出显示最大值和最小值      496 技巧306  画趋势线并显示趋势线公式      497 技巧307  动态图表——辅助列      499 技巧308  动态图表——定义名称      500 技巧309  动态图表——最后7天的数据      502 技巧310  动态图表——活动单元格      504 技巧311  求任意点的坐标      505 技巧312  美丽的万花规      508 技巧313  批量绘图      511 技巧314  数据系列快速着色      513 技巧315  批量添加数据标签      515 技巧316  快速将图表输出为图片      518 第29章  经典图表实例      519 技巧317  Xbar-R控制图      519 技巧318  双层饼图      523 技巧319  柏拉图      527 技巧320  直方图和正态分布图      531 技巧321  立体雷达图      536 技巧322  自定义多轴图表      538 技巧323  不等宽柱形图      542 技巧324  任意函数曲线图      545 技巧325  步进图      548 技巧326  甘特图      550 技巧327  比较柱形图      554 技巧328  盈亏平衡分析图      556 技巧329  波士顿矩阵图      560 第30章  图示和图形      564 技巧330  组织结构图      564 技巧331  图示中的文本设置      566 技巧332  流程图      569 技巧333  两个阴影的图形      571 技巧334  图表转为自选图形      572 技巧335  图形对齐技巧      574 技巧336  自选图形群的编辑      576 技巧337  用Excel画画      578 技巧338  自选图形的变形      580 技巧339  在批注中插入图表      581 技巧340  条码图形      583 第八篇  VBA实例与技巧                                         587 技巧341  让VBA代码更加美观易读      588 技巧342  加速VBA代码执行效率      590 技巧343  利用日期控件输入时间      594 技巧344  确定当前单元格所在的页数      597 技巧345  利用VBA取不重复值      601 技巧346  提取字符中指定类型的字符      602 技巧347  替换工具栏按钮的图标      603 技巧348  制作路径选择对话框      604 技巧349  从其他工作簿取值      607 技巧350  自动添加控件及事件代码      608 技巧351  定义Application级别的事件代码      610 技巧352  批量修改工作表中的批注      611 技巧353  快速隐藏周围区域      613 技巧354  设置缺省目录和文件名      615 技巧355  在长时间运算中使用友好提示      616 技巧356  制作个性化启动画面      617 技巧357  记录工作簿最后更新时间      619 技巧358  判断文件是否已经打开和是否存在      620 技巧359  人民币大写转换函数      621 技巧360  按颜色求和与计数函数      622 附录                                 625 附录A  Excel的各种版本      626 附录B  Excel常用快捷键      628 附录C  Excel常用函数      633 附录D  Excel的各种规范与限制      644 附录E  Excel简、繁、英文词汇对照表      647 附录F  Excel相关的网站推荐      651 附录G  光盘内容      652 非常感谢您选择了《Excel实战技巧精粹》！ 这本书凝聚了多位中国资深Excel专家的心血，这些专家都来自大型中文Excel学习网站Excel Home。作为作者团队中的一员和Excel Home的站长，我非常清楚，他们中的每一个人都具有多年Excel应用经验，而且是其所在行业中的佼佼者。他们在Excel Home的技术论坛上回答了成千上万的网友提问，发表了无数有关Excel的绝妙应用方案。经过他们的共同努力，无数的Excel用户在使用水平上获得了长足的进步。 为了把这样的学习活动进行线下推广，我们联合起来，根据这几年时间里在线答疑过程中的积累，从100多万帖子中精选出网友们最关注或最迫切需要掌握的Excel应用技巧，汇编成本书。本书的技巧基本含括了Excel方方面面的功能，也涉及了Excel在各行各业中的应用。同时，本书还提供了大量的实例，并在内容编排上尽量细致和人性化，以求读者能方便而又愉快地学习。 当然，要想在一本书里罗列出Excel的所有技巧是不可能的事情。所以我们只能尽可能多地把最通用和实用的挑选出来，展现给读者，尽管这些仍只不过是冰山一角。对于我们不得不放弃的其他技巧，读者可以登录Excel Home网站，在海量的文章库和发帖中搜索自己所需要的。 众所周知，在当今的图书市场上已经有很多关于Excel的书，无论它们的质量好坏，都有一个共性，就是只专注于Excel的使用方案或如何创建一个方案，而对许多的技术细节缺乏详细的说明，更没有教读者如何学习Excel。我们希望本书的问世能打破这种局面。在本书中，我们除了向读者提供大量的实用技巧以外，还会传授学习方法与高手的成长经验，以期能“授人以渔”。 读者对象 本书面向的读者群是Excel的中、高级用户以及IT技术人员，因此，希望读者在阅读本书以前具备Excel 2000以及更高版本的使用经验，了解键盘与鼠标在Excel中的使用方法，掌握Excel的基本功能和对菜单命令的操作方法。 另外，如果读者不清楚自己属于Excel用户群中的哪一个层别（这是很现实的问题，至今为止，微软公司自己也没有发布过一个标准来划分用户的水平），本书的绪论部分为读者准备了这方面的内容。 本书约定 在正式开始阅读本书之前，建议读者花上几分钟时间来了解一下本书在编写和组织上使用的一些惯例，这会对您的阅读有很大的帮助。 软件版本 本书的写作基础是安装于Windows XP专业版操作系统上的Excel 2003。尽管如此，除了少数特别注明的部分以外，本书中的技巧也适用于Excel的早期版本，如Excel 2000。 菜单命令 我们会这样来描述在Excel或Windows，以及其他Windows程序中的操作，比如在讲到用Excel打印一份文件时，会写成：单击菜单“文件”→“打印”。 鼠标指令 本书中表示鼠标操作的时候都使用标准用语：“指向”、“单击”、“右键单击”、“拖动”、“双击”等，您可以很清楚知道它们表示的意思。 键盘指令 当读者见到类似这样的键盘指令时，表示同时按下Ctrl键和F3键。 Win表示Windows键，就是键盘上画着ÿ的键。本书还会出现一些特殊的键盘指令，表示方法相同，但操作方法会稍许不一样，有关内容会在相应的技巧中详细说明。 Excel函数与单元格地址 本书中涉及的Excel函数与单元格地址将全部使用大写，如SUM()、A1∶B5。但在讲到函数的参数时，为了和Excel中显示一致，函数参数全部使用小写，如SUM(number1，number2，...)。 阅读技巧 虽然我们按照一定的顺序来组织所介绍的技巧，但这并不意味着读者需要逐页阅读。读者完全可以凭着自己的兴趣和需要，选择其中的某些技巧来读。当然，为了保证对将要阅读到的技巧能够很好地理解，建议读者从难度较低的技巧开始。万一遇到读不懂的地方也不必着急，可以先“知其然”而不必“知其所以然”，参照我们的示例把技巧应用到练习或者工作中去，以解决燃眉之急，然后在空闲的时间，通过阅读其他相关章节的内容，或者按照我们在本书中提供的学习方法把自己欠缺的知识点补上，那么就能逐步理解所有的技巧了。 致谢 本书的第一篇由周庆麟编写，第二、三篇由周庆麟和王建发编写，第四篇至第六篇由李幼乂和陈国良编写，第七篇由盛杰编写，第八篇由郗金甲和周庆麟编写，最后由周庆麟完成统稿。本书在编写过程中得到了周建平、周元平、孔贵生、陈军、顾斌、黄朝阳、林树珊等多位Excel Home版主的支持和帮助，在此向他们表示由衷的感谢！ 在本书的编写过程中，尽管我们的每一位团队成员都未敢稍有疏虞，但纰缪和不足之处仍在所难免。敬请读者能够提出宝贵的意见和建议，您的反馈将是我们继续努力的动力，本书的后继版本也将会更臻完善。我们已经在Excel Home上开设了专门的版块用于本书的讨论与交流，您也可以发送电子邮件到book@excelhome.net，我们将尽力为您服务。 编  著   2006年11月 当Kevin第一次和我提起Excel Home出了一本自己的书要我写序的时候，我们正忙着Microsoft Office system 2007在中国11月30日的上市发布。那个时候只是脑子想了一下“太棒了，Excel Home的专家们要出书了，一定是实用加精彩，赶紧先睹为快就好”，等到真正有时间读到书稿的时候已经近12月中旬了。 事实上，我曾对于要不要写这个序有过顾虑，因为我们刚刚发布了新的版本—Office 2007，而这本书是以2003版本为基础的，书中的某些技巧会因为版本的升级而过时。不过，当读完本书不到1/3的内容之后，我就决定应该写一点东西给Kevin和这本书的读者。 第一，感动于Kevin及其一班高手自发组织的Excel Home网站。他们挤出自己的私人时间创建了完全开放、免费的在线交流平台，并通过这种方式和更多的人互相分享如何使用Excel的经验，解答大家的问题，帮助大家共同提高应用水平。 第二，这本书不但凝结了Excel Home的高手、专家们通过自己多年的实践总结的Excel实用技巧，更重要的是，他们在通过这本书教大家解决问题的方法，而不只是结果，就是所谓的“授人以渔”。 第三，也是最重要的，这本书中的技巧的确是大家日常所需的，很实用、很有帮助。这一点正得益于Excel Home多年来实际案例的大量积累，所以对于“Excel实战技巧精粹”的书名，是真正的文如其名。 由于工作的原因，在过去的几年中有机会和很多Office的最终用户打交道并听到他们的反馈。有两个问题很普遍：第一，很多人都在使用MS Excel软件，但大多数人印象中的Excel只是用来做做表格，用加减乘除的公式做做计算，很少人知道并且在深度地使用Excel 做分析和管理的事情；第二，很多用户觉得去了解软件的功能和新特性是个麻烦的事情，他们倾向于满足现有的工作环境而不愿意尝试去利用新的技术以改进工作方式和提升工作效率。 而事实上，功能强大的Excel可以很好地帮助用户完成各种简单和复杂的工作。举几个例子吧。 有一次我们去客户公司给他们的销售和财务人员做应用培训，当讲到Excel的“数据透视表”功能的时候，刚开始大家并不是那么专注，觉得在讲一个他们根本不会用到的功能。但是当讲师拿一个类似的销售数据一步完成了数据透视表，并开始神奇般地用鼠标轻松“拖、拉、拽”一些关键数据，轻松地显现各区域各季度的销售状况时，下面开始议论纷纷。当讲师继续操作一步，生成数据透视图，使得所有销售情况以直观加美观的方式展现出来的时候，有个销售人员说：“平常我想给老板做个类似的报告要花很多时间，还要想怎么做个直观并且专业的图表。特别是老板再要更多数据或要从不同角度分析结果的时候，那就只好再来一遍，太费时费力了。这个‘数据透视表’的功能就是我需要的，方便动态查询，看起来能省很多时间。” 还有一次，有个同事要贷款买房，想大概了解每月还多少钱，我拿出Office中一个贷款计算器的模板，他输入了贷款总额、贷款时间、贷款利率，一回车，每月的还贷额依次列出。我的同事没想到Excel可以帮到这个忙。 提到这些，只是想说明，任何软件就是一个工具，它的出现是服务于大家的需求，帮助大家轻松地工作和生活的。所以希望大家都能在实践中善用工具，最大化利用工具的效能，以简化工作、提升效率。 这本书的作者们都是各自行业中的佼佼者，同时对Excel在本行业中的应用有着丰富的经验。他们在对Excel Home技术论坛上数百万贴子进行分析与提炼之后，汇集了用户在使用过程中最常见的各种需求，通过几百个实例的演示与讲解，帮助用户从以前教科书式的操作中解放出来，充分发挥自己的创意，更灵活有效地使用Excel来处理自己的问题。除了介绍Excel的应用技巧以外，书中还以独特的视角与读者讨论“如何学习Excel”这个非常有意义的话题。 希望众多Excel专家奉献出的宝贵经验能够帮助读者更科学更有效地学习Excel，逐步成为真正的高手。也希望广大的用户能够充分利用书中提供的技巧，提高自己的工作效率，获得更佳的Office产品体验。 张燕  微软（中国）有限公司Office产品市场经理 技巧72合并单元格的同时保留所有数据的方法 这在很多时候会让用户觉得为难，合并会丢失数据，影响数据的计算，而不合并则无法兼顾到美观性。下面的方法可以突破Excel的这种局限，在合并单元格的同时保留所有数值。假设有如图72‑2所示的表格，现在需要分别把A1:A4和A5:A8进行单元格合并。 图72‑2  需要合并单元格的表格 选择C1:C4，单击工具栏中的“合并与居中”按钮。同样的方法将C5:C8也合并单元格，如图72‑3所示。 图72‑3  在空白的单元格区域按照目标 区域的大小合并单元格 选择C1:C8，单击工具栏中的“格式刷”按钮，然后单击A1进行格式复制。结果如图72‑4所示。在这个过程里面，Excel并不会出现任何警告。 图72‑4  把空白区域的格式复制到 目标区域 为了验证一下被合并的单元格是否还保留了原来的数据，可以在D列中使用公式进行引用计算，在D1中输入公式： =A1 然后拖曳到D8进行公式复制。结果如图72‑5所示。 图72‑5  合并单元格后所有数值都得以保留 技巧73为同一单元格文本设置不同的格式 在Excel里面，设置单元格格式的对象并不必是整个单元格，也可以只是单元格里面的一部分内容——前提是这个单元格存储的是文本型内容。利用这个特性，用户可以把一个单元格中的内容设置成不同的格式，以满足外观上的需要。 先选定单元格，然后在编辑栏中选定需要设置格式的部分内容，就可以通过使用工具栏上的各个格式按钮来改变格式。比如加粗、倾斜、改变颜色、字体等。 在选定单元格中部分内容后，还可以按组合键调出“单元格格式”对话框进行设置，如图73‑1所示。此时的“单元格格式”对话框只有“字体”一个选项卡可用。 图73‑1  只能设置字体的“单元格格式”对话框 图73‑2中显示了对单元格的内容设置不同格式的例子。 图73‑2  对单元格的内容设置不同的格式 在报表里面制作斜线表头是中国人特有的习惯，遗憾的是Excel并没有直接提供对这一样式的良好支持，所以这个问题成为了BBS上最常被提问的问题之一。 本技巧将介绍一些方法，尽可能完美地解决这个问题。 74‑1  单线表头 如果表头中只需要一条斜线，可以利用Excel的边框设置来画斜线，然后填入文字内容。在单元格内画斜线的方法如下。 选定单元格，按组合键调出“单元格格式”对话框。 在“单元格格式”对话框的“边框”选项卡中，在“边框”区中单击斜线按钮。如图74‑1所示。 图74‑1  在单元格内画斜线 画好斜线后在单元格内填充表头内容的方法有三种。 方法1  使用文本框 单击菜单“插入”→“文本框”→“横排”（视情况也可以选择“竖排”），在单元格内连续插入两个文本框。 分别编辑两个文本框中的相关文字，并调整文本框大小和文字字号以相互适应。 选定文本框，单击菜单“格式”→“文本框”，在“设置文本框格式”对话框的“颜色与线条”选项卡中，分别将“填充颜色”与“线条颜色”设置为“无”，如图74‑2所示。 图74‑2  设置文本框格式 用鼠标把两个文本框分别移动到画有斜线的单元格的合适位置。 完成后的效果如图74‑3中第2行所示。 图74‑3  单斜线表头 方法2  使用上下标 在画有斜线的单元格中输入文字。 把不同部分的内容分别设置为上标或下标格式（具体方法请参阅技巧73）。 根据需要，还可在不同部分之间插入适量的空格进行美化。 完成后的效果如图74‑3中第4行所示。 方法3  使用空格进行中分 如果表头的项目文字都非常少，也可以直接输入文字，然后在不同部分之间插入足够的空格，如图74‑3中第6行所示。 74‑2  多斜线表头 如果表头中需要画多条斜线，就只能借助自选图形来做了。 选定单元格，设置足够的长度和高度。 单击工具栏中的“绘图”按钮，在“绘图”工具栏中选择“直线”工具，绘制两条或多条直线到单元格中。 使用插入文本框的方式逐个添加表头项目。 完成后的效果如图74‑4所示。 图74‑4  多斜线表头 如果在单元格里面输入很多字符，Excel会因为单元格的宽度不够而没有在工作表上显示多出的部分。如果长文本单元格的右侧是空单元格，Excel会继续显示文本的其他内容只到全部内容都被显示或者遇到一个非空单元格而不再显示。如图75‑1所示。 图75‑1  长文本单元格的显示方式 注意 在文本格式的单元格中，如果输入的字符数超过255个，则单元格的内容虽然能在编辑栏中正常显示，但在工作表中只显示为一长串“#”。而常规格式的单元格没有这样的限制。关于Excel单元格的规范与限制，请参阅附录D。 很多时候用户因为受到工作表布局的限制而无法加宽长文本单元格到足够的宽度，但又希望能够完整的显示所有文本内容，那么可按如下方法来解决。 选定长文本单元格，按组合键调出“单元格格式”对话框。 在“单元格格式”对话框的“对齐”选项卡中勾选“自动换行”复选框，单击“确定”，如图75‑2所示。 图75‑2  单元格内容自动换行 此时，Excel会增加单元格高度，让长文本在单元格中自动换行，以便完整显示。 自动换行能够满足用户在显示方面的基本要求，但做得不够好，因为它不允许用户按照自己希望的方式进行换行。如果要自定义换行，可以在编辑栏中用“软回车”强制单元格中的内容按指定的方式换行。沿用上例，选定单元格后，把光标依次定位在每个逗号后并按组合键，就能够实现如图75‑3所示的效果。 图75‑3  在单元格中使用硬的效果 默认情况下，Excel没有提供设置行间距的功能。如果用户希望在多行显示时设置行间距，可按如下步骤来做。 选定长文本单元格，按组合键调出“单元格格式”对话框。 在“单元格格式”对话框的“对齐”选项卡中，选择“垂直对齐”方式为“两端对齐”，单击“确定”。 适当调整单元格的高度，就可以得到不同的行间距。 效果如图75‑4所示。 图75‑4  为多行文本设置行间距 多数用户都知道可以在工作表中使用背景图片，方法是：单击菜单“格式”→“工作表”→“背景”，然后选择一张图片进行插入，效果如图76‑1所示。 图76‑1  在工作表中使用背景图片 为了让背景与工作表内容更和谐，有时需要关闭工作表的网格线显示，因为网格线会显示在背景图片上方，破坏美感。关闭工作表网络线显示的方法是：单击菜单“工具”→“选项”，在“选项”对话框的“视图”选项卡中，清除“网格线”复选框，单击“确定”按钮，效果如图76-2所示。 图76‑2  不显示网格线时的工作表背景 在默认情况下，背景图片会平铺在整个工作表中，而且无法被打印出来。下面介绍的技巧能够巧妙地突破这些限制，帮助用户更灵活地发挥背景图片的威力。 76‑1  只在特定单元格区域中显示背景 如果不希望背景图片在整个工作表中平铺显示，或者只希望在特定的单元格区域中显示，如图76‑3所示，可以在插入工作表背景以后如下操作。 图76‑3  只在特定单元格区域中显示背景 按组合键全选整张工作表，然后按组合键调出“单元格格式”对话框，在“图案”选项卡中，选择单元格底纹颜色为“白色”。 选定需要背景的单元格区域，然后按组合键调出“单元格格式”对话框，在“图案”选项卡中，选择单元格底纹颜色为“无”。 76‑2  打印背景 如果需要把背景和单元格内容一起打印出来，可以如下操作。 使用Excel的摄影功能把需要打印的单元格区域复制为链接图片，粘贴到一张空白工作表上，有关Excel的摄影功能介绍，请参阅技巧67。 右键单击链接图片，在快捷菜单中选择“设置图片格式”命令。如图76‑4所示。 图76‑4  设置链接图片的格式 在“设置图片格式”对话框中选择“颜色与线条”选项卡，单击“填充”→“颜色”选项的下拉箭头，选择“填充效果”，如图76‑5所示。 图76‑5  设置链接图片的填充效果 在“填充效果”对话框中单击“选择图片”，然后选取一张图片，单击“确定”按钮，如图76‑6所示。 图76‑6  在填充效果中选择图片 现在可以在打印预览中看到设置的表格背景了，如图76‑7所示。 图76‑7  可以被打印的表格背景 定义名称有3种方法，用户在工作中可以针对不同的情况选择使用最适合的方法。 77‑1  插入名称 标准的名称定义方法是：先选择待定义的单元格或单元格区域，然后单击菜单“插入”→“名称”→“定义”（或者按组合键），在“定义名称”对话框中，在“在当前工作簿中的名称”文本框中输入名称字符，单击“确定”按钮，如图77‑1所示。 图77‑1 “定义名称”对话框 在“定义名称”对话框中，用户可以连续定义多个名称，查看名称，以及删除名称。名称一旦被定义，只能修改其引用位置，而不能修改名称的名字。比如，要把图77‑1中所定义的名称“姓名”改为“客户姓名”，只能先添加一个“客户姓名”的名称，再删除名称“姓名”。 77‑2  使用名称框 用户可以利用工作表中的“名称框”快速定义名称。以图77‑2所示的表格为例，要将单元格区域A2∶A5定义名称为“姓名”的具体步骤如下。 图77‑2  使用名称框快速定义名称 选定A2:A5。 把光标定位到名称框中，输入“姓名”，按键。 77‑3  指定名称 使用“指定”的方法可以大批量地进行名称定义。仍以图77‑2所示的表格为例，如果需要把单元格区域A2∶A5定义名称为“姓名”，同时把单元格区域B2∶B5定义名称为“金额”，步骤如下。 选定A1:B5。 单击菜单“插入”→“名称”→“指定”（或按组合键），在“指定名称”对话框中，勾选“首行”复选框，如图77‑3所示。单击“确定”按钮。 图77‑3 “指定名称”对话框 在定义名称的时候，许多用户遇到Excel提示“输入的名称无效”，定义不成功。这是因为，在定义名称时，不是任意字符都可以作为名称的，名称的定义有一定的规则。 1．名称可以是任意字符与数字组合在一起，但不能以数字开头，更不能以数字作为名称，如1PL。同时，名称不能与单元格地址相同，如B3。 如果要以数字开头，可在前面加上下划线，如_1PL。 2．不能以字母R、C、r、c作为名称，因为R、C在R1C1引用样式中表示工作表的行、列。 3．名称中不能包含空格，可以用下划线或点号代替。 4．不能使用除下划线、点号和反斜线（/）以外的其他符号，允许用问号（?），但不能作为名称的开头，如Wange?可以，但?Wage就不可以。 5．名称字符不能超过255个字符。一般情况下，名称应该便于记忆且尽量简短，否则就违背了定义名称的初衷。 6．名称中的字母不区分大小写。 另外，在Excel中，有一些有特殊含义的名称，是用户在使用了诸如高级生产筛选功能以后，由Excel自动定义的。在此介绍两个实用的特殊名称，Print_Titles和Print_Area。 被定义为Print_Titles的区域将成为当前工作表的打印的顶端标题行和左端标题行。比如，将工作表的1∶1定义名称为“Print_Titles”，则工作表在打印时，会在每页中都打印这一行。有关定义打印的顶端标题行和左端标题行的相关内容，请参阅技巧112。 被定义为Print_Area的区域将被设置为工作表的打印区域。有关与此的更多内容，请参阅技巧111 在技巧68中谈到过：无论为单元格应用了何种数字格式，都只会改变单元格的显示内容，而不会改变单元存储的真正内容。因此，Excel没有提供直接的方法来让用户得到自定义数字格式的显示值。如果复制一个有格式的单元格到另一个单元格，用户只能得到原始数值。 但是，仍有多种技巧可以帮助用户达到这一目的，下面介绍一种最简单有效的方法。 选定应用了数字格式的单元格或单元格区域。 快速地连续两次按组合键，Office剪贴板出现在任务窗格中，如图71‑1所示。 图71‑1  连续两次按会让Office剪贴板出现 选定目标单元格或单元格区域，单击Office剪贴板中刚才复制项目旁边的下拉箭头，在弹出的菜单中选择“粘贴”命令，如图71‑2所示。这样，原始区域的数据会被粘贴到目标区域。 图71‑2  从Office剪贴板粘贴数值到目标区域 单击菜单“编辑”→“选择性粘贴”，在“选择性粘贴”对话框中选择“文本”，然后单击“确定”，如图71‑3所示。 图71‑3 “选择性粘贴”对话框中选择“文本” 经过两次粘贴，用户最终可以得到与原始区域显示值完全相同的内容。 在本技巧中，读者可以学习到一些自定义数字格式的经典实例。有关自定义数字格式的创建与应用方法，请参阅技巧68。 70‑1  零值不显示 单击菜单“工具”→“选项”，在“选项”对话框的“视图”中，如果取消勾选“零值”复选框，Excel将不显示当前工作表中的零值。 利用自定义数字格式，用户不但能够实现同样的效果，而且能够让工作表中的一部分单元格不显示零值而其他的单元格仍然显示零值。 G/通用格式;G/通用格式; 上面的格式代码中，第3区段留空，这样就可以使零值显示为空白。当然，对应于正数与负数的第1、2区段，也可以另行定义格式。 70‑2  快速放缩数值 许多用户在工作中常常需要处理很大的数字，利用下面的自定义数字格式，能够在不改变数值本身的同时，把它们进行缩放，如图70‑1所示。 图70‑1  使用自定义数字格式缩放数值 70‑3  智能显示百分比 下面的自定义数字格式只让小于1的数字按“百分比”格式显示，大于等于1的数字使用标准格式显示，同时还让所有的数字排列整齐，如图70‑2所示。 图70‑2  智能显示百分比 格式代码： [<1]0.00%;#.00_% 这段代码有两个区段，第1个区段使用了一个判断，对应数值小于1时的格式，第2个区段则对应不小于1时的格式。在第2个区段中，百分号前使用了一个下划线，目的是保留一个与百分号等宽的空格。 70‑4  显示分数 Excel内置了一些分数的格式，用户还可以使用自定义数字格式得到更多分数的表示方法，比如在显示的时候加上“又”字、加上表示单位的符号，或者使用一个任意的数字作为分母，如图70‑3所示。 图70‑3  更多显示分数的自定义数字格式 有关分数的其他信息，请参阅技巧54。 70‑5  隐藏某些类型的内容 可以使用自定义数字格式隐藏某些类型的输入内容，或者把某些类型的输入内容用特定的内容来替换，如图70‑4所示。 图70‑4  使用自定义数字格式隐藏某种类型的数值 日期与时间是用户在Excel中经常需要处理的一类数值，在不同的报告中，会使用不同的日期格式来表示它们。 在默认情况下，当用户在单元格中输入日期或时间的时候，Excel会使用系统短日期格式来显示。更改系统日期格式设置的方法如下。 单击Windows的开始菜单→“设置”→“控制面板”。 在控制面板”对话框中双击“区域和语言选项”图标。 在“区域和语言选项”对话框中单击“自定义”按钮，然后可以分别对系统日期格式与时间格式进行设置，如图69‑1所示。 图69‑1  系统日期与时间格式的设置 注意 在这里的更改，会影响到多个应用程序而不仅仅是Excel。 Excel也内置了许多日期与时间类的格式，在“单元格格式”对话框的“数字”选项卡中，用户可以在“分类”列表框中选择“日期”或“时间”项下面的某种格式并应用它们如图69-2所示。 用户还可以自定义日期与时间类的数字格式，只是格式代码会简单很多。表69‑1介绍了用于定义日期与时间的代码，关于如何创建自定义数字格式，请参阅68‑2小节。 图69‑2  Excel内置的日期与时间格式 表69‑1                                  日期与时间类自定义数字格式代码 代    码 注    释 m 使用没有前导零的数字来显示月份（1～12） mm 使用有前导零的数字来显示月份（01～12） mmm 使用英文缩写来显示月份（Jan～Dec） mmmm 使用英文全称来显示月份（January～December） mmmmm 显示月份的英文首字母（J～D） d 使用没有前导零的数字来显示日期（1～31） dd 使用有前导零的数字来显示日期（01～31） ddd 使用英文缩写来显示星期几（Sun～Sat） dddd 使用英文全称来显示星期几（Sunday～Saturday） aaaa 使用中文来显示星期几（星期一～星期日） aaa 使用中文显示星期几（一～日），不显示“星期”两字 yy 使用两位数显示年份（00～99） yyyy 使用4位数显示年份（1900～9999） h 使用没有前导零的数字来显示小时（0～23） hh 使用有前导零的数字来显示小时（00～23） m 使用没有前导零的数字来显示分钟（0～59） mm 使用有前导零的数字来显示分钟（00～59） s 使用没有前导零的数字来显示秒钟（0～59） ss 使用有前导零的数字来显示秒钟（00～59） [ ] 显示超出进制的时间（如大于24的小时数或大于60的分与秒） AM/PM 上午/下午 使用12小时制显示小时 图69-3中用上述代码创建了一些常用的实例，其中的“原始数值”是日期或时间的序列值。 图69‑3  日期/时间自定义格式实例 数字格式是单元格格式中最有用的功能之一，专门用于对单元格数值进行格式化。 单击菜单“格式”→“单元格”，或者按组合键，用户可以在“单元格格式”对话框的“数字”选项卡中看到有关数字格式的各项设置，如图68-1所示。通过选择不同的格式设置，甚至应用自定义的格式，能够让单元格的显示更加符合它的表达目标。 图68‑1 “单元格格式”对话框中的“数字”选项卡 注意 无论为单元格应用了何种数字格式，都只会改变单元格的显示形式，而不会改变单元存储的真正内容。反之，用户在工作表上看到的单元格内容，并不一定是其真正的内容，而可能是原始内容经过各种变化后的一种表现形式。如果用户需要在改变格式的同时也改变实际内容，需要借助TEXT或其他函数来实现。 68‑1  Excel内置的数字格式 Excel内置的数字格式多种多样，能够满足用户在一般情况下的需要。下面用几个例子来说明同样的数字被设置为不同的数字格式后，显示效果会有哪些改变。假设一个单元格中有数字1023.4，在默认情况下，Excel不对单元格设置任何数字格式，此时的格式名称为“常规”，数值按照它的真实面貌显示出来。用户在“单元格格式”对话框的“数字”选项卡中可以看到一个“示例”项目，它可以在用户单击“确定”按钮以前就显示当前单元格的数值在应用了指定的数字格式以后的样式，如图68‑2所示。 图68‑2 “常规”数字格式就是没有任何格式 在“分类”列表框中选择“数值”项，然后勾选“使用千位分隔符”复选框，示例内容会发生改变，如图68‑3所示。 图68‑3 “数值”数字格式 在“分类”列表框中选择“货币”项，然后在“货币符号（国家/地区）”列表框中选择“￥中文（中国）”，则示例内容将再次发生改变，如图68‑4所示。 图68‑4 “货币”数字格式 以下是在“分类”列表框中选择“百分比”项后的示例内容，如图68‑5所示。 图68‑5 “百分比”数字格式 在“分类”列表框选择“分数”项，示例内容中的数值以分数形式显示，如图68-6所示。 图68‑6 “分数”数字格式 “分类”列表框中还有许多其他项目，每个项目中又有多个参数可选，读者可以自己进行尝试，以找到符合需要的最佳格式。 68‑2  创建自定义数字格式 如果Excel内置的数字格式无法满足用户在实际工作中的需求，还可以创建自定义数字格式。 在“单元格格式”对话框中，选择“分类”为“自定义”，如图68‑7所示。 图68‑7  自定义数字格式 在“类型”文本框中输入自定义的数字格式代码，或者修改原有格式代码。 单击“确定”即完成。 在“类型”下方的列表框中，已经有许多的格式代码，这些代码就是Excel内置的数字格式所对应的格式代码，或是由用户成功创建的自定义数字格式的格式代码。 如果用户先在“分类”列表框中选定一个内置的数字格式，然后再选定“自定义”项，就能够在“类型”文本框中看到与之对应的格式代码。在原有格式代码的基础上进行修改，能够更快速地得到自己的自定义格式代码。 68‑3  自定义数字格式的代码组成规则 许多Excel用户可能不了解自定义数字格式能够让他们几乎随心所欲地显示单元格数值，或者因为害怕面对长长的格式代码而放弃这个有用的工具。而实际上，自定义数字格式代码并没有想像中那么复杂和困难，只要掌握了它的规则，就很容易读懂和书写格式代码来创建自定义数字格式。 自定义格式代码可以为4种类型的数值指定不同的格式：正数、负数、零值和文本。在代码中，用分号来分隔不同的区段，每个区段的代码作用于不同类型的数值。完整格式代码的组成结构为： “大于条件值”格式;“小于条件值”格式;“等于条件值”格式;文本格式 在没有特别指定条件值的时候，默认的条件值为0，因此，格式代码的组成结构也可视作： 正数格式;负数格式;零值格式;文本格式 用户并不需要每次都严格按照4个区段来编写格式代码，只写1个或2个区段也是可以的。表68‑1中列出了没有按4区段写代码时，代码结构的变化。 表68‑1                                      自定义数据格式代码结构规则 区  段   数 代 码 结 构 1 格式代码作用于所有类型的数值 2 第1区段作用于正数和零值，第二区段作用于负数 3 第1区段作用于正数，第二区段作用于负数，第三区段作用于零值 下面有一个自定义数字格式代码的例子，它针对4种不同类型的数值定义了不同的格式： #,##0.00_;[红色] ‑#,##0.00;[绿色]G/通用格式;"“"@"”" 图68‑8是Excel中应用了这种数字格式的单元格的显示。 图68‑8  自定义数字格式的单元格的显示 68‑4  常用自定义数字格式的代码与示例 表68‑2                                  常用自定义数字格式的代码与示例 代    码 注释与示例 G/通用格式 不设置任何格式，按原始输入的数值显示 # 数字占位符，只显示有效数字，不显示无意义的零值 0 数字占位符，当数字比代码的数量少时，显示无意义的0 从上图可见，可以利用代码0来让数值显示前导零，并让数值固定按指定位数显示。下图是使用#与0组合为最常用的带小数的数字格式 ? 数字占位符，需要的时候在小数点两侧增加空格；也可以用于具有不同位数的分数 . 小数点 % 百分数 , 千位分隔符 E 科学计数符号 \ 显示格式里的下一个字符 续表 代    码 注释与示例 * 重复下一个字符来填充列宽 _ 留出与下一个字符等宽的空格 利用这种格式可以很容易地将正负数对齐 “文本” 显示双引号里面的文本 @ 文本占位符，如果只使用单个@，作用是引用原始文本 如果使用多个@，则可以重复文本 [颜色] 颜色代码 「颜色」可以是[black]／[黑色]、[white]／[白色]、[red]／[红色]、[cyan]／[青色] 、[blue]／[蓝色]、[yellow]／[黄色]、[magenta]／[紫红色]或[green]／[绿色] 要注意的是，在英文版用英文代码，在中文版则必须用中文代码 [颜色 n] 显示Excel调色板上的颜色，n是0～56之间的一个数值 [条件值] 设置格式的条件 有关日期与时间方面的自定义格式方法，请参阅技巧69。 技巧72: 在定义名称的时候，许多用户遇到Excel提示“输入的名称无效”，定义不成功。这是因为，在定义名称时，不是任意字符都可以作为名称的，名称的定义有一定的规则。 1．名称可以是任意字符与数字组合在一起，但不能以数字开头，更不能以数字作为名称，如1PL。同时，名称不能与单元格地址相同，如B3。 如果要以数字开头，可在前面加上下划线，如_1PL。 2．不能以字母R、C、r、c作为名称，因为R、C在R1C1引用样式中表示工作表的行、列。 3．名称中不能包含空格，可以用下划线或点号代替。 4．不能使用除下划线、点号和反斜线（/）以外的其他符号，允许用问号（?），但不能作为名称的开头，如Wange?可以，但?Wage就不可以。 5．名称字符不能超过255个字符。一般情况下，名称应该便于记忆且尽量简短，否则就违背了定义名称的初衷。 6．名称中的字母不区分大小写。 另外，在Excel中，有一些有特殊含义的名称，是用户在使用了诸如高级生产筛选功能以后，由Excel自动定义的。在此介绍两个实用的特殊名称，Print_Titles和Print_Area。 被定义为Print_Titles的区域将成为当前工作表的打印的顶端标题行和左端标题行。比如，将工作表的1∶1定义名称为“Print_Titles”，则工作表在打印时，会在每页中都打印这一行。有关定义打印的顶端标题行和左端标题行的相关内容，请参阅技巧112。 被定义为Print_Area的区域将被设置为工作表的打印区域。有关与此的更多内容，请参阅技巧111 许多用户对名称的理解不够透彻，这有碍于他们在使用Excel的过程中进一步挖掘名称的用途。 Excel中的名称，并不仅仅是为单元格或单元格区域提供一个容易记忆的名字这么简单。在“定义名称”对话框中，如图77‑1所示，“引用位置”文本框中的内容永远是以“=”开头的，而“=”在Excel中是公式的标志。所以，完全可以把名称理解为一个有名字的公式。创建名称，实质上是创建命名公式，只不过这个公式不存放于单元格中而已。 基于以上理论，在名称中不但能够使用单元格引用，还能够使用常量与函数。 79‑1  使用常量 在名称中，可以使用数字、文本、数组，或者简单的

计算公式

六西格玛计算公式下载结构力学静力计算公式下载重复性计算公式下载六西格玛计算公式下载年假计算公式

。使用常量名称的优点是，可简化公式的编写并使工作表更加整洁，并且随时可以修改常量名称的定义，以实现对表格中的大量计算公式快速修改。 示例一 假设有一张表格用于计算公司应缴税额，其中需要频繁引用营业税的税率，此时可以使用一个名称来存储税率。方法如下。 单击菜单“插入”→“名称”→“定义”（或者按组合键）。 在“定义名称”对话框中，在“在当前工作簿中的名称”文本框中输入“Tax”，在“引用位置”文本框中输入“=5%”，单击“确定”按钮，如图79‑1所示。 图79‑1  创建常量名称 下面就可以在工作表中使用刚才创建的常量名称，例如要计算B2单元格中营业额对应的税额，可以使用公式： =Tax*B2 如图79‑2所示。 图79‑2  使用常量名称进行公式计算 如果修改“Tax”的定义，将引用位置改为“=3%”，则表格中所有引用了该名称的公式都会改变计算结果。 示例二 假设在表格中经常会使用到相同的文字，如公司名称，则也可以把它定义为名称来使用，创建文本名称的方法同示例一。如，可以创建一个名称“Co”，定义其引用位置为“=”人民邮电出版社””。 79‑2  使用函数 在如图79‑3所示的表格中，H列用于计算总成绩，它的公式为：“=总成绩”。 图79‑3  使用带有函数的名称做求和计算 原来，该工作簿中已经创建了一个带有求和函数的名称，如图79‑4所示。 图79‑4  创建带函数的名称 注意 定义此名称时，其公式中使用的是相对引用，而非绝对引用。如果名称中使用相对引用，则工作表中引用该名称的公式在求值时，会随活动单元格的位置变化而对不同区域进行计算。 在默认情况下，所有的名称都能够在工作簿中的任何一张工作表中使用。例如，创建一个叫做“Name”的名称，引用Sheet1工作表中的单元格A1，然后在当前工作簿的所有工作表中都可以直接使用这一名称。这种能够作用于整个工作簿的名称被称为工作簿级名称。 在实际工作中，用户可能需要在多张工作表中使用相同的名称，比如，Sheet1是用于记录公司1月份的销售数据，其中的A1∶A100被定义名称为“城市”。Sheet2与Sheet1的表格形式完全相同，用于记录公司2月份的销售数据，如果希望为Sheet2的A1∶A100也定义名称为“城市”，要怎么解决呢？ 在这种情况下，需要创建仅能作用于一张工作表的名称，称为工作表级名称，或局部性名称。 创建工作表级名称的方法有两种，分别是使用名称框和“插入名称”，具体操作步骤与创建工作簿级名称无异，请参阅技巧77。 工作表级名称的特征是：工作表名称＋感叹号＋名称，也就是在工作簿级名称的前面加上工作表名称和感叹号。例如，“城市”是一个工作簿级名称，如果创建一个“Sheet2!城市”的名称，就成为只作用于Sheet2的工作表名称。如果工作表名称中包含有空格，那么在创建名称时必须用单引号把工作表名称引起来。例如，在“销售数据新”工作表中创建工作表级名称，必须写为 '销售数据 新'!城市 工作表级名称所在的工作表中使用该名称，可以不加入工作表名称。例如，在Sheet2中使用名称“Sheet2!城市”，只用写“城市”即可。但是在其他工作表中使用该名称，必须用完整的名称写法“Sheet2!城市”。 在Excel，如果需要重新编辑已定义名称的引用位置，可按组合键，在“定义名称”对话框中选中目标名称，然后把光标定位到“引用位置”文本框，进行修改。 在通常情况下，用户会在编辑名称引用时遇到一些麻烦。比如，图81‑1中显示了一个已经存在的名称，该名称的引用位置内容是：=Sheet1!$A$1:$E$10 图81‑1  待编辑的名称 假设需要把引用位置改为=Sheet1!$A$5:$E$15，操作方法是把光标定位到=Sheet1!$A$之后，按键删除1，输入5，然后使用右箭头键将光标往右移，希望能够把末尾的10改为15。可是，当按下右箭头键时，光标并没有发生移动，引用内容却改变了，如图81‑2所示。 这是因为，“引用位置”文本框默认情况下处于“指向”模式，此时箭头键的作用是在工作表中选定区域而不是移动光标。解决方法是在编辑前把光标定位到“引用位置”文本框，按一下键，切换到“编辑”模式，再进行内容编辑。 图81‑2  编辑时按箭头键的结果 这一技巧在任何出现类似文本框的地方都适用，比如在设置数据有效性的来源，或者在条件格式的公式编辑时。 利用OFFSET函数与COUNTA函数的组合，可以创建一个动态的名称。动态名称是名称的高级用法，能够实现对一个未知大小的区域的引用，此用法在Excel的诸多功能中都能发挥强大的威力。 在实际工作中，经常会使用如图82‑1所示的表格来连续记录数据，表格的行数会随着记录追加而不断增多。 图82‑1  不断追加记录的表格 如果需要创建一个名称来引用C列中的数据，但又不希望这个名称引用到空白单元格，那么就不得不在每次追加记录后都改变名称的引用位置，以适应表格行数的增加。在这种情况下，可以创建动态名称，根据用户追加或删除数据的结果来自动调整引用位置，以达到始终只引用非空白单元格的效果。 创建动态名称的方法如下。 单击菜单“插入”→“名称”→“定义”（或者按组合键）。 在“定义名称”对话框中，在“在当前工作簿中的名称”文本框中输入“Data”，在“引用位置”文本框中输入公式： =OFFSET(Sheet1!$C$4,,,COUNTA(Sheet1!$C:$C) ‑1) 如图82‑2所示。 图82‑2  创建动态名称 单击“确定”按钮。 以上公式先计算B列中除了列标题以外的非空白单元格的数量，然后以C4单元格（首个数据单元格）为基准开始定位，定位的行数等于刚才计算出来的数量。 下面可以在C列以外的单元格中通过计算来验证此名称的引用是否正确，比如在B1中输入公式：=SUM(Data)，如图82‑3所示。 图82‑3  使用动态名称进行计算 如果继续追回记录，名称“Data”的引用位置会自动发生改变，B2中的计算结果能够体现这一点，如图82‑4所示。 图82‑4  动态名称的引用位置自动适应表格的变化 注意 以上公式只能正确计算不间断的连续数据，如果表格中的数据有空白单元格，那么动态名称的引用位置将发生错误。 在Excel中，使用动态名称与ActiveX控件，能够轻松地实现工作表中的图片自动更新的特殊效果。本技巧中将以制作一个简单的职员资料表为例，使职员的相片能够随着姓名的改变而改变。 职员资料表工作簿内含有两张工作表，“资料表”工作表用于显示职员的资料，“图片”工作表用于存储所有职员的相片。“资料表”中的表格如图83‑1所示。 图83‑1  职员资料表格 “图片”工作表中的表格如图83‑2所示，A列是职员的姓名，B列当前是空白，用于存放职员的相片。 图83‑2 “图片”工作表 采用以下步骤增加相片。 单击B1，然后单击菜单“插入”→“图片”→“来自文件”，在“插入图片”对话框中选择相应的相片文件，单击“插入”。 因为相片的原始大小比单元格大，所以需要调整大小。单击相片，然后把光标移动到右下角的圆圈上，当光标变成一个斜线箭头时，往左上方向拖动，如图83‑3所示，直到单元格能容纳整张相片。 图83‑3  调整相片大小 为了能使相片更好地被单元格所容纳，还可以使用以下方法。 单击相片，然后单击绘图工具栏的“绘图”→“自动靠齐”→“对齐网格”，如图83‑4所示。如果绘图工具栏没有显示，可以右键单击工具栏，在弹出的菜单中单击“绘图”项。 图83‑4  设置图片自动靠齐网格 双击相片，在“设置图片格式”对话框的“属性”选项卡中，选择“大小位置，随单元格而变”项，单击“确定”按钮，如图83‑5所示。 图83‑5  设置相片的属性 使用相同的方法为所有职员插入相片，如图83‑6所示。 图83‑6  为所有职员插入相片 按组合键，在“定义名称”对话框中，在“在当前工作簿中的名称”文本框中输入“pic”，在“引用位置”文本框中输入“=OFFSET(图片!$B$1,MATCH(资料表!$A$2,图片!$A$1:图片!$A$4,0) ‑1,0)”，单击“确定”按钮。 切换到“资料表”工作表，右键单击工具栏，在弹出的菜单中选择“控件工具箱”项，在“控件工具箱”工具栏中单击“命令按钮”控件，如图83‑7所示。 图83‑7 “控件工具箱”工具栏 单击B3单元格的左上角，然后往右下方向拖动，画出一个符合单元格大小的命令按钮，如图83‑8所示。 图83‑8  在表格中插入命令按钮 在A2中输入某职员的姓名，如“张三”。单击命令按钮，把光标定位到编辑栏，将原有内容“=EMBED("Forms.CommandButton.1", "")”改为“=pic”。现在，张三的相片就显示出来了，如图83‑9所示。 图83‑9  图像在命令按钮中显示 为了让相片的大小与单元格大小相匹配，可以调整命令按钮的大小，方法同步骤2。调整大小后的显示效果如图83‑10所示。 图83‑10  调整大小后的相片 在A2单元格内输入不同职员的姓名，在B3中就能够自动显示其相片，如图83‑11所示。 图83‑11  相片自动更新 当工作簿中定义了较多的名称时，可以使用以下两种方法快速选择定义名称的区域。 方法1  使用名称框 单击名称框的下拉箭头，在下拉列表中会显示当前工作簿中的所有名称（但不包含常量名称与函数名称），选择其中的一项，就能让该名称所引用的区域处于选择状态。如图84‑1所示。 图84‑1  使用名称框选定名称区域 方法2  使用定位 按键，在“定位”对话框中，会显示当前工作簿中的所有名称（但不包含常量名称与函数名称），双击其中的一项，就能让该名称所引用的区域处于选中状态。如图84‑2所示。 图84‑2  使用定位选定名称区域 如果某个工作簿文件中定义了很多名称，可以创建一份名称表格来查看所有的名称以及它们的引用内容。 选定要存放名称表格的首个单元格，如A1，单击菜单“插入”→“名称”→“粘贴”，或者按键，在“粘贴名称”对话框中，可以看到所有的名称，如图85‑1所示。 单击“粘贴列表”按钮，所有的名称和引用内容都会填入到单元格中，如图85‑2所示。                              图85‑1 “粘贴名称”对话框                             图85‑2  名称表格 如果原有单元格区域存储了数据，则会覆盖原有数据，所以粘贴列表时一定要注意。 在Excel中有一个鲜为人知的特性，就是能够使用图形方式查看定义了名称的区域。 假设有一张工作表中定义了3个名称，分别是Myrange1、Myrange2、Myrange3。 单击菜单“视图”→“显示比例”，在“显示比例”对话框中，选择“自定义”项并把比例值设置为小于40%，如39%，Excel就会以黑色边框显示名称区域，并以蓝色字体显示名称，如图86‑1所示。 图86‑1  以图形方式显示名称 在Excel的“定义名称”对话框中，只能逐个删除名称，这在需要大量删除名称时是非常繁琐的。利用一小段宏代码，可以轻松完成这项工作。 按打开VBA编辑器窗口，单击菜单“插入”→“模块”来插入一个新模块，默认情况下为“模块1”，然后在模块1的代码窗口中输入以下代码： Sub DelNames()     Dim nm As Name     For Each nm In ThisWorkbook.Names         nm.Delete     Next nm End Sub 最后，按F5键来运行这段代码，就能一次性删除工作簿中的所有名称。 利用Excel的条件格式功能，用户可以为自己的数据区域设置精美而又宜于阅读的格式。这种设置效果不同于单元格格式，它是完全动态的，无论在数据区域中增加或删除行、列，格式都会自动进行相应地调整，保持原有的风格。 88‑1  国际象棋棋盘式底纹 要设置如图88‑1所示的国际象棋棋盘式底纹，方法如下。 图88‑1  国际象棋棋盘式底纹 选择A1:F19，单击菜单“格式”→“条件格式”。 在“条件格式”对话框中，单击“条件1(1)”的下拉箭头，在列表中选择“公式”项，在右边的文本框中输入公式： =MOD(ROW()+COLUMN(),2) 如图88‑2所示。 图88‑2  设置条件格式中的公式 单击“格式”按钮，在“单元格格式”对话框的“图案”选项卡中选择单元格底纹颜色为淡蓝，单击“确定”按钮，如图88‑3所示。 图88‑3  设置目标格式的图案 单击“条件格式”对话框的“确定”按钮。 该条件格式的公式用于判断行号与列号之和除以2的余数是否为0。如果为0，说明行数与列数的奇偶性相同，则填充单元格为浅绿色，否则就不填充。 在条件格式中，公式结果返回一个数字时，非0数字即为TRUE，0和错误值为FALSE。因此，上面的公式也可以写为： =MOD(ROW()+COLUMN(),2)<>0 根据此思路还可以设置双色的国际象棋棋盘式底纹，方法如下。 在“条件格式”对话框中单击“添加”按钮，单击“条件2(2)”的下拉箭头，在列表中选择“公式”项，在右边的文本框中输入公式： =MOD(ROW()+COLUMN(),2)=0 单击“条件2(2)”的“格式”按钮，在“单元格格式”对话框的“图案”选项卡中选择单元格底纹颜色为橙色，单击“确定”按钮，如图88‑4所示。 图88‑4  设置第二种颜色 单击“条件格式”对话框的“确定”按钮，最后效果如图88‑5所示。 图88‑5  双色的国际象棋棋盘式底纹 88‑2  奇偶行不同底纹 要设置如图88‑6所示的奇偶行不同底纹，方法如下。 图88‑6  奇偶行不同底纹 选择A1:F19，单击菜单“格式”→“条件格式”。 在“条件格式”对话框中，单击“条件1(1)”的下拉箭头，在列表中选择“公式”项，在右边的文本框中输入公式： =MOD(ROW(),2) 单击“格式”按钮，在“单元格格式”对话框的“图案”选项卡中选择单元格底纹颜色为淡蓝，单击“确定”按钮。 单击“条件格式”对话框的“确定”按钮。 此条件格式的公式用于判断当前行是否为偶数行，如果是，则填充颜色到单元格中，否则就不填充。 根据此思路，还可以设置奇偶行为不同颜色，原理与前文中设置双色的国际象棋棋盘式底纹相同，在此不再赘述。 如果希望设置格式为每3行应用一次底纹，可以使用公式： =MOD(ROW(),3)=1 如果希望设置奇偶列不同底纹，只要把公式中的ROW()改为COLUMN()即可，如： =MOD(COLUMN(),2) 在技巧89 中讲述了如何利用条件功能实现数据表的间隔底纹效果，这种效果是完全动态的，无论在数据表中插入行或者删除行，其风格都不会改变。但是有一种情况会例外。假设如图89‑1所示的数据表，已经设置了间隔底纹的效果，如果对此数据表进行自动筛选操作，并设置A列的 图89‑1  设置了间隔底纹效果的数据表 筛选条件为“A001”，则间隔底纹效果就被破坏了，如图89‑2所示。 图89‑2  自动筛选操作对间隔底纹效果的影响 如果不希望自动筛选对间隔底纹效果产生不良影响，可以通过下面的方法来实现。 单击A2并拖动光标到C19以选定区域A2:C19，单击菜单“格式”→“条件格式”，在“条件格式”对话框中单击“删除”按钮，在弹出的“删除条件格式”对话框中，勾选“条件1(1)”和“条件2(2)”复选框，单击“确定”按钮，如图89‑3所示。 图89‑3  删除条件 在“条件格式”对话框中，单击“条件1(1)”的下拉箭头，在列表中选择“公式”项，在右边的文本框中输入公式： =MOD(SUBTOTAL(3,A$2:A2),2)=0 单击“格式”按钮，在“单元格格式”对话框的“图案”选项卡中选择单元格底纹颜色为海绿，单击“确定”按钮。 在“条件格式”对话框中单击“添加”按钮，单击“条件2(2)”的下拉箭头，在列表中选择“公式”项，在右边的文本框中输入公式： =MOD(SUBTOTAL(3,A$2:A2),2)=1 单击“条件2(2)”的“格式”按钮，在“单元格格式”对话框的“图案”选项卡中选择单元格底纹颜色为淡蓝，单击“确定”按钮，如图89‑4所示。 图89‑4 “条件格式”对话框 单击“条件格式”对话框的“确定”按钮。 经过这样设置的间隔底纹，就不会再受到自动筛选或者隐藏行操作的影响了，如图89‑5所示。 图89‑5  保持不变的间隔底纹 本技巧利用SUBTOTAL函数来判断可见行的奇偶次序，有关此函数的详细内容，请参阅技巧212。另外，在条件格式的公式中，使用的是相对引用，这会让条件格式的判断对象随着行的变化而自动改变，达到“只判断当前行的序号”的目的。 注意 在条件格式的公式中使用相对引用时，一定要根据活动单元格的位置来使用相匹配的引用。如果一次性对整体区域设置条件格式，活动单元格是选定区域操作中第一次选择的单元格。 Excel的多窗口特性能够帮助用户比较不同区域的数据（具体内容请参阅技巧50），但是当需要比较的区域较大时，人工比较不但费时而且准确率不高。此时如果利用条件格式功能，则能快速而又准确地完成对比工作。 在如图90‑1所示的工作表中，源数据和校验数据分别位于A2∶B21和D2∶E21，如果希望标记出与源数据不匹配的校验数据，方法如下。 图90‑1  需要对比的两个数据区域 单击D2并拖动光标到E21以选定区域D2:E21，单击菜单“格式”→“条件格式”。 在“条件格式”对话框中，单击“条件1(1)”的下拉箭头，在列表中选择“单元格数值”项，运算符选择“不等于”，在右边的文本框中输入：=A2 单击“格式”按钮，在“单元格格式”对话框的“图案”选项卡中选择单元格底纹颜色为淡蓝，单击“确定”按钮，如图90‑2所示。 图90‑2  设置条件格式对比数据 单击“条件格式”对话框的“确定”按钮，关闭对话框。 从图90‑3中可以看到，所有不匹配源数据的校验数据都已经被标出。 图90‑3  不符的数据被条件格式做出标记 在技巧90中介绍了利用条件格式快速对比不同区域中的数值的一种方法。然而在实际工作中，需要对比的数据可能并不是位置一一对应的。例如，在如图91‑1所示的表格中，需要把与账号二不匹配的账号一标记出来，但两列账号的排列顺序并不相同，对于这种情况，条件格式的设置要相对复杂一些。 图91‑1  待检查的表格 单击A2并拖动光标到A11以选定区域A2:A11，单击菜单“格式”→“条件格式”。 在“条件格式”对话框中，单击“条件1(1)”的下拉箭头，在列表中选择“公式”项，在右边的文本框中输入公式： =OR(EXACT(A2,B$2:B$11))=FALSE 单击“格式”按钮，在“单元格格式”对话框的“图案”选项卡中选择单元格底纹颜色为淡蓝，单击“确定”按钮。 单击“条件格式”对话框的“确定”按钮。 以上操作完成后，账号一中所有与账号二不匹配的内容都会被标记出来，如图91‑2所示。 图91‑2  与账号二不匹配的账号一都被标记出来了 在上述公式中，EXACT函数用于比较两个文本字符串是否完全相同，如完全相同则返回TRUE，否则返回FALSE。 使用以下3个公式，也能够完成同样的任务： =ISNA(MATCH(A2,B$2:B$11,)) =ISNA(VLOOKUP(A2,B$2:B$11,1,)) =NOT(OR(A2=B$2:B$11)) 有的读者可能更熟悉COUNTIF函数，但是在这里，如果使用公式=COUNTIF(B$3:B$12,A3)，会得到错误的结果。因为当字符型数字的长度超过15位时，COUNTIF函数会把从第16位开始的数字都当作0计算。 如果使用COUNTIF函数，可以按下面的方法来创建公式： =COUNTIF(B$3:B$12,A3&"*")=0 这样就可以强制COUNTIF使用文本方式进行计数。 在实际工作，常常需要在表格中使用小计行或小计列来汇总某类数据，如图92‑1所示。 图92‑1  包括小计行与小计列的表格 使用条件格式功能，能够快速地为所有小计行与小计列设置醒目的格式。 单击A1并拖动光标到H11以选定区域A1:H11，单击菜单“格式”→“条件格式”。 在“条件格式”对话框中，单击“条件1(1)”的下拉箭头，在列表中选择“公式”项，在右边的文本框中输入公式： =($A1="小计")+(A$1="小计")>0 单击“格式”按钮，在“单元格格式”对话框的“字体”选项卡中选择“字形”为“加粗”，颜色为蓝色，单击“确定”按钮。如图92‑2所示。 图92‑2  设置小计的格式 单击“条件格式”对话框的“确定”按钮，如图92‑3所示。 图92‑3 “条件格式”对话框 现在，整张表格中的“小计”行与列都自动以醒目的格式显示了，如图92‑4所示。 图92‑4  自动醒目的小计 上述的公式中使用的是相对引用，目的是判断A列与第1行中是否会出现“小计”字样，如果出现，则在相同的行、列中应用醒目的格式。 根据逻辑值与数值的转换关系，以及逻辑值计算的原理，上述公式也可以写为 =($A1="小计")+(A$1="小计") 或者 =OR(($A1="小计"),(A$1="小计")) 很多用户喜欢使用Excel制作工作日历，如图93‑1所示的是某公司人力资源部的工作日历的一部分。 图93‑1  工作日历表格 在这张表格中，有些工作是需要在周休日开展的，如果能把所有的周休日都醒目地标识出来，就能够避免一些时间安排上的问题了。使用条件格式可以方便地实现这个要求，方法如下。 单击A2并拖动光标到A17以选定区域A2:A17，单击菜单“格式”→“条件格式”。 在“条件格式”对话框中，单击“条件1(1)”的下拉箭头，在列表中选择“公式”项，在右边的文本框中输入公式： =WEEKDAY(A2,2)>5 单击“格式”按钮，在“单元格格式”对话框的“字体”选项卡中选择“字形”为“加粗”，颜色为红色，单击“确定”按钮。 单击“条件格式”对话框的“确定”按钮，如图93‑2所示。 图93‑2 “条件格式”对话框 设置完毕后，表格中所有周六、周日的日期都会以粗体红字表示，如图93‑3所示。 上述公式使用了函数WEEKDAY计算活动单元格的星期，如果大于5即是周六或周日。根据这个思路，还可以利用条件格式分别把周六和周日用不同的颜色来显示，读者可以自己尝试一下。 图93‑3  突显周休日的工作日历 对于许多经常使用Excel排序功能的用户来说，“排序”对话框只允许一次性设置3个关键字的限制实在难以满足需要。在如图121‑1所示的工作表中，有一个5列数据的表格，如果需要按从左向右为关键字次序来排序，就成了一个难题，当单击菜单“数据”→“排序”后，在如图121‑2所示的“排序”对话框中，无法设置“完成日期”和“责任人”字段。 图121‑1  包含5列的表格 图121‑2  只能设置3个关键字的“排序”对话框 事实上，Excel的排序的关键字并不受上图中这个对话框的限制，是可以按任意数量的列作为关键字来排序的。用户只需要把握一个原则，就可以实现对超过3列的数据进行排序：在多列表格中，先被排序过的列，会在后面按其他列为标准的排序过程中，尽量保持自己的序列。 所以，对多列进行排序时，要先排序较次要（或者称为排序优先级较低）的列，后排序较重要（或者称为排序优先级最高）的列。 在本例中，因为列数并不多，甚至可以放弃使用“排序”对话框，而改用工具栏上的“升序排列”按钮，依次对“责任人”、“完成日期”、“开始日期”、“项目”、“类别”列进行排序。 另外，也可以通过使用两次“排序”对话框来完成排序：在第一次使用时，将“完成日期”作为主关键字，将“责任人”作为次要关键字；在第二次使用时，按图121‑2所示进行设置。 最近完成的排序效果如图121‑3所示。 图121‑3  多列排序最后效果 当把表格的数据按数字或字母顺序进行排序时，Excel的排序功能能够很好地工作，但是如果用户希望把某些数据按照自己的想法来排序，在默认情况下，Excel是无法完成任务的。 在如图122‑1所示的表格中，记录着公司职工的津贴数据，其中B列是所有职工的职务，现在需要按职务大小来排序整张表格。 图122‑1  职工津贴表 此时，如果用户以B列为标准进行排序，无论是升序排列还是降序排列，都无法得到令人满意的结果。图122‑2显示了对B列按升序排列的结果，从图中可以看出，Excel实际上是按照首个字的字母顺序来排序的。那么，如何才能让Excel按照用户所希望的方式来排序呢？ 首先，用户需要告诉Excel职务大小的顺序，方法是创建一个自定义序列。有关自定义序列的更多技巧，请参阅技巧51。在本例中，用户需要创建一个有关职务大小的序列，如图122‑3所示。然后，使用下面的方法，能够进行职务大小排序。 图122‑2  默认情况下的排序结果 图122‑3  职务大小序列 单击数据区域中任意单元格，如A2。 单击菜单“数据”→“排序”，出现“排序”对话框。 在“排序”对话框中，选择“主要关键字”为“职务”，排序方式为升序，如图122‑4所示。 图122‑4  在“排序”对话框中设置 主要关键字为“职务” 单击“排序”对话框中的“选项”按钮，在出现的“排序选项”对话框中，选择“自定义排序次序”列表框中的职务序列，如图122‑5所示，单击“确定”按钮。 图122‑5  设置自定义排序次序 单击“确定”按钮，关闭“排序”对话框。 如此操作后，表格中的数据就按照职务由大到小的顺序进行排列了，最后结果如图122‑6所示。 图122‑6  按职务大小排序的最后结果 注意 在使用自定义排列次序进行排序时，此次序将应用到“排序”对话框的3个关键字中，而无法为每个关键字设置单独的自定义次序。如果表格中每列都需要使用不同的自定义排列次序，则需要通过多次使用“排序”对话框，每次选择一种自定义排列次序。排序的顺序是先排序较次要（或者称为排序优先级较低）的列，后排序较重要（或者称为排序优先级最高）的列。 在默认情况下，Excel对中文字的排序方式是按照“字母”顺序的，以中文姓名为例，字母顺序即按姓的拼音的首字母在26个英文字母中出现的顺序进行排列，如果同姓，则依次计算名的第二、第三字。图123‑1中显示的表格包含了按字母顺序排列的姓名数据。 图123‑1  按字母顺序排列的姓名 然而，在中国人的习惯中，常常是按照“笔划”的顺序来排列姓名的。这种排序的规则是：按姓字的划数多少排列，同划数内的姓字按起笔顺序排列（横、竖、撇、捺、折），划数和笔形都相同的字，按字形结构排列，先左右、再上下，最后整体字。如果姓字相同，则依次看名第二、三字，规则同姓字。 在Excel中，已经考虑到了这种需求。以上面的表格为例，使用姓氏笔划的顺序来排序的方法如下。 单击数据区域中任意单元格，如A2。 单击菜单“数据”→“排序”，出现“排序”对话框。 在“排序”对话框中，选择“主要关键字”为“姓名”，排序方式为升序。 单击“排序”对话框中的“选项”按钮，在出现的“排序选项”对话框中，单击方法区域中的“笔划排序”单选按钮，如图123‑2所示，单击“确定”按钮。 图123‑2  设置按笔划排序 单击“确定”按钮，关闭“排序”对话框。 最后的排序结果如图123‑3所示。 图123‑3  按笔划排序的结果 注意 Excel中的按笔划排序并没有完全按照前文所提到的习惯来作为规则。对于相同笔划数的汉字，Excel按照其内码顺序进行排列，而不是按照笔划顺序进行排列。对于简体中文版用户而言，相应的内码为代码页936（ANSI/OEM - GBK）。 许多用户都一直认为Excel只能按列进行排序，而实际上，Excel不但能按列排序，也能够按行来排序。下面通过一个例子来介绍具体内容。 在如图124‑1所示的表格中，A列是列标题，其他的数据是以行来组织的，现在需要依次按“类别”和“项目”来排序。对于这样的表格，按列来排序是没有意义的，必须按行来排序。 图124‑1  以行来组织数据的表格 选定单元格区域B2:I5。 单击菜单“数据”→“排序”，出现“排序”对话框。 单击“排序”对话框中的“选项”按钮，在出现的“排序选项”对话框中，单击方向区域中的“按行排序”单选按钮，如图124‑2所示，单击“确定”按钮。 图124‑2  设置按行排序 在“排序”对话框中，关键字列表框中的内容此时都发生了改变。选择“主要关键字”为“行1”，排序方式为升序，再选择“次要关键字”为“行2”，排序方式为升序，如图124‑3所示，单击“确定”按钮。 图124‑3  设置按行排序后，关键字的 列表框内容发生了改变 图124‑4显示了按行排序的最后结果。 图124‑4  按行排序的最后结果 注意 在使用按行排序时，不能像使用按列排序时一样选定目标区域。因为Excel的排序功能中没有“标题列”的概念，所以如果选定全部数据区域再按行排序，标题列也会参与排序，出现意外的结果。因此，在本例的步骤1中，只选定标题列以外的数据区域。 在实际工作中，用户有时候需要按照字符的数量进行排序。例如在制作一份歌曲清单时，如图125‑1所示，人们习惯按照歌曲名字的字数来把它们分门别类。 图125‑1  歌曲清单 但是，Excel并不能直接按字数排序，如果要达到目的，需要先计算出每首歌曲名字的字数，然后再进行排序。 在C1输入“字数”，作为C列的列标题。 在C2输入公式“=LEN(B2)”，然后把此公式复制到C3:C16单元格区域。 单击C2，单击工具栏上的“升序排列”按钮。 这样，就完成了按字数排列歌名的任务，如图125‑2所示。如果必要，可以删除C列。 图125‑2  按字符数量排序后的歌曲清单 本技巧介绍的利用辅助列先计算现有数据，然后按辅助列的值进行排序的方法，是在需要按特殊属性排序时常用的一种解决方法，可以适用于多种类似的排序。 在实际工作中，用户经常会通过为单元格设置背景色或者字体颜色来标注表格中较特殊的数据，但是却无法对有颜色的单元格做进一步操作，比如排序和筛选，因为在Excel中，无论使用菜单命令还是工作表函数都无法得到单元格的颜色信息。 此时，需要借助Excel的宏表函数，才能够得到单元格的颜色信息。有关宏表函数的更多知识，请参阅第22章宏表函数。 在如图126‑1所示的表格中，部分日期数据使用了单元格背景色，部分任务数据设置了字体颜色，下面以此表格为例，介绍如何按照颜色排序。 图126‑1  使用了单元格背景色和字体颜色的表格 分别在C1、D1中输入文字Color1、Color2。 选定C2，按组合键，在“定义名称”对话框的“在当前工作簿中的名称”文本框中输入Color1，然后在引用位置输入： =GET.CELL(63,'06'!A2)+RAND()*0 单击“确定”按钮关闭“定义名称”对话框。 选定D2，按组合键，在“定义名称”对话框的“在当前工作簿中的名称”文本框中输入Color2，然后在引用位置输入： =GET.CELL(24,'06'!B2)+RAND()*0 如图126‑2所示。单击“确定”按钮关闭“定义名称”对话框。 图126‑2  使用宏表函数求颜色值 在C2中输入“=Color1”，在D2中输入“=Color2”。 选定C2∶D2，往下拖曳到C17∶D17，完成对公式的复制。 单击C2，单击工具栏上的“降序排列”按钮，就能够以日期列的单元格背景色来排序，结果如图126-3所示。 图126‑3  按颜色值排序表格数据 如果需要以任务列的字体颜色排序，则应以D列为标准进行排序。 在本例中，使用了Excel宏表函数在C列和D列分别求出A列每个单元格的背景色对应的值和B列每个单元格的字体颜色对应的值，从而使按颜色排序成为可能。 在使用Excel的筛选功能时，也可以按照单元格的背景色或字体颜色来筛选数据，原理相同，在此就不赘述了。 注意 宏表函数只能计算手工设置的单元格背景色和字体颜色的值，而不能计算由条件格式功能产生的颜色。 注意 用于计算颜色的宏表函数无法自动重算。因此，当单元格背景色或字体颜色发生改变后，用户需要按键执行手动重算，才能更新宏表函数的计算结果。 在某些情况下，用户并不希望按照既定的规则来排序数据，而是希望数据能够“乱序”，也就是对数据进行随机排序。 在如图127‑1所示的表格中，有一份歌曲清单，现在需要使用随机排序来改变它们的先后次序。 图127‑1  按歌手姓名排序的歌曲清单 在C1中输入“次序”。 在C2中输入公式“=RAND()”，并拖曳到C16以完成对公式的复制。 单击C2，单击工具栏上的“降序排列”按钮，就能够对歌曲清单进行随机排序，结果如图127‑2所示。 图127‑2  随机排序的歌曲清单 事实上，因为RAND函数是易失性函数，所以每次排序都将改变其计算值，从而改变排序次序，实现每次排序都可能不一样的结果。有关RAND函数的更多内容，请参阅技巧195。 在日常工作中，用户的表格经常会包含由字母和数字混合的数据，在对这样的数据排序时，结果总是令人无法满意，如图128‑1所示。 图128‑1  排序结果无法令人满意 通常情况下，用户希望的规则是先比较字母的大小，再比较数字的大小，但Excel是按照对字符进行逐位比较来排序的。因此在上图中，A7排在第5位，而不是第1位。 如果希望让Excel改变排序的规则，需要将数据做一些改变。 在B1中输入公式： =LEFT(A1,1) & RIGHT("000" & RIGHT(A1,LEN(A1) ‑1),3) 将B1的公式复制到B2:B10。 单击B2，单击工具栏上的“升序排列”按钮。 这样，A列中的数据就按照用户所希望的那样完成了排序，如图128‑2所示。 图128‑2  正确的排序结果 当用户反复对表格进行各种排序以后，表格的原有次序将被打乱，如果在排序后做了其他某些必要的操作，就不方便再使用Excel的撤消功能。这时，如果需要让表格返回到排序前的状态，就不那么容易了。 如果在排序前就预先知道可能需要保持表格在排序前的状态，可以在表格的左侧或右侧插入一列空白列，并填充一组连续的数字。在如图129‑1所示的表格中，A列就是新插入的列，用于记录表格的现有次序。 图129‑1  使用辅助列记录表格的当前次序 现在，无论对怎样的表格进行排序，只要最后以A列为标准做一次升序排序，就能够返回表格的原始次序。 130‑1  没有正确选择数据区域，而自动选择的区域中包含有空格 如果需要排序的数据区域不是标准的数据列表，并且包含空格，那么若在排序前没有手工先选定整个数据区域，而是只选定数据区域中的任意单元格，排序结果将很可能不正确。因为在这种情况下，Excel并不总是能为用户自动选择正确的数据区域。 130‑2  内存不足的情况 Excel是一款桌面型的电子表格软件，当处理过于庞大的数据量时，其性能会低于专业的数据库软件，并完全依赖于电脑的硬件配置。因此，当排序或筛选的数据区域过大时，Excel可能会提示用户“内存不足”。此时，可以采用以下的解决方法。 1．增加电脑的物理内存。 2．优化电脑的性能，如关闭暂时不需要的其他程序，清理系统分区以保留足够剩余空间，删除Windows临时文件等。 3．减小排序的数据区域。 130‑3  数据区域中包含有格式化为文本的数字 当数据区域中包含有格式化为文本的数字时，排序结果将会错误。在如图130‑1所示的表格中，A5∶A10是文本型数字，此时按编号进行排序，则较小的编号可能会排到较大的编号后面去。 图130‑1  排序错误的表格 要想使排序结果正确，必须先将文本型数字转换为数值型数字，方法如下。 单击工作表中任意空单元格，按组合键。 选定A5:A10，单击菜单“编辑”→“选择性粘贴”。 在“选择性粘贴”对话框中，选择“加”项，然后单击“确定”按钮。 130‑4  Excel提示“不同的单元格格式太多” 如果用户工作簿中存在着4000种以上的单元格格式组合，那么在执行许多命令时（包括排序），Excel都会提示“不同的单元格格式太多”。 这里所谈到的单元格格式组合，指工作簿中任意单元格，如果所设置的单元格格式与其他单元格有任何细微的差别，即成为一种单元格格式组合。比如，有两个单元格，都设置单元格格式为红色宋体12号字，如果其中一个单元格的数字格式使用2位小数，而另一个单元格的数据格式不使用小数，则两个单元格各使用一种单元格格式组合。 在一般情况下，4000种的上限足够用户设置数据区域，但如果某个工作簿文件经过多人之手，长年累月使用，并且有很多内容是从别的文件中Copy而来，也可能最终导致超出限制。 解决的方法是简化工作簿的格式，使用统一的字体、图案与数字格式。有关此问题的详细说明，请参阅http://support.microsoft.com/?kbid=213904。 130‑5  排序区域包含合并单元格 如果在排序的时候，Excel提示“此操作要求合并单元格都具有相同大小”，则说明数据区域中包含合并单元格，并且合并单元格的大小各不相同。例如图130‑2所示的表格，A列的数据是由合并单元格组成，而B列和C列都没有合并单元格。此时如果对整个数据区域的排序操作将无法进行。 图130‑2  包含合并单元格的数据区域 而在如图130‑3所示的表格中，同行次的合并单元格的大小完全相同，因此可以正常排序。 图130‑3  合并单元格大小相同的数据区域 对于图130‑2所示的表格，需要用户取消合并所有已合并的单元格，然后才能排序。 在实际工作中，用户常常需要把重要的工作表进行保护，以防止工作表内容被意外更改。如果在保护工作表的同时，又希望对工作表中的数据使用自动筛选功能以便进行一些数据分析工作，则需要进行一些设置。以图131‑1所示的工作表为例。 图131‑1  原始表格 131‑1  Excel 2002及以上版本 从Excel 2002开始，工作表保护功能中允许用户设置在保护工作表的同时所允许进行的部分操作类型，其中就包括自动筛选。 选中A1:C17中的任意单元格。 单击菜单“数据”→“筛选”→“自动筛选”，使表格进入自动筛选模式。 单击菜单“工具”→“保护”→“保护工作表”，在“保护工作表”对话框的“允许此工作表的所有用户进行”列表框中，勾选“使用自动筛选”选项，如图131‑2所示。 图131‑2  在“保护工作表”对话框中 勾选“使用自动筛选”选项 如果需要，可以在“取消工作表保护时使用的密码”文本框中输入保护工作表的密码，最后单击“确定”，关闭“保护工作表”对话框。 现在，虽然工作表处于受保护状态，不能对任何单元格进行修改，但仍然可以使用“自动筛选”功能，如图131‑3所示。 图131‑3  受保护的工作表仍然可以使用“自动筛选”功能 注意 步骤2与步骤3的操作顺序一定不能颠倒，即用户必须在保护工作表以前就让数据区域或列表进入自动筛选模式。否则，如果先执行保护工作表命令，则无法开启或关闭自动筛选模式，“自动筛选”命令也将是灰色的。 131‑2  Excel 2000及更低版本 在Excel 2000或更低版本中，保护工作表功能没有提供与自动筛选相关的设置，但是用户仍然可以借助宏代码来进行设置。假设数据保存在Sheet1中。 选中A1:C17中的任意单元格。 单击菜单“数据”→“筛选”→“自动筛选”，使表格进入自动筛选模式。 按打开VBA编辑器窗口，按组合键显示工程资源管理器窗口，双击其中的ThisWorkbook，在右边的代码窗口中输入以下代码： Private Sub Workbook_Open()   Worksheets("sheet1").Protect Password:="pwd", userInter Faceonly:=True   Worksheets("sheet1").EnableAutoFilter = True End Sub 保存并关闭工作簿文件。 当再次打开工作簿文件时，工作表Sheet1将被保护，但是自动筛选功能仍然可用。用户可以根据自己的需要，修改上述代码中的工作表名称与密码（pwd）。 与在Excel 2002及以上版本中相似，用户必须先让数据表进入自动筛选模式，再运行宏代码执行工作表保护。