Hàm Index trong excel

Hàm Index trong Excel có 2 kiểu trả về kết quả. Trả về một giá trị hoặc tham chiếu tới một giá trị từ trong một bảng hoặc một vùng dữ liệu
Khi bạn gõ chữ Index trong excel thì nó sẽ hiện ra 2 tùy chọn sau:

Công thức 1 : Array Form;
=Index(Array,Row_num,Column_num)

Công thức 2 : Reference Form
=Index(Reference,Row_num,Column_num,Area_num)

Tips: Bạn nên dùng functions Wizard để gọi hàm, cách làm như sau, mỗi khi đánh dấu = vào 1 cell thì trên góc trái màn hình nơi thể hiện vị trí của ô, nó sẽ xuất hiện cách hàm thường hay sử dụng, bạn nhấp chuột vào mũi tên bé bé để tìm và chọn hàm Index, hàm này không thường trực ở hộp thoại này thì bạn nhấp chuột vào More functions để gọi nó ra. Khi bạn chọn hàm Index thì sẽ có hộp thoại sau xuất hiện.



Tùy theo nhu cầu sử dụng nếu bạn muốn hàm index trả về cho bạn Giá trị của một ô cụ thể hoặc là một vùng (Có nhiều ô) thì dùng công thức Array Form (Vùng dữ liệu)-Công thức 1; Còn nếu muốn nó trả về dạng tham chiếu đến một cell cụ thể thì dùng công thức Reference Form -Công thức 2

Bây giời chúng ta đi vào từng cách dùng một nhé

Công thức 1 – Array Form để trả về giá trị của một ô hoặc một vùng dữ liệu.



Khi bạn nhấp chuột vào box Array: thì bên dưới của hộp thoại này sẽ giải thích cho ta ý nghĩa của tham số này:

►Array : is a range of cells or an array constant
Tham số này một tập hợp các ô trong excel hoặc một tập hợp các hằng số.
Nếu trong box này bạn nhập vùng dữ liệu chỉ nằm trong 1 cột hoặc 1 dòng của excel thì đôi số Row_num hoặc column_num là tùy chọn

►Row_num   se-lects the row in array f-rom which to return a value. If row_num is omitted, column_num is required.
Chúng ta nhìn vào hộp thoại functions arguménts bên trên thì tham số cần nhập là số nguyên, lớn hơn 0, tham số này dùng để chỉ định cho hàm trả về giá trị ở dóng số bao nhiêu trong vùng được chọn làm array.

►Column_num   se-lects the column in array f-rom which to return a value. If column_num is omitted, row_num is required.
Tham số cần là một số nguyên lớn hơn 0; giới hạn cho hàm trả về giá trị ở cột (column) số trong vùng dữ liệu được chọn. nếu bạn điền vào 0 hoặc 1 thì hàm sẽ trả về giá trị ở cột thứ nhất của vùng.

Ví dụ ở ô D2, ta có công thức = Index(H5:H19,10), vì vùng dữ liệu được chọn để tham chiếu chỉ có 1 cột, do đó ta chỉ nhập vị trí của giá trị cần trả về, ở đây, tôi nhập số 10, tức là tôi muốn hàm Index trả về cho tôi giá trị ở dòng thứ 10 - chữ J, tính từ dòng đầu tiên trong vùng H6:h19, bạn xem hình sau sẽ rõ.



Nếu bạn nhập vào box array nhiều hơn 1 dòng hàng và nhiều hơn 1 cột và chỉ nhập vào hoặc số dòng (row_num) hoặc số cột (column_num) thì hàm Index sẽ trả về một mảng của cả một dòng hoặc một cột trong vùng ô đã nhập trong box Array bên trên.

Cũng ví dụ trên ở ô D2, Nếu tôi chọn vùng dữ liệu trong box Array là: D5:H19:

Nếu tôi muốn hàm trả về giá trị ở cột thứ 1 cột D5:D5 thì tham số row_num phải là 1 số lớn hơn 1, nếu bạn nhập số 0, thì hàm sẽ bị lỗi, và tham số column_num là tùy chọn (từ 0~vô cùng), tương tự như trên tôi muốn lấy giá trị J ở cột H thì ta phải gõ = Index(H5:H19,10,5) Nếu tôi muốn lấy giá trị ở cột thứ 2~5 (Trong vùng này chỉ có 5 cột) thì ta sẽ nhập số column_num tương ứng.

Vậy chốt lại là: ta xem Array là một hệ trục tọa độ, có tọa độ trục là góc trên cùng, bên trái của một vùng được chọn, trong đó row_num và column_num là giá trị x, y tương ứng với một ô được chọn cho Array. Và muốn hàm Index trả về giá trị của Cell nào thì ta sẽ gán row_num và column_num theo đúng tọa độ tương ứng

Bây giờ chúng ta chuyển sang cách dùng của công thức 2 nhé.

Công thứ 2: Reference Form:

Công thức đầy đủ của nó như sau:
=INDEX(reference,row_num,column_num,area_num)
Bốn tham số có ý nghĩa như sau:
►Reference   is a reference to one or more cell ranges.
Là vùng tham chiếu, có thể là một vùng liền nhau hoặc cách nhau.
Nếu bạn chọn những vùng không liền kề nhau thì các vùng tham chiếu được cách nhau dấu "," và nằm trong ngoặc đơn ví dụ: =INDEX((A1:C6,A8:C11),2,2,2),

Nếu mỗi vùng (Area) chỉ chứa 1 cột hoặc một hàng thì tham số row_num or column_num là tùy chọn
Ví dụ tham chiếu là 1 dòng ta có công thức sau =INDEX(reference:A1:A8,,column_num).

►Row_num   is the number of the row in reference f-rom which to return a reference.
Dòng số bao nhiêu trong vùng tham chiếu muốn trả về (Tương tự ở công thức 1)

►Column_num   is the number of the column in reference f-rom which to return a reference.
Cột số bao nhiêu trong vùng tham chiếu muốn trả về (Tương tự ở công thức 1)

►Area_num   se-lects a range in reference f-rom which to return the intersection of row_num and column_num. The first area se-lected or entered is numbered 1, the second is 2, and so on. If area_num is omitted, INDEX uses area 1.
Ở công thức này ta thấy xuất hiện Area_num, hộp này sẽ giới hạng cho hàm index là chọn vùng tham chiếu nào, nếu reference có nhiều hơn một vùng - Area.

Ví du sau:
Nếu vùng tham chiếu reference là (A1:B4,D1:E4,G1:H4), area_num 1 là vùng A1:B4; area_num 2 là vùng D1:E4; area_num 3 là vùng G1:H4.

Như vậy muốn hàm index trả về giá trị nằm trong mảng reference nào thì đánh số 1, 2, hoặc 2, v.v tương ứng với số lượng reference ở trong công thức. Nếu ta nhập tham số này là 2, thì ở ví dụ trên, hàm sẽ trả về giá trị ở tham chiếu D1:E4 (Area_num: 2)

 Nếu chỉ có một vùng dữ liệu tham chiếu thì không cần tham số Area-num, và công thức 2 này trở thành công thức 1 nhé.
 

1
2
3
4
5
6
7
8
9
10
11

A	B	C
Hoa Quả	Giá	Số Lượng
Táo	0.69	40
Chuối	0.34	38
Chanh	0.55	15
Cam	0.25	25
Lê	0.59	40
Hạnh Nhân	2.80	10
Hạt điêu	3.55	16
Đậu phộng	1.25	20
Óc chó	1.75	12
Formula	Miêu tả cách thực hiện	Kết   Quả
=INDEX(A2:C6,2,3)	Giao nhau giữa hang thứ 2, cột thứ 3 trong vùng A2:C6 là giá trị của ô C3.	38
=INDEX((A1:C6,A8:C11),2,2,2)	Giao nhau giữa dòng thứ 2 (Số 2 thứ nhất) và cột thứ 2 (Số 2 thứ 2) trong vùng thứ 2 (Số 2 thứ 3)	3.55
=SUM(INDEX(A1:C11,0,3,1))	Tổng của cột 3 trong vùng thứ nhất thuộc khoảng A1:C11, chính là tổng của C1:C6	216
=SUM(B2:INDEX(A2:C6,5,2))	Tổng của khoảng bắt đầu ở B2 và kết thúc tại giao điểm của hàng 5 và cột 2 của khoảng A2:A6, chính là tổng của B2:B6.	2.42

Công thức thứ 2 này chỉ thật sự hiệu quả thi các tham số row_num, colmn_num và area_num là những con số được tạo ra tự động.

Để làm được điều đó chúng ta phải học thêm về  hàm IF, hàm Offset và Hàm Match để tối ưu hóa cho hàm index:

Cụ thể ta dùng hàm offset tham trả về vị trí của dòng cần đến vào tham số row_num
Dùng hàm match trả về vị trí của cột cần đến vào tham số column_num
Và tham số Area_num là được nhập tại một cell hoặc dựa trên một điều kiện nào đó của hàm If trong excel.

Chúc các bạn thành công.