Excel Summary tafura: sei pakusika uye kushanda? Kushanda Excel PivotTables

Vakawanda vanoshandisa hofisi vanowanzosarurwa zvinoverengeka kana kuedza kuumba uye aongorore chero Office magwaro. Kazhinji ichi ndicho nokuti kuti makambani vanoshandisa dzakawanda hofisi zvirongwa, nenhungamiro zvinogona siyana zvikuru. Kunyanya dambudziko anogona kusika Excel PivotTables.

Sezvineiwo, panongova achangobva yakabuda MS Office 2010-2013, izvo kwete chete unosanganisira dzinoverengeka yoku- Software kuti kubudiswa wenyaya mafaira, Spreadsheets, Databases uye mharidzo, asiwo unokubvumira kushanda navo panguva imwe chete kuti vashandi vakawanda vari yemakambani zvakatipoteredza anokosha.

Nei shanduro itsva?

Kuti tinzwisise mubvunzo uyu, unofanira kufunga zvose zvikuru kuchinja kuti kwakaitika pamwe ichi muhofisi Suite.

Sapakutanga, ndiye Excel ndiyo kwokudiwa chechipiri purogiramu kuti kwete chete kunoita kuti kusika nyore matafura, asi kunyange kuumba kumbonyanya kunzwisisa Database. Kufanana nevamwe zvinoriumba, yave kuwedzera "Office" bhatani, nekudzvanya pamusoro paunogona kuponesa gwaro iri kutsvakwa format, kuchinja kana kutaridzwa yaidiwa zviyero nokuda kwayo kudzivirira. Zvakange yekudyidzana upgraded kuchengeteka module yacho gwaro.

Kana rinonyatsooneka kuchinja kuna Excel, zvinofanira kucherechedzwa kugadzirisa zvakakanganiswa akawanda mune zvokuimba, nokuti chii vakambodanana neshanduro kazhinji akazviratidza zvikanganiso zvikuru zvakakomba mune Masvomhu.

Zvinofanira akati kuti zvitsva shanduro pasuru - izvi hausingori purogiramu muhofisi, asiwo chishandiswa chine simba, dzakakodzera kugadzirisa kunyange dzakaoma mabasa. Sezvaunogona isu kunzwisisa nhasi ichakurukurwa Excel pivot tafura.

Chii uye chii nokuda?

Rinonyatsooneka Kuchinja itsva shanduro "Office" ndiko kwavo inowanikwa zvakazara perekroenny, iyo yave kunzi creators ari Ribbon (Ribbon). Matepi mirayiro yose 1500 vari nyore dzikarongwa ne Mapato, saka iwe usina vachashuva kuzviwana. Kuti kutevedzera zvizere parameters izvi, kuti Developers zvakawedzera Excel kunowedzerawo PivotTable.

Nhasi isu funga Excel «kuti dummies." Summary pamatafura - chaiyo chokushandisa, umo migumisiro ine kwacho dzikarongwa zvakajeka. Zvichitaurwa zviri nyore, vanogona kushandiswa kuona vangani avo kana zvimwe zvinhu vakatengesa mumwe mutengesi kuti kusuduruka ake. Mukuwedzera, vari kushandiswa munyaya apo zvakakodzera:

• Gadzirira Analytical Masvomhu kunyora yokupedzisira mishumo.
• Change chakarurama avo ose voga.
• Kubereka muzvikwata zvisina tsarukano ne mhando mashoko.
• Kuchenesa uye-Kunyatsoongorora mashoko.

Munyaya ino tichakurukura nyore nzira kusika navo. Uchadzidza kuti Excel pivot tafura zvinobatsira kuumba uye kuongorora nguva series, kuti asike uye parse uwane kushurudzira.

Akasika achida gwaro

Saka sei kuti pave pivot tafura Excel? First unofanira kusika gwaro nyore dzatiri kuita mashoko kuti akazotevera kuongorora. Mumwe Mbiru anofanira zvinokonzera rimwe parameter. Tora muenzaniso nyore:

• Kutengeswa nguva.
• Goods akatengesa.
• The zvachose mutengo wokutengesa.

Saka, pakati parameters zvose zvakapararira neimwe Link Mbiru: vanofunga kuti shangu chatengeswa 9 am, mubereko akaita N-rubles.

formatting pamatafura

Pashure Pakugadzirira zvose mashoko yepakutanga, kuisa cursor muchitokisi rokutanga mbiru wokutanga, svinudzai Tab "Insert" uye zvadaro tinya bhatani "Summary Table". Uchava pakarepo kuona munhu nhaurirano bhokisi umo munogona kuita zvinotevera kushanda:

• Kana iwe pakarepo achizviendesa "OK" bhatani, ipapo Excel PivotTables chichaitwa pakarepo kuratidzwa pamusoro yoga jira.
• gadzirisa excretion Fully.

Mukupera zvazvingava, Developers kutipa mukana kuona kuwanda masero zvichawanika akaratidza ruzivo kutsvaka. Shure kwaizvozvo, inodzidzisa anofanira kuona apo tafura mutsva akasikwa zvakananga, kuti aivapo kana ichangobva akasika pamusoro jira. By nekudzvanya "OK", iwe uchava pakarepo kuona pamberi akagadzirira tafura. At pakasikwa pivot mabwendefa Excel ari kushanda rakapera.

Kurutivi rworudyi pamusoro jira yaiva nzvimbo, uyo uchava kushanda. Fields zvinogona vakakakatira uye zvikanyauka kupinda vakaparadzana munzvimbo, pashure iyo Data zvinofanira kuratidzwa patafura. Saizvozvowo, netafura pivot ichatambanudzirwa iri parutivi workspace rworuboshwe.

Wakabata apo kudzvanya kuruboshwe negonzo bhatani, mashoko "chigadzirwa", vakatumira kuva "title muzariro", uye baya "Sum pakati Sales" Saizvozvowo mberi mu "mutengo" (kurutivi jira kuruboshwe). Ndizvo unogona kuwana chitsama rokutengesa nokuti yose vakaongorora nguva.

Kuunza uye muzvikwata zvisina tsarukano nguva dzino

Kuongorora kutengesa dzakananga rebei, unofanira dzinoisa kuMudzidzisi ndima iri tafura pacharo. Kuti aite izvi unofanira kupinda jira "Data" uye zvadaro pinza Mbiru itsva matatu pakarepo pashure musi. Sarudza mbiru pamwe zita chigadzirwa, uye ipapo tinya bhatani "Insert".

Zvakakosha chaizvo, saka kuti zvose vachangobva akasika mbiru dzaiva mukati kumbova tafura kunobva Data. Panyaya iyi, pakusikwa pivot mabwendefa Excel zvakare kuti zvaidiwa. Uchava nyore kuwedzera minda zvitsva kuna unodikanwa mashoko.

kushandiswa fomura

Somuenzaniso, richangobva akasika mbiru vanogona kunzi "Year", "Mwedzi", "Mwedzi-makore." Kuti mashoko tiri kufarira mumwe nomumwe wavo ku nyoresa imwevo inonyatsoita kuti kuverenga:

• The "Annual" dzinoisa mashoko yokuita rekuti: "= GORE" (kureva dating).
• Mwedzi inofanira vaisanganisa nemashoko okuti: "= PAMWEDZI" (uyewo achitaura dating).
• In wechitatu mbiru chimiro isu dzinoisa mashoko payakavakirwa "= CONCATENATE" (kureva gore uye mwedzi).

Saka, tinowana mbiru matatu ose epakutanga mashoko. Zvino endai Muterere "Summary", tinya chero chinhu ari tafura kurudyi negonzo bhatani, uye ipapo wakavhurika akapoteredza Muterere kusarudza chinhu "Update". Teererai! Nyatsoongorora nyora zvokuimba Excel PivotTables, sezvo paine chikanganiso kuchaita kuti kufungidzira zvikanganiso.

Sei kuitisa kuongorora wokutengesa mashoko?

Zvakare tinoenderera kuruboshwe negonzo bhatani tinya "Year", uye ipapo achizvuzvurudza akaibayirira "Zita Column", uye ipapo rinotanga tafura pfupiso anoratidza migumisiro imwe zvibereko rokutengesa mhiri makore ose. Uye kana uchida chete kuongorora pamwedzi okutengesa? Saizvozvowo, isu achizviendesa LMB "Mwedzi", vachikweva pasi wegore mbiru.

Kuti tiwane ziso rinomutsa vana pamwedzi kuchinja rokutengesa kwemakore, kuti "Mwedzi" hakumbofi zvakananga pagore mbiru, kare akatamira mu "Zita Column". Ndapedza danho iri, uchaona tafura zvinotevera mharidzo:

• Huwandu rokutengesa imwe chigadzirwa rose gore.
• The simba wokutengesa neimwe kwemakore.

Bvisai Data ari issuance

Kufunga kuti nyaya, tinofanira kubvisa October data, saka hatina nzwisisika nhamba pairi. The pfupiso tafura achitarisa "Report Filter", uye ipapo achizvuzvurudza kuna "Year - Month".

Sefa anozviratidza pamusoro tafura apo uchida kuisa bhokisi rinotevera kuti "Sarudza vakawanda zvinhu." Pashure paizvozvo, zvawanyora inooneka, uncheck October, uye ipapo tinya "OK".

Saka, unogona kuwedzera uye kubvisa Sefa okunze, pova here nokukurumidza uye unofanira nemiedzo. Chirongwa rwoga anoti mifananidzo kune mwedzi umo uchifarira, zvakajeka kuratidza simba kuchinja zviratidzo.

Tinotarisira maberere

Edza kufungidzira migumisiro rokutengesa kwenguva yakati kuuya. Kuti aite izvi, unofanira chokukuvadza kuti pre-vaiita mhinduro kuti havana vakaita chikanganiso ari kunotangira kushurudzira.

Kuti aite izvi, akaisa cursor mu "Grand Total", ipapo tinya bhatani "Bvisai Nhamba." Zvadaro, zvichabva zvangoita update pamusoro Excel PivotTables nzira anorondedzerwa pamusoro pedu (tinya kurudyi mbeva bhatani, sarudza "Update").

Somugumisiro, mashoko aya vakarasika kubva yedu tafura pfupiso. Kuvaka kutarisana kumiririrwa Zvaifungidzirwa pamusoro rokutengesa, akaisa cursor kusvikira gore, izvo kuronga kuita kutanga, uye ipapo isu tinya mbeva wakasiya bhatani pamusoro bhatani "Purogiramu kufungidzira muenzaniso."

Pashure paizvozvo pane akanaka uye anodzidzisa girafu dzinotaurwa kuona chimiro akatarisirwa migumisiro nevatengi basa iri dzinonakidza nokufembera. Izvi achaponesa simba rako uye kuchengetedza mari inogona kupedzerwa kutenga zvinhu zvinokosha chaizvoizvo.

Tinoshandisa kumwewo umboo kuti paumbwe imwe PivotTable

Inguva kufunga kunzwisisa Excel zvidzidzo. Tafura pfupiso inogona akasikwa pachishandiswa magwaro dzave rakapepetwa vamwe vanoshandisa.

Izvi zvichatibatsira kuita "Kubatanidza". chirongwa ichi kunoita kuti vapiwa nokubatanidza mashoko gwaro uye anodiwa kero, zita kana mumwe ruzivo zvakakodzera kusika munhu zvichienderana Zvaifungidzirwa kuti iyi nyaya uye kunodiwa.

Kubatanidza basa iri kwapera shanduro "hofisi" haana kuchinja zvikuru zvichienzaniswa Office 2003. Asi Database queries kusarudza mashoko panobva kunogona kuitwa nokushandiswa Microsoft Query. Kuti mashoko pfupiso Excel spreadsheet, unofanira kuva zvinhu zviviri:

• The huru gwaro rine mashoko aunoda, uyewo munda ichiratidza Excel chirongwa, humboo rakapinzwa racho. The chikuru tafura riri mwero gwaro, spreadsheet, asi iwe unofanira zvakanyatsonanga mune nhaurirano batanidza (Mail batanidza Munyaradzi), chii chinofanira kushandiswa zvikuru.
• The date bhuku rine mashoko anodiwa kwegadziriro kushurudzira. Unogona kusika tafura idzva nenzira tava akanongedza kumusoro.

Kana muitiro pakasikwa pfupiso magwaro, chirongwa pave imwe kopi nerimwe zvinyorwa zviri date panobva faira. mafaira izvi zvinogona kuratidzwa yoga Chikamu gwaro idzva iri, kana kushandiswa senzira partition yenyu tafura pivot.

Mumwe ane chete tinya munda akakumbira rwako rworuboshwe mbeva bhatani, uye iwe unogona kuona panobva Data panzvimbo munda mazita anowanika huru gwaro. Fields anogona rakapinzwa chero nzvimbo iri gwaro.

kushandiswa nezvipoka

Uyewo unogona kushandisa BVUNZA chikamu, izvo zvinoreva kuti "vatsvake" uye FILLIN kuti zvinorevei 'vazadze "rakatevera uye NEXTIF, mumwe. Unogona zvakare nomuimbirwo iri parameters ari for pfungwa kuti uratidzwe wokupedzisira tafura. Kuwana aida kupinda zvinogoneka nokushandisa chaiyo Sefa kana nokungotaura reumwe ari data, sezvatakagamuchira akati apa.

Paunenge wagadzirira kusika munhu tenzi gwaro, batanidza bhokisi nhaurirano rinobvumira inodzidzisa kuziva uye kugadzirisa zvimwe zvikanganiso vasati zvichiitika. Kana zvose zviri kuongororwa, unogona zvakachengeteka achizviendesa bhatani "Kubatanidza". Kana Zvisinei, kwemapazi yaifanira, kunze vachishandisa e-mail, iwe uchava chakaita yokugadza tsamba chirongwa (kana usina akatogadza).

Kana paine chikanganiso, zviri nedanho batanidzo hurukuro pamusoro munda zvisiri raanotaura imwe gwaro, kana kuti kushandisa mashoko ari imwe unsupported format. Naizvozvo, nzira iyi inofanira zvakare kudzokororwa.

Kazhinji, Excel (basa pamwe PivotTables mu zvatakaona) inobvumira kwete chete vashoma kunzwisisa kuongorora ichitenga simba, asiwo kuti pave nokuda Zvaifungidzirwa pavo.