随手查 Excel函数与公式应用技巧PDF电子书下载

第1章 函数基础知识 1

1.1函数基础 2

例1使用“自动求和”按钮求和 2

例2手动输入函数 3

例3通过功能区输入函数 3

例4如何查询函数 4

例5输入嵌套函数 4

例6如何自定义函数 5

例7怎样调用自定义函数 7

1.2公式基础 8

例1在Excel中直接输入公式 8

例2如何修改公式 9

例3通过引用快速输入公式 9

例4复制公式 10

例5让公式隐藏起来 11

例6如何保护公式不被修改 12

例7删除公式时保留计算结果 13

例8如何使用R1 C1样式 13

例9快速查看工作表中的所有公式 14

例10在同一工作簿中引起其他单元格数据 15

例11引用其他工作表中的单元格数据 15

例12用“错误检查”功能检查公式 15

例13使用“公式求值”功能 16

1.3数组的使用 17

例1使用数组公式 17

例2如何显示数组公式的计算结果 18

例3创建计算多个结果的数组公式 18

例4修改数组公式 19

1.4错误分析与处理 19

例1解决“####”错误 19

例2解决“#DIV/O！”错误 20

例3解决“#VALUE！”错误 20

例4解决“#NUM！”错误 21

例5解决“#NULL！”错误 22

例6解决“#NAME？”错误 22

例7解决“#REF！”错误 24

例8解决“#N/A”错误 24

第2章 财务函数应用技巧 27

2.1计算支付额和累计额 28

例1贷款在给定期间累计须偿还的利息数 28

例2返回贷款在给定期间累计偿还的本金数额 29

例3计算贷款的每期付款额 30

例4返回给定期限内投资的利息偿还额 31

例5计算房屋抵押贷款月偿本金 32

例6计算购房还款金额 33

2.2计算利率和资产 33

例1计算资产在给定期间内的折旧值 33

例2计算有效的年利率 34

例3计算某项投资的未来值 35

例4返回一组现金流的内部收益率 36

例5计算连续期间内现金流的修正内部收益率 37

例6计算名义年利率 38

例7计算投资总期数 38

例8计算投资净现值 39

例9计算年金的各期利率 40

例10计算资产的线性折旧值 41

例11按年限折旧法计算折旧值 42

例12计算任何期间内的折旧值 43

例13计算现金流的净现值 44

例14预知n年后的存款总额 45

2.3证券的计算 46

例1计算定期付息有价证券的应计利息 46

例2计算首期付息日不固定的有价证券的收益率 47

例3计算到期一次性付息的有价证券的应计利息 48

例4返回当前付息期内截止到成交日的天数 49

例5计算从成交日到下一付息日之间的天数 50

例6返回成交日和到期日之间的付息次数 51

例7计算成交日之前的付息日期 51

例8返回有价证券的贴现率 52

例9计算一次性付息证券的利率 53

例10计算首期付息日不固定的有价证券价格 54

例11计算首期付息日不固定的有价证券的收益率 55

例12计算末期付息日不固定的有价证券价格 56

例13计算末期付息日不固定的有价证券的收益率 57

例14计算有价证券到期收回的金额 57

例15计算折价发行的有价证券的价格 58

例16计算到期付息的有价证券的价格 59

例17计算折价发行的有价证券的年收益率 60

例18计算到期付息的有价证券的年收益率 60

例19计算面值￥100的国库券价格 61

例20计算国库券的收益率 61

例21计算国库券的等效收益率 62

例22计算国库券的应计利息 63

例23计算短期债券的应计利息 64

第3章 逻辑与文本函数应用技巧 65

3.1逻辑函数应用技巧 66

例1创建条件公式 66

例2对数据执行真假值判断 67

例3不以TRUE或FALSE作为逻辑判断结果 68

例4判断支出是否在预算内 69

例5返回逻辑值FALSE或TRUE 69

例6自动给出成绩的优劣等级 69

例7使用IF函数计算个人所得税 70

3.2文本操作技巧 72

例1返回文本字符串的字符数 72

例2返回文本字符串中代表字符的字节数 73

例3返回字符串的字符起始位置（区分大小写） 73

例4返回字符串的字节起始位置（区分大小写） 74

例5返回字符第1次出现的位置（不区分大小写） 75

例6返回字符基于字节数的起始位置（不区分大小写） 75

例7从第一个字符开始返回指定个数的字符 76

例8从第一个字符开始返回指定字节数的字符 77

例9从指定位置起返回指定长度的字符 78

例10从指定位置起返回指定字节数的字符 78

例11从最后一个字符开始返回指定个数的字符 79

例12从最后一个字符起返回指定字节数的字符 80

例13将部分字符用另一个字符串替换 80

例14将部分字符根据指定字节数用新字符串替换 81

3.3文本转换技巧 82

例1将半角字符转换为全角字符 82

例2将全角字符转换为半角字符 82

例3将大写字母转换为小写字母 82

例4将小写转换为大写形式 83

例5将数值转换为按指定格式表示的文本 83

例6将字符串中的首字母转换为大写形式 84

例7将数字按指定位数取整 84

例8将数字转换为大写汉字 85

例9将数字转换为泰语文本 86

例10将文本转换为数字 86

例11返回对应于数字代码的字符 87

例12返回文本字符串中第一个字符的数字代码 87

例13返回值引用的文本 88

例14四舍五入数值并转换为带＄和位符号的文本 89

例15四舍五入数值并转换为带￥和位符号的文本 90

3.4文本比较与删除技巧 90

例1在单元格中重复某个字符 90

例2计算非空白单元格的个数 91

例3比较单元格的内容 92

例4从文本左侧删除指定数量的字符 92

例5从文本右侧删除指定数量的字符 93

例6合并电话号码的区号和号码 93

例7删除单元格开头和结尾的空格 94

例8只显示身份号码后四位数 94

例9从身份证号中提取出生日期和性别 95

第4章 日期和时间函数应用技巧 97

4.1日期和时间的输入技巧 98

例1插入当前日期和时间 98

例2插入可更新的日期和时间 98

例3在时间单位之间转换时间 99

例4返回时间值的小时数 100

例5把时间值换算成秒数 100

例6将小时从十进制数转换为标准时间格式 101

例7快速输入有规律的日期系列 102

例8将日期转换为日期序列号 102

例9显示员工进入公司的年份 103

例10显示商品的销售月份 104

4.2计算期间差 105

例1计算两个日期之间的工作日 105

例2计算两个日期之间的年份数 106

例3计算两个日期之间的月份数 107

例4计算两个时间之间的小时数 108

例5计算两个时间间隔的小时和分钟数 108

例6计算两个时间间隔的精确时间 109

例7计算某个日期之前的天数 109

例8计算月底日期 110

例9计算某一年的天数 111

例10计算日期对应的星期数 112

例11计算两个日期之间的周数 113

例12根据生日计算员工年龄 114

例13计算员工的工龄 115

4.3对日期求和 116

例1为日期添加天数 116

例2为日期添加月数 116

例3为日期添加年数 117

例4对24小时制时间求和 117

例5按12小时制给时间添加小时 118

例6按12小时制给时间添加分钟 119

例7按12小时制给时间添加秒 119

第5章 查找与引用函数应用技巧 121

5.1查找函数应用技巧 122

例1基于索引号返回参数列表中的数值 122

例2使用数字格式以隐藏零值 123

例3通过与首行的值对比来查找值 123

例4通过与首列值对比来查找值 125

例5返回数组中指定的单元格或单元格数组的数值 126

例6在一个未排序的区域中查找值 127

例7在未排序且大小不定的区域里查找值 128

例8计算某个值重复出现的次数 130

例9计算多个值重复出现的次数 131

例10提取表格中的姓名 132

例11计算60分到90分之间的人数 133

例12根据姓名查找身份证号 134

例13将字母转换为评分 135

5.2单元格引用技巧 137

例1建立文本类型的单元格地址 137

例2查询抽查的产品编号地址 138

例3计算引用中包含的区域个数 139

例4返回给定引用的列标 140

例5返回由文本字符串指定的引用 141

例6返回指定行列交叉处引用的单元格 142

例7多区域计数 142

例8计算多个工作表中成绩的最大平均值 143

例9返回引用的行号 144

例10产生每两行空一行后累加1的编号 145

例11检查每个志愿是否与前面的重复 146

例12返回数组或引用的行数 147

例13将行转置成列 147

第6章 统计函数应用技巧 149

6.1基础统计 150

例1返回满足条件的单元格的平均值 150

例2计算参数列表中数值的平均值 152

例3返回满足多重条件的单元格的平均值 153

例4返回数据与其均值的绝对偏差的平均值 155

例5统计空白单元格数目 155

例6计算参数列表中数字的个数 156

例7计算参数列表中值的个数 157

例8计算区域中满足给定条件的单元格的个数 158

例9使用通配符 159

例10返回几何平均值 160

例11返回偏差的平方和 160

例12返回数据集的峰值 161

例13计算数据集合的调和平均值 162

例14返回数据集中出现最多的值 163

例15返回数据集中第k个最大值 163

例16返回数据集的四分位数 164

例17返回分布的偏斜度 165

例18返回参数列表中的最大值 166

例19返回给定数值集合的中值 167

例20返回参数列表中的最小值 167

例21返回区域中的第k个百分位值 168

例22返回数据集中值的百分比排位 169

例23返回给定数目对象的排列数 170

例24基于样本估算标准偏差 171

例25计算基于整个样本总体的标准偏差 171

例26基于样本估算方差 172

例27基于整个样本总体计算方差 173

6.2概率分布与检验 173

例1计算Beta累积分布函数 173

例2返回一元二项式分布的概率值 174

例3返回X2分布的单尾概率 176

例4返回X2分布单尾概率的反函数值 177

例5返回总体平均值的置信区间 177

例6返回指数分布 178

例7 返回F概率分布 180

例8返回F概率分布的反函数值 181

例9返回使累积二项式分布大于等于临界值的最小值 182

例10以矢量数组形式返回频率分布 183

例11返回伽玛分布 184

例12返回反伽玛累积分布 185

例13返回伽玛函数的自然对数 187

例14返回x的对数累积分布函数 187

例15返回x的对数累积分布函数的反函数 188

例16返回负二项式分布 189

例17返回正态累积分布 190

例18返回反正态累积分布 191

例19返回标准正态累积分布 193

例20返回反标准正态累积分布 193

例21返回皮尔生分布 194

例22返回泊松分布 195

例23返回韦伯分布 196

例24返回区域中的值在上下限之间的概率 198

6.3协方差、相关与回归 199

例1返回协方差 199

例2计算斜率和Y轴截距 201

例3计算描述指数回归拟合曲线的数值数组 202

例4返回两个数据集之间的相关系数 204

例5返回点x的Fisher变换 205

例6返回Fisher变换的反函数值 205

例7根据线性趋势返回值 206

例8返回线性回归直线的斜率 207

例9根据现有数据预测指数增长值 208

例10计算每个x的y预测值时所产生的标准误差 210

例11计算Pearson乘积矩相关系数的平方 211

第7章 信息与工程函数应用技巧 213

7.1返回相应信息函数 214

例1返回数值的类型 214

例2判断公式的错误类型 215

例3返回错误值#N/A 216

例4返回一个字符串的格式代码 217

例5返回当前操作环境的信息 219

7.2数据检查与转换 221

例1检测一个值是否为文本 221

例2检测一个值是否不是文本 221

例3判断测试对象是否为数值 222

例4判断数值是否为奇数 222

例5判断数值是否为偶数 223

例6判断测试对象是否为逻辑值 223

例7判断测试对象是否为空单元格 224

例8检测指定单元格是否为错误值 225

例9判断测试对象是否为#N/A错误值 225

例10判断对象是否为除#N/A外的错误值 226

例11将参数中指定的值转换为数值形式 227

7.3数据的换算技巧 227

例1换算数值的单位 227

例2将二进制数换算为八进制数 229

例3将二进制数换算为十进制数 229

例4将二进制数换算为十六进制数 230

例5将八进制数转换为二进制数 230

例6将八进制数转换为十进制数 231

例7将八进制数转换为十六进制数 232

例8将十进制数换算为二进制数 232

例9将十进制数换算为八进制数 233

例10将十进制数换算为十六进制数 234

例11将十六进制数换算为八进制数 234

例12将十六进制数换算为二进制数 235

例13将十六进制数换算为十进制数 236

例14测试两个数值是否相等 237

例15测试某个数值是否比阀值大 237

7.4其他工程类函数应用技巧 238

例1将实系数和虚系数转换为复数 238

例2返回实数的实部 239

例3返回实数的虚部 239

例4返回复数的绝对值 240

例5计算两个复数之和 241

例6计算两个复数之差 241

例7返回复数之积 242

例8返回两个复数的商 242

例9求复数的共轭复数 243

例10返回复数的指数 244

例11返回复数以2为底的对数 244

例12返回复数的整数幂 245

例13返回复数的自然对数 246

例14返回复数以10为底的对数 246

例15返回复数的平方根 247

例16计算数字的双倍阶乘 247

例17返回以弧度表示的角 248

例18返回复数的余弦 248

例19返回复数的正弦值 249

例20求误差函数在上下限之间的积分 249

例21返回余误差函数 250

第8章 其他函数应用技巧 251

8.1数据库函数应用技巧 252

例1计算员工的销售额 252

例2计算公司男/女员工的人数 253

例3统计公司有“联系电话”的员工人数 255

例4统计销售额最高的员工 256

例5统计销售量最少的员工 257

例6计算A组总成绩 259

例7计算A组平均成绩 260

例8统计某个单项成绩大于90分的最高总成绩 261

例9统计某个单项成绩小于80分的最低总成绩 262

例10计算公司女员工销售额的方差 263

例11计算公司女员工销售额的总体方差 264

例12计算公司员工的年龄标准差 265

例13计算公司员工的总体年龄标准差 266

8.2外部函数应用技巧 268

例1使用CALL函数 268

例2返回已注册过的指定动态链接库 270

例3返回已注册过的代码源的注册号 270

例4与外部数据源连接 271

例5欧洲各国的货币及其ISO代码 274

例6换算欧洲货币 275

例7欧元与法国法郎的相互换算 276

例8将德国马克转换为欧元 276

例9将荷兰盾转换为欧元 277

例10德拉马克与里拉的相互换算 278

8.3多维数据集函数 278

例1返回关键性能指标KPI属性 278

例2返回多维数据集中的成员或元组 279

例3返回多维数据集中成员属性的值 280

例4返回集合中的项目数 280

例5返回集合中的第n个成员或排名成员 280

例6从多维数据集中返回汇总值 281

例7定义成员或元组的计算集 281