筛选表以在Excel中创建另一个表 [英] Filtering table to create another table in Excel
问题描述
我需要从满足条件的表中接收值.例如,我有名字,我需要从出现名字的表中获取所有值.例如,表格由日期,名称,支出组成:
I need to receive values from a table where a condition is met. For example I have names and I need to take all values from a table where the name appears. For example the table consists of date, name, expenditures:
Date Name Expenditures
2019-01-28 Sara 2.45
2019-04-26 John 32.67
2019-05-07 Peter 55.88
2019-06-14 Sara 62.09
2019-07-03 Sara 12.94
2019-09-30 Peter 5.64
我需要接收Sara支付的所有费用以及完成日期后的 Date
天.结果应为以下内容:
I need to receive all expenditures which has been made by Sara and the Date
days when it was done. The result should be the following:
Date Name Expenditures
2019-01-28 Sara 2.45
2019-06-14 Sara 62.09
2019-07-03 Sara 12.94
我需要将它显示为表格,因此过滤不适合此处的工作.基本上,我有一个大表,如第一个代码片段中所述,我需要创建另一个结构相同的表,但只按 Name
进行过滤.
I need this to appear as a table, so filtering does not do the job right here. Basically I have a large table as mentioned in the first code snippet and I need to create another table with the same structure but only filtering by the Name
.
推荐答案
Sub test()
Dim allData As Range, criteriaRng As Range, i As Long
Set allData = Range("A1").CurrentRegion
For i = 1 To allData.Rows.Count
If UCase(allData(i, 2)) = "SARA" Then
If criteriaRng Is Nothing Then
Set criteriaRng = allData.Range(Cells(i, 1), Cells(i, allData.Columns.Count))
Else
Set criteriaRng = Union(criteriaRng, allData.Range(Cells(i, 1), Cells(i, allData.Columns.Count)))
End If
End If
Next
criteriaRng.Copy
ActiveSheet.Paste Destination:=Worksheets("Sheet1").Range("A11")
Application.CutCopyMode = False
End Sub
这篇关于筛选表以在Excel中创建另一个表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!