Hàm offset trong Excel: Công Thức và Cách sử dụng Hàm offset trong Excel có ví dụ minh họa cụ thể

Excel là một công cụ hỗ trợ chúng ta rất nhiều hàm tính toán trong các lĩnh vực khác nhau: toán học, thống kê, kế toán, ... và hàm OFFSET thường được mọi người sử dụng để tham chiếu đến các ô, dãy, các trang tính hoặc các bảng tính. Trong bài viết sau đây, mình sẽ chia sẻ kiến thức về công thức và cách sử dụng hàm OFFSET một cách chi tiết và cụ thể nhất kèm theo ví dụ minh họa để bạn có thể hình dung rõ hơn. Ngoài ra, hàm OFFSET có thể kết hợp được với nhiều hàm thông dụng khác để hỗ trợ công việc của bạn hiệu quả hơn.

1. Hàm offset trong Excel là gì?

 

Hình ảnh minh họa về hàm OFFSET trong Excel

 

Hàm OFFSET trong Excel là hàm trả về tham chiếu đến một vùng nào đó, được tính bằng 1 ô hay một dãy ô bắt đầu và số dòng hoặc số cột, một khoảng cách được chỉ định trước. Ngoài ra, chúng ta có thể chỉ định rõ số hàng số cột của vùng tham chiếu trả về. Hay 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 ô.

2. Công thức của hàm offset

 

Hàm OFFSET có công thức như sau:

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

 

Trong đó: 

• reference có nghĩa là vùng mà chúng ta muốn làm điểm xuất phát để tham chiếu đến vùng khác. Reference phải chỉ đến một ô hoặc một dãy ô liên tục, nếu không thì hàm OFFSET sẽ báo lỗi #VALUE!
•  
• rows có nghĩa là số dòng dời lên (hoặc xuống) tính từ reference, nhập số dương nếu muốn dời xuống, hoặc số âm nếu muốn dời lên.
•  
• cols có nghĩa là số cột dời sang phải trái (hoặc phải) tính từ reference, nhập số dương nếu muốn dời sang phải, hoặc số âm nếu muốn dời sang trái.
•  
• height có nghĩa là số dòng (độ cao) của vùng tham chiếu cần trả về.
•  
• width có nghĩa là số cột (độ rộng) của vùng tham chiếu cần trả về.

 

3. Cách sử dụng hàm offset

 

3.1. Hàm OFFSET được dùng để tham chiếu đến một vùng nào đó, bắt đầu từ một ô, hoặc một dãy ô được chỉ định

 

Ví dụ 1: Áp dụng hàm OFFSET để trả về giá trị của ô E6 trong ô B8 thông qua ô tham chiếu cơ sở B5.

 

Tại ô B8 chúng ta nhập công thức như sau:

=OFFSET(B5,1,3,1,1)

 

Trong đó:

 

B5 là một ô cần tham chiếu

 

1 : rows là số dòng muốn rời xuống

 

3: cols là số cột muốn rời sang phải

 

1: height là số dòng của vùng tham chiếu cần trả về.

 

1: weight là số cột của vùng tham chiếu cần trả về.

 

Hình ảnh minh họa về cách sử dụng hàm OFFSET

 

Ví dụ 2:  Hãy áp dụng hàm OFFSET để lấy ra giá trị ô ở vị trí cột 2 dòng 3 trong bảng sau.

 

Bước 1: Trong bảng dữ liệu tính Excel, chúng ta nhập công thức vào ô tham chiếu muốn hiển thị kết quả như sau:

 =OFFSET(B3,3,2) 

 

Hình ảnh minh họa về cách sử dụng hàm OFFSET trong Excel

 

Bước 2: sau đó nhấn Enter và chúng ta nhận được kết quả như sau:

 

Hình ảnh minh họa về cách sử dụng hàm OFFSET trong Excel

 

3.2.  Hàm OFFSET là một hàm có thể kết hợp với hầu hết các loại hàm.  

 

Ví dụ 2: Sử dụng kết hợp hàm OFFSET và hàm SUM để tính tổng của cột D ở bảng sau đây.

 

Bước 1: Trong bảng dữ liệu tính Excel, chúng ta nhập công thức  vào ô tham chiếu muốn hiển thị kết quả như sau:

=SUM(OFFSET(A3,1,3,5,1)) 

 

Trong đó:

• SUM và OFFSET có nghĩa là lệnh hàm.

•  

• A3 có nghĩa là ô tham chiếu.

•  

• 1 có nghĩa là số hàng di chuyển xuống bắt đầu từ ô tham chiếu A3.

•  

• 3: có nghĩa là số cột được di chuyển sang phải bắt đầu từ ô tham chiếu A3.

•  

• 5: có nghĩa là tổng số hàng lấy dữ liệu tính kết quả.

•  

• 1: có nghĩa là tổng số cột lấy dữ liệu tính kết quả.

 

Hình ảnh minh họa hàm OFFSET kết hợp hàm SUM.

 

Bước 2: sau đó nhấn phím Enter để nhận được kết quả sau:

 

Hình ảnh minh họa hàm OFFSET kết hợp hàm SUM.

4. Một số lưu ý khi sử dụng hàm offset

• Khi chúng ta thực hiện công thức của hàm OFFSET thì Reference phải chỉ đến một ô hoặc một dãy ô liên tục, nếu không hàm sẽ trả về lỗi #VALUE!.
•  
• Trong công thức của hàm OFFSET, hàng có thể là số dương (bên dưới) hoặc số âm (bên trên), Cột có thể là số dương (bên phải vùng tham chiếu bắt đầu) hoặc số âm (bên trái vùng tham chiếu bắt đầu) và Height và width phải là số dương.
•  
• Trong trường hợp đối số hàng và cột làm cho vùng tham chiếu trả về vượt ra ngoài đường biên của trang tính, thì hàm OFFSET trả về giá trị lỗi #REF!
•  
• Trong trường hợp đối số chiều cao và độ rộng được bỏ qua, thì nó được giả định có cùng chiều cao và độ rộng với vùng tham chiếu.

 

Qua bài viết trên, chúng ta đã có thể hiểu rõ cụ thể về hàm OFFSET về cú pháp của hàm và cách sử dụng của hàm OFFSET. Trên đây là hướng dẫn chi tiết cách sử dụng hàm OFFSET cơ bản nhất và một số ví dụ cụ thể về sự kết hợp của hàm OFFSET với hàm SUM trong Excel. Hi vọng các bạn đã hiểu rõ hơn về hàm OFFSET và có thể ứng dụng nó tốt nhất trong công việc của mình. Chúc các bạn thành công!