Tổng hợp số CT phát sinh vào sổ NKC

[handung107]

Bach Ve
(www.webketoan.com)​

Hàm SUMIF​

Tất cả các nghiệp vụ kinh tế đều được tập hợp ở sheet chứng từ (tạm thời ký hiệu sheet này là CT). Dữ liệu để tổng hợp số phát sinh được lấy từ sheet này.
Hàm SUMIF có cú pháp như sau:
SUMIF(khối điều kiện, giá trị so sánh, khối tính tổng)

SheetCT có kết cấu như sau: Ngày; Số CT; Diễn giải; TKNợ; TKCó; Số tiền.

Sheet tổng hợp số phát sinh: SHTK, Tên TK, PS Nợ, PS Có

Công thức PSN ở ô C4= Sumif(CT!D,A4,CT!F:F)
PSC ở ô D4= Sumif(CT!E:E,A4,CT!F:F)

Hai công thức trên chỉ khác nhau ở khối điều kiện là TK Nợ và TK Có. Còn giá trị so sánh là số hiệu của TK đang tổng hợp. Khối tính tổng là khối số tiền.
Sau khi lập xong công thức, chỉ cần sao chép xuống cho những TK tiếp theo.
Ngoài ra cũng có thể tập hợp số phát sinh từ sổ NKC bằng hàm SUMIF tương tự như trên.

 

[handung107]

Tự động cập nhật dữ liệu

VD: Sổ Nhật ký chung.

Hàm OFFSET.​

Sổ NKC có kết cấu gồm: Ngày, Số, Diễn giải, TK, Nợ, Có.
Chúng ta sẽ lập sổ này, lấy dữ liệu từ sheetCT. Có một điểm lưu ý là ở SheetCT mỗi CT được lập trên 1 dòng, còn ở SheetNKC mỗi chứng từ lại được lập thành 2 dòng.

VD: SheetCT:

SheetNKC:

 

[handung107]

Thực hiện

Các bước cần thực hiện như sau:

1. Xác định dòng của CT đầu tiên trong sheetCT để lập cột phụ. (Cột phụ được sử dụng cho hàm Offset)

2. Xác định dòng của CT cuối cùng trong sheetCT để tính xem có bao nhiêu CT trong sheetCT. (Trong VD này giả sử có 10 chứng từ). Việc này để xác định vị trí phải đặt dòng tổng cộng ở dòng nào.

3. Lập công thức tính vị trí của dòng tổng cộng: (10*2)+3=23
số 10 nghĩa là 10 chứng từ.
số 2 để làm cho mỗi chứng từ được thể hiện trên 2 dòng.
số 3 chính là dòng CT đầu tiên trong sheetNKC.

Trong thực tế có hàng trăm CT chứ không phải 10 do đó dùng hàm dò tìm MATCH để tính xem có bao nhiêu CT.
(*Không được tự nhập hằng số thay cho hàm Match, bởi vì khi thêm CT mới vào sheetCT thì ở sheetNKC dữ liệu sẽ tự động cập nhật sai)
Thủ thuật là điền một số đặc biệt vào dòng cuối cùng trong sheetCT sau mẩu tin cuối cùng, và ở cột A, ở đây xin chọn là 999.
Công thức tìm 999 là:
MATCH(999,CT!A:A,0)=12
0 là mã dò tìm chính xác.
=> Tổng số CT= (12-1)-1=10
Số 1 cuối cùng là vị trí dòng của dòng tiêu đề: Ngày, Số, Diễn giải…. trong sheetCT, có thể khi lập sheetCT, các bạn không đặt nó ngay dòng 1.

Tổng quát vị trí dòng tổng cộng:
=((Match(999,CT!A:A,0)-1-row(CT!dòng tiêu đề))*2)+row(NKC!dòng CT đầu tiên)

4. Tạo cột phụ như trên, để một CT được ghi trên 2 dòng.
Cách lập cột phụ: Trong VD trên, CT đầu tiên trong sheetCT là ở dòng 2.
Do đó trước tiên cần phải điền số 2 vào A3 à A4 như trong VD ở sheetNKC.
Công thức ô A5=If(A3=A4,A4+1,A4)


5. Lập CT cho các cột: Ngày, Số, Diễn giải:
Dùng hàm OFFSET để lấy dữ liệu bên sheetCT

Ngày ở ô B3=Offset(CT!$A$1,A3-1,0).

Do lấy ô A1 làm mốc nên khoảng cách từ ô A1 đến ô Ngày của CT đầu tiên trong sheetCT là (A3-1) dòng và 0 cột. (A3 chính là ô ở cột phụ).
Các cột Số, Diễn giải cũng tương tự, chỉ khác ở chỗ khoảng cách đến ô A1 là 1 và 2 cột.
Số ở ô C3=Offset(CT!$A$1,A3-1,1).
Diễn giải ở ô D3=Offset(CT!$A$1,A3-1,2).

6. Lập công thức cho các cột TK, Nợ, Có cũng dựa vào cột phụ và dùng hàm Offset, vấn đề là lấy TK bên nợ hay bên có, và số tiền được ghi ở cột nợ hay cột có. Chỉ cần sử dụng thêm hàm IF là xử lý được.

7. Trình tự thực hiện lập sổ NKC:
Điền các dòng tiêu đề.
Lập công thức tìm vị trí dòng tổng cộng và di chuyển dòng tổng cộng đến ví trí đó.
Lập các công thức cho cột phụ và các cột khác.
Sao chép các công thức đến dòng liền kề với dòng tổng cộng khi nãy.
Mỗi khi thêm CT mới vào sheetCT thì ở sheetNKC sẽ tự động thay đổi vị trí dòng tổng cộng, ta chỉ cần kéo dòng tổng cộng đến vị trí mới và sao chép các công thức.

*Sau khi hoàn thành cần phải dấu cột phụ đi bằng chức năng hide column.
*Muốn xem những chứng từ được lập trong khoảng thời gian nào đó thì thêm ô NgayD và NgayC.
Và thêm cột phụ lập công thức so sánh 2 ô trên với ô Ngày, Sau đó dùng Auto Filter để lọc, kết hợp với hàm SUBTOTAL.
Cột phụ này nên nằm cách bảng NKC 1 cột trống thì dùng Auto Filter mới được.
*Nên đùng Auto Filter hơn là Advanced Filter.
Xong! một sổ nhật ký chung hoàn chỉnh, tuyệt đẹp

 

[Đào Việt Cường]

Dear handung107,
-----------------
Em xin không có ý kiến gì về các kỹ thuật tổng hợp dữ liệu trong chủ đề này, chỉ xin có một vài ý kiến nhỏ trong việc tổ chức cơ sở dữ liệu theo hình thức kế toán Nhật ký chung:
Theo Quy định hiện hành về tổ chức công tác kế toán trên máy thì việc áp dụng các mẫu sổ kế toán cần phù hợp với kế toán trên máy và các quy định về việc ghi sổ kế toán; không bắt buộc phải tuân thủ theo mẫu sổ kế toán ghi bằng tay. Chúng ta hoàn toàn có thể thiết kế mẫu sổ Nhật ký chung phù hợp với điều kiện hạch toán bằng máy vi tính. Theo em, kết cấu của sổ Nhật ký chung trong điều kiện sử dụng MS Excel được thiết kế như sau:
- Số thứ tự: Số thứ tự dòng cho nghiệp vụ kinh tế phát sinh (đôi khi chúng ta gọi các định khoản chỉ bằng con số này)
- Số chứng từ: Số chứng từ gốc hoặc chứng từ kế toán dùng để ghi sổ
- Ngày chứng từ: rõ quá rồi!
- Ngày ghi sổ: không nên bỏ cột này vì chúng ta có thể theo dõi các phát sinh theo ngày ghi sổ
- Nội dung: Nội dung tóm tắt nghiệp vụ kinh tế phát sinh
- Tài khoản Nợ: Số hiệu tài khoản được ghi vào bên Nợ
- Tài khoản Có: Số hiệu tài khoản được ghi vào bên Có
- Số tiền: Số tiền tương ứng cho một định khoản
[-Ghi chú: nếu cần thiết]
Việc ghi sổ như vậy thể hiện được bản chất của định khoản kế toán: Ghi đồng thời vào bên Nợ hoặc bên Có các đối tượng kế toán liên quan với cùng một số tiền.
Cách tổ chức sổ Nhật ký chung như vậy không làm thay đổi phương pháp ghi chép và hạch toán các nghiệp vụ kinh tế phát sinh. Dữ liệu cũng gọn hơn vì mỗi một định khoản được phản ánh chỉ trên một dòng mà vẫn đảm bảo đầy đủ các thông tin. Quan trọng hơn cả là phù hợp cho kế toán trích lọc các sổ và tổng hợp cân đối số phát sinh.
Em cũng hiểu việc tổ chức hệ thống sổ sách như thế nào tuỳ thuộc vào sở thích và sở trường của mỗi người. Cũng chính vì vậy việc tìm ra cách tổ chức cơ sở dữ liệu nào là đơn giản tối ưu vẫn là điều mà mọi người mong muốn khi tham gia vào www.giaiphapexcel.com này!