Hàm OFFSET cho phép bạn tham chiếu gián tiếp đến một ô hoặc một dải ô 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ỉ cho 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ể tự sử dụ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 tiếp để 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ẽ đảm nhận vị trí của tài liệu tham khảo ô, và sau đó di chuyển từ đó theo số hàng và colsvà sau đó trả về tham chiếu của ô đích.
Nếu Chiều cao và bề rộng các tham số được đặt thành lớn hơn một, thì một dải ô trong vùng đó sẽ được trả về. Chiều cao và chiều rộng là hai thông 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 ô đơn.
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ố hàng tham số. Nó sẽ di chuyển sang phải theo số cols tham số.
Từ đó trở đi, OFFSET sẽ trả về một tham chiếu đến ô đích. Nếu bạn đặt thông số cho Chiều cao và bề rộng cũng như vậy, 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 có vẻ phức tạp trên giấy tờ, nhưng nó dễ hiểu hơn nhiều trong thực tế. Hãy xem hàm OFFSET hoạt động như thế nào với một vài ví dụ.
1. Ví dụ về hàm OFFSET: Tham chiếu đến một ô đơn
Để bắt đầu mọi thứ, hãy tự sử dụng hàm OFFSET để nắm bắt cách nó điều hướng bảng tính. Trong ví dụ này, chúng ta 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 hàm OFFSET được sử dụng thuần túy, nó sẽ xuất ra 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, khi chúng ta nhập công thức. Bắt đầu nào.
- Chọn một ô để nhập công thức. Chúng ta sẽ sử dụng ô G1.
- Trong thanh công thức, hãy nhập công thức bên dưới:
=OFFSET(A1, 3, 2)Công thức này yêu cầu BÙ LẠI chức năng đảm nhận vị trí của ô A1sau đó di chuyển xuống 3 ô, và sau đó di chuyển sang phải 2 tế bào để tiếp cận tế bào C4.
- Nhấn đi vào.
Một khi bạn nhấn đi vào và công thức được ban hành, ô 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 các công thức Excel, chẳng hạn như OFFSET.
2. Ví dụ về hàm OFFSET: Tham chiếu đến một phạm vi ô
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 dải ô bằng hàm OFFSET trong Excel. Hãy tiếp tục nơi chúng ta đã dừng lại từ ví dụ cuối cùng.
Lần này, chúng ta sẽ trả về các ô 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 của chúng tôi.
- Chọn ô mà bạn muốn nhập công thức. Chúng ta sẽ sử dụng ô G1 lại.
- Trong thanh công thức, hãy 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ừ việc khi nó đến ô đích (C4), nó sẽ có 6 hàng và 2 cột thay vì chỉ một ô đích. C4 sẽ là ô đầu tiên trong phạm vi mới.
- Nhấn đi vào.
Bây giờ phạm vi ô C4 đến D9 sẽ xuất hiện ở nơi bạn nhập công thức. Đầu ra sẽ là một mảng.
3. Ví dụ về hàm OFFSET: Công thức tổng hợp
Hai ví dụ trước đây là một 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 từ 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 cách sử dụng OFFSET trong Excel. Khi bạn thay đổi tổng số năm, công thức tính 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 tôi sẽ sử dụng OFFSET. Bằng cách này, chúng ta có thể nhận động phạm vi ô sẽ được kết hợp vào SUM.
Trong ví dụ này, năm 2021 nằm trong ô H5 và tổng số năm nằm trong ô C1. Chúng tôi đã sử dụng định dạng tùy chỉnh trong ô chứa năm. Giá trị thực tế là số năm và văn bản Năm là một 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.
- Chọn ô mà bạn muốn tính tổng.
- Trong thanh công thức, hãy 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 BÙ LẠI và TỔNG. Trong bước đầu tiên, OFFSET sẽ chuyển đến ô H5. Từ đó, nó sẽ di chuyển bằng 0 hàng và 0 và sau đó nó sẽ trả về một dải ô bắt đầu từ H5. Lựa chọn này sẽ là 1 ô theo chiều cao và -C1 ô theo chiều rộng. Đ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ằng C1 tế bào.
- Trong bước cuối cùng, hàm SUM sẽ cộng các số trong phạm vi với nhau và xuất chúng.
- Nhấn đi vào.
Con số bạn nhận được là tổng số 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 đổi số năm thành 7, hàm SUM sẽ tính tổng từ năm 2015 đến năm 2021.
Bảng tính động với OFFSET
Hàm OFFSET trong Excel có nhiều tham số và tự 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ù OFFSET không được sử dụng nhiều, khi 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 năng động và phức tạp.
