5. Uniwersalny arkusz budżetu

Zbudujmy tabelkę z danymi:

Rys. 1 Dane w Excelu do przeanalizowania.

 

Ile wynosi suma kolumny KwotaWn dla KontaWn 301 ?

Odpowiedź daje funkcja Suma.Jeżeli() o trzech argumentach:

1 – zakres komórek zawierających kryteria wyboru elementów do sumowania – to kolumna A – w której podane są dla każdego wiersza wartości „kontoWn”. Najlepiej podać w go najogólniejszej formie: A:A, co oznacza cała kolumnę.

2 – wartość kryterium, dla której chcemy otrzymać sumę. Np. kontoWn 301.

3 – zakres komórek, które chcemy posumować, dla danego kryterium (np. konta 301). Np. kolumna C zawierająca kwoty WN dla każdego kontaWN.

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

Wynik w komórce: 328,24

Arkusz z danymi nazywa się Styczeń. Jeżeli utworzymy nowy arkusz (skrót klawiszowy - Shift-F11) o nazwie Obrotówka (kliknąć dwukrotnie na nazwie arkusza i wpisać nową nazwę) i tam przeniesiemy (Ctrl-X w miejscu skąd przenosimy – Ctrl-V- w miejscu dokąd przenosimy) 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

Formuła i jej wynik będą takie same w każdym nowym miejscu, ponieważ przenoszenie nie zmienia formuł, nawet bez znaków $!. Excel dołożył nazwę arkusza do pierwszego i trzeciego argumentu. Brak jej natomiast znaków $.

Uzupełnijmy więc formułę (F2 -edycja, ustawienie się kursorem na adresach A:A i C:C i następnie klawisz F4):

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

Wynik w komórce: 324,24

 

Zwróćmy uwagę, że argumenty pierwszy i trzeci są napisami bez cudzysłowu, zaś argument drugi ma cudzysłów. To dlatego, że skrajne argumenty są typu ADRES (a właściwie są zmiennymi adresowymi), zaś środkowy jest TEKSTEM. Skrajne argumenty podają zakresy komórek, po których Excel porusza się, aby obliczyć wartość funkcji.

Środkowy argument konkretnym tekstem podaje wartość konta.

Sparametryzujmy ten argument odwołaniem do konta w kolumnie C.

To już każdy czytelnik, który przebrnął przez poprzedni artykuł, powinien potrafić, ale dla pewności powtarzam:

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

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

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

Wynik w komórce D3: 1600,50

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

 

Chcemy kopiować tę formułę do kolumn na prawo (inne miesiące!), należy więc „zamrozić” adres kolumny C.             =Suma.Jeżeli(Styczeń!$A:$A;$C3;Styczeń!$C:$C)

Wynik w komórce D3: 1600,50

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.

 

Funkcje wyszukiwania i adresu

Gdy wpisujemy zakres adresów odręcznie (bez cudzysłowu!) lub pokazujemy go myszką, Excel zamienia tekst na zmienną adresową, czyli na argument typu adres. Nie potrafi natomiast zamienić tekstu zawartego w innej komórce na adres, więc nie można wprost parametryzować odwołania do arkusza danych, tak, jak do konta.

 

Sparametryzowanie, czyli uogólnienie odwołania do danych poszczególnych miesięcy, uzyskamy przy pomocy funkcji typu wyszukiwania i adresu, która zamienia tekst na adres: ADR.POŚR().

Jeśli ustawienia kolumn są oznaczane literami (zapraszamy do lektury artykułu o ustawieniach Excela, jest przewidziany), funkcja wymaga tylko jednego argumentu: albo tekstu w cudzysłowie określającego prawidłowo adres (jakiejś komórki lub obszaru), albo adresu komórki, który taki adres zawiera.

Przypuśćmy, że w komórce A1 jest liczba 100,00. Jak odwołać się do zawartości tej komórki podając tylko jej adres? Są dwa sposoby:

  1. W komórce A2 napisać odręcznie formułę: =ADR.POŚR(„A1”) ;  Wynik: 100,00
  2. Do komórki np. B1 wpisać tekst: A1 i do komórki A3 formułę: =ADR.POŚR(B1) ; Wynik: 100,00

Przy przenoszeniu formuły do innego arkusza Excel podpowiedział nam, jak pisze się adres komórki znajdującej się w innym arkuszu: Styczeń!A2 czyli NazwaArkusza!AdresKomórki

Oto formuła w dwóch wariantach:

1. =ADR.POŚR(„Styczeń!A2”)

2. =ADR.POŚR(B1)  i w komórce B1 tekst Styczeń!A2

 

Musimy jeszcze nauczyć się łączyć ze sobą różne teksty lub inaczej łańcuchy. Operacja łączenia tekstów nazywa się uczenie konkatenacją łańcuchów.

Nie musicie tego, Drodzy Czytelnicy/Czytelniczki pamiętać, tak tylko mimochodem wspomniałem, gdybyście chcieli błysnąć w towarzystwie.

Jak łączymy teksty? Powiedzmy, że mamy dwa teksty (łańcuchy): Styczeń i ! (wykrzyknik). Połączenie ich w jeden łańcuch odbywa się przy pomocy operatora konkatenacji (pardon!, łączenia): &.

=„Styczeń” & „!”

– wynik w komórce: Styczeń!

Jeśli tekst Styczeń znajduje się w komórce B3, zaś wykrzyknik wpiszemy w cudzysłowie,

otrzymamy formułę:

=B3 & „!”

- wynik ten sam: Styczeń!

Po takim przygotowaniu możemy już parametryzować naszą obrotówkę miesiącami:

Będziemy postępować zgodnie z metodą stopniowej komplikacji formuł (MSKP ).

 

Każda następna formuła jest bardziej skomplikowana, chociaż prostsza niż ostateczna, mniej od niej ogólna, ale... działa!

 

Zaczynamy:

Mamy formułę:

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

Najpierw zastąpimy argumenty 1-szy i 3-ci w postaci adresów, funkcją ADR.POŚR, która, jak już wiemy, akceptuje adresy w postaci tekstów.

Wchodzimy do edycji komórki B3, gdzie jest nasza niedoskonała, ale dająca dobry wynik dla stycznia, formuła:

Klawisz F2, przechodzimy kursorem za lewy nawias a przed pierwszy adres Styczeń!$A:$A, zamieniamy go na „sztywny” tekst dodając cudzysłów z obu stron i „obudowując” go funkcją ADR.POŚR. To samo robimy z ostatnim argumentem.

Otrzymujemy formułę:

=Suma.Jeżeli(ADR.POŚR(„Styczeń!$A:$A”);$C3; ADR.POŚR(„Styczeń!$C:$C”))

Zastępujemy teraz sztywny tekst Styczeń przez zawartość komórki D2:

Klawisz F2, przesuwamy kursor za pierwszy lewy nawias, kasujemy klawiszem Delete wyraz Styczeń, cofamy kursor w lewo przed lewy cudzysłów i dopisujemy D2 &.

Tak samo postępujemy z trzecim argumentem.

Oto nowa formuła:

=Suma.Jeżeli(ADR.POŚR(D2&„!$A:$A”);$C3; ADR.POŚR(D2&„!$C:$C”))

Dalej działa? Jeśli nie, to znaczy, że coś nam nie wyszło, proponuję wtedy klawisz [Alt -Backspace] lub [Ctrl-Z], czyli wycofać się z nieudanej próby i jeszcze raz, aż do skutku.

Prawie dobrze, ale .... co tu trzeba skorygować?

Tak! Należy zamrozić nr wiersza z określeniem miesiąca! Będziemy kopiowali formułę w dół, a miesiąc dla całej kolumny musi być czytany z wiersza 2-giego:

Znów:

F2, dojechać do D2 pierwszego argumentu i klawiszem F4 operować tak długo, aż adres D2 zmieni się na D$2. To samo powtórzyć z trzecim argumentem.

 

Ostatecznie otrzymaliśmy formułę:

=Suma.Jeżeli(ADR.POŚR(D$2&„!$A:$A”);$C3; ADR.POŚR(D$2&„!$C:$C”)) ,

Sami robimy arkusz wykonania budżetu. Gratulacje!

 

Test:

  1. Jakie argumenty ma Suma.Jeżeli() ?
  2. Co to jest argument typu ADRES, a co - typu TEKST?
  3. Do czego służy funkcja ADR.POŚR() ? Jakiego typu akceptuje argument.? Kiedy jest jednoargumentowa?
  4. Jaki znak (operator) służy do łączenia łańcuchów?
  5. Ćwiczenie: Przekształcić formułę obliczania obrotówki tak, żeby zawierała przyjazne nazwy zamiast adresów komórek i zakresów.