假设上表为SHEET1
在SHEET2的B1中输入
小明
在B2中输入或复制粘贴下列公式
=HLOOKUP(B$1,SHEET1!$1:$4,ROW(A2),0)
下拉填充
INDEX函数用于交叉引用查找数据,它返回指定的行与列交叉处的单元格数值,有引用形式和数组形式两种用法,引用形式可以有多个不连续的区域,数组形式通常返回数值或数值数组,下面来说一下它们的具体用法。
方法/步骤
如上图,应用index函数查找某一货物所在的库位及数量。在index引用形式下,其语法格式为:INDEX(reference,row_num,column_num,area_num) reference 是对一个或多个单元格区域的引用,如本例,只引用了一个单元格区域A2:D9,也就是要查找的区域范围,Row_num 所在区域的行号,Column_num 所在区域的列号,如本例中两个公式所示。
如上图,在这个例子中,公式用到了INDEX函数的第4个参数:area_num, 它指的是:选择引用中第几个区域,如本例中,A15=INDEX((A2:D9,C2:D9),4,2,2),引用中有两个区域,分别是:A2:D9和C2:D9,area_num 设置的是2,即引用第二个区域:C2:D9,它表明要返第2个区域中 row_num 和 column_num 的交叉处的数值。选中或输入的第一个区域序号为 1,第二个为 2,以此类推。如果省略 area_num,则函数 INDEX 使用区域 1。
注意:多个 区域应用()括起来。
其实要实现这一功能,可以用VLOOKUP 函数代替。
下面来说一下INDEX函数的数组形式。
语法格式:INDEX(array,row_num,column_num)
Array 为单元格区域或数组常量。
如果数组只包含一行或一列,则相对应的参数 row_num 或 column_num 为可选参数。
如果数组有多行和多列,但只使用 row_num 或 column_num,函数 INDEX 返回数组中的整行或整列,且返回值也为数组。
Row_num 数组中某行的行号,函数从该行返回数值。如果省略 row_num,则必须有 column_num。
Column_num 数组中某列的列标,函数从该列返回数值。如果省略 column_num,则必须有 row_num。
如上图:用到的即是INDEX的数组形式,查询库区A内货物的明细信息。
公式解析:B15=INDEX(B2:E9,IF(E2:E9=B11,ROW(E2:E9)-1,65535),1)
首先这是一个数组公式,写完函数应用shift +ctrl +enter 三键输入!
IF(E2:E9=B11,ROW(E2:E9)-1,65535),指如果库位与B11中一致,则显示为该行所在行号并减1,因为表格有表头占用了第一行,因此就要减去1,如果库位不同就显示 65535。
因此IF 函数返回的结果是{1,2,3,4,65535,65535,65535,65535}
INDEX(B2:E9,IF(E2:E9=B11,ROW(E2:E9)-1,65535),1) 把区域B2:E9,中第1,2,3,4行第1列的数值显示出来,就得到了结果
由于65535中无数值因此后面的都显示错误。
其它2个公式类同。
使用VLOOKUP函数。
在Excel中先建好一个表格,上面输入所有员工的名字、工号等数据。(假设这个表格取名“员工目录表”)
按你的要求,名字要排在第一列。
如图。
在另一个表格中的单元格输入函数=VLOOKUP(B2,员工目录表!$A$1:$D$3135,2,FALSE)
当在A2单元格输入“张三”时,B2单元格就会自动出来工号等你选定的“张三”的资料。
直接写=sheet1!A1即可
ISNA(value)判断值是否为错误值 #N/A(值不存在)。
VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
在表格或数值数组的首列查找指定的数值,并由此返回表格或数组当前行中指定列处的数值。
Lookup_value 为需要在数组第一列中查找的数值。Lookup_value 可以为数值、引用或文本字符串。
Table_array 为需要在其中查找数据的数据表。可以使用对区域或区域名称的引用,例如数据库或列表。
Col_index_num 为 table_array 中待返回的匹配值的列序号。Col_index_num 为 1 时,返回 table_array 第一列中的数值;col_index_num 为 2,返回 table_array 第二列中的数值,以此类推。如果 col_index_num 小于 1,函数 VLOOKUP 返回错误值值 #VALUE!;如果 col_index_num 大于 table_array 的列数,函数 VLOOKUP 返回错误值 #REF!。
Range_lookup 为一逻辑值,指明函数 VLOOKUP 返回时是精确匹配还是近似匹配。如果为 TRUE 或省略,则返回近似匹配值,也就是说,如果找不到精确匹配值,则返回小于 lookup_value 的最大数值;如果 range_value 为 FALSE,函数 VLOOKUP 将返回精确匹配值。如果找不到,则返回错误值 #N/A。
例如=VLOOKUP(A2,Sheet2!A:B,2,FALSE)
以上就是关于Excel数据库建立和引用的问题全部的内容,包括:Excel数据库建立和引用的问题、excel查找 引用多列数据的问题、如何在EXCEL中使用数据库等相关内容解答,如果想了解更多相关内容,可以关注我们,你们的支持是我们更新的动力!
欢迎分享,转载请注明来源:内存溢出
评论列表(0条)