Excel cho kế toán: 15 hàm dùng hằng ngày và tình huống áp dụng

Học hàm theo danh sách thì nhanh quên; học hàm gắn với một việc cụ thể thì nhớ lâu. Bài Excel cho kế toán này chọn 15 hàm mà kế toán viên mới gần như dùng mỗi ngày, mỗi hàm đi kèm một tình huống thật: đối chiếu công nợ, tổng hợp hoá đơn, tính tuổi nợ. Các ví dụ được rút từ cách đội ngũ ACCA Consulting Vietnam xử lý bảng kê hằng tháng.

1. Nhóm hàm tổng hợp: cộng đúng điều kiện

Phần lớn câu hỏi của sếp bắt đầu bằng “tổng bao nhiêu”: tổng mua của nhà cung cấp A trong tháng 6, tổng hoá đơn chưa thanh toán, tổng chi phí của bộ phận bán hàng. Ba hàm sau giải quyết gần hết.

• SUMIFS: cộng theo nhiều điều kiện. Ví dụ tổng tiền hàng của nhà cung cấp theo MST trong một khoảng ngày: =SUMIFS(TienHang; MST; "0301234567"; NgayHD; ">="&F1; NgayHD; "<="&F2).
• COUNTIFS: đếm số hoá đơn theo điều kiện, dùng để đối chiếu số lượng hoá đơn trên bảng kê với số hoá đơn đã hạch toán.
• SUBTOTAL: cộng phần đang hiển thị sau khi lọc. Dùng SUBTOTAL(9; …) thay SUM khi bạn hay lọc bảng, để không cộng nhầm dòng ẩn.

2. Nhóm tra cứu: ghép hai bảng dữ liệu

Đối chiếu là việc hằng ngày của kế toán: hoá đơn trên cổng thuế với sổ mua hàng, sao kê ngân hàng với sổ tiền gửi, công nợ của mình với biên bản của khách.

• XLOOKUP: tìm số hoá đơn trong sổ và trả về số tiền đã hạch toán. Có tham số khi không tìm thấy, ví dụ trả về “Chưa ghi sổ”, rất tiện để lọc hoá đơn sót.
• INDEX/MATCH: cặp hàm thay thế khi file phải mở được trên bản Excel cũ không có XLOOKUP. Linh hoạt khi cột cần lấy nằm bên trái cột tìm kiếm.
• VLOOKUP: vẫn gặp nhiều trong file cũ; luôn để tham số cuối là FALSE (khớp chính xác) để tránh lấy nhầm số.
• IFERROR: bọc ngoài hàm tra cứu để bảng gọn, nhưng chỉ dùng khi bạn đã hiểu vì sao có lỗi, không dùng để che lỗi.

3. Nhóm ngày tháng: kỳ kế toán và tuổi nợ

• EOMONTH: trả ngày cuối tháng. =EOMONTH(NgayHD;0) đưa mọi hoá đơn về kỳ tháng để tổng hợp; =EOMONTH(NgayHD;1) cho hạn thanh toán “cuối tháng sau”.
• EDATE: cộng số tháng, dùng tính ngày hết hạn hợp đồng hoặc lịch phân bổ chi phí trả trước.
• TODAY: ngày hiện tại, kết hợp với ngày hoá đơn để tính số ngày quá hạn.
• DATEDIF hoặc phép trừ ngày đơn giản: tính số ngày công nợ, làm đầu vào cho bảng tuổi nợ.

Ví dụ tuổi nợ: cột Số ngày = TODAY() − Hạn thanh toán; cột Nhóm tuổi dùng IFS để phân loại “Chưa đến hạn”, “1–30 ngày”, “31–90 ngày”, “Trên 90 ngày”. Sau đó SUMIFS theo nhóm tuổi là có ngay bảng báo cáo cho sếp.

4. Nhóm văn bản và làm sạch dữ liệu

Dữ liệu tải từ phần mềm hoặc cổng hoá đơn thường “bẩn”: thừa khoảng trắng, MST dạng số bị mất số 0 đầu, ngày bị lưu thành chữ.

• TRIM: bỏ khoảng trắng thừa trong tên khách hàng, tránh tra cứu thất bại vì một dấu cách.
• TEXT: định dạng số và ngày thành chuỗi, ví dụ =TEXT(MST;"0000000000") để giữ đủ 10 chữ số, hoặc =TEXT(NgayHD;"mm/yyyy") để tạo khoá kỳ.
• ROUND: làm tròn tiền thuế tới đồng ngay trong công thức để tổng chi tiết khớp với tổng trên tờ khai.

5. Bảng tra nhanh: hàm Excel cho kế toán theo tình huống

Khi đã thạo 15 hàm, bước tiếp theo là Pivot Table cho kế toán và cách tránh các lỗi Excel kinh điển làm sai số liệu.

Câu hỏi thường gặp