Dò tìm giá trị gần nhất trong cột với giá trị yêu cầu (2 người xem)

[mymapmap]

Trong trường hợp như file đính kèm thì dùng công thức nào để dò tìm giá trị gần nhất vối giá trị cho trước trong A1;
Xin cảm ơn các ACE trả lời câu hỏi!

 

[nghiaphuc]

Trong trường hợp như file đính kèm thì dùng công thức nào để dò tìm giá trị gần nhất vối giá trị cho trước trong A1;
Xin cảm ơn các ACE trả lời câu hỏi!

Thử công thức này xem sao:

=INDEX($G$2:$G$17,MATCH(A1,$G$2:$G$17,1)+(A1>=(INDEX($G$2:$G$17,MATCH(A1,$G$2:$G$17,1)+1)+INDEX($G$2:$G$17,MATCH(A1,$G$2:$G$17,1)))/2))

Hy vọng là có công thức khác ngắn gọn hơn, nhìn công thức dài ngoằng mà phát ớn!

 

[bebo021999]

Trong trường hợp như file đính kèm thì dùng công thức nào để dò tìm giá trị gần nhất vối giá trị cho trước trong A1;
Xin cảm ơn các ACE trả lời câu hỏi!

Quy luật để tìm gần nhất của bạn? 30<A1<37; 37<A1<45 thì sao?

 

[Lê Duy Thương]

Thử công thức này xem sao:

=INDEX($G$2:$G$17,MATCH(A1,$G$2:$G$17,1)+(A1>=(INDEX($G$2:$G$17,MATCH(A1,$G$2:$G$17,1)+1)+INDEX($G$2:$G$17,MATCH(A1,$G$2:$G$17,1)))/2))

Hy vọng là có công thức khác ngắn gọn hơn, nhìn công thức dài ngoằng mà phát ớn!

dùng thử cái này xem nghĩa phúc . mình chưa test kỹ. tuy ngắn hơn nhưng ko biết kết quả thế nào
ẹc ẹc

=LOOKUP($A$1,$G$1:$G$17,IF(--RIGHT($A$1)>=5,$G$2:$G$18,$G$1:$G$17))

 

[nghiaphuc]

dùng thử cái này xem nghĩa phúc . mình chưa test kỹ. tuy ngắn hơn nhưng ko biết kết quả thế nào
ẹc ẹc

=LOOKUP($A$1,$G$1:$G$17,IF(--RIGHT($A$1)>=5,$G$2:$G$18,$G$1:$G$17))

Em thử test một xíu: Khi A1=34 thì công thức của em ra 37, còn công thức của anh ra 30. Như vậy công thức của anh có vẻ chưa ổn, vì yêu cầu của tác giả là "Dò tìm giá trị gần nhất trong cột với giá trị yêu cầu" và rõ ràng 34 gần với 37 hơn so với 30.

 

[ptm0412]

Thử 3 công thức mảng:

=INDEX(G2:G17;MATCH(MIN(ABS(G2:G17-$A$1));ABS(G2:G17-$A$1);0))
=OFFSET(G1;MATCH(MIN(ABS(G2:G17-$A$1));ABS(G2:G17-$A$1);0)
=LOOKUP(MIN(ABS(G2:G17-$A$1));G2:G17-$A$1;G2:G17)

 

[concogia]

Theo mình hiểu thì :
- Nếu [A1]= giá trị nào trong vùng G2:G17 sẽ lấy giá trị đó
- Nếu giá trị ở [A1] chui vào giữa 2 giá trị nào đó thì:
Nếu A1>= trung bình cộng của 2 giá trị đó thì lấy giá trị lớn & ngược lại
Mình tham gia một công thức:

=OFFSET($G$1,IF($A$1<AVERAGE(OFFSET($G$1,MATCH($A$1,$G$2:$G$17),,2)),MATCH($A$1,$G$2:$G$17),MATCH($A$1,$G$2:$G$17)+1),)

 

[ptm0412]

Công thức của Cò là lấy trung bình cộng 2 ô liền kề tính từ ô match
Nếu ô Match là ô cuối cùng: 250
Ô liền kề là 0
Trung bình cộng là 125
Công thức "tèo"
Cò "tiêu"

 

[concogia]

Công thức của Cò là lấy trung bình cộng 2 ô liền kề tính từ ô match
Nếu ô Match là ô cuối cùng: 250
Ô liền kề là 0
Trung bình cộng là 125
Công thức "tèo"
Cò "tiêu"

Mà hình như dạng bài này không có số trùng đâu Thầy ơi, chắc bạn í cũng loại số Min & Max, còn không mình lại phải thêm trường hợp đó vào công thức nữa. Mà thôi, chủ topic không giải thích rõ thì mình làm đến thế là Ok rồi
Híc

 

[ptm0412]

Không phải số trùng Cò ơi
Mà là lỗi ở match(A1, ...,1) á
Cò thử cho A1 =250 trở lên xem, biết liền hà.

 

[ngocmaipretty]

Các Quý Ông, Quý Ngài đi tìm ở đâu đâu á. NgocMai cứ nghĩ đơn giản vầy nè:
Gần nhất = khoảng cách nhỏ nhất
Khoảng cách nhỏ nhất = Min (khoảng cách)
Min(khoảng cách) = Min(đích đến - đầu đi)

Vậy thì trong công thức sẽ dùng hàm Min và 1 bài toán trừ mảng. Sau đó đi tìm cái min đó trong mảng kết quả trừ đó đó.

 

[ndu96081631]

Thử 3 công thức mảng:

=INDEX(G2:G17;MATCH(MIN(ABS(G2:G17-$A$1));ABS(G2:G17-$A$1);0))
=OFFSET(G1;MATCH(MIN(ABS(G2:G17-$A$1));ABS(G2:G17-$A$1);0)
=LOOKUP(MIN(ABS(G2:G17-$A$1));G2:G17-$A$1;G2:G17)

hao hao giống bài này nhỉ:
http://www.giaiphapexcel.com/forum/showthread.php?52846-Trợ-giúp-với-hàm-dò-tìm-!

 

[MonaLisa7775]

Vậy thì trong công thức sẽ dùng hàm Min và 1 bài toán trừ mảng. Sau đó đi tìm cái min đó trong mảng kết quả trừ đó đó.

Tỷ tỷ nói sao í chứ, Monalisa thấy sư phụ ptm đã dùng Min và đi tìm Min rồi kà.

Có 1 chỗ này Mona không hiểu nà, nhờ tỷ tỷ giải thích 1 xíu xiu:
Sao mà hễ dùng Match thì giữa 2 số bằng nhau và bằng Min, 2 công thức dùng Match lấy số trước, còn không dùng Match thì Công thức lookup của sư phụ lại lấy số sau?

Thí dụ muội cho A1 = 113
Khoảng cách giữa 105 và 113 = 8
Khoảng cách 113 và 121 cũng = 8

Thế mà 2 công thức Match của sư phụ thì lấy 105, còn công thức Lookup thì lấy số 121?

 

[ngocmaipretty]

Sao lại hỏi tỷ tỷ? Cởi chuông phải kiếm người cột chuông chứ.
Tỷ tỷ cũng bó tay rồi, không biết huynh Cò Già nghĩ sao, thấy lấp ló hoài.

 

[ptm0412]

Thí dụ muội cho A1 = 113
Khoảng cách giữa 105 và 113 = 8
Khoảng cách 113 và 121 cũng = 8
Thế mà 2 công thức Match của sư phụ thì lấy 105, còn công thức Lookup thì lấy số 121?

Monalisa thử suy nghĩ về bản chất của 2 số 8 đó có giống nhau không.

 

[MonaLisa7775]

Sư phụ, em nghĩ ra rồi nà:

Khoảng cách giữa 105 và 113 = 8
Khoảng cách 113 và 121 cũng = 8

2 số 8 trong phát biểu trên, xét về bản chất thì 1 số âm, 1 số dương, hoàn toàn hong giống nhau.

Hehe, tính MIN thì tính trong ABS, cả 2 bị mất bản chất và biến thành dương ráo trọi (có vậy mới ra MIN đúng)

Nhưng khi dò tìm con số 8, 2 công thức trên dò tìm cũng trong ABS bằng Match sẽ thấy 2 số 8, và lấy 8 trên.
Còn trong công thức dò tìm bằng lookup, lại là dò tìm trong mảng không có ABS, chỉ có 1 số 8 thôi, số còn lại nằm bên trên, là -8. Thía là nó lấy giá trị dưới, tương ứng với +8
Hihi, khoái quá!

 

[mymapmap]

Thử công thức này xem sao:

=INDEX($G$2:$G$17,MATCH(A1,$G$2:$G$17,1)+(A1>=(INDEX($G$2:$G$17,MATCH(A1,$G$2:$G$17,1)+1)+INDEX($G$2:$G$17,MATCH(A1,$G$2:$G$17,1)))/2))

Hy vọng là có công thức khác ngắn gọn hơn, nhìn công thức dài ngoằng mà phát ớn!

Cảm ơn các thay gianh thoi gian chia se vấn đề của mình do bận quá không theo doi het các comment. hôm nay mới quay lại thay đông vui quá.
Minh đang sử dụng công thức của bac Nghiaphuc đáp ứng được ứng dụng của minh. nhưng bị vấn đề khi số tìm là biên trên trên 250 thì ko tìm được 250. và muốn yêu cầu thêm khi số tìm là ngoài range thì lấy giá trị tại biên đấy.
vd: 260 trả về 250 hoặc -80 trả về -75
Xin cảm ơn!