Saturs
Pieminot datubāzes (DB), pirmais, kas nāk prātā, protams, ir visdažādākie buzzwords kā SQL, Oracle, 1C vai vismaz Access. Protams, tās ir ļoti jaudīgas (un lielākoties dārgas) programmas, kas spēj automatizēt liela un sarežģīta uzņēmuma darbu ar daudz datu. Problēma ir tāda, ka dažreiz šāda jauda vienkārši nav vajadzīga. Jūsu bizness var būt mazs un ar salīdzinoši vienkāršiem biznesa procesiem, taču vēlaties to arī automatizēt. Un tieši maziem uzņēmumiem tas bieži vien ir izdzīvošanas jautājums.
Sākumā formulēsim TOR. Vairumā gadījumu grāmatvedības datu bāzei, piemēram, klasiskajai tirdzniecībai, jāspēj:
- glabāt tabulās informāciju par precēm (cenu), veiktajiem darījumiem un klientiem un sasaistīt šīs tabulas savā starpā
- ir ērti ievades veidlapas dati (ar nolaižamajiem sarakstiem utt.)
- automātiski aizpildiet dažus datus iespiestas veidlapas (maksājumi, rēķini utt.)
- izdot nepieciešamo ziņojumi kontrolēt visu biznesa procesu no vadītāja viedokļa
Microsoft Excel var tikt galā ar to visu ar nelielu piepūli. Mēģināsim to īstenot.
Solis 1. Sākotnējie dati tabulu veidā
Informāciju par produktiem, izpārdošanu un klientiem glabāsim trīs tabulās (uz vienas lapas vai uz dažādām – nav nozīmes). Ir būtiski svarīgi tos pārvērst par “viedajiem galdiem” ar automātisko izmēru, lai nākotnē par to nedomātu. Tas tiek darīts ar komandu Formatēt kā tabulu tab Sākumlapa (Sākums — formatēt kā tabulu). Cilnē, kas pēc tam tiek parādīta Konstruktors (Dizains) dot tabulām aprakstošus nosaukumus laukā Tabulas nosaukums vēlākai lietošanai:
Kopumā mums vajadzētu iegūt trīs “viedās tabulas”:
Lūdzu, ņemiet vērā, ka tabulās var būt papildu precizējoši dati. Tā, piemēram, mūsu Сenasatur papildu informāciju par katras preces kategoriju (preču grupa, iepakojums, svars utt.), un tabulu Klients — katra pilsēta un reģions (adrese, TIN, bankas rekvizīti utt.).
Tabula Atlaides mēs izmantosim vēlāk, lai ievadītu tajā pabeigtos darījumus.
2. solis. Izveidojiet datu ievades veidlapu
Protams, pārdošanas datus varat ievadīt tieši zaļajā tabulā Atlaides, taču tas ne vienmēr ir ērti un rada kļūdas un drukas kļūdas “cilvēciskā faktora” dēļ. Tāpēc labāk būtu izveidot īpašu veidlapu datu ievadīšanai atsevišķā lapā, piemēram:
Šūnā B3, lai iegūtu atjauninātu pašreizējo datumu-laiku, izmantojiet funkciju TDATA (TAGAD). Ja laiks nav vajadzīgs, tad tā vietā TDATA funkciju var pielietot ŠODIEN (ŠODIEN).
Šūnā B11 atrodiet izvēlētās preces cenu viedās tabulas trešajā kolonnā Сena izmantojot funkciju VPR (MEKLĒT). Ja vēl neesi ar to saskāries, tad vispirms izlasi un noskaties video šeit.
Šūnā B7 mums ir nepieciešams nolaižamais saraksts ar produktiem no cenrāža. Šim nolūkam varat izmantot komandu Dati – datu validācija (Dati — validācija), norādiet kā ierobežojumu saraksts (Saraksts) un pēc tam ievadiet laukā avots (Avots) saite uz kolonnu Vārds no mūsu viedā galda Сena:
Līdzīgi tiek izveidots nolaižamais saraksts ar klientiem, taču avots būs šaurāks:
=NETIEŠA(“Klienti[Klients]”)
funkcija NETIEŠAIS (NETIEŠI) šajā gadījumā ir nepieciešams, jo Excel diemžēl nesaprot tiešās saites uz viedajām tabulām laukā Avots. Bet tā pati saite “iesaiņota” funkcijā NETIEŠAIS tajā pašā laikā tas darbojas ar triecienu (vairāk par to bija rakstā par nolaižamo sarakstu izveidi ar saturu).
3. darbība. Pārdošanas ieraksta makro pievienošana
Pēc veidlapas aizpildīšanas tabulas beigās jāpievieno tajā ievadītie dati Atlaides. Izmantojot vienkāršas saites, mēs izveidosim rindu, kas jāpievieno tieši zem veidlapas:
Tie. šūnai A20 būs saite uz =B3, šūnai B20 būs saite uz =B7 un tā tālāk.
Tagad pievienosim 2 rindu elementāru makro, kas kopē ģenerēto virkni un pievieno to tabulai Pārdošana. Lai to izdarītu, nospiediet kombināciju Alt+F11 vai pogu Visual Basic tab attīstītājs (Izstrādātājs). Ja šī cilne nav redzama, vispirms iespējojiet to iestatījumos Fails - Opcijas - Lentes iestatīšana (Fails — opcijas — pielāgot lenti). Atvērtajā Visual Basic redaktora logā izvēlnē ievietojiet jaunu tukšu moduli Ievietot - modulis un ievadiet tur mūsu makro kodu:
Apakšpied. . Rinda 'nosaka tabulas pēdējās rindas numuru. Pārdošanas darblapas ("Pārdošana").Šūnas (n + 20, 20).PasteSpecial Paste:=xlPasteValues 'ielīmējiet nākamajā tukšajā rindā Worksheets ("Ievades veidlapa"). Diapazons ("B100000,B1,B1"). ClearContents 'notīrīt beigu apakšveidlapu Tagad mēs savai veidlapai varam pievienot pogu, lai palaistu izveidoto makro, izmantojot nolaižamo sarakstu ielaidums tab attīstītājs (Izstrādātājs — Ievietot — poga):
Pēc tā uzzīmēšanas, turot nospiestu peles kreiso pogu, Excel jautās, kurš makro jums tam jāpiešķir – atlasiet mūsu makro Add_Sell. Jūs varat mainīt tekstu uz pogas, ar peles labo pogu noklikšķinot uz tās un atlasot komandu Mainīt tekstu.
Tagad pēc veidlapas aizpildīšanas varat vienkārši noklikšķināt uz mūsu pogas, un ievadītie dati tiks automātiski pievienoti tabulai Atlaides, un pēc tam veidlapa tiek notīrīta, lai ievadītu jaunu darījumu.
4. solis Tabulu saistīšana
Pirms pārskata izveides sasaistīsim savas tabulas, lai vēlāk varētu ātri aprēķināt pārdošanas apjomu pēc reģiona, klienta vai kategorijas. Vecākajās Excel versijās tam būtu jāizmanto vairākas funkcijas. VPR (MEKLĒT) cenu, kategoriju, klientu, pilsētu utt. aizvietošanai ar tabulu Atlaides. Tas no mums prasa laiku un pūles, kā arī “apēd” daudz Excel resursu. Sākot ar Excel 2013, visu var ieviest daudz vienkāršāk, izveidojot attiecības starp tabulām.
Lai to izdarītu, cilnē Datums (Datums) klikšķis attiecība (Attiecības). Parādītajā logā noklikšķiniet uz pogas izveidot (jauns) un nolaižamajos sarakstos atlasiet tabulu un kolonnu nosaukumus, ar kuriem tie jāsaista:
Svarīgs punkts: tabulas jānorāda šādā secībā, ti, saistītā tabula (Сena) nedrīkst būt atslēgas kolonnā (Vārds) dublēt produktus, kā tas notiek tabulā Atlaides. Citiem vārdiem sakot, saistītajai tabulai ir jābūt tādai, kurā jūs meklētu datus VPRja tas tiktu izmantots.
Protams, tabula ir savienota līdzīgi Atlaides ar galdu Klients pēc kopējās kolonnas Klients:
Pēc saišu iestatīšanas var aizvērt saišu pārvaldības logu; jums šī procedūra nav jāatkārto.
5. darbība. Mēs veidojam atskaites, izmantojot kopsavilkumu
Tagad, lai analizētu pārdošanu un izsekotu procesa dinamikai, izveidosim, piemēram, sava veida pārskatu, izmantojot rakurstabulu. Iestatīt aktīvo šūnu uz tabulu Atlaides un atlasiet lentes cilni Ievietošana — PivotTable (Ievietot — rakurstabula). Atvērtajā logā programma Excel mums jautās par datu avotu (piemēram, tabulu Atlaides) un vieta, kur augšupielādēt pārskatu (vēlams jaunā lapā):
Svarīgi ir tas, ka ir jāiespējo izvēles rūtiņa Pievienojiet šos datus datu modelim (Pievienot datus datu modelim) loga apakšā, lai Excel saprastu, ka mēs vēlamies izveidot atskaiti ne tikai par pašreizējo tabulu, bet arī izmantot visas attiecības.
Pēc noklikšķināšanas uz OK loga labajā pusē parādīsies panelis Rakurstabulas laukikur noklikšķināt uz saites visilai redzētu ne tikai pašreizējo, bet visas grāmatā esošās “gudrās tabulas” uzreiz. Un pēc tam, tāpat kā klasiskajā rakurstabulā, varat vienkārši ievilkt vajadzīgos laukus no jebkuras saistītās tabulas uz apgabalu. filtrs, Rindas, Stolbcovs or Vērtības – un Excel lapā nekavējoties izveidos jebkuru mums nepieciešamo pārskatu:
Neaizmirstiet, ka rakurstabula ir periodiski jāatjaunina (kad mainās avota dati), ar peles labo pogu noklikšķinot uz tās un atlasot komandu Atjaunināt un saglabāt (Atjaunot), jo tas nevar to izdarīt automātiski.
Tāpat, atlasot jebkuru šūnu kopsavilkumā un nospiežot pogu Pagrieziena diagramma (Rakurdiagramma) tab Analīze (Analīze) or parametri (Opcijas) jūs varat ātri vizualizēt tajā aprēķinātos rezultātus.
6. darbība. Aizpildiet drukājamās lapas
Vēl viens tipisks jebkuras datu bāzes uzdevums ir dažādu drukātu veidlapu un veidlapu automātiska aizpildīšana (rēķini, rēķini, akti utt.). Es jau rakstīju par vienu no veidiem, kā to izdarīt. Šeit mēs īstenojam, piemēram, veidlapas aizpildīšanu pēc konta numura:
Tiek pieņemts, ka šūnā C2 lietotājs ievadīs skaitli (rindas numurs tabulā Atlaides, patiesībā), un pēc tam mums nepieciešamie dati tiek izvilkti, izmantojot jau pazīstamo funkciju VPR (MEKLĒT) un funkcijas INDEX (INDEX).
- Kā izmantot funkciju VLOOKUP, lai meklētu un uzmeklētu vērtības
- Kā aizstāt VLOOKUP ar INDEX un MATCH funkcijām
- Automātiska veidlapu un veidlapu aizpildīšana ar datiem no tabulas
- Atskaišu izveide, izmantojot rakurstabulas