Dlaczego kontrola jest potrzebna
Excel jest uproszczonym środowiskiem programistycznym, a w programowaniu obowiązuje zasada GIGO, czyli „śmieci na wejściu – śmieci na wyjściu”. Musimy kontrolować poprawność wprowadzanych danych, bo program nie domyśli się, że wpisując np. „sad” mieliśmy na myśli „sąd”, więc jeśli w jednym skoroszycie użyliśmy obu tych wyrazów, to potraktuje je jako różne obiekty.
Przykład: fundacja rodzinna
Na przykładzie niektórych obliczeń na potrzeby fundacji rodzinnej prześledzimy metody kontroli danych.
Załóżmy, że przy zakładaniu fundacji rodzinnej chcemy m.in. obliczyć 2 rzeczy:
- czy każdy fundator wniósł na fundusz założycielski ustawowe minimum (obecnie wynosi ono 100 000 zł),
- jakie proporcje w spisie mienia fundacji[1] będą mieli poszczególni fundatorzy po uiszczeniu takich lub innych świadczeń na fundusz założycielski.
Robimy więc tabelę, w której poszczególne wiersze będą opisywać składniki mienia fundacji. Nadajmy jej nazwę tbWkłady.
Osobno wypisujemy wartość ustawowego minimum, bo przecież ustawodawca może kiedyś tą wartość zmienić, a my będziemy przecież nadal korzystać z naszego skoroszytu. Komórce z wartością minimum proponuję nadać nazwę zdefiniowaną minimum.
Całość wygląda tak:
W trzech pierwszych kolumnach umieszczamy odpowiednie wartości tekstowe bądź liczbowe, a w ostatniej wpisujemy formułę:
=SUMA.JEŻELI([fundator];[@fundator];[wartość])>=minimum
Zwraca nam ona wartość logiczną PRAWDA albo FAŁSZ. Już więc widzimy, że w przykładzie z obrazka fundatorka pierwsza od góry nie osiągnęła jeszcze ustawowego minimum. Mówimy jej o tym, na co ona sięga głębiej do kabzy i kładzie na stół kolejne swoje aktywa. Dopisujemy je do tabeli. Przypomnijmy: wystarczy ustawić się pod tabelą i zacząć wpisywać kolejne pozycje.
Gdy pracowaliśmy nad sprawą ze wspomnianą fundatorką, wtrąciła się druga z nich i dorzuciła jeszcze coś od siebie. Na tym etapie nasza tabela wygląda tak:
Coś jest nie tak! Przecież na oko widać, że Kangurzyca ma łącznie więcej niż 100 000 zł. Aby to unaocznić, posortujmy tabelę według kolumny „fundator” alfabetycznie rosnąco. Uzyskujemy taki efekt:
I teraz widzimy nie tylko, że Kangurzyca ma ponad 100 000 zł, ale i to, że za trzecim razem wpisaliśmy jej imię z błędem. To dlatego formuła w ostatniej kolumnie zwraca wynik FAŁSZ. Dla nas Kangurzyca i Kangurzzyca to to samo, ot po prostu oczywista omyłka. Natomiast dla Excela to dwa różne ciągi tekstowe. W naszej formule pełnią one rolę argumentu „kryterium” w funkcji SUMA.JEŻELI. Dla Excela Kangurzyca ma zatem 50 216 zł, a Kangurzzyca 60 753 zł i dopóki nie poprawimy literówki, on tych wartości nie zsumuje.
W naszym przykładzie wydaje się to być niewielkim problemem. Jednak co będzie, jeśli fundatorów będzie kilkunastu? Wyobraźmy sobie też jakieś projekty inne niż fundacja rodzinna, gdzie „udziałowców” mogłoby być kilkudziesięciu, a formuła miałaby zliczać ich wkłady lub mienie[2]. Aby zapobiec błędom stosujemy kontrolę poprawności danych.
Dynamiczna lista rozwijana
Lista rozwijana to ta forma kontroli poprawności danych, której potrzebujemy w trzeciej kolumnie tabeli tbWkłady. Taka lista musi mieć swoje źródło. Rozpocznijmy tworzenie tego źródła.
Tworzymy osobną tabelę z jedną kolumną. Nazwijmy ją „tbFundatorzy” i wypiszmy wszystkich naszych fundatorów.
Celowo pokazuję przykład, w którym nie wpisałem fundatorów alfabetycznie. Po pierwsze tabelę można posortować alfabetycznie później. Po drugie niżej pokażę, że nawet nieposortowaną tabelę można wykorzystać tak, aby jej pozycje wyświetlały się alfabetycznie na liście rozwijanej.
Teraz przejdźmy do samego ustawienia poprawności danych. Trzeba zaznaczyć całą kolumnę fundator, bez nagłówka (skrót klawiaturowy: ctrl + spacja).
Na karcie Dane w sekcji Narzędzia danych wybieramy polecenie Poprawność danych:
Otworzy się okno, w którym należy podać kryterium poprawności. Wybieramy pozycję Lista.
Następnie, jeśli wybraliśmy listę, to musimy podać jej źródło. Nie możemy użyć nazwy tabeli jako odwołania do źródła listy w taki sposób:
Nie możemy też użyć nazwy kolumny tabeli[3]:
Należy obejść to ograniczenie poprzez nadanie kolumnie nazwy zdefiniowanej. W tym celu otwieramy Menadżer nazw (ctrl+F3) i klikamy polecenie Nowy:
Następnie nadajemy nazwę, a jako źródło odwołania podajemy nazwę kolumny z fundatorami:
W tym wypadku gdybym wpisał tu nazwę tabeli (tbFundatorzy), wszystko zadziałałoby tak samo, ponieważ kolumna tbFundatorzy[fundatorzy] to jedyna kolumna w tej tabeli. Dobrą praktyką jednak jest odwołać się tylko do kolumny. Nie możemy przecież wykluczyć, że w przyszłości rozszerzymy tabelę tbFundatorzy o nowe kolumny, gdyby miała nam ona służyć do przechowywania np. jakichś informacji o fundatorach.
Teraz możemy ponownie uruchomić polecenie Poprawność danych, wybrać listę jako kryterium i podać jako źródło listy utworzoną właśnie nazwę zakresu:
Źródło listy należy zawsze podawać ze znakiem równości na początku.
Teraz w kolumnie tbWkłady[fundator] można wprowadzać tylko dane pochodzące z listy fundatorów. Wygląda to tak:
Przycisk rozwijania listy pojawia się po wybraniu danej komórki (kliknięciem lub klawiszami kursora z klawiatury).
Nasze źródło danych jest zakresem dynamicznym. Jeśli zechcemy do naszej symulacji fundacji rodzinnej dodać kolejnych fundatorów, to rozszerzy się także lista rozwijana. Zobaczmy. Dopiszmy więc kolejnego fundatora. Wystarczy stanąć pod tabelą tbFundatorzy wpisać nazwę i zatwierdzić klawiszem Enter lub Tab.
Lista rozwijana natychmiast zawiera tego kolejnego fundatora. Możemy go więc wybierać podając kolejne składniki mienia fundacji.
Alfabetyczne sortowanie listy rozwijanej
W podanym tu przykładzie alfabetyczne ułożenie elementów listy nie ma istotnego znaczenia, ponieważ elementów tych nie jest dużo. Ponadto, alfabetyczne ułożenie elementów jest nieistotne dla użytkownika, który wie co chce wpisać, czyli np. zna imiona i nazwiska fundatorów. Wystarczy bowiem zacząć wpisywać litery, aby program zaczął podpowiadać pasujące elementy listy. Jeśli natomiast elementów byłoby dużo, a użytkownik nie znałby z góry co chce w danym wierszu tabeli wpisać, to przydałoby się posortować alfabetycznie te elementy.
Żeby wykonać takie sortowanie wystarczy w tabeli zawierającej elementy (w naszym przykładzie jest to tabela tbFundatorzy) kliknąć polecenie sortowania, które jest w menu dostępnym po kliknięciu przycisku w nagłówku kolumny. Każdorazowe sortowanie tabeli po każdym dopisaniu kolejnego fundatora może być jednak kłopotliwe. Możemy więc zaprogramować arkusz w taki sposób, by lista rozwijana była uporządkowana alfabetycznie, nawet gdy tabela tbFundatorzy nie jest posortowana. W tym celu należy utworzyć pośredni krok w odwołaniu do źródła listy. Robimy to następująco.
W ustalonej kolumnie[4] tworzymy formułę tablicową:
=SORTUJ(tbFundatorzy[fundatorzy])
Formuła tablicowa to taka, która w wyniku zwraca tablicę, czyli zbiór wartości. Powoduje to rozlanie elementów tablicy do komórek położonych poniżej. Dlatego właśnie musimy znaleźć kolumnę, w której nic innego poniżej nie umieściliśmy, ani nie umieścimy. Teraz wystarczy w Menadżerze nazw podmienić zawartość nazwy. Załóżmy, że wspomnianą formułę tablicową umieściliśmy w osobnym arkuszu o nazwie setup, w komórce B3. A zatem edytując nazwę fundatorzy, podając odwołanie w polu Odwołuje się do: zamiast
=tbFundatorzy[fundatorzy]
wpisujemy:
=setup!$B$3#
Znak # na końcu jest tu niezbędny, bo oznacza odwołanie do dynamicznej tablicy, czyli nie tylko komórki B3, ale i do tych komórek, na jakie rozlewa się tablica zwracana formułą umieszczoną w komórce B3. Równie niezbędne są znaki $.
Od teraz elementy na liście rozwijanej zawsze będą ułożone alfabetycznie i nie musimy tego poprawiać przy każdym uzupełnieniu listy w tabeli tbFundatorzy.
Liczby
Skoro w naszej sprawie należy podawać kwoty, możemy też zadbać, by nasi współpracownicy (ci gorzej znający się na Excelu) do kolumny wartość nie wpisywali np. „5000 zł” albo „5.000”, bo to są teksty, a nie liczby i Excel tego nie zsumuje. W tym celu dla kolumny wartość należy ustawić poprawność danych wybierając jako kategorię Dziesiętne, a jako minimum 0.
Teraz w tej kolumnie będzie można umieszczać tylko kwoty wyższe od 0, także z groszami. Aby nasz współpracownik nie zdziwił się dlaczego nie może wpisać „100.000 zł”, możemy zaprogramować komunikat wejściowy lub alert o błędzie:
Teraz pozostaje zaprogramować arkusze, które zwrócą wielkość obciążeń podatkowych dla poszczególnych beneficjentów świadczeń w zależności od ich relacji rodzinnych z fundatorami. Nie jest to miejsce by tłumaczyć, jak to zrobiłem, bo to poza zakresem tematu tego odcinka. Wyjaśnię tylko, że trzeba zacząć od obliczenia proporcji w spisie mienia fundacji (art. 28 ustawy), które będą mieli poszczególni fundatorzy.
Proponuję wykorzystać do tego tabelę tbFundatorzy, bo z założenia każdy fundator jest w niej wymieniony tylko raz. Dostawmy więc z prawej strony kolejną kolumnę, nazwijmy ją np. proporcja i umieśćmy w niej formułę:
=SUMA.JEŻELI(tbWkłady[fundator];[@fundatorzy];tbWkłady[wartość])/SUMA(tbWkłady[wartość])
Potem tylko trzeba sformatować komórki w tej kolumnie na Procentowe:
I proszę:
Oczywiście do celu w postaci rzetelnej symulacji świadczeń i obciążeń podatkowych prowadzi nas jeszcze długa droga. Przydałoby się też zaokrąglanie niektórych wyników, np. funkcją ZAOKR. Ale to już byłby temat na inny wpis.
[1] O którym mowa w art. 28 ustawy o fundacjach rodzinnych.
[2] Na przykład wspólnota mieszkaniowa, w której niektórzy właściciele mogą mieć więcej niż 1 lokal.
[3] W tym wypadku jest to jedyna kolumna, ale i tak ma ona swoją nazwę.
[4] Zgodnie z zaleceniem, o którym pisałem przy nazwach zdefiniowanych, najlepiej zrobić to w osobnym specjalnym arkuszu.
Dodaj komentarz