Excel OFFSET Chức năng

Excel Chức năng OFFSET trả về một tham chiếu đến một ô hoặc một dải ô được bù đắp từ một ô cụ thể bởi một số hàng và cột nhất định.

cú pháp

=OFFSET (reference, rows, cols, [height], [width])

Lập luận

Giá trị trả lại

Hàm OFFSET sẽ trả về độ lệch tham chiếu ô từ một điểm bắt đầu nhất định.

Ghi chú chức năng

1. #VALUE! giá trị lỗi sẽ trả về khi tham chiếu tham chiếu đến một dải ô không liên tục.
2. #REF! giá trị lỗi sẽ trả về khi các hàng và cột tham chiếu lệch qua cạnh của trang tính.

Các ví dụ

Ví dụ 1: Cách sử dụng cơ bản cho hàm OFFSET

Trả lại tham chiếu đến một ô có công thức dưới đây:
=OFFSET(B2,3,1)

Trong trường hợp này, B2 là điểm bắt đầu, số 3 và 1 có nghĩa là để di chuyển 3 hàng xuống và 1 cột ngay từ ô B2 và trả về giá trị ở C5 cuối cùng. Xem ảnh chụp màn hình:

Trả lại tham chiếu đến một dải ô với công thức dưới đây:
=OFFSET(B2,3,1,2,2)

Trong trường hợp này, bạn sẽ nhận được kết quả của phạm vi 2 x 2 là 3 hàng bên dưới và 1 cột bên phải của ô B2.

Chú thích: #GIÁ TRỊ! Lỗi sẽ xảy ra khi bạn chỉ chọn một ô để áp dụng hàm OFFSET để trả về một dải ô. Bạn cần chọn một phạm vi 2 x 2 (có nghĩa là 4 ô trống), nhập công thức và nhấn Ctrl + sự thay đổi + đăng ký hạng mục thi phím để nhận kết quả.

Ví dụ 2: Sử dụng hàm OFFSET để tính tổng một dải giá trị

Như chúng tôi đã đề cập ở trên, nếu bạn cố gắng sử dụng chức năng OFFSET chẳng hạn như =OFFSET(B2,3,1,2,2) riêng trong một ô, nó sẽ trả về lỗi #VALUE! Lỗi. Tuy nhiên, nếu bạn kết hợp hàm SUM và hàm OFFSET như hình minh họa bên dưới, nó sẽ trả về trực tiếp tổng các giá trị trong phạm vi C5: D6.

1. Chọn một ô trống, sao chép công thức bên dưới vào đó và nhấn đăng ký hạng mục thi phím để nhận kết quả.
=SUM(OFFSET(B2,3,1,2,2)))

Ví dụ 3: Tính tổng các cột dựa trên các tiêu chí nhất định

Như ảnh chụp màn hình dưới đây, làm thế nào để có được tổng doanh thu của Mango từ Chủ nhật đến Thứ ba? Vui lòng thử như bên dưới.

1. Chọn một ô trống, sao chép công thức bên dưới vào đó và nhấn đăng ký hạng mục thi phím để nhận kết quả.
=SUM(OFFSET(C2:E2,MATCH(G4,B3:B8,),))

Chú ý:

1. Trong công thức trên, MATCH(G4,B3:B8) đang tìm kiếm Mango và trở lại vị trí của nó trong phạm vi B3: B8. Hãy xem, Mango định vị 5 hàng bên dưới ô B2, vì vậy nó trả về số 5;
2. Vì kết quả MATCH là 5 và dấu phẩy cuối cùng ở đây đại diện cho 0, hàm OFFSET hiện đang hiển thị là OFFSET(C2:E2,5,0), có nghĩa là khoảng lệch bắt đầu từ phạm vi C2: E2, di chuyển 5 hàng xuống và 0 cột bên phải để nhận tham chiếu đến phạm vi C7: E7;
3. Sau đó, hàm SUM hiển thị là SUM(C7:E7), và cuối cùng trả về tổng các giá trị trong phạm vi C7: E7.

Thêm ví dụ

Làm thế nào để tính tổng mỗi n hàng trong Excel?

Làm cách nào để tạo phạm vi được đặt tên động trong Excel?

Làm cách nào để trả về giá trị ô mỗi năm hoặc hàng thứ n trong Excel?