offset,match,lookup函數應用

1、OffsetOffset函函数数 以指定的引用为参照系，通过给定偏移量得到新的引用。返回的引用可以为一个单元格或单元格区域。并可以指定返回的行数或列数。语语法法OFFSET(reference,rows,cols,height,width)参参数数Reference 作为偏移量参照系的引用区域。Reference 必须为对单元格或相连单元格区域的引用；否则，函数 OFFSET 返回错误值 #VALUE!。Rows 相对于偏移量参照系的左上角单元格，上（下）偏移的行数。如果使用 5 作为参数 Rows，则说明目标引用区域的左上角单元格比 reference 低 5 行。行数可为正数（代表在起始引

2、用的下方）或负数（代表在起始引用的上方）。Cols 相对于偏移量参照系的左上角单元格，左（右）偏移的列数。如果使用 5 作为参数 Cols，则说明目标引用区域的左上角的单元格比 reference 靠右 5 列。列数可为正数（代表在起始引用的右边）或负数（代表在起始引用的左边）。Height 高度，即所要返回的引用区域的行数。Height 必须为正数。Width 宽度，即所要返回的引用区域的列数。Width 必须为正数。说说明明如果行数和列数偏移量超出工作表边缘，函数OFFSET返回错误值#REF!。如果省略height或width，则假设其高度或宽度与reference相同。函数OFFSET

3、实际上并不移动任何单元格或更改选定区域，它只是返回一个引用。函数OFFSET可用于任何需要 将引用作为参数的函数。例如，公式 SUM(OFFSET(C2,1,2,3,1) 将计算比单元格 C2 靠下 1 行并靠右 2 列的 3 行 1 列的区域的总值。示示例例ABCD122132445465857556969874公公式式说说明明（结结果果）98显示单元格 C29中的值 (98)519对数据区域 A26:C29 求和 (519)#REF!返回错误值 #REF!，因为引用区域不在工作表中 以指定的引用为参照系，通过给定偏移量得到新的引用。返回的引用可以为一个单元格或单元格区域。 作为偏移量参照系

4、的引用区域。Reference 必须为对单元格或相连单元格区域的引用； 相对于偏移量参照系的左上角单元格，上（下）偏移的行数。如果使用 5 作为参数 Rows，则说明目标引用区域的左上角单元格比 reference 低 5 行。行数可为正数（代表在起始引用 相对于偏移量参照系的左上角单元格，左（右）偏移的列数。如果使用 5 作为参数 Cols，则说明目标引用区域的左上角的单元格比 reference 靠右 5 列。列数可为正数（代表在起始函数OFFSET实际上并不移动任何单元格或更改选定区域，它只是返回一个引用。函数OFFSET可用于任何需要 将引用作为参数的函数。例如，公式 SUM(OFFS

5、ET(C2,1,2,3,1) 将计算比单元格 C2 靠下 1 行并靠右 2 列的 说说明明（结结果果）显示单元格 C29中的值 (98)对数据区域 A26:C29 求和 (519)返回错误值 #REF!，因为引用区域不在工作表中MatchMatch函函数数 返回在指定方式下与指定数值匹配的数组（数组：用于建立可生成多个结果或可对在行和列中排列的一组参数进行运算的单个公式。数组区域共用一个公式；数组常量是用作参数的一组常量。）中元素的相应位置。如果需要找出匹配元素的位置而不是匹配元素本身，则应该使用 MATCH 函数而不是 LOOKUP函数。语语法法MATCH(lookup_value,look

6、up_array,match_type)参参数数Lookup_value 为需要在数据表中查找的数值。Lookup_value为需要在Look_array中查找的数值。例如，如果要在电话簿中查找某人的电话号码，则应该将姓名作为查找值，但实际上需要的是电话号码。Lookup_value可以为数值（数字、文本或逻辑值）或对数字、文本或逻辑值的单元格引用。Lookup_array 可能包含所要查找的数值的连续单元格区域。Lookup_array 应为数组或数组引用。Match_type 为数字 -1、0 或 1。Match-type 指明 Microsoft Excel 如何在 lookup_arr

7、ay 中查找 lookup_value。如果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、.，等等。如果省略match_typ

8、e，则假设为1。说说明明函数MATCH返回lookup_array中目标值的位置，而不是数值本身。例如，MATCH(b,a,b,c,0)返回2，即“b”在数组a,b,c中的相应位置。查找文本值时，函数MATCH不区分大小写字母。如果函数MATCH查找不成功，则返回错误值#N/A。如果match_type为0且lookup_value为文本，lookup_value可以包含通配符、星号(*)和问号(?)。星号可以匹配任何字符序列；问号可以匹配单个字符。示示例例ProductCountBananas25Oranges38Apples40Pears41公公式式说说明明（结结果果）2由于此处无正确的匹

9、配，所以返回数据区域B32:B35中最接近的下一个值(38)的位置。(2)4数据区域 B2:B5 中 41 的位置。(4)#N/A由于数据区域B32:B35不是按降序排列，所以返回错误值。(#N/A) 返回在指定方式下与指定数值匹配的数组（数组：用于建立可生成多个结果或可对在行和列中排列的一组参数进行运算的单个公式。数组区域共用一个公式；数组常量是用作参数的一组常量。）中元素的相应位置。如果需要找出匹配元素的位置而不是匹配元素本身，则应该使用 MATCH 函数而不是 LOOKUPLookup_value为需要在Look_array中查找的数值。例如，如果要在电话簿中查找某人的电话号码，Look

10、up_value可以为数值（数字、文本或逻辑值）或对数字、文本或逻辑值的单元格引用。 可能包含所要查找的数值的连续单元格区域。Lookup_array 应为数组或数组引用。 为数字 -1、0 或 1。Match-type 指明 Microsoft Excel 如何在 lookup_array 中查找 lookup_value。如果match_type为1，函数MATCH查找小于或等于lookup_value的最大数值。Lookup_array如果match_type为0，函数MATCH查找等于lookup_value的第一个数值。Lookup_array可以按如果match_type为-1，函

11、数MATCH查找大于或等于lookup_value的最小数值。Lookup_array函数MATCH返回lookup_array中目标值的位置，而不是数值本身。例如，MATCH(b,a,b,c,0)如果match_type为0且lookup_value为文本，lookup_value可以包含通配符、星号(*)和问号(?)。说说明明（结结果果）由于此处无正确的匹配，所以返回数据区域B32:B35中最接近的下一个值(38)的位置。(2)数据区域 B2:B5 中 41 的位置。(4)由于数据区域B32:B35不是按降序排列，所以返回错误值。(#N/A)LookupLookup函函数数向量是只含一行或

12、一列的区域。LOOKUP的向量形式在单行区域或单列区域（称为“向量”）中查找值，然后返回第二个单行区域或单列区域中相同位置的值。当要指定的区域包含要匹配的值时，请使用 LOOKUP 函数的这种形式。语语法法LOOKUP(lookup_value,lookup_vector,result_vector)参参数数Lookup_value LOOKUP 在第一个向量中搜索的值。Lookup_value 可以是数字、文本、逻辑值、名称或对值的引用。Lookup_vector 只包含一行或一列的区域。lookup_vector 中的值可以是文本、数字或逻辑值。要要点点lookup_vector中的值必须

13、以升序顺序放置：.,-2,-1,0,1,2,.；A-Z；FALSE,TRUE。否则，LOOKUP可能无法提供正确的值。大写文本和小写文本是等同的。Result_vector 只包含一行或一列的区域。它必须与 lookup_vector 大小相同。说说明明如果LOOKUP找不到lookup_value，则它与lookup_vector中小于或等于lookup_value的最大值匹配。如果lookup_value小于lookup_vector中的最小值，则LOOKUP会提供#N/A错误值。示示例例频率颜色4.14红色4.19橙色5.17黄色5.77绿色6.39蓝色公公式式说说明明（结结果果）橙色在

14、列 A 中查找 4.19，然后返回列 B 中同一行内的值（橙色）橙色在列 A 中查找 5.00，与接近它的最小值 (4.19) 匹配，然后返回列 B 中同一行内的值（橙色）蓝色在列 A 中查找 7.66，与接近它的最小值 (6.39) 匹配，然后返回列 B 中同一行内的值（蓝色）#N/A在列A中查找0，并返回错误，因为0小于lookup_vectorA19:A23中的最小值(#N/A)向量是只含一行或一列的区域。LOOKUP的向量形式在单行区域或单列区域（称为“向量”）中查找值，然后返回第二个单行区域或单列区域中相同位置的值。当要指定的区域包含要匹配的值时，请使用 LOOKUP 函数的这LOO

15、KUP 在第一个向量中搜索的值。Lookup_value 可以是数字、文本、逻辑值、名称或对值的引用。只包含一行或一列的区域。lookup_vector 中的值可以是文本、数字或逻辑值。要要点点lookup_vector中的值必须以升序顺序放置：.,-2,-1,0,1,2,.；A-Z；FALSE,TRUE。否则，如果LOOKUP找不到lookup_value，则它与lookup_vector中小于或等于lookup_value的最大值匹配。如果lookup_value小于lookup_vector中的最小值，则LOOKUP会提供#N/A错误值。说说明明（结结果果）在列 A 中查找 4.19，然

16、后返回列 B 中同一行内的值（橙色）在列 A 中查找 5.00，与接近它的最小值 (4.19) 匹配，然后返回列 B 中同一行内的值（橙色）在列 A 中查找 7.66，与接近它的最小值 (6.39) 匹配，然后返回列 B 中同一行内的值（蓝色）在列A中查找0，并返回错误，因为0小于lookup_vectorA19:A23中的最小值(#N/A)技巧1 在单元格创建下拉列表 有许多读者在工作中第一次见到下图所示的工作表时，会惊奇于单元格中是如何能够出项下拉列表的。 在上图所示的工作表中，如果选定B2单元格，就会在单元格右侧出现一个倒三角的箭头，单击箭头，会出现个下拉列表，单击其中的任何一项，该项的

17、内容就别输入到B2单元格中。 要实现这样的效果并不困难，利用EXCEL的数据有效性对话框就能完成.下面来介绍刚才的例子是如何制作的.第一步 在一个连续的单元格区域输入列表中的项目,如C1:C7,如图所示第二步 单击菜单数据-有效性,在数据有效性对话框的设置选项卡中,在允许下拉列表中选择序列项.第三步 在来源框中指定C1:C7第四步 勾选忽略空值与提供下拉箭头复选框,如图所示第五步 单击确定按钮,关闭数据有效性对话框. 这样,就能实现第一张图所示的效果了. 如果列表的内容较少,或者不方便在工作表中输入列表项目,也可以省略上述的第一步,然后将第三步的操作改为:直接在来源框中输入列表内容,项目之间以

18、半角的逗号分隔.如图所示 在一般情况下,数据的有效性中的序列来源,只能引用当前工作表中的单元格区域,如果希望能够引用其他工作表中的单元格区域,则必须先为单元格区域定义名称,然后在来源框中输入名称.例如,将另一张工作表中的D1:D10名称定义为SOFT,然后在数据有效性的来源框中输入=SOFT. 有许多读者在工作中第一次见到下图所示的工作表时，会惊奇于单元格中是如何能够出项下拉列表的。 在上图所示的工作表中，如果选定B2单元格，就会在单元格右侧出现一个倒三角的箭头，单击箭头，会出 要实现这样的效果并不困难，利用EXCEL的数据有效性对话框就能完成.下面来介绍刚才的例子是如何制第二步 单击菜单数据

19、-有效性,在数据有效性对话框的设置选项卡中,在允许下拉列表中选择序 如果列表的内容较少,或者不方便在工作表中输入列表项目,也可以省略上述的第一步,然后将第三步的操作 在一般情况下,数据的有效性中的序列来源,只能引用当前工作表中的单元格区域,如果希望能够引用其他工作表中的单元格区域,则必须先为单元格区域定义名称,然后在来源框中输入名称.例如,将另一张工作表技巧2 另类的批注 当用户需要对表格中的项目进行特别说明时,常常会使用EXCEL的批注功能.使用批注的方法是先选定目标单元格,然后单击菜单插入-批注,在新出现的文本框中输入批注内容后,单击文本框以后的工作表任何区即完成. 插入批注完成后,在刚才

20、的单元格的右上角会出现一个红三角,表示单元格包含批注,当把光标悬停在此单元格上方时,批注内容就会显示出来,如图所示. EXCEL的批注有两个不足之处:一是当单元格的行高进行设置(特别是隐藏)后,批注框的大小尺寸也会因此受到影响;二是默认情况下，必须把光标悬停在单元格的上方批注内容才会显示出来，否则即使当单元格处于活动状态时，它也不会显示。 利用数据有效性功能，我们可以实现另类的批注效果，克服以上两个不足。第一步：选定单元格，如C1。第二步：单击菜单数据-有效性,在数据有效性对话框的输入信息选项卡中,勾选选定单元格时显示输入信息复选框,并在标题和输入信息文本框中输入相应内容.如图所示第三步:单击

21、确定按钮,关闭数据有效性对话框. 设置完毕后,当此单元格处于活动状态时,就会显示刚才输入的内容,效果如图所示 用鼠标左键按住上图中的提示框,可以将它移动到工作表中的任何位置.如果对多个单元格连续设置,并将提示框的位置固定在同一个地方,则会让工作表显得非常专业. 当用户需要对表格中的项目进行特别说明时,常常会使用EXCEL的批注功能.使用批注的方法是先选定目标单元格,然后单击菜单插入-批注,在新出现的文本框中输入批注内容后,单击文本框以后的工作表任何区 插入批注完成后,在刚才的单元格的右上角会出现一个红三角,表示单元格包含批注,当把光标悬停在此单 EXCEL的批注有两个不足之处:一是当单元格的行

22、高进行设置(特别是隐藏)后,批注框的大小尺寸也会因此受到影响;二是默认情况下，必须把光标悬停在单元格的上方批注内容才会显示出来，否则即使当单元格处于第二步：单击菜单数据-有效性,在数据有效性对话框的输入信息选项卡中,勾选选定单元格时显示 用鼠标左键按住上图中的提示框,可以将它移动到工作表中的任何位置.如果对多个单元格连续设置,并将技巧三 快速切换有效性的来源 在通常情况下,当用户使用数据有效性时,只能处理一组数据来源,而在某些场合中,数据来源不止一组,是否有某种方法可以为有效性快速切换数据来源呢?例如在如图所示的工作表中,分别有LIST.1,LIST.2,LIST.3三组数据源,现在需要在A2

23、单元格中设置有效性,要求能够灵活地选择其中任何一组作为数据源第一步:选定单元格D1:D10,单击菜单插入-名称-指定,在指定名称对话框中,勾选首行,单击确定按钮.如此,得到了一个名称定义LIST.1,引用区域为D1:D10.第二步:选定单元格A2,单击菜单数据-有效性,在数据有效性对话框的设置选项卡中,在允许下拉列表框中选择序列项.第三步:在来源框中输入:=OFFSET(LIST.1,A1-1)第四步:勾选忽略空值与提供下拉箭头复选框,如图所示,单击确定按钮,关闭数据有效性对话框. 现在当用户在单元格中分别输入1.2.3时,A2的下拉列表框中会出现不同的选择项目,分别对应3个数据源,如图所示.

24、 从本实例可以看出,当设置数据有效性时,序列来源不仅可以指定一个单元格区域.使用名称来引用单元格区域,还可以使用公式来完成更复杂的任务,上述公式利用了OFFSET函数,根据A1单元格的参数值来动态引用数据源. 在通常情况下,当用户使用数据有效性时,只能处理一组数据来源,而在某些场合中,数据来源不止一组,是否有某种方法可以为有效性快速切换数据来源呢?例如在如图所示的工作表中,分别有LIST.1,LIST.2,LIST.3三第一步:选定单元格D1:D10,单击菜单插入-名称-指定,在指定名称对话框中,勾选首行,单击确定第二步:选定单元格A2,单击菜单数据-有效性,在数据有效性对话框的设置选项卡中,

25、在允许下拉列第四步:勾选忽略空值与提供下拉箭头复选框,如图所示,单击确定按钮,关闭数据有效性对话框. 现在当用户在单元格中分别输入1.2.3时,A2的下拉列表框中会出现不同的选择项目,分别对应3个数 从本实例可以看出,当设置数据有效性时,序列来源不仅可以指定一个单元格区域.使用名称来引用单元格区域,还可以使用公式来完成更复杂的任务,上述公式利用了OFFSET函数,根据A1单元格的参数多极选择录入 在许多信息系统的单据录入界面中，都有多极选择的功能，即先选择录入某大项，然后选择录入属于此大项的小项。这是一项非常实用的功能，能够有效避免串项和项目混乱，在EXCEL中，利用数据有效性能够很容易地实现

26、单级的选择录入，如果配合名称和公式，也能够实现两级和两级以上的选择性录入效果。 在如图所示的工作表城市列表中，A列G列保存了一份中国行政区域资料，记录了部分省份的城市清单。现在希望自爱多级选择工作表中实现对省份和城市的选择性录入效果，要求可以先选择录入省份，然后选择录入该省份所有城市。第一步：按组合键，在定义名称对话框中，添加City和Province两个名称，分别对应的引用位置=城市列表！$A$2和=城市列表!$A$1:$G$1,如图所示。第二步:单击工作表标签以切换到多级选择工作表。第三步:单击A2并拖动光标A11以选定区域A2:A11。第四步:单击菜单数据-有效性，在数据有效性对话框的设置选项卡中，在允许下拉列表框中选择序列项。第五步：在来源框中输入=Province。第六步:勾选忽略空值复选框和提供下拉箭头复选框，单击确定按钮，关闭数据有效性对话框。第七步：单击B2并拖动光标到B11以选定区域B2:B11。第八步：单击菜单数据有效性，在数据有效性对话框的设置选项卡中，在允许下拉列表框中选择序列项。第九步：在来源框中输入：=OFFSET(City,MATCH($A2,Province,0)-1，COUNTA(OFFS