Tóm tắt một loạt các con số có thể khá dễ dàng, ngay cả đối với một người mới sử dụng Google Trang tính. Tuy nhiên, khi nói đến tính tổng các số dựa trên một số điều kiện, đó là lúc mọi thứ bắt đầu trở nên phức tạp.
Chà, không hoàn toàn.
Google Trang tính có một hàm SUMIF tiện dụng để giúp bạn tính tổng có điều kiện.
Khi bạn hiểu cú pháp cơ bản và xem một vài ví dụ, bạn sẽ muốn sử dụng hàm SUMIF bất cứ khi nào có thể.
Trong hướng dẫn này, chúng tôi sẽ giúp bạn hiểu cú pháp cơ bản của hàm SUMIF trong Google Trang tính và xem xét một số trường hợp sử dụng để giúp bạn biết cách nó có thể được áp dụng.
Hàm SUMIF làm gì?
Hàm SUMIF chỉ đơn giản là sự kết hợp của các hàm SUM và IF. Ứng dụng của nó rất đơn giản.
Hàm chỉ đơn giản là xem qua một loạt các ô để tìm những ô phù hợp với điều kiện đã cho.
Khi tìm thấy một kết quả phù hợp, số tương ứng với ô sẽ được đưa vào nhóm các số đã chọn.
Khi hàm đã quét xong tất cả các ô trong phạm vi, nó sẽ lấy nhóm các số đã chọn và tính tổng chúng!
Cú pháp của hàm SUMIF trong Google Trang tính
Cú pháp cho hàm SUMIF là:
= SUMIF (phạm vi; điều kiện; [диапазон_суммы])
Đây,
- phạm vi là một nhóm ô được kiểm tra một điều kiện.
- Điều kiện là tiêu chí mà một ô trong phạm vi phải đáp ứng để được coi là phù hợp. Điều kiện có thể là một giá trị (số, văn bản, ngày tháng) hoặc một tham chiếu đến một ô chứa tiêu chí.
- sum_range (dải tổng) là một tham số tùy chọn. Nếu được bật, đây là một dải ô chứa các giá trị sẽ được thêm vào nếu số tương ứng trong dải ô phù hợp với điều kiện. Nếu không bao gồm sum_range, phạm vi trong tham số đầu tiên cũng được giả định là sum_range.
Như bạn có thể thấy từ cú pháp ở trên, có hai cách để sử dụng hàm SUMIF.
- không có phạm vi số tiền riêng biệt
- với một phạm vi số tiền riêng biệt.
Nếu tất cả ba tham số được chỉ định, hàm SUMIF sẽ kiểm tra từng ô trong phạm vi để xem nó có đáp ứng điều kiện hay không.
Nếu một ô phù hợp với điều kiện, thì hàm SUMIF nhận giá trị ô tương ứng trong tổng phạm vi và đưa nó vào tổng cuối cùng.
Nếu chỉ hai tham số đầu tiên được chỉ định, thì hàm SUMIF sẽ lặp qua từng ô trong phạm vi và chỉ thêm những ô phù hợp với điều kiện.
Cuối cùng, nó trả về tổng của tất cả các ô trong phạm vi phù hợp với tiêu chí.
Các ví dụ về sử dụng hàm SUMIF (Ví dụ)
Hàm SUMIF là một hàm đa năng đến mức nó có thể được sử dụng theo nhiều cách để hoàn thành nhiều nhiệm vụ.
Ví dụ: bạn có thể sử dụng nó cho:
- Tìm tổng của chỉ số dương hoặc số âm trong một phạm vi
- Tìm tổng doanh thu cho bộ phận
- Tìm tổng chi phí cho đến một ngày nhất định
Các ứng dụng này chỉ mới chạm vào bề mặt. Có một số tính năng và lĩnh vực khác mà hàm SUMIF có thể rất hữu ích.
Tuy nhiên, trong bài viết này, chúng ta hãy chỉ xem xét ba trường hợp sử dụng này. Khi giải thích các ký tự đại diện, chúng tôi sẽ bao gồm một trường hợp sử dụng đặc biệt khác.
Sử dụng SUMIF với điều kiện số
Trường hợp sử dụng đầu tiên là tìm tổng của chỉ số dương hoặc số âm trong một phạm vi.
Hãy xem một tập dữ liệu bao gồm sự kết hợp của các số nguyên dương và âm như hình dưới đây.
Chúng tôi chỉ muốn thêm các số dương trong phạm vi A2: A10. Đây là cách áp dụng hàm SUMIF trong trường hợp này:
- Chọn ô mà bạn muốn hiển thị kết quả của tổng (trong trường hợp của chúng tôi là C2).
- Nhập công thức sau vào ô: = SUMIF (A2: A10, ”> = 0”) Lưu ý rằng chúng tôi không bao gồm tham số thứ ba trong trường hợp này.
- Nhấn phím quay lại
Điều này sẽ hiển thị tổng các số dương trong ô C2.
Giải thích công thức
Trong ví dụ này, hàm SUMIF đã kiểm tra mọi ô từ A2 đến A10 và chỉ chọn những ô có giá trị lớn hơn hoặc bằng 0.
Sau đó, nó cộng tất cả các giá trị đã chọn và hiển thị kết quả trong ô C2.
Ghi chú. Thay vào đó, nếu bạn muốn hiển thị tổng của tất cả các số âm, tất cả những gì bạn phải làm là thay đổi điều kiện thành “<0".
Sử dụng SUMIF với điều kiện văn bản
Hãy xem một ví dụ khác.
Dưới đây, chúng tôi có một tập dữ liệu mà chúng tôi muốn tóm tắt doanh thu của bộ phận vật liệu đóng gói.
Phần sau cho thấy cách này có thể được thực hiện bằng cách sử dụng công thức SUMIF:
- Chọn ô mà bạn muốn hiển thị kết quả của tổng (trong trường hợp của chúng tôi là D2).
- Nhập công thức sau vào một ô: = SUMIF (A2: A10, “Package”, B2: B10)
- Nhấn phím quay lại
Ô D2 sẽ hiển thị tổng doanh thu của bộ phận đóng gói.
Giải thích công thức
Trong ví dụ này, hàm SUMIF kiểm tra mọi ô từ A2 đến A10 và chỉ tìm kiếm những ô có chứa giá trị “Bao bì”.
Đối với mỗi ô có chứa từ “Đóng gói”, hàm SUMIF chọn giá trị bán hàng tương ứng trong cột B. Sau đó, nó thêm tất cả các giá trị đã chọn và hiển thị kết quả trong ô D2.
Ghi chú. Nếu bạn muốn hiển thị tổng doanh thu cho bất kỳ bộ phận nào khác, bạn có thể chỉ cần thay thế điều kiện trong tham số thứ hai bằng tên bộ phận bạn muốn. Đừng quên đặt tên bộ phận trong dấu ngoặc kép.
Sử dụng SUMIF với điều kiện ngày tháng
Bây giờ, hãy xem một ví dụ mà chúng tôi muốn tìm tổng chi phí cho đến một ngày nhất định, chẳng hạn như ngày 1 tháng 9 năm 2019.
Chúng tôi sẽ sử dụng tập dữ liệu được hiển thị bên dưới để trình bày cách sử dụng hàm SUMIF cho vấn đề này.
- Chọn ô mà bạn muốn hiển thị kết quả của tổng chi phí (trong trường hợp của chúng tôi là D2).
- Nhập công thức sau vào một ô: = SUMIF (A2: A10, ”<” & DATE (2019,9,1), B2: B10).
- Nhấn phím quay lại
Điều này sẽ hiển thị tổng chi phí cho đến DATE (2019,9,2), nghĩa là chỉ ngày 1 tháng 9 năm 2019. Chúng tôi chỉ sử dụng hàm DATE để biểu thị ngày.
Ghi chú. Chúng ta phải thêm toán tử (“<") vào ngày bằng dấu và (&).
Giải thích công thức
Trong ví dụ này, hàm SUMIF kiểm tra mọi ô từ A2 đến A10 và chỉ tìm kiếm những ô có chứa ngày trước ngày 1 tháng 9 năm 2019.
Đối với mỗi ô phù hợp, hàm SUMIF chọn giá trị chi phí tương ứng từ cột B.
Sau đó, nó tổng hợp tất cả các giá trị chi phí đã chọn và hiển thị kết quả trong ô D2.
Có một số cách khác để sử dụng hàm SUMIF.
Bất cứ khi nào bạn cần tìm tổng các giá trị dựa trên một điều kiện, bạn có thể sử dụng hàm này bằng cách chỉ cần sáng tạo với phần “có điều kiện” của công thức.
Sử dụng SUMIF với các ký tự đại diện
Một cách sáng tạo để sử dụng hàm SUMIF là đưa các ký tự đại diện vào phần có điều kiện của hàm.
Ví dụ: giả sử bạn có tập dữ liệu sau chứa số lượng điện thoại di động khác nhau trong kho.
Nếu bạn muốn biết tổng số của tất cả các kiểu máy Samsung, bạn có thể sử dụng các ký tự đại diện như ‘*’ hoặc ‘?’ trong hàm SUMIF của bạn như thế này:
- Chọn ô mà bạn muốn hiển thị tổng kết quả bán hàng (trong trường hợp của chúng tôi là D2).
- Nhập công thức sau vào một ô: = SUMIF (A2: A10; “Samsung *”, B2: B10)
- Nhấn phím quay lại
Điều này sẽ hiển thị số điện thoại Samsung trong ô D2.
Ghi chú. Ký tự đại diện dấu hoa thị
thường được kết hợp với cơ sở của một từ hoặc chữ cái để tìm các biến thể khác nhau của thuật ngữ.
Giải thích công thức
Trong ví dụ này, điều kiện “Samsung *” có nghĩa là “tìm tất cả các ô có chứa từ Samsung”.
Nó không nhất thiết phải là một đối sánh chính xác, nhưng ô phải chứa từ “Samsung” cùng với bất kỳ ký tự nào khác.
Khi tìm thấy kết quả khớp, hàm SUMIF lấy giá trị số lượng tương ứng với ô phù hợp và thêm nó vào danh sách các giá trị số lượng đã chọn.
Sau khi xem qua tất cả các mẫu, hàm SUMIF sẽ cộng các giá trị số lượng đã chọn và hiển thị kết quả trong ô C2.
Bạn có thể dùng “?” ký tự đại diện theo cùng một cách. ‘?’ Một ký tự đại diện được sử dụng để đại diện cho một ký tự duy nhất ở bất kỳ đâu trong chuỗi văn bản.
Vì vậy, nếu bạn muốn tìm, chẳng hạn như tất cả các mẫu Apple iPhone X, bạn có thể sử dụng “Apple iPhone X?” trong điều kiện.
Những điều cần nhớ
- Có một số điều quan trọng cần ghi nhớ khi sử dụng công thức SUMIF để bạn có thể áp dụng nó vào dữ liệu của mình hiệu quả hơn.
- Khi sử dụng phạm vi số lượng riêng biệt, hãy nhớ đảm bảo rằng cả phạm vi số lượng và phạm vi số lượng có cùng số ô.
- Hàm SUMIF không phân biệt chữ hoa chữ thường.
- Nếu điều kiện chứa giá trị văn bản, ngày tháng hoặc các ký tự đại diện, thì nó phải được đặt trong dấu ngoặc kép.
- Tham chiếu ô trong điều kiện không được đặt trong dấu ngoặc kép.
- Nếu điều kiện bao gồm sự kết hợp của toán tử và tham chiếu ô hoặc hàm khác (chẳng hạn như hàm DATE), thì chúng phải được kết hợp với dấu và (&). Ví dụ,
- «<» & DATE (2019,9.1)
- «>» И B2
- Chỉ có thể sử dụng một điều kiện trong hàm SUMIF. Nếu bạn muốn sử dụng nhiều hơn một điều kiện, bạn sẽ cần sử dụng hàm SUMIFS để thay thế.
Bạn không thể thay thế các tham chiếu range và sum_range bằng mảng.
Trong hướng dẫn này, chúng tôi đã chỉ cho bạn cách sử dụng hàm SUMIF trong Google Trang tính. Đây là một tính năng rất linh hoạt có thể được áp dụng trong nhiều lĩnh vực.
Chúng tôi đã chỉ cho bạn cách bạn có thể sử dụng hàm này trong các trường hợp sử dụng khác nhau và khuyên bạn nên sử dụng hàm SUMIF bất cứ khi nào bạn cần tính tổng các số dựa trên một điều kiện.
bài chuyển hướng
