excel中如何应用函数将符合条件的全部列出来。

1、首先打开excel表格，如下图，左边是某年级学生信息表，现在想要按班级提取相应的学生姓名。

2、先在班级前插入一列辅助列。

3、在新插入的辅助列输入中公式=B2&COUNTIF($B$1:B2,B2)，并下拉到底。

4、在E2单元格输入公式=IFERROR(VLOOKUP(E$1&ROW(A1),$A:$C,3,0),"")，并下拉直至出现空单元格。

5、最后把公式向右拉，然后向下填充，即可录入各班级对应学生姓名。

在sheet2表的b1输入公式:

=index(sheet1!b:b,small(if(sheet1!$a$1:$a$100=$a$1,row($1:$100),4^8),row(a1)))&""

数组公式,按ctrl+shift+enter三键结束

右拉填充,下拉填充

-----当你在sheet2表的a1输入品种名称时,下面将自动显示查询情况.

若sheet1表的记录较多,可把公式中的100改成更大数值,根据实际情况修正.

如图

假定A列有100行数据，B列有200行数据，均从A1开始，要求把A列中包含在B列的数据列出来。
公式：
=index(a:a,small(if(countif(b$1:b$200,a$1:a$100),row($1:$100),4^8),row(a1)))&"" 该公式是数组公式，需要按组合键ctrl+shift+enter结束公式，下拉填充。
说明：
1、if与countif函数配合将符合条件的赋予自然数序列，对不符合条件的赋值为655336
2、用small函数对符合条件的编号按从小到大的顺序排列。
3、用index函数配合row函数依次将符合条件的值一一列出。

这个用VBA实现非常的方便，
可以在你要输入品种的单元格自动得到表一品种的下拉序列，可以选择品种即可，当选择品种时，自动得到结果。
不知道你会使用VBA吗？
代码如下
Private Sub Worksheet_Change(ByVal Target As Range) '这是选择品种后自动生成数据的
Dim arr, brr(), x&, i&, y&, str1$
If Target.Address = "$A$1" Then
str1 = Target.Value
arr = Sheet1.UsedRange
For x = 2 To UBound(arr)
If arr(x, 1) = str1 Then
i = i + 1
ReDim Preserve brr(1 To UBound(arr, 2) - 1, 0 To i)
For y = 2 To UBound(arr, 2)
brr(y - 1, i) = arr(x, y)
Next y
End If
Next x
For y = 2 To UBound(arr, 2)
brr(y - 1, 0) = arr(1, y)
Next y
Range("B1:E65536").ClearContents
Range("B1").Resize(UBound(brr, 2) + 1, UBound(brr)) = Application.Transpose(brr)
End If
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range) '这是自动生成品种的下拉菜单的
Dim d As Object
Dim arr, x&
Set d = CreateObject("scripting.dictionary")
If Target.Address = "$A$1" Then
arr = Sheet1.UsedRange
For x = 2 To UBound(arr)
d(arr(x, 1)) = ""
Next x
With Target.Validation
.Delete
.Add Type:=xlValidateList, Formula1:=Join(d.keys, ",")
End With
End If
End Sub

如果不会，你也可以用高级筛选完成

高级筛选

在表中A1和B1写入两个条件
A1 品种 接货人
A2 苹果 张三

然后点高级筛选

1.将数据Copy到另一个位置
2.数据区域，选择表1的数据区域
3.筛选条件选择表2中的A1到B2
4.copy的数据位置，选择表2的C1
然后确认。