Sisu
Andmebaase (DB) mainides tulevad loomulikult esimese asjana meelde kõikvõimalikud moesõnad nagu SQL, Oracle, 1C või vähemalt Access. Loomulikult on tegemist väga võimsate (ja enamasti kallite) programmidega, mis suudavad automatiseerida suure ja keerulise, suure andmemahuga ettevõtte tööd. Probleem on selles, et mõnikord pole sellist jõudu lihtsalt vaja. Teie ettevõte võib olla väike ja suhteliselt lihtsate äriprotsessidega, kuid soovite seda ka automatiseerida. Ja just väikeste ettevõtete jaoks on see sageli ellujäämise küsimus.
Alustuseks sõnastame TOR. Enamikul juhtudel peaks raamatupidamise andmebaas, näiteks klassikaline müük, suutma:
- hoidma tabelites teave kaupade (hinna), sooritatud tehingute ja klientide kohta ning siduda need tabelid omavahel
- on mugav sisestusvormid andmed (koos ripploenditega jne)
- täitke automaatselt mõned andmed trükitud vormid (maksed, arved jne)
- väljastada vajalik aruanded juhtida kogu äriprotsessi juhi vaatenurgast
Microsoft Excel saab selle kõigega vähese vaevaga hakkama. Proovime seda rakendada.
Samm 1. Algandmed tabelite kujul
Info toodete, müügi ja klientide kohta salvestame kolme tabelisse (samale lehele või erinevatele – vahet pole). Põhimõtteliselt on oluline muuta need automaatse suurusega "nutikateks tabeliteks", et mitte tulevikus sellele mõelda. Seda tehakse käsuga Vorminda tabelina tab Avaleht (Avaleht — vorminda tabelina). Vahekaardil, mis seejärel kuvatakse Ehitaja (Kujundus) andke väljale tabelitele kirjeldavad nimed Tabeli nimi hilisemaks kasutamiseks:
Kokku peaksime saama kolm "nutikat tabelit":
Pange tähele, et tabelid võivad sisaldada täiendavaid täpsustavaid andmeid. Nii näiteks meie Hindsisaldab lisateavet iga toote kategooria (tootegrupp, pakend, kaal jne) ja tabeli kohta klient — igaühe linn ja piirkond (aadress, TIN, pangarekvisiidid jne).
Tabel Müük kasutame seda hiljem lõpetatud tehingute sisestamiseks.
Samm 2. Looge andmesisestusvorm
Müügiandmeid saab loomulikult sisestada otse rohelisse tabelisse Müük, kuid see ei ole alati mugav ja toob kaasa vigade ja kirjavigade ilmnemise "inimfaktori" tõttu. Seetõttu oleks parem teha andmete sisestamiseks spetsiaalne vorm sellisele eraldi lehele:
Lahtris B3 kasutage värskendatud praeguse kuupäeva-kellaaja hankimiseks funktsiooni TDATA (KOHE). Kui aega pole vaja, siis hoopis TDATA funktsiooni saab rakendada TÄNA (TÄNA).
Lahtrist B11 leidke nutitabeli kolmandast veerust valitud toote hind Hind funktsiooni kasutades VPR (OTSING). Kui te pole sellega varem kokku puutunud, siis esmalt lugege ja vaadake videot siit.
Lahtris B7 vajame rippmenüüd hinnakirja toodetega. Selleks saate kasutada käsku Andmed – andmete valideerimine (Andmed – valideerimine), määrake piiranguna nimekiri (loend) ja seejärel sisestage väljale allikas (Allikas) link veerule Nimi meie nutilauast Hind:
Samamoodi luuakse rippmenüü klientidega, kuid allikas on kitsam:
=KAUDNE("Kliendid[klient]"
funktsioon KAUDSEID (Kaudne) on antud juhul vajalik, sest Excel kahjuks ei mõista otselinke nutikatele tabelitele väljal Source. Kuid sama link "mähitud" funktsiooni KAUDSEID samas töötab see täie hooga (sellest oli lähemalt artiklis sisuga rippmenüüde loomisest).
Samm 3. Müügikirje makro lisamine
Pärast vormi täitmist tuleb lisada sinna sisestatud andmed tabeli lõppu Müük. Kasutades lihtsaid linke, moodustame otse vormi alla lisatava rea:
Need. lahtris A20 on link =B3, lahtris B20 on link =B7 ja nii edasi.
Nüüd lisame 2-realise elementaarmakro, mis kopeerib genereeritud stringi ja lisab selle Müügi tabelisse. Selleks vajutage kombinatsiooni Alt + F11 või nuppu Visual Basic tab arendaja (Arendaja). Kui see vahekaart pole nähtav, lubage see esmalt seadetes Fail – Valikud – Lindi seadistamine (Fail — Valikud — Kohanda linti). Avanevas Visual Basicu redaktori aknas sisestage menüü kaudu uus tühi moodul Sisesta – moodul ja sisestage sinna meie makrokood:
Sub Add_Sell() Worksheets("Sisestusvorm").Range("A20:E20").Copy 'Kopeerige andmerida vormilt n = Worksheets("Sales").Range("A100000").End(xlUp) . Rida määrake tabeli viimase rea number. Müügitöölehed("Müük").Cells(n + 1, 1).PasteSpecial Paste:=xlPasteValues'kleebi järgmisele tühjale reale Töölehed("Sisestusvorm").Vahemik("B5,B7,B9"). ClearContents'i selge lõpu alamvorm Nüüd saame lisada oma vormile nupu loodud makro käivitamiseks ripploendi abil Sisesta tab arendaja (Arendaja – Sisesta – nupp):
Pärast selle joonistamist, hoides hiire vasakut nuppu all, küsib Excel, millise makro peate sellele määrama – valige meie makro Add_Sell. Nupu teksti saab muuta, klõpsates sellel hiire parema nupuga ja valides käsu Muuda teksti.
Nüüd, pärast vormi täitmist, võite lihtsalt klõpsata meie nupul ja sisestatud andmed lisatakse automaatselt tabelisse Müükja seejärel vorm tühjendatakse uue tehingu sõlmimiseks.
4. samm Tabelite ühendamine
Enne aruande koostamist lingime oma tabelid kokku, et saaksime hiljem kiiresti arvutada müüki piirkonna, kliendi või kategooria kaupa. Exceli vanemates versioonides eeldaks see mitme funktsiooni kasutamist. VPR (OTSING) hindade, kategooriate, klientide, linnade jne asendamiseks tabeliga Müük. See nõuab meilt aega ja vaeva ning “sööb” ära ka palju Exceli ressursse. Alates Excel 2013-st saab kõike palju lihtsamalt rakendada, seadistades tabelitevahelisi seoseid.
Selleks vahekaardil kuupäev (Kuupäev) klõps suhe (Suhted). Ilmuvas aknas klõpsake nuppu Looma (uus) ja valige ripploenditest tabelite ja veergude nimed, millega need peaksid olema seotud:
Oluline punkt: tabelid tuleb täpsustada selles järjekorras, st lingitud tabel (Hind) ei tohi sisaldada võtme veerus (Nimi) dubleerivad tooted, nagu see tabelis on näidatud Müük. Teisisõnu, seotud tabel peab olema selline, millest otsiksite andmeid VPRkui seda kasutataks.
Loomulikult on laud ühendatud sarnaselt Müük koos lauaga klient ühise veeru järgi Klient:
Pärast linkide seadistamist saab linkide haldamise akna sulgeda; te ei pea seda protseduuri kordama.
5. samm. Koostame aruanded kokkuvõtte abil
Nüüd, et analüüsida müüki ja jälgida protsessi dünaamikat, koostame pivot tabeli abil näiteks mingisuguse aruande. Määra aktiivne lahter tabeliks Müük ja valige lindil vahekaart Sisesta – PivotTable (Sisesta – Pivot Table). Avanevas aknas küsib Excel meilt andmeallika (st tabeli) kohta Müük) ja aruande üleslaadimise koht (soovitavalt uuel lehel):
Oluline on see, et märkeruut on vaja lubada Lisage need andmed andmemudelisse (Andmete lisamine andmemudelisse) akna allservas, et Excel mõistaks, et soovime koostada aruande mitte ainult praeguse tabeli kohta, vaid kasutada ka kõiki seoseid.
Pärast klõpsamist nupul OK akna paremasse poolde ilmub paneel Pivot-tabeli väljadkus linki klõpsata Materjal: BPA ja flataatide vaba plastiket näha mitte ainult praegust, vaid kõiki raamatus olevaid “nutitabeleid” korraga. Ja seejärel, nagu klassikalises pivot-tabelis, saate lihtsalt lohistada vajalikud väljad mis tahes seotud tabelitest alale. filtrid, Read, Stolbtsov or Väärtused – ja Excel koostab lehel kohe kõik vajalikud aruanded:
Ärge unustage, et pivot-tabelit tuleb perioodiliselt värskendada (kui lähteandmed muutuvad), paremklõpsates sellel ja valides käsu Värskenda ja salvesta (Värskenda), sest see ei saa seda automaatselt teha.
Samuti valides kokkuvõttes suvalise lahtri ja vajutades nuppu Pivot diagramm (Pöörddiagramm) tab analüüs (Analüüs) or parameetrid (Valikud) saate selles arvutatud tulemusi kiiresti visualiseerida.
6. samm. Täitke prinditavad andmed
Iga andmebaasi teine tüüpiline ülesanne on erinevate trükitud vormide ja blankettide (arved, arved, aktid jne) automaatne täitmine. Kirjutasin juba ühest viisist, kuidas seda teha. Siin rakendame näiteks vormi täitmist kontonumbri järgi:
Eeldatakse, et lahtrisse C2 sisestab kasutaja numbri (tabeli rea number Müük, tegelikult) ja seejärel tõmmatakse meile vajalikud andmed juba tuttava funktsiooni abil üles VPR (OTSING) ja funktsioonid INDEX (INDEX).
- Funktsiooni VLOOKUP kasutamine väärtuste otsimiseks ja otsimiseks
- Kuidas asendada VLOOKUP funktsioonidega INDEX ja MATCH
- Vormide ja vormide automaatne täitmine andmetega tabelist
- Aruannete loomine PivotTable-liigendtabelitega