假设一下情况：
左侧是我们的原始数据，有姓名，以及每个人对应所在的班级，但可能存在比较乱的情况(一年级，二年级，三年级杂乱排列的)
要求：
把不同的年级进行一下整理，整理成右侧的格式。
各个年级的分别显示到对应的一行当中。
方法/步骤1:
在E2单元格输入：
=INDEX($A$1:$A$9,SMALL(IF($B$1:$B$9=$D2,ROW($B$1:$B$9),4^8),COLUMN(A1)))&""
按Ctrl+shift+回车，向右拖拽公式，向下拖拽公式，即可实现一二三年级对应的人员姓名。
=INDEX($A$1:$A$9,SMALL(IF($B$1:$B$9=$D2,ROW($B$1:$B$9),4^8),COLUMN(A1)))&""
要显示的内容区域，并加以锁定$，可以通过按F4进行添加锁定符$。
在这里，我们要显示的是姓名，所以，要选择姓名部分。
=INDEX($A$1:$A$9,SMALL(IF($B$1:$B$9=$D2,ROW($B$1:$B$9),4^8),COLUMN(A1)))&""
条件区域中要求的条件。
年级区域所对应的一年级，二年级，三年级等，$D2在这里，只锁定列，是因为下拉的时候，要自动变成二年级，三年级。
=INDEX($A$1:$A$9,SMALL(IF($B$1:$B$9=$D2,ROW($B$1:$B$9),4^8),COLUMN(A1)))&""
这一节可以变成ROW($a$1:$a$9)，ROW($c$1:$c$9)，都是可以的。
=INDEX($A$1:$A$9,SMALL(IF($B$1:$B$9=$D2,ROW($B$1:$B$9),4^8),COLUMN(A1)))&""
是4的8次方，也就是一个很大的数，根据实际，只要是比你原数据还要大的行数就行，比如，你的原数据有2000行，你输入一个3000也行。
=INDEX($A$1:$A$9,SMALL(IF($B$1:$B$9=$D2,ROW($B$1:$B$9),4^8),COLUMN(A1)))&""
这一个参数，是右拉的时候，会自动调取符合条件的第几个值。
比如，符合条件为一年级的，区域在姓名列中，找到第一个姓名，右拉再找到第二个，再右拉找到第3个。依次……
=INDEX($A$1:$A9,SMALL(IF($B$1:$B9=$D2,ROW($B$1:$B9),4^8),COLUMN(A1)))&""
上面和下面的2种写法都可以
=INDEX($A$1:$A$9,SMALL(IF($B$1:$B9=$D2,ROW($B$1:$B$9),4^8),COLUMN(A1)))&""
方法/步骤2:
下面分层介绍，if返回一个一维数组，if(条件，为真则返回，为假则返回)，IF($B$1:$B9=$D2,ROW($B$1:$B9),4^8)，如果与D2单元格值相同则反馈B列单元格的行号，如果为假，则返回4^8即65536(97-03版excel单元格个数)，所以if函数最终返回值要么是指定单元格对于行号，要么是65536(数据量较大时需具体分析)
方法/步骤3:
small返回数组中第K个最小值，small(array,k)，此处将if函数返回的数组作为small的第一参数，返回数组中第COLUMN(A1)个最小值，E列为COLUMN(A1)=1，即第一个最小值，F 列为第二个最小值，以此类推，SMALL(IF($B$1:$B9=$D2,ROW($B$1:$B9),4^8),COLUMN(A1))，所以small函数最终返回的是if函数里的行号
方法/步骤4:
最后是index函数，返回行列交叉单元格的值，index(array,row_num,[column_num])，small函数作为index的第二个参数，与array即$A$1:$A9交叉处即为需要返回的值。
方法/步骤5:
&""是为了避免0值得出现，在右拉后，如果结果已全部查询完毕，可能会出现错误提示，可将公式嵌套到iferror中，=IFERROR(INDEX($A$1:$A9,SMALL(IF($B$1:$B9=$D2,ROW($B$1:$B9),4^8),COLUMN(A1)))&"","") 完成后三键齐按(ctrl+shift+回车)即可。
除了使用index配合small和if使用外，也可以使用offset配合small和if来进行使用。
|
