The named ranges were created using

The named ranges were created using the INDEX function as shown below

Named Range Formula
prd =Master!$A$2:INDEX(Master!$A:$A,COUNTA(Master!$A:$A))
rgn =Master!$B$2:INDEX(Master!$B:$B,COUNTA(Master!$B:$B))
cust =Master!$C$2:INDEX(Master!$C:$C,COUNTA(Master!$C:$C))
agnt =Master!$D$2:INDEX(Master!$D:$D,COUNTA(Master!$D:$D))
Now we need to setup the sheet where we need the filtered data to be displayed. Headings were put in cells B5 to B8 and the drop down (using the Data Validation—List) feature was put in cells C5 to C8. Now we need to create or criteria fields in the RawData sheet, this is a requirement and cannot be any place else. When you use the Advanced Filter dialog box and try to place the output onto another sheet Excel will display a message saying “You can only copy data to the Active Sheet”. We will overcome this limit by using VBA and telling Excel where to put the filtered data. I used the cells M1 to P1 to define the headings and cells M2 to P2 to get the actual options from the “Filter Sheet”

Cells Formula
M2 =Filter!C5
N2 =Filter!C6
O2 =Filter!C7
P2 =Filter!C8
Macro to run advanced filter and extract data

Sub FilterData()
Sheets("Filter").Select
Range("B10").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Clear

Sheets(“RawData”).Range(“Table1[#All]”).AdvancedFilter Action:=xlFilterCopy, CriteriaRange:= _
Sheets(“RawData”).Range(“M1:P2”), CopyToRange:=Sheets(“Filter”).Range(“B10”), Unique:=True

Columns.AutoFit
Range(“B10”).Select
End Sub

First we ensure the current filtered data (in any) is cleared out before we run the code again and then we get the new filtered data from cell B10 onwards. Now let’s understand the actual code that filters our data here.


Sheets("RawData").Range("Table1[#All]").AdvancedFilter
Action:=xlFilterCopy,
CriteriaRange:=Sheets("RawData").Range("M1:P2"),
CopyToRange:=Sheets("Filter").Range("B10"),
Unique:=True

We converted our raw data into an excel table (Structured Reference Structured Reference), by doing this we no longer need to know how many rows our data actually goes down to, the “Table1[#All]” takes care of that for us.

We also need to specify that our data is in another sheet and we are trying to run Advanced Filter on that data range, this is done using the first line ” Sheets(“RawData”).Range(“Table1[#All]”).AdvancedFilter “.

Next we specify the action that we need which is Copy in our case, the other option is “xlFilterInPlace” which would filter right on our data itself.

Then we have specified the Criteria Range (which needs to be on the same sheet where the data is).

And finally we have specified where the output has to be sent to by using : “CopyToRange:=Sheets(“Filter”).Range(“B10″)”

We have also made sure that only Unique records are returned to us by turning Unique:=True.

Kết quả (Việt) 1:

Dãy núi được đặt tên theo được tạo ra bằng cách sử dụng các chức năng chỉ mục như hình dưới đâyCông thức được đặt tên theo phạm viPRD = Master! $A$ 2: INDEX(Master!$A:$A,COUNTA(Master!$A:$A))rgn = chủ! $B$ 2: INDEX(Master!$B:$B,COUNTA(Master!$B:$B))Kh = Master! $C$ 2: INDEX(Master!$C:$C,COUNTA(Master!$C:$C))agnt = chủ! $D$ 2: INDEX(Master!$D:$D,COUNTA(Master!$D:$D))Bây giờ chúng ta cần phải thiết lập bảng mà chúng ta cần dữ liệu lọc sẽ được hiển thị. Đề mục được đặt trong tế bào B5 B8 và thả (sử dụng xác nhận dữ liệu — danh) tính năng đã được đặt trong tế bào C5 C8. Bây giờ chúng ta cần phải tạo hoặc tiêu chuẩn các lĩnh vực trong bảng RawData, đây là một yêu cầu và không thể là bất cứ nơi nào khác. Khi bạn sử dụng hộp thoại nâng cao lọc và cố gắng đặt đầu ra vào một bảng Excel sẽ hiển thị một thông báo nói rằng "Bạn chỉ có thể sao chép dữ liệu vào bảng hoạt động". Chúng tôi sẽ vượt qua giới hạn này bằng cách sử dụng VBA và nói cho Excel nơi để đặt các dữ liệu lọc. Tôi đã sử dụng các tế bào M1 để P1 để xác định các tiêu đề và các tế bào M2 để P2 để có được các tùy chọn thực tế từ các tấm lọc""Công thức tế bàoM2 = bộ lọc! C5N2 = bộ lọc! C6O2 = bộ lọc! C7P2 = bộ lọc! C8Các vĩ mô để chạy bộ lọc nâng cao và trích xuất dữ liệuPhụ FilterData()Sheets("Filter"). ChọnRange("B10"). ChọnPhạm vi (lựa chọn, Selection.End(xlToRight)). ChọnPhạm vi (lựa chọn, Selection.End(xlDown)). ChọnSelection.ClearSheets("RawData"). Range("Table1[#all]"). Hành động AdvancedFilter: = xlFilterCopy, CriteriaRange: = _Sheets("RawData"). Range("M1:P2"), CopyToRange:=Sheets("Filter"). Range("B10"), độc đáo: = TrueColumns.AutoFitRange("B10"). ChọnCuối tiểuLần đầu tiên chúng tôi đảm bảo dữ liệu lọc hiện tại (trong bất kỳ) được xóa ra trước khi chúng tôi chạy mã một lần nữa và sau đó chúng tôi nhận được dữ liệu lọc mới từ tế bào B10 trở đi. Bây giờ chúng ta hãy hiểu mã thực tế bộ lọc dữ liệu của chúng tôi ở đây.Sheets("RawData"). Range("Table1[#all]"). AdvancedFilterHành động: = xlFilterCopy,CriteriaRange:=Sheets("RawData"). Range("M1:P2"),CopyToRange:=Sheets("Filter"). Range("B10"),Độc đáo: = TrueChúng tôi chuyển đổi dữ liệu thô của chúng tôi vào một bảng excel (cấu trúc tài liệu tham khảo có cấu trúc tham chiếu), bằng cách thực hiện điều này chúng ta không còn cần phải biết làm thế nào nhiều hàng dữ liệu của chúng tôi thực sự đi xuống đến, các "Table1 [#All]" sẽ chăm sóc đó cho chúng tôi.Chúng tôi cũng cần phải chỉ ra rằng dữ liệu của chúng tôi là trong một bảng và chúng tôi đang cố gắng để chạy bộ lọc nâng cao trên phạm vi dữ liệu đó, điều này được thực hiện bằng cách sử dụng dòng đầu tiên "Sheets("RawData"). Range("Table1[#all]"). AdvancedFilter ".Tiếp theo, chúng tôi xác định các hành động mà chúng ta cần đó là bản sao trong trường hợp của chúng tôi, các tùy chọn khác là "xlFilterInPlace" mà sẽ lọc ngay trên dữ liệu của chính nó.Sau đó chúng tôi đã xác định phạm vi tiêu chuẩn (mà cần phải trên bảng cùng một dữ liệu ở đâu).Và cuối cùng chúng tôi đã xác định nơi đầu ra đã được gửi đến bằng cách sử dụng: "CopyToRange:=Sheets("Filter"). Range("B10″)"Chúng tôi cũng đã thực hiện chắc chắn rằng hồ sơ chỉ duy nhất được trả về cho chúng tôi bằng cách độc đáo: = True.

Kết quả (Việt) 2:

Những dãy tên được tạo ra bằng cách sử dụng hàm INDEX như hình dưới đây Name Range Formula PRD = Master $ A $ 2: INDEX (Master $ A:! $ A, COUNTA (Master $ A: $ A))! RGN = Master $ B $ 2: INDEX (! Thầy $ B: $ B, COUNTA (Master $ B: $ B)) cust = Master $ C $ 2:! INDEX (Master $ C: $ C, COUNTA (Master $ C: $ C ))! agnt = Master $ D $ 2: INDEX (Master $ D: $ D, COUNTA (Master $ D: $ D)) Bây giờ chúng ta cần phải thiết lập các sheet, mà chúng ta cần các dữ liệu đã lọc sẽ được hiển thị. Tiêu đề được đưa vào tế bào B5 đến B8 và thả xuống (bằng cách sử dụng Data Validation-List) tính năng được đưa vào tế bào C5 đến C8. Bây giờ chúng ta cần phải tạo ra hoặc tiêu chí lĩnh vực trong bảng RawData, đây là một yêu cầu và không thể là bất kỳ nơi nào khác. Khi bạn sử dụng hộp thoại Advanced Filter và cố gắng đặt các đầu ra vào một tờ giấy khác Excel sẽ hiển thị một thông báo nói rằng "Bạn chỉ có thể sao chép dữ liệu cho các Sheet Active". Chúng tôi sẽ khắc phục giới hạn này bằng cách sử dụng VBA và nói với Excel nơi để đặt các dữ liệu đã lọc. Tôi đã sử dụng các tế bào M1 đến P1 để xác định các tiêu đề và các tế bào M2 đến P2 để có được sự lựa chọn thực tế từ "Filter Sheet" Cells Formula M2 = Lọc! C5 N2 = Lọc C6! O2 C7 = Lọc! P2 = Lọc C8! Macro để chạy lọc dữ liệu và chiết xuất tiên tiến Sub FilterData () Sheets ("Filter"). Select Range ("B10"). Select Range (Selection, Selection.End (xlToRight)). Select Range (Selection, Hành động: = xlFilterCopy, CriteriaRange: = _ Sheets ("RawData") Range ("M1: P2")., CopyToRange:. = Sheets ("Filter") Range ("B10"), Unique: = True Columns.AutoFit Phạm vi ("B10"). Select End Sub Trước tiên chúng tôi đảm bảo các dữ liệu đã lọc hiện tại (trong bất kỳ) là xóa ra trước khi chúng tôi chạy lại mã và sau đó chúng ta có được những dữ liệu đã lọc mới từ tế bào B10 trở đi. Bây giờ chúng ta hãy hiểu mã thực tế mà các bộ lọc dữ liệu của chúng tôi chuyển đổi dữ liệu liệu của chúng tôi vào một bảng excel (Tham khảo cấu trúc cấu trúc tham khảo), bằng cách làm này, chúng ta không còn cần phải biết làm thế nào nhiều hàng dữ liệu của chúng tôi thực sự đi xuống, các "Table1 [#All]" chăm sóc đó cho chúng tôi. Chúng tôi cũng cần phải xác định rằng dữ liệu của chúng tôi là trong một tờ giấy khác và chúng tôi đang cố gắng chạy Advanced Filter có khoảng từ dữ liệu, điều này được thực hiện bằng cách sử dụng dòng đầu tiên "Sheets (" RawData "). Range (" Table1 [#All] "). AdvancedFilter ". Tiếp theo chúng ta xác định các hành động mà chúng ta cần đó là sao chép trong trường hợp của chúng tôi, các tùy chọn khác là" xlFilterInPlace "mà sẽ lọc ngay trên dữ liệu của chúng tôi riêng của mình. Sau đó, chúng tôi đã xác định các Range (mà cần phải được trên các tờ cùng một tiêu chí . nơi mà dữ liệu) Và cuối cùng chúng tôi đã xác định nơi sản lượng phải được gửi đến bằng cách sử dụng: "). Range (": "CopyToRange = Sheets (" Filter B10 ")" Chúng tôi cũng đã thực hiện chắc chắn rằng chỉ có hồ sơ duy nhất là trả lại cho chúng tôi bằng cách chuyển Unique: = True.