Excel 2010操作与技巧

内容简介

《Excel 2010操作与技巧》是“Excel疑难千寻千解”丛书之一，展示Excel 2010新界面下的操作方法与技巧，对众多用户在实际套用中遇到的疑难问题进行答疑解惑。《Excel 2010操作与技巧》侧重于“解决问题”，主要展示诸多常见疑难的解决方案。《Excel 2010操作与技巧》共包含217个疑难案例，每个疑难都包含一个或多个解决方案，并融入了一个或多个知识点的讲解，内容涵盖档案管理与个性化设定、列印、单元格和工作表操作、排序与筛选、条件格式、数据有效性、名称、数据透视表以及方案管理器、单变数求解、规划求解、模拟运算表等数据分析工具的套用技巧、综合套用等。阅读《Excel 2010操作与技巧》，可以解决工作中的诸多疑难杂症，有助于提升对Excel的操作能力，提高工作效率。

专业书评

“Excel疑难千寻千解”丛书依託于功能更趋完善的Excel2010版本，用详尽的文字和图片介绍了Excel2010各方面的知识和套用，非常适合初学者和有一定基础的读者进阶学习。书中示例贴近实际，有利于读者将所学知识与自己的工作结合起来，进而提高数据分析能力和工作效率。——Excelpx-com 站长 赵志东（网名：兰色幻想）不论是在社区中帮助网友解决问题，还是将Excel 方方面面的知识点归纳整理成书，ExcelTip-Net 一直都做得很好，很有自己的特色！——Officefans-net 站长 谢汝祥（网名：andyxie）Excel 的博大精深已令活跃于网路社区中的众多高手深有体会，一直以来人们都在探寻关于Excel 的最佳学习方法和途径，希望通过其获取最新而且实用的Excel 套用技术，相信本书的读者能得到一份这方面的满意答卷。——微软全球最有价值专家MVP 陈 军（网名：chenjun）深入浅出的讲解，引人入胜的描述，经典的场景和贴心的知识拓展，多思路并举的操作方法，不知不觉中便使人沉浸于Excel无穷的技巧中，让人钦佩作者的独具匠心和非凡智慧。无论是Excel 新手，或是Excel 高手，相信本书都可给人以耳目一新的感觉。——微软全球最有价值专家MVP 周建平（网名：守柔）“Excel 疑难千寻千解”丛书视角独特，贴近用户，由点及面，举一反三，非常适合初学者深入学习，是不可多得的Excel 学习宝典。——微软全球最有价值专家MVP 杨彬（网名：BIN_YANG168）虽然我对Excel 2010 已经不陌生了，但打开本书，仍有一种全新的感觉。本书一改Office帮助的那种“选单式说教”，而是将日常运用过程中经常会遇到的疑难问题一一列出并详加说明解决方案，有的甚至还列出多项解决方案，这无疑给使用者带来巨大的方便。本书不仅可以作为Excel 2010 爱好者学习的自学教材，还可以作为经常使用Excel 2010 办公人士的“解疑字典”，在遭遇“奇怪现象”时可以直接找到解决问题的捷径。——微软全球最有价值专家MVP 方洁影（网名：小妖）本套丛书立意新颖，内容充实，按照“提出问题→解决问题→扩展问题”的模式，图文并茂地对大量实际工作中的常见Excel 问题进行了细緻的描述和深入浅出的讲解。授人以“鱼”，同时授人以“渔”，适合从基础到高级的Excel 用户和程式设计师阅读，使您的工作事半功倍，得心应手！——微软全球最有价值专家MVP 杨志宁（网名：northwolves）

媒体推荐

在浏览过琳琅满目、各种类型各种专题的Excel图书之后，在领略过百科全书式的详细解析或是武功秘籍般的招式演绎之后，依然有这样一套“疑难乾寻千解丛书”能够让人眼前一亮。刮目相看的原因在于，这套书真正把立场和视角建立在读者基础之上——每一个问题都来源于Excel用户所可能真实面对的疑惑和困扰，每一个解答可能正是许多坐在计算机前束手无策的朋友正在搜寻的目标。——微软全球最有价值专家MVP 方骥

图书目录

第1章 档案管理与个性化设定11.1 档案管理1疑难1 如何防止他人对工作簿内工作表的增删1保护工作簿“结构2疑难2 如何加密文档3保护工作簿“视窗” 3在“信息”选单中加密文档4疑难3 工作簿可能有哪些个人信息，如何清除6“文档检查器”的使用6在“档案属性”中删除信息8疑难4 如何取消保存时的兼容性检查9兼容性检查的取消9兼容性检查的恢复9疑难5 为什幺档案无法保存为Excel 97-2003 工作簿格式10在“档案阻止设定”中设定阻止“保存” 10疑难6 最近使用的文档列表数如何更改11修改“最近使用的文档”数目12疑难7 如何将某些文档固定在最近使用的文档列表中13图钉图示的使用13疑难8 如何隐藏最近使用过的文档列表15取消文档的固定15隐藏最近使用过的文档列表151.2 个性化设定Excel17疑难9 如何设定新建工作簿的初始字型17设定新建工作簿的初始字型17疑难10 为什幺公式下拉複製后计算结果都一样19自动重算 19疑难11 如何取消某种单元格错误检查提示20手动重算 20忽略公式错误检查21“错误检查”选项的设定21取消后台错误检查22疑难12 如何设定档案默认的保存类型23设定档案默认的保存类型23疑难13 如何设定档案默认的保存位置24设定档案默认的保存位置24疑难14 如何修改按下【Enter】键后活动单元格的移动方向26设定按下【ENTER】键后活动单元格移动的方向26疑难15 如何添加自定义序列列表27删除添加的“自定义序列”28疑难16 如何在输入数据后自动添加小数点29自动插入小数点30疑难17 如何显示或隐藏所有批注31批量显示或隐藏批注31疑难18 如何使Excel 不输入“=”号也能计算32批注的排序 32转换 Lotus 1-2-3 公式33疑难19 如何在自定义快速访问工具列中添加只属于某工作簿的按钮34
添加特定工作簿的按钮341.3 练习与思考36第2章 列印 1062.1 分页37疑难20 如何强制在某个单元格处重新开始分页列印37手动分页列印38疑难21 如何删除某行或某列的手动分页符39删除指定的分页符40疑难22 如何删除所有的分页符41删除工作表中所有的手动分页符412.2 页面设定的详细设定42疑难23 如何列印每页的标题行42设定列印标题行43疑难24 如何只列印工作表中某部分的单元格区域44使用名称设定“顶端标题行44设定工作表列印区域45视图管理器 45列印选定区域47疑难25 如何在页眉或页脚中插入图片48在页眉或页脚中插入图片48疑难26 如何能不打开页面设定而快速设定页眉或页脚50“页面布局”视图的使用51疑难27 如何在Excel 中取消首页的页眉页脚52取消首页的页眉页脚53疑难28 如何设定起始页码不是154设定起始页码不是 155疑难29 如何把页面设定複製到其他工作表中57使用工作组複製页面设定58疑难30 如何在每页列印粗外框线线59列印“格线线” 59疑难31 如何能不列印工作表中的图层对象61“草稿品质”列印62设定图片的 PrintObject 属性63疑难32 如何不列印单元格底纹颜色64“草稿品质”列印的效果64单色列印 64疑难33 如何一次列印工作簿中的所有工作表65列印整个工作簿65疑难34 如何把 A4 纸页面的内容缩放列印到 16K 纸中66缩放列印 672.3 练习与思考68第3章 单元格和工作表操作1253.1 複製和贴上69剪贴簿的使用69疑难35 如何多次複製、贴上相同的档案和图片69Office 剪贴簿简介70剪贴簿可容纳的项目数70疑难36 複製时弹出“不能清除剪贴簿”对话框，如何解决71重启计算机以清除剪贴簿71疑难37 如何将自定义格式的显示结果转换为真实数据72将自定义格式的显示结果转换为真实数据 72疑难38 如何将单元格区域複製为图片73“複製为图片”功能73疑难39 如何可以不用照相机实现相同的连结图片效果75贴上图片连结753.2 丰富的单元格格式75疑难40 如何将文字颠倒顺序显示75“@宋体”的设定76疑难41 如何在Excel 中製作打钩的方框77“Wingdings”字型的使用77
疑难42 如何给汉字标注拼音78疑难43 如何在Excel 中输入带音调的拼音79拼音指南 79疑难44 如何设定表格中表头的倾斜程度80输入带音调的拼音80疑难45 如何隐藏不需要显示的区域81字型对齐方向设定81自定义数字格式隐藏单元格内容82自定义格式代码分析82疑难46 如何对手机号码进行分段显示83自定义数字格式83疑难47 如何让输入的数据以万为单位显示84“!”在自定义数字格式中的使用84疑难48 如何对不同範围的数值使用不同颜色进行区别85在自定义数字格式中使用颜色85疑难49 如何快速输入性别86设定特定数字显示为特定字元86疑难50 如何输入身份证号码87疑难51 如何快速录入标段里程88超过 15 位数字的输入方法883.3 快速美化单元格89疑难52 如何对不连续区域使用格式刷89自定义格式添加特定字元89疑难53 如何批量修改套用了某一单元格样式的单元格样式90连续使用格式刷90单元格样式的修改90疑难54 如何将单元格样式套用到其他工作簿93合併样式 933.4 编辑单元格94疑难55 如何批量合併相同内容的单元格94分类汇总、格式刷、定位95疑难56 如何根据需要在单元格内强制换行97疑难57 如何把单元格内容和格式填充到其他工作表的相同位置中97单元格内强制换行97疑难58 怎样在Excel 中输入千分号（‰） 98操作工作组 98疑难59 在Excel 中如何使用带圈数字99输入千分号（‰） 99输入带圈数字100疑难60 如何取消多个单元格中的超连结101取消超连结 101疑难61 怎样阻止Excel 自动创建超连结102自动更正设定103疑难62 如何编辑有超连结的单元格104编辑超连结单元格104疑难63 如何改变批注框的形状105改变批注形状105疑难64 如何在批注中添加背景图片106在批注中添加背景图片1063.5 快速查找与定位107疑难65 如何清除某特定格式的单元格内容108按格式查找 108疑难66 如何查找某位用户的所有批注109疑难67 如何查找自定义格式单元格中的内容110查找批注 110查找自定义格式的内容111疑难68 如何在整个工作簿中进行查找112查找範围 112
疑难69 如何一次性取消Excel 表中的换行符113疑难70 如何删除单元格中的通配符113替换换行符 113通配符的替换113疑难71 如何批量删除特定字元114不明字元的替换114疑难72 如何指定区域进行替换114替换指定区域115疑难73 如何快速定位到当前的活动单元格116疑难74 如何巧用双击定位到列表的最后一行116定位活动单元格116疑难75 如何突出显示所有包含公式的单元格117使用双击定位117定位公式单元格1183.6 插入与删除119疑难76 如何快速插入工作表119疑难77 如何製作工资条119插入工作表 119疑难78 如何一次插入多行121多行插入 121疑难79 如何在一张列表中插入行数间隔不同的空行122填充等差系列123疑难80 如何将某个区域内容移动并插入到指定位置125移动并插入单元格125疑难81 如何在插入或删除行时保持编号连续126表、ROW()函式的使用126疑难82 工作表只使用了一小部分为什幺无法插入列127清除工作表最后一列的数据127疑难83 为何在所有工作表中插入行或列时均提示“不能将对象移动到表外” 1273.7 工作表的处理128疑难84 如何隐藏工作表128複製工作表至新工作簿128隐藏工作表 128疑难85 如何临时性查看隐藏列的数据129Lotus 1-2-3 常用键129疑难86 如何把其他工作簿的引用转换为数值而保留本工作簿引用的公式131疑难87 如何处理失去连结的工作簿131外部工作簿连结131疑难88 怎样限定用户只能选择可编辑区域132更新外部连结132保护工作表 132疑难89 如何一次性将全部工作表改成“普通”视图模式134普通视图 134疑难90 如何显示“标尺”135疑难91 如何对两个工作表进行并排对比135切换工作簿视图135开新视窗、并排查看136疑难92 如何一次性打开多个经常使用的工作簿137工作区 1373.8 练习与思考138第4章 排序与筛选1894.1 排序的技巧140疑难93 如何按汉字的笔画排序140按笔画排序 141疑难94 如何按行排序142按行排序 142疑难95 如何对多列数据中的某一列数据进行排序143指定区域排序143疑难96 分类汇总后如何按照汇总值排序144疑难97 如何对某个合併单元格相邻的数据区域进行排序145
按分类汇总值排序145合併单元格的排序145疑难98 如何按照单元格背景颜色排序146按单元格颜色排序147疑难99 如何在设定保护的工作表中使用自动筛选和排序功能148在受保护的工作表中启用筛选和排序功能 148疑难100 如何将多行内容随机重新排序149乱序排序 149疑难101 如何在排序保存后仍能恢复排序前的状态150疑难102 如何对字母和数字组成的混合文本进行有规律的排序150恢复排序前的顺序150对字母和数字组成的混合文本排序 151Excel 排序依据1524.2 自动筛选152疑难103 如何对双行标题列表进行筛选153双行标题的工作表筛选153疑难104 如何快速删除系统导出档案中的大量空白行154批量删除空白行154疑难105 如何自动筛选奇数行或偶数行的数据155筛选奇数或偶数行156疑难106 如何一次性取消所有工作表的自动筛选状态157疑难107 如何使用筛选列表中的搜寻功能158批量取消自动筛选158在自动筛选中使用搜寻功能158疑难108 在自动筛选日期时，如何让日期不按年月日分组160在自动筛选时让日期不按年月日分组 160疑难109 如何不增加辅助列对日期按星期筛选161日期按星期筛选162自动筛选的筛选方式介绍162疑难110 如何进行反向选择163反向筛选 163疑难111 如何以字型颜色为条件求和164SUBTOTAL()函式求和1654.3 高级筛选165疑难112 如何将筛选的结果複製到其他工作表中166将高级筛选的结果複製到其他工作表中 166疑难113 如何返回指定数据源表中的某几列167筛选指定列的内容168疑难114 如何使用公式作为筛选条件168在高级筛选中使用公式168疑难115 如何获取不重複记录169高级筛选不重複记录1704.4 练习与思考170第5章 条件格式 2235.1 基本技巧172疑难116 如何用数据条表示不同级别人员的工资172数据条的使用173让数据条不显示单元格数值173设定数据条的最大值和最小值174疑难117 如何用图示把考试成绩等级形象地表示出来176图示集条件格式177自定义图示集的分隔数178疑难118 如何複製由条件格式产生的颜色179複製条件格式的颜色180疑难119 如何更改数据条框线的颜色181调出 Office 剪贴簿181
更改数据条框线颜色182疑难120 如何标识出面试成绩中的两个最高分和两个最低分183“项目选择规则”条件格式183疑难121 如何标记出招聘报名表中的重複姓名185标识重複值186疑难122 如何设定具有立体感的单元格187单元格框线设定技巧1875.2 在条件格式中使用公式189疑难123 如何标记特定年龄段的人员189双条件设定条件格式190疑难124 如何标识出工资表中的非数字单元格192条件格式规则192标识非数字单元格193疑难125 如何利用条件格式凸显双休日194在条件格式中使用 WEEKDAY()函式194疑难126 如何只在不及格成绩的单元格上显示图示集196条件格式中“如果为真则停止”的使用 197疑难127 如何突出显示部门总经理名册记录199调整条件格式的优先顺序199使用 MATCH()函式判断首条记录199疑难128 如何在条件格式中使用自定义数字格式200在条件格式中使用自定义数字格式 2015.3 练习与思考202第6章 数据有效性2586.1 基本技巧203疑难129 如何使用户在选定单元格时出现提示信息203使用数据有效性的信息204疑难130 如何製作企业评级可选择下拉选单205製作下拉选单205疑难131 如何只複製单元格中的数据有效性设定206“选择性贴上”数据有效性207在多个不连续的单元格中设定相同的数据有效性 208疑难132 为什幺数据有效性不显示下拉箭头209显示对象 210疑难133 如何在有效性序列来源中使用左右箭头移动游标211切换数据有效性的“点模式”和“输入模式” 212疑难134 如何在多个工作表同时设定相同的有效性验证212在工作组中设定数据有效性213疑难135 如何让具有数据有效性的单元格允许输入非法值214数据有效性中非法值的输入2156.2 套用举例216疑难136 如何把不符合要求的数据找出来217圈释无效数据217疑难137 如何避免在一列中输入重複内容218禁止输入重複值219疑难138 如何在数据有效性序列中引用其他工作表的单元格区域220引用其他工作表单元格区域作为数据有效性“来源” 220疑难139 为什幺单元格禁止输入任何内容，数据有效性中却看不到设定2212003 版製作方法221清除数据有效性222疑难140 如何设定数据有效性使单元格只能输入汉字223数据有效性规则223
只能输入汉字的有效性224疑难141 如何利用数据有效性实现联想输入225数据有效性联想输入225疑难142 怎样限定单元格输入小数不超过2 位227限制输入小数点的位数2276.3 练习与思考228第7章 名称 2917.1 创建与删除229疑难143 为什幺创建名称时提示输入名称无效229疑难144 如何在一个工作簿中定义两个相同的名称230工作表级名称231疑难145 如何批量创建名称232指定名称 233疑难146 如何一次性删除所有定义的名称234查看定义的名称234批量删除名称235删除表名称236疑难147 如何使工作表内显示定义的名称237调整“显示比例”以显示定义的名称 237疑难148 如何在名称中使用常量239在名称中使用常量239疑难149 如何使用名称动态查询行、列交叉的单元格241交叉运算符的使用242疑难150 如何创建引用动态区域的名称243Excel 的4 种类型的运算符243使用组合函式创建动态名称2447.2 名称管理器245疑难151 如何对名称排序246排序名称 246疑难152 如何筛选名称247筛选名称 2487.3 练习与思考249第8章 数据透视表3278.1 创建251疑难153 如何按多条件进行金额汇总分析251创建数据透视表252疑难154 如何更改数据透视表的数据源254更改数据透视表数据源255疑难 155 如何选取前50%的记录进行汇总分析256导入外部数据创建数据透视表257疑难156 如何将二维表格转换成数据列表259创建“多重合併计算数据区域”透视表 259疑难157 如何创建年份和季度的双页栏位数据透视表262创建双页栏位数据透视表263疑难158 如何创建数据透视图265创建数据透视图2658.2 格式化266疑难159 如何更好地区分不同的数据透视表267更改数据透视表名267疑难160 如何保持列宽固定不变267刷新透视表保持列宽不变268疑难161 如何合併单元格标誌269在数据透视表中合併标誌269疑难162 如何去掉数据透视表中的“(空白)”数据270在数据透视表中替换“(空白)”数据270疑难163 如何隐藏数据透视表中的计算错误271更改错误值的显示值271疑难164 如何显示表格形式的数据透视表272
设定“报表布局” 272疑难165 如何设定默认样式274设定默认的数据透视表样式274疑难166 如何重点强调支出大于预算的数据275在数据透视表中使用条件格式276疑难167 如何显示重複项目的行栏位标籤277重複项目标籤278疑难168 如何为每个分级之间添加空白行279在每个项目后插入空行279疑难 169 如何设定标籤所在的单元格区域为居中格式2808.3 栏位的设定281疑难170 如何在数据透视表中使用滑鼠拖放282使用“经典数据透视表布局282疑难171 如何快速调整报表结构283把栏位移动到数据透视表的其他区域 283疑难172 在透视表栏位列表中如何显示更多的栏位284更改栏位列表布局284疑难173 如何添加计算栏位285疑难174 如何添加计算项288疑难175 如何对于一个值栏位进行多种方式的统计289疑难176 如何更改栏位列表中栏位的显示顺序2928.4 组合293疑难177 如何对日期型栏位分别按年和季度显示293疑难178 如何对栏位快速套用手动组合2958.5 刷新297疑难179 如何不保留数据源删除项目297疑难180 如何延迟报表数据更新299疑难181 如何对数据透视表设定批量刷新3008.6 报表的数据处理与分析301疑难182 如何快速取消总计到301疑难183 如何显示数据占分类的百分比302疑难184 如何得到数据透视表中的明细数据304显示详细信息304疑难185 如何按月份显示金额汇总305数据透视表的报表筛选功能306疑难186 如何对栏位的项进行手动排序307手动排序栏位307疑难187 如何对数值区域使用自动筛选功能308在数据透视表的数值区域使用自动筛选 309疑难188 如何使用切片器快速筛选数据310切片器的使用311疑难189 如何创建前10 个最大值报表312筛选“10 个最大值3138.7 练习与思考314第9章 数据分析工具3489.1 重複值的处理317疑难190 如何快速删除材料表中的重複记录317删除重複项318疑难191 如何取得多表中的不重複值姓名319什幺是重複项319合併计算 3209.2 分类汇总324疑难192 如何创建分类汇总324分类汇总 324疑难193 如何进行多级分类汇总326
多级分类汇总326疑难194 如何删除分类汇总327删除分类汇总327疑难195 如何快速合併同类别的单元格328分类汇总、定位328经典数据透视表布局、合併标誌3309.3 分析工具库333疑难196 已知税后收入如何求税前收入333单变数求解334疑难197 如何使用方案管理器分析各类别的人员工资335方案管理器336方案摘要、方案数据透视表337疑难198 如何使用模拟运算表製作九九乘法表338模拟运算表338疑难199 如何以最低成本购买固定数量的礼品340疑难200 如何对规定总和的数据组合求解343规划求解 3449.4 练习与思考346第10章 综合套用40510.1 SQL 的威力347疑难201 如何查询销售额10 万元以上的记录347使用 Microsoft Query 查询、SQL 语句348SQL 语句介绍350修改简化 SQL 语句352疑难202 如何将物料名称与型号规格分离353SQL 语句之INSTR()函式354SQL 语句之MID()函式356疑难203 如何把大小写混合的英文姓氏完全大写357SQL 语句之UCASE()函式358疑难204 如何分别统计男生和女生的平均年龄359SQL 语句之AVG()函式359SQL 语句之GROUP BY 子句360疑难205 如何计算在每个得分範围内的数量361SQL 语句之IIF()函式362在 SQL 中使用列序号代替栏位名362使用 CHOOSE()函式363SQL 语句之CHOOSE()函式363CHOOSE()函式从参数列表中选择并返回值363使用 PARTITION()函式363疑难206 如何提取日期中的季度、年份、月份、星期等信息364SQL 语句之PARTITION()函式364使用 DATAPART()函式365SQL 语句之DATAPART()函式365疑难207 如何统计各部门不重複人员数367SQL 语句之日期时间函式367SQL 语句之DISTINCT 谓词368疑难208 如何提取销售金额前3 名的记录369SQL 语句之TOP 关键字369疑难209 如何去除数据列表中某栏位为空白的行372SQL 语句之IN 谓词372SQL 语句之IS NOT NULL 判断非空值373疑难210 如何筛选重複特定次数以上的数据374
SQL 语句之HAVING 子句375疑难211 如何在Excel 中更新数据377SQL 语句之UPDATA 语句378疑难212 如何按部门、级别统计各岗位人数379SQL 语句之TRANSFORM 语句379疑难213 如何罗列出可能的工作分配方案381交叉连线的套用381疑难214 如何对两张表进行内连线384联合查询 38410.2 巧用XML 编辑器进行批量操作386疑难215 如何批量修改工作表名称386Excel 与XML 之修改WORKBOOK.XML 档案387疑难216 如何批量修改批注内容389Excel 与XML 之修改COMMENTS1.XML 档案390疑难217 如何批量修改超连结地址391Excel 与XML 之修改SHAREDSTRINGS.XML 档案39210.3 练习与思考393附录A Excel 键盘快捷键396附录B Excel 2010 规范与限制400附录C 光碟内容403

作者简介

王建发，网名gvntw，微软全球最有价值专家（MVP），ExcelTip-net技术社区总版主，多个Office技术社区资深版主，多年从事金融行业人力行政管理工作，精通Excel操作、函式和VBA，拥有丰富的实战经验，参与过多部Excel畅销书的编着工作。李术彬，网名tobyO8，ExcelTip-net技术社区版主，从事税务谘询工作，较注重Excel的基础知识，在许多操作功能上有独特见解，并积累了丰富的数据处理与分析实战经验。黄朝阳，网名apolloh，ExcelTip.rlet站长，微软全球最有价值专家（MVP）。拥有十多年丰富的企业信息化实践和管理经验，精通Excel和MSSQL在企业中的套用。早期曾在多个Office技术社区担任版主，参与过多部Excel畅销书的编着工作。