Cập nhật nội dung chi tiết về Hàm Sumif Trong Kiểm Toán mới nhất trên website Beiqthatgioi.com. Hy vọng thông tin trong bài viết sẽ đáp ứng được nhu cầu ngoài mong đợi của bạn, chúng tôi sẽ làm việc thường xuyên để cập nhật nội dung mới nhằm giúp 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.
[FREE DOWNLOAD] TUYỂN TẬP KINH NGHIỆM 10 PHẦN HÀNH KIỂM TOÁN CƠ BẢN
6 Hàm Thường Dùng Trong Nghiệp Vụ Kế Toán Kiểm Toán
1. HÀM TÍNH TỔNG (SUMPRODUCT, SUMIF, SUMIFS)
a. Hàm SUMPRODUCT
Cú pháp: = SUMPRODUCT(mảng 1,mảng 2, …,mảng n)
Công dụng: Nhân các thành phần tương ứng trong các mảng đã cho và trả về tổng của các tích số này.
Lưu ý:
Các đối số mảng phải có cùng kích thước. Nếu không, hàm SUMPRODUCT trả về giá trị lỗi #VALUE!
Hàm SUMPRODUCT coi các mục của mảng không có dạng số là số không.
Ví dụ: Tổng VAT = 1,000,000 *5% + 2,000,000*10% + 3,000,000 *15%
Lưu ý:
Nếu vùng điều kiện trùng với vùng tính tổng, chúng ta chỉ không cần phải nhập vùng tính tổng vào công thức. Khi đó, cú pháp của hàm SUMIF sẽ trở thành như sau: Cú pháp: =SUMIF(Vùng điều kiện,Điều kiện). Ví dụ: tính tổng doanh số khách hàng doanh thu trên 1 tỷ.
c. Hàm SUMIFS
Cú pháp: =SUMIFS(Vùng tính tổng, vùng điều kiện 1, điều kiện 1, vùng điều kiện n, điều kiện n)
Công dụng: Tính tổng theo 1 hoặc nhiều điều kiện
Lưu ý: Sumifs có nhiều ưu điểm hơn Sumif: trả về vùng dữ liệu sum khi làm truy soát, ( Ctrl+[), thao tác trên cùng một worksheet
2. HÀM DÒ TÌM (VLOOKUP, HLOOKUP)
Cú pháp: = VLOOKUP( Giá trị dò tìm,Bảng dò tìm,Số thứ tự cột dò tìm,Kiểu dò tìm)
Công dụng: Tìm kiếm giá trị từ bảng dò tìm theo chiều dọc.
Kiểu dò tìm = 0 hoặc FALSE: dò tìm chính xác, nếu không tìm thấy sẽ trả về lỗi #N/A.
Kiểu dò tìm = 1 hoặc TRUE: dò tìm gần đúng, khi đó hàm sẽ lấy giá trị lớn nhất gần bằng giá trị dò tìm nhưng nhỏ hơn giá trị dò tìm. Dữ liệu ở cột dò tìm phải được sắp xếp theo thứ tự tăng dần.
Ngược lại với hàm VLOOKUP là hàm HLOOKUP (Horizontal lookup), tức là dò tìm theo chiều ngang.
Ví dụ: hlookup = 100 & vlookup = 100
a. Hàm MID()
– Cấu trúc của hàm Mid() trong Excel MID( Chuỗi ,Vị trí bắt đầu, [Số ký tự])
– Công dụng: Hàm Mid() dùng để lấy ra n ký tự của chuỗi (Text) từ ngay vị trí bắt đầu được chỉ định Giải thích:
Chuỗi: Là chuỗi văn bản có chứa các ký tự cần lấy ra.
Vị trí bắt đầu: Vị trí bắt đầu để lấy n ký tự từ trong chuỗi đã cho
Số ký tự: Là số ký tự muốn lấy ra từ ngay vị trí bắt đầu của Chuỗi đã cho.
– Ví dụ
Mid(“Excel thuc hanh kiem toan”,7,9) = thuc hanh
Hàm Mid() sẽ lấy ra 9 ký tự từ vị trí bắt đầu là số 7 (chữ t) của chuỗi “Excel thuc hanh kiem toan”. b. Hàm LEN() – Cấu trúc: =LEN(Chuỗi)
– Chức năng: Hàm Len() dùng để đếm chiều dài (số ký tự) của chuỗi (Text)
Giải thích:
Là chuỗi văn bản có chứa các ký tự cần đếm tổng chiều dài bao nhiêu ký tự.
– Ví dụ
Len(“ZaloPay”) = 7
Hàm Len() sẽ đếm tổng số ký tự của chuỗi “ZaloPay”.
c. Hàm LEFT() – Cấu trúc: =LEFT (chuỗi, số ký tự) – Chức năng: để lấy ra ký tự bên trái chuỗi – Ví dụ: =Left(“ZALOPAY”,4)=ZALO d. Hàm MID
– Cấu trúc: =MID(chuỗi,số ký tự bắt đầu, tổng số ký tự muốn lấy)
– Chức năng: lấy các ký tự nằm ” giữa ” chuỗi
– Ví dụ:
=MID(“2018/05/19”,6,2) sẽ trả kết quả là 05
e. Hàm TRIM
– Cấu trúc: =TRIM(chuỗi ký tự)
– Chức năng: Loại bỏ các khoảng trắng thừa trong chuỗi.
– Ví dụ:
=TRIM(” Zalo Pay “) sẽ cho kết quả là ZaloPay.
5. HÀM ĐIỀU KIỆN (IF, IFERROR)
a. Hàm IF
Cú Pháp: IF (điều kiện, giá trị 1, giá trị 2)
Ý Nghĩa: Nếu như ” điều kiện” đúng thì kết quả hàm trả về là ” giá trị 1“, ngược lại trả về ” giá trị 2 “.
b. Hàm IFERROR
Cú pháp: = IFERROR( giá trị, giá trị nếu lỗi)
Công dụng: Nếu biểu thức giá trị không tạo ra một lỗi, IFERROR() trả về kết quả của biểu thức; còn nếu không, nó sẽ trả về giá trị nếu lỗi (là chuỗi rỗng hoặc một thông báo lỗi).
c. Kết hợp IFERROR và VLOOKUP =IFERROR(VLOOKUP(giá trị tìm, vùng tìm 1, số cột tìm 1,0),VLOOKUP(giá trị tìm, vùng tìm 2, số cột tìm 2,0)
6. HÀM ĐẾM DỮ LIỆU (COUNT, COUNTA, COUNTIF)
a. Hàm COUNT – Cú pháp: COUNT(Value1, Value2, …)
– Chức năng: Hàm này dùng để đếm các ô chứa dữ liệu kiểu số trong dãy
Với các tham số: Value1, Value2… là mảng hay dãy dữ liệu.
b. Hàm COUNTA – Cú pháp: COUNTA(Value1, Value2, …)
– Chức năng: Đếm tất cả các ô chứa dữ liệu.
c. Hàm COUNTIF Cú pháp: COUNTIF(Range, Criteria)
Chức năng: Hàm này có chức năng đếm các ô chứa dữ liệu kiểu số theo một điều kiện cho trước.
Các tham số: – Range: Dãy dữ liệu mà bạn muốn đếm. – Criteria: Là tiêu chuẩn cho các ô được đếm.
d. Hàm COUNTIFS – Cú pháp: COUNTIFS(range1, criteria1, range2, criteria2, …)
– Chức năng: Hàm này dùng để đếm tổng số ô thỏa điều kiện yêu cầu
Tổng Hợp Các Hàm Excel Cho Kiểm Toán Và Kế Toán
1. Hàm VLOOKUP
VLOOKUP là hàm phổ biến nhất trong Kế toán, Kiểm toán. VLOOKUP chính là Vertical Lookup, tìm kiếm theo chiều dọc.
Công dụng của hàm VLOOKUP
Tìm đơn giá Xuất kho từ bên Bảng kê Nhập Xuất Tồn về Phiếu Xuất kho.
Tìm số dư của đầu tháng N căn cứ vào cột số dư của tháng N-1.
Tìm số “Khấu hao (Phân bổ) luỹ kế từ kỳ trước” của Bảng khấu hao (bảng phân phối chi phí trả trước ) căn cứ vào Giá trị khấu hao (phân bổ) luỹ kế của tháng N-1.
Tìm kiếm số dư của chi tiết từng khách hàng phải thu, phải trả cuối năm dựa trên báo cáo tuổi nợ và Sổ chi tiết công nợ theo từng đối tượng.
Câu lệnh
=Vlookup (lookup_value, table_array, col_index_num, [range_lookup])
Giải thích
lookup_value: Giá trị để tìm kiếm chỉ là một Ô và phải có Tên trong Vùng dữ liệu tìm kiếm (là Ô mã hàng hoá, Mã tài khoản, Mã tài sản, Mã Công Cụ dụng cụ….).
table_array: “Vùng dữ liệu tìm kiếm” phải chứa tên của “Giá trị để tìm kiếm” và phải chứa “Giá trị cần tìm”. Để bắt đầu của vùng được tính từ dãy số có chứa “Giá trị để tìm kiếm”. (Cụ thể: là bảng dữ liệu của tháng trước hoặc dữ liệu của Sheet khác).
col_index_num: Là số thứ tự cột, tính từ bên trái sang vủa vùng dữ liệu tìm kiếm (Bạn đếm từ bên trái của vùng sang đến cột cần lấy dữ liệu xem là cột thứ mấy).
range_lookup: thường để giá trị là 0, nghĩa là không thực hiện sắp xếp theo thứ tự nào.
2. Hàm IF
Công dụng
Hàm IF là hàm được sử dụng nhiều nhất trong Kế toán, Kiểm toán. Hầu như trong trường hợp nào bạn cũng có thể sử dụng hàm IF. Thông thường, hàm được sử dụng để: kiểm tra sự chính xác của HTK, so sánh các giá trị, tính lương,v.v
Câu lệnh
= IF(Logical_test, value_if_true, value_if_false)
Giải thích
Logical_test: Biểu thức điều kiện
Value_if_true: Giá trị trả về nếu thỏa mãn điều kiện
Value_if_false: Giá trị trả về nếu không thỏa mãn điều kiện
3. Hàm SUMIF
Công dụng
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 v.v
Câu lệnh
=SUMIF (range, criteria, [sum_range])
Giải thích
range: 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…
criteria: 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 ô).
4. Hàm SUBTOTAL
Công dụng
SUBTOTAL là hàm tính toán cho một nhóm con trong một danh sách hoặc bảng dữ liệu tuỳ theo phép tính mà bạn chọn lựa trong đối số thứ nhất. Hàm SUBTOTAL thường dùng để: Tính tổng phát sinh trong kỳ, tính tổng tiền tồn cuối ngày, tính tổng cho từng tài khoản kế toán.
Câu lệnh
=SUBTOTAL(function_num,ref1,ref2,…)
Giải thích
Function_num là các con số từ 1 đến 11 (hay có thêm 101 đến 111 trong phiên bản Excel 2003, 2007) qui định hàm nào sẽ được dùng để tính toán trong SUBTOTAL.
Ref1, ref2 là các vùng địa chỉ tham chiếu mà bạn muốn thực hiện phép tính trên đó.
Ghi chú
Nếu có hàm subtotal khác lồng đặt tại các đối số ref1, ref2,… thì các hàm lồng này sẽ bị bỏ qua không được tính nhằm tránh trường hợp tính toán 2 lần.
Đối số function_num nếu từ 1 đến 11 thì hàm SUBTOTAL tính toán bao gồm cả các giá trị ẩn trong tập số liệu (hàng ẩn). Đối số function_num nếu từ 101 đến 111 thì hàm SUBTOTAL chỉ tính toán cho các giá trị không ẩn trong tập số liệu (bỏ qua các giá trị ẩn).
Hàm SUBTOTAL sẽ bỏ qua không tính toán tất cả các hàng bị ẩn bởi lệnh Filter (Auto Filter) không phụ thuộc vào đối số function_num được dùng.
Hàm SUBTOTAL được thiết kế để tính toán cho các cột số liệu theo chiều dọc, nó không được thiết kế để tính theo chiều ngang.
Hàm này chỉ tính toán cho dữ liệu 2-D do vậy nếu dữ liệu tham chiếu dạng 3-D (Ví dụ về tham chiếu 3-D: =SUM(Sheet2:Sheet13!B5) thì hàm SUBTOTAL báo lỗi VALUE!.
[KHAI GIẢNG] KHÓA HỌC KIỂM TOÁN THỰC HÀNH TRÊN EXCEL
Hàm Sumif Trong Excel Là Gì? Cách Sử Dụng Hàm Sumifs
Hàm SUMIF là gì? Sự khác nhau giữa hàm SUMIFS trong Excel với hàm SUMIF là như thế nào? Bài viết này sẽ hướng dẫn cho các bạn cú pháp, ý nghĩa, cách sử dụng và một số cách kết hợp giữa hàm SUMIF, SUMIFS với một số hàm thông dụng khác.
#1. Tổng quan về hàm SUMIFS
Trong công việc hàng ngày có sử dụng đến phần mềm Excel, để cộng các giá trị cùng một điều kiện cho trước với nhau thì chúng ta thường nghĩ ngay tới hàm SUMIF. Vậy Hàm SUMIF là gì?
Hàm SUMIF là gì?
Hàm SUMIFS trong Excel là một trong những hàm Excel cơ bản, hàm tính toán thường được dùng trong Excel. Để tính tổng trong Excel chúng ta sẽ dùng đến hàm SUM, nếu muốn thêm 1 điều kiện nhất định cho hàm tính tổng đó sẽ dùng hàm SUMIF.
#2. Cách sử dụng hàm SUMIFS
#2.1. Cú pháp và cách sử dụng hàm SUMIF
Cú pháp hàm này như sau:
=SUMIF(range,criteria,[sum_range])
Cú pháp hàm SUMIF có các đối số sau đây:
– Range (Bắt buộc): Phạm vi ô bạn muốn được đánh giá theo tiêu chí. 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 (ví dụ bên dưới).
– criteria (Bắt buộc): Tiêu chí ở dạng số, biểu thức, tham chiếu ô, văn bản hoặc hàm xác định sẽ cộng các ô nào. Ký tự đại diện có thể được bao gồm-dấu chấm hỏi (?) để khớp với bất kỳ ký tự đơn 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 hay dấu sao thực sự, hãy gõ dấu ngã (~) trước ký tự.
– sum_range Tùy chọn. Các ô thực tế để cộng nếu bạn muốn cộng các ô không phải là các ô đã xác định trong đối số range. Nếu đối số sum_range bị bỏ qua, Excel cộng các ô được xác định trong đối số range (chính các ô đã được áp dụng tiêu chí).
Sum_range phải có cùng kích cỡ và hình dạng theo phạm vi. Nếu không, hiệu suất có thể bị, và công thức sẽ tính tổng phạm vi ô bắt đầu với ô đầu tiên trong sum_range nhưng có cùng kích thước như phạm vi.
Bước 1: Tại ô G8, chúng ta viết công thức sau: =SUMIF(E8:E22;”nam”;F8:F22).
Trong công thức này, vùng phạm vi đánh giá tiêu chí là từ ô E8 đến ô E22, tiêu chí là giới tính nam, vùng tính tổng là từ ô F8 đến F22.
Bước 2: Bấm phím Enter, kết quả sẽ là 87.860.000 đồng. Tức tổng số tiền lương của các nhân viên nam là 87,86 triệu đồng.
#2.2. Cú pháp và cách sử dụng hàm SUMIFS
Hàm này có cú pháp tổng quát như sau:
=SUMIFS(sum_range,criteria_range1,criteria1,[criter_range2, criteria2], …)
Bước 1: Tại ô G7, chúng ta viết công thức sau:
=SUMIFS(F7:F21;E7:E21;”Nam”;D7:D21;”Văn phòng”)
Trong công thức này, vùng tính tổng là từ ô F7 đến ô F21, vùng tiêu chí đánh giá thứ nhất là từ ô E7 đến ô F21, tiêu chí thứ nhất là giới tính nam, cùng tiêu chí đánh giá thứ 2 là từ ô D7 đến ô D21, tiêu chí thứ 2 là văn phòng.
Bước 2: Bấm phím Enter, kết quả của phép tính sẽ là: 15.910.000.
Vậy, tổng tiền lương của các nhân viên nam ở bộ phận văn phòng là 15.910.000 đồng.
#2.3. Hàm SUMIFS nhiều điều kiện
Ví dụ #3, Tính tổng tiền lương của nhân viên thỏa mãn các điều kiện sau:
– Giới tính nam;
– Thuộc bộ phận văn phòng;
– Sinh năm 1992
Để giải quyết bài toàn này, chúng ta thực hiện như sau:
Bước 1: Tại ô H7, chúng ta viết công thức:
=SUMIFS(G7:G21;F7:F21;”nam”;E7:E21;”văn phòng”;D7:D21;1992)
Bước 2: Bấm phím Enter, chúng ta sẽ ra kết quả là: 8.110.000 đồng.
Hỏi: Sự khác nhau giữa hàm SUMIF và SUMIFS như thế nào?
Trả lời: Hàm SUMIF dùng để tính tổng các phần tử theo 1 điều kiện cho trước, còn SUMIFS dùng để tính tổng các phần tử thỏa mãn từ hai điều kiện trở lên.
Hỏi: Tối đa có thể dùng bao nhiêu điều kiện trong hàm SUMIFS?
Hỏi: Cách sử dụng các phím tắt trong Excel?
Hỏi: Tải file hướng dẫn hàm SUMIFS tại đâu?
Bạn đang đọc nội dung bài viết Hàm Sumif Trong Kiểm Toán trên website Beiqthatgioi.com. Hy vọng một phần nào đó những thông tin mà chúng tôi đã cung cấp là rất hữu ích với bạn. Nếu nội dung bài viết 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!