Adres pośredni (OpenOffice)

Czasem, gdy bawię się różnymi statystykami, chciałbym zmienić przykładowo zakres danych, z których obliczana jest średnia. Nie jest to jakiś wielki problem, ale gdy odpowiednich formuł jest więcej sprawa staje się trochę pracochłonna. Przydałaby się wtedy możliwość jakiejś parametryzacji przekazywanych do funkcji adresów.

Niestety z tego co mi wiadomo, OpenOffice nie przewiduje takiej możliwości – nie bezpośrednio w każdym bądź razie. Na szczęście posiada on bardzo przydatną funkcję:

ADR.POŚR( Odwołanie )

Funkcja ta posiada jeszcze jeden parametr, jest on jednak opcjonalny i jest całkowicie nieistotny.

Funkcja jest bardzo prosta w użyciu: jako odwołanie podajemy jej po prostu ciąg tekstowy który jest adresem komórki. Zwracany jest adres komórki. Przykład:

ADR.POŚR(”A1”)

Funkcja zwróci adres A1. Jeśli wpiszemy ją bezpośrednio w komórkę, to tak jakbyśmy wpisali '=A1' czyli w tym przypadku rezultat będzie zawartością komórki A1 (np. jakąś liczbą).

ADR.POŚR(A1)

Tu widzimy subtelną różnicę – przekazując funkcji adres komórki, mówimy jej że wymagany przez nią ciąg znaków znajduje się w podanej komórce (A1). Jeśli w komórce A1 będzie ciąg tekstowy oznaczający adres komórki (przykładowo ”B1”), to funkcja zwróci właśnie ten adres (tj. B1). Jeśli w komórce A1 będzie jakaś liczba, data czy inny ciąg znaków nie będący poprawnym adresem, funkcja zwróci błąd 502 (nieprawidłowy argument).

Zanim przejdę do przykładu zastosowania, chciałbym zwrócić uwagę na jedną rzecz. Mianowicie adresem może być też zakres komórek – jest to ważne właśnie gdy obliczamy przykładowo średnie:

ADR.POŚR(”A1:A3”)

Teraz już mogę przejść do konkretnego zastosowania. Na przykładzie: mam jakiś zestaw liczb i chcę każdej liczbie przyporządkować średnią z pewnego zakresu otaczającego tę liczbę (np. z poprzedniej, bieżącej i następnej liczby) – innymi słowy chciałbym wygładzić wykres (tzw. średnia ruchoma). Ale chciałbym wypróbować tę procedurę dla różnych rozmiarów zakresów.

Normalnie mógłbym po prostu zmienić ręcznie formułę (wystarczy w pierwszym wierszu – potem można ją skopiować), ale zmienianie w tę i we w tę jest trochę czasochłonne i mija się z celem używania arkuszy kalkulacyjnych (które służą do automatyzacji obliczeń). Mógłbym też zrobić kilka kolumn z różnymi zakresami – ale zajmują one dodatkowe miejsce, ich zrobienie też zajmuje czas a ostatecznie okazują się one niepotrzebne, gdy już zdecyduję się na jeden konkretny zakres.

I tu przydaje się funckcja ADR.POŚR(). Musimy sobie zarezerwować przynajmniej jedną komórkę, która będzie zawierała wielkość zakresu (ale mając do dyspozycji taką swobodę możemy sobie zrobić dwie zmienne, które będą określały zakres osobno z każdej strony).

Przykładowo mając dane w kolumnie A oraz średnie w komórce B, zarezerwujmy sobie komórkę C1 jako wielkość zakresu. Wtedy formuła średniej będzie wyglądać następująco:

=ŚREDNIA( ADR.POŚR( "A" & WIERSZ()-C$1 & ":A" & WIERSZ()+C$1 ) )

Objaśnienie:

• funkcja WIERSZ() zwraca wiersz bieżącej komórki (można podać jako argument adres konkretnej komórki – wtedy zwraca ona wiersz w którym znajduje się ta komórka);
• znak '&' oznacza konkatenację (złączenie) ciągów tekstowych;
• użyłem adresu bezwzględnego C$1 aby nie zmienił się on przy kopiowaniu komórek.

Teraz manipulując tylko i wyłącznie wartością w komórce C1 możemy szybko i łatwo zmieniać parametry średniej i oglądać sobie na wykresie otrzymane rezultaty.

Ta metoda ma też oczywiście swoje wady:

1. tak wpisany adres jest mało czytelny;
2. OpenOffice nie podświetla odpowiednich komórek tak, jak miałoby to miejsce w przypadku bezpośredniego wpisania adresu;
3. Nie jest to istotna wada, ale jeśli zostawimy formułę identyczną dla wszystkich komórek (nasz cel – jak największa automatyzacja), to skrajne wartości będą zakłamane. Można co prawda poprawić tę formułę za pomocą funkcji JEŻELI(), MAKS() i MIN(), ale w większości przypadków nie jest to konieczne (poza tym dodatkowo komplikujemy w ten sposób naszą formułę – nieznacznie, co prawda, ale jednak). Przykładowo jeśli dane znajdują się w wierszu od 1 do 100, to możemy zabezpieczyć formułę w następujący sposób:

=ŚREDNIA( ADR.POŚR( "A" & MAKS(WIERSZ()-C$1;1) & ":A" & MIN(WIERSZ()+C$1;100) ) )

W przypadku, gdy będziemy stopniowo dopisywać wiersze, można użyć różnych metod do obliczenia maksymalnego wiersza:

• Można użyć przykładowo funkcji ILE.NIEPUSTYCH() z jakimś dużym zakresem. Problem pojawi się, gdy mimo to dojdziemy do końca owego zakresu. Wtedy pewną metodą jest dodawanie wierszy na nowe dane w objętym przez tę funkcję zakresie (arkusz automatycznie zmieni sobie wtedy wielkość zakresu).
• Podobną metodą jest ustawienie tzw. strażnika na końcu kolumny danych. Podobnie jak wyżej będziemy musieli wtedy dodawać wiersze powyżej strażnika. Maksymalny zaś wiersz zakresu z danymi możemy obliczyć wtedy z wykorzystaniem funkcji WIERSZ().

Jeśli jednak w danej kolumnie nie ma już żadnych innych danych – można sobie takie ograniczenia całkowicie darować.

* * *

Przez długi czas używałem funkcji ADR.POŚR() i okazała się ona dla mnie bardzo użyteczna – dlatego o niej napisałem. Z jakiegoś jednak powodu w okresie, gdy szukałem rozwiązania przedstawionego na wstępie problemu, umknęło mi istnienie innej funkcji dającej podobne możliwości, mianowicie PRZESUNIĘCIE(). Odkryłem ją dopiero niedawno. A jako że teraz będę korzystał raczej z niej właśnie, postanowiłem spisać dla siebie oraz dla innych moje doświadczenia w używaniu obydwu tych funkcji. O funkcji PRZESUNIĘCIE() napiszę jednak następnym razem.