Khóa học
Nhập môn Thống kê với Google Sheets
4 giờ
48.5K
Thông thường, khi mới học sử dụng Excel lần đầu, rất có thể VLOOKUP sẽ được giới thiệu như phương pháp chính để xử lý dữ liệu. VLOOKUP đúng là một hàm mạnh mẽ, có thể thực hiện nhiều tác vụ liên quan đến xử lý dữ liệu như minh họa trong Xử lý dữ liệu với VLOOKUP trong bảng tính. Trong cùng bài hướng dẫn đó, chúng tôi cũng đã đề cập các kiến thức cơ bản về kết hợp INDEX-MATCH, một phương pháp khác để tra cứu dọc trong bảng, có một số ưu điểm so với VLOOKUP nhưng công thức phức tạp hơn.
Những ưu điểm này bao gồm các tính năng như:
- Tham chiếu cột động: Cho phép bạn chèn và xóa cột trong tập dữ liệu một cách an toàn mà không ảnh hưởng đến hàm tra cứu.
- Tốc độ xử lý cao hơn với tập dữ liệu lớn: Nếu bảng của bạn rất rộng, bạn sẽ nhận thấy INDEX-MATCH nhanh hơn nhiều so với VLOOKUP vì INDEX-MATCH chỉ quan tâm đến cột bạn muốn lấy và cột dùng để tra cứu.
- Vị trí của giá trị tra cứu: VLOOKUP không thể tra cứu các giá trị nằm bên trái cột đầu tiên bạn chọn trong phạm vi bảng, nhưng INDEX-MATCH có thể tra cứu theo chiều ngang nên không bị hạn chế đó.
- Giới hạn độ dài giá trị tra cứu: VLOOKUP không thể tra cứu các giá trị dài hơn 255 ký tự, trong khi INDEX-MATCH có thể.
Hơn nữa, INDEX-MATCH còn cho phép khớp dựa trên giá trị của nhiều cột và khớp phân biệt chữ hoa/thường, điều này rất hữu ích khi bạn phải xử lý một số ID có thể khác nhau dựa trên chữ hoa và chữ thường. Vì vậy, hướng dẫn này sẽ đi sâu hơn vào các tính năng nâng cao của kết hợp INDEX-MATCH, đồng thời đưa ra ví dụ về cách những tính năng này hữu ích trong thực tế.
Những điều cơ bản về INDEX-MATCH
Ở dạng đơn giản nhất, INDEX-MATCH có thể được sử dụng giống như VLOOKUP để thực hiện các tra cứu dọc đơn giản dựa trên một khóa chung. Cấu trúc cơ bản của công thức như sau:
=INDEX(column_range, MATCH(lookup_value, lookup_column_range, match_type))
- column_range: Phạm vi cột chứa giá trị bạn muốn lấy.
- lookup_value: Giá trị bạn muốn tra cứu (khóa).
- lookup_column_range: Phạm vi cột chứa các khóa bạn muốn khớp.
- match_type: Hoặc 0 hoặc 1. 0 biểu thị khớp chính xác và 1 biểu thị khớp gần đúng. Tương tự cờ FALSE/TRUE trong VLOOKUP.
Tiếp theo, hãy minh họa cách INDEX-MATCH hoạt động bằng một vài bảng ví dụ nhỏ. Bảng đầu tiên chứa một số tên cũng như một số khóa duy nhất. Bảng thứ hai chứa các khóa giống nhau (không nhất thiết theo cùng thứ tự) và một số mô tả. Nhiệm vụ là nối các mô tả trong bảng thứ hai với tên trong bảng thứ nhất bằng INDEX-MATCH. Hai bảng như bên dưới:
 |
 |
Như bạn thấy, bảng đầu tiên thiếu phần mô tả. Giờ hãy tiến hành nối tên với mô tả của nó bằng INDEX-MATCH:
Dễ phải không? Như trong video, bước đầu tiên là chọn column_range chứa dữ liệu muốn lấy trong hàm INDEX. Trong ví dụ này là cột Description (M2:M8). Bước hai là chọn lookup_value trong hàm MATCH, tức là ô B2. Cuối cùng, thêm lookup_column_range (L2:L8) vào hàm MATCH và chọn 0 cho match_type vì chúng ta cần khớp chính xác với lookup_value. Phần còn lại chỉ là nhấn ENTER và kéo công thức xuống để nối tên với mô tả.
INDEX-MATCH cũng có thể dùng để nối các bảng ở những trang tính khác nhau giống như VLOOKUP. Ví dụ minh họa dưới đây:
Lưu ý: Bạn có thể nhận thấy các phạm vi cột tôi chọn trong video thường được bao quanh bởi dấu $. Điều này nhằm cố định phạm vi để không bị thay đổi khi bạn kéo công thức theo chiều dọc hoặc ngang. Tôi sẽ tiếp tục dùng ký hiệu này trong suốt bài hướng dẫn.
Đến đây, mọi thứ hoạt động khá giống một VLOOKUP đơn giản nhưng phiền hơn đôi chút. Tuy nhiên, như đã đề cập ở phần đầu, đi theo hướng INDEX-MATCH có nhiều lợi thế. Một trong những lợi ích hữu ích nhất là tham chiếu cột động, tức cho phép bạn thêm cột vào phạm vi bảng nơi đang tra cứu mà không ảnh hưởng đến thao tác tra cứu.
Nếu thêm một cột mới trong ví dụ nhỏ và dùng VLOOKUP, chúng ta sẽ phải viết lại công thức ở bảng bên trái để tham chiếu đến mô tả, lúc này trở thành cột 3. Tuy nhiên, nếu dùng INDEX-MATCH với tham chiếu cột động, điều đó là không cần thiết. So sánh này thể hiện trong video dưới đây:
Ví dụ trên cho thấy cột dùng INDEX-MATCH đã tự điều chỉnh khi thêm cột mới chứa loại tàu vũ trụ, trong khi cột dùng VLOOKUP thì không. Ưu thế này của INDEX-MATCH so với VLOOKUP thực sự tỏa sáng trong các tác vụ tra cứu trên những bảng Excel lớn như báo cáo doanh số. Nói cách khác, hãy tưởng tượng bạn có nhiều bảng điều khiển doanh nghiệp xây dựng trong Excel và tất cả đều tham chiếu đến một bảng dữ liệu duy nhất. Nếu bạn dùng VLOOKUP và một ngày cần chèn thêm cột ở giữa để bổ sung một trường mới, bạn sẽ phải thay đổi lời gọi hàm VLOOKUP trong tất cả dashboard phụ thuộc vào bảng đó. Nhưng nếu bạn xây dựng dashboard bằng INDEX-MATCH làm phương thức tra cứu, bạn sẽ không gặp vấn đề này.
INDEX-MATCH nâng cao: Khớp phân biệt chữ hoa/thường
Tham chiếu cột động rất hữu ích, nhưng đó không phải là tất cả ưu điểm mà INDEX-MATCH mang lại. Ví dụ, có thể tăng cường cú pháp cơ bản của INDEX-MATCH để thực hiện tra cứu phân biệt chữ hoa/thường. Cụ thể, chúng ta sẽ thêm hàm EXACT vào trong hàm MATCH để đạt được điều này. Cú pháp chi tiết như sau:
{=INDEX(data_range, MATCH(TRUE,EXACT(lookup_value, lookup_column_range), match_type), desired_column_number)}
- data_range: Phạm vi bảng quan tâm, bao gồm cột tra cứu và cột mong muốn bạn muốn lấy.
- lookup_value: Giá trị bạn muốn tra cứu (khóa)
- lookup_column_range: Phạm vi cột chứa các khóa bạn muốn khớp
- match_type: Hoặc 0 hoặc 1. 0 biểu thị khớp chính xác, 1 biểu thị khớp gần đúng. Tương tự cờ FALSE/TRUE trong VLOOKUP
- desired_column_number: Tương tự số chỉ mục cột trong VLOOKUP. Đây là chỉ mục cột chứa dữ liệu bạn muốn lấy.
Khớp phân biệt chữ hoa/thường bằng kết hợp INDEX-MATCH đã chỉnh sửa này rất hữu ích khi khớp các khóa có chứa chữ cái và được phân biệt bởi chữ hoa/thường (ví dụ bạn có hai khóa khác nhau, một là "00567UUp" và cái kia là "00567UUP"). Những tình huống như vậy có thể bắt gặp, chẳng hạn, trong hệ thống CRM Salesforce khi cố gắng khớp Account ID hoặc Opportunity ID. Tuy nhiên, ở đây chúng ta sẽ không dùng bộ dữ liệu Salesforce, mà sẽ dùng phiên bản chỉnh sửa của các bảng ví dụ nhỏ trong video dưới đây để minh họa công thức hoạt động.
Lưu ý: tổ hợp hàm trên được đặt trong ngoặc nhọn. Điều này có nghĩa đây là một công thức mảng và phải nhập bằng cách nhấn Cntr+Shift+Enter.
Như bạn thấy, cú pháp công thức này khá dài khi gõ, nhưng kết quả đúng như mong đợi. Nó tra cứu giá trị giống như INDEX-MATCH thông thường, nhưng có tính đến phân biệt chữ hoa/thường. Phần “ma thuật” nằm ở hàm EXACT trả về một mảng TRUE và FALSE. Ở vị trí nào trong mảng là TRUE, có nghĩa là có một giá trị có kiểu chữ trùng khớp hoàn toàn. Sau đó, hàm MATCH sẽ xác định vị trí lần xuất hiện TRUE đầu tiên.
Xin lưu ý rằng việc nhấn Cntrl+Shift+Enter khi hoàn tất công thức là cực kỳ quan trọng, nếu không bạn sẽ gặp lỗi #N/A khó chẩn đoán.
INDEX-MATCH nâng cao: Khớp dựa trên nhiều tiêu chí
Tra cứu phân biệt chữ hoa/thường trong Excel khá thú vị, nhưng còn một tính năng hay hơn: tra cứu dựa trên nhiều tiêu chí. Xét nhiều tiêu chí là điều thường gặp khi đếm hoặc tính tổng có điều kiện như COUNTIFS hoặc SUMIFS, nhưng lại ít thấy trong các hàm tra cứu của Excel. Tuy nhiên, trong một số tình huống, đây lại là lợi thế. Chẳng hạn, nếu bạn có tệp Excel của bộ phận nhân sự và muốn tra cứu lương của một nhân viên dựa trên cả tên và họ. Nếu chỉ xét một trong hai cột, có thể bạn sẽ lấy nhầm lương của nhân viên khác do trùng tên hoặc trùng họ. Tuy nhiên, khả năng lấy đúng người sẽ cao hơn nhiều nếu bạn tra cứu dựa trên cả tên và họ.
Với điều đó, hãy xem cấu trúc của INDEX-MATCH khi xét nhiều tiêu chí:
{=INDEX(column_range, MATCH(1,(lookup_value_1 = lookup_column_range_1)*(lookup_value_2 = lookup_column_range_2)*...(lookup_value_n = lookup_column_range_n), match_type))}
Về cơ bản, công thức có các thành phần giống hệt INDEX-MATCH thông thường, nhưng giờ có thể xét $n$ giá trị tra cứu với số lượng phạm vi tra cứu tương ứng. Nếu cần nhắc lại các thành phần này là gì, bạn có thể cuộn lên phần cú pháp cơ bản của INDEX-MATCH. Lý do có số "1" trong hàm MATCH là vì công thức tạo ra các mảng gồm 1 và 0 đại diện cho các tiêu chí khớp. Sau đó dùng MATCH để lấy vị trí của số 1 đầu tiên được tìm thấy.
Giống như công thức phân biệt chữ hoa/thường, công thức này cũng được đặt trong ngoặc nhọn, nghĩa là bạn phải nhấn Cntr+Shift+Enter khi viết xong vì đây là công thức mảng.
Hãy xem ví dụ cách công thức hoạt động bằng cách chọn các tàu vũ trụ thuộc một chủng tộc nhất định và có tốc độ warp tối đa nhất định trong video sau:
Voilà! Giờ bạn đã có công cụ để khớp theo nhiều khóa. Lưu ý rằng trong ví dụ nhỏ này, tôi chỉ dùng hai tiêu chí, khóa và tốc độ warp tối đa, nhưng về lý thuyết, bạn có thể tiếp tục thêm tiêu chí nếu muốn. Quay lại ví dụ nhân sự, bạn có thể khớp dựa trên tên, họ và cả chức danh công việc để giảm thiểu khả năng lấy nhầm mức lương của nhân viên khác.
Kết luận
Chúc mừng! Bạn đã quen thuộc với INDEX-MATCH và nhiều điều có thể làm được với nó vượt ra ngoài tra cứu dọc đơn giản. Nếu một lúc nào đó bạn được giao xử lý các báo cáo Excel phức tạp, cặp hàm này rất có thể sẽ trở thành “bạn thân”. Giống như nhiều người, tôi được dạy VLOOKUP ở trường, nhưng khi bắt đầu dùng Excel nghiêm túc cho các tác vụ như tra cứu phân biệt chữ hoa/thường đối với Salesforce Account ID, tôi nhận ra INDEX-MATCH là lựa chọn tốt hơn về lâu dài, dù công thức rườm rà và dễ sai sót khi gõ. Tôi khuyến khích bạn thử dùng lần tới khi cần tra cứu dọc trong công việc Excel. Biết đâu bạn cũng sẽ thích nó như tôi.
Nếu muốn học thêm, bạn có thể xem khóa Data Analysis with Spreadsheets của DataCamp.
