查找所有table中的最大值_excel之查找与引用函数的实际操作1

以前一直觉得excel很简单，后来深入学习发现excel真的是很有用，今天总结一下excel里常用的查找与引用函数。

1.vlookup函数 （lookup_value,table_array,col_index_num,[range_lookup]）

查找值一般在范围的第一列，只能从左往右查

参数1：查找值

参数2：查找区域

参数3：返回查找区域内第几列的数据

参数4：精确匹配/F 还是模糊匹配/T

例：查找各学生数学课的成绩

查找值：姓名B2

查找范围：B10：C14 固定范围需要绝对引用

返回数据：数学成绩在查找范围的第二列，返回2

精确查找：F

2.offset引用函数 --offset（reference，rows，cols，[height],[width]）

以指定的引用为参照系，通过给定偏移量得到新的引用。返回的引用可以为一个单元格/区域

参数1：基点 可随机指定

参数2：行偏 基点与偏移后的基点的偏差 A3与C5的行偏为2

参数3：列偏 A3与C5的列偏为2

参数4：行高 需要偏移后的区域C5:E7 行高为3

参数5：列高 列高为3

注意：如果是多行多列，必须选中足够的区域，按下ctrl+shift+enter

例：将A12：C17区域的数据移动到E18开始的单元格

3.match函数--match(lookup_value,lookup_array,[match_type])

知道这个值是多少，查它的位置

返回一个值的相对位置(数字)

参数1： 查找的内容

参数2：查找的范围

参数3： 1--小于（向下查找最大值），0--精确匹配，-1--大于（向上查找最小值）

例：查找10在区域内的位置

=MATCH(A3,A1:A8,0) 10在该区域内的位置为3，则返回值3

查找两列数据中的相同个数

返回结果为4

4.index---index（array，row_num,[column_num]）

给定这个位置，查它的值是多少

参数1：范围

参数2：相对行号

参数3：相对行号

例：查找A12：A19区域中第五行第一列的数据，位置是在该区域的相对位置。

indirect函数 找到单元格的地址 INDIRECT(ref_text, [a1])

5.累计函数

例：根据日销量得到累计销量

1.sum求和 SUM($B$2:B2)

2.offset求累计 问题：如何确定行高

ROW()引用行的数据 / COLUMN()引用列的数据

SUM(OFFSET($B$2,0,0,DAY(A2),1))

SUM(OFFSET($B$2,0,0,ROW(A1),1))

SUM(OFFSET($B$2,0,0,COUNT($B$2:B2),1))

6.案例分析

对员工入职表工龄及地区进行填充

表1为员工的信息表

表2为各地区对应的代码

1.根据身份证号得出员工出生日期----截取文本

mid（） 从字符串指定位置截取指定长度 text（）将数值转换为文本形式

函数公式：TEXT(MID(D2,7,8),"#-00-00")*1 *1是将文本转化为日期

2.根据出生日期计算员工年龄

函数：DATEDIF(start_date,end_date,unit)

Start_date 为一个日期，它代表时间段内的第一个日期或起始日期。(起始日期必须在1900年之后)

End_date 为一个日期，它代表时间段内的最后一个日期或结束日期。

Unit 为所需信息的返回类型。

计算公式：datedif(G2,TODAY(),"y")

3.根据省份证得到所属省/自治区/直辖市

计算公式：vlookup(left(D2,2),全国地区表！A:B,2,F)

遇到的问题 只输入上面的公式一直报错，不知道问题是什么，后来发现是因为left截取的是文本，在函数中找值找不到，后来将文本改为数值型就可以了。

VLOOKUP(VALUE(LEFT(D2,2)),全国地区表!A:B,2,FALSE)

4.计算员工工龄

datedif（E2，today（），“y”）

5.计算不足一年的工龄

DATEDIF(E2,TODAY(),"ym")