[Video] Cách sử dụng hàm FILTER trong Google Sheet lọc dữ liệu

[Video] Cách sử dụng hàm FILTER trong Google Sheet lọc dữ liệu

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

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

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

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

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

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

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 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

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 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 đ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)

Lọc điều theo theo dạng ngày tháng

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

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!

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

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á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á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á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

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

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!

Tìm hiểu thêm:  Hé lộ 40 thủ thuật Excel mà dân văn phòng không thể bỏ qua