Đánh giá lequocthai.com:
Hàm này thường được dùng khi làm việc với các bộ dữ liệu lớn và khi tạo các dải dữ liệu động. Nếu bạn biết vị trí cần lấy trong bảng, INDEX sẽ nhanh chóng trả về giá trị tương ứng.
Cú pháp
Hàm INDEX có hai dạng cú pháp riêng biệt; dạng thứ hai bao gồm một đối số bổ sung. Tùy theo nhu cầu, người dùng có thể chọn các đối số phù hợp.
Đối với dạng mảng, cú pháp như sau:
=INDEX(array, row_num, [column_num])Dạng tham chiếu cho phép chọn một trong nhiều dải ô để lấy dữ liệu:
=INDEX(reference, row_num, [column_num], [area_num])Đối số:
- array – Đối số bắt buộc, là dải ô hoặc mảng dùng để lấy dữ liệu. Giá trị của array cũng có thể là một hằng số mảng.
- reference – Đối số bắt buộc khi có nhiều hơn một dải ô. Nó chứa các tham chiếu tới các dải dữ liệu cần lấy. Nếu các dải ô không liền kề, các giá trị phải được đặt trong dấu ngoặc.
- Nếu array hoặc reference chỉ chứa một hàng hoặc một cột, các đối số row_num và column_num sẽ trở thành tùy chọn.
- row_num – Số hàng muốn lấy. Giá trị phải là số nguyên dương. Khi không cung cấp column_num, row_num là đối số bắt buộc.
- column_num – Số cột muốn lấy. Khi không cung cấp row_num, column_num là đối số bắt buộc.
- Khi cả row_num và column_num đều được chỉ định, hàm INDEX trả về giá trị tại giao điểm của hàng và cột đó.
- area_num – Đối số cuối cùng của dạng tham chiếu, chỉ định dải ô nào sẽ được lấy dữ liệu khi có nhiều dải ô. Mặc định là 1.
Đặc điểm quan trọng của hàm INDEX
- Khi cả row_num và column_num được đặt bằng 0, hàm INDEX sẽ trả về toàn bộ mảng hoặc toàn bộ dải ô đầu vào.
- Nếu row_num hoặc column_num là số âm, hàm INDEX sẽ sinh ra lỗi
#VALUE!. - Hàm INDEX trả về lỗi
#REF!khi row_num, column_num hoặc area_num không trỏ tới ô nào trong array hoặc reference đã cho. - Khi sử dụng dạng tham chiếu, tất cả các dải ô phải nằm trong cùng một bảng tính; nếu không, hàm INDEX sẽ sinh ra lỗi
#VALUE!.
Ví dụ về hàm INDEX
Hãy bắt đầu bằng cách nắm rõ chức năng cốt lõi của hàm INDEX. Dưới đây là một bộ dữ liệu mẫu và các ví dụ minh họa.
Ví dụ đầu tiên là trường hợp điển hình của hàm INDEX với mảng hai chiều (A1:C8) và mục tiêu lấy giá trị ở giao điểm của hàng thứ 4 và cột thứ 1. Kết quả trả về là “Tablet”.
Hai ví dụ tiếp theo minh họa mảng hoặc dải ô một chiều. Khi đó chỉ cần dùng một trong hai đối số. Trong ví dụ thứ hai, dữ liệu được lấy từ hàng thứ hai của dải A1:A8; trong ví dụ thứ ba, dữ liệu được lấy từ cột thứ hai của dải A2:C2.
Hai ví dụ cuối cùng khám phá cách hoạt động khi một trong các đối số row_num hoặc column_num được đặt bằng 0. Trong những trường hợp này, toàn bộ hàng hoặc toàn bộ cột sẽ được trả về.
Các ví dụ trên cho thấy độ linh hoạt và sức mạnh của hàm INDEX trong việc truy xuất dữ liệu.
Ví dụ 1 – Sử dụng đơn giản hàm INDEX
Giả sử bạn đang lên kế hoạch cho một chuyến đi biển và muốn chọn ngày thích hợp nhất dựa trên thời tiết. Bạn đã tải báo cáo thời tiết cho tháng tới, bắt đầu từ ngày 1. Dữ liệu chứa các chi tiết thời tiết cho mỗi ngày. Bây giờ bạn muốn lấy thông tin thời tiết cho một ngày cụ thể bằng cách nhập số ngày.
Ví dụ này minh họa một ứng dụng cơ bản của hàm INDEX, cho phép lấy một giá trị cụ thể từ mảng dựa trên vị trí.
Sử dụng ô F1 làm tham chiếu cho số ngày nhập vào, công thức sẽ như sau:
=INDEX(A1:D21,F1+1,0)Hàm sẽ trả về toàn bộ hàng F1+1 trong mảng A1:D21 (cộng thêm 1 để bù cho hàng tiêu đề). Vì column_num đặt là 0, toàn bộ hàng sẽ được trả về, cung cấp đầy đủ thông tin thời tiết cho ngày đã chọn.
Nếu chỉ muốn lấy điều kiện thời tiết (cột thứ 4), công thức thay đổi như sau:
=INDEX(A1:D21,F1+1,4)Ở đây column_num là 4 vì cột chứa điều kiện thời tiết là cột thứ 4.
Ví dụ 2 – Sử dụng dạng tham chiếu của hàm INDEX
Trong trường hợp này, chúng ta có dữ liệu bán hàng của công ty trong ba tuần liên tiếp, mỗi tuần được lưu trong một bộ dữ liệu riêng. Mục tiêu là lấy dữ liệu bán hàng cho ngày cụ thể từ một bộ dữ liệu được chọn.
Một cách là dùng hàm INDEX riêng biệt cho ba bộ dữ liệu; cách hiệu quả hơn là dùng dạng tham chiếu của hàm INDEX. Khi sử dụng nhiều dải ô, chúng ta có thể đặt chúng trong dấu ngoặc và ngăn cách bằng dấu phẩy, ví dụ (A3:C6,A9:C12,A15:C18) cho ba tuần.
Giả sử muốn lấy dữ liệu của ngày thứ 2 trong tuần 2, công thức sẽ như sau:
=INDEX((A3:C6,A9:C12,A15:C18),2,,2)Để trích xuất toàn bộ hàng, để trống đối số column_num. Đặt area_num bằng 2 để chỉ định lấy dữ liệu từ bộ dữ liệu thứ hai.
Không cần phải ghép các bộ dữ liệu lại trước khi trích xuất.
Ví dụ 3 – Tạo dải dữ liệu động với hàm INDEX
Vì giá điện đã tăng, chúng ta muốn phân tích mức tiêu thụ trung bình cùng các yếu tố khác để tìm cách tối ưu tiêu thụ và chi phí một cách bền vững. Dữ liệu tiêu thụ điện hàng tháng được sắp xếp theo các cột: tiêu thụ điện, chi phí mỗi kWh và tổng chi phí.
Một cách để tính trung bình là dùng hàm AVERAGE và nhập dải ô thủ công; cách hiệu quả hơn là dùng hàm INDEX để tạo dải dữ liệu động dựa trên đầu vào của người dùng.
Ý tưởng: tính trung bình một dải giá trị ở cột B, bắt đầu từ ô B3, và điểm kết thúc được xác định động bằng hàm INDEX.
Lấy ô G1 để nhập số tháng cần tính trung bình. Giá trị này sẽ làm row_num cho hàm INDEX, cho phép lựa chọn dải dữ liệu một cách động. Công thức:
=INDEX(B:B,G1+2)Ta cộng thêm 2 để bao gồm hàng tiêu đề. Khi kết hợp với AVERAGE, công thức sẽ là:
=AVERAGE(B3:INDEX(B:B,G1+2))
Khi làm việc với dữ liệu lớn, dải dữ liệu động giúp truy xuất nhanh hơn.
Hàm INDEX và MATCH
Khi tìm kiếm dữ liệu, hàm thường được nhắc đến nhất là VLOOKUP. Tuy nhiên, VLOOKUP có hạn chế là cột tra cứu phải nằm bên trái của cột trả về. Để khắc phục, bạn có thể dùng XLOOKUP (dành cho MS 365 và Excel 2021). Nếu không, sự kết hợp giữa hàm INDEX và MATCH là lựa chọn được khuyến nghị.
Hàm INDEX cho phép lấy dữ liệu khi biết vị trí chính xác. Hàm MATCH giúp tìm vị trí của dữ liệu trong bảng. Khi kết hợp, chúng ta có thể thực hiện tra cứu động.
Ví dụ: bảng chứa mã nhân viên, mức lương và các thông tin phụ. Người dùng muốn lấy toàn bộ thông tin dựa trên mã nhân viên.
Hàm MATCH sẽ tìm vị trí của mã nhân viên trong cột B:
=MATCH(G1,B:B,0)
Sau khi có vị trí hàng, hàm INDEX có thể lấy tên, phòng ban và vị trí công việc từ các cột A, C và D tương ứng. Công thức:
=INDEX(A:D,MATCH(G1,B:B,0),)
Đó là những ưu điểm nổi bật của hàm INDEX và khả năng trích xuất thông tin mục tiêu từ khối lượng dữ liệu khổng lồ. Khi đối mặt với các nhiệm vụ tra cứu dữ liệu, hãy nhớ đến hàm INDEX. Trong thời gian tới, chúng tôi sẽ chuẩn bị một hàm mới để mang đến cho bạn những tính năng tinh tế hơn. Chúc các bạn thành công!
