Điều này giúp 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 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(tham chiếu, hàng, cột, chiều cao, chiều rộng)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 1, thì công thức sẽ trả về kết quả là một dải ô trong khu vực đó. 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. Sau 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ố của tham số cột.
Từ đó trở đi, 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, thì 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.
Nghe có vẻ rất phức tạp, nhưng bạn sẽ thấy nó dễ hiểu rất nhiều khi xem xét ví dụ thực tế về cách hàm OFFSET được sử dụng.
1. Ví dụ về hàm OFFSET: Tham chiếu đến một ô đơn lẻ
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 hàm OFFSET được sử dụng một cách đơn giản, nó sẽ trả về nội dung của ô đích. Vì vậy, chúng ta sẽ nhận được nội dung của ô C4, tức là số 5, sau 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, nhập:
=OFFSET(A1, 3, 2)Công thức này gọi hàm OFFSET để lấy vị trí của ô A1, sau đó di chuyển xuống 3 ô rồi di chuyển sang phải 2 ô để đến ô C4.
- Nhấn Enter.
Khi bạn nhấn Enter và công thức được thực hiện, ô sẽ trả về 5. Đây là số lấy từ ô C4, vì vậy công thức của bạn đã hoạt động!
2. Ví dụ về hàm OFFSET: Tham chiếu đến một dải ô
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. Chúng ta vẫn sẽ sử dụng A1 làm điểm tham chiếu ban đầu.
- Chọn ô mà bạn muốn nhập công thức. Chúng ta sẽ sử dụng lại ô G1.
- Trong thanh công thức, nhập:
=OFFSET(A1, 3, 2, 6, 2)Công thức này hoạt động giống như công thức trước, tuy nhiên điểm khác là 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.
- Nhấn Enter.
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 dùng cùng với các hàm khác
Trong thực tế, OFFSET thường được lồng với các hàm Excel khác. Hãy xem xét ví dụ dưới đây.
Trong ví dụ này, chúng tôi có thu nhập của một dự án trong các năm 2017-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ụ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 theo.
Để đạ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.
- Chọn ô mà bạn muốn tính tổng.
- 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 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 .
- Ở 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.
- Nhấn Enter.
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, hàm SUM sẽ tính tổng từ 2015 đến 2021.
Bảng tính động với hàm OFFSET trong Excel
Hàm OFFSET trong Excel nghe có vẻ khó hiểu, nhưng một 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.
Tìm hiểu ngay chương trình học công nghệ thông tin trực tuyến tại FUNiX ở đây:
Vân Nguyễn
Dịch từ: https://www.makeuseof.com/use-offset-function-excel/
Bình luận (0
)