2026-08-18 · Kế toán & Dữ liệu
Cuối tháng, bảng kê chứng từ của bạn đã có gần đủ mọi thứ: ngày chứng từ, số chứng từ, diễn giải, tài khoản Nợ, tài khoản Có, số tiền. Mỗi nghiệp vụ nằm gọn một dòng.
Rồi tới lúc phải có Sổ Nhật ký chung. Và bạn ngồi gõ lại từng dòng, vì mẫu sổ đòi trình bày khác: tài khoản Nợ một dòng, tài khoản Có một dòng riêng, số tiền tách sang hai cột.
Việc này lặp lại mỗi tháng, không thêm một thông tin nào mới, và chỉ cần lỡ tay một dòng là số tổng lệch. Bài này hướng dẫn để Power Query làm nó, và làm lại bằng một nút Refresh cho tháng sau.
Hai bảng này chỉ khác nhau ở đúng một chỗ
Bảng kê chứng từ ghi mỗi định khoản một dòng, tài khoản Nợ và tài khoản Có nằm cạnh nhau:
| Ngày CT | Số CT | Diễn giải | TK Nợ | TK Có | Số tiền |
|---|---|---|---|---|---|
| 02/01/2025 | PT0001 | Thu tiền hàng của khách | 1111 | 131 | 166.529.000 |
Sổ Nhật ký chung thì tách đôi dòng đó ra. Hướng dẫn ghi sổ nói rất rõ: cột "Số hiệu TK đối ứng" ghi số hiệu các tài khoản ghi Nợ và ghi Có theo định khoản, tài khoản ghi Nợ ghi trước, tài khoản ghi Có ghi sau, mỗi tài khoản được ghi một dòng riêng. Số tiền của tài khoản ghi Nợ vào cột 1, số tiền của tài khoản ghi Có vào cột 2.
| Ngày ghi sổ | Số CT | Diễn giải | STT dòng | TK đối ứng | Nợ | Có |
|---|---|---|---|---|---|---|
| 02/01/2025 | PT0001 | Thu tiền hàng của khách | 1 | 1111 | 166.529.000 | |
| 02/01/2025 | PT0001 | Thu tiền hàng của khách | 2 | 131 | 166.529.000 |
Toàn bộ công việc gõ tay mỗi tháng nằm ở đúng phép biến đổi này: một dòng thành hai dòng. Đây là loại việc máy làm chính xác hơn người, vì nó không mỏi mắt ở dòng thứ tám trăm.
Mẫu S03a-DN đòi những cột nào
Trước khi dựng, cần biết đích đến trông ra sao. Sổ Nhật ký chung mẫu S03a-DN ban hành theo Thông tư 200/2014/TT-BTC có các cột sau:
| Cột | Nội dung | Lấy từ đâu trong bảng kê |
|---|---|---|
| A | Ngày, tháng ghi sổ | Thường trùng ngày chứng từ, nhưng là hai khái niệm khác nhau |
| B | Số hiệu chứng từ | Cột Số CT |
| C | Ngày, tháng chứng từ | Cột Ngày CT |
| D | Diễn giải | Cột Diễn giải |
| E | Đã ghi Sổ Cái | Đánh dấu khi đã ghi sang Sổ Cái, để trống lúc lập |
| G | STT dòng | Số thứ tự dòng trong nhật ký, đánh sau khi đã tách dòng |
| H | Số hiệu TK đối ứng | Cột TK Nợ và TK Có, tách thành hai dòng |
| 1 | Số phát sinh Nợ | Số tiền, chỉ điền ở dòng tài khoản ghi Nợ |
| 2 | Số phát sinh Có | Số tiền, chỉ điền ở dòng tài khoản ghi Có |
Hai cột dễ bị bỏ quên là E và A. Cột E để trống lúc lập là đúng, nó dành cho lúc đối chiếu với Sổ Cái. Cột A thì đừng mặc định gán bằng ngày chứng từ — hai ngày này có thể lệch nhau, và nếu doanh nghiệp bạn có quy định riêng thì phải theo quy định đó.
Chuẩn bị bảng kê trước khi mở Power Query
Ba điều kiện dưới đây quyết định việc này chạy trơn hay tắc giữa chừng:
- Bảng kê phải là bảng phẳng. Một dòng tiêu đề duy nhất, không ô gộp, không dòng trống chen giữa, không dòng tổng nằm trong vùng dữ liệu. Dòng tổng để riêng hẳn ra ngoài hoặc bỏ đi, vì Power Query sẽ coi nó là một nghiệp vụ.
- Số tài khoản phải là chữ, không phải số. Nghe ngược đời nhưng đúng: tài khoản 1111 mà để dạng số thì Excel sẽ nuốt số 0 ở đầu của những tài khoản như 0011, và tài khoản chi tiết dạng 131.KH005 sẽ báo lỗi kiểu dữ liệu. Đặt cả cột TK Nợ và TK Có là Text ngay từ đầu.
- Cột số tiền phải là số thật. Số bị lưu dạng chữ là nguyên nhân số một khiến tổng cộng ra 0 mà không báo lỗi gì. Nhìn nhanh: số căn trái trong ô là dấu hiệu nó đang là chữ.
Sáu bước dựng bằng Power Query
Làm trong Excel, không cần cài thêm gì nếu bạn dùng Excel 2016 trở lên.
Bước 1 — Đưa bảng kê vào Power Query
Đặt con trỏ vào vùng dữ liệu, vào Data → From Table/Range. Excel hỏi vùng dữ liệu và tích "My table has headers". Cửa sổ Power Query Editor mở ra.
Nếu bảng kê nằm ở file khác, dùng Data → Get Data → From File → From Workbook để trỏ tới file đó. Cách này tốt hơn hẳn cho việc lặp hằng tháng, vì tháng sau bạn chỉ cần ghi đè file nguồn rồi Refresh.
Bước 2 — Dọn và ép kiểu dữ liệu
Giữ lại đúng các cột cần dùng: Ngày CT, Số CT, Diễn giải, TK Nợ, TK Có, Số tiền. Các cột như Mã đối tượng, Tên đối tượng thì giữ nếu bạn muốn tra ngược, nhưng chúng không lên sổ nhật ký chung.
Đặt kiểu cho từng cột: Ngày CT là Date, TK Nợ và TK Có là Text, Số tiền là Decimal Number. Bấm vào biểu tượng kiểu dữ liệu bên trái tên cột để đổi.
Lọc bỏ dòng rỗng: bấm mũi tên cột Số tiền, bỏ tích (null).
Bước 3 — Tách một dòng thành hai bằng Unpivot
Đây là bước lõi. Chọn đồng thời hai cột TK Nợ và TK Có, chuột phải, chọn Unpivot Columns.
Power Query sinh ra hai cột mới: Attribute chứa chữ "TK Nợ" hoặc "TK Có", Value chứa số hiệu tài khoản. Và mỗi dòng cũ giờ thành hai dòng, giữ nguyên ngày, số chứng từ, diễn giải, số tiền. Đúng cái bạn cần. Nếu bạn chưa quen thao tác này, bài Unpivot trong Power Query giải thích kỹ hơn cơ chế của nó.
Đổi tên Value thành TK đối ứng cho khớp với cột H của mẫu sổ.
Bước 4 — Tách số tiền sang hai cột Nợ và Có
Vào Add Column → Conditional Column, đặt tên cột mới là Phát sinh Nợ, với điều kiện: nếu Attribute bằng "TK Nợ" thì lấy giá trị cột Số tiền, ngược lại để null.
Làm thêm một cột Phát sinh Có theo đúng cách đó, đổi điều kiện thành "TK Có".
Xong thì xóa cột Số tiền gốc và giữ lại cột Attribute thêm một bước nữa — sẽ cần ở bước sắp xếp.
Bước 5 — Sắp xếp cho tài khoản Nợ đứng trước
Mẫu sổ yêu cầu tài khoản ghi Nợ đứng trước tài khoản ghi Có trong cùng một chứng từ. Unpivot đã trả về đúng thứ tự đó vì cột TK Nợ nằm trước trong bảng gốc, nhưng đừng phụ thuộc vào may mắn.
Thêm một cột thứ tự để khóa chặt: Add Column → Conditional Column, đặt tên Thứ tự, quy tắc là nếu Attribute bằng "TK Nợ" thì 1, ngược lại 2. Sau đó sắp xếp bảng theo Ngày CT tăng dần, rồi Số CT tăng dần, rồi Thứ tự tăng dần.
Xong rồi thì xóa cả cột Attribute lẫn cột Thứ tự, vì chúng chỉ là công cụ trung gian.
Bước 6 — Đánh số thứ tự dòng và đổ ra sổ
Vào Add Column → Index Column → From 1, rồi đổi tên cột đó thành STT dòng — chính là cột G của mẫu sổ. Thêm cột Đã ghi Sổ Cái để trống nếu bạn muốn bản in đủ cột.
Cuối cùng bấm Home → Close & Load To, chọn Table và trỏ vào sheet Nhật ký chung. Tháng sau, thay dữ liệu nguồn rồi bấm Data → Refresh All là ra sổ mới.
Cái bẫy ở bút toán nhiều Nợ một Có
Đây là chỗ mà bản làm nhanh hay sai, và sai theo kiểu số tổng vẫn khớp nên rất khó phát hiện.
Lấy một nghiệp vụ quen thuộc: mua hàng có thuế GTGT đầu vào, thanh toán bằng tiền mặt. Trên bảng kê, nó nằm ở hai dòng vì có hai tài khoản ghi Nợ:
| Số CT | Diễn giải | TK Nợ | TK Có | Số tiền |
|---|---|---|---|---|
| PC0001 | Thanh toán cước viễn thông | 6427 | 1111 | 17.071.000 |
| PC0001 | Thanh toán cước viễn thông | 1331 | 1111 | 1.707.000 |
Chạy y nguyên sáu bước trên, bạn sẽ ra bốn dòng: Nợ 6427, Có 1111, Nợ 1331, Có 1111. Tổng tiền vẫn đúng, cân đối vẫn đúng. Nhưng tài khoản 1111 xuất hiện hai lần cho cùng một phiếu chi, trong khi bút toán thật chỉ có một vế Có.
Cách xử lý, làm ngay trong Power Query sau bước 4:
- Vào Home → Group By, chọn Advanced.
- Nhóm theo Ngày CT, Số CT, Diễn giải, TK đối ứng.
- Thêm hai phép tính tổng: Sum của Phát sinh Nợ, Sum của Phát sinh Có.
Sau bước này, hai dòng Có 1111 gộp thành một dòng 18.778.000, còn hai dòng Nợ giữ nguyên vì khác tài khoản. Rồi mới sắp xếp và đánh số thứ tự dòng.
Lưu ý một trường hợp ngoại lệ: nếu doanh nghiệp bạn cố ý tách nhiều dòng cùng tài khoản trong một chứng từ để theo dõi chi tiết theo đối tượng, thì thêm cột đối tượng vào nhóm ở bước 2, đừng gộp mất chi tiết đó.
Ba phép kiểm trước khi in sổ
Đừng tin kết quả chỉ vì nó chạy không báo lỗi. Ba phép kiểm này làm mất chưa tới hai phút:
- Tổng Nợ bằng tổng Có. Đặt hai công thức SUM ở cuối hai cột phát sinh. Lệch là có dòng bị mất hoặc bị nhân đôi ở đâu đó.
- Tổng phát sinh Nợ bằng tổng cột Số tiền của bảng kê gốc. Phép kiểm này bắt được đúng lỗi mà phép kiểm thứ nhất bỏ sót: nếu một dòng bị nhân đôi cả Nợ lẫn Có thì cân đối vẫn khớp, nhưng tổng sẽ vượt bảng kê.
- Số dòng phải đúng. Trước khi gộp, số dòng nhật ký phải bằng đúng hai lần số dòng bảng kê. Sau khi gộp thì ít hơn, và phần chênh phải giải thích được bằng số chứng từ nhiều dòng.
Nếu bạn đang làm nhiều loại đối chiếu tương tự, cách đặt phép kiểm này dùng lại được nguyên vẹn — cùng logic với bài đối chiếu công nợ tự động bằng Power Query.
Câu hỏi thường gặp
Doanh nghiệp nhỏ theo Thông tư 133 có làm được cách này không?
Được, cách làm giống hệt. Thông tư 133/2016/TT-BTC cũng có mẫu Sổ Nhật ký chung với cấu trúc cột tương tự, chỉ khác ký hiệu mẫu. Bạn chỉ cần đổi tiêu đề cột ở bước cuối cho khớp mẫu mình đang dùng.
Bảng kê của tôi xuất từ phần mềm kế toán, mỗi tháng tên cột lại hơi khác. Có sao không?
Có, và đây là nguyên nhân hỏng phổ biến nhất khi Refresh tháng sau. Power Query ghi nhớ tên cột ở từng bước, tên đổi là báo lỗi cột không tồn tại. Cách chắc ăn là đổi tên cột ngay ở bước đầu tiên về một bộ tên cố định do bạn đặt, rồi mọi bước sau chỉ dùng bộ tên đó.
Sổ nhật ký chung có bắt buộc cộng lũy kế cuối mỗi trang không?
Hướng dẫn ghi sổ có nêu việc cuối trang cộng số phát sinh lũy kế để chuyển sang trang sau, và đầu trang sau ghi số cộng trang trước chuyển sang. Đây là phần thuộc khâu in ấn và trình bày, không thuộc khâu Power Query. Bạn xử lý nó ở phần thiết lập in trong Excel hoặc ở phần mềm in sổ.
Làm cách này rồi có thay được phần mềm kế toán không?
Không, và cũng không nên đặt mục tiêu đó. Cách này giải quyết đúng một việc: chuyển dữ liệu bạn đã có sang đúng hình thức trình bày mà mẫu sổ yêu cầu, lặp lại được hằng tháng. Phần hạch toán, kiểm soát chứng từ và lưu trữ vẫn thuộc về phần mềm kế toán.
Chốt lại
Phép biến đổi ở đây gọn hơn vẻ ngoài của nó: Unpivot hai cột tài khoản, tách số tiền sang hai cột, gộp lại theo chứng từ, đánh số dòng. Bốn ý đó là toàn bộ phần kỹ thuật. Phần còn lại là biết mẫu sổ đòi gì và kiểm cho kỹ trước khi in.
Đội ngũ EzTech gặp bài toán này ở gần như mọi doanh nghiệp có bộ phận kế toán, và nó thường là bước đầu tiên khiến mọi người tin rằng Power Query dùng được cho việc thật. Nếu bạn muốn học bài bản để tự làm những việc dạng này, khóa DATA FINANCE đi sâu vào Power Query trong Excel cho dân kế toán - tài chính. Cần đội ngũ dựng sẵn quy trình cho phòng kế toán của mình, xem dịch vụ giải pháp dữ liệu hoặc liên hệ.
