教师必须知道的80个Excel函数 双色版PDF电子书下载

第1章 Excel函数知识 2

1.1 Excel常见名词 2

1.2 单元格引用 3

1.3 公式基础 9

1.4 函数基础 12

1.5 错误分析与检查 22

1.6 Excel数据安全 24

第2章 数学和三角函数2.1 SUM 返回某一单元格区域中所有数字之和 30

2.2 SUMFS 对区域中满足多个条件的单元格求和 32

2.3 SUMPRODUCT 返回数组间对应的元素乘积之和 36

2.4 MMULT 返回两个数组的矩阵乘积 38

2.5 RAND 返回一个随机值 44

2.6 EVEN 返回沿绝对值增大方向取整后最接近的偶数 47

2.7 INT 返回参数的整数部分 49

2.8 CEILING 返回沿绝对值增大方向的舍入值 51

2.9 SUBTOTAL 返回列表或数据库中的分类汇总 53

2.10 PRODUCT 返回所有参数乘积值 58

2.11 MDETERM 返回一个数组的矩阵行列式的值 60

2.12 MINVERSE 返回数组中存储的矩阵的逆矩阵 62

2.13 FLOOR 将number向下舍入（向零的方向）到最接近的singnificance的倍数 64

2.14 MOD 返回两数相除的余数 66

第3章 逻辑函数 70

3.1 AND 判定指定的多个条件是否全部成立 70

3.2 NOT 对其参数的逻辑求反 73

3.3 IF 根据指定的条件返回不同的结果 76

3.4 OR 判定指定的任一条件是为真，即返回真 81

第4章 文本函数 84

4.1 CONCATENATE 84

4.2 FIND 查找字符串字符起始位置（区分大小写） 87

4.3 SEARCH 查找字符串字符起始位置（不区分大小写） 90

4.4 SUBSTITUTE 在文本字符串中用新文本替换旧文本 93

4.5 LEFT 返回文本值中最左边的字符 96

4.6 MID 从文本字符串中的指定位置起返回特定个数的字符 98

4.7 RIGHT 返回文本值中最右边的字符 101

4.8 FINDB 查找字符串字节起始位置 104

4.9 SEARCHB 查找字符串字节起始位置（不区分大小写） 106

4.10 REPLACEB 替换字符串中的部分字符（根据字节数） 109

4.11 REPLACE 替换字符串中的部分字符（根据字符数） 111

4.12 TEXT 设置数字格式并将其转换为文本 113

4.13 MIDB 从文本字符串中的指定位置起返回特定个数的字符 117

4.14 LEFTB 根据所指定的字节数返回文本字符串中最左边的字符 119

第5章 统计函数 122

5.1 AVERAGE 返回参数的平均值 122

5.2 AVERAGEA 计算参数列表中数值的平均值 127

5.3 AVERAGEIFS 返回满足多重条件的所有单元格的平均值 131

5.4 COUNT 返回参数列表中的数字个数 134

5.5 COUNTA 返回参数列表中非空值的单元格个数 138

5.6 COUNTIF 计算区域中满足给定条件的单元格的个数 142

5.7 COUNTIFS 计算区域内符合多个条件的单元格的数量 153

5.8 MAX 返回一组值中的最大值 157

5.9 MAXA 返回参数列表中的最大值 162

5.10 MEDIAN 返回给定数值的中值 165

5.11 MIN 返回参数中最小值 167

5.12 SMALL 求一组数值中第k个最小值 171

5.13 LARGE 求一组数值中第k个最大值 178

5.14 RANK 返回一组数字的排列顺序 182

5.15 FREQUENCY 返回一个垂直数组 186

5.16 AVEDEV 返回一组数据与其平均值的绝对偏差的平均值 198

5.17 GEOMEAN 返回正数数组或区域的几何平均值 200

5.18 COUNTBLANK 返回参数列表中非空值的单元格个数 202

5.19 PERCENTILE 返回区域中数值的第K个百分点的值 204

5.20 PERCENTRANK 返回特定数值在一个数据集中的百分比排位 206

5.21 QUARTILE 返回数据集的四分位数 208

第6章 日期和时间函数6.1 YEAR 返回某日期对应的年份 216

6.2 TODAY 返回当前日期 218

6.3 NOW 返回当前的日期和时间 220

6.4 MONTH 返回某日期对应的月份 222

6.5 DATE 返回特定日期的年、月、日 224

6.6 TIME 返回某一特定时间的小数值 227

第7章 查找和引用函数7.1 ROW 返回引用的行号 230

7.2 ROWS 返回数组或引用的行数 237

7.3 COLUMN 返回引用的列标 239

7.4 COLUMNS 返回数组或引用的列数 242

7.5 INDIRECT 返回由文本字符串指定的引用 244

7.6 ADDRESS 按照指定的行号和列标，返回单元格引用地址 251

7.7 OFFSET 以指定引用为参照系，通过给定偏移量得到新的引用 254

7.8 INDEX 返回指定单元格或单元格数组的值（数组形式） 272

7.9 LOOKUP 从单列或单行区域返回值（向量形式） 279

7.10 VLOOKUP 在数据表的首列查找指定的值，并返回数据表当前行中指定列的值 284

7.11 HLOOKUP 在数据表的首行查找指定的数值，并在数据表中指定行的同一列中返回一个数值 291

7.12 MATCH 返回指定方式下与指定数值匹配的元素位置 296

7.13 CHOOSE 返回指定数值参数列表中的数值 301

7.14 HYPERLINK 创建一个快捷方式，打开存储在网络服务器、Intranet或Internet中的文件 304

7.15 AREAS 返回引用中包含的区域个数 307

第8章 信息函数 310

8.1 ISEVEN 偶数判断 310

8.2 ISTEXT 文本判断 314

8.3 N 返回转换为数字后的值 317

8.4 NAMES 返回指定工作簿中被定义的指定名字 322

8.5 GET.OBJECT 返回可用于处理该对象的其他宏公式方面的信息 324

8.6 EVALUATE 对以文字表示的一个公式或表达式求值，并返回结果 329

第2章 数学和三角函数案例1 求图书订购价格总和（SUM） 30

案例2 只汇总60到80分的成绩（SUMIFS） 33

案例3 汇总三年级二班人员迟到次数（SUMIFS） 34

案例4 汇总一班人员获奖次数（SUMPRODUCT） 36

案例5 求第一名人员最多有几次（MMULT） 38

案例6 在不同班级有同名前提下计算学生人数（MMULT） 39

案例7 计算前进中学参赛人数（MMULT） 40

案例8 计算成绩在60到80分之间合计数与个数（MMULT） 42

案例9 将20个学生的考位随机排列（RAND） 44

案例10 将三个学校植树人员随机分组（RAND） 46

案例11 统计参考人数（EVEN） 47

案例12 成绩表的格式转换（INT） 49

案例13 成绩表的格式转换（CEILING） 51

案例14 按需求对成绩进行分类汇总（SUBTOTAL） 54

案例15 不间断的序号（SUBTOTAL） 55

案例16 仅对筛选出的人员排名次（SUBTOTAL） 56

案例17 计算每种教材打折后的价格（PRODUCT） 58

案例18 解二元联立方程组（MDETERM） 60

案例19 解多元联立方程组（MINVERSE） 62

案例20 FLCOOR函数处理正负数混合区域（FLOOR） 64

案例21 将数值逐位相加成一位数（MOD） 66

第3章 逻辑函数 70

案例22 判断学生是否符合奖学金发放条件（AND） 70

案例23 所有裁判都给“通过”就进入决赛（AND） 71

案例24 没有任何裁判给“不通过”就进行决赛（NOT） 73

案例25 计算成绩区域数字个数（NOT） 74

案例26 评定学生成绩是否及格（IF） 76

案例27 根据学生成绩自动产生评语（IF） 77

案例28 将成绩从大到小排列（IF） 78

案例29 排除空值（IF） 79

案例30 根据年龄判断教师职工是否退休（OR） 81

第4章 文本函数 84

案例31 计算平均成绩及评判是否及格（CONCATENATE） 84

案例32 提取前三名人员姓名（CONCATENATE） 85

案例33 提取学校与医院地址（FIND） 88

案例34 将年级或者专业与班级名称分开（SEARCH） 91

案例35 提取最后一次短跑成绩（SUBSTITUTE） 94

案例36 计算小学参赛者人数（LEFT） 96

案例37 提取成绩并计算平均（MID） 99

案例38 提取参赛选手姓名（MID） 100

案例39 对所有人员按平均分排序（RIGHT） 101

案例40 提取学生英文名字（FINDB） 105

案例41 提取各软件的版本号（SEARCHB） 107

案例42 将15位身份证号码升级为18位（REPLACEB） 110

案例43 求最大时间（REPLACE） 112

案例44 根据身份证号码计算学生出生年月日（TEXT） 115

案例45 提取各年级获奖人员姓名（MIDB） 118

案例46 提取学生所在省市名称（LEFTB） 120

第5章 统计函数 122

案例47 计算平均成绩（忽略缺考人员）（AVERAGE） 122

案例48 计算90分以上的平均成绩（AVERAGE） 123

案例49 计算当前表以外的所有工作表平均值（AVERAGE） 125

案例50 计算平均成绩，成绩空白也计算（AVERAGEA） 127

案例51 计算二年级所有人员的平均获奖率（AVERAGEA） 128

案例52 统计前三名人员的平均成绩（AVERAGEA） 130

案例53 去掉首尾求平均分（AVERAGEIFS） 132

案例54 计算及格率（COUNT） 134

案例55 统计各分数段人数（COUNT） 135

案例56 统计有多少个选手（COUNT） 136

案例57 统计出勤异常人数（COUNTA） 138

案例58 判断是否有人缺考（COUNTA） 139

案例59 根据毕业学校统计中学学历人数（COUNTIF） 142

案例60 计算两列数据相同个数（COUNTIF） 143

案例61 统计连续三次进入前十名的人数（COUNTIF） 145

案例62 统计淘汰者人数（COUNTIF） 146

案例63 统计区域中不重复数据的个数（COUNTIF） 147

案例64 统计冠军榜前三名（COUNTIF） 148

案例65 统计真空、假空单元格个数（COUNTIF） 150

案例66 对名册表进行混合编号（COUNTIF） 151

案例67 统计大于80分的三好学生个数（COUNTIFS） 154

案例68 统计二班和三班数学竞赛获奖人数（COUNTIFS） 155

案例69 根据身高计算各班淘汰人数（COUNTIFS） 156

案例70 查找第一名学生姓名（MAX） 157

案例71 计算卫冕失败最多的次数（MAX） 158

案例72 低于平均成绩中的最优成绩（MAX） 159

案例73 计算语文成绩大于90分者的最高总成绩（MAXA） 162

案例74 计算数学成绩等于100分的男生最高总成绩（MAXA） 164

案例75 计算中间成绩（MEDIAN） 165

案例76 确定最佳成绩（MIN） 167

案例77 将科目与成绩分开（MIN） 168

案例78 计算五个班的第一名人员的最低成绩（MIN） 170

案例79 第三个最小的成绩（SMALL） 171

案例80 计算最后三名成绩的平均值（SMALL） 172

案例81 将成绩按升序排列（SMALL） 173

案例82 罗列三个班第一名成绩（SMALL） 174

案例83 罗列三个工作表B列最后三名成绩（SMALL） 175

案例84 第3个最小成绩到第6个最小成绩之间的人数（SMALL） 176

案例85 计算与第3个最大值并列的个数（LARGE） 178

案例86 按成绩列出学生排行榜（LARGE） 179

案例87 最后一次获得第一名是第几届（LARGE） 180

案例88 对学生成绩排名（RANK） 182

案例89 计算两列数值相同个数（RANK） 183

案例90 查询某人成绩在三个班中的排名（RANK） 185

案例91 分别统计每个分数段的人员个数（FREQUENCY） 187

案例92 蝉联冠军最多的次数（FREQUENCY） 188

案例93 计算最多经过几次测试才成功（FREQUENCY） 189

案例94 计算三个不连续区间的频率分布（FREQUENCY） 190

案例95 计算小学加初中人数及中专加大学人数（FREQUENCY） 191

案例96 计算文本的频率分布（FREQUENCY） 192

案例97 夺冠排行榜（FREQUENCY） 193

案例98 谁蝉联冠军次数最多（FREQUENCY） 194

案例99 中国式排名（FREQUENCY） 195

案例100 谁获得第二名（FREQUENCY） 196

案例101 计算零件质量系数的平均偏差（AVEDEV） 198

案例102 计算利润的平均增长率（GEOMEAN） 200

案例103 统计未参加测试的人数（COUNTBLANK） 202

案例104 根据习题完成量计算指定百分点的值（PERCENTILE） 204

案例105 计算某个学生所完成的习题量在所有学生中的百分比排位（PERCENTRANK） 206

案例106 根据学生习题完成情况计算四分位数（QUARTILE） 209

第6章 日期和时间函数案例107 计算2000年前电脑培训平均收费（YEAR） 216

案例108 本月有几个科目需要结束课时（TODAY） 218

案例109 确定是否已到丢弃时间（NOW） 220

案例110 计算8月份笔筒和毛笔的进货数量（MONTH） 222

案例111 确定今年母亲节的日期（DATE） 225

案例112 安排会议时间（TIME） 227

第7章 查找和引用函数案例113 最后一次不及格是哪次测试（ROW） 231

案例114 计算第11名到第30名学员的平均成绩（ROW） 232

案例115 计算成绩排名，不能产生并列名次（ROW） 233

案例116 对班级和成绩升序排列（ROW） 234

案例117 罗列第一名学生姓名（ROW） 235

案例118 检测每个志愿是否与之前的重复（ROWS） 237

案例119 班级成绩查询（COLUMN） 239

案例120 罗列每日缺席名单（COLUMN） 240

案例121 提取引用区域右下角的数据（COLUMNS） 242

案例122 整理成绩单（INDIRECT） 245

案例123 合并三个工作表的数据（INDIRECT） 246

案例124 多区域计数（INDIRECT） 248

案例125 计算五个工作表最大平均值（INDIRECT） 249

案例126 根据下拉列表罗列班级成绩第一名姓名（ADDRESS） 252

案例127 查询成绩（OFFSET） 255

案例128 计算10届运动会中有几次破纪录（OFFSET） 256

案例129 计算至少两科不及格的学生人数（OFFSET） 257

案例130 列出成绩最好的科目（OFFSET） 258

案例131 计算及格率不超过50％的科目数（OFFSET） 259

案例132 计算语文、英语、化学、政治哪科总分最高（OFFSET） 260

案例133 罗列及格率最高的学生姓名（OFFSET） 261

案例134 排列植树最多的班级（OFFSET） 262

案例135 多表成绩查询（OFFSET） 263

案例136 计算每个学生总分是否高于本班平均成绩（OFFSET） 265

案例137 计算每个学生进入前三名的科目数（OFFSET） 266

案例138 计算高于单科平均值的科目总数（OFFSET） 267

案例139 罗列平均成绩倒数三名的班级（OFFSET） 268

案例140 重组培训科目表（OFFSET） 269

案例141 分别计算每个班第一名的成绩和姓名（OFFSET） 270

案例142 计算60分到95分之间的人员个数（INDEX） 273

案例143 按身高对学生排列座次表（INDEX） 274

案例144 重组教师授课表（INDEX） 275

案例145 提取三个不规则区域的交集（INDEX） 277

案例146 计算某班六年中谁获第一名次数最多（LOOKUP） 280

案例147 罗列每个名次的所有姓名（LOOKUP） 281

案例148 填补空白区（LOOKUP） 282

案例149 将得分转换成等级（VLOOKUP） 285

案例150 查找双列信息（VLOOKUP） 286

案例151 按学历对姓名排序（VLOOKUP） 288

案例152 多工作表查找最大值（VLOOKUP） 289

案例153 在同一行查找数据（HLOOKUP） 292

案例154 制作准考证（HLOOKUP） 293

案例155 计算补课科目总数（MATCH） 297

案例156 按班级插入分隔行（MATCH） 298

案例157 统计一、二班举重参赛人员数（MATCH） 299

案例158 让VLOOKUP函数在多区域查找（CHOOSE） 302

案例159 选择二年级旷课人员名单（HYPERLINK） 305

案例160 统计分公司数量（AREAS） 307

第8章 信息函数 310

案例161 计算期末平均成绩（ISEVEN） 310

案例162 提取期末成绩明细（ISEVEN） 311

案例163 提取每年级第一名名单（ISTEXT） 314

案例164 统计各班所有科目成绩大于60分者人数（N） 317

案例165 将数据间隔着色（N） 319

案例166 罗列工作簿中所有名称（NAMES） 322

案例167 提取单元格中的批注（GET.OBJECT） 327

案例168 按分隔符取数并求平均（EVALUATE） 329