Việc lọc dữ liệu từ sheet này sang sheet khác là một việc thường gặp phải trong công việc. Việc này chính là việc lập báo cáo chi tiết dựa vào một số điều kiện lập báo cáo. Mục đích là lấy những nội dung thỏa mãn điều kiện trong bảng dữ liệu (nằm trên 1 sheet riêng) đưa sang báo cáo chi tiết (nằm trên 1 sheet riêng). Trong Excel việc này được thực hiện một cách khá đơn giản với công cụ Advanced Filter (bộ lọc nâng cao). Sau đây chúng ta cùng tìm hiểu cách lọc dữ liệu sang sheet khác bằng Advanced Filter trong Excel nhé:
Xem nhanh
1. Giới thiệu về công cụ Advanced Filter
1. Giới thiệu về công cụ Advanced Filter
a. Mở công cụ 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ốcAction/ Copy to another location: Sao chép kết quả lọc được sang vị trí khácList range: Vùng dữ liệu cần lọcCriteria range: Vùng điều kiện lọcCopy to: Nơi đặt kết quả lọc (khi chọn sao chép kết quả sang vị trí khác)Unique records only: Chỉ lấy các giá trị không trùng nhau (xuất hiện 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 buộc phải chọn Copy to another location khi thực hiện Advanced Filter.Bạn đang xem: Cách lọc dữ liệu từ nhiều sheet trong excel
Nơi đặt kết quả lọc (copy to) phải tương ứng với sheet chứa vùng điều kiện (criteria range), tức là trong cùng 1 sheet.
2. Cách thực hiện
Xé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 Sheet mới
Bước 1: Tạo mới 1 sheet, lấy tên là “Report01”Bước 2: Thiết lập điều kiện 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 quả thu được tại Report01
Như vậy chúng ta đã có thể lấy được kết quả lọc dữ liệu từ sheet Data để đưa sang sheet Report bằng cách sử dụng Advanced Filter.
3. Cách thiết lập điều kiện trong Advanced Filter
Vùng điều kiện trong Advanced Filter bắt buộc phải tuân theo nguyên tắc sau:
Bao gồm tên tiêu đề của trường dữ liệu có liên 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 hệ phụ thuộc / Điều kiện dạng Và: Tất cả các điều kiện đều thỏa mãn
Mối quan hệ bổ sung / Điều kiện dạng Hoặc: Chỉ cần 1 trong những điều kiện được thỏa mãn
Các điều kiện được đặt so le nhau, không nằm cùng 1 hàng.
Trên đây là những nội dung chính về việc sử dụng Advanced Filter trong Excel để lọc / trích xuất dữ liệu từ sheet này sang sheet khác. Việc sử dụng Advanced Filter khá đơn giản, chỉ cần chú ý về cách xây dựng vùng điều kiện và vị trí đặt vùng điều kiện.
Ngoài ra Học Excel Online xin giới thiệu với các bạn Khóa học Excel từ cơ bản tới nâng cao dành cho người đi làm <Đăng ký>. Đây là 1 khóa học rất đầy đủ kiến thức và bổ trợ rất tuyệt vời cho bạn trong việc làm quen với Excel, sử dụng các công cụ, các hàm trong Excel để đáp ứng yêu cầu công việc tổ chức, quản lý dữ liệu và lập báo cáo trên Excel.
Trường hợp lưu trữ dữ liệu thành từng Tab trong một file Excel vô cùng phổ biến. Đó có thể là dữ liệu theo từng tháng trong năm với cấu trúc giống nhau mà bạn cần phải kết hợp dữ liệu từ những Tab này trong cùng một file Excel khi bạn đang sử dụng Power BI hoặc Power query/Get & Transform trong Excel. Và trong bài blog này, chúng ta sẽ cùng bàn về cách tích hợp dữ liệu từ các Tab trong 1 File Excel và thậm chí là áp dụng cùng một quy trình thay đổi dữ liệu cho từng tab vì cấu trúc của chúng giống nhau. Hi vọng đây sẽ là một ứng dụng tuyệt vời của Power BI giúp bạn xử lý gọn nhẹ việc liên kết các bảng dữ liệu tách rời này.Trước hết, để thực hành, các bạn hãy chuẩn bị nguồn đơn giản trong 1 file Excel như sau. Giả sử bạn có một bảng tính Excel với bốn Tab: Q1, Q2, Q3 và Q4. Trong mỗi Tab là một số dữ liệu bán hàng trong ba tháng trong mỗi quý. Ví dụ như bảng tính Q1 trông như thế này:
Bước 1: Chọn một bảng mẫu trong các Tab được liệt kê
Trong Power BI, hãy kết nối với tệp Excel của bạn như bình thường. Sau đó, bạn không cần chọn bất cứ bảng nào vội mà chỉ cần ấn chuột phải vào Folder Excel ‘Sample.xlsx’, sau đó Transform Data:
Bước 2: Tạo truy vấn mẫu của bạn
Nhân đôi truy vấn (Duplicate) ở trên và gọi là mẫu truy vấn mới (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
Sau đó, và điều này là quan trọng: loại bỏ 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ôn ngữ M được tạo ra cho bước tiếp theo bạn sẽ làm. Việc loại bỏ tất cả các cột này thay đổi cách mà M thực hiện và đảm bảo rằng tên của bảng tính sẽ không được mã hóa cứng ở bất cứ đâu.Sau đó nhấp vào liên kết “Table” bên trong ô và bạn sẽ thấy nội dung của bảng tính:Khi làm như vậy, Power BI sẽ tự động bổ sung thêm 2 bước đó chính là Promoted Header (chuyển dòng đầu thanh tên trường) và Change Types (thay đổi định dạng). Chúng ta cần để ý bước Change Type sẽ gọi ra tên trường và điều này là không nên có khi áp dụng cho các bảng ở Tab khác vì tên trường sẽ khác hoàn toàn.Vì vậy, chúng ta cần xóa đi bước “Change
Type”:Bây giờ bạn có thể thực hiện bất kỳ biến đổi nào khác mà bạn cần trên truy vấn này, nhưng bạn sẽ cần phải tránh bất kỳ biến đổi nào tạo ra mã M đề cập đến bất kỳ tên cột nào trên bảng tính. Vì chúng ta sẽ áp dụng các bước thay đổ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 nhớ rằng, những biến đổi này sẽ cần phải được áp dụng cho các bảng tính khác và chúng sẽ thất bại nếu chúng áp dụng cho các cột không phải là cột hiện tại. Đây là lý do tại sao bạn phải xoá bước thay đổi loại trước đó, bởi vì nó đặt các loại vào tháng một, tháng hai và tháng ba, và bạn có thể mở trình chỉnh sửa nâng cao (Advance Editor) và kiểm tra mã M cho toàn bộ truy vấn chỉ để đảm bảo không có bất cứ tên trường nào được gọi ra.Trong trường hợp này tất cả những gì chúng ta cần làm là unpivot các cột tháng bằng cách chọn cột “sản phẩm” và sử dụng “Unpivot Other Column” trên Transform tab, và sau đó đổi tên các cột thích hợp:Việc có tên trường Sản Phẩm ở đây sẽ không phải vấn đề vì ở các Tab đều có 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 cách nhấp vào nút Manange Parameters/New Parameters, gọi tham số Worksheet, đặt kiểu dữ liệu thành văn bản và trả về tên của bảng tính bạn đã chọn trong bước trước:Bây giờ, quay lại truy vấn mẫu, tìm các bước được gọi là lọc hàng về phía đầu mà bạn đã lọc xuống một bảng tính và nhấp vào biểu tượng bánh răng bên cạnh bước để chỉnh sửa nó:Sau đó, chỉnh sửa bước để nó sử dụng giá trị trả lại bằng tham số để lọc theo thay vì giá trị cứng mã hoá mà bạn đã 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à sau đó chọn tham số Worksheet trong 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 và nhấp chuột phải vào truy vấn mẫu và chọn “Create Function”:Bạn sẽ được nhắc để cung cấp cho các chức năng mới một tên; gọi nó là Get
Data:Và chúng ta đã tạo được một chức năng Function là Get Data bao gồm Mẫu Sample Query và Parameter
Bước 4: Gọi hàm và kết hợp dữ liệu
Cuối cùng, quay lại bản sao trùng lặp của truy vấn ban đầu được tạo ở đầu bước 2. Sau đó đi đến “Add column tab” trên thanh công cụ và nhấp vào nút “Invoke Custom Function” và gọi hàm Get
Data, đi qua trong nội dung của cột tên cho tham số chỉ của hàm:Cuối cùng, nhấp vào Expand/Aggregate trên cột mới và mở rộng các bảng lồng nhau:Sau khi loại bỏ bất kỳ 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 kết hợp thành một bảng duy nhất như mong muốn:
Đừng quên đặt các loại dữ liệu trên mỗi cột!Hi vọng bài viết trên sẽ giúp các bạn có thể giải quyết được một 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ó cùng cấu trúc. Và giờ thì happy analyzing!