Contents [show]
Draaitabelle is een van die kragtigste gereedskap in Excel. Hulle laat jou toe om verskeie opsommings van groot hoeveelhede data met net 'n paar muisklikke te ontleed en op te som. In hierdie artikel sal ons kennis maak met spiltabelle, verstaan wat dit is, leer hoe om dit te skep en aan te pas.
Hierdie artikel is geskryf met behulp van Excel 2010. Die konsep van PivotTables het oor die jare nie veel verander nie, maar die manier waarop jy dit skep, verskil effens in elke nuwe weergawe van Excel. As jy 'n weergawe van Excel het en nie 2010 nie, wees dan voorbereid dat die skermkiekies in hierdie artikel sal verskil van wat jy op jou skerm sien.
'N bietjie geskiedenis
In die vroeë dae van sigblad sagteware, die Lotus 1-2-3 reël bal. Sy oorheersing was so volledig dat Microsoft se pogings om sy eie sagteware (Excel) as 'n alternatief vir Lotus te ontwikkel, soos 'n mors van tyd gelyk het. Nou vinnig vorentoe na 2010! Excel oorheers sigblaaie meer as wat Lotus-kode nog ooit in sy geskiedenis gedoen het, en die aantal mense wat nog Lotus gebruik, is naby aan nul. Hoe kon dit gebeur? Wat was die rede vir so 'n dramatiese wending?
Ontleders identifiseer twee hooffaktore:
- Eerstens het Lotus besluit dat hierdie nuwerwetse GUI-platform genaamd Windows net 'n verbygaande gier was wat nie lank sou hou nie. Hulle het geweier om 'n Windows-weergawe van Lotus 1-2-3 te bou (maar net vir 'n paar jaar), en het voorspel dat die DOS-weergawe van hul sagteware al sou wees wat verbruikers ooit sou benodig. Microsoft het natuurlik Excel spesifiek vir Windows ontwikkel.
- Tweedens het Microsoft 'n instrument in Excel bekendgestel genaamd PivotTables wat nie in Lotus 1-2-3 beskikbaar was nie. PivotTables, eksklusief vir Excel, was so oorweldigend nuttig dat mense geneig was om by die nuwe Excel-sagtewarepakket te bly eerder as om voort te gaan met Lotus 1-2-3, wat dit nie gehad het nie.
PivotTables, tesame met die onderskat van die sukses van Windows in die algemeen, het die doodsmars vir Lotus 1-2-3 gespeel en die sukses van Microsoft Excel ingelui.
Wat is spiltafels?
So, wat is die beste manier om te karakteriseer wat PivotTables is?
In eenvoudige terme, spilpunttabelle is opsommings van sommige data, geskep om die ontleding van hierdie data te vergemaklik. In teenstelling met handgemaakte totale, is Excel-draaitabelle interaktief. Sodra dit geskep is, kan jy hulle maklik verander as hulle nie die prentjie gee waarop jy gehoop het nie. Met net 'n paar muisklikke kan totale omgekeer word sodat kolomopskrifte ryopskrifte word en omgekeerd. Jy kan baie dinge doen met draaitabelle. In plaas daarvan om al die kenmerke van spiltabelle in woorde te probeer beskryf, is dit makliker om dit in die praktyk te demonstreer ...
Die data wat jy met PivotTables ontleed kan nie lukraak wees nie. Dit moet rou rou data wees, soos 'n lys van een of ander aard. Dit kan byvoorbeeld 'n lys wees van verkope wat die maatskappy in die afgelope ses maande gemaak het.
Kyk na die data wat in die figuur hieronder getoon word:
Let daarop dat dit nie rou rou data is nie, aangesien dit reeds opgesom is. In sel B3 sien ons $30000, wat waarskynlik die totale resultaat is wat James Cook in Januarie behaal het. Waar is die oorspronklike data dan? Waar het die $30000 XNUMX-syfer vandaan gekom? Waar is die oorspronklike lys van verkope waaruit hierdie maandelikse totaal afgelei is? Dit is duidelik dat iemand 'n goeie werk gedoen het om al die verkoopsdata vir die afgelope ses maande te organiseer en te sorteer en dit om te skakel in die tabel van totale wat ons sien. Hoe lank dink jy het dit geneem? Uur? Tienuur?
Die feit is dat die tabel hierbo nie 'n spiltabel is nie. Dit is met die hand gemaak van rou data wat elders gestoor is en het ten minste 'n paar uur geneem om te verwerk. Net so 'n opsommingstabel kan binne 'n paar sekondes met behulp van spilpunttabelle geskep word. Kom ons vind uit hoe...
As ons teruggaan na die oorspronklike verkoopslys, sal dit iets soos volg lyk:
Jy sal dalk verbaas wees dat ons uit hierdie lys van ambagte met behulp van draaitabelle en in net 'n paar sekondes 'n maandelikse verkoopsverslag in Excel kan skep, wat ons hierbo ontleed het. Ja, ons kan dit en meer doen!
Hoe om 'n spiltabel te skep?
Maak eers seker dat jy 'n paar brondata in 'n Excel-blad het. Die lys van finansiële transaksies is die mees tipiese wat voorkom. Trouens, dit kan 'n lys van enigiets wees: kontakbesonderhede van werknemers, 'n CD-versameling of jou maatskappy se brandstofverbruikdata.
So, ons begin Excel ... en laai so 'n lys ...
Nadat ons hierdie lys in Excel oopgemaak het, kan ons begin om 'n spiltabel te skep.
Kies enige sel uit hierdie lys:
Dan op die blad invoeging (Voeg in) kies opdrag Draaitabel (Swaaitabel):
'n Dialoogkassie sal verskyn Skep draaitabel (Skep 'n spiltabel) met twee vrae vir jou:
- Watter data om te gebruik om 'n nuwe spiltabel te skep?
- Waar om die spiltabel te plaas?
Aangesien ons in die vorige stap reeds een van die lysselle gekies het, sal die hele lys outomaties gekies word om 'n spiltabel te skep. Let daarop dat ons 'n ander reeks, 'n ander tabel, en selfs 'n eksterne databron soos 'n Access- of MS-SQL-databasistabel kan kies. Daarbenewens moet ons kies waar om die nuwe spiltafel te plaas: op 'n nuwe vel of op een van die bestaandes. In hierdie voorbeeld sal ons die opsie kies - Nuwe Werkkaart (na 'n nuwe blad):
Excel sal 'n nuwe vel skep en 'n leë spiltabel daarop plaas:
Sodra ons op enige sel in die spilpunttabel klik, sal 'n ander dialoogkassie verskyn: PivotTable Veldlys (Draaitabelvelde).
Die lys velde aan die bokant van die dialoogkassie is 'n lys van al die titels van die oorspronklike lys. Die vier leë areas aan die onderkant van die skerm laat jou toe om die draaitabel te vertel hoe jy die data wil opsom. Solank hierdie areas leeg is, is daar ook niks in die tabel nie. Al wat ons hoef te doen is om die opskrifte van die boonste area na die leë areas hieronder te sleep. Terselfdertyd word 'n spiltabel outomaties gegenereer, in ooreenstemming met ons instruksies. As ons 'n fout maak, kan ons die opskrifte van die onderste area verwyder of ander sleep om dit te vervang.
Area Waardes (Betekenisse) is seker die belangrikste van die vier. Watter opskrif in hierdie area geplaas word, bepaal watter data opgesom sal word (som, gemiddeld, maksimum, minimum, ens.) Dit is amper altyd numeriese waardes. 'n Uitstekende kandidaat vir 'n plek in hierdie area is die data onder die opskrif bedrag (Koste) van ons oorspronklike tabel. Sleep hierdie titel na die area Waardes (Waardes):
Neem asseblief kennis dat die titel bedrag is nou gemerk met 'n regmerkie, en in die area Waardes (Waardes) 'n inskrywing het verskyn Som van Bedrag (Bedrag veld Bedrag), wat aandui dat die kolom bedrag opgesom.
As ons na die spiltabel self kyk, sal ons die som van alle waardes uit die kolom sien bedrag oorspronklike tabel.
So, ons eerste spiltabel is geskep! Gerieflik, maar nie besonder indrukwekkend nie. Ons wil waarskynlik meer inligting oor ons data kry as wat ons tans het.
Kom ons blaai na die oorspronklike data en probeer om een of meer kolomme te identifiseer wat gebruik kan word om hierdie som te verdeel. Ons kan byvoorbeeld ons spilpunttabel op so 'n manier vorm dat die totale hoeveelheid verkope vir elke verkoper individueel bereken word. Dié. rye sal by ons spilpunttabel gevoeg word met die naam van elke verkoopspersoon in die maatskappy en hul totale verkoopsbedrag. Om hierdie resultaat te bereik, sleep net die titel verkoopspersoon (verkoopsverteenwoordiger) na die streek Ryetikette (Snare):
Dit word meer interessant! Ons draaitabel begin vorm aanneem ...
Sien u die voordele? In 'n paar kliks het ons 'n tabel geskep wat baie lank sou neem om handmatig te skep.
Wat anders kan ons doen? Wel, in 'n sekere sin is ons spiltafel gereed. Ons het 'n nuttige opsomming van die oorspronklike data geskep. Belangrike inligting reeds ontvang! In die res van hierdie artikel sal ons kyk na 'n paar maniere om meer komplekse draaitabelle te skep, asook leer hoe om dit aan te pas.
Opstelling van draaitabel
Eerstens kan ons 'n tweedimensionele spiltabel skep. Kom ons doen dit deur die kolomopskrif te gebruik Betalings metode (Betalings metode). Sleep net die titel Betalings metode na die gebied Kolometikette (Kolomme):
Ons kry die resultaat:
Lyk baie gaaf!
Kom ons maak nou 'n driedimensionele tabel. Hoe sou so 'n tafel lyk? Kom ons kyk…
Sleep kop pakket (Kompleks) na die area rapporteer filters (Filters):
Let op waar hy is...
Dit gee ons die geleentheid om die verslag te filtreer op grond van "Vir watter vakansiekompleks is betaal." Ons kan byvoorbeeld 'n uiteensetting deur verkopers en volgens betaalmetodes vir alle komplekse sien, of in 'n paar muisklikke, die aansig van die spiltabel verander en dieselfde uiteensetting wys slegs vir diegene wat die kompleks bestel het Sonsoekers.
Dus, as jy dit reg verstaan, kan ons spiltabel driedimensioneel genoem word. Kom ons gaan voort met die opstel...
As dit skielik blyk dat slegs betaling per tjek en kredietkaart (dit wil sê kontantlose betaling) in die spiltabel vertoon moet word, dan kan ons die vertoning van die titel afskakel Kontant (Kontant). Hiervoor, langs Kolometikette klik op die afpyltjie en ontmerk die blokkie in die aftreklys Kontant:
Kom ons kyk hoe ons spiltabel nou lyk. Soos jy kan sien, die kolom Kontant het van haar verdwyn.
Formatering van draaitabelle in Excel
PivotTables is natuurlik 'n baie kragtige instrument, maar tot dusver lyk die resultate 'n bietjie eenvoudig en vervelig. Byvoorbeeld, die getalle wat ons optel, lyk nie soos dollarbedrae nie – dit is net getalle. Kom ons maak dit reg.
Dit is aanloklik om in so 'n situasie te doen waaraan jy gewoond is en eenvoudig die hele tabel (of die hele blad) te kies en die standaard getalformateringknoppies op die nutsbalk te gebruik om die verlangde formaat in te stel. Die probleem met hierdie benadering is dat as jy ooit die struktuur van die spilpunttabel in die toekoms verander (wat gebeur met 'n 99% kans), die formatering verlore gaan. Wat ons nodig het, is 'n manier om dit (amper) permanent te maak.
Kom ons soek eers die inskrywing Som van Bedrag in Waardes (Waardes) en klik daarop. Kies die item in die kieslys wat verskyn Waardeveldinstellings (Waarde veld opsies):
'n Dialoogkassie sal verskyn Waardeveldinstellings (Waarde veld opsies).
pers Getalformaat (Getalformaat), 'n dialoogkassie sal oopmaak. Formateer selle (selformaat):
Uit die lys kategorie (Getalformate) kies Rekeningkunde (Finansieel) en stel die aantal desimale plekke op nul. Druk nou 'n paar keer OKom terug te gaan na ons spiltabel.
Soos u kan sien, word die getalle as dollarbedrae geformateer.
Terwyl ons besig is met formatering, laat ons die formaat vir die hele draaitabel opstel. Daar is verskeie maniere om dit te doen. Ons gebruik die een wat eenvoudiger is ...
Klik op die PivotTable Tools: Ontwerp (Werk met PivotTables: Konstrukteur):
Brei dan die spyskaart uit deur op die pyltjie in die onderste regterhoek van die afdeling te klik Draaitabelstyle (Draaitafelstyle) om die uitgebreide versameling inlynstyle te sien:
Kies enige geskikte styl en kyk na die resultaat in jou spilpunttabel:
Ander draaitabelinstellings in Excel
Soms moet jy data volgens datums filter. Byvoorbeeld, in ons lys van ambagte is daar baie, baie datums. Excel bied 'n hulpmiddel om data volgens dag, maand, jaar, ensovoorts, te groepeer. Kom ons kyk hoe dit gedoen word.
Verwyder eers die inskrywing. Betalings metode uit die streek Kolometikette (Kolomme). Om dit te doen, sleep dit terug na die lys titels en skuif die titel in sy plek Datum bespreek (datum van bespreking):
Soos u kan sien, het dit ons spilpunttabel tydelik nutteloos gemaak. Excel het 'n aparte kolom geskep vir elke datum waarop 'n handel gemaak is. Gevolglik het ons 'n baie wye tafel gekry!
Om dit reg te stel, regskliek op enige datum en kies uit die konteks kieslys GROEP (Groep):
Die groepering dialoogkassie sal verskyn. Ons kies Maande (Maande) en klik OK:
Voila! Hierdie tabel is baie nuttiger:
Terloops, hierdie tabel is amper identies aan die een wat aan die begin van die artikel gewys word, waar die verkoopstotale handmatig saamgestel is.
Daar is nog 'n baie belangrike punt wat jy moet weet! Jy kan nie een, maar verskeie vlakke van ry (of kolom) opskrifte skep:
… en dit sal so lyk …
Dieselfde kan gedoen word met kolomopskrifte (of selfs filters).
Kom ons keer terug na die oorspronklike vorm van die tabel en kyk hoe om gemiddeldes in plaas van somme te vertoon.
Om te begin, klik op Som van Bedrag en kies uit die spyskaart wat verskyn Waardeveldinstellings (Waarde veld opsies):
Die lys Som waardeveld op deur (Bedryf) in die dialoogkassie Waardeveldinstellings (Waarde veld opsies) kies Gemiddeld (Gemiddeld):
Terselfdertyd, terwyl ons hier is, laat ons verander Gepasmaakte Naam (Gepasmaakte naam) met Gemiddelde van Bedrag (Bedrag-veld Bedrag) na iets korter. Tik in hierdie veld iets soos Gem:
Druk OK en kyk wat gebeur. Let daarop dat alle waardes van totale na gemiddeldes verander het, en die tabelopskrif (in die boonste linker sel) het verander na Gem:
As jy wil, kan jy dadelik die bedrag, gemiddelde en getal (verkope) in een spiltabel geplaas kry.
Hier is 'n stap-vir-stap gids oor hoe om dit te doen, wat begin met 'n leë spiltabel:
- Sleep kop verkoopspersoon (verkoopsverteenwoordiger) na die streek Kolometikette (Kolomme).
- Sleep die titel drie keer bedrag (Koste) tot area Waardes (Waardes).
- Vir die eerste veld bedrag verander die titel na Totaal (Bedrag), en die getalformaat in hierdie veld is Rekeningkunde (Finansieel). Die aantal desimale plekke is nul.
- Tweede veld bedrag naam Gemiddelde, stel die bewerking daarvoor in Gemiddeld (Gemiddeld) en die getalformaat in hierdie veld verander ook na Rekeningkunde (Finansieel) met nul desimale plekke.
- Vir die derde veld bedrag stel 'n titel Tel en 'n operasie vir hom - Tel (Hoeveelheid)
- In die Kolometikette (Kolomme) veld outomaties geskep Σ Waardes (Σ Waardes) – sleep dit na die area Ryetikette (Lyne)
Hier is waarmee ons sal eindig:
Totale bedrag, gemiddelde waarde en aantal verkope – alles in een spiltabel!
Gevolgtrekking
Spiltabelle in Microsoft Excel bevat baie kenmerke en instellings. In so 'n klein artikel is hulle nie eens naby om hulle almal te dek nie. Dit sal 'n klein boek of 'n groot webwerf neem om al die moontlikhede van spilpunttabelle volledig te beskryf. Vet en nuuskierige lesers kan voortgaan met hul verkenning van spilpunttabelle. Om dit te doen, kliek net met die rechtermuisknop op byna enige element van die spilpunttabel en kyk watter funksies en instellings oopmaak. Op die lint sal jy twee oortjies vind: PivotTable Tools: Opsies (ontleding) en ontwerp (Konstrukteur). Moenie bang wees om 'n fout te maak nie, jy kan altyd die draaitabel uitvee en oor begin. Jy het 'n geleentheid wat lang tyd gebruikers van DOS en Lotus 1-2-3 nog nooit gehad het nie.