1Công thức hàm INDEX
Hàm INDEX trong Excel là hàm cho kết quả trả về là một giá trị hoặc tham chiếu tới một giá trị trong bảng hoặc phạm vi nhất định.
Cú pháp hàm INDEX:
- 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 dùng đến cả 2 đối số row_num và column_num, hàm sẽ trả về giá trị trong ô tại giao điểm row_num và column_num.
- Hàm trả về lỗi #REF! nếu row_num và column_num không trỏ tới bất kỳ một ô trong mảng đã khai báo.
- Nếu khai báo row_num hoặc column_num là 0 (không), hàm 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 hàm INDEX làm công thức mảng.
Bên cạnh đó, hàm INDEX cũng cho phép mở rộng phạm vi tham chiếu đến nhiều mảng. Chúng ta có thể tìm phần tử của một 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.
Lưu ý:
- Nếu khai báo row_num hoặc column_num là 0 (không), hàm 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 hàm INDEX làm công thức mảng.
- Hàm trả về lỗi #REF! nếu row_num và column_num không trỏ tới bất kỳ một ô nào trong mảng đã khai báo. Nếu row_num và column_num được bỏ qua, 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ị mà hàm INDEX trả về có thể được dùng như một tham chiếu hoặc một giá trị tùy theo công thức bạn áp dụng cho kết quả đó.
2Một số bài tập hàm INDEX
Dưới đây là một số ví dụ sử dụng hàm INDEX để xử lý dữ liệu trên 1 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:
Bây giờ, 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 ở đây gồm 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ẽ như hình dưới.
Ví dụ 2: Lấy giá trị từ nhiều mảng 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. Bạn nhập công thức:
=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à 2 mảng chứa giá trị cần trả về. 2 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. Kết quả trả về sẽ như hình dưới.
3Một số lỗi khi sử dụng hàm INDEX
Dưới đây là một số lỗi bạn có thể gặp khi thao tác với hàm INDEX:
Lỗi #N/A hoặc #VALUE!
– Nguyên nhân: Sử dụng công thức mảng mà không nhấn Ctrl + Shift + Enter.
Như đã đề cập ở trên, bạn có thể khai báo row_num hoặc column_num là 0 (không) để hàm 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 hàm INDEX làm công thức mảng. Tuy nhiên, trong trường hợp này, trừ phiên bản mới nhất của Microsoft 365, đối với các phiên bản khác, Excel sẽ báo lỗi nếu không làm đúng quy trình dùng công thức mảng.
– Cách khắc phục:
Với các phiên bản Excel không phải phiên bản mới của Microsoft 365, bạn sẽ phải nhập công thức mảng dưới dạng công thức mảng 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 sau đó nhấn Ctrl + Shift + Enter.
Ví dụ: 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 làm theo các bước sau:
Bước 1: Chọn phạm vi đầu ra, ở đây chọn 10 hàng trong cột G, có địa chỉ là G4:G13.
Bước 2: Nhập công thức ở ô 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 Ctrl + Shift + Enter để xác nhận. Excel sẽ chèn dấu ngoặc nhọn ở đầu và cuối công thức cho bạn. Cuối cùng, bạn xem kết quả hoàn thành như sau:
Lỗi #REF!
– Nguyên nhân: Các đối số row_num và column_num không trỏ tới bất kỳ một ô trong mảng đã khai báo, ví dụ mảng khai báo chỉ có 4 cột nhưng bạn vô tình nhập column_num là 6.
– Cách khắc phục: Kiểm tra lại công thức xem đã đúng chưa và chỉnh lại cho phù hợp, nhất là các đối số column_num, row_num.
Trên đây là hướng dẫn cách dùng hàm INDEX trong Excel kèm bài tập minh họa. Hy vọng bài viết sẽ giúp ích cho bạn trong việc thao tác với dữ liệu Excel và tối ưu hiệu quả làm việc nhé!
