Chương trình quản lý công nợ Version 1.0

[ngh_khanh]

Thời gian rảnh nên mình là file quản lý công nợ này. Mình up lên mọi người tham khảo nha!

 

[Nguyễn Duy Tuân]

Thời gian rảnh nên mình là file quản lý công nợ này. Mình up lên mọi người tham khảo nha!

Tôi thấy bạn thiết kế chương trình khá gọn gàng, đơn giản và dễ dùng. Để hoàn thiện hơn bạn cải tiến tốc độ chạy cho nhanh nhé. Hiện nay mới chỉ có vài dòng chứng từ thôi nhưng các báo cáo chạy chậm lắm.

 

[ngh_khanh]

Tôi thấy bạn thiết kế chương trình khá gọn gàng, đơn giản và dễ dùng. Để hoàn thiện hơn bạn cải tiến tốc độ chạy cho nhanh nhé. Hiện nay mới chỉ có vài dòng chứng từ thôi nhưng các báo cáo chạy chậm lắm.

Em mới chỉ biết vậy thui. Còn mảng thì em biết ít lắm! Nhờ thầy tư vấn cụ thể thêm dùm cho em nha!

 

[yeudoi]

Cảm ơn bạn về chương trình. Theo mình bạn nên làm một form màn hình chính có các nút lệnh để mọi người có thể dể sử dụng và cũng không nên khóa VBA có như vậy mọi người mới chỉnh sửa các code giúp bạn. Mong bạn hoàn thiện thêm chương trình.

 

[ptm0412]

.Vài lời góp ý:

1. Sheet THCNO vẫn có cái checkbox y như topic chương trình quản lý hàng hoá của NHDK (chắc cùng tác giả), chỉ có mỗi 1 mục đích là dấu dòng:
- Đâu cần phải insert sẵn 2000 dòng trống rồi phải dấu dòng?
- Đã dùng công thức SumIf thì đâu cần VBA?
- Đã là tổng hợp mọi khách hàng thì chỉ cần copy DS khách hàng trong danh mục qua, rồi dùng công thức hoặc VBA tính tổng phát sinh và dư nợ cuối kỳ. Có bao nhiêu KH thì chép bấy nhiêu dòng, không cần tới 2000 dòng và cũng không cần click chuột phải để chọn khách hàng.
- Nên có thêm lựa chọn :Tổng hợp công nợ từ ngày A đến ngày B, hoặc theo tháng - năm

2. Cũng 1 lỗi y như topic vừa nêu: không dùng name động nên cái listbox xổ xuống có tới 9000 dòng trắng.

3. Bạn muốn mọi người tham khảo mà vẫn để pass VBA. Chả biết có cái gì cao siêu trong đó.
Như anh Tuân nói: file chạy quá chậm, tôi muốn tìm lý do chậm để cải tiến cho bạn chẳng hạn, đụng đầu vào bức tường đòi pass 1 cái là mất hứng.

4. Không có form nhập liệu, nhập trực tiếp trên sheet thì không sao. Nhưng chỗ cần nhấn chuột phải chọn mã KH cho khỏi nhập liệu nhầm thì không làm.

5. Trong bảng đối chiếu công nợ: Có mục chọn công nợ từ ngày ... đến ngày, nhưng dù chọn ngày bắt đầu là ngày mấy đi nữa, số dư đầu kỳ cũng là số dư đầu kỳ của ngày mở sổ (sheet THCNO). Và chỉ có 2 cells đơn giản PS nợ, có mà phải dùng VBA!

6. ....

Không thấy cải thiện gì hơn so với chương trình quản lý hàng hoá

 

[ngh_khanh]

.Vài lời góp ý:

1. Sheet THCNO vẫn có cái checkbox y như topic chương trình quản lý hàng hoá của NHDK (chắc cùng tác giả), chỉ có mỗi 1 mục đích là dấu dòng:
- Đâu cần phải insert sẵn 2000 dòng trống rồi phải dấu dòng?
- Đã dùng công thức SumIf thì đâu cần VBA?
- Đã là tổng hợp mọi khách hàng thì chỉ cần copy DS khách hàng trong danh mục qua, rồi dùng công thức hoặc VBA tính tổng phát sinh và dư nợ cuối kỳ. Có bao nhiêu KH thì chép bấy nhiêu dòng, không cần tới 2000 dòng và cũng không cần click chuột phải để chọn khách hàng.
- Nên có thêm lựa chọn :Tổng hợp công nợ từ ngày A đến ngày B, hoặc theo tháng - năm

2. Cũng 1 lỗi y như topic vừa nêu: không dùng name động nên cái listbox xổ xuống có tới 9000 dòng trắng.

3. Bạn muốn mọi người tham khảo mà vẫn để pass VBA. Chả biết có cái gì cao siêu trong đó.
Như anh Tuân nói: file chạy quá chậm, tôi muốn tìm lý do chậm để cải tiến cho bạn chẳng hạn, đụng đầu vào bức tường đòi pass 1 cái là mất hứng.

4. Không có form nhập liệu, nhập trực tiếp trên sheet thì không sao. Nhưng chỗ cần nhấn chuột phải chọn mã KH cho khỏi nhập liệu nhầm thì không làm.

5. Trong bảng đối chiếu công nợ: Có mục chọn công nợ từ ngày ... đến ngày, nhưng dù chọn ngày bắt đầu là ngày mấy đi nữa, số dư đầu kỳ cũng là số dư đầu kỳ của ngày mở sổ (sheet THCNO). Và chỉ có 2 cells đơn giản PS nợ, có mà phải dùng VBA!

6. ....

Không thấy cải thiện gì hơn so với chương trình quản lý hàng hoá

Cám ơn sư phụ! Em cũng biết nhưng quả thật em vẫn chưa làm được. Em đang gặp khó khăn ở Sheet TH_CNO này. Em đưa file không khoá lên nhờ sư phụ và AC giúp em thêm cho Sheet TH_CNO và sửa lại cho em code để tối ưu nó hơn!
Em cám ơn nhiều!

 

[ngh_khanh]

Xin gửi bài này đến anh ptm0412

Em đang gặp khó khăn trong Sheet TH_CNO. Em gửi cho anh và nhờ anh giúp đỡ. (Câu hỏi cụ thể trong file đính kèm).
Cám ơn anh ptm0412 nhiều!

 

[ptm0412]

Tại sao phải kêu đích danh ptm0412 nhỉ? Nếu ptm0412 bận thì chờ đến bao giờ? Vả lại, cao thủ GPE giỏi hơn ptm0412 đông lắm.
Không có thi giờ xem file và code đâu, chỉ vài gợi ý:

Cách tổng quát để tính ra bảng cân đối công nợ từ ngày A đến ngày B:

- Phải có mốc đầu kỳ khi mở sổ, thí dụ 01/01/2011 và số dư công nợ đầu ngày hôm đó (tức là cuối ngày trước)

- Copy danh sách KH từ Dmuc KH sang.

- Tồn đầu ngày A = Vlookup(Mã KH, DSKH,n, 0) + SumProduct((ngày<ngàyA)*(mã = mãKH)*tien) - SumProduct((ngày<ngàyA)*(mã = mãKH)*thanhtoan)

- Phát sinh nợ từ ngày A đến ngày B: =SumProduct((ngày<=ngày B)*(Ngày >= ngày A)*(mã = mãKH)*tien)

- Phát sinh có từ ngày A đến ngày B: =SumProduct((ngày<=ngày B)*(Ngày >= ngày A)*(mã = mãKH)*thanhtoan)

- Tính số dư cuối kỳ

- Tạo dòng tổng.

- Copy và paste special value.

- Filter các dòng không có dư đầu kỳ cũng như không có phát sinh tăng giảm (có thể dùng cột phụ). Xoá các dòng này. Xoá luôn cột phụ.

- Ghi macro các bước trên,

Ghi chú: Để làm các bước này phải nắm vững:
- Cách sử dụng hàm SumProduct
- Cách tạo và sử dụng name (tốt nhất là name động), do đó phải biết hàm Offset
- Khi ghi macro xong, phải chỉnh sửa nhất là dòng tổng để mang tính tổng quát. Muốn vậy, phải biết đọc hiểu và biết sử dụng R1C1 Formula

Ghi chú 2:
Cách này chậm vì dùng hàm mảng. Cách tốt hơn là dùng
- Array VBA,
- hoặc ADODB như topic này ADO: Kết nối Excel Database cho công việc kế toán,
- hoặc dùng MS Query như topic External MS Query


Tóm lại, là bất cứ ai muốn sửa vào file này đều phải làm rất nhiều việc, liên quan đến nhiều kiến thức, làm xong coi như chẳng còn gì là của "công ty NH_DK" cả. Do đó phải tự làm thôi.

 

[ndu96081631]

Tóm lại, là bất cứ ai muốn sửa vào file này đều phải làm rất nhiều việc, liên quan đến nhiều kiến thức, làm xong coi như chẳng còn gì là của "công ty NH_DK" cả. Do đó phải tự làm thôi.

Em cứ bị "dụ dổ" vì cái chổ này đây ---> Sửa 1 chổ đâu có được, vì cứ thấy ngứa mắt khi ngó sang chổ khác... thế là lại.. sửa tiếp ---> Sửa 1 hồi thành ra là.. SỬA TẤT
Ẹc... Ẹc...

 

[ngh_khanh]

Em đã làm thế này rùi mà vẫn chưa có kết quả mong muốn. Em nhờ các sư phụ sửa dùm em nha!

Option Explicit
Sub tao_so()
    Dim rng As Range
    Dim eR As Long, iR As Long
    Application.ScreenUpdating = False
    S6.Range("A7:G65000").ClearContents
    With s3
        eR = .Range("A65535").End(3).Row
        .Range("A2:A" & eR).Resize(, 3).Copy S6.Range("A7")
    End With ' Kiem tra dum em code duoi nhe! Sao no lai khong ra ket qua dung!?
        iR = S6.Cells(Cells.Rows.Count, "A").End(3).Row
        Range("D7").Formula = "=SUMPRODUCT(--(TH_CNO!$C$4<=DATA!$A2),--(TH_CNO!$E$4>=DATA!A2),--(DATA!D2=TH_CNO!$A1),DATA!G2)"
        Range("E7").Formula = "=SUMPRODUCT(--(TH_CNO!$C$4<=DATA!$A2),--(TH_CNO!$E$4>=DATA!A2),--(DATA!D2=TH_CNO!$A1),DATA!H2)"
        Range("F7").Formula = "=max(C7+D7-e7,0)"
        Set rng = S6.Range("D7:F" & iR)
        Range("D7:F7").Copy rng
        'rng.Value = rng.Value
        Set rng = Nothing
End Sub

Bài này em đã xử lý được rùi.

 

[nghiaphuc]

Em đã làm thế này rùi mà vẫn chưa có kết quả mong muốn. Em nhờ các sư phụ sửa dùm em nha!

Thử sửa code lại như vầy xem có đúng không (chú ý những chỗ màu đỏ):

Sub tao_so()
    Dim rng As Range
    Dim eR As Long, iR As Long, [COLOR=red]iR1 As Long[/COLOR]
    Application.ScreenUpdating = False
    S6.Range("A7:G65000").ClearContents
    With s3
        eR = .Range("A65535").End(3).Row
        [COLOR=red]iR1 = S1.[A65536].End(xlUp).Row[/COLOR]
        .Range("A2:A" & eR).Resize(, 3).Copy S6.Range("A7")
    End With
        iR = S6.Cells(Cells.Rows.Count, "A").End(3).Row
        [COLOR=red]Range("D7").Formula = "=SUMPRODUCT((TH_CNO!$C$4<=DATA!$A$2:$A$" & iR1 & ")*(TH_CNO!$E$4>=DATA!$A$2:$A$" & iR1 & ")*(DATA!$D$2:$D$" & iR1 & "=TH_CNO!A7),DATA!$G$2:$G$" & iR1 & ")"[/COLOR]
[COLOR=red]       Range("E7").Formula = "=SUMPRODUCT((TH_CNO!$C$4<=DATA!$A$2:$A$" & iR1 & ")*(TH_CNO!$E$4>=DATA!$A$2:$A$" & iR1 & ")*(DATA!$D$2:$D$" & iR1 & "=TH_CNO!A7)*DATA!$H$2:$H$" & iR1 & ")"[/COLOR]
        Range("F7").Formula = "=MAX(C7+D7-E7,0)"
        Set rng = S6.Range("D7:F" & iR)
        Range("D7:F7").Copy rng
        'rng.Value = rng.Value
        Set rng = Nothing
End Sub

 

[ngh_khanh]

Em đã hoàn thiện file Quản lý công nợ này rùi. Nhưng có điều sao dung lượng của nó tăng lớn thế!? Nhờ các AC tư vấn thêm cho em nha!

 

[sealand]

Như mình đã nói về tiện ích của trang Tmp, vậy thì sao em không dùng luôn cái sheet FOOTER để làm vùng tạm có mấy cái công thức đâu có ảnh hưởng gì. Code sẽ đơn giản như sau thôi

Sub tao_so()
    Dim eR As Long, iR As Long
    Application.ScreenUpdating = False
    S6.Range("A7:G65000").Clear
        eR = s3.Range("A65535").End(3).Row
        s3.Range("A2:A" & eR).Resize(, 3).Copy S6.Range("A7")
        S5.[A18:G18].Copy: S6.[A7:G7].Resize(eR - 1).PasteSpecial Paste:=xlPasteFormats
        S5.[D18:F18].Copy: S6.[D7:F7].Resize(eR - 1).PasteSpecial Paste:=xlPasteFormulas
        S5.[A19:G19].Copy S6.[a7].Offset(eR - 1)
        S6.[A7:H7].Resize(eR).Value = S6.[A7:H7].Resize(eR).Value
        S5.[a21:H22].Copy S6.[a7].Offset(eR + 2)
End Sub

 

[Nguyễn Duy Tuân]

Em đã hoàn thiện file Quản lý công nợ này rùi. Nhưng có điều sao dung lượng của nó tăng lớn thế!? Nhờ các AC tư vấn thêm cho em nha!

Tôi đã chạy thử phiên bản 1.1 của bạn. Tốc độ vẫn chậm, dù chỉ có vài chứng từ. Khi tôi vào sheet "DATA" thêm dữ liệu cho 10,000 dòng thì chương trình gần như đơ. Bạn nên xem lại cách dùng Advance Filter, nếu có thể phải bỏ đi dùng phương án vòng lặp.

Tôi không biết sao các bạn hay dùng Offset và Resize như vậy, vì nó làm tăng công việc tính toán của Excel, chỉ nên dùng nó những lúc thực sự cần thiết. Các tình huống của bạn đều có thể dùng Cells(dòng, cột).Value hoặc Range("Địa chỉ").Value thuần túy.

 

[ptm0412]

Em đã hoàn thiện file Quản lý công nợ này rùi. Nhưng có điều sao dung lượng của nó tăng lớn thế!? Nhờ các AC tư vấn thêm cho em nha!

Xoá tất cả dòng thừa sheet CNO_CTI và sheet TH_CNO, file chỉ còn 148Kb.
Chính dòng lệnh sau đây tạo ra dòng thừa:
S6.Range("A7:G65000").Clear
Chỉ xoá đến dòng cuối có dữ liệu thôi.

Nhận xét:

Bạn không chú ý 1 vài điều tôi gợi ý ở trên:

- Khi chọn báo cáo từ ngày A đến ngày B, thì số dư đầu ngày A phải tính lại, chứ không phải lấy số dư đầu năm. Cụ thể là chưa tính toán cột C sheet tổng hợp.

- Tôi đã lưu ý là phương pháp này chậm, chỉ nên dùng cho khoảng tối đa 20 khách hàng.

- Sửa xong mấy cái này, chỉ có thể nói là hoàn thiện sheet THop, còn các sheet khác chưa ngó tới.

Vài gợi ý về code:

- Các câu lệnh gán công thức cho cợt C, D, E, F, có thể gán 1 lúc cho cả 1 range, không cần phải gán cho 1 dòng rồi copy xuống.
Thí dụ:
Range("C7:C" & endRow2).FormulaR1C1 = ...

- Thay vì copy danh mục thì gán trực tiếp giá trị: SheetA.Range(...).Value = SheetB.Range(...).Value

- Chưa có dòng lệnh xoá dòng không phát sinh & không có số dư đầu kỳ.

- ....

 

[ngh_khanh]

Xoá tất cả dòng thừa sheet CNO_CTI và sheet TH_CNO, file chỉ còn 148Kb.

Chính dòng lệnh sau đây tạo ra dòng thừa:
S6.Range("A7:G65000").Clear
Chỉ xoá đến dòng cuối có dữ liệu thôi.

Nhận xét:

Bạn không chú ý 1 vài điều tôi gợi ý ở trên:

- Khi chọn báo cáo từ ngày A đến ngày B, thì số dư đầu ngày A phải tính lại, chứ không phải lấy số dư đầu năm. Cụ thể là chưa tính toán cột C sheet tổng hợp.

- Tôi đã lưu ý là phương pháp này chậm, chỉ nên dùng cho khoảng tối đa 20 khách hàng.

- Sửa xong mấy cái này, chỉ có thể nói là hoàn thiện sheet THop, còn các sheet khác chưa ngó tới.

Vài gợi ý về code:

- Các câu lệnh gán công thức cho cợt C, D, E, F, có thể gán 1 lúc cho cả 1 range, không cần phải gán cho 1 dòng rồi copy xuống.
Thí dụ:
Range("C7:C" & endRow2).FormulaR1C1 = ...

- Thay vì copy danh mục thì gán trực tiếp giá trị: SheetA.Range(...).Value = SheetB.Range(...).Value

- Chưa có dòng lệnh xoá dòng không phát sinh & không có số dư đầu kỳ.

- ....

Em đã sửa 1 số lỗi như anh hướng dẫn rùi. Còn cái này:

- Khi chọn báo cáo từ ngày A đến ngày B, thì số dư đầu ngày A phải tính lại, chứ không phải lấy số dư đầu năm. Cụ thể là chưa tính toán cột C sheet tổng hợp.

thì quả thật em chưa làm được.
Nhưng sao dung lượng vẫn chưa ổn?
Em biết là cũng phiền nhiều các AC. Quả thật em cũng đang rất cần nó để làm. Nên em đã hỏi.
AC giúp em thêm chút nữa nha!

 

[ptm0412]

Xoá dòng trống tức là chọn nguyên dòng ngay dưới dòng dữ liệu cuối cùng, nhấn Ctrl mũi tên xuống, nhấn chuột phải và chọn delete row. Lưu lại sẽ thấy kết quả: Thanh cuộn không còn nhỏ xíu.

Còn số dư đầu ngày A ở trên đã gợi ý rồi mà?

- Tồn đầu ngày A = Vlookup(Mã KH, DSKH,n, 0) + SumProduct((ngày<ngàyA)*(mã = mãKH)*tien) - SumProduct((ngày<ngàyA) *(mã = mãKH) *thanhtoan)

Cụ thể:

C7 =VLOOKUP(A7;DMKH!$A$2:$C$4;3;0)+SUMPRODUCT((DATA!$A$2:$A$14<$C$4)*(MKH=A7)* (DATA!$G$2:$G$14)) - SUMPRODUCT((DATA!$A$2:$A$14<$C$4) *(MKH=A7) *(DATA!$H$2:$HG$14))

Ghi chú: Làm ơn đừng set Application.Caption thành tên công ty mình nữa, hoặc cứ set nhưng khi thoát ra phải trả về nguyên bản cho anh em nhờ.

 

[ngh_khanh]

Em có việc này muốn nhờ ACE chỉ dùm thêm: Ở Sheet BÁO CAO CÔNG NỢ CHI TIẾT của em, code hiện tại vẫn chưa ổn. Cụ thể là: Khi em chọn mã KH nào đó mà không có phát sinh bên Sheet DATA thì nó copy đè vùng chân trang vào dòng số dư đầu kỳ. Vậy em phải bẫy lỗi thế nào để nó không copy như vậy nữa!?
ACE vui lòng chỉ giáo!

 

[NH_DK]

Hỏi lỗi kết quả không đúng ý muốn.

Dạ, em đã sửa được rùi ah. Còn 1 phần này nữa cũng nằm ở sheet đó. Em muốn nhờ AC xem và hoàn thiện dùm em thêm.

 

[ptm0412]

Vậy Ngọc viết câu lệnh này có nghĩa gì:

           MsgBox "Khong co phat sinh", vbOKOnly: Exit Sub