Thủ thuật

Hướng dẫn cách kết hợp hàm SUM và hàm FILTER trong Google Sheet chi tiết, đơn giản nhất

Trương Nguyễn Anh Thư

25-04-2023, 11:38 am

Kết hợp hàm SUM với hàm FILTER là cách thường được sử dụng để tính toán tổng một dãy số dữ liệu phục vụ nhiều mục đích khác nhau trong Google Sheet. Nếu các bạn vẫn chưa rõ hay còn thắc mắc cách kết hợp này như thế nào thì hãy cùng. theo dõi bài viết sau đây nhé!

Hướng dẫn cách sử dụng hàm SUM trong Google Sheet chi tiết

Hàm SUM là gì?

Hàm SUM là hàm được sử dụng để tính tổng của một dãy số dữ liệu trong Google Sheet.

Cách để sử dụng hàm SUM

Công thức hàm SUM:

=SUM(number1, number2,…)

Trong đó:

number1 và number2 là các số hoặc dãy số được tính tổng.

Ví dụ minh họa: Bạn sử dụng bảng dữ liệu sau, gồm 6 trường: Tên sản phẩm, Giá/Sản phẩm, Số lượng đã bán ra, Tổng số doanh thu trên một loại sản phẩm, Ngày nhập hàng, Loại hàng.

Bảng dữ liệu minh họa mẫu như hình trên

Hãy tính tổng doanh thu tất cả sản phẩm bằng hàm SUM.

=SUM(D2:D9)

Tổng doanh thu tất cả các sản phẩm

Hướng dẫn cách sử dụng hàm FILTER trong Google Sheet đơn giản

Hàm FILTER là gì?

Hàm FILTER là một hàm lọc dữ liệu theo một điều kiện cho trước mà không làm ảnh hưởng gì đến những 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ách để sử dụng hàm FILTER

Công thức:

=FILTER(Range of values; Condition 1; Condition 2,...)

Trong đó:

- Range of values: nghĩa là vùng chứa giá trị bạn muốn lọc.

- Condition 1, Condition 2,...: là các điều kiện đối với giá trị cần lọc. Có thể có điều kiện 2 hoặc không có.

Ví dụ minh họa: Ví dụ bạn sử dụng bảng dữ liệu cũ như phần 1, lọc những sản phẩm được bán trên 10 lần để bạn biết sản phẩm nào đang được ưa chuộng.

=FILTER(A2:A9;C2:C9>10)

Ví dụ minh họa của hàm FILTER

Hướng dẫn cách kết hợp hàm SUM và hàm FILTER trong Google Sheet nhanh chóng

Công thức:

=SUM(FILTER("Range of values"; "Condition 1"; "Condition 2", ...);...)

Giới thiệu các giá trị:

- Range of values: nghĩa là vùng chứa giá trị bạn muốn lọc.

- Condition 1, Condition 2,... : là các điều kiện đối với giá trị cần lọc. Có thể có điều kiện 2 hoặc không có.

- Kết quả hàm FILTER cho ra là một dãy số để hàm SUM tính tổng.

Ví dụ minh họa: Bạn dùng bảng dữ liệu cũ, để tính doanh thu những sản phẩm có số lượng bán lớn hơn hoặc bằng 10 lần.

Công thức:

=SUM(FILTER(D2:D9;C2:C9>=10))

Giải thích:

Khi dùng hàm FILTER lấy tổng doanh thu trên những sản phẩm có số lượng bán lớn hơn hoặc bằng 10. Hàm SUM tính tổng trên dữ liệu trả về của hàm FILTER, kết cho ra sau khi tính là 2042000.

Tổng số doanh thu của những sản phẩm có số lượng bán lớn hơn hoặc bằng 10 lần

Một số ví dụ cơ bản kết hợp giữa hàm SUM và hàm FILTER

Việc kéo dữ liệu từ file khác và lọc theo điều kiện ngày, tháng, năm

Ví dụ: Từ bảng dữ liệu cũ trên, bạn có thể tính tổng doanh thu cho các sản phẩm có nhập hàng vào tháng 4.

Công thức:

=SUM(FILTER(D2:D9;MONTH(E2:E9)=4))

Giải thích:

- Hàm FILTER để xét trong phạm vi E2 đến E9 nếu có tháng là 4 thì trả về dữ liệu tương ứng bên cột D2 đến D9.

- Hàm SUM thì tính tổng dựa trên dữ liệu trả về của hàm FILTER. Kết quả sau cho ra khi tính: 617000.

Tổng của doanh thu cho các sản phẩm có nhập hàng vào tháng 4

Việc kéo dữ liệu từ file khác và lọc theo điều kiện so sánh chữ

Ví dụ: Từ bảng dữ liệu cũ trên, bạn có thể tính tổng số lượng đã bán của loại hàng nước có giá hơn 10000.

Công thức:

=SUM(FILTER(C2:C9;F2:F9="N";B2:B9>10000))

Giải thích:

- Hàm FILTER xét trong phạm vi F2 đến F9, nếu có loại hàng hóa là N thì bạn xét tiếp trong phạm vi tương ứng trong cột B2:B9 xem giá sản phẩm có lớn hơn 10000 không, nếu đúng thì trả về dữ liệu tương ứng bên cột C2 đến C9.

- Hàm SUM tính tổng sẽ dựa trên dữ liệu trả về của hàm FILTER. Kết quả sau cho ra sau khi tính: 47

Tổng số lượng đã bán của các loại hàng nước có giá hơn 10000

Việc kéo dữ liệu từ file khác và lọc theo điều kiện so sánh số

Ví dụ: Từ bảng dữ liệu đã nêu trên, bạn có thể tính tổng doanh thu cho các loại sản phẩm có giá trên 10000.

=SUM(FILTER(D2:D9;B2:B9>10000))

Giải thích:

- Hàm FILTER xét trong phạm vi B2 đến B9 xem giá sản phẩm có lớn hơn 10000 không, nếu đúng thì sẽ trả về dữ liệu tương ứng bên cột D2 đến D9.

- Hàm SUM tính tổng sẽ dựa trên dữ liệu trả về của hàm FILTER. Kết quả cho ra sau khi tính: 1807000.

Tổng doanh thu cho các loại sản phẩm có giá trị trên 10000

Các lỗi thường gặp cơ bản khi kết hợp hàm SUM và hàm FILTER

Lỗi #N/A

Đây là lỗi cơ bản là do không tìm thấy kết quả có điều kiện phù hợp. Để sửa lại lỗi này, bạn phải kiểm tra lại phần điều kiện trong hàm FILTER có kết quả trả về nào không.

Ví dụ: bạn dùng bảng dữ liệu cũ để tính tổng doanh thu hàng hóa có số lượng bán lớn hơn 10.

Công thức sai là: =SUM(FILTER(D2:D9;C2:C9=10))

Công thức đúng là: =SUM(FILTER(D2:D9;C2:C9>10))

Giải thích: Phần công thức sai bị lỗi #N/A chính là do dữ liệu không có hàng hóa nào có số lượng bán bằng 10 và công thức này có lẽ đã hiểu sai đề bài đưa ra.

Lỗi của #N/A

Lỗi #REF

Đây là lỗi do quay vòng, thuật toán đã bị lặp đi lặp lại. Để sửa lỗi này bạn cần phải xem lại dữ liệu đưa vào và điều kiện đặt ra đã đúng chưa nhé!

Ví dụ: bạn sử dụng bảng dữ liệu cũ tính tổng doanh thu hàng hóa có số lượng bán lớn hơn 10.

Công thức sai là: =SUM(FILTER(D2:D;C2:C>10))

Công thức đúng là: =SUM(FILTER(D2:D9;C2:C9>10))

Giải thích: Phần công thức sai là công thức bị sai phạm vi xét Range of values và điều kiện Condition 1.

Lỗi của #REF

Lỗi #ERROR

Đây là lỗi chủ yếu do ghi sai cú pháp của hàm. Để sửa lỗi này bạn cần phải kiểm tra xem cú pháp đã nhập có đúng với cú pháp hàm quy định hay chưa.

Ví dụ: bạn dùng bảng dữ liệu cũ tính tổng doanh thu hàng hóa có số lượng bán lớn hơn 10.

Công thức sai là: =SUM( B2 FILTER(D2:D9;C2:C9>10))

Công thức đúng là: =SUM(FILTER(D2:D9;C2:C9>10))

Giải thích: Phần công thức sai đã ghi sai cú pháp của hàm SUM khi để ô B2 nằm riêng như trên, bạn sửa lại bằng cách xóa đi ô B2 là xong.

Lỗi của #ERROR

Lỗi #VALUE

Đây là lỗi do bạn sử dụng dữ liệu sai. Để sửa lại, bạn kiểm tra lại xem dữ liệu đưa vào khi kết hợp với hàm có đúng không, ví dụ như nếu là kiểu dữ liệu số thì bạn không thể dùng phép nhân.

Ví dụ: Bạn muốn tính tổng số lượng hàng hóa đã bán được trong ngày 2 và số lượng bánh quy đã bán ra.

Công thức sai là: =SUM(FILTER(C2:C9;day(E2:E9)=2); - A2)

Công thức đúng là: =SUM(FILTER(C2:C9;day(E2:E9)=2);C2)

Giải thích: Phần công thức sai vì A2 là chữ nên bạn không thể kết hợp với phép trừ được và sẽ bị sai so với đề bài đưa ra.

Lỗi của #VALUE

Một số lưu ý cần thiết khi kết hợp hàm SUM và hàm FILTER

Hàm SUM và FILTER sẽ không phân biệt chữ hoa hay chữ thường trong lúc nhập công thức, vì vậy bạn có thể nhập filter, sum khi gọi hàm.
Bạn hoàn toàn có thể kết hợp hàm SUM với nhiều hàm FILTER, mỗi hàm FILTER trong SUM có thể có hơn 1 điều kiện nhé!
Nhớ chú ý dữ liệu đưa vào và điều kiện so sánh trong hàm FILTER phải trả về ít nhất một kết quả.
Khi truy vấn các dữ liệu có dạng là text bị bắt buộc bài có dấu nháy đơn ' ', còn dữ liệu là số thì không cần dấu nháy.

Một số bài tập ví dụ về kết hợp hàm SUM và hàm FILTER đơn giản

Cho sẵn bản dữ liệu sau:

Cho bảng dữ liệu bài tập mẫu

Câu 1: Hãy tính tổng số lượng đã bán các sản phẩm có ngày nhập hàng vào tháng 3.

=SUM(FILTER(C2:C15;MONTH(E2:E15)=3))

Tổng số lượng đã bán của các sản phẩm nhập hàng vào tháng 3

Câu 2: Hãy tính tổng doanh thu các sản phẩm nước.

=SUM(FILTER(D2:D15;F2:F15="N"))

Tổng doanh thu của các sản phẩm nước

Những câu hỏi cơ bản thường gặp khi kết hợp hàm SUM và hàm FILTER

Lợi ích khi dùng hàm FILTER kết hợp với hàm SUM là gì?

Trả lời: Sự kết hợp hàm FILTER với hàm SUM sẽ giúp các bạn dễ dàng tính tổng dữ liệu kết hợp với nhiều điều kiện dễ dàng, linh hoạt hơn và không cần dùng đến những công thức phức tạp khác.

Hàm FILTER còn có thể kết hợp với những hàm nào khác?

Trả lời: Ngoài sự kết hợp hàm SUM với FILTER, bạn cũng có thể kết hợp với một số hàm khác như: QUERY, OFFSET, LEFT, RIGHT,...

Trên đây là phần hướng dẫn cách kết hợp hàm SUM và hàm FILTER trong Google Sheet, hy vọng với kiến thức cung cấp trên có thể giúp cho các bạn sử dụng phần mềm này một cách có hiệu quả nhất. Chúc các bạn thành công!