Việc lọc dữ liệu từ sheet này sang trọng sheet khác là một trong việc thường gặp bắt buộc trong công việc. Bài toán này đó là việc lập báo cáo chi tiết nhờ vào một số điều khiếu nại lập báo cáo. Mục đích là lấy gần như nội dung thỏa mãn điều kiện trong bảng tài liệu (nằm bên trên 1 sheet riêng) đưa sang báo cáo chi huyết (nằm bên trên 1 sheet riêng). Trong Excel câu hỏi này được thực hiện một biện pháp khá đơn giản với phép tắc Advanced Filter (bộ thanh lọc nâng cao). Sau đây họ cùng tò mò cách lọc dữ liệu sang sheet khác bằng Advanced Filter trong Excel nhé:


Xem nhanh


1. Trình làng về nguyên tắc Advanced Filter

1. Giới thiệu về cơ chế Advanced Filter

a. Mở phương pháp Advenced Filter

Trong thẻ Data > chọn Advanced Filter

*

Hộp thoại Advanced Filter như sau:

*

Trong đó

Action/ Filter the list, in-place: lọc tại chính danh sách gốc
Action/ Copy khổng lồ another location: Sao chép công dụng lọc được sang địa điểm khácList range: Vùng tài liệu cần lọcCriteria range: Vùng điều khiếu nại lọcCopy to: Nơi đặt tác dụng lọc (khi lựa chọn sao chép kết quả sang địa chỉ khác)Unique records only: Chỉ lấy những giá trị ko trùng nhau (xuất hiện tại 1 lần)

b. Chú ý

Như vậy ý muốn lọc dữ liệu từ sheet này sang sheet khác chúng ta bắt yêu cầu chọn Copy khổng lồ another location khi triển khai Advanced Filter.

Bạn đang xem: Cách lọc dữ liệu từ nhiều sheet trong excel

Nơi đặt tác dụng lọc (copy to) buộc phải tương ứng với sheet cất vùng điều kiện (criteria range), có nghĩa là trong thuộc 1 sheet.

2. Phương pháp thực hiện

Xét lấy một ví dụ sau:

Cho bảng dữ liệu tại sheet Data gồm:


*

*

Yêu cầu: lọc dữ liệu bán hàng của nhân viên Trang sang trọng Sheet mới

Bước 1: Tạo new 1 sheet, mang tên là “Report01”Bước 2: Thiết lập điều kiện như sau

*

Bước 3: tiến hành lọc bằng công rứa Advanced Filter như sau

*

List range: Tại sheet Data, vùng A1:D11 (Xem ở đề bài)

Criteria range: tại sheet Report01, vùng A1:A2

Copy to: Vị trí đặt kết quả tại Sheet Report01, ô A4

Kết trái thu được trên Report01

*

Như vậy chúng ta đã hoàn toàn có thể lấy được kết quả lọc tài liệu từ sheet Data để đưa quý phái sheet Report bằng cách sử dụng Advanced Filter.

3. Bí quyết thiết lập điều kiện trong Advanced Filter

Vùng điều khiếu nại trong Advanced Filter bắt buộc phải tuân theo nguyên lý sau:

Bao bao gồm tên tiêu đề của trường tài liệu có tương quan + nội dung điều kiện trong trường đó
Mối quan hệ giữa các điều kiện được viết như sau:

Mối quan tiền hệ dựa vào / Điều kiện dạng Và: toàn bộ các điều kiện đều thỏa mãn

*

Mỗi điều kiện sẽ đương nhiên 1 tiêu đề. Ví như trong thuộc 1 cột tiêu đề thì lặp lại cột tiêu đề đó.Các điều kiện được đặt trên thuộc 1 hàng

Mối quan lại hệ bổ sung cập nhật / Điều khiếu nại dạng Hoặc: chỉ việc 1 trong những điều kiện được thỏa mãn

*

Mỗi điều kiện đã kém theo 1 tiêu đề. Nếu trong cùng 1 cột tiêu đề thì đặt ở dòng phía dưới trên cùng cột tiêu đề đó
Các điều kiện được đặt so le nhau, ko nằm cùng 1 hàng.

Trên đây là hầu hết nội dung thiết yếu về việc sử dụng Advanced Filter trong Excel để thanh lọc / trích xuất dữ liệu từ sheet này quý phái sheet khác. Việc áp dụng Advanced Filter khá đơn giản, chỉ cần chú ý về phong thái xây dựng vùng điều kiện cùng vị trí đặt vùng điều kiện.

Ngoài ra học Excel Online xin reviews với các bạn Khóa học tập Excel từ bỏ cơ bạn dạng tới nâng cấp dành cho những người đi làm <Đăng ký>. Đây là 1 trong những khóa học rất đầy đủ kiến thức và hỗ trợ rất giỏi vời cho chính mình trong việc làm thân quen với Excel, sử dụng những công cụ, những hàm trong Excel để đáp ứng yêu thương cầu các bước tổ chức, quản lý dữ liệu cùng lập report trên Excel.

Trường hợp tàng trữ dữ liệu thành từng Tab vào một file Excel khôn cùng phổ biến. Đó rất có thể là dữ liệu theo từng tháng trong những năm với cấu trúc giống nhau mà bạn phải kết hợp dữ liệu từ hồ hết Tab này trong và một file Excel khi bạn đang sử dụng Power BI hoặc power nguồn query/Get và Transform vào Excel. Và trong bài xích blog này, chúng ta sẽ cùng bàn về kiểu cách tích hợp dữ liệu từ những Tab trong 1 File Excel và thậm chí còn là áp dụng cùng một quy trình chuyển đổi dữ liệu mang đến từng tab vì cấu tạo của bọn chúng giống nhau. Hi vọng đây sẽ là 1 ứng dụng tuyệt vời của nguồn BI giúp bạn xử lý gọn nhẹ bài toán liên kết các bảng dữ liệu tách bóc rời này.Trước hết, để thực hành, chúng ta hãy chuẩn bị nguồn dễ dàng và đơn giản trong 1 tệp tin Excel như sau. Mang sử các bạn có một bảng tính Excel với bốn Tab: Q1, Q2, quận 3 và Q4. Trong những Tab là một trong những dữ liệu bán hàng trong cha tháng trong những quý. Ví như bảng tính quận 1 trông như vậy này:
*
Trang tính quận 2 trông như vậy này:
*
… cùng như vậy. Đầu ra quan trọng cho power nguồn BI nên là 1 bảng trông giống hệt như thế này:
*
Trong trường phù hợp mỗi Tab gồm một bảng với thương hiệu cột như là nhau trên đó, bạn có thể dễ dàng thực hiện Append nhằm nối chúng lại thành 1 bảng chính. Khi kết nối với file Excel, họ sẽ thấy các bảng tính được bao hàm trong kia như dưới đây.
*
Nhiều chúng ta nghĩ bọn họ có thực hiện icon ngơi nghỉ cột Data nhằm mở các dữ liệu ngơi nghỉ từng Tab và Append bọn chúng như dưới đây. Tuy vậy trong trường hợp quan trọng đặc biệt này, vì cấu trúc của từng bảng trực thuộc dạng Semi-Tabular thay vày Tabular, nó không giải quyết và xử lý được vấn đề, cũng chính vì chúng ta nhận thấy điều này:
*
AHA, bạn có thể nói, họ phải chuyển đổi dữ liệu trước khi bạn có thể kết hòa hợp nó và vị vậy bọn họ cần phải tạo thành một công dụng (Function) và thực hiện nó cho mỗi bảng tính để gửi hóa về dạng Tabular trước khi rất có thể Append. Và điều này là gồm thể, trên kim chỉ nan thì nghe có vẻ như là dễ tuy thế điều trở ngại là bước vào chi tiết. Hãy cùng mình mày mò cách thức buổi giao lưu của nó nhé! Và dưới đây là chiến thuật theo mỗi bước một:

Bước 1: chọn 1 bảng mẫu trong số Tab được liệt kê

Trong nguồn BI, hãy liên kết với tệp Excel của công ty như bình thường. Sau đó, các bạn không bắt buộc chọn bất cứ bảng nào cấp mà chỉ cần ấn chuột đề xuất vào folder Excel ‘Sample.xlsx’, tiếp nối Transform Data:

*
Kết quả sẽ là một trong bảng trông y như thế này: (Chúng ta chỉ dựng chân lại tại bước liên kết với file Excel)
*
Nếu các bạn cần, thanh lọc ra bất kỳ hàng như thế nào không cất “Sheet” trong cột Kind với cũng thanh lọc ra bất kỳ bảng tính làm sao mà chúng ta không muốn kết hợp dữ liệu.

Bước 2: sản xuất truy vấn mẫu mã của bạn

Nhân song truy vấn (Duplicate) ở trên và điện thoại tư vấn là mẫu truy vấn bắt đầu (Sample Query).

Xem thêm: Những Cô Nàng Rắc Rối Tập 99 Tá»· đÁ»“Ng, Gia Đình Rắc Rối Lồng Tiếng

*
Từ đây, trong bảng tróc nã vấn mẫu mã Sample Query, chúng ta sẽ thực hiện quá trình cần thiết để chuyển hóa dữ liệu. Trước tiên, chọn một trong những bảng tính để sử dụng để xây dựng quá trình truy vấn sẽ được vận dụng cho tất cả các bảng tính khác và lọc bảng trên nhằm nó chỉ cất hàng mang đến bảng tính đó. Vào trường hòa hợp này tôi đang áp dụng bảng tính được điện thoại tư vấn là Q1:
*

Sau đó, và vấn đề đó là quan tiền trọng: đào thải tất cả các cột khác trong bảng ngoại trừ cột Data:

Làm điều này giúp sử dụng ngữ điệu M được tạo ra cho bước tiếp theo các bạn sẽ làm. Việc thải trừ tất cả các cột này biến hóa cách nhưng mà M thực hiện và bảo vệ rằng thương hiệu của bảng tính sẽ không còn được mã hóa cứng ở bất kể đâu.Sau đó nhấp vào link “Table” phía bên trong ô và bạn sẽ thấy câu chữ của bảng tính:Khi làm như vậy, power BI sẽ tự động bổ sung thêm 2 bước đó đó là Promoted Header (chuyển dòng đầu thanh tên trường) cùng Change Types (thay đổi định dạng). Họ cần chú ý bước Change Type sẽ call ra tên trường và điều đó là tránh việc có lúc áp dụng cho những bảng sinh sống Tab khác vày tên trường đã khác hoàn toàn.Vì vậy, bọn họ cần xóa đi cách “Change
Type”:Bây giờ chúng ta cũng có thể thực hiện bất kỳ biến đổi nào không giống mà bạn phải trên truy tìm vấn này, nhưng các bạn sẽ cần cần tránh ngẫu nhiên biến đổi nào tạo nên mã M nhắc đến bất kỳ tên cột nào trên bảng tính. Vì họ sẽ áp dụng các bước chuyển đổi này với các bảng tính khác bao gồm tên các trường khác nhau. Hãy luôn luôn nhớ rằng, những đổi khác này sẽ cần được được áp dụng cho những bảng tính khác và bọn chúng sẽ thất bại nếu chúng áp dụng cho các cột không hẳn là cột hiện tại. Đây là vì sao tại sao các bạn phải xoá bước đổi khác loại trước đó, cũng chính vì nó đặt các loại vào tháng một, tháng hai cùng tháng ba, và bạn có thể mở trình chỉnh sửa cải thiện (Advance Editor) và kiểm soát mã M cho tổng thể truy vấn chỉ để bảo đảm không có bất kể tên trường như thế nào được hotline ra.Trong trường vừa lòng này toàn bộ những gì họ cần có tác dụng là unpivot những cột tháng bằng cách chọn cột “sản phẩm” và thực hiện “Unpivot Other Column” trên Transform tab, và tiếp đến đổi tên những cột yêu thích hợp:Việc có tên trường sản phẩm ở đây sẽ chưa hẳn vấn đề bởi ở các Tab đều mang tên trường này.

Bước 3: tạo một chức năng

Tiếp theo, bạn cần tạo một tham số mới bằng phương pháp nhấp vào nút Manange Parameters/New Parameters, điện thoại tư vấn tham số Worksheet, đặt kiểu tài liệu thành văn bạn dạng và trả về thương hiệu của bảng tính chúng ta đã lựa chọn trong bước trước:Bây giờ, trở về truy vấn mẫu, tìm quá trình được call là lọc mặt hàng về phía đầu mà các bạn đã thanh lọc xuống một bảng tính với nhấp vào biểu tượng bánh răng cạnh bên bước để sửa đổi nó:Sau đó, chỉnh sửa bước để nó thực hiện giá trị trả lại bởi tham số để lọc theo thay vì chưng giá trị cứng mã hoá mà chúng ta đã nhập trước đó. Để thực hiện việc này, hãy nhấp vào nút Hiển thị bên dưới, chọn tham số và tiếp đến chọn tham số Worksheet trong vỏ hộp thả xuống tiếp theo:Cuối cùng, đi tới ngăn truy vấn ở bên trái màn hình hiển thị và click chuột phải vào truy vấn vấn chủng loại và chọn “Create Function”:Bạn sẽ tiến hành nhắc để cung cấp cho các tác dụng mới một tên; điện thoại tư vấn nó là Get
Data:Và bọn họ đã tạo được một tác dụng Function là Get Data bao hàm Mẫu Sample Query với Parameter

Bước 4: điện thoại tư vấn hàm và phối hợp dữ liệu

Cuối cùng, tảo lại bản sao đụng hàng của tầm nã vấn ban đầu được tạo ra ở đầu bước 2. Tiếp đến đi cho “Add column tab” bên trên thanh cơ chế và nhấp vào nút “Invoke Custom Function” và điện thoại tư vấn hàm Get
Data, đi qua trong văn bản của cột tên mang đến tham số chỉ của hàm:Cuối cùng, nhấp vào Expand/Aggregate trên cột mới và không ngừng mở rộng các bảng lồng nhau:Sau khi một số loại bỏ ngẫu nhiên cột không cần thiết nào, bạn sẽ thấy dữ liệu từ tất cả các bảng tính phối kết hợp thành một bảng nhất như mong muốn muốn:

Đừng quên đặt những loại dữ liệu trên từng cột!Hi vọng nội dung bài viết trên sẽ giúp đỡ các bạn có thể giải quyết được trong những bài toán điển hình với dữ liệu ở Excel được trả ra các Tab khác nhau có thuộc cấu trúc. Với giờ thì happy analyzing!