Excel là công cụ quan trọng bậc nhất của các Kế toán viên, Kiểm toán viên. Với bảng dữ liệu hàng trăm dòng, hàm trăm cột thì biết sử dụng Excel thôi là chưa đủ. Bạn cần biết dùng đúng …

Excel là công cụ quan trọng bậc nhất của các Kế toán viên, Kiểm toán viên. Với bảng dữ liệu hàng trăm dòng, hàm trăm cột thì biết sử dụng Excel thôi là chưa đủ. Bạn cần biết dùng đúng công cụ, đúng thời điểm để thu được hiệu quả công việc cao nhất. Vậy hàm gì là hàm excel cho kiểm toán, kế toán? Họ dùng nó trong công việc như thế nào?

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 Mã hàng hoá, tên hàng hoá từ Bảng chi tiết danh mục hàng hóa về Bảng Nhập Xuất Tồn.
• 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.

~> Bài tập luyện tập hàm VLOOKUP

~> 3 lỗi thường gặp của hàm VLOOKUP

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

~> Bài tập luyện tập hàm IF

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ác bảng tính có liên quan.

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 ô).
• sum_range: Là dãy ô chưa giá trị tương ứng liên quan đến điều kiện cần tính. Cụ thể trong bài: là cột phát sinh Nợ hoặc phát sinh Có trên BNL…. Chú ý: Điểm đầu và điểm cuối của dãy ô tính tổng và dãy ô điều kiện phải tương ứng nhau.

~> Bài tập tổng hợp hàm VLOOKUP, SUMIF

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.

eXcel kế toán | Là 1 kế toán thường xuyên với những con số lớn nhỏ thì đòi hỏi cần phải biết ít nhất 1 số hàm excel kế toán cơ bản. Excel luôn là công cụ hỗ trợ nhân viên làm việc linh động và hiệu quả nhất với số liệu.để ghi nhận sổ sách kế toán và lập kế hoạch tài chính. Có thể tạo ra một chương trình kế toán cơ bản hoặc sổ kế toán để theo dõi các giao dịch tài chính và so sánh thu nhập, chi tiêu của tổ chức – hãy cùng Kế toán Việt Hưng tìm hiểu sâu hơn qua bài viết. Các hàm cơ bản trong Excel kế toán cần nắm rõ

♦ Hàm SUM

– Tính tổng tất cả các số trong vùng dữ liệu được chọn.

– Công thức: SUM(Number1, Number2, Number3…)

– Trong đó, Number1, Number2, Number3… là các số cần phải tính tổng.

♦ Hàm SUMIF

– Tính tổng giá trị của các ô được chỉ định với điều kiện cụ thể, thường dùng trong nghiệp vụ kết chuyển cuối tháng khi làm Nhật ký chung, làm các bảng tổng hợp nhập – xuất hàng…

– Công thức SUMIF(Range, Criteria, Sum_range) = SUMIF(Vùng chứa điều kiện, Điều kiện, Vùng cần tính tổng)

– Trong đó:

Range: Dãy số muốn xác định tổngCriteria: điều kiện muốn tính tổng (có thể là biểu thức, chuỗi hoặc số)Sum_range: các ô chứa giá trị cần tính tổng

– Ví dụ: =SUMIF (B2:B10, “<=100”) – tính tổng giá trị trong vùng chọn từ B2 đến B10 với điều kiện giá trị nhỏ hơn hoặc bằng 100)

♦ Hàm AVERAGE

– Hàm tính giá trị trung bình của các đối số nhập vào.

– Công thức AVERAGE(Number1, Number2, Number3…)

– Trong đó, Number1, Number2, Number3… là các số nhập vào cần tính giá trị trung bình.

♦ Hàm SUMPRODUCT

– Tính tích của dãy ô, sau đó tính tổng của các tích đó.

– Công thức: SUMPRODUCT(Array1, Array2, Array3…)

– Trong đó: Array1, Array2, Array3… lần lượt là các dãy ô bạn muốn tính tích, sau đó tính tổng các tích.

– Ví dụ: =SUMPRODUCT(A2:A7, B3:B8, C5:C12)

♦ Hàm MAX

– Trả về kết quả số lớn nhất trong dãy được nhập vào.

– Công thức: MAX(Number1, Number2, Number3…)

– Trong đó, Number1, Number2, Number3… là dãy số bạn muốn tìm giá trị lớn nhất trong đó.

♦ Hàm LAGRE

– Tìm số lớn thứ k trong dãy ô được nhập.

– Công thức: LAGRE(Array, k)

– Trong đó, Array là dãy ô cần xác định, k là thứ hạng số muốn tìm, tính từ số lớn nhất.

Ví dụ =LAGRE(D4:D20, 3) – tìm số lớn thứ 3 trong dãy ô từ D4 đến D20.

♦ Hàm MIN

– Trả về kết quả là số nhỏ nhất trong dãy được nhập vào

– Công thức: MIN(Number1, Number2, Number3…)

– Trong đó, Number1, Number2, Number3… là dãy số mà bạn muốn tìm giá trị nhỏ nhất trong đó.

♦ Hàm SMALL

– Tìm số có giá trị nhỏ thứ k trong dãy ô được nhập vào.

– Công thức: SMALL(Array, k)

– Trong đó, Array là dãy ô được chọn, k là thứ hạng của số cần tìm tính từ số nhỏ nhất).

♦ Hàm COUNT

– Là hàm đếm dữ liệu của các ô chứa dữ liệu kiểu số trong dãy được nhập.

– Công thức: COUNT(Value1, Value2, Value3…)

– Trong đó, Value1, Value2, Value3… là dãy hay mảng dữ liệu được chọn.

♦ Hàm COUNTA

– Là hàm đếm dữ liệu của tất cả của tất cả các cô chứa dữ liệu.

– Công thức: COUNTA(Value1, Value2, Value3…)

♦ Hàm COUNTIF

– Đếm các ô chứa giá trị thỏa mãn điều kiện cho trước.

– Công thức: COUNTIF(Range, Criteria)

– Trong đó:

Range là dãy dữ liệu muốn đếm.Criteria: điều kiện cho các ô được đếm

– Ví dụ: =COUNTIF(A2:A30, “>1000”) – Đếm tất cả các ô trong dãy từ A2 đến A30 thỏa mãn điều kiện lớn hơn 1000.

2. Các hàm excel kế toán nhóm ĐIỀU KIỆN

♦ Hàm IF

– Hàm điều kiện này trả về giá trị 1 nếu điều kiện đúng, trả về giá trị 2 nếu điều kiện sai. Hàm này thường được sử dụng khi lập bảng lương cho nhân viên, tính thuế thu nhập cá nhân, thưởng doanh số cho nhân viên kinh doanh…

– Công thức: IF(logical-test,[value_if_true],[value_if_true]) = IF(Điều kiện, Giá trị 1, Giá trị 2)

– Ví dụ: =IF(C2>=5, “DUNG”, “SAI”) = DUNG

\=IF(C2>=6, “DUNG”, “SAI”) = SAI

3. Các hàm excel kế toán nhóm TÌM KIẾM

♦ Hàm VLOOKUP

– Là hàm trả về giá trị tìm kiếm theo cột đưa từ bảng tham chiếu với bảng cơ sở dữ liệu theo đúng giá trị dò tìm. Nếu X = 0 thì kết quả dò tìm chính xác, X = 1 là kết quả dò tìm một cách tương đối.

– Công thức: VLOOKUP(Lookup Value, Table Array, Col idx num, [range lookup]) = VLOOKUP(Giá trị dò tìm, Bảng tham chiếu, Cột cần lấy, X)

– Ví dụ: =VLOOKUP(E15,$C$20:$D$22,3,0) – tìm một giá trị bằng giá trị ở ô E15 và lấy giá trị tương ứng ở cột thứ 3.

♦ Hàm HLOOKUP

– Tìm kiếm giá trị như hàm VLOOKUP nhưng bằng cách so sánh với giá trị trong hàng đầu tiên của bảng tham chiếu nhập vào.

– Công thức: HLOOKUP(Lookup Value, Table Array, Col idx num, [range lookup])

4. Các hàm excel kế toán nhóm LOGIC

♦ Hàm AND

– Công thức: AND(Logical1, Logical2,…)

– Trong đó, Logical1, Logical2,… là các biểu thức có điều kiện. Các đối số nhập vào phải là giá trị logic hoặc mảng/ tham chiếu có chứa giá trị logic, nếu không kết quả sẽ trả về

VALUE!

– Nếu hàm cho kết quả TRUE (1) nếu các đối số nhập vào là đúng, và khi hàm trả về giá trị FALSE (0) nếu có 1 hay nhiều đối số của nó bị nhập sai.

– Ví dụ: =AND(D6>0,D6<6000)

♦ Hàm OR

– Công thức: OR(Logical1, Logical2…)

– Trong đó, Logical1, Logical2… là các biểu thức điều kiện. Hàm sẽ trả về giá trị True (1) nếu bất kỳ đối số nào nhập vào là đúng và trả về giá trị FALSE(0) nếu tất cả các đối số nhập vào sai.

– Ví dụ: =OR(D6>04/03/67,D6>01/01/2018)

♦ Hàm NOT

– Đây là hàm đảo ngược giá trị của đối số nhập vào.

– Công thức: NOT(Logical)

– Trong đó Logical là biểu thức logic hoặc một giá trị

5. Các hàm excel kế toán nhóm TOÁN HỌC

♦ ​Hàm ABS

– Hàm ABS giúp lấy giá trị tuyệt đối của một số.

– Công thức: ABS(Number)

– Trong đó, Number là một giá trị số, một biểu thức/ tham chiếu

– Ví dụ: =ABS(B7+7)

♦ Hàm PRODUCT

– Tính tích của một dãy số nhập vào.

– Cú pháp: PRODUCT(Number1, Number2, Number3…)

– Trong đó, Number1, Number2, Number3… là dãy số cần tính tích.

♦ ​Hàm MOD

– Tính giá trị dư của phép chia

– Công thức: MOD(Number, pisor)

– Trong đó, number là số bị chia, pisor là số chia.

6. Các hàm excel kế toán nhóm TIỀN LƯƠNG

– Những hàm sẽ sử dụng khi lập bảng tiền lương bao gồm:

• Hàm LEFT(text,số ký tự cần lấy)

VD: LEFT(“ketoanviethung”,13)= “ketoanviethung”

• Hàm VLOOKUP như trên
• Hàm LEN(text)

VD: LEN(“ketoanviethung”)=13

• Hàm SUMIF
• Hàm SUBTOTAL(function_num,relf1,relf2,…)

function_num: là con số từ 1 -> 11 (có thêm 101 đến 111 trong Excel 2003,2007)

relf1,relf2: là các vùng địa chỉ tham chiếu

VD: Kế toán thường sử dụng đối số 9 & thường sử dụng tính tổng cho từng tài khoản, tính tổng phát sinh bên Nợ, Có tính tổng số tiền cuối ngày.

\= SUBTOTAL(9;dãy ô cần tính tổng) (Số 9 là cú pháp mặc định của hàm cho việc tính tổng)

• Hàm MAX
• Hàm MIN
• Hàm MID
• Hàm IF
• Hàm AND(đối 1, đối 2,…)

VD: =AND(D7>0,D7<5000)

Các đối số: là các biểu thức có điều kiện

• Hàm OR(đối 1, đối 2,…)

VD: =OR(F7>03/02/74,F7<01/01/20XX)

– Tra cứu thông tin nhân viên có trong bảng danh sách nhân viên:

Hàm VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])

Hàm INDEX+MATCH: kết hợp 2 hàm này với nhau để truy vấn, tìm kiếm thông tin

– Việc chấm công, tính lương thực hiện theo tháng, vì vậy những hàm xử lý ngày tháng, thời gian trong excel cũng thường được sử dụng:

• Hàm DATE(year,month,day): tạo ra 1 giá trị ngày tháng xác định rõ bởi năm, tháng, ngày
• Hàm YEAR(serial_number) : theo dõi số năm của 1 giá trị ngày tháng
• Hàm MONTH(serial_number): theo dõi số tháng của 1 giá trị ngày tháng
• Hàm DAY(serial_number): theo dõi số ngày của 1 giá trị ngày tháng
• Hàm HOUR(serial_number): theo dõi số giờ của 1 giá trị thời gian
• Hàm MIN(serial_number): theo dõi số phút của 1 giá trị thời gian

– Chấm công làm việc của nhân viên trong công ty

• COUNTIF : Đếm số ký hiệu công trong bảng chấm công (theo 1 điều kiện duy nhất)
• COUNTIFS : Đếm số ký hiệu công trong bảng chấm công (theo nhiều điều kiện cùng lúc)
• SUMIF : Tính tổng số giờ công trong bảng chấm công (theo 1 điều kiện duy nhất)
• SUMIFS : Tính tổng số giờ công trong bảng chấm công (theo nhiều điều kiện cùng lúc)

– Tính lương cho nhân viên

• IF : Cần biện luận logic trong việc tính toán trong 1 số trường hợp (Nếu theo yêu cầu 1 thì kết quả là gì, nếu không thỏa mãn yêu cầu 1 thì kết quả là gì)
• Hàm AND / OR : Khi cần biện luận nhiều điều kiện kết hợp nhau thì sẽ sử dụng hàm And/Or để ghép nối các điều kiện trong hàm IF
• Hàm LOOKUP / VLOOKUP : Truy vấn tới các nội dung: Thông tin tính lương, thông tin nhân viên, kết quả chấm công…
• Hàm SUM / SUMIF / SUMIFS : Tính tổng (có điều kiện hoặc không) các nội dung tiền lương.

7. Các hàm excel kế toán nhóm CÔNG NỢ

Những hàm sẽ sử dụng khi lập bảng công nợ bao gồm:

• Hàm VLOOKUP, SUMIFS
• Hàm MIN, MAX

(1) Cách tính số dư đầu kỳ công nợ

Hướng dẫn lập bảng tổng hợp công nợ phải thu khách hàng trên Excel chúng ta quy định số dư đầu kỳ là số dư tính tới thời điểm từ ngày (G7). Từ đó chúng ta có công thức ở ô E12 tức là nợ đầu kỳ như sau:

\=MAX(VLOOKUP(C12,DMKH!$A$3:$H$6,7,0) -VLOOKUP(C12,DMKH!$A$3:$H$6,8,0)+SUMIFS(SO_TIEN,CT_NO,C12,NGAY_GS,”<” & $G$7) – SUMIFS(SO_TIEN,CT_CO,C12,NGAY_GS, “<” &$G$7),0).

Công thức ở ô E13 là:

\=-MIN(VLOOKUP(C12,DMKH!$A$3:$H$6,7,0) -VLOOKUP(C12,DMKH!$A$3:$H$6,8,0)+SUMIFS(SO_TIEN,CT_NO,C12,NGAY_GS,”<” & $G$7) – SUMIFS(SO_TIEN,CT_CO,C12,NGAY_GS, “<” &$G$7),0).

Dễ thấy rằng 2 công thức trên có nhiều điểm tương đồng. Thay vì sử dụng kết hợp hàm MAX để xác định số dư bên Nợ thì số dư bên Có chúng có thể thay thế là MAX = -MIN. Qua đó, việc bạn thay đổi ngày tháng ở ô G7 thì số dư này luôn đảm bảo tính chính xác.

(2) Cách tính số phát sinh trong kỳ công nợ

Số phát sinh bên Nợ (G12) thực hiện theo công thức:

\=SUMIFS(SO_TIEN,CT_NO,C12,NGAY_GS,”>=” & $G$7, NGAY_GS, “<=” & $G$8).

Số phát sinh bên Có (H12) áp dụng công thức:

\=SUMIFS(SO_TIEN,CT_CO,C12,NGAY_GS,”>=” & $G$7, NGAY_GS, “<=” & $G$8).

(3) Tính số dư cuối kỳ

Nhập công thức tại ô I12 =MAX(E12+G12-F12-H12,0) nhằm mục đích tính số dư cuối kỳ bên Nợ.

Nhập công thức tại ô J12 để tính số dư cuối kỳ bên Có: =-MIN(E12+G12-F12-H12,0).

THAM KHẢO: Hơn 60 Khoá học kế toán Online chất lượng cam kết đầu ra

Trên đây là các hàm cơ bản trong Excel kế toán cần nắm rõ dễ dàng áp dụng khi xử lý các nghiệp vụ kế toán hàng ngày kết hợp với phần mềm kế toán giảm tải công việc không mất quá nhiều thời gian – Tham gia ngay Khoá học kế toán Online 1 kèm 1 bậc thầy chỉ sau 48H cam kết làm được việc!