OFFSET gebruiken om naar hyperblocks te verwijzen

In dit gedeelte wordt de functie OFFSET besproken. Hierbij wordt gebruikgemaakt van voorbeeldrapport RP006 uit de voorbeeldapplicatie.

De OFFSET-functie is niet specifiek voor Application Studio maar is met name handig bij het werken met hyperblocks. De reden hiervan is dat hyperblocks in de weergavemodus dynamisch zijn. In de ontwerpmodus is een hyperblock statisch. Er wordt dan een specifiek bereik aan cellen bezet. In de weergavemodus groeit en krimpt een hyperblock echter mee met de inhoud, waardoor specifieke celverwijzingen niet zinvol zijn. Dus als u bijvoorbeeld met een formule buiten een hyperblock wilt verwijzen naar cellen binnen het hyperblock, hebt u een dynamische formule nodig.

Met OFFSET identificeert u cellen door hun positie in een relatie tot een referentiecel op te geven. U geeft de positie op met coördinaten die de afstand aangeven in aantal rijen en kolommen vanaf de referentiecel.

In dit voorbeeld wordt verwezen naar cel C13:

=OFFSET(F11,2,-3)

Cel F11 fungeert dus als referentiecel. Het tweede argument 2 geeft aan dat de cel waarnaar verwezen wordt, zich twee rijen onder F11 bevindt. Het derde argument 3 geeft aan dat de cel waarnaar verwezen wordt zich drie kolommen links van F11 bevindt.

In dit voorbeeld wordt eveneens verwezen naar cel C13:

=OFFSET(B21,-8,1)

Cel B21 fungeert dus als referentiecel. Het tweede argument -8 geeft aan dat de cel waarnaar verwezen wordt zich acht rijen boven B21 bevindt. Het derde argument 1 geeft aan dat de cel waarnaar verwezen wordt zich één kolom rechts van B21 bevindt.

De OFFSET-functie wordt veel gebruikt in Application Studio voor het berekenen van cumulatieve waarden voor gebruik in bijvoorbeeld ABC-analyse of Pareto-diagrammen. U kunt een cumulatieve waarde berekenen door de waarde van de huidige cel toe te voegen aan de waarde van de cel daarboven. Maar in de dynamische omgeving van een hyperblock kan de cel erboven niet worden opgegeven met een statische celverwijzing.

Report RP006 bevat een voorbeeld van de manier waarop OFFSET gebruikt kan worden om cumulatieve waarden te berekenen.

Rapport RP006 maakt gebruik van een hyperblock dat is gemaakt vanuit de Product-dimensie van de Analysis-kubus uit de voorbeeldapplicatie.

Het hyperblock geeft twee waardecellen. In de eerste waardecel geeft een ROC-formule een waarde weer voor elke groep producten. De tweede waardecel bevat een OFFSET-formule.

In de weergavemodus dupliceert de cel de waarden van de eerste waardecel. De reden daarvoor is dat de OFFSET-formule onjuist is.

De formule in cel E5 is:

=OFFSET(E5,0,0)+D5

Ga als volgt te werk om de OFFSET-formule zo te wijzigen dat er cumulatieve waarden worden weergegeven:

  1. Klik in de ontwerpmodus op cel E5.
  2. In de Formule-editor verandert u het tweede argument van de OFFSET-formule van 0 in -1. De nieuwe formule wordt dus: =OFFSET(E5,-1,0)+D5, waarbij -1 staat voor 'de rij erboven'.

    In de weergavemodus worden cumulatieve waarden en het label Cumulatief weergegeven. Dit kan ook worden bereikt door een OFFSET-functie in cel E3 te gebruiken, genest in een ALS-instructie.

    De ALS-instructie is: =IF(OFFSET(E3,3,0)=OFFSET(E3,3,-1),"","Cumulatief")

    We weten dat de waarden in de eerste rij van de resultaten altijd gelijk zijn. Als er echter cumulatieve waarden worden weergegeven, zijn de waarden in alle andere rijen niet gelijk. Met de ALS-instructie kan dus worden getest of de waarden in rij 2 gelijk zijn.

    De ALS-instructie laat zich als volgt vertalen: Als de waarde van de cel die zich drie rijen onder cel E3 bevindt, gelijk is aan de waarde in de cel die zich drie rijen onder en één kolom links van E3 bevindt, moet er niets worden weergegeven (""). Anders moet Cumulatief worden weergegeven.