Tìm các dòng có chứa giá trị cộng tổng sấp xỉ bằng số cho trước

[tienhuy832011]

Chào các bác, em làm số liệu exel cho công ty , hiện tại có file exel muốn tìm các dòng trong cột C và đánh dấu màu tại các ô có giá trị ở cột C sao cho tổng các giá trị đó lại xấp xỉ cho phép chênh lệch =, hoặc lớn hơn ,hoặc nhỏ hơn từ 1 đồng đến 200 đồng so với giá trị E2. (Tổng các dòng trong cột C>, hoặc bằng, hoặc < E2 chênh lệch từ 1 đồng đến 200 đồng thì đánh dấu mầu vào các ô ở cột C) .Mong các cao nhân giúp em chứ tìm bằng mắt thường trên exel so với giấy tờ thì hơi oải ạ

 

[Duy Tùng 9x]

Chào các bác, em làm số liệu exel cho công ty , hiện tại có file exel muốn tìm các dòng trong cột C và đánh dấu màu tại các ô có giá trị ở cột C sao cho tổng các giá trị đó lại xấp xỉ cho phép chênh lệch =, hoặc lớn hơn ,hoặc nhỏ hơn từ 1 đồng đến 200 đồng so với giá trị E2. (Tổng các dòng trong cột C>, hoặc bằng, hoặc < E2 chênh lệch từ 1 đồng đến 200 đồng thì đánh dấu mầu vào các ô ở cột C) .Mong các cao nhân giúp em chứ tìm bằng mắt thường trên exel so với giấy tờ thì hơi oải ạ

Hàm excel mình từng thử với dạng này, nhưng số nhỏ và data ít còn chạy. Lớn là sập luôn. Nên mình nghĩ nếu bạn dùng được python thì cũng là 1 giải pháp, có lẽ không tồi đâu.

 

[tienhuy832011]

View attachment 305897
Hàm excel mình từng thử với dạng này, nhưng số nhỏ và data ít còn chạy. Lớn là sập luôn. Nên mình nghĩ nếu bạn dùng được python thì cũng là 1 giải pháp, có lẽ không tồi đâu.

mình lại không biết cách dùng này , số liệu của mình chỉ tầm 20 đến 30 dòng đổ lại thôi , bạn có thể chỉ mình cách chạy lệnh này được không

 

[tienhuy832011]

View attachment 305897
Hàm excel mình từng thử với dạng này, nhưng số nhỏ và data ít còn chạy. Lớn là sập luôn. Nên mình nghĩ nếu bạn dùng được python thì cũng là 1 giải pháp, có lẽ không tồi đâu.

cho mình xin cod với bạn ơi, mình chưa biết dùng cái này , có cod mình sẽ nghiên cứu cách sử dụng ạ

 

[Hoàng Tuấn 868]

cho mình xin cod với bạn ơi, mình chưa biết dùng cái này , có cod mình sẽ nghiên cứu cách sử dụng ạ

Bạn phải học lập trình Python đã.

 

[tienhuy832011]

Bạn phải học lập trình Python đã.

cảm ơn bác, nay em mới dc nghe đến lập trình Python này , nó tiện dụng hơn VBA không bác

 

[Hoàng Tuấn 868]

cảm ơn bác, nay em mới nghe đến lập trình Python này , nó tiện dụng hơn VBA không bác

Còn tùy kỹ năng của từng người và từng trường hợp cụ thể mới có thể nói phù hợp hay không bạn ạ.

 

[Duy Tùng 9x]

cảm ơn bác, nay em mới dc nghe đến lập trình Python này , nó tiện dụng hơn VBA không bác

Nó hay, nhưng tiện dụng hay không thì còn tùy nhu cầu sử dụng của bạn. Mình thử hàm Excel (365 nhé), chạy cũng được nhưng mà số to nên chạy mất thời gian. Này chưa đáp ứng đúng nhu cầu, chỉ là mình đưa, nếu bạn có Office 365 thì có thể tham khảo để điều chỉnh lại cho đúng. Thử quanh mấy hàm thì cái này còn đáp ứng nổi chút. Đệ quy dùng Reduce nó load thì mệt ngủ luôn Hàm hay code thì mình cũng công khai đủ, chả úp mở gì.
=LET(a, C2:C25,b, F2, e, LAMBDA(c,n, IF(c="", INDEX(a,n),c&", "&INDEX(a,n))),f, LAMBDA(r,i,k,[c],,LET(j, i, m, COUNTA(a), t, k+(--INDEX(a,j)),u, IF(t=b,VSTACK(u,e(c,i)),u), x, IF((j<m)*(t<b),r(r,j+1,t,e(c,j),u),u), IF(i<m,r(r,i+1,k,c,x),u))), IFERROR(DROP(f(f,1,0,,""),1),""))

 

[HUONGHCKT]

Chào các bác, em làm số liệu exel cho công ty , hiện tại có file exel muốn tìm các dòng trong cột C và đánh dấu màu tại các ô có giá trị ở cột C sao cho tổng các giá trị đó lại xấp xỉ cho phép chênh lệch =, hoặc lớn hơn ,hoặc nhỏ hơn từ 1 đồng đến 200 đồng so với giá trị E2. (Tổng các dòng trong cột C>, hoặc bằng, hoặc < E2 chênh lệch từ 1 đồng đến 200 đồng thì đánh dấu mầu vào các ô ở cột C) .Mong các cao nhân giúp em chứ tìm bằng mắt thường trên exel so với giấy tờ thì hơi oải ạ

"Tầm chương, trích cú" cho vui.
Tham khảo code trong file (của 1 tác giả nào đó trên diễn đàn mà tôi quên tên).
Nhấn nút và chọn (bôi đen ) vùng cần tìm(C2:C25)=> Ok, và chọn ô chứa giá trị mục tiêu(Tổng số cho trước=E2)=>OK để được kết quả.

 

[tienhuy832011]

"Tầm chương, trích cú" cho vui.
Tham khảo code trong file (của 1 tác giả nào đó trên diễn đàn mà tôi quên tên).
Nhấn nút và chọn (bôi đen ) vùng cần tìm(C2:C25)=> Ok, và chọn ô chứa giá trị mục tiêu(Tổng số cho trước=E2)=>OK để được kết quả.

Làm thế nào để em xóa ô màu đi khi tìm theo 1 dữ liệu mới vậy bác

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

Nó hay, nhưng tiện dụng hay không thì còn tùy nhu cầu sử dụng của bạn. Mình thử hàm Excel (365 nhé), chạy cũng được nhưng mà số to nên chạy mất thời gian. Này chưa đáp ứng đúng nhu cầu, chỉ là mình đưa, nếu bạn có Office 365 thì có thể tham khảo để điều chỉnh lại cho đúng. Thử quanh mấy hàm thì cái này còn đáp ứng nổi chút. Đệ quy dùng Reduce nó load thì mệt ngủ luôn Hàm hay code thì mình cũng công khai đủ, chả úp mở gì.
=LET(a, C2:C25,b, F2, e, LAMBDA(c,n, IF(c="", INDEX(a,n),c&", "&INDEX(a,n))),f, LAMBDA(r,i,k,[c],,LET(j, i, m, COUNTA(a), t, k+(--INDEX(a,j)),u, IF(t=b,VSTACK(u,e(c,i)),u), x, IF((j<m)*(t<b),r(r,j+1,t,e(c,j),u),u), IF(i<m,r(r,i+1,k,c,x),u))), IFERROR(DROP(f(f,1,0,,""),1),""))

View attachment 305904

mình dùng công thức của bạn nhưng nó báo lỗi này là sao vậy , chỉ giúp mình với

 

[HUONGHCKT]

Làm thế nào để em xóa ô màu đi khi tìm theo 1 dữ liệu mới vậy bác

Bạn đã thử chạy code chưa?
Khi chạy code, trước khi tô màu những ô thảo mãn điều kiện, thì nó đã đưa tất cả các ô trong vùng định tim ấy về trạng thái không tô màu rồi mà.
Muốn thay đổi dữ liệu trong vùng các ô cần tìm thì cứ thay đổi thôi. khi thay đổi xong nhấn nút và thực hiện nhu hướng dẫn là được.
Muốn nó lấy kết quả những ô thỏa mãn ra một cột khác thì phải sửa lại code.

 

[tienhuy832011]

Bạn đã thử chạy code chưa?
Khi chạy code, trước khi tô màu những ô thảo mãn điều kiện, thì nó đã đưa tất cả các ô trong vùng định tim ấy về trạng thái không tô màu rồi mà.
Muốn thay đổi dữ liệu trong vùng các ô cần tìm thì cứ thay đổi thôi. khi thay đổi xong nhấn nút và thực hiện nhu hướng dẫn là được.
Muốn nó lấy kết quả những ô thỏa mãn ra một cột khác thì phải sửa lại code.

ah em thử lại thì ok rồi , nếu cần tìm dữ liệu mới thì phải gõ số mới vào E2 rồi dò tìm lại, nó sẽ xóa màu đánh dấu cũ rồi đánh dấu theo số liệu E2 mới dò tìm được bác ạ, nãy là em gõ số liệu mới xuống E3 rồi dò tìm E3 , del E2 thì nó vẫn còn bôi màu theo dữ liệu cũ E2.
trường hợp mà nó có nhiều tổ hợp trả về giá trị ấy thì có cách nào để lấy hết các tổ hợp không vậy bác. Phiền bác nghiên cứu giúp em . Em cảm ơn ạ

 

[HUONGHCKT]

ah em thử lại thì ok rồi , nếu cần tìm dữ liệu mới thì phải gõ số mới vào E2 rồi dò tìm lại, nó sẽ xóa màu đánh dấu cũ rồi đánh dấu theo số liệu E2 mới dò tìm được bác ạ, nãy là em gõ số liệu mới xuống E3 rồi dò tìm E3 , del E2 thì nó vẫn còn bôi màu theo dữ liệu cũ E2.
trường hợp mà nó có nhiều tổ hợp trả về giá trị ấy thì có cách nào để lấy hết các tổ hợp không vậy bác. Phiền bác nghiên cứu giúp em . Em cảm ơn ạ

1/ Trường hợp có nhiều Số tổng cần lấy tổ hợp (ví dụ E2,E3,E4,....) Thì có thể dùng vòng lặp để xác định số tổng cần tìm các ô thỏa mãn. Ta phải bỏ inputbox thứ 2 đi và thay vào đó là .Range("E"&i) với for i = 2 to dòng cuối cùng cột E(số số tổng cần tính)
2/Trường hợp cần lấy nhiều tổ hợp thỏa mãn 1 số tổng ở E2 và ghi ra cột khác thì có thế phải dùng Dictionary để loại trừ Ô đã thỏa mãn của phương án trước.
3/Nếu cả hai (nhiều số tổng và liệt kê tất cả các phương án của từng tổng ra cột khác) thì code sẽ chạy lâu hơn và có thể có tổng không có phương án nào thỏa mãn.
Trong 3 trường họp tôi nêu trên, trường hợp nào phù hợp với bạn?

 

[tienhuy832011]

1/ Trường hợp có nhiều Số tổng cần lấy tổ hợp (ví dụ E2,E3,E4,....) Thì có thể dùng vòng lặp để xác định số tổng cần tìm các ô thỏa mãn. Ta phải bỏ inputbox thứ 2 đi và thay vào đó là .Range("E"&i) với for i = 2 to dòng cuối cùng cột E(số số tổng cần tính)
2/Trường hợp cần lấy nhiều tổ hợp thỏa mãn 1 số tổng ở E2 và ghi ra cột khác thì có thế phải dùng Dictionary để loại trừ Ô đã thỏa mãn của phương án trước.
3/Nếu cả hai (nhiều số tổng và liệt kê tất cả các phương án của từng tổng ra cột khác) thì code sẽ chạy lâu hơn và có thể có tổng không có phương án nào thỏa mãn.
Trong 3 trường họp tôi nêu trên, trường hợp nào phù hợp với bạn?

Trường hợp 2 phù hợp với em bác ạ, mong bác giúp em

 

[HUONGHCKT]

Trường hợp 2 phù hợp với em bác ạ, mong bác giúp em

Bạn đã tham khảo Add_in này chưa? Trong đó có hàm BS-Comby...

https://www.giaiphapexcel.com/diendan/threads/phi%C3%AAn-b%E1%BA%A3n-m%E1%BB%9Bi-add-in-a-tools-v10-2024.171507/.

Hy vọng là nó đúng ý bạn

 

[tienhuy832011]

Bạn đã tham khảo Add_in này chưa? Trong đó có hàm BS-Comby...

https://www.giaiphapexcel.com/diendan/threads/phi%C3%AAn-b%E1%BA%A3n-m%E1%BB%9Bi-add-in-a-tools-v10-2024.171507/.

Hy vọng là nó đúng ý bạn

nó là add-in pro hay add-in free vậy bác. em tìm chưa thấy hàm bác bảo ạ

 

[befaint]

Cái này Solver tẹo là được.

Solver ở đây rất nhiều bài rồi.

 

[tienhuy832011]

Cái này Solver tẹo là được.

Solver ở đây rất nhiều bài rồ

Cái này Solver tẹo là được.

Solver ở đây rất nhiều bài rồi.

vâng em cảm ơn bác nhiều ạ