Turinys
Minint duomenų bazes (DB), pirmas dalykas, kuris ateina į galvą, žinoma, yra visokie populiarūs žodžiai, tokie kaip SQL, Oracle, 1C ar bent jau Access. Žinoma, tai labai galingos (ir dažniausiai brangios) programos, galinčios automatizuoti didelės ir sudėtingos, daug duomenų turinčios įmonės darbą. Bėda ta, kad kartais tokios galios tiesiog nereikia. Jūsų verslas gali būti mažas ir su gana paprastais verslo procesais, bet jūs taip pat norite jį automatizuoti. Ir būtent mažoms įmonėms tai dažnai yra išlikimo reikalas.
Pirmiausia suformuluokime TOR. Daugeliu atvejų apskaitos duomenų bazė, pavyzdžiui, klasikiniai pardavimai, turėtų turėti galimybę:
- išlaikyti lentelėse informaciją apie prekes (kainą), atliktas operacijas ir klientus ir šias lenteles susieti tarpusavyje
- turėti patogus įvesties formas duomenys (su išskleidžiamaisiais sąrašais ir kt.)
- automatiškai užpildo kai kuriuos duomenis spausdintos formos (mokėjimai, sąskaitos ir kt.)
- išduoti reikalingą ataskaitos valdyti visą verslo procesą vadovo požiūriu
„Microsoft Excel“ gali visa tai susidoroti su šiek tiek pastangų. Pabandykime tai įgyvendinti.
1 žingsnis. Pradiniai duomenys lentelių pavidalu
Informaciją apie produktus, pardavimus ir klientus kaupsime trijose lentelėse (tame pačiame lape ar skirtingose – nesvarbu). Iš esmės svarbu juos paversti „išmaniaisiais stalais“ su automatiniu dydžiu, kad ateityje apie tai negalvotumėte. Tai daroma su komanda Formatuokite kaip lentelę kortelė Pagrindinis (Pagrindinis – formatuoti kaip lentelę). Tada rodomame skirtuke Konstruktorius (Dizainas) lauke nurodykite lentelių aprašomuosius pavadinimus Lentelės pavadinimas vėlesniam naudojimui:
Iš viso turėtume gauti tris „išmaniąsias lenteles“:
Atkreipkite dėmesį, kad lentelėse gali būti papildomų patikslinančių duomenų. Taigi, pavyzdžiui, mūsų Kaina yra papildomos informacijos apie kiekvienos prekės kategoriją (prekių grupę, pakuotę, svorį ir kt.) bei lentelę klientas — kiekvieno iš jų miestas ir regionas (adresas, TIN, banko duomenys ir kt.).
Lentelė Pardavimas vėliau naudosime užbaigtoms operacijoms įvesti.
2 veiksmas. Sukurkite duomenų įvedimo formą
Žinoma, pardavimo duomenis galite įvesti tiesiai į žaliąją lentelę Pardavimas, tačiau tai ne visada patogu ir dėl „žmogiškojo faktoriaus“ atsiranda klaidų ir rašybos klaidų. Todėl geriau būtų sukurti specialią formą duomenims įvesti atskirame lape, pavyzdžiui:
B3 langelyje, norėdami gauti atnaujintą dabartinę datą-laiką, naudokite funkciją TDATA (DABAR). Jei laiko nereikia, tada vietoj to TDATA funkcija gali būti pritaikyta ŠIANDIEN (ŠIANDIEN).
B11 langelyje trečiame išmaniosios lentelės stulpelyje raskite pasirinktos prekės kainą Kaina naudojant funkciją VPR (ŽIŪRĖTI). Jei anksčiau su tuo nesusidūrėte, pirmiausia perskaitykite ir žiūrėkite vaizdo įrašą čia.
B7 langelyje mums reikia išskleidžiamojo sąrašo su produktais iš kainoraščio. Tam galite naudoti komandą Duomenys – duomenų patvirtinimas (Duomenys – patvirtinimas), nurodykite kaip apribojimą sąrašas (sąrašas) ir tada įveskite lauką Šaltinis (Šaltinis) nuoroda į stulpelį Vardas nuo mūsų išmaniojo stalo Kaina :
Panašiai sukuriamas išskleidžiamasis sąrašas su klientais, tačiau šaltinis bus siauresnis:
=NETIESIOGINĖ ("Klientai[Klientas]")
Funkcija NETIESIOGINĖS (NETIESIOGINIS) šiuo atveju reikalinga, nes Excel, deja, nesupranta tiesioginių nuorodų į išmaniąsias lenteles lauke Šaltinis. Bet ta pati nuoroda „įvyniota“ į funkciją NETIESIOGINĖS tuo pačiu jis veikia su kaupu (daugiau apie tai buvo straipsnyje apie išskleidžiamųjų sąrašų su turiniu kūrimą).
3 veiksmas. Pardavimo įrašo makrokomandos pridėjimas
Užpildę formą, į ją įvestus duomenis reikia pridėti lentelės gale Pardavimas. Naudodami paprastas nuorodas sudarysime eilutę, kuri bus pridėta tiesiai po forma:
Tie. langelis A20 turės nuorodą į =B3, langelis B20 turės nuorodą į =B7 ir pan.
Dabar pridėkime 2 eilučių elementarią makrokomandą, kuri nukopijuoja sugeneruotą eilutę ir prideda ją prie pardavimo lentelės. Norėdami tai padaryti, paspauskite derinį Alt + F11 arba mygtuką "Visual Basic" kortelė ryškalas (Programuotojas). Jei šio skirtuko nematote, pirmiausia įjunkite jį nustatymuose Failas – Parinktys – Juostos sąranka (Failas – Parinktys – Tinkinti juostelę). Atsidariusiame Visual Basic redaktoriaus lange įdėkite naują tuščią modulį per meniu Įterpimas – modulis ir ten įveskite mūsų makrokomandos kodą:
Sub Add_Sell() Worksheets("Įvesties forma").Range("A20:E20").Kopijuoti 'Nukopijuoti duomenų eilutę iš formos n = Worksheets("Sales").Range("A100000").End(xlUp) . Eilutė „nustatykite paskutinės lentelės eilutės numerį. Pardavimo darbalapiai ("Pardavimas").Ląstelės (n + 1, 1).PasteSpecial Paste:=xlPasteValues'įklijuokite į kitą tuščią eilutę Worksheets ("Įvesties forma"). Diapazonas ("B5,B7,B9"). „ClearContents“ išvalymo antrinė forma Dabar prie savo formos galime pridėti mygtuką, kad paleistume sukurtą makrokomandą naudodami išskleidžiamąjį sąrašą įsiuvas kortelė ryškalas (Kūrėjas – įterpti – mygtukas):
Nupiešę, laikydami nuspaudę kairįjį pelės mygtuką, Excel paklaus, kurią makrokomandą jai reikia priskirti – pasirinkite mūsų makrokomandą Add_Sell. Mygtuko tekstą galite pakeisti dešiniuoju pelės mygtuku spustelėję jį ir pasirinkę komandą Keisti tekstą.
Dabar, užpildę formą, galite tiesiog paspausti mūsų mygtuką ir įvesti duomenys bus automatiškai įtraukti į lentelę Pardavimas, tada forma išvaloma, kad būtų galima sudaryti naują sandorį.
4 žingsnis Lentelių susiejimas
Prieš kurdami ataskaitą, susiekite lenteles, kad vėliau galėtume greitai apskaičiuoti pardavimus pagal regioną, klientą ar kategoriją. Senesnėse „Excel“ versijose tam reikėtų naudoti kelias funkcijas. VPR (ŽIŪRĖTI) už lentelę pakeisti kainas, kategorijas, klientus, miestus ir kt Pardavimas. Tai reikalauja iš mūsų laiko ir pastangų, taip pat „suvalgo“ daug Excel resursų. Pradedant nuo Excel 2013, viską galima įgyvendinti daug paprasčiau, nustatant ryšius tarp lentelių.
Norėdami tai padaryti, skirtuke Duomenys (Data) spragtelėjimas santykis (Santykiai). Atsidariusiame lange spustelėkite mygtuką kurti (nauja) ir iš išskleidžiamųjų sąrašų pasirinkite lenteles ir stulpelių pavadinimus, pagal kuriuos jie turėtų būti susieti:
Svarbus dalykas: lentelės turi būti nurodytos tokia tvarka, ty susieta lentelė (Kaina ) neturi būti rakto stulpelyje (Vardas) pasikartojančius produktus, kaip tai atsitinka lentelėje Pardavimas. Kitaip tariant, susijusi lentelė turi būti ta, kurioje ieškotumėte duomenų naudodami VPRjei jis būtų naudojamas.
Žinoma, lentelė jungiama panašiai Pardavimas su stalu klientas pagal bendrą stulpelį Klientas:
Nustačius nuorodas galima uždaryti nuorodų tvarkymo langą; Jums nereikia kartoti šios procedūros.
5 veiksmas. Sudarome ataskaitas naudodami santrauką
Dabar, norėdami analizuoti pardavimus ir stebėti proceso dinamiką, sukurkime, pavyzdžiui, kokią nors ataskaitą naudodami suvestinę lentelę. Nustatyti aktyvų langelį į lentelę Pardavimas ir pasirinkite juostelės skirtuką Įterpimas – PivotTable (Įterpti – Suvestinė lentelė). Atsidariusiame lange „Excel“ paklaus mūsų apie duomenų šaltinį (ty lentelę Pardavimas) ir vieta, kur įkelti ataskaitą (geriausia naujame lape):
Svarbus dalykas yra tai, kad būtina įjungti žymimąjį laukelį Pridėkite šiuos duomenis prie duomenų modelio (Pridėti duomenis prie duomenų modelio) lango apačioje, kad „Excel“ suprastų, jog norime sukurti ataskaitą ne tik dabartinėje lentelėje, bet ir naudoti visus ryšius.
Spustelėję OK dešinėje lango pusėje atsiras skydelis Suvestinės lentelės laukaikur paspausti nuorodą visipamatyti ne tik dabartinę, bet ir visas knygoje esančias „išmaniąsias lenteles“ iš karto. Ir tada, kaip ir klasikinėje suvestinėje lentelėje, galite tiesiog nuvilkti mums reikalingus laukus iš bet kurios susijusios lentelės į sritį Filtruoti, Eilutės, Stolbcovas or Vertybės – ir „Excel“ lape akimirksniu sukurs bet kokią mums reikalingą ataskaitą:
Nepamirškite, kad suvestinę lentelę reikia periodiškai atnaujinti (kai pasikeičia šaltinio duomenys) spustelėjus ją dešiniuoju pelės mygtuku ir pasirenkant komandą Atnaujinkite ir išsaugokite (Atnaujinti), nes negali to padaryti automatiškai.
Be to, pažymėdami bet kurį suvestinėje esantį langelį ir paspausdami mygtuką Sukamoji diagrama (Suvestinė diagrama) kortelė Analizė (Analizė) or Parametrai (Galimybės) galite greitai vizualizuoti joje apskaičiuotus rezultatus.
6 veiksmas. Užpildykite spausdinamus elementus
Kitas tipiškas bet kurios duomenų bazės uždavinys – automatinis įvairių spausdintų formų ir formų (sąskaitų faktūrų, sąskaitų faktūrų, aktų ir kt.) pildymas. Jau rašiau apie vieną iš būdų tai padaryti. Čia įgyvendiname, pavyzdžiui, formos užpildymą pagal sąskaitos numerį:
Daroma prielaida, kad langelyje C2 vartotojas įves skaičių (lentelės eilutės numerį Pardavimas, iš tikrųjų), ir tada mums reikalingi duomenys ištraukiami naudojant jau pažįstamą funkciją VPR (ŽIŪRĖTI) ir funkcijos RODYKLĖ (INDEKSAS).
- Kaip naudoti VLOOKUP funkciją norint ieškoti ir ieškoti verčių
- Kaip VLOOKUP pakeisti INDEX ir MATCH funkcijomis
- Automatinis formų ir formų pildymas duomenimis iš lentelės
- Ataskaitų kūrimas naudojant „PivotTable“.