Trong bài viết này, Học Excel Online sẽ lấy ví dụ về hàm INDEX trong Excel, qua đó mô tả cho bạn những cách sử dụng hiệu quả công cụ này.

Trong tất cả các chức năng của Excel mà sức mạnh của chúng thường bị đánh giá thấp và không được sử dụng đúng cách, thì INDEX chắc chắn sẽ xếp hạng ở đâu đó trong top 10. Bởi hàm này rất nhạy và có thể kết hợp với các hàm khác nữa để giải quyết được nhiều vấn đề đặt ra.

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

Điều này nghe có vẻ tầm thường, nhưng khi bạn nhận ra tiềm năng thực sự của hàm INDEX, thì nó có thể tạo nên những thay đổi quan trọng đối với cách mà bạn tính toán, phân tích và trình bày dữ liệu trong các bảng tính của bạn.

Hàm INDEX – cú pháp và sử dụng cơ bản

Có hai phiên bản của hàm INDEX trong Excel – định dạng dải và định dạng tham chiếu. Và cả hai mẫu đều có thể được sử dụng trong tất cả các phiên bản của Microsoft Excel 2013, Excel 2010, Excel 2007 và 2003.

Xem ngay: Tài liệu hướng dẫn sử dụng excel 2003

INDEX  làm việc với mảng

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

Cấu trúc hàm

INDEX (array, row_num, [column_num])

• array – là một dãi các ô, có tên dải hoặc bảng.
• row_num – là hàng thứ mấy trong mảng mà bạn muốn được trả về một giá trị. Nếu row_num bị bỏ qua, column_num là bắt buộc.
• column_num – là cột thứ mấy để trả về một giá trị. Nếu column_num bị bỏ qua, row_num là bắt buộc.

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

Để có ý tưởng về cách hàm INDEX hoạt động trên dữ liệu thực, hãy xem ví dụ sau:

Thay vì nhập số hàng và cột trong công thức, bạn có thể cung cấp tham chiếu ô để có được công thức: “= INDEX ($B$2:$D$6,G2,G1)”

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

Dạng mảng và INDEX – điều cần nhớ

1. Nếu đối số mảng chỉ bao gồm một hàng hoặc cột, thì bạn có thể xác định hoặc không xác định đối số row_num hoặc column_num tương ứng.
2. Nếu đối số mảng bao gồm nhiều hơn một dòng và row_num bị bỏ qua hoặc bằng 0, thì hàm INDEX trả về một mảng của toàn bộ cột. Tương tự, nếu mảng bao gồm nhiều cột và đối số column_num bị bỏ qua hoặc bằng 0, công thức INDEX sẽ trả về toàn bộ hàng. Dưới đây là một ví dụ về công thức minh họa.
3. Các đối số row_num và column_num phải tham chiếu đến một ô trong mảng; Nếu không, công thức INDEX sẽ trả về #REF! lỗi.

Định dạng tham chiếu INDEX

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

Cấu trúc hàm

INDEX (reference, row_num, [column_num], [area_num])

• reference – là một hoặc nhiều dải.

Nếu bạn nhập nhiều hơn một phạm vi, hãy tách các khoảng bằng dấu phẩy và kèm theo đối số tham chiếu trong ngoặc đơn, ví dụ (A1:B5,D1:F5).

Nếu mỗi dãy trong tài liệu tham khảo chỉ chứa một hàng hoặc cột, đối số row_num hoặc column_num tương ứng là tùy chọn.

• row_num – số thứ tự của hàng nằm trong khoảng mà từ đó trả về một ô tham chiếu, nó tương tự như dạng mảng.
• column_num – số thứ tự cột để trả về một ô tham chiếu, cũng hoạt động 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. Nếu bỏ qua, 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.

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 thứ 3 và cột thứ 4 trong khu vực thứ hai (A5: D7).

Định dạng tham chiếu INDEX – những điều cần nhớ

1. Nếu đối số row_num hoặc column_num được đặt thành 0, thì một công thức Excel INDEX trả về tham chiếu cho toàn bộ cột hoặc hàng tương ứng.
2. Nếu cả hai row_num và column_num bị bỏ qua, hàm INDEX trả về vùng được chỉ định trong đối số area_num.
3. Tất cả các _num arguments (row_num, column_num và area_num) phải tham khảo một ô trong tham chiếu; Nếu không, công thức INDEX sẽ trả về #REF! lỗi.

Cả hai công thức INDEX chúng ta đã thảo luận cho đến nay đều rất đơn giản và chỉ minh hoạ cho khái niệm. Công thức thực sự của bạn có thể phức tạp hơn nhiều so với đó, vì vậy, hãy cùng khám phá một vài sử dụng hiệu quả nhất của INDEX trong Excel.

Các hàm thường dùng trong excel

Cách dùng hàm INDEX trong Excel- ví dụ, công thức

Có thể nó không có nhiều ứng dụng thực tế, nhưng nếu kết hợp với các hàm khác như MATCH hay COUNTA, nó có thể tạo các công thức rất mạnh.

Nguồn dữ liệu (SourceData)

Tất cả các công thức Excel INDEX của chúng tôi (ngoại trừ công thức cuối cùng), chúng tôi sẽ sử dụng dữ liệu dưới đây. Để thuận tiện, nó được tổ chức trong một bảng có tên SourceData.

Việc sử dụng các bảng hoặc các mảng được đặt tên có thể làm cho công thức dài hơn một tí, nhưng nó cũng làm cho chúng linh hoạt và dễ đọc hơn. Để điều chỉnh bất kỳ công thức INDEX nào cho các bảng tính của bạn, bạn chỉ cần chỉnh sửa một cái tên duy nhất.

Tất nhiên, không có gì ngăn cản bạn sử dụng mảng thông thường nếu bạn muốn. Trong trường hợp này, bạn chỉ cần thay thế tên bảng SourceData bằng tham chiếu mảng thích hợp.

1. Lấy dữ liệu ở vị trí thứ N từ danh sách

Đây là cách sử dụng cơ bản và là ví dụ về hàm INDEX dễ hiểu nhất. Để lấy một dữ liệu nhất định trong danh sách, bạn chỉ cần viết = INDEX (range, n), trong đó range là một dải ô hoặc dải ô được đặt tên, và n là vị trí của dữ liệu mà bạn muốn nhận.

Khi làm việc với bảng Excel, bạn có thể dùng trỏ chuột chọn cột và Excel sẽ đưa tên của cột cùng với tên của bảng vô công thức:

Để nhận giá trị của ô ở giao điểm của một hàng và cột đã cho, bạn sử dụng cách tiếp cận tương tự với sự khác biệt duy nhất là bạn sử dụng cả hai – số hàng và số cột.

Và đây là một ví dụ khác: Trong bảng tính mà chúng tôi lấy làm ví dụ, để tìm ra hành tinh lớn thứ 2 trong hệ mặt trời, bạn sắp xếp bảng theo cột Đường kính (Diameter) và sử dụng công thức INDEX như sau:

= INDEX (SourceData, 2, 3)

• Array là tên bảng, hoặc một tham chiếu dải, tương ứng với SourceData trong ví dụ này.
• Row_num là 2 vì bạn đang tìm kiếm mục thứ hai trong danh sách
• Column_num là 3 vì Diameter ở vị trí cột thứ 3 trong bảng.

Nếu bạn muốn trả lại tên của hành tinh thay vì đường kính, thì bạn phải thay đổi column_num thành 1. Và tự nhiên, bạn có thể sử dụng một tham chiếu ô trong đối số row_num và/hoặc column_num để làm cho công thức INDEX của bạn linh hoạt hơn, như được thể hiện trong hình bên dưới:

2. Nhận được tất cả các giá trị trong một hàng hoặc cột

Ngoài việc lấy ra một ô duy nhất, hàm INDEX có thể trả về một dải các giá trị từ toàn bộ các hàng hoặc các cột. Để lấy tất cả các giá trị từ một cột nhất định, bạn phải bỏ qua đối số row_num hoặc đặt nó là 0. Tương tự, để có được toàn bộ hàng, bạn bỏ qua hoặc 0 trong column_num.

Các công thức INDEX như vậy khó có thể được sử dụng bởi vì Excel không thể phù hợp với dải các giá trị được trả về bởi công thức trong một ô duy nhất, mà thay vào đó thì bạn sẽ nhận được #VALUE! lỗi. Tuy nhiên, nếu bạn sử dụng INDEX kết hợp với các hàm trong Excel khác, chẳng hạn như SUM hoặc AVERAGE, bạn sẽ thu được kết quả tuyệt vời.

Ví dụ, bạn có thể sử dụng công thức Index Excel sau để tính nhiệt độ trung bình của hành tinh trong hệ mặt trời:

= AVERAGE (INDEX (SourceData,, 4))

Trong công thức trên, đối số column_num là 4 vì Temperature là cột thứ 4 trong bảng của chúng ta. Thông số row_num bị bỏ qua.

Tương tự như vậy, bạn có thể tìm được 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 hành tinh (Mass là cột thứ 2 trong bảng):

= SUM (INDEX (SourceData,, 2))

Từ quan điểm thực tế, hàm INDEX trong công thức trên là không cần thiết. Bạn chỉ cần viết = AVERAGE (dải) hoặc = SUM (dải) và cũng nhận được kết quả tương tự.

Khi làm việc với dữ liệu thực, tính năng này có thể hữu ích như là một phần của các công thức phức tạp hơn mà bạn sử dụng để phân tích dữ liệu.

3. Sử dụng INDEX với các hàm Excel khác (SUM, AVERAGE, MAX, MIN)

Từ các ví dụ trước, bạn có thể nghĩ rằng công thức INDEX trong Excel trả về các giá trị, nhưng thực tế là nó trả về một tham chiếu đến ô có chứa giá trị. Và ví dụ này thể hiện bản chất thực sự của hàm INDEX.

Với kết quả của một công thức INDEX mang tính tham chiếu, chúng ta có thể sử dụng nó trong các hàm khác để tạo ra một dải động. Khó hiểu ư? Công thức sau đây sẽ làm rõ mọi thứ:

Giả sử bạn có một công thức = AVERAGE (A1: A10) trả về giá trị trung bình của các ô A1: A10. Thay vì viết dải trực tiếp như trong công thức, thì bạn có thể thay thế cả A1 hoặc A10 hoặc cả hai bằng các hàm INDEX như sau:

= AVERAGE(A1: INDEX (A1: A20,10))

Cả hai công thức trên sẽ mang lại kết quả tương tự bởi vì hàm INDEX cũng trả về một tham chiếu đến ô A10 (row_num được đặt là 10, col_num bỏ qua). Sự khác biệt là công thức AVERAGE / INDEX sử dụng cho dải động và khi bạn thay đổi đối số row_num trong INDEX, thì dải mà được xử lý bởi hàm AVERAGE sẽ thay đổi và công thức sẽ trả về một kết quả khác.

Rõ ràng, tuy cách thức công thức INDEX vận hành tỏ ra quá phức tạp, nhưng nó có các ứng dụng thực tế, như trong các ví dụ sau đây:

Ví dụ 1. Tính trung bình của các mục trong danh sách n mục đứng đầu:

Giả sử bạn muốn biết đường kính trung bình của n hành tinh lớn nhất trong hệ thống của chúng tôi. Vì vậy, bạn sắp xếp bảng theo cột Diameter từ lớn nhất đến nhỏ nhất và sử dụng công thức Average / Index sau:

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

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

Trong trường hợp bạn muốn xác định giới hạn trên và giới hạn dưới trong công thức của mình, chỉ cần sử dụng hai hàm INDEX để trả lại mục đầu tiên và mục cuối cùng bạn muốn.

Ví dụ: công thức sau trả về tổng các giá trị trong cột Diameter (đường kính) giữa hai dữ liệu được chỉ định trong ô B1 và ​​B2:

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

4. Công thức INDEX để tạo các dải động và danh sách theo mục

Điều này rất thường xảy ra. Khi bạn bắt đầu tổ chức dữ liệu trong một bảng tính, bạn có thể không biết có bao nhiêu mục bạn sẽ có.

Dù sao thì, nếu bạn có số các mục trong một cột đã cho thay đổi, từ A1 đến An, và bạn có thể muốn tạo ra một dải được đặt tên – bao gồm tất cả các ô có dữ liệu. Tại đó, bạn muốn dải được điều chỉnh tự động khi bạn thêm mục mới hoặc xóa một số mục hiện có. Ví dụ: nếu bạn hiện có 10 mục, dải được đặt tên của bạn sẽ là A1: A10. Nếu bạn thêm một mục nhập mới, phạm vi tên sẽ tự động mở rộng lên A1: A11 và nếu bạn thay đổi ý định và xóa dữ liệu mới được thêm vào, phạm vi sẽ tự động quay trở lại A1: A10.

Lợi ích chính của cách tiếp cận này là bạn không phải liên tục cập nhật tất cả các công thức trong bảng tính của bạn để đảm bảo chúng tham chiếu đến các dải một cách chính xác.

Một cách để xác định một dải động được sử dụng hàm OFFSET:

= OFFSET (Sheet_Name! $ A $ 1, 0, 0, COUNTA (Sheet_Name! $A :$A ), 1)

Một giải pháp có thể khác là sử dụng Excel INDEX cùng với COUNTA:

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

Trong cả hai công thức, A1 là ô có chứa mục đầu tiên của danh sách và dải động được tạo ra bởi cả hai công thức sẽ giống hệt nhau.

Sự khác biệt ở đây chính là cách tiếp cận. Hàm INDEX tìm thấy một ô ở giao điểm của một hàng và cột cụ thể, trong khi hàm OFFSET di chuyển từ điểm làm mốc bởi một số hàng và / hoặc các cột nhất định. Hàm COUNTA, được sử dụng trong cả hai công thức, tính số lượng các ô không rỗng trong cột và đảm bảo rằng chỉ những ô có dữ liệu được bao gồm trong dải đã đặt tên.

Hình sau đây sẽ cho bạn thấy cách bạn có thể sử dụng công thức Index để tạo một danh sách tùy chọn.

Mẹo: Cách dễ nhất để tạo một danh sách tùy chọn được cập nhật tự động trong Excel là tạo một danh sách (đã đặt tên) và được liệt kê dựa trên một bảng. Trong trường hợp này, bạn sẽ không cần bất kỳ công thức phức tạp nào vì các bảng Excel là dải động.

5. Sức mạnh của các hàm Vlookup khi kết hợp với INDEX / MATCH:

Thực hiện các hàm Vlookup theo chiều dọc trong Excel – đây là nơi mà hàm INDEX thực sự tỏa sáng. Nếu bạn đã từng thử sử dụng hàm VLOOKUP, bạn sẽ nhận thức được nhiều hạn chế của nó, chẳng hạn như không có khả năng kéo giá trị từ các cột qua bên trái của cột tra cứu hoặc giới hạn 255 ký tự cho một giá trị tra cứu. Và vì thế cho nên kết hợp index và match là giải pháp mà nhiều người lựa chọn để thay thê

Xem thêm: Sự kết hợp hàm INDEX và MATCH trong Excel – Sự thay thế tốt hơn cả hàm VLOOKUP

Hàm INDEX / MATCH tốt hơn VLOOKUP ở nhiều khía cạnh:

• Không có vấn đề với dò tìm bên trái.
• Không giới hạn kích thước giá trị tra cứu.
• Không yêu cầu phân loại (VLOOKUP với kết quả tương đối khi yêu cầu sắp xếp cột tra cứu theo thứ tự tăng dần).
• Bạn được tự do chèn và loại bỏ các cột trong một bảng mà không cần cập nhật mỗi công thức liên quan.

Và cuối cùng nhưng không kém phần quan trọng, INDEX / MATCH không làm chậm Excel của bạn như nhiều hàm Vlookup đã làm.

Bạn sử dụng INDEX / MATCH – hàm index kết hợp match theo cách sau:

= INDEX (column to return a value from, (MATCH (lookup value, column to lookup against, 0))

Ví dụ: nếu chúng ta mở bảng dữ liệu gốc của chúng ta ra với tên hành tinh (planet name) trở thành cột đầu tiên bên phải, thì hàm công thức INDEX / MATCH vẫn lấy giá trị phù hợp từ cột bên trái mà không gặp bất kì trở ngại nào.

Khóa học excel kế toán online

6. Sử dụng hàm INDEX để lấy 1 dải từ danh sách các dải:

Một cách sử dụng thông minh và hữu hiệu hàm INDEX trong Excel là bạn có thể có được một dải từ một danh sách các dải.

Giả sử, bạn có nhiều danh sách với số lượng các mục khác nhau.

Trước hết, bạn  đặt tên cho mỗi dải trong mỗi danh sách; Ví như có thể đặt là PlanetsD và MoonsD trong ví dụ này:

Tuy nhiên, bảng Moons còn chưa hoàn chỉnh, có 176 mặt trăng tự nhiên được biết đến trong Hệ Mặt trời của chúng ta, Jupiter có 63 và vẫn còn nhiều hơn nữa. Đối với ví dụ này, tôi chọn 11 cái ngẫu nhiên.

Giả sử rằng PlanetsD là dải 1 của bạn và MoonsD nằm trong dải 2 và ô B1 là nơi bạn đặt số dãy, bạn có thể sử dụng công thức Index sau để tính giá trị trung bình của các giá trị trong dải ô được chọn:

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

Hãy lưu ý rằng bây giờ chúng ta đang sử dụng định dạng tham chiếu của hàm INDEX, và số trong đối số cuối cùng (area_num) cho công thức biết phạm vi dải nào được lựa chọn.

Trong hình bên dưới, area_num (cell B1) được đặt thành 2, trong công thức tính đường kính trung bình của Moons vì dải MoonsD đứng thứ 2 trong đối số tham chiếu.

Nếu bạn làm việc với nhiều danh sách và không muốn nhớ các số liên quan, bạn có thể sử dụng thêm hàm IF để làm điều này cho bạn:

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

Trong hàm IF, bạn sử dụng một số tên danh sách đơn giản và dễ nhớ mà bạn muốn người dùng nhập vào ô B1 thay vì số. Xin lưu ý rằng, để công thức làm việc chính xác, văn bản trong B1 phải chính xác như nhau (không phân biệt chữ hoa chữ thường) như trong các thông số của IF, nếu không công thức Index của bạn sẽ xuất ra #VALUE lỗi.

Thậm chí, để công thức thân thiện hơn, bạn có thể sử dụng Excel Data Validation để tạo danh sách tùy chọn để tránh lỗi chính tả và lỗi trang in:

Cuối cùng, để làm cho công thức INDEX của bạn hoàn toàn hoàn hảo, bạn có thể đặt nó trong hàm IFERROR sẽ nhắc người dùng chọn một mục từ danh sách theo mục, nếu không có lựa chọn nào được đưa ra:

= IFERROR (AVERAGE (INDEX ((PlanetsD, MoonsD),,, IF (B1 = “planet”, 1, IF (B1 = “moon”, 2)))), “Please select the list!”)

Trên đây là hướng dẫn cách bạn sử dụng công thức INDEX trong Excel. Tôi hy vọng những ví dụ này cho bạn vài cách để khai thác tiềm năng của hàm INDEX trong Excel trong bảng tính của bạn. Cảm ơn bạn đã đọc!

Để có thể ứng dụng tốt Excel vào trong công việc, chúng ta không chỉ nắm vững được các hàm mà còn phải sử dụng tốt cả các công cụ của Excel. Những hàm nâng cao giúp áp dụng tốt vào công việc như SUMIFS, COUNTIFS, SUMPRODUCT, INDEX + MATCH… Những công cụ thường sử dụng là Data validation, Conditional formatting, Pivot table…

Toàn bộ những kiến thức này các bạn đều có thể học được trong khóa học:

EX101 – Excel từ cơ bản đến nâng cao dành cho người đi làm