Zamkatimu
Potchula ma database (DB), chinthu choyamba chomwe chimabwera m'maganizo, ndithudi, ndi mitundu yonse ya buzzwords monga SQL, Oracle, 1C, kapena Access. Zoonadi, izi ndi zamphamvu kwambiri (komanso zodula kwambiri) mapulogalamu omwe amatha kupanga ntchito ya kampani yaikulu ndi yovuta yomwe ili ndi deta yambiri. Vuto ndilakuti nthawi zina mphamvu zotere sizifunikira. Bizinesi yanu ikhoza kukhala yaying'ono komanso yokhala ndi njira zosavuta zamabizinesi, koma mukufunanso kuyisintha. Ndipo ndi makampani ang'onoang'ono omwe nthawi zambiri amakhala ndi moyo.
Poyamba, tiyeni tipange TOR. Nthawi zambiri, nkhokwe yowerengera ndalama, mwachitsanzo, malonda akale ayenera kukhala:
- kusunga mu matebulo zokhudza katundu (mtengo), anamaliza wotuluka ndi makasitomala ndi kulumikiza matebulo awa wina ndi mzake
- kukhala omasuka mafomu olowetsa data (yokhala ndi mindandanda yotsikira pansi, etc.)
- lembani zokha data ina mafomu osindikizidwa (malipiro, mabilu, etc.)
- kupereka zofunika malipoti kuwongolera njira yonse yabizinesi kuchokera pakuwona kwa manejala
Microsoft Excel imatha kuthana ndi zonsezi ndi kuyesetsa pang'ono. Tiyeni tiyesetse kukhazikitsa izi.
Gawo 1. Deta yoyamba mu mawonekedwe a matebulo
Tidzasunga zambiri za malonda, malonda ndi makasitomala m'matebulo atatu (pa pepala limodzi kapena osiyana - zilibe kanthu). Ndikofunikira kwambiri kuwasandutsa "matebulo anzeru" okhala ndi kukula kwake, kuti musamaganizire zamtsogolo. Izi zimachitika ndi lamulo Pangani ngati tebulo tsamba Kunyumba (Kunyumba - Pangani Monga Table). Pa tabu yomwe ikuwonekera Constructor (Kapangidwe) perekani matebulo mayina ofotokozera m'munda Dzina latebulo kuti mugwiritse ntchito pambuyo pake:
Pazonse, tiyenera kupeza "matebulo anzeru" atatu:
Chonde dziwani kuti matebulo atha kukhala ndi chidziwitso chowonjezera. Kotero, mwachitsanzo, athu Pricelili ndi zina zowonjezera za gulu (gulu lazinthu, kulongedza, kulemera, ndi zina) za chinthu chilichonse, ndi tebulo kasitomala - mzinda ndi dera (adiresi, TIN, zambiri banki, etc.) aliyense wa iwo.
Table Sales idzagwiritsidwa ntchito ndi ife mtsogolo kuti tilowe muzochita zomalizidwa mmenemo.
Gawo 2. Pangani fomu yolowera deta
Inde, mukhoza kulowetsa deta yogulitsa mwachindunji mu tebulo lobiriwira Sales, koma izi sizothandiza nthawi zonse ndipo zimaphatikizapo mawonekedwe a zolakwika ndi typos chifukwa cha "munthu". Chifukwa chake, zingakhale bwino kupanga mawonekedwe apadera olowetsa deta papepala losiyana la zinthu monga izi:
Mu cell B3, kuti mupeze nthawi yomwe yasinthidwa, gwiritsani ntchito ntchitoyi Mtengo TDATA (TSOPANO). Ngati nthawi sikufunika, ndiye m'malo Mtengo TDATA ntchito ingagwiritsidwe ntchito TODAY (LERO).
Mu cell B11, pezani mtengo wa chinthu chomwe mwasankha mugawo lachitatu la tebulo lanzeru Price pogwiritsa ntchito VPR (VLOOKUP). Ngati simunakumanepo nazo, ndiye choyamba werengani ndikuwonera kanema apa.
Mu cell B7, timafunikira mndandanda wotsitsa wokhala ndi zinthu zochokera pamndandanda wamitengo. Kwa ichi mungagwiritse ntchito lamulo Deta - Kutsimikizika kwa data (Deta - Kutsimikizika), tchulani ngati cholepheretsa List (Mndandanda) ndiyeno kulowa m’munda gwero (Chitsime) kulumikiza ku column dzina kuchokera pa tebulo lathu lanzeru Price:
Momwemonso, mndandanda wotsikira pansi wokhala ndi makasitomala umapangidwa, koma gwero lidzakhala locheperako:
=INDIRECT(“Makasitomala[Kasitomala]”)
ntchito KUMALO (KODI) pakufunika, pankhaniyi, chifukwa Excel, mwatsoka, samamvetsetsa maulalo achindunji amatebulo anzeru m'gawo la Source. Koma ulalo womwewo "wokutidwa" mu ntchito KUMALO nthawi yomweyo, imagwira ntchito ndi bang (zambiri za izi zinali m'nkhani yokhudza kupanga mindandanda yotsitsa ndi zomwe zili).
Khwerero 3. Kuwonjeza ma macro entry
Mukamaliza kulemba fomuyo, muyenera kuwonjezera zomwe zalowetsedwamo mpaka kumapeto kwa tebulo Sales. Pogwiritsa ntchito maulalo osavuta, tipanga mzere woti tiwonjezedwe pansipa fomu:
Iwo. cell A20 idzakhala ndi ulalo = B3, cell B20 idzakhala ndi ulalo = B7, ndi zina zotero.
Tsopano tiyeni tiwonjezere 2-mizere yoyambira macro yomwe imakopera chingwe chopangidwa ndikuchiwonjezera patebulo la Zogulitsa. Kuti muchite izi, dinani kuphatikiza Alt + F11 kapena batani Zooneka Basic tsamba Woyambitsa (Wolemba Mapulogalamu). Ngati tsamba ili silikuwoneka, yambitsani kaye pazokonda Fayilo - Zosankha - Kukhazikitsa Riboni (Fayilo - Zosankha - Sinthani Riboni). Pawindo la Visual Basic editor lomwe limatsegula, ikani gawo latsopano lopanda kanthu kudzera mu menyu Ikani - Module ndikulowetsa macrocode athu pamenepo:
Sub Add_Sell() Worksheets("Input Form").Range("A20:E20").Koperani 'Koperani mzere wa deta kuchokera pa fomu n = Worksheets("Sales").Range("A100000").Mapeto(xlUp) . Mzere 'dziwani kuchuluka kwa mzere womaliza mu tebulo. Mapepala Ogulitsa ("Malonda").Maselo(n + 1, 1).PasteSpecial Paste:=xlPasteValues'makani pamzere wopanda kanthu wotsatira Mapepala a Ntchito("Fomu Yolowetsa").Range("B5,B7,B9"). ClearContents 'mapeto omveka bwino Tsopano titha kuwonjezera batani pa fomu yathu kuti tigwiritse ntchito macro omwe adapangidwa pogwiritsa ntchito mndandanda wotsitsa Ikani tsamba Woyambitsa (Madivelopa - Ikani - Batani):
Mukachijambula, ndikuyika batani lakumanzere la mbewa, Excel ikufunsani macro omwe muyenera kuwapatsa - sankhani macro athu. Add_Sell. Mutha kusintha mawuwo pa batani podina kumanja ndikusankha lamulo Sinthani mawu.
Tsopano, mutadzaza fomuyo, mutha kungodina batani lathu, ndipo zomwe zalowetsedwa zidzawonjezedwa patebulo. Sales, ndiyeno mawonekedwe amachotsedwa kuti alowe nawo mgwirizano watsopano.
Khwerero 4 Lumikizani Matebulo
Tisanamange lipoti, tiyeni tilumikizane ndi matebulo athu kuti pambuyo pake tithe kuwerengera mwachangu malonda ndi dera, kasitomala, kapena gulu. M'mitundu yakale ya Excel, izi zimafuna kugwiritsa ntchito ntchito zingapo. VPR (VLOOKUP) m'malo mwa mitengo, magulu, makasitomala, mizinda, ndi zina zambiri patebulo Sales. Izi zimafuna nthawi ndi khama kuchokera kwa ife, komanso "kudya" zambiri za Excel. Kuyambira ndi Excel 2013, zonse zitha kukhazikitsidwa mochulukira mwa kukhazikitsa ubale pakati pa matebulo.
Kuti muchite izi, dinani pa tabu Deta (Tsiku) pitani paubwenzi (Ubale). Pazenera lomwe likuwoneka, dinani batani Pangani (chatsopano) ndipo sankhani kuchokera pamndandanda wotsikira pansi omwe amalumikizana nawo:
Mfundo yofunika: matebulo ayenera kufotokozedwa motere, mwachitsanzo, tebulo lolumikizana (Price) sichiyenera kukhala ndi mgawo wachinsinsi (dzina) zinthu zobwerezabwereza, monga zimachitikira patebulo Sales. Mwanjira ina, tebulo logwirizana liyenera kukhala lomwe mungasakasaka pogwiritsa ntchito VPRngati atagwiritsidwa ntchito.
Zoonadi, tebulo limagwirizanitsidwa mofananamo Sales ndi tebulo kasitomala ndi Common column kasitomala:
Pambuyo kukhazikitsa maulalo, zenera loyang'anira maulalo litha kutsekedwa; simuyenera kubwereza ndondomekoyi.
Gawo 5. Timamanga malipoti pogwiritsa ntchito chidule
Tsopano, kuti tifufuze malonda ndi kutsata zochitika za ndondomekoyi, tiyeni tipange, mwachitsanzo, mtundu wina wa lipoti pogwiritsa ntchito tebulo la pivot. Khazikitsani selo yogwira kukhala tebulo Sales ndikusankha tabu pa riboni Ikani - PivotTable (Lowetsani - Pivot Table). Pazenera lomwe limatsegulidwa, Excel idzatifunsa za komwe kumachokera deta (ie table Sales) ndi malo oyika lipoti (makamaka patsamba latsopano):
Chofunikira ndichakuti ndikofunikira kuyatsa bokosilo Onjezani deta iyi kumtundu wa data (Onjezani data ku Model Model) pansi pawindo kuti Excel amvetse kuti tikufuna kumanga lipoti osati pa tebulo lamakono, komanso kugwiritsa ntchito maubwenzi onse.
Pambuyo pang'anani OK gulu adzaoneka mu theka lamanja la zenera Pivot table fieldskomwe mungadina ulalo onsekuti muwone osati yamakono, komanso "matebulo anzeru" onse omwe ali m'buku nthawi imodzi. Kenako, monga momwe zilili patebulo lachikale la pivot, mutha kungokoka minda yomwe tikufuna kuchokera kumatebulo aliwonse okhudzana ndi malowa. fyuluta, Mizere, Zithunzi za Stolbtsov or Makhalidwe - ndipo Excel ipanga nthawi yomweyo lipoti lililonse lomwe tikufuna papepala:
Musaiwale kuti tebulo la pivot liyenera kusinthidwa nthawi ndi nthawi (pamene gwero likusintha) ndikudina kumanja ndikusankha lamulo. Sinthani & Sungani (Bwezeretsani), chifukwa sichingachite zokha.
Komanso, posankha selo iliyonse mwachidule ndikukanikiza batani Tchati cha Pivot (Pivot Chart) tsamba Analysis (Analysis) or magawo (Zosankha) mutha kuwona mwachangu zotsatira zomwe zawerengedwa mmenemo.
Gawo 6. Lembani zosindikiza
Ntchito inanso yachinsinsi chilichonse ndikudzaza mafomu ndi mafomu osiyanasiyana osindikizidwa (ma invoice, ma invoice, zochita, ndi zina). Ndinalemba kale za njira imodzi yochitira izi. Apa tikukhazikitsa, mwachitsanzo, kudzaza fomuyo ndi nambala ya akaunti:
Zimaganiziridwa kuti mu selo C2 wosuta adzalowetsa nambala (nambala ya mzere patebulo Sales, m'malo mwake), ndiyeno zomwe tikufuna zimakokedwa pogwiritsa ntchito zomwe zadziwika kale VPR (VLOOKUP) ndi zizindikiro INDEX (INDEX).
- Momwe mungagwiritsire ntchito ntchito ya VLOOKUP kuti muwone ndikuyang'ana makonda
- Momwe mungasinthire VLOOKUP ndi INDEX ndi MATCH ntchito
- Kudzaza mafomu ndi mafomu ndi data kuchokera patebulo
- Kupanga Malipoti ndi PivotTables