澳门赌场

Excel Match函数的利用体例的8个实例,含与Sum、OffSet、Indirect组合完成提取数据和对变更地区静态跨表求

亮术网 2018-09-02 本网首创

在 Excel 中,Match函数用于前往查找值在援用单位格中的绝对地位。它共有三个参数,第一个参数为查找值,第二参数为查找地区,第三个参数为婚配选项;婚配选项能够设置为切确婚配和恍惚婚配,此中恍惚婚配须要排序。

Match函数常与Index、Sum、OffSet、Indirect函数组合利用,此中 Index + Match 或 Indirect + Match 组合都能完成前往行列对应的多个值(即提取数据),OffSet + Match 组合能前往大于即是查找值的最小值对应的某列的值,Sum + OffSet + Indirect + Match 组合完成对变更地区静态跨表乞降。以下是Match函数的利用体例的8个实例。

 

一、Excel Match函数语法

1、抒发式:MATCH(LookUp_Value, LookUp_Array, [Match_Type])

中文抒发式:MATCH(查找值, 查找地区, [婚配范例])

 

2、申明:

(1)Match_Type 为可选项,它有三个取值,别离为 1、0 和 -1,若是省略 Match_Type,默许取 1。

A、若是 Match_Type 省略或取 1,则查找小于等查找值的最大值,并且查找地区要按升序排序,不然能够前往不准确的值。

B、若是 Match_Type 取 0,则查找第一个完整即是查找值的值,查找地区不须要排序。

C、若是 Match_Type 取 -1,则查找大于即是查找值的最小值,查找地区要按降序排序,不然能够前往不准确的值。

(2)当 Match_Type 为 0 且查找值是文本,可在查找值中利用通配符“问号 (?) 和星号 (*)”;问号表现肆意一个字符,星号表现肆意一个或一串字符;若是要查找问号或星号,须要在它们前加本义字符(~);比方:查找题目应当如许写:~?,查找星号应当如许写:~*。

(3)若是Match函数找不到值,将前往毛病值 #N/A;别的,查找文本时,Match函数不辨别巨细写。

 

 

二、Excel Match函数的利用体例及实例

(一)Match_Type 省略或取 1 的实例

1、假设要查找员工表中“姓名”为“黄子辛”的地位。双击 A9 单位格,把公式 =MATCH("黄子辛",A2:A8) 复制到 A9,按回车,前往毛病值 #N/A;框选 A2:A8,挑选“数据”选项卡,单击“升序”图标,翻开“排序提示”窗口,坚持默许选项“扩大选定地区”,单击“肯定”,则表格按“姓名”升序摆列;再次双击 A9,按回车,前往“黄子辛”在 A2:A8 的地位 1;双击 A9,给公式增加第三个参数 1,按回车,也前往 1;操纵进程步骤,如图1所示:

Excel Match函数的利用体例及实例

图1

2、公式申明:

A、公式 =MATCH("黄子辛",A2:A8) 中,“黄子辛”为查找值,A2:A8 为查找地区,它省略了参数 Match_Type,当不对 A 列按升序排序时,前往毛病值 #N/A,当排序后能力前往“黄子辛”在 A2:A8 中的地位 1,当给公式加上参数 Match_Type(设置为 1)后,一样前往 1,申明参数 Match_Type 省略或取 1 时,查找地区须要按升序排序且查找小于即是查找值的最大值。

B、别的,查找值在查找地区中的地位从选定地区起头算起,这里的选定地区从第二行起头算起,是以前往 1。

 

(二)Match_Type 取 0 的实例

1、一样以查找员工表中“姓名”为“黄子辛”的地位为例。双击 A9 单位格,把公式 =MATCH("黄子辛",A2:A8,0) 复制到 A9,按回车,前往 3;双击 A9,把公式后面的 0 删除,按回车,也前往 3;操纵进程步骤,如图2所示:

Excel Match函数 Match_Type 取 0 的实例

图2

2、公式申明:

A、当参数 Match_Type 取 0 时,在不按升序排序时,仍能准确前往“黄子辛”在 A2:A8 中的地位 3,申明 Match 取 0 时,查找地区不必排序。

B、公式 =MATCH("黄子辛",A2:A8,0) 与 =MATCH("黄子辛",A2:A8,) 能前往不异的值,申明省略第三个参数 Match_Type 时,若是第二个参数后有逗号(,),Match_Type 默许取 0;若是第二个参数后不逗号,Match_Type 默许取 1,这一点要注重。

 

(三)Match_Type 取 -1 的实例

1、假设要在生果销量表中查找销量 2050 和 2000 的地位。双击 D8 单位格,把公式 =MATCH(2050,D2:D8,-1) 复制到 D8,按回车,前往毛病值 #N/A;选中 D8,按 Delete 键把公式删除;挑选“数据”选项卡,单击“降序”图标把表格按“销量”降序排序;再次把公式 =MATCH(2050,D2:D8,-1) 复制到 D8,按回车,前往 2050 在 D2:D8 中的地位 3;双击 D8,把 2050 改成 2000,按回车,一样前往 3;操纵进程步骤,如图3所示:

Excel Match函数 Match_Type 取 -1 的实例

图3

2、公式申明:

A、当 Match_Type 取 -1 时,在未对查找地区“销量”列排序前,公式前往毛病值 #N/A,当按“降序”排序后,能前往 2050 在 D2:D8中的准确地位 3,申明 Match_Type 取 -1,查找地区必须按降序排序;当把 2050 改成 2000 后,因为 D2:D8 中不 2000,以是查找大于即是 2000 的最小值,即 2050,最初前往该值的地位。

 

(四)查找值利用通配符问号(?)或星号(*)的实例

1、假设要查找以肆意一个字或三个字开首且以“瓜”开首的生果称号在 A 列中的地位。双击 D8 单位格,把公式 =MATCH("?瓜",A2:A7,0) 复制到 D8,按回车,前往 2;双击 D8,把 "?瓜" 改成 "???瓜",按回车,前往 4;再次双击 D8,把 "???瓜" 改成 "*桃",按回车,前往 5;操纵进程步骤,如图4所示:

Excel Match函数查找值利用通配符问号(?)或星号(*)的实例

图4

2、公式申明:

公式 =MATCH("?瓜",A2:A7,0) 中的查找值 "?瓜" 有一个问号,它表现肆意一个字,找到的是“香瓜”,是以前往地位 2;"???瓜" 表现由肆意三个字开首且以“瓜”开首,是以找到“青皮西瓜”;"*桃" 表现以肆意一个或多个字开首且以“桃”开首,是以找到“猕猴桃”。利用通配符除以上几种查找体例外,还能够组合出多种查找体例,在后面的篇章中已先容过屡次。

 

 

三、Excel Match函数的利用体例扩大实例

(一)Index + Match 组合利用实例

Index + Match 有两种组合,一种为 =Index(, Match(,,)),另一种为 =Index(, Match(,,),Match(,,)),详细请检查《Excel Index函数的利用体例及与Match、Small、If共同前往行列对应的多个值和一对多、多对多查找》一文。

 

(二)Indirect + Match 组合完成前往行列对应的多个值(提取数据)实例

1、双击 B10 单位格,把公式 =INDIRECT("r"&MATCH($A10,$A$1:$A$7,)&"c"&COLUMN(),0) 复制到 B10,按回车,前往“浆果”;选中B10,把鼠标移到 B10 右下角的单位格添补柄上,鼠标变为十字架后,按住左键,往右拖,则提掏出“香蕉”的一切数据;把鼠标移到 D10 右下角单位格添补柄上,按住左键往下拖,则提取“香瓜”的一切数据;双击 B13,把公式 =INDEX($A$1:$D$7,MATCH($A13,$A$1:$A$7,),MATCH(B$9,$A$1:$D$1,)) 复制到 B13,按回车,一样前往“浆果”,再用往右拖和往下拖的体例提取残剩数据;操纵进程步骤,如图5所示:

Excel Match函数Indirect + Match 组合完成前往行列对应的多个值(提取数据)实例

图5

 

2、公式申明:

(1)公式 =INDIRECT("r"&MATCH($A10,$A$1:$A$7,)&"c"&COLUMN(),0)

A、$A10 表现绝对援用列绝对援用行,往右拖时,A10 不会变为 B10、C10、……;往下拖时,A10 会变为 A11、A12、……;$A$1:$A$7以数组情势前往 A1:A7 中的“称号”。

B、MATCH($A10,$A$1:$A$7,) 用于前往 A10(香蕉)在 A1:A7 中的地位,成果为 4;COLUMN() 用于前往公式地点列列号,当公式在 B10 时,前往 2。

C、则公式变为 =INDIRECT("r"&4&"c"&2,0),& 是毗连符,在这里用于把字符(或字符串)与数字毗连起来,进一步计较变为 =INDIRECT(r4c2,0),r4c2 中 r 表现行、c 表现列,r4c2 表现第4行第2列,即 B4;参数 0 表现单位格援用被诠释为 r1b1;最初用 Indirect 前往 r4c2 的援用,即前往“浆果”。

(2)公式 =INDEX($A$1:$D$7,MATCH($A13,$A$1:$A$7,),MATCH(B$9,$A$1:$D$1,)) 与 =INDIRECT("r"&MATCH($A10,$A$1:$A$7,)&"c"&COLUMN(),0) 感化一样,所差别的是 Index + Match 在公式中要援用表格的列名(如 B$9),而 Indirect + Match 不需援用;Index + Match 的诠释请参考下面的“Index + Match 组合利用实例”。

 

(三)OffSet + Match 组合利用实例

1、假设要前往大于即是查找值的最小值对应的某列的值,比方要查找价钱为 3.5 或 3.8 对应的“称号”。先把 C 列按“降序”排序(体例在下面“Match_Type 取 -1 的实例”已先容),双击 B10 单位格,把公式 =OFFSET(A1,MATCH(A10,C2:C7,-1),) 复制到 B10,按回车,前往“猕猴桃”;双击 C10,把公式 =VLOOKUP(A10,CHOOSE({1,2},C2:C7,A2:A7),2,) 复制到 C10,按回车,也前往“猕猴桃”;双击 A10,把 3.5 改成 3.8,单击一下 B10(或按 Ctrl + S 保管),B10 的内容变为“香蕉”,C10 的内容变为毛病值 #N/A;操纵进程步骤,如图6 所示:

Excel OffSet + Match 组合利用实例

图6

 

2、公式申明:

A、=OFFSET(A1,MATCH(A10,C2:C7,-1),) 中 MATCH(A10,C2:C7,-1) 用于前往 A10(3.5)在 C2:C7 中的地位 3;则公式变为 =OFFSET(A1,3,),而后用 OffSet 前往距 A1 三行 0 列的单位格中的值,恰好是 A5 中的“猕猴桃”。

B、公式 =VLOOKUP(A10,CHOOSE({1,2},C2:C7,A2:A7),2,) 与 =OFFSET(A1,MATCH(A10,C2:C7,-1),) 能前往不异的值仅限于查找规模中有查找值,比方 A10 为 3.5 时,C 列中有 3.5;当把 3.5 改成 3.8,OffSet + Match 能前往准确的值,而 VLookUp + Choose 前往 #N/A,申明 OffSet + Match 能查找大于即是查找值的最小值,而 VLookUp + Choose 却不能;别的,OffSet + Match 比 VLookUp + Choose 效力高。

C、公式 =VLOOKUP(A10,CHOOSE({1,2},C2:C7,A2:A7),2,) 在《Excel VLookUp函数的利用体例,含逆向查找、一对多查找和近似婚配实例》一文中已有近似先容,若是不懂得能够参考。

 

(四)Sum + OffSet + Indirect + Match 组合完成对变更地区静态跨表乞降实例

1、假设要汇总每一个月的女装和男装的销量,女装销量与男装销量别离在一个表,要把它们汇总到“汇总”表。以后任务表为“服装网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net销量女装”,单击“服装网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net销量男装”切换到该表,单击“汇总”表切换到该表;双击 B2 单位格,把公式 =SUM(OFFSET(INDIRECT("服装网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net销量"&$A2&"!$B$3:$B$7"),,MATCH(B$1,INDIRECT("服装网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net销量"&$A2&"!$C$2:$H$2"),))) 复制到 B2,按回车,前往“4月”的“女装”销量汇总成果2407;选中 B2,用往右拖的体例前往“女装”残剩月份的销量汇总成果,再用往下拖的体例前往“男装”各月的销量汇总成果;操纵进程步骤,如图7所示:

Excel Sum + OffSet + Indirect + Match 组合完成对变更地区静态跨表乞降实例

图7

 

2、公式 =SUM(OFFSET(INDIRECT("服装网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net销量"&$A2&"!$B$3:$B$7"),,MATCH(B$1,INDIRECT("服装网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net销量"&$A2&"!$C$2:$H$2"),))) 申明:

A、"服装网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net销量"&$A2&" 用于组合任务表称号,每一个任务表都以“服装网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net销量”开首,A2 中为“女装”,是以,公式履行时,"服装网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net销量"&$A2&" 变为“服装网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net销量女装”。当往下拖时, A2 变为 A3,"服装网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net销量"&$A2&" 变为 "服装网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net销量"&$A3&",即“服装网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net销量男装”。

B、$B$3:$B$7 以数组情势前往 B3 至 B7 中的值,INDIRECT("服装网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net销量"&$A2&"!$B$3:$B$7") 把字符转为对单位格的援用,它前往“服装网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net销量女装!$B$3:$B$7”。

C、$C$2:$H$2 以数组情势前往 C2 至 H2 中的值,即前往 C2 至 H2 的列名;INDIRECT("服装网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net销量"&$A2&"!$C$2:$H$2") 前往“服装网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net销量女装!$C$2:$H$2”。

D、MATCH(B$1,INDIRECT("服装网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net销量"&$A2&"!$C$2:$H$2"),) 变为 MATCH(B$1,服装网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net销量女装!$C$2:$H$2,),接着用 Match 前往 B1(4月)在 C2 至 H2 中地位 2。

E、则公式变为 =SUM(OFFSET(服装网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net销量女装!$B$3:$B$7,,2,))),进一步计较用 OffSet 前往“服装网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net销量女装!$D3$3:$D$7”,是怎样前往的呢?履行到 OffSet 时,首掏出 B3,而后前往与 B3 相隔两列的单位格,即前往 D3;第二次掏出 B4,前往与 B4 相隔两列的单位格 D4;别的的以此类推,是以最初前往“服装网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net销量女装!$D3$3:$D$7”;则公式变为 =SUM(服装网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net销量女装!$D$3:$D$7),最初用 Sum 对 D3:D7 乞降,以是前往 2407。