HomeTin học Văn phòngExcelCách sử dụng...

Cách sử dụng Hàm OFFSET 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ả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ỉ 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 hàm đó 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ẽ đảm nhận vị trí của thẩm quyền giải quyết ô, rồi di chuyển từ đó theo số ô hàng colsrồi trả về tham chiếu của ô đích.

Nếu Chiều cao chiều rộng các tham số được đặt thành 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 thêm  Cách để Chèn một tệp PDF trong Excel

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 hàng tham số. Nó sẽ di chuyển sang bên phải theo số lượng 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 caochiều rộng đồng thời, 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, 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ụ: Tham chiếu đến một ô đơn lẻ

Một bảng tính mẫu trong Excel

Để 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 để chỉ C4.

Khi chức năng OFFSET được sử dụng đơn giản, 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 dưới đây:
     =OFFSET(A1, 3, 2) 

    Công thức này kêu gọi BÙ LẠI chức năng đảm nhận vị trí của ô A1sau đó di chuyển xuống bởi 3 các ô, rồi di chuyển sang phải 2 tế bào để tiếp cận tế bào C4.

  3. Nhấn đi vào.

một khi bạn nhấn đi vào 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 ta sẽ sử dụng A1 làm điểm tham chiếu ban đầu.

  1. Chọn ô mà bạn muốn nhập công thức. Chúng ta sẽ sử dụng ô G1 lần nữa.
  2. Trong thanh công thức, nhập công thức dưới đây:
     =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 ô đí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 đi vào.
Hàm OFFSET được sử dụng để trả về một phạm vi ô

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. 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 các năm nhất định bắt đầu từ năm 2021.

Một ví dụ thực tế về hàm OFFSET trong Excel

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 số năm cho tổng số, 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 kết 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 dưới đây:
     =SUM(OFFSET(H5,0, 0, 1, -C1)) 

    Công thức này bao gồm BÙ LẠI TỔNG. Ở bước đầu tiên, OFFSET sẽ chuyển đến ô H5. Từ đó, nó sẽ di chuyển theo hàng và cột và sau đó nó sẽ trả về một dải ô bắt đầu từ H5. Sự lựa chọn này sẽ được 1 ô theo chiều cao, và -C1 các ô 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ởi C1 tế bào.

  3. Ở bước cuối cùng, hàm SUM sẽ cộng các số trong phạm vi lại với nhau và xuất chúng.
  4. Nhấn đi vào.
Kết quả của một ví dụ OFFSET trong Excel

Con số bạn nhận được dưới dạng tổng hiện tại 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.



Zik.vn – Biên dịch & Biên soạn Lại

spot_img

Create a website from scratch

Just drag and drop elements in a page to get started with Newspaper Theme.

Buy Now ⟶

Bài viết liên quang

DMCA.com Protection Status