excel中如何应用函数将符合条件的全部列出来。
表一中内容
品种 到货重量 接货人
苹果 300 张三
香蕉 65 李四
葡萄 45 王五
葡萄 487 张三
苹果 154 张三
香蕉 14 张三
如何在表二中某单元格输入苹果出现如下内容
苹果 300 张三
154 张三
1、首先打开excel表格，如下图，左边是某年级学生信息表，现在想要按班级提取相应的学生姓名。
2、先在班级前插入一列辅助列。
3、在新插入的辅助列输入中公式=B2&COUNTIF($B$1:B2,B2)，并下拉到底。
4、在E2单元格输入公式=IFERROR(VLOOKUP(E$1&ROW(A1),$A:$C,3,0),"")，并下拉直至出现空单元格。
5、最后把公式向右拉，然后向下填充，即可录入各班级对应学生姓名。
温馨提示：答案为网友推荐，仅供参考
第1个回答 2013-02-22
在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改成更大数值,根据实际情况修正.
如图
第2个回答 推荐于2019-02-14
假定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函数依次将符合条件的值一一列出。本回答被网友采纳
公式：
=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函数依次将符合条件的值一一列出。本回答被网友采纳
第3个回答 2013-02-22
这个用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
如果不会，你也可以用高级筛选完成
可以在你要输入品种的单元格自动得到表一品种的下拉序列，可以选择品种即可，当选择品种时，自动得到结果。
不知道你会使用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
如果不会，你也可以用高级筛选完成
第4个回答 2013-02-22
高级筛选
在表中A1和B1写入两个条件
A1 品种 接货人
A2 苹果 张三
然后点高级筛选
1.将数据Copy到另一个位置
2.数据区域，选择表1的数据区域
3.筛选条件选择表2中的A1到B2
4.copy的数据位置，选择表2的C1
然后确认。
在表中A1和B1写入两个条件
A1 品种 接货人
A2 苹果 张三
然后点高级筛选
1.将数据Copy到另一个位置
2.数据区域，选择表1的数据区域
3.筛选条件选择表2中的A1到B2
4.copy的数据位置，选择表2的C1
然后确认。
相似回答