Excel w księgowości: Niezbędne szablony i formuły

Nawet pomimo rozwoju dedykowanego oprogramowania księgowego opartego na chmurze, Microsoft Excel pozostaje niekwestionowanym narzędziem pracy w branży finansowej i księgowej. Od przygotowywania uzgodnień na koniec miesiąca po tworzenie złożonych modeli finansowych, Excel oferuje elastyczność i surową moc obliczeniową, której często brakuje sztywnym systemom księgowym.

Niezależnie od tego, czy jesteś właścicielem małej firmy prowadzącym własne księgi, czy księgowym w korporacji zajmującym się tysiącami wierszy danych transakcyjnych, biegłość w programie Excel to absolutnie niezbędna umiejętność. W tym przewodniku omówimy kluczowe szablony i formuły w Excelu, których potrzebuje każdy profesjonalista w dziedzinie księgowości, wraz z praktycznymi instrukcjami i konkretnymi przykładami.

Konfiguracja szablonu Księgi Głównej (GL)

Księga Główna to główne repozytorium wszystkich Twoich transakcji finansowych. Jeśli używasz Excela do prowadzenia ksiąg rachunkowych małej firmy, prawidłowe ustrukturyzowanie Księgi Głównej (GL) od pierwszego dnia jest kluczowe. Źle skonstruowana księga główna uniemożliwi późniejsze generowanie zautomatyzowanych raportów.

Standardowa księga główna w Excelu powinna być zorganizowana w ciągłym formacie tabelarycznym. Unikaj pomijania wierszy lub wstawiania pustych kolumn między danymi. Oto przykład idealnej struktury kolumn:

Data	ID transakcji	Kod konta	Opis	Winien	Ma	Saldo bieżące
2023-10-01	TRX-001	1010 (Gotówka)	Inwestycja właściciela	$10,000		$10,000
2023-10-03	TRX-002	6010 (Czynsz)	Opłata za czynsz za październik		$2,000	$8,000
2023-10-05	TRX-003	4010 (Sprzedaż)	Faktura - Klient A	$1,500		$9,500

Tworzenie dynamicznego salda bieżącego

Aby obliczyć saldo bieżące, które aktualizuje się dynamicznie w miarę dodawania wierszy, potrzebujesz formuły, która dodaje obciążenia (Winien) i odejmuje uznania (Ma) od salda z poprzedniego wiersza. Zakładając, że wiersz 1 to nagłówek, a wiersz 2 zawiera pierwszą transakcję, umieść saldo początkowe w komórce G2. W komórce G3 wpisz:

=G2 + E3 - F3

Przeciągnij tę formułę w dół. Aby zapobiec wyświetlaniu przez formułę powtarzających się sum w pustych wierszach pod danymi, zagnieźdź ją w instrukcji IF, która sprawdza, czy kolumna z datą (A) jest pusta:

=IF(A3="", "", G2 + E3 - F3)

Wskazówka: Aby zapewnić spójność i zapobiec literówkom w kolumnie Kod konta, utwórz Plan Kont w osobnej zakładce i użyj walidacji danych do kontrolowania wprowadzanych wartości za pomocą menu rozwijanego. Zaoszczędzi Ci to godzin rozwiązywania problemów, gdy przyjdzie czas na budowę sprawozdań finansowych.

Budowa dynamicznych sprawozdań finansowych (Rachunek zysków i strat oraz Bilans)

Gdy Księga Główna jest prawidłowo ustrukturyzowana, generowanie Rachunku zysków i strat (P&L) oraz Bilansu staje się kwestią agregacji danych na podstawie kodów kont. Najpotężniejszą funkcją do tego zadania jest SUMIFS.

SUMIFS pozwala zsumować wartości w zakresie tylko wtedy, gdy spełniają one wiele kryteriów (np. pasują do określonego kodu konta ORAZ mieszczą się w określonym zakresie dat). Opanowanie sumowania warunkowego za pomocą SUMIF i SUMIFS jest kluczowe dla zautomatyzowanego raportowania finansowego.

Krok po kroku: Automatyzacja Rachunku zysków i strat

1. W nowym arkuszu wylistuj nazwy kont Przychody i Koszty w Kolumnie A, a odpowiadające im Kody Kont w Kolumnie B.
2. W komórkach C1 i D1 wprowadź daty okresu sprawozdawczego (np. Data początkowa: 2023-10-01, Data końcowa: 2023-10-31).
3. W Kolumnie E (kolumna Suma), użyj funkcji SUMIFS, aby pobrać całkowite obciążenia lub uznania dla tego konkretnego kodu konta w danym zakresie dat.

Oto składnia do zsumowania kolumny Ma (Przychody) z arkusza o nazwie "GL" dla Kodu Konta "4010" w październiku:

=SUMIFS(GL!$F:$F, GL!$C:$C, 4010, GL!$A:$A, ">="&$C$1, GL!$A:$A, "<="&$D$1)

Rozłóżmy na czynniki pierwsze, co robi ta formuła:

• GL!$F:$F: Zakres, który chcemy zsumować (kolumna Ma).
• GL!$C:$C: Pierwszy zakres kryteriów (kolumna Kod konta).
• 4010: Konkretny kod konta, którego szukamy.
• GL!$A:$A: Drugi zakres kryteriów (kolumna Data).
• ">="&$C$1: Informuje program Excel, że data musi być większa lub równa naszej dacie początkowej.
• "<="&$D$1: Informuje program Excel, że data musi być mniejsza lub równa naszej dacie końcowej.

Usprawnienie uzgadniania kont bankowych

Uzgadnianie kont bankowych to proces dopasowywania sald w ewidencji księgowej jednostki do odpowiednich informacji na wyciągu bankowym. Excel jest nieoceniony w wykrywaniu rozbieżności, brakujących czeków lub podwójnych opłat bankowych.

Najszybszym sposobem na uzgodnienie długich list transakcji jest eksport wyciągu bankowego do Excela i umieszczenie go obok księgi wewnętrznej. Następnie użyj funkcji wyszukiwania, aby znaleźć pasujące kwoty lub numery referencyjne.

Chociaż VLOOKUP jest powszechnie używany przez wielu księgowych, przejście na metodę wyszukiwania INDEX MATCH oferuje znacznie większą elastyczność, zwłaszcza gdy szukana wartość (np. numer czeku) nie znajduje się w pierwszej kolumnie tabeli.

Użycie EXACT i Formatowania warunkowego

Jeśli obie listy zostały posortowane według daty i kwoty, możesz po prostu odjąć Kwotę bankową od Kwoty z ksiąg. Wynik rzędu 0 oznacza, że się zgadzają.

=Book_Amount - Bank_Amount

Możesz wtedy zastosować Formatowanie warunkowe (Reguły wyróżniania komórek > Równe > 0), aby zmienić kolor wszystkich pasujących wierszy na zielony, sprawiając, że pozostałe niewyróżnione pozycje (pozycje do uzgodnienia) natychmiast się wyróżnią.

Zarządzanie Należnościami i Zobowiązaniami (Raporty wiekowania)

Przepływy pieniężne to krwiobieg każdej firmy. Śledzenie Należności (kto jest Ci winien pieniądze) i Zobowiązań (komu Ty jesteś winien) to codzienne zadanie. Tworzenie Raportu wiekowania w Excelu pomaga zidentyfikować, które faktury są bieżące, przeterminowane lub poważnie opóźnione.

Aby zbudować raport wiekowania, musisz obliczyć różnicę między bieżącą datą a terminem płatności faktury, a następnie przyporządkować tę liczbę do kategorii (np. 0-30 Dni, 31-60 Dni, 61-90 Dni, 90+ Dni).

Przewodnik krok po kroku: Tworzenie formuły wiekowania

Załóżmy, że Kolumna A zawiera Numer faktury, Kolumna B to Nazwa klienta, Kolumna C to Termin płatności, a Kolumna D to Otwarte saldo. W Kolumnie E chcemy obliczyć Dni przeterminowania.

=TODAY() - C2

Funkcja TODAY() zawsze zwraca bieżącą datę. Jeśli wynikiem jest liczba ujemna, faktura nie jest jeszcze wymagalna. Następnie kategoryzujemy dni przeterminowania w Kolumnie F. Możesz użyć testów logicznych i zagnieżdżonych funkcji IF, aby idealnie skategoryzować te zaległe faktury:

=IF(E2<0, "Not Due", IF(E2<=30, "1-30 Days", IF(E2<=60, "31-60 Days", IF(E2<=90, "61-90 Days", "Over 90 Days"))))

Gdy dane są już skategoryzowane, możesz wstawić Tabelę przestawną, aby podsumować nieuregulowane salda według Klienta i Kategorii wiekowania, dając kierownictwu jasny obraz priorytetów windykacyjnych.

5 Niezbędnych formuł Excela, których potrzebuje każdy księgowy

Poza podstawową arytmetyką, nowoczesna księgowość wymaga garści specjalistycznych formuł do zarządzania amortyzacją, rozliczeniami międzyokresowymi i prognozowaniem.

1. EOMONTH (Koniec miesiąca): Niezbędna do prognozowania rozliczeń międzyokresowych i tworzenia standardowych nagłówków miesięcznych. =EOMONTH(A2, 0) zwraca ostatni dzień miesiąca dla daty w komórce A2. Zmiana 0 na 1 daje ostatni dzień następnego miesiąca.
2. EDATE: Idealna do obliczania terminów zapadalności pożyczek lub inwestycji. Dodaje określoną liczbę miesięcy do daty. =EDATE(Start_Date, 12) dodaje dokładnie 12 miesięcy.
3. PMT (Płatność): Używana do obliczania raty pożyczki na podstawie stałych płatności i stałej stopy procentowej. Kluczowa przy amortyzacji długu. Składnia: =PMT(rate, nper, pv).
4. SLN (Amortyzacja liniowa): Zwraca wartość amortyzacji liniowej środka trwałego za jeden okres. Składnia: =SLN(cost, salvage, life).
5. XLOOKUP: Nowoczesny zamiennik dla VLOOKUP oraz INDEX MATCH. Potrafi przeszukiwać w lewo, domyślnie wyświetlać konkretny komunikat w razie błędu i szukać od dołu do góry. Wysoce polecana, jeśli korzystasz z Microsoft 365.

Automatyzacja żmudnej pracy za pomocą Power Query

Kopiowanie i wklejanie danych z oprogramowania księgowego do szablonów Excela każdego miesiąca jest żmudne i podatne na błędy ludzkie. Jeśli co miesiąc ręcznie formatujesz eksporty CSV z programów QuickBooks, Xero lub z Twojego banku, czas usprawnić swój przepływ pracy.

Możesz użyć Power Query do importowania i przekształcania danych jak profesjonalista. Power Query pozwala zbudować połączenie z plikiem danych surowych (np. miesięcznym zrzutem CSV). Możesz skonfigurować reguły, które automatycznie usuną niepotrzebne górne wiersze, zamienią tekst na daty, wypełnią w dół puste numery kont i "odpivotują" (anulują przestawienie) kolumny. W kolejnym miesiącu po prostu wrzucasz nowy plik CSV do folderu, klikasz "Odśwież" w Excelu, a wszystkie kroki formatowania zostają natychmiast zastosowane.

Pisz formuły księgowe szybciej dzięki AI

Zapamiętywanie skomplikowanych, głęboko zagnieżdżonych formuł może być zniechęcające, nawet dla doświadczonych profesjonalistów w dziedzinie finansów. Jeśli kiedykolwiek masz problem z przypomnieniem sobie dokładnej składni zawiłego wyszukiwania, instrukcji IF dla koszyków wiekowania lub skomplikowanego obliczenia amortyzacji, narzędzia takie jak ExcelGPT mogą pomóc. Wystarczy opisać swoje potrzeby w zwykłym języku – np. "oblicz amortyzację liniową środka trwałego w ciągu 5 lat z pominięciem wartości odzysku" – i natychmiast otrzymać gotową, działającą formułę.

Łącząc silne podstawy wiedzy o strukturze Excela z nowoczesną pomocą sztucznej inteligencji, możesz tworzyć niezawodne, wolne od błędów szablony księgowe w ułamku czasu.

Często Zadawane Pytania

Jak chronić moje formuły księgowe przed przypadkowym usunięciem?

Możesz chronić swoje szablony, korzystając z funkcji "Chroń arkusz" w programie Excel. Najpierw zaznacz komórki, w których dozwolone jest wprowadzanie danych (np. szczegóły transakcji), kliknij prawym przyciskiem myszy, wybierz Formatuj komórki, przejdź do zakładki Ochrona i odznacz pole "Zablokowane". Następnie przejdź do zakładki Recenzja na Wstążce i kliknij "Chroń arkusz". Twoje formuły zostaną zablokowane, ale użytkownicy nadal będą mogli wprowadzać dane.

Czy Excel może całkowicie zastąpić dedykowane oprogramowanie księgowe, takie jak QuickBooks?

Chociaż bardzo mała lub nowo założona firma może używać Excela do śledzenia podstawowych przychodów i wydatków, nie jest to zalecane jako stałe zastępstwo dla dedykowanego oprogramowania księgowego. Dedykowane oprogramowanie zapewnia rygorystyczne przestrzeganie zasad podwójnego zapisu, utrzymuje rygorystyczne ścieżki audytu i natywnie obsługuje złożone raportowanie podatkowe. Excel najlepiej sprawdza się jako narzędzie analityczne i raportowe stanowiące uzupełnienie głównego systemu księgowego.

Jaki jest najlepszy sposób na podsumowanie dużych ilości danych z księgi głównej?

Tabele przestawne to najwydajniejszy sposób na podsumowanie tysięcy wierszy danych z księgi. Po wstawieniu Tabeli przestawnej, możesz przeciągnąć "Nazwę konta" do pola Wiersze, "Datę" (pogrupowaną według miesięcy) do pola Kolumny, a "Kwotę" do pola Wartości, aby natychmiast wygenerować krzyżowe podsumowanie finansowe bez konieczności wpisywania ani jednej formuły.

Jak znaleźć zduplikowane wpisy transakcji w mojej księdze?

Najszybszym sposobem jest użycie Formatowania warunkowego. Zaznacz kolumnę zawierającą referencje transakcji (takie jak numery czeków lub ID faktur), przejdź do zakładki Narzędzia główne, kliknij Formatowanie warunkowe, wskaż Reguły wyróżniania komórek i wybierz "Zduplikowane wartości". Excel natychmiast wyróżni każdą transakcję, która została wprowadzona więcej niż raz.