Buzz
Trong Google Sheet, các công thức hàm thường được sử dụng cùng nhau để tăng hiệu suất tính toán. Vậy khi kết hợp hàm MATCH và OFFSET trong Google Sheet, chúng ta có thể làm gì? Cùng khám phá!
1. Cách sử dụng hàm MATCH
Hàm MATCH là gì?
Hàm MATCH trong Google Sheet là công cụ tìm kiếm giá trị xác định trong một phạm vi và trả về 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 áp dụng:
Đặc điểm của dữ liệu:
Ví dụ để minh họa:
Giả sử có một bảng dữ liệu với các trường: STT, TÊN SẢN PHẨM, NHÀ SẢN XUẤT, GIÁ BÁN.
Sử dụng hàm MATCH để tìm vị trí của các sản phẩm.
Ví dụ minh họa về việc sử dụng hàm MATCH
2. Cách sử dụng hàm OFFSET trong Google Sheet
Ý nghĩa của hàm OFFSET:
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à một ô hoặc một phạm vi ô dựa vào số hàng và số cột bạn muốn trả về.
2. Sử dụng hàm OFFSET
Nguyên tắc hoạt động:
Trong đó:
+ ô_tham_chiếu: Ô ban đầu để xác định hàng_bù_trừ và cột_bù_trừ.
+ hàng_bù_trừ: Số hàng cần bù trừ.
+ cột_bù_trừ: Số cột cần bù trừ.
+ (chiều cao): Chiều cao của phạm vi ô trả về, tính từ điểm bù trừ.
+(chiều rộng): Chiều rộng của phạm vi ô trả về, tính từ điểm bù trừ.
Ví dụ minh họa:
Giả sử có một bảng dữ liệu với các trường: STT, TÊN SẢN PHẨM, NHÀ SẢN XUẤT, GIÁ BÁN.
Sử dụng hàm OFFSET để xác định nhà sản xuất của các sản phẩm.
Ví dụ minh họa về việc sử dụng hàm MATCH
3. 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 áp dụng:
Trong đó:
Ví dụ minh họa:
Giả sử có bảng dữ liệu với các trường: MÃ SẢN PHẨM, TÊN SẢN PHẨM, NHÀ SẢN XUẤT, GIÁ BÁN.
Kết hợp MATCH và OFFSET để xác định giá bán của sản phẩm.
Công thức:
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 SẢN PHẨM.
+ 3: Từ ô A1 đến cột D (GIÁ BÁN) có 3 cột.
Kết hợp hàm MATCH và OFFSET
4. Các lỗi phổ biến khi kết hợp hàm MATCH và hàm OFFSET
Lỗi #N/A
Lý do trả kết quả lỗi: Thiếu đối số của hàm, không tìm thấy giá trị, hoặc dải ô trong hàm MATCH không phù hợp.
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ị ghi đè lên ô khác.
Lỗi #REF
Lỗi #ERROR
Lý do trả kết quả lỗi: Do nhập sai cú pháp của hàm.
Lỗi #ERROR
Lỗi #VALUE
Lý do trả kết quả lỗi: Không tìm thấy giá trị trong mảng, nhập sai kiểu dữ liệu cho đối số, hoặc tham số chiều cao và chiều rộng của hàm OFFSET nhỏ hơn 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ị, không trả về giá trị đó.
- Hàm MATCH và OFFSET không phân biệt chữ hoa và chữ thường khi nhập. Ví dụ: OFFSET=offset.
- Nếu hàm MATCH không tìm thấy giá trị chính xác, 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ề cách 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 Nhân viên, Chức vụ, Phòng ban theo Mã NV.
- Tìm Tên Nhân viên theo Mã NV:
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) có 1 cột.
- Tìm Chức vụ theo Mã NV:
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Ụ) có 2 cột.
- Tìm Phòng ban theo Mã NV:
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 Nhân viên, 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.
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.
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 sử dụng hàm MATCH cùng hàm OFFSET, chúng ta có thể tự động tham chiếu ô mà không cần phải lặp lại công thức OFFSET nhiều lần.
Câu hỏi thường gặp về hàm MATCH và OFFSET
Trên đây là cách kết hợp hàm MATCH và OFFSET trong Google Sheet. Hy vọng bài viết này sẽ cung cấp thông tin hữu ích cho bạn. Cảm ơn bạn đã đọc và chúc bạn thành công!
