Tin Nóng![[Video] Cách kết hợp hàm MATCH với OFFSET trong Google Sheet chi tiết](https://bachkhoavietnam.vn/wp-content/uploads/2024/09/video-cach-ket-hop-ham-match-voi-offset-trong-google-sheetTHUMB.jpg)
Trong Google Sheet có rất nhiều công thức hàm, và những công thức hàm này thường hỗ trợ nhau để giúp chúng ta tính toán nhanh hơn. Vậy hàm MATCH kết hợp với hàm OFFSET trong Google Sheet sẽ như thế nào? Cùng tìm hiểu nhé!
1. Cách sử dụng hàm MATCH trong Google Sheet
Hàm MATCH là gì?
Hàm MATCH trong hàm Google Sheet là hàm tìm kiếm một giá trị xác định trước trong một phạm vi ô rồi trả về đúng vị trí tương đối của giá trị trong phạm vi đó.
Cách sử dụng hàm MATCH
Công thức:
= MATCH(khóa_tìm_kiếm; dải_ô; loại_tìm_kiếm)
Trong đó:
+ khóa_tìm_kiếm: Giá trị mà bạn muốn tìm kiếm.
+ dải_ô: Vùng dữ liệu bạn muốn xác định vị trí của khóa_tìm_kiếm. Dải ô mà bạn chọn chỉ có thể có duy nhất 1 hàng hoặc 1 cột. Ví dụ: A1:A10 hoặc A1:H1.
+ [loại_tìm_kiếm]: Là phương thức tìm kiếm. Thường có 3 kiểu tìm kiếm là -1; 0 và 1.
Ví dụ minh họa:
Cho bảng dữ liệu gồm các trường: STT, TÊN SP, HÃNG, GIÁ BÁN.
Sử dụng hàm MATCH để xác định vị trí của các sản phẩm.
Ví dụ minh họa hàm MATCH
2. Cách sử dụng hàm OFFSET trong Google Sheet
Hàm OFFSET là gì?
Hàm OFFSET trong Google Sheet là hàm trả về giá trị tham chiếu cách một số hàng hoặc một số cột đã xác định trước. Kết quả trả về có thể là 1 ô hoặc dải ô dựa vào số hàng và số cột bạn muốn trả về.
Cách sử dụng hàm OFFSET
Công thức:
=OFFSET(tham_chiếu_ô; hàng_bù_trừ; cột_bù_trừ; chiều cao; chiều rộng)
Trong đó:
+ tham_chiếu_ô: Ô cố định ban đầu để xác định hàng_bù_trừ và cột_bù_trừ.
+ hàng_bù_trừ: Số hàng để bù trừ.
+ cột_bù_trừ: Số cột để bù trừ.
+ (chiều cao): Chiều cao của dải ô trả về, bắt đầu từ đích bù trừ.
+(chiều rộng): Chiều rộng của dải ô trả về, bắt đầu từ đích bù trừ.
Ví dụ minh họa:
Cho bảng dữ liệu gồm các trường: STT, TÊN SP, HÃNG, GIÁ BÁN.
Sử dụng hàm OFFSET để xác định hãng của các sản phẩm.
Ví dụ minh họa hàm MATCH
3. Cách kết hợp hàm MATCH và hàm OFFSET trong Google Sheet
Video hướng dẫn cách kết hợp hàm MATCH và hàm OFFSET trong Google Sheet.
Công thức:
=OFFSET(tham_chiếu_ô; MATCH(khóa_tìm_kiếm; dải_ô; loại_tìm_kiếm); cột_bù_trừ; chiều cao; chiều rộng)ại_tìm_kiếm>
Trong đó:
+ tham_chiếu_ô: Ô cố định ban đầu để xác định hàng_bù_trừ và cột_bù_trừ.
+ MATCH(khóa_tìm_kiếm; dải_ô; loại_tìm_kiếm)
+ cột_bù_trừ: Số cột để bù trừ.
+ chiều cao: Chiều cao của dải ô trả về, bắt đầu từ đích bù trừ.
+ chiều rộng: Chiều rộng của dải ô trả về, bắt đầu từ đích bù trừ.
Ví dụ minh họa:
Cho bảng dữ liệu gồm các trường: MÃ SP, TÊN SP, HÃNG, GIÁ BÁN.
Kết hợp MATCH và OFFSET để tìm giá bán của sản phẩm.
Công thức:
=OFFSET($A$1;MATCH(F2;$B$2:$B$10;0);3)
Giải thích:
+ $A$1: Ô cố định để xác định hàng và cột bù trừ.
+ MATCH(F2;$B$2:$B$10;0): Xác định số hàng dựa vào vị trí của tên sản phẩm trong cột TÊN SP.
+ 3: Từ ô A1 đến cột D (GIÁ BÁN) là 3.
Kết hợp hàm MATCH và OFFSET
4. Các lỗi thường gặp khi kết hợp hàm MATCH và hàm OFFSET
Lỗi #N/A
Lý do trả kết quả lỗi: Nhập thiếu đối số của hàm, không tìm thấy đối số ước tính, không tìm thấy giá trị, hoặc trong hàm MATCH dải ô phải là hàng đơn hoặc cột đơn.
Cách khắc phục: Kiểm tra lại các đối số của hàm MATCH và OFFSET đã nhập đủ và đúng chưa, dải ô đã chọn trong hàm MATCH có phải hàng đơn hay cột đơn không (ví dụ: A2:A10, A1:G1).
Lỗi #N/A
Lỗi #REF
Lý do trả kết quả lỗi: Đối số hàng hoặc cột bù trừ của hàm OFFSET trả về giá trị nằm ngoài bảng tính hoặc kết quả trả về bị đè lên ô khác.
Cách khắc phục: Kiểm tra lại đối số hàng bù trừ và cột bù trừ. Kiểm tra kết quả trả về bị đè lên ô nào thì chúng ta sẽ dịch chuyển công thức hàm đến vị trí khác phù hợp.
Lỗi #REF
Lỗi #ERROR
Lý do trả kết quả lỗi: Do nhập sai cú pháp hàm.
Cách khắc phục: Kiểm tra các đối số và dấu ngăn cách các đối số đã nhập đúng quy cách không.
Lỗi #ERROR
Lỗi #VALUE
Lý do trả kết quả lỗi: Không tìm thấy giá trị mảng, nhập sai kiểu dữ liệu của đối số. Hoặc tham số chiều cao và chiều rộng của hàm OFFSET nhỏ hơn 1.
Cách khắc phục: Kiểm tra lại kiểu dữ liệu của từng đối số, giá trị tìm kiếm có phù hợp với dải ô chứa giá trị tìm kiếm không. Tham số chiều cao và chiều rộng của hàm OFFSET phải lớn hơn hoặc bằng 1.
Lỗi #VALUE
5. Một số lưu ý khi kết hợp hàm MATCH và hàm OFFSET
– Hàm MATCH chỉ trả về vị trí của giá trị đó chứ không trả về giá trị đó.
– Hàm MATCH và OFFSET không phân biệt chữ hoa hay chữ thường khi bạn nhập. Ví dụ: OFFSET=offset.
– Nếu hàm MATCH không tìm thấy giá trị chính xác nào thì sẽ trả về lỗi #N/A.
– Đối số thứ 4 và 5 của hàm OFFSET phải lớn hơn hoặc bằng 1. Nếu sai sẽ trả về giá trị #VALUE!.
– Trong OFFSET nếu hàng hoặc cột bù trừ nằm ngoài bảng tính sẽ trả về lỗi #REF!.
– Trong OFFSET nếu kết quả mảng trả về đè lên ô khác sẽ trả về lỗi #REF!.
6. Một số bài tập ví dụ về kết hợp hàm MATCH và hàm OFFSET
Video hướng dẫn một số bài tập ví dụ về kết hợp hàm MATCH và hàm OFFSET.
Bảng dữ liệu quản lý lương nhân viên
Bài tập 1: Tìm Tên NV, Chức vụ, Phòng ban theo Mã NV.
– Tìm Tên NV theo Mã NV:
=OFFSET($A$2;MATCH(C16;$A$3:$A$13;0);1)
Giải thích:
+ $A$2: Ô cố định để xác định hàng và cột bù trừ.
+ MATCH(C16;$A$3:$A$13;0): Xác định số hàng dựa vào vị trí của mã nhân viên trong cột MÃ NV.
+ 1: Từ ô A2 đến cột B (TÊN NV) là 1.
– Tìm Chức vụ theo Mã NV:
=OFFSET($A$2;MATCH(C16;$A$3:$A$13;0);2)
Giải thích:
+ $A$2: Ô cố định để xác định hàng và cột bù trừ.
+ MATCH(C16;$A$3:$A$13;0): Xác định số hàng dựa vào vị trí của mã nhân viên trong cột MÃ NV.
+ 2: Từ ô A2 đến cột C (CHỨC VỤ) là 2.
– Tìm Phòng ban theo Mã NV:
=OFFSET($A$2;MATCH(C16;$A$3:$A$13;0);3)
Giải thích:
+ $A$2: Ô cố định để xác định hàng và cột bù trừ.
+ MATCH(C16;$A$3:$A$13;0): Xác định số hàng dựa vào vị trí của mã nhân viên trong cột MÃ NV.
+ 3: Từ ô A2 đến cột D (PHÒNG BAN) là 3.
Tìm Tên NV, Chức vụ, Phòng ban theo Mã NV
Bài tập 2: Tìm lương theo tháng của nhân viên.
=OFFSET($A$2;MATCH(C16;$A$3:$A$13;0);MATCH(C15;B2:P2;0))
Giải thích:
+ $A$2: Ô cố định để xác định hàng và cột bù trừ.
+ MATCH(C16;$A$3:$A$13;0): Xác định số hàng dựa vào vị trí của mã nhân viên trong cột MÃ NV.
+ MATCH(C15;B2:P2;0): Xác định số cột dựa vào vị trí của tháng trong bảng lương 12 tháng.
Tìm lương theo tháng của nhân viên
Bài tập 3: Tìm lương 12 tháng của một nhân viên.
=OFFSET($A$2;MATCH(C16;$A$3:$A$13;0);4;1;12)
Giải thích:
+ $A$2: Ô cố định để xác định hàng và cột bù trừ.
+ MATCH(C16;$A$3:$A$13;0): Xác định số hàng dựa vào vị trí của mã nhân viên trong cột MÃ NV.
+ 4: Từ ô A2 đến vị trí lương tháng đầu tiên (Tháng 01) là 4.
+ 1: Chiều cao của kết quả trả về là 1 tương ứng với một nhân viên.
+ 12: Chiều rộng của kết quả trả về là 12 tương ứng với 12 tháng.
Tìm lương 12 tháng của một nhân viên
7. Những câu hỏi thường gặp khi kết hợp hàm MATCH và hàm OFFSET
Hàm MATCH có thể kết hợp được với hàm nào nữa không?
Ngoài việc kết hợp với hàm OFFSET thì hàm MATCH còn có thể kết hợp với nhiều hàm khác như hàm INDEX, VLOOKUP, IF, INDIRECT, IMPORTRANGE giúp công thức hàm cập nhật tự động khi dữ liệu có thay đổi.
Lợi ích của việc kết hợp hàm MATCH với hàm OFFSET
Khi kết hợp hàm MATCH với hàm OFFSET sẽ giúp chúng ta tham chiếu ô một cách tự động mà không cần phải sử dụng lặp đi lặp lại công thức hàm OFFSET nhiều lần.
Câu hỏi thường gặp về hàm MATCH và OFFSET
Một số mẫu laptop giúp bạn làm việc tốt hơn với khối việc văn phòng:
Trên đây là cách kết hợp hàm MATCH và OFFSET trong Google Sheet trong Google Sheet. Mong rằng bài viết này sẽ mang lại những thông tin bạn cần. Cảm ơn bạn đã theo dõi và chúc bạn thành công nhé!