Więcej o funkcjach

Uff! W poprzednim odcinku o formułach i funkcjach tak namotałem, że cała wakacyjna formuła tego cyklu jest zagrożona. Wyobrazić sobie, że któraś z wiernych czytelniczek Gazety Prawnej rozważa na plaży niuanse adresów względnych i kopiowania formuł!

Teraz (jest połowa czerwca), gdy wprowadzam kolejny przykład na kopiowanie formuł z adresami względnymi i bezwzględnymi, cały czas mam przed oczami taką plażę w Sobieszewie i kogoś ... sympatycznego, który sobie kupił w kiosku GP i ponieważ nasi prawodawcy też są na urlopie, postanowił wreszcie zgłębić ten nieprzenikniony temat wypełniania wielkich arkuszy jedną uniwersalna formułą....

Jeśli ktoś przeczytał poprzedni artykuł i jeszcze wziął do ręki niniejszy, to ....zasługuje, żeby go mniej nudzić a w każdym razie sprawić, żeby odczuł korzyści z podjętego wysiłku. Podejmuję to zobowiązanie.

Skoro bowiem umiemy napisać formułę na obliczenie podatku VAT w taki sposób, żeby skopiować ją w dowolne miejsce arkusza, to otwiera się przed nami ogrom możliwości analiz i Excel przestaje być programem do tworzenia tabelek z podsumowaniami.

Cały czas mamy na względzie nasz cel: Samodzielnie zbudować arkusz budżetu sparametryzowany ze względu na pozycje budżetu i na okresy rozliczeniowe – jedną formułą!

Rys. 1 Nasz cel – jużodrobine bliższy: Wykonanie budżetu

Aby się zbliżyć o następny krok do naszego celu musimy poznać jeszcze kilka funkcji.

Po tym męczącym przygotowaniu teraz pójdzie nam nieco szybciej: podawanie wartości argumentów funkcjom powinniśmy przyjmować bez oporów, ustalanie czy adresy parametrów są względne, czy bezwzględne jeszcze się będzie zacinało, ale kolejne przykłady utrwalą wiedzę. Spróbujemy?

Poznaliśmy na razie funkcje matematyczne Suma() – to było pewnie lepsze poznanie starego znajomego i Zaokr() czyli funkcję zaokrągleń, funkcję daty i czasu - bezargumentową Teraz() i jedną funkcję -ciekawostkę dla badaczy rzymskich rękopisów, żeby pokazać, że Excel nie tylko potrafi pomóc księgowym i controllerom.

Na tych funkcjach oraz na formułach dodawania, mnożenia i dzielenia wprawdzie takiego arkusza budżetu zbudować się nie da, ale niejeden wirtuoz gry na jednej strunie buduje piękne arkusze – analizy i sprawozdania. Excel jest jednak instrumentem wielostrunowym, ma około 450 funkcji a istnieje rynek dodatków z nieprzebranym bogactwem następnych.

Nie pretendujemy do nawet pobieżnego przeglądu, chcemy jednak zapoznać Państwa z jeszcze kilkoma funkcjami. Reszta zależy już od Was.

Przykłady innych funkcji matematycznych:

Co bardziej spostrzegawczy czytelnicy już wiedzą, czeo się chcą dowiedzieć: w pasku formuły rys. 1 widać funkcję Suma.Jeżeli().

Dobrze, bierzmy byka za rogi.

Na razie zbudujmy sobie małą tabelkę z danymi: na razie nie będziemy się interesowac, skąd się one wzięły w Excelu. Powiedzmy, że ktoś je nam do Excela przeniósł.

Rys. 2 Dane w Excelu do przeanalizowania.

Powiedzmy, że mamy takie dane i chcemy w Excelu sporządzić ich analizę. Funkcja Suma nadaje się tylko do podsumowania całości, ale jak dowiedzieć się, ile wynosi suma kolumny KwotaWn dla KontaWn 301 ? Jest kilka sposobów. Poznamy od razu najlepszy – to jest korzyść czytania GP na plaży.

Właśnie funkcja Suma.Jeżeli() nadaje się do tego idealnie.

Jest to funkcja o trzech argumentach:

Pierwszy – podaje zakres komórek, w których są kryteria wyboru elementów do sumowania – to kolumna A – w której podane są dla każdego wiersza wartości kontoWn. Ten zakres najlepiej podać w najogólniejszej formie: A:A co oznacza cała kolumnę. Nie trzeba się obawiać, że to da Excelowi pracę przeglądania ponad 65 tys. Wierszy. Excel potrafi szybko sumować i przeszukiwać całe kolumny, ponieważ wie, która część arkusza jest pusta.

Drugi – podaje wartość kryterium, dla której chcemy otrzymać sumę. Powiedzieliśmy, że nas interesuje kontoWn 301.

Trzeci – podaje zakres komórek, które chcemy posumować, gdy kryterium z drugiego argumentu jest spełnione. To kolumna C zawierająca kwoty WN dla każdego kontaWN.

Najpierw podamy formułę w postaci jawnej:

=Suma.Jeżeli(A:A;”301”;C:C)

Wynik w komórce: 328,24

Ta formuła ma jednak wadę: jest ważna tylko w arkuszu, w którym znajdują się dane.

Arkusz z danymi nazywa się Styczeń. Jeżeli utworzymy nowy arkusz (klawisz Shift-F11, żadnych klikań po menu, jesteśmy profesjonalistami!) o nazwie Obrotówka (tu już trzeba kliknąć dwukrotnie na nazwie arkusza i wpisać nową nazwę) i tam przeniesiemy (Uwaga: przenosimy, nie – skopiujemy – Ctrl-X w miejscu skąd przenosimy – Ctrl-V- w miejscu dokąd przenosimy, zamiast Ctrl-C – Ctrl-V) naszą formułę, to Excel ją sam zmodyfikuje do postaci:

=Suma.Jeżeli(Styczeń!A:A;”301”;Styczeń!C:C)

Wynik w komórce: 324,24

Ten wynik jest taki sam niezależnie od tego, do której komórki nowego arkusza przeniesiemy komórkę w arkuszu. To ważne: tak działa przenoszenie- nie zmienia formuł, nawet bez znaków $!. Excel traktuje wówczas wszystkie adresy jako bezwzględne. Sam dołożył nazwę arkusza do pierwszego i trzeciego argumetu, żeby znaleźć dane do wyliczenia funkcji.

Mimo to, formuła jest dalej niedoskonała: kopiowanie jej w nowym arkuszu do innych kolumn będzie powodowało zmianę adresów kolumn w argumentach pierwszym i trzecim. Brak tam jest przecież znaków $.

Uzupełnijmy więc formułę:

=Suma.Jeżeli(Styczeń!$A:$A;”301”;Styczeń!$C:$C)

Wynik w komórce: 324,24

Teraz już trochę lepiej, jeszcze trochę do końca.

Zanim jednak pójdziemy dalej, kilka uwag o tych argumentach. Proszę zwrócić uwagę, że argumenty pierwszy i trzeci są napisami bez cudzysłowu, zaś argument drugi ma cudzysłów.

Jest to dobra okazja, żeby powiedzieć o różnicy między typami tych argumentów. Dwa skrajne argumenty są typu ADRES, zaś środkowy jest typu TEKST.

To logiczne: skrajne argumenty podają zakresy komórek, po których Excel ma się poruszać, aby wykonać to, co wynika z funkcji, która oblicza. Środkowy argument podaje konkretny tekst do wykorzystania.

Tu napotykamy pewne ograniczenie Excela. Gdy wpisujemy adres „z ręki, lub pokazując myszką zakres adresów, potrafi zamienić pisany przez nas tekst na adres, natomiast nie potrafi on zamienić tekstu zawartego w innej komórce na adres „w locie”, więc nie możemy tak prosto sparametryzować odwołania do arkusza danych, jak do konta.

Na razie więc poprzestańmy na danych stycznia i sparametryzujmy przynajmniej odwołanie do konta. To już każdy czytelnik, który przebrnął przez poprzedni artykuł, powinien potrafić, ale dla pewności powtarzam:

W kolumnie A arkusza Obrotówka wypisujemy kolejno interesujące konta:

Formuła nasza zmieni się teraz następująco:

=Suma.Jeżeli(Styczeń!$A:$A;A1;Styczeń!$C:$C)

Wynik w komórce B1: 1600,50

Rys.3 Obrotówka dla stycznia prawie gotowa...

Znów powstaje problem z dolarami: jeśli chcemy kopiować te formułę do kolumn na prawo, należy „zamrozić” adres kolumny A. Jeśli chcemy sporządzić naszą obrotówkę w układzie miesięcy, to takie skopiowanie będzie konieczne.

=Suma.Jeżeli(Styczeń!$A:$A;$A1;Styczeń!$C:$C)

Wynik w komórce B1: 1600,50

Oczywiście skopiowanie formuły w dół powinno skutkować zmianą numeru wiersza: konto zawsze bierzemy z tego samego wiersza, co formuła wyliczająca stan tego konta w m-cu, dlatego numeru wiersza nie zamrażamy.

Pozostaje ostatni krok, sparametryzować odwołania do miesięcy.

W tym celu trzeba użyć specjalnej funkcji typu wyszukiwania i adresu która potrafi zamienić tekst na adres.

Wiemy juz jak powinien