vsebina
Ob omembi podatkovnih baz (DB) seveda najprej pomislimo na najrazličnejše floskule, kot so SQL, Oracle, 1C ali vsaj Access. Seveda gre za zelo zmogljive (in večinoma drage) programe, ki lahko avtomatizirajo delo velikega in kompleksnega podjetja z veliko podatkov. Težava je v tem, da včasih takšna moč preprosto ni potrebna. Vaše podjetje je morda majhno in z relativno enostavnimi poslovnimi procesi, vendar ga želite tudi avtomatizirati. In prav za mala podjetja je to pogosto vprašanje preživetja.
Za začetek oblikujmo TOR. Podatkovna baza za računovodstvo, na primer klasična prodaja, naj bi v večini primerov omogočala:
- naprej v tabelah podatke o blagu (cena), opravljenih transakcijah in kupcih ter te tabele med seboj povezati
- imeti udobno vnosne obrazce podatki (s spustnimi seznami itd.)
- samodejno izpolni nekaj podatkov tiskani obrazci (plačila, računi itd.)
- izdati potrebno Poročila obvladovati celoten poslovni proces z vidika vodje
Microsoft Excel zmore vse to z malo truda. Poskusimo to uresničiti.
Korak 1. Začetni podatki v obliki tabel
Podatke o izdelkih, prodaji in kupcih bomo hranili v treh tabelah (na istem listu ali na različnih – ni pomembno). Bistvenega pomena je, da jih spremenite v "pametne tabele" s samodejno velikostjo, da o tem ne bi razmišljali v prihodnosti. To se naredi z ukazom Oblikuj kot tabelo tab Domov (Domov — Oblikuj kot tabelo). Na zavihku, ki se nato prikaže Konstruktor (Oblikovanje) dajte tabelam opisna imena v polju Ime tabele za kasnejšo uporabo:
Skupaj bi morali dobiti tri "pametne tabele":
Upoštevajte, da lahko tabele vsebujejo dodatne pojasnjevalne podatke. Tako na primer naš Cenavsebuje dodatne informacije o kategoriji (skupina izdelkov, pakiranje, teža itd.) posameznega izdelka in tabelo Pomoč — mesto in regija (naslov, TIN, bančni podatki itd.) vsakega od njih.
Tabela Prodaja bomo kasneje uporabili za vnos opravljenih transakcij vanj.
2. korak. Ustvarite obrazec za vnos podatkov
Seveda pa lahko podatke o prodaji vnesete neposredno v zeleno tabelo Prodaja, vendar to ni vedno priročno in povzroča pojav napak in tipkarskih napak zaradi "človeškega faktorja". Zato bi bilo bolje narediti poseben obrazec za vnos podatkov na posebnem listu nekaj takega:
V celici B3 uporabite funkcijo za pridobitev posodobljenega trenutnega datuma in časa TDATA (ZDAJ). Če čas ni potreben, potem namesto tega TDATA funkcijo je mogoče uporabiti DANES (DANES).
V celici B11 poiščite ceno izbranega izdelka v tretjem stolpcu pametne tabele Cena uporabo funkcije VPR (VLOOKUP). Če se s tem še niste srečali, potem najprej preberite in si oglejte video tukaj.
V celici B7 potrebujemo spustni seznam z izdelki iz cenika. Za to lahko uporabite ukaz Podatki – Preverjanje podatkov (Podatki — Validacija), navedite kot omejitev Seznam (Seznam) in nato vnesite v polje vir (Vir) povezava do stolpca Ime iz naše pametne mize Cena:
Podobno se ustvari spustni seznam s strankami, vendar bo vir ožji:
=INDIRECT(“Stranke[Stranka]”)
funkcija INDIRECT (POSREDNO) je v tem primeru potreben, ker Excel na žalost ne razume neposrednih povezav do pametnih tabel v polju Izvor. Toda ista povezava je "zavita" v funkcijo INDIRECT hkrati pa deluje s pokom (več o tem je bilo v članku o ustvarjanju spustnih seznamov z vsebino).
3. korak. Dodajanje makra prodajnega vnosa
Ko izpolnite obrazec, morate na konec tabele dodati vanj vnesene podatke Prodaja. S preprostimi povezavami bomo oblikovali vrstico, ki bo dodana tik pod obrazcem:
Tisti. Celica A20 bo imela povezavo do =B3, celica B20 bo imela povezavo do =B7 in tako naprej.
Zdaj pa dodamo 2-vrstični osnovni makro, ki kopira ustvarjeni niz in ga doda v tabelo Prodaja. Če želite to narediti, pritisnite kombinacijo Alt + F11 ali gumb Visual Basic tab razvijalec (Razvijalec). Če ta zavihek ni viden, ga najprej omogočite v nastavitvah Datoteka – Možnosti – Nastavitev traku (Datoteka — Možnosti — Prilagodi trak). V oknu urejevalnika Visual Basic, ki se odpre, prek menija vstavite nov prazen modul Vstavi – Modul in tam vnesite našo makro kodo:
Sub Add_Sell() Worksheets("Input Form").Range("A20:E20").Copy 'Kopiraj podatkovno vrstico iz obrazca n = Worksheets("Sales").Range("A100000").End(xlUp) . Vrstica 'določite številko zadnje vrstice v tabeli. Sales Worksheets("Sales").Cells(n + 1, 1).PasteSpecial Paste:=xlPasteValues 'prilepite v naslednjo prazno vrstico Worksheets("Input Form").Range("B5,B7,B9"). ClearContents 'počisti končni podobrazec Zdaj lahko obrazcu dodamo gumb za zagon ustvarjenega makra s spustnim seznamom Vstavi tab razvijalec (Razvijalec — Vstavi — Gumb):
Ko ga narišete in držite levi gumb miške, vas Excel vpraša, kateri makro mu morate dodeliti – izberite naš makro Dodaj_Prodaj. Besedilo na gumbu lahko spremenite tako, da nanj kliknete z desno tipko miške in izberete ukaz Spremeni besedilo.
Sedaj lahko po izpolnitvi obrazca preprosto kliknete na naš gumb in vneseni podatki bodo samodejno dodani v tabelo Prodaja, nato pa se obrazec počisti za vnos novega posla.
4. korak Povezovanje tabel
Pred izdelavo poročila povežimo naše tabele, da bomo kasneje lahko hitro izračunali prodajo po regijah, kupcih ali kategorijah. V starejših različicah Excela bi to zahtevalo uporabo več funkcij. VPR (VLOOKUP) za zamenjavo cen, kategorij, strank, mest itd. v tabelo Prodaja. To od nas zahteva čas in trud, poleg tega pa »požre« veliko Excelovih virov. Začenši z Excelom 2013 je vse mogoče implementirati veliko bolj preprosto z nastavitvijo odnosov med tabelami.
Če želite to narediti, na zavihku datum (Datum) klik razmerja (Odnosi). V oknu, ki se prikaže, kliknite gumb ustvarjanje (novo) in s spustnih seznamov izberite tabele in imena stolpcev, s katerimi naj bodo povezani:
Pomembna točka: tabele morajo biti določene v tem vrstnem redu, tj povezana tabela (Cena) v ključnem stolpcu ne sme vsebovati (Ime) podvojeni izdelki, kot se zgodi v tabeli Prodaja. Z drugimi besedami, povezana tabela mora biti tista, v kateri bi iskali podatke z uporabo VPRče bi bila uporabljena.
Seveda je miza povezana na podoben način Prodaja z mizo Pomoč po skupnem stolpcu Pomoč:
Po nastavitvi povezav lahko okno za upravljanje povezav zaprete; tega postopka vam ni treba ponoviti.
Korak 5. Gradimo poročila s pomočjo povzetka
Zdaj, da analiziramo prodajo in sledimo dinamiki procesa, ustvarimo na primer nekakšno poročilo z uporabo vrtilne tabele. Nastavi aktivno celico na tabelo Prodaja in izberite zavihek na traku Vstavi – vrtilna tabela (Vstavi — vrtilna tabela). V oknu, ki se odpre, nas bo Excel vprašal o viru podatkov (tj. tabeli Prodaja) in mesto za nalaganje poročila (po možnosti na nov list):
Bistvena točka je, da je treba omogočiti potrditveno polje Dodajte te podatke v podatkovni model (Dodaj podatke v podatkovni model) na dnu okna, tako da Excel razume, da želimo zgraditi poročilo ne samo na trenutni tabeli, ampak uporabiti tudi vse relacije.
Po kliku OK v desni polovici okna se prikaže plošča Polja vrtilne tabelekje klikniti povezavo vsida vidite ne samo trenutno, ampak vse "pametne tabele", ki so v knjigi hkrati. In potem, kot v klasični vrtilni tabeli, lahko preprosto povlečete polja, ki jih potrebujemo, iz katere koli povezane tabele v območje filter, Vrstice, Stolbcov or Vrednote – in Excel bo na listu takoj sestavil vsako poročilo, ki ga potrebujemo:
Ne pozabite, da je treba vrtilno tabelo občasno posodobiti (ko se spremenijo izvorni podatki), tako da nanjo kliknete z desno tipko miške in izberete ukaz Posodobi in shrani (Osveži), ker tega ne more narediti samodejno.
Tudi tako, da izberete poljubno celico v povzetku in pritisnete gumb Vrtilni grafikon (Vrtilni grafikon) tab Analiza (Analiza) or parametri (Opcije) lahko hitro vizualizirate rezultate, izračunane v njem.
Korak 6. Izpolnite natisljive podatke
Druga značilna naloga vsake baze podatkov je avtomatsko izpolnjevanje različnih tiskanih obrazcev in obrazcev (računi, računi, akti itd.). O enem od načinov za to sem že pisal. Tukaj izvajamo na primer izpolnjevanje obrazca po številki računa:
Predpostavlja se, da bo uporabnik v celico C2 vnesel številko (številko vrstice v tabeli Prodaja, pravzaprav), nato pa podatke, ki jih potrebujemo, potegnemo navzgor z uporabo že znane funkcije VPR (VLOOKUP) in značilnosti INDEX (KAZALO).
- Kako uporabljati funkcijo VLOOKUP za iskanje in iskanje vrednosti
- Kako zamenjati VLOOKUP s funkcijama INDEX in MATCH
- Samodejno polnjenje obrazcev in obrazcev s podatki iz tabele
- Ustvarjanje poročil z vrtilnimi tabelami