Nhờ giúp đỡ chỉnh lại hàm dò tìm kết hợp INDEX và MATCH (1 người xem)

[acrox84]

Như tiêu đề, mình có bảng dò tìm (dò cột A, ra kết quả cột B)

công thức ô E2
=IFERROR(INDEX($B$1:$B$100,MATCH(D2-1,$A$1:$A$100,1)+1),"")

--> Mình cần số Đúng ở cột G, nhờ anh em giúp đỡ sửa code, thanks!

 

[hoangtuaotrang_hp_vn]

Như tiêu đề, mình có bảng dò tìm (dò cột A, ra kết quả cột B)

--> Mình cần số Đúng ở cột G, nhờ anh em giúp đỡ sửa code, thanks!
View attachment 288347

Một công thức vô cùng củ chuối:

=IFERROR(IF(D5=0,25000,INDEX($B$1:$B$200,MATCH(D5-0.4,$A$1:$A$200,1)+1)),"")

 

[ptm0412]

!!!.... .. . . ..

Sao cứ viết lung tung rồi tự xóa, mất công BQT phải dọn dẹp vậy?
Công thức theo suy luận đơn giản:
=INDEX($B$1:$B$71,MATCH(D2,$A$1:$A$71,1)+IF(ISNA(MATCH(D2,$A$1:$A$71,0)),1,0),1)

 

[Hoàng Tuấn 868]

Công thức theo suy luận đơn giản:
=INDEX($B$1:$B$71,MATCH(D2,$A$1:$A$71,1)+IF(ISNA(MATCH(D2,$A$1:$A$71,0)),1,0),1)

Em định đưa ra công thức này thôi anh.

=IFERROR(INDEX($A$1:$B$71,MATCH(D2-0.4,$A$1:$A$71,1)+1,2),25000)

 

[acrox84]

Một công thức vô cùng củ chuối:

=IFERROR(IF(D5=0,25000,INDEX($B$1:$B$200,MATCH(D5-0.4,$A$1:$A$200,1)+1)),"")

hehe, công thức đó mà nãy gõ sai chỗ -1 là -0.1 cũng ra đúng nữa, paste lên đây coi lại mới thấy. Để mình thử edit lại công thức mấy bạn giúp xem thế nào.
Chân thành cảm ơn mọi người, đã xử lý xong ạ

 

[ptm0412]

Em định đưa ra công thức này thôi anh.

=IFERROR(INDEX($A$1:$B$71,MATCH(D2-0.4,$A$1:$A$71,1)+1,2),25000)

Con số 0.4 và con số 25 ngàn đưa cứng vào công thức là chết.
Đâu có biết từ 2.01 đến 2.39 lấy dòng nào?

 

[kelacloi]

Như tiêu đề, mình có bảng dò tìm (dò cột A, ra kết quả cột B)

--> Mình cần số Đúng ở cột G, nhờ anh em giúp đỡ sửa code, thanks!
View attachment 288347

Thư công thức này xem.

=IF(ISNA(INDEX($B$1:$B$71,MATCH(D2,$A$1:$A$71,0))),INDEX($B$1:$B$71,MATCH(MIN(IF($A$1:$A$71>D2,$A$1:$A$71)),$A$1:$A$71,0)),INDEX($B$1:$B$71,MATCH(D2,$A$1:$A$71,0)))

Công thức chỗ anh PTM hình như cột tham chiếu cần sắp xếp tăng dần.

 

[ptm0412]

Thư công thức này xem.

=IF(ISNA(INDEX($B$1:$B$71,MATCH(D2,$A$1:$A$71,0))),INDEX($B$1:$B$71,MATCH(MIN(IF($A$1:$A$71>D2,$A$1:$A$71)),$A$1:$A$71,0)),INDEX($B$1:$B$71,MATCH(D2,$A$1:$A$71,0)))

Công thức chỗ anh PTM hình như cột tham chiếu cần sắp xếp tăng dần.

Bản chất dò tìm không chính xác bắt buộc phải dò tìm tăng dần. Công thức bài 1 tác giả gọi là đang sai, công thức bài 2, bài 4, bài 5 cũng là dò tìm không chính xác. Vậy thì dữ liệu bắt buộc phỉ tăng dần (như trong file)

 

[HieuCD]

Như tiêu đề, mình có bảng dò tìm (dò cột A, ra kết quả cột B)

--> Mình cần số Đúng ở cột G, nhờ anh em giúp đỡ sửa code, thanks!
View attachment 288347

Cộng thêm 4 số 9

=VLOOKUP(D2+0.9999,$A$1:$B$71,2)

 

[VetMini]

(A) Thời thượng cổ, máy tính còn yếu, Excel chưa chỉnh đốn lại kỹ thuạt tham chiếu cho nên INDEX/MATCH nhanh hơn VLOOKUP. Lý do chính là hàm INDEX rất hiệu quả, và INDEX/MATCH nạp mảng 1 chiều nhanh hơn VLOOKUP nạp mảng hai chiều.
Thời buối công nghệ tiên tiến bi giờ, những cái đó khác nhau chỉ một tí xíu, hiệu quả về tốc độ không đáng kể nữa. Nếu nới tham chiếu và nơi truy cập có thể gom về một vùng thì dùng hàm VLOOKUP dễ nhìn, dễ chỉnh sửa hơn.
Dân GPE quen lối mòn cũ cho nên cứ đụng vào là INDEX/MATCH. Nếu theo đúng thời cuộc thì cặp hàm này ngày nay chỉ sử dụng khi nơi tham chiếu (MATCH) và nơi truy vấn (INDEX) riêng biệt nhau, hoặc tham chiếu bên phải truy vấn,...

(B) MATCH và VLOOKUP mặc định dò theo gần đúng (trừ phi tham số cuois là 0). Bảng dò BẮT BUỘC phải được sắp xếp theo tăng dần bởi vì lô gic "gần đúng" gồm hai điều chính:
1. Nếu không sắp xếp thì gần đúng là gần đúng với cái quái gì? Phải sắp xếp mới có thể lý luận được "vị trí dò được chứa trị nhỏ hơn hay bằng trị càn tìm; tất cả các trị sau dố đều lớn hơn trị cần tìm"
2. Khi dò gần đúng thì code của hàm dò theo thuật toán nhị phân. Thuật toán nhị phân BẮT BUỘC bảng dò phải sắp xếp tăng dần. Nếu không sắp xếp thì kết quả không đáng tin cậy.

 

[ptm0412]

Cộng thêm 4 số 9

=VLOOKUP(D2+0.9999,$A$1:$B$71,2)

Theo tôi việc cộng hay trừ 1 con số để tính nó hơi gượng ép. Nếu thông minh thì nghĩ ra ngay 1 con số hợp lý để cộng/ trừ, nếu chậm chạp sẽ phải thử vài ba lần và phải biết cách test thật kỹ. Chọn con số sai như bài 1 sẽ thấy sai ngay, chọn như bài 5 sẽ sai tiềm ẩn.

dùng hàm VLOOKUP dễ nhìn, dễ chỉnh sửa hơn.
Dân GPE quen lối mòn cũ cho nên cứ đụng vào là INDEX/MATCH.

Bài 6 là tôi sửa công thức sai, nếu tôi viết bằng Vlookup thì:
=VLOOKUP(ROUNDUP(D2,0),$A$1:$B$71,2,1)
Với thực trạng là khoảng cách luôn bằng 1.
Tôi không thông minh nên không mày mò để tìm ra con số hợp lý là 0.9999 hoặc số khác

 

[LmoovoenX]

Theo mình hiểu thì ở đây là dò tìm lấy cận trên của đoạn, vì tham số là số nguyên nên có thể dùng được như sau:

=INDEX($B$1:$B$200,MATCH(roundup(D2,0),$A$1:$A$200,1))

 

[ptm0412]

Nói thêm:
Công thức của tôi ở bài 3 bất chấp việc cột A là nguyên hay thập phân, cách đều 1 hay 2 hay khoảng cách bất kỳ. Chỉ cần sắp xếp tăng dần.

 

[VetMini]

Theo mình hiểu thì ở đây là dò tìm lấy cận trên của đoạn, vì tham số là số nguyên nên có thể dùng được như sau:

=INDEX($B$1:$B$200,MATCH(roundup(D2,0),$A$1:$A$200,1))

Công thức này chỉ đúng nếu bảng dò tăng theo bậc 1.
Nếu tăng lớn hơn 1 thì sẽ sai:
MATCH(roundup(3.4,0),{ 1, 2, 3, 4, 5, 6 },1) = 4
MATCH(roundup(3.4,0),{ 1, 2, 3, 5, 7, 8 },1) = 3

 

[hoangtuaotrang_hp_vn]

Trong ví dụ của chủ thớt có đoạn này:
Nên dẫn đến người đọc đề (như cháu/em) sẽ hiểu là:
với ngưỡng >=0.4 thì sẽ lấy theo mốc trên 70.000
với ngưỡng <0.4 thì sẽ lấy theo mốc dưới 65.000
Nếu đúng như thế này thì công thức của cháu/em và anh Hoàng Tuấn sẽ sát với đề bài

Nên em/cháu vẫn muốn làm rõ ý của chủ thớt sẽ là như thế nào trong 2 trường hợp >=0.4 và <0.4

 

[ptm0412]

Công thức này chỉ đúng nếu bảng dò tăng theo bậc 1.

Công thức cộng 4 số 9 và công thức trừ 0.4 cũng vậy.

với ngưỡng >=0.4 thì sẽ lấy theo mốc trên 70.000
với ngưỡng <0.4 thì sẽ lấy theo mốc dưới 65.000

Tôi hiểu theo suy luận thông thường:
Theo thông thường thì chia khoảng theo hàm Round: <0.5 và >=0.5. Tác giả cho 2 thí dụ dưới 5 (0.4) và trên 5 (0.9). Cả 2 đều lấy cận trên
Trường hợp đặc biệt không chia khoảng như hàm Round (mốc 0.4) phải có 1 thí dụ dưới 0.4. Không có thí dụ nào cho việc <0.4 lấy cận dưới.

 

[VetMini]

Túm lại, có hai cách làm việc:

1.
Theo cách làm việc của Match và Vlookup: nếu trị dò nằm giữa hai trị thì hàm luôn chọn cận dưới
Muốn chọn cận trên thì cứ để hàm chọn cận dưới rồi +1.
Để giải quyết trường hợp trị dò bằng cận dưới thì đem trị dò trừ đi một trị rất nhỏ.
Vấn đề còn lại là lỗi sẽ xảy ra khi trị dò bằng trị nhỏ nhất. Bẫy lỗi này. Hết.
(vì giải thuật này lấy mảng so le cho nên VLookup không làm được)
=INDEX($B$1:$B$71,IFERROR(MATCH($D2-1E-6,$A$1:$A$71,1),0)+1)

2.
Cứ lấy cận trên rồi so sánh kết quả (bài #3)

 

[Nhattanktnn]

Một cách tiếp cận khác khá đơn giản, là đếm có bao nhiêu ô lớn hơn hoặc bằng nó rồi trừ đi và +1

=INDEX($B$1:$B$71,ROWS($A$1:$A$71)-COUNTIF($A$1:$A$71,">="&D2)+1)

 

[VetMini]

Đơn giản hay không thì tùy ý khách/chủ quan.
Riêng hàm Match dò gân đúng rất nhanh, trung bình nó chỉ duyệt qua vài trị trên mảng.

Vả lại, nếu trừ đi một số thật nhỏ thì chỉ cần đếm số nhỏ hơn nó và +1. Khỏi phải mang cái hàm Rows.

 

[acrox84]

Cộng thêm 4 số 9

=VLOOKUP(D2+0.9999,$A$1:$B$71,2)

mình thử thấy dùng vlookup như trên đúng cho trường hợp mình cần,
Cho hỏi thêm là nếu muốn bỏ ô trống "" cho những ô D2 không có giá trị thì chỉnh sửa sao ạ
*Ảnh minh họa, do kéo công thức full bảng mà cột D2 thì phần dưới không có giá trị.

 

[Phuocam]

mình thử thấy dùng vlookup như trên đúng cho trường hợp mình cần,
Cho hỏi thêm là nếu muốn bỏ ô trống "" cho những ô D2 không có giá trị thì chỉnh sửa sao ạ
*Ảnh minh họa, do kéo công thức full bảng mà cột D2 thì phần dưới không có giá trị.
View attachment 288706

Thêm hàm IF vào công thức:

=IF(D2="","",VLOOKUP(CEILING(D2,1),$A$1:$B$71,2,0))

.

 

[acrox84]

Thêm hàm IF vào công thức:

=IF(D2="","",VLOOKUP(CEILING(D2,1),$A$1:$B$71,2,0))

.

ổn rồi ạ, cảm ơn bạn nhiều!

 

[Lê Duy Thương]

Con số 0.4 và con số 25 ngàn đưa cứng vào công thức là chết.
Đâu có biết từ 2.01 đến 2.39 lấy dòng nào?

Theo em dùng lookup cho trường hợp này ổn hơn nhiều.