Çfarë është një PivotChart në Excel dhe si ta shfrytëzoni sa më shumë?

Nëse punoni shpesh me të dhëna në Excel, ndoshta në një moment të caktuar jeni ndjerë të mbingarkuar nga mijëra rreshta dhe nuk keni ditur nga t'ia filloni. Në këto raste, një grafik dinamik ose PivotChart Mund të jetë aleati juaj më i mirë për të parë të gjithë pamjen me një shikim, pa u humbur në numra. Ky lloj grafiku mbështetet nga një tabelë pivot dhe ju lejon të analizoni, filtroni dhe paraqisni informacionin shpejt dhe në mënyrë interaktive.

Para se të futemi në grafika dinamike, është e rëndësishme të jemi shumë të qartë se çfarë janë ato. tabela dinamikeDo të shpjegojmë se çfarë janë PivotChart-et dhe si t'i konfigurojmë ato, sepse një PivotChart është në thelb komponenti vizual i gjithë asaj analize. Nga aty, do të shohim se si të krijojmë një grafik dinamik nga e para, si të gjenerojmë një nga një tabelë pivot ekzistuese, si të përmbledhim dhe shfaqim vlerat, si dhe çfarë kufizimesh kanë këto grafikë gjatë krijimit të prezantimeve profesionale, së bashku me disa zgjidhje praktike.

Çfarë është një PivotChart në Excel?

Një grafik dinamik në Excel, i njohur si Grafiku PivotNjë grafik është një paraqitje grafike e të dhënave nga një tabelë pivot. Me fjalë të tjera, është një grafik i "lidhur" me një tabelë pivot që përditësohet dhe ndryshon bazuar në filtrat, fushat dhe paraqitjet që aplikoni në atë tabelë.

Këto grafikë janë plotësisht interaktiveKur zhvendosni fushat midis rreshtave, kolonave, filtrave ose vlerave në tabelën pivot, grafiku përshtatet automatikisht. Ai gjithashtu përmban butona të dedikuar për fusha dhe filtra, në mënyrë që të mund ta ndryshoni pamjen direkt nga grafiku, pa e prekur tabelën.

Ndryshe nga grafikët e rregullt në Excel, një grafik dinamik është projektuar për të eksploroni dhe analizoni të dhënatjo vetëm për dekorim. Kjo është arsyeja pse është kaq e dobishme kur punoni me vëllime të mëdha të dhënash dhe duhet të shihni modele, trende ose krahasime pa shkruar formula komplekse.

Marrëdhënia midis tabelës pivot dhe grafikut pivot

Themeli i çdo PivotChart është gjithmonë një tabela dinamikeKjo tabelë bazohet në një gamë të dhënash (ose disa tabela të lidhura) dhe ju lejon të gruponi, përmbledhni dhe riorganizoni informacionin pa modifikuar të dhënat burimore.

Në një tabelë pivot, fushat shpërndahen në katër zona kryesore: Filtra, Kolona, ​​Rreshta dhe VleraDuke zvarritur fushat në secilën prej këtyre zonave, përmbledhje të ndryshme ndërtohen mbi të njëjtin grup të dhënash, pa pasur nevojë të shkruani ndonjë formulë. Grafiku dinamik "trashëgon" të gjithë këtë strukturë.

Kur krijoni një grafik dinamik nga ajo tabelë, seri, kategori dhe legjenda Ato bazohen direkt në fushat që tabela ka në Rreshta, Kolona dhe Vlera. Nëse e ndryshoni tabelën, grafiku ndryshon. Nëse filtroni ose gruponi datat në tabelë (sipas muajve, tremujorëve, viteve, etj.), grafiku përditësohet për të treguar saktësisht atë pamje.

Çfarë është një tabelë pivot dhe pse është kaq e rëndësishme për PivotCharts?

Me një tabelë pivot mundeni ndërroni rreshtat dhe kolonat menjëherë që shfaqen në ekran, aplikojnë filtra, grupojnë vlera dhe rillogaritin totalet brenda pak sekondash. E gjithë kjo pa pasur nevojë të ndërtoni formula të gjata ose të ndërlikuara, diçka veçanërisht e dobishme për përdoruesit që shpesh shpenzojnë shumë kohë duke krijuar raporte të përsëritura.

Imagjinoni, për shembull, sikur të keni një listë shitjesh me mijëra të dhëna: datën, vendin, rajonin, qytetin, produktin, shitësin dhe shumën. Me një tabelë pivot, mund ta merrni këtë informacion brenda sekondash. shitjet sipas venditPastaj mund të shikoni shitjet sipas rajonit brenda secilit vend, ose shitjet sipas qytetit dhe produktit, thjesht duke zvarritur fushat në zona të ndryshme. Nga këto pamje, mund të krijoni grafikë dinamikë që shfaqin vizualisht të njëjtën analizë.

Përdorimet kryesore të tabelave pivot në analizën e të dhënave

Tabelat pivot përdoren kryesisht për të krijuar raporte dhe panele interaktiveku përdoruesi mund të ndryshojë filtrat dhe fushat për t'iu përgjigjur pyetjeve të ndryshme të biznesit. Nuk ka të bëjë vetëm me shtimin e të dhënave, por edhe me mundësinë për t'i parë ato shpejt nga këndvështrime të ndryshme.

Një shembull tipik është ai i raporteve periodike të shitjeve: me një tabelë të dhënash me një burim të vetëm dhe një tabelë pivot të projektuar mirë, ju mundeni Shikoni shitjet sipas vendit, produktit, shitësit ose periudhës Pa dublikuar fletë ose pa krijuar formula për secilin raport. Thjesht zhvendosni fushat dhe ndryshoni filtrat.

Këto lloje raportesh janë hapi natyror tjetër drejt krijimit grafika dinamike që lejojnë analizën vizuale të trendeve, krahasimet midis kategorive, evolucionin kohor etj. Kështu, ajo që është një grup numrash në tabelë bëhet një grafik që mund të lexohet me një shikim.

Si të krijoni një tabelë pivot hap pas hapi (bazuar në PivotChart)

1. Përgatitni ose zgjidhni burimin e të dhënave

Hapi i parë është organizimi i të dhënave në formën e rreshta dhe kolonapa rreshta bosh midis tyre dhe me tituj të qartë për secilën kolonë. Mund të jetë një tabelë Excel, një diapazon i formatuar si tabelë ose edhe të dhëna të importuara nga një bazë të dhënash.

Për shembull, mund të keni një tabelë me të dhëna shitjesh nga një dyqan kompjuterash, me fusha si numri i faturës, emri komercial, data, modeli, marka dhe shumaKjo do të jetë baza mbi të cilën tabela kryesore do të gjenerojë përmbledhjet.

2. Krijo tabelën pivot

Me çdo qelizë të zgjedhur brenda diapazonit të të dhënave, shkoni te skeda futur dhe klikoni te “PivotTable”. Excel do ta zbulojë automatikisht të gjithë diapazonin nëse të dhënat janë të strukturuara mirë.

Në kutinë e dialogut që shfaqet, mund të zgjidhni nëse dëshironi ta vendosni raportin e tabelës së pivotit në një spreadsheet i ri ose në një fletë ekzistuese. Nëse e keni shtesën. Pivot i energjisëGjithashtu mund t’i dërgoni ato të dhëna në modelin e të dhënave për t’i kombinuar ato me tabela të tjera të lidhura.

Pasi ta pranoni, Excel do të krijojë një tabelë pivot bosh dhe do ta shfaqë panelin në të djathtë. Lista e fushave, ku do të shihni të gjitha fushat nga tabela burimore gati për t'u zvarritur në zona të ndryshme.

3. Konfiguroni fushat e tabelës së pivotit

Në panelin e fushave të tabelës pivot keni katër fusha kryesore ku të vendosen fushat:

• filtra: për të aplikuar një filtër të përgjithshëm në të gjithë raportin (për shembull, filtro sipas vitit ose sipas vendit).
• Kolonat: për fushat që dëshironi t'i shihni si tituj kolonash pa përsëritje (për shembull, markat e produkteve).
• Rreshtat: për fushat që dëshironi të listoni si rreshta (për shembull, biznese, produkte ose qytete).
• Vlerat: për fushat numerike në të cilat do të aplikohen funksionet e grumbullimit (shuma, numërimi, mesatarja, etj.).

Duke zvarritur dhe lëshuar fusha në këto zona, ju i jepni formë raportit. Excel, si parazgjedhje, zbaton Funksioni SUM në fushat numerike që vendosni në Vlerat. Nëse fusha përmban tekst ose një përzierje të llojeve të të dhënave, zakonisht përdor funksionin KONTARKjo është arsyeja pse është e rëndësishme t'i mbani llojet e të dhënave të pastra në tabelën tuaj burimore.

Në varësi të kombinimit të fushave që zgjidhni, mund të merrni përmbledhje krejtësisht të ndryshme në të njëjtin burim të dhënash. Për shembull, mund ta vendosni fushën "emri i shitësit" në Rreshta, "markë" në Kolona dhe "shuma totale" në Vlera për të parë se sa ka shitur secili shitës për markë. Nëse më vonë i ndërroni shitësit dhe markën, do të merrni një raport të strukturuar në të kundërt, por me të njëjtat të dhëna.

Konfiguro "Përmbledh vlerat nga" në një tabelë pivot

Në zonën Vlerat, mund të ndryshoni mënyrën e llogaritjes së të dhënave. Duke klikuar mbi shigjeta në të djathtë të emrit të fushës së vlerës Duke zgjedhur "Cilësimet e fushës së vlerës", hapet një kuti dialogu me disa opsione.

Në skedën “Përmbledh vlerat ngaMund të zgjidhni funksionin e agregimit që dëshironi të përdorni: Shuma, Numërimi, Mesatarja, Maks., Min., Produkti, Numërimi i Numrave, Devijimi St, Var, etj. Excel do ta shtojë automatikisht emrin e funksionit në emrin e fushës në raport, për shembull, "Shuma e Sasisë", megjithëse mund ta ndryshoni atë në kutinë "Emri i Personalizuar".

Në të njëjtën kuti mund të hyni në ""Formati i numrave"Për të vendosur formatin për të gjitha vlerat në atë fushë (monedhë, përqindje, numër dhjetor, etj.). Kjo siguron që si tabela pivot ashtu edhe grafiku pivot i shoqëruar të shfaqin të dhënat në një format të qëndrueshëm dhe të lexueshëm."

Konfiguro "Shfaq vlerat si" për llogaritjet e avancuara

Përveç përmbledhjes, tabelat pivot ju lejojnë të transformoni rezultatet duke përdorur ""Shfaq vlerat siNga e njëjta dritare Cilësimet e Fushës së Vlerave, në skedën përkatëse, mund të specifikoni që vlerat të shfaqen, për shembull, si:

• % e totalit të përgjithshëm
• % e totalit të rreshtit ose kolonës
• % e një elementi të zgjedhur
• Dallimi nga një vlerë bazë
• % ndryshim nga një vlerë bazë

Kjo është veçanërisht e dobishme kur doni të shihni, për shembull, se çfarë përqindjeje përfaqëson secila kategori nga shitjet totale në vend të vetëm shumës në euro. Në rastin klasik të shpenzimeve familjare, mund t'i transformoni shumat për secilën kategori në përqindjet e totalit për të parë se çfarë pjese shkon për strehim, transport, ushqim etj.

Nëse duhet të shihni vlerën absolute dhe përqindjen në të njëjtën kohë, mundeni tërhiqni të njëjtën fushë numerike dy herë Në zonën Vlerat: në njërën, lëreni si Shumë, dhe në tjetrën, aplikoni Trego vlerat si “% e totalit të përgjithshëm”. Në këtë mënyrë, për secilin artikull, do të keni shumën dhe përqindjen përkatëse në të njëjtën tabelë pivot, dhe të dyja mund të përdoren në grafikun pivot.

Krijimi i grafikëve dinamikë (PivotCharts) në Excel

Krijoni një grafik dinamik nga një tabelë të dhënash

Nëse nuk keni ende një tabelë pivot, mund ta krijoni një të tillë. grafik dinamik direkt nga të dhënatExcel do të gjenerojë tabelën pivot prapa skenave dhe do ta shoqërojë atë me grafikun. Hapat bazë janë:

1. Zgjidhni një qelizë brenda tabelës ose diapazonit të të dhënave.
2. Shko te skeda futur dhe zgjidhni opsionin Dinamik Grafik (ose llojin e grafikut dinamik që dëshironi, varësisht nga versioni).
3. Specifikoni se ku dëshironi ta vendosni grafikun dinamik (fletë të re ose fletë ekzistuese).
4. Klikoni mbi pranoj.
5. Zgjidhni ose tërhiqni fushat që dëshironi të shfaqen në panelin e fushave të grafikut dinamik.

Paralelisht, Excel do të ketë krijuar një tabela e lidhur me pivotKjo është ajo që në fakt e ushqen grafikun. Kur e modifikoni atë tabelë (fushat, filtrat, dizajnin), grafiku do të përditësohet automatikisht pa pasur nevojë të prekni asgjë.

Krijo një grafik dinamik nga një tabelë pivot ekzistuese

Nëse tashmë keni një tabelë pivot të konfiguruar mirë, procesi është edhe më i thjeshtë. Ju duhet vetëm të:

1. Zgjidhni çdo qelizë brenda tabelës pivot.
2. Shko te skeda futur dhe klikoni në "Grafiku Dinamik".
3. Zgjidhni llojin e grafikut (kolonë, shirit, vijë, formë torte, etj.).
4. Konfirmo me pranoj.

Grafiku do të mbetet i lidhur automatikisht në atë tabelë specifike pivot. Çdo ndryshim dizajni, filtër ose prerës që aplikoni në tabelë do të pasqyrohet menjëherë në grafik. Në mënyrë të ngjashme, butonat e filtrit dhe të fushës të shfaqura në grafik ju lejojnë të bashkëveproni me të dhënat pa pasur nevojë të shkoni në tabelë.

Tabelat pivot me tabela dhe marrëdhënie të shumëfishta burimore

Excel ju lejon të ndërtoni tabela pivot nga disa tabela të lidhuraKjo është shumë e dobishme kur të dhënat tuaja janë në tabela të ndryshme të një baze të dhënash relacionale (për shembull, SQL Server, Oracle ose Access).

Hapat bazë Për të punuar me tabela të shumta në një tabelë pivot, duhet të:

Hapi 1: Importoni tabelat përkatëse
Ju importoni tabela të ndryshme nga baza e të dhënave dhe mund ta bëni këtë të gjitha menjëherë nëse zgjidhni disa. Këto tabela shtohen në modelin e të dhënave dhe mund të lidhen me njëra-tjetrën.

Hapi 2: Shtoni fusha në tabelën pivot
Në listën e fushave që tani do të shihni të gjitha tavolinat që keni importuar. Mund ta zgjeroni secilën prej tyre për të parë fushat e saj dhe t'i tërhiqni ato në zonat Rreshta, Kolona, ​​Vlera ose Filtra, edhe nëse vijnë nga tabela të ndryshme, për sa kohë që janë të lidhura saktë.

Hapi 3: Krijoni marrëdhënie nëse është e nevojshme
Nëse Excel zbulon se nevojitet një marrëdhënie midis dy tabelave për të përfunduar analizën, do të shfaqë një mesazh që tregon se duhet krijo një marrëdhënieNë atë rast, ju përcaktoni marrëdhënien midis fushave kryesore (për shembull, ID e Klientit në një tabelë shitjesh dhe ID e Klientit në një tabelë klienti) dhe, prej andej, tabela pivot do të jetë në gjendje të përdorë fushat nga të dyja tabelat pa probleme.

Kur këto tabela pivot janë të lidhura me grafika dinamikeJu mund të përfaqësoni vizualisht të dhëna nga tabela të shumta, të tilla si shitjet sipas zonës gjeografike duke kombinuar të dhënat e klientëve dhe të shitjeve, ose përmbledhje sipas produktit duke përdorur informacion nga burime të ndryshme.

Grafikët dhe segmentimet dinamike për analiza interaktive

Grafikët dinamikë funksionojnë veçanërisht mirë kur kombinohen me segmentime (Slicers) dhe shkallë kohoreSegmentimi ju lejon të filtroni vizualisht informacionin sipas fushave të tilla si vendi, kategoria e produktit, përfaqësuesi i shitjeve, etj., duke përdorur butona. Shkallët kohore përdoren për të filtruar periudhat e datave (vite, tremujorë, muaj, ditë) në një mënyrë shumë intuitive.

Kur aplikoni një prerës në tabelën pivot, përditësime dinamike të grafikëve menjëherë për të shfaqur vetëm të dhënat e filtruara. Kjo e thjeshton shumë eksplorimin e informacionit në takime, prezantime të brendshme ose analiza ad hoc, pa pasur nevojë të modifikoni formulat ose të rikrijoni grafikët çdo herë.

Kufizimet e grafikëve dinamikë të Excel për prezantime

Edhe pse PivotCharts janë të shkëlqyer si një mjet për eksplorim dhe analizëAto kanë disa kufizime kur doni t'i përdorni në prezantime formale (për shembull në PowerPoint) ose kur keni nevojë për një dizajn grafik shumë të rafinuar dhe fleksibël.

Kufizimet kryesore të grafikës dinamike përfshijnë:

• Grafiku është i fortë i lidhur me tabelën pivotNëse doni të hiqni vetëm një seri nga grafiku, duhet ta hiqni atë edhe nga tabela, gjë që mund të mos jetë e dëshirueshme për analizë.
• Paraqitja e grafikut ndjek struktura e rreshtave dhe kolonave të tabelës pivotNëse doni të ndërroni seritë dhe kategoritë, duhet të ndryshoni rreshtat dhe kolonat e tabelës, gjë që mund të dëmtojë paraqitjen e fletës ose edhe të prishë formulat e tjera.
• La grupimi i datave Ajo që është e dobishme në tabelë (p.sh., muajt ose tremujorët) mund të mos rezultojë në një grafik vizualisht të lexueshëm ose estetikisht të këndshëm.
• L formatet e numrave Ato nuk sinkronizohen gjithmonë në mënyrë të përsosur midis tabelës së pivotit dhe grafikut, duke gjeneruar mospërputhje të vogla në etiketa dhe vlera.
• Grafiku tregon butonat e fushës dhe filtrit të cilat janë shumë të dobishme për analiza, por bezdisëse kur dëshironi një grafik të pastër për një diapozitiv.

Shtuar në të gjitha këto janë të tyret kufizimet e formatit të grafikëve standardë në Excel dhe PowerPoint (etiketa të paqëndrueshme të boshtit, vështirësi në nxjerrjen në pah të pikave të caktuara, etj.), të cilat janë edhe më të dukshme kur të dhënat vijnë nga një strukturë komplekse e tabelës pivot.

Tabelat e referencës si një zgjidhje për kufizimet e PivotCharts

Një mënyrë shumë praktike për t'u marrë me këto kufizime është shkëput grafikun nga tabela e pivot-it duke përdorur një tabelë referimi të ndërmjetme. Ideja është që ta përdorni tabelën pivot vetëm për të analizuar dhe përgatitur të dhënat, dhe më pas ta kopjoni ose lidhni rezultatin me një tabelë tjetër "të sheshtë" që do të shërbejë si burim për një grafik normal.

Ekzistojnë tre forma kryesore për të krijuar këto tabela referimi:

• Kopjoni dhe ngjisni vlerat nga tabela e pivotit: Ju kopjoni rezultatet nga tabela kryesore dhe i ngjitni ato si vlera në një zonë tjetër. Kjo kap gjendjen aktuale të analizës, ideale kur të dhënat janë finalizuar dhe nuk prisni ndonjë ndryshim. Mund të vazhdoni të punoni në tabelën kryesore pa ndikuar në tabelën e referencës.
• Përdorni formula që i referohen qelizave të tabelës pivot: Në këtë mënyrë, tabela e referencës përditësohet kur të dhënat në tabelën pivot ndryshojnë (për shembull, kur burimi i të dhënave rifreskohet). Rreziku është që nëse ndryshoni strukturën e tabelës pivot, referencat e qelizave do të zhvendosen dhe do të çrregullohen.
• Përdorni funksionin GETPIVOTDATA: Ky funksion ju lejon të referoni të dhënat në tabelën pivot. sipas emrit të fushës dhe elementitPra, nëse struktura ndryshon, formulat zakonisht vazhdojnë të funksionojnë. Është më e fuqishme dhe e qëndrueshme, por edhe disi më komplekse për t'u konfiguruar.

Pasi tabela e referencës të jetë gati, mund të krijoni një grafik normal i Excel-it Bazuar në atë tabelë, me liri të plotë formatimi, pa butona filtri dhe pa ngurtësinë e një strukture të tabelës pivot. Rekomandohet që tabela pivot të jetë e përcaktuar mirë dhe analiza të jetë finalizuar para krijimit të tabelës referuese për të shmangur rikrijimin e saj shumë herë.

Mos harroni ato tabelat pivot Ato nuk përditësohen automatikisht Kur ndryshon burimi i të dhënave, duhet të rifreskoni tabelën pivot në mënyrë që tabelat dhe grafikët referues të bazuar në to të përditësohen gjithashtu.

Lidhni të dhënat e tabelës pivot dhe grafikut me PowerPoint

Kur keni një grafik të bazuar në një tabelë referimi ose direkt në një tabelë pivot dhe doni ta çoni atë në PowerPointMund të përdorni opsione të ndryshme të Zam i veçantë:

Opsionet kryesore shëndoshë:

• Objekti i grafikut në Microsoft Excel: Futni një grafik me një fletë të dhënash të integruar në PowerPoint. Mund të modifikoni si grafikun ashtu edhe të dhënat direkt nga prezantimi.
• Objekti i grafikut të Microsoft Excel i lidhur: Grafiku mban një lidhje me skedarin origjinal të Excel-it. Kur përditësoni të dhënat në Excel, përditësohet edhe grafiku në PowerPoint.
• Imazh (JPG, PNG, etj.): Krijo një pamje statike të grafikut në formatin e tij aktual, perfekte nëse të dhënat nuk do të ndryshojnë më.

Çdo opsion ka të metat e veta: grafikët e palidhur mund të bëhen të vjetëruar nëse të dhënat në Excel ndryshojnë; grafikët e lidhur mund të humbasin lidhjen e tyre nëse skedari zhvendoset ose riemërtohet; dhe imazhet nuk përditësohen nëse ka ndryshime të mëvonshme. Nëse përdorni mjete të specializuara vizualizimi si p.sh. qelizë mendimiMund të përfitoni nga lidhjet më të fuqishme midis Excel dhe PowerPoint, si për grafikët ashtu edhe për tekstin, duke mbajtur gjithmonë parasysh që tabela kryesore duhet të rifreskohet mirë.

Grafikët dinamikë të Excel-it janë një nga mjetet kryesore për transformimin e tabelave të mëdha të të dhënave në raporte të qarta, fleksibile dhe interaktiveTë kuptuarit se si funksionojnë tabelat pivot, si të përmbledhësh dhe shfaqësh vlerat, kufizimet e tyre në prezantime dhe zgjidhjet duke përdorur tabelat referuese ju lejon të përfitoni shumë më tepër nga Excel, qoftë për analizimin e informacionit të përditshëm apo për përgatitjen e raporteve periodike që mirëmbahen me shumë pak përpjekje.