Các câu hỏi về Hàm Index và Match

[tienbv]

Anh/chị nào biết về hàm match và index chỉ giúp tui với.
Cho ví dụ cụ thể để tiện theo dõi, (đọc mãi ko hiểu).
Thanks in advance!
tienbv

 

[ThuNghi]

Tôi xin mạn phép các cao thủ dịch "nôm" hàm match

Cú pháp
MATCH(lookup_value,lookup_array,match_type)
Trả về thứ tự (vị trí) của trị dò trong bảng dò. Trả về 1 số nếu tìm ra.
Lookup_value : Trị dò
Trị dò có thể là number, text, hay logical value.
Lookup_array: Mảng dò là 1 mảng bao gồm trị dò.
is a contiguous range of cells containing possible lookup values. Lookup_array must be an array or an array reference.
Match_type: Kiểu match: là các số -1, 0, 1

1- Nếu match_type is 0, sẽ tìm chính xác trị dò trong mảng dò
Ta có mảng loaikh: SUN; MON; TUE; WED; THU; FRI; SAT
MATCH("TUE",LOAIKH,0) = 3

2- Nếu match_type is 1, hàm match s ẽ tìm giá trị lớn nhất mà nhỏ hơn hay bằng trị dò
Mảng dò phải là 1 dãy sort tăng dần.
Ta có mảng donggop: 0, 100, 200, 300, 400, 500 (dãy tăng dần)
Một phụ huynh ký sổ vàng là 101, ? phụ huynh xếp lọai nào, (theo tt tiền)
Match(101,donggop,1) = 2

3- Nếu match_type is -1, nghiên cứu từ 1.
Nếu match_type không nhập, hàm này xem như match_type là 1.

Tuy nhiên, match cũng có thể trả về thứ tự như sau
Vi dụ: MATCH("b",{"a","b","c"},0) vị trí của “b” trong {"a","b","c"} là 2.
MATCH không phân biệt chữ thường hay chữa hoa
Nếu hàm MATCH không tìm thấy, sẽ trả về #N/A
Thông thường dung match, ta hay thấy
If(isna(match(…)),””,match(…)), ie nếu hàm match trả về N/A thì lấy rỗng,…
Nếu match_type =1 và trị dò là text, trị dò có thể dung ký tự “*” hay “?”
Ta có mảng loaikh: SUN; MON; TUE; WED; THU; FRI; SAT
MATCH("S*",LOAIKH,0) = 1.
Nên nhớ hàm match luôn trả về 1 vị trí đầu tiên mà nó tìm, từ trên xuống dưới.

Hiểu hàm match sẽ tiếp index, yêu cầu các bạn nên tham khảo cách tạo name.
Có ví dụ kèm để thực tập. Bạn hãy dùng vlookup để điền vào từ f2:F8, phần còn lại của file chưa làm.

 

[Mr Okebab]

Anh/chị nào biết về hàm match và index chỉ giúp tui với.
Cho ví dụ cụ thể để tiện theo dõi, (đọc mãi ko hiểu).
Thanks in advance!
tienbv

Đã biết TA như vậy thì bạn chỉ cần

1. Mở Excel
2. nhấn F1
3. Nhập match (hoặc index)
4. Enter

Như vậy bạn hoàn toàn có thể hiểu được rồi.

 

[tienbv]

Kết hợp giữa match và index

Các Pác chỉ giùm em bài tạp sau với!
thanks!View attachment BT12_TIENBV.xls

 

[ThuNghi]

Bạn nghiên cứu đáp án nhé.
Tổng tháng thì dùng sumif
Mà sao BT mà bạn không tự giải.

 

[anhtuan1066]

Đây nè... Xem thử đúng ý chưa?

 

[kachiusa108]

Chắc câu hỏi của em kỳ cục đối với mọi người lắm,,nhưng em mở 173 công thức excel ra, đến phần công thức với INDEX thì khó hiểu quá,(viết bằng tiếng anh nên em dịch lõm bõm lắm), anh chị nào rành chỉ giùm em với, khi liên kết vớ i MATCH thì sẽ thế nào ạ.Cảm ơn mọi người nhiều

Chính là cái sheet INDEX trong file 173 công thức thông dụng của excel ạ. còn cái liên kết em hỏi chính là phần tính thuế trong bài tập này, em đọc ko hiểu ạ,(trong cột này có liên kết giữa index và match) mong anh chỉ giùm em

em vừa tìm thấy 1 đề tài tương tự ạ, để em xem cái này đã ạ

 

[hieplv3010]

Gởi Kachiusa108. Anh nói theo cách mình hiểu, em xem có được không nhé. Người ta dùng index và match trường hợp bảng dò có nhiều dòng nhiều cột, trường hợp ít dòng, cột em dùng vlookup, thì thấy gọn hơn, nhưng nhiều dòng, cột thì công thức vlookup sẽ rất dài.
Người ta kết hợp index và match vì
Index để dò tìm trong một bảng, có nhiều dòng, nhiều cột. C.thức index(vùng dò tìm, dòng cần chọn, cột cần chọn). Tức là tại dòng thứ mấy, cột thứ mấy lấy ra giá trị cần tìm.
Bây giờ để tìm dòng, or cột cần chọn, ta kết hợp với match, để chỉ ra giá trị cần tìm ở dòng thứ mấy, or cột thứ mấy.
match(giá trị cần tìm, trong dãy cần tìm,0). Vd: Bây giờ để tìm B, trong dãy A, B, C, D thì nó sẽ chỉ ra B đang ở dòng thứ mấy, hoặc cột thứ mấy. 0 là lấy giá trị tuyệt đối, 1 là gần đúng.
Như vậy match hỗ trợ để chỉ ra dòng thứ mấy, hoặc cột thứ mấy thoả mãn với c.thức index cần tìm đối với bảng cần tìm có nhiều dòng, nhiều cột.
Vài ý kiến với bạn. Thân!

 

[SoiBien]

Mình bổ sung thêm một ý

dùng kết hợp Index và Match các bạn có thể tìm "ngược" (left/up lookup).

 

[Mr Okebab]

Mình bổ sung thêm một ý

dùng kết hợp Index và Match các bạn có thể tìm "ngược" (left/up lookup).

Mình đã có một bài nói về Index-Match và Vlookup : Mình sơ lại nhé

1. Vloopkup : Chỉ 1 chiều từ trái sang phải, vùng chứa dữ liệu tham chiếu phải nằm bên trái; Chỉ cần dùng một bảng duy nhất, rất tiện lợi cho việc đặt name
2. Match-Index : Đa chiều, bên trái hay phải được hết. Việc đặt nảm tốn nhiều thời gian, cần nhiều bảng (ít nhất là 2); Tuy nhiên khi lập công thức và thay đổi vị trí cột thì lại rất .. . . sướng

Tùy các bạn nhé.

 

[coganglen]

Xin hỏi về hàm Index và Match?

Tôi dùng hàm match và index tra bảng dựa vào các tham chiếu nhưng một số tham chiếu lại không cho ra kết quả mặc dù giá trị tìm vẫn có trong bảng. Vậy xin nhờ mọi người xem giúp!

 

[nguyentuhp]

Tôi dùng hàm match và index tra bảng dựa vào các tham chiếu nhưng một số tham chiếu lại không cho ra kết quả mặc dù giá trị tìm vẫn có trong bảng. Vậy xin nhờ mọi người xem giúp!

Bạn sửa lại công thức này
=IF(MOD(10*ROUNDDOWN($B$32,1),2)<>0,ROUNDDOWN($B$32,1)+0.1,ROUNDDOWN($B$32,1)+0.2)
thành:
=IF(MOD(10*ROUNDDOWN($B$32,1),2)<>0,ROUNDDOWN($B$32+0.1,1),ROUNDDOWN($B$32+0.2,1))

Tương tự sửa công thức tại cột I
=IF(MOD(10*ROUNDDOWN($B$32,1),2)<>0,ROUNDDOWN($B$32,1)-0.1,ROUNDDOWN($B$32,1))
thành:
=IF(MOD(10*ROUNDDOWN($B$32,1),2)<>0,ROUNDDOWN($B$32-0.1,1),ROUNDDOWN($B$32,1))

Nhưng tôi thấy công thức của bạn chưa chuẩn thì phải?
Giả sử MOD(10*ROUNDDOWN($B$32,1),2)= 0 rồi thì h/R trên và dưới đều = h/R thưc tế => Giá trị nội suy sẽ chính xác 100%.

Excel còn có một số lỗi nên phải linh động. Ví Dụ:
=9.2-int(9.2)=0.199999999 <> 0.2

 

[salam]

Công thưvs của bạn là đúng sai là do Bác Bill. Bạn xem bài này sẽ hiểu ngay vấn đề.
http://www.giaiphapexcel.com/forum/showthread.php?t=7060

Thân chào!

 

[nson]

Góp ý Về Hàm Index Và Match

Bạn coganglen thân mến! Đối với hàm Match nó có đến 3 cách dò (0,1,-1).
Đối với bài toán của bạn thì chỉ cần dùng cách dò là -1 cho hàm match là được.
vì: các giá trị trong khối dò của bài toán(a5:a17,a5:j5) đều đã được sắp xếp theo trật tự giảm dần (bảng chữ cái z-a).
Chúc bạn thành công!

 

[BNTT]

Thật sự chịu thua!
Nói thiệt, tôi đã ngồi với bài toán này gần 5 tiếng đồng hồ... Thử đủ cách, thay hàm, thay định dạng, thậm chí tạo luôn một file mới nhập lại, cũng vẫn không được!

Vấn đề không phải là hàm MATCH hay INDEX đâu. Mà vì cái gì đó, tôi không hiểu.

Trường hợp mà bạn Coganglen gọi là OK, chỉ là may mắn thôi.
Bác nào đã tải file này về, thay con số h = (ô B23), cho nó chạy từng nấc thử xem... có cái thì được, có cái thì #NA...

Các cách sửa của các bạn đã nêu ra, sẽ sai (báo lỗi #NA) với một vài trường hợp nào đó của h. Thật đấy, các bạn thử lại xem (cho h chạy sao cho H22 và I22 nằm trong khoảng từ 0.4 đến 2)

Có một cái này vui lắm, xin kể cho các bác nghe đặng suy nghĩ thêm:
Để yên kết quả của công thức cho h/R = 1.2, thì nó báo #NA, nhập thẳng 1.2 vào H22 thì hết (giống cách của bạn Coganglen), tôi thử kiểu khác, không nhập thẳng nữa, mà thay vì để yên 1.2 trên ô F5, tôi nhập F5 = 6*0.2, thì lại hết báo lỗi ! Nhưng F5 = 1.2*1 thì không à nghe ! Không hiểu được !

Sau đó tôi cho h chạy tiếp vài số... lại đụng số khác, tôi lại dùng chiêu trên để làm, nhưng lần này thì không ép phê nữa... Thậm chí gõ thẳng vào H22 chứ không lấy kết quả của công thức, nó cũng không chịu. Tức quá, tôi dùng một ô trống, gõ luôn = MATCH... (dò tìm riêng con số đó trong dãy A5:J5), nó vẫn báo là tìm không thấy. Mà rõ ràng trong A5:J5 có con số này! Vậy là tại cái gì ? Tôi đã thử thay định dạng ô, cũng không ăn thua.

Như trên đã nói, tôi làm luôn một sheet mới, rồi cho h chạy thử với những con số đã báo lỗi bên sheet của Coganglen, thấy không sao, mừng quá... mà mừng hụt, vì cho h chạy tiếp, thì lại tiếp tục đụng con số khác... Mà chỉ bị bên mảng A5:J5 thôi, chứ mảng cột (A6:A17) thì không bị.

Còn dùng -1 như bạn nvson nói, nó không báo lỗi nhưng cho kết quả sai, vì nó sẽ tìm "số nhỏ nhất mà lớn hơn số đi dò", chứ thật sự nó vẫn không tìm ra số mình cần dò. Ví dụ, tìm 1.2 không ra thì nó lấy đại 1.4... Khi dùng hàm MATCH, tôi vẫn khuyên người ta nên xài cách tìm là 0, để luôn được kết quả chính xác (với lại chả cần quan tâm trong mảng có sort hay chưa...)

Đôi lời tâm sự cùng các bác. Quả thật tới bi giờ thì tôi đuối... Từ hồi biết Excel đến giờ, chưa bao giờ tôi gặp chuyện này.

 

[nguyentuhp]

Thật sự chịu thua!
............
Các cách sửa của các bạn đã nêu ra, sẽ sai (báo lỗi #NA) với một vài trường hợp nào đó của h. Thật đấy, các bạn thử lại xem (cho h chạy sao cho H22 và I22 nằm trong khoảng từ 0.4 đến 2)
.............
.

Tôi Test công thức của tôi đưa lên và cho kết quả đều đúng cả mà!
và còn 1 cách nữa là thêm Round vào đầu công thức tính h/R:
=Round( If( ......), 1) cũng ổn mà!

 

[BNTT]

Gửi bạn nguyentuhp: Bạn nói đúng rồi. Xin lỗi bạn. Tôi đã không để ý cái chuyện bạn đưa +0.1 vào bên trong dấu ngoặc đơn. Có lẽ tại vì cứ loay hoay với MATCH và INDEX, mà không để ý đến những cái khác.

 

[tigertiger]

ĐÚng rồi, Giải pháp là ta phải làm tròn số trước khi đưa vào tra theo hàm match,

Vì thế ở đây bạn nên dùng công thức
=ROUND (<...>,1) đối với h/R
và
=ROUND (<...>,0) đối với E/Ech

Như thế là tra được -> bạn test xem sao?

Nếu ko được bạn thử dùng cách nội suy 2 chiều -> Sd AddIn của tôi - download tại đây: http://www.giaiphapexcel.com/forum/showpost.php?p=50931&postcount=6
thì chắc chắn được - nhưng nhớ là sau khi nội suy thì dùng hàm Round(<...>,2) để chỉ lấy 2 chữ số thôi nhé

 

[BNTT]

Đúng rồi, Giải pháp là ta phải làm tròn số trước khi đưa vào tra theo hàm match...

TigerTiger à, bạn đọc không kỹ đề bài rồi. Không phải là làm tròn số đâu. ROUNDDOWN() khác với ROUND().
ROUNDDOWN() có tác dụng giống như là cắt bớt đi phần lẻ, chỉ giữ lại bao nhiêu con số lẻ sau dấu thập phân, tương tự như hàm TRUNC() vậy.

Bạn thử nhé:

ROUND(1.97, 1) = 2.0 | ROUNDDOWN(1.97, 1) = 1.9 | TRUNC(1.97, 1) = 1.9

ROUND(1.92, 1) = 1.9 | ROUNDDOWN(1.92, 1) = 1.9 | TRUNC(1.92, 1) = 1.9
​

ROUNDDOWN() là làm tròn xuống, chính xác là xuống phía số 0, nghĩa là luôn làm tròn thành một số nhỏ hơn số đem đi làm tròn, và lấy mấy số lẻ thì tùy vào ta chỉ định. Nên khi dùng ROUNDOWN() làm tròn lấy 1 số lẻ, thì 1.97 cũng giống như 1.92 thôi, đều bằng 1.9

Trong khi ROUND() thì giống như cách chúng ta hay tính nhẩm, nghĩa là số ở sau mà lớn hơn hoặc bằng 5, thì khi làm tròn, sẽ đôn số trước nó lên 1, nên 1.97 khi làm tròn lên sẽ là 2.0, còn 1.92 thì chỉ là 1.9

Bạn thấy sự khác nhau giữa ROUND() và ROUNDDOWN() rồi chứ...

 

[tigertiger]

Bạn thấy sự khác nhau giữa ROUND() và ROUNDDOWN() rồi chứ...

- Cái này thì tôi biết rõ rồi , BNTT ah

- Vẫn đề ở đây của bạn đó là lỗi làm tròn -> vì ko tròn nên không khớp với bảng có sẵn - có vậy thôi. Giải pháp thì có 2 cách :
+ một là các như nguyentuhp
+ hai là cách như tigertiger đã làm

vậy thôi, NHƯNG vấn đề ở đây là phải chỉ rõ cho bạn ấy biết về lỗi này , và quan trọng hơn cả là khi làm việc với MT nói chung và Excel nói riêng phải luôn chú ý đến vấn đề SAI SỐ -> phải làm tròn trước khi tra,