Nilalaman
Kapag binabanggit ang mga database (DB), ang unang bagay na pumapasok sa isip, siyempre, ay ang lahat ng uri ng mga buzzword tulad ng SQL, Oracle, 1C, o hindi bababa sa Access. Siyempre, ang mga ito ay napakalakas (at mahal para sa karamihan) na mga programa na maaaring i-automate ang gawain ng isang malaki at kumplikadong kumpanya na may maraming data. Ang problema ay kung minsan ang gayong kapangyarihan ay hindi kailangan. Maaaring maliit ang iyong negosyo at may mga simpleng proseso ng negosyo, ngunit gusto mo rin itong i-automate. At ito ay para sa maliliit na kumpanya na ito ay madalas na isang bagay ng kaligtasan.
Upang magsimula, buuin natin ang TOR. Sa karamihan ng mga kaso, ang isang database para sa accounting, halimbawa, ang mga klasikong benta ay dapat na:
- panatilihin sa mga talahanayan ng impormasyon sa mga kalakal (presyo), nakumpletong mga transaksyon at mga customer at iugnay ang mga talahanayang ito sa isa't isa
- magkaroon ng komportable mga form ng pag-input data (na may mga drop-down na listahan, atbp.)
- awtomatikong punan ang ilang data nakalimbag na mga form (mga pagbabayad, bill, atbp.)
- ilabas ang kailangan ulat upang kontrolin ang buong proseso ng negosyo mula sa punto ng view ng manager
Kakayanin ng Microsoft Excel ang lahat ng ito sa kaunting pagsisikap. Subukan nating ipatupad ito.
Hakbang 1. Paunang data sa anyo ng mga talahanayan
Mag-iimbak kami ng impormasyon tungkol sa mga produkto, benta, at mga customer sa tatlong talahanayan (sa parehong sheet o sa iba't ibang mga talahanayan - hindi ito mahalaga). Sa panimula ay mahalaga na gawing "matalinong mga talahanayan" ang mga ito na may awtomatikong laki, upang hindi isipin ang tungkol dito sa hinaharap. Ginagawa ito gamit ang utos I-format bilang isang talahanayan tab Tahanan (Home — Format bilang Talahanayan). Sa tab na lilitaw pagkatapos Tagagawa (Disenyo) bigyan ang mga talahanayan ng mga naglalarawang pangalan sa field Pangalan ng talahanayan para magamit sa ibang pagkakataon:
Sa kabuuan, dapat tayong makakuha ng tatlong "matalinong talahanayan":
Pakitandaan na ang mga talahanayan ay maaaring maglaman ng karagdagang data sa paglilinaw. Kaya, halimbawa, ang aming presyonaglalaman ng karagdagang impormasyon tungkol sa kategorya (pangkat ng produkto, packaging, timbang, atbp.) ng bawat produkto, at ang talahanayan Kliente — lungsod at rehiyon (address, TIN, mga detalye ng bangko, atbp.) ng bawat isa sa kanila.
mesa Bintahan ay gagamitin namin sa ibang pagkakataon upang ipasok ang mga nakumpletong transaksyon dito.
Hakbang 2. Gumawa ng form ng data entry
Siyempre, maaari mong ipasok ang data ng mga benta nang direkta sa berdeng talahanayan Bintahan, ngunit hindi ito palaging maginhawa at nangangailangan ng paglitaw ng mga error at typo dahil sa "human factor". Samakatuwid, mas mahusay na gumawa ng isang espesyal na form para sa pagpasok ng data sa isang hiwalay na sheet ng isang bagay na tulad nito:
Sa cell B3, upang makuha ang na-update na kasalukuyang petsa-oras, gamitin ang function Ang TDATA (NGAYON). Kung hindi kailangan ng oras, sa halip Ang TDATA maaaring ilapat ang function NGAYONG ARAW (NGAYON).
Sa cell B11, hanapin ang presyo ng napiling produkto sa ikatlong column ng smart table presyo gamit ang function VPR (VLOOKUP). Kung hindi mo pa ito na-encounter, basahin mo muna at panoorin ang video dito.
Sa cell B7, kailangan namin ng dropdown list na may mga produkto mula sa listahan ng presyo. Para dito maaari mong gamitin ang command Data – Pagpapatunay ng Data (Data — Pagpapatunay), tukuyin bilang isang hadlang listahan (Listahan) at pagkatapos ay pumasok sa field pinagmulan (Pinagmulan) link sa column Pangalan mula sa aming matalinong mesa presyo:
Katulad nito, ang isang drop-down na listahan na may mga kliyente ay ginawa, ngunit ang pinagmulan ay magiging mas makitid:
=INDIRECT(“Mga Customer [Kliyente]”)
tungkulin HINDI DIREKTA (INDIRECT) ay kinakailangan, sa kasong ito, dahil ang Excel, sa kasamaang-palad, ay hindi nauunawaan ang mga direktang link sa mga smart table sa Source field. Ngunit ang parehong link ay "nakabalot" sa isang function HINDI DIREKTA sa parehong oras, ito ay gumagana nang malakas (higit pa tungkol dito ay nasa artikulo tungkol sa paggawa ng mga drop-down na listahan na may nilalaman).
Hakbang 3. Pagdaragdag ng isang sales entry macro
Matapos punan ang form, kailangan mong idagdag ang data na ipinasok dito sa dulo ng talahanayan Bintahan. Gamit ang mga simpleng link, bubuo kami ng isang linya na idaragdag sa ibaba mismo ng form:
Yung. Ang cell A20 ay magkakaroon ng link sa =B3, ang cell B20 ay magkakaroon ng link sa =B7, at iba pa.
Ngayon, magdagdag tayo ng 2-line elementary macro na kumukopya sa nabuong string at idinaragdag ito sa talahanayan ng Sales. Upang gawin ito, pindutin ang kumbinasyon Alt + F11 o pindutan Visual Basic tab developer (Developer). Kung hindi nakikita ang tab na ito, paganahin muna ito sa mga setting File – Opsyon – Ribbon Setup (File — Mga Pagpipilian — I-customize ang Ribbon). Sa window ng Visual Basic editor na bubukas, magpasok ng isang bagong walang laman na module sa pamamagitan ng menu Ipasok – Module at ilagay ang aming macro code doon:
Sub Add_Sell() Worksheets("Input Form"). Range("A20:E20").Kopyahin ang 'Kopyahin ang linya ng data mula sa form n = Worksheets("Sales"). Range("A100000").End(xlUp) . Tinutukoy ng Row ang bilang ng huling row sa talahanayan. Sales Worksheet("Sales"). Mga Cell(n + 1, 1).PasteEspesyal na Paste:=xlPasteValues 'i-paste sa susunod na bakanteng linya na Worksheets("Input Form"). Range("B5,B7,B9"). ClearContents 'clear end sub form Ngayon ay maaari kaming magdagdag ng isang pindutan sa aming form upang patakbuhin ang nilikhang macro gamit ang dropdown na listahan Isingit tab developer (Developer — Insert — Button):
Pagkatapos mong iguhit ito, habang pinipigilan ang kaliwang pindutan ng mouse, tatanungin ka ng Excel kung aling macro ang kailangan mong italaga dito - piliin ang aming macro Magdagdag_Magbenta. Maaari mong baguhin ang teksto sa isang pindutan sa pamamagitan ng pag-right-click dito at pagpili sa command Baguhin ang text.
Ngayon, pagkatapos punan ang form, maaari mong i-click lamang ang aming pindutan, at ang ipinasok na data ay awtomatikong idaragdag sa talahanayan Bintahan, at pagkatapos ay i-clear ang form para magpasok ng bagong deal.
Hakbang 4 Pag-uugnay ng mga Table
Bago buuin ang ulat, i-link natin ang ating mga talahanayan nang magkasama upang sa ibang pagkakataon ay mabilis nating makalkula ang mga benta ayon sa rehiyon, customer, o kategorya. Sa mga mas lumang bersyon ng Excel, mangangailangan ito ng paggamit ng ilang function. VPR (VLOOKUP) para sa pagpapalit ng mga presyo, kategorya, customer, lungsod, atbp. sa talahanayan Bintahan. Nangangailangan ito ng oras at pagsisikap mula sa amin, at "kumakain" din ng maraming mapagkukunan ng Excel. Simula sa Excel 2013, mas maipapatupad ang lahat sa pamamagitan lamang ng pag-set up ng mga ugnayan sa pagitan ng mga talahanayan.
Upang gawin ito, sa tab data (Petsa) mag-click relasyon (Relasyon). Sa window na lilitaw, i-click ang pindutan Lumikha (bago) at piliin mula sa drop-down na mga listahan ng mga talahanayan at mga pangalan ng column kung saan dapat na nauugnay ang mga ito:
Isang mahalagang punto: ang mga talahanayan ay dapat na tinukoy sa pagkakasunud-sunod na ito, ibig sabihin, naka-link na talahanayan (presyo) ay hindi dapat maglaman sa key column (Pangalan) mga duplicate na produkto, tulad ng nangyayari sa talahanayan Bintahan. Sa madaling salita, ang nauugnay na talahanayan ay dapat na isa kung saan ka maghahanap ng data gamit VPRkung ito ay ginamit.
Siyempre, ang talahanayan ay konektado sa isang katulad na paraan Bintahan may mesa Kliente sa pamamagitan ng karaniwang hanay Parokyano:
Pagkatapos i-set up ang mga link, maaaring isara ang window para sa pamamahala ng mga link; hindi mo kailangang ulitin ang pamamaraang ito.
Hakbang 5. Bumubuo kami ng mga ulat gamit ang buod
Ngayon, para pag-aralan ang mga benta at subaybayan ang dynamics ng proseso, gumawa tayo, halimbawa, ng ilang uri ng ulat gamit ang pivot table. Itakda ang aktibong cell sa talahanayan Bintahan at piliin ang tab sa laso Ipasok – PivotTable (Ipasok — Pivot Table). Sa window na bubukas, tatanungin kami ng Excel tungkol sa pinagmumulan ng data (ie table Bintahan) at isang lugar upang i-upload ang ulat (mas mabuti sa isang bagong sheet):
Ang mahalagang punto ay na ito ay kinakailangan upang paganahin ang checkbox Idagdag ang data na ito sa modelo ng data (Magdagdag ng data sa Modelo ng Data) sa ibaba ng window upang maunawaan ng Excel na gusto naming bumuo ng isang ulat hindi lamang sa kasalukuyang talahanayan, ngunit gamitin din ang lahat ng mga relasyon.
Pagkatapos ng pag-click sa OK may lalabas na panel sa kanang kalahati ng window Mga field ng pivot tablekung saan i-click ang link lahatupang makita hindi lamang ang kasalukuyan, ngunit ang lahat ng "matalinong talahanayan" na nasa aklat nang sabay-sabay. At pagkatapos, tulad ng sa classic na pivot table, maaari mong i-drag lang ang mga field na kailangan namin mula sa anumang nauugnay na mga talahanayan papunta sa lugar. Filter, Mga hilera, Stolbtsov or Halaga ng – at ang Excel ay agad na bubuo ng anumang ulat na kailangan namin sa sheet:
Huwag kalimutan na ang pivot table ay kailangang i-update nang pana-panahon (kapag nagbago ang source data) sa pamamagitan ng pag-right click dito at pagpili sa command. I-update at I-save (Refresh), dahil hindi ito awtomatikong magagawa.
Gayundin, sa pamamagitan ng pagpili ng anumang cell sa buod at pagpindot sa pindutan Tsart ng Pivot (Pivot Chart) tab Pagsusuri (Pagsusuri) or parameter (Mga Opsyon) mabilis mong maisalarawan ang mga resultang kinakalkula dito.
Hakbang 6. Punan ang mga napi-print
Ang isa pang tipikal na gawain ng anumang database ay ang awtomatikong pagpuno ng iba't ibang mga naka-print na form at form (mga invoice, invoice, kilos, atbp.). Nagsulat na ako tungkol sa isa sa mga paraan para gawin ito. Dito namin ipinapatupad, halimbawa, ang pagpuno sa form sa pamamagitan ng account number:
Ipinapalagay na sa cell C2 ang user ay maglalagay ng numero (row number sa table Bintahan, sa katunayan), at pagkatapos ay kinuha ang data na kailangan namin gamit ang pamilyar na function VPR (VLOOKUP) at mga tampok INDEX (INDEX).
- Paano gamitin ang VLOOKUP function upang maghanap at maghanap ng mga halaga
- Paano palitan ang VLOOKUP ng mga function ng INDEX at MATCH
- Awtomatikong pagpuno ng mga form at form gamit ang data mula sa talahanayan
- Paglikha ng Mga Ulat gamit ang PivotTables