Theo dõi công nợ khách hàng bằng excel (7 người xem)

[SA_DQ]

Bạn không giả lập thì tôi giả lập & bạn cho ý kiến nha!

Để lập bào cáo nơ quá hạn trong quý 3/2009 trên cơ sở bảng số liệu như sau:

|AD|AE|AF|AG|AH|AI|AJ|AK
| NgayCT | KhHang | DĐKy | PhatSinnh | STien | CTu | NoCK | GhiChu
|6/27/09|CTi CF SMC|4.103.232||0||4.103.232|
|7/10/09|Đông hải|29.000.000||0||29.000.000|1
|7/13/09|TWT/GMD|150.000||0||150.000|2
|7/13/09|TWT/GMD|150.000||0||150.000|3
|7/13/09|2uang minh|6.800.000||0||6.800.000|4
|7/13/09|GS Toàn quốc|51.450.000||51.450.000|ACB21.01|0|5
|7/13/09|Thanh bình HT|60.300.000||60.300.000||0|6
|7/13/09|Trường phong|23.400.000|0|||23.400.000|7
|7/29/09|GS Toàn quốc||22.050.000|22.050.000|0||8
|7/29/09|TWT/GMD||150.000|0||0|9
|8/9/09|Đông hải||46.000.000|0|||10
|8/9/09|Đông hải||36.000.000|0|||11
|8/9/09|Đông hải||39.000.000|0|||12
|8/9/09|Thanh bình HT||81.200.000|0|||13
|8/9/09|TWT/GMD||2.546.400|0||0|14
|8/9/09|TWT/GMD||2.546.400|0||0|15
|8/9/09|TWT/GMD||2.546.400|0|||16
||||0|0||

(*) Bỡi ngày 7/1/09 đầu quý không có giao dịch, nên macro sẽ tìm ngày trước đó gần nhất (6/27/09) làm mốc để lập bảng nợ quá hạn quý 3/09

(*) Đầu quý là anh Đông hải; Trong quý có 4 giao dịch. So với hôm nay (8/20/09) chỉ có 1 giao dịch (1) là quá hạn => sẽ được chuyển sang trang 'QuaHan' ?

(*) Đến anh TWT/GMD có 6 giao dịch. Trong đó có 3 giao dịch quá hạn 1 tháng => Vô trang quá hạn;

(*) 2uang minh 1 giao dịch quá hạn;

(*) Toàn quốc: 1 giao dịch đã thanh toán xong nơ.

(**) tóm lại giao dịch trước 7/20/09 còn dư nợ sẽ vô sổ đen, phải không?

(Đáng ra những dữ liệu này các bạn phải cung cấp - Chán các bạn quá đi mất!)

 

[youkosuk]

Để lập bào cáo nơ quá hạn trong quý 3/2009 trên cơ sở bảng số liệu như sau:

|AD|AE|AF|AG|AH|AI|AJ|AK
| NgayCT | KhHang | DĐKy | PhatSinnh | STien | CTu | NoCK | GhiChu
|6/27/09|CTi CF SMC|4.103.232||0||4.103.232|
|7/10/09|Đông hải|29.000.000||0||29.000.000|1
|7/13/09|TWT/GMD|150.000||0||150.000|2
|7/13/09|TWT/GMD|150.000||0||150.000|3
|7/13/09|2uang minh|6.800.000||0||6.800.000|4
|7/13/09|GS Toàn quốc|51.450.000||51.450.000|ACB21.01|0|5
|7/13/09|Thanh bình HT|60.300.000||60.300.000||0|6
|7/13/09|Trường phong|23.400.000|0|||23.400.000|7
|7/29/09|GS Toàn quốc||22.050.000|22.050.000|0||8
|7/29/09|TWT/GMD||150.000|0||0|9
|8/9/09|Đông hải||46.000.000|0|||10
|8/9/09|Đông hải||36.000.000|0|||11
|8/9/09|Đông hải||39.000.000|0|||12
|8/9/09|Thanh bình HT||81.200.000|0|||13
|8/9/09|TWT/GMD||2.546.400|0||0|14
|8/9/09|TWT/GMD||2.546.400|0||0|15
|8/9/09|TWT/GMD||2.546.400|0|||16
||||0|0||

(*) Bỡi ngày 7/1/09 đầu quý không có giao dịch, nên macro sẽ tìm ngày trước đó gần nhất (6/27/09) làm mốc để lập bảng nợ quá hạn quý 3/09

(*) Đầu quý là anh Đông hải; Trong quý có 4 giao dịch. So với hôm nay (8/20/09) chỉ có 1 giao dịch (1) là quá hạn => sẽ được chuyển sang trang 'QuaHan' ?

(*) Đến anh TWT/GMD có 6 giao dịch. Trong đó có 3 giao dịch quá hạn 1 tháng => Vô trang quá hạn;

(*) 2uang minh 1 giao dịch quá hạn;

(*) Toàn quốc: 1 giao dịch đã thanh toán xong nơ.

(**) tóm lại giao dịch trước 7/20/09 còn dư nợ sẽ vô sổ đen, phải không?

(Đáng ra những dữ liệu này các bạn phải cung cấp - Chán các bạn quá đi mất!)

Tính theo tháng đi bạn ơi, nếu quá 30 thì được coi là nợ xấu. Ví dụ ngày phát hành hóa đơn là ngày 09/07/2009 mà ngày xem báo cáo là 08/08/2009 thì hóa đớn đó vẫn còn hợp lệ, nhưng nếu xem ở ngày 09/08/2009 thì hóa đơn đó được xem là nợ xấu (tính luốn ngày phát hành hóa đơn là 1 ngày luôn).

 

[ChanhTQ@]

Tính theo tháng đi bạn ơi, nếu quá 30 thì được coi là nợ xấu. Ví dụ ngày phát hành hóa đơn là ngày 09/07/2009 mà ngày xem báo cáo là 08/08/2009 thì hóa đớn đó vẫn còn hợp lệ, nhưng nếu xem ở ngày 09/08/2009 thì hóa đơn đó được xem là nợ xấu (tính luốn ngày phát hành hóa đơn là 1 ngày luôn).

Vấn đề là trong bảng nợ quá hạn này liệt kê bắt đầu từ mốc thời gian nào sau đây:

(*) Từ đầu CSDL (Nghĩa là liệt kê tất tần tật)

(*) Từ Đầu năm hiện hành

(*) Từ đầu quý, như trên đang dự định

(*) Từ đầu của tháng trước; cách tháng hiện hành 1 tháng Ví dụ hiện là tháng 8 thì chỉ đưa vô báo cáo nợ quá hạn những khách hàng nợ quá hạn từ đầu tháng trước đó.

 

[youkosuk]

Vấn đề là trong bảng nợ quá hạn này liệt kê bắt đầu từ mốc thời gian nào sau đây:

(*) Từ đầu CSDL (Nghĩa là liệt kê tất tần tật)

(*) Từ Đầu năm hiện hành

(*) Từ đầu quý, như trên đang dự định

(*) Từ đầu của tháng trước; cách tháng hiện hành 1 tháng Ví dụ hiện là tháng 8 thì chỉ đưa vô báo cáo nợ quá hạn những khách hàng nợ quá hạn từ đầu tháng trước đó.

Mình cũng chưa hiểu ý của bạn là như thế nào. Chắc là theo cách này:
"Từ đầu của tháng trước; cách tháng hiện hành 1 tháng Ví dụ hiện là tháng 8 thì chỉ đưa vô báo cáo nợ quá hạn những khách hàng nợ quá hạn từ đầu tháng trước đó"

Cách thức hoạt động của cái sheet CSDL mình đang làm là :

Dữ liệu phát sinh theo tháng.
Ví dụ dữ liệu phát sinh bắt đầu từ tháng 07/2009. (trước đó coi như bằng 0, không có gì hết => là dữ liệu mới hoàn toàn). thì cột đầu kỳ sẽ bằng 0.

Hóa đơn phát hành trong tháng 07/2009 thì nhập vào cột dữ liệu phát sinh, thu tiền thì nhập vào cột thanh toán (còn trong tháng mà không phát sinh thì 2 cột này bằng 0).

Đến cuối tháng thì tự động cột cuối kỳ sẽ cộng trừ để tạo ra số dư cuối kỳ.

Sang tháng 08/2009 mình tạo ra 1 cái sheet mới và copy toàn bộ số dư cuối kỳ để làm số dư đầu kỳ cho tháng 08/2009. và lại thực hiện lặp lại như cho số liệu phát sinh như tháng 07/2009.

Qua bảng đối chiếu công nợ thì mình copy thủ công trên bảng CSDL dán vào bảng đối chiếu công nợ rồi in ra để chuyển cho khách hàng => thì giờ mình muốn cái này chạy tự động là mình chỉ chọn tên khách hàng thì các dữ liệu công nợ như tồn đầu kỳ, phát sinh, thanh toán đều chạy ra hết.

Qua bảng công nợ quá hạn thì mình lấy ngày phát hành hóa đơn trừ đi ngày hiện hành và so sánh có lớn hơn 30 không, nếu lớn hơn thì mình coi đó là công nợ quá hạn và copy tất cả các dòng có số lớn hơn 30 qua bảng công nợ quá hạn.

Mình đã sữa lại cái bảng dữ liệu theo hướng phát sinh tháng 07/2009 chuyển số dư sang tháng 08/2009.

 

[SA_DQ]

Mình không thể nào xem file đính kèm của bạn được

Bạn xem của mình vậy, ở trang tính quá hạn đó (Bấm vô nút để chạy macro)

Sub QuaHan()
 Dim Dat As Date, Thg As Integer, fRw As Long, eRw As Long
 Dim Temp, DFormat As String
 Dim Rng As Range, sRng As Range, Sh As Worksheet
 
 Thg = Month(Date):                                      Set Sh = Sheets("Data")
 Set Rng = Sh.Range(Sh.[AD4], Sh.[aD65500].End(xlUp)):   eRw = Rng.Rows.Count + 4
 If Thg = 1 Then
   Dat = DateSerial(Year(Date - 1), 12, 1)
 Else
   Dat = DateSerial(Year(Date), Month(Date) - 1, 1)
 End If
 DFormat = Rng.Offset(1).NumberFormat
 Rng.NumberFormat = "M/D/yyyy"
 Range("B5:H" & eRw).ClearContents
 Set sRng = Rng.Find(Dat, , xlFormulas, xlWhole)
 If sRng Is Nothing Then
   For Thg = 1 To (Date - Dat)
      If Not Rng.Find(Dat + Thg) Is Nothing Then
         fRw = Rng.Find(Dat + Thg).Row:                  Exit For
      End If
   Next Thg
 Else
   fRw = sRng.Row
 End If
 Thg = [B4].Interior.ColorIndex
 [A4].Resize(, 8).Interior.ColorIndex = IIf(Thg < 42, Thg + 1, 34)
 If fRw <> 0 Then
   For Thg = fRw To eRw
      If Date - Sh.Cells(Thg, "AD").Value > 30 And Sh.Cells(Thg, "AJ").Value > 0 Then
         With [B65500].End(xlUp)
            .Offset(1).Value = Sh.Cells(Thg, "AE").Value 'KHg'
            .Offset(1, 1).Resize(, 2).Value = Sh.Cells(Thg, "Ac").Resize(, 2).Value
            .Offset(1, 3).Value = Sh.Cells(Thg, "AH").Value
            .Offset(1, 4).Value = Date + 30 - Sh.Cells(Thg, "AD").Value
            .Offset(1, 5).Value = Sh.Cells(Thg, "Ag").Value
            Temp = Sh.Cells(Thg, "Ak").Value
            .Offset(1, 6).Value = IIf(Temp = "", Sh.Cells(Thg, "Aj").Value, Temp)
         End With
      End If
   Next Thg
 End If
 Rng.NumberFormat = DFormat
 End Sub

Chờ phản hồi từ bạn & khoan hãy xem trang tính cuối nha!

 

[youkosuk]

Bạn xem của mình vậy, ở trang tính quá hạn đó (Bấm vô nút để chạy macro)

Chờ phản hồi từ bạn & khoan hãy xem trang tính cuối nha!

File mình download xuống lại vẫn xem được mà.

Sao của Gạch Men Hoàng gia chạy ra không ra, nó cũng là nợ quá hạn mà.

 

[SA_DQ]

Sao của Gạch Men Hoàng gia chạy ra không ra, nó cũng là nợ quá hạn mà.

Mình cũng chưa hiểu ý của bạn là như thế nào. Chắc là theo cách này:
"Từ đầu của tháng trước; cách tháng hiện hành 1 tháng Ví dụ hiện là tháng 8 thì chỉ đưa vô báo cáo nợ quá hạn những khách hàng nợ quá hạn từ đầu tháng trước đó (1/7)"

Trong file đính kèm của mình, anh chàng gạch men HG này có lần giao dịch cuối cùng là 11/10 của năm trước mà;
Còn trong báo cáo chỉ liết kê nợ quá hạn từ 1/7/2009 đến nay thôi!

Chắc chúng ta chưa hiểu ý nhau!

 

[youkosuk]

Trong file đính kèm của mình, anh chàng gạch men HG này có lần giao dịch cuối cùng là 11/10 của năm trước mà;
Còn trong báo cáo chỉ liết kê nợ quá hạn từ 1/7/2009 đến nay thôi!

Chắc chúng ta chưa hiểu ý nhau!

Chắc là chưa hiểu ý nhau rồi. Vì tất cả những số liệu cong nợ đó tính tới thời điểm này là công nợ xấu hết.

Bạn xem cái file dữ liệu mình mới load lên lại hôm qua. Mình đã sữa lại dữ liệu phát sinh cho tháng 07 và chuyển số dư sang tháng 08.

 

[SA_DQ]

Đã sửa lại để muốn gì bạn sẽ được nấy!

Hồi sáng giờ mở file ra đọc mà cũng chưa hiểu lắm, nhưng giờ thì bắt đầu hiểu.

Vấn đề 1:-Thay vì mình sử dụng sheet CSLD để nhập dữ liệu và lọc dữ liệu thì bạn thiết kế lại thành sheet Data => như vậy cho mình hỏi:
- Thứ nhất . . .i.

- Thư hai là sao mình nhìn tất cảc các khách hàng không được => chỉ xem được 1 khách hàng ? có thể cho thêm mục xem tất cả các khách hàng trong cái list sổ xuống thì ngon lành.

Thì ngon lành đây, xin mời:

(*) Macro dưới đây đã được gán tổ hợp phím tắt, bạn có thể chạy bằng bấm tổ hợp đó;

(*) Cách thứ hai để chạy macro: Vô trang tính 'Data' tại danh sách đổ xuống ta chọn dòng có chữ 'All' (Hình như dòng đầu thì phải)

Chúc thành công!

Option Explicit
Sub CongNo()
 Dim Rng As Range, cRng As Range, Clls As Range, sRng As Range, tRng As Range
 Dim Sh As Worksheet, MyAdd As String, Rws As Long
 
 Sheets("Data").Select:                   Set Sh = Sheets("Report")
 Sh.Range("C7:l" & Sh.[c65500].End(xlUp).Row + 7).Clear
 Set cRng = Range([Y2], [Y65500].End(xlUp))
 Set Rng = Range([Ae4], [Ae65500].End(xlUp))
 Rws = Sh.[B5].Interior.ColorIndex
 Sh.[B5].Resize(, 11).Interior.ColorIndex = IIf(Rws < 42, Rws + 1, 34)
 For Each Clls In cRng
   Set sRng = Rng.Find(Clls.Value, , xlFormulas, xlWhole)
   If Not sRng Is Nothing Then
      MyAdd = sRng.Address
         Do
            Sh.[e65500].End(xlUp).Offset(1, -2).Resize(, 9).Value = _
               sRng.Offset(, -2).Resize(, 9).Value
            Set sRng = Rng.FindNext(sRng)
         Loop While Not sRng Is Nothing And sRng.Address <> MyAdd
   End If
      Set tRng = Sh.[e65500].End(xlUp)
      tRng.Offset(1).FormulaR1C1 = "=TCong"
      tRng.Offset(1).Resize(, 6).Font.Bold = True
      If tRng.Offset(-1).Value <> tRng.Offset(1).Value Then
         Rws = tRng.Row - tRng.Offset(-1, -1).End(xlUp).Row + 1
         tRng.Offset(1, 1).FormulaR1C1 = "=SUM(R[-" & Rws & "]C:R[-1]C)"
         tRng.Offset(1, 1).AutoFill Destination:=tRng.Offset(1, 1).Resize(, 3)
         tRng.Offset(1, 5).FormulaR1C1 = "=SUM(R[-" & Rws & "]C:R[-1]C)"
      Else
         tRng.Offset(1, 1).FormulaR1C1 = "=SUM(R[-1]C)"
         tRng.Offset(1, 5).FormulaR1C1 = "=SUM(R[-1]C)"
      End If
 Next Clls
 Sh.Select
End Sub

 

[youkosuk]

Thì ngon lành đây, xin mời:

(*) Macro dưới đây đã được gán tổ hợp phím tắt, bạn có thể chạy bằng bấm tổ hợp đó;

(*) Cách thứ hai để chạy macro: Vô trang tính 'Data' tại danh sách đổ xuống ta chọn dòng có chữ 'All' (Hình như dòng đầu thì phải)

Chúc thành công!

[/PHP]

Mình sẽ nhập dữ liệu vào sheet nao, sheet data hay report. Thông thường lúc trước thì mình sẽ nhập vào sheet report.

 

[SA_DQ]

Mình sẽ nhập dữ liệu vào sheet nao, sheet data hay report. Thông thường lúc trước thì mình sẽ nhập vào sheet report.

Vùng B của sheets("Data") bạn à.

Lúc đó chỉ nhập những dòng dữ liệu thôi; Những dòng cộng,. . . sẽ nhường lại máy làm, chính xác hơn bạn là cái chắc!

Đó là cách nhiều người hay làm với CSDL & mọi thứ cần có trong báo cáo mà máy làm được không bao giờ nên có trong CSDL.

 

[youkosuk]

Vùng B của sheets("Data") bạn à.

Lúc đó chỉ nhập những dòng dữ liệu thôi; Những dòng cộng,. . . sẽ nhường lại máy làm, chính xác hơn bạn là cái chắc!

Đó là cách nhiều người hay làm với CSDL & mọi thứ cần có trong báo cáo mà máy làm được không bao giờ nên có trong CSDL.

Ok, vậy thì trên sheet data nên tách cái phần xem từng khách hàng qua sheet report cho gọn. trên sheet report nếu muốn xem tất cả thì chọn all, còn xem từng khách hàng thì chọn tên khách hàng => dễ dàng hơn.

Còn sheet data chi để phục vụ nhập dữ liệu thôi.

Mình nghỉ nếu tạo ra một sheet là màn hình chính bao gồm các nút lệnh:
1- Nút nhập dữ liệu => nhấn vào chạy qua sheet data để nhập dữ liệu.
2- Nút xem báo cáo nợ quá hạn => nhấn vào tự động chạy qua sheet report, nếu muốn xem tất cả thì all, từng khách hàng thì chọn tên khách hàng.
3- Nút gửi đối chiếu công nợ hàng tháng => nhấn nút này thì chạy qua sheet đối chiếu công nợ.

Thanks

 

[SA_DQ]

À ha! Sau 1 vòng với các yêu cầu của bạn ta lại quay về vạch xuất phát

Nhưng ở tầm cao hơn nhiều rồi!

Ok, vậy thì

Mình nghỉ nếu tạo ra một sheet là màn hình chính bao gồm các nút lệnh:
1- Nút nhập dữ liệu => nhấn vào chạy qua sheet data để nhập dữ liệu.

2- Nút xem báo cáo nợ quá hạn => nhấn vào tự động chạy qua sheet report, nếu muốn xem tất cả thì all, từng khách hàng thì chọn tên khách hàng.

3- Nút gửi đối chiếu công nợ hàng tháng => nhấn nút này thì chạy qua sheet đối chiếu công nợ.

Thanks

Bổ sung thêm một điều quan trọng nữa, đó là không nên nhập dữ liệu trực tiếp lên CSDL tại Data, mà lấy 1 trang tính nữa làm trang nhập liệu

Mình sẽ giúp bạn hoàn tất những yêu cầu này trong 30 ngày!

Đầu tiên ta sẽ thống nhứt số các trang tính như sau:
'Nhap'; 'Data' (Hay 'CSDL'); 'CongNo'; 'QuaHan'; 'Form'
(Ở đây 'Form' để đổ dữ liệu vô đó, in ra & gởi đi các đối tác)

Các trường của 'Data', gồm:
[TT]: Cho vui thôi, theo thói quen;
[SCTu]:= Lưu số chứng từ;
[NgayCT] Kiểu dữ liệu Pháp-Mỹ?;
[KhHang] - Nếu nhiều khách hàng ta chuyển sang xài mã KH;
[DuDK];
[PhSinh];
[SoTienTT];
[CTuTT];
[NoCK];
& cuối cùng là [GhiChu]

2ui ước dữ liệu nhập tăng lên theo trường [NgayCT]

Bạn xem có điều gì góp thêm nha.

 

[youkosuk]

nhưng ở tầm cao hơn nhiều rồi!


Bổ sung thêm một điều quan trọng nữa, đó là không nên nhập dữ liệu trực tiếp lên csdl tại data, mà lấy 1 trang tính nữa làm trang nhập liệu

mình sẽ giúp bạn hoàn tất những yêu cầu này trong 30 ngày!

đầu tiên ta sẽ thống nhứt số các trang tính như sau:
'nhap'; 'data' (hay 'csdl'); 'congno'; 'quahan'; 'form'
(ở đây 'form' để đổ dữ liệu vô đó, in ra & gởi đi các đối tác)

các trường của 'data', gồm:
[tt]: Cho vui thôi, theo thói quen;
[sctu]:= lưu số chứng từ;
[ngayct] kiểu dữ liệu pháp-mỹ?;
[khhang] - nếu nhiều khách hàng ta chuyển sang xài mã kh;
[dudk];
[phsinh];
[sotientt];
[ctutt];
[nock];
& cuối cùng là [ghichu]

2ui ước dữ liệu nhập tăng lên theo trường [ngayct]

bạn xem có điều gì góp thêm nha.

ok, cỨ thực hiện như vậy đi. Có gì sẽ bổ sung sau.

 

[SA_DQ]

Xong cơ bản rồi đây; Bạn xài thử & cho nhận xét để bổ sung nha!

ok, cỨ thực hiện như vậy đi. Có gì sẽ bổ sung sau.

Ghi chú:

(1) Đề án trên đã được sửa đôi chút, tại chổ ni:

Thay vì ta có 2 trang tính 'Form' & 'CongNo' mình đã gộp chung lại;
Bạn chỉ việc copy dữ liệu trong trang này từ vùng B sang vùng A trước khi in & gởi đến đối tác;

(2) Hình như tại 'QuaHan' có gì đó chưa ổn lắm!

 

[SA_DQ]

Hứa 1 tháng, nhưng 1 tuần đã xong rồi đây

Chờ phản hồi từ bạn!
&
Chúc thành công!

 

[youkosuk]

chờ phản hồi từ bạn!
&
chúc thành công!

Chưa hiểu được, cái nút CÔNG Nợ bấm vào không thấy chạy. bật security ở mức low rồi.
Mình gửi cái file sơ đồ như sau, bạn xem file đính kèm.

 

[yuyumi]

Mình có 1 mẫu file công nợ do minh tự thiết kế, nhưng hầu hết là làm bằng tay, bạn nao co thể chỉ mình công thức được không?
mình muốn bảng tổng hợp công nợ phải thể hiện được số đã thu , chưa thu và thêm cả phần người bán hàng để có thể tính % hoa hồng cho họ nữa.
Bạn nào giúp mình với!

 

[hoanglong84]

Mình có 1 mẫu file công nợ do minh tự thiết kế, nhưng hầu hết là làm bằng tay, bạn nao co thể chỉ mình công thức được không?
mình muốn bảng tổng hợp công nợ phải thể hiện được số đã thu , chưa thu và thêm cả phần người bán hàng để có thể tính % hoa hồng cho họ nữa.
Bạn nào giúp mình với!

bạn hãy dùng các hàm trong excel để công việc ghi sổ được đơn giản hơn như hàm Vlookup, Hlookup, I F, Sumif,...

 

[kenichi2003]

Ai chỉ giùm mình cách tính hàm sumif 2 điều kiện mình cần tính: Tổng tiền của dịch vụ e-com năm 2010! Mình chỉ cắt một đoạn dán lên thôi vì file nặng quá bỏ lên hết ko được! Cám ơn nhiều