Dùng Pivot in chi tiết công nợ của tất cả các đối tượng phát sinh

[Ng_Duy_Long]

Dùng Pivot in chi tiết công nợ của tất cả các đối tượng phát sinh. Các bạn xem file đính kèm

 

[ptm0412]

Gởi NgDuyLong,

Một vài góp ý:

1. Công đoạn tách dữ liệu theo điều kiện bằng ADO:
Bạn lấy file dữ liệu ở đâu trên GPE này thì bạn đã biết. Trong topic đó dùng ADO để tách dữ liệu riêng cho
- tài khoản 131 hoặc 331
- ngày nhỏ hơn 1 ngày cho trước
- ngày trong khoảng thời gian cho trước

từ đó tính ra phát sinh trước thời kỳ báo cáo, số dư đầu kỳ báo cáo.

Dùng những câu SQL trong topic đó có thể lấy được số tiền nợ riêng, có riêng, tài khoản đối ứng, ... Tại sao bạn lại khổ công:
- làm lại tất cả những công đoạn đó với 4 cột phụ chèn thêm?
- kết quả phải có thêm 2 cột công thức để tìm số hiệu TK nợ và số hiệu TK có trong khi Data có sẵn?
- phải làm 2 lần, một cho phát sinh nợ và 1 cho phát sinh có, rồi nối và sort lại?

2. Công đoạn tạo pivot table:

Bạn đã tốn công tạo 1 pivot table với số phát sinh trong kỳ, bạn dùng code gán số dư đầu kỳ và số dư cuối kỳ, phải nói là rất kỳ công. Nhưng kết cấu của báo cáo công nợ như vậy rất khó đọc. Thông thường là:
- Dư đầu kỳ (nợ, có)
- Phát sinh trong kỳ (nợ, có)
- Dư cuối kỳ (nợ, có)
Tất cả theo cột.
Bạn lại làm dư đầu và dư cuối theo hàng, và dư đầu ở dưới chót đáy ngang với dư cuối! Tôi chưa thấy mẫu báo cáo kế toán nào có kết cấu như vậy cả.

3. Về ý nghĩa của in liên tục:

Với ý tưởng in liên tục, bạn đã dùng Pivot table và kết quả như tôi đã chỉ ra ở trên. Theo tôi, bạn hãy dùng ADO lần lượt cho từng mã đối tượng, copy xuống sheet nối tiếp nhau, cách nhau 1 dòng cộng và 1 dòng số dư cuối kỳ. Dù vậy cũng không phải viết câu SQL 100 lần cho 100 đối tượng, mà chỉ dùng 1 câu SQL duy nhất với biến thay đổi, biến này chạy theo danh sách mã đối tượng.

 

[Ng_Duy_Long]

Cám ơn ptm0412 đã góp ý.
Tôi mới nghiên cứu ADO và pivot table, trước đây tôi thường lọc dữ liệu bằng Advanced filter, tôi thấy lọc dữ liệu bằng SQL rất hay và hiệu quả hơn AF. Để nghiên cứu ADO tôi đã mượn Data base của file trên diễn đàn. Tôi ko muốn sử dụng toàn bộ code của file đó vì muốn nghiên cứu phải tự mình làm code mới hiểu được code. Tôi chưa viết được 1 SQL để tạo ra data có kết cấu như DATA1 nếu ko dùng cột phụ (nhờ pmt0412 viêt giúp). Tôi tạo ra DATA1 là để tạo ra Pivot còn nếu dùng ADO lần lượt cho từng mã đối tượng, copy xuống sheet nối tiếp nhau, cách nhau 1 dòng cộng và 1 dòng số dư cuối kỳ thì tốc độ có thể chậm hơn dùng Pivot nếu có hàng trăm khách hàng.

 

[sealand]

Tôi tạo ra DATA1 là để tạo ra Pivot còn nếu dùng ADO lần lượt cho từng mã đối tượng, copy xuống sheet nối tiếp nhau, cách nhau 1 dòng cộng và 1 dòng số dư cuối kỳ thì tốc độ có thể chậm hơn dùng Pivot nếu có hàng trăm khách hàng.

Mình thấy Ng_Duy_Long so sánh vậy vẫn không thoả đáng giữa 2 phương pháp. Tất nhiên, Pivot là con đẻ của Excel nên tốc độ tạo lập có thể nhanh hơn, nhưng cái giá phải trả cũng không nhỏ đâu nhé. Với số lượng khách hàng vậy, mỗi lần Refresh thời gian không ít đâu nha, nhất là khi bạn không cần mà nó cứ chiếm bộ nhớ và thực hiện. Còn ADO thì sau khi có kết quả nó hoàn toàn ly khai với nguồn, nó chỉ chiếm bộ nhớ để lưu trữ dữ liệu mà thôi.
Đây là vấn đề nhiều bạn đang trăn trở khi sử dụng Excel để sử lý khối lượng dữ liệu lớn. Bạn nên xem xét, so sánh và chọn cho mình 1 cách sử lý hợp lý nhé.

 

[ptm0412]

I. Về việc không dùng cột phụ:

Tôi chưa viết được 1 SQL để tạo ra data có kết cấu như DATA1 nếu ko dùng cột phụ (nhờ pmt0412 viêt giúp).

Câu SQL cho Sub Data1() của bạn:

mySQL_Dr = "Select DATA.So_CT, DATA.Ngay_CT, DATA.Dien_giai, DATA.Ma_DT,” & _
 DATA.DKCo, DATA.So_tien, DATA.TKNo” & _ 
“ FROM DATA WHERE (DATA.DKNo like('" & a & "')) and (DATA.Ngay_CT >= " & c & ") and “ & _ 
“(DATA.Ngay_CT <= " & d & ") "

hoàn toàn có thể không dùng 2 cột phụ DKCo và TKNo nếu sửa thành:

mySQL_Dr = "Select So_CT, Ngay_CT, Dien_giai, Ma_DT, Co as TKDU, So_tien,” & _
“ 'No' as D_khoan” & _
“FROM DATA WHERE (DATA.DKNo like('" & a & "')) and (DATA.Ngay_CT >= " & c & ") and “ & _ 
“(DATA.Ngay_CT <= " & d & ") "

Tương tự là Sub Data2(), không dùng 2 cột phụ DKNo và TKCo.

Không những thế, bạn có thể chỉ dùng 1 câu SQL duy nhất cho cả Data1 và Data2, thậm chí không cần dùng code để sort như sau:

mySQL_Dr = "Select So_CT, Ngay_CT, Dien_giai, Ma_DT, IIf(No = '" & a & "', Co, No) as TKDU," & _ 
" So_tien, IIf(No= '" & a & "','No', 'Co') as D_khoan " & Chr(10) & _
"FROM DATA WHERE (DATA.No like '" & a & "' Or DATA.Co like '" & a & "') and " & _ 
"(DATA.Ngay_CT >= " & c & ") and (DATA.Ngay_CT <= " & d & ") " & Chr(10) & _
"Order by Ngay_CT;"

Ghi chú:

1. Thủ thuật SQL: muốn chèn 1 cột với toàn bộ giá trị là 'No', chỉ cần Select 'No' As Sth, cũng như chèn 1 cột toàn bằng giá trị zero, chỉ cần Select 0 As Sth


2. Thủ thuật SQL: Muốn chèn 1 cột D_Khoan có cả 2 giá trị 'No' và 'Co' theo điều kiện thì dùng Select IIf(Data.No= a ,'No', 'Co') as D_Khoan
hooặc chèn 1 cột TKDU: Select IIf(Data.No= a, Data.Co, Data.No) as TKDU

3. Do ô C2 sheet Pivot của bạn là số, nên bạn đã phải đi vòng xa là dùng 2 biến
b = Sheet4.Cells(2, 3).Value
a = "_" & b & "%"
Nếu định dạng text theo kiểu dữ liệu hiện hữu của trường Data, thì không cần trung gian, chỉ cần:
a = Sheet4.Cells(2, 3).


4. 2 cột H và I của sheet Temp không hề dùng tới, vậy bạn dùng công thức chèn vào làm gì cho nặng vậy?

II. Về việc dùng ADO hay PivotTable:

Tôi vẫn không ủng hộ việc dùng PivotTable như là 1 báo cáo hoàn chỉnh.
Lý do là cấu trúc 1 báo cáo khác xa cấu trúc của Pivot table, thêm dòng thêm cột thì rất gượng ép mà vẫn không đúng. Về tốc độ thì bạn phải tự kiểm chứng.

 

[Ng_Duy_Long]

Tôi gửi file công nợ in liên tục theo gợi ý của bạn ptm0412. Bạn ptm0412 và các bạn góp ý thêm. Cho tôi hỏi thêm: tại sao sau khi chạy macro chứa các câu lệnh SQL thi các thao tác như edit cell, format cell,... lại chậm hơn bình thường, càng chạy nhiều câu lệnh, máy càng chậm.

 

[ptm0412]

File của bạn chứa virus XM.VNN, 624 name rác, 1 sheet ẩn chứa macro4. Các bạn khác cẩn thận.
Bạn có thể tham khảo thêm bài này, cũng là dùng SQL cho lần lượt từng khách hàng, copy nối tiếp nhau xuống sheet.

Hình như file của bạn chưa tính đến vụ trong khoảng thời gian từ ngày đến ngày thì phải. Tôi chỉ đoán thôi vì khi mở được file lên thì code kiết bị cái antivirus xoá sạnh, không đọc được.

 

[Ng_Duy_Long]

Rất cảm ơn bạn ptm0412. Tôi Upload lại file (đã hết virut va name rác), bạn xem hộ mình tại sao nó lại chậm thế.

 

[ptm0412]

Có vài gợi ý sau:
1. Bạn nên dùng 1 cặp câu lệnh ở đầu và cuối code:

Application.ScreenUpdating = False
.....
Application.ScreenUpdating = True

2. Bạn nên tách 1 phần dữ liệu trong Data theo điều kiện từ ngày đến ngày, và chỉ tách riêng những dòng có liên quan đến tài khoản công nợ (131, 331) copy xuống 1 sheet temp. Lý do:

Code chạy SQL rất nhiều lần, nếu mỗi lần chỉ lọc từ 1 Data (tmp) ngắn, sẽ nhanh hơn là chạy bằng ấy lần từ 1 bảng Data dài.

3. Bạn chưa bỏ hẳn 2 cột phụ

4. Bạn vẫn dùng 1 biến trung gian b rồi mới tới a. Bởi vì bạn vẫn để ô C3 là dạng số. Bạn lọc theo điều kiện số trong 1 trường dữ liệu text, sẽ lâu hơn. Thà là bạn dùng câu:
a = Str([C3]), rồi lọc theo biến a này còn nhanh hơn.


File của bạn hình như vẫn còn hậu quả của virus để lại, chạy 1 vài lần là treo máy luôn, dù cho sửa code lại. Có khi còn báo lỗi connection bị lost, dù cho không hề đóng Cnnx.