Excel函数、图表与透视表从入门到精通（全新版）

目 录 第1 篇 入门及函数篇 第1 章 Excel入门............................. 2 1.1 熟悉Excel 界面................................ 2 1.1.1 Excel 界面浏览........................ 2 1.1.2 新界面的优势........................... 4 1.1.3 多功能的状态栏....................... 5 1.1.4 自定义用户界面....................... 5 1.2 Excel 功能区简介............................. 6 1.2.1 功能区的功能分布................... 6 1.2.2 调用上下文选项卡................... 6 1.3 调用快捷键....................................... 7 1.3.1 万能的功能键【Alt】............. 7 1.3.2 调用旧版菜单键....................... 8 1.3.3 自定义快捷键........................... 8 1.4 设置Excel 选项................................ 9 1.4.1 Excel 选项功能介绍................ 9 1.4.2 详解公式选项......................... 11 1.5 及时调用帮助................................. 12 1.5.1 调用函数帮助的4种方式..... 13 1.5.2 Excel 帮助的缺点.................. 14 第2 章 初识公式............................ 15 2.1 公式的概念与结构......................... 15 2.1.1 何谓公式................................. 15 2.1.2 公式的结构............................. 16 2.2 公式的优缺点................................. 16 2.2.1 公式的应用前景..................... 16 2.2.2 公式的计算速度..................... 17 2.2.3 公式的3个缺点..................... 17 2.2.4 用VBA扩展内部公式的功能.18 2.3 录入与编辑公式............................. 18 2.3.1 如何录入公式......................... 18 2.3.2 公式与文本的两种转换方式. 19 2.3.3 修改公式................................. 20 2.4 认识引用......................................... 21 2.4.1 如何引用单元格..................... 21 2.4.2 跨表引用技巧......................... 21 2.4.3 相对引用、绝对引用与混合 引用......................................... 23 2.4.4 将引用转换成值..................... 24 2.4.5 区分引用与文本..................... 24 2.5 认识运算符与优先级..................... 24 2.5.1 Excel 公式所支持的运算符.. 24 2.5.2 Excel 公式所支持的数学符号.25 2.5.3 了解运算优先级..................... 25 2.6 复制公式......................................... 26 2.6.1 复制公式的必要性................. 26 2.6.2 复制公式的多种技巧............. 26 2.7 定位公式......................................... 28 2.7.1 选择所有公式......................... 28 2.7.2 隐藏所有公式......................... 28 2.7.3 删除所有值为错误的公式..... 29 2.8 公式的错误类型............................. 30 2.8.1 公式错误的种类..................... 30 2.8.2 #DIV/0!错误........................... 30 2.8.3 #N/A 错误............................... 30 2.8.4 #NAME?错误......................... 30 2.8.5 #NUM!错误............................ 31 2.8.6 #VALUE!错误........................ 31 2.8.7 #REF!错误.............................. 31 2.8.8 检查错误................................. 31 2.9 公式求值......................................... 32 II Excel 函数、图表与透视表从入门到精通（全新版） 2.10 迭代计算....................................... 33 2.10.1 何谓迭代计算....................... 33 2.10.2 迭代计算的应用：累加....... 33 2.10.3 迭代计算的应用：自动 添加不更新的时间............... 34 2.10.4 迭代计算的应用：解方程... 35 第3 章 公式的灵魂——函数.......... 36 3.1 函数的作用..................................... 36 3.1.1 提升速度................................. 36 3.1.2 简化公式................................. 37 3.1.3 实现更强大的计算功能......... 37 3.1.4 选择性地统计运算................. 37 3.2 函数的分类..................................... 38 3.2.1 按用途分类............................. 38 3.2.2 按参数分类............................. 38 3.2.3 按刷新方式分类..................... 40 3.3 函数的参数..................................... 40 3.3.1 参数的作用............................. 40 3.3.2 参数的类型............................. 40 3.3.3 参数嵌套................................. 42 3.3.4 选择参数的技巧..................... 42 3.3.5 省略参数与省略参数的值..... 42 3.4 函数的录入方式............................. 43 3.4.1 使用函数库............................. 43 3.4.2 使用插入函数向导................. 44 3.4.3 直接录入................................. 44 3.5 函数运算中的空单元格与空文本. 45 3.6 函数与公式的限制......................... 46 3.6.1 数字计算精度......................... 46 3.6.2 日期与时间计算精度............. 46 3.6.3 浮点运算偏差......................... 47 3.6.4 公式长度限制......................... 48 3.6.5 参数个数限制......................... 49 3.6.6 参数的类型限制..................... 49 3.6.7 IF 函数嵌套限制.................... 50 3.6.8 功能限制及例外项................. 50 第4 章 数学运算............................ 51 4.1 求和运算......................................... 51 4.1.1 SUM 求和............................... 51 4.1.2 SUMIF 条件求和................... 53 4.1.3 SUMIFS多条件求和............. 57 4.1.4 SUMPRODUCT 乘积之和.... 57 4.1.5 SUMSQ求平方和.................. 59 4.2 求积运算......................................... 59 4.2.1 PRODUCT求积..................... 60 4.2.2 MMULT两数组的矩阵积..... 61 4.3 商与余数......................................... 65 4.3.1 QUOTIENT 求商................... 65 4.3.2 MOD 求余数.......................... 66 4.4 随机数............................................. 68 4.4.1 RAND随机小数.................... 68 4.4.2 RANDBETWEEN 随机整数. 70 4.5 乘方与开方..................................... 71 4.5.1 SQRT 开平方......................... 71 4.5.2 POWER 乘方.......................... 72 4.6 数据的舍入问题............................. 73 4.6.1 ODD/EVEN 取奇数与偶数... 74 4.6.2 TRUNC/INT截取为指定位数小 数或整数................................. 74 4.6.3 CEILING 向上舍入为最接近基 数的倍数................................. 75 4.6.4 FLOOR向下舍入为最接近基数 的倍数..................................... 76 4.6.5 ROUND四舍五入................. 76 4.6.6 ROUNDDOWN向下舍入..... 77 4.7 分类汇总......................................... 78 第5 章 逻辑运算............................ 81 5.1 真假值运算..................................... 81 5.1.1 TRUE/FALSE真假运算........ 81 III 目 录 5.1.2 AND/OR 判断是否符合 条件......................................... 83 5.1.3 NOT 逻辑值求反................... 85 5.2 条件判断......................................... 85 5.2.1 IF 条件判断............................ 86 5.2.2 IFERROR 防错函数............... 88 第6 章 文本处理............................ 90 6.1 字符码转换..................................... 90 6.1.1 CODE返回字符的数字代码.... 90 6.1.2 CHAR 返回数字参数指定的 字符......................................... 91 6.2 文本合并......................................... 93 6.2.1 CONCATENATE串连字符..... 94 6.2.2 自定义合并函数..................... 95 6.3 字符转换......................................... 97 6.3.1 PROPER 将单词转换为 首字母大写............................. 97 6.3.2 UPPER/LOWER 将单词 转换为大写或小写................. 98 6.3.3 T文本检查及降维应用......... 99 6.3.4 REPT按指定次数重复文本... 101 6.3.5 TRIM 去除多余空格............ 103 6.3.6 TEXT 格式化数字............... 105 6.4 字符比较....................................... 112 6.5 长度计算及提取指定长度的 字符............................................... 114 6.5.1 LEN 字符长度计算.............. 114 6.5.2 LEFT 取左边的字符............ 115 6.5.3 RIGHT取右边的字符......... 116 6.5.4 MID 取中间的字符.............. 118 6.6 字符查找与替换........................... 120 6.6.1 FIND 区分大小写的字符 查找......................................................120 6.6.2 SEARCH 支持通配符的字 符查找................................... 121 6.6.3 REPLACE 替换指定位置的 字符....................................... 123 6.6.4 SUBSTITUTE替换指定 字符...........................................124 第7 章 统计运算..........................126 7.1 平均值统计................................... 126 7.1.1 AVERAGE 统计参数的 算术平均值........................... 126 7.1.2 AVERAGEA 统计包括逻辑值 与文本的算术平均值........... 128 7.1.3 AVERAGEIF 按条件统计参数 的算术平均值....................... 128 7.1.4 AVERAGEIFS按多条件统计参 数的算术平均值................... 130 7.2 数量统计....................................... 130 7.2.1 COUNT 统计包含数字的单元 格个数................................... 131 7.2.2 COUNTA 统计非空单元格的 个数....................................... 132 7.2.3 COUNTIF统计区域中满足条件 的单元格个数....................... 134 7.2.4 COUNTIFS 统计区域中满足多 条件的单元格个数............... 138 7.3 极值与中值统计........................... 139 7.3.1 MIN 统计参数中的最小值.. 139 7.3.2 SMALL统计数据中第k个最 小值....................................... 142 7.3.3 MAX 统计参数中的最大值144 7.3.4 LARGE统计数据中第k个最 大值....................................... 145 7.3.5 MEDIAN统计一组数据的 中值....................................... 146 7.3.6 MODE统计一组数据中的 众数....................................... 147 7.3.7 MODE.MULT 统计一组数据的 中值的垂直数组................... 148 IV Excel 函数、图表与透视表从入门到精通（全新版） 7.4 排名统计....................................... 149 7.5 频率分布....................................... 151 第8 章 日期和时间运算............... 153 8.1 日期运算....................................... 153 8.1.1 TODAY获取当前系统日期153 8.1.2 YEAR/MONTH/DAY 获取年/ 月/日...................................... 155 8.1.3 DATE 根据指定的年月日序号 返回日期............................... 157 8.1.4 EDATE返回指定日期之前或之 后几个月的日期序号........... 158 8.1.5 EOMONTH 返回指定日期之前 或之后的月末日期序号....... 159 8.1.6 DATEDIF计算两个日期间的间 隔时间................................... 160 8.2 时间运算....................................... 161 8.2.1 NOW 获取当前日期和时间161 8.2.2 HOUR/MINUTE/SECOND获取 时/分/秒................................ 163 8.3 星期与工作日运算....................... 163 8.3.1 WEEKDAY 将日期转换成代表 星期的序号........................... 163 8.3.2 WEEKNUM返回日期在一年中 的周数................................... 166 8.3.3 NETWORKDAYS 返回两个日 期之间的完整工作日数....... 167 8.3.4 WORKDAY返回指定工作日之 前/后的日期......................... 169 第9 章 查找与引用....................... 170 9.1 引用函数....................................... 170 9.1.1 ROW/COLUMN 引用行号/ 列号....................................... 170 9.1.2 ROWS/COLUMNS 引用行数/ 列数....................................... 173 9.1.3 ADDRESS 返回指定行/列的引 用地址................................... 174 9.1.4 INDIRECT 返回文本指定的 引用....................................... 175 9.1.5 OFFSET产生指定偏移量的 引用....................................... 179 9.1.6 INDEX 引用区域或者数组中指 定行与列的值....................... 181 9.1.7 TRANSPOSE 将引用的方向进 行转置................................... 184 9.1.8 CHOOSE 从参数中引用索引值 所指定的值........................... 186