HÀM SỐ TRONG EXCEL -HÀM TÌM KIẾM VÀ THAM CHIẾU

Đánh giá bài viết này

CÁC HÀM SỐ TRONG EXCEL

 Data & Time          Math        Logical

 Text & Data        Information  Lookup

Statistical                    Error

HÀM TÌM KIẾM VÀ THAM CHIẾU (Lookup & Reference)

Bao gồm các hàm tìm kiếm và tham chiếu rất hữu ích khi bạn làm việc với CSDL lớn trong EXCEL như kế toán, tính lương, thuế…
Tên hàm
Công dụng
Tên hàm
Công dụng
ADDRESS Tạo địa chỉ dạng chuỗi ký tự AREAS Đếm số vùng tham chiếu
CHOOSE Trả về giá trị trong mảng giá trị tại vị trí được chỉ định. COLUMN Trả về số thứ tự cột của ô đầu tiên trong vùng tham chiếu.
COLUMNS Trả về số cột của vùng tham chiếu. HLOOKUP Dò tìm một giá trị trên hàng đầu tiên và trả về …
HYPERLINK Tạo một siêu liên kết INDEX Trả về một giá trị trong bảng dữ liệu tương ứng với chỉ mục của nó.
INDIRECT Trả về giá trị của một tham chiếu LOOKUP Dò tìm một giá trị
MATCH Trả về vị trí của một giá trị trong bảng dữ liệu OFFSET Trả về một vùng tham chiếu từ một vùng xuất phát.
ROW Trả về số thứ tự dòng của ô đầu tiên trong dãy ô. ROWS Trả về số dòng của dãy tham chiếu.
TRANSPOSE Hoán vị hướng một vùng một giá trị. VLOOKUP Dò tìm một giá trị trên cột đầu tiên và trả về …
 
=====================
ADDRESS

Công dụng

Tạo địa chỉ ở dạng chuỗi văn bản.

Công thức

=ADDRESS(row_num,column_num,abs_num,a1,sheet_text) row_num: số thứ tự dòng của địa chỉ
colmn_num: số thứ cột của địa chỉ abs_num: loại địa chỉ trả về

abs_num Kiểu địa chỉ trả về
1 (hoặc không có)
Tuyệt đối
2 Dòng tuyệt đối, cột tương đối
3 Dòng tương đối, cột tuyệt đối
4 Tương đối
a1 là giá trị kiểu logic xác định dạng địa chỉ trả về ở dạng A1 (<Tên cột><Tên dòng>) hay R1C1 (<Số thứ dòng><Số thứ cột>). Nếu a1 là TRUE thì địa chỉ trả về dạng A1, ngược lại là dạng R1C1.
sheet_text tên trang bảng tính đặt trong dấu nháy kép. Nếu bỏ qua địa chỉ trả về không có tên trang bảng tính đi kèm.

Ví dụ

Để dễ hiểu hơn, bạn hãy nhập các công thức sau vào ô trống bất kỳ trong bảng tính.

=ADDRESS(5,10). Trả về $J$5.
=ADDRESS(5,10,4,FALSE). Trả về R[5]C[10].=ADDRESS(5,10,,,”[Cham cong]Bang luong “). Trả về ‘[Cham cong]Bang luong ‘!$J$5


AREAS

Công dụng

Trả về số vùng tham chiếu trong một tham chiếu. Mỗi vùng tham chiếu là một ô rời rạc hoặc là một dãy ô liên tục trong bảng tính.

Công thức

=AREAS(reference)

reference: là một hoặc nhiều vùng tham chiếu mà bạn cần đếm. Nếu muốn tạo đưa nhiều vùng rời rạc nhau vào công thức thì bạn phân cách chúng bằng dấu phẩy. Cần phải đặt tất cả các vùng địa chỉ này vào trong dấu ngoặc đơn ngoài dấu ngoặc đơn của hàm số.

Ví dụ

Để dễ hiểu hơn, bạn hãy nhập các công thức sau vào ô trống bất kỳ trong bảng tính.

=AREAS((A1,C$15,B30,H49)). Trả về 4.
=AREAS((A5:A10,C10:C15)). Trả về 2.


CHOOSE

Công dụng

Trả về một giá trị tại vị trí được chỉ định trong dãy giá trị.

Công thức

=CHOOSE(index_num,value1,value2,…)

index_num: là vị trí của giá trị cần trả về. Nếu index_num là 1 thì hàm trả về giá trị thứ nhất, index_num là 2 thì hàm trả về giá trị thứ 2,…
value1, value2,… có thể có từ 1 đế 29 giá trị. Các giá trị này có thể là số, địa chỉ ô, tên vùng tham chiếu, công thức, hàm hoặc chuỗi.

Lưu ý!

Nếu index_num là một số nhỏ hơn 1 và lớn hơn số giá trị có trong công thức, hàm trả về lỗi #VALUE!.
Nếu index_num là phân số, nó sẽ lấy phần nguyên của số đó.
Nếu index_num là một mảng giá trị, thì từng giá trị trong bảng đó sẽ được thực hiện với hàm CHOOSE.

Danh sách các giá trị có thể là giá trị đơn lẽ hoặc vùng tham chiếu.

Ví dụ

Để dễ hiểu hơn, bạn hãy copy dữ liệu bên trong bảng dưới và nhập các công thức sau vào ô trống bất kỳ trong bảng tính.
A B C
D
E
F
1
LỊCH THEO DÕI DỊCH CÚM TYPE A H5N1
Thứ Bác sĩ trực Số bệnh nhân Dương tính Tử vong Ghi chú
2 Bình 15 1 0
3 Nhân 12 0 0
4 Toán 10 2 1
5 Hùng 5 3 0
6 Dũng 4 2 1
7 Hoàng 6 0 0
CN Trí 8 1 0
2
3
4
5
6
7
8
9
=CHOOSE(2,B3:B9). Trả về #VALUE!.
=CHOOSE(2,B3,B4,B5,B6,B7,B8,B9). Trả về Nhân.
=SUM(CHOOSE(3,B3:B9,D3:D9,E3:E9). Trả về 2. Kết hợp hàm CHOOSE và hàm SUM để tính tổng số người tử vong.

COLUMN

Công dụng

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ông thức

=COLUMN(reference)

reference: là ô hoặc vùng ô. Nếu reference không nhập thì hàm trả về số thứ tự cột của ô đang đứng.

Lưu ý!

reference không thể bao gồm nhiều vùng tham chiếu.

Ví dụ

Để dễ hiểu hơn, bạn hãy nhập các công thức sau vào ô trống bất kỳ trong bảng tính.

=COLUMN(S10:T20). Trả về 19.
=COLUMN(Z1). Trả về 26.


COLUMNS

Công dụng

Trả về số cột vùng tham chiếu.

Công thức

=COLUMNS(reference)

reference: là ô hoặc vùng ô, mảng tham chiếu.

Ví dụ

Để dễ hiểu hơn, bạn hãy nhập các công thức sau vào ô trống bất kỳ trong bảng tính.
=COLUMNS(S10:T20). Trả về 2.

=COLUMNS(A1:E10). Trả về 5.


HLOOKUP

Công dụng

Dò tìm một giá trị ở dòng đầu tiên của một bảng dữ liệu. Nếu tìm thấy sẽ trả về giá trị ở cùng trên cột với giá trị tìm thấy trên hàng mà bạn chỉ định. Hàm HLOOKUP thường dùng để điền thông tin vào bảng dữ liệu từ bảng dữ liệu phụ.

HLOOKUP xuất phát từ horizontal lookup : dò tìm theo phương ngang, hay theo dòng.

Công thức

=HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)

lookup_value: là tìm một giá trị dùng để tìm kiếm, nó có thể là một giá trị, một tham chiếu hay một chuỗi ký tự.
table_array là vùng chứa dữ liệu cần tìm. Đây là bảng dữ liệu phụ có nội dung thường cố định, bao quát để bạn lấy dữ liệu.
  • Các giá trị ở dòng đầu tiên có thể là giá trị số, chuỗi ký tự, hoặc logic.
  • Nếu range_lookup là TRUE thì các giá trị ở dòng đầu tiên của bảng dữ liệu phụ này phải được sắp xếp tăng dần từ -2,1,0,1,2,…,A-Z,FALSE,TRUE. Nếu không hàm HLOOKUP sẽ trả giá trị không chuẩn xác.
  • Để sắp xếp các giá trị trong bảng dữ liệu từ trái qua phải để hàm cho kết quả phù hợp khi bạn dùng range_lookup là TRUE: Chọn vùng dữ liệu cần sắp xếp, kích vào menu Data, Sort. Nhấn nút Options bên dưới, đánh dấu Soft left to right, rồi nhấn OK. Kích chọn dòng cần sắp xếp trong danh sách. Chọn Ascending, và nhấn OK

row_index_num số thứ tự dòng trên bảng dữ liệu phụ mà dữ liệu bạn cần lấy. Giá trị trả về nằm trên dòng bạn chỉ định này và ở cột mà hàm tìm thấy giá trị dò tìm lookup_value.

range_lookup là giá trị logic bạn chỉ định muốn HLOOKUP tìm kiếm chính xác hay là tương đối. Nếu range_lookup là TRUE hàm sẽ trả về kết quả tìm kiếm tương đối. Nếu không tìm thấy kết quả chính xác, nó sẽ trả về một giá trị lớn nhất mà nhỏ hơn giá trị tìm kiếm lookup_value. Nếu range_lookup là FALSE hàm tìm kiếm chính xác, nếu không có trả về lỗi #N/A!

Lưu ý!

Nếu lookup_value nhỏ hơn giá trị nhỏ nhất trong dòng đầu tiên của bảng dữ liệu phụ, HLOOKUP trả về lỗi #N/A!.
Khi xuất hiện lỗi #N/A! bạn có thể mắc lỗi nhập dư một khoảng trống ở phía sau giá trị dò tìm hoặc trong bảng dữ liệu, kể cả chính và phụ.
Khi dùng hàm HLOOKUP để điền dữ liệu cho một bảng dữ liệu thì trong công thức cần phải tạo địa chỉ tuyệt đối cho bảng dữ liệu phụ table_array để công thức đúng cho các hàng còn lại khi bạn copy công thức xuống các ô bên dưới.

Ví dụ

Để dễ hiểu hơn, bạn hãy nhập các công thức sau vào ô trống bất kỳ trong bảng tính.
A B C D E
1
DANH MỤC HÀNG HÓA NHẬP KHẨU THÁNG 12
STT Mã hàng Thuế Ngày nhập Số lượng
1 HDD ?
01/12/2006
20
2 CPU ?
03/12/2006
50
3 CDR ? 05/12/2006 70
4 HDD ? 07/12/2006 100
5 CPU ? 08/12/2006 200
6 USB ? 07/12/2006 500
THUẾ NHẬP KHẨU
MH HDD CPU CDR USB
Thuế 5% 8% 7% 10%

2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

=HLOOKUP(B3,$B$10:$E$11,2,0).  Nhập công thức vào ô C3 để lấy mức thuế nhập khẩu tương ứng biểu thuế của từng mặt hàng bên dưới.
 Copy công thức xuống các ô còn lại. Lưu ý địa chỉ vùng ô của bảng dữ liệu phụ phải là địa chỉ tuyệt đối để khi copy công thức hàm HLookup mới đúng cho các ô tiếp theo.


HYPERLINK

Công dụng

Tạo một kết nối, hay lối tắt để mở một trang tài liệu từ một máy trong mạng LAN, intranet, internet… Khi bạn kích hoạt ô có chứa hàm HYPERLINKS, Microsoft Excel sẽ mở tài liệu được lưu trữ trong đường dẫn của hàm.

Công thức

=HYPERLINK(link_location,friendly_name)

link_location: đường dẫn của tài liệu cần mở nhập ở dạng chuỗi ký tự. Link_location có thể chỉ đến một nơi nào đó trong tài liệu như một ô đặc biệt, tên của một dãy ô trong một trang bảng tính hoặc một bảng tính, hoặc chỉ đến một đánh dấu (bookmark) trong Microsoft Excel. Đường dẫn này cũng có thể chỉ đến một tập tin lưu trên ổ cứng, hoặc một đường dẫn truy xuất nội bộ trên một máy chủ hoặc một đường dẫn tài nguyên URL trên mạng intranet, internet.
  • Link_location có thể là một chuỗi ký tự đặt trong dấu nháy kép, hoặc một ô nào đó chứa đường dẫn dưới dạng chuỗi ký tự.
  • Nếu link_location liên kết đến một tài nguyên không tồn tại, sẽ xuất hiện lỗi khi bạn kích vào ô chứa hàm HYPERLINK này.

friendly_name Là nội dung hiển thị trong ô chứa hàm HYPERLINK, có thể là một số, hoặc chuỗi ký tự. Nội dung này sẽ hiển thị bằng màu xanh và có gạch chân, nếu không có nó thì link_location sẽ hiển thị.

  • Friendly_name có thể là một giá trị, một chuỗi ký tự, một tên mảng, hoặc một ô liên kết đến một giá trị hoặc một chuỗi văn bản.
  • Nếu frinedly_name liên kết đến một giá trị bị lỗi, thì chính tên cái lỗi đó sẽ được hiển thị để thay thế cho nội dung bạn cần.

Lưu ý!

Đế chọn ô chứa HYPERLINK mà không mở liên kết đó thì bạn đưa chuột đến ô và nhấn giữ cho đến khi xuất hiện dấu cộng màu trắng thì thả chuột ra.

Ví dụ

Để dễ hiểu hơn, bạn hãy nhập các công thức sau vào ô trống bất kỳ trong bảng tính.

=HYPERLINK(“http://www.microsoft/excel/report.xls“,”Báo cáo”) Hàm này sẽ giúp bạn mở một tài nguyên trên Internet (nếu tồn tại).
=HYPERLINK(“C:”,”Mở ổ C:”) Giúp mở ổ C:.


INDEX

Công dụng

Trả về một giá trị, hoặc một tham chiếu đến một giá trị nằm bên trong một bảng hoặc một mảng dựa vào chỉ số dòng và cột. Hàm INDEX có 2 dạng: Mảng và Tham chiếu. Dạng mảng luôn luôn trả về một giá trị hoặc một mảng giá trị. Dạng tham chiếu luôn trả về một tham chiếu.

Dạng mảng

Công thức=INDEX(array,row_num,column_num)
array là một dãy ô hoặc mảng hằng.

Nếu array chỉ có một dòng hoặc một cột, các đối số row_num hoặc column_num là tùy chọn.
Nếu array có nhiều hơn một dòng hoặc một cột, chỉ có một đối số row_num hoặc column_num được dùng.
row_num chỉ số dòng cần trả về giá trị. Nếu không nhập thì hàm sẽ lấy chỉ số cột colum_num.
colum_num chỉ số cột cần trả về giá trị. Nếu không nhập thì hàm sẽ lấy chỉ số dòng row_num.

Lưu ý!

Nếu cả 2 đối số row_numcolum_num đều được dùng thì hàm trả về giá trị trong ô giao điểm giữa row_num và colum_rum.
Nếu cả 2 đối số row_num và colum_num đều là 0. Hàm sẽ trả về một mảng giá trị theo thứ tự. Để trả về một mảng giá trị trong Excel bạn phải nhập công thức dưới dạng công thức mảng. Để nhập công thức mảng: Bôi đen vùng ô cho công thức, Nhấn phím F2, nhập công thức và nhấn tổ hợp phím CTRL + SHIFT + ENTER. Nếu không hàm sẽ trả về lỗi #VALUE!
row_numcolumn_num phải chỉ vào một thứ tự của mảng giá trị. Nếu không hàm INDEX sẽ trả về lỗi #REF!

Ví dụ

Để dễ hiểu hơn, bạn hãy copy dữ liệu bên trong bảng sau vào một trang bảng tính mới, nhập công thức bên dưới vào.
A B C
1
Ma trận A
5 10 2
2 -5 6
5 10 4
2
3
4
=INDEX(A2:C4,2,2). Trả về -5. Phần tử (2,2) của ma trận A

Dạng tham chiếu

Công thức=INDEX(reference,row_num,column_num,area_num)
reference tham chiếu đến một hoặc nhiều dãy ô.

  • Nếu bạn nhập một dãy ô không liên tục thì phải đặt chúng trong dấu ngoặc đơn.
  • Nếu một vùng tham chiếu chỉ có một dòng hoặc một cột thì các tham số row_num, column_num là tùy chọn, theo thứ tự định sẵn.

row_num chỉ số dòng cần trả về tham chiếu. column_num chỉ số cột cần trả về tham chiếu.
area_num thứ tự dãy tham chiếu cần trả về tham chiếu.

Lưu ý!

Nếu row_num, colum_num là 0, INDEX trả về tham chiếu đầy đủ từ reference. Khi đó bạn cần nhập công thức dưới dạng công thức mảng. Để nhập công thức mảng: Chọn vùng ô cần cho công thức, nhấn F2, nhập công thức và nhấn tổ hợp phím CTRL + Shift + ENTER. Nếu không hàm trả về lỗi #VALUE!
row_num, colum_num phải chỉ vào thứ tự trong vùng tham chiếu. Nếu không hàm INDEX trả về lỗi #REF!

INDIRECT

Công dụng

Trả về một tham chiếu từ chuỗi ký tự. Tham chiếu được trả về ngay tức thời để hiển thị nội dung của chúng. Dùng hàm INDIRECT khi bạn muốn thay đổi tham chiếu tới một ô bên trong một công thức mà không cần thay đổi công thức đó.

Công thức

=INDIRECT(ref_text,a1)
ref_text là tham chiếu tới một ô có thể là dạng A1, dạng R1C1, tên định nghĩa của một tham chiếu hoặc một tham chiếu dạng chuỗi ký tự. Nếu ref_text không hợp lệ, INDIRECT trả về lỗi #REF!.
a1 là giá trị logic xác định dạng tham chiếu bên trong ref_text. Nếu TRUE (hoặc không nhập) là kiểu tham chiếu A1 (<Tên cột><Tên dòng>), FALSE là kiểu R1C1(R<Số thứ tự dòng>C<Số thứ tự cột>).

Lưu ý!

Nếu ref_text chứa tham chiếu đến một bảng tính khác (tham chiếu ngoại) thì bảng tính này phải được mở ra. Nếu không INDIRECT sẽ trả về lỗi #REF!.
a1 bạn có thể nhập số 1 thay cho nhập TRUE, số 0 cho nhập FALSE vì Excel có khả năng tự chuyển đổi các giá trị phù hợp với công thức.

Ví dụ

Để dễ hiểu hơn, bạn hãy nhập các công thức sau vào ô trống bất kỳ trong bảng tính.
A B C
1
Ma trận A
5 10 2
2 -5 6
5 10 4
2
3
4
=INDIRECT(“A2”). Trả về 5.

=INDIRECT(“R2C2”,0). Trả về 10.


LOOKUP

Công dụng

Trả về một giá trị một giá trị từ một dòng hoặc một cột trong dãy ô hoặc mảng giá trị. LOOKUP có 2 dạng hàm: VECTƠMẢNG.
  • Dạng vetơ tìm kiếm một giá trị trên một dòng hoặc một cột của dãy ô, nếu tìm thấy sẽ trả về giá trị cùng vị trí trên dòng hoặc cột của dãy ô thứ 2.
  • Dạng mảng tìm kiếm một giá trị trên cột hoặc dòng đầu tiên của mảng. Nếu tìm thấy sẽ trả về giá trị tại vị trí tương ứng trên dòng hoặc cột cuối cùng của mảng giá trị.

Dạng VECTƠ

Công thức

=LOOKUP(lookup_value,lookup_vetor,result_vector)

lookup_value là giá trị LOOKUP sẽ tìm kiếm trên vetơ đầu tiên. Nó có thể là một số, ký tự, một giá trị logic, một tên định nghĩa một vùng ô hoặc một tham chiếu đến một giá trị.
lookup_vetor là một dãy ô chỉ bao gồm một cột hoặc một dòng chứa giá trị cần tìm. Những giá trong dãy này có thể là ký tự, số hoặc giá trị logic.
result_vector là một dãy ô chỉ bao gồm một cột hoặc một dòng chứa giá trị trả về. Kích thước của result_vetor bắt buộc phải bằng kích thước của lookup_vetor.

Lưu ý!

Các giá trị trong lookup_vetor phải được sắp xếp tăng dần -2,-1,0,1,2,…a-z,FALSE,TRUE. Nếu không LOOKUP có thể trả về một giá trị không chính xác.
Nếu không tìm thấy giá trị cần tìm lookup_value trong vectơ lookup_vetor thì hàm sẽ lấy giá trị lớn nhất mà nhỏ hơn hoặc bằng giá trị tìm kiếm trong lookup_vetor.
Nếu giá trị tìm kiếm lookup_value nhỏ hơn giá trị nhỏ nhất trong lookup_vetor thì LOOKUP trả về lỗi #N/A!.

Ví dụ:  Để dễ hiểu hơn, bạn hãy copy dữ liệu bên trong bảng sau vào một trang bảng tính mới, nhập công thức bên dưới vào.

A B C
1
STT Mặt hàng Số lượng
1 CPU 100
2 Mainboard 95
3 HDD 200
2
3
4

=LOOKUP(2,A2:C4,B2:B4). Trả về Mainboard

Dạng mảng

Công thức

=LOOKUP(lookup_value,array)

lookup_value là giá trị cần tìm trong mảng giá trị. Nó có thể là một số, ký tự, một giá trị logic, một tên định nghĩa một vùng ô hoặc một tham chiếu đến một giá trị.

  • Nếu LOOKUP không tìm thấy lookup_value, thì nó sẽ dùng giá trị lớn nhất trong mảng mà nhỏ hơn hoặc bằng giá trị cần tìm lookup_value.
  • Nếu giá trị cần tìm lookup_value nhỏ hơn giá trị nhỏ nhất của dòng hoặc cột đầu tiên trong mảng (phụ thuộc và việc bạn khai báo mảng này trong công thức) thì LOOKUP trả về lỗi #N/A.

array  là dãy ô có thể là ký tự, số, giá trị logic mà bạn cần tìm lookup_value.

Dạng mảng của hàm LOOKUP tương tư như hàm HLOOKUPVLOOKUP. Nhưng khác biệt ở chỗ hàm HLOOKUP tìm trên dòng đầu tiên (horizotal), VLOOKUP tìm trên cột đầu tiên (Vertical) còn LOOKUP tìm trên cột hoặc trên dòng tùy thuộc vào việc khai báo mảng giá trị trong công thức.
  • Nếu một mảng có nhiều cột hơn dòng tức mảng đứng, LOOKUP sẽ tìm giá trị lookup_value trên hàng đầu tiên.
  • Nếu một mảng có nhiều dòng hơn cột tức mảng nằm ngang, LOOKUP sẽ tìm giá trị lookup_value trên cột đầu tiên.
  • Với HLOOKUP, VLOOKUP bạn có thể chỉ định cột trả về giá trị cần tìm. Nhưng đối với LOOKUP luôn trả về giá trị ở dòng hoặc cột cuối cùng.

Lưu ý!

Các giá trị trên hàng hoặc cột đầu tiên của mảng giá trị dùng để tìm kiếm phải được sắp xếp theo thứ tự tăng dần từ -2,-1,0,1,2,…a-z,FALSE,TRUE…Nếu không hàm có thể trả về một kết quả không chính xác.

Ví dụ

Để dễ hiểu hơn, bạn hãy copy dữ liệu bên trong bảng sau vào một trang bảng tính mới, nhập công thức bên dưới vào.
A B C
1
STT Mặt hàng Số lượng
1 CPU 100
2 Mainboard 95
3 HDD 200
2
3
4
=LOOKUP(“2”,A2:C4). Trả về 95

Tham khảo Hàm HLOOKUP, Hàm VLOOKUP


MATCH

Công dụng

Trả về vị trí (chỉ mục) của một giá trị từ một dãy giá trị.

Công thức

=MATCH(lookup_value, lookup_array,match_type)

lookup_value là giá trị cần tìm trong bảng giá trị.

lookup_array là một dãy ô liên tục để tìm kiếm giá trị.
match_type là một số -1, 0 hoặc 1 chỉ định kiểu tìm kiếm.

  • match_type = 1 (hoặc không nhập), MATCH sẽ dùng giá trị lớn nhất mà nhỏ hơn hoặc bằng giá trị cần tìm lookup_value. Và bắt buộc dãy giá trị lookup_array  phải đựơc sắp xếp theo thứ tự tăng dần.
  • match_type = 0, MATCH sẽ dùng giá trị lookup_value. Dãy giá trị lookup_array không cần sắp xếp.
  • match_type = -1, MATCH dùng giá trị nhỏ nhất mà lớn hơn hoặc bằng giá trị cần tìm lookup_value. Và bắt buộc dãy giá trị lookup_array phải được sắp xếp theo thứ tự giảm dần.

Lưu ý!

Nếu không tìm thấy giá trị cần tìm trong bảng giá trị, MATCH trả về lỗi #N/A.
Nếu match_type = 0, và giá trị cần tìm là ký tự, thì bạn có thể dùng dấu sao (*) để đại diện cho nhiều ký tự, dùng dấu hỏi (?) để đại diện cho ký tự tại vị trí mà bạn đặt nó.

OFFSET

Công dụng

Trả về tham chiếu đến một vùng nào đó được tính bằng một ô hoặc dãy ô bắt đầu và khoảng cách với số dòng, cột được chỉ định. Bạn có thể chỉ định số dòng, cột của vùng tham chiếu trả về.

Công thức

=OFFSET(reference,rows,cols,height,width)

reference là vùng tham chiếu mà bạn muốn làm điểm xuất phát để tạo vùng tham chiếu mới. reference phải chỉ đến một ô hoặc một dãy ô liên tục, nếu không hàm sẽ trả về lỗi #VALUE!

rows là số dòng tính từ vùng xuất phát.
cols là số cột tính từ vùng xuất phát. height là số dòng của vùng tham chiếu cần trả về. Bạn phải nhập số dương
width là số cột của vùng tham chiếu cần trả về. Bạn phải nhập số dương

Lưu ý!

Nếu các dòng và cột tham chiếu ngoài phạm vị trang bảng tính, OFFSET trả về lỗi #REF!
Nếu heightwidth không nhập, mặc định nó giống như vùng tham chiếu xuất phát reference.

Ví dụ

Để dễ hiểu hơn, bạn có thể copy dữ liệu bên trong bảng bên dưới vào một trang bảng tính mới và nhập công thức vào.
A B C
1
Doanh thu tháng 12
Tên hàng Số tiền Ghi chú
Monitor 15000000
CPU 20000000
CD-ROM 4000000
Tổng cộng
2
3
4
5
6
=SUM(OFFSET(A3:A5,0,1)). Trả về 39000000. Tính tổng cùng ô B2:B5 do hàm OFFSET trả về.

=OFFSET(A3,2,2). Trả về 0. Giá trị ô C5.


ROW

Công dụng

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ông thức

=ROW(reference)

reference: là ô hoặc vùng ô. Nếu reference không nhập thì hàm trả về số thứ tự dòng của ô đang đứng.

Lưu ý!

reference không thể bao gồm nhiều vùng tham chiếu.

Ví dụ

Để dễ hiểu hơn, bạn hãy nhập các công thức sau vào ô trống bất kỳ trong bảng tính.
=ROW(S10:T20). Trả về 10.
=ROW(Z1). Trả về 1.

ROWS

Công dụng

Trả về số dòng của vùng tham chiếu.

Công thức

=ROWS(reference)
reference: là ô hoặc vùng ô, mảng.

Ví dụ

Để dễ hiểu hơn, hãy nhập các công thức sau vào ô trống bất kỳ trong bảng tính.

=ROWS(S10:T20). Trả về 11.
=ROWS(A1:E10). Trả về 10.


TRANSPOSE

Công dụng

Chuyển một vùng dữ liệu nằm ngang thành thẳng đứng và ngược lại. Công thức STRANPOSE luôn nhập ở dạng công thức mảng.

Công thức

=TRANSPOSE(array)

array: là mảng giá trị, địa chỉ dãy ô cần hoán vị.

Lưu ý!

Hàm TRANSPOSE phải luôn nhập ở dạn công thức mảng: Bôi đen vùng ô cần đưa dữ liệu đến. Nhấn phím F2, nhập công thức và chọn vùng tham chiếu cần hoán vị, nhấn tổ hợp phím CTRL + SHIFT + ENTER.

VLOOKUP

Công dụng

Dò tìm một giá trị ở cột đầu tiên bên trái của một bảng dữ liệu. Nếu tìm thấy sẽ trả về giá trị ở cùng trên dòng với giá trị tìm thấy trên cột mà bạn chỉ định. Hàm VLOOKUP thường dùng để điền thông tin vào bảng dữ liệu từ bảng dữ liệu phụ.

VLOOKUP xuất phát từ vertical lookup : dò tìm theo phương đứng, hay theo cột.

Công thức

=VLOOKUP(lookup_value,table_array,row_index_num,range_lookup)

lookup_value: là tìm một giá trị dùng để tìm kiếm, nó có thể là một giá trị, một tham chiếu hay một chuỗi ký tự.
table_array là vùng chứa dữ liệu cần tìm. Đây là bảng dữ liệu phụ có nội dung thường cố định, bao quát để bạn lấy dữ liệu.
  • Các giá trị ở cột đầu tiên có thể là giá trị số, chuỗi ký tự, hoặc logic.
  • Nếu range_lookupTRUE thì các giá trị ở cột đầu tiên của bảng dữ liệu phụ này phải được sắp xếp tăng dần từ -2,1,0,1,2,…,A-Z,FALSE,TRUE. Nếu không hàm VLOOKUP sẽ trả giá trị không chuẩn xác.
  • Để sắp xếp các giá trị trong bảng dữ liệu từ trên xuống để hàm cho kết quả phù hợp khi bạn dùng range_lookup là TRUE: Chọn vùng dữ liệu cần sắp xếp, kích vào menu Data, Sort. Nhấn nút Options bên dưới, đánh dấu Soft top to bottom, rồi nhấn OK. Kích chọn cột cần sắp xếp trong danh sách. Chọn Ascending, và nhấn OK

row_index_num số thứ tự cột trên bảng dữ liệu phụ mà dữ liệu bạn cần lấy. Giá trị trả về nằm trên cột bạn chỉ định này và ở dòng mà hàm tìm thấy giá trị dò tìm lookup_value.

range_lookup là giá trị logic bạn chỉ định muốn VLOOKUP tìm kiếm chính xác hay là tương đối. Nếu range_lookup là TRUE hàm sẽ trả về kết quả tìm kiếm tương đối. Nếu không tìm thấy kết quả chính xác, nó sẽ trả về một giá trị lớn nhất mà nhỏ hơn giá trị tìm kiếm lookup_value. Nếu range_lookup là FALSE hàm tìm kiếm chính xác, nếu không có trả về lỗi #N/A!

Lưu ý!

Nếu lookup_value nhỏ hơn giá trị nhỏ nhất trong cột đầu tiên của bảng dữ liệu phụ, VLOOKUP trả về lỗi #N/A!.
Khi xuất hiện lỗi #N/A! bạn có thể mắc lỗi nhập dư một khoảng trống ở phía sau giá trị dò tìm hoặc trong bảng dữ liệu, kể cả chính và phụ.
Khi dùng hàm VLOOKUP để điền dữ liệu cho một bảng dữ liệu thì trong công thức cần phải tạo địa chỉ tuyệt đối cho bảng dữ liệu phụ table_array để công thức đúng cho các hàng còn lại khi bạn copy công thức xuống các ô bên dưới.

Ví dụ

Để dễ hiểu hơn, bạn hãy copy dữ liệu bên trong bảng dưới và nhập các công thức sau vào ô trống bất kỳ trong bảng tính.
A B C D E
1
DANH MỤC HÀNG HÓA NHẬP KHẨU THÁNG 12
STT Mã hàng Tên hàng Ngày nhập Số lượng
1 HDD ?
01/12/2006
20
2 CPU ?
03/12/2006
50
3 CDW ? 05/12/2006 70
4 HDD ? 07/12/2006 100
5 CPU ? 08/12/2006 200
6 USB ? 07/12/2006 500
BẢNG TÊN HÀNG
Mã hàng Tên hàng
CDR Ổ CD – Rom
CDW Ổ ghi CD-Rom
HDD Ổ đĩa cứng
USB Ổ đĩa cứng USB
CAS Thùng máy

2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

=VLOOKUP(B3,$C$11:$D$15,2,0). Nhập công thức vào ô C3 để lấy tên hàng tương ứng với mã hàng từ bảng dữ liệu phụ.
Copy công thức xuống các ô còn lại. Lưu ý địa chỉ vùng ô của bảng dữ liệu phụ phải là địa chỉ tuyệt đối để khi copy công thức hàm VLookup mới đúng cho các ô tiếp theo.


Bình luận