Excel kwa Analyst. 4 Njira Zowunikira Data mu Excel

Excel ndi imodzi mwamapulogalamu abwino kwambiri osanthula deta. Ndipo pafupifupi munthu aliyense pa nthawi ina ya moyo amayenera kuthana ndi manambala ndi zolemba zolemba ndikuzikonza pansi pamasiku omaliza. Ngati mukufunikirabe kuchita izi, ndiye kuti tikufotokozera njira zomwe zingakuthandizeni kwambiri kusintha moyo wanu. Ndipo kuti zikhale zowoneka bwino, tiwonetsa momwe tingawagwiritsire ntchito pogwiritsa ntchito makanema ojambula pamanja.

Kusanthula Kwa data Kupyolera mu Excel PivotTables

Pivot tables ndi imodzi mwa njira zosavuta zosinthira zidziwitso. Zimakuthandizani kuti muwunjike mndandanda waukulu wa data womwe sunapangidwe. Mukachigwiritsa ntchito, mutha kuyiwala nthawi zonse za zomwe fyuluta ndi kusanja pamanja. Ndipo kuti muwapange, ingodinani mabatani angapo ndikuyika magawo osavuta, kutengera njira yowonetsera zotsatira zomwe mukufuna makamaka munthawi inayake.

Pali njira zambiri zosinthira kusanthula kwa data mu Excel. Izi ndi zida zomangidwira komanso zowonjezera zomwe zitha kutsitsidwa pa intaneti. Palinso "Analysis Toolkit" yowonjezera, yomwe idapangidwa ndi Microsoft. Ili ndi zofunikira zonse kuti mupeze zotsatira zonse zomwe mukufuna mufayilo imodzi ya Excel.

Phukusi losanthula deta lopangidwa ndi Microsoft litha kugwiritsidwa ntchito patsamba limodzi pagawo limodzi la nthawi. Ngati ikonza zambiri zomwe zili pazigawo zingapo, ndiye kuti zomwe zatsatiridwa zidzawonetsedwa pa imodzi yokha. Mwa zina, mizere idzawonetsedwa popanda zikhalidwe zilizonse, momwe mumangokhalira mawonekedwe. Kuti mufufuze zambiri pamapepala angapo, muyenera kugwiritsa ntchito chida ichi padera. Ichi ndi gawo lalikulu kwambiri lomwe limathandizira kuchuluka kwazinthu, makamaka, limakupatsani mwayi wochita izi:

1. Kusanthula kwamwano.
2. Kusanthula kwa mgwirizano.
3. Covariance.
4. Kusuntha avareji kuwerengera. Njira yotchuka kwambiri paziwerengero ndi malonda.
5. Pezani manambala mwachisawawa.
6. Chitani ntchito zosankhidwa.

Zowonjezera izi sizimatsegulidwa mwachisawawa, koma zimaphatikizidwa mu phukusi lokhazikika. Kuti mugwiritse ntchito, muyenera kuyiyambitsa. Kuti muchite izi, tsatirani izi:

1. Pitani ku "Fayilo" menyu, ndikupeza "Zosankha" batani. Pambuyo pake, pitani ku "Zowonjezera". Ngati mudayika mtundu wa 2007 wa Excel, ndiye kuti muyenera dinani batani la "Excel Options", lomwe lili mumenyu ya Office.
2. Kenako, menyu yoyambira imawonekera, yotchedwa "Management". Kumeneko timapeza chinthu "Zowonjezera za Excel", dinani pa izo, ndiyeno - pa batani la "Pitani". Ngati mukugwiritsa ntchito kompyuta ya Apple, ingotsegulani tabu ya "Zida" mumenyu, kenako pezani chinthu cha "Add-ins for Excel" pamndandanda wotsitsa.
3. Muzokambirana zomwe zidawonekera pambuyo pake, muyenera kuyang'ana bokosi lomwe lili pafupi ndi chinthu cha "Analysis Package", ndikutsimikizira zochita zanu podina batani "Chabwino".

Nthawi zina, zitha kuwoneka kuti zowonjezerazi sizinapezeke. Pankhaniyi, sizikhala pamndandanda wa ma addons. Kuti muchite izi, dinani batani "Sakatulani". Mutha kulandiranso zambiri kuti phukusili likusowa pakompyutayi. Pankhaniyi, muyenera kukhazikitsa. Kuti muchite izi, dinani batani "Inde".

Musanayambe kuloleza paketi yowunikira, muyenera kuyambitsa VBA. Kuti muchite izi, muyenera kukopera chimodzimodzi monga chowonjezera chokha.

Momwe mungagwiritsire ntchito ma pivot tables

Chidziwitso choyambirira chikhoza kukhala chilichonse. Izi zitha kukhala zambiri zokhudzana ndi malonda, kutumiza, kutumiza zinthu, ndi zina zotero. Mosasamala kanthu za izi, kutsatizana kwa masitepe kumakhala kofanana nthawi zonse:

1. Tsegulani fayilo yomwe ili ndi tebulo.
2. Sankhani magulu osiyanasiyana omwe tidzasanthula pogwiritsa ntchito tebulo la pivot.
3. Tsegulani tabu ya "Insert", ndipo pamenepo muyenera kupeza gulu la "Matebulo", pomwe pali batani la "Pivot Table". Ngati mugwiritsa ntchito kompyuta pansi pa Mac OS opaleshoni dongosolo, muyenera kutsegula "Data" tabu, ndipo batani ili mu "Analysis" tabu.
4. Izi zidzatsegula zokambirana zotchedwa "Pangani PivotTable".
5. Kenako ikani chiwonetsero cha data kuti chifanane ndi mtundu womwe wasankhidwa.

Tatsegula tebulo, chidziwitso chomwe sichinapangidwe mwanjira iliyonse. Kuti muchite izi, mutha kugwiritsa ntchito makonda a magawo a tebulo la pivot kumanja kwa chinsalu. Mwachitsanzo, tiyeni titumize “Kuchuluka kwa maoda” mugawo la “Values”, ndi zambiri za ogulitsa ndi tsiku logulitsa – m’mizere ya tebulo. Kutengera ndi zomwe zili mu tebulo ili, ndalamazo zidadziwika zokha. Ngati ndi kotheka, mukhoza kutsegula zambiri chaka chilichonse, kotala kapena mwezi. Izi zikuthandizani kuti mupeze zambiri zomwe mukufuna panthawi inayake.

Magawo omwe alipo adzasiyana ndi kuchuluka kwa magawo omwe alipo. Mwachitsanzo, chiwerengero cha mizati ndi 5. Ndipo timangofunika kuziyika ndikuzisankha m'njira yoyenera, ndikuwonetsa kuchuluka kwake. Pankhaniyi, timachita zomwe zikuwonetsedwa mu makanema ojambula.

Mutha kutchula tebulo la pivot pofotokoza, mwachitsanzo, dziko. Kuti tichite izi, timaphatikizapo "Dziko".

Mutha kuwonanso zambiri za ogulitsa. Kuti tichite izi, timalowetsa gawo la "Dziko" ndi "Wogulitsa". Zotsatira zake zidzakhala zotsatirazi.

Kusanthula kwa data ndi mamapu a 3D

Njira yowonetsera geo-referenced iyi imapangitsa kuti zitheke kuyang'ana machitidwe ogwirizana ndi zigawo, komanso kusanthula chidziwitso chamtunduwu.

Ubwino wa njirayi ndikuti palibe chifukwa cholembera padera zogwirizanitsa. Mukungoyenera kulemba molondola malo omwe ali patebulo.

Momwe mungagwiritsire ntchito mamapu a 3D mu Excel

Mndandanda wazinthu zomwe muyenera kutsatira kuti mugwiritse ntchito mamapu a 3D ndi motere:

1. Tsegulani fayilo yomwe ili ndi zokonda zambiri. Mwachitsanzo, tebulo lomwe lili ndi gawo "Dziko" kapena "Mzinda".
2. Chidziwitso chomwe chidzawonetsedwa pamapu chiyenera kusinthidwa kukhala tebulo. Kuti muchite izi, muyenera kupeza chinthu choyenera pa tabu "Home".
3. Sankhani maselo oti muwunikenso.
4. Pambuyo pake, pitani ku tabu "Ikani", ndipo timapeza batani la "3D map".

Kenako mapu athu asonyezedwa, pamene mizinda imene ili patebulopo ikuimiridwa ngati madontho. Koma sikuti timangofunika kukhala ndi chidziwitso chokhudza midzi yomwe ili pamapu. Ndikofunikira kwambiri kwa ife kuwona zomwe zikugwirizana nazo. Mwachitsanzo, ndalama zomwe zitha kuwonetsedwa ngati kutalika kwa gawolo. Tikatha kuchita zomwe zasonyezedwa mu makanema ojambulawa, mukayang'ana pamwamba pa mzere womwewo, zomwe zikugwirizana nazo zidzawonetsedwa.

Mukhozanso kugwiritsa ntchito tchati cha chitumbuwa, chomwe chimakhala chodziwitsa zambiri nthawi zina. Kukula kwa bwalo kumadalira kuchuluka kwake komwe kuli.

Tsamba la Forecast mu Excel

Nthawi zambiri njira zamabizinesi zimadalira nyengo. Ndipo zinthu zoterezi ziyenera kuganiziridwa pokonzekera. Pali chida chapadera cha Excel cha izi, chomwe mungakonde ndi kulondola kwake kwakukulu. Zimagwira ntchito kwambiri kuposa njira zonse zomwe zafotokozedwa pamwambapa, ngakhale zitakhala zabwino bwanji. Momwemonso, kuchuluka kwa ntchito yake ndi kwakukulu kwambiri - malonda, ndalama, malonda komanso ngakhale maboma.

zofunika: kuti muwerenge zaneneratu, muyenera kupeza zambiri za nthawi yapitayi. Ubwino wa zolosera zimatengera nthawi yayitali bwanji. Ndikofunikira kukhala ndi data yomwe imagawidwa m'magawo okhazikika (mwachitsanzo, kotala kapena mwezi uliwonse).

Momwe mungagwirire ntchito ndi pepala lolosera

Kuti mugwiritse ntchito ndi pepala lolosera, muyenera kuchita izi:

1. Tsegulani fayilo, yomwe ili ndi chidziwitso chochuluka pa zizindikiro zomwe tiyenera kuzifufuza. Mwachitsanzo, m'chaka chatha (ngakhale zabwino kwambiri).
2. Onetsani mizere iwiri ya chidziwitso.
3. Pitani ku menyu ya "Data", ndipo dinani batani la "Forecast Sheet".
4. Pambuyo pake, zokambirana zidzatsegulidwa momwe mungasankhire mtundu wa chithunzithunzi chowonetseratu: graph kapena histogram. Sankhani yomwe ikugwirizana ndi vuto lanu.
5. Khazikitsani tsiku loti kulosera kuthe.

Mu chitsanzo pansipa, timapereka zambiri kwa zaka zitatu - 2011-2013. Pankhaniyi, tikulimbikitsidwa kusonyeza nthawi intervals, osati manambala enieni. Ndiko kuti, ndi bwino kulemba March 2013, osati nambala yeniyeni monga March 7, 2013. Kuti mupeze zowonetseratu za 2014 pogwiritsa ntchito deta iyi, m'pofunika kupeza deta yokonzedwa m'mizere ndi tsiku ndi zizindikiro zomwe anali nthawi imeneyo. Onetsani mizere iyi.

Kenako pitani ku tabu "Data" ndikuyang'ana gulu la "Forecast". Pambuyo pake, pitani ku "Forecast sheet" menyu. Pambuyo pake, zenera lidzawonekera pomwe tidzasankhanso njira yowonetsera zoloserazo, ndikuyika tsiku lomwe kulosera kumalizidwe. Pambuyo pake, dinani "Pangani", kenako timapeza njira zitatu zowonetsera (zowonetsedwa ndi mzere wa lalanje).

Kusanthula mwachangu mu Excel

Njira yapitayi ndi yabwino kwambiri chifukwa imakulolani kupanga maulosi enieni pogwiritsa ntchito zizindikiro zowerengera. Koma njira iyi imakulolani kuti muzichita bizinesi yodzaza ndi nzeru. Ndizosangalatsa kwambiri kuti gawoli limapangidwa mokhazikika momwe mungathere, chifukwa kuti mukwaniritse zomwe mukufuna, muyenera kuchitapo kanthu pang'ono. Palibe kuwerengera pamanja, osalemba mafomu aliwonse. Ingosankhani mndandanda womwe ukuyenera kuwunikiridwa ndikukhazikitsa cholinga chomaliza.

Ndizotheka kupanga ma chart osiyanasiyana ndi ma micrograph mu cell momwemo.

Momwe mungagwirire ntchito

Choncho, kuti tigwire ntchito, tifunika kutsegula fayilo yomwe ili ndi deta yomwe iyenera kufufuzidwa ndikusankha mtundu woyenera. Tikasankha, tidzakhala ndi batani lomwe limapangitsa kuti zitheke kujambula chidule kapena kuchita zina. Amatchedwa kusanthula mwachangu. Titha kufotokozeranso ndalama zomwe zidzalowetsedwa pansi. Mutha kuwona bwino lomwe momwe zimagwirira ntchito mu makanema ojambula awa.

Kusanthula mwachangu Mbali kumakupatsaninso mwayi wojambula zomwe zachitika m'njira zosiyanasiyana. Ndipo mutha kudziwa kuti ndi mfundo ziti zomwe zili mochulukirapo kapena mochepera mwachindunji m'maselo a histogram omwe amawonekera titakonza chida ichi.

Komanso, wogwiritsa ntchito amatha kuyika zolembera zosiyanasiyana zomwe zikuwonetsa zazikulu komanso zazing'ono zomwe zimagwirizana ndi zomwe zili pachiwonetsero. Chifukwa chake, zazikuluzikulu zidzawonetsedwa zobiriwira, ndipo zing'onozing'ono zofiira.

Ndikufuna kukhulupirira kuti njirazi zidzakuthandizani kuti muwonjezere bwino ntchito yanu ndi ma spreadsheets ndikukwaniritsa zonse zomwe mukufuna mwamsanga. Monga mukuwonera, pulogalamu ya spreadsheet iyi imapereka mwayi wokulirapo ngakhale pamachitidwe wamba. Ndipo tinganene chiyani za zowonjezera, zomwe zili zambiri pa intaneti. Ndikofunika kuzindikira kuti ma addons onse ayenera kuyang'anitsitsa mavairasi, chifukwa ma modules olembedwa ndi anthu ena akhoza kukhala ndi code yoyipa. Ngati zowonjezera zapangidwa ndi Microsoft, ndiye kuti zitha kugwiritsidwa ntchito mosamala.

Analysis Pack yochokera ku Microsoft ndiyowonjezera yogwira ntchito kwambiri yomwe imapangitsa wosuta kukhala katswiri weniweni. Zimakuthandizani kuti muzichita pafupifupi chilichonse chokhudza kuchuluka kwa data, koma ndizovuta kwambiri kwa wogwiritsa ntchito novice. Tsamba lovomerezeka la Microsoft lili ndi malangizo atsatanetsatane amomwe mungagwiritsire ntchito mitundu yosiyanasiyana ya kusanthula ndi phukusili.