Cập nhật thông tin chi tiết về Hàm Sumif Trong Kiểm Toán – Công Cụ Không Thể Bỏ Qua mới nhất trên website Trucbachconcert.com. 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.
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.
Hàm Sumif Trong Kiểm Toán
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.
[FREE DOWNLOAD] TUYỂN TẬP KINH NGHIỆM 10 PHẦN HÀNH KIỂM TOÁN CƠ BẢN
Hướng Dẫn Cách Sử Dụng Công Thức Sumif Trong Excel Qua Những Ví Dụ Cụ Thể
Cú pháp hàm SUMIF và cách sử dụng trong Excel
Cú pháp của hàm như sau:
SUMIF(range; criteria; [sum_range])
Như bạn thấy, hàm SUMIF có 3 đối số – 2 đối số đầu tiên là bắt buộc và đối số thứ 3 là tùy chọn.
range – đây là vùng chứa điều kiện. Vùng này thường nằm ở bảng dữ liệu, ví dụ Sheet1!A1:A10 (vùng A1:A10 trong Sheet1 – là Sheet chứa bảng dữ liệu)
criteria – điều kiện làm tiêu chí tính tổng. Điều kiện thường nằm ở bảng báo cáo, ví dụ Sheet2!A5 (vùng A5 trong Sheet2 – là Sheet bạn muốn đặt kết quả tính toán, hay là bảng báo cáo)
sum_range – vùng tính tổng. Vùng này thường nằm ở bảng dữ liệu và có độ lớn tương ứng với vùng chứa điều kiện, ví dụ Sheet1!D1:D10 (vùng D1:D10 trong Sheet1 – là Sheet chứa bảng dữ liệu). Hàm sẽ tính tổng những dòng chứa giá trị thỏa mãn tương ứng với vùng chứa điều kiện. Tham số này không bắt buộc, nếu bạn bỏ qua tham số này, Excel sẽ tính tổng trên vùng chứa điều kiện (chỉ áp dụng khi vùng chứa điều kiện bao gồm toàn các giá trị là số).
Để hiểu rõ hơn hàm SUMIF của Excel, hãy xem xét ví dụ đơn giản sau:
Bây giờ, hãy xác định các đối số cho công thức SUMIF:
Lúc này công thức trên sẽ là:
Trong ví dụ trên chúng ta nhập trực tiếp chữ Táo vào trong hàm tại vị trí điều kiện. Với cách nhập trực tiếp như vậy thì chữ Táo phải được đặt trong cặp dấu nháy kép là “Táo”, bởi vì đây là ký tự dạng văn bản.
Có 1 cách viết khác là bạn có thể chọn tham chiếu tới 1 ô chứa nội dung là chữ Táo, ví dụ như đó là ô E1 thì có thể viết như sau:
Một số ví dụ về các trường hợp sử dụng hàm SUMIF
Hàm SUMIF với dấu lớn hơn, nhỏ hơn hoặc bằng
Bây giờ hãy xem xét một vài công thức hàm SUMIF mà bạn có thể sử dụng để thêm các giá trị lớn hơn, nhỏ hơn hoặc bằng một giá trị nhất định.
Ghi chú. Trong các công thức SUMIF của Excel, một toán tử so sánh được theo sau bởi một số hoặc văn bản phải luôn được đặt trong dấu ngoặc kép (“”).
Hướng dẫn cách sử dụng công thức SUMIF trong EXCEL qua những ví dụ cụ thể
Cách sử dụng SUMIF với văn bản
Ngoài các số, hàm SUMIF cho phép thêm các giá trị tùy thuộc vào việc một ô tương ứng trong cột khác có chứa văn bản đã cho hay không.
Tính tổng nếu bằng
Tham chiếu một phần: =SUMIF(A2:A8; ” Táo “;C2:C8)
Tổng giá trị trong các ô C2:C8 nếu một ô tương ứng trong cột A chứa từ “Táo”, một mình hoặc kết hợp với bất kỳ từ nào khác. Các từ khóa như “Táo xanh”, “Táo đỏ” hoặc “Táo!” đều được tính tổng
Tổng nếu không bằng
Tổng giá trị trong các ô C2:C8 nếu một ô tương ứng trong cột A không chứa từ “Táo”, một mình hoặc kết hợp với bất kỳ từ nào khác. Các từ khóa như “Táo xanh” hoặc “Táo đỏ” sẽ không được tính
Nếu muốn sử dụng công thức hàm SUMIF một cách tổng quát hơn, bạn có thể sử dụng cách viết tham chiếu tới một ô như sau:
Chú ý:
Khi viết dạng tham chiếu kết hợp với dấu toán tử, bạn cần đặt dấu toán tử vào trong cặp dấu nháy kép (“”) và thêm dấu & để nối với tọa độ tham chiếu.
Công thức hàm SUMIF với các ký tự đại diện
Nếu nhắm đến các ô có điều kiện dựa trên tiêu chí “văn bản” và bạn muốn tính tổng theo từng phần thì cần sử dụng các ký tự đại diện trong công thức hàm SUMIF của mình.
Các ký tự đại diện sau đây đều được hỗ trợ trong Excel:
Dấu hoa thị (*) – Đại diện cho bất kỳ ký tự nào, không giới hạn số lượng ký tự
Dấu hỏi (?) – Đại diện cho một ký tự ở một vị trí cụ thể
=SUMIF(A2:A8;”táo“;C2:C8) – tiêu chí bao gồm văn bản được đính kèm trong dấu hoa thị ().
=SUMIF(A2:A8;”“&E1&”*”;C2:C8) – Tiêu chí bao gồm một tham chiếu ô được đặt trong dấu hoa thị.
Nếu muốn chỉ đếm những ô bắt đầu hoặc kết thúc bằng một văn bản nhất định, hãy thêm chỉ một dấu * trước hoặc sau văn bản:
=SUMIF(A2:A8;”táo“;C2:C8) – Tổng giá trị từ các ô C2:C8 nếu một ô tương ứng trong cột A bắt đầu bằng từ “táo”.
=SUMIF(A2:A8;”táo”;C2:C8) – Tổng giá trị từ các ô C2:C8 nếu một ô tương ứng trong cột A kết thúc bằng từ “táo”.
=SUMIF(A2:A8;”“&E1&”“;C2:C8) – Tính tổng giá trị từ các ô C2:C8 nếu có từ “táo”.
Ví dụ 2. Tính tổng các ô tương ứng với các giá trị văn bản
Nếu bảng tính của bạn chứa các loại dữ liệu khác nhau và bạn chỉ muốn tính tổng các ô tương ứng với các giá trị văn bản, các công thức SUMIF sau đều trả về cùng một kết quả:
=SUMIF(A2:A8;”?“;C2:C8) – Cộng các giá trị trong các ô C2:C8 nếu một ô tương ứng trong cột A chứa ít nhất 1 ký hiệu.
=SUMIF(A2:A8;”“;C2:C8) – Cộng các giá trị bao gồm cả các ô như ô trống có chứa các chuỗi có độ dài bằng không được trả về bởi một số công thức khác, ví dụ =”” .
Cả hai công thức trên đều bỏ qua các giá trị không phải là văn bản như các lỗi, các phép toán luận, các chữ số và ngày tháng.
Ví dụ 3. Sử dụng các ký hiệu * hay? như bình thường
Nếu bạn muốn sử dụng các ký hiệu * hoặc ? như là một chữ mà không phải là ký tự đại diện thì nên sử dụng dấu ngã trước (~). Ví dụ: công thức SUMIF sau đây sẽ thêm các giá trị trong các ô C2:C8 nếu một ô trong cột A trong cùng một hàng chứa một dấu hỏi:
Cách tính tổng các ô tương ứng với các ô trống
Nếu “blank” có nghĩa là các ô hoàn toàn không chứa gì – không có công thức, không có chuỗi có độ dài bằng 0 được trả về bởi một số hàm Excel khác, thì hãy sử dụng “=” làm tiêu chí, như trong công thức SUMIF sau:
Nếu “blank” bao gồm các chuỗi có độ dài bằng không (ví dụ: các ô có công thức như =”” ), thì sử dụng “” làm tiêu chí:
Cả hai công thức trên đều đánh giá các ô trong cột A và nếu tìm thấy bất kỳ ô trống nào, các giá trị tương ứng từ cột C sẽ được cộng vào.
Cách tính tổng các ô tương ứng với các ô không trống
Công thức trên tính các giá trị tương ứng với tất cả các ô không trống, bao gồm các chuỗi có độ dài bằng không.
Cách sử dụng hàm SUMIF với ngày, tháng, năm
Sử dụng hàm SUMIF để tính tổng các giá trị theo điều kiện dựa trên ngày giống như cách bạn sử dụng tiêu chí văn bản và số.
Nếu muốn tính tổng các giá trị tương ứng với các ngày lớn hơn, nhỏ hơn hoặc bằng với ngày bạn chỉ định, thì hãy sử dụng các toán tử so sánh. Một vài ví dụ về công thức hàm SUMIF của Excel dựa trên ngày, tháng như sau:
Tổng kết
Qua bài viết vừa rồi, Gitiho đã hướng dẫn các bạn cách làm việc với hàm SUMIF cùng với những ví dụ cụ thể về cách ứng dụng hàm này trong công việc thực tế.
Hi vọng các bạn sẽ thực hành và áp dụng hàm SUMIF hiệu quả cho công việc của mình.
Hướng dẫn cách dùng hàm index trong Excel chi tiết nhất Hướng dẫn cách copy chỉ những giá trị hiển thị sau khi lọc trong Excel Hướng dẫn cách sắp xếp ngày trong Excel Hướng dẫn cách dùng hàm VLOOKUP qua các ví dụ từ cơ bản đến nâng cao
Hướng dẫn cách sử dụng hàm IF với nhiều điều kiện: AND, OR, hàm IF lồng nhau và hơn th ế
Với sứ mệnh: ” Mang cơ hội phát triển kỹ năng, phát triển nghề nghiệp tới hàng triệu người “, đội ngũ phát triển đã và đang làm việc với những học viện, trung tâm đào tạo, các chuyên gia đầu ngành để nghiên cứu và xây dựng lên các chương trình đào tạo từ cơ bản đến chuyên sâu xung quanh các lĩnh vực: Tin học văn phòng, Phân tích dữ liệu, Thiết kế, Công nghệ thông tin, Kinh doanh, Marketing, Quản lý dự án…
Gitiho tự hào khi được đồng hành cùng:
50+ khách hàng doanh nghiệp lớn trong nhiều lĩnh vực như: Vietinbank, Vietcombank, BIDV, VP Bank, TH True Milk, VNPT, FPT Software, Samsung SDIV, Ajinomoto Việt Nam, Messer,…
Hàm Sumif Trong Excel: Cách Sử Dụng Và Ví Dụ Cụ Thể
Hàm SUMIF là gì?
Bạn hẳn sẽ thắc mắc, hàm SUMIF khác gì hàm SUM? Hàm SUMIF mở rộng khả năng của hàm SUM, thay vì tính tổng trong một phạm vi nhất định, để được tính tổng trong SUMIF các ô buộc phải thỏa mãn điều kiện mà người dùng truyền vào tham số criteria. Hàm SUMIF tiết kiệm rất nhiều công sức nếu bạn muốn tính tổng doanh thu của một đơn vị, doanh số của một nhóm nhân viên, hoặc doanh thu trong khoảng thời gian nhất định, tổng lương theo điều kiện nào đó…
Mục đích của hàm SUMIF là để tính tổng các số trong phạm vi đáp ứng được các tiêu chí đã cung cấp. Kết quả trả về là tổng các giá trị đã cung cấp.
Công thức hàm SUMIF
=SUMIF (range, criteria, [sum_range])
Tham số của hàm SUMIF:
Range: Phạm vi các ô bạn muốn đánh giá theo tiêu chí Criteria. Các ô trong mỗi phạm vi phải là số hoặc tên, mảng hay tham chiếu chứa số. Giá trị trống và giá trị văn bản bị bỏ qua. Phạm vi được chọn có thể chứa các ngày ở định dạng Excel tiêu chuẩn.
Criteria: Tiêu chí xác định các giá trị được tính tổng. Nó có thể là số, biểu thức hoặc một chuỗi văn bản.
Sum_range: Tham số này là tùy chọn, nó sẽ chỉ ra các ô cần tính tổng. Nếu để trống sum_range, những ô trong phạm vi đánh giá sẽ được thay thế.
Ví dụ và cách sử dụng SUMIF trong Excel
Trong Excel bảng được trình bày như sau:
Ví dụ 1: Sử dụng hàm SUMIF để tính tổng thuế thu nhập cá nhân của những người có thu nhập dưới 50 triệu đồng.
Để giải quyết ví dụ này, trước tiên ta cần xác định được 3 tham số của hàm SUMIF, cụ thể:
Range: Phạm vi cột chứa tổng thu nhập, ở đây là B4:B9
Criteria là <50.000.000, vì có chứa kí hiệu toán học, nên cần đặt trong “‘: “<50.000.000”
Sum_range là phạm vi cột chứa thuế thu nhập cá nhân cần tính tổng, ở đây là H4:H9
Khi đó, ta sẽ có công thức: =SUMIF(B4:B9,”<50.000.000″,H4:H9), bạn nhập công thức này vào ô chứa kết quả, trong ví dụ của chúng tôi là ô H19.
Nhấn Enter và sẽ nhận được số tiền thuế thu nhập cá nhân cần đóng cho những người có thu nhập dưới 50.000.0000 là 2.229.775.
Ví dụ 2: Vẫn với bảng tính trên, hãy tính tổng thuế thu nhập cá nhân của người có 2 người phụ thuộc. Lúc này:
Range: Phạm vi cột chứa số người phụ thuộc, ở đây là D4:D9
Criteria là =2, có thể viết là 2 hoặc “=2”
Sum_range là phạm vi cột chứa thuế thu nhập cá nhân cần tính tổng, ở đây là H4:H9.
Như vậy, công thức sẽ là: =SUMIF(D4:D9,2,H4:H9), bạn nhập công thức vào ô chứa kết quả, trong ví dụ là H21:
Nhấn Enter để nhận được tổng thuế trả về là 32.267.050 đồng.
Ví dụ 3: Vẫn trong bảng trên, tính tổng thu nhập của những người có thu nhập trên 80 triệu đồng. Lúc này:
Range: Phạm vi cột chứa tổng thu nhập, ở đây là B4:B9
Sum_range trong trường hợp này chính là Range nên bạn không cần nhập tham số này nữa.
Nhấn Enter để nhận kết quả tổng thu nhập của những người có thu nhập trên 80 triệu là 1.100.000.000:
Range: Phạm vi cột chứa danh sách phòng ở đây là J4:J9
Criteria là Bán hàng, viết là “Bán hàng”
Sum_range là phạm vi cột chứa thu nhập cần tính tổng, ở đây là B4:B9
Như vậy công thức sẽ là: =SUMIF(J4:J9,”Bán hàng”,B4:B9), bạn nhập công thức vào ô chứa kết quả, như hình dưới là ô H23:
Nhấn Enter và nhận về tổng thu nhập của phòng bán hàng là 60.000.000:
Chú ý khi sử dụng hàm SUMIF
Khi sum_range bị bỏ qua, tổng sẽ được tính theo Range.
Criteria có chứa chữ hay ký hiệu toán học đều phải được đặt trong dấu ngoặc kép “”.
Phạm vi xác định dạng số có thể được cung cấp là số sẽ không phải dùng đến dấu ngoặc.
Các ký tự ? và * đều có thể được sử dụng trong Criteria. Một dấu chấm hỏi khớp với bất kỳ ký tự đơn nào; một dấu sao phù hợ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 hay dấu sao thực sự, hãy gõ dấu ngã (~) trước ký tự.
Bạn đang xem bài viết Hàm Sumif Trong Kiểm Toán – Công Cụ Không Thể Bỏ Qua trên website Trucbachconcert.com. 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. Chúc bạn một ngày tốt lành!