Giúp thay đổi dữ liệu theo điều kiện tổng (1 người xem)

[tueyennhi]

Em có bài toán như sau mong mọi người giúp đỡ:

Tại vùng dữ liệu từ cột BK đến CO ta có số liệu tổng là cột CP.

- Giả sử tại dòng 4: CP4=BJ4 thì ta giữ nguyên dữ liệu từ cột BK đến CO không thay đổi.

- Nếu dòng bất kỳ của cột CP >= dòng tương ứng cột BJ thì ta thay đổi số liệu trong cột bất kỳ từ BK đến CO của dòng đó sao cho tổng mới của CP <=BJ (Nếu bằng thì tốt quá vì em sợ như thế khó, còn nếu không được thì nhỏ hơn một chút xíu cũng được chỉ cần không chênh lệch quá).

- Số liệu thay đổi tại cell so với dữ liệu cũ của chính cell đó chỉ được phép nhỏ hơn và là số >=0, không được phép lớn hơn dữ liệu cũ..

"Bài toán này nếu vba excel có thể làm được thì thực sự kinh ngạc về khả năng của nó và em có thể học hỏi lên một bài học quý giá!"

*Theo gợi ý của bạn Befaint em biết thêm về Solver thì thấy chạy rất tốt nhưng chưa biết cách áp dụng để chạy cho toàn bộ bảng tính một lúc.

Em cảm ơn ạ!

 

[befaint]

Từ khóa: Solver

 

[tueyennhi]

Từ khóa: Solver

Mình đã diễn giải và up lại file ở bài 1 dễ hiểu hơn befaint nhé. Solver mình cũng đang tìm hiểu, trong khi đó nếu mọi người có đáp án rồi thì giúp em luôn ạ.

 

[tueyennhi]

Befaint ơi có thể gợi ý mình thêm được không. Mình đọc về Solver rồi mà không hiểu lắm

 

[tueyennhi]

Mình có đọc về Solver tại http://bis.net.vn/forums/t/500.aspx
Hình như nó chỉ tối ưu cho từng dòng một còn của mình là một vùng dữ liệu so với một cột tổng đối chiếu. Và luôn có hàm mục tiêu cho toàn bộ bảng tính, của mình thì mỗi dòng một mục tiêu riêng.
Mình lập thử Solver thì nó báo có quá nhiều cell và không xử lý.

Tại $C$P2 mình để công thức bắt trước trên mạng thì là
=+SUMPRODUCT($BK$4:$CO$4205,$H$4:$AL$4205)

Mình cũng đã đọc Solver trên giaiphapexcel mà chưa hiểu vấn đề, rất mong mọi người chỉ giúp. Dùng vba hay solver đều được miễn sao kết quả như mong muốn mình đề ra.

 

[tueyennhi]

Trong khi chờ giải pháp về VBA mình up lên file sử dụng solver (mới tính cho một dòng) và thỏa mãn điều kiện đặt ra. Mọi người tham khảo giúp mình làm sao áp dụng cho toàn bộ các dòng nhé.

Mình thử ghi Macro để xử lý hàng loạt mà nhìn đoạn mã nó ghi lại @@, mờ mắt luôn.

 

[NguyenNgocThuHien]

Như thế này

 

[tueyennhi]

Có vẻ như giải pháp Solver với vướng mắc hàm giằng buộc với một địa chỉ cell duy nhất khiến cho việc xử lý dữ liệu của một bảng tính là bất khả thi khi mà mỗi dòng là riêng biệt không liên quan đến các dòng khác.

 

[NguyenNgocThuHien]

xin lỗi vì dùng điện thoại nên copy liink không chính xác. lúc nào online bằng máy tính mình gửi lại. nó là một video trên fb của mình thôi. nhờ bạn xóa hộ link không hợp lệ trên với

 

[tueyennhi]

xin lỗi vì dùng điện thoại nên copy liink không chính xác. lúc nào online bằng máy tính mình gửi lại. nó là một video trên fb của mình thôi. nhờ bạn xóa hộ link không hợp lệ trên với

Mình mày mò cả buổi thì tựa chung thấy các bài toán dùng Solver đều dựa theo một hàm tiêu chuẩn mà hàm này bao quát toàn bộ bảng tính. Trường hợp của mình mỗi dòng có một tiêu chuẩn riêng cho nên hiện tại mới xây dựng chạy cho một dòng nhất định. Nếu chạy dòng khác phải sửa thông số dòng tương ứng.

Sau một hồi vật vã thì cũng đã có code VBA đạt được yêu cầu cho dòng 4. Vậy để làm cho toàn bộ các dòng còn lại thì code em cần thay đổi cái gì và tốc độ xử lý có khả quan không mong mọi người chỉ giúp.

Sub Solver()

SolverReset
SOLVERok "$CP$4", 1, 0, "$BK$4:$CO$4"
SolverAdd "$CP$4", 1, "$BJ$4"
SolverAdd "$BK$4:$CO$4", 1, "$H$4:$AL$4"
SolverSolve

End Sub

 

[befaint]

- Giả sử tại dòng 4: CP4=BJ4 thì ta giữ nguyên dữ liệu từ cột BK đến CO không thay đổi.
- Nếu dòng bất kỳ của cột CP >= dòng tương ứng cột BJ thì ta thay đổi số liệu trong cột bất kỳ từ BK đến CO của dòng đó sao cho tổng mới của CP <=BJ

Hai trường hợp đều có "=", vậy làm thế nào?
Trường hợp CP(i)<BJ(i) thì thế nào?
Solver áp dụng từng dòng cần tính theo điều kiện trên là được.

- Công thức cho cột CQ: CQ4=CP4>BJ4 rồi lọc ở cột CQ những cái = True.
- Bỏ gộp ô ở dòng 2 đi.
- Công thức cho CR59:

CR59=SUMPRODUCT(BK59:CO59,$BK$2:$CO$2)

- Thiết lập trong Solver như hình.
- Run Solver.
- Được kết quả ở dòng 2 thì nhân với dòng 59 là giá trị cần cần.
(Làm tương tự cho dòng còn lại, hoặc Record macro).

 

[tueyennhi]

Hai trường hợp đều có "=", vậy làm thế nào?
Trường hợp CP(i)<BJ(i) thì thế nào?
Solver áp dụng từng dòng cần tính theo điều kiện trên là được.

- Công thức cho cột CQ: CQ4=CP4>BJ4 rồi lọc ở cột CQ những cái = True.
- Bỏ gộp ô ở dòng 2 đi.
- Công thức cho CR59:

CR59=SUMPRODUCT(BK59:CO59,$BK$2:$CO$2)

- Thiết lập trong Solver như hình.
- Run Solver.
- Được kết quả ở dòng 2 thì nhân với dòng 59 là giá trị cần cần.
(Làm tương tự cho dòng còn lại, hoặc Record macro).

View attachment 183996

Mình record và rút gọn được code như bài #10 nhưng chưa biết làm thế nào để chạy cho tất cả các dòng.

 

[NguyenNgocThuHien]

Hôm trước dán link nhầm:
https://www.facebook.com/permalink.php?story_fbid=616965065360375&id=100011406609466

 

[tueyennhi]

Hôm trước dán link nhầm:
https://www.facebook.com/permalink.php?story_fbid=616965065360375&id=100011406609466

Link này chỉ mình bạn mở được thôi

 

[tueyennhi]

Anh chị em nào có lời giải cho em không??? Em bó tay rồi, Solver hay nhưng chạy từng dòng 1 nên không khả quan, cũng không biết làm thế nào để thay đổi code trong file đính kèm sao cho có thể áp dụng cùng với VBA để xử lý cho toàn bộ bảng tính. Anh chị em bấm click để ra kết quả từ BK4 đến CO4. Nếu tạo được vòng lặp để chạy từ đầu đến cuối bằng vba thì tốt quá.

 

[HieuCD]

Anh chị em nào có lời giải cho em không??? Em bó tay rồi, Solver hay nhưng chạy từng dòng 1 nên không khả quan, cũng không biết làm thế nào để thay đổi code trong file đính kèm sao cho có thể áp dụng cùng với VBA để xử lý cho toàn bộ bảng tính. Anh chị em bấm click để ra kết quả từ BK4 đến CO4. Nếu tạo được vòng lặp để chạy từ đầu đến cuối bằng vba thì tốt quá.

Chay thử code

Sub GPE()
Dim Arr As Variant, i As Long, j As Integer, k1 As Integer, k2 As Integer, R As Double, D As Double
Arr = Range("BK4:CO" & Range("BK" & Rows.Count).End(xlUp).Row).Value
col = UBound(Arr, 2)
For i = 1 To UBound(Arr)
  R = Int(100 * Range("CP" & i + 3).Value - 100 * Range("BJ" & i + 3).Value)
  If R > 0 Then
    k1 = 0
    For j = 1 To col
      If Arr(i, j) = 0 Then k1 = k1 + 1
    Next j
    D = Int(R / (col - k1)) / 100
    R = 0
    k2 = 0
    For j = 1 To col
      If Arr(i, j) > 0 Then
        k2 = k2 + 1
        If k2 = col - k1 Then
          Arr(i, j) = Range("BJ" & i + 3).Value - R
          Exit For
        Else
          Arr(i, j) = Arr(i, j) - D
          R = R + Arr(i, j)
        End If
      End If
    Next j
  End If
Next i
Range("BK4").Resize(UBound(Arr), col) = Arr
End Sub

 

[tueyennhi]

Chay thử code

Sub GPE()
Dim Arr As Variant, i As Long, j As Integer, k1 As Integer, k2 As Integer, R As Double, D As Double
Arr = Range("BK4:CO" & Range("BK" & Rows.Count).End(xlUp).Row).Value
col = UBound(Arr, 2)
For i = 1 To UBound(Arr)
  R = Int(100 * Range("CP" & i + 3).Value - 100 * Range("BJ" & i + 3).Value)
  If R > 0 Then
    k1 = 0
    For j = 1 To col
      If Arr(i, j) = 0 Then k1 = k1 + 1
    Next j
    D = Int(R / (col - k1)) / 100
    R = 0
    k2 = 0
    For j = 1 To col
      If Arr(i, j) > 0 Then
        k2 = k2 + 1
        If k2 = col - k1 Then
          Arr(i, j) = Range("BJ" & i + 3).Value - R
          Exit For
        Else
          Arr(i, j) = Arr(i, j) - D
          R = R + Arr(i, j)
        End If
      End If
    Next j
  End If
Next i
Range("BK4").Resize(UBound(Arr), col) = Arr
End Sub

Tuyệt vời!!!! Cảm ơn bạn HieuCD rất rất nhiều
Nếu có thể bạn diễn giải cho mình ý nghĩa các dòng lệnh được không? Ví dụ tại sao lại là
R = Int(100 * Range("CP" & i + 3).Value - 100 * Range("BJ" & i + 3).Value) mà không phải là
R = Int( Range("CP" & i + 3).Value - Range("BJ" & i + 3).Value)

Ngoài ra tại CN4 mình thử đổi giá trị thành 100 và chạy code thì kết quả thu được xuất hiện các giá trị âm. Cái này lỗi ở mình không nói đủ là kết quả phải luôn luôn >=0. Theo mình hiểu thì tất cả các giá trị ô >0 sẽ bị trừ một số (Mà số này là kết quả của chênh lệch giữa CP với BJ chia cho tổng số ô chứa giá trị >0).

Mình đang nghĩ một cách khắc phục là nếu ô nào trừ mà bị âm thì ô đó sẽ giữ nguyên còn số trừ sẽ chia tiếp trung bình để trừ cho toàn bộ các ô còn lại. Lặp lại nếu như tiếp tục xuất hiện các ô khác. Phương pháp này không hẳn là tối ưu và chính xác tuyệt đối nhưng mình chưa nghĩ ra thêm. Code bạn viết mình vẫn mơ hồ kiểu như nhìn thấy nhưng chưa chạm tới được nên cũng chỉ nghĩ ra ý đồ thế mà chưa biết phải viết như nào.

*P/s: Mình vừa thử với trường hợp ở dòng 3513 khi mình chạy với Solver ở trong bài #10 thì thấy ý đồ nó rất hay đó là nếu CP > BJ thì đếm toàn bộ các ô có giá trị lớn hơn 0 (mình giả sử là 15) sau đó lấy số tại cột BJ chia cho 15 (Giả sử tại BJ là 30), ta được kết quả là 2. Gán giá trị 2 vào 15 ô này. Khá là hay và đảm bảo kết quả các cột luôn luôn là số dương. Nhược điểm cách viết này là các số không được "tự nhiên" so với cách viết của bạn.

 

[HieuCD]

Tuyệt vời!!!! Cảm ơn bạn HieuCD rất rất nhiều
Nếu có thể bạn diễn giải cho mình ý nghĩa các dòng lệnh được không? Ví dụ tại sao lại là
R = Int(100 * Range("CP" & i + 3).Value - 100 * Range("BJ" & i + 3).Value) mà không phải là
R = Int( Range("CP" & i + 3).Value - Range("BJ" & i + 3).Value)

Ngoài ra tại CN4 mình thử đổi giá trị thành 100 và chạy code thì kết quả thu được xuất hiện các giá trị âm. Cái này lỗi ở mình không nói đủ là kết quả phải luôn luôn >=0. Theo mình hiểu thì tất cả các giá trị ô >0 sẽ bị trừ một số (Mà số này là kết quả của chênh lệch giữa CP với BJ chia cho tổng số ô chứa giá trị >0).

theo file, mình qui định các số chỉ có 2 số lẽ, giá trị giảm mình làm tròn xuống lấy 2 số lẽ, nên nhân cho 100 và dùng hàm int để làm tròn xuống hàng đơn vị, sao đó có lệnh chia lại cho 100
khi rảnh mình sẽ viết tiếp loại số âm

 

[tueyennhi]

theo file, mình qui định các số chỉ có 2 số lẽ, giá trị giảm mình làm tròn xuống lấy 2 số lẽ, nên nhân cho 100 và dùng hàm int để làm tròn xuống hàng đơn vị, sao đó có lệnh chia lại cho 100
khi rảnh mình sẽ viết tiếp loại số âm

Ừm, cảm ơn bạn nhé, mình đợi tin

 

[TheThienChu]

Em có bài toán như sau mong mọi người giúp đỡ:

Tại vùng dữ liệu từ cột BK đến CO ta có số liệu tổng là cột CP.

- Giả sử tại dòng 4: CP4=BJ4 thì ta giữ nguyên dữ liệu từ cột BK đến CO không thay đổi.

- Nếu dòng bất kỳ của cột CP >= dòng tương ứng cột BJ thì ta thay đổi số liệu trong cột bất kỳ từ BK đến CO của dòng đó sao cho tổng mới của CP <=BJ (Nếu bằng thì tốt quá vì em sợ như thế khó, còn nếu không được thì nhỏ hơn một chút xíu cũng được chỉ cần không chênh lệch quá).

- Số liệu thay đổi tại cell so với dữ liệu cũ của chính cell đó chỉ được phép nhỏ hơn và là số >=0, không được phép lớn hơn dữ liệu cũ..

"Bài toán này nếu vba excel có thể làm được thì thực sự kinh ngạc về khả năng của nó và em có thể học hỏi lên một bài học quý giá!"

*Theo gợi ý của bạn Befaint em biết thêm về Solver thì thấy chạy rất tốt nhưng chưa biết cách áp dụng để chạy cho toàn bộ bảng tính một lúc.

Em cảm ơn ạ!

Bạn thử chạy cái này xem sao

Option Explicit

Public Sub TueYenNhi()
Dim DArr, Res, MySum
Dim i As Long, j As Long, k

With Sheet4
Res = .Range("BJ4", .Range("BJ4").End(xlDown))
MySum = .Range("CP4", .Range("CP4").End(xlDown))
DArr = .Range("BK4", "CO" & .Range("CP1000000").End(xlUp).Row)
For i = 1 To UBound(MySum)
    If MySum(i, 1) > Res(i, 1) Then
        k = Res(i, 1) / MySum(i, 1)
            For j = 1 To UBound(DArr, 2)
                DArr(i, j) = DArr(i, j) * k
            Next j
    End If
Next i
.Range("BK4").Resize(UBound(DArr), UBound(DArr, 2)) = DArr
End With
End Sub