Excel求高手指点 表格涵数 另一页显示需要内容

用宏实现：

Sub 查找项目()

Dim fruitrow As Long
Dim fruitcol As Long
fruitrow = Sheets("sheet1").Cells(rows.Count, 1).End(xlUp).Row
fruitcol = Sheets("sheet1").Cells(1, Columns.Count).End(xlToLeft).Column

Dim dateindex, typeindex, placeindex, weightindex, priceindex As Integer
For i = 1 To fruitcol
If Sheets("sheet1").Cells(1, i) = "日期" Then
dateindex = i
End If
If Sheets("sheet1").Cells(1, i) = "品类" Then
typeindex = i
End If
If Sheets("sheet1").Cells(1, i) = "产地" Then
placeindex = i
End If
If Sheets("sheet1").Cells(1, i) = "重悉渣量" Then
weightindex = i
End If
If Sheets("sheet1").Cells(1, i) = "金额" Then
priceindex = i
End If
Next i
Range(Sheets("sheet1"睁租悄).Cells(3, 8), Sheets("sheet1").Cells(fruitrow, typeindex).Offset(0, 10)).Clear
For i = 2 To fruitrow
If Sheets("sheet1").Cells(1, 8) = Sheets("sheet1").Cells(i, typeindex) Then
Range(Sheets("sheet1").Cells(i, typeindex).Offset(0, -1), Sheets("sheet1").Cells(i, typeindex).Offset(0, 3)).Copy Sheets("sheet1").Cells(i + 1, typeindex).Offset(0, 6)
End If
Next i
Range(Sheets("sheet1").Cells(3, 8), Sheets("sheet1").Cells(fruitrow, typeindex).Offset(0, 10)).Select
Dim r As Range, tmp As Range
Set r = Intersect(ActiveSheet.UsedRange, Selection)
For i = r.Cells.rows.Count To 1 Step -1
For j = r.Cells.Columns.Count To 1 Step -1
Set tmp = r.Cells(i, j)
If tmp.Value = "型枝" Then
tmp.Delete xlShiftUp
End If
Next j
Next i


End Sub

这个构建一个辅助列判定李卖起来会比较简嫌侍单一点，

首先在F列F2单元格输入以下公哪者逗式向下填充

=(B2=$H$1)+F1

然后再自动显示区域输入以下公式填充就行。

=IFERROR(INDEX($A:$E,MATCH(ROW(A1),$F:$F,0),MATCH(H$2,$A$1:$E$1,0)),"")