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

Buzz

Nội dung bài viết

Các hàm VLOOKUP, INDEX, MATCH,... là những công cụ phức tạp nhưng rất hữu ích trong việc xử lý dữ liệu trong Excel. Trong bài viết này, tôi sẽ hướng dẫn chi tiết cách sử dụng hàm VLOOKUP và các hàm tương tự một cách hiệu quả.

1. Hàm VLOOKUP

2. Hàm INDEX

3. Hàm MATCH

3. Hàm INDIRECT

4. Hàm OFFSET

5. Hàm TRANSPOSE

6. Hàm HYPERLINK

Xem thêm

Đọc tóm tắt

- Các hàm VLOOKUP, INDEX, MATCH,... là công cụ phức tạp nhưng hữu ích trong Excel.
- Hướng dẫn cách sử dụng hàm VLOOKUP hiệu quả.
- Hàm VLOOKUP: Tìm kiếm giá trị và trích xuất dữ liệu từ cột khác.
- Hàm INDEX: Trả về tham chiếu đến ô trong mảng dựa trên số hàng và cột.
- Hàm MATCH: Tìm vị trí của giá trị trong dãy ô.
- Hàm INDIRECT: Trả về tham chiếu ô hoặc dải ô từ chuỗi văn bản.
- Hàm OFFSET: Trả về tham chiếu đến phạm vi cách một ô đã xác định.
- Hàm TRANSPOSE: Biến đổi dải ô từ hàng sang cột và ngược lại.
- Hàm HYPERLINK: Tạo liên kết siêu văn bản đến tài liệu.

Các hàm VLOOKUP, INDEX, MATCH,... là những công cụ phức tạp nhưng rất hữu ích trong việc xử lý dữ liệu trong Excel. Trong bài viết này, tôi sẽ hướng dẫn chi tiết cách sử dụng hàm VLOOKUP và các hàm tương tự một cách hiệu quả.

1. Hàm VLOOKUP

Chức năng: Dùng để tìm kiếm một giá trị đã được chỉ định trong cột đầu tiên và trích xuất dữ liệu tương ứng 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ợ tốt cho các nhiệm vụ văn phòng phức tạp.

Công thức: =VLOOKUP (giá_trị_tìm_kiếm, mảng_bảng, số_thứ_tự_cột, [tìm_kiếm_phạm_vi])

Trong đó:

+ Giá_trị_tìm_kiếm: Giá trị cần tìm kiếm.

+ Mảng_bảng: Hai hoặc nhiều cột dữ liệu.

+ Số_thứ_tự_cột: Số cột để trích xuất dữ liệu.

+ Tìm_kiếm_phạm_vi: 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ụ: Sử dụng hàm VLOOKUP để 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 để tìm kiế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

Chức năng: Trả về một tham chiếu đến một ô trong mảng dựa trên số hàng và cột bạn chỉ định.

Công thức: =INDEX(mảng, số_dòng, [số_cột])

Trong đó: Array (bắt buộc): Một phạm vi ô hoặc một dãy 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 sử dụng hàm INDEX. Array là mảng cần tìm kiếm 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à trả về vị trí tương đối của giá trị đó.

Công thức: =MATCH(giá_trị_tìm_kiếm, mảng_tìm_kiếm, [kiểu_kết_quả])

Trong đó:

+ giá_trị_tra_cứu (bắt buộc): Giá trị mà bạn muốn so khớp trong phạm vi 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.

+ mảng_tìm_kiếm (bắt buộc): Phạm vi ô được tìm kiếm.

+ kiểu_kết_quả (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 phạm vi 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 sử dụng hàm MATCH. Khi nhập công thức =MATCH(FF2& “*”, “B2:B7”,0) (1) thì kết quả là 3 (2).

Hàm MATCH

3. Hàm INDIRECT

Chức nă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. Sử dụng hàm INDIRECT khi bạn muốn thay đổi tham chiếu đến một ô trong một công thức mà không làm thay đổi chính công thức đó.

Công thức: =INDIRECT(ref_text, [a1])

Trong đó:

+ Tham_chiếu_ô (bắt buộc): Tham chiếu đến một ô chứa kiểu tham chiếu A1, kiểu tham chiếu R1C1 hoặc tham chiếu đến ô 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 đến một số làm việc khác (tham chiếu bên 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 hoa hồng và bạn muốn khi kéo thả hoặc di chuyển ô kết quả đến vị trí khác mà không làm thay đổi công thức tính thành tiền, thì sử dụng hàm INDIRECT. Khi nhập công thức

= INDIRECT(“B2”,TRUE)*INDIRECT(“C2”,TRUE) (1) sẽ đưa ra kết quả là 700*100=70000 (2).

Hàm INDIRECT

4. Hàm OFFSET

Chức năng: Trả về tham chiếu đến một phạm vi cách một ô hoặc phạm vi của một số hàng và một số cột đã xác định. Tham chiếu 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 đó:

+ Tham_chiếu (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 đến một ô hoặc một phạm vi các ô liền kề; nếu không, hàm OFFSET sẽ trả về giá trị lỗi #VALUE!.

+ Hàng (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 đến. Hàng có thể là số dương (nghĩa là ở dưới vùng tham chiếu bắt đầu) hoặc số âm (nghĩa là ở trên vùng tham chiếu bắt đầu).

+ Cột (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 đến. Cột có thể là số dương hoặc số âm.

+ Chiều cao (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.

+ Độ rộng (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 sử dụng OFFSET để xác định vùng giá trị, sau đó sử dụng hàm SUM để tính tổng. Ô mà ta tham chiếu là A1; so với A1 thì Rows của vùng tham chiếu phía dưới 1 ô nên Rows là 1; so với A1 thì Cols phía 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

Chức năng: Biến đổi một dải ô nằm ngang thành một dải ô đứng và ngược lại, tức là chuyển hàng thành cột và cột thành hàng.

Công thức: =TRANSPOSE(array)

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 muốn chuyển toàn bộ bảng giá sản phẩm từ dọc sang ngang, ta sử dụng hàm TRANSPOSE với mảng giá trị từ B2 đến C4. Khi nhập công thức =TRANSPOSE(B2:C4) (1) sẽ đưa ra kết quả như trong hình (2).

Hàm TRANSPOSE

6. Hàm HYPERLINK

Chức năng: Tạo một liên kết siêu văn bản đế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ể chỉ đến một vị trí trong tài liệu, như một ô cụ thể hoặc một phạm vi đã được đặt tên trong bảng tính hoặc trong sổ làm việc Excel, hoặc đến một thẻ đánh dấu trong tài liệu Microsoft Word.

+ Đường link có thể chỉ đến một tệp được lưu trữ trên ổ đĩa cứng. Đường link cũng có thể là một đường dẫn quy ước đặt tên phổ biến (UNC) trên máy chủ (trong Microsoft Excel cho Windows).

Ví dụ: Các đường liên kết dài và phức tạp có thể làm khó chịu cho người dùng. Vì vậy, hãy sử dụng HYPERLINK để tạo ra giao diện dễ sử 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

Bài viết đã hướng dẫn về các hàm VLOOKUP và các hàm liên quan. Hy vọng rằng bài viết sẽ hữu ích với các bạn.

Nội dung được phát triển bởi đội ngũ Mytour với mục đích chăm sóc khách hàng và chỉ dành cho khích lệ tinh thần trải nghiệm du lịch, chúng tôi không chịu trách nhiệm và không đưa ra lời khuyên cho mục đích khác.

Nếu bạn thấy bài viết này không phù hợp hoặc sai sót xin vui lòng liên hệ với chúng tôi qua email [email protected]

Các câu hỏi thường gặp

Hàm VLOOKUP trong Excel có thể làm gì và cách sử dụng nó?

Hàm VLOOKUP giúp tìm kiếm giá trị trong cột đầu tiên và trả về dữ liệu từ cột khác. Công thức =VLOOKUP(giá_trị_tìm_kiếm, mảng_bảng, số_thứ_tự_cột, [tìm_kiếm_phạm_vi]).

Làm thế nào để sử dụng hàm INDEX để trả về giá trị từ bảng tính?

Hàm INDEX trả về giá trị dựa trên chỉ số hàng và cột bạn chỉ định. Ví dụ: =INDEX(B2:C5, 1, 2) để lấy giá trị ở dòng 1, cột 2.

Khi nào nên sử dụng hàm MATCH trong Excel và cách áp dụng?

Hàm MATCH tìm kiếm một giá trị trong phạm vi và trả về vị trí của nó. Công thức =MATCH(giá_trị_tìm_kiếm, mảng_tìm_kiếm, [kiểu_kết_quả]) giúp xác định vị trí trong dãy ô.

Hàm INDIRECT trong Excel có tác dụng gì và làm thế nào để sử dụng?

Hàm INDIRECT cho phép thay đổi tham chiếu ô trong công thức mà không làm thay đổi công thức. Công thức =INDIRECT(‘B2’, TRUE) giúp tham chiếu ô một cách linh hoạt.

Hàm OFFSET trong Excel hoạt động ra sao và cách áp dụng đúng?

Hàm OFFSET trả về tham chiếu đến một phạm vi dựa trên số hàng và cột. Ví dụ: =OFFSET(A1, 1, 1) sẽ di chuyển tham chiếu một ô xuống và sang phải.

Làm sao để chuyển đổi dữ liệu trong Excel từ hàng thành cột với hàm TRANSPOSE?

Hàm TRANSPOSE chuyển đổi hàng thành cột hoặc ngược lại. Sử dụng =TRANSPOSE(B2:C4) để thay đổi cách bố trí dữ liệu từ dọc sang ngang.

Cách tạo liên kết siêu văn bản trong Excel bằng hàm HYPERLINK?

Hàm HYPERLINK tạo liên kết đến tài liệu hoặc trang web. Ví dụ: =HYPERLINK(‘A1’, ‘Google’) tạo liên kết siêu văn bản đến địa chỉ trong ô A1 với tên hiển thị là 'Google'.