Hàm INDEX trong Excel có chức năng trả về một giá trị hoặc tham chiếu đến một giá trị trong bảng hoặc phạm vi. Chúng ta hãy cùng khám phá công thức và cách sử dụng của hàm INDEX trong bài viết dưới đây từ Tin học văn phòng PRO.
- Hướng dẫn dưới đây được thực hiện trên laptop hệ điều hành Windows, phiên bản Excel 2016. Bạn có thể thực hiện tương tự trên các phiên bản Excel 2007, 2010, 2013 và 2019.
- Bạn có thể tải một số ví dụ về hàm INDEX trong Excel để làm quen nhé!
Công thức hàm INDEX trong Excel
Hàm INDEX là một hàm hữu ích trong việc tìm kiếm và truy xuất dữ liệu trong bảng tính. Nó cho phép chúng ta xác định vị trí của một giá trị cụ thể trong một phạm vi dữ liệu đã cho. Điều này rất hữu ích khi chúng ta cần lấy dữ liệu từ một bảng tính lớn hoặc khi cần thay đổi vị trí của các ô trong bảng tính. Hàm INDEX có thể được sử dụng đơn giản hoặc kết hợp với các hàm khác như MATCH để tìm kiếm và truy xuất dữ liệu một cách nhanh chóng và chính xác.
Cú pháp của hàm INDEX:
=INDEX(array, row_num, column_num)
Trong đó:
- array: Phạm vi ô hoặc một hằng số mảng. Nếu mảng chỉ chứa một hàng hoặc cột thì row_num hoặc column_num tương ứng là tùy chọn. Nếu mảng có nhiều hàng và nhiều hơn một cột mà bạn chỉ khai báo 1 trong 2 đối số row_num hoặc column_num, hàm sẽ trả về một mảng của toàn bộ hàng hoặc cột trong mảng.
- row_num: Thứ tự của hàng trong mảng chứa giá trị trả về. Đây là đối số bắt buộc phải có, trừ khi bạn khai báo column_num. Nếu bỏ qua row_num, bạn cần phải khai báo column_num.
- column_num: Thứ tự của cột trong mảng chứa giá trị trả về. Nếu bỏ qua column_num, bạn cần khai báo row_num.
Lưu ý:
- Nếu sử dụng cả hai đối số row_num và column_num, hàm sẽ trả về giá trị của ô tại giao điểm của row_num và column_num.
- Hàm sẽ trả về lỗi #REF! nếu row_num và column_num không trỏ tới bất kỳ ô nào trong mảng đã khai báo.
- Nếu row_num hoặc column_num được khai báo là 0 (không), hàm sẽ trả về một mảng giá trị cho toàn bộ cột hoặc hàng tương ứng với điều kiện bạn nhập vào công thức INDEX như một công thức mảng.
Ngoài ra, hàm INDEX còn cho phép mở rộng phạm vi tham chiếu đến nhiều mảng. Chúng ta có thể tìm giá trị của một ô trong vùng tham chiếu thuộc nhiều mảng khác nhau thông qua công thức sau:
=INDEX(reference;row_num;column_num,area_num)
- reference: Vùng tham chiếu (có thể kết hợp nhiều mảng với nhau, các mảng sẽ phân cách bằng dấu phẩy).
- row_num: Thứ tự của hàng trong mảng chứa giá trị trả về.
- column_num: Thứ tự của cột trong mảng chứa giá trị trả về.
- area_num: Số thứ tự của mảng trong vùng tham chiếu. Nếu bỏ qua area_num, hàm INDEX mặc định sử dụng dữ liệu ở mảng 1.
Chú ý:
- Nếu khai báo row_num hoặc column_num là 0 (không), hàm sẽ trả về mảng giá trị cho toàn bộ cột hoặc hàng tương ứng với điều kiện bạn nhập vào công thức INDEX.
- Hàm sẽ trả về lỗi #REF! nếu row_num và column_num không trỏ tới bất kỳ ô nào trong mảng đã khai báo. Nếu không khai báo row_num và column_num, hàm sẽ trả về vùng trong tham chiếu được xác định bởi area_num.
- Kết quả của hàm INDEX là một tham chiếu. Giá trị trả về có thể được sử dụng như một tham chiếu hoặc một giá trị tùy thuộc vào công thức áp dụng cho kết quả đó.
Các bài tập về hàm INDEX trong Excel
Dưới đây là một vài ví dụ về việc sử dụng hàm INDEX để xử lý dữ liệu trên một hoặc nhiều mảng tham chiếu:
Ví dụ 1: Lấy giá trị từ mảng dữ liệu được khai báo
Giả sử bạn có bảng dữ liệu sau:
Giả sử bạn cần tìm giá trị của phần tử ở dòng thứ 4, cột thứ 1 trong mảng. Mảng này có kích thước là 10 dòng và 4 cột (có địa chỉ là B4:E13).
Bạn nhập công thức: =INDEX(B4:E13,4,1)
Giải thích công thức (theo thứ tự các đối số trong công thức từ trái sang):
- B4:E13 là mảng chứa giá trị cần trả về.
- 4 là số thứ tự của hàng trong mảng chứa giá trị cần trả về.
- 1 là số thứ tự của cột trong mảng chứa giá trị cần trả về.
Sau khi nhập xong, bạn nhấn Enter. Kết quả trả về sẽ hiển thị như hình dưới đây.
Ví dụ 2: Trích xuất thông tin từ nhiều nguồn dữ liệu khác nhau.
Giả sử bạn có bảng dữ liệu sau:
Bây giờ, bạn muốn tìm phần tử hàng thứ 2, cột thứ 1 của mảng thứ 2. Để làm điều này, bạn sử dụng công thức sau:
=INDEX((B4:E8,B9:E13),2,1,2)
Giải thích công thức (theo thứ tự các đối số trong công thức từ trái sang):
- (B4:E8,B9:E13) là hai mảng chứa giá trị cần trả về. Hai mảng này được phân cách bởi dấu phẩy (,)
- 2 là số thứ tự của hàng trong mảng chứa giá trị cần trả về.
- 1 là số thứ tự của cột trong mảng chứa giá trị cần trả về.
- 2 là thứ tự của mảng mà bạn muốn lấy dữ liệu trong vùng tham chiếu.
Sau khi nhập xong, bạn nhấn Enter để hoàn tất. Kết quả trả về sẽ như hình dưới đây.
Các vấn đề thường gặp khi sử dụng hàm INDEX
Dưới đây là một số vấn đề có thể xảy ra khi sử dụng hàm INDEX:
Lỗi #N/A hoặc #VALUE!
– Nguyên nhân: Không sử dụng đúng cách công thức mảng bằng việc không nhấn tổ hợp phím Ctrl + Shift + Enter.
Như đã đề cập trước đó, bạn có thể chỉ định row_num hoặc column_num là 0 (không) để hàm INDEX trả về mảng giá trị cho toàn bộ cột hoặc hàng tương ứng với điều kiện được nhập vào trong công thức mảng. Tuy nhiên, trong trường hợp này, trừ khi sử dụng phiên bản mới nhất của Microsoft 365, các phiên bản khác của Excel sẽ thông báo lỗi nếu không tuân theo quy trình sử dụng công thức mảng.
– Cách khắc phục:
Đối với các phiên bản Excel không phải là phiên bản mới nhất của Microsoft 365, bạn cần nhập công thức mảng dưới dạng công thức kế thừa bằng cách chọn vùng dữ liệu đầu ra, nhập công thức vào ô trên cùng, bên trái của phạm vi đầu ra và sau đó nhấn tổ hợp phím Ctrl + Shift + Enter.
Ví dụ: Nếu bạn muốn hàm INDEX trả về mảng giá trị cho toàn bộ cột 1 trong mảng B4:E13, bạn có thể làm theo các bước sau:
Bước 1: Chọn phạm vi đầu ra, ví dụ như chọn 10 hàng trong cột G với địa chỉ là G4:G13.
Bước 2: Nhập công thức vào ô trên cùng bên trái của phạm vi đầu ra (Ở đây là ô G4).
Tại ô G4, bạn nhập: =INDEX(B4:E13,0,1)
Bước 3: Nhấn tổ hợp phím Ctrl + Shift + Enter để xác nhận. Excel sẽ tự động thêm dấu ngoặc nhọn ở đầu và cuối công thức cho bạn. Cuối cùng, bạn có thể xem kết quả như sau:
Lỗi #REF!
– Nguyên nhân: Nguyên nhân gây ra lỗi là do các tham số row_num và column_num không trỏ đến bất kỳ ô nào trong mảng đã được khai báo.
Ví dụ, nếu mảng chỉ có 4 cột nhưng bạn nhập column_num là 6.
– Cách khắc phục: Để khắc phục lỗi này, bạn cần kiểm tra lại công thức và sửa đổi các tham số column_num và row_num sao cho phù hợp.
Trên đây là hướng dẫn cách sử dụng hàm INDEX trong Excel cùng với ví dụ minh họa. Hy vọng bài viết sẽ giúp bạn hiểu rõ hơn về cách làm việc với dữ liệu trong Excel và tối ưu hóa hiệu quả công việc!
Comments 1