Hỗ trợ tìm vị trí tổng các số hạng, từ vị trí cuối đếm lên

[Tình nghĩa giang hồ]

Chào anh chị, em thật là phiền. Nhưng có một vấn đề hành em mấy tháng trời chưa giải quyết được.
Hiện tại chỉ còn duy nhất mảnh ghép cuối cùng này thôi. Nên nhờ anh chị hỗ trợ giúp em.
Dữ liệu của em có 2 cột: cột J và cột O. Cột A là cột em ghi để diễn giải thôi. Thực tế không có cột A. Kết quả mong muốn của em là cột P
Quy tắc như sau:

CỘTA

CỘT J

CỘT O

CỘT P

Vị trí

Payment Amount

Assigned

Kết quả mong muốn

0​

120​

155​

7​

1​

130​

6​

2​

25-

5​

3​

35​

4​

160​

5​

80​

6​

25​

7​

65​

8​

60-

9​

35-

10​

Khách hàng nợ 155 ngàn, cột J là các tờ hoá đơn. Phía dưới là hoá đơn cũ nhất.
Ở đây: với 155 ngàn ta sẽ xoá nợ tờ hoá đơn vị trí 7, xoá nợ tờ hoá đơn vị trí thứ 6, và xoá nợ một phần tờ hoá đơn thứ 5. Vì vậy kết quả là 7, 6, 5.
Số âm thì làm ngược lại nhé anh chị, chỉ xoá nợ những giá trị số âm. Em cũng chẳng biết vì lý do gì mà phần mềm của Đức thiết kế số âm thì dấu trừ nằm bên phải. (Việt Nam thì dấu trừ số âm nằm bên trái)
Nhờ anh chị hỗ trợ giúp em trường hợp này.
Em cảm ơn anh chị nhiều.

 

[BuiQuangThuan]

Theo mô tả. Thì đi từ dưới cùng đi ngược lên. Cái nào cùng loại với cột O và tổng từ dưới lên mà < hơn số ở côt O thì nhặt giá trị theo số thứ tự từ đang duyệt xem sao. Cho tới lúc lớn hơn thì dừng lại

 

[Tình nghĩa giang hồ]

Theo mô tả. Thì đi từ dưới cùng đi ngược lên. Cái nào cùng loại với cột O và tổng từ dưới lên mà < hơn số ở côt O thì nhặt giá trị theo số thứ tự từ đang duyệt xem sao. Cho tới lúc lớn hơn thì dừng lại

Theo mô tả. Thì đi từ dưới cùng đi ngược lên. Cái nào cùng loại với cột O và tổng từ dưới lên mà < hơn số ở côt O thì nhặt giá trị theo số thứ tự từ đang duyệt xem sao. Cho tới lúc lớn hơn thì dừng lại

Dạ không phải trường hợp nào cũng < cột O đâu anh. Mục đích là Clear công nợ.

Cột A

Cột J

Cột O

Cột P

Vị trí

Payment Amount

Assigned

Kết quả mong muốn

0​

120​

160​

9​

1​

130​

2​

25-

3​

35​

4​

160​

5​

80​

6​

25​

7​

65​

8​

60-

9​

170​

Như trường hợp này ở cột O 160: tức là người ta trả nợ 160 ngàn, Nhưng tờ hoá đơn nợ ở vị trí số 9 là 170 ngàn. Tức là sẽ xoá nợ 1 phần tờ hoá đơn số 9. Duy nhất hoá đơn số 9 thui. Cột O hiểu nôm na là số tiền khách hàng trả mình. Nên kết quả sẽ là 9.
(hiểu theo thông thường thì tờ hoá đơn số 9: 170-160 sẽ còn 10 ngàn nhưng cái này phần mềm nó đã làm dùm mình rùi.) Còn hiện tại em chỉ cần lấy ra vị trí số 9 đó là ok anh.

 

[BuiQuangThuan]

Dạ không phải trường hợp nào cũng < cột O đâu anh. Mục đích là Clear công nợ.

Cột A

Cột J

Cột O

Cột P

Vị trí

Payment Amount

Assigned

Kết quả mong muốn

0​

120​

160​

9​

1​

130​

2​

25-

3​

35​

4​

160​

5​

80​

6​

25​

7​

65​

8​

60-

9​

170​

Như trường hợp này ở cột O 160: tức là người ta trả nợ 160 ngàn, Nhưng tờ hoá đơn nợ ở vị trí số 9 là 170 ngàn. Tức là sẽ xoá nợ 1 phần tờ hoá đơn số 9. Duy nhất hoá đơn số 9 thui. Cột O hiểu nôm na là số tiền khách hàng trả mình. Nên kết quả sẽ là 9

Thế thì xét nếu vị trí đang duyệt mà lớn hơn hoặc bằng thì ghi nhận luôn rồi thoát for

 

[Tình nghĩa giang hồ]

Thế thì xét nếu vị trí đang duyệt mà lớn hơn hoặc bằng thì ghi nhận luôn rồi thoát for

Dạ đúng rồi anh, mục đích là vậy đó anh. Nhưng mà điều kiện: thì lấy lớn hơn thì ghi nhận rồi thoát for thì đúng hơn anh. bỏ trường hợp = nhé, vì em chưa thấy trường hợp nào = hết....... toàn là lớn hơn thui. Nhưng anh có thể ghi chú giúp em, nếu muốn làm thêm trường hợp nào = thì sửa ở đây. Em cảm ơn anh.

 

[BuiQuangThuan]

Dạ đúng rồi anh, mục đích là vậy đó anh. Nhưng mà điều kiện: thì lấy lớn hơn thì ghi nhận rồi thoát for thì đúng hơn anh. bỏ trường hợp = nhé, vì em chưa thấy trường hợp nào = hết....... toàn là lớn hơn thui. Nhưng anh có thể ghi chú giúp em, nếu muốn làm thêm trường hợp nào = thì sửa ở đây. Em cảm ơn anh.

Mình gợi ý theo ý mình nghĩ thế thôi. Bạn cũng biết code. Bạn cứ code đi. Vướng đâu lại hỏi tiếp

 

[Tình nghĩa giang hồ]

Mình gợi ý theo ý mình nghĩ thế thôi. Bạn cũng biết code. Bạn cứ code đi. Vướng đâu lại hỏi tiếp

Em đã làm tới khúc sắp xếp lại những số cần, còn tới khúc sum thì bó tay anh

 

[ptm0412]

Muốn tính sheet nào thì đứng tại sheet đó chạy code

Sub DecreaseDebt()
Dim LastRw As Long, InvNo(), PmtAmount(), AssignedAmt As Double, ResultArr(), DraftArr()
Dim TmpAmt As Double
With ActiveSheet
    .[P2:P100].Clear
    .[K2:L1000].Clear
    LastRw = .[J1000].End(xlUp).Row
    AssignedAmt = .[O2].Value
    If AssignedAmt = 0 Then Exit Sub
    InvNo = .Range("A2:A" & LastRw).Value
    PmtAmount = .Range("J2:J" & LastRw).Value
    ReDim ResultArr(1 To UBound(InvNo, 1), 1 To 1)
    ReDim DraftArr(1 To UBound(InvNo, 1), 1 To 2)
    k = 0: TmpAmt = 0
    For i = UBound(InvNo, 1) To 1 Step -1
        If AssignedAmt > 0 Then
            If (PmtAmount(i, 1) > 0) Then
            TmpAmt = Application.Min(AccumAmt, AssignedAmt)
                If AccumAmt <= AssignedAmt Then
                    k = k + 1
                    ResultArr(k, 1) = InvNo(i, 1)
                    DraftArr(i, 1) = Application.Min(PmtAmount(i, 1), AssignedAmt - TmpAmt)
                    DraftArr(i, 2) = PmtAmount(i, 1) - DraftArr(i, 1)
                    AccumAmt = AccumAmt + PmtAmount(i, 1)
                Else
                    Exit For
                End If
            End If
        Else
            If (PmtAmount(i, 1) < 0) Then
            TmpAmt = Application.Max(AccumAmt, AssignedAmt)
                If AccumAmt >= AssignedAmt Then
                    k = k + 1
                    ResultArr(k, 1) = InvNo(i, 1)
                    DraftArr(i, 1) = Application.Max(-Val(PmtAmount(i, 1)), AssignedAmt - TmpAmt)
                    DraftArr(i, 2) = -Val(PmtAmount(i, 1)) - DraftArr(i, 1)
                    AccumAmt = AccumAmt - Val(PmtAmount(i, 1))
                Else
                    Exit For
                End If
            End If
        
        End If
    Next
    If k > 0 Then
        .[P2].Resize(k, 1).Value = ResultArr
        .[K2].Resize(UBound(InvNo, 1), 2).Value = DraftArr
    End If
End With
Erase ResultArr, DraftArr
        
End Sub

 

[Tình nghĩa giang hồ]

Dạ em cảm ơn Smod Ptm0412 nhiều.

 

[bebo021999]

Nhấn nút "TÍNH TỔNG" để chạy code nhé

Option Explicit
Sub tinhtong()
Dim lr&, i&, k&, total&, rng, arr(1 To 1000, 1 To 1)
lr = Cells(Rows.Count, "J").End(xlUp).Row
rng = Range("J2:J" & lr).Value ' vùng chứa Payment Amount
If Range("O2").Value = 0 Then Exit Sub ' nếu số tổng = 0 thì thoát
For i = UBound(rng) To 1 Step -1 ' loop từ dưới lên
    If Abs(total) >= Abs(Range("O2").Value) Then Exit For ' nếu tổng vượt số assigned thì thoát
    Select Case Range("O2").Value > 0
        Case True ' trường hợp số dương
            If rng(i, 1) > 0 Then
                total = total + rng(i, 1)
                k = k + 1
                arr(k, 1) = i - 1 ' nạp vị trí vào mảng arr
            End If
        Case Else ' trường hợp số âm
            If rng(i, 1) < 0 Then
                total = total + rng(i, 1)
                k = k + 1
                arr(k, 1) = i - 1 ' nạp vị trí vào mảng arr
            End If
    End Select
Next
Range("P2:P1000").ClearContents ' xóa dữ liệu cũ
Range("P2").Resize(k, 1).Value = arr ' chép mảng vị trí xuống sheet
End Sub
[/code]

 

[Tình nghĩa giang hồ]

Nhấn nút "TÍNH TỔNG" để chạy code nhé

Option Explicit
Sub tinhtong()
Dim lr&, i&, k&, total&, rng, arr(1 To 1000, 1 To 1)
lr = Cells(Rows.Count, "J").End(xlUp).Row
rng = Range("J2:J" & lr).Value ' vùng chứa Payment Amount
If Range("O2").Value = 0 Then Exit Sub ' nếu số tổng = 0 thì thoát
For i = UBound(rng) To 1 Step -1 ' loop từ dưới lên
    If Abs(total) >= Abs(Range("O2").Value) Then Exit For ' nếu tổng vượt số assigned thì thoát
    Select Case Range("O2").Value > 0
        Case True ' trường hợp số dương
            If rng(i, 1) > 0 Then
                total = total + rng(i, 1)
                k = k + 1
                arr(k, 1) = i - 1 ' nạp vị trí vào mảng arr
            End If
        Case Else ' trường hợp số âm
            If rng(i, 1) < 0 Then
                total = total + rng(i, 1)
                k = k + 1
                arr(k, 1) = i - 1 ' nạp vị trí vào mảng arr
            End If
    End Select
Next
Range("P2:P1000").ClearContents ' xóa dữ liệu cũ
Range("P2").Resize(k, 1).Value = arr ' chép mảng vị trí xuống sheet
End Sub
[/code]

dạ em cảm ơn anh bebo021999

 

[VetMini]

.... Còn hiện tại em chỉ cần lấy ra vị trí số 9 đó là ok anh.

Lưu ý: tránh dùng tiếng Tây trong khi diễn tả vấn đề. Trong ngữ cảnh này, từ 'ok' khá tối nghĩa. Tôi có đến 3-4 cách để hiểu.
Trường hợp ngoại lệ chỉ áp dụng cho từ tiếng Tây về khoa hộc, kỹ thuật, hoặc thương mại.

Vấn đề chính:
Công việc thanh toán nợ khách hàng (debtor clearance) này bạn đã thiết kế sai rồi. Trong bảng tính, thứ tự của dòng là biến số, ít khi là hằng.
Phân bổ (apportion) vào dòng và theo dõi bằng thứ tự dòng sẽ có ngày chết đứng. Muốn tham chiếu dòng thì mỗi dòng phải đặt ID từng dòng. Nếu có cách cho biết chắc chắn cái nào cũ hơn cái nào càng tốt.

...Số âm thì làm ngược lại nhé anh chị, chỉ xoá nợ những giá trị số âm. Em cũng chẳng biết vì lý do gì mà phần mềm của Đức thiết kế số âm thì dấu trừ nằm bên phải. (Việt Nam thì dấu trừ số âm nằm bên trái)...

Theo kiểu kế toán xưa, dấu trừ viết bên phải, số dương thì ở vị trí đó là dấu cách.
Cách viết này sẽ cho thấy rõ hơn độ lớn của số (phần triệu, ngàn, trăm,... ngay nhau), và chỉ cần liếc qua bên phải thì người ta thấy rõ nó là âm hay dương.
Cách viết dấu trừ bên trái có thể hơi khó đọc khi số chạy tùm lum từ 1-2 chữ số đếm 9-10 chữ số.

Theo kiểu kế toán mới, số âm đặt bên trong dấu ngoặc, số dương có một dấu cách ở sau, ứng với vị trí đóng dấu ngoặc của số âm.

 

[Tình nghĩa giang hồ]

Lưu ý: tránh dùng tiếng Tây trong khi diễn tả vấn đề. Trong ngữ cảnh này, từ 'ok' khá tối nghĩa. Tôi có đến 3-4 cách để hiểu.
Trường hợp ngoại lệ chỉ áp dụng cho từ tiếng Tây về khoa hộc, kỹ thuật, hoặc thương mại.

Vấn đề chính:
Công việc thanh toán nợ khách hàng (debtor clearance) này bạn đã thiết kế sai rồi. Trong bảng tính, thứ tự của dòng là biến số, ít khi là hằng.
Phân bổ (apportion) vào dòng và theo dõi bằng thứ tự dòng sẽ có ngày chết đứng. Muốn tham chiếu dòng thì mỗi dòng phải đặt ID từng dòng. Nếu có cách cho biết chắc chắn cái nào cũ hơn cái nào càng tốt.


Theo kiểu kế toán xưa, dấu trừ viết bên phải, số dương thì ở vị trí đó là dấu cách.
Cách viết này sẽ cho thấy rõ hơn độ lớn của số (phần triệu, ngàn, trăm,... ngay nhau), và chỉ cần liếc qua bên phải thì người ta thấy rõ nó là âm hay dương.
Cách viết dấu trừ bên trái có thể hơi khó đọc khi số chạy tùm lum từ 1-2 chữ số đếm 9-10 chữ số.

Theo kiểu kế toán mới, số âm đặt bên trong dấu ngoặc, số dương có một dấu cách ở sau, ứng với vị trí đóng dấu ngoặc của số âm.

Cảm ơn góp ý của anh VetMini .
Nhưng vấn đề này về bảng tính em không phải thiết kế để làm cho Excel anh.
VBA của mình có thể tương tác cho nhiều môi trường khác: Word, Outlok, AutoCard......
Trong trường hợp của em là em kết nối với SAP bằng VBA.
Mà ở SAP ERP thì người ta thiết kế dòng theo kiểu: có 9 dòng sẽ là 0,1,2,3,4,5,6,7,8

 

[VetMini]

...
Nhưng vấn đề này về bảng tính em không phải thiết kế để làm cho Excel anh.
...

Nói chuyện mâu thuẫn. Excel là phần mềm chuyên làm việc với bảng tính. Bảng tính mà không dùng Excel thì dùng cái gì? Google Sheets?

Khi tôi viết bài trên thì tôi đã dự đoán trước cái "nhưng" này.
Vì vậy, bài ấy thực ra không phải cho riêng bạn, mà tôi chỉ dẫn chung cho người làm bảng tính.

Chú thích: SAP không phải là đồ chơi. ERP quan trọng ở cái quy trình và giao diện chứ không ở phần mềm. Giao diện chúng thừa sức đưa cho bạn dữ liệu chính chắn.
Đưa cho bạn cái mớ dữ liệu hầm bà lằng ấy là do bên SAP và ERP lười biếng. Cũng có thể người ta hiểu lầm nhu cầu của bạn. Riêng bản thân tôi thì tin rằng người ta chả hiểu bạn muốn gì: ngay cả việc có dùng Excel để phân tích mớ dữ liệu ấy bạn cũng không chịu xác nhận.