Hướng dẫn sử dụng hàm OFFSET trong Excel
Chức năng OFFSET là gì?
Nó trả về một phạm vi cho không. hàng và cột.
Người dùng cung cấp cho hàng, cột và tham chiếu và bất cứ nơi nào hàng và cột đáp ứng, công thức bù đắp trong Excel sẽ cung cấp cho đầu ra dựa trên đó.
Các Phạm vi được trả về bởi một hàm OFFSET trong Excel là một tế bào đơn. Nó thậm chí có thể là một phạm vi của nhiều tế bào liền kề.
Hàm OFFSET trong Excel hoạt động giống như hàm VLOOKUP . Nó cung cấp cho bạn Đầu ra thông qua Tham chiếu.
Và khi nó trả về giá trị, công thức OFFSET chỉ định Kích thước mà bạn chèn là số hàng và cột.
Chức năng bù đắp Excel không được sử dụng chủ yếu để tìm giá trị độc lập. Bạn có thể tìm Phạm vi giá trị động với hàm OFFSET .
Khi bạn đang làm việc trên Bảng điều khiển , KPI và Biểu đồ khác nhau, chức năng OFFSET và OFFSET với Match sẽ thực sự hữu ích.
Chức năng OFFSET hoạt động như thế nào?
Tình huống: Trong ví dụ cụ thể, bạn sẽ có thể xem danh sách các Phòng ban như Bán hàng, Tiếp thị , Tài chính một cách tương ứng. Nó được theo sau bởi không. của nhân viên đã có mặt tại mỗi Sở trong một tháng đặc biệt.
Số tháng: Tháng 1 đến tháng 5
Vấn đề: Bạn cần tìm hiểu:
Các SUM Last 3 Những hàng ngày với hàm OFFSET
Các trung bình của 4 cột cuối cùng của Bộ ‘dữ liệu’ với hàm OFFSET
Làm thế nào bạn sẽ tìm thấy các giá trị?
Giải pháp: Công thức OFFSET và OFFSET Match sẽ giúp bạn đạt được điều này trong vài phút
Quan trọng: Các đối số trong Công thức OFFSET:
Tham chiếu- Điểm bắt đầu Được cung cấp dưới dạng Tham chiếu ô. Nó đề cập đến một ô hoặc một dải ô trên một trang tính. Sử dụng nó trong một công thức để bạn có thể tìm thấy các giá trị hoặc dữ liệu mà bạn muốn công thức tính toán.
rows- Số hàng bạn cần tính toán sau khi áp dụng hàm OFFSET, từ điểm bên dưới tham chiếu
chiều cao – Chiều cao yêu cầu của Hàng sẽ được trả lại trong Tham chiếu
width- Chiều rộng cần thiết của cột đó là để được trả lại trong Reference
OFFSET Formula để tìm SUM của 3 hàng cuối cùng:
Cú pháp:
= OFFSET (tham chiếu, hàng, cols, [chiều cao], [chiều rộng])
Loại = OFFSET,
trong tham chiếu: Chọn Bộ nghĩa là A1 ,
Chèn COUNTA với OFFSET trước hàng .
Chọn các giá trị COUNTA tức D1 đến D6 .
Đối với các hàng: Gõ ‘-3’
Đối với cols: Gõ ‘3’
Đối với Chiều cao: Nhập ‘3’
Cho Chiều rộng: Nhập ‘1’
Công thức OFFSET sẽ giống như “= OFFSET (A1, COUNTA (D1: D6) -3, 3,3,1) ”
CHÚ THÍCH 1: Số đếm cột / hàng cho công thức OFFSET bắt đầu bằng 0.
CHÚ THÍCH 2: Counta với hàm OFFSET sẽ đếm giá trị mà chúng ta sẽ thêm vào trong các ô không trống.
Trong Thương hiệu mẫu và tháng 5 của chúng tôi là Hàng và Cột phản hồi mà chúng tôi đã để trống.
Chúng tôi đã chọn Giá trị hàng là 3 vì chúng tôi muốn SUM của 3 Giá trị hàng
Chúng tôi đã chọn Giá trị cột là 3 khi Số đếm từ Cục đến tháng 3 là 0-3
Điều này sẽ cung cấp cho bạn một lỗi #VALUE! LỖI
Chú ý: Bây giờ, đây là sai lầm mà mọi người đều mắc phải.
WRONG “= OFFSET (A1, COUNTA (D1: D6) -3,3,3,1)”
“Bạn cần chèn Công thức cho SUM trước OFFSET”.
Chèn Công thức SUM trước Công thức Bù đắp “= SUM (OFFSET (A1, COUNTA (D1: D6) -3,3,3,1))”
Bây giờ công thức OFFSET trong Excel sẽ giống như sau:
QUYỀN: “= SUM (OFFSET (A1, COUNTA (D1: D6) -3,3,3,1))”
Điều này sẽ cung cấp cho bạn Giá trị 115.
(115 là SUM của 3 hàng cuối cùng của tháng 3)
Thêm vào 50 trong cột cuối cùng của MARCH e. nhân viên để xây dựng thương hiệu trong tháng 3 .
Hàm SUM ngay lập tức thay đổi thành 125
Hiện tại, hàm OFFSET trả về giá trị SUM đã thay đổi (40 + 35 + 50)
Nó không phải là phép thuật sao?
Đây là lợi ích của việc sử dụng chức năng Counta với chức năng bù đắp trong Excel.
Hãy để chúng tôi kiểm tra, làm thế nào để tìm:
OFFSET Chức năng tìm trung bình của 4 cột cuối cùng của DATA của bộ phận:
Cú pháp:
= OFFSET (tham chiếu, hàng, cols, [chiều cao], [chiều rộng])
Nhập = AVERAGE, chọn OFFSET bên trong khung,
trong tài liệu tham khảo: Chọn Sở tức là A1 ,
Đối với các hàng: Nhập ‘4’ . Chèn COUNTA với OFFSET trước cột )
Chọn các giá trị COUNTA tức A5 để F5 . Nhập ‘-4’ (khi chúng tôi đếm 4 Cột)
Đối với chiều cao: Nhập ‘1’
Đối với chiều rộng: Loại ‘4’
CHÚ THÍCH 1: Số đếm cột / hàng cho hàm OFFSET bắt đầu bằng 0.
CHÚ THÍCH 2: Đếm bằng OFFSET sẽ đếm giá trị mà chúng ta sẽ thêm vào trong các ô không trống.
Chúng tôi đã chọn Giá trị hàng là 4 khi Số từ Bộ đến tháng 4 là 0-4
Chúng tôi đã chọn Giá trị cột là 4 vì chúng tôi cần mức trung bình của 4 cột có chức năng bù đắp
Bây giờ công thức OFFSET trong Excel sẽ giống như sau:
= AVERAGE (OFFSET (A1,4, COUNTA (A5: F5) -4,1,4))
Điều này sẽ cung cấp cho bạn Giá trị 30.5
(30.5 là trung bình của 4 cột dữ liệu cuối cùng)
Thêm 40 vào hàng cuối cùng của dữ liệu e. các nhân viên cho dữ liệu trong tháng của tháng .
Hàm SUM ngay lập tức thay đổi thành 35,5
Hiện tại, hàm OFFSET trả về giá trị trung bình thay đổi (30 + 35 + 37 + 40)
Giá trị thay đổi ngay lập tức khi chúng tôi đã sử dụng chức năng Counta với chức năng bù đắp trong Excel.
Mời bạn xem thêm: 26 bài tập excel có lời giải && chuyển powerpoint sang video && phần mềm sắp xếp tên theo abc trong excel
Xem thêm ngay và luôn
⇒ Công Ty TNHH Thương Mại Vận Tải Đắc Khải
⇒ Công Ty TNHH Mtv Tm Dv Sơn Hải Núi Thành
⇒ Công Ty Cổ Phần Abn
Tags:
bài tập về hàm offset ;
hàm offset nâng cao ;
hàm offset trong vba ;
Bình luận
Các bài viết mới
Các tin cũ hơn
