【Excel函数基础】75个Excel函数搞定一切.pdf

75 个 Excel 函数搞定一切 EXCEL 爱好者必学的基础函数 75 个 Excel 函数搞定一切 悟君居士 著 北京 BEIJING 致谢 作者是一个 Excel 爱好者，初学 Excel 函数时，感觉 Excel 函数太多太复杂，不知道从哪里入手。有幸找到了一位微软 MVP，Sumit Bansal，向他学了一些 Excel 函数。作者在学习的过程中动手将这些函数一一进行演练，从而对 Excel 函数有了一定的掌握。此书即作者平时练习的手稿整理。在本书完成过程中，还要非常感谢韩旭、李浩、高晓东等几位朋友的支持，也感谢他们所提供的宝贵建议。悟君居士 2016-04-12 前言 初入职场时，我们更多地工作可能是与表格打交道。然而，很多刚毕业的大学生初入职场时，对 Excel 掌握并不熟练，尤其是非常重要的 Excel 函数，平时运用就更加少了。面对职场工作中繁琐的数据，他们往往不知道如何用 Excel 函数进行处理。市面上大多数 Excel 函数的书有很多，要么太长，让人望而生畏；要么是枯燥乏味，让人看不下去。因此，作者在学习函数的过程中将练习笔记整理成一本短小精悍的手册。希望能够帮助那些想要学习 Excel 函数却不知道从哪里入手的人迅速提高自己的 Excel 函数水平。本书主要适用于以下几类人群：初入职场的毕业生。Excel 函数基础薄弱者。需要获得 Excel 学习资源者。尽管作者已经尽力确保本书内容的准确性和完整性，但由于个人能力有限，书中难免出现错误，如遇错误，也欢迎读者进行批评指正。此外，如果想获得更多的 Excel 学习资源，欢迎光临作者博客：http:/ 欢迎各位读者关注作者新浪微博，与作者进行关于 Excel 方面的交流与互动，新浪微博二维码为：简介 本书主要是面向于 Excel 函数基础薄弱，且想要提高自身函数水平的人群。与其他 Excel 函数相关书籍不同的是，本书主要存在以下几个特点：言简意赅。以最简单的语言帮助读者理解函数。案例教学。每个函数都通过多个案例进行展示和解释。资源丰富。作者在本书最后给读者提供了大量可以学习 Excel 的网上资源。本书的安排如下：本书主要分为 6 个章节，每个章节相对独立，可以跳跃学习。章节安排如下：第1章 文本函数。本章一共介绍了 13 个常用的文本函数。第2章 查找引用函数。这一章共介绍了 10 个常用的查找引用函数。第3章 逻辑函数。本章介绍了 8 个相关的逻辑函数。第4章 日期函数。这一章介绍了 13 个与时间和日期相关的函数。第5章 数学函数。本章介绍了 9 个常用的数学函数。第6章 统计函数。这一章介绍了 11 个常用的统计函数。希望读者朋友们通过对此书能够有所收获，能够对读者朋友带来哪怕一丝帮助，作者也会感到欣慰。下面让我们马上开始学习吧！目录 致谢.2 前言.3 简介.4 第 1 章 文本函数.9 1.1 LEN 函数.9 1.2 RIGHT 函数.9 1.3 LEFT 函数.10 1.4 MID 函数.11 1.5 PROPER 函数.11 1.6 LOWER 函数.12 1.7 UPPER 函数.12 1.8 SEARCH 函数.13 1.9 FIND 函数.13 1.10 REPLACE 函数.14 1.11 SUBSTITUTE 函数.15 1.12 REPT 函数.15 1.13 CONCATENATE 函数.16 第 2 章 查找引用函数.17 2.1 COLUMN 函数.17 2.2 VLOOKUP 函数.17 2.3 HLOOKUP 函数.18 2.4 COLUMNS 函数.18 2.5 INDEX 函数.19 2.6 INDIRECT 函数.19 2.7 MATCH 函数.20 2.8 OFFSET 函数.21 2.9 ROWS 函数.21 2.10 ROW 函数.22 第 3 章 逻辑函数.23 3.1 IF 函数.23 3.2 IS 函数.23 3.3 AND 函数.24 3.4 FALSE 函数.25 3.5 OR 函数.25 3.6 TRUE 函数.26 3.7 IFERROR 函数.26 3.8 NOT 函数.27 第 4 章 日期函数.28 4.1 WORKDAY 函数.28 4.2 WORKDAY.INTL 函数.28 4.3 WEEKDAY 函数.29 4.4 DATE 函数.30 4.5 DATEVALUE 函数.30 4.6 DAY 函数.31 4.7 HOUR 函数.32 4.8 MINUTE 函数.32 4.9 NETWORKDAYS.INTL 函数.33 4.10 NETWORKDAYS 函数.34 4.11 SECOND 函数.34 4.12 TODAY 函数.35 4.13 NOW 函数.35 第 5 章 数学函数.37 5.1 INT 函数.37 5.2 SUMIF 函数.37 5.3 SUMPRODUCT 函数.38 5.4 SUMIFS 函数.39 5.5 SUM 函数.39 5.6 RANDBETWEEN 函数.40 5.7 RAND 函数.40 5.8 ROUND 函数.41 5.9 MOD 函数.41 第 6 章 统计函数.43 6.1 AVERAGE 函数.43 6.2 AVERAGEIF 函数.43 6.3 AVERAGEIFS 函数.44 6.4 COUNT 函数.45 6.5 COUNTIF 函数.45 6.6 COUNTIFS 函数.46 6.7 LARGE 函数.46 6.8 SMALL 函数.47 6.9 MAX 函数.48 6.10 MIN 函数.48 6.11 RANK 函数.49 参考文献.50 学习资源.51 关于作者.55 第 1 章 文本函数 本章我们将一起学习 LEN、RIGHT、LEFT、MID、PROPER、LOWER、UPPER、SEARCH、FIND、REPLACE、SUBSTITUTE、REPT 和 CONCATENATE 一共 13 个常用的文本函数。1.1 LEN1.1 LEN 函数 基本语法：=LEN(text)基本用途：计算字符的长度。案例数据：案例展示：1.21.2 RIGHTRIGHT 函数 基本语法：=RIGHT(text，num_charts)基本用途：提取字符中右侧的 N 个数值。案例数据：案例展示：1.31.3 LEFTLEFT 函数 基本语法：=LEFT(text，num_charts)基本用途：提取字符中左侧的 N 个数值。案例数据：案例展示：1.4 MID1.4 MID 函数 基本语法：=MID(text，start_num，num_charts)基本用途：从字符中第 n 位开始，向右侧提取 N 个数值。案例数据：案例展示：1.5 1.5 PROPERPROPER 函数 基本语法：=PROPER(text)基本用途：将单词首字母改为大写。案例数据：案例展示：1.6 LOWER1.6 LOWER 函数 基本语法：=LOWER(text)基本用途：将单词中所有字母改为小写。案例数据：案例展示：1.7 UPPER1.7 UPPER 函数 基本语法：=UPPER(text)基本用途：将单词中所有字母改为大写。案例数据：案例展示：1.8 SEARCH1.8 SEARCH 函数 基本语法：=SEARCH(find_text，within_text，start_num)基本用途：在第二个文本中，从第 N 位开始查找第一个文本的位置。案例数据：案例展示：1.9 FIND1.9 FIND 函数 基本语法：=FIND(find_text，within_text，start_num)基本用途：在第二个文本中，从第 N 位开始查找第一个文本的位置。案例数据：案例展示：1.10 REPLACE1.10 REPLACE 函数 基本语法：=REPLACE(old_text，start_num，num_charts，new_charts)基本用途：从字符中第 n 位开始到第 N 位，用某个字符替换。案例数据：案例展示：1.11 SUBSTITUTE1.11 SUBSTITUTE 函数 基本语法：=SUBSTITUTE(text，old_text，new_text，instance_num)基本用途：将文本中的字符替换为其他字符。案例数据：案例展示：1.12 REPT1.12 REPT 函数 基本语法：=REPT(text，number_times)基本用途：将某一特定文本重复 N 次。案例数据：案例展示：1.13 CONCATENATE1.13 CONCATENATE 函数 基本语法：=CONCATENATE(text1，text2,)基本用途：将多个文本合并成新的文本。案例数据：案例展示：第 2 章 查找引用函数 本章我们将学习 COLUMN、VLOOKUP、HLOOKUP、COLUMNS、INDEX、INDIRECT、MATCH、OFFSET、ROWS、ROW 一共 10 个常用的查找引用函数。2.1 COLUMN2.1 COLUMN 函数 基本语法：=COLUMN(reference)基本用途：返回给定单元格的列数。案例展示：2.2 VLOOKUP2.2 VLOOKUP 函数 基本语法：=VLOOKUP(lookup_value，table_array，col_index_numrange_lookup)基本用途：从某区域中查找特定的值，并返回该值在区域对应的其他列的值。案例数据：案例展示：2.32.3 HLOOKUPHLOOKUP 函数 基本语法：=HLOOKUP(lookup_value，table_array，row_index_num，range_lookup)基本用途：从某区域中查找特定的值，并返回该值在区域对应的其他行的值。案例数据：案例展示：2.4 2.4 COLUMNCOLUMNS S 函数 基本语法：=COLUMNS(array)基本用途：返回特定数组或范围对应的列数。案例展示：2.5 INDEX2.5 INDEX 函数 基本语法：=INDEX(array，row_num，column_num)基本用途：提取数列中的第 n 行第 N 列的值。案例数据：案例展示：2.6 INDIRECT2.6 INDIRECT 函数 基本语法：=INDIRECT(ref_text，a1)基本用途：返回特定的引用值。案例数据：案例展示：2.7 MATCH2.7 MATCH 函数 基本语法：=MATCH(lookup_value，lookup_array，match_type)基本用途：在某一区域内查找某个值，并返回其对应的位置。案例数据：案例展示：2.8 OFFSET2.8 OFFSET 函数 基本语法：=OFFSET(reference,rows,cols,height,width)基本用途：引用某一区域内的数据。案例数据：案例展示：2.9 ROWS2.9 ROWS 函数 基本语法：=ROWS(array)基本用途：返回特定数组或范围对应的行数。案例展示：2.10 ROW2.10 ROW 函数 基本语法：=ROW(reference)基本用途：返回给定单元格的行数。案例展示：第 3 章 逻辑函数 这一章我们将介绍 IF、IS、AND、FALSE、OR、TRUE、IFERROR、NOT 常见的 8 个逻辑函数。3.13.1 IF IF 函数 基本语法：=IF(logical_test,value_if_true,value_if_false)基本用途：如果结果为真，显示为一个值；如果结果为假，显示另一个值。案例数据：案例展示：3.2 IS3.2 IS 函数 基本语法：=ISBLANK();ISERROR();ISNA();ISNUMBER();ISODD();ISEVEN();ISTEXT()基本用途：返回给定单元格的行数。案例数据：案例展示：3.3 AND3.3 AND 函数 基本语法：=AND(logical1,logical2,)基本用途：所有条件为 TRUE 时为 TRUE。案例数据：案例展示：3.4 FALSE3.4 FALSE 函数 基本语法：=FALSE()基本用途：返回逻辑值 FALSE。案例展示：3.5 OR3.5 OR 函数 基本语法：=OR(logical1,logical2,)基本用途：有一个 TRUE 结果即为 TRUE。案例数据：案例展示：3.6 TRUE3.6 TRUE 函数 基本语法：=TRUE()基本用途：返回逻辑值 FALSE。案例展示：3.7 IFERROR3.7 IFERROR 函数 基本语法：=IFERROR(value,value_if_false)基本用途：当结果有错误显示特定值。案例数据：案例展示：3.8 NOT3.8 NOT 函数 基本语法：=NOT(logical)基本用途：取判断结果相反的逻辑值。案例数据：案例展示：第 4 章 日期函数 本章我们将一起学习 WORKDAY、WORKDAY.INTL、WEEKDAY、DATE、DATEVALUE、DAY、HOUR、MINUTE、NETWORKDAYS.INTL、NETWORKDAYS、SECOND、TODAY、NOW 一共 13 个日期和时间相关的函数。4.14.1 WORKDAYWORKDAY 函数 基本语法：=WORKDAY(start_date,days,holidays)基本用途：返回给定工作日之前或之后的某些天数的日期。案例数据：案例展示：4.2 WORKDAY.INTL4.2 WORKDAY.INTL 函数 基本语法：=WORKDAY.INTL(start_date,days,weekend,holidays)基本用途：返回给定工作日之前或之后的某些天数的日期。案例数据：案例展示：4.3 WEEKDAY4.3 WEEKDAY 函数 基本语法：=WEEKDAY(serial_number，return_type)基本用途：返回一个周当中的某一天。案例数据：案例展示：4.4 DATE4.4 DATE 函数 基本语法：=DATE(year,month,day)基本用途：根据数值返回特定日期。案例数据：案例展示：4.5 4.5 DATEVALUEDATEVALUE 函数 基本语法：=DATEVALUE(date_text)基本用途：把日期转换为数值。案例数据：案例展示：注意：前两个公式出现错误的原因是，日期设置须和电脑时钟设置日期类型相同，否则容易出现上述错误。本例演示的电脑时钟日期类型为 YYYY-MM-DD。4.6 DAY4.6 DAY 函数 基本语法：=DAY(serial_number)基本用途：根据日期返回天数。案例数据：案例展示：4.7 HOUR4.7 HOUR 函数 基本语法：=HOUR(serial_number)基本用途：返回特定时间的小时数。案例数据：案例展示：4.8 MINUTE4.8 MINUTE 函数 基本语法：=MINUTE(serial_number)基本用途：返回特定时间的分钟数。案例数据：案例展示：4.9 NETWORKDAYS.INTL4.9 NETWORKDAYS.INTL 函数 基本语法：=NETWORKDAYS.INTL(start_date，end_date，weekend，holidays)基本用途：返回两个日期之间的工作日。案例数据：案例展示：4.10 NETWORKDAYS4.10 NETWORKDAYS 函数 基本语法：=NETWORKDAYS(start_date，end_date，holidays)基本用途：返回两个日期之间的工作日。案例数据：案例展示：4.11 SECOND4.11 SECOND 函数 基本语法：=SECOND(serial_number)基本用途：返回特定时间的秒钟数。案例数据：案例展示：4.12 TODAY4.12 TODAY 函数 基本语法：=TODAY()基本用途：返回特定的日期。案例数据：案例展示：4.13 NOW4.13 NOW 函数 基本语法：=NOW()基本用途：返回当前日期和时间。案例数据：案例展示：第 5 章 数学函数 本章我们将一起来学习 INT、SUMIF、SUMPRODUCT、SUMIFS、SUM、RANDBETWEEN、RAND、ROUND、MOD 常见的 9 个数学函数。5.15.1 INTINT 函数 基本语法：=INT(number)基本用途：返回小于等于给定数值的最大整数。案例数据：案例展示：5.2 SUMIF5.2 SUMIF 函数 基本语法：=SUMIF(range,criteria,sum_range)基本用途：根据某个条件求和。案例数据：案例展示：5.3 5.3 SUMPRODUCTSUMPRODUCT 函数 基本语法：=SUMPRODUCT(array1，array2，)基本用途：根据多个条件求和。案例数据：案例展示：5.4 SUMIFS5.4 SUMIFS 函数 基本语法：=SUMIFS(sum_range,criteria_range1,criteria1,criteria_range2,criteria2,)基本用途：根据多个条件求和。案例数据：案例展示：5.5 SUM5.5 SUM 函数 基本语法：=SUM(number1，number2，)基本用途：计算一系列数值之和。案例数据：案例展示：5.6 RANDBETWEEN5.6 RANDBETWEEN 函数 基本语法：=RANDBETWEEN(bottom,top)基本用途：生成 bottom 到 top 之间的随机整数。案例数据：案例展示：5.7 RAND5.7 RAND 函数 基本语法：=RAND()基本用途：生成 0 到 1 之间的随机小数。案例展示：5.8 ROUND5.8 ROUND 函数 基本语法：=ROUND(number,num_digits)基本用途：保留数值的某一部分。案例展示：5.9 MOD5.9 MOD 函数 基本语法：=MOD(number，divisor)基本用途：计算特定数值被除后的余数。案例数据：案例展示：第 6 章 统计函数 这一章我们将介绍 AVERAGE、AVERAGEIF、AVERAGEIFS、COUNT、COUNTIF、COUNTIFS、LARGE、SMALL、MAX、MIN、RANK 一共 11 个常见的统计类函数。6.16.1 AVERAGEAVERAGE 函数 基本语法：=AVERAGE(number1，number2,)基本用途：计算一系列数值的平均数。案例数据：案例展示：6.2 6.2 AVERAGEIFAVERAGEIF 函数 基本语法：=AVERAGEIF(range,criteria，average_range)基本用途：计算满足某个条件的数值的平均值。案例数据：案例展示：6.3 AVERAGEIFS6.3 AVERAGEIFS 函数 基本语法：=AVERAGEIFS(average_range，criteria_range1，criteria1，criteria_range2，criteria2，)基本用途：计算满足某些条件的数值的平均值。案例数据：案例展示：6.4 COUNT6.4 COUNT 函数 基本语法：=COUNT(value1,value2,)基本用途：计算包含数值的单元格的个数。案例数据：案例展示：6.5 COUNTIF6.5 COUNTIF 函数 基本语法：=COUNTIF(range，criteria)基本用途：计算满足某个条件的单元格的个数。案例数据：案例展示：6.6 COUNTIFS6.6 COUNTIFS 函数 基本语法：=COUNTIFS(criteria_range1，criteria1)，criteria_range2，criteria2,)基本用途：计算满足某些条件的单元格的个数。案例数据：案例展示：6.7 LARGE6.7 LARGE 函数 基本语法：=LARGE(array,k)基本用途：返回某列数值中第 k 大的值。案例数据：案例展示：6.8 SMALL6.8 SMALL 函数 基本语法：=SMALL(array,k)基本用途：返回某列数值中第 k 小的值。案例数据：案例展示：6.9 MAX6.9 MAX 函数 基本语法：=MAX(number1，number2,)基本用途：返回一系列数值中的最大值。案例数据：案例展示：6.10 MIN6.10 MIN 函数 基本语法：=MIN(number1，number2,)基本用途：返回一系列数值中的最小值。案例数据：案例展示：6.11 RANK6.11 RANK 函数 基本语法：=RANK(number，ref，order)基本用途：给某列数据进行排序。案例数据：案例展示：参考文献 1.陈国良、荣胜军、黄朝阳，Excel 2010 函数与公式，北京：电子工业出版社，2010 2.John Walkenbach，Excel 2010 Formulas，Wiley，2010 学习资源 1、网站名称：ExcelHome 网址：http:/ 2、网站名称：52excel 网址：http:/ 3、网站名称：Excel 精英培训 网址：http:/ 4、网站名称：Excel 技巧网 网址：http:/ 5、网站名称：Excelservice 网址：http:/ 学习网 网址：http:/ 办公达人 网址：http:/ 网址：http:/ and Simply 网址：http:/ 网址：http:/chandoo.org/wp/11、网站名称：Business Spreadsheets 网址：http:/www.business- 网址：http:/ 网址：http:/ 网址：http:/www.scoop.it/15、网站名称：Enterprise Dashboard Digest 网址：http:/enterprise- 365 网址：http:/ 网址：https:/ 网址：http:/ Forum 网址：http:/ Dashboard School 网址：https:/ Dashboard Widgets 网址：http:/ 22、网站名称：Excel 学习网 网址：http:/ Templates 网址：http:/ Shoppe 网址：http:/ 网址：https:/ Excel 网址：http:/ Excel Information 网址：http:/andypope.info/28、网站名称：Template Lab 网址：http:/ Trick 网址：http:/ for learners 网址：http:/ 31、网站名称：Excel Unusual 网址：http:/ Daily News 网址：http:/ Spreadsheet Page 网址：http:/ 网址：http:/ Tech Blog 网址：http:/ Solvers 网址：http:/ Excel 网址：http:/ blog 网址：http:/ tech 网址：http:/ Park 网址：http:/ 41、网站名称：ExcelIsFun 网址：https:/ 42、网站名称：ExcelVbaIsFun 网址：https:/