Hàm INDEX trong Excel: Hướng Dẫn Sử Dụng Chi Tiết và Hiệu Quả

Excel cung cấp nhiều hàm mạnh mẽ, nhưng đôi khi chúng bị bỏ quên hoặc sử dụng chưa đúng cách. Hàm INDEX chắc chắn nằm trong top những hàm như vậy. Với tính linh hoạt cao và khả năng kết hợp với các hàm khác, INDEX giúp giải quyết nhiều bài toán phức tạp trong Excel.

Vậy hàm INDEX trong Excel là gì? Nói đơn giản, INDEX trả về giá trị của một ô trong một vùng dữ liệu xác định. Bạn sử dụng INDEX khi biết vị trí (hoặc có thể tính toán) của một phần tử trong vùng dữ liệu và muốn lấy giá trị của nó.

Thoạt nghe có vẻ đơn giản, nhưng khi nắm được sức mạnh thực sự của INDEX, bạn sẽ thay đổi cách tính toán, phân tích và trình bày dữ liệu trong bảng tính của mình.

Cú Pháp và Cách Sử Dụng Cơ Bản Hàm INDEX

Hàm INDEX trong Excel có hai dạng: dạng mảng và dạng tham chiếu. Cả hai dạng đều tương thích với các phiên bản Excel 2013, 2010, 2007 và 2003.

INDEX với Mảng

Dạng mảng của INDEX trả về giá trị của một phần tử trong bảng hoặc mảng dựa trên số hàng và số cột bạn chỉ định.

Cấu Trúc Hàm

INDEX(mảng, số_hàng, [số_cột])

• mảng: Vùng dữ liệu, tên vùng dữ liệu hoặc bảng.
• số_hàng: Số thứ tự hàng trong mảng mà bạn muốn lấy giá trị. Nếu bỏ qua số_hàng, thì số_cột là bắt buộc.
• số_cột: Số thứ tự cột để trả về giá trị. Nếu bỏ qua số_cột, thì số_hàng là bắt buộc.

Ví dụ, công thức =INDEX(A1:D6, 4, 3) trả về giá trị tại giao điểm của hàng 4 và cột 3 trong mảng A1:D6, tức là giá trị trong ô C4.

Ví dụ thực tế:

Ví dụ về dạng mảng của INDEX

Thay vì nhập số hàng và cột trực tiếp, bạn có thể sử dụng tham chiếu ô: =INDEX($B$2:$D$6,G2,G1).

Lưu ý: Sử dụng tham chiếu tuyệt đối ($B$2:$D$6) thay vì tham chiếu tương đối (B2:D6) trong đối số mảng giúp dễ dàng sao chép công thức. Bạn cũng có thể chuyển đổi vùng dữ liệu thành bảng (Ctrl + T) và tham chiếu bằng tên bảng.

Những Điều Cần Nhớ Khi Sử Dụng Dạng Mảng của INDEX

1. Nếu mảng chỉ có một hàng hoặc một cột, bạn có thể bỏ qua số_hàng hoặc số_cột tương ứng.
2. Nếu mảng có nhiều hàng và số_hàng bị bỏ qua hoặc bằng 0, INDEX trả về mảng của toàn bộ cột. Tương tự, nếu mảng có nhiều cột và số_cột bị bỏ qua hoặc bằng 0, INDEX trả về toàn bộ hàng.
3. số_hàng và số_cột phải nằm trong mảng; nếu không, INDEX trả về lỗi #REF!.

Dạng Tham Chiếu của INDEX

Dạng tham chiếu của INDEX trả về ô tại giao điểm của hàng và cột đã chỉ định.

Cấu Trúc Hàm

INDEX(tham_chiếu, số_hàng, [số_cột], [số_vùng])

• tham_chiếu: Một hoặc nhiều vùng dữ liệu. Nếu nhập nhiều vùng, phân tách bằng dấu phẩy và đặt trong ngoặc đơn, ví dụ: (A1:B5,D1:F5).
• số_hàng: Số thứ tự hàng trong vùng dữ liệu.
• số_cột: Số thứ tự cột trong vùng dữ liệu.
• số_vùng: Số thứ tự vùng dữ liệu được sử dụng. Nếu bỏ qua, INDEX sử dụng vùng đầu tiên trong tham_chiếu.

Ví dụ, công thức =INDEX((A2:D3,A5:D7),3,4,2) trả về giá trị của ô D7, tại giao điểm của hàng 3 và cột 4 trong vùng thứ hai (A5:D7).

Ví dụ về dạng tham chiếu của INDEX

Những Điều Cần Nhớ Khi Sử Dụng Dạng Tham Chiếu của INDEX

1. Nếu số_hàng hoặc số_cột bằng 0, INDEX trả về tham chiếu đến toàn bộ cột hoặc hàng tương ứng.
2. Nếu cả số_hàng và số_cột bị bỏ qua, INDEX trả về vùng được chỉ định bởi số_vùng.
3. Tất cả các đối số _số (số_hàng, số_cột và số_vùng) phải nằm trong tham_chiếu; nếu không, INDEX trả về lỗi #REF!.

Cách Dùng Hàm INDEX trong Excel: Ví Dụ và Công Thức

Kết hợp INDEX với các hàm khác như MATCH hoặc COUNTA tạo ra những công thức mạnh mẽ.

Nguồn Dữ Liệu (SourceData)

Các ví dụ dưới đây sử dụng bảng dữ liệu sau, được đặt tên là SourceData.

Bảng dữ liệu ví dụ

Sử dụng bảng hoặc vùng dữ liệu đặt tên giúp công thức dễ đọc và linh hoạt hơn.

1. Lấy Dữ Liệu ở Vị Trí Thứ N từ Danh Sách

Để lấy dữ liệu thứ n trong danh sách, sử dụng công thức =INDEX(vùng_dữ_liệu, n), trong đó vùng_dữ_liệu là vùng dữ liệu hoặc tên vùng dữ liệu, và n là vị trí của dữ liệu cần lấy.

Công thức INDEX để lấy phần tử thứ N

Để lấy giá trị tại giao điểm của hàng và cột, sử dụng cả số_hàng và số_cột.

Công thức INDEX để lấy giá trị tại giao điểm hàng và cột

2. Lấy Tất Cả Giá Trị trong Hàng hoặc Cột

INDEX có thể trả về toàn bộ hàng hoặc cột. Để lấy toàn bộ cột, bỏ qua hoặc đặt số_hàng là 0. Tương tự, để lấy toàn bộ hàng, bỏ qua hoặc đặt số_cột là 0.

Kết hợp INDEX với các hàm khác như SUM hoặc AVERAGE cho kết quả hữu ích.

Ví dụ, tính nhiệt độ trung bình:

=AVERAGE(INDEX(SourceData,,4))

Công thức tính trung bình

Tương tự, tìm nhiệt độ tối thiểu và tối đa:

=MAX(INDEX(SourceData,,4))

=MIN(INDEX(SourceData,,4))

Và tính tổng khối lượng:

=SUM(INDEX(SourceData,,2))

3. Sử Dụng INDEX với Các Hàm Khác (SUM, AVERAGE, MAX, MIN)

INDEX trả về tham chiếu đến ô chứa giá trị. Điều này cho phép sử dụng INDEX trong các hàm khác để tạo vùng dữ liệu động.

Ví dụ: =AVERAGE(A1:INDEX(A1:A20,10))

Công thức này tương đương với =AVERAGE(A1:A10), nhưng vùng dữ liệu trong công thức AVERAGE/INDEX là động.

Ví dụ 1. Tính trung bình n mục đầu tiên:

=AVERAGE(C5:INDEX(SourceData[Diameter],B1))

Tính trung bình n mục đầu

Ví dụ 2. Tính tổng giá trị giữa hai mục:

=SUM(INDEX(SourceData[Diameter],B1):INDEX(SourceData[Diameter],B2))

Tính tổng giá trị giữa hai mục

4. Sử Dụng INDEX để Tạo Vùng Dữ Liệu Động

INDEX kết hợp với COUNTA giúp tạo vùng dữ liệu động tự điều chỉnh khi thêm hoặc xóa mục.

=Sheet_Name!$A$1:INDEX(Sheet_Name!$A:$A,COUNTA(Sheet_Name!$A:$A))

Tạo vùng dữ liệu động

Mẹo: Tạo bảng Excel là cách đơn giản nhất để tạo danh sách tự động cập nhật.

5. Kết Hợp INDEX/MATCH Thay Thế VLOOKUP

INDEX/MATCH vượt trội hơn VLOOKUP ở nhiều điểm:

• Tra cứu bên trái.
• Không giới hạn độ dài giá trị tra cứu.
• Không yêu cầu sắp xếp.
• Linh hoạt khi thêm/xóa cột.
• Hiệu suất tốt hơn.

Cú pháp:

=INDEX(cột_trả_về, MATCH(giá_trị_tra_cứu, cột_tra_cứu, 0))

INDEX/MATCH thay thế VLOOKUP

6. Sử Dụng INDEX để Lấy Vùng Dữ Liệu từ Danh Sách Vùng Dữ Liệu

INDEX có thể lấy vùng dữ liệu từ danh sách các vùng dữ liệu.

Vùng dữ liệu đặt tên

Ví dụ:

=AVERAGE(INDEX((PlanetsD,MoonsD),,,B1))

Lấy vùng dữ liệu từ danh sách

Kết hợp với IF:

=AVERAGE(INDEX((PlanetsD,MoonsD),,,IF(B1="planets",1,IF(B1="moon",2))))

INDEX/IF

Và với IFERROR:

=IFERROR(AVERAGE(INDEX((PlanetsD,MoonsD),,,IF(B1="planet",1,IF(B1="moon",2)))),"Vui lòng chọn danh sách!")

Trên đây là hướng dẫn sử dụng hàm INDEX trong Excel. Hy vọng những ví dụ này giúp bạn khai thác sức mạnh của INDEX trong bảng tính. Để ứng dụng Excel hiệu quả, bạn cần nắm vững các hàm và công cụ khác như SUMIFS, COUNTIFS, SUMPRODUCT, Data Validation, Conditional Formatting, Pivot Table.