Arkusz kalkulacyjny do zarządzania budżetem to niezwykle przydatne narzędzie, ale mnogość danych i skomplikowane obliczenia mogą szybko stać się przytłaczające. Na szczęście, Google Sheets oferuje szeroki wachlarz funkcji, które mogą znacząco uprościć proces budżetowania. Te formuły ułatwiają monitorowanie wydatków, zarządzanie przychodami i kontrolowanie postępów w realizacji celów finansowych. Dzięki prostym i efektywnym rozwiązaniom, arkusz kalkulacyjny staje się przejrzysty i intuicyjny, co pozwala na lepsze planowanie przyszłości. W niniejszym artykule przyjrzymy się ośmiu kluczowym formułom, które mogą znacząco usprawnić Twój arkusz budżetowy, nadając mu nowy wymiar efektywności i klarowności.
1. AVERAGEIF
Funkcja AVERAGEIF umożliwia wyliczenie średniej wartości w zbiorze danych, który spełnia określone kryteria. Jej składnia przedstawia się następująco:
=AVERAGEIF(zakres, kryterium, [zakres_średniej])
Gdzie „zakres” to obszar komórek, który poddajemy analizie, „kryterium” to warunek, jaki musi być spełniony, a „[zakres_średniej]” to opcjonalny zakres komórek, z których obliczana jest średnia.
Przykład użycia AVERAGEIF
Załóżmy, że stworzyłeś arkusz kalkulacyjny do monitorowania wydatków wraz z datami, tak jak w przedstawionym przykładzie:
Aby obliczyć średnią kwotę wydaną na zakupy spożywcze, gdzie „Zakupy Spożywcze” są wyszczególnione w kolumnie A, a kwota w kolumnie B, użyj formuły:
=AVERAGEIF(A:A, "Zakupy Spożywcze", B:B)
Na podstawie przedstawionych danych, średnia kwota wydana na produkty spożywcze wynosi 150 zł.
2. SUMIF
Funkcja SUMIF pozwala na sumowanie wartości w obrębie zakresu, które spełniają określone kryteria. Składnia tej funkcji jest następująca:
=SUMIF(zakres, kryterium, [zakres_sumy])
Gdzie „zakres” to obszar komórek poddany ocenie, „kryterium” to warunek, który musi być spełniony, a „[zakres_sumy]” to opcjonalny zakres komórek, które mają zostać zsumowane.
Przykład użycia SUMIF
Jeśli chcesz zsumować wszystkie wydatki na zakupy spożywcze, użyj następującej formuły:
=SUMIF(A:A, "Zakupy Spożywcze", B:B)
W tym przypadku, łączna kwota wydana na zakupy spożywcze wynosi 450 zł, zgodnie z przykładowymi danymi.
3. COUNTIF
Funkcja COUNTIF umożliwia zliczanie komórek w danym obszarze, które spełniają określone kryteria, co ułatwia monitorowanie częstości występowania niektórych wydatków.
Składnia funkcji COUNTIF jest następująca:
=COUNTIF(zakres, kryterium)
Gdzie „zakres” to obszar komórek do zliczenia, a „kryterium” to warunek, który musi być spełniony.
Przykład użycia COUNTIF
Aby policzyć, ile razy dokonałeś zakupu produktów spożywczych, użyj tej formuły:
=COUNTIF(A:A, "Zakupy Spożywcze")
Na podstawie przykładowych danych, formuła zwraca wartość 3, co oznacza, że zakupy dokonano trzykrotnie. Jeśli chcesz uwzględnić wiele kryteriów, możesz użyć funkcji COUNTIFS.
4. IFS
IFS to bardziej zaawansowana funkcja, która pozwala na testowanie wielu warunków. Jest szczególnie przydatna do kategoryzowania wydatków w oparciu o różne kryteria.
Składnia funkcji IFS wygląda następująco:
=IFS(warunek1, wartość_jeśli_prawda1, [warunek2, wartość_jeśli_prawda2], ...)
„Warunek1” to pierwszy warunek podlegający ocenie, a „wartość_jeśli_prawda1” to wynik, który zostanie wyświetlony, jeśli warunek zostanie spełniony. Możliwe jest dodanie kolejnych warunków i odpowiadających im wyników.
Przykład użycia IFS
Jeśli chcesz sklasyfikować swoje wydatki w oparciu o kwoty, zastosuj tę formułę:
=IFS(B2<50, "Niskie", B2<100, "Średnie", B2>=100, "Wysokie")
Ta formuła oznaczy każdy wydatek jako „Niski”, „Średni” lub „Wysoki”, zależnie od jego wartości. Na przykład, zakup spożywczy za 150 zł zostanie oznaczony jako „Wysoki”, jak to widać na poniższym zrzucie ekranu.
5. TEXT
Funkcja TEXT umożliwia formatowanie liczb jako tekstu, co jest przydatne do prezentowania liczb w czytelny sposób (np. z symbolem waluty lub jako procenty).
Składnia funkcji TEXT przedstawia się następująco:
=TEXT(wartość, format_tekstu)
Gdzie „wartość” to liczba, którą chcesz sformatować, a „format_tekstu” to oczekiwany format (np. waluta lub procent).
Przykład użycia TEXT
Aby wyświetlić liczbę jako wartość w walucie, użyj tej formuły:
=TEXT(B2, "$#,##0.00")
Ponieważ komórka B2 zawiera liczbę 150, funkcja TEXT pokaże ją jako $150.00, aby jednoznacznie wskazać, że wartość komórki reprezentuje kwotę pieniędzy.
6. INDIRECT
Funkcja INDIRECT pozwala na dynamiczne odwoływanie się do komórek, przekształcając ciąg tekstowy w adres komórki. Ułatwia to aktualizację formuł w oparciu o zmieniające się dane wejściowe, takie jak różne arkusze lub zakresy w arkuszu kalkulacyjnym.
Składnia funkcji INDIRECT jest następująca:
=INDIRECT(ref_text, [a1])
Gdzie „ref_text” to odwołanie podane jako tekst, a „[a1]” to opcjonalny argument określający, czy odwołanie ma być w stylu A1 (PRAWDA) czy R1C1 (FAŁSZ). Domyślnie [a1] przyjmuje wartość stylu A1 (PRAWDA).
Przykład użycia INDIRECT
Załóżmy, że masz odniesienie do komórki w formie tekstu (np. „B2”) i chcesz je przekształcić w rzeczywiste odwołanie do komórki. Użyj formuły:
=INDIRECT("B2")
Ta formuła zwróci wartość z komórki B2, która wynosi 100 zł.
Załóżmy, że masz kilka arkuszy w swoim skoroszycie budżetowym, każdy reprezentujący inny miesiąc (np. „Styczeń”, „Luty”, „Marzec”). Przykładowo, na poniższym zrzucie ekranu przedstawiono dane dotyczące miesięcznych wydatków za marzec:
Całkowita kwota wydatków jest umieszczona w komórce B7 i wynosi 1775 zł.
Załóżmy, że chcesz stworzyć arkusz podsumowujący, w którym w kolumnie A będą wyświetlane miesiące, a w kolumnie B będą dynamicznie pobierane łączne wydatki z każdego miesiąca. Poniżej przykład jak wyglądałby ten arkusz:
Teraz, aby pobrać całkowity wydatek z komórki B7 z arkusza marcowego i wyświetlić go w arkuszu podsumowującym, możesz użyć funkcji INDIRECT w następujący sposób:
=INDIRECT("'" & A4 & "'!B7")
W tym przykładzie A4 odnosi się do komórki w arkuszu podsumowującym, zawierającej nazwę arkusza, z którego chcesz pobrać dane (np. „Marzec”). Formuła dynamicznie odnosi się do komórki B7 w arkuszu marcowym, zawierającej całkowite wydatki za dany miesiąc. Znak „&” jest używany do konkatenacji, czyli łączenia ciągów tekstowych.
W tym przypadku łączy pojedynczy apostrof (’) dla nazw arkuszy ze spacjami, nazwę arkusza z komórki A4 oraz ’!B7′, gdzie wykrzyknik (!) oddziela nazwę arkusza od adresu komórki.
Na zrzucie ekranu widać, że całkowite miesięczne wydatki za marzec wynoszą 1775 zł, co zostało obliczone za pomocą funkcji INDIRECT. Odpowiada to całkowitym miesięcznym wydatkom w arkuszu „Marzec”. Co ciekawe, zmieniając wartość w A4 na „Luty”, automatycznie zaktualizujesz odwołanie do arkusza lutowego.
7. FILTER
Funkcja FILTER umożliwia filtrowanie danych na podstawie określonych warunków, co ułatwia wyodrębnienie konkretnych wydatków lub kategorii.
Składnia funkcji FILTER jest następująca:
=FILTER(zakres, warunek1, [warunek2], ...)
Gdzie „zakres” to dane, które chcesz przefiltrować, a „warunek1” i „warunek2” to warunki, które dane muszą spełnić.
Przykład użycia FILTER
Oto jak użyć funkcji FILTER, aby przefiltrować wyłącznie wydatki na zakupy spożywcze, w oparciu o nasze przykładowe dane:
=FILTER(A:B, A:A="Zakupy Spożywcze")
Wprowadź formułę w nowej kolumnie. W naszym przykładzie wprowadziliśmy ją do komórki E2 w kolumnie E. Formuła zwróci tylko te wiersze, w których w kolumnie A znajduje się wpis „Zakupy Spożywcze”, umożliwiając skupienie się na wydatkach na żywność.
8. XLOOKUP
XLOOKUP to wszechstronna funkcja, która przeszukuje określony zakres w poszukiwaniu dopasowania i zwraca odpowiadającą mu wartość. Jest idealna do wyszukiwania konkretnych wydatków lub kategorii.
Składnia funkcji XLOOKUP przedstawia się następująco:
=XLOOKUP(wartość_do_wyszukania, tablica_wyszukiwania, tablica_zwrotu, [jeśli_nie_znaleziono], [tryb_dopasowania], [tryb_wyszukiwania])
Gdzie „wartość_do_wyszukania” to wartość, której szukamy, „tablica_wyszukiwania” to zakres, w którym poszukujemy, a „tablica_zwrotu” to zakres, z którego zwracana jest wartość. Opcjonalne parametry pozwalają określić, co zrobić, jeśli nie znaleziono dopasowania i jak obsługiwać kryteria dopasowania.
Przykład użycia XLOOKUP
Załóżmy, że masz arkusz budżetowy, który monitoruje różne wydatki w kategoriach w kolumnie A, kwoty w kolumnie B oraz nazwy produktów w kolumnie C. Chcesz sprawdzić, ile wydałeś na konkretny produkt spożywczy, na przykład „Warzywa”.
Oto funkcja XLOOKUP, która Ci w tym pomoże:
=XLOOKUP("Warzywa", C:C, B:B)
W tym przykładzie, formuła zwróciła 150 zł, kwotę wydaną na „Warzywa”.
Wszystkie omówione formuły mogą znacząco ułatwić proces budżetowania i efektywne zarządzanie finansami. Zachęcamy do wypróbowania ich i przekonania się, jak bardzo mogą uprościć codzienne zadania związane z budżetem.