速查速用·Excel公式与函数应用技巧

第1章 公式编辑与数据引用技巧 1
1.1 公式的输入与编辑 2
技巧1 查看长公式中的某一步计算结果 2
技巧2 快速选中公式中引用的单元格 3
技巧3 隐藏公式 3
技巧4 大范围复制公式 5
技巧5 将公式运算结果转换为
数值 6
技巧6 查看工作表中的所有
公式 6
技巧7 公式记忆式键入 8
技巧8 审核公式 9
技巧9 为公式定义名称 9
1.2 单元格的引用 10
技巧10 数据源的相对引用 11
技巧11 数据源的绝对引用 11
技巧12 通过混合引用创建乘
法表 13
技巧13 引用多张工作表中特定数据源进行计算 13
技巧14 在相对引用和绝对引用之间进行切换 16
技巧15 在不同工作表中引用区域名称进行计算 16
第2章 逻辑信息函数实战
应用技巧 18
2.1 逻辑运算函数 19
技巧16 判断员工的销售额是否达标 19
技巧17 根据编码返回部门
名称 20
技巧18 使用AND函数考评成绩是否合格 20
技巧19 使用OR函数判断员工考核是否达标 21
技巧20 使用NOT函数进行
筛选 21
技巧21 使用数据组公式比较两组庞大数据 23
2.2 逻辑判断函数 24
技巧22 使用IF函数判断给定值是否在指定区间 24
技巧23 将工作表中的“0”
替换成“_” 26
技巧24 快速识别产品类别 27
技巧25 解决计算结果为“0”、错误值的问题 28
第3章 日期与时间函数实战
应用技巧 30
3.1 日期函数 31
技巧26 使用DATEVALUE函数计算请假天数 31
技巧27 返回当前日期与
星期数 32
技巧28 计算倒计时天数 32
技巧29 根据出生日期快速
计算年龄 34
技巧30 将非日期数据转换为标准的日期 35
技巧31 判断一个月的最大
天数 35
技巧32 在报表中自动填写当前
月份 36
技巧33 已知一年中的第几天，计算其准确日期 36
技巧34 快速自动追加
工龄工资 37
技巧35 批量计算员工工龄 39
技巧36 使用DATEDIF函数计算总借款天数 40
技巧37 使用DAYS360函数计算总借款天数 40
技巧38 计算还款剩余天数 42
技巧39 计算应收账款的
账龄 43
技巧40 快速查看某一日期对应
的星期数 43
技巧41 以中文星期数形式查看
日期对应的星期数 44
技巧42 查看指定年份
各月天数 45
技巧43 计算指定日期到月底的
天数 45
技巧44 计算两个日期之间的实
际工作日 46
技巧45 计算年假占全年工作日
的百分比 47
3.2 时间函数 47
技巧46 计算员工外出使用的
时间 48
技巧47 计算员工演讲所用的
时间 50
第4章 数学计算函数实战
应用技巧 53
4.1 常规计算 54
技巧48 使用SUM函数
求和 54
技巧49 已知销售量和销售单价
计算其总销售额 54
技巧50 在不连续的单元格中
求和 55
技巧51 统计某人的总
销售金额 56
技巧52 统计两位或多位人员的
总销售金额 57
技巧53 将数据按照月份进行
汇总 58
技巧54 统计不同时间段不同类
别产品的销售金额 59
技巧55 使用SUMIF函数按条
件对数字进行求和 59
技巧56 统计各部门数据
总数 60
技巧57 在SUMIF函数中使用通配符 61
技巧58 统计各个时段的销售
总金额 61
技巧59 统计多种类别产品的
总销售金额 62
技巧60 使用AVERAGE函数求
平均值 63
技巧61 使用SUMIFS函数实现
多条件统计 64
技巧62 统计某日期区间的销售
金额 64
4.2 舍入计算 65
技巧63 使用INT函数对平均销量取整 65
技巧64 使用ROUND函数按位数进行四舍五入 66
技巧65 使用MOD函数
取余数 66
技巧66 计算加班时长 67
技巧67 使用FLOOR函数制定
产品价格 68
技巧68 使用CEILING函数计算
通话费用 69
4.3 阶乘与随机数 69
技巧69 确定一组对象所有可能
的组合数目 70
技巧70 使用RAND函数随机创
建彩票号码 70
技巧71 使用PRODUCT函数计
算多个数字的乘积 71
技巧72 使用SUMPRODUCT函
数统计多种产品的总销
售金额 71
技巧73 统计非工作日销售
金额 71
第5章 文本信息函数实战
应用技巧 73
5.1 获取特定文本信息 74
技巧74 使用CONCATENATE函数自动提取序号 74
技巧75 从E-mail地址中提
取账号 74
技巧76 使用RIGHT函数判断性别 75
技巧77 使用REPLACE函数为电话号码升级 76
技巧78 从身份证号码中提取
出生日期 76
技巧79 从一段信息中分离三
段信息 77
技巧80 从单元格中提取有用的
数据并合并起来 78
技巧81 使用SUBSTITUTE函数
缩写名称 79
技巧82 去掉文本中的所有
空格 80
5.2 获取其他文本信息 80
技巧83 使用LEN函数检查身份
证位数是否正确 80
技巧84 计算各项课程的实际参
加人数 81
技巧85 检查具有固定长度的字符串位数是否正确 81
技巧86 使用EXACT函数比
较两个字符串是否
相同 82
技巧87 利用REPT函数一次性输
入多个相同符号 83
技巧88 用“一”表示员工销售
业绩 83
技巧89 解决因四舍五入而造成
的显示误差问题 84
第6章 统计分析函数实战
应用技巧 85
6.1 平均值函数 86
技巧90 使用AVERAGEA函数求不连续的平均值 86
技巧91 求平均值时忽略计算区域中的0值 86
技巧92 求包含文本值的
平均值 87
技巧93 按指定条件求
平均值 88
技巧94 统计指定月份的平均销售额 88
技巧95 在AVERAGEIF函数中使用通配符 89
技巧96 在AVERAGEIFS函数中使用通配符 90
技巧97 使用AVERAGEIFS
函数忽略计算中的
0值 91
技巧98 计算出满足多重条件的数据的平均值 91
技巧99 使用GEOMEAN函数计算几何平均值 91
技巧100 数据集的内部
平均值 92
6.2 数理统计函数 92
技巧101 使用MODE函数
统计哪种商品最受
欢迎 92
技巧102 使用CRITBINOM
函数检测产品的合
格数 93
技巧103 使用GROWTH函
数预测全年的销售
金额 94
6.3 条目统计函数 94
技巧104 统计销售记录
条数 94
技巧105 统计包含文本值的单
元格数 95
技巧106 使用COUNT函数按条
件统计 96
技巧107 统计出各类产品的销
售记录条数 96
技巧108 在COUNTIF函数中使
用通配符 97
技巧109 统计出大于指定数值的记录条数 98
技巧110 统计出大于某个单
元格中数值的记录
条数 98
技巧111 统计出大于平均值的
记录条数 99
技巧112 统计出指定区域中
满足多个条件的记
录数目 99
技巧113 统计空白单元格的
数目 100
6.4 最大值与最小值函数 100
技巧114 按指定条件返回最
高分 100
技巧115 返回数据表中前三名
分数 101
技巧116 返回数据表中后三名
分数 101
技巧117 使用MAX函数统计最
高分数 102
技巧118 使用MIN函数统计最
低分数 103
技巧119 求最小值时忽略
0值 103
技巧120 按条件求取
最大值 104
技巧121 计算学生考试成绩
中最高分数（包含
文本） 105
技巧122 计算学生考试成绩
中最低分数（包含
文本） 106
第7章 财务函数实战应用技巧 107
7.1 投资计算类函数 108
技巧123 使用PV函数计算某项
投资的现值 108
技巧124 使用PMT函数计算贷
款每期付款金额 109
技巧125 计算贷款指定期间的
本金偿还金额 109
技巧126 计算贷款在两个期
间累积偿还的本金
数额 110
技巧127 使用FV函数计算某项
投资的未来值 110
技巧128 使用NPER函数计
算出贷款的清还
年数 111
技巧129 使用NPV函数计算某
项投资的净现值 111
技巧130 计算出一组不定期盈
利额的净现值 112
技巧131 计算某项投资的投资
期数 112
技巧132 计算某项投资在可变
利率下的未来值 113
7.2 偿还率计算函数 113
技巧133 使用RATE函数计算某项借款的收益率 114
技巧134 使用IRR函数计算
某项投资的内部收
益率 114
技巧135 计算某项投资的修正内部收益率 114
7.3 折旧值计算函数 115
技巧136 使用SLN函数计算固
定资产的折旧值 115
技巧137 使用DB函数计算每年
的折旧值 116
技巧138 使用DB函数计算每月
的折旧值 116
技巧139 使用SYD函数计算
固定资产每年折
旧额 117
技巧140 使用DDB函数计
算固定资产每年折
旧额 118
技巧141 采用余额递减法计算
累积折旧额 119
技巧142 使用直线法计算累计
折旧额 120
7.4 证券计算函数 121
技巧143 使用YIELD函数
计算有价证券的收
益率 121
技巧144 计算证券成交日和
到期日之间的付息
次数 121
第8章 查找和引用函数实战
应用技巧 123
8.1 普通查询 124
技巧145 使用LOOKUP函
数查找信息（向
量型） 124
技巧146 使用LOOKUP函
数查找信息（数
组型） 125
技巧147 使用VLOOKUP函数进行查询 125
技巧148 使用CHOOSE函数判
断成绩是否合格 127
技巧149 求取一组数据的反转
数据 127
8.2 引用查询 128
技巧150 使用INDEX函数按指
定条件查找 128
技巧151 使用MATCH函数
返回指定元素所在
位置 128
技巧152 配合使用INDEX
与MATCH函数
查询 129
技巧153 使用INDEX与MATCH
函数反向查询 130
技巧154 使用INDEX与MATCH函数双条件查询 130
技巧155 使用TRANSPOSE函
数实现行列转置 131
技巧156 使用OFFSET函数实
现动态查询 132
8.3 建立有规律的三级序列
编号 133
技巧157 使用ROW函数建立三
级序列编号 133
技巧158 使用COLUMN函数建
立三级序列编号 133
第9章 数据库函数实战
应用技巧 135
9.1 DSUM函数 136
技巧159 统计销售员一个月对应的销售数量 136
技巧160 使用DSUM函数
统计去除指定条件
的数据 136
技巧161 在DSUM函数中使用
通配符 137
技巧162 使用DSUM函数进行
双条件计算 138
技巧163 DSUM函数与SUMIF
函数的区别 138
9.2 DAVERAGE函数 139
技巧164 统计某个职业的
平均工资 139
技巧165 在DAVERAGE函数参
数中使用通配符 139
技巧166 使用DAVERAGE函数
进行计算后查询 140
9.3 DCOUNT函数 141
技巧167 使用DCOUNT函
数统计满足条件的
数据 141
技巧168 使用DCOUNT函数忽
略0值统计数据 142
技巧169 使用DCOUNT函数实
现双条件统计 142
9.4 其他函数 143
技巧170 统计基本工资小于800元的员工人数 143
技巧171 统计指定销售日期
和产品名称对应的
销量 143
第10章 函数返回错误值的
解决办法 145
技巧172 “#####”错误值的解决办法 146
技巧173 “#N/A”错误值的解决办法 146
技巧174 “#DIV/0!”错误值的解决办法 147
技巧175 “#NAME?”错误值的解决办法 148
技巧176 “#VALUE!”错误值的解决办法 149
技巧177 “#NUM!”错误值的解决办法 149
技巧178 “#REF!”错误值的解决办法 150
技巧179 “#NULL”错误值的解决办法 150