Công thức offset hoạt động như thế nào trong Excel?

Hàm OFFSET cho phép bạn tham chiếu gián tiếp đến một ô hoặc một dãy ô trong Excel. Với OFFSET, bạn có thể chọn một điểm tham chiếu bắt đầu, sau đó nhập địa chỉ vào các ô đích thay vì tham chiếu trực tiếp đến chúng

Điều này làm cho hàm OFFSET cực kỳ hữu ích trong bảng tính Excel động. Bạn có thể sử dụng riêng hàm OFFSET, nhưng tiềm năng thực sự của nó chỉ xuất hiện khi bạn lồng nó vào bên trong các hàm khác trong Excel. Đọc để tìm hiểu tất cả về OFFSET trong Excel với ba ví dụ

Hàm OFFSET trong Excel là gì?

Hàm OFFSET trả về một tham chiếu đến một ô hoặc một dải ô. OFFSET có năm tham số và cú pháp của nó như sau

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

OFFSET sẽ lấy vị trí của ô tham chiếu, sau đó di chuyển từ đó theo số hàng và cột, sau đó trả về tham chiếu của ô đích

Nếu tham số chiều cao và chiều rộng được đặt lớn hơn một, thì một dải ô trong khu vực đó sẽ được trả về. Chiều cao và chiều rộng là hai tham số tùy chọn. Nếu bạn để trống, OFFSET sẽ đọc chúng là 1 và trả về một tham chiếu ô duy nhất

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

Để sử dụng OFFSET trong Excel, bạn cần nhập ba tham số bắt buộc. Khi bạn đặt ô tham chiếu, OFFSET sẽ di chuyển từ ô đó sang các ô bên dưới theo số lượng tham số hàng. Nó sẽ di chuyển sang bên phải theo số lượng tham số cols

Từ đó, OFFSET sẽ trả về một tham chiếu đến ô đích. Nếu bạn cũng đặt tham số cho chiều cao và chiều rộng, OFFSET sẽ trả về các tham chiếu đến một dải ô, trong đó ô đích ban đầu sẽ là ô phía trên bên trái

Điều này nghe có vẻ phức tạp trên giấy tờ, nhưng thực tế sẽ dễ hiểu hơn nhiều. Hãy xem cách hàm OFFSET thực hiện với một vài ví dụ

1. Hàm OFFSET Ví dụ. Đề cập đến một tế bào duy nhất

Để bắt đầu, hãy tự mình sử dụng hàm OFFSET để hiểu cách nó điều hướng bảng tính. Trong ví dụ này, chúng tôi có một loạt các số trong bảng tính Excel. Chúng ta sẽ sử dụng hàm OFFSET để tham chiếu đến C4

Khi chức năng OFFSET được sử dụng rõ ràng, nó sẽ xuất nội dung của ô đích. Vì vậy, chúng ta sẽ nhận được nội dung của ô C4, là số năm, sau khi chúng ta nhập công thức. Bắt đầu nào

1. Chọn một ô để nhập công thức. Chúng ta sẽ sử dụng ô G1
2. Trong thanh công thức, nhập công thức bên dưới.

    =OFFSET[A1, 3, 2] 

   Công thức này gọi hàm OFFSET đến vị trí của ô A1, sau đó di chuyển xuống 3 ô, rồi di chuyển sang phải 2 ô để đến ô C4
3. nhấn nút Enter

Khi bạn nhấn Enter và công thức được thực hiện, ô sẽ trả về 5. Đây là số từ ô C4, vì vậy công thức của bạn đã hoạt động. Bạn cũng có thể sử dụng tính năng đánh giá để hiểu rõ hơn về các công thức Excel, chẳng hạn như OFFSET

2. Hàm OFFSET Ví dụ. Tham chiếu đến một dãy ô

Sử dụng hai tham số tùy chọn, chiều cao và chiều rộng, bạn cũng có thể trả về một phạm vi ô bằng hàm OFFSET trong Excel. Hãy bắt đầu lại từ ví dụ trước

Lần này, chúng ta sẽ trả lại các ô từ C4 đến D9 bằng cách sử dụng OFFSET trong Excel. Giống như trước đây, chúng tôi sẽ sử dụng A1 làm điểm tham chiếu bắt đầu

1. Chọn ô mà bạn muốn nhập công thức. Chúng ta sẽ sử dụng lại ô G1
2. Trong thanh công thức, nhập công thức bên dưới.

    =OFFSET[A1, 3, 2, 6, 2] 

   Công thức này hoạt động giống như công thức trước, ngoại trừ một khi đến ô đích [C4], nó sẽ chiếm 6 hàng và 2 cột thay vì chỉ ô đích. C4 sẽ là ô đầu tiên trong phạm vi mới
3. nhấn nút Enter

Bây giờ, phạm vi ô C4 đến D9 sẽ xuất hiện tại nơi bạn nhập công thức. Đầu ra sẽ là một mảng

3. Hàm OFFSET Ví dụ. công thức hợp chất

Hai ví dụ trước đây là phần khởi động để xem cách OFFSET hoạt động trong Excel. OFFSET thường được lồng với các hàm Excel khác trong các tình huống thực tế. Hãy chứng minh điều này bằng một ví dụ mới

Trong ví dụ này, chúng tôi có thu nhập của một dự án phụ trong suốt các năm 2017 đến 2021. Ở phía trên, chúng tôi có một ô được cho là hiển thị tổng thu nhập trong những năm nhất định bắt đầu từ năm 2021

Mục tiêu ở đây là tạo một bảng tính động bằng OFFSET trong Excel. Khi bạn thay đổi tổng số năm, công thức tính toán giá trị này cũng sẽ cập nhật

Để đạt được mục tiêu này, chúng ta sẽ sử dụng hàm OFFSET cùng với hàm SUM. Thay vì tham chiếu trực tiếp đến các ô, chúng ta sẽ sử dụng OFFSET. Bằng cách này, chúng ta có thể tự động lấy phạm vi ô sẽ được tích hợp vào SUM

Trong ví dụ này, năm 2021 nằm trong ô H5 và số năm cho tổng số nằm trong ô C1. Chúng tôi đã sử dụng định dạng tùy chỉnh trong ô chứa các năm. Giá trị thực tế là số năm và văn bản Năm là hậu tố. Hãy bắt đầu bằng cách tạo một công thức để tính tổng. Chúng ta sẽ sử dụng hàm SUM trong Excel

1. Chọn ô mà bạn muốn tính tổng
2. Trong thanh công thức, nhập công thức bên dưới.

    =SUM[OFFSET[H5,0, 0, 1, -C1]] 

   Công thức này bao gồm OFFSET và SUM. Ở bước đầu tiên, OFFSET sẽ chuyển đến ô H5. Từ đó, nó sẽ di chuyển theo 0 hàng và 0 cột, sau đó nó sẽ trả về một dải ô bắt đầu từ H5. Lựa chọn này sẽ có chiều cao 1 ô và chiều rộng -C1 ô. Điều này có nghĩa là OFFSET sẽ trả về một phạm vi bắt đầu từ H5 và di chuyển sang trái bởi các ô C1
3. Ở bước cuối cùng, hàm SUM sẽ cộng các số trong dãy lại với nhau và xuất chúng
4. nhấn nút Enter

Tổng số bạn nhận được bây giờ phụ thuộc vào số năm bạn đã nhập. Nếu số năm được đặt thành 1, thì bạn sẽ chỉ nhận được tổng số cho năm 2021

Lưu ý rằng tổng số cập nhật ngay lập tức khi bạn thay đổi số năm. Nếu bạn thay số năm thành 7 thì hàm SUM sẽ tính tổng từ 2015 đến 2021

Bảng tính động với OFFSET

Hàm OFFSET trong Excel có nhiều tham số và bản thân nó có thể gây nhầm lẫn, nhưng khi bạn đã sử dụng nó, bạn sẽ biết rằng, giống như nhiều hàm Excel khác, OFFSET là một hàm thân thiện

Mặc dù bản thân OFFSET không có nhiều công dụng, nhưng khi được kết hợp với các hàm Excel khác, OFFSET cung cấp cho bạn sức mạnh để tạo các bảng tính động và phức tạp

Làm thế nào để tính toán bù trừ trong Excel?

Ví dụ về giá trị offset là gì?