Hàm OFFSET trong Excel
Hàm OFFSET trong Excel

Tóm lược

Hàm OFFSET trong Excel trả về một tham chiếu đến một phạm vi được xây dựng với năm đầu vào: (1) điểm bắt đầu, (2) độ lệch hàng, (3) độ lệch cột, (4) chiều cao ở hàng, (5) chiều rộng bằng cột. OFFSET rất tiện dụng trong các công thức yêu cầu phạm vi động.

Mục đích
Tạo độ lệch tham chiếu từ điểm bắt đầu đã cho

Giá trị trả lại
Tham chiếu ô.

Cú pháp
= OFFSET (tham chiếu, hàng, cột, [height], [width])
Tranh luận
  • tài liệu tham khảo – Điểm bắt đầu, được cung cấp dưới dạng tham chiếu ô hoặc dải ô.
  • hàng – Số hàng cần bù bên dưới tham chiếu bắt đầu.
  • cols – Số cột cần bù đắp ở bên phải của tham chiếu bắt đầu.
  • Chiều cao – [optional] Chiều cao trong các hàng của tham chiếu được trả về.
  • chiều rộng – [optional] Chiều rộng trong các cột của tham chiếu được trả về.
Phiên bản
Ghi chú sử dụng

Hàm OFFSET trong Excel trả về một phạm vi động được xây dựng với năm đầu vào: (1) điểm bắt đầu, (2) độ lệch hàng, (3) độ lệch cột, (4) chiều cao trong hàng, (5) chiều rộng trong cột.

Điểm bắt đầu ( tài liệu tham khảo đối số) có thể là một ô hoặc một dải ô. Các hàngcols đối số là số ô cần “bù đắp” từ điểm bắt đầu. Các Chiều caochiều rộng đối số là tùy chọn và xác định kích thước của phạm vi được tạo. Khi nào Chiều caochiều rộng bị bỏ qua, chúng mặc định là chiều cao và chiều rộng của tài liệu tham khảo.

Ví dụ: để tham chiếu C5 bắt đầu từ A1, tài liệu tham khảo là A1, hàng là 4 và cols là 2:

=OFFSET(A1,4,2) // returns reference to C5

Để tham chiếu C1: C5 từ A1, tài liệu tham khảo là A1, hàng là 0, cols là 2, Chiều cao là 5, và chiều rộng là 1:

=OFFSET(A1,0,2,5,1) // returns reference to C1:C5

Lưu ý: chiều rộng có thể được bỏ qua, vì nó sẽ mặc định là 1

Người ta thường thấy OFFSET được bao bọc trong một hàm khác mong đợi một phạm vi. Ví dụ: đến SUM C1: C5, bắt đầu từ A1:

=SUM(OFFSET(A1,0,2,5,1)) // SUM C1:C5

Mục đích chính của OFFSET là cho phép các công thức tự động điều chỉnh theo dữ liệu có sẵn hoặc theo đầu vào của người dùng. Hàm OFFSET có thể được sử dụng để xây dựng phạm vi được đặt tên động cho biểu đồ hoặc bảng tổng hợp, để đảm bảo rằng dữ liệu nguồn luôn được cập nhật.

Lưu ý: Trạng thái tài liệu Excel Chiều caochiều rộng không thể là số âm, nhưng các giá trị âm dường như đã hoạt động tốt kể từ đầu những năm 1990. Hàm OFFSET trong Google Trang tính sẽ không cho phép giá trị âm cho các đối số chiều cao hoặc chiều rộng.

Các ví dụ

Các ví dụ dưới đây cho thấy cách OFFSET có thể được định cấu hình để trả về các loại phạm vi khác nhau. Những màn hình này được chụp bằng Excel 365, vì vậy OFFSET trả về một mảng động khi kết quả là nhiều hơn một ô. Trong các phiên bản Excel cũ hơn, bạn có thể sử dụng Phím F9 để kiểm tra kết quả trả về từ OFFSET.

Ví dụ 1

Trong màn hình bên dưới, chúng tôi sử dụng OFFSET để trả về giá trị thứ ba (tháng Ba) trong cột thứ hai (Tây). Công thức của H4 là:

=OFFSET(B3,3,2) // returns D6

Hàm OFFSET Ví dụ 1

Ví dụ số 2

Trong màn hình bên dưới, chúng tôi sử dụng OFFSET để trả về giá trị cuối cùng (tháng 6) trong cột thứ ba (miền Bắc). Công thức của H4 là:

=OFFSET(B3,6,3) // returns E9

Hàm OFFSET Ví dụ 2

Ví dụ # 3

Dưới đây, chúng tôi sử dụng OFFSET để trả về tất cả các giá trị trong cột thứ ba (North). Công thức của H4 là:

=OFFSET(B3,1,3,6) // returns E4:E9

Hàm OFFSET Ví dụ 3

Ví dụ # 4

Dưới đây, chúng tôi sử dụng OFFSET để trả về tất cả các giá trị cho tháng Năm (hàng thứ năm). Công thức của H4 là:

=OFFSET(B3,5,1,1,4) // returns C8:F8

Hàm OFFSET Ví dụ 4

Ví dụ số 5

Dưới đây, chúng tôi sử dụng OFFSET để trả về giá trị tháng 4, tháng 5 và tháng 6 cho khu vực phía Tây. Công thức của H4 là:

=OFFSET(B3,4,2,3,1) // returns D7:D9

Hàm OFFSET Ví dụ 5

Ví dụ # 6

Dưới đây, chúng tôi sử dụng OFFSET để trả về giá trị tháng 4, tháng 5 và tháng 6 cho Tây và Bắc. Công thức của H4 là:

=OFFSET(B3,4,2,3,2) // returns D7:E9

Hàm OFFSET Ví dụ 6

Ghi chú

  • OFFSET chỉ trả về một tham chiếu, không có ô nào được di chuyển.
  • Cả hai hàngcols có thể được cung cấp dưới dạng số âm để đảo ngược hướng bù đắp bình thường của chúng – âm cols bù đắp sang trái và phủ định hàng bù đắp trên.
  • OFFSET là một “chức năng dễ bay hơi“- nó sẽ tính toán lại với mỗi lần thay đổi trang tính. Các hàm dễ thay đổi có thể làm cho các sổ làm việc lớn hơn và phức tạp hơn chạy chậm.
  • OFFSET sẽ hiển thị lỗi #REF! giá trị lỗi nếu phần bù nằm ngoài cạnh của trang tính.
  • Khi chiều cao hoặc chiều rộng bị bỏ qua, chiều cao và chiều rộng của tài liệu tham khảo Được sử dụng.
  • OFFSET có thể được sử dụng với bất kỳ chức năng nào khác mong đợi nhận được tham chiếu.
  • Tài liệu Excel cho biết Chiều caochiều rộng không thể là tiêu cực, nhưng các giá trị âm có hiệu quả.

https://exceljet.net/excel-functions/excel-offset-function