Các hàm Excel thông dụng thường dùng trong kế toán mà bạn cần biết

Ngoài các phần mềm kế toán chuyên nghiệp như Misa, Fast, Mcom…, Excel là phầm mềm được sử dụng phố biến trong công việc của kế toán viên. Việc nắm vững các hàm Excel thông dụng thường giúp cho các bạn kế toán viên tiết kiệm thời gian và công sức so với cách tính thủ công thông thường. Hôm nay, Pgdphurieng.edu.vn sẽ liệt kê và hướng dẫn bạn đọc cách sử dụng một số hàm thường được sử dụng trong kế toán.

Hàm Cộng Trừ Nhân Chia

Phép cộng

Cú pháp =SUM(number1,[number2],…)

Trong đó:

• SUM: là hàm dùng để tính tổng.
• Number1: là đối số thứ 1.
• Number2,…: là đối số thứ 2 trở đi (có thể có, có thể không).

Đối số có thể là một vùng dữ liệu, một số, một tham chiếu ô đơn, và được phân cách nhau bằng dấu phẩy (,).

Hoặc bạn có thể dùng dấu cộng (+) để thực hiện phép cộng.

Phép trừ

Cú pháp: =number1 – number2

Trong đó:

• Dấu trừ (-):  là toán tử tính toán dùng để thực hiện phép trừ.
• Number1: là số bị trừ.
• Number2: là số trừ.

Number có thể là số hoặc là vị trí một ô chứa dữ liệu.

Ví dụ:Bạn gõ:  = 5-2 vào ô tính => kết quả là 3.

Phép nhân

Cú pháp: =PRODUCT(number1,[number2],…)

Trong đó:

• PRODUCT: là hàm dùng để nhân các thừa số..
• Number1: là thừa số thứ 1.
• Number2,…: là thừa số thứ 2 trở đi (có thể có, có thể không).

Number có thể là một vùng dữ liệu, một số, một tham chiếu ô đơn, và được phân cách nhau bằng dấu phẩy (,).

Hoặc bạn có thể dùng dấu hoa thị (*) để thực hiện phép nhân.

Ví dụ: Bạn gõ công thức :=PRODUCT(2,6,9) vào một ô tính => kết quả là 108.

Phép chia

Cú pháp: =number1 / number2

Trong đó:

• Dấu gạch chéo (/): là toán tử tính toán dùng để thực hiện phép chia.
• Number1: là số bị chia.
• Number2: là số chia.

Number có thể là số hoặc là vị trí một ô chứa dữ liệu.

Ví dụ: Bạn gõ vào một ô tính: =10/3 => kết quả thu được là 3.33333…

Hàm AVERAGE

Cú pháp: = AVERAGE (number1, [number2], …)

Trong đó:

• AVERAGE: là tên hàm dùng để tính giá trị trung bình của các đối số.
• Number1: là đối số thứ 1.
• Number2,…: là đối số thứ 2 trở đi (có thể có, có thể không).

Đối số có thể là một vùng dữ liệu, một số, một tham chiếu ô đơn, và được phân cách nhau bằng dấu phẩy (,).

Ví dụ: =AVERAGE(1,2,3) => trả về kết quả là 2.

Hàm SUBTOTAL

Cú pháp: = SUBTOTAL (function_num,ref1,[ref2],…)

Trong đó:

• SUBTOTAL: là tên hàm dùng để tính toán các dòng được hiện ra. Các dòng bị ẩn bởi Filter sẽ được bỏ qua không tính.
• Function_num: có giá trị từ 1-11 hay 101-111 chỉ định hàm sử dụng cho tổng phụ. 1-11 bao gồm những hàng ẩn bằng cách thủ công, còn 101-111 loại trừ chúng ra; những ô được lọc ra sẽ luôn được loại trừ.
• Ref1: là phạm vi hoặc tham chiếu được đặt tên đầu tiên mà bạn muốn tính tổng phụ cho nó.
• Ref2,… : giá trị tùy chọn. Phạm vi hoặc chuỗi được đặt tên từ 2 đến 254 mà bạn muốn tính tổng phụ cho nó.

Ví dụ bạn muốn cộng tổng các dòng được lọc ra bằng công cụ Filter, ở ô tổng cộng, bạn dùng công thức: =SUBTOTAL(9,G3:G12). Trong đó 9: là yêu cầu cộng tổng, G3:G12 là vùng chứa dữ liệu áp dụng công thức. Khi ta sử dụng công cụ lọc thông tin theo yêu cầu, ô tổng sẽ chỉ cộng các dòng mà được lọc.

Tổng doanh thu lọc theo Chi nhánh Hà Nội:

Tổng doanh thu lọc theo quí 1/2018:

Hàm tìm kiếm (VLOOKUP và HLOOK UP)

VLOOKUP

Cú pháp hàm: = VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])

Trong đó:

• VLOOKUP: là tên hàm dùng để tìm kiếm dữ liệu theo hàng dọc (thẳng đứng).
• lookup_value: là giá trị cần tìm kiếm
• table_array: là bảng tham chiếu.
• col_index_num: số cột trong bảng tham chiếu chứa giá trị cần trả về.
• [range_lookup]: đối số tùy chọn. Trả về kết quả phù hợp gần đúng hoặc chính xác – được chỉ khai báo hai giá trị là 1/TRUE (xấp xỉ) , hoặc 0/FALSE (chính xác).

Ví dụ bạn có bảng đơn giá cần điền, bạn sử dụng hàm VLOOKUP theo công thức sau đây:

Lưu ý:

• Cột đầu tiên phải chứa dữ liệu cần tham chiếu.
• Vùng tham chiếu luôn luôn phải để giá trị tuyệt đối. (Trừ khi bạn có yêu cầu khác).
• Nếu hàm VLOOKUP trả về giá trị lỗi #N/A nếu như giá trị cần tìm không có trong bảng tham chiếu. Bạn nên kiểm tra lại bảng tham chiếu và bổ sung thông tin.

Hàm HLOOKUP

Cú pháp hàm: = HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])

Trong đó:

• HLOOKUP: là tên hàm dùng để tìm kiếm dữ liệu theo hàng ngang.
• lookup_value: là giá trị cần tìm kiếm
• table_array: là bảng tham chiếu.
• row_index_num: số cột trong bảng tham chiếu chứa giá trị cần trả về.
• [range_lookup]: đối số tùy chọn. Trả về kết quả phù hợp gần đúng hoặc chính xác – được chỉ khai báo hai giá trị là 1/TRUE (xấp xỉ) , hoặc 0/FALSE (chính xác).

Cách sử dụng hàm HLOOKUP tương tự như VLOOKUP, chỉ khác hàm HLOOKUP tìm kiếm dữ kiệu theo dòng.

Hàm IF

Cấu trúc hàm: = IF (Logical_test,[value_if_true],[value_if_false])

Trong đó:

• IF: là tên hàm điều kiện sẽ trả về giá trị 1, nếu điều kiện đúng, và giá trị 2 nếu điều kiện sai.
• Logical_test: là điều kiện tham chiếu.
• [value_if_true]: đối số tùy chọn. Là giá trị trả về nếu điều kiện Logical_test là đúng. Nếu người dùng không nhập giá trị value_if_true, Excel sẽ trả về giá trị TRUE nếu điều kiện đúng.
• [value_if_false]: đối số tùy chọn. Là giá trị trả về nếu điều kiện Logical_test là sai. Nếu người dùng không nhập giá trị value_if_false, Excel sẽ trả về giá trị FALSE nếu điều kiện sai.

Ví dụ bạn có bảng điểm sau cần phân loại:

• Những thí sinh lớn hơn hoặc bằng 5 điểm => Đỗ.
• Những thí sinh dưới 5 điểm => Trượt.
  Ta sử dụng công thức cho ô C2 là; =IF(B2>=5,”Đỗ”,”Trượt”).

Hàm SUMIF và SUMFIS

Cấu trúc hàm SUMIF: =SUMIF (range, criteria,sum_range)

Trong đó:

• SUMIF: là tên hàm dùng để tính tổng theo một điều kiện yêu cầu.
• Range: là vùng tham chiếu chứa dữ liệu điều kiện.
• Criteria: là điều kiện cần thỏa mãn.
• sum_range: là vùngcần tính tổng.

Ví dụ: Bạn có bảng doanh thu như bên dưới, và cần tính tổng doanh thu của chi nhánh Hà nội, Bạn gõ công thức vào một ô trong bảng tính Excel =SUMIF(B3:B12,”Chi nhánh Hà Nội”,G3:G12).

Cấu trúc hàm SUMIFS: = SUMIFS sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)

Trong đó:

• SUMIFS: là tên hàm dùng để tính tổng thỏa mãn nhiều điều kiện.
• sum_range: là vùngcần tính tổng.
• criteria_range1,2,3…: là vùng tham chiếu chứa dữ liệu điều kiện 1,2,3…
• Criteria1,2,3…: là điều kiện cần thỏa mãn 1,2,3…

Với bản doanh thu ví dụ trên, ta cần tính doanh thu của Chi nhánh Hà Nội trong quí 1/2018, ta dùng công thức: =SUMIFS(G3:G12,B3:B12,”Chi nhánh Hà Nội”,C3:C12,”1/2018″).

Hàm AND VÀ OR

Cấu trúc hàm: = AND(Logical1; [Logical2]; [Logical3];…)

Trong đó:

• AND: là tên hàm có ý nghĩa là VÀ, và trả về kết quả TRUE khi tất cả các đối số đều đúng.
• Logical1,2,3…: Đối số là các hằng, các biểu thức logic.

Cấu trúc hàm:= OR(Logical1; [Logical2]; [Logical3];…)

Trong đó:

• OR: là tên hàm có ý nghĩa là HOẶC, và trả về kết quả FALSE khi tất cả các đối số đều sai.
• Logical1,2,3…: Đối số là các hằng, các biểu thức logic.

Ví dụ phân biệt AND và OR:

AND(TRUE,FALSE) = FALSE

OR(TRUE,FALSE) =TRUE.

Hàm MIN, MAX

Cấu trúc hàm: =MIN(number 1, number 2, …)

Trong đó:

• MIN: là tên hàm dùng để trả về giá trị nhỏ nhất trong chuỗi số.
• number 1,2…: là chuỗi số cần tìm giá trị nhỏ nhất.

Cấu trúc hàm: =MAX(number 1, number 2, …)

Trong đó:

• MAX: là tên hàm dùng để trả về giá trị lớn nhất trong chuỗi số.
• number 1,2…: là chuỗi số cần tìm giá trị nhỏ nhất.

Ví dụ: MIN(10,5,16) =5 và MAX(10,5,16)=16.

Hàm LEFT, RIGHT,MID

Các hàm LEFT, RIGHT,MID cho phép người dùng lấy ra một số ký từ trong chuỗi.

Cú pháp:

= LEFT(text, [num_chars]).
= RIGHT(text, [num_chars]).
= MID(text, start_num, num_chars).

Trong đó:

• LEFT: là tên hàm muốn trích xuất ký tự từ trái sang phải của chuỗi văn bản.
• RIGHT: là tên hàm muốn trích xuất ký tự từ phải sang trái của chuỗi văn bản.
• MID: là tên hàm muốn trích xuất ký tự từ start_num mà bạn qui địnhtính từ trái sang phải.
• Text: Chuỗi văn bản có chứa các ký tự mà bạn muốn trích xuất.
• Start_num: số thứ tự của ký tựmà hàm MID bắt đầu lấy.
• Num_chars: Chỉ rõ số ký tự mà bạn muốn hàm LEFT/RIGHT muốn trích xuất. Num_chars phải lớn hơn hoặc bằng không.Nếu num_chars lớn hơn độ dài của văn bản, hàm LEFT/RIGHT trả về toàn bộ văn bản. Nếu num_chars được bỏ qua, thì nó được mặc định là 1.

Ví dụ bạn có chuỗi “Pgdphurieng.edu.vn.VN”, bạn lần lượt lấy 5 ký tự bên trái, 5 ký tự bên phải, và 5 ký tự tính từ kí tự thứ 4;

=LEFT(“Pgdphurieng.edu.vn.VN”,5) => kết quả là: ThuTh;
=RIGHT(“Pgdphurieng.edu.vn.VN”,5) => kết quả là: em.VN;
=MID(“Pgdphurieng.edu.vn.VN”,4,5) => kết quả là: Thuat.

HÀM TEXT

Cú pháp: = TEXT(number,format)

Trong đó:

• TEXT: là tên hàm dùng để thay đổi cách hiển thị số .
• Number: là số cần định dạng.
• Format: mã định dạng mà bạn muốn áp dụng.

Hàm TEXT dùng để đổi định dang số sang chữ thông thường, hay định dạng số dưới dạng ngày tháng, phần trăm, tiền tệ,…

Dưới đây là một số mã định dạng phổ biến mà Thủ Thuật Phần Mềm giới thiệu cho bạn:

Mã định dạng	Mô tả	Một số định dạng mã mẫu
“#.000”:	Làm tròn số và thể hiện đến phân số thứ 3 (tự động thêm số 0 vào vị trí còn thiếu).	Ví dụ: TEXT(2.7,”#.000″) =2.700
“#.##”	Làm tròn số và thể hiện đến phân số thứ 2 và không thể hiện số 0.	Ví dụ: TEXT(2.7856,”#.##”) =2.79
“?????.??”	Thêm các dấu cách đứng đầu và làm tròn, thể hiện phân số thứ 2.	Ví dụ: TEXT(2.7,”?????.??”) =˽˽˽˽2.7
“#,###.00”	Sử dụng dấu ngăn cách phần nghìn và 2 số thập phân	Ví dụ: TEXT(1234567.99,”###,###,###.00″) =1,234,567.99
“$#,###.00”	Thêm định dạng tiền tệ với dấu tách phần nghìn và hai số thập phân.	Ví dụ: =TEXT(1234567.99,”$#,###.00″) =$1,234,567.99
“% .00”	Thêm định dạng tiền tệ với hai số thập phân.	Ví dụ: =TEXT(2.7,”% .00″) =%270.00
“MM/DD/YY”	Định dạng tháng /ngày/năm.	Ví dụ: TEXT(TODAY(),”MM/DD/YY”) =07/03/19
“H:MM AM/PM”	Định dạng giờ: phút sáng/chiều.	Ví dụ: TEXT(NOW(),”H:MM AM/PM”) = 0:41 PM.

Trên đây Pgdphurieng.edu.vn đã hướng dẫn bạn một số hàm thường dùng trong kết toán. Hy vọng bài viết này sẽ hữu ích cho các bạn. Chúc các bạn thành công!