Sadržaj
Kada se spominju baze podataka (DB), prvo što pada na pamet su naravno raznorazne poštapalice kao što su SQL, Oracle, 1C ili barem Access. Naravno, radi se o vrlo moćnim (i skupim većinom) programima koji mogu automatizirati rad velike i složene tvrtke s puno podataka. Problem je u tome što ponekad takva snaga jednostavno nije potrebna. Vaša tvrtka može biti mala i s relativno jednostavnim poslovnim procesima, ali je također želite automatizirati. A upravo je malim tvrtkama to često pitanje opstanka.
Za početak, formulirajmo TOR. U većini slučajeva, baza podataka za računovodstvo, npr. klasičnu prodaju, trebala bi moći:
- zadržati u tablicama informacije o robi (cijena), obavljenim transakcijama i kupcima te te tablice međusobno povezati
- imati udobno obrasci za unos podaci (s padajućim popisima itd.)
- automatski ispuniti neke podatke tiskani obrasci (plaćanja, računi itd.)
- izdati potrebne izvješća kontrolirati cjelokupni poslovni proces sa stajališta menadžera
Sve to može Microsoft Excel riješiti uz malo truda. Pokušajmo ovo implementirati.
Korak 1. Početni podaci u obliku tablica
Podatke o proizvodima, prodaji i kupcima pohranit ćemo u tri tablice (na istom listu ili na različitim – svejedno). Temeljno je važno pretvoriti ih u "pametne tablice" s automatskom veličinom, kako o tome ne bi razmišljali u budućnosti. To se radi naredbom Formatiraj kao tablicu kartica Naslovna (Početna — Formatiraj kao tablicu). Na kartici koja se tada pojavljuje Konstruktor (Oblikovati) dajte tablicama opisna imena u polju Naziv tablice za kasniju upotrebu:
Ukupno bismo trebali dobiti tri "pametne tablice":
Imajte na umu da tablice mogu sadržavati dodatne podatke za pojašnjenje. Tako, na primjer, naš Cijenasadrži dodatne podatke o kategoriji (grupa proizvoda, pakiranje, težina i sl.) svakog proizvoda, te tablicu Klijent — grad i regija (adresa, TIN, bankovni podaci itd.) svakog od njih.
Stol Prodaje koristit ćemo kasnije za unos izvršenih transakcija u njega.
Korak 2. Napravite obrazac za unos podataka
Naravno, podatke o prodaji možete unijeti izravno u zelenu tablicu Prodaje, ali to nije uvijek zgodno i podrazumijeva pojavu pogrešaka i tipfelera zbog "ljudskog faktora". Stoga bi bilo bolje napraviti poseban obrazac za unos podataka na posebnom listu ovako:
U ćeliji B3, da biste dobili ažurirani trenutni datum-vrijeme, koristite funkciju TDATA (SADA). Ako vrijeme nije potrebno, onda umjesto toga TDATA funkcija se može primijeniti DANAS (DANAS).
U ćeliji B11 pronađite cijenu odabranog proizvoda u trećem stupcu pametne tablice Cijena koristeći funkciju VPR (VLOOKUP). Ako se dosad niste susreli s tim, prvo pročitajte i pogledajte video ovdje.
U ćeliji B7 trebamo padajući popis s proizvodima iz cjenika. Za to možete koristiti naredbu Podaci – Provjera valjanosti podataka (Provjera valjanosti podataka), odredite kao ograničenje Popis (Popis) a zatim unesite u polje izvor (Izvor) poveznica na stupac Ime i Prezime s našeg pametnog stola Cijena:
Slično se stvara padajući popis s klijentima, ali će izvor biti uži:
=INDIRECT(“Kupci[Klijent]”)
funkcija INDIREKTNO (Neizravno) je u ovom slučaju potreban jer Excel, nažalost, ne razumije izravne veze na pametne tablice u polju Izvor. Ali ista poveznica "zamotana" u funkciju INDIREKTNO u isto vrijeme, radi s praskom (više o tome bilo je u članku o stvaranju padajućih popisa sa sadržajem).
Korak 3. Dodavanje makronaredbe unosa prodaje
Nakon popunjavanja obrasca potrebno je na kraj tablice dodati podatke koji su u njega uneseni Prodaje. Koristeći jednostavne poveznice, formirat ćemo liniju koja će se dodati odmah ispod forme:
Oni. ćelija A20 će imati vezu na =B3, ćelija B20 će imati vezu na =B7, i tako dalje.
Dodajmo sada elementarni makronaredbu u 2 retka koja kopira generirani niz i dodaje ga u tablicu Prodaja. Da biste to učinili, pritisnite kombinaciju Alt + F11 ili gumb Visual Basic kartica razvijač (Programer). Ako ova kartica nije vidljiva, prvo je omogućite u postavkama Datoteka – Opcije – Postavljanje vrpce (Datoteka — Opcije — Prilagodi vrpcu). U prozoru uređivača Visual Basic koji se otvori umetnite novi prazan modul kroz izbornik Umetak – Modul i tamo unesite naš makro kod:
Sub Add_Sell() Worksheets("Input Form").Range("A20:E20").Copy 'Copy data line from the form n = Worksheets("Sales").Range("A100000").End(xlUp) . Red 'odredite broj posljednjeg retka u tablici. Sales Worksheets("Sales").Cells(n + 1, 1).PasteSpecial Paste:=xlPasteValues 'zalijepite u sljedeći prazan red Worksheets("Input Form").Range("B5,B7,B9"). ClearContents 'očisti završni podobrazac Sada možemo dodati gumb našem obrascu za pokretanje stvorene makronaredbe pomoću padajućeg popisa umetak kartica razvijač (Programer — Umetni — Gumb):
Nakon što ga nacrtate, držeći lijevu tipku miša, Excel će vas pitati koji mu makro trebate dodijeliti – odaberite naš makro Dodaj_Prodaj. Tekst na gumbu možete promijeniti desnim klikom na njega i odabirom naredbe Promjena teksta.
Sada, nakon što ispunite formu, možete jednostavno kliknuti na naš gumb, a uneseni podaci će se automatski dodati u tablicu Prodaje, a zatim se obrazac briše za unos novog posla.
Korak 4 Povezivanje tablica
Prije izrade izvješća, povežimo naše tablice kako bismo kasnije mogli brzo izračunati prodaju po regiji, kupcu ili kategoriji. U starijim verzijama Excela to bi zahtijevalo korištenje nekoliko funkcija. VPR (VLOOKUP) za zamjenu cijena, kategorija, kupaca, gradova itd. u tablicu Prodaje. To od nas zahtijeva vrijeme i trud, a također “jede” puno Excel resursa. Počevši od Excela 2013, sve se može puno jednostavnije implementirati postavljanjem odnosa između tablica.
Da biste to učinili, na kartici Datum (Datum) klik odnosi (Odnosi). U prozoru koji se pojavi kliknite gumb stvoriti (novi) i s padajućih popisa odaberite nazive tablica i stupaca prema kojima bi se trebali povezati:
Važna točka: tablice moraju biti navedene ovim redoslijedom, tj. povezana tablica (Cijena) ne smije sadržavati u ključnom stupcu (Ime i Prezime) duplicirani proizvodi, kao što se događa u tablici Prodaje. Drugim riječima, povezana tablica mora biti ona u kojoj biste tražili podatke pomoću VPRako bi se koristio.
Naravno, stol je povezan na sličan način Prodaje sa stolom Klijent zajedničkim stupcem Kupac:
Nakon postavljanja poveznica, prozor za upravljanje vezama se može zatvoriti; ne morate ponavljati ovaj postupak.
Korak 5. Izrađujemo izvješća pomoću sažetka
Sada, da analiziramo prodaju i pratimo dinamiku procesa, stvorimo, na primjer, neku vrstu izvješća pomoću zaokretne tablice. Postavite aktivnu ćeliju u tablicu Prodaje i odaberite karticu na vrpci Umetanje – zaokretna tablica (Umetni — zaokretna tablica). U prozoru koji se otvori Excel će nas pitati o izvoru podataka (tj. tablica Prodaje) i mjesto za učitavanje izvješća (po mogućnosti na novom listu):
Bitno je da je potrebno omogućiti potvrdni okvir Dodajte ove podatke u podatkovni model (Dodaj podatke u podatkovni model) na dnu prozora kako bi Excel shvatio da želimo izraditi izvješće ne samo na trenutnoj tablici, već i koristiti sve odnose.
Nakon što kliknete OK pojavit će se ploča u desnoj polovici prozora Polja zaokretne tablicegdje kliknuti vezu Svividjeti ne samo trenutnu, već sve "pametne tablice" koje se nalaze u knjizi odjednom. A zatim, kao u klasičnoj zaokretnoj tablici, možete jednostavno povući potrebna polja iz bilo koje povezane tablice u područje Filter, Redovi, Stolbcov or Vrijednosti – i Excel će odmah izraditi bilo koje izvješće koje nam je potrebno na listu:
Ne zaboravite da je zaokretnu tablicu potrebno povremeno ažurirati (kada se promijene izvorni podaci) desnim klikom na nju i odabirom naredbe Ažurirajte i spremite (Osvježiti), jer to ne može učiniti automatski.
Također, odabirom bilo koje ćelije u sažetku i pritiskom na tipku Zaokretni grafikon (zaokretni grafikon) kartica Analiza (Analiza) or Parametri (Opcije) možete brzo vizualizirati rezultate izračunate u njemu.
Korak 6. Ispunite ispise
Druga tipična zadaća svake baze podataka je automatsko ispunjavanje raznih tiskanih obrazaca i obrazaca (računi, dostavnice, akti i sl.). Već sam pisao o jednom od načina za to. Ovdje implementiramo, na primjer, ispunjavanje obrasca prema broju računa:
Pretpostavlja se da će u ćeliju C2 korisnik unijeti broj (broj reda u tablici Prodaje, zapravo), a zatim se podaci koji su nam potrebni izvlače pomoću već poznate funkcije VPR (VLOOKUP) i značajke INDEKS (INDEKS).
- Kako koristiti funkciju VLOOKUP za traženje i traženje vrijednosti
- Kako zamijeniti VLOOKUP funkcijama INDEX i MATCH
- Automatsko popunjavanje obrazaca i obrazaca podacima iz tablice
- Izrada izvješća sa zaokretnim tablicama