Một số hàm trong excel có khả năng kết hợp với nhiều hàm để giải quyết những vấn đề khó nhưng gây không ít khó khăn cho người dùng là hàm index. Vậy cách dùng hàm index như thế nào để phát huy được hiệu quả, tính năng của nó. Bài viết dưới đây sẽ giúp bạn hiểu về nguyên lý, ứng dụng, thao tác cài đặt hàm index.
Giới thiệu về hàm index trong excel
Hàm index thường được sử dụng trong excel với mục đích tìm giá trị thực của một phần tử trong dải khi bạn biết vị trí của nó trong dải ô excel. Hàm index có thể tạo nên những thay đổi quan trọng quyết định cách thức bạn tính toán, trình bày, phân tích dữ liệu trong bảng tính.
Hàm index có thể định dạng dải và định dạng tham chiếu. Cách dùng hàm index dưới dạng mảng và tham chiếu khác nhau ở bất kỳ phiên bản nào của excel.
Sử dụng index làm việc với mảng
Dưới định dạng mảng, hàm index sẽ dựa trên số cột hoặc hàng bạn quy định, cài đặt trong công thức để trả về giá trị của một phần tử trong bảng số liệu hoặc mảng số liệu.
Hàm index làm việc với mảng sẽ có cấu trúc cú pháp như sau:
INDEX (array, row_num, [column_num])
Muốn biết cách dùng hàm index, bạn phải hiểu về từng thành phần trong công thức. Ở đây, các thành phần công thức sẽ có ý nghĩa cụ thể:
array: là một dãy các ô theo tên dải hoặc bảng.
row_num – quy định thứ tự hàng thứ mấy trong mảng mà bạn cần hàm index trả về một giá trị. Nếu row_num có thể được bỏ qua
column_num – thành phần bắt buộc phải nhập nếu bỏ qua row_num.
Column_num là thứ tự cột để trả về một giá trị.
Ví dụ cách sử dụng hàm index qua công thức sau: = INDEX (A1: D6, 4, 3)
Khi vận dụng hàm index, công thức cài đặt sẽ trả về giá trị trong C4 là giao điểm của hàng tứ 4 và cột thứ 3 trong mảng dữ liệu từ A1 đến D6.
Để bạn hình dung rõ hơn về thao tác cài đặt công thức và cách dùng hàm index trên bảng số liệu thực tế, chúng tôi sẽ đưa ra ví dụ cụ thể:
Bạn có thể nhập công thức tham chiếu ô thay vì số hàng và số cột khi cài đặt index dưới đây:
“= INDEX ($B$2:$D$6,G2,G1)”
Trong công thức index này, tham chiếu tương đối (B2:D6) được thay thế bởi tài liệu tham khảo tuyệt đối ($B$2:$D$6) trong đối số của mảng sẽ giúp bạn thuận lợi sao chép công thức từ ô excel này sang ô excel khác. Bên cạnh đó, bạn có thể chuyển đổi một dải thành một bảng với phím tắt Ctrl T.
Lưu ý khi sử dụng dạng mảng và index
Cách dùng hàm index trả về giá trị chuẩn xác đó là phải chú ý các thành phần cơ bản trong công thức và hiểu được giá trị index trả về khi một trong các thành phần trong cú pháp bị khuyết thiếu. Cụ thể:
Hàm index sẽ trả về vùng được chỉ định đối số area_num khi row_num và column_num đồng thời được bỏ qua, không được nhập trên công thức
Index trên excel sẽ trả về tham chiếu cho toàn bộ cột và hàng tương ứng nếu column_num hoặc row_num được đặt giá trị là 0.
Công thức index sẽ trả về lỗi #REP! nếu _num arguments (row_num, column_num và area_num) không thuộc tham khảo một ô trong tham chiếu.
Sử dụng định dạng tham chiếu index
Khi sử định dạng tham chiếu index trên excel sẽ trả về ô tham chiếu tại hàng và cột tương ứng.
Cách dùng hàm index với tính năng này được quy ước bằng công thức sau:
INDEX (reference, row_num, [column_num], [area_num])
reference có thể được quy định bằng một dải hoặc nhiều dải. Nếu bạn nhập trên một dải dữ liệu, nhiều hơn một phạm vi dữ liệu hãy đặt các dải trong dấu ngoặc kép và phân tách các dải bằng dấu chấm phẩy .
Ví dụ như (A1:B5,D1:F5).
row_num là thứ tự của hàng nằm trong khoảng trả về một ô tham chiếu, nó tương tự như dạng mảng.
area_num – một tham số tùy chọn chỉ ra mảng từ đối số tham chiếu để sử dụng. công thức INDEX sẽ trả lại kết quả cho mảng đầu tiên được liệt kê trong danh sách tham khảo nếu bạn bỏ qua.
column_num – số thứ tự cột trả về một ô tham chiếu
Để hình dung cách dùng hàm index, bạn có thể tham khảo ví dụ dưới đây:
= INDEX ((A2: D3, A5: D7), 3, 4, 2)
Ở công thức này, sử dụng hàm index sẽ trả về giá trị tại giao điểm của hàng thứ 3 và cột thứ 4 trong khu vực thứ hai (A5: D7) là giá trị của ô D7
Lưu ý sử dụng định dạng tham chiếu index
Công thức index tham chiếu trên excel sẽ trả về tham chiếu cho các ô và cột tương ứng nếu đối số row_num hoặc column_num được đặt thành 0
Công thức index sẽ trả về lỗi #REF! nếu _num arguments (row_num, column_num và area_num) không được tham khảo một ô trong tham chiếu
hàm INDEX trả về vùng được chỉ định trong đối số area_num nếu row_num và column_num bị bỏ qua
Bài viết sẽ đưa ra ví dụ cụ thể về cách dùng hàm index để bạn hiểu cần thao tác sử dụng hàm này như thế nào. Cho bảng số liệu về các hành tinh trong hệ mặt trời, bạn cần tìm ra hành tinh có đường kính lớn thứ 2. Bạn sẽ nhập công thức hàm index như sau:
= INDEX (SourceData, 2, 3)
Array: tương ứng với SourceData
Row_num là 2 vì bạn đang tìm kiếm mục thứ hai có tên Đường kính (Diameter) trong danh sách
Column_num là 3 vì Diameter đang ở vị trí cột thứ 3 trong bảng dữ liệu.
Kết quả sẽ trả về “Uranus” – hành tinh có đường kính lớn thứ 2 trong hệ mặt trời
Bài viết trên đây đã chỉ cho bạn cách dùng hàm index với dưới định dạng mảng và tham chiếu để tìm dữ liệu. Hy vọng bạn sẽ vận dụng linh hoạt để giúp công việc phân tích, tính toán, tìm kiếm dữ liệu trên excel thuận lợi.
