Các hàm SUMIF / SUMIFS trong Google Sheets giúp tính tổng một cột dựa trên sự xuất hiện của các tiêu chí cụ thể trong cột khác. Trong nhiều trường hợp, việc xử lý các giá trị trùng lặp không phải là vấn đề quan trọng. Tuy nhiên, chúng ta có thể gặp khó khăn khi muốn loại bỏ giá trị trùng lặp khi sử dụng SUMIF / SUMIFS trong Google Sheets. Bài viết này sẽ hướng dẫn bạn cách làm điều này.
Các giá trị trùng lặp trong các hàm SUMIF / SUMIFS: Ý nghĩa và cách loại bỏ
Giả sử bạn có một bảng dữ liệu trong đó cột A chứa các mặt hàng, cột B chứa số lượng đã đặt hàng, cột C chứa tên khách hàng và cột D chứa số đơn đặt hàng. Hãy tưởng tượng bạn muốn tính tổng số lượng đặt hàng của mặt hàng “Cát màu đen 3/16” mà không tính các giá trị trùng lặp liên quan đến khách hàng.
Để tính tổng đơn đặt hàng của mặt hàng “Cát màu đen 3/16” (bất kể khách hàng), bạn có thể sử dụng các công thức sau:
=SUMIF(A2:A, "Cát màu đen 3/16", B2:B)// trả về 675=SUMIFS(B2:B, A2:A,"Cát màu đen 3/16")// trả về 675
Nếu bạn chỉ muốn tính tổng đơn đặt hàng của mặt hàng “Cát màu đen 3/16” đặc biệt cho “Khách hàng 1”, bạn có thể sử dụng công thức sau:
=SUMIFS(B2:B,A2:A,"Cát màu đen 3/16",C2:C,"Khách hàng 1")// trả về 225
Đến đây, chúng ta đã hiểu về khái niệm giá trị trùng lặp trong SUMIF / SUMIFS trong Google Sheets.
Cách loại bỏ giá trị trùng lặp trong SUMIF / SUMIFS
Bây giờ, chúng ta sẽ tìm hiểu cách loại bỏ các giá trị trùng lặp trong tính toán SUMIF / SUMIFS.
Sử dụng một cột hỗ trợ
Giả sử dữ liệu mẫu ở Sheet1!A1:D. Nhập công thức SORTN sau đây vào Sheet2!A1.
=SORTN(Sheet1!A2:D,9^9,2,Sheet1!A2:A&Sheet1!C2:C,1)
Hàm SORTN được sử dụng để loại bỏ giá trị trùng lặp. Nó trả về các giá trị duy nhất dựa trên hai cột là cột A và cột C.
Cú pháp của hàm SORTN trong Google Sheets:
SORTN(range, [n], [display_ties_mode], [sort_column], [is_ascending], [sort_column2, …], [is_ascending2, …])
Trong đó:
- Sheet1!A2:D: Đây là phạm vi dữ liệu mà bạn muốn loại bỏ các giá trị trùng lặp.
- 9^9: Số này được sử dụng như một số rất lớn để đảm bảo rằng tất cả các hàng đều được bao gồm sau khi xử lý.
- 2: Số display_ties_mode này chỉ ra rằng bạn muốn loại bỏ giá trị trùng lặp.
- Sheet1!A2:A&Sheet1!C2:C: Phần này là điểm quan trọng của công thức. Nó ghép các giá trị từ cột A và cột C cho mỗi hàng. Việc lặp lại các giá trị này trong các hàng tiếp theo sẽ được coi là giá trị trùng lặp.
- 1: Đối số này chỉ định thứ tự sắp xếp, với 1 kết hợp chỉ thứ tự tăng dần.
Bây giờ bạn có thể sử dụng công thức SUMIF / SUMIFS thông thường trong dữ liệu mới này có chứa các bản ghi duy nhất liên quan đến mặt hàng và khách hàng.
=SUMIF(A1:A,"Cát màu đen 3/16", B1:B)
=SUMIFS(B1:B, A1:A, "Cát màu đen 3/16")
Điều này tương đương với loại bỏ các giá trị trùng lặp trong dữ liệu gốc khi sử dụng SUMIF / SUMIFS.
Phương thức công thức không sử dụng cột hỗ trợ
SUMIFS và SUMIF không hoạt động với các cột ảo trong phạm vi tổng. Do đó, rõ ràng là chúng ta không thể sử dụng dữ liệu trên trong các hàm này.
Thay vào đó, chúng ta có thể sử dụng truy vấn QUERY như sau:
=QUERY(SORTN(Sheet1!A2:D, 9^9, 2, Sheet1!A2:A&Sheet1!C2:C, 0), "select sum(Col2) where Col1='Cát màu đen 3/16' label sum(Col2)''")
Câu lệnh này tính tổng cột 2 khi giá trị trong cột 1 bằng “Cát màu đen 3/16”.
Vì vậy, QUERY là một phương thức thay thế cho SUMIF / SUMIFS loại bỏ giá trị trùng lặp trong Google Sheets.
Kết luận
Nhu cầu của bạn khi sử dụng SUMIF / SUMIFS loại bỏ các giá trị trùng lặp có thể khác với ví dụ đã nêu ở trên. Tuy nhiên, bạn có thể coi điều này như một điểm khởi đầu.
Hãy mô tả yêu cầu cụ thể của bạn trong các bình luận bên dưới. Tôi sẽ xem xét từng ý kiến liên quan một cách cá nhân.
Liên quan: Mastering Unique Summing in Google Sheets Without SUMUNIQUEIFS
