Cynnwys
Tablau pivot yw un o'r arfau mwyaf pwerus yn Excel. Maent yn eich galluogi i ddadansoddi a chrynhoi amrywiol grynodebau o symiau mawr o ddata gyda dim ond ychydig o gliciau llygoden. Yn yr erthygl hon, byddwn yn dod yn gyfarwydd â thablau colyn, yn deall beth ydyn nhw, yn dysgu sut i'w creu a'u haddasu.
Ysgrifennwyd yr erthygl hon gan ddefnyddio Excel 2010. Nid yw cysyniad PivotTables wedi newid llawer dros y blynyddoedd, ond mae'r ffordd rydych chi'n eu creu ychydig yn wahanol ym mhob fersiwn newydd o Excel. Os oes gennych fersiwn o Excel nid 2010, byddwch yn barod y bydd y sgrinluniau yn yr erthygl hon yn wahanol i'r hyn a welwch ar eich sgrin.
Tipyn o hanes
Yn nyddiau cynnar meddalwedd taenlen, pêl rheol Lotus 1-2-3. Roedd ei oruchafiaeth mor gyflawn fel bod ymdrechion Microsoft i ddatblygu ei feddalwedd ei hun (Excel) fel dewis arall yn lle Lotus yn ymddangos fel gwastraff amser. Nawr yn gyflym ymlaen at 2010! Mae Excel yn dominyddu taenlenni yn fwy nag y mae cod Lotus erioed wedi'i wneud yn ei hanes, ac mae nifer y bobl sy'n dal i ddefnyddio Lotus yn agos at sero. Sut gallai hyn ddigwydd? Beth oedd y rheswm am droad mor ddramatig o ddigwyddiadau?
Mae dadansoddwyr yn nodi dau brif ffactor:
- Yn gyntaf, penderfynodd Lotus fod y platfform GUI newfangled hwn o'r enw Windows yn ddim ond chwiw pasio na fyddai'n para'n hir. Gwrthodasant adeiladu fersiwn Windows o Lotus 1-2-3 (ond dim ond am ychydig flynyddoedd), gan ragweld y byddai'r fersiwn DOS o'u meddalwedd yn holl ddefnyddwyr erioed. Datblygodd Microsoft Excel yn benodol ar gyfer Windows yn naturiol.
- Yn ail, cyflwynodd Microsoft offeryn yn Excel o'r enw PivotTables nad oedd ar gael yn Lotus 1-2-3. Profodd PivotTables, sy'n unigryw i Excel, i fod mor hynod ddefnyddiol nes bod pobl yn tueddu i gadw at y gyfres feddalwedd Excel newydd yn hytrach na pharhau â Lotus 1-2-3, nad oedd ganddynt hwy.
Chwaraeodd PivotTables, ynghyd â thanamcangyfrif llwyddiant Windows yn gyffredinol, yr orymdaith farwolaeth ar gyfer Lotus 1-2-3 a chyflwynodd lwyddiant Microsoft Excel.
Beth yw tablau colyn?
Felly, beth yw'r ffordd orau o ddisgrifio beth yw PivotTables?
Yn syml, mae tablau colyn yn grynodebau o rywfaint o ddata, a grëwyd i hwyluso dadansoddi'r data hwn. Yn wahanol i gyfansymiau a grëwyd â llaw, mae Excel PivotTables yn rhyngweithiol. Ar ôl eu creu, gallwch chi eu haddasu'n hawdd os nad ydyn nhw'n rhoi'r llun roeddech chi'n gobeithio amdano. Gydag ychydig o gliciau llygoden yn unig, gellir troi cyfansymiau fel bod penawdau colofnau yn dod yn benawdau rhes ac i'r gwrthwyneb. Gallwch chi wneud llawer o bethau gyda thablau colyn. Yn lle ceisio disgrifio mewn geiriau holl nodweddion tablau colyn, mae'n haws ei ddangos yn ymarferol ...
Ni all y data rydych chi'n ei ddadansoddi gyda PivotTables fod ar hap. Dylai fod yn ddata crai amrwd, fel rhestr o ryw fath. Er enghraifft, gallai fod yn rhestr o werthiannau a wnaed gan y cwmni yn ystod y chwe mis diwethaf.
Edrychwch ar y data a ddangosir yn y ffigur isod:
Sylwch nad yw hwn yn ddata crai amrwd, gan ei fod eisoes wedi'i grynhoi. Yng nghell B3 gwelwn $30000, sef cyfanswm y canlyniad a wnaeth James Cook ym mis Ionawr fwy na thebyg. Ble mae'r data gwreiddiol felly? O ble daeth y ffigwr $30000? O ble mae'r rhestr wreiddiol o werthiannau y deilliodd y cyfanswm misol hwn ohoni? Mae’n amlwg bod rhywun wedi gwneud gwaith gwych o drefnu a didoli’r holl ddata gwerthiant am y chwe mis diwethaf a’i droi’n dabl o gyfansymiau a welwn. Faint o amser gymerodd hi yn eich barn chi? Awr? Deg o'r gloch?
Y ffaith yw nad yw'r tabl uchod yn dabl colyn. Cafodd ei grefftio â llaw o ddata crai a storiwyd mewn mannau eraill a chymerodd o leiaf ychydig oriau i'w brosesu. Gellir creu tabl crynhoi o'r fath gan ddefnyddio tablau colyn mewn ychydig eiliadau yn unig. Gadewch i ni ddarganfod sut…
Os awn yn ôl i'r rhestr werthu wreiddiol, byddai'n edrych yn debyg i hyn:
Efallai y byddwch chi'n synnu, o'r rhestr hon o grefftau gyda chymorth tablau colyn ac mewn ychydig eiliadau yn unig, y gallwn greu adroddiad gwerthu misol yn Excel, a ddadansoddwyd gennym uchod. Ydym, gallwn wneud hynny a mwy!
Sut i greu bwrdd colyn?
Yn gyntaf, gwnewch yn siŵr bod gennych rywfaint o ddata ffynhonnell mewn taflen Excel. Y rhestr o drafodion ariannol yw'r mwyaf nodweddiadol sy'n digwydd. Mewn gwirionedd, gallai fod yn rhestr o unrhyw beth: manylion cyswllt gweithwyr, casgliad CD, neu ddata defnydd tanwydd eich cwmni.
Felly, rydyn ni'n cychwyn Excel ... ac yn llwytho rhestr o'r fath ...
Ar ôl i ni agor y rhestr hon yn Excel, gallwn ddechrau creu tabl colyn.
Dewiswch unrhyw gell o'r rhestr hon:
Yna ar y tab mewnosod (Mewnosod) dewiswch gorchymyn PivotTable (tabl colyn):
Bydd blwch deialog yn ymddangos Creu PivotTable (Creu tabl colyn) gyda dau gwestiwn i chi:
- Pa ddata i'w ddefnyddio i greu tabl colyn newydd?
- Ble i roi'r bwrdd colyn?
Gan ein bod yn y cam blaenorol eisoes wedi dewis un o gelloedd y rhestr, bydd y rhestr gyfan yn cael ei dewis yn awtomatig i greu tabl colyn. Sylwch y gallwn ddewis ystod wahanol, tabl gwahanol, a hyd yn oed rhywfaint o ffynhonnell ddata allanol fel tabl cronfa ddata Access neu MS-SQL. Yn ogystal, mae angen inni ddewis ble i osod y bwrdd colyn newydd: ar ddalen newydd neu ar un o'r rhai presennol. Yn yr enghraifft hon, byddwn yn dewis yr opsiwn - Taflen Waith Newydd (i ddalen newydd):
Bydd Excel yn creu dalen newydd ac yn gosod bwrdd colyn gwag arni:
Cyn gynted ag y byddwn yn clicio ar unrhyw gell yn y tabl colyn, bydd blwch deialog arall yn ymddangos: Rhestr Maes PivotTable (Meysydd tabl colyn).
Mae'r rhestr o feysydd ar frig y blwch deialog yn rhestr o'r holl deitlau o'r rhestr wreiddiol. Mae'r pedair ardal wag ar waelod y sgrin yn caniatáu ichi ddweud wrth y PivotTable sut rydych chi am grynhoi'r data. Cyn belled â bod yr ardaloedd hyn yn wag, nid oes dim yn y tabl ychwaith. Y cyfan sy'n rhaid i ni ei wneud yw llusgo'r penawdau o'r ardal uchaf i'r mannau gwag isod. Ar yr un pryd, mae tabl colyn yn cael ei gynhyrchu'n awtomatig, yn unol â'n cyfarwyddiadau. Os byddwn yn gwneud camgymeriad, gallwn dynnu'r penawdau o'r ardal waelod neu lusgo eraill i'w disodli.
Ardal Gwerthoedd (Meanings) mae'n debyg yw'r pwysicaf o'r pedwar. Pa bennawd a roddir yn y maes hwn sy'n pennu pa ddata fydd yn cael ei grynhoi (swm, cyfartaledd, uchafswm, lleiafswm, ac ati) Mae'r rhain bron bob amser yn werthoedd rhifiadol. Ymgeisydd rhagorol am le yn y maes hwn yw'r data o dan y pennawd swm (Cost) ein tabl gwreiddiol. Llusgwch y teitl hwn i'r ardal Gwerthoedd (Gwerthoedd):
Sylwch fod y teitl swm bellach wedi'i farcio â marc siec, ac yn yr ardal Gwerthoedd (Gwerthoedd) cofnod wedi ymddangos Swm Swm (Swm maes Swm), gan nodi bod y golofn swm crynhoi.
Os edrychwn ar y tabl colyn ei hun, fe welwn swm yr holl werthoedd o'r golofn swm tabl gwreiddiol.
Felly, mae ein tabl colyn cyntaf yn cael ei greu! Cyfleus, ond nid yn arbennig o drawiadol. Mae'n debyg ein bod am gael mwy o wybodaeth am ein data nag sydd gennym ar hyn o bryd.
Gadewch i ni droi at y data gwreiddiol a cheisio nodi un neu fwy o golofnau y gellir eu defnyddio i rannu'r swm hwn. Er enghraifft, gallwn ffurfio ein tabl colyn yn y fath fodd fel bod cyfanswm y gwerthiannau yn cael ei gyfrifo ar gyfer pob gwerthwr yn unigol. Y rhai. bydd rhesi'n cael eu hychwanegu at ein tabl colyn gydag enw pob gwerthwr yn y cwmni a chyfanswm eu gwerthiant. I gyflawni'r canlyniad hwn, llusgwch y teitl gwerthwr (cynrychiolydd gwerthu) i'r rhanbarth Labeli Row (Llinynnau):
Mae'n dod yn fwy diddorol! Mae ein PivotTable yn dechrau cymryd siâp…
Gweld y manteision? Mewn cwpl o gliciau, fe wnaethon ni greu tabl a fyddai wedi cymryd amser hir iawn i'w greu â llaw.
Beth arall allwn ni ei wneud? Wel, ar ryw ystyr, mae ein bwrdd colyn ni yn barod. Rydym wedi creu crynodeb defnyddiol o'r data gwreiddiol. Gwybodaeth bwysig wedi dod i law yn barod! Yng ngweddill yr erthygl hon, byddwn yn edrych ar rai ffyrdd o greu PivotTables mwy cymhleth, yn ogystal â dysgu sut i'w haddasu.
Gosod PivotTable
Yn gyntaf, gallwn greu tabl colyn dau ddimensiwn. Gadewch i ni wneud hyn gan ddefnyddio pennawd y golofn Dull talu (Dull talu). Llusgwch y teitl Dull talu i'r ardal Labeli Colofn (Colofnau):
Cawn y canlyniad:
Edrych yn cwl iawn!
Nawr, gadewch i ni wneud tabl tri dimensiwn. Sut olwg fyddai ar fwrdd o'r fath? Gawn ni weld…
Llusgwch Pennawd pecyn (Cymhleth) i'r ardal hidlwyr adroddiadau (Hidlyddion):
Sylwch ble mae e…
Mae hyn yn rhoi’r cyfle i ni hidlo’r adroddiad ar sail “Pa gyfadeilad gwyliau y talwyd amdano.” Er enghraifft, gallwn weld dadansoddiad yn ôl gwerthwyr a thrwy ddulliau talu ar gyfer pob cyfadeilad, neu mewn cwpl o gliciau llygoden, newid golwg y tabl colyn a dangos yr un dadansoddiad yn unig ar gyfer y rhai a archebodd y cyfadeilad Chwilwyr haul.
Felly, os ydych chi'n deall hyn yn gywir, yna gellir galw ein tabl colyn yn dri dimensiwn. Gadewch i ni barhau i sefydlu…
Os daw'n amlwg yn sydyn mai dim ond taliad gyda siec a cherdyn credyd (hynny yw, taliad heb arian) y dylid ei arddangos yn y tabl colyn, yna gallwn ddiffodd arddangosiad y teitl arian (Arian parod). Am hyn, nesaf at Labeli Colofn cliciwch ar y saeth i lawr a dad-diciwch y blwch yn y gwymplen arian:
Gawn ni weld sut olwg sydd ar ein bwrdd colyn ni nawr. Fel y gwelwch, y golofn arian wedi diflannu oddi wrthi.
Fformatio PivotTables yn Excel
Mae PivotTables yn amlwg yn arf pwerus iawn, ond hyd yn hyn mae'r canlyniadau'n edrych ychydig yn blaen ac yn ddiflas. Er enghraifft, nid yw'r niferoedd rydyn ni'n eu hadio yn edrych fel symiau doler - dim ond rhifau ydyn nhw. Gadewch i ni drwsio hyn.
Mae'n demtasiwn gwneud yr hyn yr ydych wedi arfer ag ef mewn sefyllfa o'r fath a dewis y tabl cyfan (neu'r ddalen gyfan) a defnyddio'r botymau fformatio rhif safonol ar y bar offer i osod y fformat a ddymunir. Y broblem gyda'r dull hwn yw, os byddwch chi byth yn newid strwythur y tabl colyn yn y dyfodol (sy'n digwydd gyda siawns o 99%), bydd y fformatio yn cael ei golli. Yr hyn sydd ei angen arnom yw ffordd i'w wneud (bron) yn barhaol.
Yn gyntaf, gadewch i ni ddod o hyd i'r cofnod Swm Swm in Gwerthoedd (Gwerthoedd) a chliciwch arno. Yn y ddewislen sy'n ymddangos, dewiswch yr eitem Gwerth Gosodiadau Maes (Dewisiadau maes gwerth):
Bydd blwch deialog yn ymddangos Gwerth Gosodiadau Maes (Dewisiadau maes gwerth).
y wasg Fformat Rhif (Fformat Rhif), bydd blwch deialog yn agor. Celloedd Fformat (fformat cell):
O'r rhestr Categori (Fformatau rhif) dewiswch Cyfrifeg (Ariannol) a gosodwch nifer y lleoedd degol i sero. Nawr pwyswch ychydig o weithiau OKi fynd yn ôl at ein bwrdd colyn.
Fel y gallwch weld, mae'r niferoedd yn cael eu fformatio fel symiau doler.
Tra ein bod ni wrthi gyda fformatio, gadewch i ni sefydlu'r fformat ar gyfer y PivotTable cyfan. Mae sawl ffordd o wneud hyn. Rydyn ni'n defnyddio'r un sy'n symlach ...
Cliciwch ar y Offer PivotTable: Dylunio (Gweithio gyda PivotTables: Adeiladwr):
Nesaf, ehangwch y ddewislen trwy glicio ar y saeth yng nghornel dde isaf yr adran Arddulliau PivotTable (Arddulliau PivotTable) i weld y casgliad helaeth o arddulliau mewnol:
Dewiswch unrhyw arddull addas ac edrychwch ar y canlyniad yn eich tabl colyn:
Gosodiadau PivotTable Eraill yn Excel
Weithiau mae angen i chi hidlo data yn ôl dyddiadau. Er enghraifft, yn ein rhestr o grefftau mae yna lawer, llawer o ddyddiadau. Mae Excel yn darparu offeryn i grwpio data fesul diwrnod, mis, blwyddyn, ac ati. Gawn ni weld sut mae'n cael ei wneud.
Tynnwch y cofnod yn gyntaf. Dull talu o'r rhanbarth Labeli Colofn (Colofnau). I wneud hyn, llusgwch ef yn ôl i'r rhestr o deitlau, ac yn ei le, symudwch y teitl Dyddiad Archebu (dyddiad archebu):
Fel y gallwch weld, roedd hyn dros dro yn gwneud ein bwrdd colyn yn ddiwerth. Creodd Excel golofn ar wahân ar gyfer pob dyddiad y gwnaed masnach. O ganlyniad, cawsom fwrdd eang iawn!
I drwsio hyn, de-gliciwch ar unrhyw ddyddiad a dewiswch o'r ddewislen cyd-destun GRWP (Grŵp):
Bydd y blwch deialog grwpio yn ymddangos. Rydym yn dewis Misoedd (Misoedd) a chliciwch OK:
Ystyr geiriau: Voila! Mae'r tabl hwn yn llawer mwy defnyddiol:
Gyda llaw, mae'r tabl hwn bron yn union yr un fath â'r un a ddangosir ar ddechrau'r erthygl, lle cafodd y cyfansymiau gwerthiant eu llunio â llaw.
Mae pwynt pwysig iawn arall y mae angen i chi ei wybod! Gallwch greu nid un, ond sawl lefel o benawdau rhes (neu golofn):
… a bydd yn edrych fel hyn…
Gellir gwneud yr un peth gyda phenawdau colofn (neu hyd yn oed hidlwyr).
Gadewch i ni ddychwelyd i ffurf wreiddiol y tabl a gweld sut i ddangos cyfartaleddau yn lle symiau.
I ddechrau, cliciwch ar Swm Swm ac o'r ddewislen sy'n ymddangos dewiswch Gwerth Gosodiadau Maes (Dewisiadau maes gwerth):
Y rhestr Crynhowch y maes gwerth erbyn (Gweithrediad) yn y blwch deialog Gwerth Gosodiadau Maes (Dewisiadau maes gwerth) dewiswch Cyfartaledd (Cyfartaledd):
Ar yr un pryd, tra ein bod ni yma, gadewch i ni newid Enw Custom (Enw personol) gyda Cyfartaledd y Swm (Maes Swm Swm) i rywbeth byrrach. Rhowch rywbeth tebyg yn y maes hwn Cyfartaledd:
Pwyswch OK a gweld beth sy'n digwydd. Sylwch fod yr holl werthoedd wedi newid o gyfansymiau i gyfartaleddau, ac mae pennawd y tabl (yn y gell chwith uchaf) wedi newid i Cyfartaledd:
Os dymunwch, gallwch gael y swm, y cyfartaledd a'r nifer (gwerthiannau) ar unwaith mewn un tabl colyn.
Dyma ganllaw cam wrth gam ar sut i wneud hyn, gan ddechrau gyda thabl colyn gwag:
- Llusgwch Pennawd gwerthwr (cynrychiolydd gwerthu) i'r rhanbarth Labeli Colofn (Colofnau).
- Llusgwch y teitl dair gwaith swm (Cost) i'r ardal Gwerthoedd (Gwerthoedd).
- Am y maes cyntaf swm newid y teitl i Cyfanswm (Swm), a'r fformat rhif yn y maes hwn yw Cyfrifeg (Ariannol). Nifer y lleoedd degol yw sero.
- Ail faes swm enw Average, gosodwch y llawdriniaeth ar ei gyfer Cyfartaledd (Cyfartaledd) a'r fformat rhif yn y maes hwn hefyd yn newid i Cyfrifeg (Ariannol) gyda dim lle degol.
- Ar gyfer y trydydd maes swm gosod teitl Cyfri a llawdriniaeth iddo - Cyfri (Swm)
- Yn y Labeli Colofn (Colofnau) maes a grëwyd yn awtomatig Σ Gwerthoedd (Σ Gwerthoedd) – llusgwch ef i'r ardal Labeli Row (Llinellau)
Dyma beth fyddwn ni'n ei wneud yn y pen draw:
Cyfanswm, gwerth cyfartalog a nifer y gwerthiannau – i gyd mewn un tabl colyn!
Casgliad
Mae tablau colyn yn Microsoft Excel yn cynnwys llawer o nodweddion a gosodiadau. Mewn erthygl mor fach, nid ydynt hyd yn oed yn agos at eu cwmpasu i gyd. Byddai'n cymryd llyfr bach neu wefan fawr i ddisgrifio'n llawn holl bosibiliadau byrddau colyn. Gall darllenwyr beiddgar a chwilfrydig barhau â'u harchwiliad o dablau colyn. I wneud hyn, de-gliciwch ar bron unrhyw elfen o'r tabl colyn a gweld pa swyddogaethau a gosodiadau sy'n agor. Ar y Rhuban fe welwch ddau dab: Offer PivotTable: Opsiynau (dadansoddiad) a dylunio (Adeiladwr). Peidiwch â bod ofn gwneud camgymeriad, gallwch chi bob amser ddileu'r PivotTable a dechrau drosodd. Mae gennych chi gyfle na chafodd defnyddwyr amser hir DOS a Lotus 1-2-3 erioed.