Excel经典教程——公式与函数

第 1章 将区域使用到极致 1 1.1 高级区域选择技巧 1 1.1.1 鼠标区域选择窍门 1 1.1.2 键盘区域选择窍门 2 1.1.3 三维区域选择 2 1.1.4 利用【定位】对话框选择区域 3 1.1.5 利用【定位条件】对话框选择区域 4 1.2 在区域中快速选择单元格 8 1.3 区域的填充 9 1.4 使用填充柄 9 1.4.1 使用自动填充来创建序列文本和序列数值 9 1.4.2 创建自己的自动填充表 11 1.4.3 填充区域 12 1.5 创建序列 12 1.6 高级区域复制技巧 13 1.6.1 复制选定单元格的属性 14 1.6.2 用运算方法合并源单元格与目标单元格 15 1.6.3 行与列的转置 15 1.7 清除区域内容 16 1.8 在区域中应用条件格式 16 1.8.1 创建突出显示单元格规则 17 1.8.2 创建项目选取规则 18 1.8.3 添加数据条 20 1.8.4 添加色阶 22 1.8.5 添加图标集 24 第 2章 使用区域名称 26 2.1 定义区域名称 26 2.1.1 使用【名称框】 27 2.1.2 使用【新建名称】对话框 28 2.1.3 改变范围来定义表级名称 29 2.1.4 使用工作表文本框定义名称 30 2.1.5 命名常量 32 2.2 使用区域名称 33 2.2.1 应用区域名称 33 2.2.2 使用自动填充 35 2.2.3 区域名称导航 36 2.2.4 在工作表中粘贴区域名称列表 36 2.2.5 展开【名称管理器】对话框 36 2.2.6 筛选名称 37 2.2.7 编辑区域名称的坐标 37 2.2.8 自动调整区域名称坐标 37 2.2.9 改变区域名称 39 2.2.10 删除区域名称 39 2.2.11 区域名称的交集操作 39 第3章 建立基本公式 41 3.1 了解公式的基础知识 41 3.1.1 Excel公式中的一些数量限制 41 3.1.2 输入和编辑公式 42 3.1.3 使用运算公式 43 3.1.4 使用比较公式 43 3.1.5 使用文本公式 44 3.1.6 使用引用函数 44 3.2 了解运算符的优先级 45 3.2.1 优先顺序 45 3.2.2 管理优先顺序 46 3.3 管理工作表运算 47 3.4 复制和移动公式 49 3.4.1 了解相对引用格式 50 3.4.2 了解绝对引用格式 51 3.4.3 复制公式但不调整相对引用 52 3.5 显示工作表公式 52 3.6 转换公式为数据 53 3.7 在公式中使用区域名称 53 3.7.1 将名称粘贴到公式中 54 3.7.2 在公式中应用名称 54 3.7.3 命名公式 57 3.8 在公式中使用链接 57 3.8.1 了解外部引用 58 3.8.2 更新链接 59 3.8.3 改变链接源 60 3.9 格式化数字、日期和时间 60 3.9.1 数字显示格式 60 3.9.2 日期和时间显示格式 67 3.9.3 删除自定义格式 70 第4章 创建高级公式 71 4.1 使用数组 71 使用数组公式 71 4.2 理解数组公式 72 数组公式在多重区域中的操作 73 4.3 使用数组常量 74 函数：使用或返回数组 74 4.4 使用迭代计算和循环引用 76 4.5 合并多表数据 78 4.5.1 按位置进行合并计算 78 4.5.2 按分类进行合并计算 81 4.6 在单元格中应用数据有效性规则 83 4.7 在工作表中使用对话框控件 85 4.7.1 显示开发工具标签 85 4.7.2 使用表单控件 86 4.7.3 给工作表添加控件 86 4.7.4 链接控件与单元格数值 87 4.7.5 了解工作表控件 87 第5章 解决公式中的问题 92 5.1 了解Excel中的错误值 92 5.1.1 #DIV/0! 92 5.1.2 #N/A 93 5.1.3 #NAME？ 93 5.1.4 #NULL! 95 5.1.5 #NUM! 95 5.1.6 #REF! 95 5.1.7 #VALUE! 96 5.2 解决公式中的其他问题 96 5.2.1 括号的缺失或不匹配 96 5.2.2 错误的公式结果 97 5.2.3 解决循环引用 98 5.3 使用IFERROR( )函数来解决公式中的问题 99 5.4 使用公式错误检查功能 99 设置错误检查选项 101 5.5 审核工作表 103 5.5.1 理解审核 103 5.5.2 追踪引用单元格 104 5.5.3 追踪从属单元格 104 5.5.4 追踪错误 105 5.5.5 移去追踪箭头 105 5.5.6 公式求值 105 第6章 理解函数 107 6.1 关于Excel的函数 107 6.2 函数的构成 108 6.3 在公式中输入函数 110 6.4 使用函数插入功能 111 6.5 加载分析工具库 113 第7章 使用文本函数 114 7.1 Excel的文本函数 114 7.2 使用字符和代码 115 7.2.1 CHAR( )函数 115 7.2.2 CODE( )函数 118 7.3 转换文本 119 7.3.1 LOWER( )函数 119 7.3.2 UPPER( )函数 119 7.3.3 PROPER( )函数 120 7.4 格式化文本 120 7.4.1 DOLLAR( )函数 120 7.4.2 FIXED( )函数 121 7.4.3 TEXT( )函数 121 7.4.4 显示工作簿*近更新 122 7.5 从字符串中删掉不需要的字符 122 7.5.1 TRIM( )函数 122 7.5.2 CLEAN( )函数 123 7.5.3 REPT( )函数：重复输入字符 124 7.5.4 REPT( )函数：填充单元格 124 7.5.5 REPT( )函数：绘制文本图表 124 7.6 提取子串 126 7.6.1 LEFT( )函数 126 7.6.2 RIGHT( )函数 126 7.6.3 MID( )函数 126 7.6.4 将文本更改为句首字母大写 127 7.6.5 日期转换公式 127 7.7 查找子串 128 7.7.1 FIND( )函数和SEARCH( )函数 128 7.7.2 案例分析：生成账号1 128 7.7.3 提取名或姓 129 7.7.4 提取名、姓及中间名缩写 130 7.7.5 确定列标签字母 131 7.8 用一个子串来代替另一个 132 7.8.1 REPLACE( )函数 132 7.8.2 SUBSTITUTE( )函数 132 7.8.3 从字符串中删除字符 133 7.8.4 从字符串中删除两个不同的字符 133 7.8.5 案例分析：生成账号2 134 7.8.6 删除换行符 134 第8章 使用逻辑函数和信息函数 135 8.1 在逻辑函数中添加智能 135 8.1.1 使用IF( )函数 136 8.1.2 完成复合逻辑测试 138 8.1.3 AND( )函数 140 8.1.4 OR( )函数 141 8.1.5 将逻辑函数与数组混合使用 143 8.2 案例分析：建立应收账款账龄表 148 8.2.1 计算更精确的到期日 149 8.2.2 将逾期发票按时间分类 150 8.3 通过信息函数来找到数据 151 8.3.1 CELL( )函数 152 8.3.2 ERROR.TYPE( )函数 154 8.3.3 INFO( )函数 156 8.3.4 IS函数 157 第9章 使用查找函数 159 9.1 了解查找表 160 9.2 CHOOSE( )函数 160 9.2.1 定义一周中某天的名称 161 9.2.2 定义财务年度月份 161 9.2.3 核算加权问卷 162 9.2.4 合并CHOOSE( )函数和工作表选项按钮 163 9.2.5 在表格中查找值 163 9.3 VLOOKUP( )函数 164 9.4 HLOOKUP( )函数 164 9.5 使用区域查找返回客户折扣率 165 9.6 使用区域查找返回税率 166 9.7 查找精确匹配 167 9.7.1 查找客户账号 167 9.7.2 精确匹配查找和单元格内组合框的结合使用 168 9.8 高级查找操作 169 9.8.1 MATCH( )和INDEX( )函数 169 9.8.2 使用列表框查找值 170 9.8.3 使用任意列作为查找列 171 9.8.4 创建行-列查找 172 9.8.5 创建多列查找 173 第 10章 使用日期和时间函数 174 10.1 Excel是如何处理日期和时间的 174 10.1.1 输入日期和时间 175 10.1.2 Excel和两位数的年份 176 10.2 使用Excel的日期函数 176 10.2.1 返回一个完整的日期 177 10.2.2 返回日期的组成部分 179 10.2.3 计算两个日期之间的天数差 186 10.3 使用Excel的时间函数 189 10.3.1 返回一个时间 190 10.3.2 返回部分时间 191 10.3.3 计算两个时间之间的时间差 192 10.4 案例分析：建立员工考勤表 193 10.4.1 输入考勤表数据 194 10.4.2 计算每日工作时间 194 10.4.3 计算每周工作时间 195 10.4.4 计算周薪 196 第 11章 使用数学函数 197 11.1 了解Excel的四舍五入函数 199 11.1.1 ROUND( )函数 200 11.1.2 MROUND( )函数 200 11.1.3 ROUNDDOWN( )和ROUNDUP( )函数 201 11.1.4 CEILING( )和FLOOR( )函数 202 11.1.5 EVEN( )和ODD( )函数 203 11.1.6 INT( )和TRUNC( )函数 203 11.1.7 使用四舍五入来避免误差 204 11.1.8 设置价格点 204 11.2 案例分析：四舍五入计费时间 205 11.3 数值求和 205 SUM( )函数 206 11.4 MOD( )函数 207 11.5 生成随机数 210 11.5.1 RAND( )函数 210 11.5.2 RANDBETWEEN( )函数 213 第 12章 使用统计函数 214 12.1 了解描述统计学 216 12.2 使用COUNT( )函数计数 216 12.3 计算平均值 217 12.3.1 AVERAGE( )函数 217 12.3.2 MEDIAN( )函数 217 12.3.3 MODE( )函数 218 12.3.4 计算加权平均值 218 12.4 计算极值 219 12.4.1 MAX( )和MIN( )函数 220 12.4.2 LARGE( )和SMALL( )函数 220 12.4.3 对排在前k位的值执行计算 221 12.4.4 对排在后k位的值执行计算 222 12.5 计算差异 222 12.5.1 计算全距 222 12.5.2 计算方差 222 12.5.3 计算标准差 223 12.6 使用频数分布 224 12.6.1 FREQUENCY( )函数 225 12.6.2 了解正态分布和NORMDIST( ) 函数 226 12.6.3 曲线形状1：SKEW( )函数 228 12.6.4 曲线形状2：KURT( )函数 229 12.7 使用分析工具库中的数据分析 工具 230 12.7.1 使用描述统计工具 232 12.7.2 使用相关系数工具 234 12.7.3 使用直方图工具 236 12.7.4 使用随机数发生器工具 237 12.7.5 使用排位与百分比排位工具 240 第 13章 使用表分析数据 242 13.1 将区域转换为表 243 13.2 表的基本操作 244 13.3 给表格内数据排序 246 13.3.1 执行更复杂的排序 246 13.3.2 按照自然顺序给表格排序 247 13.3.3 给域内的一部分排序 249 13.3.4 排序时忽略冠词 250 13.4 筛选表格数据 250 13.4.1 利用【筛选】功能来筛选 数据 251 13.4.2 使用复杂条件筛选表格 254 13.4.3 输入估算条件 257 13.4.4 复制筛选数据到不同的区域 258 13.5 在公式中引用表 259 13.5.1 使用表说明符 260 13.5.2 输入表公式 261 13.6 Excel的表函数 263 13.6.1 关于表函数 263 13.6.2 不需要条件区域的表函数 263 13.6.3 接受多重条件的表函数 265 13.6.4 需要条件区域的表函数 268 13.6.5 案例分析：在“缺陷”数据库中 应用统计表函数 271 第 14章 使用数据透视表分析 数据 273 14.1 什么是数据透视表 273 数据透视表是如何工作的 274 14.2 建立数据透视表 276 14.2.1 由表或区域建立数据透视表 276 14.2.2 从外部数据中建立数据 透视表 279 14.2.3 使用并自定义数据透视表 280 14.3 使用数据透视表的分类汇总 功能 281 14.4 更改数据字段的汇总计算 282 14.4.1 使用差异汇总计算 283 14.4.2 切换：差异计算 285 14.4.3 使用百分比汇总计算 285 14.4.4 按某一字段汇总 287 14.4.5 使用指数汇总计算 289 14.5 创建自定义数据透视表 计算 290 14.5.1 创建计算字段 291 14.5.2 创建计算项 293 14.6 案例分析：使用计算项制定 预算 294 14.7 在工作表公式中使用数据 透视表结果 296 第 15章 使用Excel的商业模型工具 298 15.1 使用模拟分析 298 15.1.1 设置单输入模拟运算表 299 15.1.2 在模拟运算表中添加更多的 公式 301 15.1.3 设置双输入模拟运算表 302 15.1.4 编辑模拟运算表 303 15.2 使用单变量求解 304 15.2.1 运行单变量求解 304 15.2.2 优化产品利润率 306 15.2.3 注意单变量求解的近似值 307 15.2.4 执行收支平衡分析 308 15.2.5 求解代数方程 310 15.3 使用方案 311 15.3.1 理解方案 311 15.3.2 设置工作表方案 312 15.3.3 添加方案 312 15.3.4 显示方案 313 15.3.5 编辑方案 314 15.3.6 合并方案 315 15.3.7 生成摘要报表 315 15.3.8 删除方案 317 第 16章 使用回归分析追踪趋势并 作出预测 318 16.1 选择回归分析方法 318 16.2 在线性数据中使用简单回归 319 16.2.1 使用*佳拟合线分析趋势 319 16.2.2 作出预测 326 16.3 案例分析：季节性销售模型的 趋势分析和预测 331 16.3.1 关于预测工作簿 331 16.3.2 计算常规趋势 332 16.3.3 计算预测趋势 333 16.3.4 计算季节性趋势 334 16.4 在非线性数据中使用简单 回归 338 16.4.1 使用指数趋势 338 16.4.2 使用对数趋势 343 16.4.3 使用幂趋势 345 16.4.4 使用多项式回归分析 348 16.4.5 使用多重回归分析 350 第 17章 使用规划求解解决复杂 问题 353 17.1 关于规划求解的一些背景 353 17.1.1 规划求解的优点 353 17.1.2 什么时候使用规划求解 354 17.2 加载规划求解 354 17.3 使用规划求解 355 17.4 添加约束 357 17.5 将解保存为方案 359 17.6 设置规划求解的其他选项 360 17.6.1 选择规划求解使用的方法 360 17.6.2 控制规划求解的工作 361 17.6.3 使用规划求解模型 363 17.7 理解规划求解的信息 364 17.8 案例分析：解决运输问题 365 17.9 显示规划求解报告 368 17.9.1 运算结果报告 368 17.9.2 敏感性报告 369 17.9.3 极限值报告 371 第 18章 建立贷款公式 372 18.1 了解货币的时间价值 372 18.2 计算贷款偿还 373 18.2.1 贷款偿还分析 373 18.2.2 解决气球式贷款 374 18.2.3 计算利息成本（一） 375 18.2.4 计算本金和利息 375 18.2.5 计算利息成本（二） 376 18.2.6 计算累积本金和利息 377 18.3 制定贷款分期偿还计划表 378 18.3.1 制定固定利率分期偿还 计划表 378 18.3.2 制定动态分期偿还计划表 379 18.4 计算贷款期限 381 18.5 计算贷款所需的利率 383 18.6 计算可借金额 384 18.7 案例分析：抵押贷款 384 18.7.1 建立浮动利率抵押分期偿还 计划表 385 18.7.2 允许抵押本金偿还 386 第 19章 建立投资公式 389 19.1 使用利率 389 19.1.1 了解复利 389 19.1.2 票面利率vs实际利率 389 19.1.3 票面利率和实际利率之间的 转换 390 19.2 计算将来值 391 19.2.1 整笔款项的将来值 392 19.2.2 系列存款的将来值 392 19.2.3 整笔款项加系列存款的 将来值 393 19.3 达到投资目标 393 19.3.1 计算所需利率 393 19.3.2 计算所需的周期数 394 19.3.3 计算所需的定期存款 395 19.3.4 计算所需的初始存款 396 19.3.5 根据浮动利率计算将来值 396 19.4 案例分析：建立投资计划表 397 第 20章 建立贴现公式 400 20.1 计算现值 400 20.1.1 将通货膨胀考虑进去 401 20.1.2 使用PV( )函数计算现值 401 20.1.3 收益型投资vs购置出租房 402 20.1.4 购买vs租赁 403 20.2 贴现现金流 404 20.2.1 计算净现值 405 20.2.2 使用NPV( )函数计算净现值 406 20.2.3 净现值和浮动现金流 407 20.2.4 不定期现金流的净现值 408 20.3 计算投资回收期 409 20.3.1 简单的无贴现投资回收期 409 20.3.2 确切的无贴现投资回收期 410 20.3.3 贴现投资回收期 411 20.3.4 计算内部收益率 412 20.3.5 使用IRR( )函数 412 20.3.6 计算不定期现金流的内部 收益率 413 20.3.7 计算修正内部收益率 413 20.4 案例分析：出版一本书 414 20.4.1 每单位常量 414 20.4.2 操作成本和销售 415 20.4.3 现金流 416 20.4.4 现金流分析 416