2024年1月27日发(作者：)

湖北蕲春县实验高级中学朱中良 zhu421126@

EXCEL中查询数组公式

“INDEX-SMALL-IF-ROW-ROW”的应用举例

朱中良

（湖北省蕲春县实验高中，436300）

摘要：通过图书借阅查询设计案例说明“INDEX-SMALL-IF-ROW-ROW”数组公式的应用。

关键词：excel函数查询公式；“INDEX-SMALL-IF-ROW-ROW”一、设计目标

设计四张表,第一张表是借书证办理登记表;第二张表是学校图书编码库表;第三张表是所有人员的借阅登记表;第四张表是查询表,要求在查询表中输入某人的借书证号便可以查询到这个人的所有借阅图书情况和相关信息.(如图所示)

1

湖北蕲春县实验高级中学朱中良 zhu421126@

二、设计依据

利用数组公式“INDEX-SMALL-IF-ROW-ROW”查询功能。

下面以公式=INDEX(B:B,SMALL(IF(A$2:A$8=E$1,ROW(A$2:A$8),4^8),ROW(1:1)))&""为例进行解读：

1、引用模块：INDEX(引用列，SMALL得到的行号)如：INDEX(B:B,2)——表示引用单元格B2；INDEX(B:B,65536)——表示引用单元格B65536

2、条件模块：IF(A$2:A$8=E$1,ROW(A$2:A$8),4^8)

上式中，如果A2:A8的姓名等于E1的“张三”，则返回A2:A8的行号，否则返回4^8，即65536。(当然我们也可以不用此65536，比如用20000，也行)。IF（如果，那么，否则）条件模块经过数组运算，若A2、A5、A8满足条件，所以返回的是行号数组{2;65536;65536;5;65536;65536;8}。

3、排序模块：SMALL(IF,ROW(1:1))

公式的第1行，ROW(1:1)返回{1}，在第2行返回{2}……因此，利用2

湖北蕲春县实验高级中学朱中良 zhu421126@

SMALL+ROW可以将条件模块返回的行号数组从小到大依次排序得出。当然，此例中SMALL({2;65536;65536;5;65536;65536;8},1)得到第1小的是2，第2小的是5，第3小的是8，第4小及以后都是65536。

4、容错模块：&""

当公式到了第4行，3个满足条件的记录都已经找出来，此时公式是=INDEX(B:B,65536)&“”,因为Excel2003的最大行数是65536行，而在这一行中，一般不会有人输入数据，是空单元格。因此，利用空单元格与空文本合并返回空文本的特性，将超出结果数量的部分不显示出来。

5、Excel中SMALL函数的用法是：返回数据集中第 k 个最小值。使用此函数可以返回数据集中特定位置上的数值。SMALL函数的语法是：SMALL(array,k)

SMALL函数的参数说明：第一，Array：为需要找到第 k 个最小值的数组或数字型数据区域。第二，K：为返回的数据在数组或数据区域里的位置（从小到大）。SMALL函数使用需注意：如果 array 为空，函数 SMALL 返回错误值

#NUM!。 如果 k ≤ 0 或 k 超过了数据点个数，函数 SMALL 返回错误值

#NUM!。 如果 n 为数组中的数据点个数，则 SMALL(array,1) 等于最小值，SMALL(array,n) 等于最大值。

Excel中SMALL函数和LARGE函数是一对相反的函数，都是属于excel的排名函数，SMALL函数是返回排名从小到大的值，LARGE函数是返回排名从大到小的值。

三、解决办法

图书借阅查询表（四）所用系列公式如下：

1、查询表！C2单元格中的公式是：=VLOOKUP(F2,借书证与姓名!B:F,3,0)；

查询表！I2单元格中公式是：=VLOOKUP(F2,借书证与姓名!B:F,2,0)。这里用到了VLOOKUP函数。

2、查询表！B4单元格公式是：=IF(H4<>"",ROW(B4)-3,"")，如果有记录，自动编录流水号，编号为行号减3;

3

湖北蕲春县实验高级中学朱中良 zhu421126@

3、查询表！C4单元格公式是：{=INDEX(借阅情况表!H:H,SMALL(IF(借阅情况表!$C:$C=查询表!$I$2,ROW(借阅情况表!$C:$C),4^8),ROW(1:1)))&""}

这里对公式解释一下：

(1)IF(借阅情况表!$C:$C=查询表!$I$2,ROW(借阅情况表!$C:$C),4^8)

得到的是一组数据。判断借阅情况表!$C:$C中，哪些单元格中数据=查询表!$I$2的数据（如等于42112614001）；如果借阅情况表!$C:$C等于查询表!$I$2中的数据，则返回借阅情况表!$C:$C所在行号，不等于则返回一个较大的数4^8；于是得到一个数据组{3，65536，65536，65536，65536，65536，65536，65536，9，65536，65536，12，65536，65536，〃〃〃}

（2）SMALL((3，65536，65536，65536，65536，65536，65536，65536，9，65536，65536，12，65536，65536),ROW(1:1))返回第ROW(1:1)=1小的数3；

(3)在C4单元格中公式INDEX(借阅情况表!H:H,3),返回数据列借阅情况表!H:H第3行=“小王”；

（4）在C5单元格中公式 INDEX(借阅情况表!H:H,9),（因为9是数据中第ROW(2:2)=2小的数9，故返回数据列借阅情况表!H:H第9行；

查询表C列其它单元格公式类推。当出现65536时，此行没有数据，用&""返回空文本；

4、查询表！D4单元格公式是：=TEXT(E4,"e-m-d")这是一个日期格式公式；在查询表中，这里隐藏了一个数据列E. 如果E4单元格中是42275，这出现五位数字转为年月日，下面我提供两种方法来解决，方法一：D4=TEXT(E4,”e-m-d”)

则显示2015-9-28；方法二：D4=text(E4,”e年m月d日”)则显示为2015年9月28日。在制作表采用隐藏方式，不影响表格的美观。

5、查询表！F4单元格公式是：{=INDEX(借阅情况表!F:F,SMALL(IF(借阅情况表!$C:$C=查询表!$I$2,ROW(借阅情况表!$C:$C),4^8),ROW(1:1)))&""}

查询表！F5单元格公式是：=｛INDEX(借阅登记表!F:F,SMALL(IF(借阅登记表!$C:$C=查询表!$I$2,ROW(借阅登记表!$C:$C),4^8),ROW(2:2)))&""｝

4

湖北蕲春县实验高级中学朱中良 zhu421126@

查询表！F6单元格公式是：= ｛INDEX(借阅登记表!F:F,SMALL(IF(借阅登记表!$C:$C=查询表!$I$2,ROW(借阅登记表!$C:$C),4^8),ROW(3:3)))&""｝。

6、查询表！G4单元格公式是：{=INDEX(借阅情况表!E:E,SMALL(IF(借阅情况表!$C:$C=查询表!$I$2,ROW(借阅情况表!$C:$C),4^8),ROW(1:1)))&""}

7、查询表！H4单元格公式是：{=INDEX(借阅情况表!G:G,SMALL(IF(借阅情况表!$C:$C=查询表!$I$2,ROW(借阅情况表!$C:$C),4^8),ROW(1:1)))&""}

其它单元格公式不再赘述。

8、关于勾选项的设置方法

先设置勾选系列数据；再打开数据---数据验证---数据验证----允许值(A)---序列---来源---进入数据范围区域选项,选定数据范围。有了勾选项，在查找数据是很方便的。

四、补充说明

“INDEX-SMALL-IF-ROW-ROW”数组公式在查询“一对一”或“一对多”的数据记录大有用武之地，如：单位人事管理，学校学生成绩查询，它具有解决其它查询函数所不能实现查询功能。

参考文献：

1、/s/blog_；

2、《EXCEL2010函数与公式》陈国良 荣胜军 黄朝阳 电子工业出版社

5