excle查找函数精粹
- 格式:xlsx
- 大小:93.99 KB
- 文档页数:7
Excel必学的16大类查找公式,个个都是神级操作办公工具箱 2018-03-13 14:39:36在Excel处理数据中,只会Vlookup函数是远远不够的。
以下是最全的16类excel查找公式,请收好:1、普通查找=VLOOKUP(H2,B:F,5,0)2、交叉查找=VLOOKUP(H2,A:F,MATCH(I2,1:1,0),0)3、反向查找=INDEX(A:A,MATCH(H2,B:B,0))4、多条件查找=LOOKUP(1,0/((A2:A7=E2)*(B2:B7=F2)),C2:C7)5、区间查找=LOOKUP(A2,$D$2:$E$5)6、双区间查找=INDEX(B3:F7,MATCH(D11,A3:A7),MATCH(E11,B2:F2)) 7、线型插值如下图所示,A列是数量,B列是数量对应的系数值。
现要求出数字8所对应的系数值。
=TREND(OFFSET(B1,MATCH(D3,A2:A6,1),,2,1),OFFSET(A1,M ATCH(D3,A2:A6,1),,2,1),D3)8、查找最后一个符合条件记录=LOOKUP(1,0/(B2:B9=A13),C2:C9)9、模糊查找如下图所示,要求根据提供的城市从上表中查找该市名的第2列的值。
=VLOOKUP("*"&A7&"*",A1:B4,2,0)10、匹配查找=lookup(9^9.find(A$3:A$6,A10),B$3:B$6)11、最后一个非空值查找=LOOKUP(1,0/(B2:B13<>""),$A2:$A13)12、多工作表查找【例10】从各部门中查找员工的基本工资,在哪一个表中不一定。
方法1=IFERROR(VLOOKUP(A2,服务!A:G,7,0),IFERROR(VLOOKUP(A2,人事!A:G,7,0),IFERROR(VLOOKUP(A2,综合!A:G,7,0),IFERROR(VLOOKUP(A2,财务!A:G,7,0),IFERROR(VLOOKUP(A2,销售!A:G,7,0),"无此人信息"))))) 方法2:=VLOOKUP(A2,INDIRECT(LOOKUP(1,0/COUNTIF(INDIRECT({ "销售";"服务";"人事";"综合";"财务"}&"!a:a"),A2),{"销售";"服务";"人事";"综合";"财务"})&"!a:g"),7,0)13、一对多查找A2 =B2&COUNTIF(B$1:B2,B2)B11=IFERROR(VLOOKUP($A11&COLUMN(A1),$A:$C,3,0) ,"")14、查找销量最大的城市{=INDEX(A:A,MAX((MAX(B3:B7)=B3:B7)*ROW(B3:B7)))} 15、最接近值查找根据D4的价格,在B列查找最接近的价格,并返回相对应的日期(数组公式按ctrl+shift+enter三键输入){=LOOKUP(1,0/(MIN(ABS(B3:B7-D4))=ABS(B3:B7-D4))*ROW(B3:B7),A3:A7)}16、跨多文件查找跨多个文件查找,估计你搜遍网络也找不到。
Excel中的查找和引用函数是两个常用的函数,用于在表格中查找数据和引用数据。
以下是这些函数的详细解释:1. VLOOKUP函数:VLOOKUP函数用于在表格中查找数据。
它接受四个参数:要查找的值、要查找的表格、要返回的结果和精确匹配。
如果设置为模糊匹配,则可以使用通配符代替要查找的值。
例如,如果您要在表格中查找“John”的名字,可以使用以下公式:=VLOOKUP(John, 表格名称, 列数, 精确匹配)2. HLOOKUP函数:HLOOKUP函数用于在表格中引用数据。
它接受四个参数:要引用的值、要引用的表格、要返回的值和精确匹配。
如果设置为模糊匹配,则可以使用通配符代替要引用的值。
例如,如果您要在表格中引用“John”的名字,可以使用以下公式:=HLOOKUP(John, 表格名称, 列数, 精确匹配)3. INDEX函数:INDEX函数用于在表格中返回指定行和列的数据。
它接受三个参数:要返回的表格、要返回的行和要返回的列。
例如,如果您要在表格中返回第2行第3列的数据,可以使用以下公式:=INDEX(表格名称, 行数, 列数)4. MATCH函数:MATCH函数用于在表格中查找指定值的位置。
它接受两个参数:要查找的值和要返回的位置。
例如,如果您要在表格中查找“John”的名字,可以使用以下公式:=MATCH(John, 表格名称, 精确匹配)5. OFFSET函数:OFFSET函数用于在表格中返回指定行和列的数据。
它接受四个参数:要返回的表格、要返回的行和要返回的列、要返回的行偏移量和要返回的列偏移量。
例如,如果您要在表格中返回第2行第3列的数据,可以使用以下公式:=OFFSET(表格名称, 偏移量)。
excel 文本查找函数Excel文本查找函数可以在单元格或一个单元格区域中搜索文本字符串或值。
该函数提供了一种快速、高效的方法来搜索任何文本字符串或值,因此,在任何情况下,只需几次点击即可找到所需的信息和数据。
Excel中的文本查找函数包括FIND、SEARCH、HLOOKUP和VLOOKUP。
每个函数都被设计用来快速查找特定字符串所在的位置或内容。
FIND函数是最简单也是最常用的文本查找函数之一,它可以在单元格或整个文档中查找指定的字符串,并返回字符串在单元格中的位置。
用法如下: =FIND(要搜索的文本,单元格),例如: =FIND (“dog”,A1)。
SEARCH函数与FIND函数类似,它主要用于在单元格或文档中搜索文本,并将找到的文本返回到指定的单元格。
用法如下:=SEARCH (要搜索的文本,搜索范围),例如:=SEARCH (dogA1)。
HLOOKUPVLOOKUP函数都可以用来在单元格区域中搜索文本字符串或值,尤其是在处理大型数据记录时,它们会变得特别方便有效。
VLOOKUP函数是在一个垂直列中搜索文本,然后返回查找结果,而HLOOKUP函数是在水平表格中搜索文本,并将查询结果返回到指定的单元格中。
用法如下:VLOOKUP(要搜索的文本,搜索范围,搜索结果的列数,TRUE/FALSE),例如:=VLOOKUP(dogA1:C10,2,TRUE/FALSE);HLOOKUP(要搜索的文本,搜索范围,搜索结果的行数,TRUE/FALSE),例如:HLOOKUP(dogA1:C10,2,TRUE/FALSE) 。
Excel本查找函数的实际应用也很广泛,使用者可以通过它快速有效地检索数据,并得到准确的结果。
比如,在财务管理方面,我们可以使用FIND函数来搜索特定会计期间的收入,也可以使用VLOOKUP 函数来查找特定客户的账单,这样可以大大减少时间摆弄数据的繁琐程序。
另外,Excel中的文本查找函数也可以帮助用户构建动态报表,并灵活处理数据。
excel在多个工作簿中查找的函数Excel是一款功能强大的电子表格软件,它提供了许多方便实用的函数来帮助用户进行各种数据处理和分析。
其中,一项非常重要的功能就是在多个工作簿中进行查找。
本文将介绍几个常用的Excel函数,帮助读者了解如何在多个工作簿中进行查找。
一、VLOOKUP函数VLOOKUP函数是Excel中最常用的函数之一,它可以在单个工作簿中查找指定的数值或文本,并返回相应的结果。
但是,对于多个工作簿的查找,VLOOKUP函数并不直接支持。
不过,我们可以通过一些技巧来实现多个工作簿的查找。
我们可以使用INDIRECT函数和CONCATENATE函数将需要查找的工作簿名称和工作表名称合并成一个文本字符串,然后将该文本字符串作为VLOOKUP函数的参数传入,即可实现在多个工作簿中进行查找。
二、INDEX函数INDEX函数也是一种在多个工作簿中进行查找的常用函数。
与VLOOKUP函数不同的是,INDEX函数可以根据指定的行数和列数,返回查找结果所在的单元格的值。
要使用INDEX函数进行多个工作簿的查找,我们可以先使用MATCH函数在指定的工作簿中查找要查找的数值或文本所在的行数或列数,然后将该行数或列数作为INDEX函数的参数传入,即可返回查找结果。
三、HLOOKUP函数HLOOKUP函数与VLOOKUP函数类似,但它是按行进行查找而不是按列进行查找。
对于多个工作簿的查找,我们可以将HLOOKUP 函数与INDIRECT函数和CONCATENATE函数结合使用,实现在多个工作簿中按行进行查找的功能。
四、MATCH函数MATCH函数是一种用于查找数值或文本在指定范围内的位置的函数。
在多个工作簿中进行查找时,我们可以使用MATCH函数先在指定的工作簿中查找要查找的数值或文本所在的位置,然后将该位置作为其他函数的参数传入,实现多个工作簿的查找。
五、IF函数IF函数是一种逻辑函数,它可以根据指定的条件返回不同的结果。
Excel 2003函数速查一览一、数据库函数(·DAVERAGE·DCOUNT·DCOUNTA·DPRODUCT·DSTDEV·DSTDEVP·GETPIVOTDATA二、日期与时间函数·DATE ·DATEVaLUE·DAY·HOUR·MINUTE·MONTH·TIME·TIMEVaLUE·TODAY·YEAR·YEARFRAC三、外部函数(·EUROCONVERT·SQL.REQUEST四、工程函数(·BESSELI·BESSELJ·BESSELK·BIN2OCT·COMPLEX·CONVERT·DELTA·ERF·ERFC七、逻辑运算符·AND·FALSE·IF八、查找和引用函数17条)·ADDRESS·AREAS·CHOOSE ·HYPERLINK·INDEX·INDIRECT·ROW·ROWS·RTD九、数学和三角函数60条)·ABS·ACOS·ACOSH·ATAN2·ATANH·CEILING·COUNTIF·DEGREES·EVEN·FLOOR·GCD·INT·LOG10·MDETERM·MINVERSE ·MULTINOMIAL·ODD·PI·RADIANS·RAND·RANDBETWEEN ·ROUNDUP·SERIESSUM·SIGN·SQRTPI·SUBTOTAL·SUM·SUMX2MY2·SUMX2PY2·SUMXMY2十、统计函数(80条)·AVEDEV·AVERAGE·AVERAGEA·CHIDIST·CHIINV·CHITEST ·COUNTA·COUNTBLANK·COUNTIF ·EXPONDIST·FDIST·FINV ·FREQUENCY·FTEST·GAMMADIST ·GROWTH·HARMEAN·HYPGEOMDIST·LINEST·LOGEST·LOGINV·MEDIAN·MIN·MINA ·NORMSINV·NORMSDIST·NORMSINV ·PERMUT·POISSON·PROB·SKEW·SLOPE·SMALL·STDEVP·STDEVPA·STEYX ·TRIMMEAN·TTEST·VAR ·WEIBULL·ZTEST十一、文本和数据函28条)3函数速查一览函数(13条)·DGET·DMAX·DMIN·DSUM·DVAR·DVARP间函数(20条)·DAYS360·EDATE·EOMONTH ·NETWORKDAYS·NOW·SECOND ·WEEKDAY·WEEKNUM·WORKDAY函数(2条)函数(39条)·BESSELY·BIN2DEC·BIN2HEX·DEC2BIN·DEC2HEX·DEC2OCT·GESTEP·HEX2BIN·HEX2DEC。
Excel常用的20个查找与引用函数及用法1. VLOOKUP():垂直查找某个值在表格中的位置并返回对应的值。
2. HLOOKUP():水平查找某个值在表格中的位置并返回对应的值。
3. INDEX():返回某个区域或数组中指定位置的值。
4. MATCH():查找某个值在区域或数组中的位置。
5. OFFSET():返回基于给定的起始位置和偏移量的新区域。
6. ADDRESS():返回特定单元格的地址。
7. CHOOSE():基于特定条件选择相应的值。
8. INDIRECT():返回以文本形式表示的单元格地址的值。
9. AREAS():返回区域中单元格数目的数量。
10. COLUMN():返回单元格所在的列号。
11. ROW():返回单元格所在的行号。
12. COUNTIF():计算符合特定条件的单元格数量。
13. SUMIF():计算符合特定条件的单元格汇总值。
14. AVERAGEIF():计算符合特定条件的单元格平均值。
15. MAX():返回给定区域内的最大值。
16. MIN():返回给定区域内的最小值。
17. LARGE():返回给定区域内的第n个最大值。
18. SMALL():返回给定区域内的第n个最小值。
19. COUNTBLANK():计算给定区域内的空单元格数量。
20.IF():基于特定条件返回不同的值。
用法:1. VLOOKUP(要查找的值, 表格区域, 返回值所在列数, 是否按近似匹配)2. HLOOKUP(要查找的值, 表格区域, 返回值所在行数, 是否按近似匹配)3. INDEX(数组或区域, 行号, 列号)4. MATCH(要查找的值, 数组或区域, 是否按近似匹配)5. OFFSET(基准单元格, 行偏移量, 列偏移量, 返回区域的行数, 返回区域的列数)6. ADDRESS(行号, 列号)7. CHOOSE(条件序号, 值1, 值2, ...)8. INDIRECT(以文本形式表示的单元格地址)9. AREAS(区域)10. COLUMN(单元格)11. ROW(单元格)12. COUNTIF(区域, 符合条件的值)13. SUMIF(区域, 符合条件的值, 求和的区域)14. AVERAGEIF(区域, 符合条件的值, 求平均的区域)15. MAX(区域)16. MIN(区域)17. LARGE(区域, n)18. SMALL(区域, n)19. COUNTBLANK(区域)20. IF(条件, 如果条件为真返回的值, 如果条件为假返回的值)。
Excel高级技巧使用VLOOKUP函数进行快速数据查找Excel高级技巧:使用VLOOKUP函数进行快速数据查找Excel是一款功能强大的电子表格软件,广泛应用于数据分析、汇总和管理。
在大量数据处理的过程中,查找特定数据是一个常见的需求。
为了提高效率,我们可以利用Excel的VLOOKUP函数来实现快速数据查找。
本文将介绍VLOOKUP函数的用法和一些高级技巧,帮助您更好地利用Excel进行数据查找和分析。
一、VLOOKUP函数简介VLOOKUP函数是Excel中一种非常有用的查找函数,它可以在一个表格或数据范围中查找特定值,并返回与之相对应的其他列的值。
其基本语法如下:VLOOKUP(要查找的值, 要查找的范围, 返回的列数, [是否精确匹配]) VLOOKUP函数的参数解释如下:1. 要查找的值:即需要在数据范围中查找的值。
2. 要查找的范围:即需要进行查找的数据范围。
3. 返回的列数:即要返回的值所在的列,以查找范围的左侧列为基准,从1开始计数。
4. 是否精确匹配(可选):该参数用于指定是否进行精确匹配,设为FALSE或0表示进行近似匹配,TRUE或1表示进行精确匹配。
二、基本用法VLOOKUP函数的基本用法是在一个表格中查找某个特定值,并返回与之相对应的其他列的值。
假设我们有一个表格,其中包含了员工的姓名和对应的工资,我们需要根据员工姓名来查找对应的工资。
可以使用以下公式来实现:=VLOOKUP("员工姓名", 数据范围, 返回的列数, FALSE)其中,"员工姓名"为需要查找的值,数据范围为包含员工姓名和工资的表格范围,返回的列数为工资列的位置(以数据范围的左侧列为基准),FALSE表示进行精确匹配。
三、近似匹配除了精确匹配,VLOOKUP函数还可以进行近似匹配。
在某些场景下,我们可能不需要完全匹配的结果,可以使用近似匹配来查找最接近的数值或字符。
文章主题:Excel查找函数的使用方法导言Excel是一款广泛应用于各行各业的电子表格软件,其中的查找函数是帮助用户快速定位和分析数据的重要工具。
今天,我们将深入探讨Excel中几种常用的查找函数,并学习它们的使用方法,以便更高效地处理和分析数据。
一、查找函数的作用和应用场景1. VLOOKUP函数VLOOKUP函数是Excel中最常用的查找函数之一,它能够根据指定的值在数据表中进行垂直查找,并返回该值所在行的指定列的数值。
在处理大量数据时,VLOOKUP函数可以帮助用户快速定位相关信息,常用于制作报表、查询销售记录等场景。
2. HLOOKUP函数HLOOKUP函数与VLOOKUP函数类似,区别在于它是水平查找函数,能够根据指定的值在数据表中进行水平查找,并返回该值所在列的指定行的数值。
HLOOKUP函数常用于横向比较数据或查找特定日期的数据记录。
3. INDEX-MATCH函数组合INDEX-MATCH函数组合是一种强大的查找函数组合,通过使用INDEX函数和MATCH函数,可以实现灵活的多条件查找功能。
与VLOOKUP函数相比,INDEX-MATCH函数组合在处理大规模数据时更快速、更稳定,且能满足更多复杂的查找需求。
二、如何使用查找函数1. VLOOKUP函数的使用方法要使用VLOOKUP函数,首先需要了解其基本语法:=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])。
其中,lookup_value为要查找的值,table_array 为要进行查找的数据表,col_index_num为要返回数值的列数,range_lookup为近似匹配类型,一般设置为FALSE。
下面是一个简单的例子:```=VLOOKUP(A2, Sheet2!A1:B100, 2, FALSE)```2. HLOOKUP函数的使用方法HLOOKUP函数的基本语法与VLOOKUP类似,只是将垂直查找改为水平查找。
excel根据条件查找结果的函数Excel中拥有许多强大的函数,帮助用户快速准确地处理和分析大量数据。
其中,根据条件查找结果的函数可帮助用户筛选数据集合,以便找到满足特定条件的数据。
本文将一步一步回答关于Excel根据条件查找结果的函数的问题,帮助读者更好地理解和运用这些函数。
一、什么是根据条件查找结果的函数?根据条件查找结果的函数是Excel中的一类函数,其目的是根据用户指定的条件,在给定的数据集合中查找并返回满足条件的数据。
这些函数通常以条件为输入,并返回满足条件的单个值、多个值、计数、平均值等结果。
二、常见的根据条件查找结果的函数有哪些?在Excel中,常见的根据条件查找结果的函数包括IF函数、VLOOKUP函数、MATCH函数、INDEX函数以及SUMIFS函数等。
下面将逐一介绍它们的用法和示例。
1. IF函数IF函数是一种条件函数,用于根据指定的条件返回不同的结果。
它的基本语法为:IF(条件,结果1,结果2)其中,条件是用户希望判断的表达式或比较值,结果1是当条件为真时返回的值,结果2是当条件为假时返回的值。
示例:假设我们有一个学生考试成绩表格,其中A列为学生姓名,B列为考试成绩。
我们希望根据学生的成绩是否达到及格(大于等于60分),将结果标记为"及格"或"不及格"。
在C2单元格中输入以下公式,并拖动填充至C7单元格:=IF(B2>=60,"及格","不及格")2. VLOOKUP函数VLOOKUP函数是一种基于垂直查找的函数,用于在指定的表格范围内定位并返回与特定值匹配的数据。
它的基本语法为:VLOOKUP(要查找的值,查找区域,返回列数,[是否精确匹配]) 其中,要查找的值是需要匹配的值,查找区域是指定的数据表格,返回列数是需要返回的数据所在的列号,是否精确匹配是一个逻辑值,用于指定是否需要精确匹配。
查找整个工作簿里的某个内容对于Excel用户来说是一个常见的需求。
在处理大量数据的情况下,用户常常需要快速准确地找到特定内容所在的位置。
幸运的是,Excel 提供了一种强大的函数来实现这一目的,即“查找”函数。
一、“查找”函数的基本用法1. “查找”函数是 Excel 中非常常用的函数之一,其语法如下:=查找(要查找的内容, 查找范围, 开始位置)其中,“要查找的内容”为要查找的内容或单元格引用,“查找范围”为要在其中查找的范围,“开始位置”为可选参数,表示从哪个位置开始查找。
2. 要在 A 列中查找“苹果”这个单词,可以使用以下公式:=查找("苹果", A:A, 1)这样,Excel 会返回“苹果”在 A 列中第一次出现的位置。
二、在整个工作簿中查找内容有时候,用户希望在整个工作簿中查找某个内容所在的位置。
这时,可以使用“查找”函数配合其他函数来实现。
1. 用户可以通过“合并工作簿”功能将多个工作表合并成一个工作表。
然后再在合并后的工作表中使用“查找”函数进行查找。
2. 另外,用户还可以使用“INDIRECT”函数和“CONCATENATE”函数来动态构建查找范围,实现在多个工作表中查找内容的目的。
三、利用“条件格式化”快速定位内容位置除了使用“查找”函数外,用户还可以利用 Excel 中强大的“条件格式化”功能来快速定位内容所在的位置。
1. 用户可以通过“条件格式化”功能将要查找的内容进行高亮显示。
这样,用户在浏览工作表时可以快速地定位到目标内容。
2. 用户还可以利用“条件格式化”功能将与目标内容相关的其他信息进行标注,以进一步帮助定位。
四、使用“查找和替换”功能除了“查找”函数之外,Excel 还提供了“查找和替换”功能来帮助用户查找内容。
1. 用户可以通过“编辑”菜单中的“查找”功能来查找指定内容所在的位置。
2. 如果用户需要在整个工作簿中进行查找,可以选择“查找和替换”功能,并在“选项”中选择“在工作簿中查找”。
1,row、column函数
row(reference),返回(单元格或区域)的行号。
Row()没有参数,表示返回当前所在行数
column(reference),返回单元格或区域的列号。
Lolumn()没有参数,表示返回当前所在列数
rows(array)计算行数
columns(array)计算列数
row(A1)竖着拉
c olumn(A1)横着拉
row(1:5)={1;2;3;4;5}
column(A:E)={1,2,3,4,5}
2,vlookup进阶
vlookup的第三参数可以是数组,即与row函数结合使用,效果很好
{=vlookup("B4",B$8:M$17,row(B8:B17))}
3,hlookup按行查找函数
hlookup(要查找的值,查找的区域,返回的行号,查找方式)
hlookup(lookup_value,table-array,row_index_num,range_lookup)
=HLOOKU[(B$21,B$8:M$17,ROW()-20,)
4.lookup函数
lookup(lookup_value,lookup_vector,result-vector)=(要查找的值,查找值所在行/列,
(1)向量用法:既可以按行查找,也可以按列查找,但只能是单行单列
第二参数的内容必须按升序排列,否则可能出错(不论有没有排序,该函数都会认为是按升序排列)
示例1:按列查找
姓名性别籍贯年龄姓名籍贯年龄
陈青女广东中山29李启秀湖南永州40
陈秀峰男湖北宜昌32
胡苹男江苏南通28按行查找
李梅女江西上饶29123
李启秀女湖南永州40A B C
刘思燕女云南大理34B=LOOKUP(2,F30:H30,F31:H31)
王生军男四川成都28C=LOOKUP(100,F31:H31,F32:H32)
张三林男山西大同33没有100,但是函数会找到比100小的最接近它的值,示例2:找出最后一次的销售数量
日期品名数量
2012/5/5A86
2012/5/9A5489=LOOKUP(9E+307,A38:A42,C38:C42)
2012/5/7B56说明1: 9E+307是excle中最大的数字
2012/5/11E43说明2: 第二参数没有升序排列,但是lookup函数认为它是2012/5/4C89所以它认为最后一个日期2012/5/4是最大的数字,找示例3:找出品名为A,型号为Ⅱ的最后一次销售数量
品名型号数量
AⅡ65多条件查找最后一个值
AⅢ564185=LOOKUP(1,0/(A45:A50="A")*(B45:B50="Ⅱ"),C45: BⅡ365说明:LOOKUP(1,{0;0;#DIV/0!;0;0;#DIV/0!},C45:C50)
AⅠ56 lookup可以忽略错误值,所以找到最后一个0最接近1,AⅡ185
CⅡ9
(2)数组用法
lookup(lookup_value,array)
数组的第一列不管是数字还是文本都必须按升序排列
lookup数组用法,按第一列(行)查找,返回最后一列(行)
示例:
姓名性别籍贯年龄姓名籍贯
张三林男山西大同33王生军四川成都
李启秀女湖南永州40=LOOKUP(F56,A56:C60)
王生军男四川成都28按姓名查找,然后返回数列的最后一列籍贯
陈秀峰男湖北宜昌32
李梅女江西上饶29
5,choose函数
choose(index_num,value1,value2,...)
1=<index_num<=29
第一参数是小数,则会被截尾取整
示例1:12345
姓名性别逻辑值单复姓称呼3
赵玉晓女FALSE1赵先生FALSE
徐翔男FALSE1徐先生FALSE
欧阳丽丽女TRUE2欧阳女士TRUE
王洪成男FALSE1王先生FALSE
吴娟红女FALSE1吴女士FALSE
司马相如男TRUE2司马先生TRUE
令狐冲冲男TRUE2令狐女士TRUE
示例2:
姓名成绩级别
张167中>=90优
张289良>=80良
张339差>=60中
张491优<60差
张585良
张677中
张710差
6,match查找匹配函数
match(lookup_value,lookup_array,,match_type),该函数返回的是被查找值的位置
第三参数
0精确匹配任何顺序查找匹配等于lookup_value的值的位置
1模糊匹配升序查找匹配(等于或小于loolup_value)的最大值
-1模糊匹配降序查找匹配(等于或大于lookup_value)的最小值
省略如果省略match_type,则假设为1
示例:
赵玉晓3282403=MATCH(30,I95:I99,-1)
徐翔2933
欧阳丽丽3232
王洪成3329
吴娟红4028
注意区别它们查找的结果0
match(value,array,0)=match(value,array,)011
match(value,array,1)=match(value,array)044
7,index函数
i ndex(array,roe_num,column_num)
引用的用法哈哈哈哈=B173:INDEX(B173:B179,MATCH(E173,A173:A179,),)
姓名语文数学历史政治英语
赵玉晓5395527963
徐翔7477689398row8
欧阳丽丽9987897859column6
王洪成9552806551
吴娟红976887676872=INDEX(A103:F115,I10司马相如5073635869
令狐冲冲7289745293
张三林7069579972
李启秀9680586572
王生军6152607594
姚旭5377766361
邓修银7861506369
朱银云5383756797
(1)如果数组只包含一行或一列,则相对应的参数可省略
123456
赵玉晓徐翔欧阳丽丽王洪成吴娟红司马相如
=INDEX($A$103:$A$115,COLUMN(A1))
=INDEX($A$103:$A$115,COLUMN())
123456
赵玉晓5395527963
当省略第二参数时,不能直接什么都不写,否则第三参数将被看做第二参数,可以打逗号空出来
=INDEX($A$103:$F$103,,COLUMN())
(2)如果数组有多行和多列,但只使用row_num或column_num,函数index返回整行整列,且返回值是数徐翔=INDEX(A103:F115,2,)={"徐翔",74,77,68,93,98}
8,offset函数
offset(reference,rows,cols,height,width)
以指定的引用为参照系,通过给定偏移量得到新的引用,返回的引用
可以为一个单元格或单元格区域。
并可以指定返回的行数或列数。
注意:如果省略height或width,则其高度或宽度与reference
示例:计算最好三天的平均销售价
日期产品价格
2009/10/2苹果8
2009/10/3苹果9花生
2009/10/4苹果4 4.33333
2009/10/5苹果3
2009/10/6桃子8
2009/10/6桃子10
2009/10/8桃子6
2009/10/9桃子10
2009/10/9花生8
前所在行数
回当前所在列数
/列,要返回的行/列)
按升序排列)
0,F31:H31)
H31,F32:H32)
比100小的最接近它的值,即找3对应的C 但是lookup函数认为它是按升序排列的,12/5/4是最大的数字,找到对应的值89。
)*(B45:B50="Ⅱ"),C45:C50)
#DIV/0!},C45:C50)
找到最后一个0最接近1,跳过最后的错误值的最后一列籍贯
=INDEX(A103:F115,I104,I105)
以打逗号空出来
整行整列,且返回值是数组。