Match函数 | 完美Excel excel match函数用法

MATCH函数

2011年04月25日, 12:28 下午

          (1 人投票, 平均: 4.00 out of 5)

引言:本文及相关的一系列文章都是在contextures Blog的30 Excel Functions in 30 Days系列的基础上整理而成。系统地学习这些函数确实是一项挑战,能够坚持下来一定会获益匪浅。然而,这些文章都采用了很好的描述方法,将函数的用法及常用示例集中在一起,方便理解,也对实际应用很有帮助。

contextures Blog也是一个非常好的Excel学习网站,作者更新很快,分享的内容实用,思路很清晰,文章很简洁但点中实质。

MATCH函数返回指定值在数组中的位置,如果在数组中没有找到该值则返回#N/A。数组可以已经排序或没有排序,并且MATCH函数不区分大小写。



什么情况下使用MATCH函数?

MATCH函数返回指定值在数组中的位置,其结果可以被其他函数使用,例如INDEX函数或VLOOKUP函数。例如:

在未经排序的列表中找到数据项的位置

和CHOOSE一起使用获取学生成绩

和VLOOKUP一起使用来灵活选择列

和INDEX一起使用来显示获胜者的姓名

MATCH函数语法

MATCH函数的语法如下:

MATCH(lookup_value,lookup_array,[match_type])

lookup_value可以是文本、数值或逻辑值

lookup_array是数组或数组引用(在单行或列中的连续单元格)

match_type可以是-1, 0或1。如果忽略,则为1

MATCH陷阱

MATCH函数返回所查找项的位置,而不是值。如果需要获取值,那么与其他函数,如INDEX,一起使用。

示例1:在未排序的列表中查找数据项

对于未排序的列表,可以使用0作为match_type参数,以查找完全匹配的值。如果查找文本并使用0作为参数,那么可以在查找值中包括通配符。

本例中,可以输入月名或带有通配符的部分名字,查找列表中该月的位置。

=MATCH(D2,B3:B7,0)



可以输入数组作为lookup_array参数来代替数组引用。如下面的公式所示,单元格D5中输入要查找的月名,在MATCH函数的第二个参数中输入3个月份名。如果在单元格D5中输入的最后一个月名,例如是Oct,那么结果将是#N/A。

=MATCH(D5,{“Jan”,”Feb”,”Mar”},0)



示例2: 将学生成绩修改为字母

本例中,与CHOOSE函数一起来获取字母成绩。match_type是-1,因为成绩以降序排列。

当参数match_type是-1时,结果为大于或等于查找值的最小值。本例中,查找值是54,不在成绩列表中,因此返回60所在的位置。因为60在成绩列表中的位置是4,所以在CHOOSE函数选项中的第4个值是结果,即单元格C6,其值为D。

=CHOOSE(MATCH(B9,B3:B7,-1),C3,C4,C5,C6,C7)



示例3: 在VLOOKUP函数中创建灵活的列选择

要使VLOOKUP公式更灵活,可以使用MATCH函数来查找列号,而不是在公式中硬编码。本例中,用户可以在单元格H1中选择区域,作为VLOOKUP函数的值。然后,可以在单元格H2中选择月份,MATCH函数返回该月份所在的列。

=VLOOKUP(H1,$B$2:$E$5,MATCH(H2,B1:E1,0),FALSE)



示例4:与INDEX函数一起查找最接近的匹配项

MATCH函数也可以和INDEX函数一起使用。本例中,MATCH函数用于查找最接近正确数值的猜测。

1.ABS函数返回每项猜测和正确数值的绝对差。

2.MIN函数查找最小的差值。

3.MATCH函数在差值列表中查找最小的差值。如果有多个相同的值,那么返回第一个值。

4.INDEX函数返回名字列表中该位置的名字。

=INDEX(B2:B5,MATCH(MIN(ABS(C2:C5-F1)),ABS(C2:C5-F1),0))



MATCH函数示例

相关文章

在VBA中使用工作表公式查找值

Excel中区分大小写的查找

SUMIF函数、COUNTIF函数、INDEX和MATCH函数实现条件求和及组合查找

显示满足条件的所有数据—VLookup函数、IF函数、Row函数、Small函数、Index函数、Match函数、IFERROR函数、表结构的组合使用
Match函数 | 完美Excel excel match函数用法

标签:Match函数

Category: Excel函数与公式| 评论|2,204 次阅读

在VBA中使用工作表公式查找值

2011年03月31日, 8:21 上午

          (1 人投票, 平均: 1.00 out of 5)

问题:

在G列中查找A列中的值,如果找到则将G列中相应行对应的H列中的值复制到该值在A列中相应行的B列中。



例如,单元格A2中的“砖基础”在单元格G3中,则将单元格H3中的值复制到B2中。结果如下:



这样的数据也许会有成千上万上,如果使用VBA程序来处理,将大大节约时间以及减小出错的机率。

首先定义一个动态的名称,以便G列中添加项目时能够自动更新。

名称:LookupRange

=OFFSET(Sheet1!$G$2,0,0,COUNTA(Sheet1!$G:$G)-1)

然后,编写代码如下:

Sub CopyData() Dim lLastRowA As Long Dim rngA As Range Dim rngValueA As Range Dim lRow As Long On Error Resume Next '列A中有数据的最后一行的行号 lLastRowA = Cells(Rows.Count, 1).End(xlUp).Row '设置需要处理的数据区域 Set rngA = Range("A2:" & "A" & lLastRowA) '遍历需要处理的数据区域 For Each rngValueA In rngA '使用工作表函数查找数据所在的行并返回行号 lRow = Application.WorksheetFunction.Match(rngValueA, [LookupRange], 0) + 1 '如果找到则进行相应的操作 If lRow > 0 Then Range("B" & rngValueA.Row) = Range("H" & lRow) lRow = 0 End If NextEnd Sub

本来也可以使用两个循环来解决问题,也就是在里面再使用一个For Each循环遍历G列中的内容,但使用工作表公式使得程序代码更简洁,也更快捷。

相关文章

MATCH函数

在VBA中使用Excel公式

Excel中区分大小写的查找

SUMIF函数、COUNTIF函数、INDEX和MATCH函数实现条件求和及组合查找

显示满足条件的所有数据—VLookup函数、IF函数、Row函数、Small函数、Index函数、Match函数、IFERROR函数、表结构的组合使用

标签:Match函数, WorksheetFunction对象

Category: VBA| 评论|1,558 次阅读

Excel中区分大小写的查找

2009年10月17日, 9:13 上午

          (3 人投票, 平均: 5.00 out of 5)

在Excel中,需要查找表中的数值时,通常使用Lookup系列的查找函数,但是这些函数不区分大小写。例如下图所示的查找问题,我们需要得到的结果是6,但是VLOOKUP函数给我的结果却是5。



Microsoft Excel MVP Peo Sjoblom给出了一个解决方法,使用了数组公式:

{=INDEX(B1:B6,MATCH(1,–EXACT(A1:A6,D1),0))}

结果如下图所示。



注解:

INDEX函数返回表或区域中值或值的引用。

MATCH函数返回在指定方式下与指定数值匹配的数组中元素的位置。

EXACT函数测试两个字符串是否完全相同,如果完全相同则返回TRUE,否则返回FALSE。能够区分大小写。

补充:

还可以使用下面的公式:

=LOOKUP(1,1/EXACT(A1:A6,D1),B1:B6)

相关文章

SUMIF函数、COUNTIF函数、INDEX和MATCH函数实现条件求和及组合查找

显示满足条件的所有数据—VLookup函数、IF函数、Row函数、Small函数、Index函数、Match函数、IFERROR函数、表结构的组合使用

使用IF函数和SUM函数组成的数组公式进行条件统计

“威力强大”的Index函数

INDEX函数

MATCH函数

在VBA中使用工作表公式查找值

查看数组公式的中间值

EXACT函数

Excel中创建动态扩展的区域(总结)

标签:EXACT函数, Index函数, Match函数, VLookup函数, 函数技巧, 数组公式

Category: Excel函数与公式| 评论|3,895 次阅读

SUMIF函数、COUNTIF函数、INDEX和MATCH函数实现条件求和及组合查找

2009年07月3日, 1:33 下午

          (2 人投票, 平均: 4.50 out of 5)

问题的提出

如下图1所示,在工作表的A列、B列和C列中存放着一些统计数据,即每天每间隔一小时的数据,现在要求在F列求出每天所对应数据的平均值,在G列中获取每天12时的数据。



图1:原始数据

使用SUMIF函数和COUNTIF函数求每天所对应数据的平均值

第1步:选择单元格F2,输入公式=SUMIF($A$2:$A$73,E2,$C$2:$C$73)/COUNTIF($A$2:$A$73,E2)。

第2步:选择单元格F2,下拉至所有单元格(或双击右下角的填充柄)。

也可以使用名称来简化公式。例如,将列A中的数据命名为Date,列B中的数据命名为Time,列C中的数据命名为Data。此时的公式为:

=SUMIF(Date,E2,Data)/COUNTIF(Date,E2)

使用INDEX函数和MATCH函数实现查找每天12时的数据

第1步:将列E中的数据命名为FindDate,在单元格I1中输入12:00并将其命名为FindTime。

第2步:选择单元格区域G2:G4,输入公式=INDEX(Data,MATCH(FindDate&FindTime,Date&Time,0)),并按下Ctrl+Shift+Enter组合键,即输入数据公式。

最终的结果如下图2所示。



图2:最终结果

以上示例给出了具体步骤,若数据区域有变化,作相应的调整即可。

示例下载:

更详细的示例下载:

相关文章

Excel中区分大小写的查找

显示满足条件的所有数据—VLookup函数、IF函数、Row函数、Small函数、Index函数、Match函数、IFERROR函数、表结构的组合使用

“威力强大”的Index函数

INDEX函数

MATCH函数

在VBA中使用工作表公式查找值

Excel中创建动态扩展的区域(总结)

在Excel中查找部分匹配的数据(2)

在Excel中查找部分匹配的数据(1)

Excel矩阵函数简介及应用示例

标签:CountIf函数, Index函数, Match函数, SumIf函数, 函数技巧, 工作表函数

Category: Excel函数与公式| 3 评论|9,528 次阅读

显示满足条件的所有数据—VLookup函数、IF函数、Row函数、Small函数、Index函数、Match函数、IFERROR函数、表结构的组合使用

2009年03月20日, 1:26 下午

           (11 人投票, 平均: 4.36 out of 5)

一个简单的示例:查找Excel工作表中的重复数据

记得一位网友曾问:要求找出Excel工作表中的重复数据并显示在工作表相应的单元格中。我给出了一个数组公式供参考,但不是太符合要求,因为这个数组公式虽然找出了重复数据,但是如果将数组公式向下复制时超出了出现重复数据的数量,会在相应单元格中显示错误。不久,这位朋友获得了更好的一个公式。这个公式非常好,完美地解决了这类问题,因此,我将其转贴于此,供有兴趣的朋友参考。

先看看下图:



在列A和列B中存在一系列数据(表中只是示例,可能还有更多的数据),要求找出某人(即列A中的姓名)所对应的所有培训记录(即列B中的数据)。也就是说,在单元格E1中输入某人的姓名后,下面会自动显示这个人所有的培训记录。

我们知道,Excel的LOOKUP系列函数能够很方便地实现查找,但是对于查找后返回一系列的结果,这类函数无能为力,因此只能联合其它函数来实现。

这里,在方法一中使用了INDEX函数、SMALL函数、IF函数和ROW函数,在方法二中还使用了Excel 2007中新增的IFERROR函数。

方法一:

选择单元格E3;

输入公式:

=INDEX(B:B,SMALL(IF($A$2:$A$25=$E$1,ROW($A$2:$A$25),65536),ROW(1:1))) & “”

然后同时按下Ctrl+Shift+Enter键,即输入数组公式。

选择单元格E3后下拉至所有单元格。

方法二:

选择单元格F3;

输入公 式:

=IFERROR(INDEX($A$2:$B$9,SMALL(IF($A$2:$A$9=$E$1,ROW($A$2:$A$9)-ROW($A$2)+1,ROW($A$9)+1),ROW(1:1)),2),”")

然后同时按下Ctrl+Shift+Enter键,即输入数组公式。

选择单元格F3后下拉至所有单元格。

示例文档下载:

一个复杂的示例:查找不同工作表中的数据并显示满足条件的所有数据

对于VLOOKUP函数来说,其主要缺点是仅能返回与查找条件相匹配的单条数据,不能够返回与某条件相匹配的所有数据,但其优势是能够相当容易地获取与所给条件相匹配的第一条数据,如下图1所示。



图1:使用VLOOKUP函数返回包含某顾客姓名相应的行中的数据是很容易的方式

在Excel 2007中使用结构化引用,VLOOKUP函数不仅容易使用,而且可读性也很强。在示例中,将表Table1中第一列的值与单元格A9中的值相匹配,并从表中第3列返回值。但是,不能返回表中第二个与“Dan”相匹配的值。事实上,很多时候我们都会碰到这样的情况,我们想要返回与条件相匹配的所有值,但是VLOOKUP函数只能返回满足条件的第一个值。

下面,让我们看看如何从表中获取满足条件的所有数据。首先,准备一些要操作的数据。我们以Office自带的Northwind示例数据库的一部分数据来演示,将其中的部分数据导入Excel表中,如下图2、图3、图4所示。



图2:导入到工作表Customers中的“Customer”表并命名为“tblCustomers”



图3:导入到工作表Orders中的“Orders”表并命名为“tblOrders”



图4:导入到工作表Details中的“OrderDetails”表并命名为“tblDetails”

当然,上述数据都不需要自已手工输入,只需从Northwind数据库中导入即可。现在,希望选择订单号(Order Number)后,能够显示该订单的详细信息,如下图5所示。



图5:当选择某订单号后,会显示该订单的详细信息

下面是图5显示的界面的主要设计过程。

步骤1 在工作表相应的单元格中输入下列字符:

单元格B1:Order Number

单元格C3:Order Information

单元格C4:Customer

单元格F4:Order Date

单元格F5:Status

单元格F6:Salesperson

单元格F7:Ship Date

单元格C10:Order Details

单元格C11:Product

单元格D11:Quantity

单元格E11:Unit Price

单元格F11:Discount

单元格G11:Total Price

单元格H11:Status ID

步骤2 创建包含所有订单IDs的一个命名区域,然后使用该名称在数据有效性中创建订单号的下拉列表。为此,单击“公式—定义名称”,在“新建名称”对话框中输入:

名称:OrderIds

引用位置:=tblOrders[ID]

注:数据有效性不能够引用不同工作表中的单元格区域,除非为该区域定义名称。

步骤3 选择单元格D1,将其命名为rngOrderId。

步骤4 选择单元格D1,单击“数据—数据有效性”,在“允许”中选择“序列”,在“来源”框中输入“=OrderIds”。

步骤5 选择单元格C5,输入下列函数:

=VLOOKUP(rngOrderId, tblOrders, MATCH(C4, tblOrders[#标题], 0), FALSE)

步骤6 与单元格C5中的函数相似,设置剩余单元格的查找函数。

C6:=VLOOKUP($C$5,tblCustomers,MATCH(“Address”,tblCustomers[#标题],0), FALSE)

C7:=VLOOKUP($C$5, tblCustomers, MATCH(“City”,tblCustomers[#标题],0), FALSE) & “, ” & VLOOKUP($C$5, tblCustomers, MATCH(“State”,tblCustomers[#标题],0), FALSE) & ” ” & VLOOKUP($C$5, tblCustomers, MATCH(“Zip”,tblCustomers[#标题],0), FALSE)

H4:=VLOOKUP(rngOrderId, tblOrders, MATCH(F4, tblOrders[#标题], 0), FALSE)

H5:=VLOOKUP(rngOrderId, tblOrders, MATCH(F5, tblOrders[#标题], 0), FALSE)

H6:=VLOOKUP(rngOrderId, tblOrders, MATCH(F6, tblOrders[#标题], 0), FALSE)

H7:=VLOOKUP(rngOrderId, tblOrders, MATCH(F7, tblOrders[#标题], 0), FALSE)

至此,完成了我们的界面的上半部分,如图6所示。



图6:已完成的查找界面

接下来,让我们看看后面将要使用的公式中的一些函数。

实际上,我们将不会使用VLOOKUP函数。因为需要返回多个条目,因此需要一种方式来返回一组值,而VLOOKUP函数不具备这样的功能。这里,我们使用INDEX函数返回指定行和列交叉部分的值。

我们需要指定想要在单元格中显示的数组中的条目,这里可使用SMALL函数和ROW函数来实现。

我们希望如果在源数据中添加额外的列时确保数据能够自动调整,这里使用MATCH函数。

最后,我们希望有错误处理功能,这里使用IFERROR函数来确保如果在计算中有错误将只是显示空单元格。(IFERROR函数是Excel 2007中新增的函数)

好了,先让我们来看看已经完成的函数,如下图7中的单元格C12所示(注意,这是一个数组公式,其左右两侧的花括号不需要自已输入。在输完公式后按下Ctrl+Shift+Enter组合键即可)。



图7:数组公式用于返回与特定值相匹配的第N项

这个公式看起来很复杂,为便于阅读,将其书写如下:

{

=IFERROR(

INDEX(tblDetails,

SMALL(

IF(tblDetails[Order ID]=rngOrderId,

ROW(tblDetails[Order ID])-ROW(tblDetails[#标题])

),

ROW(1:1)

),

MATCH(C$11, tblDetails[#标题], 0)

),

“”)

}

首先,看看SMALL函数,该函数接受一组值并从中返回第N小的值。

SMALL(

IF(tblDetails[Order ID]=rngOrderId,

ROW(tblDetails[Order ID])-ROW(tblDetails[#标题])

),

ROW(1:1)

),

本例中,数组的值由IF函数确定。特别地,如果Details表中某行的Order ID与在下拉列表中选择的Order ID相等,那么添加该行的行号到数组值中。通过使用ROW函数,Excel能够自动调整公式来向下填充单元格。并且,通过使用结构化引用,使得工作表数据能够自动适应源数据的变化。

然后,再看看INDEX函数,该函数接受一个二维数据并返回数组中指定行列位置的值。

INDEX(tblDetails,

SMALL(

IF(tblDetails[Order ID]=rngOrderId,

ROW(tblDetails[Order ID])-ROW(tblDetails[#标题])

),

ROW(1:1)

),

MATCH(C$11, tblDetails[#标题], 0)

),

我们已经知道,本例中的SMALL函数返回与输入的Order ID相匹配的Order Details列表中ROW(Nth)的值,这里是第一个值即行号。接着需要获取列号,即使用MATCH函数,本例中该函数接受C11的值并查找在Details表中有相同名称的列。

最后使用的是IFERROR函数,如果包含的计算式发生错误,使用空字符串(””)替换错误值。

现在,选择单元格C12,拖动其右下角的填充柄至单元格H12。选择单元格区域C12:H12,拖动填充句柄向下10行或更多。现在,将显示满足该订单号的所有信息,并且当我们改变Order ID时,将会自动更新相关信息。



图8:完成的界面表,使用数组公式向下填充以获取所有相关信息

示例文档下载:

相关文章

Excel中区分大小写的查找

Excel矩阵函数简介及应用示例

SUMIF函数、COUNTIF函数、INDEX和MATCH函数实现条件求和及组合查找

“威力强大”的Index函数

INDEX函数

MATCH函数

在VBA中使用工作表公式查找值

Excel中创建动态扩展的区域(总结)

在Excel中查找部分匹配的数据(2)

在Excel中查找部分匹配的数据(1)

标签:IFERROR函数, IF函数, Index函数, Match函数, Row函数, Small函数, VLookup函数, 表

Category: Excel函数与公式| 11 评论|27,021 次阅读

订阅本站

  

爱华网本文地址 » http://www.aihuau.com/a/25101013/160861.html

更多阅读

MATLAB 各种 对数函数 用法以及实例 matlab 对数函数

MATLAB 各种 对数函数 用法以及实例——简介在MATLAB运算当中,我们常常需要求对数,在编写M文件的过程中,我们也需要表示对数,下面我就通过一些示例介绍一下如何在MATLAB中求对数。MATLAB 各种 对数函数 用法以及实例——工具/原料MATLA

Excel函数详解:166 FREQUENCY函数用法

Excel函数详解:[166]FREQUENCY函数用法——简介FREQUENCY函数,用于以垂直数组的形式返回多个数值在某个区域内出现的频率。该函数返回的是数组,所以必须以数组公式的形式进行输入。Excel函数详解:[166]FREQUENCY函数用法——工具/原料

matlab曲线拟合 函数 用法以及例子 如何用matlab拟合曲线

matlab曲线拟合 函数 用法以及例子——简介在运行MATLAB编程进行数据的处理过程当中,我们常常用到matlab曲线拟合,但是工具箱由于需要人工交互,得到的拟合结果,需要人工的去提取,再输入,所以,工具箱拟合结果十分不适合调用,以及继续下面的操

谈谈 Excel SUMIF函数用法 多条件求和 sumif日期条件求和

2010年9月1日 Surda 317 次 发表评论 阅读评论上篇文章跟大家分享了SUM函数非一般的用法,本文我们来品味SUMIF的魅力。SUMIF,根据指定条件对若干单元格求和。其函数原型:SUMIF(range,criteria,sum_range),参数:range 为用于条件判断的

Excel统计函数COUNTIF的用法及实例 vb随机函数用法和实例

[转自]★笨笨☆ http://blog.sina.com.cn/pengjichang一:首先介绍COUNTIF函数的用法英文通用格式是:COUNTIF(range,criteria)中文通用格式是:COUNTIF(数据区域,条件表达式)1、返加包含值12的单元格数量=COUNTIF(range,12)2、返回包含负值的

声明:《Match函数 | 完美Excel excel match函数用法》为网友出售灵魂分享!如侵犯到您的合法权益请联系我们删除