EXCEL2010常用函数应用技巧宝典 (上册)共39页Word打印版

Excel 2010常用函数应用宝典(上册) EXCEL2010 高效办公宝典（上） 常用函数应用指南 本Excel2010常用函数应用宝典分为上中下三册，每册40页左右，均为word精华打印版 上 册 目 录 2单条件求和——SUMIF函数 2符合多个条件的数据求和——SUMIFS函数 2满足多重条件的单元格数目——COUNTIFS函数 3不显示计算结果为0值的项——AND函数、IF函数 4判断单元格是否为空——ISBIANK函数 5名次排位函数——RANK函数 7提取字符串中的某些字符函数——LEFT，RIGHT，MID， 8插入当前时间函数——NOW函数 9计算两个日期间的天数——DATEVALUE函数 9分解时间函数——YEAR函数 10计算员工工龄函数——YEAR函数 11去尾取整函数——INT函数 11向下舍数字函数——ROUNDDOWN函数 12余数函数——MOD函数 12四舍五入函数——ROUND函数 13查找函数 13寻找并参照函数——vlookup函数（只查找第一次出现的） 13VLOOKUP函数的使用（入门级） 15VLOOKUP函数的使用方法（初级篇） 17VLOOKUP函数的使用方法（进阶篇） 20VLOOKUP函数的使用方法(高级篇) 23返回指定行、列或单元格的值——INDEX函数 25返回匹配值的位置函数——MATCH函数（只查找第一次出现的） 26INDEX和MATCH函数组合应用（相当于vlookup函数） 29引用函数——OFFSET 31OFFSET函数的用法及应用实战讲解（上） 34OFFSET函数的经典应用（下） 单条件求和——SUMIF函数 如要统计C列中的数据,要求统计条件是B列中数据为"条件一"。并将结果放在C6单元格中，我们只要在C6单元格中输入公式“=SUMIF（B2：B5，"条件一"，C2：C5）”即完成这一统计。 符合多个条件的数据求和——SUMIFS函数 统计5班中语文名次、数学名次均位于前20名同学的总分之和。在相应单元格内输入公式“=SUMIFS(M2:M80,A2:A80,"5",D2:D80,"<=20",F2:F80,"<=20")”，按下回车键，就一切OK。如图所示。 满足多重条件的单元格数目——COUNTIFS函数 COUNTIFS函数计算某个区域中满足多重条件的单元格数目。 要统计满足多个条件的学生人数，可以使用COUNTIFS函数统计满足多个条件的学生人数。如上图所示，在C8单元格输入公式： =COUNTIFS(B2:B5,">=60",C2:C5,">=60") 按回车键即可统计满足多个条件的学生人数。返回语文和数学成绩都及格的人数。 相关说明： COUNTIFS函数语法：COUNTIFS(range1, criteria1,range2, criteria2…) range1, range2, …：是计算关联条件的 1 至 127 个区域。每个区域中的单元格必须是数字或包含数字的名称、数组或引用。空值和文本值会被忽略。 criteria1, criteria2, … ：是数字、达式、单元格引用或文本形式的 1 至 127 个条件，用于定义要对哪些单元格进行计算。例如，条件可以表示为 32、"32"、">32" 、"apples" 或 B4。 仅当区域中的每一单元格满足为其指定的所有相应条件时才对其进行计算。 可以在条件中使用通配符、问号 (?) 和星号 (*)。问号匹配任意单个字符；星号匹配任意一串字符。如果要查找实际的问号或星号，请在该字符前键入波形符 (~)。 补充： 不显示计算结果为0值的项——AND函数、IF函数 在Excel中，使用公式进行填充运算时，当引用单元格都没有输入数值时，结果单元格会显示0值，如果不显示计算结果为O值的项，可使用AND函数不显示计算结果为O值的项。 如上图所示，当B2和C2单元格同时为空值时，D2单元格公式的计算结果显示为“0”。如果不显示计算结果为O值的项可将D2单元格的公式改为： =IF(AND(B2="",C2=""),"",B2+C2) 如上图所示，当B2和C2单元格同时为空值时，D2单元格不显示0值。其它的公式可使用Excel自动填充进行公式填充。 Excel2007使用AND函数可判断不显示计算结果为O值的项。 相关说明： · AND函数语法：AND(logical1,logical2, ...) · 所有参数的逻辑值为真时，返回 TRUE；只要一个参数的逻辑值为假，即返回 FALSE。 · 参数必须是逻辑值 TRUE 或 FALSE, 或者包含逻辑值的数组或引用。 · 如果数组或引用参数中包含文本或空白单元格，则这些值将被忽略。 · 如果指定的单元格区域内包括非逻辑值，则 AND 将返回错误值 #VALUE!。 判断单元格是否为空——ISBIANK函数 　　第一，isblank函数的用法判断单元格是否为空 　　Excel中isblank函数的用法是判断单元格是否为空。 　　下图所示：A1单元格没有输入任何东西，A2单元格输入：=""。 　　我们在C1单元格输入公式：=IF(ISBLANK(A1),TRUE,FALSE)，公式的意思是用ISBLANK(A1)做为IF条件，如果单元格为空，则返回TRUE，反之返回FALSE。 　　解释：isblank函数的用法是判别该单元格是否为空单元格（真空），而A2单元格中是=""为假空而非真空单元格，所以会返回FALSE。 　　第二，isblank函数的用法实例 　　从帮助文件我们得知isblank函数的用法是检验指定值并返回 TRUE 或 FALSE。例如，如果参数 value 引用的是空单元格，ISBLANK 函数返回逻辑值 TRUE；否则，返回 FALSE。 　　下面截图根据AB列成绩，如果两科成绩都不为空，就求两科的平均分，如果任意一科为空，则返回“缺考”。 　　我们根据题意，在C2单元格写出公式：=IF(OR(ISBLANK(A2),ISBLANK(B2)),"缺考",AVERAGE(A2:B2))，并下拉复制完成。 名次排位函数——RANK函数 rank函数最常用的是求某一个数值在某一区域内的排名。 rank函数语法形式：rank(number,ref,[order]) 函数名后面的参数中 number 为需要求排名的那个数值或者单元格名称（单元格内必须为数字），ref 为排名的参照数值区域，order的为0和1，默认不用输入，得到的就是从大到小的排名，若是想求倒数第几，order的值请使用1。 下面给出几个rank函数的范例： 示例1：正排名   此例中，我们在B2单元格求20这个数值在 A1:A5 区域内的排名情况，我们并没有输入order参数，不输入order参数的情况下，默认order值为0，也就是从高到低排序。此例中20在 A1:A5 区域内的正排序是1，所以显示的结果是1。 示例2：倒排名 此例中，我们在上面示例的情况下，将order值输入为1，发现结果大变，因为order值为1，意思是求倒数的排名，20在A1:A5 区域内的倒数排名就是4。 示例3：求一列数的排名 在实际应用中，我们往往需要求某一列的数值的排名情况，例如，我们求A1到A5单元格内的数据的各自排名情况。我们可以使用单元格引用的方法来排名：=rank(a1,a1:a5) ，此公式就是求a1单元格在a1:a5单元格的排名情况，当我们使用自动填充工具拖拽数据时，发现结果是不对的，仔细研究一下，发现a2单元格的公式居然变成了 =rank(a2,a2:a6) 这超出了我们的预期，我们比较的数据的区域是a1:a5，不能变化，所以，我们需要使用 $ 符号锁定公式中 a1:a2 这段公式，所以，a1单元格的公式就变成了 =rank(a1,a$1:a$5)。 提取字符串中的某些字符函数——LEFT，RIGHT，MID， LEFT，RIGHT，MID，这三个函数都是文本的提取的。前两个的是一样的，只是提取的方向正好相反。LEFT是从左向右取，RIGHT是从右向左取。 　　LEFT（text，num_chars） 　　RIGHT（text，num_chars） 　　第一个参数text，是文本，是你想要在那提取东西的文本，可以是一个字符串，或是一个单元格。第二个参数chars是想要提取的个数。 　　比如：=LEFT（A1，2），也是从A1单元格的文本里，从左边第一位开始，向右提取两位。比如A1的数据是：“找吃的蚂蚁”，得到的结果是：“找吃”。 　　那么：=RIGHT（A1，2），也就是从A1单元格的文本里，从右边第一位开，向左提取两位，这样就得到：“蚂蚁”。 　　当然你也可以这样写，如果你喜欢：=LEFT（"找吃的蚂蚁"，2）。 　　MID这个函数也是从左向右提取的，但不一定是从第一个起，可以从中间开始。先看它的格式吧：MID（text，start_num，num_chars） 　　第一个参数也是text，它的属性与前面两个是一样的。第二个参数，star_num，也就是要提取的开始字符，第三个参数num_chars是要提取的个数。 　　用前面的例子得到“找吃”的结果的：=MID（A1，1，2），而得到“蚂蚁”的公式是：=MID（A1，4，2）。 　　第一个公式的意思是：在A1单元格里提取文本，从文本的第1位开始，向右提取2个。 　　第二个公式的意思是：在A1单元格里提取文本，从文本的第4位开始，向右提取2个。 　　注意，MID函数只能从左向右提取的。 插入当前时间函数——NOW函数 TODAY和NOW都是Excel中与日期时间相关的函数，二者的不同之处是：     TODAY函数仅插入当前的日期，如：     =TODAY()     会在所输入的单元格中显示当前日期，如“2009/5/14”。     而NOW函数同时插入日期和时间，如：     =NOW()     单元格中显示为：“2009/5/14 16:06”     因此，当需要计算某日期距今天有多少整数天时，应使用TODAY函数。例如今天为2009年5月14日，计算上海世博会距今有多少天：     =DATE(2010,5,1)-TODAY()     公式返回352天。 计算两个日期间的天数——DATEVALUE函数 分解时间函数——YEAR函数 计算员工工龄函数——YEAR函数 去尾取整函数——INT函数 注：不可调整小数位数。 向下舍数字函数——ROUNDDOWN函数 ⑴功能 按指定的位数对数值进行舍入。 ⑵格式 ROUNDDOWN(数值或数值单元格，指定的位数) ⑶示例 说明： 函数ROUNDDOWN的第1个参数可以是具体的数值也可以是数值单元格引用。 函数ROUNDDOWN的第2个参数——指定保留的位数，保留小数位用正整数表示，即1，2，3，4……（对应十分位、百分位、千分位、万分位……）；保留整数位用非正整数表示，即0，-1，-2，-3，……（对应个位、十位、百位……）。 函数ROUND与函数ROUNDDOWN的对比： ROUND函数按指定位数把右侧数位上的数字进行四舍五入， ROUNDDOWN函数按指定位数把右侧数位上的数字舍弃为0。 余数函数——MOD函数 四舍五入函数——ROUND函数 查找函数 寻找并参照函数——vlookup函数（只查找第一次出现的） 首先我们介绍下使用的函数 vlookup 的几个参数，vlookup是判断引用数据的函数，它总共有四个参数，依次是： 1、判断的条件 2、跟踪数据的区域 3、返回第几列的数据 4、是否精确匹配   VLOOKUP函数的使用方法（入门级）       VLOOKUP是一个查找函数，给定一个查找的目标，它就能从指定的查找区域中查找返回想要查找到的值。它的基本语法为：       VLOOKUP（查找目标，查找范围，返回值的列数，精确OR模糊查找) 下面以一个实例来介绍一下这四个参数的使用      例1：如下图所示，要求根据表二中的姓名，查找姓名所对应的年龄。      公式：B13 =VLOOKUP(A13,$B$2:$D$8,3,0)      参数说明：        1 查找目标：就是你指定的查找的内容或单元格引用。本例中表二A列的姓名就是查找目标。我们要根据表二的“姓名”在表一中A列进行查找。         公式：B13 =VLOOKUP(A13,$B$2:$D$8,3,0)           2 查找范围（VLOOKUP(A13,$B$2:$D$8,3,0) ）：指定了查找目标，如果没有说从哪里查找，EXCEL肯定会很为难。所以下一步我们就要指定从哪个范围中进行查找。VLOOKUP的这第二个参数可以从一个单元格区域中查找，也可以从一个常量数组或内存数组中查找。本例中要从表一中进行查找，那么范围我们要怎么指定呢？这里也是极易出错的地方。大家一定要注意，给定的第二个参数查找范围要符合以下条件才不会出错：         A 查找目标一定要在该区域的第一列。本例中查找表二的姓名，那么姓名所对应的表一的姓名列，那么表一的姓名列（列）一定要是查找区域的第一列。象本例中，给定的区域要从第二列开始，即$B$2:$D$8，而不能是$A$2:$D$8。因为查找的“姓名”不在$A$2:$D$8区域的第一列。         B 该区域中一定要包含要返回值所在的列，本例中要返回的值是年龄。年龄列（表一的D列）一定要包括在这个范围内，即：$B$2:$D$8，如果写成$B$2:$C$8就是错的。        3 返回值的列数（B13 =VLOOKUP(A13,$B$2:$D$8,3,0)）。这是VLOOKUP第3个参数。它是一个整数值。它怎么得来的呢。它是“返回值”在第二个参数给定的区域中的列数。本例中我们要返回的是“年龄”，它是第二个参数查找范围$B$2:$D$8的第3列。这里一定要注意，列数不是在工作表中的列数（不是第4列），而是在查找范围区域的第几列。如果本例中要是查找姓名所对应的性别，第3个参数的值应该设置为多少呢。是2。因为性别在$B$2:$D$8的第2列中。        4 精确OR模糊查找（VLOOKUP(A13,$B$2:$D$8,3,0)  ），最后一个参数是决定函数精确和模糊查找的关键。精确即完全一样，模糊即包含的意思。第4个参数如果指定值是0或FALSE就表示精确查找，而值为1 或TRUE时则表示模糊。这里兰色提醒大家切记切记，在使用VLOOKUP时千万不要把这个参数给漏掉了，如果缺少这个参数默为值为模糊查找，我们就无法精确查找到结果了。         好了，关于VLOOKUP函数的入门级应用就说到这里，VLOOKUP函数可不只是这么简单的查找，我们讲的还只是1/10的用法。其他的没法在一篇文章中说明。敬请期待“VLOOKUP的使用方法-进阶篇”吧。 VLOOKUP函数的使用方法（初级篇） 上一讲咱们学习了VLOOKUP的基本用法和示例，本讲将介绍VLOOKUP在使用中的一些小技巧。 一、VLOOKUP多行查找时复制公式的问题     VLOOKUP函数的第三个参数是查找返回值所在的列数，如果我们需要查找返回多列时，这个列数值需要一个个的更改，比如返回第2列的，参数设置为2，如果需要返回第3列的，就需要把值改为3。。。如果有十几列会很麻烦的。那么能不能让第3个参数自动变呢？向后复制时自动变为2，3，4，5。。。        在EXCEL中有一个函数COLUMN，它可以返回指定单元格的列数，比如          =COLUMNS（A1） 返回值1          =COLUMNS（B1） 返回值2    而单元格引用复制时会自动发生变化，即A1随公式向右复制时会变成B1，C1，D1。。这样我们用COLUMN函数就可以转换成数字1，2，3，4。。。      例：下例中需要同时查找性别，年龄，身高，体重。      公式：=VLOOKUP($A13,$B$2:$F$8,COLUMN(B1),0)   公式说明：这里就是使用COLUMN（B1）转化成可以自动递增的数字。 二、VLOOKUP查找出现错误值的问题。     1、如何避免出现错误值。      EXCEL2003 在VLOOKUP查找不到，就#N/A的错误值，我们可以利用错误处理函数把错误值转换成0或空值。       即：=IF(ISERROR(VLOOKUP（参数略）),"",VLOOKUP（参数略）      EXCEL2007，EXCEL2010中提供了一个新函数IFERROR，处理起来比EXCEL2003简单多了。      IFERROR（VLOOKUP（），"")      2、VLOOKUP函数查找时出现错误值的几个原因       A、实在是没有所要查找到的值       B、查找的字符串或被查找的字符中含有空格或看不见的空字符，验证方法是用=号对比一下，如果结果是FALSE，就表示两个单元格看上去相同，其实结果不同。       C、参数设置错误。VLOOKUP的最后一个参数没有设置成1或者是没有设置掉。第二个参数数据源区域，查找的值不是区域的第一列，或者需要反回的字段不在区域里，参数设置在入门讲里已注明，请参阅。      D、数值格式不同，如果查找值是文本，被查找的是数字类型，就会查找不到。解决方法是把查找的转换成文本或数值，转换方法如下：      文本转换成数值：*1或--或/1      数值转抱成文本：&""        VLOOKUP函数的初级篇就说到这里了，咱们下一讲将介绍VLOOKUP的模糊查找有、反向查找等。敬请大家关注：VLOOKUP函数的使用方法（进阶篇）。本系列为兰色幻想原创。转载请注明作者和转自“EXCEL精英” VLOOKUP函数的使用方法（进阶篇） 在学习了VLOOKUP的入门和初级篇后，本文将带将大家学习VLOOKUP的进阶篇：VLOOKUP的模糊查找。     一、字符的模糊查找             在A列我们知道如何查找型号为“AAA”的产品所对应的B列价格，即：     =VLOOKUP（C1,A:B,2,0)        如果我们需要查找包含“AAA”的产品名称怎么表示呢？如下图表中所示。      公式=VLOOKUP("*"&A10&"*",A2:B6,2,0)       公式说明：VLOOKUP的第一个参数允许使用通配符“*”来表示包含的意思，把*放在字符的两边，即"*" & 字符 & "*"。    二、数字的区间查找       数字的区间查找即给定多个区间，指定一个数就可以查找出它在哪个区间并返回这个区间所对应的值。     在VLOOKUP入门中我们提示VLOOKUP的第4个参数，如果为0或FALSE是精确查找，如果是1或TRUE或省略则为模糊查找，那么实现区间查找正是第4个参数的模糊查找应用。     首先我们需要了解一下VLOOKUP函数模糊查找的两个重要规则：     1、引用的数字区域一定要从小到大排序。杂乱的数字是无法准确查找到的。如下面A列符合模糊查找的前题，B列则不符合。      2、模糊查找的原理是：给一定个数，它会找到和它最接近，但比它小的那个数。详见下图说明。         最后看一个实例：      例：如下图所示，要求根据上面的提成比率表，在提成表计算表中计算每个销售额的提成比率和提成额。         公式：=VLOOKUP(A11,$A$3:$B$7,2)    公式说明：     1、上述公式省略了VLOOKUP最后一个参数，相当于把第四个参数设置成1或TRUE。这表示VLOOKUP要进行数字的区间查找。     2、图中公式中在查找5000时返回比率表0所对应的比率1%，原因是0和10000与5000最接近，但VLOOKUP只选比查找值小的那一个，所以公式会返回0所对应的比率1%。 VLOOKUP函数的使用方法(高级篇) 前言：前面我们分别学习了VLOOKUP函数的入门、初级和进阶篇。今天我们学习VLOOKUP函数的高级应用部分-VLOOKUP函数的数组应用。  一、VLOOKUP的反向查找。     一般情况下，VLOOKUP函数只能从左向右查找。但如果需要从右向右查找，则需要把区域进行“乾坤大挪移”，把列的位置用数组互换一下。     例1：要求在如下图所示表中的姓名反查工号。           公式：=VLOOKUP(A9,IF({1,0},B2:B5,A2:A5),2,0)     公式剖析：         1、这里其实不是VLOOKUP可以实现从右至右的查找，而是利用IF函数的数组效应把两列换位重新组合后，再按正常的从左至右查找。         2、IF（{1,0},B2:B5,A2:A5)这是本公式中最重要的组成部分。在EXCEL函数中使用数组时（前提时该函数的参数支持数组），返回的结果也会是一个数组。这里1和0不是实际意义上的数字，而是1相关于TRUE，0相当于FALSE，当为1时，它会返回IF的第二个参数（B列），为0时返回第二个参数（A列）。根据数组运算返回数组，所以使用IF后的结果返回一个数组（非单元格区域）：{"张一","A001";"赵三","A002";"杨五","A003";"孙二","A004"}  二、VLOOKUP函数的多条件查找。       VLOOKUP函数需要借用数组才能实现多条件查找。      例2：要求根据部门和姓名查找C列的加班时间。      分析：我们可以延用例1的思路，我们的努力方向不是让VLOOKUP本身实现多条件查找，而是想办法重构一个数组。多个条件我们可以用&连接在一起，同样两列我们也可以连接成一列数据，然后用IF函数进行组合。     公式：{=VLOOKUP(A9&B9,IF({1,0},A2:A5&B2:B5,C2:C5),2,0)}     公式剖析：        1、A9&B9 把两个条件连接在一起。把他们做为一个整体进行查找。        2、A2:A5&B2:B5，和条件连接相对应，把部分和姓名列也连接在一起，作为一个待查找的整体。        3、IF({1,0},A2:A5&B2:B5,C2:C5) 用IF（{1，0}把连接后的两列与C列数据合并成一个两列的内存数组。按F9后可以查看的结果为：        {"销售张一",1;"销售赵三",5;"人事杨五",3;"销售赵三",6}        4、完成了数组的重构后，接下来就是VLOOKUP的基本查找功能了，另外公式中含有多个数据与多个数据运算(A2:A5&B2:B5),，所以必须以数组形式输入，即按ctrl+shift后按ENTER结束输入。      三、VLOOKUP函数的批量查找。      VLOOKUP一般情况下只能查找一个，那么多项该怎么查找呢？      例3 要求把如图表中所有张一的消费金额全列出来      分析：经过前面的学习，我们也有这样一个思路，我们在实现复杂的查找时，努力的方向是怎么重构一个查找内容和查找的区域。要想实现多项查找，我们可以对查找的内容进行编号，第一个出现的是后面连接1，第二个出现的连接2。。。      公式：{=VLOOKUP(B$9&ROW(A1),IF({1,0},$B$2:$B$6&COUNTIF(INDIRECT("b2:b"&ROW($2:$6)),B$9),$C$2:$C$6),2,)}      公式剖析：         1、B$9&ROW(A1) 连接序号，公式向下复制时会变成B$9连接1，2，3         2、给所有的张一进行编号。要想生成编号，就需要生成一个不断扩充的区域（INDIRECT("b2:b"&ROW($2:$6))，然后在这个逐行扩充的区域内统计“张一”的个数，在连接上$B$2:$B$6后就可以对所有的张一进行编号了。        3、IF（{1，0}把编号后的B列和C组重构成一个两列数组      通过以上的讲解，我们需要知道，VLOOKUP函数的基本用法是固定的，要实现高级查找，就需要借助其他函数来重构查找内容和查找数组。 返回指定行、列或单元格的值——INDEX函数 INDEX(数据区域(可以有多个数据区域),返回第几行数据,返回第几列数据,查找第几个区域的数据) 　　INDEX函数的功能就是返回指定单元格区域或数组常量。如果同时使用参数行号和列号，函数INDEX返回 行号和列号交叉处的单元格中的值。 　　INDEX函数语法：INDEX(单元格区域，行号，列号) 　　可以通过下图来认识INDEX函数的用法： 　　=INDEX(A1:C6,2,3)，意思就是返回A1:C6中行号是2 列号是3 ,即第二行与第三列的交叉处，也就是C2单元格的值，为84。 　　二、实例介绍 　　看下表；下表中，有两个数据区域，第一个区域为A2：C6；第二个区域为A8：C10； 　　现在，我们将要使用INDEX函数，求取这两个数据区域中的某单元格的数据。 　　下面，我们要获取C9单元格的值，即29，如何通过这个函数来获取数据呢？ 　　还是先从函数语法来分析： 　　INDEX(数据区域,返回第几行数据,返回第几列数据,查找第几个区域的数据) 　　通过套用以上的函数语法，我们应该使用如下的函数公式： 　　=INDEX((A2:C6,A8:C10,A12:C13),2,3,2) 　　如上图吧，输入公式之后，按下回车键即可得结果。 　　三、函数公式分析 　　=INDEX((A2:C6,A8:C10),2,3,2)，其中： 　　(A2:C6,A8:C10)代表函数要查找的数据范围为两个区域，中间使用，隔开，第一个区域为A2:C6；第二个区域为A8:C10；(如果有第三个、第四个区域，同样可以填入函数中)。 　　=INDEX((A2:C6,A8:C10),2,3,2)，其中的2代表要查找的数据范围为第二个数据区域，即A8:C10；而不是第一个区域A2:C6； 　　=INDEX((A2:C6,A8:C10),2,3,2)，其中的3代表返回已选择的数据区域的第三列数据； 　　=INDEX((A2:C6,A8:C10),2,3,2)，其中的2代表返回已选择的数据区域的第二行数据； 　　当然，该函数也可以只使用一个数据区域。比如，同样，要返回C9单元格的值，我们可以使用如此公式代码： 　　=INDEX((A8:C10),2,3) 　　此时，由于只有一个数据区域，因此，数据区域的参数可省略不填写。 　　四：知识扩展 　　该函数的使用极其灵活，因为任何一个参数都可变化。 　　数据范围：可以只有一个数据范围，也可以有多个数据范围(多个使用逗号隔开)； 　　要返回的数据的行和列号，自行设定。 　　要返回哪个数据范围的数据，就填写序号，比如返回第一个数据范围的数据，就填写1，其它依此类推。 　　当然了，如果您使用该函数发生错误时，只能说明您指定的参数存在错误。 返回匹配值的位置函数——MATCH函数（只查找第一次出现的） 　　MATCH函数也是一个查找函数。MATCH 函数会返回匹配值的位置而不是匹配值本身。在使用时，MATCH函数在众多的数字中只查找第一次出现的，后来出现的它返回的也是第一次出现的位置。 　　MATCH函数语法：MATCH(查找值，查找区域，查找模式) 　　可以通过下图来认识MATCH函数的用法： 　　=MATCH(41,B2:B5,0)，得到结果为4，返回数据区域 B2:B5 中 41 的位置。 　　=MATCH(39,B2:B5,1)，得到结果为2，由于此处无正确匹配，所以返回数据区域 B2:B5 中(38) 的位置。注：匹配的查找值，MATCH 函数会查找小于或等于(39)的最大值。 =MATCH(40,B2:B5,-1)，得到结果为#N/A，由于数据区域 B2:B5 不是按降序排列，所以返回错误值。 如果 match_type 为 1，函数 MATCH 查找小于或等于 lookup_value 的最大数值。Lookup_array 必须按升序排列：...、-2、-1、0、1、2、...、A-Z、FALSE、TRUE。 如果 match_type 为 0，函数 MATCH 查找等于 lookup_value 的第一个数值。Lookup_array 可以按任何顺序排列。 如果 match_type 为 -1，函数 MATCH 查找大于或等于 lookup_value 的最小数值。Lookup_array 必须按降序排列：TRUE、FALSE、Z-A、...、2、1、0、-1、-2、...，等等。 　INDEX和MATCH函数组合应用（相当于vlookup函数） 　　下图工作表所示的是一个产品的型号和规格的价格明细表。通过这个表的数据，进行一些对应的查询操作。 　　第一，单击B5单元格下拉按钮，选择型号，然后在B6单元格完成型号所在行号的查询。如下图所示： 　　随意选择一个型号，比如A0110，然后在B6单元格输入公式：=MATCH($B$5,$D$4:$D$12,0)，得到结果1。 　　公式解释：用MATCH函数查找B5单元格这个型号在D4:D12区域中对应的位置。其中的0参数可以省略不写。MATCH函数中0代表精确查找，1是模糊查找。 　　第二，单击B9单元格下拉按钮，选择规格，然后在B10单元格完成规格所在列号的查询。如下图所示： 　　随意选择一个规格，比如101，然后在B10单元格输入公式：=MATCH(B9,E3:G3,0)，得到结果1。 　　第三，查询B6和B10单元格所对应的价格。 　　价格的查询，可以使用index函数完成，输入公式：=INDEX(E4:G12,B6,B10)可以得到结果为78。嵌套上面的match函数，可以将公式改为：=INDEX(E4:G12,MATCH(B5,D4:D12,0),MATCH(B9,E3:G3,0))。大家可以变化C3中的型号来看看结果是否正确。 　　通过下面工作表的源数据，利用index函数实现行列汇总查询。 　　INDEX函数的帮助文件知道，如果将 row_num 或 column_num 设置为 0，函数 INDEX 则分别返回整个列或行的数组数值。通过用法说明，来实现上图的行列汇总。 　　首先，单击C3单元格下拉按钮，选择数据，比如选择A0111，然后在C4单元格进行C3单元格对应的行号查找，公式为：=MATCH(C3,E2:E10,)，得到结果为2，说明A0111在E2:E10区域的第二行。 　　然后对行号所对应的价格进行汇总求和。在C5单元格输入公式：=SUM(INDEX(F2:H10,MATCH(C3,E2:E10,),)) 　　如果选中公式中的INDEX(F2:H10,MATCH(C3,E2:E10,),)部分，按下F9键，执行结果是{80,97,84}，可以看到就是对第二行的数据进行求和。 　　同样的方法，可以进行列汇总，在C9单元格输入公式：=SUM(INDEX(F2:H10,,MATCH(C7,F1:H1,)))。 　　说明：查找行号和查找列号，只是过渡一下，帮助新手朋友加深对match函数的理解和使用，对熟悉的朋友，可以直接在行汇总一步完成公式的输入。 　　根据下面的工作表，进行区域汇总求和。 　　首先，分别在开始行号、结束行号、结束列号、结束列号选定需要求和的区域，比如A0110、A0111、201、301。此题可以套用下图的格式进行求和。 　　因此，在C20单元格输入求和公式：=SUM(INDEX(F16:H24,MATCH(C16,E16:E24,),MATCH(C17,E16:E24,)):INDEX(F16:H24,MATCH(C18,F15:H15,),MATCH(C19,F15:H15,))) 引用函数——OFFSET OFFSET(基点单元格，移动的行数，移动的列数，所要引用的高度，所要引用的宽度） 例 =OFFSET(A3,2,3,1,1) A3是基点单元格， 2是正数，为向下移动2 行，负为向上移动2 行 3是正数，为向右移动3列，负为向左移动3列 1是引用 1 个单元格的高度 1 是引用 1 个单元格的宽度 它的结果是引用了D5中数值。 作用：以指定的引用为参照系，通过给定偏移量得到新的引用。返回的引用可以为一个单元格或单元格区域。并可以指定返回的行数或列数。 公式 说明（结果） =OFFSET(C3,2,3,1,1) 显示单元格 F5 中的值 (0) =SUM(OFFSET(C3:E5,-1,0,3,3)) 对数据区域 C2:E4 求和 (0) =OFFSET(C3:E5,0,-3,3,3) 返回错误值 #REF!，因为引用区域不在工作表中 也可以运用counta函数看看有多少非空单元格=counta(OFFSET(C3:E5,-1,0,3,3)) OFFSET函数的用法及应用实战讲解（上） 　　首先，认识一下OFFSET函数。 　　从下图说明来认识一下excel中OFFSET函数的用法。 　　在C7单元格，输入公式：=SUM(OFFSET(C2,1,2,3,1))，得到结果为18。这个公式是什么意思呢?就是计算C2单元格靠下1 行并靠右2 列的3 行 1 列的区域的和。 　　可以在公式编辑栏，选中OFFSET(C2,1,2,3,1) 部分，按F9键抹黑，得到运算结果为：{3;8;7}，此时公式变为：=SUM({3;8;7})。从上图可以得知，就是利用OFFSET函数来得到一个新的区域，然后使用SUM函数求出这个新区域的和。 　　下面，介绍OFFSET函数的用法。 　　Offset函数主要应用在单元格区域的定位和统计方面，一般做数据透视表定义名称都需要用到Offset函数。Offset函数属于查找与引用类的函数。 　　OFFSET函数以指定的引用为参照系，通过给定偏移量得到新的引用。返回的引用可以为一个单元格或单元格区域，并可以指定返回的行数或列数。 　　OFFSET函数的语法是：OFFSET(reference,rows,cols,height,width)，按照中文的说法即是：OFFSET(引用区域,行数,列数,[高度],[宽度]) 　　其中的参数意义如下： 　　Reference：作为偏移量参照系的引用区域。Reference必须为对单元格或相连单元格区域的引用;否则，函数 OFFSET 返回错误值 #VALUE!。 　　Rows：相对于偏移量参照系的左上角单元格，上(下)偏移的行数。如果使用 5 作为参数 Rows，则说明目标引用区域的左上角单元格比 reference 低 5 行。行数可为正数(代表在起始引用的下方)或负数(代表在起始引用的上方)。 　　Cols：相对于偏移量参照系的左上角单元格，左(右)偏移的列数。如果使用 5 作为参数 Cols，则说明目标引用区域的左上角的单元格比 reference 靠右 5 列。列数可为正数(代表在起始引用的右边)或负数(代表在起始引用的左边)。 　　Height：高度，即所要返回的引用区域的行数。Height 必须为正数。 　　Width：宽度，即所要返回的引用区域的列数。Width 必须为正数。 　　学习使用OFFSET函数需要注意以下几点： 　　第一，如果行数和列数偏移量超出工作表边缘，函数 OFFSET 返回错误值 #REF!。 　　第二，如果省略 height 或 width，则假设其高度或宽度与 reference 相同。 　　第三，函数 OFFSET 实际上并不移动任何单元格或更改选定区域，它只是返回一个引用。 　　第四，函数 OFFSET 可用于任何需要将引用作为参数的函数。 　　下面，结合几个实例学习OFFSET函数的应用。 　　OFFSET函数通常与其它函数来嵌套使用。下图所示的区域为实例的源数据区域。 　　OFFSET函数应用第一题：如下图所示，计算上图B列姓名下面所有单元格的数量。 　　在F3单元格输入函数：=OFFSET(B2,0,0,COUNTA(B:B)-1)，全选公式，按F9键，得到：={"张三";"王五";"李四";"闻一";"张二";"江八";"张六";"赵六";"钱子";"赵一";"孙六";"周末"}，我们可以数一下个数，得到12个。 　　提示：COUNTA 函数就是返回参数列表中非空值的单元格个数。 　　接下来，在F5单元格输入公式：=COUNTA(OFFSET(B2,0,0,COUNTA(B:B)-1))，得多结果为12。 　　OFFSET函数应用第二题：如下图所示，计算最后的平均成绩。分三种情况：第一，求最后一位、最后三位、最后五位的平均成绩。 　　单击F8单元格，可以看到有一个向下的箭头，单击箭头可以看到有1、3、5三个选项。 　　在F9单元格输入公式：=AVERAGE(OFFSET(C1,COUNTA(C:C)-1,,-F8))，就可以得到F8单元格所显示的平均成绩。 　　公式分析：首先使用OFFSET函数来计算出行数的值，然后使用AVERAGE来除以OFFSET部分得到的值，就得到了最后几个的平均成绩。 　　公式中，其中COUNTA(C:C)部分是统计C列的非空单元格个数为13，从而可以得到COUNTA(C:C)-1的值为12，即从C1单元格向下偏移12行。 　　另外-F8的意思是：减去F8单元格中的值。 　　当改变F8单元格的值，就可以得到其余行数的平均成绩。 　　OFFSET函数应用第三题：如下图所示，要求统计出班级=H3,成绩>=I3的人数。 　　H3和I3单元格的值是条件。单击这两个单元格，右下角有一个向下的三角形，单击此按钮，可以显示选择其余的条件值。比如上图所示，就是统计出班级为3版，成绩在>=70分的人数有几人。 　　本题的统计结果，可以在I5单元格输入公式：=COUNTIF(OFFSET(C1,MATCH(H3,A2:A13,0),,COUNTIF(A2:A13,H3)),">="&I3)，得到结果。 　　公式分析：OFFSET的第二参数使用MATCH来精确查找位置。偏移的行数就由MATCH(H3,A2:A13,0)来控制，高度由COUNTIF(A2:A13,H3)来控制。然后把OFFSET函数的值用COUNTIF来计算符合条件的有多少个。 OFFSET函数的经典应用（下） 在第4期讲座中，简单老师从零讲解了OFFSET函数的使用，本文最主要通过一个实例来讲解OFFSET函数在定义名称方面的运用。 下图所示的工作表命名为：名称实例，其中A1：C13区域为源数据，本讲座要完成的作业是通过源数据，使用OFFSET函数定义一个叫“AA”的名称，然后完成下图1、2、3个班级的实考人数、总分、最高分、优秀数、平均分的统计。 本题最核心的一个问题就是定义AA名称。 单击菜单“插入”——“名称”——“定义”，在“名称”框中输入名称“AA”，然后在下面的引用位置输入：=OFFSET(名称实例!$A$1,MATCH(名称实例!$D17,名称实例!$A$2:$A$13,),2,COUNTIF(名称实例!$A$2:$A$13,名称实例!$D17)) 引用位置输入公式的含义，解释如下。把上面这个公式分成两部分来查看，就很容易了。 第一部分：MATCH(名称实例!$D17,名称实例!$A$2:$A$13,)，这个值是OFFSET的第二个参数，使用MATCH来精确查找位置。代表偏移的行数就由MATCH部分的值来控制。这个公式的意思就是查找D17单元格的值在A2：A13区域中的位置。 MATCH函数也是一个查找函数。MATCH 函数会返回中匹配值的位置而不是匹配值本身。在使用时，输入单值它就返回单值，输入多值就返回多值。MATCH函数在众多的数字中只查找第一次出现的，后来出现的它返回的也是第一次出现的位置。MATCH函数一般都和别的函数嵌套使用。 第二部分：COUNTIF(名称实例!$A$2:$A$13,名称实例!$D17)，这个值是OFFSET的第四个参数，高度就由COUNTIF的值来控制。 定义完名称之后，在E17单元格输入公式：=COUNTA(AA)，然后下拉，就可以求出各个班级的实考人数。 在F17单元格输入公式：=SUM(AA)，然后下拉，就可以求出各个班级的总分。 在G17单元格输入公式：=MAX(AA)，然后下拉，就可以求出各个班级的最分。 在J17单元格套用COUNTIF函数的公式：countif(区域，条件)，输入公式：=COUNTIF(AA,">=85")，然后下拉，就可以求出各个班级的优秀人数。在这个公式中，我们假定优秀人数的分数为大于等于85分。 在K17单元格输入公式：=AVERAGE(AA)，然后下拉，就可以求出各个班级的平均分。 本Excel2010常用函数应用宝典分为上中下三册，每册40页左右，均为word精华打印版 36 Sheet1 大钢牙甜甜 个 1 w 呃 2 e 钢 3 1 大 4 大钢牙甜甜 5 w 飞 6 区域中“大钢牙甜甜”出现次数： ，=COUNTIF(A1:C6，"大钢牙甜甜") 返回值为： 2 Sheet1 12.351 325.525 ①保留2位小数——舍去千分位及以后的小数位保留到百分位。 =ROUNDDOWN(A1,2) 12.35 =ROUNDDOWN(A2,2) 325.52 ②舍去小数位保留整数——舍去十分位及以后的小数位保留整数部分。 =ROUNDDOWN(A1,0) 12 =ROUNDDOWN(A2,0) 325 ③整数保留到十位——整数部分舍去个位上大于0的数字（用0代替），保留十位及以前的高位数字。 =ROUNDDOWN(A1,-1) 10 =ROUNDDOWN(A2,-1) 320