Hà Vân
Hà Vân
Thảo luận 0 thảo luận
Vỗ tay 0 vỗ tay
Lượt xem 84 lượt xem

Cách kết hợp hàm OFFSET và hàm MATCH để tìm kiếm nhiều điều kiện

Apr 28 2022

Có thể nhiều bạn đã biết cách sử dụng hàm OFFSET. Nhưng các bạn đã biết kết hợp hàm OFFSET với hàm MATCH để tìm kiếm theo nhiều điều kiện cùng lúc chưa? Hãy cùng chúng mình tìm hiểu trong bài tập Excel dưới đây nhé.

Lý thuyết về hàm OFFSET và hàm MATCH

Hàm OFFSET

Hàm OFFSET trong Excel được sử dụng khi chúng ta cần xem hoặc tính toán dữ liệu của một ô hoặc một dãy các ô trong bảng tính dựa vào một vùng tham chiếu có sẵn.

Cú pháp của hàm OFFSET có dạng như sau: 

=OFFSET(reference, rows, cols, height, width)

Trong đó:

  • Reference: là vùng tham chiếu làm cơ sở cho hàm (làm điểm xuất phát) để tạo vùng tham chiếu mới.
  • Rows: là số dòng bên trên hoặc bên dưới reference, tính từ ô đầu tiên (ô ở góc trên bên trái) của reference. 
  • Cols: là số cột bên trái hoặc bên phải reference, tính từ ô đầu tiên (ô ở góc trên bên trái) của reference.
  • Height: là số dòng của vùng tham chiếu cần trả về. Height phải là số dương.
  • Width: là số cột của vùng tham chiếu cần trả về. Width phải là số dương.

Hàm MATCH

Hàm MATCH trong Excel là một hàm tìm kiếm có tác dụng tìm một mục được chỉ định trong phạm vi của ô, sau đó trả về vị trí tương đối của mục đó trong phạm vi này.

Cú pháp của hàm này có dạng như sau:

Trong đó bao gồm các thành phần là:

  • lookup_value: Giá trị cần tìm (tìm kiếm theo giá trị nào?). Giá trị này có thể là số, text, logic hoặc tham chiếu ô.
  • lookup_array: Vùng tìm kiếm (tìm kiếm ở đâu?). Bạn có thể dò tìm trên 1 mảng (array) hoặc 1 vùng ô (range).
  • match_type: Phương pháp tìm kiếm (tham số không bắt buộc). Có 3 phương pháp tìm kiếm:
  • Không nhấp tham số hoặc nhập "1": Tìm kiếm tương đối theo giá trị nhỏ hơn gần nhất. 
  • Nhập "-1": Tìm kiếm tương đối theo giá trị lớn hơn gần nhất. Chú ý: vùng tìm kiếm cần được sắp xếp theo thứ tự giảm dần, nghĩa là, từ giá trị lớn nhất đến giá trị nhỏ nhất hoặc từ Z tới A.
  • Nhập số 0: Tìm chính xác theo giá trị lookup_value mà nó tìm được.

Vậy khi chúng ta kết hợp hàm MATCH và hàm OFFSET sẽ mang lại kết quả như thế nào? Hãy cùng thử sức với bài tập ở ngay dưới đây để biết nhé.

Xem thêm: Hướng dẫn cách dùng hàm OFFSET để tạo danh sách động trong Excel

Bài tập kết hợp hàm OFFSET và hàm MATCH

Đề bài

Cho 2 bảng dữ liệu như sau:

Cách kết hợp hàm OFFSET và hàm MATCH để dò tìm nhiều điều kiện

Yêu cầu: Xác định đơn giá cho từng mặt hàng được chọn trong bảng báo giá dựa theo mã sản phẩm ở cột B và size sản phẩm ở cột C. Trong đó mã sản phẩm và size sản phẩm được xác định dựa vào bảng danh mục hàng hóa.

Cách thực hiện

Bước 1: Nhập công thức hàm OFFSET vào ô D6 (Cột đơn giá) trong bảng báo giá.

Cách kết hợp hàm OFFSET và hàm MATCH để dò tìm nhiều điều kiện

Bước 2: Chuyển sang bảng danh mục hàng hóa, chọn ô A3 làm gốc tọa độ. Chúng ta nên chọn vị trí đầu tiên trong bảng làm gốc là tốt nhất. Các bạn nhớ cố định tọa độ của ô A3 để khi đến bước sau có thể copy công thức xuống dòng khác nhé.

Cách kết hợp hàm OFFSET và hàm MATCH để dò tìm nhiều điều kiện
Cách kết hợp hàm OFFSET và hàm MATCH để dò tìm nhiều điều kiện

Bước 3: Lồng hàm MATCH vào công thức để tìm số dòng sẽ được di chuyển (rows trong hàm OFFSET), lấy lookup_value là ô B6 của bảng báo giá. Sau đó các bạn tiếp tục chuyển sang bảng danh mục hàng hóa và nhập tham chiếu mã sản phẩm là từ ô A4:A19.

Cách kết hợp hàm OFFSET và hàm MATCH để dò tìm nhiều điều kiện

Đừng quên cố định tọa độ cho vùng tham chiếu A4:A19 nhé:

Cách kết hợp hàm OFFSET và hàm MATCH để dò tìm nhiều điều kiện

Bởi vì mã sản phẩm của chúng ta có định dạng là Text nên tham số match_type chúng ta sẽ nhập là số 0 để tìm kiếm chính xác. Sau đó các bạn đóng hàm MATCH lại.

Bước 4: Lồng hàm MATCH vào công thức để tìm số cột sẽ được di chuyển (cols trong hàm OFFSET). Chúng ta sẽ lấy lookup_value cho hàm MATCH là ô C6 trong bảng báo giá. Sau đó các bạn chuyển sang bảng danh mục hàng hóa, chọn vùng tham chiếu là B3:E3.

Cách kết hợp hàm OFFSET và hàm MATCH để dò tìm nhiều điều kiện

Đừng quên cố định tọa độ cho vùng tham chiếu và nhập match_type là 0 để tìm kiếm chính xác nhé.

Cách kết hợp hàm OFFSET và hàm MATCH để dò tìm nhiều điều kiện

Bước 5: Thêm dấu ngoặc để đóng công thức hàm lại và bấm Enter để thu được kết quả.

Cách kết hợp hàm OFFSET và hàm MATCH để dò tìm nhiều điều kiện
Cách kết hợp hàm OFFSET và hàm MATCH để dò tìm nhiều điều kiện

Các bạn đối chiếu kết quả chúng ta tính được ở bảng báo giá với bảng danh mục hàng hóa ban đầu sẽ thấy đây là kết quả chính xác. Sau đó các bạn sao chép công thức xuống các dòng dưới là được nhé.

Bước 6: Đây là một bước tùy chọn. Các bạn có thể lựa chọn làm hoặc không làm. Nếu trong bảng báo giá của bạn có dòng trống thì khi sao chép công thức xuống sẽ hiển thị lỗi #N/A như thế này.

Cách kết hợp hàm OFFSET và hàm MATCH để dò tìm nhiều điều kiện

Chúng ta chỉ cần thêm một biện luận vào công thức hàm OFFSET ở bước 5 để nếu gặp ô trống, không có giá trị mã sản phẩm hay size thì không hiển thị lỗi #N/A. Chắc hẳn các bạn cũng đã biết là chúng ta nên lồng hàm IF và hàm OR vào đây. Công thức chi tiết như sau:

Cách kết hợp hàm OFFSET và hàm MATCH để dò tìm nhiều điều kiện

Tất nhiên, khi lồng thêm hàm thì các bạn cần thêm một dấu ngoặc đơn ở cuối công thức thì mới thực hiện được. Công thức cuối cùng có dạng như sau:

Cách kết hợp hàm OFFSET và hàm MATCH để dò tìm nhiều điều kiện

Kết quả mà chúng ta thu được như sau:

Cách kết hợp hàm OFFSET và hàm MATCH để dò tìm nhiều điều kiện

Xem thêm: Hướng dẫn cách viết hàm OFFSET trong VBA Excel hiệu quả

Kết luận

Qua bài viết này các bạn có thể thấy rằng chỉ cần biết cách kết hợp các hàm quen thuộc là chúng ta có thể xử lý được một vấn đề phức tạp. Nếu bạn muốn học cách sử dụng thành thạo 150+ hàm thông dụng trong Excel thì hãy đăng ký khóa học dưới đây:

Tuyệt đỉnh Excel: Trở thành bậc thầy Excel sau 16 giờ

Khóa học sẽ giúp bạn nắm rõ cách sử dụng các công cụ và hàm trong Excel để ứng dụng vào công việc thực tế. Trong quá trình học, giảng viên sẽ hỗ trợ bạn qua mục Hỏi - Đáp, đảm bảo mọi thắc mắc của bạn đều được trả lời nhanh chóng, kịp thời. Các bạn có thể học bất cứ khi nào bạn muốn vì Gitiho không giới hạn thời gian hay số lượt học. 

Đặc biệt, ngay khi đăng ký khóa học các bạn sẽ được nhận 3 phần quà giá trị là:

  1. Sử dụng miễn phí khóa học Thủ thuật Excel cho dân phòng trị giá 199k.
  2. Ebook tổng hợp phím tắt Excel được biên soạn bởi chuyên gia tin học văn phòng hàng đầu.
  3. Add In độc quyền của Gitiho giúp bạn tăng 200% hiệu suất làm việc.

Hãy đăng ký ngay hôm nay để không bỏ lỡ ưu đãi học phí hấp dẫn nhất nhé!

Cùng tham gia cộng đồng hỏi đáp về chủ đề Excel Cơ Bản

Thảo luận 0 câu trả lời
Lượt xem 84 lượt xem
Vỗ tay vỗ tay

0 Bình luận

@ 2020 - Bản quyền của Công ty cổ phần công nghệ giáo dục Gitiho Việt Nam
Giấy chứng nhận Đăng ký doanh nghiệp số: 0109077145, cấp bởi Sở kế hoạch và đầu tư TP. Hà Nội