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
然后确认。