Cách làm Bảng Cân đối số phát sinh ?

[Mr Okebab]

Đến bây giờ có rất nhiều bạn bối rối khi lập BCĐSPS, nào là có nhiều tài khoản con, nào là dễ nhầm lẫn đến cuối cùng sai 1 đồng mà không biết từ đâu. Và việc sai này là thường xuyên.

Hôm nay mình thử làm mẫu cho các bạn 1 File về BCĐSPS, vì để công thức cho các bạn dễ theo dõi nên File hơi nặng.
Do đó mình để chế độ Calculation = Manunal
Các bạn cần nhấn Shift+F9 khi muốn tính toán.

Mình giả sử có 3 tháng số liệu (6.117 dòng) và 364 tài khoản lớn nhỏ.
Các tài khoản hạch toán luôn là tài khoản chi tiết

(Trong bài không quan tâm lắm đến hạch toán cuối kỳ nên các tài khoản 5, 6, 7,8,9 có thể vẫn có số dư-Các bạn có thể sửa lại)

File gồm 2sheet

1. Nhật ký : Ghi data phát sinh : Đã được đặt name. Để theo dõi name các bạn nhấn Ctrl+F3
2. Bảng cân đối : Gồm các chỉ tiêu : Đầu năm, Đầu kỳ, Phát sinh, Lũy Kế, Cuối kỳ. Các bạn nhập ngày đầu tháng vào F2; Cuối tháng vào H2. Sau đó nhấn Shift+F9. Kết quả sẽ hiện ra

VD này chủ yếu cho các bạn thấy sức mạnh của hàm Sumproduct trong tổng hợp Kế toán.

Hôm sau mình sẽ tiếp tục với Bảng Cân Đối Kế toán và Báo Cáo KQKD cũng như LCTT

Chúc các bạn thành công !

Thân !

 

[Thien]

A.Hiếu ơi.
1/ Nếu để nghiên cứu học hỏi hàm Sumproduct thì không có gì. Nhưng nếu dùng trong công tác kế toán thì theo quy định đâu có được đặt dấu _ trước TK đâu.

2/ Dấu _ trước tài khoản có tác dụng gì? Nếu không dùng thì có chạy nhanh hơn không?

TC.

 

[ThuNghi]

Xin phép trả lời hộ Bác Hiếu. Đặt dấu _ trước TK nhằm hiểu và phân biệt số TK là số hay test. Có những công thức nếu không xác định rõ định dạng thì rất khó hệ thống.

 

[Mr Okebab]

A.Hiếu ơi.
1/ Nếu để nghiên cứu học hỏi hàm Sumproduct thì không có gì. Nhưng nếu dùng trong công tác kế toán thì theo quy định đâu có được đặt dấu _ trước TK đâu.

2/ Dấu _ trước tài khoản có tác dụng gì? Nếu không dùng thì có chạy nhanh hơn không?

TC.

Dĩ nhiên không có tài khoản _111, nhưng nếu ta in ra TK _111 thì ai cũng hiểu cả. Mình thấy thuế đâu bắt bẻ đâu (chắc là chưa ???)

Thực ra đúng như bác ThuNghi nói, "cực chẳng đã" mới làm vậy vì nếu để 111 thì khi sử dụng các hàm tổng hợp trong Excel nhiều khi sai vì lúc nó cho đó là text, lúc là number (mặc dù có định dạng text trước).

Chọn khoảng trắng thì không được, không biết chọn ký tự nào nên chọn đại, các bạn có ý kiến gì đóng góp nhé.

Thân!

 

[handung107]

Hiếu ơi, em chuyển sang định dạng .rar hay .zip và gởi cho chị được không ?

 

[anhtuan1066]

WinRAR cũng giãi nén dc 7z mà Dung!

 

[chicuong]

Sao của mình ko giải nén được nhỉ? Bạn xem lại nhé

 

[Mr Okebab]

Sao của mình ko giải nén được nhỉ? Bạn xem lại nhé

Winrar 3.4 trở lên là xả nén được hết mà.
Bạn tích hợp các dạng File nén vào Winrar nhé : Winrar/Setting/Integration/Select All

Thân!

 

[OverAC]

Bắt giò bác Bắb,
Số dư đầu kỳ bị sai rùi phải công thêm số dư đầu năm nữa mới đúng.
Dù sao thì đây là lỗi không đáng kể.

 

[Mr Okebab]

Bắt giò bác Bắb,
Số dư đầu kỳ bị sai rùi phải công thêm số dư đầu năm nữa mới đúng.
Dù sao thì đây là lỗi không đáng kể.

Ừ nhỉ, mải làm cái này lại quên cái khác. Xa vợ có khác. Tệ thật.

Cảm ơn cậu nhé.

 

[ThuNghi]

Cám ơn sự đóng góp của Mr Okebab, nhưng nếu có công ty mà dữ liệu # 2000 dòng 1 tháng => 24.000 dòng/năm, mới chỉ cân đối dùng sumproduct đã thấy mệt rồi, nếu thêm 1 vài báo cáo thì "điên".
1/ Theo tôi dùng thêm cột phụ dùng sumif() nhanh hơn.
2/ Đặt các TK cấp con khoa học hơn => dùng sumif vẫn tính ra TK mẹ
nôm na như sau:

=SUMIF(NKNo,TEXT($A5&"*","0"),NKThanhTien)

_331
_331CT
_331CTA
_331CTE
_3313C
Màu xanh phải là mẹ của màu đỏ và đen. Không nên đặt_331CT và _331CTA đồng cấp.
Vài lời góp ý, nếu không phải xin tha thứ, chấp nhận phạt 5 chai Ken.

 

[OverAC]

Đồng ý với bác ThuNghi, em cũng định kiến nghị với bác Bắb ở cả 2 điểm đó. Nhưng:
- 1. Bác Bắb cố tình sử dụng Calculation = manual để tránh tính toán nhiều lần. Cũng được. Tùy vào sở thích của mỗi người (nhanh hay tiện lợi). Cũng giống như việc bác làm phần mềm mỗi lần ra báo cáo cũng phải generate chứ đâu có ngay.
- 2. File này cùng cho các bác có trình độ tí chứ không phải ai cũng dùng được. Theo thiển ý của em, những vụ chi nhánh hoặc khách hàng em sẽ làm riêng cho nó một cột dữ liệu khác. Rồi toán riêng cho nó sau. Bảng cân đối này với em sẽ chỉ có tài khoản.

 

[Mr Okebab]

Cám ơn sự đóng góp của Mr Okebab, nhưng nếu có công ty mà dữ liệu # 2000 dòng 1 tháng => 24.000 dòng/năm, mới chỉ cân đối dùng sumproduct đã thấy mệt rồi, nếu thêm 1 vài báo cáo thì "điên".
1/ Theo tôi dùng thêm cột phụ dùng sumif() nhanh hơn.
2/ Đặt các TK cấp con khoa học hơn => dùng sumif vẫn tính ra TK mẹ
nôm na như sau:

_331
_331CT
_331CTA
_331CTE
_3313C
Màu xanh phải là mẹ của màu đỏ và đen. Không nên đặt_331CT và _331CTA đồng cấp.
Vài lời góp ý, nếu không phải xin tha thứ, chấp nhận phạt 5 chai Ken.

Thông cảm, dữ liệu data này không phải của em mà em mượn của người khác (member Thien) nên em không care lắm việc đặt tên các tài khoản. Em chỉ hướng dẫn mọi người có cái nhìn tổng quan về bảng CĐSPS thôi.

Nếu chỉ có 1 ĐK thì dùng sumif nhanh hơn (đã có 1 VD mà bác anhtuan1066 đưa lên), tuy nhiên bác chú ý rằng ở đây có ít nhất 3 ĐK (Mã TK, ngày đầu kỳ, ngày cuối kỳ), nếu dùng sumif thì sẽ không được (vì không so sánh sự > hay < được)
(bác có thấy bảng của em thêm chỉ tiêu : Từ ngày . . . đến ngày ., . . không ??)


Vì vậy người ta có thể chọn bất cứ trong khoảng thời gian nào cũng được. Đây mới là sức mạnh của sumproduct

Việc cho các TK theo mà đỏ, đen . . . cái này cũng đơn giản thôi. Cái đó em định trình bày sau. nhưng giờ khái quát luôn.

• Tài khoản thì luôn biến đổi, BCĐSPS sẽ lấy từ DM tài khoản ra, vì vậy các hàng chứa TK tổng hợp là không cố định. Như vậy ta phải dùng 1 vòng lặp duyệt qua hoặc dùng Condition Formating để làm (File BaoCao của em đang theo hướng này)

• Vì vậy việc tô màu sẽ trở lên cực kỳ đơn giản (trong bảng DM tài khoản đã nêu rõ TK nào là TH, TK nào là CT, cấp 1, cấp 2 . . . rồi mà, cứ căn cứ vào đó mà ra ĐK thôi)

- 2. File này cùng cho các bác có trình độ tí chứ không phải ai cũng dùng được. Theo thiển ý của em, những vụ chi nhánh hoặc khách hàng em sẽ làm riêng cho nó một cột dữ liệu khác. Rồi toán riêng cho nó sau. Bảng cân đối này với em sẽ chỉ có tài khoản.

Khi đã thanh thục về hiểu rõ vấn đề này thì việc cho phép trong bảng CĐSPS chỉ hiển thị những TK nào thật đơn giản. Không cần phải chia riêng đâu.
Muốn Bảng CĐSPS tổng hợp thì sẽ có ngay, muốn chi tiết hơn xíu, có ngay, muốn tất cả cũng có.

Vì vậy bảng CĐSPS như trên sẽ dùng cho tất cả các năm, không hạn chế thời gian. Đây chính là điều khác biệt của sumproduct

Thân!

 

[handung107]

Chị chưa có thì giờ xem File của Hiếu, nhưng một điều chị biết chắc là hàm Sumproduct rất mạnh và dễ sử dụng hơn công thức mảng trong trường hợp tính tổng nhiều điều kiện, Từ lâu rồi, chị đã đưa bài hướng dẫn sử dụng hàm Sumproduct, nhưng hình như ít người sử dụng nó lắm, mà hay nghiêng về công thức mảng và VBA. Bản thân chị sử dụng hàm Sumproduct gần như thường xuyên, và chị nghĩ nó rất hữu ích cho các bạn trong công việc phân tích, tổng hợp dữ liệu. Cám ơn Hiếu đã đặt một đề tài liên quan đến hàm Sumproduct này.

 

[Mr Okebab]

Chị chưa có thì giờ xem File của Hiếu, nhưng một điều chị biết chắc là hàm Sumproduct rất mạnh và dễ sử dụng hơn công thức mảng trong trường hợp tính tổng nhiều điều kiện, Từ lâu rồi, chị đã đưa bài hướng dẫn sử dụng hàm Sumproduct, nhưng hình như ít người sử dụng nó lắm, mà hay nghiêng về công thức mảng và VBA. Bản thân chị sử dụng hàm Sumproduct gần như thường xuyên, và chị nghĩ nó rất hữu ích cho các bạn trong công việc phân tích, tổng hợp dữ liệu. Cám ơn Hiếu đã đặt một đề tài liên quan đến hàm Sumproduct này.

File em gửi cho chị bị thiếu mất phần đầu kỳ như Over AC nói. Em sẽ gửi lại ạ

Thực ra em cũng dùng VBA, nhưng trước khi dúng VBA thì phải hình dung xem nếu là hàm thì dùng thế nào ???

Khi dùng tổng hợp các TK đối ứng, bảng CĐKT, KQKD, LCTT thì đúng là sumproduct không có đối thủ.

Cảm ơn chị.

 

[ThuNghi]

Không ai nói sumproduct không hay cả, mình cũng hay dùng mà. Nhưng ý là theo như bài này, dữ liệu nhiều quá dùng thêm cột phụ và sumif thì sẽ nhanh hơn nhiều. (kỳ trước đã thử)
Cụ thể: NKNo:=IF(AND($A2>=NgayDau,$A2<=NgayCuoi),C2,"") hàm này cho if vài lần mà vẫn nhanh hơn.
Vậy thì sumif nhanh hơn rất nhiều. Nhiều lúc cũng nên vận dụng.

 

[Mr Okebab]

Không ai nói sumproduct không hay cả, mình cũng hay dùng mà. Nhưng ý là theo như bài này, dữ liệu nhiều quá dùng thêm cột phụ và sumif thì sẽ nhanh hơn nhiều. (kỳ trước đã thử)
Cụ thể: NKNo:=IF(AND($A2>=NgayDau,$A2<=NgayCuoi),C2,"") hàm này cho if vài lần mà vẫn nhanh hơn.
Vậy thì sumif nhanh hơn rất nhiều. Nhiều lúc cũng nên vận dụng.

Cảm ơn bác, nhưng quả thật em không biết sửa (hic . . híc), hình như hàm mảng.
VD : PS nợ :
=SUMPRODUCT((LEFT(NKNo;LEN(A6))=A6)*(NKNgay>=$F$2)*(NKNgay<=$H$2)*(NKThanhTien))
Bác sửa giùm em.

Và nếu có thể bác sửa lại luôn vào File rồi up cho anh em để còn học tập.

Cảm ơn bác nhiều

 

[handung107]

Nếu dùng Sumif thì chắc chắn phải dùng thêm cột phụ rồi, và tùy vào điều kiện nhiều hay ít, mà ta có thể dùgn 1 hay hơn các cột phụ, và sử dụng hàm If để xác định các điều kiện. Đôi lúc, việc sử dụng các cột phụ sẽ khỏe hơn, Hiếu ạ. Thí dụ : Giả sử em muốn lọc TK Nợ là 1111 và TK có là 6421 em có thể dùng cột phụ nối 2 TK này lại là 11116421 và dùng hàm Sumif lấy một diều kiện là "11116421". Vậy thôi. Hàm Sumif thì dễ dùng hơn và dĩ nhiên nhanh hơn Sumproduct rồi, nhưng Sumproduct thì nhanh hơn công thức mảng và VBA

 

[Mr Okebab]

Nếu dùng Sumif thì chắc chắn phải dùng thêm cột phụ rồi, và tùy vào điều kiện nhiều hay ít, mà ta có thể dùgn 1 hay hơn các cột phụ, và sử dụng hàm If để xác định các điều kiện. Đôi lúc, việc sử dụng các cột phụ sẽ khỏe hơn, Hiếu ạ. Thí dụ : Giả sử em muốn lọc TK Nợ là 1111 và TK có là 6421 em có thể dùng cột phụ nối 2 TK này lại là 11116421 và dùng hàm Sumif lấy một diều kiện là "11116421". Vậy thôi. Hàm Sumif thì dễ dùng hơn và dĩ nhiên nhanh hơn Sumproduct rồi, nhưng Sumproduct thì nhanh hơn công thức mảng và VBA

Vâng, cảm ơn chị nhiều.
Hàm sumif đúng là có thế mạnh riêng là tính toán nhanh. Em công nhận điều này, chỉ có điều ta lại phải thêm cột phụ. (Nếu 2ĐK trở lên)

Cảm ơn chị và bác ThuNghi.

Tuy nhiên ý định của em là hướng mọi người đến 1 CSDL trong excel (tạm gọi là như vậy) mà tất cả các phát sinh đều nằm trong 1 sheet, không thừa và không thiếu.

Từ đó ta có thể làm được rất nhiều các báo cáo khác nhau.
Mà mỗi báo cáo thì có những điều kiện so sánh riêng (VD : Công nợ theo TK, công nợ theo NVKD, công nợ theo hàng hóa . . .. . . ), có rất nhiều chỉ tiêu để làm việc này.

Vì vậy nếu dùng sumif không tạo cột phụ thì em tán thành, nếu dùng cột phụ thì chỉ ở các bài đơn giản thôi, chứ nếu có kết cấu CSDL như em trình bày trên thì sẽ phá nát CSDL ngay, và hơn nữa tạo ra quá nhiều cột phụ (vì mỗi báo cáo sẽ đòi hỏi các chỉ tiêu rất khác nhau (thậm chí là có những báo cáo phải tạo ra 4,5 cột phụ).

(Ngày trước, bảng của em gồm có hơn 20 cột phụ, sau em hoảng quá nên chuyển qua cách khác)

Vì vậy em định là qua loạt bài này sẽ hướng mọi người đến cách tổ chức một CSDL tốt hơn, tối ưu hơn.

Tuy nhiên em cũng rất cảm ơn chị và bác ThuNghi.

Thân!

 

[handung107]

Vâng, cảm ơn chị nhiều.
Hàm sumif đúng là có thế mạnh riêng là tính toán nhanh. Em công nhận điều này, chỉ có điều ta lại phải thêm cột phụ. (Nếu 2ĐK trở lên)

Cảm ơn chị và bác ThuNghi.

Tuy nhiên ý định của em là hướng mọi người đến 1 CSDL trong excel (tạm gọi là như vậy) mà tất cả các phát sinh đều nằm trong 1 sheet, không thừa và không thiếu.

Từ đó ta có thể làm được rất nhiều các báo cáo khác nhau.
Mà mỗi báo cáo thì có những điều kiện so sánh riêng (VD : Công nợ theo TK, công nợ theo NVKD, công nợ theo hàng hóa . . .. . . ), có rất nhiều chỉ tiêu để làm việc này.

Vì vậy nếu dùng sumif không tạo cột phụ thì em tán thành, nếu dùng cột phụ thì chỉ ở các bài đơn giản thôi, chứ nếu có kết cấu CSDL như em trình bày trên thì sẽ phá nát CSDL ngay, và hơn nữa tạo ra quá nhiều cột phụ (vì mỗi báo cáo sẽ đòi hỏi các chỉ tiêu rất khác nhau (thậm chí là có những báo cáo phải tạo ra 4,5 cột phụ).

(Ngày trước, bảng của em gồm có hơn 20 cột phụ, sau em hoảng quá nên chuyển qua cách khác)

Vì vậy em định là qua loạt bài này sẽ hướng mọi người đến cách tổ chức một CSDL tốt hơn, tối ưu hơn.

Theo chị, nếu dùng cột phụ mà không muốn làm hỏng CSDL, em có thể đặt các điều kiện lọc hay các cột phụ này tại một Sheet riêng không nằm chung với Sheet data.
Việc vận dụng hàm Sumproduct hay Sumif theo trường hợp nào là tối ưu tùy vào từng trường hợp cụ thể mới nói hết được. Chị rất thích hàm Sumproduct, và hay dùng nó, nhưng chị cũng không phản đối việc thiết lập cột phụ, tuy nhiên, lạm dụng quá nhiều cột phụ để dùng hàm Sumif, cũng làm cho File trở nên cồng kềnh và thiếu hiệu quả

thôi, để vài ngày nữa rảnh rảnh, chị xem File của em, chị sẽ góp ý cụ thể hơn với em nhé. Thân