NHÓM HÀM THAM CHIẾU VÀ DÒ TÌM

1. Hàm COLUMN( )
Chức năng: Hàm COLUMN trả về số thứ tự cột của ô đầu tiên góc trên bên trái của vùng tham chiếu.
Cú pháp: COLUMN(reference)
Các tham số: 

• Reeference: Là vùng tham chiếu. Reference có thể là một ô hay nhiều ô liên tiếp nhau. Nếu vùng tham chiếu để trống thì hàm trả về số thứ tự cột của ô hiện hành chứa công thức.

Vd:
 

2. Hàm COLUMNS( )
Chức năng: Hàm COLUMNS  trả về số cột có trong vùng tham chiếu.
Cú pháp: COLUMNS(array)
Các tham số:

• Array: Là vùng tham chiếu, có thể là một ô, một vùng nhiều ô hay một mảng tham chiếu.

Vd:
 

3. Hàm ROW( )
Chức năng: Hàm ROW trả về số thứ tự dòng của ô đầu tiên góc trên bên trái của vùng tham chiếu.
Cú pháp: ROW(reference)
Các tham số:

• Reference: Là vùng tham chiếu, Reference có thể là một ô hay nhiều ô liên tiếp nhau. Nếu vùng tham chiếu để trống thì hàm trả về số thứ tự dòng của ô hiện hành chứa công thức.

Vd:
 
4. Hàm ROWS( )
Chức năng: Hàm ROWS trả về số dòng có trong vùng tham chiếu.
Cú pháp: ROWS(array)
Các tham số:

• Array: Là vùng tham chiếu, có thê là một ô, một vùng nhiều ô hay một mảng tham chiếu.

Vd:
 
5. Hàm MATCH( )
Chức năng: Hàm Match là hàm dò tìm, sẽ trả về giá trị của của giá trị dò tìm (một dòng hoặc một cột).
Cú pháp: MATCH(lookup_value,lookup_array,match_type)
Các tham số:

• Lookup_value: Là giá trị dùng để dò tìm, lookup_value có thể là một số, một chuỗi, một giá trị logic hay một tham chiếu.
• Lookup_array: Là vùng dò tìm, có thể có một cột hoặc một dòng, hoặc một mảng các giá trị.
• Match_type: Là tùy chọn để xác định kiểu dò tìm. Có 3 tùy chọn là -1, 0, 1.
• Match_type = 0: Hàm sẽ dò tìm chính xác giá trị lookup_value trong lookup_array, nếu không tìm thấy hàm sẽ báo lỗi #N/A.
• Macth_type = 1(hoặc để trống): Hàm sẽ dò tìm giá trị lớn nhất trong lookup_array mà các giá trị <= giá trị của lookup_value. Trường hợp này, các phần tử trong lookup_array phải được sắp xếp từ nhỏ đến lớn (..-2, -1, 0, 1, 2… A, B, C..Z… False, True).
• Match_type = -1: Hàm sẽ dò tìm giá trị nhỏ nhất trong lookup_array mà có giá trị >= giá trị của lookup_value. Trường hợp này, các phần tử trong lookup_array phải được sắp xếp từ lớn đến nhỏ (True, False,.. Z…A, ..2, 1, 0, -1, -2).

Vd1: Match_type = 0
 
Vd2: Match_type = 1
 

6. Hàm INDEX( )
Chức năng: Hàm INDEX trả về một giá trị hay tham chiếu đến một giá trị trong một bảng hoặc một mảng.
Cú pháp: Hàm INDEX có hai hình thức là dạng mảng và dạng tham chiếu.
a.    Dạng Mảng: INDEX(array, row_num, col_num)
Các tham số:

• Array: Là một dãy ô hay một hằng mảng.
• Row_num: Là số chỉ dòng của giá trị trong mảng cần trả về. Nếu bỏ qua row_num thì buộc phải có col_num.
• Col_num: Là chỉ số cột của giá trị trong mảng cần trả về. Nếu bỏ qua col_num thì buộc phải có row_num.

Lưu ý:

• Nếu mảng chỉ có một dòng hoặc một cột, thì row_num hay col_num tương ứng là tùy chọn.
• Nếu mảng có nhiều hơn một dòng hoặc một cột, và chỉ có hoặc row_num hoặc col_num được sử dụng, INDEX ( ) sẽ trả về toàn bộ dòng hay cột của mảng.
• Nếu hai đối số col_num và row_num đều được sử dụng, INDEX( ) sẽ trả về giá trị của ô là giao điểm của col_num và row_num.
• Row_num và col_num phải chỉ tới một ô trong mảng, nếu không, INDEX( ) sẽ thông báo #REF!

b.    Dạng Tham Chiếu: INDEX(reference,row_num,col_num,area_num)
Các tham số:

• Reference: Là một tham chiếu đến một hoặc nhiều dãy ô liên tiếp.
• Row_num: Là chỉ số dòng tham chiếu trong vùng được trả về.
• Col_num: Là số chỉ cột trong vùng tham chiếu được trả về.
• Area_num: Là chỉ số thứ tự cửa vùng trong tham chiếu được chọn. Nếu để trống, hàm mặc định chọn vùng 1.

Lưu ý:

• Nếu row_num hoặc col_num bằng 0, hàm sẽ trả về tham chiếu cho toàn bộ cột hoặc toàn bộ dòng tương ứng.
• Col_num, row_num và area_num phải chỉ tới một ô bên trong tham chiếu, nếu không, INDEX( ) sẽ báo lỗi #REF!.
• Nếu bỏ qua col_num và row_num, INDEX( ) sẽ trả về một vùng trong tham chiếu, được chỉ định bởi area_num.
• Kết quả mà hàm INDEX trả về chính là một tham chiếu, là giao điểm của dòng và cột trong vùng tham chiếu được trả về.

Vd1:
 
Vd2:
 

7. Hàm LOOKUP( )
Chức năng: Dùng để dò tìm một giá trị trong một dòng hoặc một cột hoặc trong một mảng các giá trị.
Cú pháp: Hàm LOOKUP có hai hình thức là dạng vector và dạng mảng:

• LOOKUP(lookup_value,lookup_vector,result1_vector)
• LOOKUP(lookup_value,array)

Các tham số:

• Lookup_value: Là giá trị sẽ được tìm kiếm trong vùng Lookup_vector. Lookup_value có thể là một số, một chuỗi hay một tham chiếu.
• Lookup_vector: Là vùng dò tìm, vùng này có thể là một dòng hay một cột. Giá trị chứa trong vùng có thể là số, chuỗi.
• Result_vector: Là vùng chứa giá trị trả về (1 cột hoặc 1 dòng). Độ lớn của Result_vector phải tương ứng với Lookup_vector.
• Array: Là vùng tìm kiếm, có thể là một vùng hay một mảng. Giá trị chứa trong array có thể là số, chuỗi, giá trị logic.

Lưu ý:

• Lookup_vector phải được sắp xếp theo thứ tự tăng dần như: -2, -1, 0, 1, 2; A, B, C..Z; False, True. Nếu không, kết quả trả về nhiều khi không chính xác.
• Nếu như không tìm thấy Lookup_value trong vùng dò tìm, hàm sẽ lấy giá trị lớn nhất có trong vùng dò tìm (Lookup_Vector) mà có giá trị nhỏ hơn hoặc bằng giá trị dò tìm Lookup_vector.
• Nếu giá trị nhỏ nhất trong vùng dò tìm Lookup_vector mà lớn hơn giá trị của Lookup_value thì hàm báo lỗi #N/A.

Vd:
 

8. Hàm INDIRECT( )
Chức năng: Hàm INDIRECT trả về một tham chiếu từ một chuỗi ký tự. Cũng có thể dùng hàm INDIRECT để thay đổi tham chiếu tới một ô bên trong công thức mà không cần thay đổi công thức đó.
Cú pháp: INDIRECT(ref_text,a1)
Các tham số:

• Ref_text: Là tham chiếu đến một ô (tham chiếu có dạng là A1 hay R1C1), là tên định nghĩa của một tham chiếu hay một tham chiếu đến ô dưới dạng chuỗi.
• Nếu ref_text là một ô tham chiếu không hợp lệ, hàm sẽ trả về lỗi #REF!.
• Nếu ref_text chứa tham chiếu đến một bảng tính khác thì bảng tính này phải được mở, nếu không hàm sẽ trả về #REF!.
• A1: Là một giá trị logic để xác định kiểu tham chiếu trong ref_text.
• Nếu A1 là True hay 1 (mặc định là để trống) thì ref_text có dạng tham chiếu là A1.
• Nếu A1 là False hay 2 thì ref_text có dạng tham chiếu là R1C1.

Vd: