Cách sử dụng hàm OFFSET trong Excel để tham chiếu dữ liệu có ví dụ

Hàm OFFSET được xem là hàm có thể kết hợp với hầu hết các hàm khác trong bảng tính, chỉ cần các hàm kết hợp có sự xuất hiện của ô hoặc dải ô tham chiếu là được. Để biết hàm OFFSET là gì? Công chức chuẩn như thế nào và cách sử dụng hàm OFFSET trong excel ra sao? Bạn hãy tham khảo ngay nội dung bài viết bên dưới nhé. 

Hàm OFFSET trong Excel là gì? 

Hàm OFFSET trong excel trả về một ô hoặc phạm vi ô là số lượng hàng và cột được chỉ định từ một ô hoặc một phạm vi ô.

Trong Excel, hàm OFFSET được sử dụng với mục đích:

• Tiết kiệm thời gian trong việc tính toán dữ liệu của một ô.
• Hàm này kết hợp với hàm khác giúp bạn nâng cao hiệu suất làm việc. 
• 

Hàm OFFSET trong excel giúp tiết kiệm thời gian khi tính toán

>>> Xem thêm: Hướng dẫn chi tiết cách sử dụng hàm Dcounta trong excel

Ứng dụng của hàm OFFSET trong excel

Lệnh OFFSET trong excel thường được sử dụng trong một số trường hợp sau đây:

• Được dùng để tham chiếu dữ liệu của một ô hoặc một dải ô trong bảng tính.

• Được dùng để tính toán dữ liệu của một ô hoặc một dải ô trong bảng tính căn cứ theo vùng tham chiếu sẵn có trước đó.

• Được dùng để kết hợp được với các hàm khác trong excel để hỗ trợ giải quyết công việc.

>>> Tất cả đều có trong cuốn sách “HƯỚNG DẪN THỰC HÀNH EXCEL TỪ CƠ BẢN ĐẾN NÂNG CAO”

ĐĂNG KÝ MUA NGAY

Công thức OFFSET trong excel

Công thức Offset trong Excel 

=OFFSET (tham chiếu, hàng, cols, chiều cao, chiều rộng )

Trong đó: 

• Tham chiếu: là khu vực mà bạn muốn tham chiếu đến phần nào đó
• Hàng: có thể là tích cực hoặc tiêu cực.
• Cols: Số lượng cột, ở bên trái hoặc bên phải, mà bạn muốn ô trên bên trái của tham chiếu được trả về để tham chiếu. Ví dụ, sử dụng cols bằng 2 chỉ định rằng ô trên bên trái của tham chiếu được trả về sẽ là hai cột ở bên phải của ô tham chiếu trên cùng bên trái. Cols có thể là tích cực hoặc tiêu cực.
• Chiều cao: tùy chọn, theo số lượng hàng, mà bạn muốn tham chiếu được trả về. Chiều cao phải là một số dương. Nếu chiều cao bị bỏ qua, tham chiếu được trả về sẽ có cùng chiều cao với tham chiếu.
• Chiều rộng: tùy chọn, theo số lượng cột, mà bạn muốn tham chiếu được trả về. Chiều rộng phải là một số dương. Nếu chiều rộng bị bỏ qua, tham chiếu được trả về sẽ có cùng chiều rộng với tham chiếu.

Công thức hàm OFFSET trong Excel

Cách sử dụng hàm OFFSET trong Excel

Để giúp bạn hiểu rõ hơn về công dụng hàm OFFSET trong excel, chúng tôi sẽ hướng dẫn chi tiết cách dùng hàm OFFSET excel kèm ví dụ minh họa cụ thể ngay bên dưới đây.

Tạo các dải động bằng hàm OFFSET

Cách tạo dải động bằng hàm OFFSET sẽ giúp ích rất nhiều cho các bảng tính cần thêm các cột dữ liệu. Ví dụ như: Bảng tính cần thêm các cột thưởng hàng tháng. 

Lúc này chúng ta sẽ ứng dụng công thức hàm OFFSET như sau:

= OFFSET (Sheet_Name! $A$1, 0, 0, COUNTA (Sheet_Name! $A:$A), 1)

Cụ thể từng bước thực hiện sẽ tiến hành theo trình tự sau: 

Bước 1: Đặt tên cho dải bằng cách vào thanh công cụ của bảng tính, di chuyển đến tab Formulas, chọn mục Date $ Time. Sau khi bảng tùy chọn hiển thị bạn hãy chọn MONTH. Khi bảng tùy chỉnh khác hiển thị bạn nhập số thứ tự của tháng vào rồi nhấn Enter.

Ví dụ tạo các dải động bằng hàm OFFSET

Bước 2: Tiếp theo bạn hãy chọn mục Define Name cũng trong tab Formulas. Khi giao diện hiển thị, bạn hãy nhập công thức = OFFSET (Sheet_Name! $A$1, 0, 0, COUNTA (Sheet_Name! $A:$A), 1) vào ô Refers to. Sau đó chọn OK.

Thao tác nhập hàm OFFSET

Bước 3: Sau khi bạn đã tạo một dải động bằng công thức OFFSET thì bạn có thể dùng Excel Data Validation để tạo danh sách theo mục. Cụ thể như sau: bạn hãy vào tab Data. Trên thanh công cụ bạn hãy chọn Data Validation, khi bảng tùy chọn hiển thị bạn hãy chọn Data Validation… theo hình minh họa bên dưới.

Thao tác tạo danh sách theo mục

Lúc này danh sách sẽ luôn được cập nhật tự động cập nhật ngay khi bạn thêm hoặc xóa các mục từ bảng tính gốc.

Bước 4: Khi hộp thoại Data Validation hiển thị. Bạn hãy vào tab Setting. Ở mục Allow bạn hãy chọn List. Sau đó. tại mục Source bạn hãy điền dải ô cần tham chiếu vào. Trong ví dụ minh họa chúng ta có dải ô tham chiếu là A2:A8. Lưu ý dải ô tham chiếu này phải đặt trong tham chiếu tuyệt đối ($).

Điền dải ô tham chiếu

Bước 5: Nhấn Ok.

>>> Xem thêm: Cách sử dụng hàm choose trong excel chi tiết kèm ví dụ minh họa

Lấy dải từ ô gốc bằng hàm OFFSET trong Excel

Ví dụ: Bạn sử dụng hàm OFFSET trong excel để lấy dải từ ô gốc trong bảng tính. Trong ví dụ này, vị trí ô sẽ nằm ở cột 2 dòng 5.

Bước 1: Đầu tiên bạn nhập công thức hàm theo cú pháp: =OFFSET(B5,3,2)

Nhập công thức hàm

Bước 2: Sau khi nhập công thức kết quả hiển thị như sau:

Kết quả hiển thị

Hàm OFFSET thường kết hợp với hàm nào?

Sau khi đã hiểu hàm OFFSET trong excel là gì, phần tiếp theo của bài viết chúng tôi sẽ hướng dẫn bạn cách dùng hàm OFFSET kết hợp với những hàm khác trong excel, bạn hãy khám phá ngay nhé.

Hàm OFFSET kết hợp với hàm SUM

Ví dụ: Tính tổng lương và thưởng của nhân viên Nguyễn Văn B bắt đầu từ ô B6 (lương của nhân viên đầu tiên).

Áp dụng hàm: =SUM(OFFSET(B6,3,0,1,2) các bạn sẽ được kết quả như sau:

Hàm OFFSET kết hợp với hàm SUM

Hàm OFFSET kết hợp với hàm AVERAGE, MAX, MIN

Bước 1: Cho ví dụ như mô tả dưới ảnh, để tính giá trị trung bình, bạn nhập công thức sau: =AVERAGE(OFFSET(B1,COUNT(B:B)-E2+1,0,E2,1)).

Hàm OFFSET kết hợp với hàm AVERAGE

Bước 2: Nhấn Enter để hiển thị kết quả.

Hình minh họa kết quả nhận được

Bước 3: Để tính số lớn nhất trong dải, bạn nhập công thức sau: =MAX(OFFSET(B1,COUNT(B:B)-E2+1,0,E2,1)).

Hàm OFFSET kết hợp hàm MAX

Bước 4: Nhấn Enter để nhận kết quả.

Nhấn Enter hiện kết quả

Bước 5: Để tìm số nhỏ nhất trong dải, bạn nhập công thức hàm min trong excel: 

=MIN(OFFSET(B1,COUNT(B:B)-E2+1,0,E2,1)).

Hàm OFFSET kết hợp hàm MIN

Bước 6: Nhấn Enter để nhận kết quả.

Kết quả hàm MIN và OFFSET kết hợp

Hàm OFFSET kết hợp với hàm VLOOKUP

Bước 1: Trong ô tham chiếu muốn hiển thị kết quả, bạn nhập công thức =VLOOKUP(B1,A5:B11,2,FALSE).

Hàm OFFSET kết hợp với hàm VLOOKUP

Bước 2: Nhấn Enter để nhận kết quả. 

Kết quả nhận được

Ngoài ra, hàm OFFSET có thể kết hợp với nhiều hàm khác để tính toán và xử lý dữ liệu trong excel. Hàm sẽ giúp ích các bạn rất nhiều nếu các bạn biết áp dụng và kết hợp hàm một cách hiệu quả.

Những điều lưu ý khi sử dụng hàm OFFSET

Cách sử dụng hàm OFFSET trong excel sẽ chuẩn hơn khi bạn ghi nhớ những điểm sau đây:

• Hàm OFFSET trong Excel chỉ trả về một tham chiếu trong phạm vi dữ liệu sẵn có chứ không di chuyển đến bất kỳ ô hoặc dải nào trong bảng dữ liệu. 

• Kết quả trả về của hàm OFFSET là một dải ô thì các đối số rows và các đối số cols được đề cập đến luôn luôn là phía trên bên trái.

• Đối số reference trong công thức hàm OFFSET phải là một ô hoặc một dải ô liền kề. Nếu bạn nhập sai đối số Reference thì kết quả trả về sẽ là lỗi #VALUE!.

• Nếu đối số rows và đối số cols xác định một ô tham chiếu vượt khỏi phạm vi của trang tính Excel, thì kết quả tham chiếu sẽ báo lỗi #REF!.

Lưu ý khi sử dụng hàm OFFSET

• Nếu bạn bỏ qua các đối số height và đối số width trong công thức thì tham chiếu trả về mặc định có cùng chiều cao và độ rộng với vùng tham chiếu gốc.

• Nếu đối số height và đối số width lớn hơn 1, nghĩa là hàm phải trả về kết quả nhiều hơn 1 ô. Lúc này, bạn phải sử dụng công thức mảng hàm OFFSET, nếu không kết quả sẽ báo lỗi #VALUE!.

• Đối số height và đối số width phải là số dương.

• Hàm OFFSET có thể được kết hợp với các trong Excel nếu hàm chấp nhận tham chiếu ô hoặc dải xuất hiện trong các đối số của nó.

Kết luận

Cách sử dụng hàm OFFSET trong excel sẽ đơn giản và dễ thực hiện hơn nhiều khi bạn thực hành ứng dụng hàm OFFSET nhiều lần cho đến khi thành thạo. Vì vậy, hãy chăm chỉ rèn luyện và học tập nhé. Nếu bạn muốn nâng cao thêm kỹ năng tin học văn phòng, hãy tham gia khoá học Excel, Word, Powpoint trên Unica nhé.

Mời bạn đọc cùng khám phá.