Mukati
Kana uchitaura dhatabhesi (DB), chinhu chekutanga chinouya mupfungwa, hongu, marudzi ese emazwi seSQL, Oracle, 1C, kana kanenge Access. Zvechokwadi, izvi zvine simba zvikuru (uye zvinodhura kune chikamu chikuru) zvirongwa zvinogona kugadzirisa basa rekambani huru uye yakaoma ine data yakawanda. Chinetso ndechokuti dzimwe nguva simba rakadaro haringodiwi. Bhizinesi rako rinogona kunge riri diki uye riine maitiro akareruka ebhizinesi, asi iwe zvakare unoda kurigadzirisa. Uye ndezvemakambani madiki iyo inowanzova nyaya yekurarama.
Kutanga, ngatigadzirirei TOR. Muzviitiko zvakawanda, dhatabhesi ye accounting, semuenzaniso, classic sales inofanirwa kukwanisa:
- chengeta mumatafura ruzivo pamusoro pezvinhu (mutengo), akapedzwa kutengeserana uye vatengi uye batanidza matafura aya kune mumwe nemumwe
- uve wakasununguka mafomu ekupinza data (ine zvinyorwa zvekudonha, nezvimwewo)
- otomatiki zadza imwe data mafomu akadhindwa (kubhadhara, mabhiri, nezvimwewo)
- buritsa zvinodiwa mishumo kudzora hurongwa hwese hwebhizinesi kubva pakuona kwemaneja
Microsoft Excel inogona kubata zvese izvi nekuedza kushoma. Ngatiedzei kuita izvi.
Danho 1. Data yekutanga mumhando yematafura
Isu tichachengeta ruzivo nezve zvigadzirwa, kutengesa uye vatengi mumatafura matatu (papepa rimwechete kana pane akasiyana - hazvina basa). Izvo zvakakosha zvakanyanya kuashandura kuita "smart tafura" neauto-saizi, kuti usazofunga nezvazvo mune ramangwana. Izvi zvinoitwa nemurairo Fomati setafura Tab Home (Kumba - Fomati seTafura). Pane tab inobva yaonekwa Constructor (Dhizaini) ipa matafura mazita anotsanangura mumunda Tafura zita kuitira kushandiswa gare gare:
Pakazara, isu tinofanirwa kuwana matatu "smart tafura":
Ndokumbira utarise kuti matafura anogona kunge aine imwe data yekujekesa. Saka, semuenzaniso, yedu mutengoine rumwe ruzivo nezve chikamu (chigadzirwa boka, kurongedza, huremu, nezvimwewo) chechigadzirwa chimwe nechimwe, uye tafura. munhu anoda kubetserwa - guta uye dunhu (kero, TIN, ruzivo rwebhangi, nezvimwewo) zvemumwe nemumwe wavo.
tafura Sales ichashandiswa nesu gare gare kuisa matransaction akapera mairi.
Danho 2. Gadzira fomu rekuisa data
Zvechokwadi, iwe unogona kuisa data yekutengesa zvakananga mutafura yakasvibirira Sales, asi izvi hazvisi nguva dzose zviri nyore uye zvinosanganisira kuonekwa kwezvikanganiso uye typos nekuda kwe "human factor". Naizvozvo, zvingave nani kugadzira fomu rakakosha rekuisa data pane rakasiyana pepa rechinhu chakadai:
Musero B3, kuti uwane yakagadziridzwa yazvino-nguva-nguva, shandisa basa racho Iye TDATA (ZVINO). Kana nguva isingadikanwi, saka pane kudaro Iye TDATA basa rinogona kushandiswa TODAY (NHASI).
Musero B11, tsvaga mutengo wechigadzirwa chakasarudzwa muchikamu chechitatu chetafura yakangwara mutengo kushandisa basa VPR (VLOOKUP). Kana usati wambosangana nazvo, saka tanga waverenga uye kuona vhidhiyo pano.
Musero B7, isu tinoda rondedzero yekudonha ine zvigadzirwa kubva pamutengo runyorwa. Nokuda kweizvi unogona kushandisa murairo Data - Kusimbiswa kweData (Data - Validation), tsanangura sechisungo List (Rondedzero) uye ipapo ugopinda mumunda mabviro (Chinhu) link kune column zita kubva patafura yedu yakangwara mutengo:
Saizvozvo, rondedzero yekudonhedza pasi nevatengi inogadzirwa, asi sosi ichava diki:
= INDIRECT("Vatengi[Mutengi]")
basa INDIRECT (ZVINOKOSHA) inodiwa, mune iyi kesi, nekuti Excel, zvinosuruvarisa, hainzwisise zvakananga zvinongedzo kune akangwara matafura muSime munda. Asi chinongedzo chimwechete "chakaputirwa" mune basa INDIRECT panguva imwe chete, inoshanda ne bang (zvimwe pamusoro peizvi zvaive muchinyorwa pamusoro pekugadzira zvinyorwa zvekudonha zvine zvinyorwa).
Danho 3. Kuwedzera kutengesa kupinda macro
Mushure mekuzadza fomu, iwe unofanirwa kuwedzera iyo data yakaiswa mairi kusvika kumagumo etafura Sales. Tichishandisa zviri nyore zvinongedzo, isu tichagadzira mutsara wekuwedzera pazasi pefomu:
Avo. sero A20 ichange iine chinongedzo ku = B3, sero B20 ichange iine chinongedzo ku = B7, zvichingodaro.
Zvino ngatiwedzerei 2-mutsara wekutanga macro iyo inokopa tambo yakagadzirwa uye inoiwedzera kune Kutengesa tafura. Kuti uite izvi, shandisa musanganiswa Alt + F11 kana bhatani Visual Basic Tab yokuvaka (Mugadziri). Kana iyi tebhu isingaonekwe, ibva waigonesa kutanga muzvirongwa Faira - Sarudzo - Ribhoni Setup (Faira - Sarudzo - Gadzirisa Ribhoni). MuVisual Basic mupepeti hwindo rinovhura, isa nyowani isina chinhu module kuburikidza nemenyu Pinza – Module uye isa macro kodhi yedu ipapo:
Sub Add_Sell() Worksheets("Input Form").Range("A20:E20").Kopi 'Kopa mutsara wedata kubva pafomu n = Mashizha ekushanda("Kutengesa").Range("A100000").End(xlUp) . Row 'sarudza nhamba yemutsara wekupedzisira mutafura. Sales Worksheets("Sales").Masero(n + 1, 1).PasteSpecial Paste:=xlPasteValues'namira mumutsara unotevera usina chinhu Mashizha ekushanda("Fomu Rokupinza").Range("B5,B7,B9"). ClearContents 'clear end sub form Iye zvino isu tinogona kuwedzera bhatani kufomu redu kuti timhanye iyo yakagadzirwa macro tichishandisa iyo yekudonhedza rondedzero Insert Tab yokuvaka (Mugadziri - Isa - Bhatani):
Mushure mekunge waidhirowa, wakabata pasi bhatani rekuruboshwe, Excel ichakubvunza kuti ndeipi macro yaunoda kugovera kwairi - sarudza yedu macro. Add_Sell. Iwe unogona kushandura zvinyorwa pabhatani nekudzvanya-kurudyi pairi uye nekusarudza rairo Chinja mavara.
Zvino, mushure mekuzadza fomu, unogona kungodzvanya bhatani redu, uye iyo data yakapinda inozongowedzerwa patafura. Sales, uye ipapo fomu rinobviswa kuti upinde chibvumirano chitsva.
Nhanho 4 Kubatanidza Matafura
Tisati tavaka rondedzero, ngatibatanidzei matafura edu pamwechete kuitira kuti gare gare tigone kukurumidza kuverenga kutengesa nedunhu, mutengi, kana chikamu. Mune shanduro dzekare dzeExcel, izvi zvinoda kushandiswa kwemabasa akati wandei. VPR (VLOOKUP) yekutsiva mitengo, zvikamu, vatengi, maguta, nezvimwe patafura Sales. Izvi zvinoda nguva uye kushanda nesimba kubva kwatiri, uye zvakare "inodya" yakawanda yeExcel zviwanikwa. Kutanga neExcel 2013, zvese zvinogona kuitwa zvakanyanya nyore nekumisa hukama pakati pematafura.
Kuti uite izvi, pane tab Data (Zuva) watinya Ukama (Relations). Pahwindo rinoonekwa, tinya bhatani sika (nyowani) uye sarudza kubva pakudonhedza-pasi rondedzero matafura uye mazita emakoramu aanofanira kuenderana nawo:
Chinhu chakakosha: matafura anofanirwa kutsanangurwa nenzira iyi, kureva tafura yakabatana (mutengo) haifanirwe kunge iine mukoramu kiyi (zita) duplicate zvigadzirwa, sezvazvinoitika mutafura Sales. Mune mamwe mazwi, tafura yakabatana inofanira kunge iri imwe yaungatsvaga kushandisa data VPRkana yakashandiswa.
Zvechokwadi, tafura yakabatanidzwa nenzira yakafanana Sales netafura munhu anoda kubetserwa by common column kasitoma:
Mushure mekugadzirisa zvinongedzo, hwindo rekutarisira zvinongedzo rinogona kuvharwa; haufanirwe kudzokorora nzira iyi.
Nhanho 5. Tinovaka mishumo tichishandisa pfupiso
Zvino, kuti tiongorore kutengesa uye kutevedzera dynamics yemaitiro, ngatigadzire, semuenzaniso, imwe mhando yemushumo tichishandisa pivot tafura. Gadzirisa sero rinoshanda patafura Sales uye sarudza tab iri paribhoni Pinza – PivotTable (Isa - Pivot Tafura). Muhwindo rinovhurwa, Excel ichatibvunza nezve data sosi (kureva tafura Sales) uye nzvimbo yekurodha mushumo (zvichida pane pepa idzva):
Chinhu chakakosha ndechekuti zvakakosha kugonesa bhokisi rekutarisa Wedzera iyi data kune data model (Wedzera data kuData Model) pazasi pehwindo kuitira kuti Excel inzwisise kuti tinoda kuvaka mushumo kwete chete patafura yemazuva ano, asiwo kushandisa hukama hwose.
Mushure mekunge mavharidza OK pani ichaonekwa muhafu yekurudyi yehwindo Pivot tafura mindapakubata link zvosekuti uone kwete yazvino chete, asi ese "smart tables" ari mubhuku kamwechete. Uye zvakare, sepaiyo classic pivot tafura, unogona kungodhonza minda yatinoda kubva kune chero inoenderana matafura munzvimbo. firita, Rows, Stolbtsov or Values - uye Excel ichaita ipapo kuvaka chero chirevo chatinoda pane pepa:
Usakanganwa kuti tafura yepivot inoda kuvandudzwa nguva nenguva (kana iyo sosi data yachinja) nekudzvanya-kurudyi pairi uye nekusarudza rairo. Gadziridza & Chengeta (Refresh), nokuti haigoni kuzviita zvoga.
Zvakare, nekusarudza chero sero muchidimbu uye nekudzvanya bhatani Pivot Chati (Pivot Chati) Tab ongororo (Ongororo) or Parameters (Sarudzo) iwe unogona kukurumidza kuona mhedzisiro yakaverengerwa mairi.
Nhanho 6. Zadza zvinyorwa zvinodhindwa
Rimwe basa rakajairika rechero dhatabhesi kuzadza otomatiki kweakasiyana akadhindwa mafomu uye mafomu (invoice, invoice, zviito, nezvimwewo). Ndakatonyora nezve imwe yenzira dzekuita izvi. Pano isu tinoshandisa, semuenzaniso, kuzadza fomu neakaundi nhamba:
Zvinofungidzirwa kuti muchitokisi C2 mushandisi achaisa nhamba (mutsara nhamba mutafura Sales, kutaura zvazviri), uyezve data yatinoda inodhonzwa tichishandisa basa ragara richizivikanwa VPR (VLOOKUP) Uye zvikamu INDEX (INDEX).
- Maitiro ekushandisa VLOOKUP basa kutarisa kumusoro uye kutarisa maitiro
- Maitiro ekutsiva VLOOKUP neINDEX uye MATCH mabasa
- Kuzadza otomatiki kwemafomu uye mafomu ane data kubva patafura
- Kugadzira Mishumo nePivotTables