Excel办公技巧查询与应用宝典.pdf

收藏

编号:20200522101740213186    类型:共享资源    大小:17.75MB    格式:PDF    上传时间:2020-05-22
  
1
文币
关 键 词:
Excel 办公 技巧 查询 应用 宝典
资源描述:
高效办公一本通 Excel 办公技巧查询 与应用宝典 轶煊文化 组编 张军翔 周倩倩 等编著 机 械 工 业 出 版 社 本书第 1 章至第 5 章讲解了表格的基本操作技巧,包括表格的美化、简 单的数据编辑处理和表格的打印设置等。 第 6 章至第 9 章分别介绍了条件格 式、数据有效性设置、数据的排序筛选和数据分析技巧,这些技巧都能够很 好地帮助读者在日常办公中达到事半功倍的效果。 第 10 章和第 11 章讲解了 数据透视表和图表使用,它们都是数据分析的工具。第 12 章至第 16 章介绍 了常用的函数,并分别配备了在人事、教育、销售等行业比较实用的例子, 同时配合公式解析深入浅出地进行讲解, 帮助读者更加深刻地理解公式的意 义,培养读者对公式拈来就用的本领。 全书内容全面、结构清晰、语言简练,编写方式以图为主。本书既可以 为即将步入职场的人士的系统学习材料, 也可以作为职场人士的案头查询手 册,同时也可以作为培训学校的培训教材使用。 图书在版编目(CIP)数据 Excel 办公技巧查询与应用宝典 / 轶煊文化组编;张军翔等编著.—北京:机 械工业出版社,2014.4 (高效办公一本通) ISBN 978-7-111-46666-6 Ⅰ.①E… Ⅱ.①轶… ②张… Ⅲ.①表处理软件 Ⅳ.①TP391.13 中国版本图书馆 CIP 数据核字(2014)第 094209 号 机械工业出版社(北京市百万庄大街 22 号 邮政编码 100037) 策划编辑:丁 诚 责任编辑:丁 诚 师沫迪 责任印制:乔 宇 北京铭城印刷有限公司印刷 2014 年 6 月第 1 版·第 1 次印刷 140mm×203mm·16.875 印张·451 千字 0001―4000 册 标准书号:ISBN 978-7-111-46666-6 定价:59.00 元 凡购本书,如有缺页、倒页、脱页,由本社发行部调换 电话服务网络服务 社 服 务中 心: (010)88361066 教 材 网:http:// 销 售 一 部: (010)68326294 机工官网:http:// 销 售 二 部: (010)88379649 机工官博:http:// 读者购书热线: (010)88379203 封面无防伪标均为盗版 前言 ○ III 前 言 Excel 软件在过去、现在、未来都会给我们的工作带来无限 的帮助。因为它具有强大的数据整理、计算与分析能力,无论您 从事什么样的工作都会或多或少地使用到它。文案工作者用它整 理数据、策划者用它分析数据、决策者用它查看结果。 正因 Excel 的强大功能,越来越多的人选择使用 Excel 来办 公。但究其精准且最大化的应用,绝大多数人都没做到,那么让 《Excel 办公技巧查询与应用宝典》这本书来帮您解决这个问题, 随用随查,确保您在工作中不再出现束手无策的尴尬局面。 本书特色分析  按应用划分章节 不完全按功能划分,按实际应用进行划分。例如,怎样输入 各类型数据、如何批量输入、如何美化待打印表格、如何按目的 筛选数据等,方便系统地学习,也方便即查即用。  操作数据真实 操作数据源严禁拼凑,使所用数据体现每个技巧的意义, 展现给读者的是确实解决了某一个问题。有应用环境,才能给 读者举一反三的思路,进而当读者再遇到类似问题时才能独立 解决,使问题迎刃而解。  技巧形式、即查即用 快节奏的商务办公大环境,让大多数人很难有过多的时间坐 下来细细研读一本书,所以解决当下问题为优先,技巧形式是不 二选择。 IV ○ Excel 办公技巧查询与应用宝典 本书主要由张军翔、周倩倩编写,参与编写与核对的人员还 有:吴爱妤、王波、姜楠、余杭、余曼曼、朱梦婷、夏慧文、黄 美玲、彭志霞、彭丽、吴祖兵、李伟、程亚丽、胡凤悦、彭丹丹、 聂芊芊、王成成、王鹏程、徐海林、许琴、殷晓如、张茂文、郑 发建、张雨晴、王涛等,在此对他们表示深深的谢意! 尽管作者对书中的内容精益求精,但疏漏之处在所难免。如 果您发现书中存在错误,敬请与我们联系,我们将尽快回复与纠 正。再次感谢您的支持! 编著者 2014 年 3 月 目录 ○ V 目 录 前言 第 1 章 操作工作簿 ········································································ 1 技巧 1 使用模板创建新工作簿 ····················································· 1 技巧 2 自定义工作簿默认的保存类型 ··········································· 3 技巧 3 自定义工作簿默认的保存位置 ··········································· 4 技巧 4 固定工作簿在最近使用的文档列表 ···································· 5 技巧 5 查看工作簿的保存路径 ····················································· 6 技巧 6 实现自动启动常用工作簿 ················································· 6 技巧 7 重新设置工作簿数据的默认字体、字号 ····························· 7 技巧 8 自定义工作簿默认工作表数 ·············································· 8 技巧 9 清除打开的工作簿的记录 ················································· 9 技巧 10 快速定位到文件夹 ························································ 10 技巧 11 快速合并与拆分多个工作簿数据 ····································· 11 技巧 12 加密工作簿 ·································································· 13 技巧 13 设置只允许用户进行部分操作 ······································· 14 技巧 14 繁简转换不求人 ··························································· 16 技巧 15 外语翻译助手 ······························································· 17 技巧 16 向“快速访问工具栏”中添加命令 ································ 18 第 2 章 操作工作表 ······································································ 21 2.1 工作表的编辑 ····································································· 21 技巧 1 操作工作表时快速切换至任意工作表······························· 21 技巧 2 批量重命名工作表名称 ··················································· 21 技巧 3 将工作表复制或移至另一工作簿中 ·································· 24 技巧 4 改变工作表标签颜色 ······················································ 25 VI ○ Excel 办公技巧查询与应用宝典 技巧 5 设置工作表图片背景 ······················································ 26 技巧 6 防止他人对工作表的增删 ··············································· 27 技巧 7 指定工作表中的可编辑区域 ············································ 29 技巧 8 只保护工作表中的特定单元格或区域······························· 30 技巧 9 隐藏工作表内的部分数据 ··············································· 31 技巧 10 彻底隐藏工作表 ··························································· 32 2.2 单元格、行、列操作 ························································· 33 技巧 11 准确定位多个任意的单元格区域 ···································· 33 技巧 12 选取多个工作表的相同区域 ·········································· 34 技巧 13 一次插入多行或多列····················································· 35 技巧 14 一次插入不连续的多行或多列 ······································· 36 技巧 15 隔行插入空行 ······························································· 37 技巧 16 快速删除所有空行 ························································ 39 技巧 17 设置合适的行高或列宽 ················································· 41 技巧 18 不连续行(列)的行高(列宽)一次性设置 ··················· 42 技巧 19 拖动交换两列数据的位置 ·············································· 43 第 3 章 数据的输入及自动填充 ·················································· 45 3.1 数据输入 ············································································· 45 技巧 1 让数据前面的 0 不省略 ··················································· 45 技巧 2 如何输入生僻字 ····························································· 46 技巧 3 记忆式输入提高输入效率 ··············································· 47 技巧 4 不连续单元格中相同数据一次输入 ·································· 48 技巧 5 多个工作表中相同数据一次输入 ····································· 49 技巧 6 插入专业型数学公式 ······················································ 50 技巧 7 让输入数据后自动添加小数点 ········································· 53 技巧 8 快速输入指定格式日期 ··················································· 54 技巧 9 快速输入指定格式时间 ··················································· 55 技巧 10 输入大写人民币金额····················································· 57 技巧 11 文字与数值在同一单元格并存且能计算 ·························· 58 技巧 12 部分重复数据快速输入有捷径 ······································· 60 目录 ○ VII 技巧 13 改变大金额数据的显示单位 ·········································· 61 技巧 14 按小数点对齐小数 ························································ 62 3.2 数据批量填充技巧 ····························································· 64 技巧 15 连续单元格填充递增的数字 ·········································· 64 技巧 16 连续单元格快速填充相同日期 ······································· 64 技巧 17 只填充工作日日期 ························································ 65 技巧 18 妙用填充引用单元格的格式 ·········································· 66 技巧 19 快速填充区域内的空白单元格 ······································· 67 技巧 20 自定义填充序列 ··························································· 68 技巧 21 让空白单元格自动填充上面的数据 ································ 70 技巧 22 超大数据块的一次性填充 ·············································· 71 3.3 导入外部数据 ····································································· 72 技巧 23 导入其他 Excel 表格中数据 ··········································· 72 技巧 24 导入文本文件数据 ························································ 73 技巧 25 导入 Word 文档中表格数据 ··········································· 76 技巧 26 导入网页中的有用表格 ················································· 77 技巧 27 根据网页内容更新工作表数据 ······································· 78 第 4 章 数据编辑与处理 ······························································ 81 4.1 常用编辑技巧 ····································································· 81 技巧 1 数据随单元格的列宽自动换行 ········································· 81 技巧 2 在任意位置上强制换行 ··················································· 82 技巧 3 设置文字上下标效果 ······················································ 83 技巧 4 妙用进行重复操作 ··············································· 84 技巧 5 “拆分窗口”滚屏查看数据 ·············································· 85 技巧 6 “冻结窗口”滚屏查看数据 ·············································· 86 技巧 7 文本格式数字转换为数字格式 ········································· 87 技巧 8 将数字转换为日期 ·························································· 90 技巧 9 链接到其他资料文档 ······················································ 92 技巧 10 鼠标指向链接对象时显示提示文字 ································ 93 技巧 11 阻止 Excel 自动创建超链接 ··········································· 94 VIII ○ Excel 办公技巧查询与应用宝典 技巧 12 如何编辑有超链接的单元格 ·········································· 95 4.2 数据合并拆分 ····································································· 96 技巧 13 合并单元格时保留所有文本内容 ···································· 96 技巧 14 取消单元格合并的数据到未合并的区域中 ······················ 97 技巧 15 如何批量合并相同内容的单元格 ···································· 98 技巧 16 将一个单元格中的数据拆分到多列 ······························ 100 4.3 数据复制 ··········································································· 104 技巧 17 粘贴数据时匹配目标区域格式 ····································· 104 技巧 18 数据行列转置生成新表格 ············································ 105 技巧 19 复制数据表保持原行高列宽 ········································ 105 技巧 20 多次复制、粘贴相同的文件和图片 ······························ 106 技巧 21 选择性粘贴实现简易数据计算 ····································· 108 技巧 22 选择性粘贴功能计算时忽略空单元格 ···························· 110 技巧 23 让粘贴数据随原数据自动更新 ······································ 111 技巧 24 将表格快速转换为图片 ················································ 112 技巧 25 将公式计算结果转换为实际值 ······································ 113 技巧 26 一次性复制公式到多个工作表中 ··································· 114 4.4 查找和替换数据 ································································ 115 技巧 27 查找数据并突出显示出来 ············································· 115 技巧 28 一次性找到所有空值单元格 ········································· 117 技巧 29 一次性找到所有设置了公式的单元格 ···························· 118 技巧 30 妙用通配符进行模糊查找 ············································· 119 技巧 31 替换的同时自动设置格式 ············································ 120 技巧 32 一次性取消 Excel 表中的换行符 ·································· 122 技巧 33 批量删除特定的字符··················································· 123 技巧 34 只在指定区域中进行替换 ············································ 123 第 5 章 表格美化与打印 ···························································· 125 5.1 表格美化 ··········································································· 125 技巧 1 跨列居中显示表格的标题 ············································· 125 技巧 2 标题应用会计用双下划线 ············································· 126 目录 ○ IX 技巧 3 设置表格标题倾斜显示 ················································· 128 技巧 4 套用表格样式 ······························································ 129 技巧 5 自定义表格样式 ··························································· 130 技巧 6 设置默认表格样式 ························································ 134 技巧 7 自定义单元格样式 ························································ 135 技巧 8 单元格样式的修改 ························································ 137 技巧 9 将单元格样式应用到其他工作簿中 ································ 137 技巧 10 强大的格式刷 ····························································· 139 5.2 表格页面设置与打印 ······················································· 140 技巧 11 手动直观调整页边距 ··················································· 140 技巧 12 让表格显示在纸张的正中间 ········································ 141 技巧 13 在任意需要的位置上分页 ············································ 143 技巧 14 删除所有手动分页符··················································· 144 技巧 15 在页眉页脚中使用图片 ··············································· 145 技巧 16 设置页脚起始页码为所需的页数 ·································· 147 技巧 17 把页面设置复制到其他工作表中 ·································· 148 技巧 18 打印指定的页 ····························································· 151 技巧 19 打印工作中某一特定区域 ············································ 151 技巧 20 打印整个工作簿 ························································· 153 技巧 21 一次性打印多张工作表 ··············································· 154 技巧 22 将数据缩印在一页纸内打印 ········································ 154 技巧 23 打印超宽工作表 ························································· 156 技巧 24 将表格列标题打印到每一页中 ····································· 157 技巧 25 将行、列号一起打印出来 ············································ 159 技巧 26 在打印时不打印单元格中的颜色和底纹效果 ················· 160 技巧 27 将工作表上的网格线打印出来 ····································· 161 技巧 28 将表格中添加的批注信息也打印出来 ··························· 162 技巧 29 巧妙打印工作表背景··················································· 163 第 6 章 条件格式 ········································································ 165 6.1 条件格式设置 ··································································· 165 X ○ Excel 办公技巧查询与应用宝典 技巧 1 当数值大于或小于指定数值时特殊显示 ························· 165 技巧 2 当数值介于指定值之间时特殊显示 ································ 166 技巧 3 当排名前 N名时显示特殊格式 ······································ 168 技巧 4 让值班安排表中只值班一次的特殊显示 ························· 169 技巧 5 当文本包含某个值时突出显示 ······································· 171 技巧 6 将日期是本月的记录特殊显示 ······································· 172 技巧 7 用数据条显示出不同级别人员的工资····························· 173 技巧 8 复制使用条件格式 ························································ 175 技巧 9 清除建立的条件格式规则 ············································· 177 技巧 10 屏蔽公式返回的错误值 ··············································· 178 6.2 在条件格式中使用公式 ··················································· 180 技巧 11 工作表自动隔行填色 ··················································· 180 技巧 12 当两个单元格采购价格不同时就突出显示出来 ············· 182 技巧 13 为不包含某一特定字符的单元格设置格式 ···················· 183 技巧 14 标记特定年龄段的人员 ··············································· 185 技巧 15 标识出表格中非数字单元格 ········································ 186 技巧 16 特殊显示双休日 ························································· 188 技巧 17 只在不及格成绩的单元格上显示图标集························ 189 技巧 18 突出显示部门总经理··················································· 192 技巧 19 将销售业绩高于平均值的标注为“优”························ 194 第 7 章 数据有效性设置 ···························································· 197 7.1 数据有效性设置 ······························································· 197 技巧 1 创建下拉列表提高输入效率 ·········································· 197 技巧 2 选中单元格时给出输入提示 ·········································· 199 技巧 3 限定输入指定范围内的日期 ·········································· 200 技巧 4 复制单元格的数据有效性设置 ······································· 201 技巧 5 在数据有效性序列中引用其他工作表的单元格区域 ········ 202 技巧 6 在多个工作表中同时设置相同的有效性验证 ·················· 205 技巧 7 为什么数据有效性不显示下拉箭头 ································ 207 技巧 8 为什么单元格禁止输入任何内容但数据有效性中 目录 ○ XI 看不到设置 ·································································· 207 技巧 9 圈释无效数据 ······························································ 209 7.2 设置数据有效性时使用公式 ··········································· 210 技巧 10 限制输入空格 ····························································· 210 技巧 11 禁止输入重复值 ·························································· 211 技巧 12 设置数据有效性使单元格只能输入汉字························ 212 技巧 13 限定输入小数不超过两位 ············································ 214 技巧 14 禁止录入错误格式的产品规格 ····································· 215 技巧 15 限定单元格输入必须包含指定内容 ······························ 217 技巧 16 把不符合要求的数据找出来 ········································ 218 技巧 17 提示出货数量大于进货数量 ········································ 220 技巧 18 使用有效性保护公式单元格不被修改 ··························· 221 技巧 19 创建二级联动下拉列表 ··············································· 223 技巧 20 创建多级联动下拉列表 ··············································· 225 技巧 21 带提示功能的下拉列表实现多列快速录入 ···················· 230 第 8 章 表格数据的排序、筛选 ················································ 235 8.1 排序的技巧 ······································································· 235 技巧 1 按姓名的汉字笔画排序 ················································· 235 技巧 2 设置按行排序 ······························································ 236 技巧 3 有相同记录时按第二个关键字排序 ································ 237 技巧 4 自定义排序规则 ··························································· 239 技巧 5 分类汇总后如何按汇总值排序 ······························
展开阅读全文
  文库网所有资源均是用户自行上传分享,仅供网友学习交流,未经上传用户书面授权,请勿作他用。
0条评论

还可以输入200字符

暂无评论,赶快抢占沙发吧。

关于本文
本文标题:Excel办公技巧查询与应用宝典.pdf
链接地址:http://www.wenkunet.com/p-2185459.html

当前资源信息

浏览:6次
始于喜****深爱上传于2020-05-22

文档标签

文库网广告
关于我们 - 网站声明 - 网站地图 - 资源地图 - 友情链接 - 网站客服 - 联系我们

文库网用户QQ群:731843829    百度熊掌号:文库网精选     微信公众号:WENKUNET

copyright© 2018-2020 文库网 wenkunet.com 网站版权所有

经营许可证编号:粤ICP备19143267号-1 


1.png 2.png 3.png 4.png 5.png 6.png 7.png 8.png 9.png 10.png