Jak stworzyć profesjonalną fakturę VAT w Excelu

Tworzenie faktury VAT składa się z trzech etapów:

1) stworzenia bazy danych (tj. informacji o artykułach i klientach),

2) utworzenia wersji roboczej faktury VAT (tj. arkusza, do którego wprowadzane są podstawowe dane i liczone formuły),

3) stworzenia obszaru wydruku (tj. dokumentu, który otrzymujemy w formie drukowanej).

I. Pierwszy etap jest najprostszy, ale najbardziej pracochłonny (szczególnie na początku). Polega na wpisaniu do arkusza kalkulacyjnego podstawowych informacji o sprzedawanych produktach (arkusz 1 - który można nazwać "Baza produktów") oraz danych o klientach (arkusz 2 "Baza klientów"). Wypełniając poszczególne komórki arkuszy można wprowadzić wiele informacji definiujących - np. symbol artykułu, jego pełną nazwę, stawkę VAT, jednostkę miary itp. Tak samo można postąpić w przypadku bazy klientów - podając takie dane, jak NIP klienta ¹ , jego numer kolejny w bazie, nazwę pełną, miasto, ulicę, kod pocztowy oraz kraj odbiorcy. Przykładową bazę klientów utworzoną w Excelu przedstawia tabela 1.

Kliknij aby zobaczyć ilustrację.

UWAGA!

Radzimy, by baza klientów i baza towarów była jednym plikiem, zaś stworzona faktura wraz z obszarem wydruku osobnym plikiem. W przypadku gdy sprzedawca będzie chciał zachować każdą fakturę na twardym dysku komputera, nie będzie musiał wraz z nią zapisywać pliku z bazą.

II. Etap drugi to tworzenie "roboczej" wersji faktury. Polega on na opracowaniu odpowiednich funkcji , za pomocą których z bazy będą pobierane informacje o klientach oraz artykułach, a także wyliczane pozycje rachunkowe faktury.

Obszar wersji roboczej ( tabela 2 ) został podzielony na trzy obszary zaznaczone następującymi kolorami:

Kliknij aby zobaczyć ilustrację.

Ciemnoszare pola faktury połączone są z bazą danych o klientach oraz artykułach. Wynikiem tej funkcji jest zawsze numer pozycji danego klienta bądź artykułu w bazie. Jeśli klient bądź artykuł przez nas wpisywany nie występuje w bazie klientów - wynikiem będzie wyrażenie #N/D! , świadczące o popełnionym przez nas błędzie ² .

Składnia funkcji w polu K3 (sprawdzająca odbiorcę) to: ³

=JEŻELI($E$3<>'''';PODAJ.POZYCJĘ($E$3;'[BazaDanychDoFaktury.xls]BazaKlientow'! $A$4:$A$13; 0);'''')

Funkcja ta działa następująco: jeżeli pole E3 (NIP klienta) nie jest puste ⁴ , to podajemy pozycję tego klienta w bazie klientów. Jeśli E3 jest puste, to pozostawiamy pole jako puste, przy czym wyrażenie [BazaDanychDoFaktury.xls]BazaKlientow'' jest adresem pliku bazy klientów, zaś $A$4:$A$13 to obszar tablicy/tabeli, w której funkcja "podaj pozycję" szuka wprowadzonego klienta. Podobnie będzie się działo w pozostałych ciemnoszarych polach. Z tym że komórka K4 będzie sprawdzać płatnika (wówczas zamiast $E$3 w funkcji powyżej występuje $F$3 ), zaś komórki od K12 do K35 sprawdzają wprowadzane w komórkach od B12 do B35 artykuły z bazy artykułów. Zatem zmieni się adres pliku oraz obszar tablicy/tabeli, którą funkcja "podaj pozycję" będzie przeszukiwać.

Pola szare (np.: nr wyrobu, ilość) to komórki, które należy wypełnić "ręcznie":
- C2 - numer kolejny faktury,
- C5 - miejsce załadunku,
- C6 - środek transportu,
- C7 - sposób zapłaty,
- C8 - termin zapłaty jako liczba dni udzielonego kredytu handlowego (termin zapłaty, jego datę zwraca/oblicza komórka C9),
- B12 do B35 - indeksy artykułów (format tych komórek musi być zgodny z formatem komórek z bazy artykułów),
- D12 do D35 - ilość,
- E3 - NIP klienta (format tej komórki należy ustalić tak samo jak w bazie klientów i powinien być wykonany poprzez polecenie menu: "Format" Ţ "komórki" Ţ "specjalne" Ţ "Numer NIP"),
- F3 - podobnie jak E3.

Pola jasnofioletowe (np. nazwa wyrobu, jednostka miary, stawka VAT itp.) to komórki, w których umieszczone są funkcje, a wyniki (cyfry lub tekst) są bezpośrednim skutkiem ich działania. W komórkach zostały umieszczone następujące funkcje:
- C3  =DZIŚ() - funkcja zwraca dzisiejszą datę (ważne, by czas systemowy był zgodny z datą kalendarzową),
- C4  =JEŻELI($K$3=0;0;WYSZUKAJ.PIONOWO($E$3;[BazaDanychDoFaktury.xls] BazaKlientow''!$A$4:$F$13;4;0)). Funkcja "czerpie" dane z bazy klientów i odwołuje się konkretnie do komórek "MiastoKlienta" z tabeli 2 ,
- C9  =DZIŚ()+C8 ,
- W komórkach od A12 do A35 funkcje są te same, ale zmieniają się ich argumenty dla każdej komórki i tak: A12 =JEŻELI($B12<>'''';$A11+1;'''') - funkcja "dostawia" numer kolejny pozycji (dodaje 1 do liczby w komórce wyżej), jeżeli komórka B (indeks wyrobu) nie jest pusta. Przykładowo: dla komórki A14 =JEŻELI($B14<>'''';$A13+1;'''') dając wynik 3, bo 2 (komórka A13) + 1 równa się 3 przy spełnieniu warunku, że B14 nie jest puste.
- W komórkach od C12 do C35 =JEŻELI($K12='''';'''';WYSZUKAJ.PIONOWO($B12;[Baza DanychDoFaktury.xls]BazaArtykulow''!$A$4:$E$23;2;0)) . Jeżeli komórka K12 jest pusta, to funkcja pozostawia pole puste. W przeciwnym razie wyszukuje w bazie artykułów dany indeks i zwraca nam jego nazwę. Wynikiem może być także wyrażenie #N/D!, jeśli wpisany przez nas indeks jest błędny, gdyż funkcja weryfikująca w kolumnie K nie znalazła go w bazie.
- Komórki od E12 do E35; F12 do F35; G12 do G35 działają identycznie jak funkcja opisana powyżej. Zwraca jednostki miary, stawki VAT oraz ceny netto, wyszukując je w bazie artykułów - jeśli komórka w kolumnie K danego wiersza nie jest pusta lub nie jest błędem.
- W komórkach od H12 do H35 funkcja =JEŻELI($B12<>'''';$D12*$G12;'''') mnoży cenę netto przez ilość, jeśli pole "Numer wyrobu" jest wypełnione.
- Funkcja w komórkach od I12 do I35 =JEŻELI($B12<>'''';JEŻELI($F12="ZW";0;$H12*$F12); "") składa się z podwójnego "jeżeli", tzn. jeżeli pole "Numer wyrobu" nie jest puste, to realizowana jest druga funkcja "jeżeli". W przeciwnym razie pole pozostaje puste. Druga funkcja "jeżeli" służy jedynie do tego, by określić działanie, jakie ma wykonać. Gdy stawka VAT jest ZW, to wstawia zero, w pozostałych przypadkach zaś mnoży wartość netto przez stawkę VAT.
- W komórkach od J12 do J35 =JEŻELI($B12<>'''';($H12+$I12);''''). Jeżeli pole w kolumnie "Numer wyrobu" jest puste, to funkcja pozostawia pole w kolumnie "Wartość brutto" puste. W przeciwnym razie dodaje kwotę netto do kwoty VAT.
- Komórki H36; I36; J36 to proste sumy wierszy faktury.
- Funkcja w komórkach od I38 do I42 =SUMA.JEŻELI($F$12:$F$35;$G38;$I$12:$I$35) przeszukuje wiersze od F12 do F35 i dodaje liczby z wierszy I12 do I35, jeśli spełniony będzie warunek, że stawka VAT podana w komórkach od G38 do G42 będzie zgodna ze stawką podaną w komórkach z kolumny F. Czyli dla VAT 22% doda kwoty VAT wyrobów, które mają podaną stawkę 22% w kolumnie F (i tak dla każdej innej stawki),
- C38 = J36,
- C40 =slownie(C38)⁵,
- Komórki od E4 do E8 oraz od F4 do F8 - w komórkach wyszukiwane są za pomocą funkcji "Wyszukaj.pionowo" poszczególne dane, tj. nazwa, ulica, kod pocztowy, miasto i kraj. Funkcja ta jest wynikiem podania posiadanego w bazie klientów numeru NIP odbiorcy i płatnika w komórkach E3 i F3,

III. Tworzenie obszaru wydruku w dużej mierze zależy od "gustu" twórcy faktury, tzn. od tego, jak chciałby, aby faktura wyglądała na "papierze". W artykule przedstawiamy tylko jeden z wielu przykładów faktury VAT ( tabela 3 ).

Kliknij aby zobaczyć ilustrację.

Pola z widocznym tekstem i kolorem szarym w tle to komórki bez funkcji. Są to po prostu wypełnione tekstem komórki. Pola z tekstem/cyframi - ale na białym tle - to pola z funkcjami odwołującymi się do określonych pól z arkusza zawierającego obszar roboczy faktury .

Odwołania proponujemy wykonać następująco: wejść do arkusza z fakturą "roboczą"-> wskazać komórkę, którą chcemy wkleić do obszaru wydruku, i wykonać następujące kroki: "Edycja"->"kopiuj", przejść do arkusza z obszarem wydruku, a następnie: "Edycja"->"wklej specjalnie" i z okna dialogowego wybrać opcję -> "wklej łącze".

Po rozplanowaniu widoku faktury, wklejeniu odwołań, zrobieniu kilku "zabiegów estetycznych" (np. obramowania komórek), należy zaznaczyć obszar faktury, a następnie w menu plik wybrać "Obszar wydruku"->"Ustaw obszar wydruku", co spowoduje, że zaznaczony obszar będzie drukowany na jednej stronie.

Następnie w menu "plik" wybrać "ustawienia strony" i w oknie dialogowym wybrać "zakładkę"->"Nagłówek/stopka" i nacisnąć opcję "nagłówek niestandardowy". Tu zwykle umieszcza się nazwę firmy (np.: w lewej sekcji), zaś w prawej często miejsce/miejscowość i numery telefonów do firmy ⁶ . Podobnie robimy ze stopką, wprowadzając tam np.: w lewej sekcji podpis osoby uprawnionej do odbioru faktury, zaś w prawej - osobę uprawnioną do jej wystawienia.

Gotowa faktura powinna wyglądać tak jak w tabeli 4. Teraz wystarczy ją wydrukować.

Kliknij aby zobaczyć ilustrację.


Krzysztof Jaros

ekspert w zakresie finansów, doktorant w WSPiZ
w Warszawie, pracownik Swedwood Poland Sp. z o.o. Oddział Lubawa

Łukasz Graczyk

specjalista ds. cen i analiz Swedwood Poland Sp. z o.o. Oddział Lubawa

¹ Jak wynika z praktyki, NIP jest najlepszym parametrem do wyszukiwania klienta w bazie, jeśli sprzedawca ma ich wielu.

² Baza odbiorców i płatników jest tą samą bazą.

³ Poza tym umieszczenie takiej funkcji pozwala sprawdzić, czy rzeczywiście w danej pozycji wpisany jest klient, którego szukamy.

⁴ W funkcjach Excela, jeśli chcemy oznaczyć warunek "pustości" pola, określamy to poprzez wpisanie dwóch cudzysłowów.

⁵  Funkcja ta nie jest opcjonalnie dostępna w Excelu, ale możliwa do ściągnięcia z internetu ze strony http://excel.republika.pl/dopobrania/slownie.htm

⁶ W tych miejscach można także umieścić obraz, np.: zeskanowane logo firmy.