Hàm tham chiếu và tìm kiếm phần 2

Chia sẻ: Vu Tien DUNG | Ngày: | Loại File: DOC | Số trang:11

Thêm vào BST

Báo xấu

114
lượt xem 19
download

Download Vui lòng tải xuống để xem tài liệu đầy đủ

Hàm ADDRESS Chức năng: Hàm ADDRESS trả về địa chỉ của một ô nào đó dưới dạng text dựa trên chỉ số dòng và cột.

Chủ đề:

Bình luận(0) Đăng nhập để gửi bình luận!

Lưu

Nội dung Text: Hàm tham chiếu và tìm kiếm phần 2

Hàm tham chiếu và tìm kiếm (Lookup functions) phần 2(hết) Hàm ADDRESS Chức năng: Hàm ADDRESS trả về địa chỉ của một ô nào đó dưới dạng text dựa trên chỉ số dòng và cột. Cú pháp hàm: ADDRESS(row_num,col_num,abs_num,a1,sheet_text) Row_num: là số thứ tự của dòng trong ô tham chiếu cần trả về. Col_num: là số thứ tự của cột trong ô tham chiếu cần trả về. Abs_num: là tùy chọn để xác định kiểu địa chỉ sẽ trả về. Abs_num có giá trị từ 1 đến 4: • Abs_num = 1 hoặc 5 hoặc để trống: Kiểu địa chỉ trả về là tuyệt đối ($A$1). • Abs_num = 2 hoặc 6: Kiểu địa chỉ trả về là dòng tuyệt đối và cột tương đối (A$1). • Abs_num = 3 hoặc 7: Kiểu địa chỉ trả về là dòng tương đối và cột tuyệt đối ($A1). • Abs_num = 4 hoặc 8: Kiểu địa chỉ trả về là tương đối (A1). A1: là tùy chọn để xác định dạng địa chỉ sẽ trả về. A1 có 2 giá trị là True or Fale: • True hoặc để trống: dạng địa chỉ trả về là A1 • Fale: dạng địa chỉ trả về là R1C1 Sheet_text: là tên sheet có trong địa chỉ tham chiếu cần trả về, mặc định là không có. Ví dụ: Hàm AREAS Chức năng: Hàm AREAS trả về số vùng tham chiếu trong một tham chiếu Cú pháp hàm: AREAS(reference) Reference: là tham chiếu, Reference có thể chứa nhiều vùng tham chiếu, những vùng tham chiếu này có thể là một ô rời rạc hay nhiều ô kế tiếp nhau và được phân cách nhau bằng dấu phẩy. Ví dụ: Hàm CHOOSE Chức năng: Hàm CHOOSE trả về một giá trị nào đó trong list danh sách các giá trị cho trước Cú pháp hàm: CHOOSE(index_num,value1,value2,…,value29) Index_num: là số chỉ vị trí của giá trị cần trả về trong list danh sách, index_num là số nguyên và có giá trị từ 1 đến 29.
index_num có thể là một số, một tham chiếu đến ô chứa giá trị số hay một công thức trả về giá trị • số. • giá trị của index_num phải
Nếu mục và chỉ mục để trống, hàm sẽ trả về giá trị tổng cộng của tất cả các mục có trong bảng • dữ liệu báo cáo. Giá trị trả về phải thỏa mãn tất cả các mục và chỉ mục có trong công thức. Nếu không hàm sẽ trả • về #REF. Ví dụ: Ta có bảng báo cáo dữ liệu như sau: Hàm HLOOKUP Chức năng: Hàm HLOOKUP là hàm dò tìm theo dòng, sẽ trả về giá trị của một ô nằm trên một dòng nào đó nếu thỏa mãn điều kiện dò tìm. Cú pháp hàm: HLOOKUP(lookup_value,table_array,row_index_num,option_lookup) Lookup_value: là giá trị dùng để dò tìm, giá trị này sẽ được dò tìm trong dòng đầu tiên của bảng dữ liệu dò tìm. Giá trị dò tìm có thể là một số, một chuỗi, một công thức trả về giá trị hay một tham chiếu đến một ô nào đó dùng làm giá trị dò tìm. Table_array: là bảng dùng để dò tìm, bảng dò tìm có thể là tham chiếu đến một vùng nào đó hay Name trả về vùng dò tìm. Bảng dò tìm gồm có Rj hàng và Ci cột (I,j >=1), trong đó dòng thứ nhất của bảng dò tìm sẽ được dùng để dò tìm. Row_index_num: là số thứ tự của dòng (tính từ trên xuống dưới) trong bảng dò tìm chứa giá trị mà ta muốn trả về. Row_index_num phải >=1 và
Hàm HYPERLINK Chức năng: Hàm HYPERLINK dùng để tạo một siêu liên kết đến một ô trong một sheet nào đó, hay tạo link để mở một ứng dụng. Cú pháp hàm: HYPERLINK(link_location,friendly_name) Link_location: là một chuỗi text miêu tả đường dẫn đến ô trong sheet nào đó, hay đến một ứng dụng nào đó. Friendly_name: là nội dung được hiện thị trong ô chứa hàm Hyperlink, friendly_name có thể là một chuỗi text hay một tham chiếu. Nếu Friendly_name để trống thì nội dung của link_location sẽ hiện thị. Ví dụ: =HYPERLINK("[Date time lookup.xls]lookup!A1"," Go to A1") excel sẽ đi đến ô A1 trong sheet Lookup =HYPERLINK("D:\DATA\excel.xls","click to open excel.xls") excel s ẽ mở file excel.xls trong ổ đĩa D =HYPERLINK("[Date time lookup.xls]lookup!A"&MAX(1,2,3)," Go to A3") excel s ẽ đi đến ô A3 trong sheet Lookup =HYPERLINK("http://www.giaiphapexcel.com") excel sẽ mở trang Giaiphapexcel.com khi click vào đường dẫn 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. 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) 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à số chỉ 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 column_num tương ứng là tùy chọn. • Nếu mảng có nhiều hơn một dòng hoặc nhiều hơn một cột, và chỉ có hoặc là row_num hoặc là column_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. • Nếu row_num hoặc col_num bằng 0, hàm sẽ trả về mảng các giá trị là toàn bộ cột hoặc toàn bộ dòng tương ứng.
• Row_num và col_num phải chỉ tới một ô trong mảng, nếu không, INDEX() sẽ báo lỗi #REF! Ví dụ: b. Dạng Tham chiếu: INDEX(reference,row_num,col_num,area_num) Reference: là một tham chiếu đến một hoặc nhiều dãy ô liên tiếp. Row_num: là số chỉ dòng trong vùng tham chiếu được trả về. Col_num: là số chỉ cột trong vùng tham chiếu được trả về. Area_num: là số chỉ 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ề. Ví dụ: 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 phải thay đổi công thức đó.
Cú pháp: INDIRECT(ref_text,a1) 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 là 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 dạng 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. Ví dụ: Hàm LOOKUP Chức năng: Hàm LOOKUP dùng để dò tìm môt giá trị trong một dòng, một cột hoặc trong một mảng các giá trị. Hàm LOOKUP có hai hình thức là dạng vector và dạng mảng: a. Dạng Vector: LOOKUP(lookup_value,lookup_vector,result_vector) 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ề (1cột hoặc 1dòng). Độ lớn của Result_vector phải tương ứng với Lookup_vector. 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. Ví dụ:
b. Dạng Mảng: LOOKUP(lookup_value,array) Lookup_value: là giá trị sẽ được tìm kiếm trong mảng Array. Lookup_value có thể là một số, một chuỗi hay một tham chiếu. Array: là vùng tìm kiếm, có thể là một vùng nhiều ô hay một mảng. Giá trị chứa trong array có thể là số, chuỗi, giá trị logic.. Lưu ý: • 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 Lookup_value nhỏ hơn giá trị nhỏ nhất trong cột hoặc hàng đầu tiên trong array thì hàm sẽ báo lỗi #NA!. • Hàm lookup dạng mảng gần giống với Hàm Hlookup và Vlookup. Điểm khác biệt là hàm Vlookup hay hàm Hlookup tìm kiếm trên dòng (hoặc cột) đầu tiên, còn hàm Lookup thì tim kiếm tùy thuộc vào kích thước của mảng. Nếu mảng (array) có số cột nhiều hơn số dòng thì hàm sẽ tìm trên dòng đầu tiên của mảng, và ngược lại. Nếu mảng có số cột bằng số dòng thì hàm sẽ tìm trên cột đầu tiên của mảng. • Hàm lookup luôn trả về giá trị ở cột hoặc dòng cuối cùng trong mảng. • Các giá trị ở dòng hoặc cột đầu tiên trong mảng phải được sắp xếp theo thứ tự tăng dần, nếu không kết quả trả về nhiều khi không chính xác. Ví dụ: Hàm MATCH
Chức năng: Hàm Match là hàm dò tìm, sẽ trả về vị trí của giá trị dò tìm trong vùng dò tìm (một dòng hoặc một cột). Cú pháp: MATCH(lookup_value,lookup_array,match_type) 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ể là 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. • match_type = 1(hoặc để trống): Hàm sẽ dò tìm giá trị lớn 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..). Lưu ý: Nếu match_type = 0, lookup_value là một chuỗi ký tự thì bạn có thể dùng ký tự * hoặc ? Để đại diện cho một vài ký tự trong chuỗi dò tìm lookup_value ( * đại diện cho một vài ký tự, ? Đại diện cho 1 ký tự). Ví dụ: Hàm OFFSET Chức năng: Hàm OFFSET sẽ trả về một tham chiếu đến một vùng nào đó, vị trí bắt đầu của tham chiếu từ một ô hay một vùng nào đó cho trước với độ lớn của vùng được chỉ định. Cú pháp: OFFSET(reference,rows,cols,height,width) Reference: là vùng hay ô được chọn làm điểm xuất phát (điểm mốc) của vùng tham chiếu sẽ trả về trong công thức. Rows: là số chỉ số dòng lệch lên hay lệch xuống của vùng tham chiếu trả về so với vùng được chọn làm mốc. Rows có giá trị âm "" thì vùng trả về sẽ lệch lên trên so với vùng chọn làm mốc và ngược lại. Cols: là số chỉ số cột lệch qua trái hay qua phải của vùng tham chiếu trả về so với vùng được chọn làm mốc. Cols có giá trị âm "" thì vùng trả về sẽ lệch sang trái so với vùng chọn làm mốc và ngược lại. Height: là số chỉ số dòng (độ cao) có trong vùng tham chiếu sẽ trả về trong công thức. Width: là số chỉ số cột (độ rộng) có trong vùng tham chiếu sẽ trả về trong công thức.
Lưu ý: Nếu trong công thức không nhập giá trị của height và width thì xem như nó bằng với độ lớn của Reference. Ví dụ: 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) 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. Ví dụ: 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) 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. Ví dụ: Hàm TRANSPOSE Chức năng: Hàm TRANSPOSE chuyển đổi vùng dữ liệu từ cột thành hàng và hàng thành cột. Cú pháp hàm: TRANSPOSE(array) Array: là vùng dữ liệu cần chuyển đổi, có thể là một dòng, một cột hay một vùng gồm nhiều dòng nhiều cột. Cách thao tác: • Chọn vùng (bôi đen) muốn đặt kết quả của sự chuyển đổi. Vùng này phải có độ lớn tương ứng với vùng Array. • Sau khi chọn vùng cần đặt kết quả trả về, nhập công thức vào thanh formula bar: Transpose(array)
Do Transpose là hàm mảng nên sau khi nhập xong công thức, nhấn kết hợp 3 phím: • Ctrl+Shift+Enter Lưu ý: nếu array có kích thước nhỏ hơn vùng đặt kết quả trả về thì tại những vị trí dư ra trong vùng trả về sẽ trả về #N/A. Ví dụ: Hàm VLOOKUP Chức năng: Hàm VLOOKUP là hàm dò tìm theo cột, sẽ trả về giá trị của một ô nằm trên một cột nào đó nếu thỏa mãn điều kiện dò tìm. Cú pháp hàm: VLOOKUP(lookup_value,table_array,col_index_num,option_lookup) Lookup_value: là giá trị dùng để dò tìm, giá trị này sẽ được dò tìm trong cột đầu tiên của bảng dữ liệu dò tìm. Giá trị dò tìm có thể là một số, một chuỗi, một công thức trả về giá trị hay một tham chiếu đến một ô nào đó dùng làm giá trị dò tìm. Table_array: là bảng dùng để dò tìm, bảng dò tìm có thể là tham chiếu đến một vùng nào đó hay Name trả về vùng dò tìm. Bảng dò tìm gồm có Rj hàng và Ci cột (I,j >=1), trong đó cột thứ nhất của bảng dò tìm sẽ được dùng để dò tìm. Col_index_num: là số thứ tự của cột (tính từ trái qua phải) trong bảng dò tìm chứa giá trị mà ta muốn trả về. Col_index_num phải >=1 và
True hoặc 1 hoặc để trống: là kiểu dò tìm tương đối, hàm sẽ lấy giá trị đầu tiên mà nó tìm được • trên cột đầu tiên trong bảng dò tìm. Trong trường hợp tìm không thấy, nó sẽ lấy giá trị lớn nhất mà có giá trị nhỏ hơn giá trị dò tìm. False hoặc 0: là kiểu dò tìm chính xác, hàm sẽ lấy giá trị đầu tiên mà nó tìm được trên cột đầu • tiên trong bảng dò tìm. Trong trường hợp tìm không thấy, hàm sẽ trả về #N/A. Ví dụ: