Cách sử dụng hàm VLOOKUP, hàm INDEX,…trong Excel bạn nên biết

Cá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, minh 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ả.

Cá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, minh 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ả.

– 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.

Trường hợp áp dụng: Khi cần dò tìm theo chiều dọc.

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ụ của từng nhân viên là ô C2; mảng để dò tìm giá trị là mảng F1:G3, lưu ý: ta cần dùng dấu đô la cố định mảng để khi tính các giá trị kế tiếp thì mảng không bị thay đổi vị trí; cột cần dò tìm là cột Tiền thưởng ở vị trí số 2; khi chỉ cần tìm kiếm giá trị tương đối thì ta dùng số 0. 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

– 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.

Lưu ý:

Nếu mảng chỉ chứa một hàng hoặc cột, tham đối row_num hoặc column_num tương ứng là tùy chọn.

Nếu mảng có nhiều hàng và nhiều hơn một cột và chỉ các row_num hoặc column_num được dùng, chỉ mục sẽ trả về một mảng của toàn bộ hàng hoặc cột trong mảng.

Row_num (bắt buộc, trừ khi column_num có mặt) Chọn hàng trong mảng mà từ đó trả về một giá trị. Nếu row_num được bỏ qua, column_num được yêu cầu.

Column_num (tùy chọn): Chọn cột trong mảng mà từ đó trả về một giá trị. Nếu column_num được bỏ qua, row_num được yêu cầu.

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

– 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. Ví dụ, khi bạn tra cứu số điện thoại của một ai đó trong sổ điện thoại, bạn sẽ dùng tên của người đó làm giá trị tra cứu nhưng số điện thoại mới là giá trị mà bạn muốn tì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

– 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. Các tham chiếu có thể được đánh giá tức thì để 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 ô 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])​.

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, tên đã xác định dưới dạng tham chiếu, 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! .

Lưu ý: Tham chiếu ngoài không được hỗ trợ trong Excel Online.

Nếu ref_text tham chiếu tới một phạm vi ô bên ngoài giới hạn hàng 1.048.576 hoặc giới hạn cột 16.384 (XFD), hàm INDIRECT trả về lỗi #REF! .

Lưu ý: Hành vi này khác với các phiên bản Excel cũ hơn Microsoft Office Excel 2007, vốn sẽ bỏ qua giới hạn vượt quá và sẽ trả về một giá trị.

A1 (tùy chọn): Một giá trị lô-gic chỉ rõ kiểu tham chiếu nào được chứa trong văn bản tham chiếu ô.

Nếu a1 là TRUE hoặc được bỏ qua, thì văn bản tham chiếu được hiểu là tham chiếu kiểu A1

Nếu a1 là FALSE, thì văn bản tham chiếu được hiểu là tham chiếu kiểu R1C1.

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. Ref_text là ô cần tham chiếu, ở đây ta tham chiếu đến ô B2 là ô đơn giá, chọn a1 là TRUE vì sẽ tham chiếu được mọi kiểu. Sau đó nhân với ô số lượng với cách làm tương tự. 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

– 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]).

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. Dùng 5 làm đối số hàng sẽ chỉ ra rằng ô ở góc trên bên trái trong tham chiếu cách vùng tham chiếu 5 ô về phía dướ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. Dùng 5 làm đối số cột sẽ chỉ ra rằng ô ở góc trên bên trái trong tham chiếu cách vùng tham chiếu 5 cột về bên phải. Cột có thể là số dương (có nghĩa là ở bên phải vùng tham chiếu bắt đầu) hoặc số âm (có nghĩa là ở bên trái vùng tham chiếu bắt đầu).

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ụ: Khi cần tính tổng số lượng hàng hóa mà nhiều giá trị ở ngang và dọc thì ta dùng OFFSET để khoanh vùng giá trị đó lại, 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

– 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(

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

– 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]).

Link_location (bắt buộc): Đường dẫn và tên tệp đến tài liệu được mở. Link_location có thể tham chiếu tới một vị trí trong tài liệu — chẳng hạn như một ô cụ thể hoặc một phạm vi đã đặt tên trong trang tính hay trong sổ thao tá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), hoặc là đường dẫn Bộ định vị Tài nguyên Đồng nhất (URL) trên Internet hay trên mạng nội bộ.

Lưu ý Excel dành cho web hàm siêu kết nối có giá trị đối với địa chỉ web (URL). Link_location có thể là chuỗi văn bản nằm giữa dấu nháy kép hoặc là một tham chiếu đến một ô có chứa nối kết ở dạng chuỗi văn bản.

Nếu bước nhảy được chỉ định trong link_location không tồn tại hoặc không thể dẫn hướng được, sẽ xuất hiện lỗi khi bạn bấm vào ô đó.

Friendly_name (tùy chọn): Văn bản nhảy hoặc giá trị số hiển thị trong ô. Tên thân thiện được hiển thị bằng màu xanh và được gạch dưới. Nếu tên thân thiện bị bỏ qua, thì ô sẽ hiển thị vị trí nối kết là văn bản nhảy.

Friendly_name có thể là một giá trị, chuỗi văn bản, tên hoặc ô có chứa văn bản nhảy hoặc giá trị. Nếu friendly_name trả về giá trị lỗi (ví dụ #VALUE!), thì ô sẽ hiển thị lỗi thay vì văn bản nhảy.

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, bên cạnh đó còn gây khó khăn để phân biệt khi có quá nhiều đường link trong một trang tài liệu. Vậy nên hãy dùng HYPERLINK để giao diện dễ dùng hơn. Link_location là ô có link gốc, trong bảng là ô A1. Friendly_name là tên mà mình muốn đặt để dễ nhớ và dễ tìm, trong bảng mình đặt là LINK GOOGLE. Khi bấm vào ô này thì yêu cầu chuyển hướng được thực hiện và ta sẽ đến được trang Google. Khi nhập công thức =HYPERLINK(A1, “LINK GOOGLE”) (1) sẽ cho kết quả LINK GOOGLE (2).

Hàm HYPERLINK

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.

Cám ơn đã theo dõi. Hẹn gặp lại ở các bài viết sau!

Xem thêm

>>> Trọn bộ các hàm văn bản, hàm toán học trong Excel bạn nên biết.

>>>

Trọn bộ các hàm tài chính trong Excel bạn nên biết

>>> Cách in Excel 2010, 2007, 2003, 2013, 2016

Từ khóa: hàm Excel cơ bản, hàm vlookup, hàm index, hàm match, hàm hyperlink, cách sử dụng hàm excel
22 lượt xem
Chấm điểm bài viết





Chấm điểm
Cám ơn bạn đã đánh giá bài viết

Nguồn: Ciscolinksys

[related_posts_by_tax posts_per_page="4" title="Bài viết cùng chủ đề"]

Trả lời

Email của bạn sẽ không được hiển thị công khai. Các trường bắt buộc được đánh dấu *