Nếu bạn đang xử lý công việc tổng hợp dữ liệu trong Excel, thì các hàm SUMIFS và SUMIF là những gì bạn cần. Hướng dẫn trong bài viết này sẽ giải thích ngắn gọn cú pháp của các hàm và cách sử dụng chúng …
Đối với những bạn thường xuyên phải tính toán hệ số công việc trong excel thì việc liên tục bổ sung kiến thức trong thực tế bằng một số ví dụ công thức của hàm SUMIF và SUMIFS trong excel là vô cùng quan trọng. Nó không chỉ giúp tăng hiệu quả trong quá trình tự học excel cơ bản mà còn giúp tăng năng suất công việc lên rất nhiều. Trong bài viết dưới đây, cùng 123job tìm hiểu về hàm SUMIF, cách sử dụng hàm SUMIFS trong excel và các ví dụ cụ thể về nó từ hàm SUMIF cơ bản đến hàm SUMIFS nâng cao…
I. Phân biệt sumif và sumif. chức năng
1. Sumif và sumifs. khái niệm chức năng
Hàm SUMIF được sử dụng để tính tổng dữ liệu có điều kiện, dựa trên một điều kiện.
Hàm SUMIFS trong Excel được sử dụng để tìm tổng giá trị hợp lệ dựa trên nhiều điều kiện. So với SUMIF, cú pháp của hàm SUMIFS phức tạp hơn một chút.
2. Sumif và sumifs. cú pháp hàm
Cấu trúc hàm sumif được thiết lập như sau:
= SUMIF (phạm vi, điều kiện, [sum_range])
Cấu trúc hàm sumifs trong excel được thiết lập như dưới đây, lưu ý rằng các đối số sau có thể được sử dụng để xây dựng các sumifs nâng cao, sumifs có điều kiện ngày tháng …:
= SUMIFS (sum_range, tiêu_phí1, tiêu chí1, [criter_range2, criteria2]…)
II. Cách sử dụng các hàm sumif và sumifs (các ví dụ cụ thể hơn để tham khảo)
1. Cách sử dụng sumif. hàm số
Hãy cùng 123job.vn học excel cơ bản bằng hàm SUMIF.
Dạng hàm
= SUMIF (phạm vi, điều kiện, [sum_range])
range – nghĩa là phạm vi ô sẽ được đánh giá theo điều kiện bạn đưa ra, lưu ý rằng nó là bắt buộc.
tiêu chí – điều kiện cần được đáp ứng, là bắt buộc
sum_range – các ô tính tổng nếu các điều kiện đã chọn được đáp ứng, là tùy chọn.
Giả sử chúng ta có bảng tổng hợp các chỉ tiêu về thu nhập và thuế thu nhập cá nhân như sau:
Tổng hợp các chỉ tiêu về thu nhập và thuế thu nhập cá nhân
Trong Excel, bảng được trình bày chi tiết như sau:
Trong Excel, bảng được trình bày chi tiết
Ví dụ
Sử dụng hàm SUMIF để tính tổng số thuế thu nhập cá nhân của những người có thu nhập thống kê dưới 50 triệu đồng.
Để giải quyết ví dụ này, về cơ bản chúng ta cần xác định 3 tham số của hàm SUMIF, cụ thể:
Phạm vi: Phạm vi cột chứa tổng thu nhập, đây là B4: B9
Tiêu chí là
Sum_range là phạm vi cột chứa thuế thu nhập cá nhân phải tổng, đây là H4: H9
Sau đó, chúng tôi thiết lập công thức: = SUMIF (B4: B9, ”
Cài đặt công thức
Nhấn Enter và sẽ nhận được số thuế thu nhập cá nhân phải nộp cho những người có thu nhập dưới 50.000.0000 là 2.229.775.
Nhận số thuế thu nhập cá nhân phải nộp
Chú ý khi sử dụng SUMIF. hàm số
Khi sum_range bị bỏ qua, tổng của hàm đó sẽ được tính theo Range.
Tiêu chí là một trường chứa các chữ cái hoặc ký hiệu toán học phải được đặt trong dấu ngoặc kép “”.
Phạm vi xác định định dạng số của dữ liệu có thể được cung cấp là số mà không cần sử dụng dấu ngoặc đơn.
Các nhân vật ? và * đều có thể được sử dụng trong Tiêu chí. Một dấu hỏi phù hợp với bất kỳ ký tự xuất hiện đơn lẻ nào; dấu hoa thị khớp với bất kỳ chuỗi ký tự nào. Nếu bạn muốn tìm một dấu chấm hỏi hoặc dấu hoa thị thực sự, hãy nhập dấu ngã (~) trước ký tự.
Nếu chúng ta muốn tính tổng giá trị có nhiều hơn 1 điều kiện thì chúng ta cần sử dụng hàm sumifs, ví dụ như ứng dụng của hàm sumifs điều kiện ngày, lãi suất, trạng thái của sự vật …
2. Cách sử dụng hàm sumifs để tính tổng nhiều điều kiện trong học excel cơ bản
Cấu trúc hàm SUMIFS trong Excel có dạng
= SUMIFS (sum_range, tiêu chuẩn1, tiêu chí1, tiêu chí_phạm vi2, tiêu chí2,…).
Trong đó:
Sum_range: là trường của các ô cần tính tổng trong bảng dữ liệu, giá trị trống và giá trị văn bản sẽ lần lượt bị bỏ qua, tham số là dữ liệu bắt buộc.
Criteria_range1: phạm vi dữ liệu được kiểm tra với điều kiện tiêu chí1, đây hoàn toàn là giá trị bắt buộc.
Criteria1: điều kiện cụ thể áp dụng cho criteria_range1, có thể là một số, biểu thức, tham chiếu trực tiếp đến các ô để xác định các ô nào trong criteria_range1 sẽ được tính tổng, cũng là giá trị phần tử bắt buộc.
Criteria_range2, criteria2,…: trực tiếp tùy chọn các phạm vi và điều kiện bổ sung, theo đó tối đa 127 cặp tiêu chí, tiêu chí.
Ví dụ về các ứng dụng, sử dụng sumifs. hàm số
Sử dụng cấu trúc hàm sumifs, hay hàm sumifs nâng cao, chúng ta có thể ứng dụng linh hoạt trong nhiều mục đích như hàm sumifs điều kiện ngày tháng, hàm sumifs nâng cao… Trong ví dụ, chúng ta sẽ tính tổng. kèm theo một số điều kiện khác nhau với bảng dữ liệu bên dưới.
Tính tổng với một số điều kiện
Áp dụng cấu trúc hàm sumifs, hãy tính tổng sản phẩm mà nhân viên Hoài bán được với đơn giá dưới 400.000 đồng.
Trong ô nhập liệu, chúng ta sẽ điền trực tiếp vào công thức
= SUMIFS (D2: D7, C2: C7, “Hoài”, E2: E7, ”
Trong đó:
D2: D7 là vùng cần tính tổng hạng mục.
C2: C7 là vùng chứa các giá trị điều kiện tên nhân viên.
“Hoài” là điều kiện cụ thể của tên nhân viên cần tính số lượng các mặt hàng có trong vùng C2: C7.
E2: E7 là điều kiện mặt hàng.
“400.000” là điều kiện để chứa các mục hoàn toàn nằm trong vùng E2: E7.
Trực tiếp điền vào công thức
Kết quả của tổng số mục sử dụng hàm sumifs sẽ được trình bày như hình dưới đây.
Kết quả là tổng số mục
Ngoài ra, bạn cũng có thể thử ứng dụng hàm sumifs điều kiện ngày tháng – đây là trường hợp xuất hiện khá nhiều trong tin học văn phòng hàng ngày.
Lưu ý về cách sử dụng hàm SUMIFS trong Excel
Mỗi ô trong phạm vi, sum_range trong cấu trúc sumifs và hàm sumifs nâng cao sẽ được tính tổng khi tất cả các điều kiện tương ứng đã được xác định là đúng cho ô đó.
Các ô trong sum_range chứa giá trị TRUE được coi là 1, các ô chứa giá trị FALSE được coi là 0.
Mỗi dải tiêu chí phải có cùng kích thước lựa chọn như sum_range, dải tiêu chí và sum_range phải có cùng số hàng và cùng số cột.
Criteria là một trường có thể sử dụng ký tự dấu chấm hỏi (?) Thay vì một ký tự duy nhất, dấu hoa thị
thay vì một chuỗi. Nếu điều kiện là dấu chấm hỏi hoặc dấu hoa thị, bạn phải nhập dấu ~ vào trước, giá trị của điều kiện là văn bản trong dấu “.
Ngoài ứng dụng trên chúng ta có thể có thêm nhiều ứng dụng sumifs khác như sumifs có điều kiện ngày tháng, lợi nhuận định kỳ, tình trạng kinh tế …
III. Cách sửa lỗi #VALUE! lỗi trong SUMIF / SUMIFS. hàm số
Trong quá trình học excel cơ bản, đặc biệt là mày mò các hàm sumif và sumifs, có khá nhiều trường hợp thao tác nhầm dẫn đến lỗi ở dạng hiển thị giá trị. Lỗi tường phổ biến nhất là “#VALUE!”. Dưới đây là 3 cách sửa lỗi phổ biến cho lỗi này.
1. Công thức tham chiếu đến các ô trong sổ làm việc đã đóng
Hàm SUMIF / SUMIFS có thể tham chiếu đến một ô hoặc phạm vi trong sổ làm việc đã đóng, dẫn đến lỗi #VALUE! .
Lưu ý: Đây là sự cố đã biết với một số hàm Excel khác như COUNTBLANK COUNTIF, COUNTIFS, để đặt tên cho một số trường.
Giải pháp: Mở sổ làm việc có thể được biểu diễn trong công thức, rồi nhấn phím F9 để làm mới công thức. Bạn cũng có thể tránh vấn đề này bằng cách sử dụng hàm SUM hoặc hàm IF cùng nhau trong một công thức mảng.
2. Chuỗi tiêu chí hơn 255 ký tự
Hàm SUMIF / SUMIFS trả về kết quả không chính xác khi bạn cố gắng so khớp các chuỗi dài hơn 255 ký tự.
Giải pháp: Rút ngắn chuỗi giá trị nếu có thể. Nếu bạn không thể rút ngắn chuỗi, hãy sử dụng hàm CONCATENATE hoặc toán tử dấu và (&) để ngắt giá trị thành nhiều chuỗi. Ví dụ:
= SUMIF (B2: B12; “chuỗi dài” & “một chuỗi dài khác”)
3. Trong hàm SUMIFS, đối số dải_văn_bố không được hiển thị nhất quán với đối số dải_đoàn_có.
Các đối số phạm vi phải luôn thực sự giống hệt nhau trong hàm SUMIFS. Điều đó có nghĩa là tất cả các đối số criteria_range và total_range sẽ tham chiếu đến cùng một số hàng và cột cụ thể.
dẫn đến lỗi #VALUE!
Dẫn đến #VALUE!
Giải pháp: Tiếp tục với ví dụ cụ thể này, hãy thay đổi dải_ô_tổng thành C2: C12 và thử lại công thức.
IV. Tổng kết
Trên đây là cách học excel cơ bản với hàm SUMIF, thực ra rất dễ phải không các bạn. Bạn chỉ cần xác định đúng phạm vi cột cần thỏa mãn từng tiêu chí tương ứng, tiêu chí và phạm vi tính tổng của từng cột. Nếu bạn cần tính tổng dữ liệu theo nhiều điều kiện thì hãy nhớ sử dụng hàm SUMIFS đã giới thiệu ở trên, dễ dàng và nhanh chóng hơn rất nhiều.
Bài viết này giúp bạn nắm bắt được thông tin về các thủ thuật khi sử dụng các ứng dụng văn phòng. Để từ đó có thể sử dụng trực tiếp những thủ thuật này để đáp ứng các yêu cầu của bạn. Việc hiểu biết các thông tin trên sẽ giúp cho công việc của bạn được thực hiện dễ dàng và thuận tiện hơn. Ngoài ra, nếu bạn còn điều gì thắc mắc, hãy truy cập website: https://www.amorstay.com.vn/ để biết thêm các thông tin chi tiết hơn nhé.
Comments