Hướng Dẫn Làm Kế Toán Trên Excel: Từ Cơ Bản Đến Nâng Cao

Có thể bạn quan tâm: Cách Thu Gọn Trang Excel: Bí Kíp Tối Ưu Hóa Bảng Tính, Tiết Kiệm Không Gian Và Nâng Cao Hiệu Suất Làm Việc

1. Giới thiệu tổng quan về kế toán trên Excel

Excel không chỉ là một công cụ tính toán đơn thuần, mà còn là một nền tảng mạnh mẽ để thực hiện các công việc kế toán. Với khả năng xử lý dữ liệu lớn, hỗ trợ các hàm tài chính, biểu đồ, và công cụ PivotTable, Excel giúp kế toán viên tự động hoá quy trình, giảm thiểu sai sót và tăng hiệu suất làm việc. Bài viết này sẽ hướng dẫn chi tiết cách xây dựng hệ thống kế toán trên Excel, bao gồm:

• Thiết kế sổ sách kế toán cơ bản (sổ nhật ký, sổ cái).
• Lập báo cáo tài chính (bảng cân đối kế toán, báo cáo kết quả hoạt động kinh doanh).
• Sử dụng các hàm tài chính và công cụ phân tích dữ liệu.
• Tự động hoá quy trình bằng macro và VBA.

Mục tiêu cuối cùng là giúp bạn, dù là người mới bắt đầu hay đã có kinh nghiệm, có thể xây dựng một hệ thống kế toán hoàn chỉnh, linh hoạt và dễ bảo trì trên Excel.

Có thể bạn quan tâm: Cách Sửa Lỗi #value! Trong Excel: Hướng Dẫn Chi Tiết Từ A Đến Z

2. Chuẩn bị môi trường làm việc

2.1. Yêu cầu phần mềm

• Microsoft Excel 2026 trở lên (có hỗ trợ Power Query, Power Pivot).
• Office 365 (nếu muốn sử dụng các tính năng đám mây và cập nhật tự động).

2.2. Cài đặt và cấu hình cơ bản

1. Bật tính năng “Developer”:
2. Vào File → Options → Customize Ribbon → tích chọn “Developer”.
3. Kích hoạt “Analysis ToolPak”:
4. File → Options → Add-ins → Manage: Excel Add-ins → Go → Tick “Analysis ToolPak”.
5. Lưu file ở định dạng .xlsx (đối với macro, dùng .xlsm).

2.3. Quy tắc đặt tên và cấu trúc thư mục

• Folder:
• Kế Toán/
  • Data/ (dữ liệu gốc)
  • Templates/ (mẫu báo cáo)
  • Archives/ (sao lưu tháng)
• Tên file: KếToán_YYYYMM.xlsx (ví dụ: KếToán_202406.xlsx).

Có thể bạn quan tâm: Cách Sử Dụng Excel Miễn Phí: Hướng Dẫn Từ Cơ Bản Đến Nâng Cao Cho Người Mới Bắt Đầu

3. Thiết kế sổ sách kế toán trên Excel

3.1. Sổ nhật ký (Journal)

3.1.1. Cấu trúc cột chuẩn

3.1.2. Công thức kiểm tra cân bằng

• Cột J (Xác nhận): =IF(F2=H2,"OK","LỖI") (giả sử F = tiền nợ, H = tiền có).

3.1.3. Định dạng dữ liệu

• Cột ngày: dd/mm/yyyy.
• Cột tiền: định dạng Number với 0 decimal, dấu phẩy ngăn cách hàng nghìn.

3.1.4. Tạo danh sách thả xuống cho tài khoản

• Data → Data Validation → List và trỏ tới danh sách tài khoản (tạo sheet “ChartOfAccounts”).

3.2. Sổ cái (General Ledger)

3.2.1. Tạo sheet cho từng tài khoản

• Sử dụng công thức FILTER (Excel 365) hoặc Advanced Filter để trích xuất các giao dịch liên quan tới mỗi tài khoản.

=FILTER(Journal!$A$2:$J$1000, (Journal!$D$2:$D$1000=AccountCode) + (Journal!$F$2:$F$1000=AccountCode))

3.2.2. Tính số dư đầu kỳ và cuối kỳ

• Số dư đầu kỳ (ô B2): nhập số dư ban đầu.
• Cột D (Số dư cuối kỳ): =B2 + SUMIF(Journal!$D$2:$D$1000,AccountCode,Journal!$E$2:$E$1000) - SUMIF(Journal!$F$2:$F$1000,AccountCode,Journal!$G$2:$G$1000)

3.3. Bảng cân đối kế toán (Balance Sheet)

3.3.1. Định dạng chuẩn

• Tài khoản: liệt kê các tài khoản tài sản, nợ phải trả, vốn chủ sở hữu.

3.3.2. Kết nối với sổ cái

• Sử dụng hàm VLOOKUP hoặc XLOOKUP để lấy số dư cuối kỳ từ sheet “General Ledger”.

=XLOOKUP(A2, Ledger!$A$2:$A$1000, Ledger!$D$2:$D$1000, 0)

3.3.3. Kiểm tra cân đối

• Ô tổng tài sản: =SUM(B2:B10) (giả sử dòng 2‑10 là tài sản).
• Ô tổng nợ và vốn: =SUM(B12:B20) (nợ + vốn).
• Ô kiểm tra: =IF(ABS(B21-B22)<0.01,"Cân Đối","KHÔNG CÂN ĐỐI").

3.4. Báo cáo kết quả hoạt động kinh doanh (Income Statement)

3.4.1. Cấu trúc

3.4.2. Tự động cập nhật

• Khi nhập giao dịch mới vào “Journal”, các báo cáo trên sẽ tự động cập nhật nhờ các hàm SUMIF / XLOOKUP.

4. Sử dụng các hàm tài chính nâng cao

4.1. Hàm NPV (Net Present Value)

=NPV(rate, value1, value2, …) + Initial_Investment

• Ứng dụng: Đánh giá dự án đầu tư, tính giá trị hiện tại ròng.

4.2. Hàm IRR (Internal Rate of Return)

=IRR(values, guess)

• Dòng tiền: nhập vào một cột, bắt đầu bằng khoản đầu tư âm, tiếp theo là các dòng tiền dương.

4.3. Hàm XIRR (IRR cho các ngày không đều)

=XIRR(values, dates, guess)

• Thích hợp cho doanh nghiệp có thu nhập/chi phí không đều theo thời gian.

4.4. Hàm PMT (Thanh toán định kỳ)

=PMT(rate, nper, pv, fv, type)

• Tính toán khoản trả nợ vay ngân hàng, trả góp.

5. Phân tích dữ liệu với PivotTable và Power Query

5.1. Tạo PivotTable từ “Journal”

1. Chọn Insert → PivotTable → Chọn bảng “Journal”.
2. Kéo TK Nợ vào “Rows”, Số tiền Nợ vào “Values”.
3. Thêm Ngày vào “Columns” để xem tổng nợ theo tháng.

5.2. Sử dụng Power Query để hợp nhất dữ liệu

• Khi dữ liệu được lưu trong nhiều file (ví dụ: các tháng khác nhau), dùng Data → Get Data → From Folder để import tự động.
• Áp dụng Transform Data: loại bỏ cột không cần, chuẩn hoá định dạng ngày, và gộp thành một bảng master.

5.3. Đồ thị phân tích

• Column Chart: So sánh doanh thu theo tháng.
• Waterfall Chart: Trình bày biến động lợi nhuận.

6. Tự động hoá bằng Macro và VBA

Có thể bạn quan tâm: Cách Thêm Công Thức Vào Excel: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao

6.1. Ghi macro nhập chứng từ nhanh

1. Developer → Record Macro → Đặt tên “NhapChungTu”.
2. Thực hiện các bước: chọn ô A2, nhập ngày, số chứng từ, …, rồi dừng ghi.
3. Gán macro này vào nút “Thêm chứng từ”.

6.2. Mã VBA mẫu: Kiểm tra cân bằng tự động

Sub KiemTraCanBang() Dim ws As Worksheet Dim lastRow As Long, i As Long Set ws = ThisWorkbook.Sheets("Journal") lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row For i = 2 To lastRow If ws.Cells(i, "E").Value <> ws.Cells(i, "G").Value Then ws.Cells(i, "J").Value = "LỖI" ws.Cells(i, "J").Interior.Color = vbRed Else ws.Cells(i, "J").Value = "OK" ws.Cells(i, "J").Interior.Color = vbGreen End If Next i MsgBox "Kiểm tra hoàn tất!", vbInformation
End Sub

• Nút “Kiểm tra” sẽ chạy macro này, tô màu lỗi ngay lập tức.

6.3. Tự động tạo báo cáo tháng

Sub TaoBaoCaoThang() Dim wb As Workbook, wsJ As Worksheet, wsBC As Worksheet Set wb = ThisWorkbook Set wsJ = wb.Sheets("Journal") Set wsBC = wb.Sheets.Add(After:=wb.Sheets(wb.Sheets.Count)) wsBC.Name = "BC_" & Format(Date, "yyyymm") 'Copy tiêu đề wsJ.Rows(1).Copy Destination:=wsBC.Rows(1) 'Lọc dữ liệu tháng hiện tại wsJ.AutoFilterMode = False wsJ.Range("A1:J1").AutoFilter Field:=1, Criteria1:=Format(Date, "mm/yyyy") wsJ.Range("A2:J" & wsJ.Cells(wsJ.Rows.Count, "A").End(xlUp).Row).SpecialCells(xlCellTypeVisible).Copy _ Destination:=wsBC.Range("A2") wsJ.AutoFilterMode = False MsgBox "Báo cáo tháng đã tạo ở sheet: " & wsBC.Name, vbInformation
End Sub

• Macro này tạo một sheet mới chứa toàn bộ chứng từ của tháng hiện tại, hữu ích cho việc lưu trữ và nộp báo cáo.

7. Kiểm soát nội bộ và bảo mật dữ liệu

7.1. Mật khẩu bảo vệ sheet và workbook

• Review → Protect Sheet: đặt mật khẩu cho các sheet chứa công thức quan trọng.
• File → Info → Protect Workbook: ngăn người dùng thêm, xóa, hoặc đổi tên sheet.

7.2. Sử dụng tính năng Data Validation để ngăn nhập sai

• Giới hạn nhập ngày trong khoảng hợp lệ.
• Đặt Custom Rule: =AND(ISNUMBER(E2),E2>0) để chỉ cho phép số dương trong cột tiền.

7.3. Sao lưu tự động

• File → Options → Save → Tick “Save AutoRecover information every X minutes”.
• Đặt Backup: mỗi tháng lưu một bản sao vào thư mục Archives.

8. Thực hành: Xây dựng một hệ thống kế toán mẫu từ A‑Z

8.1. Bước 1: Tạo “Chart of Accounts” (Danh mục tài khoản)

• Định dạng dưới dạng Table (Ctrl+T) để dễ tham chiếu.

8.2. Bước 2: Thiết lập “Journal”

• Nhập dữ liệu mẫu:

• Áp dụng Data Validation cho cột “TK Nợ” và “TK Có” trỏ tới bảng “Chart of Accounts”.

8.3. Bước 3: Tạo “General Ledger” tự động

• Tạo sheet “Ledger”.
• Dòng tiêu đề: AccountCode, Tên TK, Ngày, Diễn giải, Nợ, Có, Số dư.
• Dùng công thức FILTER để lấy dữ liệu:

=LET( code, A2, data, FILTER(Journal!A$2:J$1000, (Journal!D$2:D$1000=code)+(Journal!F$2:F$1000=code)), IFERROR(data, {"",""} )
)

• Sau khi có dữ liệu, tính cột “Số dư” bằng công thức chạy dọc:

=IF(ROW()=2, $B$2, D2 + IF(C2=code, E2, -F2))

8.4. Bước 4: Xây dựng báo cáo tài chính

• Balance Sheet: Dùng XLOOKUP để lấy số dư cuối kỳ từ “Ledger”.
• Income Statement: SUMIF dựa trên mã tài khoản doanh thu (711) và giá vốn (511).

8.5. Bước 5: Thêm PivotTable phân tích

• PivotTable: Rows = TK Nợ, Columns = Tháng, Values = Số tiền Nợ (Sum).
• Đặt slicer “Tháng” để người dùng có thể chọn xem dữ liệu tháng nào.

8.6. Bước 6: Tự động tạo báo cáo tháng bằng Macro

• Gắn macro “TaoBaoCaoThang” vào nút Create Monthly Report trên sheet “Dashboard”.

9. Lời khuyên tối ưu hoá và tránh lỗi thường gặp

9.1. Sử dụng Table để giảm lỗi

• Khi bạn chuyển dải ô thành Table (Ctrl+T), các công thức sẽ tự động mở rộng khi thêm dòng mới.

9.2. Kiểm tra định dạng ngày

• Đảm bảo mọi ngày đều ở cùng một định dạng (dd/mm/yyyy). Sử dụng Data → Text to Columns nếu cần chuyển đổi.

10. Kết luận và hướng phát triển tiếp theo

Việc triển khai một hệ thống kế toán trên Excel không chỉ giúp doanh nghiệp nhỏ, vừa giảm chi phí phần mềm mà còn tạo ra một môi trường linh hoạt, dễ tùy biến. Bằng cách áp dụng các kỹ thuật từ cơ bản (sổ nhật ký, sổ cái) tới nâng cao (PivotTable, VBA), bạn có thể:

1. Tự động hoá quy trình nhập liệu và giảm thiểu sai sót.
2. Cập nhật báo cáo tài chính trong thời gian thực nhờ công thức động.
3. Phân tích sâu bằng Power Query và PivotTable, đưa ra quyết định nhanh chóng.
4. Bảo mật dữ liệu bằng mật khẩu, validation và sao lưu định kỳ.

10.1. Các bước mở rộng trong tương lai

• Kết nối Excel với ERP/CRM qua ODBC hoặc Power Query để đồng bộ dữ liệu tự động.
• Xây dựng Dashboard tương tác bằng Power BI, nhập dữ liệu từ file Excel hiện tại.
• Áp dụng Machine Learning (Excel + Azure ML) để dự báo doanh thu, chi phí.

Cuối cùng, dù Excel mạnh mẽ, nhưng khi doanh nghiệp phát triển vượt quá khả năng quản lý dữ liệu trên bảng tính, hãy cân nhắc chuyển sang phần mềm kế toán chuyên nghiệp (SAP, Oracle NetSuite, QuickBooks) để duy trì tính ổn định và tuân thủ quy định. Tuy nhiên, kiến thức và kinh nghiệm xây dựng hệ thống trên Excel sẽ luôn là nền tảng vững chắc, giúp bạn hiểu sâu về quy trình kế toán và dữ liệu tài chính.

Chúc bạn thành công trong việc xây dựng và vận hành hệ thống kế toán trên Excel!

Tiến sĩ Lê Thống Nhất

Tiến sĩ Lê Thống Nhất sinh năm 1955 tại Nghệ An, là nhà giáo, nhà báo và doanh nhân trong lĩnh vực giáo dục. Ông từng giảng dạy Toán học tại Trường THPT Chuyên Phan Bội Châu và đảm nhiệm nhiều vai trò trong ngành giáo dục, từ viết sách, biên soạn tài liệu đến sáng lập các dự án hỗ trợ học tập trực tuyến.

Ông là người sáng lập BigSchool – nền tảng giáo dục trực tuyến kết nối giáo viên và học sinh cả nước. Ngoài ra, ông còn nổi tiếng với vai trò tác giả, diễn giả truyền cảm hứng và khách mời trên nhiều chương trình truyền hình, trong đó có “Ai là triệu phú” trên truyền hình VTV.

Trong suốt sự nghiệp, Tiến sĩ Lê Thống Nhất đã nhận nhiều giải thưởng giáo dục và được biết đến như một người luôn đổi mới, ứng dụng công nghệ vào dạy và học, góp phần lan tỏa tri thức và niềm say mê học tập cho thế hệ trẻ.