Excel函数.公式与图表

第1篇 基础知识 第1章 电子表格的基本操作 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章 数据的输入与编辑 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章 公式运算基础操作 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中各函数功能解析 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“#!”错误值 81 第5章 使用图表直观表现数据 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章 使用数据透视表分析数据 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篇 行业案例 第7章 函数、公式、图表在员工档案管理中的应用 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章 函数、公式、图表在考勤管理中的应用 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章 函数、公式、图表在销售数据管理中的应用 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章 函数、公式、图表在产品库存管理中的应用 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章 函数、公式、图表在企业日常费用支出与预算中的应用 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章 函数、公式、图表在企业往来账款管理与分析中的应用 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章 设计动态图表——动态显示企业各分部支出费用情况 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章 设计动态图表——动态显示商品房的销售情况 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篇 技能提高 第15章 文本、逻辑函数范例应用技巧 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 技巧1IF函数配合LEFT函数根据代码返回部门名称 314 技巧2使用IF函数建立单元格条件格式 315 技巧3使用AND函数配合IF函数进行成绩考评 316 技巧4使用OR函数对员工的考核成绩进行综合评定 316 技巧5OR函数配合AND函数对考核成绩进行综合评定 317 技巧6使用IF与OR函数解决计算结果为“0”、错误值的问题 317 技巧7使用IF函数计算个人所得税 318 技巧8使用逻辑函数判断未来年份是闰年还是平年 319 第16章 日期函数范例应用技巧 技巧1TODAY函数在账龄分析中的应用 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章 数学、统计函数范例应用技巧 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章 财务函数范例应用技巧 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章 查找、数据库函数范例应用技巧 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 技巧6DSUM与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章 图表创建、编辑与设置技巧 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图表数据系列设置技巧 4377 技巧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