Excel for Analyst. 4 Izindlela Zokuhlaziya Idatha ku-Excel

I-Excel ingenye yesofthiwe engcono kakhulu yokuhlaziya idatha. Futhi cishe wonke umuntu esigabeni esisodwa noma kwesinye sempilo kwakudingeka abhekane nezinombolo nedatha yombhalo futhi acubungule ngaphansi kwezikhathi zokugcina eziqinile. Uma usadinga ukwenza lokhu, sizobe sesichaza amasu azosiza kakhulu ukuthuthukisa impilo yakho. Futhi ukuze siyenze ibonakale kakhudlwana, sizobonisa ukuthi singayisebenzisa kanjani sisebenzisa izithombe ezinyakazayo.

Ukuhlaziywa Kwedatha Nge-Excel PivotTables

Amathebula e-Pivot angenye yezindlela ezilula zokuzenzakalela ukucutshungulwa kolwazi. Ikuvumela ukuthi uqongelele uhlu olukhulu lwedatha olungahlelekile neze. Uma uyisebenzisa, cishe ungakhohlwa unaphakade mayelana nokuthi siyini isihlungi nokuhlelwa mathupha. Futhi ukuze uzidale, vele ucindezele izinkinobho ezimbalwa nje bese ufaka amapharamitha ambalwa alula, kuye ngokuthi iyiphi indlela yokwethula imiphumela oyidingayo ngokuqondile esimweni esithile.

Kunezindlela eziningi zokwenza ukuhlaziya idatha ngokuzenzakalelayo ku-Excel. Womabili amathuluzi akhelwe ngaphakathi kanye nezengezo ezingalandwa ku-inthanethi. Kukhona nesengezo esithi “Analysis Toolkit”, esathuthukiswa iMicrosoft. Inazo zonke izici ezidingekayo ukuze ukwazi ukuthola yonke imiphumela oyidingayo kufayela elilodwa le-Excel.

Iphakheji yokuhlaziya idatha eyakhiwe yi-Microsoft ingasetshenziswa kuphela ekhasini lokusebenza elilodwa ngeyunithi eyodwa yesikhathi. Uma izocubungula imininingwane etholakala kokumbalwa, khona-ke imininingwane ewumphumela izovezwa kuphela kokukodwa. Kwezinye, ububanzi buzoboniswa ngaphandle kwanoma imaphi amanani, lapho kukhona amafomethi kuphela. Ukuze uhlaziye ulwazi kumashidi amaningi, udinga ukusebenzisa leli thuluzi ngokuhlukana. Lena imojula enkulu kakhulu esekela inani elikhulu lezici, ikakhulukazi, ikuvumela ukuthi wenze izinhlobo ezilandelayo zokucubungula:

1. Ukuhlaziywa kokuhlakazeka.
2. Ukuhlaziya ukuhlobana.
3. I-Covariance.
4. Isibalo esimaphakathi esinyakazayo. Indlela edume kakhulu kwizibalo nokuhweba.
5. Thola izinombolo ezingahleliwe.
6. Yenza imisebenzi yokukhetha.

Lesi sengezo asenziwa sisebenze ngokuzenzakalelayo, kodwa sifakiwe kuphakheji evamile. Ukuze uyisebenzise, ​​kufanele uyivule. Ukuze wenze lokhu, thatha izinyathelo ezilandelayo:

1. Iya kumenyu ethi "Ifayela", bese uthola inkinobho ethi "Izinketho". Ngemva kwalokho, iya kokuthi "Izengezo". Uma ufake inguqulo ka-2007 ye-Excel, kuzomele uchofoze inkinobho ethi “Izinketho ze-Excel”, etholakala kumenyu ye-Office.
2. Okulandelayo, kuvela imenyu ye-pop-up, enesihloko esithi "Ukuphatha". Lapho sithola into ethi "Excel Add-ins", chofoza kukho, bese - enkinobheni ethi "Hamba". Uma usebenzisa ikhompyutha ye-Apple, vele uvule ithebhu ethi “Amathuluzi” kumenyu, bese uthola into ethi “Engeza i-Excel” ohlwini lokudonsela phansi.
3. Ebhokisini elivele ngemva kwalokho, udinga ukuhlola ibhokisi eduze kwento ethi "Iphakheji Lokuhlaziya", bese uqinisekisa izenzo zakho ngokuchofoza inkinobho ethi "KULUNGILE".

Kwezinye izimo, kungase kuvele ukuthi lesi sengezo asitholakalanga. Kulokhu, ngeke kube ohlwini lwama-addon. Ukuze wenze lokhu, chofoza inkinobho "Phequlula". Ungase futhi uthole ulwazi lokuthi iphakheji ayikho ngokuphelele kule khompyutha. Kulokhu, udinga ukuyifaka. Ukuze wenze lokhu, chofoza inkinobho "Yebo".

Ngaphambi kokuthi uvule iphekhi yokuhlaziya, kufanele uqale uvule i-VBA. Ukuze wenze lokhu, udinga ukuyilanda ngendlela efanayo nesengezo ngokwaso.

Ungasebenza kanjani ngamathebula e-pivot

Ulwazi lokuqala lungaba yinoma yini. Lokhu kungaba ulwazi mayelana nokuthengisa, ukulethwa, ukuthunyelwa kwemikhiqizo, njalonjalo. Kungakhathalekile lokhu, ukulandelana kwezinyathelo kuzohlala kufana:

1. Vula ifayela eliqukethe ithebula.
2. Khetha ububanzi bamaseli esizowuhlaziya sisebenzisa ithebula le-pivot.
3. Vula ithebhu ethi "Faka", futhi lapho udinga ukuthola iqembu elithi "Amathebula", lapho kukhona inkinobho ethi "Pivot Table". Uma usebenzisa ikhompyutha ngaphansi kwesistimu yokusebenza ye-Mac, udinga ukuvula ithebhu ethi "Idatha", futhi le nkinobho izotholakala kuthebhu ethi "Analysis".
4. Lokhu kuzovula ibhokisi elinesihloko esithi “Dala i-PivotTable”.
5. Bese usetha isibonisi sedatha ukuze sifane nobubanzi obukhethiwe.

Sivule itafula, ulwazi olungahlelwanga nganoma iyiphi indlela. Ukwenza lokhu, ungasebenzisa izilungiselelo zezinkambu zethebula le-pivot ngakwesokudla kwesikrini. Ngokwesibonelo, masithumele “Inani lama-oda” kunkambu ethi “Values”, kanye nolwazi olumayelana nabathengisi kanye nedethi yokuthengisa – emigqeni yethebula. Ngokusekelwe kudatha equkethwe kuleli thebula, amanani anqunywe ngokuzenzakalelayo. Uma kunesidingo, ungakwazi ukuvula ulwazi unyaka ngamunye, ikota noma inyanga. Lokhu kuzokuvumela ukuthi uthole imininingwane enemininingwane oyidingayo ngesikhathi esithile.

Isethi yamapharamitha atholakalayo izohluka kokuthi mangaki amakholomu akhona. Isibonelo, inani eliphelele lamakholomu ngu-5. Futhi sidinga nje ukubeka futhi siwakhethe ngendlela efanele, futhi sibonise isamba. Kulokhu, senza izenzo eziboniswe kulesi sithombe sopopayi.

Ungacacisa ithebula le-pivot ngokucacisa, isibonelo, izwe. Ukuze wenze lokhu, sifaka into ethi "Izwe".

Ungakwazi futhi ukubuka ulwazi mayelana nabathengisi. Ukuze senze lokhu, sishintsha ikholomu ethi “Izwe” ngethi “Umthengisi”. Umphumela uzoba okulandelayo.

Ukuhlaziywa kwedatha ngamamephu e-3D

Le ndlela yokubuka ye-geo-referenced yenza kube nokwenzeka ukubheka amaphethini ahlanganiswe nezifunda, kanye nokuhlaziya lolu hlobo lolwazi.

Inzuzo yale ndlela ukuthi asikho isidingo sokubhalisa ngokwehlukana izixhumanisi. Udinga nje ukubhala kahle indawo yejografi etafuleni.

Ungasebenza kanjani ngamamephu e-3D ku-Excel

Ukulandelana kwezenzo okudingeka uzilandele ukuze usebenze ngamamephu e-3D limi kanje:

1. Vula ifayela eliqukethe ububanzi bedatha othakaselayo. Isibonelo, ithebula lapho kukhona ikholomu "Izwe" noma "Idolobha".
2. Ulwazi oluzoboniswa kumephu kufanele lufomethwe njengethebula. Ukuze wenze lokhu, udinga ukuthola into ehambisanayo kuthebhu "Ekhaya".
3. Khetha amaseli azohlaziywa.
4. Ngemva kwalokho, iya kuthebhu "Faka", futhi lapho sithola inkinobho "imephu ye-3D".

Khona-ke ibalazwe lethu liyaboniswa, lapho amadolobha asetafuleni emelelwa njengamachashazi. Kodwa empeleni asidingi nje ukuba khona kolwazi mayelana nezindawo zokuhlala kumephu. Kubaluleke kakhulu kithina ukubona ulwazi oluxhumene nabo. Isibonelo, lawo manani angaboniswa njengobude bekholomu. Ngemuva kokuthi senze izenzo ezikhonjiswe kulokhu kugqwayiza, uma uhambisa phezulu kwekholomu ehambisanayo, idatha ehlotshaniswa nayo izoboniswa.

Ungasebenzisa futhi ishadi likaphayi, elifundisa kakhulu kwezinye izimo. Ubukhulu bombuthano buncike ekutheni inani eliphelele liyini.

Ishidi lesibikezelo ku-Excel

Ngokuvamile izinqubo zebhizinisi zincike ezicini zesizini. Futhi izici ezinjalo kufanele zicatshangelwe esigabeni sokuhlela. Kukhona ithuluzi elikhethekile le-Excel lalokhu, ozolithanda ngokunemba kwalo okuphezulu. Isebenza kakhulu ukwedlula zonke izindlela ezichazwe ngenhla, kungakhathaliseki ukuthi zinhle kangakanani. Ngendlela efanayo, ububanzi bokusetshenziswa kwayo bukhulu kakhulu - ezohwebo, ezezimali, ezokuthengisa kanye nezakhiwo zikahulumeni.

Okubalulekile: ukubala isibikezelo, udinga ukuthola ulwazi lwesikhathi esedlule. Izinga lokubikezela lincike ekutheni idatha iyisikhathi eside kangakanani. Kunconywa ukuthi kube nedatha ehlukaniswe ngezikhathi ezivamile (isibonelo, ngekota noma nyanga zonke).

Ungasebenza kanjani ngeshidi lesibikezelo

Ukuze usebenze ngeshidi lesibikezelo, kufanele wenze lokhu okulandelayo:

1. Vula ifayela, eliqukethe inani elikhulu lolwazi ezinkomba okudingeka sizihlaziye. Isibonelo, phakathi nonyaka odlule (yize kungcono kakhulu).
2. Gqamisa imigqa emibili yolwazi.
3. Iya kumenyu ethi "Idatha", bese uchofoza inkinobho ethi "Ishidi Lokubikezela".
4. Ngemva kwalokho, ingxoxo izovuleka lapho ungakhetha khona uhlobo lokumelwa okubonakalayo kwesibikezelo: igrafu noma i-histogram. Khetha evumelana nesimo sakho.
5. Setha idethi okufanele kuphele ngayo isibikezelo.

Esibonelweni esingezansi, sihlinzeka ngolwazi lweminyaka emithathu - 2011-2013. Kulokhu, kunconywa ukukhombisa izikhawu zesikhathi, hhayi izinombolo ezithile. Okusho ukuthi, kungcono ukubhala ngoMashi 2013, hhayi inombolo ethile efana noMashi 7, 2013. Ukuze uthole isibikezelo sika-2014 ngokusekelwe kule datha, kuyadingeka ukuthola idatha ehlelwe ngemigqa enosuku kanye nezinkomba ukuthi ngaleso sikhathi. Gqamisa le migqa.

Bese uye kuthebhu ethi “Idatha” bese ubheka iqembu elithi “Isibikezelo”. Ngemuva kwalokho, iya kumenyu ethi "Ishidi lesibikezelo". Ngemuva kwalokho, kuzovela iwindi lapho sizophinde sikhethe indlela yokwethula isibikezelo, bese sibeka usuku okufanele kuqedwe ngalo isibikezelo. Ngemva kwalokho, chofoza "Dala", ngemva kwalokho sithola izinketho ezintathu zesibikezelo (esiboniswe umugqa wewolintshi).

Ukuhlaziya okusheshayo ku-Excel

Indlela yangaphambilini inhle ngempela ngoba ikuvumela ukuthi wenze izibikezelo zangempela ngokusekelwe ezinkomba zezibalo. Kepha le ndlela ikuvumela ukuthi uqhube ubuhlakani bebhizinisi obugcwele. Kuhle kakhulu ukuthi lesi sici sidalwe njenge-ergonomically ngangokunokwenzeka, ngoba ukuze uzuze umphumela oyifunayo, udinga ukwenza ngokoqobo izenzo ezimbalwa. Azikho izibalo ezenziwa ngesandla, akukho ukubhala phansi noma yimaphi amafomula. Vele ukhethe ububanzi obuzohlaziywa bese usetha umgomo wokugcina.

Kungenzeka ukudala amashadi ahlukahlukene kanye nama-micrographs khona kanye kuseli.

Isebenza kanjani

Ngakho-ke, ukuze sisebenze, sidinga ukuvula ifayela eliqukethe isethi yedatha okudingeka ihlaziywe futhi sikhethe ububanzi obufanele. Ngemva kokuyikhetha, sizoba nenkinobho ngokuzenzakalelayo eyenza kube nokwenzeka ukudweba isifinyezo noma ukwenza isethi yezinye izenzo. Kubizwa ngokuthi ukuhlaziya okusheshayo. Singabuye sichaze amanani azofakwa ngokuzenzakalelayo phansi. Ungabona ngokucace kakhudlwana ukuthi kusebenza kanjani kulokhu kugqwayiza.

Isici sokuhlaziya okusheshayo sikuvumela futhi ukuthi ufomethe idatha ewumphumela ngezindlela ezihlukene. Futhi ungakwazi ukunquma ukuthi yimaphi amanani angaphezulu noma ngaphansi ngokuqondile kumaseli we-histogram evela ngemva kokumisa leli thuluzi.

Futhi, umsebenzisi angabeka omaka abahlukahlukene abakhombisa amanani amakhulu namancane ahlobene nalawo akusampula. Ngakho-ke, amanani amakhulu kakhulu azoboniswa ngokuluhlaza, futhi amancane kakhulu abomvu.

Ngifuna ngempela ukukholelwa ukuthi lezi zindlela zizokuvumela ukuthi ukhulise kakhulu ukusebenza kahle komsebenzi wakho ngamaspredishithi futhi uzuze konke okufunayo ngokushesha ngangokunokwenzeka. Njengoba ubona, lolu hlelo lwesipredishithi luhlinzeka ngamathuba abanzi kakhulu ngisho nasekusebenzeni okujwayelekile. Futhi singathini mayelana nezengezo, eziningi kakhulu ku-inthanethi. Kubalulekile kuphela ukuqaphela ukuthi wonke ama-addons kufanele abhekwe ngokucophelela amagciwane, ngoba amamojula abhalwe ngabanye abantu angase aqukathe ikhodi enonya. Uma izengezo zakhiwe yiMicrosoft, khona-ke zingasetshenziswa ngokuphepha.

I-Analysis Pack evela ku-Microsoft iyisengezo esisebenza kahle kakhulu esenza umsebenzisi abe uchwepheshe wangempela. Ikuvumela ukuthi wenze cishe noma yikuphi ukucutshungulwa kwedatha yobuningi, kodwa kuyinkimbinkimbi kakhulu kumsebenzisi we-novice. Isiza sosizo esisemthethweni seMicrosoft sinemiyalelo enemininingwane yokuthi ungasebenzisa kanjani izinhlobo ezahlukene zokuhlaziya ngale phakheji.