EXCEL2010公式、函数与图表PDF电子书下载

Part 1基础知识 2

Chapter 01学习使用Excel 2010工作簿与工作表 2

1.1工作簿的创建与保存 2

1.1.1创建新工作簿 2

1.1.2保存工作簿 4

1.2 Excel 2010工作表的基本操作 5

1.2.1插入与删除工作表 5

1.2.2移动与复制工作表 6

1.2.3重命名工作表 7

1.2.4通过工作表标签颜色体现工作表的不同重要性 8

1.2.5隐藏和显示工作表 8

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

1.3单元格的基本操作 10

1.3.1插入和删除单元格 10

1.3.2插入行或列 10

1.3.3合并单元格 12

1.3.4调整单元格的高度与宽度 12

1.3.5隐藏含有重要数据的行或列 13

1.4保护工作表与工作簿数据 13

1.4.1保护工作表 13

1.4.2加密保护工作簿 14

Chapter 02电子表格中数据的输入与编辑 17

2.1不同类型数据的输入 17

2.1.1了解常规格式数据与文本格式数据 17

2.1.2输入不同的数值（小数、百分比、货币） 18

2.1.3输入日期和时间数据 19

2.1.4输入特殊符号 21

2.1.5用填充功能批量输入 21

2.1.6导入外部数据 23

2.2表格数据的修改、移动、复制与删除 25

2.2.1修改数据 25

2.2.2移动数据 25

2.2.3复制数据 26

2.2.4利用“粘贴预览”功能提前预览粘贴效果 26

2.2.5删除数据 27

2.3查找与替换数据 28

2.3.1表格数据的查找 28

2.3.2表格数据的替换 29

Chapter 03公式、函数计算的基础知识 32

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

3.1.1什么是公式 32

3.1.2公式运算符 32

3.1.3输入公式 33

3.1.4编辑公式 33

3.1.5复制公式完成批量计算 34

3.2公式计算中函数的使用 34

3.2.1函数的构成 34

3.2.2函数的参数及其说明 35

3.2.3了解函数类型与函数的帮助功能 35

3.2.4插入函数时使用“插入函数”向导 36

3.3公式计算中对单元格的引用 39

3.3.1引用相对数据源计算 39

3.3.2引用绝对数据源计算 40

3.3.3引用其他工作表中的单元格 41

3.3.4引用其他工作簿中的单元格 41

3.4公式中对名称的使用 42

3.4.1将公式中要使用的单元格区域定义为名称 42

3.4.2将定义的名称作为函数参数使用 43

3.5公式检测与审核 44

3.5.1追踪引用单元格 44

3.5.2追踪从属单元格 44

3.5.3“错误检查”功能 45

3.5.4公式分步求值 45

3.6公式返回错误值分析与解决办法 46

3.6.1 “#DIV/0！”错误值 46

3.6.2 “#N/A”错误值 47

3.6.3 “#NAME？”错误值 47

3.6.4 “ # N U M！”错误值 48

3.6.5 “#VALUE！”错误值 48

3.6.6 “#REF！”错误值 49

Chapter 04使用图表直观分析数据 51

4.1初识图表 51

4.1.1了解图表结构 51

4.1.2各图表类型应用范围解析 52

4.2创建新图表 56

4.2.1按分析目的创建针对性图表 57

4.2.2迷你图的应用 59

4.2.3变更图表类型 60

4.2.4变更图表数据源 60

4.3图表大小及位置调整 62

4.3.1调整图表大小 62

4.3.2更改图表位置 62

4.4图表的复制与删除 63

4.4.1以链接方式复制图表 63

4.4.2将图表复制为图片 64

4.4.3删除图表 65

4.5图表元素的添加 65

4.5.1添加图表标题 65

4.5.2重新设置坐标轴 65

4.5.3应用数据标签 66

4.6图表的美化 68

4.6.1设置图表文字格式 68

4.6.2设置图表中对象的填充效果 69

4.6.3设置图表中对象的形状特效 70

4.6.4套用图表样式快速美化图表 72

Chapter 05数据透视表与数据透视图 74

5.1了解数据透视表 74

5.1.1 数据透视表的作用及使用场合 74

5.1.2了解数据透视表的结构 74

5.2创建数据透视表分析数据 75

5.2.1创建数据透视表 75

5.2.2神奇的字段位置 77

5.3数据透视表的编辑、排序和筛选 79

5.3.1设置字段汇总方式、值显示方式 79

5.3.2对数据透视表统计结果进行排序 81

5.3.3在数据透视表筛选查看部分数据 82

5.3.4数据透视表的移动、刷新、删除、转换 84

5.3.5一键美化数据透视表 85

5.4用数据透视图直观查看分析结果 86

5.4.1创建数据透视图 86

5.4.2优化数据透视图 86

5.4.3数据透视图筛选查看 88

Chapter 06高级分析工具的应用 90

6.1安装与卸载分析工具库 90

6.1.1安装分析工具库 90

6.1.2卸载分析工具库 91

6.2模拟运算表 91

6.2.1单变量模拟运算 91

6.2.2双变量模拟运算 92

6.2.3将模拟运算结果转换为常量 93

6.2.4删除模拟运算结果 94

6.3单变量求解 94

6.3.1单变量求解预定销售计划 94

6.3.2单变量求解可贷款金额 95

6.3.3单变量求解贷款年限 96

6.4方案管理器 97

6.4.1建立方案 97

6.4.2显示方案 99

6.4.3合并方案 99

6.4.4编辑方案 101

6.4.5创建方案摘要 101

6.5规划求解 103

6.5.1规划求解确定最优生产方案 103

6.5.2规划求解确定最优运输方案 105

Part 2技巧点拨 110

Chapter 07逻辑、文本函数应用技巧 110

7.1逻辑、文本函数功能介绍 110

1.AND函数 110

2.NOT函数 110

3.OR函数 110

4.IF函数 110

5.CONCATENATE函数 110

6.FIND函数 111

7.LEFT函数 111

8.LEN函数 111

9.MID函数 111

10.REPT函数 111

11.RIGHT函数 112

12.SEARCH函数 112

13.SUBSTITUTE函数 112

14.TEXT函数 112

7.2逻辑函数范例 113

1.判断给定值是否在指定区间 113

2.根据多项成绩判断最终考评结果是否合格 113

3.根据多项培训成绩判断员工是否参加二次考评 113

4.快速比较各商品两个月份的采购价格是否相同 114

5.库存量过小时提示补货 114

6.根据消费卡类别与消费额派发赠品 115

7.根据职工性别和职务判断退休年龄 116

8.计算个人所得税 117

7.3文本函数范例 118

1.合并商品货号、码数及颜色 118

2.从商品全称中提取产地 118

3.检验具有固定长度的字符串输入的位数是否正确 119

4.一次性输入多个相同符号 119

5.分离电话号码的区号与号码 119

6.从产品编码中提取产品的货号 120

7.去掉多余的空格 120

8.根据人员姓名统计各项课程的实际参加人数 121

9.根据商品的名称与颜色进行一次性调价 121

10.发票金额的分列填写 122

11.从项目编码中提取合同号 123

12.合并编码时不忽略前面的0 123

13.让合并的日期显示正确格式 124

14.让计算得到金额显示为“余款：15，850.00”形式 124

15.解决总金额无法计算的问题 125

Chapter 08日期、时间函数应用技巧 127

8.1日期、时间函数功能介绍 127

1.DATE函数 127

2.DATEDIF函数 127

3.DAY函数 127

4.DAYS360函数 127

5.EOMONTH函数 128

6.MONTH函数 128

7.NETWORKDAYS函数 128

8.TODAY函数 128

9.WEEKDAY函数 129

10.WORKDAY函数 129

11.YEAR函数 129

12.HOUR函数 129

13.MINUTE函数 129

14.SECOND函数 130

8.2日期函数范例 130

1.返回当前日期与星期数 130

2.已知一年中的第几天，返回其完整日期 130

3.创建倒计时牌 130

4.将文本日期转换为标准日期 131

5.利用TODAY函数进行账龄统计 131

6.计算总借款天数 132

7.判断借款是否逾期 132

8.自动填写报表中的月份 132

9.根据出生日期快速得出年龄 133

10.计算借款至今日时长（显示为＊年＊月＊日形式） 133

11.根据工龄自动追加工龄工资 134

12.返回值班日期对应的星期数 134

13.判断值班日期是平时加班还是双休日加班 135

14.计算请假天数占全年工作日的百分比 135

15.计算指定日期到月底的天数 136

16.实现查询指定年份中各月的天数 136

8.3时间函数范例 137

1.计算来访签到时间的区间 137

2.计算停车时间 137

3.计算通话秒数 137

4.计算高于或低于标准时间的值 138

Chapter 09数学函数应用技巧 140

9.1数学函数功能介绍 140

1.SUM函数 140

2.SUMIF函数 140

3.SUMIFS函数 140

4.SUMPRODUCT函数 140

5.ABS函数 141

6.INT函数 141

7.MOD函数 141

8.CEILING函数 141

9.FLOOR函数 141

10.ROUND函数 141

11.ROUNDDOWN函数 142

12.ROUNDUP函数 142

9.2求和函数范例 142

1.根据各月份预算费用一步计算总预算费用 142

2.得知每个课程的人数与价格，用SUM函数求取总金额 142

3.利用SUM函数统计某一经办人的总销售金额 143

4.统计每个月的出库总数量 143

5.统计各部门工资总额 144

6.统计某个时段的销售金额 144

7.在SUMIF函数中使用通配符 145

8.计算销售金额前三名合计值 145

9.统计指定地点指定时间的销售金额 145

10.统计某一日期区间的销售金额 146

11.根据销售数量与单价计算总销售额 146

12.统计指定类别产品的总销售额（表格中只统计了销售数量与单价） 146

13.统计某两位（或多位）经办人的总销售金额 147

14.统计指定部门获取奖金的人数（去除空值） 147

15.统计指定班级分数大于指定值的人数 148

16.统计指定店铺各品牌产品的销售金额合计值 148

17.统计非工作日的销售金额 149

9.3其他数学函数范例 149

1.ABS函数在数据比较中的应用 149

2.平均销售数量时取整数 149

3.正数显示总支出金额 150

4.计算每位员工的加班时长 150

5.以1个百分点为单位计算奖金或扣款 151

6.计算物品的快递费用 151

7.计算上网费用 152

8.以6秒（不足6秒按6秒计算）为单位计算通话费用 153

9.计件工资中的奖金计算 153

10.计算商品运送车次 153

11.解决浮点运算造成ROUND函数计算不准确的问题 154

Chapter 10统计函数应用技巧 156

10.1统计函数功能介绍 156

1.AVERAGE函数 156

2.AVERAGEA函数 156

3.AVERAGEIF函数 156

4.AVERAGEIFS函数 156

5.MAX函数 156

6.LARGE函数 157

7.MIN函数 157

8.SMALL函数 157

9.MODE.MULT函数 157

10.COUNT函数 157

11.COUNTA函数 157

12.COUNTBLANK函数 158

13.COUNTIF函数 158

14.COUNTIFS函数 158

15.MEDIAN函数 158

16.PERCENTILE.INC函数 158

17.RANK.EQ函数 159

10.2求平均值函数范例 159

1.忽略0值求平均分数 159

2.统计各班级的平均分 159

3.统计数据表中前5名的平均值 160

4.统计各部门的平均工资 160

5.计算每月平均出库数量 161

6.计算销售利润时使用通配符 161

7.计算一车间女职工平均工资 162

8.计算指定班级平均分并且忽略0值 162

9.求介于某一区间内的平均值 162

10.统计指定店面所有男装品牌的平均利润 163

10.3求最大/最小值函数范例 163

1.利用LARGE函数按指定条件返回最大值 163

2.利用LARGE函数求解指定部门中前3名销售额之和 164

3.利用SMALL函数按指定条件返回最小值 165

4.快速返回所有店铺最高销售金额与最低销售金额 165

5.返回上半个月单笔最高销售金额 166

6.返回企业女员工的最大年龄 166

7.忽略0值求最小值 167

10.4数目统计函数范例 167

1.统计成绩表中缺考的人数 167

2.根据参加课程人员的姓名统计总人数 168

3.统计测试时无故障的机器台数 168

4.统计大于某个单元格中值的记录条数 168

5.统计成绩大于平均分数的学生人数 169

6.使用通配符统计某一类数据的记录条数 169

7.统计是三好学生且参加数学竞赛的人数 170

8.统计各店面男装的销售记录条数（双条件） 170

9.统计指定时间指定类别商品的销售记录数 171

10.统计指定类型指定影院指定时间的影片放映数量 171

11.统计指定产品每日的销售记录数 172

12.统计一组数据中出现次数最多的数据 172

13.一次性统计一组数据中各数据出现的次数 173

10.5数据排位函数范例 173

1.为各销售员的全年销售金额排名次 173

2.对不连续单元格排名次 174

3.将不在同一列中的数据统一排名 174

4.只显示满足条件的排名 175

5.返回数值区域的K百分比数值点 175

6.统计全年各月利润值中的中位数 176

Chapter 11财务函数应用技巧 178

11.1财务函数功能介绍 178

1.FV函数 178

2.PV函数 178

3.N PV函数 178

4.XNPV函数 178

5.PMT函数 179

6.PPMT函数 179

7.IPMT函数 179

8.ISPMT函数 179

9.NPER函数 180

10.RATE函数 180

11.XIRR函数 180

12.IRR函数 180

13.MIRR函数 180

14.DB函数 181

15.DDB函数 181

16.SLN函数 181

17.SYD函数 181

18.VDB函数 182

11.2投资计算函数应用 182

1.当支付次数为按季度（月）支付时计算每期应偿还额 182

2.快速计算各年偿还额中包含的本金 182

3.快速计算各期偿还额中包含的利息额 183

4.计算住房贷款中每月还款利息额 183

5.在等额本金还款方式下计算某项贷款的利息 184

6.计算住房公积金的未来值 184

7.计算某项投资的投资期数 184

8.判断购买某项保险是否合算 185

9.计算某投资的净现值 185

10.计算一组不定期盈利额的净现值 186

11.3偿还率计算函数应用 186

1.计算某项投资的内部收益率 186

2.计算一组不定期盈利额的内部收益率 186

3.计算某项投资的修正内部收益率 187

4.计算购买某项保险的收益率 187

11.4折旧计算函数应用 188

1.直线法计算固定资产的每月折旧额 188

2.直线法计算固定资产的每天折旧额 188

3.固定余额递减法计算每年折旧 188

4.固定余额递减法计算固定资产的每月折旧额 189

5.年限总和法计算每年折旧 189

6.双倍余额递减法计算每年折旧额 189

7.利用直线法计算累计折旧额 190

8.利用双倍余额递减法计算累计折旧额 190

Chapter 12查找、数据库函数应用技巧 193

12.1查找、数据库函数功能介绍 193

1.ADDRESS函数 193

2.CHOOSE函数 193

3.COLUMN函数 193

4.COLUMNS函数 193

5.HLOOKUP函数 194

6.INDEX函数 194

7.INDIRECT函数 194

8.LOOKUP函数（向量型语法） 195

9.LOOKUP函数（数组型语法） 195

10.MATCH函数 195

11.OFFSET函数 195

12.ROW函数 196

13.ROWS函数 196

14.VLOOKUP函数 196

15.DSUM函数 196

16.DAVERAGE函数 197

17.DCOUNT函数 197

18.DCOUNTA函数 197

19.DGET函数 197

20.DMAX函数 198

21.DMIN函数 198

12.2查找和引用函数范例 198

1.使用LOOKUP函数（向量型语法）实现自动查询 198

2.使用LOOKUP函数（数组型语法）实现自动查询 199

3.使用HLOOKUP函数查询某一科目的成绩 200

4.根据值班日期自动返回工资标准 200

5.在销售表中自动返回产品单价 201

6.根据多条件计算员工年终奖 201

7.使用VLOOKUP函数进行反向查询 202

8.查找指定月份指定专柜的销售金额 202

9.返回成绩最高的学生的姓名 203

10.查询总金额最高的对应的店铺（反向查询） 203

11.求取一组数据的反转数据 204

12.根据产品不合格率决定产品处理办法 204

13.查找最大销售额所在位置 205

14.解决合并单元格引用数据列出现跳跃的问题 205

15.使用ROW函数控制年份序列的显示行数 206

16.按奇数月与偶数月统计销量 206

17.实现数据的动态查询 207

18.对每日出库数量累计求和 208

12.3数据库函数范例 208

1.使用DSUM函数实现满足双条件求和 208

2.计算总工资时去除某一个（或多个）部门 209

3.使用通配符统计一类数据之和 209

4.避免DSUM函数的模糊匹配 210

5.计算指定班级平均分 211

6.计算指定车间指定性别员工的平均工资（双条件） 211

7.查询指定班级各科目平均成绩 211

8.统计满足条件的记录条数 212

9.统计同时满足两个条件时的记录条数 212

10.从成绩表中统计某一分数区间的人数 213

11.忽略0值统计记录条数 213

12.统计满足指定条件的且为“文本”类型的记录条数 213

13.统计指定性别测试合格的人数 214

14.返回指定车间指定性别员工的最高工资 214

15.查询指定班级各科目中的最高分 214

16.从数据库中提取符合条件的单个值 215

Chapter 13图表使用中的常用技巧 217

13.1图表设置技巧 217

1.按实际需要更改系列名称 217

2.重新调整数据系列的显示次序 217

3.一张图表使用两种图表类型 218

4.让图表绘制出被隐藏的数据源 218

5.以0值替代数据源的空值 219

6.设置竖排标题 219

7.让图表标题与表格标题同步更新 220

8.将垂直轴显示在图表正中位置 220

9.让水平轴标签从右向左显示 221

10.解决条形图垂直轴标签显示次序颠倒的问题 221

11.根据图表需求更改刻度线标签的位置 222

12.设置数据系列分离（重叠）显示 223

13.将看不到的系列绘制于次坐标轴上 223

14.解决更改图表数据源数据标签不能同步更改的问题 225

15.使用对数刻度绘制图表 225

16.使创建的复合饼图达到表达效果 226

17.固定图表的大小与位置让其不改变 227

18.快速取消图表的所有格式 227

13.2图表安全保护及协同使用 227

1.隐藏工作表中的图表 227

2.只允许对指定图表进行编辑 228

3.保护图表不被随意更改 228

4.实现在Word中编辑Excel图表 228

5.在PowerPoint演示文稿中使用Excel图表 229

Part 3行业应用 231

Chapter 14 公式、函数、图表在考勤管理与分析中的应用 231

范例制作 231

14.1创建考勤表记录员工出勤情况 231

14.1.1创建可自由选择年、月的考勤表表头 231

14.1.2根据当前年、月自动显示当月日期及对应星期数 234

14.1.3创建考勤记录表表体并按实际情况记录考勤 238

14.2汇总每位员工本月出勤数据 240

14.2.1统计各员工本月出勤数据 240

14.2.2计算满勤奖、应扣工资 242

范例应用 243

14.3比较分析本月各部门缺勤情况 243

14.3.1建立数据透视表分析各部门缺勤情况 243

14.3.2建立数据透视图直观比较缺勤情况 244

14.4本月出勤率计算及趋势分析 246

14.4.1计算每日出勤率 246

14.4.2建立全月出勤率走势折线图 248

范例扩展 249

14.5员工考勤表 249

14.6员工请假流程图 250

Chapter 15公式、函数、图表在加班管理中的应用 252

范例制作 252

15.1统计分析逐条记录的加班情况 252

15.1.1建立加班记录表 252

15.1.2计算加班费 254

15.1.3不同加班类型的加班时长比较 256

15.1.4每位员工加班总时数比较图表 258

15.1.5不同加班类型总时数比较图表 258

15.2统计分析按考勤方式记录的加班情况 259

15.2.1建立加班记录表 259

15.2.2加班时长统计表及加班费的计算 261

15.2.3查看本月未加班的员工 263

15.2.4各部门的加班费比较分析 264

范例扩展 266

15.3加班申请单 266

15.4值班提醒表 266

Chapter 16公式、函数、图表在档案管理中的应用范例制作 268

16.1建立档案管理表格 268

16.1.1建立员工档案表 268

16.1.2设置公式自动返回相关信息 271

范例应用 274

16.2输入任意员工姓名查询其档案 274

16.2.1建立员工档案查询表框架 274

16.2.2设置单元格的公式并实现查询 276

16.3员工学历层次的分析 277

16.3.1建立数据透视表分析员工学历层次 277

16.3.2用数据透视图直观显示各学历占比情况 278

16.4员工年龄层次的分析 279

16.4.1建立数据透视表分析员工年龄层次 279

16.4.2用数据透视图直观显示各年龄段人数占比 281

16.5分析员工稳定性 281

16.5.1建立数据透视表统计各工龄段的人数 282

16.5.2用数据透视图直观显示各工龄段人数占比 283

范例扩展 283

16.6员工个人资料登记表 283

16.7管理人才储备表 284

Chapter 17 公式、函数、图表在销售管理及业绩考核中的应用 286

范例制作 286

17.1建立产品销售数据统计表 286

17.1.1建立企业产品备案表 286

17.1.2创建销售记录表 286

17.1.3设置公式实现只输入产品编码自动返回品牌、产品名称等信息 287

范例应用 288

17.2统计各类别产品的销售数据 288

17.2.1使用SUMIF函数建立公式 288

17.2.2用图表直观显示各类别产品的销售占比 290

17.3分析各销售人员业绩 291

17.4统计各销售员销售各品牌产品的金额 293

17.4.1使用SUMPRODUCT函数建立公式 293

17.4.2建立图表直观比较各品牌商品销售金额与各销售员金额 295

17.5查询各产品的最高售价及对应的销售员 296

17.6销售员全年业绩排名、等级、趋势分析 298

17.6.1建立变化趋势迷你图 299

17.6.2计算平均销售金额、总销售金额、排名次 300

范例扩展 302

17.7销售单据 302

17.8销售费用计划报表 302

Chapter 18 公式、函数、图表在库存管理、分析与查询中的应用 304

范例制作 304

18.1建立入库数据管理表与出库数据管理表 304

18.1.1建立商品基本信息表 304

18.1.2建立入库数据管理表 304

18.1.3建立出库数据管理表 307

18.2建立库存数据统计表 308

18.2.1设置公式返回产品基本信息 308

18.2.2设置公式计算本期入库、出库与库存 310

范例应用 312

18.3出、入库数据统计分析 312

18.3.1从各供应商处采购汇总 313

18.3.2采购的各系列产品的汇总 314

18.3.3比较各系列商品的出、入库数据 315

18.3.4查询期末库存较小的记录 317

18.4根据本期出、入库数据计算销售成本、利润等 318

范例扩展 321

18.5进货单据 321

18.6商品进销存明细表 321

Chapter 19动态图表的应用分析——按钮调节式 323

范例制作 323

19.1选择公司对各月数据进行比较 323

19.1.1建立动态图表数据源 323

19.1.2添加控件按钮控制可变单元格的值 325

19.1.3建立动态图表 326

19.2选择某月份对各公司数据进行比较 328

19.2.1建立动态图表数据源 328

19.2.2添加控件按钮控制可变单元格的值 330

19.2.3建立动态图表 331

范例扩展 333

19.3创建列表框式动态折线图 333

Chapter 20动态图表的应用分析——下拉列表式 335

范例制作 335

20.1建立动态图表数据系列并创建图表 335

20.1.1建立辅助序列 335

20.1.2将图表数据系列定义为名称 336

20.1.3使用定义的名称建立图表 337

20.2添加控件控制图表的显示 339

20.2.1添加组合框控制“销售金额”、“首付金额”、“销售套数” 339

20.2.2添加组合框控制“一居”、“二居”等户型 339

20.3图表优化设置 340

20.3.1添加文本框到图表中 340

20.3.2添加百分比数据标签并显示两位小数 341

20.3.3动态图表美化及效果测试 342

20.4将图表与图表内各对象组合为一个对象 343

范例扩展 344

20.5创建单选项式动态圆环图 344