Używanie funkcji OFFSET na potrzeby odwołania do hiperbloków

W tym temacie opisano funkcję OFFSET. Odnosi się on do przykładowego raportu RP006 w aplikacji przykładowej.

Funkcja OFFSET nie jest specyficzna dla Application Studio, jednak jest ona szczególnie użyteczna przy pracy z hiperblokami. Jest tak, ponieważ w trybie widoku hiperbloki są dynamiczne. W trybie projektowania hiperblok jest statyczny. Oznacza to, że zajmuje określony zakres komórek. W trybie widoku hiperblok rozwija się i zwija wraz z zawartością, w wyniku czego określone odwołania do komórek są bezużyteczne. Jeśli formuła znajduje się poza hiperblokiem, który musi odwołać się do komórek w hiperbloku, potrzebna jest formuła dynamiczna.

Przy pomocy funkcji OFFSET wskazuje się komórki poprzez określenie ich położenia względem początkowej komórki odwołania. Ich położenie określa się przy pomocy współrzędnych odpowiadających odległości wyrażonej w liczbie wierszy i kolumn od początkowej komórki odwołania.

W tym przykładzie przedstawiono odwołanie do komórki C13:

=OFFSET(F11,2,-3)

Oznacza to, że komórka F11 jest początkową komórką odwołania. Drugi argument, 2, wskazuje, że komórka, do której wstępuje odwołanie, znajduje się dwa wiersze poniżej komórki F11. Trzeci argument, -3, wskazuje, że komórka, do której występuje odwołanie, znajduje się o trzy kolumny w lewo od komórki F11.

W tym przykładzie również przedstawiono odwołanie do komórki C13:

=OFFSET(B21,-8,1)

Oznacza to, że komórka B21 jest początkową komórką odwołania. Drugi argument, -8, wskazuje, że komórka, do której wstępuje odwołanie, znajduje się osiem wierszy powyżej komórki B21. Trzeci argument, 1, wskazuje, że komórka, do której występuje odwołanie, znajduje się o jedną kolumnę w prawo od komórki B21.

W Application Studio funkcja OFFSET jest powszechnie stosowana w obliczaniu wartości skumulowanej na użytek na przykład Analizy ABC czy wykresów Pareto. W celu obliczenia wartości skumulowanej dodaje się wartość obecnie wybranej komórki do wartości komórki powyżej. Jednakże w środowisku dynamicznym hiperbloku komórki powyżej nie można określić poprzez statyczne odniesienie do komórki powyżej.

Raport RP006 zawiera przykład sposobu użycia funkcji OFFSET do obliczenia wartości skumulowanej.

Raport RP006 zawiera hiperblok utworzony z wymiaru Produktu kostki Analizy aplikacji przykładowej.

Hiperblok zawiera dwie komórki wartości. W pierwszej komórce wartości formuła ROC wyświetla wartość dla każdej grupy produktów. W drugiej komórce wartości znajduje się formuła OFFSET.

W trybie widoku komórka duplikuje wartości z pierwszej komórki wartości. Dzieje się tak, ponieważ formuła OFFSET jest niepoprawna.

Formuła w komórce E5 to:

=OFFSET(E5,0,0)+D5

Aby zmienić formułę OFFSET tak aby wyświetlała wartości skumulowane:

  1. W trybie projektowania kliknij komórkę E5.
  2. W edytorze formuł zmień drugi argument formuły OFFSET z 0 na -1. Innymi słowy zmień formułę na: =OFFSET(E5,-1,0)+D5, gdzie -1 określa „wiersz powyżej”.

    W trybie widoku są wyświetlone wartości skumulowane oraz etykieta Skumulowane. Dzieje się tak również dzięki zastosowaniu funkcji OFFSET w komórce E3 – zagnieżdżonej w instrukcji IF.

    Instrukcja IF to:=IF(OFFSET(E3,3,0)=OFFSET(E3,3,-1),"","Skumulowane").

    Wiemy, że wartości w pierwszym wierszu wyników są zawsze równe. Jednakże w przypadku wyświetlania wartości skumulowanych wartości we wszystkich pozostałych wierszach nie są równe. Instrukcja IF sprawdza, czy wartości w wierszu 2 są równe.

    Instrukcja IF oznacza: Jeżeli wartość komórki znajdującej się trzy wiersze powyżej komórki E3 jest równa wartości komórki znajdującej się trzy wiersze poniżej i o jedną kolumnę w lewo od komórki E3, nie wyświetlaj nic (""). W przeciwnym wypadku wyświetl „Skumulowane”.