Excel应用与技巧大全 行政与文秘PDF|Epub|txt|kindle电子书版本网盘下载

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贷款偿还额函数PMT96

示例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章 减少重复劳动——宏与VBA175

8.1初识VBA176

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数据库导入Excel266

10.6.3在Excel中导入文本数据267

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

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

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

第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