nexsoft.vnCác hàm VLOOKUP, INDEX, MATCH,… là các hàm khá phức tạp nhưng có công dụng hiệu quả trong việc khai thác dữ liệu trong Excel. Trong bài viết này, mình sẽ hướng dẫn chi tiết các bạn cách sử dụng hàm VLOOKUP và các hàm liên quan một cách hiệu quả.
1. Hàm VLOOKUP
Công dụng: Dùng tìm một giá trị được chỉ định trong cột đầu tiên và kéo dữ liệu phù hợp từ cùng một hàng trong một cột khác. Đây là một trong những hàm phổ biến và hỗ trợ rất tốt cho các tác vụ văn phòng phức tạp.
Công thức: =VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])
Trong đó:
+ Lookup_value: Giá trị cần tìm kiếm.
+ Table_array: Hai hoặc nhiều cột dữ liệu.
+ Col_index_num: Số cột để kéo dữ liệu.
+ Range_lookup: Xác định tìm kiếm là hoàn toàn chính xác (FALSE) hay chỉ tương đối chính xác (TRUE hoặc bỏ qua).
Ví dụ: Ta dùng hàm VLOOKUP để dò tìm Tiền thưởng của từng nhân viên. Giá trị cần tìm kiếm là Chức vụ ô C2; mảng để dò tìm giá trị là mảng F1:G3. Khi nhập công thức =VLOOKUP(C2,$F$1:$G$3,2,0) (1) kết quả là NHÂN VIÊN sẽ được 2000 và TRƯỞNG PHÒNG được 5000 (2).
Hàm VLOOKUP
2. Hàm INDEX
Công dụng: Trả về một tham chiếu đến một ô bên trong mảng dựa trên số hàng và cột mà bạn chỉ định.
Công thức: =INDEX(array, row_num, [column_num])
Trong đó: Array (bắt buộc): Một phạm vi ô hoặc một hằng số mảng.
Ví dụ: Khi cần lấy giá trị của dòng đầu tiên, cột thứ 2 trong bảng, ta dùng hàm INDEX. Array là mảng ta cần dò là B2:C5; Row_num là dòng 1; Column_num là cột 2. Khi nhập công thức INDEX (B2:C5,1,2,1) (1) kết quả trả về là “TRƯỞNG PHÒNG” (2).
Hàm INDEX
3. Hàm MATCH
Công dụng: Tìm kiếm một giá trị cụ thể trong một dãy các ô, và đưa ra vị trí tương đối của giá trị đó.
Công thức: =MATCH(lookup_value, lookup_array, [match_type])
Trong đó:
+ lookup_value (bắt buộc): Giá trị mà bạn muốn so khớp trong mảng tìm kiếm. Đổi số giá trị tra cứu có thể là một giá trị (số, văn bản hoặc giá trị logic) hoặc một tham chiếu ô đến một số, văn bản hoặc giá trị logic.
+ lookup_array (bắt buộc): Phạm vi ô được tìm kiếm.
+ match_type (tùy chọn): Số -1, 0 hoặc 1. Đối số kiểu khớp chỉ rõ cách Excel so khớp giá trị tìm kiếm với các giá trị trong mảng tìm kiếm. Giá trị mặc định cho đối số này là 1.
Ví dụ: Khi cần biết vị trí của QUẦN ở vị trí thứ mấy, ta dùng hàm MATCH. Khi nhập công thức =MATCH(FF2& “*”, “B2:B7”,0) (1) thì cho kết quả 3 (2).
Hàm MATCH
3. Hàm INDIRECT
Công dụng: Trả về một tham chiếu ô hoặc dải ô được chỉ định bởi một chuỗi văn bản. Dùng hàm INDIRECT khi bạn muốn thay đổi tham chiếu tới một ô trong một công thức mà không thay đổi chính công thức đó.
Công thức: =INDIRECT(ref_text, [a1])
Trong đó:
+ Ref_text (bắt buộc): Tham chiếu tới một ô có chứa kiểu tham chiếu A1, kiểu tham chiếu R1C1 hoặc tham chiếu tới ô dưới dạng chuỗi văn bản.
+ Nếu văn bản tham chiếu không phải là một tham chiếu ô hợp lệ, hàm INDIRECT trả về giá trị lỗi #REF!.
+ Nếu ref_text tham chiếu tới một sổ làm việc khác (tham chiếu ngoài), thì sổ làm việc đó phải đang mở. Nếu sổ làm việc nguồn không mở, thì hàm INDIRECT trả về giá trị lỗi #REF!.
Ví dụ: Khi chỉ cần tính tiền của hoa hồng và bạn muốn khi kéo thả hay di chuyển ô kết quả đến vị trí khác mà vẫn không làm thay đổi công thức tính thành tiền thì ta dùng INDIRECT. Khi nhập công thức
= INDIRECT(“B2”,TRUE)*INDIRECT(“C2”,TRUE) (1) sẽ cho kết quả là 700*100=70000 (2).
Hàm INDIRECT
4. Hàm OFFSET
Công dụng: Trả về tham chiếu tới một phạm vi cách một ô hoặc phạm vi ô một số hàng và một số cột đã xác định. Tham chiếu được trả về có thể là một ô đơn hoặc một phạm vi ô. Bạn có thể chỉ rõ số hàng và số cột cần trả về.
Công thức: =OFFSET(reference, rows, cols, [height], [width])
Trong đó:
+ Reference (bắt buộc): Vùng tham chiếu mà bạn muốn căn cứ khoảng cách tham chiếu vào đó. Vùng tham chiếu phải tham chiếu tới một ô hoặc một phạm vi các ô liền kề; nếu không hàm OFFSET trả về giá trị lỗi #VALUE!.
+ Rows (bắt buộc): Số hàng, lên hoặc xuống, mà bạn muốn ô ở góc trên bên trái tham chiếu tới. Hàng có thể là số dương (có nghĩa là ở dưới vùng tham chiếu bắt đầu) hoặc số âm (có nghĩa là ở trên vùng tham chiếu bắt đầu).
+ Cols (bắt buộc): Số cột, về bên trái hoặc phải, mà bạn muốn ô ở góc trên bên trái của kết quả tham chiếu tới. Cột có thể là số dương hoặc số âm.
+ Height (tùy chọn): Chiều cao, tính bằng số hàng, mà bạn muốn có cho tham chiếu trả về. Chiều cao phải là số dương.
+ Width (tùy chọn): Độ rộng, tính bằng số cột, mà bạn muốn có cho tham chiếu trả về. Độ rộng phải là số dương.
Ví dụ: Ta dùng OFFSET để khoanh vùng giá trị, sau đó dùng hàm SUM để tính tổng. Ô để ta tham chiếu là A1; so với A1 thì Rows của vùng tham chiếu bên dưới 1 ô nên Rows là 1; so với A1 thì Cols bên trái A1 1 ô nên Cols là 1; Height ta lấy 3 dòng; Width ta lấy 2 cột. Vậy kết quả khi cộng tất cả các giá trị trong vùng là 2025.
Hàm OFFSET
5. Hàm TRANSPOSE
Công dụng: Biến đổi một khoảng nằm ngang của các ô thành một dải đứng và ngược lại, tức là chuyển hàng thành các cột và cột thành hàng.
Công thức: =TRANSPOSE(array)
Cách thực hiện
+ Bước 1: Chọn các ô trống.
+ Bước 2: Nhập =TRANSPOSE(B2:C4).
+ Bước 3: Nhập phạm vi các ô gốc.
+ Bước 4: Cuối cùng, nhấn CTRL+SHIFT+ENTER.
Ví dụ: Khi cần chuyển cả bảng giá sản phẩm từ dọc sang ngang, ta dùng hàm TRANSPOSE với mảng giá trị là B2 đến C4. Khi nhập công thức =TRANSPOSE(B2:C4) (1) sẽ cho kết quả như hình (2).
Hàm TRANSPOSE
6. Hàm HYPERLINK
Công dụng: Tạo một siêu liên kết đến một tài liệu được lưu trữ trên mạng nội bộ hoặc Internet.
Công thức: =HYPERLINK(link_location, [friendly_name]).
Trong đó:
+ Link_location có thể tham chiếu tới một vị trí trong tài liệu, như một ô cụ thể hoặc một phạm vi đã đặt tên trong trang tính hay trong sổ làm việc Excel, hoặc tới một thẻ đánh dấu trong tài liệu Microsoft Word.
+ Đường dẫn có thể đến một tệp được lưu trữ trên ổ đĩa cứng. Đường dẫn cũng có thể là một đường dẫn quy ước đặt tên phổ dụng (UNC) trên máy chủ (trong Microsoft Excel cho Windows).
Ví dụ: Những đường link dài dòng và khó hiểu sẽ gây khó chịu cho người dùng. Vậy nên hãy dùng HYPERLINK để giao diện dễ dùng hơn. Nhập công thức =HYPERLINK(A1, “LINK GOOGLE”) (1) sẽ cho kết quả LINK GOOGLE (2).
Hàm HYPERLINK
Một số mẫu laptop hiện đang được kinh doanh tại Thế Giới Di Động:
Bài viết trên đã hướng dẫn các hàm VLOOKUP và các hàm liên quan. Mong rằng bài viết trên sẽ hữu ích với các bạn.
