Tìm các số cộng lại thỏa điều kiện (3 người xem)

[thao nguyen01]

Kính gửi anh/chị trên diễn đàn,

Em muốn tìm các số cộng lại ở cột B thỏa điều kiện ô H1. Bài này em dùng solver không được ạ. Vì dữ liệu thực tế quá nhiều, solver bị giới hạn ạ. Anh/chị xem và hỗ trợ giúp em bài này với ạ. (Hàm hay code VBA đều được ạ). Kết quả mong muốn của em ở ô H3 và H4 ạ .
Em cảm ơn anh/chị nhiều ạ.

 

[Hoàng Tuấn 868]

Kính gửi anh/chị trên diễn đàn,

Em muốn tìm các số cộng lại ở cột B thỏa điều kiện ô H1. Bài này em dùng solver không được ạ. Vì dữ liệu thực tế quá nhiều, solver bị giới hạn ạ. Anh/chị xem và hỗ trợ giúp em bài này với ạ. (Hàm hay code VBA đều được ạ). Kết quả mong muốn của em ở ô H3 và H4 ạ .
Em cảm ơn anh/chị nhiều ạ.

Không thấy diễn giải điều kiện là gì. Bao nhiêu số thì thỏa mãn... Chán hẳn, bỏ luôn.

 

[Phuocam]

Kính gửi anh/chị trên diễn đàn,

Em muốn tìm các số cộng lại ở cột B thỏa điều kiện ô H1. Bài này em dùng solver không được ạ. Vì dữ liệu thực tế quá nhiều, solver bị giới hạn ạ. Anh/chị xem và hỗ trợ giúp em bài này với ạ. (Hàm hay code VBA đều được ạ). Kết quả mong muốn của em ở ô H3 và H4 ạ .
Em cảm ơn anh/chị nhiều ạ.

Bao nhiêu ô cũng được hay hạn chế số ô?

 

[thao nguyen01]

Không thấy diễn giải điều kiện là gì. Bao nhiêu số thì thỏa mãn... Chán hẳn, bỏ luôn.

Dạ, số liệu thực tế của em thường chỉ có 1 kết quả duy nhất thỏa và kết quả sẽ là 2 ô cộng lại. Nhưng nếu có thể các anh có thể viết code VBA hay mở rộng điều kiện giúp em. Trường hợp em gặp đôi khi có thể là 3 hay 4 số cộng lại thỏa ô H1 ạ.

Bài đã được tự động gộp: 27/7/24

Bao nhiêu ô cũng được hay hạn chế số ô?

Dạ, với bài này của em là 2 ô cộng lại ạ. Nhưng nếu được anh có thể hướng dẫn giúp em trường hợp tổng quát giúp em ạ (có thể 3-4 số cộng lại hay tùy chỉnh code được ạ).

 

[SA_DQ]

Cặp đáp án đầu tiên tìm thấy bằng với tổng cho trước được tìm thấy bỡi macro sau
:

Sub TimCapSoCua1Tong()
 Dim Rws As Long, J As Long, Hieu As Long, Tong As Long, Num1 As Long
 Dim Rng As Range, sRng As Range
 Dim fAddr As String
 
 Rws = [B2].End(xlDown).Row
 Tong = [F1].Value
 For J = 2 To Rws
    Set Rng = Cells(J + 1, "B").Resize(Rws)
    Num1 = Cells(J, "B").Value:         fAddr = Cells(J, "B").Address
    Hieu = Tong - Num1
    Set sRng = Rng.Find(Hieu, , xlFormulas, xlWhole)
    If Not sRng Is Nothing Then
        MsgBox sRng.Address & " " & sRng.Value, , Num1 & " " & fAddr
        Exit For
    End If
 Next J
End Sub

 

[thao nguyen01]

Cặp đáp án đầu tiên tìm thấy bằng với tổng cho trước được tìm thấy bỡi macro sau
:

Sub TimCapSoCua1Tong()
 Dim Rws As Long, J As Long, Hieu As Long, Tong As Long, Num1 As Long
 Dim Rng As Range, sRng As Range
 Dim fAddr As String
 
 Rws = [B2].End(xlDown).Row
 Tong = [F1].Value
 For J = 2 To Rws
    Set Rng = Cells(J + 1, "B").Resize(Rws)
    Num1 = Cells(J, "B").Value:         fAddr = Cells(J, "B").Address
    Hieu = Tong - Num1
    Set sRng = Rng.Find(Hieu, , xlFormulas, xlWhole)
    If Not sRng Is Nothing Then
        MsgBox sRng.Address & " " & sRng.Value, , Num1 & " " & fAddr
        Exit For
    End If
 Next J
End Sub

Dạ. Con cảm ơn Bác ạ.

 

[LmoovoenX]

Kính gửi anh/chị trên diễn đàn,

Em muốn tìm các số cộng lại ở cột B thỏa điều kiện ô H1. Bài này em dùng solver không được ạ. Vì dữ liệu thực tế quá nhiều, solver bị giới hạn ạ. Anh/chị xem và hỗ trợ giúp em bài này với ạ. (Hàm hay code VBA đều được ạ). Kết quả mong muốn của em ở ô H3 và H4 ạ .
Em cảm ơn anh/chị nhiều ạ.

=AGGREGATE(15;6;ROW(B2:B4000)+10^9*{2\3\4}/(SUBTOTAL(9;OFFSET(B1;ROW(B1:B4000);;{2\3\4}))=F1);1)

Hàm này ra kết quả 2000000143
Số 2 đầu tiên: là số số hạng liên tiếp.
Số 143: là vị trí của số hạng đầu tiên.

 

[thao nguyen01]

=AGGREGATE(15;6;ROW(B2:B4000)+10^9*{2\3\4}/(SUBTOTAL(9;OFFSET(B1;ROW(B1:B4000);;{2\3\4}))=F1);1)

Hàm này ra kết quả 2000000143
Số 2 đầu tiên: là số số hạng liên tiếp.
Số 143: là vị trí của số hạng đầu tiên.

Dạ. Em chưa hiểu cách đọc kết quả ạ. Số 143 là 1000000, còn số 2 đầu tiên em chưa hiểu ạ. Anh hướng dẫn thêm giúp em với ạ.
Hàm trên khi lên máy em, em chuyển ";" thành "," thì báo lỗi: "There is a problem with this formula" ạ. Em không biết em sai chỗ nào ạ.

 

[LmoovoenX]

Dạ. Em chưa hiểu cách đọc kết quả ạ. Số 143 là 1000000, còn số 2 đầu tiên em chưa hiểu ạ. Anh hướng dẫn thêm giúp em với ạ.
Hàm trên khi lên máy em, em chuyển ";" thành "," thì báo lỗi: "There is a problem with this formula" ạ. Em không biết em sai chỗ nào ạ.

Bạn thay dấu \ trong 2 dấu ngoặc nhọn {} thành dấu , nhé.

Dạ, với bài này của em là 2 ô cộng lại ạ. Nhưng nếu được anh có thể hướng dẫn giúp em trường hợp tổng quát giúp em ạ (có thể 3-4 số cộng lại hay tùy chỉnh code được ạ).

Vì bạn muốn tính thêm 3 hoặc 4 số nên mình mới thêm đoạn {2,3,4}.
Như hàm ở trên, nếu ra kết quả là 3000000134, tức là có 3 số liên tiếp, bắt đầu từ vị trí B134 (B134,B135,B136) có tổng bằng số ở ô F1.

 

[thao nguyen01]

Bạn thay dấu \ trong 2 dấu ngoặc nhọn {} thành dấu , nhé.

Vì bạn muốn tính thêm 3 hoặc 4 số nên mình mới thêm đoạn {2,3,4}.
Như hàm ở trên, nếu ra kết quả là 3000000134, tức là có 3 số liên tiếp, bắt đầu từ vị trí B134 (B134,B135,B136) có tổng bằng số ở ô F1.

Dạ, em cảm ơn anh ạ. Em ra kết quả là 2000000143 rồi ạ. Nhưng anh cho em hỏi trường hợp nếu kết quả không phải ở các vị trí liên tiếp nhau thì công thức còn đúng nữa không ạ? Em ví dụ: vị trí số thứ nhất là 32, số thứ 2 là 140 ạ

 

[LmoovoenX]

Dạ, em cảm ơn anh ạ. Em ra kết quả là 2000000143 rồi ạ. Nhưng anh cho em hỏi trường hợp nếu kết quả không phải ở các vị trí liên tiếp nhau thì công thức còn đúng nữa không ạ? Em ví dụ: vị trí số thứ nhất là 32, số thứ 2 là 140 ạ

Ở trên mình đọc không kỹ tưởng bạn nói các số cần cộng liên tiếp nhau.
Nếu cần tìm các số không liên tiếp nhau, chỉ có 2 số thì mình làm được. 3 số trở lên thì chưa biết.
Dữ liệu bạn nhiều quá thì cũng không nên dùng hàm nhé. Thử các phương án của các bác trên.

 

[thao nguyen01]

Ở trên mình đọc không kỹ tưởng bạn nói các số cần cộng liên tiếp nhau.
Nếu cần tìm các số không liên tiếp nhau, chỉ có 2 số thì mình làm được. 3 số trở lên thì chưa biết.
Dữ liệu bạn nhiều quá thì cũng không nên dùng hàm nhé. Thử các phương án của các bác trên.

Dạ, em cảm ơn anh nha

 

[LmoovoenX]

Dạ, em cảm ơn anh nha

=AGGREGATE(15,6,ROW($B$2:$B$4000)/(($B$2:$B$4000+TRANSPOSE($B$2:$B$4000))=$F$1),ROW(A1))

Như ở đây mình chỉ lấy 4000 dòng, mất khoảng 5 giây để chạy.
Lưu ý công thức trên chỉ đúng trong trường hợp:
1. cặp số có tổng bằng F1 là duy nhất
2. Nếu có 1 số hạng nào đó trong b2:b4000 bằng 1 nửa F1, công thức cũng sai.
Bạn nên có thêm hàm để lấy 2 số hạng tương ứng ra để kiểm tra. Ví dụ Index.

 

[thao nguyen01]

=AGGREGATE(15,6,ROW($B$2:$B$4000)/(($B$2:$B$4000+TRANSPOSE($B$2:$B$4000))=$F$1),ROW(A1))

View attachment 302729
Như ở đây mình chỉ lấy 4000 dòng, mất khoảng 5 giây để chạy.
Lưu ý công thức trên chỉ đúng trong trường hợp:
1. cặp số có tổng bằng F1 là duy nhất
2. Nếu có 1 số hạng nào đó trong b2:b4000 bằng 1 nửa F1, công thức cũng sai.
Bạn nên có thêm hàm để lấy 2 số hạng tương ứng ra để kiểm tra. Ví dụ Index.

dạ, em cảm ơn anh

 

[SA_DQ]

Mình có cặp macro rùa (Hơn nữa phút) để tìm ra ba ô đầu tiên có tổng bằng với 1 số cho trước
Mời các bác xem & giúp cải thiện tốc độ

 

[SA_DQ]

Còn nêu ta tiến hành xếp theo chiều giảm dần của cột 'B' thì macro chi tốn chưa tới 1 gy là xong!
Sau khi tìm hiểu thì thấy rằng cột 'B' này chứa hầu hết là số 1, nên mới vậy!

Ngày 30/07: Tạo 1 macro & dữ liệu để tìm 3 số có tổng bằng với số cho trước