Bạn đang xem bài viết Cách Dùng Hàm Sumif Và Sumifs Qua Ví Dụ Cụ Thể 2022 được cập nhật mới nhất trên website Hoisinhvienqnam.edu.vn. Hy vọng những thông tin mà chúng tôi đã chia sẻ là hữu ích với bạn. Nếu nội dung hay, ý nghĩa bạn hãy chia sẻ với bạn bè của mình và luôn theo dõi, ủng hộ chúng tôi để cập nhật những thông tin mới nhất.
Phân biệt cách dùng hai hàm sumif và sumifs
Hàm sumif và sumifs là những hàm phổ biến trong các phép toán của Excel, cùng được sử dụng để tính tổng có điều kiện. Nhưng sumif và sumifs lại mang những cú pháp, điều kiện áp dụng khác nhau.
Cách dùng của hàm sumif
Sumif chính là một hàm được dùng để tính tổng của một đối tượng nào đó với một điều kiện cho trước. Do đó, cú pháp của hàm Sumif cũng có phần rườm rà hơn so với hàm Sum thông thường.
Range: hiểu đơn giản đây chính là vùng điều kiện, là tập hợp nhiều ô, dải ô, vùng bắt buộc phải có và được đánh giá theo điều kiện yêu cầu.
Criteria: là điều kiện bắt buộc mà bạn cần tính tổng.
Sum_range: là vùng tính tổng. Tại đây, chính là các dải ô, vùng tùy chọn mà bạn cần tính tổng theo yêu cầu.
Cách dùng của hàm sumifs
=Sumifs(Sum_range, criteria range1, criteria1, criteria range2, criteria2,…, criteria range127, criteria127)
Do đó, công việc của bạn là cần xác định chính xác điều kiện, số điều kiện, vùng điều kiện, vùng cần tính tổng, để sử dụng sumifs đúng cách nhất. Thông thường, sumifs được áp dụng với 2 điều kiện trở lên. Đặc biệt, phép toán sumifs chỉ được thực hiện khi các điều kiện phù hợp với ô, dãi ô đó.
Một số lưu ý khi sử dụng hàm sumif và sumifs
Để sử thành thục hai loại hàm này, bạn cần ghi nhớ lưu ý sau, để tránh các lỗi trong tính toán.
Nếu điều kiện là biểu thức logic, biểu thức dạng text cần được đặt trong cặp dấu nháy kép (” “).
3 đối số của sumifs bao gồm sum_range, criteria range1, criteria1 là bắt buộc.
Sum_range là đối thứ nhất của sumifs, nhưng lại là đối số thứ 3 của sumif. Do đó, bạn cần phải đặt tham số đúng thứ tự để phép toán được thực hiện.
Dùng “&” để nối các toán tử của điều kiện với một hàm khác nằm trong sumifs.
Trong criteria, “*” và “?” được dùng làm ký tự đại diện. Hoặc bạn có thể thêm dấu “~” phía trước, để sử dụng như các ký tự bình thường.
Đối số sum range và range trong sumif không bắt buộc có cùng kích thước. Nhưng ngược lại, trong sumifs, criteria range phải chứa một số ô, hàng của sum range.
Sum range trong sumifs có thể là một ô. Và sum_range trong sumif có thể bỏ qua.
Việc sử dụng hàm sumif và sumifs cũng dễ dàng phải không bạn :-). Hy vọng qua bài viết này bạn có thể phân biệt được sự giống nhau, khác nhau giữa hai hàm sumif và sumifs. Từ đó, biết cách sử dụng, áp dụng sao cho đúng nhất, phù hợp nhất. Đặc biệt đừng quên truy cập chúng tôi để biết thêm nhiều kiến thức bổ ích khác.
Hàm Sumif Trong Excel: Ví Dụ Công Thức Cụ Thể Từng Trường Hợp
Hàm SUMIF tính tổng theo điều kiện hay tiêu chí mà bạn đưa ra.
Nó có cú pháp và tham số sau:
Range (Bắt buộc) – vùng dữ liệu được đánh dấu theo tiêu chí.
Criteria (Bắt buộc) – điều kiện hay tiêu chí đưa ra để đối chiếu với vùng dữ liệu ở tham số đầu tiên . Nó có thể được cung cấp dưới dạng một số, văn bản, ngày tháng, tham chiếu ô, biểu thức logic hoặc hàm.
Sum_range (tùy chọn) – vùng dữ liệu sẽ tính tổng . Nếu bị bỏ qua, thì phạm vi sẽ được cộng lại.
Hàm có sẵn trong tất cả các phiên bản Excel 365, Excel 2019, Excel 2016, Excel 2013, Excel 2010 và các phiên bản cũ hơn.
Cho người mới bắt đầu, chúng ta hãy xây dựng hàm SUMIF trong Excel ở dạng đơn giản nhất.
Trong bảng mẫu bên dưới, giả sử bạn muốn nhận được tổng doanh số cho một vùng cụ thể, chẳng hạn như North. Để hoàn tất, chúng ta xác định các tham số sau:
Range – danh sách các vùng (B2:B10).
Criterion – “North” hay ô chứa vùng quan tâm (F1).
Sum_range – số tiền bán hàng được cộng vào (C2:C10)
Đặt các tham số lại với nhau, chúng ta nhận được các công thức sau:
=SUMIF(B2:B10, “north”, C2:C10) =SUMIF(B2:B10, F1, C2:C10)
Cả hai chỉ tính tổng các ô North:
Làm thế nào để sử dụng hàm SUMIF trong Excel
Thoạt nhìn, SUMIF là một hàm có cú pháp khá phức tạp . Những lưu ý sau đây sẽ giúp bạn hiểu rõ hơn về logic bên trong của nó và tránh được những lỗi thường gặp.
Cú pháp của hàm SUMIF chỉ có 1 điều kiện. Để tính tổng với nhiều tiêu chí, hãy sử dụng hàm SUMIFS (cộng các ô đáp ứng tất cả các điều kiện) hoặc xây dựng công thức SUMIF với tiêu chí OR (tính tổng các ô đáp ứng bất kì điều kiện nào).
Nếu tham số tiêu chí bao gồm giá trị văn bản, ký tự đại diện hoặc phép toán logic theo sau là văn bản, số hoặc ngày, hãy đặt toàn bộ tiêu chí trong dấu ngoặc kép. Ví dụ:
=SUMIF(B2:B10, “north*”, C2:D10)
=SUMIF(C2:C10, “<=9/10/2020”, B2:B10)
Khi một phép toán logic được theo sau bởi một tham chiếu ô hoặc một hàm khác, các tiêu chí phải được cung cấp dưới dạng một chuỗi. Đối với điều này, bạn sử dụng dấu và (&) để nối một phép toán logic và một tham chiếu hoặc hàm. Ví dụ:
=SUMIF(C2:D10, “<=”&TODAY(), B2:B10)
Xin lưu ý rằng các phép toán so sánh được đặt trong dấu ngoặc kép trong khi tham chiếu ô thì không.
Range và sum_range phải có cùng kích thước
Để công thức SUMIF hoạt động chính xác, tham số sum_range phải có cùng kích thước với dải ô, nếu không bạn có thể nhận được kết quả sai lệch. Vấn đề là sum_range chỉ xác định ô phía trên bên trái của phạm vi sẽ được tính tổng, vùng còn lại được xác định bởi kích thước và hình dạng của tham số phạm vi.
=SUMIF(B2:B10, “north”, C2:D10)
Mặc dù SUMIF có thể xử lý một hằng số mảng trong các tiêu chí như được hiển thị giống như ví dụ này, nó không hỗ trợ các mảng trong dải ô và sum_range. Hai tham số này chỉ có thể là vùng giá trị.
SUMIF không phân biệt chữ hoa chữ thường
Theo thiết kế, SUMIF trong Excel không phân biệt chữ hoa chữ thường, nghĩa là nó coi chữ hoa và chữ thường như nhau. . Để tạo công thức SUMIF phân biệt chữ hoa chữ thường, hãy sử dụng hàm SUMPRODUCT cùng với EXACT.
Ví dụ về hàm SUMIF trong Excel
Vì bạn đã biết các quy tắc chính của việc sử dụng hàm SUMIF trong Excel, đã đến lúc áp dụng kiến thức thực tế và cải thiện các kỹ năng đã học.
Ví dụ 1: SUMIF lớn hơn và nhỏ hơn
Để tính tổng các số lớn hơn hoặc nhỏ hơn một giá trị cụ thể, hãy định dạng tiêu chí SUMIF bằng một trong các phép toán logic sau:
Nếu điều kiện nằm trong ô khác, giả sử là E1, hãy nối phép toán logic và tham chiếu ô:
Theo cách tương tự, bạn có thể tính tổng các giá trị nhỏ hơn 200 bằng cách sử dụng phép toán nhỏ hơn (<):
=SUMIF(B2:B10, “<200”)
Công thức SUMIF với tiêu chí “bằng” hoạt động cho cả số và văn bản. Trong các tiêu chí như vậy, dấu bằng không thực sự bắt buộc.
=SUMIF(C2:C10, 3, B2:B10)
=SUMIF(C2:C10, “=3”, B2:B10)
Để tính tổng nếu bằng ô, chỉ cần cung cấp cho tham chiếu ô cho tiêu chí
=SUMIF(C2:C10, F1, B2:B10)
Trong đó B2: B10 là số lượng, C2: C10 là thời gian vận chuyển và F1 là thời gian giao hàng mong muốn.
=SUMIF(A2:A10, “apples”, B2:B10)
=SUMIF(A2:A10, “=apples”, B2:B10)
=SUMIF(A2:A10, F1, B2:B10)
Trong đó A2: A10 là danh sách các mục để so sánh với giá trị trong F1.
Lưu ý: Các công thức trên ngụ ý rằng tiêu chí phù hợp với toàn bộ nội dung ô. Do đó, hàm SUMIF sẽ cộng thêm doanh số bán hàng của Apple, nhưng không phải là Green Apple. Để tính tổng các kết quả phù hợp, hãy xây dựng tiêu chí “nếu ô chứa” như trong công thức phép toán đại diện SUMIF.
Khi một giá trị, văn bản hoặc số, được thay thế bằng tiêu chí, hãy nhớ đặt toàn bộ cấu trúc bằng dấu ngoặc kép.
Ví dụ, để tính tổng số tiền với lô hàng ngoài 3 ngày, công thức như sau:
Để tìm tổng số tất cả các mặt hàng ngoại trừ Táo, công thức là:
Khi giá trị tiêu chí nằm trong một ô khác, hãy nối phép toán “không bằng với” và một tham chiếu ô như sau:
Ví dụ 4: SUMIF không trống
Ví dụ: đây là cách bạn có thể tính tổng doanh số bán hàng cho tất cả các khu vực, tức là trong đó cột B không trống:
Để tính tổng các ô trong một số cột nếu ô tương ứng trong một cột khác trống, hãy sử dụng một trong các tiêu chí sau:
“” – tính tổng các ô nếu một ô khác trống; các ô chứa chuỗi trống cũng được coi là trống.
“=” – để tính tổng các ô hoàn toàn trống.
Trong bảng mẫu của chúng ta, công thức sau sẽ tính tổng doanh số cho các khu vực không xác định, tức là ô trong cột B trống:
=SUMIF(B2:B10, “”, C2:D10)
Ví dụ 6. Ký tự đại diện SUMIF trong Excel
Để tính tổng các ô dựa trên kết quả khớp từng phần, bao gồm một trong các ký tự đại diện trong tiêu chí của bạn:
Dấu chấm hỏi (?) Để thay thế bất kỳ ký tự đơn nào.
Dấu hoa thị (*) để thay thế bất kỳ số lượng ký tự nào.
=SUMIF(B2:B10, “north”, C2:D10)
Để tổng doanh số bán hàng cho tất cả các khu vực, bao gồm North, North-East và North-West, hay đặt dấy hoa thị ngay sau văn bản:
=SUMIF(B2:B10, “north*”, C2:D10)
Nếu bạn muốn nhập vùng quan tâm vào một ô xác định trước (F1), sau đó nối một tham chiếu ô và một ký tự đại diện được đặt trong dấu ngoặc kép:
=SUMIF(B2:B10, F1&”*”, C2:D10)
Để khớp dấu chấm hỏi hoặc dấu hoa thị, hãy đặt dấu ngã (~) trước ký tự, ví dụ “~?” hoặc “~ *”.
Ví dụ: để tổng hợp doanh số cho các khu vực được đánh dấu *, hãy sử dụng “* ~ *” cho tiêu chí. Trong trường hợp này, dấu hoa thị đầu tiên là ký tự đại diện và dấu hoa thị thứ hai là ký tự dấu hoa thị.
=SUMIF(B2:B10, “*~*”, C2:D10)
Nếu tiêu chí (* trong trường hợp này) được cho là được nhập vào một ô riêng biệt, thì hãy nối một dấu ngã và tham chiếu ô, như sau:
=SUMIF(B2:B10, “*”&”~”&F1, C2:D10)
Ví dụ 7: SUMIF trong Excel với ngày tháng
Việc sử dụng ngày tháng làm tiêu chí SUMIF rất giống việc sử dụng số. Điều quan trọng nhất là thay thế định dạng ngày mà Excel có thể hiểu được. Nếu bạn không chắc định dạng ngày nào được hỗ trợ và định dạng ngày nào không, hàm DATE có thể là một giải pháp.
Giả sử bạn đang tìm cách tính tổng doanh số ngày 10/9/2020, tiêu chí có thể được thẻ hiện như sau:
=SUMIF(C2:C10, “<9/10/2020”, B2:B10)
=SUMIF(C2:C10, “<“&DATE(2020,9,10), B2:B10)
=SUMIF(C2:C10, “<“&F1, B2:B10)
Trong đó F1 là ngày mục tiêu.
=SUMIF(C2:C10, “<“&TODAY(), B2:B10)
Để tính tổng trong một chuỗi ngày, bạn cần xác định riêng biệt ngày nhỏ hơn và lớn hơn. Điều này có thế được thực hiện với sự trợ giúp của hàm SUMIFS hỗ trợ nhiều tiêu chí.
Ví dụ: để tính tổng nếu một ngày nằm giữa hai ngày, thì đây là công thức để sử dụng:
Trong đó B2:B10 là tổng phạm vi, C2:C10 là danh sách ngày cần kiểm tra, F1 là ngày bắt đầu và G1 là ngày kết thúc.
Cách thực hiện SUMIF từ một sheet khác
Khi bạn cần tính tổng dữ liệu có điều kiện từ một trang tính khác, chỉ cần cũng cấp tham chiếu bên ngoài cho các đối số SUMIF. Cách dễ nhất là bắt đầu nhập công thức, chuyển sang sheet khác và chọn vùng bằng chuột. Excel sẽ tự động chèn tất cả các tham chiếu mà bạn không cần phải lo lắng về cú pháp đúng.
=SUMIF(Data!B2:B10, B3, Data!C2:C10)
Công thức SUMIF phân biệt chữ hoa chữ thường trong Excel
Như đã đề cập, về bản chất, hàm SUMIF trong Excel không phân biệt chữ hoa chữ thường. Để tính tổng các ô tính tổng các ô có điều kiện cần cân nhắc các từ viết hoa, bạn sẽ phải sử dụng một số hàm khác, cụ thể là hàm SUMPRODUCT và EXACT xử lý chữ hoa và chữ thường là ký tự khác nhau:
SUMPRODUCT(-(EXACT(range, criteria)), sum_range)
Giả sử bạn có một danh sách các mã mặt hàng trong cột A, nơi mà chữ hoa và chữ thường được xác định khác nhau. Mục tiêu của bạn là tính tổng các số trong cột B cho một mục cụ thể, chẳng hạn A-01.
Để hoàn tất, bạn có thể nhập mục đích trực tiếp vào đối số thứ 2 của hàm EXACT:
=SUMPRODUCT(-(EXACT(A2:A10, “A-01”)), B2:B10)
Hoặc nhập mã mặt hàng vào một ô riêng (E1) như trong hình:
=SUMPRODUCT(-(EXACT(A2:A10, E1)), B2:B10)
Hàm này hoạt động như thế nào?
Hàm EXACT so sánh mặt hàng mục tiêu với từng mặt hàng trong danh sách nguồn và trả về giá trị TRUE nếu tìm kết quả không khớp, nếu không thì là FALSE:
{TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE}
Toán tử đơn ngôi kép (-) chuyển đổi TRUE và FALSE thành 1 và 0, tương ứng:
Hàm SUMPRODUCT nhân các phần tử của mảng trên với các mục tương ứng trong B2: B10:
SUMPRODUCT({1;0;0;0;0;0;1;0;0}, {250;155;130;255;160;280;170;285;110})
Bởi vì nhân với 0 bằng 0 chỉ những mục mà EXACT trả về TRUE (1) mới tồn tại:
SUMPRODUCT({250;0;0;0;0;0;170;0;0})
Cuối cùng, SUMPRODUCT cộng các sản phẩm và xuất ra tổng.
Hàm Sumif Trong Kiểm Toán – Công Cụ Không Thể Bỏ Qua
1. Công Dụng Của Hàm SUMIF
Kết chuyển các bút toán cuối kỳ
Tổng hợp số liệu từ Bảng nhập liệu (BNL) lên Bảng cân đối phát sinh tháng/năm, lên bảng nhập xuất tồn kho, lên Bảng tổng hợp phải thu, phải trả cho khách hàng …
2. Cách Dùng Hàm Sumif
Cú pháp hàm Sumif
=SUMIF (range, criteria, [sum_range])
=SUMIF (Dãy ô điều kiện, điều kiện cần tính, dãy ô tính tổng)
Giải thích
Dãy ô điều kiện: Là dãy ô chưa điều kiện cần tính. Ví dụ: Là dãy ô chứa tài khoản trong cột TK Nợ/TK Có trên BNL, hoặc dãy ô chứa mã hàng trên Phiếu nhập kho, Xuất kho…
Điều kiện cần tính: Phải có “Tên“ trong dãy ô điều kiện. Cụ thể trong bài: Là Tài khoản cần tính trên BNL hoặc mã hàng trên kho ( bảng Nhập Xuất Tồn ) hoặc TK cần tổng hợp trên bảng Cân đối phát sinh…. ( Điều kiện cần tính chỉ là một ô )
Tips cho bạn
Khi muốn tuyệt đối dòng hoặc cột ( việc tuyệt đối dòng hoặc cột là tuỳ vào từng trường hợp):
Bấm F4 (1 lần): Để có giá trị tuyệt đối ( tuyệt đối được hiểu là cố định cột và cố định dòng – ($cột$dòng); Ví dụ: $E$12 – tức là cố định cột E và cố định dòng 12
Bấm F4 (2 lần): Để có giá trị tương đối cột và tuyệt đối dòng – được hiểu là cố định dòng thay đổi cột – (cột$dòng) ; Ví dụ: E$12 – tức là cố định Dòng 12, không cố định cột E
Bấm F4 (3 lần): Để có giá trị tương đối dòng và tuyệt đối cột – được hiểu là cố định cột thay đổi dòng – ($cột dòng) ; Ví dụ: $E12 – tức là cố định Cột E, không cố định dòng 12.
3. Các Lỗi Thường Gặp Khi Dùng Hàm SUMIF
Lỗi 1: Công thức tham chiếu đến ô trong một workbook đã đóng
Nguyên nhân: Hàm SUMIF/SUMIFS tham chiếu đến một ô hoặc phạm vi trong một workbook đã đóng sẽ dẫn đến lỗi #VALUE!.
Giải pháp: Mở cửa sổ làm việc được chỉ ra trong công thức và nhấn F9 để làm mới các công thức.
Lỗi 2: Các chuỗi tiêu thức nhiều hơn 255 ký tự
Nguyên nhân: Hàm SUMIF/SUMIFS trả về kết quả sai khi bạn tìm cách để khớp các chuỗi dài hơn 255 ký tự.
Giải pháp: Rút ngắn chuỗi nếu có thể. Nếu bạn không thể rút ngắn này, sử dụng hàm CONCATENATE hoặc dấu và (&) toán tử ngắt xuống giá trị vào nhiều chuỗi.
Ví dụ: = SUMIF(B2:B12,”long string”&”another long string”)
Lỗi 3: Trong SUMIFS, đối số criteria_range là không nhất quán với đối số sum_range
Nguyên nhân: Các đối số phạm vi phải luôn giống nhau trong SUMIFS. Có nghĩa là gì các đối số criteria_range và sum_range nên tham chiếu đến cùng số hàng và cột.
Ví dụ: công thức được phép để trả về tổng doanh số bán hàng ngày quả táo trong Bellevue. Đối số sum_range (C2:C10) Tuy nhiên không tương ứng với cùng số hàng và cột trong các đối số criteria_range (A2:A12 & B2:B12) . Cú pháp = SUMIFS (C2:C10,A2:A12, A14,B2:B12, B14) sẽ dẫn đến #VALUE! lỗi.
Giải pháp: Theo dõi ví dụ này, thay đổi sum_range thành C2:C12 và thử lại công thức.
Cách Sử Dụng Hàm Or Và Ví Dụ Cụ Thể
Hàm OR là một hàm logic. Hàm trả về kết quả TRUE nếu giá trị thỏa mãn một hoặc một vài, tất cả trong số các điều kiện được đưa ra.
Nếu không có điều kiện nào đúng, kết quả sẽ là FALSE.
ĐỊNH NGHĨA HÀM OR
Trả về kết quả TRUE nếu giá trị thỏa mãn một trong số các điều kiện được đưa ra.
CÚ PHÁP
=OR (logical1, [logical2], …)
Trong đó:
Logical1, logical2, …: Là các giá trị logic (có thể lên tới 255 điều kiện khác nhau)
CÁCH SỬ DỤNG
Cùng thực hành qua một số ví dụ sau để biết cách sử dụng hàm:
Ví dụ cụ thể:
Giải thích: Hàm OR trả kết quả TRUE nếu điểm môn 1 hơn 70 và điểm môn 2 là 100. Còn lại trả kết quả FALSE.
HÀM OR KẾT HỢP VỚI IF
Giải thích: Nếu OR trả kết quả là TRUE, IF hiển thị “Đỗ”. Kết quả FALSE hiển thị “Trượt”.
SỬ DỤNG OR NHIỀU ĐIỀU KIỆN
Giải thích: OR trả kết quả TRUE nếu có ít nhất 1 môn đạt 90 điểm, nếu không trả kết quả FALSE. Hàm OR có thể kiểm tra tối đa 255 điều kiện. Trả kết quả FALSE nếu tất cả mọi điều kiện đều sai (hàng 6).
AND KẾT HỢP VỚI OR
Giải thích: AND trả kết quả TRUE nếu tất cả các điều kiện được thỏa mãn, trả FALSE nếu 1 trong các điều kiện không đạt. AND có 2 đối số ngăn cách bởi dấu chấm phẩy (Bàn, Xanh lá cây hoặc Xanh da trời). AND trả TRUE nếu Sản phẩm là “Bàn” và Màu là “Xanh lá cây” hoặc “Xanh da trời”.
WEEKDAY KẾT HỢP VỚI OR
Giải thích: WEEKDAY trả kết quả là số từ 1 đến 7 tương ứng với các ngày trong tuần. Có nhiều lựa chọn ngày tương ứng số 1, được biểu thị bằng đối số thứ 2 trong WEEKDAY. Đối số này là 1, tương đương số 1 bằng chủ nhật và số 7 bằng thứ 7. OR trả kết quả TRUE nếu ngày ở cột A rơi vào cuối tuần.
Cập nhật thông tin chi tiết về Cách Dùng Hàm Sumif Và Sumifs Qua Ví Dụ Cụ Thể 2022 trên website Hoisinhvienqnam.edu.vn. Hy vọng nội dung bài viết sẽ đáp ứng được nhu cầu của bạn, chúng tôi sẽ thường xuyên cập nhật mới nội dung để bạn nhận được thông tin nhanh chóng và chính xác nhất. Chúc bạn một ngày tốt lành!