Các Hàm Excel Trong Kế Toán Công Nợ: Hướng Dẫn Chi Tiết

Trong công tác kế toán, đặc biệt là kế toán công nợ, việc sử dụng Excel một cách hiệu quả giúp kế toán viên tiết kiệm thời gian, giảm thiểu sai sót và nâng cao độ chính xác trong quản lý tài chính. Các hàm Excel không chỉ hỗ trợ tính toán nhanh chóng mà còn giúp doanh nghiệp theo dõi công nợ phải thu, phải trả một cách chi tiết và khoa học.

Vậy các hàm Excel trong kế toán công nợ nào thường được sử dụng? Làm sao để áp dụng chúng vào công việc thực tế? Bài viết này Kế toán Lê Ánh sẽ cung cấp hướng dẫn chi tiết về các hàm quan trọng nhất, cùng với ví dụ minh họa cụ thể, giúp bạn tối ưu hóa quy trình làm việc. Hãy cùng khám phá ngay!

1. Tổng hợp các hàm Excel quan trọng trong kế toán công nợ

Trong kế toán công nợ, việc sử dụng các hàm Excel giúp kế toán viên theo dõi, quản lý và phân tích công nợ một cách hiệu quả. Dưới đây là các nhóm hàm quan trọng và ứng dụng cụ thể trong thực tế.

a. Nhóm hàm tính tổng và lọc dữ liệu

Hàm SUM – Tính tổng các giá trị trong một phạm vi

Hàm SUM được sử dụng để tính tổng các giá trị trong một dãy số hoặc một bảng dữ liệu.

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

Ví dụ: Tính tổng công nợ phải thu từ khách hàng: =SUM(B2:B10) (trong đó B2:B10 là danh sách các khoản công nợ).

Hàm SUMIF – Tính tổng có điều kiện

Tính tổng các giá trị trong một phạm vi dựa trên một điều kiện cụ thể.

Cú pháp: =SUMIF(range, criteria, [sum_range])

Ví dụ: Tính tổng công nợ của khách hàng có mã số "KH001": =SUMIF(A2:A100, "KH001", B2:B100)

(A2:A100 là danh sách mã khách hàng, B2:B100 là công nợ tương ứng).

>>> Xem thêm: Cấu trúc hàm sumif trong excel

Hàm SUMIFS – Tính tổng nhiều điều kiện

Tương tự SUMIF, nhưng cho phép thiết lập nhiều điều kiện.

Cú pháp: =SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

Ví dụ: Tính tổng công nợ của khách hàng tại khu vực "Hà Nội" và có hóa đơn trong tháng 01/2024: =SUMIFS(B2:B100, C2:C100, "Hà Nội", D2:D100, ">01/01/2024")

(B2:B100: công nợ, C2:C100: khu vực, D2:D100: ngày hóa đơn).

b. Nhóm hàm tìm kiếm và tra cứu dữ liệu

Hàm VLOOKUP – Tra cứu thông tin công nợ theo mã khách hàng

Tìm kiếm giá trị trong một bảng theo cột dọc.

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

Ví dụ: Tra cứu công nợ của khách hàng có mã số "KH002": =VLOOKUP("KH002", A2:C100, 2, FALSE)

(A2:C100 là bảng dữ liệu, 2 là số thứ tự cột chứa công nợ).

>>> Xem thêm: Hàm Vlookup trong Excel

Hàm HLOOKUP – Tra cứu theo hàng ngang

Tương tự VLOOKUP nhưng tìm kiếm theo hàng ngang.

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

Ví dụ: Tìm công nợ của khách hàng trong tháng 02/2024 trong bảng dữ liệu theo hàng ngang.

Hàm INDEX + MATCH – Tra cứu dữ liệu linh hoạt hơn VLOOKUP

INDEX kết hợp với MATCH giúp tìm kiếm dữ liệu chính xác hơn so với VLOOKUP.

Cú pháp: =INDEX(return_range, MATCH(lookup_value, lookup_range, 0))

Ví dụ: Tra cứu công nợ của khách hàng dựa trên mã số khách hàng: =INDEX(B2:B100, MATCH("KH003", A2:A100, 0))

c. Nhóm hàm logic và kiểm tra dữ liệu

Hàm IF – Kiểm tra công nợ đến hạn

Kiểm tra điều kiện và trả về kết quả phù hợp.

Cú pháp: =IF(logical_test, value_if_true, value_if_false)

Ví dụ: Xác định khách hàng có công nợ quá hạn 30 ngày không: 

=IF(TODAY()-D2>30, "Quá hạn", "Chưa quá hạn")

(D2 là cột ngày đến hạn thanh toán).

>>> Xem thêm: Hàm If trong excel

Hàm IFERROR – Xử lý lỗi khi tìm kiếm dữ liệu

Tránh lỗi hiển thị khi tra cứu dữ liệu.

Cú pháp: =IFERROR(value, value_if_error)

Ví dụ: Tránh lỗi khi tìm kiếm công nợ của khách hàng chưa có dữ liệu:

=IFERROR(VLOOKUP(A2, B2:C100, 2, FALSE), "Không tìm thấy")

Hàm COUNTIF – Đếm số hóa đơn nợ quá hạnĐếm số lần xuất hiện của một giá trị thỏa 

mãn điều kiện.

Cú pháp: =COUNTIF(range, criteria)

Ví dụ: Đếm số khách hàng có công nợ quá hạn 30 ngày:

=COUNTIF(D2:D100, ">30")

d. Nhóm hàm phân tích dữ liệu công nợ

Hàm DAYS – Tính số ngày quá hạn

Xác định số ngày giữa hai mốc thời gian.

Cú pháp: =DAYS(end_date, start_date)

Ví dụ: Xác định số ngày quá hạn của một khoản nợ: =DAYS(TODAY(), D2)

(D2 là cột ngày đến hạn thanh toán).

Hàm TODAY – Tính số ngày công nợ tự động

Trả về ngày hiện tại, giúp tính toán công nợ động mà không cần nhập tay.

Cú pháp: =TODAY()

Ví dụ: Tính số ngày nợ dựa trên ngày đến hạn: =TODAY()-D2

Hàm DATEDIF – Xác định tuổi nợ

Tính toán số ngày, tháng, năm giữa hai ngày cụ thể.

Cú pháp: =DATEDIF(start_date, end_date, unit)

unit = "D" – Tính số ngày

unit = "M" – Tính số tháng

unit = "Y" – Tính số năm

Ví dụ: Xác định số tháng quá hạn của công nợ: =DATEDIF(D2, TODAY(), "M")

>>> Xem thêm: Công việc của kế toán công nợ  

2. Ứng dụng các hàm Excel trong quản lý công nợ

a. Lập bảng tổng hợp công nợ phải thu khách hàng

Theo dõi tình hình công nợ của từng khách hàng, bao gồm số dư đầu kỳ, phát sinh trong kỳ và số dư cuối kỳ. Giúp kế toán xác định khách hàng nào còn nợ, nợ bao nhiêu và có quá hạn hay không.

Cách thực hiện: 

- Sử dụng hàm VLOOKUP để tra cứu thông tin khách hàng

Tìm kiếm thông tin công nợ của một khách hàng dựa trên mã số khách hàng.

Cú pháp: =VLOOKUP(A2, B2:E100, 2, FALSE)

(A2: mã khách hàng cần tra cứu, B2:E100: bảng dữ liệu công nợ, 2: cột chứa số dư).

- Áp dụng hàm SUMIFS để tính tổng phát sinh nợ và có trong kỳ

Tính tổng số tiền khách hàng đã mua hàng trong kỳ.

Cú pháp: =SUMIFS(D2:D100, A2:A100, "KH001")

(A2:A100: danh sách mã khách hàng, D2:D100: cột giá trị công nợ).

- Sử dụng hàm IF để xác định số dư cuối kỳ

Xác định số dư cuối kỳ dựa trên công thức: =IF(B2+C2-D2>0, B2+C2-D2, 0)

(B2: số dư đầu kỳ, C2: phát sinh nợ, D2: thanh toán trong kỳ).

b. Theo dõi công nợ phải trả nhà cung cấp

Quản lý các khoản phải trả để đảm bảo thanh toán đúng hạn. Tránh bị phạt do thanh toán chậm, duy trì mối quan hệ tốt với nhà cung cấp.

Cách thực hiện:

- Sử dụng hàm SUMIF để tính tổng số tiền phải trả cho từng nhà cung cấp

Công thức: =SUMIF(A2:A100, "NCC01", B2:B100)

(A2:A100: danh sách mã nhà cung cấp, B2:B100: cột công nợ).

- Áp dụng hàm IF để kiểm tra các khoản nợ đến hạn thanh toán

Xác định khoản nợ đã quá hạn hay chưa

=IF(TODAY()-C2>30, "Quá hạn", "Chưa đến hạn")

(C2: cột ngày đến hạn thanh toán).

c. Phân tích tuổi nợ (Aging Analysis)

Đánh giá thời gian tồn đọng của các khoản nợ, giúp doanh nghiệp quản lý rủi ro tín dụng; Phân loại công nợ theo 0-30 ngày, 31-60 ngày, 61-90 ngày và trên 90 ngày.

Cách thực hiện:

- Sử dụng hàm IF và TODAY để tính số ngày quá hạn

Công thức xác định số ngày nợ quá hạn: =IF(TODAY()-B2>0, TODAY()-B2, 0)

(B2: ngày đến hạn thanh toán).

- Áp dụng hàm COUNTIFS để đếm số lượng khoản nợ theo nhóm thời gian

Đếm số khoản nợ quá hạn 0-30 ngày: =COUNTIFS(C2:C100, ">0", C2:C100, "<=30")

Đếm số khoản nợ quá hạn 31-60 ngày: =COUNTIFS(C2:C100, ">30", C2:C100, "<=60")

Đếm số khoản nợ quá hạn trên 90 ngày: =COUNTIFS(C2:C100, ">90")

(C2:C100: cột chứa số ngày quá hạn).

d. Lập báo cáo công nợ tự động trên Excel

Tổng hợp dữ liệu công nợ theo nhiều tiêu chí khác nhau; Trực quan hóa dữ liệu công nợ bằng biểu đồ để dễ dàng theo dõi.

Cách thực hiện:

- Sử dụng Pivot Table để tổng hợp dữ liệu công nợ

Bước 1: Chọn dữ liệu công nợ (bao gồm mã khách hàng, ngày hóa đơn, số tiền, tình trạng thanh toán).

Bước 2: Vào Insert → Pivot Table, chọn phạm vi dữ liệu, nhấn OK.

Bước 3: Kéo các trường dữ liệu vào vùng Rows (mã khách hàng), Values (số tiền), Columns (tình trạng thanh toán: đã thanh toán/chưa thanh toán).

Bước 4: Tùy chỉnh bảng để hiển thị tổng công nợ của từng khách hàng, từng khoảng thời gian.

- Ứng dụng biểu đồ để trực quan hóa báo cáo công nợ

Bước 1: Chọn bảng Pivot Table vừa tạo.

Bước 2: Vào Insert → Recommended Charts, chọn Column Chart hoặc Pie Chart để hiển thị dữ liệu công nợ.

Bước 3: Điều chỉnh tiêu đề, màu sắc, nhãn dữ liệu để biểu đồ dễ hiểu hơn.

Ví dụ về biểu đồ trực quan hóa công nợ:

Biểu đồ cột: So sánh tổng công nợ của từng khách hàng.

Biểu đồ tròn: Thể hiện tỷ lệ công nợ đã thanh toán và chưa thanh toán.

Biểu đồ đường: Biểu diễn xu hướng công nợ theo thời gian.

>>> Tham khảo: Tổng hợp thủ thuật excel hay nhất cho kế toán

3. Lưu ý quan trọng khi sử dụng Excel trong kế toán công nợ

Sử dụng Excel trong kế toán công nợ mang lại nhiều lợi ích về tốc độ xử lý, tính toán chính xác và khả năng tổng hợp dữ liệu nhanh chóng. Tuy nhiên, để đảm bảo hiệu quả tối đa và tránh sai sót, kế toán viên cần lưu ý các điểm quan trọng sau.

- Kiểm tra dữ liệu đầu vào trước khi áp dụng công thức

Dữ liệu đầu vào không chính xác có thể dẫn đến sai lệch kết quả tính toán, ảnh hưởng đến báo cáo công nợ. Các lỗi nhập liệu phổ biến như sai mã khách hàng, nhập nhầm số tiền hoặc ngày tháng có thể gây ra các vấn đề nghiêm trọng khi lập báo cáo tài chính.

Cách kiểm tra dữ liệu hiệu quả:

Sử dụng tính năng Data Validation (Xác thực dữ liệu). Giúp kiểm soát dữ liệu nhập vào, đảm bảo chỉ nhập giá trị hợp lệ.

Cách thực hiện:

1. Chọn cột cần kiểm tra (VD: Cột ngày đến hạn thanh toán).

2. Vào Data → Data Validation.

3. Chọn loại dữ liệu phù hợp (VD: Date – Chỉ cho phép nhập ngày tháng hợp lệ).

Sử dụng Conditional Formatting (Định dạng có điều kiện). Giúp phát hiện nhanh các dữ liệu bất thường như công nợ âm, giá trị lớn bất thường.

Cách thực hiện:

1. Chọn phạm vi dữ liệu.

2. Vào Home → Conditional Formatting → Highlight Cells Rules → Greater Than…

3. Nhập ngưỡng giá trị cần cảnh báo (VD: Công nợ trên 100 triệu).

Áp dụng hàm kiểm tra lỗi (IFERROR, ISNUMBER, ISTEXT, ISBLANK)

IFERROR: Tránh lỗi khi tra cứu dữ liệu bằng VLOOKUP.

=IFERROR(VLOOKUP(A2, B2:D100, 2, FALSE), "Không tìm thấy")

ISNUMBER: Kiểm tra xem ô có chứa số hay không (tránh lỗi nhập chữ vào cột số tiền).

=ISNUMBER(B2)

ISBLANK: Kiểm tra ô có bị bỏ trống hay không (tránh thiếu dữ liệu).

=ISBLANK(C2)

- Sử dụng định dạng số và ngày tháng chuẩn để tránh sai lệch dữ liệu

Nếu dữ liệu ngày tháng không đúng định dạng, các công thức DATEDIF, TODAY, DAYS có thể cho kết quả sai. Nếu số tiền không đúng định dạng, hàm SUM, SUMIF có thể không tính toán chính xác.

Cách thiết lập định dạng đúng:

Định dạng ngày tháng chuẩn (dd/mm/yyyy)

Chọn cột ngày tháng → Format Cells (Ctrl + 1) → Date → Chọn định dạng dd/mm/yyyy.

Tránh nhập ngày tháng theo kiểu chuỗi văn bản, vì Excel sẽ không nhận dạng được.

Định dạng số tiền đúng chuẩn (Currency hoặc Accounting)

Chọn cột số tiền → Format Cells → Number → Currency hoặc Accounting. Nếu làm kế toán bằng Excel trên nhiều quốc gia, cần chọn đúng loại tiền tệ (VND, USD, EUR…).

Kiểm tra lỗi định dạng với ISNUMBER

Công thức kiểm tra xem ô có phải số hợp lệ không: =ISNUMBER(A2)

Nếu trả về FALSE, tức là ô chứa ký tự không hợp lệ.

- Thường xuyên sao lưu dữ liệu để phòng tránh mất mát thông tin quan trọng

Excel không có tính năng tự động sao lưu, nếu file bị lỗi hoặc mất điện đột ngột, dữ liệu có thể bị mất. Nếu file Excel bị lỗi #REF, #NAME, hoặc bị ghi đè nhầm, có thể gây mất dữ liệu quan trọng.

Giải pháp sao lưu hiệu quả: Sử dụng chức năng AutoSave trên OneDrive hoặc Google Drive. AutoSave giúp lưu dữ liệu tự động sau mỗi thao tác nhập liệu.

Cách bật AutoSave trên Excel 365: File → Options → Save → Chọn AutoSave OneDrive.

Sao lưu phiên bản dữ liệu thủ công

Mỗi tuần/tháng, lưu một bản sao file với định dạng "Báo cáo công nợ - tháng 02.xlsx".

Để lưu bản sao: File → Save As → Chọn thư mục an toàn.

Dùng tính năng "Version History" (Lịch sử phiên bản) trên Google Sheets

Nếu quản lý công nợ trên Google Sheets, có thể xem lại và khôi phục các phiên bản trước đó: File → Version history → See version history.

- Kết hợp Excel với phần mềm kế toán để tăng độ chính xác và hiệu quả

Excel mạnh về xử lý dữ liệu linh hoạt, nhưng không bảo mật cao như phần mềm kế toán. Phần mềm kế toán giúp tự động hóa hạch toán, trong khi Excel chỉ giúp tính toán số liệu. Khi dữ liệu công nợ nhiều, Excel có thể chậm và dễ xảy ra lỗi, trong khi phần mềm kế toán có cơ chế xử lý nhanh hơn.

Hầu hết phần mềm kế toán (MISA, FAST, Bravo…) đều cho phép xuất dữ liệu sang Excel để kế toán viên tiếp tục xử lý bằng Pivot Table, SUMIFS.

Cách thực hiện: Trên phần mềm kế toán → Chọn báo cáo công nợ → Xuất ra file Excel.

Việc sử dụng các hàm Excel trong kế toán công nợ không chỉ giúp kế toán viên xử lý dữ liệu nhanh chóng mà còn nâng cao độ chính xác trong quản lý tài chính doanh nghiệp. Để tối ưu hóa công việc, kế toán viên nên thường xuyên thực hành, cập nhật thêm các hàm nâng cao và áp dụng Excel vào thực tế một cách linh hoạt. Ngoài ra, việc kết hợp Excel với phần mềm kế toán cũng sẽ giúp doanh nghiệp quản lý công nợ hiệu quả hơn.

Hy vọng với hướng dẫn chi tiết của Kế toán Lê Ánh trong bài viết, bạn sẽ có thêm kiến thức hữu ích để áp dụng vào công việc kế toán công nợ một cách chuyên nghiệp và hiệu quả. Nếu có bất kỳ thắc mắc nào, hãy để lại bình luận để cùng thảo luận nhé!

>>> Xem thêm: 10 Hàm Excel Thường Dùng Trong Kế Toán Mà Bạn Nên Biết

-------------------------------------------

Kế Toán Lê Ánh - Nơi đào tạo kế toán thực hành uy tín nhất hiện nay, đã tổ chức thành công rất nhiều khóa học nguyên lý kế toán, khóa học kế toán tổng hợp, khóa học kế toán thuế cho người mới bắt đầu, khóa học kế toán thuế chuyên sâu, khóa học phân tích tài chính doanh nghiệp, khóa học chứng chỉ kế toán trưởng, khóa học kế toán cho chủ doanh nghiệp... và hỗ trợ kết nối tuyển dụng cho hàng nghìn học viên.

Nếu như bạn muốn tham gia các khóa học kế toán online/offline của trung tâm Lê Ánh thì có thể liên hệ hotline tư vấn: 0904.84.8855/ Mrs Lê Ánh  

Ngoài các khóa học kế toán thì trung tâm Lê Ánh còn cung cấp các khóa học hành chính nhân sự, khóa học xuất nhập khẩu TPHCM, Hà Nội, online chất lượng tốt nhất hiện nay.

Thực hiện bởi: KẾ TOÁN LÊ ÁNH - TRUNG TÂM ĐÀO TẠO KẾ TOÁN SỐ 1 VIỆT NAM