Hãy chữa khỏi sự nhầm lẫn về Excel bằng những ví dụ thực tế về cách sử dụng hàm INDEX và MATCH trong Excel để trở thành người thành thạo các hàm và trường dữ liệu trong phần mềm tính toán của bộ văn phòng.
Kỹ năng tin học văn phòng là một trong những kiến thức không thể thiếu đối với những người làm công việc văn phòng hay bất kỳ người tìm việc nào hiện nay, trong đó có liên quan đến công việc sổ sách kế toán. Trong phần mềm tính toán excel của tin học văn phòng, hàm index và hàm match được kết hợp với nhau để lấy giá trị theo hàng, cột và trả về kết quả tốt, giúp người dùng dễ dàng thay thế hàm tìm kiếm công thức. Tìm kiếm Vlookup và Hlookup. Nếu bạn chưa biết về hàm index trong Excel và các thủ thuật excel liên quan đến hàm này thì hãy đọc bài viết dưới đây để biết thêm thông tin chi tiết.
I. Các hàm INDEX và MATCH trong Excel được sử dụng như thế nào?
Hàm INDEX và MATCH trong Excel được sử dụng như thế nào?
1. Công thức hàm INDEX trong Excel
Hàm INDEX trong Excel được chia thành hàm chỉ mục mảng và hàm chỉ mục tham chiếu. Hàm chỉ mục mảng sẽ trả về giá trị của một phần của bảng hoặc mảng được chọn bởi chỉ mục số hàng và cột. Sử dụng hàm chỉ mục mảng nếu đối số đầu tiên của nó là một hằng số mảng. Hàm INDEX làm tham chiếu trả về tham chiếu ô tại giao điểm của một hàng và cột cụ thể.
Hàm INDEX của mảng:
Cú pháp của hàm INDEX trong mảng như sau: = INDEX (Array, Row_num,[Column_num])
Trong đó các thành phần của hàm INDEX là các mảng:
Mảng: là phạm vi ô hoặc một hằng số mảng là giá trị bắt buộc
Row_num: hàng trong mảng đã chọn mà từ đó một giá trị được trả về
Column_num: cột trong mảng được chọn để trả về một giá trị.
Công thức hàm INDEX của mảng bắt buộc phải có ít nhất một trong hai đối số, Row_num hoặc Column_num.
Công thức hàm INDEX theo tham chiếu:
Cú pháp của hàm INDEX dưới dạng tham chiếu: = INDEX (Tham chiếu, Row_num,[Column_num],[Area_num])
Trong đó các thành phần của hàm INDEX ở dạng tham chiếu:
Reference: là vùng tham chiếu, đây là giá trị bắt buộc.
Row_num: số hàng để trả về giá trị tham chiếu, bắt buộc phải có.
Column_num: chỉ số cột để trả về một tham chiếu, giá trị tùy chọn.
Area_num: Số phạm vi ô trả về giá trị trong tham chiếu. Nếu phần tử Area_num bị bỏ qua, thì hàm INDEX trong excel sẽ sử dụng phạm vi 1, là một giá trị tùy chọn.
2. Hàm MATCH trong Excel
Cú pháp hàm MATCH trong Excel: = MATCH (Giá trị tìm kiếm, Mảng giá trị tìm kiếm,[Match_type])
Trong đó các thành phần của hàm MATCH:
Lookup_value: là giá trị tìm kiếm (có thể là giá trị số, giá trị lôgic, văn bản, văn bản hoặc giá trị lôgic, tham chiếu ô đến một số)
Lookup_array: là mảng để tìm kiếm, là giá trị bắt buộc
Match_type: là kiểu tìm kiếm, giá trị là tùy chọn.
Có 3 loại tìm kiếm đó là:
1: Ít hơn các tìm kiếm cho một giá trị nhỏ hơn giá trị tìm kiếm
0: Đối sánh chính xác: giá trị tìm kiếm chính xác
-1: Nhiều hơn các tìm kiếm cho các giá trị lớn hơn giá trị tìm kiếm
Khi bị bỏ qua, hàm MATCH mặc định là 1.
II. Tại sao sử dụng hàm INDEX và MATCH tốt hơn hàm VLOOKUP
1. Hàm Vlookup là một công thức 1 chiều
Với chức năng VLOOKUP, người dùng chỉ có thể tra cứu dữ liệu từ trái sang phải. Trong ví dụ dưới đây, nếu bạn thực hiện tìm kiếm quốc gia có thủ đô là Seoul, bạn sẽ không thể sử dụng chức năng tìm kiếm VLOOKUP để tìm Hàn Quốc. Tuy nhiên, một thủ thuật excel sẽ giải quyết một vấn đề như thế này, đó là kết hợp các hàm INDEX và MATCH trong một công thức.
Sử dụng hàm INDEX và MATCH tốt hơn hàm VLOOKUP
2. Hàm VLOOKUP sẽ sai nếu thêm hoặc bớt các cột trong bảng tính.
Khi sử dụng hàm VLOOKUP, người dùng phải chỉ định cột nào để nhận giá trị trả về. Khi cộng hoặc trừ một cột ở giữa vị trí cột đầu tiên và cột lấy giá trị, cột lấy giá trị đó bị dịch chuyển và kết quả là công thức của hàm Vlookup không còn đúng nữa. Với hàm INDEX và MATCH, điều này không bao giờ xảy ra vì khi thêm hoặc bớt cột, công thức của hàm INDEX và MATCH luôn được điều chỉnh.
III. Hướng dẫn cách kết hợp hàm INDEX và MATCH để thay thế hàm Vlookup trong Excel
Thủ thuật excel kết hợp các hàm index và match thay thế cho hàm tìm kiếm vlookup trong excel thông qua ví dụ sau: Tìm kiếm chất liệu của mặt hàng là tất mắt cá trong bảng dữ liệu.
Kết hợp các hàm INDEX và MATCH để thay thế hàm Vlookup trong Excel
Người dùng muốn tìm chất liệu của vớ mắt cá trong bảng. Công thức để tìm kết quả như sau:
= INDEX (D1: D7, MATCH (“Tất mắt cá”, B1: B7,0))
Trong công thức hàm chỉ mục trong excel ở trên:
D1: D7 là cột chứa dữ liệu mà người dùng muốn tra cứu, tìm kiếm hoặc lọc
MATCH (“Ankle Socks”, B1: B7,0) sẽ cho chúng ta biết chất liệu của tất đến mắt cá chân thuộc hàng gì trong bảng tính trên.
Nói chung, chúng ta có công thức hàm chỉ mục trong excel:
= INDEX (vùng chứa dữ liệu cần tra cứu, (MATCH (giá trị cần tra cứu, cột chứa giá trị này, 0))
IV. Thông tin thêm về cách kết hợp các hàm INDEX và MATCH
1. Phương pháp tìm kiếm từ phải sang trái với hàm INDEX + MATCH
Cho bảng dữ liệu như trong bảng. Nếu muốn tìm các mặt hàng tương ứng với chất liệu Linen trong bảng, người dùng không thể sử dụng chức năng vlookup, chức năng này chỉ có thể tìm kiếm theo vị trí từ trái qua phải. Để giải quyết vấn đề, người dùng phải sử dụng hàm INDEX + MATCH như sau:
Phương pháp tìm kiếm từ phải sang trái với hàm INDEX + MATCH
Công thức để sử dụng lần này là: = INDEX (B1: B7, MATCH (“Linen”, D1: D7,0))
2. Bài tập tìm đơn giá của một mặt hàng theo nhiều điều kiện
Tìm đơn giá của từng mặt hàng có giá trị là mã hàng AT và mã công ty SA với bảng số liệu cho sẵn như sau:
Bài tập tìm đơn giá một mặt hàng theo nhiều điều kiện
Chúng ta có công thức sau: = INDEX (A3: E7, MATCH (H3, A3: A7,0), MATCH (H2, A3: E3,0))
V. Ứng dụng INDEX và MATCH nâng cao trong Excel
Một số thủ thuật excel nâng cao được tích hợp nhằm mục đích nâng cao trải nghiệm người dùng. Ví dụ, với bảng dữ liệu đã cho như hình dưới đây, hãy nhập giá trị vào cột D. Công thức của hàm xác định như sau:
{= INDEX ($ F $ 5: $ H $ 16, MATCH (1, (A5 = $ F $ 5: $ F $ 16) * (B5 = $ G $ 5: $ G $ 16), 0), 3)}
Trong đó:
(A5 = $ F $ 5: $ F $ 16): so sánh giá trị của A5 với dải dữ liệu $ F $ 5: $ F $ 16.
(B5 = $ G $ 5: $ G $ 16): so sánh giá trị của B5 với dải dữ liệu $ G $ 5: $ G $ 16.
Ứng dụng INDEX và MATCH nâng cao trong Excel
TẠI VÌ. Tổng kết
Như vậy, với chức năng index và match, người dùng có thể dễ dàng tra cứu các giá trị dựa trên nhiều điều kiện cho trước. Hai chức năng này hiệu quả hơn nhiều với chức năng tìm kiếm hlookup hoặc vlookup. Đây là một trong những thủ thuật mà excel cung cấp cho người dùng.
Bài viết này giúp bạn nắm bắt được thông tin về các thủ thuật khi sử dụng các ứng dụng văn phòng. Để từ đó có thể sử dụng trực tiếp những thủ thuật này để đáp ứng các yêu cầu của bạn. Việc hiểu biết các thông tin trên sẽ giúp cho công việc của bạn được thực hiện dễ dàng và thuận tiện hơn. Ngoài ra, nếu bạn còn điều gì thắc mắc, hãy truy cập website: https://www.amorstay.com.vn/ để biết thêm các thông tin chi tiết hơn nhé.