Kugwira ntchito ndi PivotTables mu Microsoft Excel

Masamba ozungulira ndi chida champhamvu kwambiri mu Excel. Amakulolani kuti mufufuze ndi kufotokozera mwachidule zidule zosiyanasiyana za deta yambiri ndikungodina pang'ono mbewa. M'nkhaniyi, tidziwa bwino matebulo a pivot, kumvetsetsa zomwe ali, kuphunzira kupanga ndikusintha mwamakonda awo.

Nkhaniyi inalembedwa pogwiritsa ntchito Excel 2010. Lingaliro la PivotTables silinasinthe kwambiri pazaka zambiri, koma momwe mumapangidwira ndizosiyana pang'ono mumtundu uliwonse watsopano wa Excel. Ngati muli ndi mtundu wa Excel osati 2010, ndiye konzekerani kuti zithunzi zomwe zili m'nkhaniyi zidzasiyana ndi zomwe mukuwona pazenera lanu.

  • Zakale za mbiriyakale
  • Kodi ma pivot tables ndi chiyani?
  • Momwe mungapangire tebulo la pivot?
  • Kukhazikitsa kwa PivotTable
  • Kusintha kwa Table ya Pivot
  • Zokonda zina za tebulo la pivot
  • Kutsiliza

Zakale za mbiriyakale

M'masiku oyambirira a pulogalamu ya spreadsheet, mpira wa Lotus 1-2-3. Kulamulira kwake kunali kokwanira kotero kuti zoyesayesa za Microsoft zopanga mapulogalamu ake (Excel) m'malo mwa Lotus zidawoneka ngati kuwononga nthawi. Tsopano dikirani mpaka 2010! Excel imayang'anira ma spreadsheets kuposa ma code a Lotus omwe adachitapo m'mbiri yake, ndipo kuchuluka kwa anthu omwe amagwiritsabe ntchito Lotus kuli pafupi ndi ziro. Kodi zimenezi zingatheke bwanji? Kodi n'chiyani chinachititsa kuti zinthu zisinthe mochititsa chidwi chonchi?

Openda amapeza zinthu ziwiri zazikulu:

  • Choyamba, Lotus adaganiza kuti nsanja yatsopanoyi ya GUI yotchedwa Windows inali chabe fashoni yomwe sichitha nthawi yayitali. Iwo anakana kupanga mtundu wa Windows wa Lotus 1-2-3 (koma kwa zaka zochepa chabe), kuneneratu kuti mtundu wa DOS wa mapulogalamu awo udzakhala onse ogula omwe angafune. Microsoft idapanga Excel mwachilengedwe makamaka ya Windows.
  • Chachiwiri, Microsoft idayambitsa chida mu Excel chotchedwa PivotTables chomwe sichinalipo mu Lotus 1-2-3. PivotTables, ku Excel yokha, idakhala yothandiza kwambiri kotero kuti anthu amakonda kumamatira ndi pulogalamu yatsopano ya Excel m'malo mopitiliza ndi Lotus 1-2-3, yomwe inalibe.

PivotTables, komanso kupeputsa kupambana kwa Windows nthawi zambiri, adasewera ulendo wakufa kwa Lotus 1-2-3 ndikuyambitsa kupambana kwa Microsoft Excel.

Kodi ma pivot tables ndi chiyani?

Ndiye, njira yabwino yodziwira zomwe PivotTables ndi iti?

M'mawu osavuta, ma pivot tables ndi chidule cha data ina, yopangidwa kuti ithandizire kusanthula detayi. Mosiyana ndi ziwopsezo zopangidwa pamanja, Excel PivotTables ndi yolumikizana. Akapangidwa, mutha kusintha mosavuta ngati sapereka chithunzi chomwe mumayembekezera. Ndi kungodina pang'ono mbewa, ziwopsezo zitha kutembenuzidwa kuti mitu yazazambiri ikhale mitu yamizere ndi mosemphanitsa. Mutha kuchita zambiri ndi ma pivot tables. M'malo moyesera kufotokoza m'mawu mawonekedwe onse a pivot tables, ndizosavuta kuziwonetsa mwakuchita ...

Zomwe mumasanthula ndi PivotTables sizingakhale mwachisawawa. Iyenera kukhala yaiwisi deta, monga mndandanda wa mtundu wina. Mwachitsanzo, ikhoza kukhala mndandanda wazogulitsa zomwe kampaniyo idagulitsa m'miyezi isanu ndi umodzi yapitayo.

Yang'anani zomwe zikuwonetsedwa pachithunzichi:

Dziwani kuti iyi si data yaiwisi, chifukwa yafotokozedwa kale mwachidule. Mu cell B3 tikuwona $ 30000, zomwe mwina ndizotsatira zonse zomwe James Cook anachita mu Januwale. Kodi deta yoyambirira ili kuti? Kodi $30000 yachokera kuti? Kodi mndandanda woyambilira wa malonda omwe chiwonkhetso cha mwezi uno chinachokera kuti? Zikuwonekeratu kuti wina wachita ntchito yabwino yokonzekera ndikukonza deta yonse yogulitsa kwa miyezi isanu ndi umodzi yapitayi ndikuisintha kukhala tebulo lazinthu zonse zomwe tikuwona. Mukuganiza kuti zidatenga nthawi yayitali bwanji? Ola? XNUMX koloko?

Chowonadi ndi chakuti tebulo pamwambapa si tebulo la pivot. Zinapangidwa ndi manja kuchokera ku data yaiwisi yosungidwa kwina ndipo zinatenga maola angapo kuti zitheke. Tebulo lachidule lotere litha kupangidwa pogwiritsa ntchito ma pivot table mumasekondi pang'ono. Tiyeni tiwone momwe…

Ngati tibwereranso kumndandanda woyambira wogulitsa, zitha kuwoneka motere:

Kugwira ntchito ndi PivotTables mu Microsoft Excel

Mutha kudabwa kuti kuchokera pamndandanda wamalonda mothandizidwa ndi matebulo a pivot ndipo mumasekondi pang'ono, titha kupanga lipoti la mwezi uliwonse ku Excel, lomwe tasanthula pamwambapa. Inde, tikhoza kuchita zimenezo ndi zina zambiri!

Momwe mungapangire tebulo la pivot?

Choyamba, onetsetsani kuti muli ndi gwero la data mu Excel sheet. Mndandanda wa zochitika zachuma ndizomwe zimachitika kwambiri. M'malo mwake, ukhoza kukhala mndandanda wa chilichonse: zidziwitso za ogwira ntchito, zosonkhanitsira ma CD, kapena zomwe kampani yanu imagwiritsa ntchito mafuta.

Chifukwa chake, timayamba Excel ... ndikuyika mndandanda wotere ...

Kugwira ntchito ndi PivotTables mu Microsoft Excel

Titatsegula mndandandawu mu Excel, titha kuyamba kupanga tebulo la pivot.

Sankhani selo lililonse pamndandandawu:

Kugwira ntchito ndi PivotTables mu Microsoft Excel

Ndiye pa tabu Kuika (Ikani) sankhani lamulo Chizindikiro (Pivot table):

Kugwira ntchito ndi PivotTables mu Microsoft Excel

A dialog box adzaoneka Pangani PivotTable (Kupanga pivot table) ndi mafunso awiri kwa inu:

  • Ndi data yanji yoti mugwiritse ntchito kupanga pivot table yatsopano?
  • Poyika tebulo la pivot?

Popeza mu sitepe yapitayi tasankha kale imodzi mwamaselo a mndandanda, mndandanda wonse udzasankhidwa kuti upange pivot table. Dziwani kuti titha kusankha mitundu yosiyanasiyana, tebulo losiyana, komanso gwero lakunja la data monga Access kapena MS-SQL database table. Kuphatikiza apo, tiyenera kusankha komwe tingayike tebulo latsopano la pivot: pa pepala latsopano kapena pa imodzi mwazomwe zilipo. Mu chitsanzo ichi, tidzasankha njira - Tsamba Latsopano la Ntchito (ku pepala latsopano):

Kugwira ntchito ndi PivotTables mu Microsoft Excel

Excel ipanga pepala latsopano ndikuyika tebulo lopanda kanthu la pivot pamenepo:

Kugwira ntchito ndi PivotTables mu Microsoft Excel

Tikangodina pa selo iliyonse pa tebulo la pivot, bokosi lina la zokambirana lidzawoneka: PivotTable Field List (Magawo a tebulo la Pivot).

Kugwira ntchito ndi PivotTables mu Microsoft Excel

Mndandanda wa minda yomwe ili pamwamba pa bokosi la zokambirana ndi mndandanda wa maudindo onse kuchokera pamndandanda woyambirira. Madera anayi opanda kanthu omwe ali pansi pazenera amakulolani kuti muwuze PivotTable momwe mukufuna kufotokozera mwachidule deta. Malingana ngati maderawa alibe kanthu, palibe chilichonse patebulopo. Zomwe tiyenera kuchita ndikukoka mitu kuchokera kumtunda kupita kumalo opanda kanthu pansipa. Panthawi imodzimodziyo, tebulo la pivot limapangidwa mokhazikika, motsatira malangizo athu. Ngati talakwitsa, tikhoza kuchotsa mitu ya m’munsi kapena kukokera ina kuti ilowe m’malo mwake.

Area Makhalidwe (Tanthauzo) mwina ndilofunika kwambiri mwa anayiwo. Mutu uti womwe wayikidwa m'derali umatsimikizira zomwe deta idzafotokozedwe mwachidule (chiwerengero, chiwerengero, chokwanira, chochepa, ndi zina zotero.) Izi ndi pafupifupi nthawi zonse ziwerengero. Munthu wabwino kwambiri pa malo mderali ndi zomwe zili pamutuwu kuchuluka (Mtengo) wa tebulo lathu loyambirira. Kokani mutuwu kuderali Makhalidwe (Value):

Kugwira ntchito ndi PivotTables mu Microsoft Excel

Chonde dziwani kuti mutuwo kuchuluka tsopano yalembedwa ndi chizindikiro, ndi m'deralo Makhalidwe (Makhalidwe) cholowa chawonekera Kuchuluka kwa Ndalama (Kuchuluka kwa gawo Kuchuluka), kusonyeza kuti gawoli kuchuluka mwachidule.

Ngati tiyang'ana pa tebulo la pivot palokha, tiwona kuchuluka kwazinthu zonse kuchokera pamndandanda kuchuluka tebulo loyambirira.

Kugwira ntchito ndi PivotTables mu Microsoft Excel

Chifukwa chake, tebulo lathu loyamba la pivot lapangidwa! Zosavuta, koma osati zochititsa chidwi. Mwina tikufuna kudziwa zambiri za data yathu kuposa zomwe tili nazo pano.

Tiyeni titembenuzire ku deta yoyambirira ndikuyesera kuzindikira ndime imodzi kapena zingapo zomwe zingagwiritsidwe ntchito kugawa chiŵerengerochi. Mwachitsanzo, titha kupanga tebulo lathu la pivot m'njira yoti kuchuluka kwa malonda kuwerengedwera kwa wogulitsa aliyense payekhapayekha. Iwo. mizere idzawonjezedwa ku tebulo lathu la pivot ndi dzina la ogulitsa aliyense pakampani komanso kuchuluka kwake komwe amagulitsa. Kuti mukwaniritse izi, ingokokani mutuwo munthu wogulitsa (woimira malonda) kudera Zolemba Pamizere (Zingwe):

Kugwira ntchito ndi PivotTables mu Microsoft Excel

Zimakhala zosangalatsa kwambiri! PivotTable yathu ikuyamba kupanga…

Kugwira ntchito ndi PivotTables mu Microsoft Excel

Mukuona ubwino wake? Mukudina pang'ono, tinapanga tebulo lomwe likanatenga nthawi yayitali kuti lipange pamanja.

Nanga tingatani? Mwanjira ina, tebulo lathu la pivot lakonzeka. Tapanga chidule chothandiza cha deta yoyambirira. Zofunikira zomwe zalandiridwa kale! M'nkhani yonseyi, tiwona njira zina zopangira PivotTables zovuta, komanso kuphunzira momwe mungasinthire makonda.

Kukhazikitsa kwa PivotTable

Choyamba, tikhoza kupanga tebulo la pivot la mbali ziwiri. Tiyeni tichite izi pogwiritsa ntchito mutu wagawo Njira yolipirira (Njira yolipirira). Ingokokani mutuwo Njira yolipirira ku dera Zolemba Pazenera (Zigawo):

Kugwira ntchito ndi PivotTables mu Microsoft Excel

Timapeza zotsatira:

Kugwira ntchito ndi PivotTables mu Microsoft Excel

Zikuwoneka zabwino kwambiri!

Tsopano tiyeni tipange tebulo la magawo atatu. Kodi tebulo loterolo limawoneka bwanji? Tiyeni tiwone…

Kokani Mutu phukusi (Zovuta) kuderali lipoti zosefera (Zosefera):

Kugwira ntchito ndi PivotTables mu Microsoft Excel

Dziwani komwe ali…

Kugwira ntchito ndi PivotTables mu Microsoft Excel

Izi zimatipatsa mwayi wosefa lipoti pamaziko a "Njira ya tchuthi yomwe idalipidwa." Mwachitsanzo, titha kuwona kuwonongeka kwa ogulitsa ndi njira zolipirira zovuta zonse, kapena kungodina pang'ono mbewa, sinthani mawonekedwe a tebulo la pivot ndikuwonetsa kusweka komweko kwa omwe adayitanitsa zovutazo. Ofufuza dzuwa.

Kugwira ntchito ndi PivotTables mu Microsoft Excel

Chifukwa chake, ngati mukumvetsetsa izi molondola, ndiye kuti tebulo lathu la pivot limatha kutchedwa lachitatu-dimensional. Tiyeni tipitilize kukonza…

Ngati mwadzidzidzi ziwoneka kuti kulipira kokha ndi cheke ndi kirediti kadi (ndiko kuti, kulipira kopanda ndalama) kuyenera kuwonetsedwa patebulo la pivot, ndiye titha kuzimitsa chiwonetsero chamutuwo. Cash (Ndalama). Kwa izi, pafupi ndi Zolemba Pazenera dinani muvi wapansi ndikuchotsani cholembera mu menyu yotsitsa Cash:

Kugwira ntchito ndi PivotTables mu Microsoft Excel

Tiyeni tiwone momwe tebulo lathu la pivot likuwonekera tsopano. Monga mukuonera, ndime Cash anazimiririka kwa iye.

Kugwira ntchito ndi PivotTables mu Microsoft Excel

Kupanga PivotTables mu Excel

PivotTables mwachiwonekere ndi chida champhamvu kwambiri, koma mpaka pano zotsatira zake zimawoneka zomveka komanso zosasangalatsa. Mwachitsanzo, manambala omwe timaphatikiza samawoneka ngati ndalama za dollar - ndi manambala chabe. Tiyeni tikonze izi.

Ndiko kuyesa kuchita zomwe mumazolowera muzochitika zotere ndikungosankha tebulo lonse (kapena pepala lonse) ndikugwiritsa ntchito mabatani ojambulira manambala pazida kuti muyike mtundu womwe mukufuna. Vuto ndi njira iyi ndikuti ngati mutasintha mawonekedwe a tebulo la pivot mtsogolomo (zomwe zimachitika ndi mwayi wa 99%), masanjidwewo adzatayika. Chomwe timafunikira ndi njira yopangira (pafupifupi) yokhazikika.

Choyamba, tiyeni tipeze cholowa Kuchuluka kwa Ndalama in Makhalidwe (Makhalidwe) ndikudina pa izo. Mu menyu omwe akuwoneka, sankhani chinthucho Zokonda Zamtengo Wapatali (Zosankha zamtengo wapatali):

Kugwira ntchito ndi PivotTables mu Microsoft Excel

A dialog box adzaoneka Zokonda Zamtengo Wapatali (Value field options).

Kugwira ntchito ndi PivotTables mu Microsoft Excel

atolankhani Mtundu wa Nambala (Nambala Format), bokosi la zokambirana lidzatsegulidwa. Sakani Maselo (mtundu wa cell):

Kugwira ntchito ndi PivotTables mu Microsoft Excel

Kuchokera pamndandanda Category (Nambala akamagwiritsa) sankhani akawunti (Zandalama) ndikuyika chiwerengero cha malo kukhala ziro. Tsopano kanikizani kangapo OKkuti tibwerere ku pivot table yathu.

Kugwira ntchito ndi PivotTables mu Microsoft Excel

Monga mukuonera, manambala amapangidwa ngati ndalama za dollar.

Tili pa izi ndi masanjidwe, tiyeni tiyike mawonekedwe a PivotTable yonse. Pali njira zingapo zochitira izi. Timagwiritsa ntchito yomwe ili yosavuta ...

Dinani Zida za PivotTable: Kupanga (Kugwira ntchito ndi PivotTables: Constructor):

Kugwira ntchito ndi PivotTables mu Microsoft Excel

Kenako, onjezerani menyu podina muvi womwe uli kumunsi kumanja kwa gawolo Mitundu ya PivotTable (PivotTable Styles) kuti muwone mndandanda wamitundu yambiri:

Kugwira ntchito ndi PivotTables mu Microsoft Excel

Sankhani masitayelo aliwonse oyenera ndikuyang'ana zotsatira pa tebulo lanu la pivot:

Kugwira ntchito ndi PivotTables mu Microsoft Excel

Zokonda Zina za PivotTable mu Excel

Nthawi zina muyenera kusefa deta potengera masiku. Mwachitsanzo, pamndandanda wathu wamalonda pali zambiri, zambiri masiku. Excel imapereka chida chophatikiza deta tsiku, mwezi, chaka, ndi zina zotero. Tiyeni tiwone momwe zimachitikira.

Choyamba chotsani cholowa. Njira yolipirira kuchokera kudera Zolemba Pazenera (Zigawo). Kuti muchite izi, kokerani ku mndandanda wa maudindo, ndipo m'malo mwake, sunthani mutuwo Tsiku Losungitsa (tsiku losungitsa):

Kugwira ntchito ndi PivotTables mu Microsoft Excel

Monga mukuwonera, izi zidapangitsa tebulo lathu la pivot kukhala lopanda ntchito kwakanthawi. Excel idapanga gawo lapadera la tsiku lililonse lomwe malonda adapangidwa. Zotsatira zake, tapeza tebulo lalikulu kwambiri!

Kugwira ntchito ndi PivotTables mu Microsoft Excel

Kuti mukonze izi, dinani kumanja pa deti lililonse ndikusankha kuchokera ku menyu yankhaniyo GULANI (Gulu):

Kugwira ntchito ndi PivotTables mu Microsoft Excel

Bokosi la zokambirana lamagulu lidzawoneka. Timasankha Miyezi (Miyezi) ndikudina OK:

Kugwira ntchito ndi PivotTables mu Microsoft Excel

Voila! Gome ili ndilothandiza kwambiri:

Kugwira ntchito ndi PivotTables mu Microsoft Excel

Mwa njira, tebulo ili ndilofanana ndi lomwe lasonyezedwa kumayambiriro kwa nkhaniyi, pamene malonda onse adapangidwa pamanja.

Palinso mfundo ina yofunika kwambiri imene muyenera kuidziwa! Simungathe kupanga imodzi, koma magawo angapo amizere (kapena mizere):

Kugwira ntchito ndi PivotTables mu Microsoft Excel

... ndipo zikuwoneka ngati izi ...

Kugwira ntchito ndi PivotTables mu Microsoft Excel

Zomwezo zitha kuchitika ndi mitu yazazambiri (kapena zosefera).

Tiyeni tibwerere ku mawonekedwe oyambirira a tebulo ndikuwona momwe tingasonyezere maavareji m'malo mwa masamu.

Kuti muyambe, dinani Kuchuluka kwa Ndalama ndi kuchokera ku menyu omwe akuwoneka sankhani Zokonda Zamtengo Wapatali (Zosankha zamtengo wapatali):

Kugwira ntchito ndi PivotTables mu Microsoft Excel

Mndandanda Fotokozerani mwachidule gawo la mtengowo (Ntchito) mu bokosi la zokambirana Zokonda Zamtengo Wapatali (Value field options) sankhani Avereji (Avereji):

Kugwira ntchito ndi PivotTables mu Microsoft Excel

Nthawi yomweyo, tili pano, tiyeni tisinthe CustomName (Dzina lokonda) ndi Avereji ya Ndalama (Kuchuluka kwa gawo Kuchuluka) ku chinthu chachifupi. Lowani m'munda uwu china chake Av:

Kugwira ntchito ndi PivotTables mu Microsoft Excel

Press OK ndikuwona zomwe zikuchitika. Zindikirani kuti zikhalidwe zonse zasintha kuchokera ku ziwopsezo kupita ku ma avareji, ndipo mutu wa tebulo (m'chipinda chakumanzere chakumanzere) wasintha kukhala Av:

Kugwira ntchito ndi PivotTables mu Microsoft Excel

Ngati mukufuna, mutha kupeza nthawi yomweyo kuchuluka, pafupifupi ndi nambala (zogulitsa) zoyikidwa patebulo limodzi la pivot.

Nayi chitsogozo cham'mene mungachitire izi, kuyambira ndi tebulo lopanda kanthu la pivot:

  1. Kokani Mutu munthu wogulitsa (woimira malonda) kudera Zolemba Pazenera (Zigawo).
  2. Kokani mutuwo katatu kuchuluka (Cost) kudera Makhalidwe (Makhalidwe).
  3. Kwa munda woyamba kuchuluka sinthani mutu kuti Total (Kuchuluka), ndi mtundu wa nambala mu gawo ili akawunti (Ndalama). Chiwerengero cha malo a decimal ndi ziro.
  4. Gawo lachiwiri kuchuluka dzina Averejie, ikani ntchito yake Avereji (Average) ndi mtundu wa nambala mu gawoli amasinthanso kukhala akawunti (Ndalama) yokhala ndi ziro decimal.
  5. Kwa gawo lachitatu kuchuluka khazikitsani mutu Chiwerengero ndi opaleshoni kwa iye - Chiwerengero (Kuchuluka)
  6. Mu Zolemba Pazenera (Zigawo) zomwe zidapangidwa zokha Σ Makhalidwe (Σ Makhalidwe) - kokerani kuderali Zolemba Pamizere (Mizere)

Nazi zomwe timaliza nazo:

Kugwira ntchito ndi PivotTables mu Microsoft Excel

Chiwerengero chonse, mtengo wapakati ndi kuchuluka kwa malonda - zonse mu tebulo limodzi la pivot!

Kutsiliza

Ma tebulo a Pivot mu Microsoft Excel ali ndi zambiri komanso zosintha. M'nkhani yaing'ono yotere, iwo sali pafupi kuphimba onse. Zingatenge kabuku kakang'ono kapena tsamba lalikulu kuti lifotokoze zonse zomwe zingatheke pa pivot tables. Owerenga molimba mtima komanso mwachidwi akhoza kupitiriza kufufuza ma pivot tables. Kuti muchite izi, ingodinani kumanja pafupifupi chilichonse patebulo la pivot ndikuwona ntchito ndi zosintha zomwe zatsegulidwa. Pa Riboni mupeza ma tabu awiri: Zida za PivotTable: Zosankha (analysis) ndi Design (Womanga). Osawopa kulakwitsa, mutha kufufuta PivotTable nthawi zonse ndikuyambanso. Muli ndi mwayi womwe ogwiritsa ntchito nthawi yayitali a DOS ndi Lotus 1-2-3 sanakhale nawo.

Siyani Mumakonda