Hàm Excel thông dụng – Nhóm hàm Tìm kiếm và tham chiếu
Hàm Excel thông dụng – Nhóm hàm Toán học và Thống kê
13 May, 2025
Hàm Excel thông dụng – Nhóm hàm logic
13 May, 2025
Last updated on 13 May, 2025
Khám phá sức mạnh của nhóm hàm tìm kiếm và tham chiếu trong Excel, những công cụ không thể thiếu giúp bạn dễ dàng truy xuất và liên kết dữ liệu giữa các bảng tính. Từ việc tra cứu thông tin chi tiết với VLOOKUP và XLOOKUP đến việc xác định vị trí chính xác của dữ liệu với MATCH và INDEX, nhóm hàm này mở ra cánh cửa xử lý dữ liệu hiệu quả, tiết kiệm thời gian và nâng cao độ chính xác trong công việc của bạn. Hãy cùng đi sâu vào cách tận dụng tối đa những hàm mạnh mẽ này để làm chủ dữ liệu Excel!
HÀM VLOOKUP:
- Thông số:
- lookup_value: Giá trị mà bạn muốn tìm kiếm trong cột đầu tiên của bảng dữ liệu. Đây có thể là một số, văn bản, ngày tháng hoặc tham chiếu đến một ô chứa giá trị tìm kiếm.
- table_array: Phạm vi ô chứa bảng dữ liệu mà bạn muốn tìm kiếm và trích xuất thông tin. Cột đầu tiên của phạm vi này sẽ được hàm VLOOKUP sử dụng để tìm kiếm lookup_value.
- col_index_num: Số thứ tự của cột trong table_array chứa giá trị mà bạn muốn hàm trả về. Ví dụ, nếu bạn muốn trả về giá trị từ cột thứ hai của bảng, bạn sẽ nhập số 2.
- [range_lookup]: Một đối số tùy chọn là một giá trị logic (TRUE hoặc FALSE) hoặc số (1 hoặc 0) chỉ định kiểu khớp bạn muốn.
- TRUE hoặc bỏ qua (hoặc 1): Tìm kiếm khớp gần đúng. Trong trường hợp này, cột đầu tiên của table_array phải được sắp xếp theo thứ tự tăng dần. Nếu không tìm thấy khớp chính xác, hàm sẽ trả về giá trị lớn nhất nhỏ hơn hoặc bằng lookup_value.
- FALSE (hoặc 0): Tìm kiếm khớp chính xác. Hàm sẽ chỉ trả về một giá trị nếu tìm thấy một giá trị hoàn toàn trùng khớp với lookup_value trong cột đầu tiên. Nếu không tìm thấy, hàm sẽ trả về lỗi #N/A.
- Ví dụ: Giả sử bạn có một bảng danh sách sản phẩm (cột A là Mã sản phẩm, cột B là Tên sản phẩm, cột C là Giá) từ ô A2 đến C100. Để tìm tên sản phẩm có mã “SP005”, bạn có thể sử dụng công thức: =VLOOKUP(“SP005”, A2:C100, 2, FALSE). Hàm này sẽ tìm kiếm “SP005” trong cột A và trả về giá trị tương ứng từ cột B (cột thứ 2) trên cùng hàng nếu tìm thấy khớp chính xác.
- Ý nghĩa: Tra cứu và trả về thông tin liên quan từ một bảng dữ liệu dựa trên một giá trị tìm kiếm ở cột đầu tiên.
- Ứng dụng: Tìm kiếm giá sản phẩm dựa trên mã sản phẩm, tra cứu tên nhân viên dựa trên mã nhân viên, lấy thông tin khách hàng dựa trên số điện thoại, v.v.
- Thông số:
HÀM INDEX:
- Thông số:
- array: Phạm vi ô chứa dữ liệu mà bạn muốn trả về một giá trị từ đó.
- row_num: Số thứ tự của hàng trong array mà bạn muốn trả về giá trị.
- [column_num]: Số thứ tự của cột trong array mà bạn muốn trả về giá trị. Đối số này là tùy chọn nếu array chỉ có một cột hoặc một hàng.
- Ví dụ: Nếu bạn có một phạm vi dữ liệu doanh số hàng tháng trong hàng thứ 5 từ cột B đến M (B5:M5), để lấy doanh số của tháng thứ 3 (tức cột D), bạn có thể sử dụng công thức: =INDEX(B5:M5, 3). Nếu bạn có một bảng dữ liệu từ A1 đến C10 và muốn lấy giá trị ở hàng thứ 2, cột thứ 3, bạn sẽ sử dụng: =INDEX(A1:C10, 2, 3).
- Ý nghĩa: Trả về giá trị của một ô tại giao điểm của một hàng và một cột cụ thể trong một phạm vi.
- Ứng dụng: Lấy dữ liệu từ một vị trí xác định trong bảng, tạo tham chiếu động đến các ô dựa trên số hàng và cột.
- Thông số:
HÀM MATCH:
- Thông số:
- lookup_value: Giá trị mà bạn muốn tìm kiếm vị trí của nó trong lookup_array.
- lookup_array: Phạm vi ô hoặc mảng mà bạn muốn tìm kiếm lookup_value. lookup_array có thể là một hàng hoặc một cột.
- [match_type]: Một đối số tùy chọn là một số -1, 0 hoặc 1 chỉ định kiểu khớp bạn muốn.
- 1 hoặc bỏ qua: Tìm kiếm giá trị lớn nhất nhỏ hơn hoặc bằng lookup_value. lookup_array phải được sắp xếp theo thứ tự tăng dần.
- 0: Tìm kiếm giá trị đầu tiên hoàn toàn bằng lookup_value. lookup_array có thể không cần được sắp xếp.
- -1: Tìm kiếm giá trị nhỏ nhất lớn hơn hoặc bằng lookup_value. lookup_array phải được sắp xếp theo thứ tự giảm dần.
- Ví dụ: Để tìm vị trí của giá trị “SP005” trong cột mã sản phẩm từ A2 đến A100, bạn có thể sử dụng công thức: =MATCH(“SP005”, A2:A100, 0). Nếu “SP005” nằm ở hàng thứ 7 trong phạm vi A2:A100, hàm sẽ trả về giá trị 6 (vị trí tương đối trong mảng).
- Ý nghĩa: Tìm vị trí tương đối của một mục trong một hàng, cột hoặc mảng.
- Ứng dụng: Xác định vị trí của một mục trong danh sách, thường được sử dụng kết hợp với hàm INDEX để tạo ra các công thức tra cứu linh hoạt hơn VLOOKUP.
- Thông số:
HÀM INDEX và MATCH (kết hợp):
- Công thức ví dụ: =INDEX(C2:C100, MATCH(“SP005”, A2:A100, 0))
- Giải thích: Trong công thức này, hàm MATCH(“SP005”, A2:A100, 0) sẽ tìm vị trí hàng của “SP005” trong cột A (từ A2 đến A100) và trả về số thứ tự hàng tương ứng. Số này sau đó được sử dụng làm đối số row_num cho hàm INDEX trên cột C (từ C2 đến C100), cho phép hàm INDEX trả về giá trị ở cột C tại hàng mà “SP005” được tìm thấy.
- Ý nghĩa: Kết hợp sức mạnh của cả hai hàm để thực hiện tra cứu dữ liệu một cách linh hoạt hơn VLOOKUP. Bạn có thể tìm kiếm theo một cột bất kỳ và trả về giá trị từ một cột khác bất kỳ trong bảng, không bị giới hạn cột tìm kiếm phải ở bên trái cột trả về.
- Ứng dụng: Tra cứu dữ liệu khi cấu trúc bảng không cố định, khi bạn muốn tìm kiếm dựa trên nhiều tiêu chí phức tạp hơn (kết hợp với các hàm khác), hoặc khi cột tìm kiếm không phải là cột đầu tiên của bảng.
HÀM XLOOKUP:
- Thông số:
- lookup_value: Giá trị bạn muốn tìm kiếm.
- lookup_array: Phạm vi hoặc mảng nơi bạn muốn tìm kiếm lookup_value.
- return_array: Phạm vi hoặc mảng chứa giá trị bạn muốn trả về.
- [if_not_found]: Một giá trị tùy chọn để trả về nếu không tìm thấy khớp.
- [match_mode]: Một đối số tùy chọn chỉ định kiểu khớp:
- 0 (mặc định): Khớp chính xác.
- -1: Khớp chính xác hoặc mục nhỏ hơn tiếp theo.
- 1: Khớp chính xác hoặc mục lớn hơn tiếp theo.
- 2: Khớp ký tự đại diện (*, ?).
- [search_mode]: Một đối số tùy chọn chỉ định hướng tìm kiếm:
- 1 (mặc định): Tìm kiếm từ mục đầu tiên.
- -1: Tìm kiếm từ mục cuối cùng.
- 2: Tìm kiếm nhị phân (yêu cầu lookup_array được sắp xếp tăng dần).
- -2: Tìm kiếm nhị phân (yêu cầu lookup_array được sắp xếp giảm dần).
- Ví dụ: Để tìm tên sản phẩm có mã “SP005” trong cột A (A2:A100) và trả về tên từ cột B (B2:B100), bạn có thể sử dụng: =XLOOKUP(“SP005”, A2:A100, B2:B100). Nếu không tìm thấy “SP005”, bạn có thể trả về “Không tìm thấy” bằng cách thêm đối số if_not_found: =XLOOKUP(“SP005”, A2:A100, B2:B100, “Không tìm thấy”).
- Ý nghĩa: Một hàm tìm kiếm hiện đại và linh hoạt, kết hợp những ưu điểm của VLOOKUP và HLOOKUP, đồng thời cung cấp nhiều tùy chọn mạnh mẽ hơn cho việc tìm kiếm và trả về dữ liệu.
- Ứng dụng: Tra cứu dữ liệu phức tạp, tìm kiếm theo cả hàng và cột (mặc dù ví dụ trên chỉ theo cột), xử lý các trường hợp không tìm thấy một cách dễ dàng, thực hiện các kiểu khớp linh hoạt hơn.
- Thông số:
Nhóm hàm tìm kiếm và tham chiếu là nền tảng vững chắc cho việc phân tích và quản lý dữ liệu phức tạp trong Excel. Bằng cách nắm vững các hàm như VLOOKUP, INDEX, MATCH và đặc biệt là XLOOKUP, bạn có khả năng linh hoạt trích xuất thông tin cần thiết từ các bảng dữ liệu lớn một cách nhanh chóng và chính xác. Việc làm chủ nhóm hàm tìm kiếm và tham chiếu này không chỉ giúp bạn tiết kiệm thời gian mà còn mở ra nhiều khả năng tự động hóa và tối ưu hóa quy trình làm việc với Excel. Hãy thực hành và ứng dụng chúng vào công việc hàng ngày để cảm nhận sự khác biệt!
Tham khảo:
