Përmbajtje
Kur përmendim bazat e të dhënave (DB), gjëja e parë që të vjen në mendje, natyrisht, janë të gjitha llojet e fjalëve si SQL, Oracle, 1C, ose të paktën Access. Sigurisht, këto janë programe shumë të fuqishme (dhe të shtrenjta në pjesën më të madhe) që mund të automatizojnë punën e një kompanie të madhe dhe komplekse me shumë të dhëna. Problemi është se ndonjëherë një fuqi e tillë thjesht nuk nevojitet. Biznesi juaj mund të jetë i vogël dhe me procese biznesi relativisht të thjeshta, por ju gjithashtu dëshironi ta automatizoni atë. Dhe është për kompanitë e vogla që kjo është shpesh një çështje mbijetese.
Për të filluar, le të formulojmë TOR-in. Në shumicën e rasteve, një bazë të dhënash për kontabilitetin, për shembull, shitjet klasike duhet të jetë në gjendje të:
- mbaj në tabela informacione për mallrat (çmimi), transaksionet e kryera dhe klientët dhe lidhni këto tabela me njëra-tjetrën
- të ketë rehat formularët hyrës të dhëna (me lista rënëse, etj.)
- plotësoni automatikisht disa të dhëna formularët e shtypur (pagesat, faturat, etj.)
- nxjerr të nevojshme Raportet për të kontrolluar të gjithë procesin e biznesit nga këndvështrimi i menaxherit
Microsoft Excel mund t'i trajtojë të gjitha këto me pak përpjekje. Le të përpiqemi ta zbatojmë këtë.
Hapi 1. Të dhënat fillestare në formën e tabelave
Ne do të ruajmë informacionin për produktet, shitjet dhe klientët në tre tabela (në të njëjtën fletë ose në të ndryshme - nuk ka rëndësi). Është thelbësisht e rëndësishme t'i ktheni ato në "tavolina inteligjente" me madhësi automatike, në mënyrë që të mos mendoni për të në të ardhmen. Kjo bëhet me komandën Formatoni si tabelë tab Fillimi (Shtëpia - Formatoni si tabelë). Në skedën që shfaqet më pas konstruktor (Dizajn) jepni tabelave emra përshkrues në fushë Emri i tabelës për përdorim të mëvonshëm:
Në total, duhet të marrim tre "tavolina inteligjente":
Ju lutemi vini re se tabelat mund të përmbajnë të dhëna sqaruese shtesë. Kështu, për shembull, tonën Çmimipërmban informacion shtesë për kategorinë (grupin e produkteve, paketimin, peshën, etj.) të secilit produkt dhe tabelën klient — qyteti dhe rajoni (adresa, TIN, të dhënat bankare, etj.) të secilit prej tyre.
Tryezë Shitjet do të përdoret nga ne më vonë për të futur transaksionet e përfunduara në të.
Hapi 2. Krijoni një formular për futjen e të dhënave
Sigurisht, ju mund të futni të dhënat e shitjeve direkt në tabelën e gjelbër Shitjet, por kjo nuk është gjithmonë e përshtatshme dhe sjell shfaqjen e gabimeve dhe gabimeve shkrimore për shkak të "faktorit njerëzor". Prandaj, do të ishte më mirë të krijoni një formular të veçantë për futjen e të dhënave në një fletë të veçantë të diçkaje si kjo:
Në qelizën B3, për të marrë datën-kohën aktuale të përditësuar, përdorni funksionin TDATA (TANI). Nëse koha nuk është e nevojshme, atëherë në vend të kësaj TDATA funksioni mund të aplikohet SOT (SOT).
Në qelizën B11, gjeni çmimin e produktit të zgjedhur në kolonën e tretë të tabelës inteligjente Çmimi duke përdorur funksionin VPR (VLOOKUP). Nëse nuk e keni hasur më parë, atëherë së pari lexoni dhe shikoni videon këtu.
Në qelizën B7, na duhet një listë rënëse me produkte nga lista e çmimeve. Për këtë ju mund të përdorni komandën Të dhënat – Validimi i të dhënave (Të dhënat - Vërtetimi), specifikoni si kufizim Listë (Listë) dhe më pas futeni në fushë Burim (Burimi) lidhje me kolonën Emër nga tavolina jonë e zgjuar Çmimi:
Në mënyrë të ngjashme, krijohet një listë rënëse me klientët, por burimi do të jetë më i ngushtë:
=INDIRECT("Klientët[Klient]")
funksion INDIREKT (Indirekt) është e nevojshme, në këtë rast, sepse Excel, për fat të keq, nuk i kupton lidhjet direkte me tabelat inteligjente në fushën Burimi. Por e njëjta lidhje "e mbështjellë" në një funksion INDIREKT në të njëjtën kohë, funksionon me zhurmë (më shumë për këtë ishte në artikullin për krijimin e listave rënëse me përmbajtje).
Hapi 3. Shtimi i një makro hyrje në shitje
Pas plotësimit të formularit, duhet të shtoni të dhënat e futura në të në fund të tabelës Shitjet. Duke përdorur lidhje të thjeshta, ne do të formojmë një rresht që do të shtohet pikërisht poshtë formularit:
ato. qeliza A20 do të ketë një lidhje me =B3, qeliza B20 do të ketë një lidhje me =B7, e kështu me radhë.
Tani le të shtojmë një makro elementare me 2 rreshta që kopjon vargun e krijuar dhe e shton atë në tabelën Sales. Për ta bërë këtë, shtypni kombinimin Alt + F11 ose butoni Visual Basic tab zhvillues (Zhvilluesi). Nëse kjo skedë nuk është e dukshme, atëherë aktivizojeni fillimisht te cilësimet Skedari – Opsionet – Konfigurimi i shiritit (Skedar - Opsione - Personalizo shiritin). Në dritaren e redaktuesit të Visual Basic që hapet, futni një modul të ri bosh përmes menysë Insert – Moduli dhe futni kodin tonë makro atje:
Sub Add_Sell() Worksheets("Input Form").Range("A20:E20").Kopjo 'Kopjo linjën e të dhënave nga forma n = Worksheets("Sales").Range("A100000").End(xlUp) . Rreshti 'përcaktoni numrin e rreshtit të fundit në tabelë. Fletët e punës së shitjeve ("Shitjet"). Qelizat (n + 1, 1). Paste Speciale:=xlPasteValues 'ngjisni në rreshtin tjetër të zbrazët të fletëve të punës ("Formulari i hyrjes"). Gama ("B5,B7,B9"). ClearContents 'nënforma për fundin e qartë Tani mund të shtojmë një buton në formularin tonë për të ekzekutuar makronë e krijuar duke përdorur listën rënëse Fut tab zhvillues (Zhvilluesi - Fut - Butoni):
Pasi ta vizatoni, duke mbajtur të shtypur butonin e majtë të miut, Excel do t'ju pyesë se cilën makro duhet t'i caktoni - zgjidhni makronë tonë Shto_Shit. Mund ta ndryshoni tekstin në një buton duke klikuar me të djathtën mbi të dhe duke zgjedhur komandën Ndrysho tekstin.
Tani, pasi të keni plotësuar formularin, thjesht mund të klikoni në butonin tonë dhe të dhënat e futura do të shtohen automatikisht në tabelë. Shitjet, dhe më pas formulari pastrohet për të hyrë në një marrëveshje të re.
Hapi 4 Lidhja e Tabelave
Përpara se të ndërtojmë raportin, le t'i lidhim tabelat tona së bashku në mënyrë që më vonë të mund të llogarisim shpejt shitjet sipas rajonit, klientit ose kategorisë. Në versionet e vjetra të Excel, kjo do të kërkonte përdorimin e disa funksioneve. VPR (VLOOKUP) për zëvendësimin në tryezë të çmimeve, kategorive, klientëve, qyteteve etj Shitjet. Kjo kërkon kohë dhe përpjekje nga ne, dhe gjithashtu "ha" shumë burime Excel. Duke filluar me Excel 2013, gjithçka mund të zbatohet shumë më thjesht duke vendosur marrëdhënie midis tabelave.
Për ta bërë këtë, në skedën Data (Data) kliko marrëdhënie (Marrëdhëniet). Në dritaren që shfaqet, klikoni butonin Krijo (i ri) dhe zgjidhni nga listat rënëse tabelat dhe emrat e kolonave me të cilat ato duhet të lidhen:
Një pikë e rëndësishme: tabelat duhet të specifikohen në këtë renditje, dmth tabela e lidhur (Çmimi) nuk duhet të përmbajë në kolonën kryesore (Emër) produkte të dyfishta, siç ndodh në tabelë Shitjet. Me fjalë të tjera, tabela e lidhur duhet të jetë ajo në të cilën do të kërkoni të dhëna duke përdorur VPRnëse do të përdorej.
Sigurisht, tabela është e lidhur në një mënyrë të ngjashme Shitjet me tavolinë klient sipas kolonës së përbashkët Klient:
Pas konfigurimit të lidhjeve, dritarja për menaxhimin e lidhjeve mund të mbyllet; nuk keni pse ta përsërisni këtë procedurë.
Hapi 5. Ne ndërtojmë raporte duke përdorur përmbledhjen
Tani, për të analizuar shitjet dhe për të ndjekur dinamikën e procesit, le të krijojmë, për shembull, një lloj raporti duke përdorur një tabelë kryesore. Cakto qelizën aktive në tabelë Shitjet dhe zgjidhni skedën në shirit Insert – PivotTable (Fut - Tabela kryesore). Në dritaren që hapet, Excel do të na pyesë për burimin e të dhënave (p.sh. tabelën Shitjet) dhe një vend për të ngarkuar raportin (mundësisht në një fletë të re):
Pika jetike është se është e nevojshme të aktivizoni kutinë e kontrollit Shtoni këto të dhëna në modelin e të dhënave (Shto të dhëna në Modelin e të Dhënave) në fund të dritares në mënyrë që Excel të kuptojë se ne duam të ndërtojmë një raport jo vetëm në tabelën aktuale, por gjithashtu të përdorim të gjitha marrëdhëniet.
Pas klikimit OK do të shfaqet një panel në gjysmën e djathtë të dritares Fushat e tabelës rrotullueseku të klikoni lidhjen të gjithëpër të parë jo vetëm atë aktualen, por të gjitha "tabelat e zgjuara" që janë në libër menjëherë. Dhe më pas, si në tabelën klasike kryesore, thjesht mund të tërhiqni fushat që na duhen nga çdo tabelë përkatëse në zonë filtra, Rreshtat, Stolbtsov or Vlerat – dhe Excel do të ndërtojë menjëherë çdo raport që na nevojitet në fletë:
Mos harroni se tabela kryesore duhet të përditësohet periodikisht (kur të dhënat e burimit ndryshojnë) duke klikuar me të djathtën mbi të dhe duke zgjedhur komandën Përditëso & Ruaj (Rifresko), sepse nuk mund ta bëjë automatikisht.
Gjithashtu, duke zgjedhur çdo qelizë në përmbledhje dhe duke shtypur butonin Grafiku i strumbullarëve (Grafiku i strumbullarëve) tab Analizë (Analizë) or Parameters (Opsione) ju mund të vizualizoni shpejt rezultatet e llogaritura në të.
Hapi 6. Plotësoni skedarët e printueshëm
Një detyrë tjetër tipike e çdo baze të dhënash është plotësimi automatik i formularëve dhe formularëve të ndryshëm të printuar (fatura, fatura, akte, etj.). Unë kam shkruar tashmë për një nga mënyrat për ta bërë këtë. Këtu ne zbatojmë, për shembull, plotësimin e formularit me numrin e llogarisë:
Supozohet se në qelizën C2 përdoruesi do të fusë një numër (numri i rreshtit në tabelë Shitjet, në fakt), dhe më pas të dhënat që na duhen nxirren duke përdorur funksionin tashmë të njohur VPR (VLOOKUP) dhe veçoritë INDEKSI (INDEX).
- Si të përdorni funksionin VLOOKUP për të kërkuar dhe kërkuar vlerat
- Si të zëvendësoni VLOOKUP me funksionet INDEX dhe MATCH
- Plotësimi automatik i formularëve dhe formularëve me të dhëna nga tabela
- Krijimi i raporteve me PivotTables