Tìm các số cộng lại thỏa điều kiện

thao nguyen01 đã viết:

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 ạ.

Nhấp chuột vào đây để mở rộng...

thao nguyen01 đã viết:

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 ạ.

Nhấp chuột vào đây để mở rộng...

Hoàng Tuấn 868 đã viết:

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.

Nhấp chuột vào đây để mở rộng...

Phuocam đã viết:

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

Nhấp chuột vào đây để mở rộng...

SA_DQ đã viết:

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
:

PHP:Sao chép.

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

Nhấp chuột vào đây để mở rộng...

thao nguyen01 đã viết:

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 ạ.

Nhấp chuột vào đây để mở rộng...

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

LmoovoenX đã viết:

Mã:Sao chép.

=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.

Nhấp chuột vào đây để mở rộng...

thao nguyen01 đã viết:

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 ạ.

Nhấp chuột vào đây để mở rộng...

thao nguyen01 đã viết:

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 ạ).

Nhấp chuột vào đây để mở rộng...

LmoovoenX đã viết:

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.

Nhấp chuột vào đây để mở rộng...

thao nguyen01 đã viết:

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 ạ

Nhấp chuột vào đây để mở rộng...

LmoovoenX đã viết:

Ở 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.

Nhấp chuột vào đây để mở rộng...

thao nguyen01 đã viết:

Dạ, em cảm ơn anh nha

Nhấp chuột vào đây để mở rộng...

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

LmoovoenX đã viết:

Mã:Sao chép.

=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.

Nhấp chuột vào đây để mở rộng...