Okuqukethwe
Uma ukhuluma ngemininingwane yolwazi (DB), into yokuqala efika engqondweni, vele, zonke izinhlobo zamagama afana ne-SQL, Oracle, 1C, noma okungenani Ukufinyelela. Yiqiniso, lezi zinamandla kakhulu (futhi zibiza kakhulu) izinhlelo ezingakwazi ukwenza umsebenzi wenkampani enkulu neyinkimbinkimbi enedatha eminingi. Inkinga iwukuthi ngezinye izikhathi amandla anjalo awadingeki. Ibhizinisi lakho lingase libe lincane futhi libe nezinqubo zebhizinisi ezilula, kodwa futhi ufuna ukulisebenzisa ngokuzenzakalelayo. Futhi kungenxa yezinkampani ezincane ukuthi lokhu kuvame ukuba yindaba yokuphila.
Okokuqala, ake sakhe i-TOR. Ezimweni eziningi, isizindalwazi se-accounting, isibonelo, ukuthengisa kwakudala kufanele ukwazi:
- Gcina kumathebula ulwazi lwezimpahla (inani), ukuthengiselana okuqediwe kanye namakhasimende futhi baxhumanise lawa mathebula kwelinye
- zikhululeke amafomu okokufaka idatha (nohlu lokudonsela phansi, njll.)
- gcwalisa ngokuzenzakalelayo enye idatha amafomu aphrintiwe (izinkokhelo, izikweletu, njll.)
- khipha okudingekayo imibiko ukulawula yonke inqubo yebhizinisi ngokombono womphathi
I-Microsoft Excel ingakwazi ukubhekana nakho konke lokhu ngomzamo omncane. Ake sizame ukwenza lokhu.
Isinyathelo 1. Idatha yokuqala ngendlela yamathebula
Sizogcina ulwazi olumayelana nemikhiqizo, ukuthengiswa kanye namakhasimende kumathebula amathathu (ephepheni elilodwa noma kwamanye ahlukene – akunandaba). Kubaluleke kakhulu ukuwenza "amathebula ahlakaniphile" anosayizi ozenzakalelayo, ukuze ungacabangi ngawo esikhathini esizayo. Lokhu kwenziwa ngomyalo Fometha njengetafula ithebhu Ikhaya (Ekhaya — Fometha Njengethebula). Kuthebhu evelayo umakhi (Umklamo) nika amathebula amagama achazayo ensimini Igama lethebula ukuze zisetshenziswe kamuva:
Sekukonke, kufanele sithole "amatafula ahlakaniphile" amathathu:
Sicela uqaphele ukuthi amathebula angase aqukathe idatha yokucacisa eyengeziwe. Ngakho, ngokwesibonelo, yethu Inaniiqukethe ulwazi olwengeziwe mayelana nesigaba (iqembu lomkhiqizo, ukupakishwa, isisindo, njll.) somkhiqizo ngamunye, kanye nethebula Client - idolobha nesifunda (ikheli, i-TIN, imininingwane yasebhange, njll.) ngayinye yazo.
Ithebula Sales izosetshenziswa yithi ngokuhamba kwesikhathi ukufaka ukuthengiselana okuqediwe kuyo.
Isinyathelo sesi-2. Dala ifomu lokufaka idatha
Yebo, ungafaka idatha yokuthengisa ngqo kuthebula eliluhlaza Sales, kodwa lokhu akulula ngaso sonke isikhathi futhi kuhlanganisa ukuvela kwamaphutha nokuthayipha ngenxa “yesici somuntu”. Ngakho-ke, kungaba ngcono ukwenza ifomu elikhethekile lokufaka idatha eshidini elihlukile lento enjengale:
Kuseli B3, ukuze uthole isikhathi sosuku samanje esibuyekeziwe, sebenzisa umsebenzi Idatha ye-TDATA (MANJE). Uma isikhathi singadingeki, esikhundleni salokho Idatha ye-TDATA umsebenzi ungasetshenziswa NAMUHLA (NAMUHLA).
Kuseli B11, thola intengo yomkhiqizo okhethiwe kukholamu yesithathu yethebula elihlakaniphile Inani usebenzisa umsebenzi VPR (VLOOKUP). Uma ungakaze uhlangane nakho ngaphambilini, qala ufunde futhi ubuke ividiyo lapha.
Kuseli B7, sidinga uhlu olwehliswayo olunemikhiqizo evela ohlwini lwamanani. Ukuze wenze lokhu ungasebenzisa umyalo Idatha - Ukuqinisekiswa Kwedatha (Idatha - Ukuqinisekisa), cacisa njengesibophezelo Uhlu (Uhlu) bese ungena ensimini Umthombo (Umthombo) isixhumanisi kukholomu Igama kusuka etafuleni lethu elihlakaniphile Inani:
Ngokufanayo, uhlu lokudonsela phansi olunamakhasimende luyakhiwa, kodwa umthombo uzoba mncane:
=INDIRECT(“Amakhasimende[Iklayenti]”)
Umsebenzi INDALO (INDIRECT) kuyadingeka, kulokhu, ngoba i-Excel, ngeshwa, ayiqondi izixhumanisi eziqondile zamatafula ahlakaniphile emkhakheni womthombo. Kodwa isixhumanisi esifanayo "sigoqwe" kumsebenzi INDALO ngesikhathi esifanayo, isebenza nge-bang (okuningi mayelana nalokhu bekuku-athikili mayelana nokudala uhlu lokudonsela phansi ngokuqukethwe).
Isinyathelo sesi-3. Ukwengeza i-macro entry yokuthengisa
Ngemva kokugcwalisa ifomu, udinga ukwengeza idatha efakwe kulo ekupheleni kwetafula Sales. Sisebenzisa izixhumanisi ezilula, sizokwakha umugqa ozokwengezwa ngezansi kwefomu:
Labo. iseli A20 izoba nesixhumanisi esiya ku-=B3, iseli B20 izoba nesixhumanisi esiya ku-=B7, njalonjalo.
Manje ake sengeze i-macro eyisisekelo enemigqa emi-2 ekopisha iyunithi yezinhlamvu ekhiqiziwe bese iyingeza kuthebula Lokuthengisa. Ukuze wenze lokhu, cindezela inhlanganisela I-Alt + F11 noma inkinobho Visual Basic ithebhu Unjiniyela (Unjiniyela). Uma le thebhu ingabonakali, yivule kuqala kuzilungiselelo Ifayela – Izinketho – Ukusethwa kweRibhoni (Ifayela — Izinketho — Yenza Iribhoni Ngokwezifiso). Kuwindi lomhleli we-Visual Basic elivulayo, faka imojuli entsha engenalutho ngemenyu Faka – Imojula bese ufaka ikhodi yethu enkulu lapho:
I-Sub Add_Thengisa() Amakhasi Okusebenzela("Ifomu Lokufaka").Ububanzi("A20:E20").Kopisha 'Kopisha umugqa wedatha efomini elithi n = Amakhasi Okusebenzela("Ukuthengisa").Ububanzi("A100000").Qeda(xlUp) . Umugqa 'nquma inani lomugqa wokugcina kuthebula. Amakhasi Okusebenzela Okuthengisa("Okuthengisa").Amaseli(n + 1, 1).Namathisela Okukhethekile Okunamathiselwe:=xlPasteValues‘namathisela emugqeni olandelayo ongenalutho Amakhasi Okusebenza("Ifomu Lokufaka").Ububanzi("B5,B7,B9"). I-ClearContents 'sula ifomu elincane lokuqeda Manje singakwazi ukwengeza inkinobho efomini lethu ukuze siqhube i-macro edaliwe sisebenzisa uhlu olwehliswayo Faka ithebhu Unjiniyela (Unjiniyela — Faka — Inkinobho):
Ngemuva kokuyidweba, ubambe inkinobho yegundane engakwesobunxele, i-Excel izokubuza ukuthi iyiphi i-macro okudingeka uyinikeze yona - khetha i-macro yethu Engeza_Thengisa. Ungashintsha umbhalo enkinobho ngokuchofoza kwesokudla kuwo bese ukhetha umyalo Shintsha umbhalo.
Manje, ngemva kokugcwalisa ifomu, ungamane uchofoze inkinobho yethu, futhi idatha efakiwe izokwengezwa ngokuzenzakalelayo etafuleni. Sales, bese ifomu liyasulwa ukuze kufakwe isivumelwano esisha.
Isinyathelo 4 Ukuxhumanisa Amathebula
Ngaphambi kokwakha umbiko, ake sixhumanise amathebula ethu ndawonye ukuze kamuva sikwazi ukubala ngokushesha ukuthengiswa ngokwesifunda, ikhasimende, noma isigaba. Ezinguqulweni ezindala ze-Excel, lokhu kuzodinga ukusetshenziswa kwemisebenzi eminingana. VPR (VLOOKUP) ngokushintsha amanani, izigaba, amakhasimende, amadolobha, njll. etafuleni Sales. Lokhu kudinga isikhathi nomzamo kithi, futhi “kudla” izinsiza eziningi ze-Excel. Ukuqala nge-Excel 2013, yonke into ingenziwa kalula ngokumisa ubudlelwano phakathi kwamathebula.
Ukuze wenze lokhu, kuthebhu Idatha (Usuku) ukuchofoza ubuhlobo (Ubudlelwano). Efasiteleni elivelayo, chofoza inkinobho Dala (okusha) bese ukhetha ohlwini lokudonsela phansi amathebula namagama ekholomu okufanele ahlotshaniswe ngawo:
Iphuzu elibalulekile: amathebula kufanele acaciswe ngale ndlela, okungukuthi ithebula elixhunyiwe (Inani) akumele ibe nekholomu yokhiye (Igama) imikhiqizo eyimpinda, njengoba kwenzeka kuthebula Sales. Ngamanye amazwi, ithebula elihlobene kufanele kube yilo lapho ungasesha khona idatha usebenzisa VPRuma isetshenzisiwe.
Yiqiniso, itafula lixhunywe ngendlela efanayo Sales ngetafula Client ngekholomu evamile ikhasimende:
Ngemva kokusetha izixhumanisi, iwindi lokuphatha izixhumanisi lingavalwa; awudingi ukuphinda le nqubo.
Isinyathelo sesi-5. Sakha imibiko sisebenzisa isifinyezo
Manje, ukuhlaziya ukuthengisa nokulandelela ukuguquguquka kwenqubo, ake sidale, isibonelo, uhlobo oluthile lombiko sisebenzisa ithebula le-pivot. Setha iseli elisebenzayo etafuleni Sales bese ukhetha ithebhu kuribhoni Faka – PivotTable (Faka — Ithebula Le-Pivot). Ewindini elivulayo, i-Excel izosibuza ngomthombo wedatha (okungukuthi, ithebula Sales) nendawo yokulayisha umbiko (okungcono eshidini elisha):
Iphuzu elibalulekile ukuthi kuyadingeka ukunika amandla ibhokisi lokuhlola Engeza le datha kumodeli yedatha (Engeza idatha kumodeli yedatha) phansi kwefasitela ukuze i-Excel iqonde ukuthi sifuna ukwakha umbiko hhayi kuphela etafuleni lamanje, kodwa futhi sisebenzise bonke ubudlelwano.
Ngemuva kokuchofoza OK iphaneli izovela engxenyeni engakwesokudla yewindi Izinkambu zethebula le-Pivotlapho ungachofoza khona isixhumanisi Zonkeukubona hhayi kuphela leli lamanje, kodwa wonke “amatafula ahlakaniphile” asencwadini ngesikhathi esisodwa. Bese, njengakuthebula le-pivot lakudala, ungavele uhudule izinkambu esizidingayo kusuka kunoma yimaphi amathebula ahlobene uwayise endaweni. Hlunga, Imigqa, Stolbtsov or Izindinganiso - futhi i-Excel izokwakha ngokushesha noma yimuphi umbiko esiwudingayo eshidini:
Ungakhohlwa ukuthi ithebula le-pivot lidinga ukubuyekezwa ngezikhathi ezithile (uma idatha yomthombo ishintsha) ngokuchofoza kwesokudla kuyo nokukhetha umyalo. Buyekeza & Londoloza (Vuselela), ngoba ayikwazi ukukwenza ngokuzenzakalelayo.
Futhi, ngokukhetha noma iyiphi iseli kusifinyezo bese ucindezela inkinobho Ishadi le-Pivot (Ishadi Le-Pivot) ithebhu Analysis (Ukuhlaziya) or Amapharamitha (Izinketho) ungakwazi ukubona ngeso lengqondo ngokushesha imiphumela ebalwe kuyo.
Isinyathelo sesi-6. Gcwalisa okuphrintekayo
Omunye umsebenzi ojwayelekile wanoma iyiphi i-database ukugcwalisa ngokuzenzakalelayo amafomu ahlukahlukene aphrintiwe namafomu (ama-invoyisi, ama-invoyisi, izenzo, njll.). Sengike ngabhala ngenye yezindlela zokwenza lokhu. Lapha sisebenzisa, ngokwesibonelo, ukugcwalisa ifomu ngenombolo ye-akhawunti:
Kucatshangwa ukuthi kuseli C2 umsebenzisi uzofaka inombolo (inombolo yomugqa etafuleni Sales, empeleni), bese idatha esiyidingayo idonswa kusetshenziswa umsebenzi osuvele ujwayelekile VPR (VLOOKUP) kanye nezici INDEX (INDEX).
- Ungawusebenzisa kanjani umsebenzi we-VLOOKUP ukuze ubheke futhi ubheke amanani
- Uyifaka kanjani esikhundleni se-VLOOKUP nge-INDEX kanye nemisebenzi ye-MATCH
- Ukugcwaliswa ngokuzenzakalelayo kwamafomu namafomu ngedatha evela etafuleni
- Ukudala Imibiko ngama-PivotTables