Excel函数、公式与图表2025|PDF|Epub|mobi|kindle电子书版本百度云盘下载

Excel函数、公式与图表PDF格式电子书版下载

注意：本站所有压缩包均有解压码： 点击下载压缩包解压工具

第1篇 基础知识2

第1章 电子表格的基本操作2

1.1工作簿的操作2

1.1.1新建工作簿2

1.1.2保存工作簿5

1.1.3设置工作簿配色方案7

1.2工作表的操作9

1.2.1插入与删除工作表9

1.2.2重命名工作表11

1.2.3设置工作表标签颜色11

1.2.4移动与复制工作表12

1.2.5隐藏和显示工作表14

1.2.6调整工作表的显示比例15

1.3单元格的操作16

1.3.1插入、删除单元格16

1.3.2合并与拆分单元格18

1.3.3插入行或列18

1.3.4删除行或列20

1.3.5调整行高和列宽21

1.3.6隐藏含有重要数据的行或列22

第2章 数据的输入与编辑26

2.1输入各种不同类型的数据26

2.1.1输入文本内容26

2.1.2输入数值27

2.1.3输入日期与时间29

2.1.4输入特殊符号30

2.2以填充的方式批量输入数据32

2.2.1快速填充相同的数据32

2.2.2快速填充有规律的数据33

2.2.3“自动填充选项”功能的使用36

2.2.4自定义填充序列37

2.3编辑数据39

2.3.1移动数据39

2.3.2修改数据40

2.3.3复制和粘贴数据40

2.3.4查找与替换数据41

2.3.5删除数据44

2.3.6数据条件格式的设置44

2.3.7数据有效性47

第3章 公式运算基础操作52

3.1使用公式进行数据计算52

3.1.1公式的运算符52

3.1.2输入公式52

3.1.3编辑公式54

3.2公式计算中单元格的引用54

3.2.1引用相对数据源55

3.2.2引用绝对数据源55

3.2.3引用当前工作表之外的单元格57

3.3公式计算中名称的使用58

3.3.1将单元格区域定义为名称58

3.3.2使用定义的名称进行数据计算60

3.3.3将公式定义为名称60

3.3.4名称管理61

3.4公式审核工具的应用62

3.4.1显示公式62

3.4.2追踪引用单元格63

3.4.3使用“错误检查”功能辅助找寻公式错误原因64

3.4.4使用“公式求值”功能逐步分解公式65

第4章 Excel中各函数功能解析68

4.1函数基础68

4.1.1函数的构成68

4.1.2什么是函数参数69

4.1.3函数类型69

4.1.4使用工具栏按钮输入函数71

4.1.5如何选择合适的函数来解决问题71

4.1.6使用“插入函数”对话框输入函数71

4.1.7使用自动完成功能输入函数72

4.1.8为什么表格列标题显示的是数字而不是字母73

4.1.9如何不让函数提示信息遮掩到工作表的列号74

4.2函数错误值的解决方法75

4.2.1 “#####”错误值的处理75

4.2.2 “#DIV＞0！”错误值的处理76

4.2.3 “#N/A”错误值76

4.2.4 “#NAME？”错误值77

4.2.5 “#NUM！”错误值79

4.2.6 “#VALUE！”错误值80

4.2.7 “#REF！”错误值81

4.2.8 “#NULL！”错误值81

第5章 使用图表直观表现数据84

5.1了解图表的组成84

5.2将销售数据表现为图表格式85

5.3修改图表86

5.3.1图表大小和位置的调整86

5.3.2图表的复制和删除89

5.3.3图表类型的更改91

5.3.4将多张图表组合成一个对象92

5.3.5图表形状样式的设置93

5.3.6将建立的图表转化为静态图片94

5.4设置图表95

5.4.1设置图表标题95

5.4.2设置图例96

5.4.3设置图表区格式97

5.4.4添加数据标签99

5.4.5设置数据系列格式100

5.4.6设置坐标轴101

5.4.7设置网格线103

5.4.8添加涨/跌柱线106

5.5图表布局与样式套用107

5.5.1图表布局的设置107

5.5.2图表样式的设置108

第6章 使用数据透视表分析数据110

6.1数据透视表概述110

6.1.1什么是数据透视表110

6.1.2数据透视表的作用110

6.1.3何时应该使用数据透视表110

6.2了解数据透视表的结构111

6.2.1数据透视表的结构111

6.2.2数据透视表中的专业术语111

6.3创建数据透视表分析财务数据113

6.3.1新建数据透视表113

6.3.2通过设置字段得到统计结果114

6.4调整数据透视表获取不同分析结果115

6.4.1更改数据透视表字段布局115

6.4.2调整字段显示顺序117

6.4.3修改汇总计算117

6.4.4数据透视表的更新118

6.5显示明细数据119

6.6数据透视表布局的更改及样式套用120

6.6.1设置数据透视表分类汇总布局120

6.6.2更改数据透视表默认布局121

6.6.3通过套用样式快速美化数据透视表121

6.7数据透视图的建立122

6.7.1建立数据透视图123

6.7.2通过“图表布局”功能快速设置图表布局123

6.7.3通过“图表样式”功能快速设置图表样式124

6.7.4更改数据透视图的图表类型125

6.7.5在新工作表中显示数据透视图126

第2篇 行业案例129

第7章 函数、公式、图表在员工档案管理中的应用129

7.1设计员工档案管理表129

7.1.1建立员工档案表框架129

7.1.2表头文字和列标识输入与文字格式设置130

7.1.3表格区域单元格格式及数据有效性设置132

7.1.4设置公式自动返回相关信息135

7.2建立工作表用于查询员工档案138

7.2.1建立员工档案查询表框架139

7.2.2设置单元格的公式140

7.3分析员工的学历层次142

7.3.1建立数据透视表统计各学历人数142

7.3.2建立图表直观显示各学历人数分布144

7.4分析员工年龄层次145

7.4.1建立数据透视表统计各学历人数145

7.4.2建立图表直观显示年龄层次147

第8章 函数、公式、图表在考勤管理中的应用150

8.1建立考勤表并实现考勤数据统计150

8.1.1创建员工考勤记录表151

8.1.2填制考勤表158

8.1.3统计各员工本月请假天数、迟到次数及应扣款159

8.1.4分析各部门请假情况164

8.2建立加班工资统计表167

8.2.1使用建立的考勤记录表进行加班记录167

8.2.2加班工资统计169

第9章 函数、公式、图表在销售数据管理中的应用173

9.1建立产品销售数据统计报表173

9.1.1建立产品基本信息备案表173

9.1.2建立销售数据统计汇总表174

9.2“分类汇总”统计177

9.2.1统计各品种产品的交易数量、交易金额177

9.2.2统计各客户本期购买金额178

9.3利用函数、图表分析销售数据179

9.3.1使用SUMIF函数分析各品种产品销售情况179

9.3.2分析各销售人员业绩183

9.3.3使用SUMPRODUCT函数精确分析186

9.4利用数据透视表分析销售数据190

9.4.1分析客户购买情况190

9.4.2分析应收账款193

第10章 函数、公式、图表在产品库存管理中的应用199

10.1建立产品库存数据统计报表199

10.1.1建立产品基本信息备案表199

10.1.2建立出入库数据统计表200

10.2统计各品种产品的出入库数据203

10.2.1计算各品种产品的出入库数据204

10.2.2建立图表比较各品种入库数量与入库金额205

10.3利用函数实现按日查询出入库数据209

10.3.1建立出入库数据日汇总、查询表209

10.3.2设置公式实现自动统计指定日期出入库数据212

10.4利用函数实现各系列产品出入库明细查询215

10.4.1建立指定品种出入库记录查询表216

10.4.2建立公式达到自动查询的目的217

10.5出入库累计汇总220

10.5.1建立库存汇总统计表220

10.5.2汇总出入库数量、金额222

第11章 函数、公式、图表在企业日常费用支出与预算中的应用229

11.1建立日常费用支出统计表229

11.1.1规划日常费用支出统计表框架229

11.1.2费用支出表编辑区域数据有效性及单元格格式230

11.2分类汇总不同产生部门与不同类别的支出费用233

11.2.1统计不同部门产生的支出金额总计234

11.2.2统计不同类别费用的支出金额总计235

11.3利用数据透视表（图）统计费用支出额236

11.3.1统计各类别费用支出金额236

11.3.2分析各部门费用支出明细239

11.3.3比较各支出部门一月、二月费用支出额240

11.4日常支出费用预算分析242

11.4.1建立全年费用预算记录表242

11.4.2一月份实际费用与预算费用比较分析243

11.4.3建立其他月份实际费用与预算费用比较分析表248

第12章 函数、公式、图表在企业往来账款管理与分析中的应用253

12.1建立应收账款清单表253

12.1.1规划应收账款清单表的框架254

12.1.2设置公式计算未收金额、判断是否到期及计算未到期金额256

12.2计算各条应收账款的账龄257

12.3分客户统计应收账款259

12.3.1建立数据透视表统计各客户应收账款259

12.3.2建立数据透视图直观显示各客户应收账款261

12.4分客户分账龄分析应收账款262

12.4.1建立“分客户分账龄分析应收账款”表262

12.4.2使用图表直观分析各客户账龄264

12.5建立应付账款清单表265

12.5.1规划应付账款清单表的框架266

12.5.2设置公式分析各项应付账款267

12.5.3查看应付账款逾期天数超过30天的记录269

12.6各往来客户总应付账款统计270

12.6.1建立“各往来单位应付账款统计”表270

12.6.2分析各往来单位应付账款占比272

第13章 设计动态图表——动态显示企业各分部支出费用情况277

13.1图表效果展示与分析277

13.1.1图表最终效果277

13.1.2分析图表与数据的关系278

13.2建立动态图表数据源278

13.2.1 OFFSET函数的应用278

13.2.2使用OFFSET函数建立数据源279

13.3创建折线图281

13.4添加下拉菜单控制图表显示282

13.4.1添加控件工具栏282

13.4.2在工作表中添加“数值调节按钮”控件283

13.4.3在图表中添加控件284

13.5图表优化设置286

第14章 设计动态图表——动态显示商品房的销售情况291

14.1图表效果展示与分析291

14.1.1图表最终效果291

14.1.2分析图表与数据的关系291

14.2使用OFFSET为数据系列定义名称292

14.2.1建立辅助序列并设置图表名称引用单元格的公式292

14.2.2将图表数据系列定义为名称293

14.3使用定义的名称新建饼形图表295

14.4添加下拉菜单式控件控制“销售金额”、“首付金额”等类型显示296

14.5添加下拉菜单式控件控制“一居”、“二居”等户型显示298

14.6图表优化设置299

14.6.1添加文本框到图表299

14.6.2添加百分比标签并显示两位小数300

第3篇 提高技能304

第15章 文本、逻辑函数范例应用技巧304

15.1文本函数范例应用技巧304

技巧1 自动生成凭证号304

技巧2 自动比较两个部门的采购价格是否一致304

技巧3 提取产品的类别编码305

技巧4 从E-mail地址中提取账号306

技巧5 使用LEN函数验证身份证号码的位数306

技巧6 使用MID函数提取产品的类别编码307

技巧7 从身份证号码中提取出生年份308

技巧8 从身份证号码中提取完整的出生日期308

技巧9 从身份证号码中判别性别309

技巧10 将手机号码的后4位替换为特定符号310

技巧11 使用“★”为考评结果标明等级311

技巧12 分离8位电话号码的区号与号码312

技巧13 分离7位、8位混合显示的电话号码的区号与号码313

技巧14 从编码中提取合同号314

15.2逻辑函数范例应用技巧314

技巧1 IF函数配合LEFT函数根据代码返回部门名称314

技巧2 使用IF函数建立单元格条件格式315

技巧3 使用AND函数配合IF函数进行成绩考评316

技巧4 使用OR函数对员工的考核成绩进行综合评定316

技巧5 OR函数配合AND函数对考核成绩进行综合评定317

技巧6 使用IF与OR函数解决计算结果为“0”、错误值的问题317

技巧7 使用IF函数计算个人所得税318

技巧8 使用逻辑函数判断未来年份是闰年还是平年319

第16章 日期函数范例应用技巧322

技巧1 TODAY函数在账龄分析中的应用322

技巧2 将非日期数据转换为标准的日期323

技巧3 判断一个月的最大天数324

技巧4 使用DAYS360函数计算总借款天数324

技巧5 使用DAYS360函数计算还款剩余天数324

技巧6 使用DAYS360函数计算固定资产的已使用月份325

技巧7 自动填写报表中的月份326

技巧8 根据员工的出生日期快速计算其年龄326

技巧9 使用DATEDIF函数计算总借款天数327

技巧10 使用DATEDIF函数计算精确账龄327

技巧11 使用DATEDIF函数自动追加工龄工资328

技巧12 快速返回值班安排表中日期对应的星期数328

技巧13 快速返回日期对应的星期数（中文星期数）329

技巧14 使用YEAR与TODAY函数计算出员工工龄330

技巧15 使用YEAR与TODAY函数计算出员工年龄330

技巧16 计算两个日期之间的实际工作日331

技巧17 计算指定日期到月底的天数332

技巧18 使用EOMONTH函数查看指定年份各月天数333

技巧19 根据当前月份自动计算出本月日期334

技巧20 返回指定月份下指定日期对应的星期数334

第17章 数学、统计函数范例应用技巧336

17.1数学函数范例应用技巧336

技巧1 计算总销售额（得知每种产品的销售量与销售单价）336

技巧2 使用SUM函数统计不同时间段不同类别产品的销售笔数336

技巧3 使用SUM函数统计不同时间段不同类别产品的销售金额337

技巧4 按业务发生时间进行汇总339

技巧5 统计各部门工资总额339

技巧6 统计某个时段之前或之后的销售总金额340

技巧7 使用SUMIF函数统计两种类别或多种类别产品总销售金额341

技巧8 使用SUMIFS函数实现多条件统计341

技巧9 使用SUMIFS函数统计某一日期区域的销售金额342

技巧10 使用CEILING函数根据通话总秒数以7秒为计价单位来计算总话费342

技巧11 使用INT函数对平均销售量取整343

技巧12 使用SUMPRODUCT函数计算总销售额343

技巧13 从销售统计表中统计指定类别产品的总销售额344

技巧14 使用SUMPRODUCT函数同时统计出某两种型号产品的销售件数344

技巧15 统计出指定部门、指定职务的员工人数345

技巧16 统计出指定部门获取奖金的人数（去除空值）346

技巧17 统计出指定部门奖金大于固定值的人数346

技巧18 从学生档案表中统计出某一出生日期区间中指定性别的人数347

技巧19 统计指定店面指定类别产品的销售金额合计值347

17.2统计函数范例应用技巧348

技巧1 求平均值时忽略计算区域中的0值348

技巧2 按指定条件求平均值349

技巧3 同时满足多个条件求平均值349

技巧4 隔列计算各销售员的产品平均销售量350

技巧5 计算平均成绩（AVERAGE）350

技巧6 使用AVERAGEA函数求包含文本值的平均值351

技巧7 使用AVERAGEIF函数计算满足条件的数据的平均值352

技巧8 在AVERAGEIF函数中使用通配符353

技巧9 使用AVERAGEIFS函数计算出满足多重条件的数据的平均值353

技巧10 在AVERAGEIFS函数中使用通配符354

技巧11 使用COUNT函数统计销售记录条数354

技巧12 使用COUNT函数按条件统计355

技巧13 使用COUNTA函数统计包含文本值的单元格数355

技巧14 使用COUNTBLANK函数统计空白单元格的数目356

技巧15 使用COUNTIF函数统计出各类别产品的销售记录条数356

技巧16 使用COUNTIF函数统计出空白单元格的个数357

技巧17 利用COUNTIF函数统计出某一数据区间的数目357

技巧18 使用COUNTIF函数设置双条件358

技巧19 使用COUNTIF函数避免重复输入358

技巧20 统计出特定区域中指定值（多个值）的个数359

技巧21 使用COUNTIFS函数统计指定区域中满足多个条件的记录数目359

技巧22 返回数据表中前三名的数据360

技巧23 统计数据表中前五名的平均值360

技巧24 在LARGE函数中按指定条件返回第一名数据361

技巧25 在LARGE函数中按指定条件返回前三名的平均值361

技巧26 返回数据表中后三名的数据362

技巧27 统计数据表中后五名的平均成绩362

技巧28 使用MAX（MIN）函数统计最高（最低）销售量363

技巧29 按条件求取最大值363

技巧30 求最小值时忽略0值364

第18章 财务函数范例应用技巧366

18.1筹资计算中的函数范例应用技巧366

技巧1 计算贷款的每期偿还额366

技巧2 当支付次数为按季度支付时计算每期应偿还额366

技巧3 计算贷款每期偿还额中包含的本金额367

技巧4 计算贷款每期偿还额中包含的利息额367

技巧5 计算出住房贷款中每月还款利息额368

技巧6 计算贷款在指定期间中（如第三年）的本金金额368

技巧7 计算贷款在指定期间中（如第三年）的利息金额369

18.2投资计算中的函数范例应用技巧369

技巧1 计算购买某项保险的未来值369

技巧2 计算住房公积金的未来值370

技巧3 计算购买某项保险的现值371

技巧4 计算出某项贷款的清还年数371

技巧5 计算出某项投资的投资期数372

技巧6 计算企业项目投资净现值372

技巧7 计算某项投资的年金现值373

技巧8 计算出一组不定期盈利额的净现值373

技巧9 计算某项投资在可变利率下的未来值374

技巧10 计算投资期内要支付的利息374

技巧11 计算某项投资的内部收益率375

技巧12 计算某项投资的修正内部收益率375

技巧13 计算某项借款的收益率376

技巧14 计算某项保险的收益率376

18.3资产折旧计算中的函数范例应用技巧377

技巧1 采用直线法计算出固定资产的每年折旧额377

技巧2 采用直线法计算出固定资产的每月折旧额378

技巧3 采用直线法计算出固定资产的每天折旧额378

技巧4 采用固定余额递减法计算出固定资产的每年折旧额379

技巧5 采用固定余额递减法计算出固定资产的每月折旧额380

技巧6 采用双倍余额递减法计算出固定资产的每年折旧额380

技巧7 计算出固定资产部分期间的设备折旧值381

技巧8 采用年限总和法计算出固定资产的每年折旧额382

技巧9 采用直线法计算累计折旧额383

技巧10 采用余额递减法计算累计折旧额384

技巧11 采用年限总和法计算累计折旧额385

技巧12 使用AMORDEGRC函数计算每个会计期间的折旧值387

第19章 查找、数据库函数范例应用技巧390

19.1查找和引用函数范例应用技巧390

技巧1 使用CHOOSE函数判断员工考核成绩是否合格390

技巧2 使用CHOOSE函数评定多个等级390

技巧3 求取一组数据的反转数据391

技巧4 使用COLUMN函数建立有规律的三级序列编号391

技巧5 使用COLUMN函数配合其他函数使用392

技巧6 返回引用数据源包含的总列数392

技巧7 建立有规律的三级序列编号（ROW）393

技巧8 使用ROW函数自动控制要显示的行数393

技巧9 返回引用数据源包含的总行数394

技巧10 使用LOOKUP函数进行查询（向量型）395

技巧11 使用LOOKUP函数进行查询（数组型）395

技巧12 使用HLOOKUP函数获取数据396

技巧13 使用HLOOKUP函数实现查询397

技巧14 使用VLOOKUP函数进行查询397

技巧15 使用VLOOKUP函数合并两张表的数据398

技巧16 使用VLOOKUP函数进行反向查询399

技巧17 使用MATCH函数返回指定元素所在位置399

技巧18 使用INDEX函数实现查找（引用型）400

技巧19 使用INDEX函数实现查找（数组型）401

技巧20 使用INDEX配合其他函数实现查询出满足同一条件的所有记录402

技巧21 配合使用INDEX与MATCH函数实现查询403

技巧22 配合使用INDEX与MATCH函数实现双条件查询404

技巧23 配合使用INDEX与MATCH函数实现反向查询404

19.2数据库函数范例应用技巧405

技巧1 统计特定产品的总销售数量405

技巧2 实现双条件计算406

技巧3 统计出去除某一位或多位销售员之外的销售数量406

技巧4 使用通配符来统计满足条件的产品总销量407

技巧5 避免DSUM函数的模糊匹配408

技巧6 DSUM与SUMIF函数的区别410

技巧7 统计特定班级平均分410

技巧8 使用通配符统计出所有一店的平均利润410

技巧9 统计某一特定班级各个科目的平均分411

技巧10 计算出指定销售日期之前或之后的平均销售金额412

技巧11 统计满足条件的记录条数413

技巧12 使用DCOUNT函数实现双条件统计413

技巧13 从成绩表中统计出某一分数区间的人数414

技巧14 忽略0值统计记录条数414

技巧15 统计满足指定条件的且为“文本”类型的记录条数415

技巧16 使用DCOUNTA函数实现双条件统计416

技巧17 统计各班成绩最高分416

技巧18 统计各班成绩最低分417

第20章 图表创建、编辑与设置技巧420

20.1图表创建与编辑技巧420

技巧1 不连续数据源建立图表420

技巧2 图表数据源不连续时，实现向图表中增加数据源421

技巧3 为图表添加数据422

技巧4 使用颜色标记的区域向图表中添加数据423

技巧5 更新图表中的数据423

技巧6 创建混合型图表424

技巧7 将创建的图表保存为模板425

技巧8 使用模板新建图表426

技巧9 一次性设置图表中所有文字的格式426

技巧10 快速更改图表类型427

技巧11 将建立的图表转化为静态图片428

技巧12 隐藏工作表中的图表429

技巧13 保护图表不被随意更改430

技巧14 只允许对工作表中指定的某一个图表进行编辑431

20.2图表坐标轴设置技巧432

技巧1 删除（恢复）坐标轴的显示432

技巧2 根据图表需求更改刻度线标签的位置434

技巧3 重新更改分类轴标签的值435

技巧4 让垂直轴显示在右侧436

20.3图表数据系列设置技巧437

技巧1 显示单个系列的数据标志437

技巧2 显示单个数据点的数据标签437

技巧3 重新建立图表数据源与数据标签的链接438

技巧4 隐藏特定的数据系列439

技巧5 更改数据系列的显示次序440

技巧6 将饼图中特定的扇面分离出来440

20.4趋势线、误差线应用与设置技巧441

技巧1 向图表中添加趋势线441

技巧2 用R平方值判断趋势线的可靠性442

技巧3 使用移动平均趋势线443

技巧4 重新更改趋势线的名称444

技巧5 向图表中添加误差线445

技巧6 在图表中表示只允许固定的负值误差446

技巧7 快速设置各个数据点不同误差量时的误差线446