Lọc dữ liệu từ các file/sheet khác nhau là một thủ thuật thường phải làm trong báo cáo chi tiết dựa trên các điều kiện có sẵn. Mục đích của phương pháp này là tìm giá trị cụ thể trong bảng dữ liệu (tạm gọi là sheet 1) để chuyển qua ô giá trị tương ứng tại bảng báo cáo (gọi là sheet 2). Đối với những file có dữ liệu ít, chúng ta có thể copy và dán từ file này qua file khác. Thế nhưng, trường hợp file nguồn chứa hàng trăm, hàng ngàn giá trị, thì chúng ta lọc dữ liệu bằng cách nào? Bài viết này sẽ hướng dẫn bạn 2 cách lọc dữ liệu từ sheet-sheet trong excel. Hãy cùng chúng tôi tìm hiểu nhé.
Cách lọc dữ liệu từ sheet-sheet trong excel bằng Advanced Filter.
Advanced Filter là công cụ rất hữu ích để lọc dữ liệu từ các sheet khác nhau trong file excel một cách nhanh chóng và hiệu quả nhất. Để sử dụng được công cụ Advanced Filter, sheet nguồn và sheet báo cáo cần đảm bảo được 2 yêu cầu sau:
- Tiêu đề của bảng dữ liệu chỉ được nằm trong 1 hàng duy nhất.
- Không có ô merge nào ở trong file dữ liệu.
Cách lọc dữ liệu từ sheet–sheet được thực hiện như sau.
Đầu tiên, chúng ta mở file có sheet chứa dữ liệu (sheet 1)
và sheet nhận dữ liệu (sheet 2)
Trong ví dụ này, chúng ta cần tìm các giá trị dựa trên thông tin như sau: Tìm danh sách những người thuộc tổ đội ABS và có số tiền thưởng là 200.000 VND trong bảng dữ liệu ở sheet 1, chuyển qua bảng báo cáo ở sheet 2.
Muốn làm điều đó, chúng ta lần lượt thực hiện các bước sau:
Bước 1: Tại sheet 2, chúng ta tìm chọn mục Data trên thanh công cụ, mở hộp thoại Advanced.
Bước 2: Cửa sổ Advanced Filter được mở ra dưới dạng:
Chúng ta tiến hành thiết lập các thông tin trong mục này.
- Ở mục Action, chọn Copy to another location để lọc bảng dữ liệu từ sheet 1 sang lưu ở sheet 2.
- Tại vùng dữ liệu cần lọc List range, ta nhấp chuột vào mũi tên màu đen bên cạnh, sau đó di chuyển chuột tới sheet 1, chọn vùng dữ liệu chứa điều kiện (nhấn giữ chuột trái và kéo hết bảng dữ liệu). Trong ví dụ này, vùng cần chọn nằm từ cột A hàng 4 đến cột F hàng 19.
Lúc này, List range ở sheet 2 có dạng.
- Nhấn Enter, tiếp tục thiết lập cho vùng điều kiện cần lọc Criteria range trong cửa sổ Advanced Filter.
- Vùng điều kiện cần lọc chính là cột C (Tổ đội) và cột D (Số tiền) ở sheet 2. Sau khi chọn vùng điều kiện, nhấn Enter.
- Tại mục Copy to trong cửa sổ Advanced Filter, bạn chọn ô muốn đặt kết quả tại sheet 2 sau khi lọc ở bảng dữ liệu sheet 1, rồi nhấn Enter.
- Tiếp đến, bạn chọn mục OK trong cửa sổ Advanced Filter, kết quả sẽ hiển thị dưới dạng.
Như vậy, Tất cả thông tin của những người thuộc tổ đội ABS có số tiền thường là 200.000VND đã được lọc ra từ file chứa dữ liệu ở sheet 1, di chuyển tới file báo cáo ở sheet 2 một cách nhanh chóng và chính xác.
Nếu bạn vẫn làm theo cách ở trên, nhưng máy tính báo lỗi ở vùng dữ liệu cần lọc List range, thì hãy kiểm tra lại sheet 1. Bạn nhớ đảm bảo không có ô nào bị merge trong file chứa dữ liệu ở sheet 1 trước khi thực hiện công cụ Advanced Filter nhé.
Cách lọc dữ liệu từ sheet-sheet trong excel bằng hàm Vlookup
Cũng với bảng chứa dữ liệu ở sheet 1 như trên, trong trường hợp tại sheet 2 bạn đã có các điều kiện cần như “Họ Tên”, “Tổ đội”, “Năm sinh”. Có một vấn đề đó là số thứ tự của danh sách ở cột “Họ tên” trong 2 sheet không giống nhau. Làm cách nào để bạn có thể tìm kiếm “Số tiền” của mỗi người trong sheet 2 ứng với dữ liệu đã có tại sheet 1? Trong trường hợp này, chúng ta sẽ sử dụng hàm Vlookup – một hàm phổ biến và rất quan trọng trong việc lọc kết quả từ sheet – sheet, hay file này qua file khác trong excel.
Để sử dụng hàm Vlookup, chúng ta thực hiện lần lượt các bước sau:
Bước 1: Mở file có chứa 2 sheet theo yêu cầu của đề bài.
Bước 2: Tại cột E, hàng 5 của sheet 2 (cột cần tìm giá trị), ta sử dụng hàm Vlookup như hình ảnh.
Giải thích công thức =VLOOKUP(B5,Sheet1!$B$2:F17,4,0)
- B5 là cột chứa giá trị dùng để dò tìm. Ở ví dụ này, giá trị dò tìm là “Họ Tên”.
- Sheet 1 là tên sheet chứa file dữ liệu.
- B2:F17 là bảng chứa giá trị cần dò tìm ở sheet 1 (Chúng ta di chuyển chuột qua sheet 1, nhấn giữ và kéo chuột đi hết bảng chứa dữ liệu).
Ở bước này chúng ta cần lưu ý như sau: Nếu cột chứa giá trị dò tìm ở sheet 2 bắt đầu từ cột “Họ tên” thì bảng chứa giá trị cần dò tìm trong sheet 1 cũng phải bắt đầu từ cột “Họ tên”. Các giá trị cần tìm phải nằm bên phải cột “Họ tên”, lúc đó máy mới tính ra kết quả chính xác cho chúng ta. Nếu ngược lại, máy sẽ báo lỗi và trả về kết quả N/A.
Bảng chứa giá trị phải để trị tuyệt đối với dầu $ phía trước để cố định hàng và cột, phục vụ cho việc copy công thức trong cột. Trong ví dụ trên, tại công thức Vlookup, bạn đặt con trỏ giữa B và 2 sau đó nhấn F4, thực hiện tương tự giữa F và 17 như vậy bạn đã cố định được hàng, cột trong bảng chứa giá trị rồi.
- Số 4 nằm trong công thức là thứ tự cột chứa giá trị dò tìm trong bảng chứa giá trị tại sheet 1.
Trong ví dụ này, bảng chứa giá trị nằm từ cột B đến F, cột chứa giá trị “Số tiền” nằm ở vị trí số 4 tính từ cột B.
- Số 0 ở cuối công thức là phạm vi tìm kiếm tuyệt đối. Nếu muốn đưa về giá trị tương đối thì ta thay số 0 bằng số 1. Tuy nhiên, trong hầu hết trường hợp, chúng ta sẽ để số 0 để tìm giá trị chính xác nhất.
Như vậy, với những thông tin trong bài này thì chúng ta đã học được 2 cách lọc dữ liệu từ sheet-sheet trong excel rồi. Rất đơn giản đúng không nào! Chúng tôi hy vọng điều này sẽ có ích đối với các bạn trong công việc cũng như trong học tập của mình. Chúc các bạn thành công!