Excel应用与技巧大全

微软公司推出的Excel早已成为专业办公人士推荐的办公软件。随着版本的升级，Excel的功能也愈加丰富和强大。本书从行政与文秘人员的日常办公需求出发，全面而系统地讲解Excel的理论知识和操作技巧，帮助读者学会运用Excel出色地完成工作任务。全书共21章，可分为3大部分。靠前部分主要讲解Excel的基本操作和重要功能；第2部分则以实例的形式讲解了行政与文秘工作中各种常见报表、图表的制作；第3部分是对前两部分内容的综合应用，讲解如何使用控件和VBA程序创建员工薪资查询系统。书中的知识点采取与典型实例相结合的讲解方式，并穿插了大量好用、实用的小技巧。读者按照书中的讲解，结合云空间资料的实例文件进行实际操作，能够更加形象、直观地理解和掌握知识点。本书内容涵盖全面，讲解深入浅出，实例针对性和适用性强，适合从事行政与文秘工作的人员学习和提升Excel应用技能，对于大多数职场人士来说也是一本提升办公效能的应用宝典。

前言
如何获取云空间资料
第1章快速有效地录入数据
1.1快速输入文本和数字数据
1.1.1普通数据的输入
示例1-1在工作表中输入数据
技巧1-1在编辑组中输入数据
1.1.2多个数据同时输入
示例1-2输入员工的入职时间
技巧1-2不同工作表中输入相同数据
1.1.3输入特殊数据
示例1-3输入以O开头的编码
技巧1-3设置输入长数据的前缀
1.2有效数据的复制和移动
1.2.1使用命令复制与移动数据
示例1-4对已有单元格数据进行复制
技巧1-4复制、剪切、粘贴命令的组合键
1.2.2使用拖动法复制与移动数据
示例1-5用拖动法复制和移动数据
1.2.3将数据复制为图片对象
示例1-6将创建好的表格复制为图片
技巧1.5对复制为图片形式的表格进行编辑
1.3高效的自动填充数据
1.3.1使用填充命令填充序列
示例1-7在单元格中填充奇数序列
技巧1-6拖动填充柄直接填充有规律的序列
1.3.2使用拖动法填充有规律的数据
示例1-8使用拖动法填充有规律的数据
技巧1-7拖动右键快速填充序列
1.3.3使用自定义填充序列
第2章快速格式化工作表
2.1设置单元格格式
2.1.1设置字体格式
示例1-9通过自定义序列在表中填充文本序列
技巧1-8从已有序列中直接导入数据
1.4在内容中查找与替换数据
1.4.1查找普通字符
示例1-10在表中查找某个关键字
技巧1-9快速打开“查找和替换”对话框
1.4.2利用定位条件查找
示例1-11查找带批注的数据
技巧1-10直接单击“批注”选项查找带批注的单元格
1.4.3替换单元格数据
示例1-12将表中某个数据替换成另一个数据
技巧1-11替换功能的快捷方式
1.4.4替换单元格格式
示例1-13将表中标题的字体格式替换成其他格式
技巧1-12单元格数据和格式一并替换
1.5对特殊关键词使用批注
1.5.1添加批注
示例1-14对单元格数据添加批注
技巧1-13右击添加批注
1.5.2修改批注
示例1-15修改数据的批注信息
技巧1-14利用功能区中的“编辑批注”按钮
1.5.3删除添加的批注
示例1-16删除带有批注信息的数据
技巧1-15批量删除批注
技巧1-16批量隐藏批注
示例2-1对表中标题文字进行字体设置
2.1.2设置对齐方式
示例2-2设置表中数据的对齐方式
技巧2-1跨行合并
2.1.3设置数字格式
示例2-3设置表中数字格式
2.2设置边框和表格底纹
2.2.1快速添加边框
示例2-4为表格添加边框
技巧2-2绘制边框
技巧2.3擦除边框
2.2.2填充表格底纹
示例2-5为表格填充底纹
技巧2-4新建单元格样式
2.3利用条件格式设置单元格样式
2.3.1突出显示单元格规则
示例2-6利用“突出显示单元格规则”将特殊数据显示出来
技巧2-5使用“项目选取规则”设置单元格格式
2.3.2利用数据条设置单元格规则
示例2-7使用数据条显示数值大小
技巧2.6数据条中的渐变填充
2.3.3设置其他规则
示例2.8自定义条件格式
技巧2.7清除条件格式
技巧2.8条件格式管理规则
2.4套用表格格式
2.4.1套用预设表格格式
示例2.9套用系统预设的表格格式美化工作表
技巧2.9取消筛选功能
2.4.2新建表样式
示例2-10自定义表样式
技巧2-10设置“列”条纹
技巧2-11设置条纹尺寸
2.5创建页眉和页脚
2.5.1在“插入”选项卡下设置页眉和页脚
示例2-11为表格添加页眉和页脚
技巧2-12“页眉和页脚工具”栏下的“选项”设置
技巧2-13在“页面设置”对话框中插入页眉和页脚
2.5.2自定义页眉和页脚样式
示例2-12在“页面设置”对话框中自定义页眉和页脚样式
技巧2-14查看页屑和页脚的方法
第3章办公数据的排序、筛选和分类汇总
3.1数字和文本数据的排序
3.1.1简单排序
示例3.1将员工工龄按升序排列
示例3.2将商品销售记录按降序排列
技巧3.1按字母进行排序
3.1.2高级排序
示例3.3将“国庆前3天商品销售记录”按某个关键字排序
技巧3.2按“复制条件”添加“次要关键字”
技巧3.3按笔画进行排序
技巧3-4按单元格颜色排序
3.1.3自定义排序
示例3-4自定义关键字排序
技巧3-5通过“Excel选项”对话框自定义序列。
3.2多类数据的筛选
3.2.1自动筛选
示例3-5筛选出商品类别为“数码相机”的项
技巧3-6自动筛选的类型
3.2.2高级筛选
示例3-6筛选出公司产品市场价格在2500以上，促销方式为礼包赠送且销售额大于-8000的项
技巧3-7在原有区域显示筛选结果
技巧3-8使用高级筛选不重复的记录
技巧3-9不同列上具有多组条件的高级筛选
3.2.3自定义筛选
示例3-7筛选出成交价格在2700以上且销售额在7000-11000之间的商品
技巧3-10使用通配符进行模糊筛选
3.3相关数据的分类汇总
3.3.1创建分类汇总
示例3-8在表中根据数据创建分类汇总
技巧3-11复制分类汇总的结果
技巧3-12使用自动分页符打印分类汇总结果
3.3.2删除分类汇总
示例3-9将创建好的分类汇总删除
3.3.3分组显示数据
示例3-10将摄像机确定为一个组显示
3.3.4隐藏与显示分类汇总明细
示例3-11对创建好的分类汇总数据进行隐藏和显示
第4章精确快速的计算——公式与函数
4.1常见Excel的计算功能
4.1.1使用∑自动求和
示例4-1将表中数据按行和列分别求和
技巧4-1使用SUM函数求和
技巧4-2快速对多个目标单元格求和
4.1.2使用“求和”列表统计个数和平均值
示例4-2计算平均值并统计个数
技巧4-3使用Average函数求平均值
技巧4-4使用Count函数统计个数
4.1.3在状态栏中快速查看基本统计结果
示例4-3在状态栏中查看平均值和计数个数
技巧4-5巧用Excel状态栏
4.2求解计算结果的公式
4.2.1公式的基本元素
1函数
2引用
3运算符
4常数
4.2.2公式中的运算符和执行顺序
1算术运算符
2比较运算符
3文本运算符
4引用运算符
5公式中执行运算的顺序
4.2.3公式的输入
示例4-4用引用单元格的方式求解结果
4.2.4公式中单元格的引用方式
1相对引用
2绝对引用
3混合引用
技巧4-6快速切换引用方式
示例4-5创建计算结果的数组公式
技巧4-7快速计算所有乘积项的和
4.3专业公式求解函数
4.3.1函数的结构
4.3.2在公式中使用函数
示例4-6用函数计算结果
技巧4-8使用“插入函数”对话框插入函数
4.3.3函数的嵌套使用
示例4-7当销售额大干3万以上，且提供9折优惠，计算折扣额
技巧4-9LOOKUP函数解决IF函数的多层嵌套
4.4行政与文秘工作中的常用函数
4.4.1IF函数
示例4-8若销售额大干等于10000视为达标，小于这个数则不达标
技巧4-10IF函数的嵌套使用
4.4.2YEAR函数
示例4-9查找员工入职年份
技巧4-11插入静态日期或时间
技巧4-12插入动态日期或时间
4.4.3COUNTIF函数
示例4-10统计公司销售部人数
技巧4-13多重条件时使用COUNTIFS函数
4.4.4SUMPRODUCT函数
示例4-11计算购买办公用品的费用
4.4.5RANK.AVG函数
示例4-12根据销售额排序
技巧4-14RANK.EQ函数
技巧4-15绝对引用减少误差
4.4.6VLOOKUP函数
示例4-13根据员工编码查找员工其他信息
技巧4-16HLOOKUP函数
4.4.7MATCH函数
示例4-14查找员工姓名在表中的位置
4.4.8贷款偿还额函数PMT
示例4-15借入为期5年的长期借款50万，年利率为6.1%，计算每月还款额
4.5公式错误值与解决方法
第5章高效处理表格——数据分析工具
5.1多重数据的分列
示例5-1将带有分隔符的列分成独立的列
技巧5-1多个分隔符的分列
技巧5-2同时插入多列或多行
5.2重复信息的删除
示例5-2删除表中的重复值
5.3验证数据有效性
5.3.1设置数据有效性
示例5.3设置数据有效性
技巧5-3设置同步修改
5.3.2圈释无效数据
示例5-4标记无效数据
技巧5-4反向操作圈释有效数据
5.4相关数据合并计算
示例5-5根据各办事处的支出费用计算各项目总支出
5.5变量求解的模拟运算
5.5.1单变量模拟运算表
示例5-6计算不同利率下的月还款额
技巧5-5通过混合引用直接复制单元格填充结果
5.5.2双变量模拟运算表
示例5-7在贷款期限和利率都不同的情况下为企业选取最优的还款方式
5.5.3单变量求解
示例5-8以8%的年利率借入为期5年的长期借款，企业每年的偿还能力只有lO万，计算企业最多能贷多少款
5.6规划求解
5.6.1创建规划求解模型
示例5-9创建一个产品销售策略的模型
5.6.2规划求解过程
示例5-10达到一定的销售额需如何分配企业的销售模式最见效
5.6.3规划求解结果报告
示例5-11将规划求解结果生成报告形式
5.7方案管理器
5.71创建方案管理器
示例5-12在工作表中创建方案管理器
5.7.2编辑管理器中的方案
示例5-13修改方案管理器中的值并生成报告
技巧5-6在工作表中修改可变单元格数值
第6章复杂数据的处理——数据透视表
6.1创建数据透视表
示例6-1为工作表创建数据透视表
6.2编辑数据透视表
6.2.1添加字段
示例6-2为数据透视表添加字段
技巧6.1右键菜单添加字段
技巧6-2复选框添加字段
技巧6-3移动数据透视表字段窗格
技巧6-4调整字段节和区域节的显示方式
6.2.2更改字段布局
示例6-3更改添加字段的位置
技巧6-5活动字段的重命名
6.2.3更改汇总字段的汇总方式
示例6-4求各部门员工年龄的平均值
技巧6-6在功能区中打开“值字段设置”对话框
技巧6-7快捷菜单更改“值汇总依据”
6.2.4更改值的数字格式
示例6-5对年龄的平均值保留1位小数
6.2.5更改值的显示方式
示例6-6将数字字段以百分比形式显示
6.3设置数据透视表的显示方式
6.3.1活动字段的展开与折叠
示例6-7展开与折叠数据信息
6.3.2筛选数据透视表中的数据
示例6-8筛选列标签中的字段
6.3.3插入切片器并筛选数据透视表中的数据
示例6-9插入切片器进行数据筛选
技巧6-8更改切片器名称
技巧619使用一个切片器筛选多张数据透视表
6.3.4应用切片器样式
示例6-10为插入的切片器应用系统样式
6.4设置数据透视表格式
6.4.1设置数据透视表布局
示例6-11为数据透视表设置布局
6.4.2套用数据透视表样式
示例6-12套用数据透视表系统样式
6.4.3移动数据透视表
示例6-13在同一张工作表中移动数据透视表
6.5创建数据透视图
示例6-14在数据透视表中创建数据透视图
6.6编辑数据透视图
示例6-15更改数据透视图的布局、颜色以及样式
……
第7章数据的直观表现——SmartArt图形与图表的应用
第8章减少重复劳动——宏与VBA
第9章文件的高速输出——页面设置与打印
第10章有效保护数据——保密与共享
第11章员工培训管理
第12章员工值班与出勤
第13章员工加班与出差管理
第14章人员离职与人事管理
第15章员工业绩评测
第16章员工薪资管理
第17章员工社会保险与福利
第18章办公用品管理
第19章会议管理
第20章日常费用管理
第21章创建员工薪资查询系统

Excel 2013是专业的电子表格软件，在它的帮助下，办公人员不仅可以准确、高效地输入数据，快速制作出规范、美观的电子表格，还能运用函数、公式和各种高级工具，对数据进行管理、统计和分析。本书从行政与文秘人员的日常办公需求出发，全面而系统地讲解Excel的理论知识和操作技巧，并配合大量典型的应用实例，帮助读者学会运用Excel出色地完成信息录入、数据统计、表格制作、表单设计和图表展示等工作任务。
    内容结构全书共21章，可分为3大部分。
    第1部分包括第1～10章，主要讲解Excel的基本操作和重要功能，内容包括：数据录入，格式化工作表，数据的排序、筛选和分类汇总，公式与函数，数据分析工具，数据透视表，SmartArt图形与图表，宏与VBA，页面设置与打印，数据保密与共享等。
    第2部分包括第11～20章，以实例的形式讲解了行政与文秘工作中各种常见报表、图表的制作，涵盖员工培训、值班与出勤、加班与出差、离职与人事管理、业绩评测、薪资管理、社保与福利、办公用品管理、会议管理、日常费用管理等工作场景。
    第3部分即第21章，是对前两部分内容的综合应用，讲解如何使用控件和VBA程序创建员工薪资查询系统。编写特色●全面：一是Excel的理论知识和操作技巧全面，从基本的数据录入、工作表格式化，到进阶的数据透视表、宏与VBA，几乎涵盖了Excel的所有重要功能；二是实例类型全面，几乎囊括了行政与文秘人员日常办公的全部内容。
    ●实用：第1部分以熟练掌握软件操作、提高办公效率为目标安排内容，并穿插了大量实用、好用的小技巧；第2、第3部分的实例文件制作不仅有完整、详细的操作步骤解析，最终完成的文件和程序还可直接应用在工作中。
    ●易学：书中的知识点采取了与典型实例相结合的讲解方式。读者按照书中的讲解，结合云空间资料的实例文件进行实际操作，能够更加形象、直观地理解和掌握知识点。
    由于编者水平有限，在编写本书的过程中难免有不足之处，恳请广大读者指正批评，除了扫描二维码添加订阅号获取资讯以外，也可加入QQ群227463225与我们交流。
    编者2015年12月