Excel应用与技巧大全 行政与文秘PDF电子书下载

第1章 快速有效地录入数据 1

1.1快速输入文本和数字数据 2

1.1.1普通数据的输入 2

示例1-1在工作表中输入数据 2

技巧1-1在编辑组中输入数据 3

1.1.2多个数据同时输入 3

示例1-2输入员工的入职时间 3

技巧1-2不同工作表中输入相同数据 5

1.1.3输入特殊数据 5

示例1-3输入以0开头的编码 5

技巧1-3设置输入长数据的前缀 6

1.2有效数据的复制和移动 6

1.2.1使用命令复制与移动数据 6

示例1-4对已有单元格数据进行复制 6

技巧1-4复制、剪切、粘贴命令的组合键 7

1.2.2使用拖动法复制与移动数据 8

示例1-5用拖动法复制和移动数据 8

1.2.3将数据复制为图片对象 9

示例1-6将创建好的表格复制为图片 9

技巧1-5对复制为图片形式的表格进行编辑 10

1.3高效的自动填充数据 11

1.3.1使用填充命令填充序列 11

示例1-7在单元格中填充奇数序列 11

技巧1-6拖动填充柄直接填充有规律的序列 12

1.3.2使用拖动法填充有规律的数据 12

示例1-8使用拖动法填充有规律的数据 12

技巧1-7拖动右键快速填充序列 13

1.3.3使用自定义填充序列 13

示例1-9通过自定义序列在表中填充文本序列 14

技巧1-8从已有序列中直接导入数据 15

1.4在内容中查找与替换数据 15

1.4.1查找普通字符 15

示例1-10在表中查找某个关键字 16

技巧1-9快速打开“查找和替换”对话框 17

1.4.2利用定位条件查找 17

示例1-11查找带批注的数据 17

技巧1-10直接单击“批注”选项查找带批注的单元格 18

1.4.3替换单元格数据 18

示例1-12将表中某个数据替换成另一个数据 18

技巧1-11替换功能的快捷方式 19

1.4.4替换单元格格式 19

示例1-13将表中标题的字体格式替换成其他格式 20

技巧1-12单元格数据和格式一并替换 21

1.5对特殊关键词使用批注 21

1.5.1添加批注 21

示例1-14对单元格数据添加批注 21

技巧1-13右击添加批注 22

1.5.2修改批注 22

示例1-15修改数据的批注信息 22

技巧1-14利用功能区中的“编辑批注”按钮 22

1.5.3删除添加的批注 23

示例1-16删除带有批注信息的数据 23

技巧1-15批量删除批注 24

技巧1-16批量隐藏批注 24

第2章 快速格式化工作表 25

2.1设置单元格格式 26

2.1.1设置字体格式 26

示例2-1对表中标题文字进行字体设置 26

2.1.2设置对齐方式 27

示例2-2设置表中数据的对齐方式 27

技巧2-1跨行合并 28

2.1.3设置数字格式 29

示例2-3设置表中数字格式 29

2.2设置边框和表格底纹 30

2.2.1快速添加边框 30

示例2-4为表格添加边框 30

技巧2-2绘制边框 31

技巧2-3擦除边框 31

2.2.2填充表格底纹 32

示例2-5为表格填充底纹 32

技巧2-4新建单元格样式 33

2.3利用条件格式设置单元格样式 34

2.3.1突出显示单元格规则 34

示例2-6利用“突出显示单元格规则”将特殊数据显示出来 34

技巧2-5使用“项目选取规则”设置单元格格式 35

2.3.2利用数据条设置单元格规则 35

示例2-7使用数据条显示数值大小 35

技巧2-6数据条中的渐变填充 36

2.3.3设置其他规则 36

示例2-8自定义条件格式 36

技巧2-7清除条件格式 38

技巧2-8条件格式管理规则 38

2.4套用表格格式 38

2.4.1套用预设表格格式 38

示例2-9套用系统预设的表格格式美化工作表 38

技巧2-9取消筛选功能 40

2.4.2新建表样式 40

示例2-10自定义表样式 40

技巧2-10设置“列”条纹 43

技巧2-11设置条纹尺寸 43

2.5创建页眉和页脚 43

2.5.1在“插入”选项卡下设置页眉和页脚 43

示例2-11为表格添加页眉和页脚 44

技巧2-12“页眉和页脚工具”栏下的“选项”设置 45

技巧2-13在“页面设置”对话框中插入页眉和页脚 45

2.5.2自定义页眉和页脚样式 46

示例2-12在“页面设置”对话框中自定义页眉和页脚样式 46

技巧2-14查看页眉和页脚的方法 47

第3章 办公数据的排序、筛选和分类汇总 48

3.1数字和文本数据的排序 49

3.1.1简单排序 49

示例3-1将员工工龄按升序排列 49

示例3-2将商品销售记录按降序排列 49

技巧3-1按字母进行排序 50

3.1.2高级排序 50

示例3-3将“国庆前3天商品销售记录”按某个关键字排序 50

技巧3-2按“复制条件”添加“次要关键字” 52

技巧3-3按笔画进行排序 52

技巧3-4按单元格颜色排序 52

3.1.3自定义排序 52

示例3-4自定义关键字排序 52

技巧3-5通过“Excel选项”对话框自定义序列 54

3.2多类数据的筛选 54

3.2.1自动筛选 54

示例3-5筛选出商品类别为“数码相机”的项 54

技巧3-6自动筛选的类型 55

3.2.2高级筛选 55

示例3-6筛选出公司产品市场价格在2500以上，促销方式为礼包赠送且销售额大于8000的项 56

技巧3-7在原有区域显示筛选结果 57

技巧3-8使用高级筛选不重复的记录 57

技巧3-9不同列上具有多组条件的高级筛选 57

3.2.3自定义筛选 58

示例3-7筛选出成交价格在2700以上且销售额在7000～11000之间的商品 58

技巧3-10使用通配符进行模糊筛选 60

3.3相关数据的分类汇总 60

3.3.1创建分类汇总 60

示例3-8在表中根据数据创建分类汇总 60

技巧3-11复制分类汇总的结果 62

技巧3-12使用自动分页符打印分类汇总结果 63

3.3.2删除分类汇总 63

示例3-9将创建好的分类汇总删除 63

3.3.3分组显示数据 64

示例3-10将摄像机确定为一个组显示 64

3.3.4隐藏与显示分类汇总明细 66

示例3-11对创建好的分类汇总数据进行隐藏和显示 66

第4章 精确快速的计算——公式与函数 69

4.1常见Excel的计算功能 70

4.1.1使用∑自动求和 70

示例4-1将表中数据按行和列分别求和 70

技巧4-1使用SUM函数求和 72

技巧4-2快速对多个目标单元格求和 72

4.1.2使用“求和”列表统计个数和平均值 72

示例4-2计算平均值并统计个数 72

技巧4-3使用Average函数求平均值 74

技巧4-4使用Count函数统计个数 74

4.1.3在状态栏中快速查看基本统计结果 74

示例4-3在状态栏中查看平均值和计数个数 75

技巧4-5巧用Excel状态栏 75

4.2求解计算结果的公式 75

4.2.1公式的基本元素 76

1函数 76

2引用 76

3运算符 76

4常数 76

4.2.2公式中的运算符和执行顺序 76

1算术运算符 76

2比较运算符 77

3文本运算符 77

4引用运算符 77

5公式中执行运算的顺序 78

4.2.3公式的输入 78

示例4-4用引用单元格的方式求解结果 78

4.2.4公式中单元格的引用方式 79

1相对引用 80

2绝对引用 80

3混合引用 80

技巧4-6快速切换引用方式 81

示例4-5创建计算结果的数组公式 81

技巧4-7快速计算所有乘积项的和 82

4.3专业公式求解函数 82

4.3.1函数的结构 82

4.3.2在公式中使用函数 82

示例4-6用函数计算结果 82

技巧4-8使用“插入函数”对话框插入函数 83

4.3.3函数的嵌套使用 84

示例4-7当销售额大于3万以上，且提供9折优惠，计算折扣额 84

技巧4-9LOOKUP函数解决IF函数的多层嵌套 86

4.4行政与文秘工作中的常用函数 86

4.4.1 IF函数 86

示例4-8若销售额大于等于10000视为达标，小于这个数则不达标 86

技巧4-10IF函数的嵌套使用 87

4.4.2 YEAR函数 88

示例4-9查找员工入职年份 88

技巧4-11插入静态日期或时间 89

技巧4-12插入动态日期或时间 89

4.4.3 COUNTIF函数 89

示例4-10统计公司销售部人数 89

技巧4-13多重条件时使用COUNTIFS函数 91

4.4.4 SUMPRODUCT函数 91

示例4-11计算购买办公用品的费用 91

4.4.5 RANK.AVG函数 92

示例4-12根据销售额排序 92

技巧4-14RAN.EQ函数 93

技巧4-15绝对引用减少误差 94

4.4.6 VLOOKUP函数 94

示例4-13根据员工编码查找员工其他信息 94

技巧4-16 HLOOKUP函数 95

4.4.7 MATCH函数 95

示例4-14查找员工姓名在表中的位置 95

4.4.8贷款偿还额函数PMT 96

示例4-15借入为期5年的长期借款50万，年利率为6.1％，计算每月还款额 96

4.5公式错误值与解决方法 97

第5章 高效处理表格——数据分析工具 98

5.1多重数据的分列 99

示例5-1将带有分隔符的列分成独立的列 99

技巧5-1多个分隔符的分列 101

技巧5-2同时插入多列或多行 101

5.2重复信息的删除 101

示例5-2删除表中的重复值 101

5.3验证数据有效性 102

5.3.1设置数据有效性 103

示例5-3设置数据有效性 103

技巧5-3设置同步修改 105

5.3.2圈释无效数据 105

示例5-4标记无效数据 105

技巧5-4反向操作圈释有效数据 106

5.4相关数据合并计算 106

示例5-5根据各办事处的支出费用计算各项目总支出 106

5.5变量求解的模拟运算 108

5.5.1单变量模拟运算表 108

示例5-6计算不同利率下的月还款额 108

技巧5-5通过混合引用直接复制单元格填充结果 109

5.5.2双变量模拟运算表 109

示例5-7在贷款期限和利率都不同的情况下为企业选取最优的还款方式 110

5.5.3单变量求解 112

示例5-8以8％的年利率借入为期5年的长期借款，企业每年的偿还能力只有10万，计算企业最多能贷多少款 112

5.6规划求解 113

5.6.1创建规划求解模型 113

示例5-9创建一个产品销售策略的模型 113

5.6.2规划求解过程 114

示例5-10达到一定的销售额需如何分配企业的销售模式最见效 114

5.6.3规划求解结果报告 116

示例5-11将规划求解结果生成报告形式 116

5.7方案管理器 117

5.7.1创建方案管理器 117

示例5-12在工作表中创建方案管理器 117

5.7.2编辑 管理器中的方案 119

示例5-13修改方案管理器中的值并生成报告 119

技巧5-6在工作表中修改可变单元格数值 120

第6章 复杂数据的处理——数据透视表 121

6.1创建数据透视表 122

示例6-1为工作表创建数据透视表 122

6.2编辑 数据透视表 123

6.2.1添加字段 123

示例6-2为数据透视表添加字段 124

技巧6-1右键菜单添加字段 125

技巧6-2复选框添加字段 125

技巧6-3移动数据透视表字段窗格 125

技巧6-4调整字段节和区域节的显示方式 126

6.2.2更改字段布局 126

示例6-3更改添加字段的位置 126

技巧6-5活动字段的重命名 127

6.2.3更改汇总字段的汇总方式 127

示例6-4求各部门员工年龄的平均值 127

技巧6-6在功能区中打开“值字段设置”对话框 128

技巧6-7快捷菜单更改“值汇总依据” 128

6.2.4更改值的数字格式 129

示例6-5对年龄的平均值保留1位小数 129

6.2.5更改值的显示方式 129

示例6-6将数字字段以百分比形式显示 130

6.3设置数据透视表的显示方式 130

6.3.1活动字段的展开与折叠 131

示例6-7展开与折叠数据信息 131

6.3.2筛选数据透视表中的数据 131

示例6-8筛选列标签中的字段 132

6.3.3插入切片器并筛选数据透视表中的数据 132

示例6-9插入切片器进行数据筛选 132

技巧6-8更改切片器名称 134

技巧6-9使用一个切片器筛选多张数据透视表 134

6.3.4应用切片器样式 135

示例6-10为插入的切片器应用系统样式 135

6.4设置数据透视表格式 136

6.4.1设置数据透视表布局 136

示例6-11为数据透视表设置布局 136

6.4.2套用数据透视表样式 137

示例6-12套用数据透视表系统样式 137

6.4.3移动数据透视表 138

示例6-13在同一张工作表中移动数据透视表 138

6.5创建数据透视图 139

示例6-14在数据透视表中创建数据透视图 139

6.6编辑数据透视图 141

示例6-15更改数据透视图的布局、颜色以及样式 141

第7章 数据的直观表现——SmartArt图形与图表的应用 143

7.1使用SmartArt图形快速建立结构图 144

7.1.1插入图片类型的SmartArt图形 144

示例7-1创建组织层次结构图 144

技巧7-1各类型SmartAty图形的用法 145

7.1.2输入图形文本 145

示例7-2在图形中输入文本 145

技巧7-2在SmartArt图形中直接输入文本 146

7.1.3在SmartArt图形中插入图片 146

示例7-3在图形中插入图片 146

技巧7-3在文本窗格中插入图片 147

7.1.4设计SmartArt图形 147

示例7-4在图形中添加项目形状 147

技巧7-4在功能区中添加形状 149

技巧7-5切换图形布局方向 149

示例7-5升降项目级别 149

技巧7-6同级别项目的位置移动 150

技巧7-7利用Backspace键升高级别 150

示例7-6修改SmartArt图形样式 151

7.1.5格式化SmartArt图形 152

示例7-7为图形添加背景色 152

示例7-8更改SmartArt图形形状 154

技巧7-8增大或缩小图形形状 155

7.2快速应用图表 155

7.2.1常用图形的简介 155

7.2.2在功能区中快速选择图表类型 155

示例7-9快速应用图表 156

技巧7-9使用Alt＋F1组合键创建嵌入式图表 157

技巧7-10使用F11键创建图表工作表 157

技巧7-11使用推荐图表 157

7.2.3更改图表类型 157

示例7-10将柱形图改为条形图 157

技巧7-12重新创建图表 158

7.3设置图表格式 158

7.3.1图表布局和样式 158

示例7-11设置图表的布局和样式 159

7.3.2图表标题和坐标轴格式 159

示例7-12设置图表标签的布局和样式 159

技巧7-13在功能区中添加图表元素 161

7.3.3图表背景设置 161

示例7-13为图表设置背景 161

7.4对图表进行分析 163

7.4.1使用趋势线预测未来值 163

示例7-14根据前5个月的销售量预测下一月的销售情况 164

技巧7-14如何为数据选择最合适的趋势线 166

技巧7-15“当前所选内容”组中的2个选项 166

7.4.2为图表添加折线 166

示例7-15为图表添加合适的折线 166

技巧7-16切换图表的行/列 167

7.4.3为图表添加误差线 168

示例7-16为图表添加误差线 168

技巧7-17筛选图表数据 170

技巧7-18使用拖动法更改图表数据范围 170

7.5使用迷你图显示数据趋势 171

7.5.1创建迷你图分析数据 171

示例7-17创建迷你图显示数据趋势 171

技巧7-19清除迷你图 172

7.5.2修改迷你图 172

示例7-18修改迷你图的数据源 172

示例7-19套用迷你图样式 173

技巧7-20设置迷你图颜色和标记颜色 174

第8章 减少重复劳动——宏与VBA 175

8.1初识VBA 176

8.1.1VBA工作界面 176

示例8-1进入VBA的运行环境 176

技巧8-1用Alt＋F11键快速进入VBA环境 177

8.1.2VBA界面中的主要菜单栏及其功能 177

8.2认识VBA宏 178

8.2.1为工作表录制宏 178

示例8-2录制一个宏，将选中单元格的字体颜色变为红色、加粗 179

技巧8-2在“开发工具”选项卡下录制宏 179

8.2.2执行录制好的宏 180

示例8-3应用录制好的宏 180

技巧8-3利用快捷键执行宏命令 181

8.2.3编辑录制好的宏 181

示例8-4为录制好的宏添加更改字体大小的功能 181

技巧8-4利用快捷键打开快速查看宏 183

8.3行政与文秘工作者常用的10个VBA程序 183

示例8-5控制百分比显示 183

技巧8-5缩放代码窗口 187

示例8-6批量新建员工考勤表 187

示例8-7按日期合并销售额 190

示例8-8自动计算员工工龄 193

示例8-9调用内置函数进行销售统计 197

示例8-10制作出货表的动态数据透视表 199

示例8-11打印工资条 202

示例8-12导出Excel文件到文本文件中 204

示例8-13自动生成销售情况分析报告 207

示例8-14客户信息的简易交互输入 212

第9章 文件的高速输出——页面设置与打印 218

9.1页面设置 219

9.1.1设置页边距 219

示例9-1自定义页边距 219

9.1.2设置纸张的大小和方向 221

示例9-2设置纸张大小为A3并以横向显示 221

技巧9-1快速启动“页面设置”对话框 222

技巧9-2在“打印”选项下进行页面设置 222

9.1.3设置打印区域和顺序 222

示例9-3设置页面打印区域和打印顺序 223

9.1.4插入分页符并设置打印标题 224

示例9-4对插入分页符后的文档分页显示标题 224

技巧9-3错误值的显示形式 226

技巧9-4调整分页符位置 226

9.1.5设置页面背景 226

示例9-5在页面设置中添加背景 226

示例9-6设置可以打印出来的背景 228

技巧9-5将插入的背景复制为图片格式打印出来 229

9.2一次性成功打印文件 229

9.2.1同时打印多份文件 230

示例9-7打印多份设置好的文件 230

9.2.2打印工作表 230

示例9-8设计完整打印工作表流程 231

第10章 有效保护数据——保密与共享 233

10.1工作簿的保护 234

10.1.1设置密码保护工作簿 234

示例10-1为创建好的工作簿设置密码 234

技巧10-1“常规选项”对话框中的其他设置 236

10.1.2保护工作簿的结构 236

示例10-2执行“保护工作簿”命令来保护工作簿 236

技巧10-2使用数字签名保护工作簿 237

10.2工作表的保护 238

10.2.1取消工作表标签的显示 238

示例10-3隐藏工作表标签 238

10.2.2在工作簿中隐藏重要工作表 239

示例10-4可撤销的隐藏工作表 239

技巧10-3在功能区中隐藏/取消隐藏工作表 240

示例10-5不可撤销的彻底隐藏工作表 240

10.2.3加密保护工作表 242

示例10-6执行“保护工作表”命令来保护工作表 242

技巧10-4其他方式打开“保护工作表”对话框 243

技巧10-5撤销工作表的保护 243

10.3单元格的保护 243

10.3.1设置用户编辑区域 244

示例10-7设置用户可编辑的区域 244

技巧10-6添加多个受密码保护的锁定区域 247

技巧10-7对受密码保护的锁定区域的修改和删除 247

10.3.2保护部分单元格 247

示例10-8保护工作表中的部分单元格区域不被更改 247

技巧10-8设置允许在保护状态下插入行功能 249

10.3.3隐藏单元格中的核心数据 249

示例10-9将表中员工的电话号码隐藏 249

10.4创建与使用共享工作簿 251

10.4.1创建共享工作簿 251

示例10-10创建一个共享工作簿 252

10.4.2编辑共享工作簿 253

示例10-11突出显示修订 253

示例10-12接受其他用户对共享工作簿的修订 254

10.4.3断开用户与共享工作簿的连接 255

示例10-13断开与其他用户共享的工作簿 255

10.4.4停止工作簿的共享状态 257

示例10-14停止工作簿的共享 257

10.4.5云端办公 258

示例10-15用OneDrive账户保存文档 258

示例10-16共享OneDrive上的文件 259

技巧10-9共享中的“获取链接”方式 261

技巧10-10在网页中登录账号进入个人云盘 261

10.5套用在线模板 261

示例10-17在线套用费用类模板 261

10.6在Excel中导入不同类型的数据 263

10.6.1在Excel中导入Word文档内容 263

示例10-18将Word文档导入Excel中 263

技巧10-11直接在Excel中创建Word文档 265

技巧10-12以图标形式插入Word文档 265

10.6.2在Excel中导入Access数据 266

示例10-19将Access数据库导入Excel 266

10.6.3在Excel中导入文本数据 267

示例10-20将文本文档导入Excel中 267

10.6.4在Excel中导入网站中的数据 269

示例10-21将网站上的数据导入Excel 269

第11章 员工培训管理 271

11.1员工培训计划表 272

11.2员工专业知识培训日程安排表 275

技巧11-1以工作日填充单元格 280

11.3员工在职培训成绩表 280

11.4员工成绩查询表 286

技巧11-2复制公式与修改参数 289

11.5在职训练结训报表 289

第12章 员工的值班与出勤 293

12.1员工签到表 294

12.2员工值班安排表 298

12.3使用规划求解解决值班日期的安排 300

12.4员工请假单 304

12.5员工出勤分布图 307

12.6员工年假表 310

第13章 员工加班与出差管理 313

13.1员工加班记录表 314

13.2加班费申请表 317

13.3出差费用结算表 320

13.4业务人员出差报告表 323

13.5年度出差日数报告表 327

技巧13-1 SUBTOTAL函数 332

第14章 人员离职与人事管理 333

14.1人事日报表 334

14.2人事流动月报表 337

14.3辞职申请表 340

14.4员工离职结算单 344

14.5人员增加说明书 347

第15章 员工业绩评测 349

15.1员工季度业绩评定 350

15.2员工工作能力与工作态度评定表 353

15.3员工季度平均成绩和年度总成绩 357

15.4员工成绩排名 361

15.5员工年度业绩分析 363

第16章 员工薪资管理 367

16.1应扣请假费的计算 368

16.2全勤奖金的计算 370

16.3薪资总额的计算 372

16.4应扣所得税的计算 374

16.5员工薪资等级统计表 377

16.6员工个人薪资明细表 380

第17章 员工社会保险与福利 383

17.1员工社会保险登记表 384

17.2参加保险单位缴费申报表 386

17.3员工年度福利统计表 390

17.4参保人员信息更正申报表 393

17.5员工补交养老保险费申请表 395

第18章 办公用品管理 398

18.1办公用品流程图 399

18.2办公用品采购表 401

18.3办公用品采购记录表 404

18.4办公用品领用申请表 407

18.5办公用品采购报价单 409

第19章 会议管理 412

19.1会议室使用申请表 413

19.2会议议程安排表 415

19.3会议出席记录表 416

19.4会议纪要 419

19.5会议成效分析表 421

第20章 日常费用管理 423

20.1日常费用统计表 424

20.2各项费用月支出比较图 426

20.3日常费用支出预算表 429

20.4各部门日常费用花销比较图 432

20.5日常费用申报表 435

第21章 创建员工薪资查询系统 438

21.1创建工资查询窗体 439

21.2创建操作界面 446

21.3创建口令登录模板 450

21.4封装工作表 452

21.5保护VBA项目 454