Dò tìm một giá trị trong bảng thỏa mãn điều kiện (1 người xem)

[dauquangvuong]

Chào mọi người !
Mình đang gặp khó khăn trong việc dò tìm 1 giá trị trong bảng khi thỏa mãn các điều kiện. mình dùng hàm sumproduct mà không được. mình có gửi file đính kèm ở dưới, mong mọi người giúp đỡ, cám ơn nhiều !

 

[nginh]

chào mọi người !
Mình đang gặp khó khăn trong việc dò tìm 1 giá trị trong bảng khi thỏa mãn các điều kiện. Mình dùng hàm sumproduct mà không được. Mình có gửi file đính kèm ở dưới, mong mọi người giúp đỡ, cám ơn nhiều !

=index($a:$a,sum(if(abs($i$5-(b$5:b$79=$i$6)*offset(b$5:b$79,,match("*"&$i$7&"*",$4:$4,0)-columns($a:$b)))=min(abs($i$5-(b$5:b$79=$i$6)*offset(b$5:b$79,,match("*"&$i$7&"*",$4:$4,0)-columns($a:$b)))),row(b$5:b$79))))

Ctrl+Shift+Enter

 

[Let'GâuGâu]

Chào mọi người !
Mình đang gặp khó khăn trong việc dò tìm 1 giá trị trong bảng khi thỏa mãn các điều kiện. mình dùng hàm sumproduct mà không được. mình có gửi file đính kèm ở dưới, mong mọi người giúp đỡ, cám ơn nhiều !

GIÁ TRỊ lvt là tính ra hay bạn nhập vào?
kết quả N mà bạn cho trong vi du là bao nihêu?

 

[dauquangvuong]

GIÁ TRỊ lvt là tính ra hay bạn nhập vào?
kết quả N mà bạn cho trong vi du là bao nihêu?

Giá trị Lvt mình tính ra bạn à . nó là cơ sở để chọn N. kết quả N là theo bảng và trong ví vụ mình cho là 650 .

 

[dauquangvuong]

=index($a:$a,sum(if(abs($i$5-(b$5:b$79=$i$6)*offset(b$5:b$79,,match("*"&$i$7&"*",$4:$4,0)-columns($a:$b)))=min(abs($i$5-(b$5:b$79=$i$6)*offset(b$5:b$79,,match("*"&$i$7&"*",$4:$4,0)-columns($a:$b)))),row(b$5:b$79))))

mình có nhận vào nhưng kết quả là 300 . nếu đúng là 650 bạn à . có chỗ nào chưa ổn chăng. ở đây với giá trị Lvt tính được dò tìm trong bảng giá trị Lvt gần với giá trị trong bảng nhất, từ đó chọn ra N bạn à .

 

[dauquangvuong]

cám ơn nginh và Let'GâuGâu nhiều

 

[leonguyenz]

cám ơn nginh và Let'GâuGâu nhiều

Công thức bài #2, nếu nhập giá trị tìm được dưới 15.8 thì kết quả N=0?.

 

[Let'GâuGâu]

Giá trị Lvt mình tính ra bạn à . nó là cơ sở để chọn N. kết quả N là theo bảng và trong ví vụ mình cho là 650 .

J9=INDEX($A$5:$A$79,MATCH(J6,INDIRECT("B"&MATCH(J5,OFFSET($B$5:$B$79,,MATCH(J7,{20,25,30,35,45},0)))&":B79"),0)+MATCH(J5,OFFSET($B$5:$B$79,,MATCH(J7,{20,25,30,35,45},0))))

nếu thấy nó dài quá thì đưa bớt vào name

hang=MATCH(Sheet1!$J$5,OFFSET(Sheet1!$B$5:$B$79,,MATCH(Sheet1!$J$7,{20,25,30,35,45},0)))

J9=INDEX($A$5:$A$79,MATCH($J$6,INDIRECT("B"&hang&":B79"),0)+hang)

 

[leonguyenz]

J9=INDEX($A$5:$A$79,MATCH(J6,INDIRECT("B"&MATCH(J5,OFFSET($B$5:$B$79,,MATCH(J7,{20,25,30,35,45},0)))&":B79"),0)+MATCH(J5,OFFSET($B$5:$B$79,,MATCH(J7,{20,25,30,35,45},0))))

nếu thấy nó dài quá thì đưa bớt vào name

hang=MATCH(Sheet1!$J$5,OFFSET(Sheet1!$B$5:$B$79,,MATCH(Sheet1!$J$7,{20,25,30,35,45},0)))

J9=INDEX($A$5:$A$79,MATCH($J$6,INDIRECT("B"&hang&":B79"),0)+hang)

Anh test thử giá trị Lvt = 130 thử xem, kết quả lấy dòng 19 (N=650).
Tham khảo thêm công thức này xem:
Đặt name Col:

=OFFSET(Sheet1!$B$5:$B$79,,MATCH(Sheet1!$I$7,{20,25,30,35,45},0))

Công thức tại I9:

=INDEX(A5:A79,IFERROR(MATCH(I5+MIN(IF(B5:B79=I6,ABS(Col-I5))),Col,0),MATCH(I5-MIN(IF(B5:B79=I6,ABS(Col-I5))),Col,0)))

 

[dauquangvuong]

Mình muốn hỏi 1 chút ạ. Ở chỗ đặt col =OFFSET(Sheet1!$B$5:$B$79,,MATCH(Sheet1!$I$7,{20,25,30,35,45},0)) thì được hiểu như thế nào vậy ? Lệnh OFFSET là đưa về 1 vùng hay 1 giá trị từ 1 ô hay 1 khoảng xác định. nhưng trong công thức trên mình không hiểu lắm. ai chỉ mình với

 

[leonguyenz]

Mình muốn hỏi 1 chút ạ. Ở chỗ đặt col =OFFSET(Sheet1!$B$5:$B$79,,MATCH(Sheet1!$I$7,{20,25,30,35,45},0)) thì được hiểu như thế nào vậy ? Lệnh OFFSET là đưa về 1 vùng hay 1 giá trị từ 1 ô hay 1 khoảng xác định. nhưng trong công thức trên mình không hiểu lắm. ai chỉ mình với

Bạn nhấn Ctrl+F3 để xem Name.
OFFSET để tham chiếu tới 1 ô hoặc 1 vùng, trong Name trên, dùng hàm OFFSET để thay đổi tham chiếu vùng khi thay đổi giá trị I7.
Ví dụ: OFFSET(B:B,,1) sẽ cho kết quả là tham chiếu C:C, nếu thay 1 là 3 thì kết quả là tham chiếu E:E (từ sau cột B thêm 3 cột là cột E).
Bạn gõ từ khóa "Hàm OFFSET" để tham khảo thêm.

 

[nginh]

Công thức bài #2, nếu nhập giá trị tìm được dưới 15.8 thì kết quả N=0?.

Bạn thử công thức sau

=INDEX(A5:A79,MATCH(MIN((I5-(B5:B79=I6)*OFFSET(A5:A79,,MATCH("*"&I7&"*",4:4,0)-1))^2),(I5-(B5:B79=I6)*OFFSET(A5:A79,,MATCH("*"&I7&"*",4:4,0)-1))^2,0))

Ctrl+Shift+Enter