Sử dụng OFFSET để tham chiếu hyperblock

Chủ đề này mô tả hàm OFFSET. Hàm này đề cập đến báo cáo mẫu RP006 trong ứng dụng Mẫu.

Hàm OFFSET không dành riêng cho Application Studio nhưng đặc biệt hữu ích khi làm việc với hyperblock. Điều này là do, trong Chế độ xem, hyperblock là động. Trong Chế độ thiết kế, hyperblock là tĩnh. Nghĩa là, nó chiếm một phạm vi khối cụ thể. Nhưng ở Chế độ xem, hyperblock mở rộng và ràng buộc với nội dung của nó, làm cho các tham chiếu ô cụ thể trở nên vô nghĩa. Vì vậy, nếu bạn có một công thức bên ngoài hyperblock phải tham chiếu đến các ô bên trong hyperblock thì bạn cần một công thức động.

Với OFFSET, bạn xác định các ô bằng cách xác định vị trí của chúng so với ô tham chiếu ban đầu. Bạn xác định vị trí của chúng với các tọa độ thể hiện khoảng cách, liên quan tới số lượng hàng và cột từ ô tham chiếu ban đầu.

Ví dụ này tham chiếu ô C13:

=OFFSET(F11,2,-3)

Tức là, ô F11 là ô tham chiếu ban đầu. Đối số thứ hai, 2, cho biết ô được tham chiếu ở phía dưới F11 hai hàng. Đối số thứ ba, 3, cho biết ô được tham chiếu ở bên trái F11 3 cột.

Ví dụ này cũng tham chiếu ô C13:

=OFFSET(B21,-8,1)

Tức là, ô B21 là ô tham chiếu ban đầu. Đối số thứ hai, -8 cho biết ô được tham chiếu ở phía trên B21 8 hàng. Đối số thứ ba, 1, cho biết ô được tham chiếu ở bên phải B21 1 cột.

Cách sử dụng phổ biến của OFFSET trong Application Studio là khi tính toán các giá trị tích lũy, ví dụ, để sử dụng trong Phân tích ABC hoặc biểu đồ Pareto. Để tính toán giá trị tích lũy, bạn thêm giá trị của ô hiện tại vào giá trị của ô phía trên. Nhưng trong môi trường động của hyperblock, không thể chỉ định ô ở trên bằng tham chiếu ô tĩnh.

Báo cáo RP006 có một ví dụ về cách sử dụng OFFSET để tính toán giá trị tích lũy.

Báo cáo RP006 có hyperblock, được tạo từ Chiều sản phẩm của Khối phân tích trong Ứng dụng mẫu.

Hyperblock có hai ô giá trị. Trong ô giá trị đầu tiên, công thức ROC hiển thị giá trị cho từng nhóm sản phẩm. Trong ô giá trị thứ hai là công thức OFFSET.

Trong Chế độ xem, ô sao chép các giá trị trong ô giá trị đầu tiên. Điều này là do công thức OFFSET không chính xác.

Công thức trong ô E5 là:

=OFFSET(E5,0,0)+D5

Để thay đổi công thức OFFSET, tức là hiển thị giá trị tích lũy:

  1. Trong Chế độ thiết kế, nhấp vào ô E5.
  2. Trong Trình soạn thảo công thức, thay đổi đối số thứ hai của công thức OFFSET từ 0 thành -1. Tức là, thay đổi công thức thành: = OFFSET (E5,-1,0)+D5 Tại đây, -1 xác định 'hàng ở trên'.

    Trong Chế độ xem, các giá trị tích lũy và nhãn Tích lũy được hiển thị. Cũng có thể đạt được điều này bằng cách sử dụng hàm OFFSET trong ô E3 - lồng vào câu lệnh IF.

    Câu lệnh IF là: =IF(OFFSET(E3,3,0)=OFFSET(E3,3,-1),"","Cumulative")

    Chúng ta biết rằng giá trị trong hàng đầu tiên của kết quả luôn bằng nhau. Tuy nhiên, nếu giá trị tích lũy được hiển thị thì giá trị trong tất cả các hàng khác đều không bằng nhau. Vì vậy, câu lệnh IF kiểm tra xem giá trị trong hàng 2 có bằng nhau hay không.

    Câu lệnh IF cần được hiểu rằng: Nếu giá trị của ô ở phía dưới 3 hàng so với E3 bằng giá trị trong ô ở phía dưới 3 hàng và một cột về phía bên trái của E3 thì không hiển thị giá trị nào (""). Hoặc hiển thị "Tích lũy".