Hàm Index cùng Match là nhì hàm rất đặc biệt trong docongtuong.edu.vn mà hoàn toàn có thể thay thế được đến hàm VLOOKUP. Để nắm rõ hơn phương pháp sử dụng tương tự như các ứng dụng của hai hàm này, chúng ta đã cùng rèn luyện cách sử dụng hàm INDEX với MATCH trải qua các bài tập kèm theo lời giải nhé.
Bạn đang xem: Bài tập về hàm index và match trong excel
Bài tập 1: thực hiện hàm MATCH tìm địa chỉ theo dòng, cột
Cho bảng tính sau:
Nếu mong muốn biết thông tin của người mang tên “Lê Thị Thủy” thì chúng ta phai làm vắt nào? chắc rằng việc đầu tiên bạn cần xác định coi người đó nằm ở vị trí thứ mấy trong bảng tính này.
Bằng cách thực hiện hàm MATCH, các bạn sẽ biết được 1 người nào đó nằm ở chiếc thứ mấy trong bảng.
Ta có công thức sau:
=MATCH(“Lê Thị Thủy”, cột chúng ta tên, số 0)
Như vậy hàm MATCH cho tác dụng là số 3, tương ứng với mẫu thứ 3 trong bảng tài liệu trên.
Tham khảo: Cách thực hiện hàm MATCH trong docongtuong.edu.vn
Vậy việc tìm và đào bới ra số 3 thì có ý nghĩa gì? Hay nói theo cách khác biết người đó ở vị trí mẫu thứ 3 trong bảng thì có công dụng gì?
Để trả lời câu hỏi này, bọn họ xem tiếp yêu mong dưới đây
Bài tập 2: Kết hòa hợp hàm INDEX với MATCH tìm email theo tên
Khi quan sát vào bảng trên, ta thấy trên vị trí dòng 3, vào cột thư điện tử sẽ mở ra Email cần tìm. Như vậy có thể hiểu: chỉ cần dóng theo cột Email, tại vị trí loại 3 vào bảng là họ có hiệu quả cần tìm.
Điều đó chính là cách phối hợp hàm INDEX cùng với MATCH:
Trong cách làm tại ô D15 tra cứu Email, bọn họ thấy:
=INDEX(E3:E8,MATCH(D12,C3:C8,0))
Công thức này, hàm INDEX gồm nhiệm vụ:
Chỉ ra cột cất giá trị đề nghị tìm là cột E, vùng E3:E8Chỉ ra dòng chứa giá bán trị phải tìm, cái này đã xác định vị hàm MATCH ở trênGiao điểm giữa chiếc và cột vào hàm INDEX tìm đó là kết quả email cần tìm.
Tham khảo: 6 tính năng công dụng nhất của hàm INDEX vào docongtuong.edu.vn
Bài tập 3: Dò tra cứu từ yêu cầu qua trái cùng với hàm INDEX kết hợp MATCH
Thông thường họ hay dò kiếm tìm theo chiều từ Trái qua Phải. Tuy thế nếu đề nghị tìm từ bắt buộc qua Trái (ngược lại cùng với thông thường) thì làm cầm cố nào?
Ví dụ như sau: tra cứu tên dựa vào thông tin Email?
Cột e-mail là cột đồ vật 3, nằm sát phải cột họ tên (cột vật dụng 1).
Như vậy để dò tìm chúng ta tên phụ thuộc vào Email có nghĩa là chúng ta đang tra cứu từ đề nghị qua trái.
Quá trình thực hiện như sau:
Dùng hàm MATCH để tra cứu vị trí loại của thư điện tử trong cột thư điện tử =MATCH(Email, vùng cất Email, 0)Kết hợp với hàm INDEX trong việc chỉ ra địa điểm cột chứa công dụng cần tìm, là cột chúng ta tên (cột C), vùng C3:C8=INDEX(C3:C8,MATCH(D12,E3:E8,0))
Rất đơn giản đề xuất không nào.
Bài tập 4: Xác định đơn giá chỉ của sản phẩm theo nhiều điều kiện
Ví dụ ta có bảng đơn giá chỉ như sau:
Làm cách nào xác định được đơn giá của một mã sản phẩm bất kỳ, vào 1 doanh nghiệp bất kỳ?
Điều này tương đương việc bọn họ phải xét đồng thời 2 điều kiện: Mã hàng và Mã công ty
Để tiến hành yêu cầu này, bọn họ sẽ sử dụng hàm Index phối kết hợp hàm Match như sau:
Bước 1: Xác định địa điểm Mã hàng nằm trên cột sản phẩm công nghệ mấy vào bảng
=MATCH(H2,A3:E3,0)
Bước 2: Xác định vị trí Mã công ty nằm trên dòng thứ mấy vào bảng
=MATCH(H3,A3:A7,0)
Như vậy giao điểm của dòng 3, cột 2 bao gồm là ô B5 (bởi bảng bắt đầu tính từ cái 3 trở đi, yêu cầu dòng 5 ứng cùng với vị trí loại thứ 3 của bảng 1-1 giá)
Bước 3: Xác định kết quả đơn giá nên tìm với hàm INDEX
Việc sót lại là sử dụng hàm INDEX để liên kết giữa chiếc và Cột đã xác định được ở trên.
Trong hàm INDEX này ta có:
Thay vì chưng tham chiếu tới 1 cột cố định tại tham số đầu tiên, chúng ta tham chiếu cho tất cả vùng bảng A3:E7.Trong bảng này, số loại xác định vị hàm MATCH tại ô I3, số cột xác định bởi vì hàm MATCH tại ô I2.Kết quả của hàm INDEX là giao điểm giữa số dòng và số cột đã xác định được.=INDEX(A3:E7,I3,I2)
Kết luận
Như vậy thông qua 4 bài xích tập ví dụ, bọn chúng ta đã hoàn toàn có thể hình dung được cách áp dụng hàm INDEX phối hợp hàm MATCH để thực hiện các yêu ước dò tìm, tham chiếu, tìm kiếm trong docongtuong.edu.vn. Khi ứng dụng tốt cách làm cho này, các bạn hoàn toàn có thể thực hiện được mọi yêu ước tìm kiếm phức tạp mà hàm VLOOKUP khó đáp ứng được.
Thay do phải đắn đo áp dụng hàm VLOOKUP xuất xắc HLOOKUP, làm cách làm dài cái khi phải phối kết hợp VLOOKUP cùng với hàm MATCH, HLOOKUP cùng với hàm MATCH, thì chúng ta chỉ cần dùng INDEX phối hợp MATCH là xong.
Hàm INDEX trong docongtuong.edu.vn được coi là một hàm tìm kiếm kiếm. Khả năng vận dụng và phối hợp của hàm INDEX với những hàm khác khá mạnh dạn mẽ. Rất có thể áp dụng và xử lý nhiều hơn các vấn đề phức tạp.
Hàm INDEX phối kết hợp hàm match gồm hay rộng VLOOKUP. Cú pháp, lấy ví dụ minh họa, bài tập và cách vận dụng hàm linh hoạt trong docongtuong.edu.vn,…
Các chủ thể trên sẽ được Trường lí giải một cách gấp rút và dễ hiểu ở sau đây bạn nhé.
Cam kết 100% chúng ta đọc kỹ nội dung bài viết này đang hiểu cùng vận cần sử dụng hàm này một bí quyết dễ dàng.
CÁC NỘI DUNG CHÍNH BẠN SẼ ĐƯỢC HỌC:
– Định nghĩa và Cú pháp
– lấy một ví dụ thực hành
– INDEX phối hợp MATCH vs. Hàm VLOOKUP (Nâng cao)
– Hàm INDEX cùng với hàm SUM (Nâng cao)
– cùng với hàm MIN/MAX (Nâng cao)
– INDEX cùng với hàm AVERAGE (Nâng cao)
– bài bác tập
Now, let’s go!
1. ĐỊNH NGHĨA VÀ CÚ PHÁP HÀM INDEX
1.1. HÀM INDEX LÀ GÌ?
Index là hàm docongtuong.edu.vn trả về giá trị hoặc tham chiếu của 01 ô vào docongtuong.edu.vn là giao nhau giữa chiếc và cột được xác minh bởi các thông số cho trước như:
– Điểm tham chiếu (Tính từ add nào)
– số lượng dòng
– số lượng cột
– Vùng ô
Lưu ý:
Bạn hoàn toàn có thể coi hàm này là Hàm tìm kiếm vào docongtuong.edu.vn.
Bạn rất có thể dùng để tìm ngược giỏi tìm xuôi một giải pháp dễ dàng. Hơn nhiều hàm VLOOKUP chỉ kiếm được theo chiều từ bỏ trái qua phải.
Hàm INDEX bao gồm 2 dạng:
Một là công dụng của hàm INDEX vẫn trả về quý giá của một ô (Khi các bạn đã cho các giá trị tham chiếu kèm theo)Hai là trả về một dải ô tham chiếu, được quy định bởi1.2. CÚ PHÁP HÀM INDEX
Hàm này có 2 cú pháp nhằm bạn áp dụng (áp dụng dòng nào tùy vào hoàn cảnh bạn nhé)
= INDEX(Array, Row_Num,
= INDEX (Reference, Row_Num,
Khi chúng ta tìm một quý hiếm theo 1 điều kiện thì chọn phương án 1
Thường thì thực hiện công thức sản phẩm 2, bạn áp dụng hàm mảng hoặc kết phù hợp với các hàm như Sum, sumif…
TRONG ĐÓ:
– Array: Mảng hoặc rất có thể hiểu là vùng dữ liệu
– Row_num: con số dòng tính tự dòng trước tiên của Array đến loại chứa giá chỉ trị buộc phải tìm kiếm
– Column_num: con số cột tính tự cột trước tiên của Array cho cột đựng trị đề nghị tìm kiếm
– Area_num: lựa chọn một vùng dữ liệu để tham chiếu tại đó trả về việc giao nhau giữa cái và cột
Row_num và Column_num giao nhau sẽ khẳng định được ô đề nghị tìm là ô nào đúng không ạ các bạn.
2. VÍ DỤ MINH HỌA CÁCH SỬ DỤNG HÀM INDEX vào docongtuong.edu.vn (CƠ BẢN)
Cho bảng tài liệu như hình ảnh dưới.
Yêu cầu: tìm lợi nhuận của cửa hàng có địa chỉ thứ 3 (Cửa mặt hàng C) tính trường đoản cú cửa hàng đầu tiên trong danh sách
Giả định đã biết trước vị trí shop C trong bảng lắp thêm tự số 3 từ bên trên xuống.
(Để biết được siêu thị đó bao gồm vị trí sản phẩm 3 bạn phải học về hàm MATCH: xem ngay)
Cú pháp hàm trong trường vừa lòng này như sau:
=INDEX($B$2:$C$7,3,2)
Trong đó:
– $B$2:$C$7 là Array/ Vùng cất giá trị mong muốn tìm
– 3 – Số sản phẩm tự chiếc của cửa ngõ hàng mình thích tìm doanh số tính từ cửa hàng đầu tiên vào vùng (Dịch đi 3 dòng tính cả B2 =B2 -> B3 ->B4)
– 2 – Số đồ vật tự cột chứa lợi nhuận tính tự cột thứ nhất của vùng – array (Từ cột B là cột sản phẩm công nghệ nhất, tính đến cột thứ 2 = cột C
=> kết quả trả về C4 = 1,300
Như vậy thông qua ví dụ dễ dàng và đơn giản này, bạn có thể kết luận:
Hàm INDEX có tính năng như một hàm kiếm tìm kiếm nâng cấp trong docongtuong.edu.vn.
Ở phần bên dưới Trường sẽ sở hữu được ví dụ sâu hơn và phân tích mạnh mẽ yếu để bạn hiểu thiệt sâu hàm này.
3. VẬN DỤNG HÀM INDEX KẾT HỢP HÀM MATCH trong docongtuong.edu.vn (NÂNG CAO)
Một đối chiếu nhanh về 3 hàm quan trọng: VLOOKUP, MATCH, INDEX, để dễ tưởng tượng trước khi bắt đầu:
| VLOOKUP | MATCH | INDEX |
| Hàm VLOOKUP kiếm tìm kiếm trong bảng với điều kiện tìm kiếm tương ứng. Xem ví dụ bên dưới |
Câu trả lời: Dùng hàm MATCH để tìm số trang bị tự dòng chúng ta ạ. Xem ngay lập tức hàm MATCH
Hàm MATCH sẽ đóng vai trò tìm thấy số sản phẩm tự mẫu (hoặc cột) cơ mà giá trị đó đang đứng => Sau đó, chúng ta tham chiếu quý phái giải ô tất cả độ lâu năm tương ứng. docongtuong.edu.vn sẽ tìm tới vị trí MATCH đã tham chiếu để tìm trả giá trị nhưng mà INDEX trả về.
VÍ DỤ CÁCH KẾT HỢP HÀM INDEX VÀ MATCH
Ví dụ tiếp sau đây sẽ phân tích và lý giải rõ cách Hàm INDEX phối hợp hàm MATCH vào docongtuong.edu.vn như thế nào.
Yêu cầu:
Tìm doanh thu của siêu thị DTìm tên cửa hàng khi biết trước doanh số3.1. YÊU CẦU SỐ 1: TÌM DOANH SỐ CỦA CỬA HÀNG DBạn có thể dùng hàm VLOOKUP để tìm nếu gặp yêu cầu này đúng không nào nào.
Nhưng ta đã biết hàm INDEX rồi thì nên tận dụng nó nhé.
Cú pháp hàm INDEX phối kết hợp hàm MATCH để xử lý yêu cầu này như sau:
=INDEX($B$2:$C$7,MATCH(C9,$B$2:$B$7,0),2)
Trong đó:
– $B$2:$C$7: Array/ vùng dữ liệu
– MATCH(C9,$B$2:$B$7,0): công dụng của hàm này đã trả về số thứ tự cái của cửa hàng D trong Array
– 2: Là cột thứ 2 tính từ bỏ cột thứ nhất mà cột đó chứa giá trị search kiếm = Doanh số
Kết quả ta nhận thấy như sau:
Bạn hãy thiết lập file về, chuyển đổi giá trị vào ô C9, kết quả trong ô C10 sẽ auto đổi theo đó bạn.
Hoặc chúng ta có thể thay đổi khớp ứng doanh thu bạn sẽ thấy kết quả thay đổi theo.
3.2. YÊU CẦU SỐ 2: TÌM TÊN CỬA HÀNG lúc BIẾT TRƯỚC DOANH SỐSử dụng hàm VLOOKUP để tìm ngược từ yêu cầu qua trái là một thách thức thực sự với tương đối nhiều người học docongtuong.edu.vn.
Nhưng khi dùng hàm INDEX thì quá dễ dãi bạn ạ.
Phương án xử lý trong trường hòa hợp này như sau:
Tìm ra vị trí của doanh số biết trước vào cột doanh sốTìm trong cột tên cửa ngõ hàng: shop đang có lợi nhuận tương ứng
Trong đó:
Cụ thể cách áp dụng hàm gồm trong hình sau:
4. HÀM INDEX KẾT HỢP HÀM MATCH SO VỚI HÀM VLOOKUP
Cái nào mạnh hơn, tuyệt hơn
Trong docongtuong.edu.vn, để hiểu rằng hàm như thế nào mạnh hơn thế thì phải xem chuyên môn của người tiêu dùng ở mức nào.
Nếu tại mức cơ bạn dạng thì hàm VLOOKUP hay hơn bởi vì nó phổ biến hơn. Hàm VLOOKUP là trong những hàm các bạn phải học khi dùng docongtuong.edu.vn, còn INDEX thì không.
Nếu ở mức độ cao hơn thì tùy vào sở trường mỗi người.
Nhưng có một số điểm chúng ta có thể tham khảo để xem chắt lọc hàm nào những hơn.
VLOOKUP chỉ tìm kiếm được khi cột chứa tài liệu tìm kiếm mở đầu bảngVLOOKUP chỉ tìm được từ trái qua phải
INDEX không riêng biệt cột chứa tài liệu là cột nào, chỉ cần tìm ra vị trí
Do kia INDEX nhiều di năng hơn
INDEX dùng sẽ giảm sút độ rộng vùng cần quét => dung lượng file tối ưu hơn
5. INDEX KẾT HỢP CÙNG HÀM SUM
Bài toán vẫn cũ, tuy vậy đổi yêu thương cầu:Tính doanh số cột C:
6. INDEX KẾT HỢP CÙNG HÀM MIN/ MAX
Bài toán vẫn cũ, nhưng lại đổi yêu ước khi phối hợp MIN/ MAXTìm shop doanh số phải chăng nhất:
7. INDEX KẾT HỢP CÙNG NHÓM HÀM AVERAGE
Bài toán vẫn cũ, mà lại đổi yêu thương cầu:Tính vừa đủ cộng lợi nhuận 4 siêu thị từ số 2 mang đến số 5
8. Download FILE MẪU VÀ BÀI TẬP THỰC HÀNH
Hãy tải về ngay file sau đây nếu:
Các bạn muốn có được file mẫu cho các ví dụ phía trên.Muốn thực hành để thành thạo hàm này hơn nữa.Xem thêm: Tổng Hợp 15 Món Ăn Ngon Nhất Thế Giới Được Cnn Bình Chọn, Top 22 Món Ăn Ngon Nhất Thế Giới
Click link sau đây để tải về bài tập các bạn nhé.