Tin Nóng![[Video] Cách sử dụng hàm FILTER trong Google Sheet lọc dữ liệu](https://bachkhoavietnam.vn/wp-content/uploads/2024/09/video-cach-su-dung-ham-filter-trong-google-sheet-loc-du-thumb.jpg)
Trong các sheet có nhiều dữ liệu bạn sẽ rất khó khăn để tìm kiếm các thông tin cần thiết. Nhận thấy được điều đó nên Google đã có hàm FILTER trong Google Sheet hỗ trợ người dùng có thể lọc được thông tin một cách dễ dàng và nhanh chóng hơn.
Sau đây là video hướng dẫn bạn cách dùng hàm FILTER trong Google Sheet cực đơn giản nhé:
1. Hàm FILTER là gì? Ứng dụng của hàm FILTER trong Google Sheet
Hàm FILTER là gì?
Hàm FILTER sẽ giúp bạn lọc dữ liệu theo một điều kiện cho trước. Hàm này không làm ảnh hưởng gì đến dữ liệu ban đầu mà chỉ lọc và hiển thị các dữ liệu mà bạn mong muốn.
Công thức hàm FILTER
=FILTER(“Range of values”; “Condition 1”; [“Condition 2”, …])
Trong đó:
+ Range of values: Vùng chứa giá trị bạn muốn lọc.
+ Condition 1, Condition 2,… : Các điều kiện đối với giá trị cần lọc.
Ví dụ về hàm FILTER
Cho bảng dữ liệu
|
Tên
|
Sản phẩm
|
|
Hoa
|
Viết
|
|
Lan
|
Thước
|
|
Cúc
|
Tập
|
|
Cúc
|
Sách
|
|
Lan
|
Bút chì
|
|
Hoa
|
Màu nước
|
|
Lan
|
Máy tính
|
Đề: Hãy tìm những sản phẩm mà Lan mua.
Cách thực hiện: Ta thấy Vùng dữ liệu là vùng để tìm giá trị mà trả về là cột Sản phẩm => vùng giá trị từ B2:B8. Điều kiện là sản phẩm của Lan, vậy nên ta sẽ dò cột Tên xem ai tên Lan => điều kiện A2:A8=”Lan”.
Công thức: =FILTER(B2:B8;A2:A8=”Lan”)
Cách lọc ra những sản phẩm Lan mua
Ứng dụng của hàm FILTER
+ Lọc dữ liệu với điều kiện cho trước.
+ Hỗ trợ các hàm khác tham chiếu dữ liệu để xử lý số liệu được dễ dàng.
2. Cách sử dụng hàm FILTER trong Google Sheet
Cho bảng dữ liệu:
|
Tên
|
Sản phẩm
|
Giá
|
|
Hoa
|
Viết
|
100.000
|
|
Lan
|
Thước
|
120.000
|
|
Cúc
|
Tập
|
50.000
|
|
Cúc
|
Sách
|
80.000
|
|
Lan
|
Bút chì
|
40.000
|
|
Hoa
|
Màu nước
|
100.000
|
|
Lan
|
Máy tính
|
200.000
|
Các cách sử dụng cơ bản
– Nhập nhiều điều kiện trong hàm FILTER
Đề: Hãy lọc những sản phẩm có giá lớn hơn 50.000 và nhỏ hơn 150.000.
Theo đề bài ta thấy có hai điều kiện và lớn hơn 50.000 và nhỏ hơn 150.000
Công thức:
=FILTER(B2:B8;C2:C8>50000;150000>C2:C8)
Cách lọc những sản phẩm có giá lớn hơn 50.000 và nhỏ hơn 150.000
– Tham chiếu nhiều cột trong hàm FILTER
Đề: Hãy tìm sản phẩm lan mua có giá trị lớn hơn 100.000.
Ta thấy ta cần điều kiện hai cột là Tên (cột A) là Lan và Giá (cột C) lớn hơn 100.000.
Công thức:
=FILTER(B2:B8;A2:A8=”Lan”;C2:C8>=100000)
Cách lọc những sản phẩm Lan mua có giá trị trên 100.000
– Tham chiếu ô trong phần điều kiện của hàm FILTER
Đề: Lọc những sản phẩm có giá trị lớn hơn ô C2.
Vậy nên ta có điều kiện C2:C8>C2.
Công thức:
Tìm những sản phẩm có giá trị lớn hơn ô C2
Kết hợp với hàm COUNT
Cho bảng dữ liệu như sau để thực hiện đề của phần: Kết hợp với hàm COUNT, kết hợp với hàm SUM, kết hợp với hàm SORT, kết hợp nhiều điều kiện.
|
Date
|
Value
|
Product
|
|
27/05/2021
|
333
|
Dog
|
|
28/05/2021
|
100
|
Cat
|
|
29/05/2021
|
400
|
Dog
|
|
30/05/2021
|
500
|
Tiger
|
|
06/06/2021
|
700
|
Cat
|
|
01/06/2021
|
100
|
Dog
|
Đề: Hãy đếm những ngày trong bảng thuộc tháng 5.
Vậy nên ta thấy điều kiện tháng phải là tháng 5 và kết hợp hàm COUNT để đếm số ngày.
Công thức:
=COUNT(FILTER(B2:B7;MONTH(A2:A7)=7))
Minh họa kết hợp FILTER và COUNTER
Kết hợp với hàm SUM
Đề: Hãy tính tổng giá trị của tháng 6.
Ta thấy có điều kiện là ngày phải trong tháng 6 và kết hợp với hàm SUM để tính tổng.
Công thức:
=SUM(FILTER(B2:B7;MONTH(A2:A7)=6))
Minh họa kết hợp FILTER và SUM
Kết hợp với hàm SORT
Đề: Hãy lọc sản phẩm có giá trị từ 500 trở lên và sắp xếp theo giá trị giảm dần.
Ta thấy có điều kiện giá trị >= 500 và kết hợp với hàm SORT để sắp xếp các giá trị này.
Công thức:
=SORT(FILTER(B2:C7;B2:B7>=500);1;FALSE)
Kết hợp hàm SORT và FILTER
Kết hợp nhiều điều kiện
Đề: Hãy lọc những sản phẩm Dog trong tháng 5.
Ta thấy phải kết hợp hai điều kiện là sản phẩm Dog và trong tháng 5. Sau khi lọc xong ta cần tính tổng giá trị của chúng.
Công thức:
=IF(AND(FILTER(C2:C7;C2:C7=”Dog”);FILTER(A2:A7;MONTH(A2:A7)=5));”value của “&FILTER(C2:C7;MONTH(A2:A7)=5;C2:C7=”Dog”)&” trong thang 5 la: “&SUM(FILTER(B2:B7;MONTH(A2:A7)=5;C2:C7=”Dog”));”Khong co”)
Kết hợp nhiều điều kiện
3. Một số kiểu dữ liệu được FILTER hỗ trợ lọc
Lọc theo điều kiện số
Đề: Hãy lọc sản phẩm có value bằng 400.
Ta thấy có điều kiện giá trị băng 400 là điều kiện số.
Công thức:
Lọc giá trị theo số
Lọc theo điều kiện text
Đề: Hãy lọc sản phẩm Dog.
Ta thấy có điều kiện là Dog là điều kiện dạng text.
Công thức:
=FLTER(A2:B7;C2:C7=”Cat”)
Lưu ý: Khi điều kiện là text là cần bỏ text đó vào dấu ngoặc kép và nhập chính xác điều kiện để tránh bị sai sót.
Lọc điều kiện theo text
Lọc theo điều kiện ngày/tháng/năm
Đề: Hãy lọc sản phẩm có ngày là 30/5/2021.
Ta thấy có điều kiện là dạng ngày tháng năm.
Công thức:
=FILTER(A2:B7;DATE(“2021″;”05″;”30”)=A2:A7)
4. Các lưu ý khi sử dung hàm FILTER trong Google Sheet
– Chỉ có thể sử dụng FILTER để lọc các hàng hoặc cột tại một thời điểm. Để lọc cả hàng và cột, hãy sử dụng giá trị trả về của hàm FILTER dưới dạng dải_ô trong một hàm khác.
– Nếu FILTER không tìm thấy giá trị thỏa mãn điều kiện cho sẵn, hàm sẽ trả về #N/A.
Lưu ý khi sử dụng hàm FILTER
5. Một số lỗi thường gặp khi dùng hàm FILTER
Lỗi #REF!
– Giải thích: Gặp lỗi #REF! khi ô giá trị bị chắn, không đủ để hiện toàn bộ kết quả.
– Cách khắc phục lỗi #REF!: Dời đến vị trí nhiều ô trống hơn.
– Ví dụ sửa lỗi: Trong bài ta thấy ô A11 bị chắn giá trị bởi ô B11 nên xảy ra lỗi #REF!. Nên khi dời sang ô C11 thì không còn lỗi nữa.
minh họa lỗi #REF!
Lỗi #N/A
– Giải thích: Lỗi #N/A là lỗi không tìm được giá trị cần tìm.
– Cách khắc phục lỗi #N/A:
+ Kiểm tra lại các bảng dữ liệu đối chiếu trong các hàm tìm kiếm và sắp xếp dữ liệu so sánh trong bảng theo giá trị từ nhỏ đến lớn.
+ Đảm bảo dữ liệu tìm kiếm và đối chiếu phải cùng một kiểu định dạng dữ liệu đồng thời giá trị của dữ liệu tìm kiếm không được nhỏ hơn giá trị nhỏ nhất của dữ liệu đối chiếu.
– Ví dụ sửa lỗi: Trong cú pháp sai ta thấy giá trị điều kiện ở cột B do vậy mà không tìm được giá trị, ta sẽ đổi giá trị thành cột C.
Công thức: =FILTER(B2:C7;C2:C7=”Cat”)
Minh họa lỗi N/A
6. Các bài tập sử dụng hàm FILTER
Đề: Cho bảng giá trị gồm các trường dữ liệu Tên, Sản phẩm, Giá, Số lượng, Thành tiền. Hãy thực hiện các yêu cầu bên dưới.
|
Tên
|
Sản phẩm
|
Giá
|
Số lượng
|
Thành tiền
|
|
Hoa
|
Viết
|
100.000
|
1
|
100000
|
|
Lan
|
Thước
|
120.000
|
2
|
240000
|
|
Cúc
|
Tập
|
50.000
|
3
|
150000
|
|
Cúc
|
Sách
|
80.000
|
4
|
320000
|
|
Lan
|
Bút chì
|
40.000
|
1
|
40000
|
|
Hoa
|
Màu nước
|
100.000
|
2
|
200000
|
|
Lan
|
Máy tính
|
200.000
|
2
|
400000
|
Câu hỏi 1: Những số tiền mà Lan đã dùng để mua dụng cụ học tập?
Trả lời: =FILTER(E2:E8;A2:A8=”Lan”)
Giải thích: Điều kiện là Tên là Lan.
Cách giải ví dụ 1
Câu hỏi 2: Giá những sản phẩm có giá từ 100.000 trở lên
Trả lời: =FILTER(B2:B8;C2:C8>=100000)
Giải thích: Điều kiện là Gía lớn hơn 100.000.
Cách giải ví dụ 2
Câu hỏi 3: Tổng hóa đơn mua hàng của Lan là bao nhiêu?
Trả lời: =SUM(FILTER(E2:E8;A2:A8=”Lan”))
Giải thích: Điều kiện là Tên là Lan và kết hợp hàm SUM để tính tổng.
Cách giải ví dụ 3
Câu hỏi 3: Tính tiền cho những người mua hàng nhận được giảm giá.
Trả lời: =IF(SUM(FILTER(E2:E8;A2:A8=B12))>=500000;”10%”;0)
Giải thích: Đầu tiên ta cần tìm tổng hóa đơn của từng người qua sự kết hợp của hàm SUM và FILTER. Sau đó kết hợp hàm IF để đặt điều kiện những người có hóa đơn trên 500.000 sẽ được giảm 10%
Cách giải ví dụ 4
7. Các câu hỏi thường gặp khi sử dụng hàm FILTER
Hàm FILTER có bao nhiêu đối số?
Đối với hàm FILTER có ít nhất hai đối số là vùng giá trị và điều kiện. Tuy nhiên, trong trường hợp bài toán cần nhiều điều kiện thì vẫn có thể có nhiều hơn.
Câu hỏi thường gặp khi dùng hàm FILTER
Một số mẫu laptop đang kinh doanh tại Thế Giới Di Động:
Mình đã hướng dẫn xong cách sử dụng hàm FILTER để lọc dữ liệu trong Google Sheet. Hẹn gặp lại ở những bài viết tiếp theo!