Bạn đang xem bài viết Hướng Dẫn Giải Bài Tập Dùng Hàm Index Kết Hợp Hàm Match Có File Mẫu được cập nhật mới nhất trên website Hoisinhvienqnam.edu.vn. Hy vọng những thông tin mà chúng tôi đã chia sẻ là hữu ích với bạn. Nếu nội dung hay, ý nghĩa bạn hãy chia sẻ với bạn bè của mình và luôn theo dõi, ủng hộ chúng tôi để cập nhật những thông tin mới nhất.
Bạn có biết hàm INDEX và MATCH là những hàm tìm kiếm rất hiệu quả và hữu ích, tuy nhiên do phải kết hợp 2 hàm này nên nhiều người thấy khó. Để hiểu rõ hơn cách sử dụng hai hàm này, Gitiho sẽ hướng dẫn các bạn cách sử dụng hàm INDEX, hàm MATCH qua một số bài tập ứng dụng. Các bạn có thể tải file bài tập này ở cuối bài viết.
Cách dùng hàm MATCH để dò tìm trong ExcelVí dụ chúng ta có bảng tính sau:
Trong bảng này chúng ta có thông tin về họ tên, số điện thoại, Email, khu vực làm việc của 6 nhân viên. Nếu muốn biết thông tin của nhân viên “Lê Thị Thủy” thì bạn phải làm thế nào? Chắc hẳn việc đầu tiên bạn cần xác định xem người đó nằm ở vị trí thứ mấy trong bảng tính này.
Khi đó bạn có thể sử dụng hàm MATCH như sau:
Cú pháp hàm MATCH gồm 3 phần:
lookup_value: giá trị làm căn cứ tìm kiếm (tìm theo cái gì); ở đây là tên Lê Thị Thủy
lookup_array: là nơi dò tìm (bạn sẽ tìm ở đâu); ở đây là cột Họ tên
match_type: (Tham số này không bắt buộc): phương pháp tìm kiếm (tìm đúng theo giá trị hay tìm theo khoảng giá trị); ở đây tìm chính xác theo tên.
Do đó chúng ta có thể viết hàm MATCH như sau:
=MATCH(Tên cần tìm, cột Họ tên, tìm chính xác theo tên)
Tại ô D12 nhập tên Lê Thị Thủy
cột Họ tên là vùng C3:C8
Như vậy hàm MATCH cho kết quả là số 3, tương ứng với dòng thứ 3 trong bảng dữ liệu trên.
Hàm INDEX kết hợp với MATCH để tìm kiếm trong ExcelKhi bạn đã có vị trí là dòng thứ 3 trong bảng, bạn có thể lấy được bất kỳ thông tin nào ở dòng này nếu xác định thêm được cột chứa nội dung cần tìm. Khi đó bạn có thể kết hợp hàm INDEX với MATCH theo cách:
Hàm INDEX chỉ định cột chứa kết quả cần tìm
Hàm MATCH chỉ định dòng cần tìm
Khi đó giao điểm giữa cột và dòng này chính là vị trí kết quả cần tìm
Tìm Email của nhân viên Lê Thị Thủy
Trong công thức tại ô D15 tìm Email, chúng ta thấy:
=INDEX(E3:E8,MATCH(D12,C3:C8,0))
Công thức này, hàm INDEX có tác dụng:
Chỉ ra cột chứa giá trị cần tìm là cột E, vùng E3:E8
Chỉ ra dòng chứa giá trị cần tìm, dòng này đã xác định bởi hàm MATCH trong ví dụ trước.
Kết quả là giao điểm của cột E với dòng 3, cho ra vị trí ô E3 là ô chứa giá trị cần tìm.
Phương pháp tìm kiếm từ phải qua trái với hàm INDEX+MATCHThông thường để tìm kiếm trong Excel chúng ta thường dùng hàm VLOOKUP. Nhưng hàm này có nhược điểm là không thể tìm được theo chiều từ phải qua trái. Nhưng bạn có thể thực hiện điều này rất dễ dàng với hàm INDEX kết hợp hàm MATCH. Hãy tìm hiểu ví dụ sau: Tìm tên dựa vào thông tin Email, biết cột Email ở bên phải cột Họ tên.
Cách làm như sau:
Viết hàm MATCH để tìm vị trí dòng chứa thông tin Email
=MATCH(tên Email, vùng chứa Email, 0)
Kết hợp với hàm INDEX trong việc chỉ ra vị trí cột chứa kết quả cần tìm, là cột Họ tên (cột C), vùng C3:C8
=INDEX(C3:C8,MATCH(D12,E3:E8,0))
Như vậy về cách viết hàm không có gì thay đổi, nhưng phương pháp tìm kiếm có thể cho phép tìm từ phải qua trái, từ dưới lên trên một cách dễ dàng.
Bài tập tìm đơn giá của mặt hàng theo nhiều điều kiệnTrong trường hợp giá trị tìm kiếm của bạn thay đổi trên cả dòng và cột trong 1 bảng dữ liệu, bạn vẫn có thể sử dụng hàm INDEX MATCH để tìm kiếm được. Hãy xem ví dụ về tìm kiếm đơn giá của 1 sản phẩm khi thay đổi cả về mã sản phẩm và mã công ty:
Trong ví dụ này, chúng ta có Mã hàng và Mã công ty là các điều kiện cần tìm. Mã này có thể thay đổi:
Mã hàng thay đổi trong vùng B3:E3
Mã công ty có thể thay đổi trong vùng A4:A7
Với bất kỳ mã nào, bạn cũng phải xác định được đơn giá phù hợp.
Các bước thực hiện như sau:
Bước 1: Xác định vị trí Mã hàng nằm trên cột thứ mấy trong bảng
Bạn sẽ dùng hàm MATCH để tìm Mã hàng (ô H2) trong vùng chứa tên mã hàng (A3:E3) để xem mã hàng cần tìm nằm ở cột thứ mấy trong bảng
=MATCH(H2,A3:E3,0)
Chúng ta thấy hàm MATCH cho kết quả là số 2, tương ứng mã hàng này ở cột thứ 2 trong bảng.
Bước 2: Xác định vị trí Mã công ty nằm trên dòng thứ mấy trong bảng
Bạn sẽ dùng hàm MATCH để tìm Mã công ty (ô H3) trong vùng chứa tên công ty (A3:A7) để xem mã công ty cần tìm nằm ở dòng thứ mấy trong bảng:
=MATCH(H3,A3:A7,0)
Kết quả hàm MATCH trong trường hợp này cho là số 3, tương ứng với vị trí dòng thứ 3 trong bảng.
Như vậy bạn đã có cả 2 vị trí: số dòng và số cột trong bảng đơn giá, chỉ cần xác định giao điểm của dòng và cột này là có thể cho ra kết quả đơn giá cần tìm. Để làm việc này chúng ta sẽ sử dụng hàm INDEX.
Bước 3: Xác định kết quả đơn giá cần tìm với hàm INDEX
Khi viết hàm INDEX, bạn cần chỉ ra 3 vị trí:
Bảng cần tìm: bảng A3:E7
Dòng cần tìm: hàm MATCH tìm theo mã công ty
Cột cần tìm: hàm MATCH tìm theo mã hàng
Khi đó chúng ta có hàm INDEX như sau:
=INDEX(A3:E7,I3,I2)
Trong đó:
ô I3 là ô chứa kết quả hàm MATCH tìm theo mã công ty
ô I2 là ô chứa kết quả hàm MATCH tìm theo mã hàng
Nếu viết 1 cách tổng quát, chúng ta có:
=INDEX(A3:E7, MATCH(H3,A3:A7,0), MATCH(H2,A3:E3,0))
Trong trường hợp này chúng ta viết hàm INDEX gồm 3 tham số, khác với 2 ví dụ trước chỉ có 2 tham số. Bởi vì:
Khi vùng tìm kiếm của hàm INDEX chỉ có duy nhất 1 cột thì bạn không cần viết tham số thứ 3 (là cột cần tìm).
Khi vùng tìm kiếm của hàm INDEX có nhiều hơn 1 cột thì bạn bắt buộc phải viết tham số thứ 3 để chỉ có tìm kết quả ở cột nào.
Như vậy chúng ta đã có thể hình dung được cách sử dụng hàm INDEX kết hợp hàm MATCH để thực hiện các yêu cầu dò tìm, tham chiếu, tìm kiếm trong Excel thông qua 4 ví dụ tiêu biểu rồi. Một số kết luận có thể rút ra là:
Hàm MATCH dùng để tìm ra vị trí số dòng, số cột
Hàm INDEX có 2 cách viết: Viết rút gọn nếu chỉ xác định trên 1 cột (hoặc 1 hàng), viết đầy đủ nếu tìm trên 1 vùng gồm nhiều dòng, nhiều cột. Khi viết đầy đủ thì cần tới 2 hàm MATCH
Hàm INDEX+MATCH thay thế được cho hàm VLOOKUP, HLOOKUP trong mọi trường hợp tìm kiếm, và phương pháp tìm kiếm không bị hạn chế.
Những kiến thức bạn đang xem thuộc khóa học Excel từ cơ bản tới nâng cao của chúng tôi Khóa học này cung cấp cho bạn kiến thức một cách đầy đủ và có hệ thống về các hàm, các công cụ trong excel, ứng dụng excel trong công việc… Hiện nay hệ thống đang có nhiều ưu đãi khi bạn đăng ký tham gia khóa học này.
Vì sao dùng INDEX và MATCH tốt hơn dùng VLOOKUP trong Excel Phân biệt hàm VLOOKUP với HLOOKUP và bí quyết sử dụng hàm Hàm vlookup trong Excel và các ứng dụng nâng cao thường gặp
Tải về file mẫu trong bài viếtBạn có thể tải về file mẫu sử dụng trong bài viết tại địa chỉ bên dưới:
Tài liệu kèm theo bài viết
Với sứ mệnh: ” Mang cơ hội phát triển kỹ năng, phát triển nghề nghiệp tới hàng triệu người “, đội ngũ phát triển đã và đang làm việc với những học viện, trung tâm đào tạo, các chuyên gia đầu ngành để nghiên cứu và xây dựng lên các chương trình đào tạo từ cơ bản đến chuyên sâu xung quanh các lĩnh vực: Tin học văn phòng, Phân tích dữ liệu, Thiết kế, Công nghệ thông tin, Kinh doanh, Marketing, Quản lý dự án…
Gitiho tự hào khi được đồng hành cùng:
50+ khách hàng doanh nghiệp lớn trong nhiều lĩnh vực như: Vietinbank, Vietcombank, BIDV, VP Bank, TH True Milk, VNPT, FPT Software, Samsung SDIV, Ajinomoto Việt Nam, Messer,…
Bài Tập Mẫu Hàm If Kết Hợp Left, Right, Mid Trong Excel (Có Lời Giải)
Bài này còn có ví dụ cho việc sử dụng tính năng ADVANCED FILTER để trích lọc dữ liệu từ một bảng cho trước. Phần này sẽ được tách ra trong bài kế tiếp.
Ngoài ra, đây còn là một bài tập ôn thi chứng chỉ tin học văn phòng hiệu quả.
Ở cuối bài, có file Excel XLS chứa bảng dữ liệu và lời giải cho từng ô, bạn cũng có thể tải về máy mở lên tham khảo.
Lý thuyết các bạn có thể xem bài lý thuyết và cách dùng các hàm trong excel.
Bảng dữ liệu bài tậpCâu 1. Điền cột Mã Xuất Nhập Khẩu. Cột này nhận 2 ký tự đầu của cột Mã Số.
Câu 2. Điền cột Tên Sản Phẩm. Biết nếu ký tự thứ ba bằng “G” là Gạo; “D” là Đường; “T” là Than.
Câu 3. Điền cột Số Hiệu. Số hiệu nhận 3 kí tự cuối của cột Mã Số.
Câu 4. Trích lọc những hàng hóa có Mã Số Hiệu là 003.
Câu 1. Điền cột Mã Xuất Nhập Khẩu. Cột này nhận 2 ký tự đầu của cột Mã Số
Theo yêu cầu, để tìm được Mã Xuất Nhập Khẩu ta chỉ cần trích 2 kí tự đầu của chuỗi Mã Số (Cột B). Tức ta dùng hàm LEFT.
Tại ô C4, gõ công thức:
Thủ thuật về cách kéo để copy công thức Thủ thuật về cách kéo để copy công thứcCâu 2. Điền cột Tên Sản Phẩm. Biết nếu ký tự thứ ba bằng “G” là Gạo; “D” là Đường; “T” là Than.
Tên sản phẩm phụ thuộc vào điều kiện của kí tự thứ 3 trong cột Mã số. Như vậy ở đây ta phải dùng hàm IF kết hợp với hàm lấy ký tự thứ 3 trong một chuỗi. Ta dùng hàm MID để lấy ký tự bất kì trong chuỗi.
Ví dụ lấy ký tự thứ 3 trong chuỗi Mã số, ta dùng như sau:
= MID(…Nội dung chỉ dành cho thành viên… )
Nghĩa là, Từ chuỗi B4, trích ra một chuỗi, bắt đầu tứ vị trí thứ 3, lấy một ký tự. Tức ta sẽ được giá trị là “G”
Trở lại yêu cầu bài toán, dựa vào kí tự thứ 3, nếu nó là “G” thì Tên Sản Phẩm là “Gạo”, nếu là “D” thì Tên Sản Phẩm là “Đường”, Nếu là “T” thì Tên Sản Phẩm là “Than”.
Như vậy, tại ô D4, ta gõ công thức sau:
= IF(MID(B4,3,…Nội dung chỉ dành cho thành viên… =”T” , “Than”)))
Lý giải chung cho công thức trên như sau: Nếu kí tự thứ 3 của Mã số là “G” thì hiện ra kết quả là …Nội dung chỉ dành cho thành viên…
Câu 3. Điền cột Số Hiệu. Số hiệu nhận 3 kí tự cuối của cột Mã Số.
Số hiệu nhận 3 kí tự cuối của cột Mã Số tức ta sẽ lấy 3 ký tự từ phía phải qua. Dùng hàm RIGHT.
Tại ô E4, gõ công thức sau:
= RIGHT(B4,3)
Câu 4. Trích lọc những hàng hóa có Mã Số Hiệu là 003
Cập nhật: 15/5: Lời giải trích lọc cho câu 4, mời các bạn xem video này https://www.youtube.com/watch?v=P7zEp3VDdCE (Nhớ chọn chế độ HD và phóng lớn để xem cũng như đừng quên chia sẻ lên FB cho bạn bè.)
Tải file XLS lời giảihttp://hoc.trungtamtinhoc.edu.vn/download/IF-LEFT-RIGHT.zip
Password: …Nội dung chỉ dành cho thành viên…
Trần Triệu Phú – chúng tôi
Nếu bạn muốn cảm ơn, vui lòng sử dụng các icon Facebook phía dưới cùng để chia sẻ cho bạn bè mình. Đó là cách để giới thiệu cũng như giúp chúng tôi phát triển.
Bài Tập Mẫu Hàm Vlookup Trong Excel (Có Lời Giải)
Bài này còn có ví dụ cho việc áp dụng hàm VLOOKUP kết hợp với các làm trích kí tự LEFT, RIGHT để dò tìm và lấy giá trị từ một bảng cho trước.
Ngoài ra, đây còn là một bài tập ôn thi chứng chỉ A tin học văn phòng hiệu quả.
Ở cuối bài, có file Excel XLS chứa bảng dữ liệu và lời giải cho từng ô, bạn cũng có thể tải về máy mở lên tham khảo.
Bảng dữ liệu bài tậpCâu 1) Dựa vào kí tự đầu và 2 kí tự cuối của “Mã HĐ”, tìm trong “Bảng Danh Mục” đề lấy tên mặt hàng điền vào cột “Mặt Hàng”.
Câu 2) Tìm trong “Bảng Danh Mục” và điền giá trị vào cột “Đơn vị”
Câu 3) Tìm trong “Bảng Danh Mục” và điền giá trị vào cột “Đơn giá”
Câu 4) Tính cột “Thành tiền” bằng “Đơn giá” nhân “SLượng”
Câu 5) Tính và điền giá trị vào bảng “Tổng số tiền bán được của các mặt hàng”
Câu 1:
Tại ô C4, gõ hàm:
Ý nghĩa là: Lấy bên trái của B4 1 kí tự rồi ghép với 2 kí tự bên phải, đem tìm cụm này trong “Bảng Danh Mục” theo chiều dọc và lấy kết quả là giá trị ở cột thứ 2 của bảng. Tìm chính xác.
Thủ thuật về cách kéo để copy công thức Thủ thuật về cách kéo để copy công thứcCâu 2:
Tại ô E4, gõ hàm:
Ý nghĩa tương tự như câu 1, nhưng lấy kết quả là giá trị ở cột thứ 3 của bảng (Tương ứng là cột Đơn vị).
Câu 3:
Tại ô G4, gõ hàm:
Ý nghĩa tương tự như câu 1, nhưng lấy kết quả là giá trị ở cột thứ 4 của bảng (Tương ứng là cột Đơn giá).
Câu 4:
Tại ô H4, gõ công thức:
=F4*G4
Ý nghĩa: Lấy giá trị cột F4 nhân cột G4.
Câu 5:
– Tại ô B29, gõ hàm:
= SUMIF(B4:B13, “S*M?”, H4:H13)
Ý nghĩa: Tính tổng doanh số của tất cả các hóa đơn bán đĩa mềm. Đây là tính tổng có điều kiện, vì vậy ta dùng SUMIF. Điều kiện là hóa đơn bán đĩa mềm, tức là các “Mã HĐ” sẽ có dạng
Vì vậy công thức trên có ý nghĩa là tìm trong cột B4:B13, Mã HĐ nào có dạng S*M? thì cộng các giá trị tương ứng trong cột H4:H13
– Tương tự, tại ô C29, gõ hàm:
– Tương tự, tại ô D29, gõ hàm:
Password giải nén là:
Trần Triệu Phú
Nếu bạn muốn cảm ơn, vui lòng sử dụng các icon Facebook phía dưới cùng để chia sẻ cho bạn bè mình. Đó là cách để giới thiệu cũng như giúp chúng tôi phát triển.
Cách Kết Hợp Hàm Index Và Match Trong Excel
Hàm tìm kiếm trong Excel thuộc nhóm hàm cơ bản Excel và có rất nhiều lựa chọn khác nhau để bạn tìm được giá trị mình cần trong bảng, như dùng hàm Vlookup Excel hay hàm Hlookup. Tuy nhiên 2 hàm tìm kiếm trên sẽ chỉ tìm giá trị theo hàng hoặc cột, theo 1 chiều mà không tìm kiếm giá trị trên cả hàng và cột.
Hướng dẫn kết hợp hàm Index và Match trong Excel1. Hàm Index trong Excel
Hàm Index có 2 dạng là hàm Index dạng mảng và Index dạng tham chiếu.
Hàm Index dạng mảng trả về giá trị của 1 ô dữ liệu có chỉ số hàng và chỉ số cột đang tìm kiếm. Cú pháp hàm dạng mạng là Index (Array, Row_num,[column_num]). Trong đó:
Array: Mảng dữ liệu tham chiếu.
Row_num: Hàng chứa giá trị cần lấy.
Column_num: Cột chứa giá trị cần lấy.
Hàm Index dạng tham chiếu trả về giá trị của 1 ô có chỉ số hàng và cột đang tìm kiếm. Cú pháp hàm dạng tham chiếu là INDEX (Reference, Row_num, [Column_num], [Area_num]. Trong đó:
Reference: Vùng tham chiếu chứa giá trị cần tìm.
Row_num: Chỉ số hàng chứa giá trị cần tìm.
Column_num: Chỉ số cột chứa giá trị cần tìm.
Area_num: Số vùng trả về, để trống mặc định là 1.
Tìm hiểu thêm về cách sử dụng hàm Index trong bài viết Cách dùng hàm Index trong Excel.
2. Hàm Match trong Excel
Hàm Match trả về số thứ tự của giá trị cần tìm trong bảng. Cấu trúc hàm Match là =MATCH (Lookup_Value, Lookup_array, [Match_type]). Trong đó:
Lookup_Value: Giá trị cần tìm kiếm.
Lookup_array: Mảng chứa giá trị cần tìm.
Match_type: Kiểu tìm kiếm. Có 3 kiểu tìm kiếm:
Tìm kiếm giá trị nhỏ hơn giá trị cần tìm kiếm khi match_type =1.
Tìm kiếm giá trị bằng giá trị cần tìm kiếm khi match_type =0.
Tìm kiếm giá trị lớn hơn giá trị cần tìm kiếm khi match_type = -1.
Cách dùng hàm Match bạn đọc trong bài viết Cách dùng hàm Match trong Excel.
Bài tập 1: Tìm kiếm chất liệu của mặt hàng Tất cổ chân trong bảng (Tìm từ phải qua trái)
Theo bảng chúng ta thấy vị trí của Tất cổ chân nằm ở STT 4 và dóng sang sẽ thấy chất liệu của Tất cổ chân là Len dệt kim. Như vậy chúng ta sẽ nhìn vào cột Chất liệu và tìm tới dòng số 5 sẽ ra được kết quả.
Bước 1:
Trước hết chúng ta sẽ tìm mặt hàng Tất cổ chân nằm ở vị trí nào trong bảng. Nhập công thức =MATCH(“Tất cổ chân”,B1:B7,0) rồi nhấn Enter. Trong đó:
Tất cổ chân: Là giá trị cần tìm đúng vị trí.
B1:B7: Vùng tìm kiếm cho giá trị, ở đây là cột Mặt hàng.
0: Tìm giá trị chính xác.
Kết quả sẽ ra 5, nghĩa là Tất cổ chân ở vị trí dòng thứ 5 trong bảng.
Bước 2:
Bây giờ chúng ta sẽ tìm giá trị tại cột Chất liệu ứng với giá trị tại dòng số 5, sẽ ra được chất liệu cho mặt hàng Tất cổ chân.
Công thức kết hợp hàm Index với hàm Match là =INDEX( cột cần tra cứu giá trị, (MATCH (giá trị dùng để tra cứu, cột chứa giá trị, 0)).
Áp dụng vào bài tập 1 thì có thể thay cả cụm MATCH (giá trị dùng để tra cứu, cột chứa giá trị, 0)) = 5, thứ tự của giá trị dùng để tra cứu.
Nhập công thức là =INDEX(D1:D7,5) và nhấn Enter. Trong đó D1:D7 là cột chứa giá trị cần tra cứu.
Kết quả sẽ ra chất liệu Len dệt Kim.
Công thức tổng quát kết hợp hàm Index với hàm Match khi áp dụng vào bài tập 1 là =INDEX(D1:D7,MATCH(“Tất cổ chân”,B1:B7,0)) rồi nhấn Enter.
Kết quả cũng cho ra Len dệt kim ứng với Tất cổ chân.
Bài tập 2: Tìm mặt hàng ứng với chất liệu Vải lanh trong bảng (Tìm từ trái sang phải) Bước 1:
Trước hết chúng ta cần tìm vị trí của Vải lanh trong cột Chất liệu. Bạn nhập công thức =MATCH(“Vải lanh”,D1:D7,0) rồi nhấn Enter. Trong đó:
Vải lanh: Cần tìm vị trí cho giá trị này.
D1:D7: Vùng dữ liệu chứa giá trị cần tìm vị trí.
0: Tìm chính xác giá trị này.
Kết quả ra 7, Vải lanh nằm ở dòng số 7 tại cột Chất liệu.
Bước 2:
Kết hợp với hàm Index chúng ta có công thức là =INDEX(B1:B7,7) rồi nhấn Enter.
Kết quả sẽ hiển thị Quần nữ ngắn đúng với bảng.
Chúng ta sẽ kết hợp hàm Index với hàm Match trong cùng 1 công thức nhập là =INDEX(B1:B7,MATCH(“Vải lanh”,D1:D7,0)) rồi nhấn Enter.
Kết quả cũng cho ra ứng với Quần nữ ngắn.
Như vậy khi kết hợp hàm Index với hàm Excel thì chúng ta sẽ tìm kiếm được giá trị theo cả 2 chiều từ trái sang phải hay từ phải sang trái. Với hàm VLOOKUP, chúng ta chỉ có thể tra cứu dữ liệu từ trái qua phải mà thôi.
Cập nhật thông tin chi tiết về Hướng Dẫn Giải Bài Tập Dùng Hàm Index Kết Hợp Hàm Match Có File Mẫu trên website Hoisinhvienqnam.edu.vn. Hy vọng nội dung bài viết sẽ đáp ứng được nhu cầu của bạn, chúng tôi sẽ thường xuyên cập nhật mới nội dung để bạn nhận được thông tin nhanh chóng và chính xác nhất. Chúc bạn một ngày tốt lành!