Excel函数与公式应用大全for Excel 365 & Excel 2021_Excel Home 编著_9787301346631

本书以 Excel 2021 为蓝本，全面系统地介绍了 Excel 365 & Excel 2021 函数与公式的技术原理、应用技巧与实战案例。内容包括函数与公式基础，文本处理、查找引用、统计求和、Web 类函数、宏表函数、自定义函数、数据库函数等常用函数的应用，以及数组公式、动态数组、多维引用等。本书采用循序渐进的方式，由易到难地介绍各个知识点，适合各个水平的 Excel 用户，既可作为初学者的入门指南，又可作为中、高级用户的参考手册。

目录绪论如何学习函数公式1 1. 学习函数很难吗1 2. 从哪里学起1 3. 如何深入学习2 第一篇函数与公式基础第1章认识公式6 1.1 公式和函数的概念6 1.2 公式的输入、编辑与删除7 1.2.1 输入7 1.2.2 修改7 1.2.3 删除8 1.3 公式的复制与填充8 1.4 设置公式保护9 1.5 浮点运算误差11 练习与巩固13 第2章公式中的运算符和数据类型14 2.1 认识运算符14 2.1.1 运算符的类型14 2.1.2 运算符的优先顺序15 2.1.3 括号与嵌套括号15 2.2 认识数据类型16 2.2.1 常见的数据类型 16 2.2.2 数据的比较18 2.3 数据类型的转换19 2.3.1 逻辑值与数值的转换19 2.3.2 文本型数字与数值的转换19 练习与巩固20 第3章单元格引用类型21 3.1 A1引用样式和R1C1引用样式21 3.1.1 A1引用样式21 3.1.2 R1C1引用样式21 3.2 相对引用、绝对引用和混合引用22 3.2.1 相对引用22 3.2.2 绝对引用23 3.2.3 混合引用23 3.2.4 切换引用方式25 3.3 单元格引用中的“隐式交集”25 3.4 单元格变动对单元格引用的影响26 练习与巩固26 第4章跨工作表引用和跨工作簿引用27 4.1 引用其他工作表区域27 4.2 引用其他工作簿中的单元格28 4.3 引用连续多工作表的相同区域28 4.3.1 三维引用输入方式28 4.3.2 用通配符输入三维引用29 练习与巩固29 第5章表格和结构化引用30 5.1 创建表格30 5.2 表格的特点31 5.3 表格应用范围的变化31 5.4 表格中的计算32 5.4.1 计算列32 5.4.2 汇总行33 5.4.3 表格的结构化引用34 练习与巩固34 第6章认识Excel函数36 6.1 Excel函数的概念36 6.1.1 Excel函数的结构36 6.1.2 可选参数与必需参数36 6.1.3 优先使用函数37 6.2 常用函数的分类38 6.3 函数的易失性39 练习与巩固39 第7章函数的输入与查看函数帮助40 7.1 输入函数的几种方式40 7.1.1 使用“自动求和”按钮插入函数40 7.1.2 使用函数库插入已知类别的函数41 7.1.3 使用“插入函数”向导搜索函数41 7.1.4 使用公式记忆式键入手工输入41 7.1.5 活用函数屏幕提示工具42 7.2 查看函数帮助文件42 练习与巩固43 第8章公式结果的验证和限制44 8.1 公式结果的验证44 8.1.1 简单统计公式结果的验证44 8.1.2 使用键查看运算结果44 8.1.3 使用公式求值查看分步计算结果44 8.1.4 单元格追踪与监视窗口45 8.2 函数与公式的限制47 8.2.1 计算精度限制47 8.2.2 公式字符限制47 8.2.3 函数参数的限制47 8.2.4 函数嵌套层数的限制48 练习与巩固48 第9章使用命名公式——名称49 9.1 认识名称49 9.1.1 名称的概念49 9.1.2 为什么要使用名称49 9.2 定义名称的方法50 9.2.1 认识名称管理器50 9.2.2 在【新建名称】对话框中定义名称51 9.2.3 使用【名称框】快速创建名称51 9.2.4 根据所选内容批量创建名称52 9.3 名称的级别53 9.3.1 工作表级名称和工作簿级名称53 9.3.2 多工作表名称54 9.4 名称命名的限制55 9.5 名称可使用的对象56 9.5.1 Excel创建的名称56 9.5.2 使用常量56 9.5.3 使用函数与公式56 9.6 名称的管理57 9.6.1 名称的修改与备注信息57 9.6.2 筛选和删除错误名称58 9.6.3 在单元格中查看名称中的公式58 9.7 名称的使用59 9.7.1 输入公式时使用名称59 9.7.2 现有公式中使用名称59 9.8 定义名称的技巧60 9.8.1 在名称中使用不同引用方式60 9.8.2 引用位置始终指向当前工作表内的单元格61 9.8.3 公式中的名称转换为单元格引用61 9.9 使用名称的注意事项62 9.9.1 工作表复制时的名称问题62 9.9.2 单元格或工作表删除引起的名称问题62 9.9.3 使用复杂公式定义名称63 9.10 使用INDIRECT函数创建不变的名称63 9.11 定义动态引用的名称63 9.11.1 使用函数公式定义动态引用的名称63 9.11.2 利用“表”区域动态引用64 9.12 使用LET函数在公式内部创建名称65 练习与巩固66 第二篇常用函数第10章文本处理技术68 10.1 认识文本型数据68 10.1.1 在公式中输入文本68 10.1.2 空单元格与空文本68 10.1.3 文本型数字与数值的互相转换69 10.2 文本函数应用69 10.2.1 用EXACT函数判断字符是否完全相同70 10.2.2 用UNICODE函数和UNICHAR函数完成字符与编码转换71 10.2.3 用UPPER函数和LOWER函数转换大小写72 10.2.4 转换单字节字符与双字节字符72 10.2.5 用LEN函数和LENB函数计算字符、字节长度72 10.2.6 CLEAN函数和TRIM函数清除多余空格和不可见字符72 10.2.7 使用NUMBERVALUE函数转换不规范数字73 10.2.8 字符替换74 10.2.9 字符串提取与拆分77 10.2.10 在字符串中查找关键字符87 10.2.11 字符串合并90 10.2.12 格式化文本92 10.2.13 认识T函数100 练习与巩固100 第11章信息提取与逻辑判断101 11.1 使用CELL函数获取单元格信息101 11.1.1 使用CELL函数获取当前工作簿名和工作表名102 11.1.2 使用CELL函数获取单元格数字格式104 11.2 常用IS类判断函数105 11.3 其他信息类函数106 11.3.1 用ERROR.TYPE函数判断错误值类型106 11.3.2 用TYPE函数判断数据类型106 11.3.3 用N函数将文本字符转换为数值106 11.3.4 用INFO函数返回操作环境信息107 11.4 逻辑判断函数107 11.4.1 逻辑函数TRUE和FALSE107 11.4.2 逻辑函数AND、OR、NOT、XOR与四则运算107 11.4.3 IF函数判断条件“真”“假”109 11.4.4 使用IFS函数实现多条件判断109 11.4.5 SWITCH函数多条件判断110 11.4.6 IFERROR和IFNA函数112 11.5 屏蔽错误值112 11.5.1 忽略原公式本身结果112 11.5.2 保留原公式本身结果114 11.5.3 利用计算规则屏蔽错误值115 11.5.4 利用函数规则屏蔽错误值115 11.5.5 其他115 练习与巩固116 第12章数学计算117 12.1 序列函数117 12.2 四则运算117 12.2.1 加、减法运算117 12.2.2 乘法运算118 12.2.3 除法运算118 12.3 幂运算与对数运算120 12.3.1 平方根函数120 12.3.2 乘方与开方运算120 12.3.3 对数运算121 12.3.4 其他平方运算122 12.4 其他数学计算122 12.4.1 绝对值122 12.4.2 最大公约数和最小公倍数123 12.4.3 排列组合运算123 12.5 取舍函数123 12.5.1 取整函数123 12.5.2 舍入函数125 12.5.3 倍数舍入函数126 12.6 数学转换函数127 12.6.1 弧度与角度的转换127 12.6.2 度分秒数据的输入和度数的转换127 12.6.3 罗马数字和阿拉伯数字的转换128 12.7 随机数函数128 12.8 数学函数的综合应用130 12.8.1 个人所得税计算130 12.8.2 替代IF多层嵌套131 练习与巩固131 第13章日期和时间计算133 13.1 输入日期和时间数据133 13.1.1 输入日期数据133 13.1.2 输入时间数据134 13.2 日期时间格式的转换136 13.2.1 文本型日期时间转“真日期”和 “真时间”136 13.2.2 “真日期”“真时间”转文本型日期和文本型时间 136 13.2.3 日期时间的“万能”转换函数136 13.2.4 其他转换137 13.3 处理日期时间的函数139 13.3.1 用TODAY函数和NOW函数显示当前日期与时间139 13.3.2 用YEAR、MONTH、HOUR函数等 “拆分”日期与时间139 13.3.3 用DATE函数和TIME函数“合并”日期与时间141 13.4 星期函数142 13.4.1 提取星期值142 13.4.2 用WEEKNUM函数判断周数144 13.5 季度运算146 13.5.1 利用日期函数和数学运算计算季度146 13.5.2 利用财务函数计算季度146 13.6 日期时间间隔147 13.6.1 日期时间的加、减等运算147 13.6.2 计算日期间隔的函数149 13.6.3 计算指定月份后的指定日期153 13.6.4 用YEARFRAC函数计算间隔天数占全年的比例155 13.7 工作日间隔156 13.7.1 计算指定工作日后的日期156 13.7.2 计算两个日期之间的工作日天数158 13.8 日期函数的综合运用161 练习与巩固165 第14章查找与引用函数167 14.1 基础查找与引用函数167 14.1.1 用ROW函数和ROWS函数返回行号和行数167 14.1.2 用COLUMN函数和COLUMNS函数返回列号和列数168 14.1.3 使用ROW函数和COLUMN函数的注意事项169 14.1.4 ROW函数和COLUMN函数的典型应用169 14.1.5 用ADDRESS函数获取单元格地址170 14.2 用VLOOKUP函数查询数据171 14.2.1 VLOOKUP函数基础应用172 14.2.2 VLOOKUP函数返回多列结果172 14.2.3 VLOOKUP函数使用通配符查找173 14.2.4 VLOOKUP函数近似查找173 14.2.5 VLOOKUP函数逆向查找174 14.2.6 VLOOKUP函数常见问题及注意事项175 14.3 用HLOOKUP函数查询数据176 14.4 用MATCH函数返回查询值的相对位置176 14.4.1 MATCH常用查找示例177 14.4.2 MATCH函数统计两列相同数据个数178 14.4.3 MATCH函数在带有合并单元格的表格中定位178 14.5 认识INDEX函数179 14.6 认识LOOKUP函数180 14.6.1 LOOKUP函数常用查找示例181 14.6.2 LOOKUP函数基础应用181 14.6.3 LOOKUP函数多条件查找182 14.6.4 LOOKUP函数模糊查找183 14.6.5 LOOKUP函数提取字符串开头连续的数字183 14.7 使用XLOOKUP函数查询数据184 14.7.1 XLOOKUP函数单条件查找185 14.7.2 XLOOKUP函数近似查找185 14.7.3 XLOOKUP函数从后往前查找186 14.7.4 XLOOKUP函数二分法查找186 14.8 用FILTER函数筛选符合条件的数据187 14.8.1 FILTER函数单条件查找187 14.8.2 FILTER函数多条件查找188 14.8.3 FILTER函数模糊查找188 14.9 认识OFFSET函数189 14.9.1 图解OFFSET函数偏移方式190 14.9.2 OFFSET函数参数规则190 14.9.3 OFFSET函数参数自动取整191 14.9.4 使用OFFSET函数制作动态下拉菜单191 14.9.5 OFFSET函数定位统计区域192 14.9.6 OFFSET函数在多维引用中的应用193 14.10 认识INDIRECT函数194 14.10.1 INDIRECT函数常用基础示例194 14.10.2 INDIRECT函数定位查询区域195 14.10.3 INDIRECT函数动态工作表查找196 14.10.4 INDIRECT函数多表数据统计汇总196 14.11 使用UNIQUE函数去重197 14.11.1 UNIQUE函数基础应用示例197 14.11.2 UNIQUE函数按条件统计不重复项个数198 14.12 使用SORT和SORTBY函数排序199 14.12.1 SORT函数199 14.12.2 SORTBY函数200 14.13 用HYPERLINK函数生成超链接201 14.14 用FORMULATEXT函数提取公式字符串203 14.15 用TRANSPOSE函数转置数组或单元格区域203 14.16 查找引用函数的综合应用204 14.16.1 与文本函数嵌套使用204 14.16.2 查找与引用函数嵌套使用205 14.16.3 利用错误值简化公式206 练习与巩固207 第15章统计与求和209 15.1 基础统计函数209 15.2 不同状态下的求和计算210 15.2.1 累计求和210 15.2.2 连续区域快速求和211 15.3 其他常用统计函数211 15.3.1 用COUNTBLANK函数统计空白单元格个数211 15.3.2 用MODE.SNGL函数和MODE.MULT 函数计算众数212 15.3.3 用MEDIAN函数统计中位数213 15.3.4 用QUARTILE函数计算四分位215 15.3.5 用LARGE函数和SMALL函数计算第K个最大（最小）值217 15.4 条件统计函数218 15.4.1 单条件计数COUNTIF函数218 15.4.2 多条件计数COUNTIFS函数224 15.4.3 单条件求和SUMIF函数226 15.4.4 多条件求和SUMIFS函数230 15.4.5 用MAXIFS函数和MINIFS函数计算指定条件的最大（最小）值231 15.5 平均值统计231 15.5.1 用AVERAGEIF函数和AVERAGEIFS 函数计算指定条件的平均值232 15.5.2 使用TRIMMEAN函数计算内部平均值233 15.5.3 使用GEOMEAN函数计算几何平均值234 15.5.4 使用HARMEAN函数计算调和平均值235 15.6 能计数、能求和的SUMPRODUCT 函数235 15.6.1 认识SUMPRODUCT函数235 15.6.2 SUMPRODUCT条件统计计算237 15.7 方差与标准差240 15.7.1 VAR.P函数和VAR.S函数计算方差240 15.7.2 STDEV.P函数和STDEV.S函数计算标准差241 15.8 筛选和隐藏状态下的统计与求和242 15.8.1 认识SUBTOTAL函数242 15.8.2 认识AGGREGATE函数245 15.9 使用FREQUENCY函数计算频数（频率）248 15.10 排列与组合250 15.10.1 用FACT函数计算阶乘250 15.10.2 用PERMUT函数与PERMUTATIONA函数计算排列数251 15.10.3 用COMBIN函数与COMBINA函数计算项目组合数252 15.11 线性趋势预测254 15.11.1 线性回归分析函数254 15.11.2 用TREND函数和FORECAST函数计算内插值255 15.12 概率分布函数257 15.12.1 标准正态分布函数257 15.12.2 卡方分布函数258 15.12.3 F分布函数259 15.12.4 t分布函数260 练习与巩固261 第16章数组运算与数组公式263 16.1 理解数组263 16.1.1 数组的相关定义263 16.1.2 数组的存在形式263 16.1 数组的直接运算266 16.1.1 数组与单值直接运算266 16.1.2 同方向一维数组之间的直接运算267 16.1.3 不同方向一维数组之间的直接运算267 16.1.4 一维数组与二维数组之间的直接运算268 16.1.5 二维数组之间的直接运算269 16.1.6 数组的矩阵运算269 16.2 数组公式的概念271 16.2.1 认识数组公式271 16.2.2 多单元格数组公式272 16.2.3 动态数组公式273 16.3 数组的重构275 16.3.1 生成自然数序列数组275 16.3.2 数组筛选275 16.3.3 数组填充277 16.3.4 数组合并278 16.3.5 数组截取281 16.3.6 数组扩展283 16.3.7 数组排序284 16.3.8 数组结构转换285 16.4 数组公式应用综合实例291 16.4.1 在条件查询与统计中的应用291 16.4.2 在去重计算中的应用294 16.4.3 在字符串整理中的应用296 16.4.4 在排名计算中的应用298 16.4.5 按先入先出法计算出货成本298 16.4.6 使用VSTACK函数和HSTACK函数模拟数据透视表299 16.5 数组公式的优化300 练习与巩固301 第17章多维引用302 17.1 多维引用的概念302 17.1.1 帮助文件中的“三维引用”302 17.1.2 函数产生的多维引用302 17.1.3 对函数产生的多维引用进行计算303 17.1.4 OFFSET函数参数中使用数值与ROW函数的差异303 17.1.5 使用N函数或T函数“降维”304 17.2 多维引用实例305 17.2.1 多工作表汇总求和305 17.2.2 使用DSUM函数完成多工作表汇总求和305 17.2.3 筛选状态下的条件计数306 17.2.4 计算修剪平均分排名307 17.2.5 计算前n个非空单元格对应的数值总和308 练习与巩固309 第18章财务函数310 18.1 财务、投资相关的基本概念与常见计算310 18.1.1 货币的时间价值310 18.1.2 年金310 18.1.3 单利和复利310 18.1.4 现值和终值311 18.1.5 年利率、月利率和日利率312 18.1.6 折现率312 18.1.7 名义利率和实际利率312 18.1.8 现金的流入与流出313 18.1.9 常见的贷款还款方式313 18.2 基本借贷和投资类函数FV、PV、RATE、NPER和PMT 314 18.3 与本金和利息相关的财务函数315 18.3.1 未来值（终值）函数FV315 18.3.2 现值函数PV316 18.3.3 利率函数RATE317 18.3.4 期数函数NPER318 18.3.5 付款额函数PMT319 18.3.6 还贷本金函数PPMT和利息函数IPMT320 18.3.7 累计还贷本金函数CUMPRINC和利息函数CUMIPMT320 18.4 名义利率函数NOMINAL与实际利率函数EFFECT321 18.5 投资评价函数322 18.5.1 净现值函数NPV322 18.5.2 内部收益率函数IRR324 18.5.3 不定期现金流净现值函数XNPV324 18.5.4 不定期现金流内部收益率函数 XIRR325 18.5.5 再投资条件下的内部收益率函数MIRR325 18.6 用SLN、SYD、DB、DDB和VDB函数计算折旧326 练习与巩固328 第19章工程函数330 19.1 贝塞尔（Bessel）函数330 19.2 数字进制转换函数330 19.3 度量衡转换函数332 19.4 误差函数333 19.5 处理复数的函数333 练习与巩固335 第20章 Web类函数336 20.1 用ENCODEURL函数对URL地址编码336 20.2 用WEBSERVICE函数从Web服务器获取数据336 20.3 用FILTERXML函数获取XML结构化内容中的信息337 练习与巩固339 第21章数据透视表函数340 21.1 初识数据透视表函数340 21.1.1 数据透视表函数的基础语法340 21.1.2 快速生成数据透视表函数340 21.1.3 数据透视表函数解读341 21.1.4 Excel 2000版本中的函数语法342 21.2 提取数据透视表不同计算字段数据344 21.3 提取各学科平均分前三名的班级344 21.4 从多个数据透视表中提取数据345 练习与巩固346 第22章数据库函数347 22.1 数据库函数基础347 22.2 数据库函数的基础用法348 22.2.1 第二参数field为列标签348 22.2.2 第二参数field使用数字表示字段位置349 22.2.3 数据库区域第一行标签为数字时的处理方法350 22.3 比较运算符和通配符的使用351 22.3.1 比较运算符的使用351 22.3.2 通配符的使用352 22.4 使用公式作为筛选条件353 22.4.1 使用列标签作为筛选条件353 22.4.2 使用单元格引用作为筛选条件354 22.5 认识DGET函数355 22.6 跨工作表统计356 22.6.1 有规律名称的跨工作表统计356 22.6.2 无规律名称的跨工作表统计357 练习与巩固358 第23章宏表函数359 23.1 什么是宏表359 23.1.1 插入宏表359 23.1.2 宏表与工作表的区别359 23.1.3 设置宏安全性360 23.2 用GET.DOCUMENT函数得到工作表信息361 23.2.1 在宏表中获得当前工作表信息361 23.2.2 使用定义名称方法获得当前工作表信息362 23.2.3 宏表函数触发重算的方法363 23.2.4 显示打印页码364 23.3 用FILES函数得到文件名清单信息365 23.3.1 提取指定目录下的文件名365 23.3.2 制作动态文件链接367 23.4 用GET.WORKBOOK函数返回工作簿信息368 23.5 用GET.CELL函数返回单元格信息370 23.5.1 返回单元格格式370 23.5.2 根据单元格格式求和371 23.5.3 返回单元格的字体颜色和填充颜色371 23.6 用EVALUATE函数计算文本算式372 23.6.1 计算简单的文本算式372 23.6.2 计算复杂的文本算式372 练习与巩固373 第24章自定义函数374 24.1 自定义函数的特点374 24.2 自定义函数的工作环境374 24.2.1 设置工作表的环境374 24.2.2 编写自定义函数375 24.2.3 制作加载宏376 24.2.4 使用加载宏376 24.3 自定义函数实例376 24.3.1 人民币小写金额转大写377 24.3.2 汉字转换成汉语拼音377 24.3.3 提取不同类型字符378 练习与巩固380 第25章 LAMBDA与迭代函数381 25.1 使用LAMBDA函数创建自定义函数381 25.1.1 基础用法381 25.1.2 递归用法382 25.2 使用BYROW函数和BYCOL函数实现逐行逐列汇总383 25.3 用MAKEARRAY函数生成指定行列的数组384 25.4 使用MAP函数遍历数组中的每一个值385 25.5 使用SCAN函数遍历数组并设置累加器388 25.6 使用REDUCE函数遍历数组并设置累加器392 练习与巩固396 第三篇函数综合应用第26章循环引用398 26.1 认识循环引用和迭代计算398 26.1.1 产生循环引用的原因398 26.1.2 设置循环引用的最多迭代次数和最大误差399 26.2 控制循环引用的开启与关闭399 26.2.1 启动开关399 26.2.2 计数器402 26.2.3 同时使用开关和计数器404 26.2.4 其他405 练习与巩固405 第27章条件筛选技术406 27.1 按条件筛选406 27.1.1 单条件筛选单个结果406 27.1.2 多条件筛选单个结果407 27.1.3 单条件筛选多个结果408 27.1.4 多条件筛选多个结果410 27.1.5 其他筛选413 27.2 提取不重复值415 27.2.1 一维区域筛选不重复记录415 27.2.2 二维数据表提取不重复记录416 27.2.3 同一单元格内筛选不重复数据417 27.3 综合运用418 27.3.1 提取指定条件的不重复记录418 27.3.2 将筛选结果去重后合并到一个单元格419 练习与巩固419 第28章排名与排序420 28.1 使用函数与公式进行排名420 28.1.1 美式排名420 28.1.2 中式排名421 28.1.3 百分比排名424 28.2 使用函数与公式进行排序425 28.2.1 数值排序425 28.2.2 使用SORT和SORTBY函数排序425 28.2.3 筛选后的排序429 28.2.4 其他排序430 练习与巩固433 第四篇其他功能中的函数应用第29章函数与公式在条件格式中的应用436 29.1 条件格式中使用函数公式的方法436 29.1.1 选择正确的引用方式438 29.1.2 查看或编辑已有条件格式公式440 29.1.3 在工作表中编写条件格式公式441 29.1.4 其他注意事项442 29.2 函数公式在条件格式中的应用实例444 29.2.1 突出显示另一列中不包含的数据444 29.2.2 使用逐行扩展的数据范围445 29.2.3 条件格式与日期函数的结合使用446 29.2.4 条件格式与VBA代码的结合使用448 29.2.5 条件格式的其他应用450 练习与巩固454 第30章函数与公式在数据验证中的应用455 30.1 数据验证中使用函数与公式的方法455 30.1.1 在数据验证中使用函数与公式455 30.1.2 查看和编辑已有的数据验证中的公式456 30.1.3 数据验证中公式的使用限制457 30.1.4 其他注意事项459 30.2 函数与公式在数据验证中的应用实例459 30.2.1 借助COUNTIF函数限制输入重复信息459 30.2.2 设置项目预算限制461 30.2.3 借助INDIRECT函数创建二级下拉列表462 30.2.4 借助OFFSET函数创建动态二级下拉列表465 练习与巩固468 第31章函数与公式在图表中的应用469 31.1 认识图表中的SERIES函数469 31.2 为图表添加参考线472 31.3 使用逻辑函数辅助创建图表475 31.3.1 使用IF函数判断数值区间制作柱形图475 31.3.2 使用IF函数制作数据列差异较大的柱形图477 31.3.3 使用IF函数制作动态甘特图480 31.3.4 使用IF+NA函数制作趋势图486 31.4 使用FILTER函数与数据验证制作动态图表494 31.5 使用SORT函数制作自动排序的条形图497 31.6 使用SQRT函数制作气泡图500 31.7 使用OFFSET函数结合定义名称、控件制作动态图表503 31.7.1 使用MATCH+OFFSET函数定义名称制作动态趋势图503 31.7.2 使用OFFSET函数、定义名称、控件制作图表506 31.7.3 使用OFFSET、控件制作多系列趋势图510 31.8 用REPT函数制作旋风图515 31.9 用HYPERLINK函数制作动态图表516 练习与巩固518 第五篇函数与公式常见错误指南第32章常见不规范表格导致的问题及处理建议520 32.1 统一字段属性520 32.1.1 数值不与文本混用520 32.1.2 使用“真日期”和“真时间”521 32.1.3 同一项目的名称统一522 32.1.4 添加具有唯一标识的字段522 32.2 使用单元格对象备注特殊数据523 32.2.1 不使用手工标注颜色523 32.2.2 不使用批注、文本框、表单控件等标记数据524 32.3 减少使用合并单元格524 32.4 使用一维表存放数据526 32.5 保持数据表的完整526 32.5.1 保持同一工作表内的数据完整526 32.5.2 同结构数据表合并在一个表内528 32.6 正确区分数据源表、统计报表及表单530 32.6.1 数据源表与统计报表530 32.6.2 数据源表与表单530 练习与巩固532 第33章公式常见错误指南533 33.1 函数名称或参数输入错误533 33.1.1 函数名称输入错误533 33.1.2 参数输入错误533 33.1.3 公式输入不完整533 33.2 循环引用534 33.3 显示公式本身534 33.4 参数设置错误534 33.4.1 正确选择默认参数535 33.4.2 参数类型不符合规范535 33.4.3 输入了太少、太多的参数或参数超出范围535 33.5 函数自身限制536 33.5.1 长数字和特殊文本的影响536 33.5.2 参数错位的影响536 33.5.3 特殊字符的影响537 33.5.4 浮点误差带来的精度影响537 33.6 引用错误537 33.6.1 单元格引用方式错误537 33.6.2 被引用的工作表名中包含特殊字符537 33.6.3 删除了行、列或单元格538 33.7 空格或不可见字符的影响538 33.8 数据类型的影响539 33.9 溢出错误539 33.10 开启了手动重算540 练习与巩固540 附录541 附录A Excel 2021主要规范与限制541 附录B Excel 2021常用快捷键544 附录C 高效办公必备工具——Excel易用宝549 目录示例目录示例1-1 使用公式计算商品金额8 示例1-2 将公式快速应用到其他工作表9 示例1-3 设置公式保护10 示例2-1 计算员工全勤奖19 示例3-1 按出勤天数计算劳务费23 示例3-2 制作乘法口诀表24 示例3-3 按期数计算累计应还利息24 示例4-1 引用其他工作表区域27 示例4-2 汇总连续多工作表的相同区域29 示例8-1 添加监视窗口46 示例9-1 批量创建名称52 示例9-2 统计销售一部的销售总额54 示例9-3 统计全部考核总分54 示例9-4 现有公式中使用名称59 示例9-5 创建动态的数据透视表64 示例9-6 利用“表”区域动态引用65 示例9-7 使用LET函数统计员工奖金66 示例10-1 屏蔽公式返回的无意义0值69 示例10-2 区分大小写的查询70 示例10-3 生成字母序列71 示例10-4 使用CLEAN函数清除不可见字符73 示例10-5 使用NUMBERVALUE函数转换不规范数字73 示例10-6 统计单元格中的人数74 示例10-7 利用迭代计算实现多重替换75 示例10-8 隐藏身份证号码中的部分内容77 示例10-9 使用REPLACEB函数插入分隔符号77 示例10-10 提取混合内容中的标准名称78 示例10-11 分列填写收款凭证79 示例10-12 从身份证号中提取出生年月80 示例10-13 提取混合内容中的中文和英文81 示例10-14 用SUBSTITUTE函数拆分会计科目82 示例10-15 提取混合内容中的工单类目83 示例10-16 清除顽固的不可见字符83 示例10-17 使用TEXTSPLIT函数提取字符串中的电话号码86 示例10-18 使用TEXTSPLIT函数统计最多连胜场次87 示例10-19 判断物料名称是否包含关键字88 示例10-20 提取指定符号后的内容88 示例10-21 从混合内容中提取班级名称89 示例10-22 提取混合内容中的英文姓名90 示例10-23 合并不同型号产品的辅料名称90 示例10-24 按部门合并人员姓名91 示例10-25 银行卡号分段显示92 示例10-26 计算课程总时长95 示例10-27 使用TEXT函数判断实际与预算差异95 示例10-28 使用TEXT函数判断考核成绩是否合格96 示例10-29 合并带数字格式的字符串97 示例10-30 在TEXT函数格式代码参数中引用单元格地址97 示例10-31 用TEXT函数转换身份证号码中的出生日期98 示例10-32 使用TEXT函数转换中文格式日期98 示例10-33 将中文小写数字转换为数值98 示例10-34 转换中文大写金额99 示例11-1 借助CELL函数获取工作簿名103 示例11-2 借助CELL函数获取工作表名104 示例11-3 判断是否能够参加某活动108 示例11-4 使用IF函数计算提成额109 示例11-5 计算多层提成额110 示例11-6 判断所辖地112 示例11-7 提取逗号后的数字114 示例11-8 屏蔽除数为0的错误值115 示例11-9 借助N函数屏蔽错误值115 示例12-1 根据身份证号判断性别119 示例12-2 利用MOD函数生成循环序列119 示例12-3 几何计算121 示例12-4 计算二分法查找次数122 示例12-5 根据坐标值计算面积122 示例12-6 利用INT函数生成重复序列124 示例12-7 解决运算“出错”问题125 示例12-8 四舍六入五成双法则126 示例12-9 生成0~100的随机加减练习题129 示例12-10 速算个人所得税130 示例13-1 生成指定范围内的随机日期和随机时间134 示例13-2 批量转换“伪日期”“伪时间”137 示例13-3 从混合数据中分别提取日期和时间140 示例13-4 计算世界时间140 示例13-5 计算每个月的例会日141 示例13-6 计算最近周促销日的日期143 示例13-7 计算格式为yyww的日期期间143 示例13-8 计算加班费144 示例13-9 根据指定期间计算周数145 示例13-10 判断指定日期所在的季度146 示例13-11 指定日期所在季度的计算147 示例13-12 判断是否超时148 示例13-13 计算员工在岗时长148 示例13-14 制作甘特图151 示例13-15 分析进度进程151 示例13-16 计算员工工龄152 示例13-17 处理DATEDIF函数的异常结果152 示例13-18 计算员工退休日期153 示例13-19 判断合同是否需要准备续签154 示例13-20 使用YEARFRAC函数计算应付利息156 示例13-21 计算员工每月发薪日期157 示例13-22 计算向客户承诺的最后期限158 示例13-23 计算员工出勤天数159 示例13-24 计算每月有几个星期日160 示例13-25 闰年判断161 示例13-26 按指定日期期间进行统计162 示例13-27 计算项目在每月的天数163 示例13-28 计算加班时数163 示例13-29 制作万年历164 示例14-2 生成连续序号168 示例14-2 利用ADDRESS函数生成列标字母171 示例14-3 VLOOKUP函数查询并返回多列结果172 示例14-4 VLOOKUP函数使用通配符查找173 示例14-5 VLOOKUP函数近似查找174 示例14-6 VLOOKUP函数逆向查找174 示例14-7 VLOOKUP函数常见问题及注意事项175 示例14-8 使用HLOOKUP查询班级人员信息176 示例14-9 MATCH函数常用查找示例177 示例14-10 MATCH函数统计两列相同数据个数178 示例14-11 MATCH函数按部门分配奖金178 示例14-12 INDEX函数和MATCH函数实现逆向查找180 示例14-13 LOOKUP函数常见的模式化用法181 示例14-14 LOOKUP函数判断考核等级181 示例14-15 LOOKUP函数多条件查询182 示例14-16 LOOKUP函数模糊匹配查询183 示例14-17 LOOKUP函数提取字符串开头连续的数值184 示例14-18 XLOOKUP函数单条件查询185 示例14-19 XLOOKUP函数判断考核等级185 示例14-20 XLOOKUP函数查询商品最新销售金额186 示例14-21 XLOOKUP函数实现二分法查询186 示例14-22 FILTER函数返回多个符合条件的记录187 示例14-23 FILTER函数实现多条件查找188 示例14-24 FILTER函数模糊查找189 示例14-25 使用OFFSET函数制作动态下拉菜单191 示例14-26 OFFSET函数计算新入职员工前三个月培训时间192 示例14-27 OFFSET函数求总成绩的最大值193 示例14-28 INDIRECT函数带合并单元格的数据查询195 示例14-29 INDIRECT函数动态表数据查询196 示例14-30 INDIRECT函数多表数据汇总196 示例14-31 UNIQUE函数统计部门不重复的人数198 示例14-32 使用SORT函数进行数据排序199 示例14-33 使用SORTBY函数进行自定义规则排序200 示例14-34 HYPERLINK函数创建有超链接的工作表目录201 示例14-35 HYPERLINK函数快速跳转到指定单元格202 示例14-36 HYPERLINK函数在不同工作簿之间建立超链接202 示例14-37 FORMULATEXT函数提取公式字符串203 示例14-38 TRANSPOSE函数制作九九乘法表204 示例14-39 提取单元格中最后一个分隔符前的内容204 示例14-40 统计指定月份销售量合计205 示例14-41 提取一二三级科目名称206 示例14-42 计算快递费206 示例15-1 基础统计函数应用209 示例15-2 累计求和210 示例15-3 连续区域快速求和211 示例15-4 COUNTBLANK函数应用及对比211 示例15-5 众数函数基础应用212 示例15-6 统计最受欢迎歌手号码213 示例15-7 中位数函数基础应用213 示例15-8 计算员工工资的平均水平214 示例15-9 设置上下限214 示例15-10 四分位数函数基础应用216 示例15-11 员工工资的四分位分布216 示例15-12 列出前三笔销量217 示例15-13 列出前三笔销量对应的日期217 示例15-14 COUNTIF函数基础应用218 示例15-15 数据区域中含通配符的统计220 示例15-16 单字段同时满足多条件的计数221 示例15-17 验证身份证号是否重复221 示例15-18 包含错误值的数据统计222 示例15-19 统计非空文本数量222 示例15-20 统计非重复值数量222 示例15-21 按不重复订单号计算订单金额223 示例15-22 COUNTIFS函数基础应用224 示例15-23 按指定条件统计非重复值数量225 示例15-24 SUMIF函数基础应用226 示例15-25 单字段同时满足多条件的求和228 示例15-26 二维区域条件求和228 示例15-27 对连续数据的最后一个非空单元格求和229 示例15-28 SUMIFS函数基础应用230 示例15-29 各班级的最高最低分231 示例15-30 条件平均函数基础应用232 示例15-31 达到各班平均分的人数232 示例15-32 计算工资的内部平均值233 示例15-33 去掉最高最低分后计算平均分数233 示例15-34 计算平均增长率234 示例15-35 计算水池灌满水的时间235 示例15-36 演讲比赛评分236 示例15-37 综合销售提成237 示例15-38 SUMPRODUCT条件统计计算237 示例15-39 二维区域统计239 示例15-40 产品包装质量比较240 示例15-41 某班学生身高分布241 示例15-42 SUBTOTAL函数在筛选状态下的统计243 示例15-43 隐藏行数据统计243 示例15-44 筛选状态下生成连续序号244 示例15-45 通过分类汇总实现SUBTOTAL 求和244 示例15-46 包含错误值的统计246 示例15-47 按指定条件汇总最大值和最小值247 示例15-48 统计不同分数段的人数248 示例15-49 判断是否为断码249 示例15-50 排列队伍顺序的种数250 示例15-51 按顺序组合三位数251 示例15-52 组合种类计算252 示例15-53 人员选择概率253 示例15-54 随机选择多选题时全部正确的概率253 示例15-55 计算一组数据的线性回归数据255 示例15-56 根据水位计算水面面积256 示例15-57 用分段线性插值法计算船舶排水量256 示例15-58 制作标准正态分布表258 示例15-59 制作卡方分布临界值表259 示例15-60 制作F分布临界值表260 示例15-61 制作t分布临界值表261 示例16-1 计算商品总销售额264 示例16-2 计算前三名的销售额占比265 示例16-3 突出显示销量最后三名的数据265 示例16-4 制作九九乘法表267 示例16-5 了解MMULT函数运算过程269 示例16-6 计算餐费分摊金额270 示例16-7 使用多单元格数组公式计算销售额272 示例16-8 使用动态数组公式计算销售额273 示例16-9 从一维数组中提取子数组276 示例16-10 从二维数组中提取子数组276 示例16-11 提取二维数组中的文本值277 示例16-12 填充合并单元格277 示例16-13 数组横向合并 278 示例16-14 数组纵向合并 278 示例16-15 使用VSTACK函数合并同工作簿中多工作表的销售数据280 示例16-16 使用TAKE函数计算累计销售额282 示例16-17 逆序排列数据284 示例16-18 随机安排考试座位284 示例16-19 一维数组转换为二维数组285 示例16-20 二维数组转换为一维数组286 示例16-21 使用TOCOL函数逆透视数据表288 示例16-22 使用TOROW函数和WRAPROWS函数转换家庭信息290 示例16-23 查询指定商品销量最大的月份291 示例16-24 统计特定身份信息的员工数量292 示例16-25 提取每个人成绩最大值求和292 示例16-26 统计合并单元格所占行数293 示例16-27 从销售业绩表提取唯一销售人员姓名294 示例16-28 二维单元格区域提取不重复姓名295 示例16-29 从消费明细中提取消费金额296 示例16-30 提取首个手机号码297 示例16-31 按各奖牌数量降序排列奖牌榜及计算排名298 示例16-32 使用TOCOL函数按先入先出法计算出货成本299 示例16-33 使用VSTACK函数和HSTACK函数模拟数据透视表300 示例17-1 汇总多个工作表中的费用金额305 示例17-2 使用DSUM函数完成多工作表费用金额汇总305 示例17-3 筛选状态下的条件计数306 示例17-4 计算修剪平均分307 示例17-5 计算造价表中前n项的总价合计值308 示例18-1 分别用单利和复利计算投资收益310 示例18-2 现值计算311 示例18-3 终值计算312 示例18-4 计算整存整取理财产品的收益315 示例18-5 计算零存整取的最终收益315 示例18-6 对比投资保险收益316 示例18-7 计算存款多少钱能在30年到达 100万316 示例18-8 计算整存零取方式养老方案317 示例18-9 计算房产投资收益率317 示例18-10 计算实际借款利率318 示例18-11 计算理财产品购买期数318 示例18-12 计算每期存款额319 示例18-13 贷款每期还款额计算319 示例18-14 贷款每期还款本金与利息320 示例18-15 贷款累计还款本金与利息321 示例18-16 名义利率与实际利率321 示例18-17 计算投资净现值323 示例18-18 出租房屋收益323 示例18-19 计算内部收益率324 示例18-20 计算不定期现金流量的净现值324 示例18-21 不定期现金流量收益率325 示例18-22 再投资条件下的内部收益率计算326 示例18-23 折旧函数对比327 示例19-1 不同进制数字的相互转换331 示例19-2 旅行费用统计334 示例20-1 英汉互译337 示例20-2 借助FILTERXML函数拆分会计科目338 示例21-1 数据透视表公式举例341 示例21-2 使用Excel 2000版本语法提取数据343 示例21-4 提取数据透视表不同计算字段数据344 示例21-5 提取各学科平均分前三名的班级344 示例21-6 从多个数据透视表中提取数据345 示例22-1 统计销售数据348 示例22-2 第二参数field使用数字表示字段位置349 示例22-3 计算各学科最高分数之和350 示例22-4 数据库区域第一行标签为数字时的处理方法350 示例22-5 比较运算符的使用351 示例22-6 通配符的使用352 示例22-7 使用数据表的列标签作为筛选条件353 示例22-8 使用单元格引用作为筛选条件354 示例22-9 使用DGET函数提取值355 示例22-10 有规律名称的跨工作表统计356 示例22-11 无规律名称的跨工作表统计357 示例23-1 获得当前工作表信息361 示例23-2 使用定义名称方法获得当前工作表信息362 示例23-3 显示打印页码364 示例23-4 提取指定目录下的文件名365 示例23-5 制作动态文件链接367 示例23-6 制作当前工作簿中的各工作表链接369 示例23-7 返回单元格格式370 示例23-8 根据单元格格式求和371 示例23-9 返回单元格的字体颜色和填充颜色371 示例23-10 计算简单的文本算式372 示例23-11 计算复杂的文本算式372 示例24-1 人民币小写金额转大写377 示例24-2 汉字转换成汉语拼音377 示例24-3 提取不同类型字符378 示例24-4 对GetChar函数的返回值计算380 示例25-1 使用LAMBDA函数求解菲波那切数列的第n项382 示例25-2 使用MAP函数计算每个客户最近3次购物金额386 示例25-3 使用MAP函数生成加减法题目387 示例25-4 使用SCAN函数给家庭编号389 示例25-5 使用SCAN函数求连续增长月份销售额最大值390 示例25-6 使用SCAN函数填充合并单元格391 示例25-7 使用REDUCE函数按指定次数展开数据394 示例25-8 使用REDUCE函数将户主放在家庭记录第一行395 示例25-9 使用REDUCE函数逆转字符串395 示例25-10 使用REDUCE函数一次性替换多个关键字396 示例26-1 利用启动开关实现或停止累加求和400 示例26-2 计算指定总额的单据组合401 示例26-3 求解二元一次方程组402 示例26-4 提取两个单元格中的相同字符403 示例26-5 提取混合内容中的中文和数字404 示例26-6 再投资情况下的企业利润计算405 示例27-1 根据单一条件筛选出唯一符合条件的结果406 示例27-2 根据多个条件筛选出唯一符合条件的结果407 示例27-3 根据单个条件筛选出多个符合条件的结果408 示例27-4 根据多个条件筛选出多个符合条件的结果410 示例27-5 用多条件符合其一的方式筛选多个符合条件的结果411 示例27-6 从多个条件中根据共同特征筛选出多个符合条件的结果412 示例27-7 按单元格内部分数据为依据筛选413 示例27-8 跨表筛选413 示例27-9 提取不重复的门店名称415 示例27-10 提取不重复的门店与品名416 示例27-11 提取矩阵中的不重复项416 示例27-12 同一单元格内筛选不重复数据417 示例27-13 提取满足指定条件最后一天的数据418 示例27-14 将筛选结果去除重复项后合并到一个单元格419 示例28-1 跨工作表排名421 示例28-2 数学竞赛排名422 示例28-3 分组排名423 示例28-4 开机速度打败了百分之多少的用户？425 示例28-5 对数据表进行重新排序426 示例28-6 筛选出唯一值的数据排序429 示例28-7 筛选出指定条件的数据排序430 示例28-8 按单元格内部分数据为依据排序430 示例28-9 同一单元格内的数据排序431 示例28-10 汉字按数值规则排序431 示例28-11 按自定义序列排序432 示例28-12 跨表排序432 示例29-1 突出显示低于计划完成额的数据436 示例29-2 自动标记业绩最高的业务员438 示例29-3 自动标记不同部门的销冠441 示例29-4 自动标记不同部门的销冠与公司的最后一名442 示例29-5 突出显示本月新增员工445 示例29-6 突出显示重复录入的姓名445 示例29-7 合同到期提醒446 示例29-8 员工生日提醒447 示例29-9 突出显示本周工作安排447 示例29-10 制作便于查看数据的“聚光灯”448 示例29-11 用条件格式制作项目进度图450 示例29-12 用条件格式标记不同部门的记录451 示例29-13 为前N名的业务员设置红旗图标452 示例30-1 使用多行多列的单元格区域作为序列来源457 示例30-2 限制输入重复信息459 示例30-3 设置项目预算限制461 示例30-4 创建地区信息二级下拉列表462 示例30-5 创建动态二级下拉列表465 示例30-6 动态三级下拉列表467 示例31-1 添加平均参考线的柱形图472 示例31-2 根据业绩区间变化颜色的柱形图475 示例31-3 展示数据列差异较大的柱形图477 示例31-4 动态甘特图480 示例31-5 突出显示最大最小值的趋势图486 示例31-6 带涨幅的滑珠图490 示例31-7 动态趋势图494 示例31-8 自动排序的条形图497 示例31-9 筛选后自动排序的柱形图498 示例31-10 百分比气泡图500 示例31-11 动态选择时间段的趋势图503 示例31-12 动态更换图表数据与类型506 示例31-13 动态选择系列折线图510 示例31-14 双选项的动态柱形图513 示例31-15 单元格旋风图515 示例31-16 鼠标触发的动态图表516 示例32-1 计算金额520 示例32-2 使用0屏蔽错误值520 示例32-3 计算加班费521 示例32-4 统计参赛人数522 示例32-5 统计每户人数522 示例32-6 计算全勤奖523 示例32-7 按品名进行统计524 示例32-8 填充合并单元格525 示例32-9 表内小计在下方的数据表汇总527 示例32-10 表内小计在上方的数据表汇总528 示例32-11 合并多个工作表/工作簿529 示例32-12 制作工资条530 示例32-13 制作职位说明书531

你还可能感兴趣

我要评论