Excel Ushwankathelo itheyibhile: ukwenza usebenze njani? Ukusebenza Excel PivotTables

Abasebenzisi abaninzi ofisi badla inani lemingeni xa uzama ukwenza ulungise naluphi uxwebhu Office. Amaxesha amaninzi oku kungenxa yokuba iinkampani zisebenzisa iinkqubo ofisi eziliqela, imigaqo yayo ziyahluka kakhulu. Ingakumbi ezininzi engxakini ukudala Excel PivotTables.

Ngethamsanqa, kwezinga kutshanje kwabonakala MS Office 2010-2013, equka inani software kwahlaziywa iifayile processing itekisi, spreadsheets, edatha kunye neentetho nje kuphela, kodwa ekuvumela ukuba usebenze nabo ngaxeshanye kubasebenzi eziliqela kwindawo yobambiswano nanto.

Kutheni version entsha?

Ukuze uqonde lo mbuzo, kufuneka ukucinga lonke utshintsho zibalulekileyo ezenzekileyo nale yisuti ye ofisi.

Njengoko ngaphambili, oko Excel yimfuneko yesibini inkqubo evumela ukuba ingabi nguwe wedwa ukudala iitafile elula, kodwa iyibeke uvimba ngokwanelisayo ezintsonkothileyo. Njengoko namanye amacandelo, kwaye kongezwa iqhosha "Office", nqakraza apho uyakwazi ukugcina uxwebhu kwi ifomathi efunekayo, utshintsho okanye ucacise amanyathelo afunekayo ukuze ukhuseleko lwayo. Iye yalungiswa kakhulu uxwebhu Imodyuli yokhuseleko.

Yintoni ekhathaza izilungiso ezibalulekileyo ukuya Excel, ziphawuleka ukulungisa iimpazamo ezininzi kule ifomula, ngenxa apho kukhupho elidlulileyo akholisa ukubakho iimpazamo ezinkulu kakhulu kwi izibalo.

Kufuneka kuthiwa, inguqulelo entsha ye package - ayikho nje iseti yeenkqubo ze ofisi, kodwa sisixhobo esinamandla oko kumlungele na iingxaki nkqu imisebenzi enzima. Njengoko uyakwazi ukuqonda thina namhlanje ziya kuqwalaselwa Excel pivot itafile.

Yintoni na kwaye yintoni na ukuba?

Oku kutshintsha kakhulu kwi kwinguqulelo entsha ye "Office" yi interface zabo perekroenny ngokupheleleyo, nto leyo eye kuthiwa Abenzi bemeko-iRibhoni (iribhoni). Amakhasethi zonke 1500 phatha lula zihlelwe izigaba, ngoko ke akuyi kuba kudala uzifune. Ukuthobela ngokupheleleyo nezi parameters, ababhekisi ngendikongezile ku Excel ngcono PivotTable.

Namhlanje siqwalasela Excel «for Dummies." iitafile Summary - sisixhobo ezizodwa, apho iziphumo inkqubo ebe ngokucacileyo. Ngamafutshane, ukuba ingasetyenziswa ukubona ukuba bangaphi na abo okanye ezinye iimpahla athengisa elowo umthengisi ukuze shift wakhe. Ukongeza, ukuba asetyenziswa kwiimeko apho kuyimfuneko;

• Lungiselela izibalo analytical zokubhala iingxelo zokugqibela.
• Bala Isalathisi yazo zonke ngokwahlukeneyo.
• Khupha ngokwamaqela yi uhlobo data.
• Ukuze lokucoca kwaye olunzulu uhlalutyo data.

Kweli nqaku siza kukhangela iindlela ezilula ukwenza kubo. Uya kufunda indlela Excel pivot itafile kunceda ukudala nokuhlalutya ixesha uthotho, ukuyila yezixhobo zecomputer umhlahlo eneenkcukacha.

Yenza uxwebhu oyifunayo

Ngoko ke indlela yokwenza ebenesizikithi itafile Excel? Okokuqala kufuneka ukwenza uxwebhu elula apho senza iinkcukacha ukwenzela uhlalutyo kamva. Enye umhlathi kufuneka akhawunti omnye parameter. Thabatha umzekelo olula:

• ixesha sentengiso.
• Izinto ezithengisiweyo.
• Iindleko lonikezelo yonke.

Ngenxa yoko, phakathi zonke iparameters eyenziwe kwikholam nganye ikhonkco: sicinge ukuba izihlangu ziye athengiswe ku-9 ekuseni, ingeniso eyenziwe engange-n.

iitafile yokufomatha

Emva kokuba elungise zonke iinkcukacha yokuqala, ubeke isikhombisi kwi kwiseli yokuqala kwikholam yokuqala, vula i tab "Faka" uze ucofe kwiqhosha "Uluhlu Isishwankathelo". Uya kubona ngoko nangoko ibhokisi yencoko yababini apho ukwazi ukwenza imisebenzi zilandelayo:

• Ukuba ngoko nangoko cinezela iqhosha "OK", ngoko Excel PivotTables lizakuboniswa ngoku kuxwebhu elahlukileyo.
• wenze ngokupheleleyo kukhuthaza.

Kwimeko yokugqibela, ababhekisi phambili kusinika ithuba ukujonga uluhlu iiseli eziya kuboniswa ulwazi wafuna. Emva koko, umsebenzisi kufuneka amisele apho etafileni entsha wadalelwa, ukuba ekhoyo okanye ngokutsha kwimakhishithi. Xa u- "OK", uya kubona kwangoko phambi kwetafile lilungile. Xa ekudalweni pivot e-Excel Oku lugqityiwe.

Kwicala lasekunene kwimimandla uxwebhu indawo, lowo uya kusebenza. Fields uyarhuqeka waphosa kwiindawo ezahlukeneyo, emva koko idatha ayakubekwa itafile. Ngako oko, isishwankathelo itafile ngokwayo iya kwi kwicala lasekhohlo kweqonga.

Ukubamba ngexesha ucofe ekhohlo iqhosha le mouse, i "imveliso", ukuthumela yona ibe "bar yesihloko", uze ucofe "Sum of Sales 'ngendlela phambili efanayo kwi" ixabiso "(kwicala lasekhohlo lwe sheet). Yiloo ndlela ungafumana isixa kwentengiso lonke ixesha zihlalutywe.

Ukuyila iqela ixesha series

Ukuze uhlaziye intengiso kubude elithile, kufuneka ufake iziqendu ezifanelekileyo kwi itafile ngokwayo. Ukuze wenze oku kufuneka ungene sheet "Data" uze ufake ezintsha column ezintathu-nangoko emva komhla. Khetha ikholam kunye negama lemveliso, uze ucofe kwiqhosha "Faka".

Kubaluleke kakhulu, kangangokuba yonke imiqolo ntsha ungaphakathi kwetafise ekhoyo kunye idatha engumthombo. Kulo mzekelo, kwakhona kufuneka ufune ulwenziwo pivot e-Excel. Uya kongeza nje amasimi ezintsha idatha ezinqwenelekayo.

ifomula esetyenzisiweyo

Umzekelo, makungazaliswa iikholam ezitsha eziye zavela ekunokuthiwa i "Unyaka", "Inyanga", "Iinyanga-kweminyaka." Ukuze ufumane i data thina abanomdla ngamnye kubo kuya kufuneka ukuba babhalise indlela eyahlukileyo ubalo lendlela:

• I "Annual" ukufaka formula fom: "= UNYAKA" (ubhekisele kumhla).
• Inyanga kumele ukuba luphunyezwe kwidama yi athi: "= INYANGA" (kwakhona ngokubhekisele umhla).
• Kwikholam yesithathu yefom thina ukufaka ifomula "= CONCATENATE" (ibhekisela kunyaka kunye nenyanga).

Ngako oko, sifumana imiqolo emithathu yonke idatha yoqobo. Ngoku, yiya kwi menu, "Isishwankathelo", nqakraza kwi nasiphi na isithuba esingenanto kwi table kwi ekunene iqhosha le mouse, yaye emva koko kwi wavula menu yentsingiselo khetha umba "Update." Ziqapheleni! bhala neefomula Excel PivotTables ngocoselelo, njengoko nayiphi na impazamo kuya kukhokelela iimpazamo uqikelelo.

Indlela yokuqhuba ukuhlalutywa yentengiso data?

Kwakhona cinezela i ekhohlo iqhosha le mouse nqakraza i "Unyaka", kwaye ngoku tsala yona ibe "Igama Column", emva koko iqala kwisishwankathelo itafile ibonisa iziphumo uhlobo oluthile intengiso yeziqhamo kwiminyaka yonke. Kwaye ukuba ufuna ukuhlalutya nje intengiso ngenyanga? Ngokufanayo, siye cinezela LMB "iNyanga", bewuzizilizisa phantsi kuluhlu yonyaka.

Ukuze ufumane i ebanzi olubonakalayo utshintsho ngenyanga kwiintengiso ekuhambeni kweminyaka, i "iNyanga" ku tsala ngqo kwikholam yonyaka, ngaphambili kuhlala i "Gama Column". Emva kokugqiba lo nyathelo, uya kubona itafile kule ntetho ilandelayo:

• Ubungakanani yokuthengisa imveliso ethile unyaka wonke.
• Ukuthengiswa Dynamics ngamnye kubo kule minyaka.

Susa i data ze kunikezelo

Ukucinga ukuba umba, kufuneka asuse data Oktobha, ngoko ke musa ukuba manani ezibanzi kuyo. Le summary table ekhangela "Ingxelo lokucoca", kwaye ngoku tsala yona kwi "Unyaka - Inyanga".

isihluzi livela ngaphezu etafileni apho ufuna ukubeka ibhokisi esecaleni "Khetha izinto ezininzi." Emva koko, olu luhlu elivelayo, sukukhangela Oktobha, kwaye ngoku nqakraza kwi "OK".

Ngoko ke, ungakwazi ukudibanisa yaye ulishenxise iziqalelo lokucoca, emenza abe bukhali ngenene kwaye udinga uvavanyo. Le program ubala ngokuzenzekelayo amanani abo iinyanga apho unomdla, ebonisa ngokucacileyo nemiba kwezalathisi tshintsho.

Silindele uqikelelo

Zama ukuqikelela iziphumo kwentengiso esithile ixesha elizayo. Ukuze wenze oku, kufuneka khubaza iitotali pre-grand, ngoko ke akazange enze impazamo sase- eyenziwe.

Ukuze likwazi ukukwenza oku, ubeke isikhombisi kwi "Grand Total", ngoko nqakraza kwiqhosha "Susa esi iyonke grand." Emva koko, lo uhlaziyo oluzenzekelayo ndlela Excel PivotTables echazwe ngentla thina (nqakraza kwi ekunene iqhosha le mouse, khetha "Update").

Ngenxa yoko, le data balahlekile ukusuka summary table yethu. Ukuze kwakhiwe lumelo olubonakalayo uqikelelo on yokuthengisa, ubeke isikhombisi kwi nyaka, siceba ukusuka apho ukwenza uqalo, size ke nqakraza mouse iqhosha iqhosha ekhohlo "umfuziselo Schedule uqikelelo."

Emva koko kukho igrafu entle ulwazi thaca kwi ifomu olubonakalayo iziphumo elindelekileyo kumsebenzi yabathengi kwi wokuxela enomdla oyenzileyo. Oku kuya konga amandla akho uze wonge imali ezinokuthi eyachithwayo kuthengwa kwempahla kuyimfuneko ngokwenene.

Sisebenzisa eminye imithombo yedatha ukuze kwakhiwe a PivotTable

Lixesha lokuba ingqalelo izifundo ezintsonkothileyo ngakumbi Excel. Le summary table anokwenziwa ngokusekelwe mpepha ziye lihlelwe ngabanye abasebenzisi.

Oku kuya kusinceda ukusebenza "Hlanganisa". Le program ivumela ukuba ukudibanisa ngokuzenzekelayo okubhaliweyo xwebhu kunye nedilesi kuyimfuneko, igama okanye olunye ulwazi oluyimfuneko ukwenza iingqikelelo ezenzelwe ukuba kule meko kwaye iyafuneka.

uhlanganise umsebenzi kwiinguqulelo yamva "ofisi" ayitshintshanga kangako kuthelekiswa Ofisi kunjalo imibuzo 2003. database ukuba ukhethe umthombo kwedatha Zenziwe ukusetyenziswa Microsoft Query. Ukuze ufumane i isishwankathelo data Excel spreadsheet, kufuneka ukuba izinto ezimbini:

• Uxwebhu oluphambili siqulathe inkcazelo oyifunayo, kwakunye entsimini obonisa Iprogram i-Excel, i-data elizakufakwa ukuya kuxwebhu. Le theyibhile engundoqo uxwebhu eqhelekileyo, spreadsheet, kodwa kufuneka ukhankanye kwi Ulungiso Lweposi yababini (Ulungiso Lweposi Mncedi), oko kufuneka lusetyenziswe njengendlela yokuqala.
• Umthombo wedatha iqulethe ingcaciso eyimfuneko ukuze ulungiselelo sase-. Unako ukudala itafile entsha ngendlela siye kubonisiwe ngentla apha.

Xa inkqubo yokuveliswa amaxwebhu isishwankathelo, inkqubo kudala ikopi enye ngamnye kweerekhodi equlathwe engumthombo data ifayile. Ezi fayile ingabonakaliswa icandelo elahlukileyo uxwebhu olutsha, okanye isetyenziswe njengendawo isahlulelo yetafile yakho pivot.

Enye kuphela nqakraza kwi endle ezinqwenelekayo iqhosha yakho mouse lasekhohlo, kwaye ungabona ukuba umthombo wedatha endaweni amagama avela kwi uxwebhu oluphambili. Fields lava nga nghenelangiki nakweyiphi na indawo kweli xwebhu.

iindidi babesebenzisa

Kwakhona ungasebenzisa BUZA udidi, gama elo elithetha "bafune" kunye bazalise oko kuthethwa "ukugcwalisa" NEXT kunye NEXTIF, ABANYE. Unga kwakhona kutsha parameters iingongoma uqikelelo ukuboniswa kolu luhlu lokugqibela. Khangela ungeno efunekayo kunokwenzeka ngokusebenzisa lokucoca ekhethekileyo okanye nje uyilungise i data, njengoko sesitshilo ngasentla.

Xa sele ukulungele ukwenza uxwebhu, Hlanganisa ibhokisi yencoko kuvumela umsebenzisi ukuba ukuchonga alungise iimpazamo ngaphambi kokuba zenzeke. Xa zonke ikhangelwe, unako ngokukhuselekileyo cinezela iqhosha "Hlanganisa". Ukuba kuyenzeka kufuneka wenziwe nge e-mail, uza kuyalelwa ukuba ufake i gocr ye-imeyile (ukuba ngaba sele ifakiwe).

Kwimeko apho kukho nayiphi na impazamo, oko ikwinkqubo iintetho naziphi malunga ecacisiwe ngokungachanekanga lolunye uxwebhu, okanye ukusebenzisa idatha kwi ifomati ayixhaswanga. Ngoko ke, le nkqubo kufuneka kwakhona zijongisiswe kwakhona.

Ngokubanzi, Excel (umsebenzi kunye PivotTables apho siye sabona) uvumela kuphela uhlalutyo entsonkothileyo kakhulu okuthenga, kodwa ukudala kuzo ngokusekelwe kuqikelelo.