Nhờ giúp đỡ code phân bổ số lượng theo không vượt quá con số cho trước?

[Hoàng Trọng Nghĩa]

OK bác cứ lấy cho em một ví dụ logic là được,cảm ơn bác.

Bài đã được tự động gộp: 29/8/21

Em thấy số bên bảng dữ liệu đang max=1500 vậy em sửa lại tiêu chuẩn trong khoảng 1600~1800 bác lấy file này giúp em nhé, mong muốn của em dùng code cho tiện không dùng công cụ có sẵn, các bác xem giúp em.

Ý của bạn số max dựa vào bảng này của sheet Tiêu Chuẩn?



Nếu vậy thì sao không insert thêm một cột để nó VLOOKUP số liệu này vào sheet Dữ Liệu luôn đi?

Ví dụ như:

 

[Ngày mai trời lại sáng]

Ý của bạn số max dựa vào bảng này của sheet Tiêu Chuẩn?

View attachment 265027

Nếu vậy thì sao không insert thêm một cột để nó VLOOKUP số liệu này vào sheet Dữ Liệu luôn đi?

Ví dụ như:

View attachment 265029

Đúng rồi sếp, insert thêm cũng được sẽ có nhiều cột phụ, dữ liệu chạy nhiều dòng sợ nặng không sếp, cột check và cột sum là em insert thêm để kiểm tra thôi về cơ bản chỉ có bảng dò tiêu chuẩn và bảng dữ liệu như vậy sếp ạ.

 

[Hoàng Trọng Nghĩa]

Đúng rồi sếp, insert thêm cũng được sẽ có nhiều cột phụ, dữ liệu chạy nhiều dòng sợ nặng không sếp, cột check và cột sum là em insert thêm để kiểm tra thôi về cơ bản chỉ có bảng dò tiêu chuẩn và bảng dữ liệu như vậy sếp ạ.

Code của nó như thế này nhé:

Sub PhanBo_HTN()
    Dim c As Byte
    Dim arrTemp, arrData
    Dim shData As Worksheet
    Dim blnCheck As Boolean
    Dim e As Long, r As Long, lngCol As Long, lngRow As Long
    Dim dblSoMax As Double, dblSoPhanBo As Double, dblThayDoi As Double, dblTemp As Double
    
    Set shData = Sheets("DU_LIEU")
    
    shData.AutoFilterMode = False
    
    e = shData.Range("B" & Rows.Count).End(xlUp).Row + 1
    
    arrTemp = shData.Range("D3:F" & e).Value
    arrData = shData.Range("G3:W" & e).Value
    
    lngRow = UBound(arrData, 1)
    lngCol = UBound(arrData, 2)
    
    For r = 1 To lngRow Step 2
        dblTemp = 0
        blnCheck = False
        dblSoMax = arrTemp(r, 1)
        dblSoPhanBo = arrTemp(r, 3)
        For c = 1 To lngCol
            If Not blnCheck Then
                dblTemp = dblTemp + (dblSoMax - arrData(r, c))
                If dblTemp > dblSoPhanBo Then
                    blnCheck = True
                    arrData(r + 1, c) = dblSoPhanBo - (dblTemp - dblSoMax)
                Else
                    arrData(r + 1, c) = dblSoMax
                End If
            Else
                arrData(r + 1, c) = arrData(r, c)
            End If
        Next
    Next
    
    shData.Range("G3:W" & e).Value = arrData
    shData.Range("A2:W2").AutoFilter
End Sub

 

[CHAOQUAY]

OK bác cứ lấy cho em một ví dụ logic là được,cảm ơn bác.

Bài đã được tự động gộp: 29/8/21

Em thấy số bên bảng dữ liệu đang max=1500 vậy em sửa lại tiêu chuẩn trong khoảng 1600~1800 bác lấy file này giúp em nhé, mong muốn của em dùng code cho tiện không dùng công cụ có sẵn, các bác xem giúp em.

Code dưới đây phân bổ theo số liệu cột E & F.
Số liệu phân bổ mới theo tiêu chí đồng biến với số liệu gốc nên có 1 số dòng có max giá trị lớn hơn max tiêu chuẩn
Tất cả các giá trị max sau phân bổ đều nằm trong phạm vi 1600- 1800.
Có 86 dòng lớn hơn max tiêu chuẩn, chênh lệch lớn hơn nhiều nhất so với max tiêu chuẩn là 150 đơn vị.

---
Công thức trong cột check của bạn có lẽ là chưa đúng.
Bài này nếu dùng solver phải kết hợp với VBA gọi nhiều lần cho từng dòng, có lẽ phức tạp hơn code trên nên không làm ở đây nhé bạn.

Option Explicit

Sub abc()
Dim Nguon, Spb
Dim TsDv
Dim Tong0, Tong1
Dim rws, cls
Dim i, j, k

With Sheet3
    rws = .Range("C" & Rows.Count).End(xlUp).Row
    Nguon = .Range("G3", "W" & rws)
    TsDv = .Range("E3", "F" & rws)
    rws = UBound(Nguon)
    cls = UBound(Nguon, 2)
    For i = 1 To rws - 1 Step 2
        Tong0 = TsDv(i, 1)
        Spb = TsDv(i, 2)
        Tong1 = Tong0 + Spb
        For j = 1 To cls
            Nguon(i + 1, j) = WorksheetFunction.Round((Nguon(i, j) / Tong0) * Tong1, 0)
        Next j
    Next i
    .Range("G3").Resize(UBound(Nguon), UBound(Nguon, 2)) = Nguon
End With
End Sub

 

[Hoàng Trọng Nghĩa]

Code dưới đây phân bổ theo số liệu cột E & F.
Số liệu phân bổ mới theo tiêu chí đồng biến với số liệu gốc nên có 1 số dòng có max giá trị lớn hơn max tiêu chuẩn
Tất cả các giá trị max sau phân bổ đều nằm trong phạm vi 1600- 1800.
Có 86 dòng lớn hơn max tiêu chuẩn, chênh lệch lớn hơn nhiều nhất so với max tiêu chuẩn là 150 đơn vị.

---
Công thức trong cột check của bạn có lẽ là chưa đúng.
Bài này nếu dùng solver phải kết hợp với VBA gọi nhiều lần cho từng dòng, có lẽ phức tạp hơn code trên nên không làm ở đây nhé bạn.

Option Explicit

Sub abc()
Dim Nguon, Spb
Dim TsDv
Dim Tong0, Tong1
Dim rws, cls
Dim i, j, k

With Sheet3
    rws = .Range("C" & Rows.Count).End(xlUp).Row
    Nguon = .Range("G3", "W" & rws)
    TsDv = .Range("E3", "F" & rws)
    rws = UBound(Nguon)
    cls = UBound(Nguon, 2)
    For i = 1 To rws - 1 Step 2
        Tong0 = TsDv(i, 1)
        Spb = TsDv(i, 2)
        Tong1 = Tong0 + Spb
        For j = 1 To cls
            Nguon(i + 1, j) = WorksheetFunction.Round((Nguon(i, j) / Tong0) * Tong1, 0)
        Next j
    Next i
    .Range("G3").Resize(UBound(Nguon), UBound(Nguon, 2)) = Nguon
End With
End Sub

Code của em cần chỉnh lại chút xíu nhé! Nó chênh lệch từ +/-3 đó.

 

[CHAOQUAY]

Code của em cần chỉnh lại chút xíu nhé! Nó chênh lệch từ +/-3 đó.

View attachment 265040

Chênh lệch này là đương nhiên thôi bạn, đây là do làm tròn của hàm ROUND() mà ra, cái này có thể điều chỉnh được.
Vấn đề là muốn chủ thớt cần đánh giá tiêu chí đồng biến của dữ liệu trước & sau phân bổ nên viết tạm như vậy. Nếu chốt tiêu chí phân bổ sẽ điều chỉnh code sau.

@3ii Bạn có thể dùng số liệu 1 dòng trước & sau phân bổ vẽ biểu đồ có lẽ sẽ thấy kết quả phân bổ cụ thể hơn

 

[Hoàng Trọng Nghĩa]

Chênh lệch này là đương nhiên thôi bạn, đây là do làm tròn của hàm ROUND() mà ra, cái này có thể điều chỉnh được.
Vấn đề là muốn chủ thớt cần đánh giá tiêu chí đồng biến của dữ liệu trước & sau phân bổ nên viết tạm như vậy. Nếu chốt tiêu chí phân bổ sẽ điều chỉnh code sau.

@3ii Bạn có thể dùng số liệu 1 dòng trước & sau phân bổ vẽ biểu đồ có lẽ sẽ thấy kết quả phân bổ cụ thể hơn

Đương nhiên là có thể chỉnh được, nhưng trong bài #43 mình nghĩ không cần phải dùng hàm round và nó tuyệt đối đúng trong trường hợp của file mẫu. Thấy nó có vẻ hơi rườm rà tí, nhưng nó chỉ tính vài cột trong tất cả các cột thôi, còn lại là nó bê xuống không tính toán gì thêm nên nó sẽ ít tốn thời gian hơn.

 

[Ngày mai trời lại sáng]

Hay quáy xin đa tạ 2 bác nhiều.
Số của em đều là số chẵn không có lẻ nên em nghĩ không phải ROUND, đơn giản là em mong muốn nó như bài 1 thôi sau đó để tìm giải pháp rồi mớt phát triển ra nhiều dữ liệu như vậy.
Cứ cộng trừ bình thường đến hết số phân bổ vào từng số mà không vượt qua giới hạn thì thôi. ROUND riếc em lại thấy vấn đề trở lên phức tạp.

 

[CHAOQUAY]

Hay quáy xin đa tạ 2 bác nhiều.
Số của em đều là số chẵn không có lẻ nên em nghĩ không phải ROUND, đơn giản là em mong muốn nó như bài 1 thôi sau đó để tìm giải pháp rồi mớt phát triển ra nhiều dữ liệu như vậy.
Cứ cộng trừ bình thường đến hết số phân bổ vào từng số mà không vượt qua giới hạn thì thôi. ROUND riếc em lại thấy vấn đề trở lên phức tạp.

Theo quan điểm cá nhân thì tỷ lệ giữa số liệu đặt & số liệu mua có lẽ là tương đối giống nhau cho các N của từng mã, code bài 44 viết theo quan điểm này.
Cách phân bổ của bài 44 là "dàn đều số liệu" không giống như cách bài 1 là dồn hết vào các N đầu tiên. Chọn cách nào tùy bạn, nhưng nếu chọn cách bài 44 thì phải sửa lại theo như bài 45 nhé bạn

 

[Ngày mai trời lại sáng]

Theo quan điểm cá nhân thì tỷ lệ giữa số liệu đặt & số liệu mua có lẽ là tương đối giống nhau cho các N của từng mã, code bài 44 viết theo quan điểm này.
Cách phân bổ của bài 44 là "dàn đều số liệu" không giống như cách bài 1 là dồn hết vào các N đầu tiên. Chọn cách nào tùy bạn, nhưng nếu chọn cách bài 44 thì phải sửa lại theo như bài 45 nhé bạn

À em đã hiểu, như vậy đây là phương pháp dàn đều cho tất cả các ngày, như vậy nó lẻ là đúng rồi, một ý tưởng cũng khá hay, cũng có lúc em sẽ cần đến phương án dàn kiểu này.
Nếu dàn đều thì bác có theo phương án chặn theo max không, nếu chặn được max hoặc min nữa thì cũng sẽ hay đấy.

 

[Hoàng Trọng Nghĩa]

Theo quan điểm cá nhân thì tỷ lệ giữa số liệu đặt & số liệu mua có lẽ là tương đối giống nhau cho các N của từng mã, code bài 44 viết theo quan điểm này.
Cách phân bổ của bài 44 là "dàn đều số liệu" không giống như cách bài 1 là dồn hết vào các N đầu tiên. Chọn cách nào tùy bạn, nhưng nếu chọn cách bài 44 thì phải sửa lại theo như bài 45 nhé bạn

Dàn đều tôi cũng đã tính tới trường hợp này, nhưng yêu cầu của tác giả là phân bổ đúng số lượng cho trước cho những ô đầu tiên đến ô cuối cùng. Song, nếu dàn đều đi chăng nữa thì cũng lưu ý đến 2 điều kiện, một là mỗi ô không được vượt quá số max, hai là tổng số phân bổ không được cao hơn hoặc thấp hơn số lượng phân bổ.

 

[Ngày mai trời lại sáng]

Dàn đều tôi cũng đã tính tới trường hợp này, nhưng yêu cầu của tác giả là phân bổ đúng số lượng cho trước cho những ô đầu tiên đến ô cuối cùng. Song, nếu dàn đều đi chăng nữa thì cũng lưu ý đến 2 điều kiện, một là mỗi ô không được vượt quá số max, hai là tổng số phân bổ không được cao hơn hoặc thấp hơn số lượng phân bổ.

Chính xác, hay sếp thêm giúp em một option này nữa nhỉ.

 

[Hoàng Trọng Nghĩa]

Chính xác, hay sếp thêm giúp em một option này nữa nhỉ.

Bạn hay quá ha, được voi đòi 2 bà Tưng! Để tôi suy nghĩ rồi làm luôn cho bạn, lỡ phóng lao rồi phải theo lao luôn!

 

[Ngày mai trời lại sáng]

Bạn hay quá ha, được voi đòi 2 bà Tưng! Để tôi suy nghĩ rồi làm luôn cho bạn, lỡ phóng lao rồi phải theo lao luôn!

Cảm ơn sếp, em ngồi đây chờ tin của sếp. Bởi đây là bài toán trong thực tế em sẽ gặp phải, nếu sếp đã lỡ phóng lao rồi sếp làm luôn giúp em trong code lấy thông tin từ bảng dò tiêu chuẩn theo mã luôn mà khỏi dùng cột phụ vlookup sếp à.

 

[Hoàng Trọng Nghĩa]

cảm ơn sếp, em ngồi đây chờ tin của sếp.. nếu sếp đã lỡ phóng lao rồi sếp làm luôn giúp em trong code lấy thông tin từ bảng dò tiêu chuẩn theo mã luôn mà khỏi dùng cột phụ vlookup sếp à.

Dò mệt lắm, để tôi dùng phương thức Find xem có nhanh không. Cho tôi hỏi, sau khi tính số phân bổ, nếu giả sử tổng số phân bổ có số dư ra thì trừ vào ô cuối trở về trước còn số thiếu thì cộng vào ô đầu đến ô cuối hay sao?

 

[Ngày mai trời lại sáng]

Dò mệt lắm, để tôi dùng phương thức Find xem có nhanh không. Cho tôi hỏi, sau khi tính số phân bổ, nếu giả sử tổng số phân bổ có số dư ra thì trừ vào ô cuối trở về trước còn số thiếu thì cộng vào ô đầu đến ô cuối hay sao?

Nếu số phân bổ sau khi đã dàn đều mà vẫn còn dư sếp để vào ngày cuối giúp em, số ngày cuối có thể vượt max(chấp nhận điều này để xác định tổng ban đầu + phân bổ), bởi các ngày sau chỉ là dụ tính (dự báo)., nên nếu phân bổ còn dư sếp dồn hết ngày vào cuối ạ.

 

[Hoàng Trọng Nghĩa]

Nếu số phân bổ sau khi đã dàn đều mà vẫn còn dư sếp để vào ngày cuối giúp em, số ngày cuối có thể vượt max(chấp nhận điều này để xác định tổng ban đầu + phân bổ), bởi các ngày sau chỉ là dụ tính (dự báo)., nên nếu phân bổ còn dư sếp dồn hết ngày vào cuối ạ.

Tôi còn 3 thắc mắc nữa là,
1) Bạn muốn làm tròn thế nào, VD: 9.9 thành 10 hay thành 9
2) Số phân bổ dàn đều chỉ trên số phân bổ hay tổng số đã có sẵn cộng với số phân bổ rồi chia đều?
3) Số dư phân bố cho số cuối bất chấp nó vượt max, trong khi những ô khác chưa đạt tới max thì sao?

 

[Ngày mai trời lại sáng]

Tôi còn 3 thắc mắc nữa là,
1) Bạn muốn làm tròn thế nào, VD: 9.9 thành 10 hay thành 9
2) Số phân bổ dàn đều chỉ trên số phân bổ hay tổng số đã có sẵn cộng với số phân bổ rồi chia đều?
3) Số dư phân bố cho số cuối bất chấp nó vượt max, trong khi những ô khác chưa đạt tới max thì sao?

Công nhận sếp nhìn xa thật,em chưa lường hết được các vấn đề này nhưng cũng sẽ đến lúc va phải, em giải thích theo từng mục câu hỏi của sếp nhưa sau:
1) Sếp cứ để lẻ 9.9 cho em cũng được, dàn đều chấp nhận lẻ ạ để đảm bảo tổng không thay đổi.
2) Số phân bổ dàn đều chỉ lấy số phân bổ để dàn đều cho các ngày, còn các số của các ngày có ban đầu theo kế hoạch đã định rồi sẽ giữ nguyên.
3 ) Nếu số dư phân bổ sau khi dàn đều mà còn dư sẽ phân bổ lại từ đầu (nghĩa là thêm vòng nữa, cứ nhủ vậy lặp cho đến khi nào hết số phân bổ) để kiểm tra xem số nào chưa đạt max thì sẽ phân bổ tiếp vào số đó.. sau khi tất cả đạt max hết thì mới đổ hết vào số cuối cùng , có lẽ đoạn này phải Do rồi sếp nhỉ.

 

[Hoàng Trọng Nghĩa]

Công nhận sếp nhìn xa thật,em chưa lường hết được các vấn đề này nhưng cũng sẽ đến lúc va phải, em giải thích theo từng mục câu hỏi của sếp nhưa sau:
1) Sếp cứ để lẻ 9.9 cho em cũng được, dàn đều chấp nhận lẻ ạ để đảm bảo tổng không thay đổi.
2) Số phân bổ dàn đều chỉ lấy số phân bổ để dàn đều cho các ngày, còn các số của các ngày có ban đầu theo kế hoạch đã định rồi sẽ giữ nguyên.
3 ) Nếu số dư phân bổ sau khi dàn đều mà còn dư sẽ phân bổ lại từ đầu (nghĩa là thêm vòng nữa, cứ nhủ vậy lặp cho đến khi nào hết số phân bổ) để kiểm tra xem số nào chưa đạt max thì sẽ phân bổ tiếp vào số đó.. sau khi tất cả đạt max hết thì mới đổ hết vào số cuối cùng

Nếu để số lẻ thì khi định dạng kiểu "0" hay "#,##0" (nhìn giống như số nguyên) sẽ có hiện tượng khi in ra giấy tổng số thì bằng số phân bổ, nhưng cộng thủ công từng ô sẽ bị chênh lệch với số tổng ráng chịu đó.

 

[Ngày mai trời lại sáng]

Nếu để số lẻ thì khi định dạng kiểu "0" hay "#,##0" (nhìn giống như số nguyên) sẽ có hiện tượng khi in ra giấy tổng số thì bằng số phân bổ, nhưng cộng thủ công từng ô sẽ bị chênh lệch với số tổng ráng chịu đó.

Em hiểu chỗ này vậy sếp đừng format là được , cứ để nguyên bản lẻ.. khi cần dùng số em có thể balance rồi làm tròn cũng được sếp ạ, đoạn này gần bờ nên sếp cứ để em tự bơi.