Cách sử dụng 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) khoảng cách hàng, (3) khoảng cá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, (chiều cao), (chiều rộng))

Tranh luận

• tham chiếu - Đ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.
• height - (tùy chọn) Chiều cao trong các hàng của tham chiếu được trả về.
• width - (tùy chọn) Chiều rộng trong các cột của tham chiếu được trả về.

Phiên bản

Excel 2003

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 ( đối số tham chiếu ) có thể là một ô hoặc một dải ô. Các đối số hàng và cột là số ô cần "bù đắp" từ điểm bắt đầu. Các đối số chiều cao và chiều rộng là tùy chọn và xác định kích thước của phạm vi được tạo. Khi chiều cao và chiều rộng bị bỏ qua, chúng mặc định là chiều cao và chiều rộng của tham chiếu .

Ví dụ: tham chiếu C5 bắt đầu từ A1, tham chiếu là A1, hàng là 4 và cột là 2:

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

Để tham chiếu C1: C5 từ A1, tham chiếu là A1, hàng là 0, cột 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 ý: có thể bỏ qua chiều rộng, 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. Chức năng OFFSET có thể được sử dụng để xây dựng một 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 ý: Tài liệu Excel cho biết chiều cao và chiều rộng không được â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.

Ví dụ

Các ví dụ dưới đây cho thấy cách cấu hình OFFSET để trả về các loại phạm vi khác nhau. Các màn hình này được chụp bằng Excel 365, vì vậy hàm OFFSET trả về một mảng động khi kết quả là nhiều ô. 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

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

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

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 5 (hàng thứ năm). Công thức của H4 là:

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

Ví dụ # 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

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

Ghi chú

• OFFSET chỉ trả về một tham chiếu, không có ô nào được di chuyển.
• Cả hai hàng và cols đều 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 - cols âm bù sang trái và các hàng âm bù ở trên.
• OFFSET là một "hàm 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 chức năng linh hoạt 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 tham chiếu sẽ đượ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 cao và chiều rộng không được âm, nhưng giá trị âm vẫn hoạt động.

Các video liên quan

Cách tạo dải ô được đặt tên động với OFFSET Trong video này, chúng ta sẽ xem xét cách tạo dải ô được đặt tên động bằng cách sử dụng hàm OFFSET, cách phổ biến nhất để tạo dải ô tên động bằng công thức.