Rozliczanie wspólnych wydatków przy pomocy LibreOffice Calc

LibreOffice może nam pomóc w wykonaniu wielu zadań, także tych codziennych. Artykuł pokazuje, jak przy jego pomocy opanować nierówny wkład do wspólnych wydatków; temat zaproponował Stanisław Borko.

Wstęp

Nasz problem zobrazujemy na przykładzie Jasia i Ani, pary wymyślonej na potrzeby tego artykułu. Niedawno postanowili przenieść swój związek na kolejny poziom i razem zamieszkali. Umówili się wtedy, że wspólne wydatki będą pokrywać w równym stopniu. Mniejsze kwoty pozostawili przypadkowi — uznali że raz Ania kupi płyn do naczyń, innym razem Jaś kupi olej do smażenia i wszystko jakoś się wyrówna.

Jednak Ania, która pracuje z domu i częściej robi zakupy, wkrótce zaczęła uważać ten system za wadliwy. Niewielkie kwoty szybko się kumulują i w rezultacie inwestuje ona w mieszkanie więcej, niż jej partner.

Z drugiej strony, pomyślała, dość często wspólnie wychodzą wieczorami. Wtedy wszystkie płatności wykonuje jedno z nich, z reguły Jaś. Druga osoba powinna oddać swoją część po powrocie, ale zazwyczaj o tym zapomina. W końcu nastrój po wspólnym wypadzie do kina czy wizycie w restauracji rzadko kiedy sprzyja dyskusjom na tematy finansowe.

W rezultacie para nie wie, kto ile komu powinien oddać, aby rzeczywiście wkład obojga do wspólnego budżetu był jednakowy.

My zaś przygotujemy arkusz kalkulacyjny, który pomoże im odpowiedzieć na to pytanie.

Założenia

Zanim zaczniemy tworzenie arkusza, zastanówmy się, jakie informacje chcemy w nim przechowywać.

Niewątpliwie będą to kwoty pieniędzy, którymi dysponowali nasi bohaterowie.

Skoro o nich mowa, każda płatność ma wykonawcę — osobę, która opłaciła dany rachunek.

Jaś i Ania są zaniepokojeni przede wszystkim wydatkami na rzeczy, z których korzystają oboje. Jednak ich rozliczenie komplikują okazjonalne pożyczki. Niedawno, gdy Jasiowi kończyły się pieniądze na karcie w telefonie, Ania wykonała przelew internetowy ze swojego konta i wysłała mu kod aktywacyjny. Innym razem, podczas wspólnego spaceru, Ania zwróciła uwagę na bluzkę w bardzo atrakcyjnej cenie. Ponieważ to była ostatnia szansa na skorzystanie z okazji, a akurat nie miała przy sobie portfela, zapłacił za nią Jaś.

Innymi słowy, wyróżnić możemy dwa rodzaje płatności: wydatki na rzeczy wspólne (gdy jedna osoba płaci za coś, za co powinny zapłacić obie) oraz pożyczki (gdy jedna osoba płaci za coś, za co powinna zapłacić druga). Ponieważ są one rozliczane odmiennie, również tę informację musimy uwzględnić w arkuszu.

Poza tym zwróćmy uwagę, że para ma kłopoty z bieżącym rozliczaniem wzajemnych zobowiązań. Możemy przypuszczać, że arkusz będzie często uzupełniany, ale wprowadzone dane będą usuwane dość rzadko. Po pewnym czasie nikt już nie będzie pamiętał, dlaczego Ania zapłaciła 15 zł za Jasia i kiedy właściwie to było. Dlatego do każdej kwoty powinniśmy przypisać jej krótki opis oraz datę.

Szkielet arkusza

Gdy wiemy już, jakie dokładnie informacje chcemy przechowywać w arkuszu, możemy przystąpić do przygotowania jego szkieletu.

Oczywiście jego ostateczna forma oraz sposób przechowywania danych zależą tylko od nas. Ja zdecydowałem się podzielić arkusz na cztery części: rzeczy wspólne kupione przez Jasia, rzeczy Ani kupione przez Jasia, rzeczy wspólne kupione przez Anię oraz rzeczy Jasia kupione przez Anię. Każda z tych części będzie zawierała listę transakcji (w tym opis oraz datę wykonania).

Na końcu każdej listy umieściłem wiersz podsumowania. Korzystając z funkcji SUMA, obliczam tam całość wydatków w wybranej kategorii. Co prawda nie jest to konieczne, ale ułatwi nam późniejsze konstruowanie formuł.

Istotne elementy arkusza wyróżniłem za pomocą obramowania oraz formatowania tekstu. Dzięki temu nasi bohaterowie otrzymają narzędzie, z którego obsługą z pewnością sobie poradzą.

W tym miejscu warto się na chwilę zatrzymać i wypełnić nasz szkielet przykładowymi danymi. Ułatwi to sprawdzenie poprawności obliczeń wykonywanych przez wprowadzone formuły.

Obliczenia

Arkusz ma pomóc Jasiowi i Ani odpowiedzieć na pytanie, kto ile powinien oddać drugiej osobie, aby ich wzajemne zobowiązania finansowe się wyrównały. Formuły i zapisane za ich pomocą obliczenia powinny wprost prowadzić do tego celu.

Najprościej będzie obliczyć całkowite wydatki każdego z bohaterów i porównać otrzymane wyniki. Większy z nich będzie sygnalizował, która osoba jest „stratna”; z kolei różnica pomiędzy uzyskanymi liczbami to kwota, którą powinna zapłacić druga osoba.

Opisane operacje możemy zapisać na kilka różnych sposobów. Ja zdecydowałem się ująć wszystkie obliczenia w jednym wzorze, który wygląda następująco:

( (<Wydatki Jasia na rzeczy wspólne>)/2 + <Wydatki Jasia na rzeczy Ani> ) - ( (<Wydatki Ani na rzeczy wspólne>)/2 + <Wydatki Ani na rzeczy Jasia>)

Gdy w odpowiednie miejsca podstawimy rzeczywiste liczby, szybko przekonamy się, że wynik będzie dodatni wtedy, gdy łączne wydatki Jasia będą większe niż łączne wydatki Ani (a więc Ania musi oddać pieniądze Jasiowi). Natomiast jeżeli Ania miała rację, i to ona pokrywa większą część wspólnych wydatków, uzyskany wynik będzie ujemny (Jaś musi oddać pieniądze Ani).

Innymi słowy, wartość absolutna wyniku równania oznacza kwotę do przekazania, zaś znak wskazuje, kto jest dłużnikiem.

LibreOffice Calc. W komórce wprowadzono formułę (D24/2 + D39) - (I24/2 + I39). Wynikiem jest -78,905

Jak widać na powyższej ilustracji, w rzadkich przypadkach — takich jak mój — kwota zwrócona przez formułę będzie nieprawdziwa. W Polsce najmniejszą jednostką pieniężną jest grosz, a tutaj mamy do czynienia z jego połową. Rozwiązaniem tego problemu zajmiemy się za chwilę.

Prezentacja wyniku

Właściwie doszliśmy już do punktu, w którym chcieliśmy się znaleźć przed przystąpieniem do pracy.

Jednak aktualny sposób prezentacji wyniku obliczeń nie jest najlepszy z możliwych. Naszym bohaterom zdarza się zapomnieć o którejś płatności, nie powinniśmy więc od nich oczekiwać, że zapamiętają sposób interpretacji wartości zwracanej przez arkusz. Dlatego warto poświęcić jeszcze chwilę na zaprezentowanie tej informacji w czytelniejszej formie.

Oczywiście po raz kolejny stoimy przed całym spektrum możliwych rozwiązań i tylko od nas zależy, które z nich wybierzemy.

Ja wykorzystam dwie komórki.

W pierwszej zamieszczę informację, kto musi oddać pieniądze. Pomoże mi w tym funkcja JEŻELI, która przyjmuje trzy argumenty: test porównawczy, działanie do wykonania gdy test okaże się prawdziwy oraz działanie do wykonania w przeciwnym przypadku. Test będzie oczywiście polegał na sprawdzeniu, czy wynik działania jest większy od zera. Jeżeli tak, to dłużnikiem jest Ania. Jeżeli zaś wynik jest mniejszy, to Jaś powinien zwrócić pieniądze.

Gotowa formuła wygląda następująco:

=JEŻELI(D42>0;H10;C10)

W drugiej komórce wstawię wynik naszego obliczenia po usunięciu znaku. Wykorzystam do tego funkcję MODUŁ.LICZBY, która zwraca wartość bezwzględną argumentu:

=MODUŁ.LICZBY(D42)

Ponadto obie te informacje wyeksponuję przy pomocy odmiennego tła komórek. Dzięki temu odpowiedź na podstawowe pytanie użytkowników będzie przyciągała wzrok od razu po otworzeniu arkusza.

Wcześniej sygnalizowałem, że czasami nasze równanie może zwrócić nieprawdziwą kwotę (zawierającą ułamkowe części grosza). Czas najwyższy zająć się tym problemem.

Najprostszym rozwiązaniem jest zmiana formatu komórki na format walutowy. Wtedy LibreOffice automatycznie zaokrągli liczbę do odpowiedniego miejsca po przecinku i doda tekst wskazujący na walutę. Aby to zrobić, wystarczy kliknąć ikonkę przedstawiającą monety na pasku narzędziowym. Innym sposobem jest kliknięcie na komórkę prawym przyciskiem myszy i wybranie z menu Formatuj komórki.... W nowym oknie, na karcie Liczby, z listy Kategoria należy wybrać Waluta, a z listy Format — dowolną pozycję.

Okno programu Calc. W górnym rogu wprowadzono podpisy („kto musi zapłacić?” oraz „Ile?”). Komórki obok nich mają żółte tło. Pierwsza z nich pokazuje słowo „Jaś” i zawiera formułę JEŻELI(D42>0;H10;C10); druga z nich pokazuje wartość bezwzględną wyniku równania sformatowaną jako walutę.

Skomplikowane płatności

Właściwie już skończyliśmy pracę — wiemy kto, komu i ile musi oddać, a informacja ta jest pokazywana w przystępnej formie. Jednak w ramach ćwiczeń możemy dodać do naszego arkusza jeszcze jedną funkcję. Pomoże ona Jasiowi i Ani w określeniu, jak zapisać skomplikowane płatności.

Przez „skomplikowane płatności” rozumiem sytuacje, w których część całkowitej kwoty do zapłacenia jest wspólna, część należy do jednej osoby, a część do drugiej. Oboje składają się na opłacenie rachunku.

Jaś i Ania znajdują się w takiej sytuacji niemal za każdym razem, gdy razem idą na zakupy. Ostatnio zostawili w sklepie 72 zł. Znaczną część tej kwoty (40 zł) stanowił proszek do prania, z którego będą korzystali wspólnie. Jaś kupił sobie maszynki do golenia za 20 zł, zaś Ania wybrała kosmetyki o wartości 12 zł. Za całość miał zapłacić Jaś, ale przy kasie okazało się, że ma tylko 50 zł. W związku z tym Ania musiała dołożyć resztę.

Gdy się nad tym zastanowimy, przekonamy się, że skomplikowaną płatność możemy sprowadzić do sytuacji pożyczki. W tym konkretnym przypadku Jaś powinien był zapłacić 40 zł (swoją część w całości + połowę części wspólnej), ale zapłacił 50. Innymi słowy, pokrył swoją część rachunku i pożyczył Ani 10 zł na jej wydatki.

Kroki na drodze do opanowania skomplikowanej płatności są takie same jak w przypadku głównej części arkusza. Wpierw przygotujemy szkielet, który pomoże wprowadzić dane. Następnie wymyślimy wzór na obliczenie bilansu wydatków, a na końcu zaprezentujemy wynik jego działania w formie bardziej przystępnej dla użytkownika.

Szkieletem będzie tabela z potrzebnymi nam informacjami: częścią wspólną rachunku, częścią rachunku Jasia oraz kwotą, którą wydał Jaś. Część Ani, jej wkład oraz łączną kwotę do zapłaty możemy pominąć.

Bilans wydatków poznamy korzystając ze wzoru:

<Część rachunku opłacona przez Jasia> - <Część wspólna zakupów>/2 - <Część Jasia zakupów>

Wynik interpretujemy tak samo jak w głównej części arkusza — liczba oznacza wartość długo, zaś znak wskazuje dłużnika.

Wreszcie, korzystając z funkcji JEŻELI oraz MODUŁ.LICZBY, podajemy użytkownikowi instrukcję, co powinien zrobić. Niestety, formuły nie mogą modyfikować zawartości arkusza, więc skopiowanie liczby do odpowiedniej komórki jest zadaniem dla użytkownika.

Aby uniknąć pomyłek, podaną kwotę najlepiej jest skopiować. Niestety, nie możemy jej po prostu wkleić w odpowiednie miejsce, ponieważ Calc skopiował formułę a nie jej wynik.

Zamiast tego należy kliknąć prawym przyciskiem myszy na pożądaną komórkę i wybrać z menu Wklej tylkoLiczba.

Opcja ta jest dostępna od LibreOffice 4.0. Użytkownicy wcześniejszych wersji pakietu muszą skorzystać z wklejania specjalnego. Wystarczy wcisnąć klawisze Ctrl + Shift + V i w nowym oknie odznaczyć Wklej wszystkoFormuły, a zaznaczyć Liczby.

Okno wklejania specjalnego. W części „Wybór” zaznaczono wszystkie opcje oprócz „Wklej wszystko” oraz „Formuły”.

Podobał Ci się ten artykuł? Zapisz się na listę subskrybentów i otrzymuj informacje o następnych

komentarzy 7

  • Grzegorz napisał(a):

    Witaj,
    wybacz Mirku, ale ten wpis mi nie pasuje do Twojego bloga 🙁
    Z niecierpliwością wyczekuję wtorku, żeby zobaczyć coś nowego, poznać nieznane, zrozumieć a tutaj „brakuje” formatowania walutowego kwoty…

    Ja wiem, że założenie było takie, żeby osoby nie znające czegoś się nauczyły.

    Czuję ogromny niedosyt.

    Pozdrawiam i czekam z niecierpliwością na przyszły wtorek!

    • Mirosław Zalewski napisał(a):

      Przede wszystkim pragnę podziękować za komentarz. Uwagi czytelników są dla mnie ważne, również te krytyczne. Jednak w tym konkretnym przypadku nie jest dla mnie jasne, czego dotyczy główny zarzut.

      Czy chodzi o brak opisu, jak sformatować wynik równania, aby przedstawiał realną kwotę pieniędzy?
      Jeżeli tak, to pewnie pozytywnie zostanie odebrana wiadomość, że zostało to już poprawione. Uzupełniłem także artykuł o fragment dotyczący kopiowania wyniku ostatniej formuły. Właściwie to był nawet napisany, ale zdecydowałem się go usunąć przed publikacją. Jednak jeżeli jest zapotrzebowanie na tak szczegółowe informacje, to proszę bardzo.

      Nie ukrywam, że czasami mam problem, na ile szczegółowo opisywać poszczególne czynności. Z jednej strony początkujący użytkownicy potrzebują instrukcji krok po kroku. Z drugiej strony, takie rozwlekanie tematu może odpychać bardziej zaawansowanych czytelników — nawet tych, dla których ogólny problem jest interesujący.

      Więc może zarzut dotyczy właśnie przesadnej szczegółowości oraz pewnej banalności artykułu (pokrywa dość podstawowe zagadnienia, o których powinno się mówić w szkole)?
      Przypuszczam, że niektórzy czytelnicy mogą tak go odebrać. Jednak nie zgadzam się, że artykuł nie pasuje do bloga. Celem strony jest przybliżenie możliwości LibreOffice i pokazanie jego praktycznych zastosowań. Jest to cel tak szeroki, jak zróżnicowane są ludzkie potrzeby. Dlatego w jego ramach mieszczą się zarówno próby odtworzenia bardziej wyrafinowanych zabiegów typograficznych w ograniczonym środowisku Writera, jak i proste arkusze zawierające dwie formuły na krzyż. W obu przypadkach znajdzie się ktoś, kto nie wie jak wykonać daną czynność i czegoś się z artykułu dowie.

      Jeżeli uznamy, że jest to problem, to ja nie widzę jego dobrego rozwiązania. Mogę opisywać najbardziej skomplikowane aspekty programu dostępne jedynie dla wąskiej grupy specjalistów, ale stracą na tym mniej zaawansowani użytkownicy (no i jest dość dyskusyjne, na ile te funkcje są naprawdę przydatne w rozwiązywaniu codziennych problemów). Natomiast gdy skupiam się na wyjaśnieniu problemu przysłowiowemu pięciolatkowi, jest to niekorzystne dla bardziej wprawionych użytkowników.

      Niemniej sprawy nie uważam za zamkniętą i jestem otwarty na propozycje. Z pewnością każdą przeczytam i rozważę.

  • Mariusz napisał(a):

    Niestety muszę zgodzić się z przedmówcą…
    Brakuje mi wpisów w stylu „Funkcja SUMA.JEŻELI i kilka warunków”, gdzie w bardzo prosty sposób przedstawiał pan zawiłe funkcje Calc’a.
    Pod wpisem podałem swoją propozycję temat – owszem, odpowiedział Pan po krótce, ale myślę, że temat nadal całkiem ciekawy dla innych także.
    Wiedzę ma Pan niesamowitą w tej dziedzinie i chętnie wraz z innymi nauczyłbym się bardziej przydatnego od Pana.
    Już kilka razy skorzystałem z Pańskich rozwiązań na tym blogu, i mam nadzieję, że będę mógł korzystać nadal
    Pozdrawiam i z niecierpliwością czekam na kolejne wpisy.

    • Mirosław Zalewski napisał(a):

      Dziękuję za komentarz.

      Mam jednak pytanie: czy akcent w nim pada raczej na „prosty sposób przedstawienia”, czy na „zawiłe funkcje Calca”?
      Bo jeżeli chodzi o ten drugi — przypuszczam, że tematy poruszane ostatnimi czasy (tabele przestawne, funkcje baz danych czy generowanie losowych danych o z góry ustalonym rozkładzie częstości) nie należą do zasobu wiedzy podręcznej każdego użytkownika arkusza kalkulacyjnego.

      Zaś w sprawie Pańskiej propozycji tematu — artykuł został opublikowany w marcu. Czy może źle zrozumiałem wtedy problem albo coś pominąłem?

      • Mariusz napisał(a):

        Uważam, że umie Pan połączyć jedno z drugim i „w prosty sposób przedstawić zawiłe funkcje Calca”.
        To tylko moje osobiste zdanie i proszę tak to odebrać, że ktoś to tu zagląda, ma pewne podstawy wiedzy na temat arkusza kalkulacyjnego.
        Pan z kolei, zna jego tajniki, które innym mogą pomóc. Publikując wpis dotykający jakiś problem używając przy tym jakichś „tajników”, takie osoby będą w stanie zaadoptować je do swoich potrzeb.
        Chyba w tym wszystkim chodzi o te „tajniki”, lub też o pewien poziom, który Pan narzucił.
        Nie jest to jakieś wywnętrzanie swoich frustracji, ale motywacja Pana, aby się Pan nie poddawał ucząc nas tego co dla Pana jest łatwością.

  • Blakecript napisał(a):

    craig android tablet review acheter reductil en ligne when does the new season of doctor who start

  • HarlanScobe napisał(a):

    bls healthcare provider course comprar sibutramina online non prescription red colored contacts

Dodaj komentarz