Xem nội dung
Hàm OFFSET của Excel cho tính toán động – Được giải thích bằng các bước đơn giản
Offset là một công thức thú vị và nó là một công thức có thể làm được nhiều hơn những gì bạn thấy. Offset là một cách cung cấp cho Excel một địa chỉ để truy cập
Bạn bắt đầu bằng cách cho nó biết cần di chuyển bao nhiêu hàng và sau đó di chuyển bao nhiêu cột để đến đích. Điểm đến này có thể là một ngôi nhà đơn lẻ [e. g. một ô], nó có thể là một đường phố [e. g. nhiều ô trong một hàng hoặc một cột] hoặc cũng có thể là một thị trấn [e. g. một khu vực của các hàng và cột]
Độ lệch Excel. Dữ liệu được sắp xếp theo chiều dọc [0. 45]
Trong ví dụ này, chúng tôi có một bảng có Tháng trong cột đầu tiên và Doanh thu bán hàng trong cột thứ hai
Một cách để lấy giá trị trung bình của sáu tháng qua là sử dụng hàm AVERAGE. Để làm điều đó, bạn đánh dấu các ô bên dưới cột Doanh số
Tuy nhiên, vấn đề là với một báo cáo động như báo cáo này, sẽ có dữ liệu hàng tháng mới sau tháng 8 và do đó, bạn sẽ phải kéo phạm vi của hàm AVERAGE theo cách thủ công để tham chiếu sáu tháng gần đây nhất
Hàm OFFSET giúp bạn không phải làm điều này
Để bắt đầu, chúng ta hãy tự tìm hiểu hàm OFFSET trước khi chuyển sang tích hợp nó trong hàm AVERAGE. Cú pháp của OFFSET là
=OFFSET[tham chiếu, hàng, cột, [chiều cao], [chiều rộng]]
Hãy làm một ví dụ đơn giản
Bạn luôn cần một điểm xuất phát để tham khảo. Chọn bất kỳ ô nào gần với phạm vi mà bạn muốn di chuyển trong đó. Trong ví dụ này, chúng ta có thể chọn ô B3, là tiêu đề cột của cột Doanh số
Sau đó, cho biết bạn muốn di chuyển xuống bao nhiêu hàng. Giả sử tôi muốn di chuyển xuống 1 hàng. Sau đó, chỉ định số lượng cột bạn muốn di chuyển. Tôi muốn ở cột này, tôi sẽ gõ 0
Hai đối số cuối cùng là [height] và [width]. Viết 1 cho chiều cao và 1 cho chiều rộng, có nghĩa là tôi muốn có một ô. Hai đối số cuối cùng này không bao giờ có thể là 0,0. Tối thiểu chúng có thể là 1,1. Nếu bạn muốn tham chiếu đến ba hàng, bạn sẽ viết 3,1
Đối với ví dụ này, hãy bắt đầu với việc sử dụng 1,1. Công thức của tôi ở giai đoạn này trông như thế này
=OFFSET[B3,1,0,1,1]
Bạn sẽ nhận thấy rằng kết quả của công thức là 100. Điều gì xảy ra là nó bắt đầu từ ô B3 và đi xuống 1 hàng, sau đó nó không di chuyển cột, sau đó nó cho chúng ta một ô. Điều này mang lại cho chúng tôi 100
Nếu bạn đặt tham số hàng thành 3, nó sẽ di chuyển xuống ba hàng và cung cấp cho bạn 130
=OFFSET[B3,3,0,1,1]
Đối với tham số cột, đặt -1 sẽ di chuyển một cột sang trái và trả về ngày trong ô A6. Sử dụng +1 cho tham số cột sẽ di chuyển một cột sang bên phải và trả về 0 vì ô C6 trống. Đây là cách OFFSET hoạt động
Nếu chúng tôi muốn sử dụng một phạm vi thay vì một ô, chúng tôi sẽ chỉ định một giá trị lớn hơn 1 [hoặc nhỏ hơn -1] cho tham số [height]. Viết cú pháp như
=OFFSET[B3,3,0,3,1]
Điều này dẫn đến một lỗi. Tại sao? . Nó bắt đầu từ ô B3, di chuyển xuống ba ô đến B6 và sau đó nó cố gắng cung cấp cho chúng tôi các ô B6, B7 và B8. Tuy nhiên, nó thực sự không thể đặt ba ô này trong một ô
Bất cứ khi nào hàm OFFSET được sử dụng theo cách mà hai đối số cuối cùng là phạm vi và chúng không chỉ trả về một ô mà là một phạm vi ô, thì bạn cần đưa công thức OFFSET của mình vào một công thức có thể xử lý các phạm vi. Ví dụ về điều này sẽ là công thức AVERAGE, SUM hoặc COUNT
Trong trường hợp này, chúng tôi sẽ sử dụng công thức AVERAGE. Chức năng bây giờ sẽ là
=AVERAGE[OFFSET[B3,3,0,3,1]]
Kết quả là 108, là giá trị trung bình của các ô B6, B7 và B8. Nếu chúng tôi mở rộng công thức này để có được sáu tháng qua, chúng tôi phải viết công thức khác
Một cách là xác định ô cuối cùng trong cột bằng hàm COUNT, sau đó sử dụng -6 làm [chiều cao], điều này giúp chúng tôi đánh dấu phạm vi của mình "ngược"
Một cách khác là tìm ô cuối cùng, sau đó quay lại 6 hàng rồi chỉ định [chiều cao] là +6. Nó có vẻ khó hiểu khi bắt đầu, vì vậy trước tiên hãy bắt đầu với phương pháp đơn giản hơn
Bắt đầu bằng cách sử dụng ô B3 làm tham chiếu của bạn. Chúng ta nên di chuyển xuống bao nhiêu ô?
Công thức COUNT chỉ tính nếu giá trị là số
Hàm COUNTA đếm một ô ngay cả khi đó là văn bản
Trong trường hợp này, việc chúng ta sử dụng hàm COUNT hoặc COUNTA không quan trọng
Sử dụng hàm COUNT, chúng tôi tô sáng các ô bên dưới B3. Điều cần thiết là làm nổi bật nhiều ô hơn những gì bạn hiện đang sử dụng vì bạn muốn tính đến dữ liệu mới sẽ được thêm vào trong tương lai. Công thức bây giờ sẽ là
=AVERAGE[OFFSET[B3,COUNTA[B4. B15],0,-6,1]]
Đây là sự phá vỡ của công thức
- Tham chiếu là ô B3
- Tham số hàng sử dụng hàm COUNTA dẫn đến kết quả là 7. Điều này báo cho OFFSET di chuyển 7 ô bên dưới B3
- Tham số cột được đặt thành 0, có nghĩa là nó nằm trong cùng một cột
- Tham số [Height] là -6 vì chúng tôi muốn lấy 6 ô cuối cùng [di chuyển ngược lại]. Trong trường hợp này, nó sẽ đánh dấu các ô từ B5 đến B10
- Thông số [Width] là 1 theo mặc định
Công thức này cho kết quả là 113
Bây giờ, khi chúng ta thêm dữ liệu cho một tháng mới trên ô B11 – giả sử là 200 – công thức sẽ tự động cập nhật thành giá trị trung bình là 127, chúng ta có thể kiểm tra giá trị này bằng cách so sánh giá trị này với giá trị trung bình được hiển thị trên thanh trạng thái khi các ô từ B6 đến B11 là
Chúng tôi cũng có thể kiểm tra điều này bằng cách đi tới Công thức > Đánh giá Công thức, rồi bấm vào nút Đánh giá. Bạn sẽ thấy rằng phạm vi cuối cùng mà nó sử dụng là mức trung bình từ B6 đến B11, đó là những gì chúng tôi dự định
Cách khác để viết công thức bù trung bình này là
=AVERAGE[OFFSET[B4,COUNT[B4. B14]-6,0,6,1]
Sự cố của điều này là
- Tham chiếu được đặt thành giá trị dữ liệu đầu tiên, B4
- Hàng sử dụng hàm COUNTA đếm tất cả các ô đã điền từ B4 đến B14 và trừ 6
- Cột là 0, cho biết không có chuyển động hoặc dịch chuyển của cột
- [Height] là 6, làm nổi bật 6 ô [đi xuống]
- [Width] mặc định là 1, vì chúng ta chỉ cần một cột
Điều này cũng dẫn đến 127. Đây là hai cách khác nhau để viết công thức
Hãy lấy một ví dụ khác trong đó dữ liệu được sắp xếp theo chiều ngang
Độ lệch Excel. Dữ liệu được sắp xếp theo chiều ngang [9. 01]
Để thực hiện tính toán tương tự, chúng tôi làm theo một cách tiếp cận tương tự. Chúng ta bắt đầu với hàm AVERAGE theo sau là hàm OFFSET. Chúng tôi viết nó như
=AVERAGE[OFFSET[A17,0,COUNT[B17. O17],1,-6]]
mà dịch là
- Tham chiếu được đặt thành A17
- Hàng được đặt thành 0, cho biết không có chuyển động. Chúng tôi muốn ở trên cùng một hàng
- Tham số cột sử dụng hàm COUNT để đếm số lượng ô có giá trị từ cột B đến O. Cột O là ước tính nơi bạn nghĩ mình sẽ có số. Phần này cho chúng ta điểm 7
- [height] được đặt thành 1 vì chúng tôi chỉ tham chiếu đến một hàng
- [chiều rộng] được đặt thành –6, làm nổi bật 6 cột ở bên trái của ô hoàn thành cuối cùng [đi ngược lại]
Điều này đạt được giống như lấy trung bình của sáu tháng qua từ tháng Bảy. Nếu chúng tôi thêm lại dữ liệu cho tháng 8, dữ liệu sẽ tự động cập nhật để bao gồm tháng 8 và sau đó tính trung bình của sáu tháng qua. Kết quả là 127
Chỉ cần sáng tạo và thực hành điều này theo mọi hướng cho đến khi bạn hiểu rõ về nó
Độ lệch Excel. Phạm vi động để tính toán trung bình động [10. 42]
Một ví dụ điển hình khác là trường hợp bạn cho phép người dùng chọn tháng và công thức của bạn cung cấp giá trị trung bình của 3 tháng tiếp theo
Đây là dữ liệu thô của bạn
Để thực hiện phép tính này, chúng ta sẽ làm điều tương tự bằng cách sử dụng hàm AVERAGE cùng với OFFSET
Hãy sửa điểm xuất phát của chúng ta thành tiêu đề cột, B25
Đối với hàng, chúng tôi muốn di chuyển xuống số lượng ô để đến tháng đã chọn mà người dùng đã chọn từ trình đơn thả xuống. Hàm MATCH sẽ cung cấp cho chúng ta vị trí của tháng đã chọn trong bảng dữ liệu
Ví dụ chọn tháng 04/2014 tương ứng với 90 thì công thức cần tìm vị trí của 90 trong bảng dữ liệu là hàng thứ 4. Để sử dụng hàm MATCH[], cú pháp là
=MATCH[giá_trị_tìm_kiếm, mảng, [loại_so khớp]]
Chúng tôi gọi ô thả xuống, F25, là giá trị tra cứu và cột Tháng A26. A51 là mảng. [match_type] được đặt 0 để khớp hoàn hảo. Công thức sẽ đọc
=MATCH[F25,A25. A51,0]
Điều này sẽ tính vị trí của tháng đã chọn. Trường hợp tháng 04/2014 ra kết quả là 4
Cột có thể được đặt thành 0 vì chúng tôi muốn ở lại cột B
[Chiều cao] phụ thuộc vào câu hỏi. Nếu chúng tôi muốn bao gồm tháng đã chọn trong tính toán, chúng tôi sẽ sử dụng 3
[Chiều rộng] được đặt thành 1
Công thức kết quả là
=AVERAGE[OFFSET[B25,MATCH[F25,A26. A51,0],0,3,1]
mang lại 102
Nếu chúng tôi không muốn bao gồm tháng đã chọn trong phép tính, chúng tôi phải thêm +1 vào hàm MATCH của mình. Điều này cho biết công thức tìm tháng đã chọn này và đi xuống một ô bổ sung. Công thức cuối cùng trở thành
=AVERAGE[OFFSET[B25,MATCH[F25,A26. A51,0]+1,0,3,1]
Sản lượng trung bình là 113, là trung bình của ba tháng sau tháng đã chọn, i. e. trung bình của B30. B32
Điều này hoàn toàn động, vì vậy nếu bạn chọn tháng 12 năm 2014, bạn sẽ nhận được trung bình 232 trong ba tháng sau tháng này
Đây là cách bạn có thể sử dụng đồng thời hàm AVERAGE, hàm OFFSET và hàm MATCH để tạo hiệu ứng động này trong các tệp Excel của mình
thận trọng
Xin lưu ý rằng OFFSET là một công thức Excel dễ thay đổi, có nghĩa là nó sẽ tính toán mỗi khi bạn thực hiện một bước di chuyển trong Excel. Thành thật mà nói, trong các tệp mà tôi đã sử dụng OFFSET trong, tôi hầu như không gặp phải tình trạng tệp của mình bị chậm do sử dụng chức năng này. Nhưng tôi cũng không dùng quá liều. Tôi khuyên bạn không nên lạm dụng chúng