Sisällys
Kun mainitaan tietokannat (DB), ensimmäisenä mieleen tulevat tietysti kaikenlaiset muotisanat, kuten SQL, Oracle, 1C tai ainakin Access. Tietenkin nämä ovat erittäin tehokkaita (ja suurimmaksi osaksi kalliita) ohjelmia, jotka voivat automatisoida suuren ja monimutkaisen yrityksen työn, jolla on paljon tietoa. Ongelmana on, että joskus tällaista tehoa ei yksinkertaisesti tarvita. Yrityksesi voi olla pieni ja suhteellisen yksinkertaisia liiketoimintaprosesseja, mutta haluat myös automatisoida sen. Pienille yrityksille tämä on usein selviytymiskysymys.
Aluksi muotoillaan TOR. Useimmissa tapauksissa kirjanpitotietokannan, esimerkiksi klassisen myynnin, pitäisi pystyä:
- pitää taulukoissa tiedot tavaroista (hinta), suoritetuista tapahtumista ja asiakkaista ja linkitä nämä taulukot toisiinsa
- olla mukava syöttölomakkeita tiedot (pudotusvalikoilla jne.)
- täyttää automaattisesti joitakin tietoja painetut lomakkeet (maksut, laskut jne.)
- antaa tarvittavan raportit hallita koko liiketoimintaprosessia johtajan näkökulmasta
Microsoft Excel pystyy käsittelemään kaiken tämän pienellä vaivalla. Yritetään toteuttaa tämä.
Vaihe 1. Alkutiedot taulukoiden muodossa
Tallennamme tiedot tuotteista, myynneistä ja asiakkaista kolmeen taulukkoon (samalle tai eri taulukolle – ei väliä). On äärimmäisen tärkeää muuttaa ne "älypöydiksi", joissa on automaattinen koko, jotta sitä ei ajatella tulevaisuudessa. Tämä tehdään komennolla Muotoile taulukkona kieleke Koti (Etusivu — Muotoile taulukkona). Välilehdellä, joka sitten tulee näkyviin Rakentaja (Design) anna taulukoille kuvaavat nimet kenttään Taulukon nimi myöhempää käyttöä varten:
Yhteensä meidän pitäisi saada kolme "älykästä pöytää":
Huomaa, että taulukot voivat sisältää lisäselventäviä tietoja. Siis esimerkiksi meidän Hintasisältää lisätietoja kunkin tuotteen luokasta (tuoteryhmä, pakkaus, paino jne.) sekä taulukon Asiakas — kunkin kaupungin kaupunki ja alue (osoite, TIN-tunnus, pankkitiedot jne.).
Pöytä Myynti käytämme myöhemmin valmiiden tapahtumien syöttämiseen siihen.
Vaihe 2. Luo tietojen syöttölomake
Voit tietysti syöttää myyntitiedot suoraan vihreään taulukkoon Myynti, mutta tämä ei ole aina kätevää ja aiheuttaa virheitä ja kirjoitusvirheitä "inhimillisestä tekijästä". Siksi olisi parempi tehdä erityinen lomake tietojen syöttämiseksi erilliselle arkille, joka on jotain tällaista:
Käytä funktiota solussa B3 saadaksesi päivitetyn nykyisen päivämäärän ja kellonajan TDATA (NYT). Jos aikaa ei tarvita, niin sen sijaan TDATA toimintoa voidaan käyttää TÄNÄÄN (TÄNÄÄN).
Etsi solusta B11 valitun tuotteen hinta älytaulukon kolmannesta sarakkeesta Hinta funktiota käyttämällä VPR (HAKU). Jos et ole aiemmin törmännyt siihen, lue ja katso video ensin täältä.
Solussa B7 tarvitsemme pudotusluettelon hinnaston tuotteista. Tätä varten voit käyttää komentoa Tiedot – Tietojen validointi (Tiedot — Vahvistus), määritä rajoituksena Lista (Lista) ja kirjoita sitten kenttään lähde (Lähde) linkki sarakkeeseen Nimi älypöydästämme Hinta:
Samalla tavalla luodaan avattava luettelo asiakkaista, mutta lähde on kapeampi:
= EPÄSUORA("Asiakkaat[Asiakas]"
Toiminto EPÄSUORISTA (Epäsuora) tarvitaan tässä tapauksessa, koska Excel ei valitettavasti ymmärrä suoria linkkejä älykkäisiin taulukoihin Lähde-kentässä. Mutta sama linkki "kääritty" funktioon EPÄSUORISTA samalla se toimii räjähdysmäisesti (lisätietoja tästä oli artikkelissa, joka käsittelee sisältöä sisältävien avattavien luetteloiden luomista).
Vaihe 3. Myyntimerkintämakron lisääminen
Lomakkeen täyttämisen jälkeen sinun on lisättävä siihen syötetyt tiedot taulukon loppuun Myynti. Yksinkertaisten linkkien avulla muodostamme rivin, joka lisätään suoraan lomakkeen alle:
Nuo. solussa A20 on linkki =B3, solussa B20 on linkki =B7 ja niin edelleen.
Lisätään nyt 2-rivinen perusmakro, joka kopioi luodun merkkijonon ja lisää sen myyntitaulukkoon. Voit tehdä tämän painamalla yhdistelmää Alt + F11 tai painiketta Visual Basic kieleke kehittäjä (Kehittäjä). Jos tämä välilehti ei ole näkyvissä, ota se käyttöön ensin asetuksista Tiedosto – Asetukset – Nauhan asetukset (Tiedosto — Asetukset — Mukauta nauhaa). Lisää avautuvan Visual Basic -editori-ikkunan valikon kautta uusi tyhjä moduuli Insert – moduuli ja syötä makrokoodimme sinne:
Sub Add_Sell() Worksheets("Input Form").Alue("A20:E20").Copy 'Kopioi tietorivi lomakkeesta n = Worksheets("Sales").Range("A100000").End(xlUp) . Rivi 'määritä taulukon viimeisen rivin numero. Sales Worksheets("Sales").Cells(n + 1, 1).PasteSpecial Paste:=xlPasteValues'liitä seuraavalle tyhjälle riville Worksheets("Input Form").Alue("B5,B7,B9"). ClearContents 'tyhjennä lopun alilomake Nyt voimme lisätä lomakkeeseen painikkeen luodun makron suorittamiseksi avattavasta luettelosta liite kieleke kehittäjä (Kehittäjä — Lisää — Painike):
Kun olet piirtänyt sen pitämällä hiiren vasenta painiketta painettuna, Excel kysyy, mikä makro sinun on määritettävä sille - valitse makromme Lisää_Myy. Voit muuttaa painikkeen tekstiä napsauttamalla sitä hiiren kakkospainikkeella ja valitsemalla komennon Muuta tekstiä.
Nyt lomakkeen täyttämisen jälkeen voit napsauttaa painikettamme ja syötetyt tiedot lisätään automaattisesti taulukkoon Myynti, ja sitten lomake tyhjennetään uuden sopimuksen tekemiseksi.
Vaihe 4 Taulukoiden linkittäminen
Ennen raportin rakentamista linkitetään taulukot yhteen, jotta voimme myöhemmin laskea myynnin nopeasti alueen, asiakkaan tai kategorian mukaan. Excelin vanhemmissa versioissa tämä vaatisi useiden funktioiden käyttöä. VPR (HAKU) hintojen, luokkien, asiakkaiden, kaupunkien jne. korvaamiseksi taulukkoon Myynti. Tämä vaatii meiltä aikaa ja vaivaa ja "syö" myös paljon Excel-resursseja. Excel 2013:sta alkaen kaikki voidaan toteuttaa paljon yksinkertaisemmin luomalla taulukoiden välisiä suhteita.
Voit tehdä tämän välilehdellä Päiväys (Päivämäärä) napsauttaa Suhteet (Suhteet). Napsauta painiketta näkyviin tulevassa ikkunassa luoda (Uusi) ja valitse avattavista luetteloista taulukot ja sarakkeiden nimet, joiden mukaan ne tulisi yhdistää:
Tärkeä kohta: taulukot on määritettävä tässä järjestyksessä, eli linkitetty taulukko (Hinta) ei saa sisältää avainsarakkeessa (Nimi) päällekkäisiä tuotteita, kuten taulukossa tapahtuu Myynti. Toisin sanoen liitetyn taulukon on oltava sellainen, josta etsit tietoja käyttämällä VPRjos sitä käytettäisiin.
Tietenkin pöytä on kytketty samalla tavalla Myynti pöydän kanssa Asiakas yhteisen sarakkeen mukaan Asiakas:
Linkkien määrittämisen jälkeen linkkien hallintaikkuna voidaan sulkea. sinun ei tarvitse toistaa tätä menettelyä.
Vaihe 5. Rakennamme raportit yhteenvedon avulla
Nyt analysoidaksemme myyntiä ja seurataksemme prosessin dynamiikkaa, luodaan esimerkiksi jonkinlainen raportti pivot-taulukon avulla. Aseta aktiivinen solu taulukoksi Myynti ja valitse nauhan välilehti Lisää – Pivot-taulukko (Lisää — Pivot-taulukko). Excel kysyy avautuvassa ikkunassa tietolähdettä (esim Myynti) ja paikka, johon raportti ladataan (mieluiten uudella taulukolla):
Tärkeintä on, että valintaruutu on otettava käyttöön Lisää nämä tiedot tietomalliin (Lisää tietoja tietomalliin) ikkunan alareunassa, jotta Excel ymmärtää, että haluamme rakentaa raportin paitsi nykyisen taulukon lisäksi myös käyttää kaikkia suhteita.
Napsauttamisen jälkeen OK paneeli tulee näkyviin ikkunan oikeaan puoleen Pivot-taulukon kentätmistä linkkiä klikkaa Kaikkinähdäksesi paitsi nykyisen, myös kaikki kirjassa olevat "älykkäät taulukot" kerralla. Ja sitten, kuten perinteisessä pivot-taulukossa, voit yksinkertaisesti vetää tarvitsemamme kentät kaikista asiaan liittyvistä taulukoista alueelle Suodattaa, Rivit, Stolbtsov or arvot – ja Excel rakentaa välittömästi kaikki tarvitsemamme raportit arkille:
Älä unohda, että pivot-taulukko on päivitettävä säännöllisesti (kun lähdetiedot muuttuvat) napsauttamalla sitä hiiren oikealla painikkeella ja valitsemalla komennon Päivitä & Tallenna (Virkistää), koska se ei voi tehdä sitä automaattisesti.
Myös valitsemalla yhteenvedosta mikä tahansa solu ja painamalla -painiketta Pivot-kaavio (Pivot-kaavio) kieleke analyysi (Analyysi) or parametrit (Vaihtoehdot) voit nopeasti visualisoida siinä lasketut tulokset.
Vaihe 6. Täytä tulostettavat tiedot
Toinen tyypillinen minkä tahansa tietokannan tehtävä on erilaisten painettujen lomakkeiden ja lomakkeiden (laskut, laskut, asiakirjat jne.) automaattinen täyttö. Olen jo kirjoittanut yhdestä tavoista tehdä tämä. Tässä toteutamme esimerkiksi lomakkeen täyttämisen tilinumerolla:
Oletetaan, että soluun C2 käyttäjä syöttää numeron (taulukon rivinumero Myyntiitse asiassa), ja sitten tarvitsemamme tiedot vedetään ylös käyttämällä jo tuttua toimintoa VPR (HAKU) ja ominaisuuksia INDEX (INDEKSI).
- Kuinka käyttää VLOOKUP-toimintoa arvojen etsimiseen ja etsimiseen
- Kuinka korvata VLOOKUP INDEX- ja MATCH-funktioilla
- Lomakkeiden ja lomakkeiden automaattinen täyttö taulukon tiedoilla
- Raporttien luominen pivot-taulukoilla