Przesunięcie (OpenOffice)

Poprzednio pisałem o funkcji ADR.POŚR(), która umożliwiała mi parametryzację zakresów. Jak już wspomniałem ostatnim razem, to samo umożliwia funkcja PRZESUNIĘCIE(), jednak robi to w o wiele prostszy sposób. Składnia dla tej funkcji wygląda następująco:

PRZESUNIĘCIE( Odwołanie; Wiersze; Kolumny; Wysokość; Szerokość )

Funkcja zwraca zakres (może być to obszar dwuwymiarowy lub jednowymiarowy, albo po prostu pojedyncza komórka).

• Odwołanie - to adres komórki, względem której będziemy podawać zakres. Jeśli jako odwołanie podamy zakres, wtedy:
  a) jeśli nie podamy Wysokości ani Szerokości, funkcja zwraca zakres o tym samym rozmiarze ale którego początek będzie przesunięty o zadaną ilość Wierszy i Kolumn;
  b) jeśli podamy Szerokość i Wysokość, funkcja zwróci zakres o zadanym rozmiarze obliczony względem lewego-górnego rogu zadanego zakresu
• Wiersze; Kolumny - to względny adres lewego-górnego rogu zakresu;
• Wysokość; Szerokość – to rozmiar zwracanego zakresu (Opcjonalny. Jeśli nie podamy żadnej wartości, rozmiar zakresu się nie zmieni - tj. dla pojedynczej komórki wyniesie on 1).

Ilustracja działania funkcji PRZESUNIĘCIE().
Wykonane przeze mnie za pomocą programów OpenOffice oraz GIMP.

Jak łatwo można się domyśleć, obliczony zakres musi zawierać się w arkuszu kalkulacyjnym (tj. lewy-górny róg nie może mieć współrzędnych mniejszych niż 1).

I to tyle jeśli chodzi o teoretyczne podstawy działania tej funkcji. Teraz postaram się pokazać na przykładzie podobnym jak poprzednio praktyczne jej zastosowanie.

Ponownie załóżmy, że posiadamy dane w kolumnie A i chcemy wpisać średnie w kolumnie B. W komórce C1 zaś umieścimy wielkość zakresu. Wtedy formuła średniej będzie wyglądać następująco (w przykładzie w komórce B1):

= ŚREDNIA( PRZESUNIĘCIE( A1; -C$1; 0; 1+2*C$1; 1) )

Uwaga – dla jasności dodałem na końcu szerokość zakresu równą 1. Możemy ją jednak spokojnie pominąć (jak napisałem wyżej, jest to wartość opcjonalna).

Wysokość przedziału to po prostu: (zakres w jedną stronę) + (1 czyli „środkowa” komórka) + (zakres w drugą stronę). Gdybyśmy w komórce C1 trzymali wielkość przedziału z jednej strony, w komórce C2 zaś wielkość przedziału z drugiej strony, odpowiedni wzór na wysokość wyglądałby tak:

C$1 + 1 + C$2

Jeśli porównać tę formułę ze wzorem stosującym funkcję ADR.POŚR() to widać od razu, o ile prostsza w użyciu jest funkcja PRZESUNIĘCIE().

Jak wspomniałem wyżej, obliczony zakres nie może wykraczać poza granice arkusza kalkulacyjnego. Innymi słowy (dla tego przykładu)

WIERSZ – ZAKRES ≥ 1

czyli

ZAKRES ≤ WIERSZ - 1

co w praktyce można zrealizować za pomocą funkcji MIN() (a nie MAKS() - bo tę wartość chcemy odjąć od współrzędnych danej komórki):

MIN( ZAKRES; WIERSZ()-1 )

Uwaga 1. ZAKRES w tym wyrażeniu nie musi oznaczać wcale zmiennej, gdzie przechowuje się zakres. Jeśli struktura danych jest trochę bardziej złożona, to przechowywany zakres może być tylko pewnym parametrem (może odnosić się do jakiegoś abstrakcyjnego zakresu) a konkretny zakres w arkuszu obliczany jest innym wzorem (np. 1+2*C$1). W takim wypadku pod ZAKRES musimy podstawić właśnie ten wzór.

Uwaga 2. Możemy posiadać w pierwszym wierszu (lub w kilku pierwszych wierszach) opisy danych. W takim wypadku, jeśli dane zaczynają się od wiersza N, powinniśmy zastąpić "WIERSZ()-1" na "WIERSZ()-N" (gdzie N jest pewną konkretną liczbą, rzecz jasna).

Powyższe wyrażenie po podstawieniu do wzoru ze średnią daje nam:

= ŚREDNIA( PRZESUNIĘCIE( A1; -MIN(C$1;WIERSZ()-1); 0; MIN(C$1;WIERSZ()-1) + 1 + C$1; 1) )

Podobne ograniczenie na zasadach wspomnianych w poprzednim artykule możemy dać od dołu zestawu danych, z reguły nie będzie jednak ono potrzebne.

I to właściwie wszystko. Jak napisałem poprzednio zamierzam teraz używać tej funkcji zamiast ADR.POŚR(), gdyż jest ona o wiele prostsza w użyciu i jest też bardziej czytelna.