Appearance
Workshop: Định khoản 20 Nghiệp vụ & Bảng Cân đối Phát sinh
Buổi 2 · Thời lượng: 60 phút · Công cụ: Microsoft Excel / Google Sheets
Mục tiêu Workshop
Sau khi hoàn thành, bạn sẽ:
- ✅ Thiết lập Danh mục Tài khoản (DanhMucTK) trên Excel
- ✅ Nhập 20 bút toán vào sheet Nhật ký chung
- ✅ Tổng hợp Sổ cái bằng công thức SUMIF
- ✅ Lập Bảng Cân đối Phát sinh hoàn chỉnh
- ✅ Kiểm tra tính cân đối và phát hiện sai sót
Chuẩn bị
| Mục | Yêu cầu |
|---|---|
| Phần mềm | Excel 2016+ hoặc Google Sheets |
| File mẫu | Tạo workbook mới, đặt tên: Buoi02_HoTen.xlsx |
| Kiến thức trước | Đã đọc Bài giảng Buổi 2 và Case Study Minh Phát |
| Thời gian | 60 phút (4 bước × 15 phút) |
Bước 1: Thiết lập Sheet DanhMucTK (15 phút)
1.1. Tạo sheet và nhập cấu trúc
Tạo sheet tên DanhMucTK với các cột:
| Cột | Tên cột | Ý nghĩa | Ví dụ |
|---|---|---|---|
| A | MaTK | Mã tài khoản | 111 |
| B | TenTK | Tên tài khoản | Tiền mặt |
| C | Loai | Loại TK (1–9) | 1 |
| D | TinhChat | Dư Nợ / Dư Có | Dư Nợ |
| E | DuDauKy | Số dư đầu kỳ | 0 |
1.2. Nhập danh mục TK
Nhập tối thiểu 19 tài khoản sau (dòng 2–20):
| MaTK | TenTK | Loai | TinhChat | DuDauKy |
|---|---|---|---|---|
| 111 | Tiền mặt | 1 | Dư Nợ | 0 |
| 112 | Tiền gửi ngân hàng | 1 | Dư Nợ | 0 |
| 131 | Phải thu khách hàng | 1 | Dư Nợ | 0 |
| 133 | Thuế GTGT được khấu trừ | 1 | Dư Nợ | 0 |
| 141 | Tạm ứng | 1 | Dư Nợ | 0 |
| 153 | Công cụ, dụng cụ | 1 | Dư Nợ | 0 |
| 156 | Hàng hóa | 1 | Dư Nợ | 0 |
| 211 | TSCĐ hữu hình | 2 | Dư Nợ | 0 |
| 214 | Hao mòn TSCĐ | 2 | Dư Có | 0 |
| 242 | Chi phí trả trước | 2 | Dư Nợ | 0 |
| 331 | Phải trả người bán | 3 | Dư Có | 0 |
| 3331 | Thuế GTGT phải nộp | 3 | Dư Có | 0 |
| 334 | Phải trả người lao động | 3 | Dư Có | 0 |
| 411 | Vốn đầu tư CSH | 4 | Dư Có | 0 |
| 511 | Doanh thu BH & CCDV | 5 | Dư Có | 0 |
| 515 | Doanh thu HĐTC | 5 | Dư Có | 0 |
| 632 | Giá vốn hàng bán | 6 | Dư Nợ | 0 |
| 641 | Chi phí bán hàng | 6 | Dư Nợ | 0 |
| 642 | Chi phí QLDN | 6 | Dư Nợ | 0 |
Mẹo Excel
Chọn toàn bộ cột A → Format as Text (để giữ nguyên mã TK dạng số, tránh Excel tự bỏ số 0 đầu sau này nếu mở rộng TK).
Bước 2: Nhập 20 Nghiệp vụ vào Sheet NhatKyChung (15 phút)
2.1. Tạo sheet và cấu trúc
Tạo sheet tên NhatKyChung với các cột:
| Cột | Tên cột | Kiểu dữ liệu | Ví dụ |
|---|---|---|---|
| A | STT | Số | 1 |
| B | Ngay | Date | 02/01/2026 |
| C | DienGiai | Text | CSH A góp vốn tiền mặt |
| D | TKNo | Text | 111 |
| E | TKCo | Text | 411 |
| F | SoTien | Number | 1200000000 |
2.2. Nhập 20 nghiệp vụ
Nhập toàn bộ 20 NV từ Case Study Minh Phát. Lưu ý: Với bút toán có nhiều TK Nợ hoặc Có, tách thành nhiều dòng.
Ví dụ NV06 (mua hàng chưa trả tiền):
| STT | Ngay | DienGiai | TKNo | TKCo | SoTien |
|---|---|---|---|---|---|
| 6a | 08/01/2026 | Mua HH – giá mua | 156 | 331 | 500000000 |
| 6b | 08/01/2026 | Mua HH – VAT đầu vào | 133 | 331 | 50000000 |
Quy tắc nhập
- Mỗi dòng chỉ chứa 1 cặp TKNo – TKCo
- Bút toán phức hợp → tách nhiều dòng (6a, 6b…)
- Tổng SoTien các dòng Nợ = Tổng SoTien các dòng Có cho cùng 1 NV
Dự kiến: khoảng 30–35 dòng (vì nhiều NV có bút toán phức hợp).
2.3. Kiểm tra nhanh
Ở ô cuối sheet, tạo công thức kiểm tra:
Cell H1: =SUMPRODUCT((D2:D100<>"")*F2:F100) ← Tổng Nợ
Cell H2: =SUMPRODUCT((E2:E100<>"")*F2:F100) ← Tổng Có
Cell H3: =H1-H2 ← Chênh lệch (phải = 0)Hoặc đơn giản hơn:
Cell H1: =SUM(F2:F100) ← Vì mỗi dòng đã là 1 cặp Nợ-Có cùng số tiềnBước 3: Tổng hợp Sổ cái bằng SUMIF (15 phút)
3.1. Tạo sheet SoCai
Cấu trúc:
| Cột | Tên cột | Công thức |
|---|---|---|
| A | MaTK | Lấy từ DanhMucTK!A2:A20 |
| B | TenTK | =VLOOKUP(A2,DanhMucTK!A:B,2,FALSE) |
| C | PSNo | =SUMIF(NhatKyChung!D:D,A2,NhatKyChung!F:F) |
| D | PSCo | =SUMIF(NhatKyChung!E:E,A2,NhatKyChung!F:F) |
3.2. Giải thích công thức
VLOOKUP — Tra cứu tên TK:
excel
=VLOOKUP(A2, DanhMucTK!A:B, 2, FALSE)| Tham số | Ý nghĩa |
|---|---|
A2 | Giá trị tra cứu (MaTK) |
DanhMucTK!A:B | Vùng dữ liệu (cột MaTK + TenTK) |
2 | Trả kết quả từ cột thứ 2 (TenTK) |
FALSE | Tìm chính xác |
SUMIF — Tổng phát sinh Nợ:
excel
=SUMIF(NhatKyChung!D:D, A2, NhatKyChung!F:F)| Tham số | Ý nghĩa |
|---|---|
NhatKyChung!D:D | Vùng điều kiện (cột TKNo) |
A2 | Điều kiện (= MaTK đang xét) |
NhatKyChung!F:F | Vùng tính tổng (cột SoTien) |
3.3. Kiểm tra
Ô C_cuối: =SUM(C2:C20) ← Tổng PS Nợ
Ô D_cuối: =SUM(D2:D20) ← Tổng PS CóNếu không bằng nhau → quay lại Bước 2 kiểm tra bút toán.
Bước 4: Lập Bảng Cân đối Phát sinh (15 phút)
4.1. Tạo sheet BangCDPS
| Cột | Tên cột | Công thức |
|---|---|---|
| A | MaTK | Lấy từ DanhMucTK |
| B | TenTK | =VLOOKUP(A2,DanhMucTK!A:B,2,FALSE) |
| C | DuDKNo | =IF(DanhMucTK!D2="Dư Nợ",DanhMucTK!E2,0) |
| D | DuDKCo | =IF(DanhMucTK!D2="Dư Có",DanhMucTK!E2,0) |
| E | PSNo | =SoCai!C2 (liên kết từ Sổ cái) |
| F | PSCo | =SoCai!D2 |
| G | DuCKNo | Công thức bên dưới |
| H | DuCKCo | Công thức bên dưới |
4.2. Công thức tính Dư cuối kỳ
Quy tắc:
- TK Dư Nợ:
. Nếu > 0 → Dư Nợ; nếu < 0 → Dư Có. - TK Dư Có:
. Nếu > 0 → Dư Có; nếu < 0 → Dư Nợ.
Công thức Excel cho cột G (DuCKNo):
excel
=IF(DanhMucTK!D2="Dư Nợ",
IF(C2+E2-F2>0, C2+E2-F2, 0),
IF(D2+F2-E2<0, ABS(D2+F2-E2), 0)
)Công thức Excel cho cột H (DuCKCo):
excel
=IF(DanhMucTK!D2="Dư Có",
IF(D2+F2-E2>0, D2+F2-E2, 0),
IF(C2+E2-F2<0, ABS(C2+E2-F2), 0)
)4.3. Dòng tổng cộng
excel
Tổng DuDKNo: =SUM(C2:C20)
Tổng DuDKCo: =SUM(D2:D20)
Tổng PSNo: =SUM(E2:E20)
Tổng PSCo: =SUM(F2:F20)
Tổng DuCKNo: =SUM(G2:G20)
Tổng DuCKCo: =SUM(H2:H20)4.4. Kiểm tra cân đối (3 điều kiện)
| Điều kiện | Công thức kiểm tra | Kết quả đúng |
|---|---|---|
| Dư ĐK cân đối | =SUM(C:C)-SUM(D:D) | 0 |
| PS cân đối | =SUM(E:E)-SUM(F:F) | 0 |
| Dư CK cân đối | =SUM(G:G)-SUM(H:H) | 0 |
Bảng Lỗi Thường Gặp
| # | Lỗi | Nguyên nhân | Cách sửa |
|---|---|---|---|
| 1 | SUMIF trả về 0 | MaTK ở DanhMucTK là Number, ở NhatKyChung là Text (hoặc ngược lại) | Đồng nhất format: tất cả Text |
| 2 | VLOOKUP trả #N/A | MaTK có khoảng trắng thừa hoặc sai chính tả | Dùng TRIM() hoặc kiểm tra lại |
| 3 | Tổng PS Nợ ≠ PS Có | Bút toán thiếu dòng (NV phức hợp chưa tách đủ) | Kiểm tra từng NV: tổng Nợ = tổng Có |
| 4 | Dư CK âm ở TK không mong muốn | Nhập sai TKNo / TKCo (ghi ngược) | Kiểm tra lại tính chất tăng/giảm |
| 5 | SoTien sai | Quên tách VAT hoặc nhầm tổng thanh toán ≠ giá mua | Tính lại: giá chưa VAT × (1 + %VAT) |
| 6 | Số dư TK 141 ≠ 0 cuối kỳ | Quên bút toán thanh toán tạm ứng | Kiểm tra NV13 |
| 7 | Google Sheets: hàm lỗi | VLOOKUP range khác | Dùng tên range tuyệt đối hoặc XLOOKUP |
Rubric Chấm điểm
| Tiêu chí | Điểm | Mô tả |
|---|---|---|
| DanhMucTK đầy đủ ≥ 19 TK | 15 | Đúng MaTK, TenTK, TinhChat |
| NhatKyChung nhập đủ 20 NV | 25 | Đúng TK, đúng số tiền, tách bút toán phức hợp |
| SoCai — SUMIF chạy đúng | 20 | Tổng PS Nợ = PS Có |
| BangCDPS — Dư CK cân đối | 25 | 3 điều kiện cân đối đều = 0 |
| Format & trình bày | 10 | Số có dấu phẩy ngăn cách, tiêu đề bold, border |
| Phát hiện & sửa lỗi | 5 (bonus) | Ghi chú lỗi gặp và cách xử lý |
| Tổng | 100 |
Tổng kết cấu trúc Workbook
Buoi02_HoTen.xlsx
├── Sheet 1: DanhMucTK ← Danh mục 19+ tài khoản
├── Sheet 2: NhatKyChung ← 30-35 dòng bút toán (20 NV)
├── Sheet 3: SoCai ← SUMIF tổng hợp PS Nợ/Có mỗi TK
└── Sheet 4: BangCDPS ← Bảng cân đối phát sinh hoàn chỉnhNộp bài
- File:
Buoi02_HoTen.xlsx - Deadline: trước buổi 3
- Nộp qua LMS hoặc email theo hướng dẫn giảng viên
Xem thêm: Buổi 2 — Bài giảng chính · Case Study — Công ty Minh Phát · Game — Bậc Thầy Tài Khoản