Hàm Excel cho kế toán: 4 việc thật và những cái bẫy

Kế toán không cần biết nhiều hàm. Cần biết đúng vài hàm và biết chỗ nào chúng làm sai số. Bài này viết theo bốn công việc thật của phòng kế toán, không liệt kê hàm theo bảng chữ cái.

Việc 1 — Đối chiếu công nợ giữa hai file

Bài toán muôn thuở: sổ nội bộ một file, sao kê ngân hàng hoặc bảng đối tác gửi một file, cần biết chênh nhau chỗ nào.

=IFERROR(VLOOKUP(A2; SaoKe!A:C; 3; 0); "Không có trong sao kê")

Ba nguyên nhân khiến đối chiếu ra lệch mà không phải do số liệu sai:

• Khoảng trắng ẩn trong mã chứng từ. Bọc TRIM cả hai bên: VLOOKUP(TRIM(A2); ...).
• Một bên là số, một bên là chữ. Mã "0001234" xuất ra từ phần mềm là chữ, gõ tay thành số 1234. Nhìn giống nhau, máy coi là khác. Ép cùng kiểu bằng TEXT(A2;"0000000") hoặc VALUE().
• Sai số thập phân. Hai số hiển thị bằng nhau nhưng lệch 0,004 do làm tròn. So sánh bằng =ABS(A2-B2)<1 thay vì =A2=B2.

Việc 2 — Tổng hợp theo tài khoản, theo tháng, theo đối tượng

SUMIFS làm hết. Cấu trúc quen thuộc nhất:

=SUMIFS(SoCai!$E:$E; SoCai!$C:$C;$A2; SoCai!$B:$B;">="&$B$1; SoCai!$B:$B;"<="&EOMONTH($B$1;0))

Đọc là: cộng cột phát sinh, với tài khoản bằng ô A2, ngày từ đầu kỳ đến cuối tháng của kỳ đó. EOMONTH tự lấy ngày cuối tháng nên không phải sửa tay mỗi kỳ.

Khoá $ đúng chỗ là thứ quyết định bảng này kéo được sang ngang. Vùng dữ liệu khoá tuyệt đối, ô điều kiện khoá cột.

Việc 3 — Tính lương, thuế và các bậc luỹ tiến

Đây là chỗ hay thấy IF lồng bảy tầng. Đừng làm vậy — sang năm đổi bậc là phải dò lại từng dấu ngoặc.

Cách bền hơn: dựng một bảng bậc thuế nằm riêng (ngưỡng dưới, thuế suất, số trừ), rồi tra cứu vào đó bằng VLOOKUP ở chế độ gần đúng — đúng một trong số ít trường hợp nên bỏ tham số 0:

=VLOOKUP(ThuNhap; BangBac!$A$2:$C$8; 3; 1)

Điều kiện bắt buộc: cột ngưỡng phải sắp xếp tăng dần. Sai thứ tự thì hàm trả về kết quả sai mà không hề báo lỗi.

Đổi chính sách thuế thì chỉ sửa bảng, không đụng công thức. Người kiểm tra cũng nhìn bảng là hiểu, thay vì phải đọc công thức dài ba dòng.

Việc 4 — Làm sạch dữ liệu xuất từ phần mềm kế toán

File xuất từ MISA, Fast, Bravo hay phần mềm nội bộ gần như luôn có bốn vấn đề, theo đúng thứ tự nên xử lý:

1. Ô gộp ở phần đầu. Bỏ gộp, điền lại giá trị xuống các dòng trống bên dưới.
2. Ngày ở dạng chữ. Canh trái là dấu hiệu. Text to Columns → Date, chọn đúng DMY.
3. Số ở dạng chữ, thường do dấu phân cách nghìn khác chuẩn máy. Nhân với 1 hoặc dùng VALUE, nếu vẫn không được thì thay dấu bằng SUBSTITUTE trước.
4. Dòng tổng nằm lẫn trong dữ liệu. Phải lọc bỏ, không thì mọi tổng đều bị đếm hai lần.

Nếu tháng nào cũng lặp lại đúng bốn bước này, hãy chuyển sang Power Query (có sẵn trong Excel, tab Data → Get Data). Ghi lại các bước một lần, tháng sau chỉ bấm Refresh. Đây là thứ tiết kiệm thời gian nhiều nhất cho kế toán mà ít người dùng nhất.

Bốn hàm nên biết thêm

• ROUND — làm tròn tiền. Kế toán nên làm tròn ở bước ghi nhận, không phải chỉ ở bước hiển thị, để tổng khớp với chứng từ.
• EOMONTH — ngày cuối tháng, dùng cho hạn thanh toán và kỳ báo cáo.
• SUBTOTAL — tính tổng chỉ trên dòng đang hiện sau khi lọc. Dùng SUM sau khi lọc vẫn cộng cả dòng đã ẩn, và đây là lỗi rất hay gặp khi soát bảng.
• SUMPRODUCT — cộng có nhiều điều kiện phức tạp mà SUMIFS chịu, ví dụ điều kiện tính trên kết quả của một phép nhân hai cột.

Khi Excel hết ngưỡng

Excel làm tốt việc tính. Nó đuối ở ba chỗ: dữ liệu quá vài chục nghìn dòng thì mở file chậm; nhiều người cùng sửa một file thì sinh ra bốn phiên bản; và mỗi kỳ báo cáo lại phải ghép tay từ nhiều nguồn.

Gặp đúng ba dấu hiệu đó thì đọc khi nào nên chuyển từ Excel sang Power BI. Muốn xem trước một báo cáo tài chính dựng sẵn trông ra sao thì có dashboard tài chính Power BI, và cách kết nối MISA AMIS với Power BI nếu đang dùng MISA.