sadržaj
Kada se spominju baze podataka (DB), prvo što vam padne na pamet su, naravno, sve vrste fraza kao što su SQL, Oracle, 1C ili barem Access. Naravno, radi se o veoma moćnim (i uglavnom skupim) programima koji mogu automatizovati rad velike i složene kompanije sa puno podataka. Problem je u tome što ponekad takva moć jednostavno nije potrebna. Vaše preduzeće može biti malo i sa relativno jednostavnim poslovnim procesima, ali takođe želite da ga automatizujete. A za male kompanije to je često pitanje opstanka.
Za početak, hajde da formulišemo TOR. U većini slučajeva, baza podataka za računovodstvo, na primjer, klasična prodaja bi trebala biti u mogućnosti:
- zadržati u tabelama informacije o robi (cijeni), obavljenim transakcijama i kupcima i povežite ove tabele među sobom
- udobno forme za unos podatke (sa padajućim listama, itd.)
- automatski popunjava neke podatke štampane forme (plaćanja, računi, itd.)
- izdati neophodno izvještaja da kontroliše kompletan poslovni proces sa stanovišta menadžera
Microsoft Excel može sve ovo podnijeti uz malo truda. Pokušajmo ovo implementirati.
Korak 1. Početni podaci u obliku tabela
Informacije o proizvodima, prodaji i kupcima ćemo pohraniti u tri tabele (na istom listu ili na različitim – nije bitno). Od suštinske je važnosti pretvoriti ih u „pametne stolove“ sa automatskom veličinom, kako ne bi razmišljali o tome u budućnosti. Ovo se radi pomoću komande Formatirajte kao tabelu tabulator Početna (Početna — Format kao tabela). Na kartici koja se tada pojavljuje konstruktor (Dizajn) dajte tablicama opisna imena u polju Naziv tabele za kasniju upotrebu:
Ukupno bismo trebali dobiti tri "pametna stola":
Imajte na umu da tabele mogu sadržavati dodatne pojašnjene podatke. Tako, na primjer, naš Cijenasadrži dodatne informacije o kategoriji (grupa proizvoda, ambalaža, težina itd.) svakog proizvoda, te tabela Klijent — grad i region (adresa, PIB, bankovni podaci, itd.) svakog od njih.
sto prodajni ćemo kasnije koristiti za unos izvršenih transakcija u njega.
Korak 2. Kreirajte obrazac za unos podataka
Naravno, podatke o prodaji možete unijeti direktno u zelenu tabelu prodajni, ali to nije uvijek zgodno i za sobom povlači pojavu grešaka i grešaka u kucanju zbog „ljudskog faktora“. Stoga bi bilo bolje napraviti poseban obrazac za unos podataka na posebnom listu otprilike ovako:
U ćeliji B3, da biste dobili ažurirani trenutni datum-vrijeme, koristite funkciju TDATA (SAD). 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ćoj koloni pametne tablice Cijena koristeći funkciju VPR (Vlookup). Ako se do sada niste susreli, prvo pročitajte i pogledajte video ovdje.
U ćeliji B7 potrebna nam je padajuća lista sa proizvodima iz cjenovnika. Za ovo možete koristiti naredbu Podaci – Validacija podataka (Podaci — Validacija), navedite kao ograničenje Popis (Lista) a zatim unesite u polje izvor (Izvor) link do kolone Ime sa našeg pametnog stola Cijena:
Slično, kreira se padajući popis sa klijentima, ali će izvor biti uži:
=INDIREKTNO(“Kupci [Klijent]”)
funkcija INDIREKTNO (INDIREKTNO) je potrebno, u ovom slučaju, jer Excel, nažalost, ne razumije direktne veze do pametnih tabela u polju Izvor. Ali ista veza je "umotana" u funkciju INDIREKTNO u isto vrijeme, radi sa praskom (više o tome bilo je u članku o kreiranju padajućih lista sa sadržajem).
Korak 3. Dodavanje makroa za unos prodaje
Nakon popunjavanja obrasca potrebno je da na kraj tabele dodate podatke unete u njega prodajni. Koristeći jednostavne veze, formiraćemo liniju koja se dodaje odmah ispod obrasca:
One. ćelija A20 će imati vezu sa =B3, ćelija B20 će imati vezu sa =B7, i tako dalje.
Sada dodajmo elementarni makro u 2 reda koji kopira generirani niz i dodaje ga u tablicu Sales. Da biste to učinili, pritisnite kombinaciju Alt + F11 ili dugme Visual Basic tabulator Developer (programer). Ako ova kartica nije vidljiva, prvo je omogućite u postavkama Datoteka – Opcije – Podešavanje trake (Datoteka — Opcije — Prilagodi traku). U prozoru Visual Basic editora koji se otvori, umetnite novi prazan modul kroz meni Umetak – Modul i tamo unesite naš makro kod:
Sub Add_Sell() Worksheets("Input Form").Range("A20:E20").Copy 'Kopiraj liniju podataka iz obrasca n = Worksheets("Sales").Range("A100000").End(xlUp) . Red 'određuje broj zadnjeg reda u tabeli. Sales Worksheets("Sales").Cells(n + 1, 1).PasteSpecial Paste:=xlPasteValues 'zalijepite u sljedeći prazan red Radni listovi("Form za unos").Raspon("B5,B7,B9"). ClearContents 'očisti podobrazac kraja Sada možemo dodati dugme u naš obrazac za pokretanje kreiranog makroa pomoću padajuće liste Ubacite tabulator Developer (Programer — Umetanje — Dugme):
Nakon što ga nacrtate, držeći lijevu tipku miša, Excel će vas pitati koji makro treba da mu dodijelite – odaberite naš makro Dodaj_Prodaj. Tekst na dugmetu možete promeniti tako što ćete kliknuti desnim tasterom miša na njega i izabrati komandu Promijenite tekst.
Sada, nakon popunjavanja obrasca, možete jednostavno kliknuti na naše dugme, a uneseni podaci će se automatski dodati u tabelu prodajni, a zatim se obrazac briše za ulazak u novu ponudu.
Korak 4 Povezivanje tabela
Prije izrade izvještaja, povežimo naše tabele zajedno kako bismo kasnije mogli brzo izračunati prodaju po regionu, 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 prodajni. Ovo od nas zahteva vreme i trud, a takođe „jede“ mnogo Excel resursa. Počevši od Excel 2013, sve se može implementirati mnogo jednostavnije postavljanjem odnosa između tabela.
Da biste to učinili, na kartici podaci (Datum) klik Odnosi (Odnosi). U prozoru koji se pojavi kliknite na dugme stvoriti (novo) i izaberite sa padajućih lista tabele i nazive kolona po kojima bi trebalo da budu povezane:
Važna stvar: tabele moraju biti specificirane ovim redoslijedom, tj. povezana tabela (Cijena) ne smije sadržavati u ključnoj koloni (Ime) dupli proizvodi, kao što se dešava u tabeli prodajni. Drugim riječima, pridružena tablica mora biti ona u kojoj biste tražili podatke koristeći VPRako bi se koristio.
Naravno, stol je povezan na sličan način prodajni sa stolom Klijent po zajedničkoj koloni kupac:
Nakon postavljanja linkova, prozor za upravljanje vezama se može zatvoriti; ne morate ponavljati ovu proceduru.
Korak 5. Izrađujemo izvještaje koristeći sažetak
Sada, da bismo analizirali prodaju i pratili dinamiku procesa, napravimo, na primjer, neku vrstu izvještaja koristeći pivot tablicu. Postavite aktivnu ćeliju na tabelu prodajni i odaberite karticu na traci Umetanje – zaokretna tabela (Insert — Pivot Table). U prozoru koji se otvori, Excel će nas pitati o izvoru podataka (tj prodajni) i mjesto za učitavanje izvještaja (po mogućnosti na novom listu):
Bitna stvar je da je potrebno omogućiti checkbox Dodajte ove podatke u model podataka (Dodajte podatke u model podataka) na dnu prozora tako da Excel shvati da želimo da napravimo izveštaj ne samo o trenutnoj tabeli, već i da koristimo sve relacije.
Posle klika na OK panel će se pojaviti u desnoj polovini prozora Polja zaokretne tabelegdje kliknuti na link Sveda vidite ne samo trenutni, već i sve „pametne stolove“ koji se nalaze u knjizi odjednom. A zatim, kao u klasičnoj pivot tablici, možete jednostavno prevući polja koja su nam potrebna iz bilo koje povezane tablice u područje Filter, Rows, Stolbcov or vrijednosti – i Excel će odmah napraviti bilo koji izvještaj koji nam je potreban na listu:
Ne zaboravite da je pivot tablicu potrebno periodično ažurirati (kada se izvorni podaci mijenjaju) desnim klikom na nju i odabirom naredbe Ažurirajte i sačuvajte (Osvježiti), jer to ne može učiniti automatski.
Također, odabirom bilo koje ćelije u sažetku i pritiskom na dugme Pivot Chart (Pivot Chart) tabulator analiza (analiza) or parametri (Opcije) možete brzo vizualizirati rezultate izračunate u njemu.
Korak 6. Popunite ispise
Još jedan tipičan zadatak svake baze podataka je automatsko popunjavanje raznih štampanih obrazaca i obrazaca (fakture, fakture, akti, itd.). Već sam pisao o jednom od načina da se to uradi. Ovdje implementiramo, na primjer, popunjavanje obrasca prema broju računa:
Pretpostavlja se da će u ćeliju C2 korisnik unijeti broj (broj reda u tabeli prodajni, zapravo), a zatim se podaci koji su nam potrebni izvlače pomoću već poznate funkcije VPR (Vlookup) i karakteristike INDEX (INDEX).
- Kako koristiti funkciju VLOOKUP za traženje i traženje vrijednosti
- Kako zamijeniti VLOOKUP sa funkcijama INDEX i MATCH
- Automatsko popunjavanje obrazaca i obrazaca podacima iz tabele
- Kreiranje izvještaja sa zaokretnim tabelama