Vad gör du om du behöver skapa en pivottabell och dina data finns på olika ark? Med Excel 2013 installerat har du ett enkelt sätt att göra detta. Det finns en teknik som kallas Data Model och den använder datarelationer som en databas gör.
I den här handledningen kommer jag att visa dig allt för att skapa en pivottabell i Excel 2013 från data över flera ark med hjälp av Data Model.
Video
Om du vill följa handledningen med din egen Excel-fil kan du göra det. Eller ladda ner zip-filen för den här lektionen, som innehåller en exempelbok Pivot Consolide.xlsx.
Datautforskning
Det finns tre arbetsblad i denna arbetsbok: kundinformation, orderinformation och betalningsinformation.
Klicka på bladet Kundinformation och se till att den innehåller ordernummer och kundernas namn och status.
Kundinfoblad
Klicka på bladet Beställningsinformation och se att den innehåller ordernummer, såväl som fält för månaden, beställda varor och om dessa varor är ekologiska.
BeställningsinformationsbladGå till fliken Betalningsinformation och se till att den innehåller ordernummer, dollarbeloppet för varje försäljning, betalningsmetod och hur en ny eller befintlig kund kommer att göra en beställning.
Informationsblad för betalningGenom att koppla alla dessa ark i aktivitetsfönstret PivotTable kan vi välja data från varje ark. Eftersom ordernummer finns på alla tre arken kommer de att bli anslutningspunkter. Detta är vad databasen kallar primärnyckel. Observera: det är inte nödvändigt att ha en primärnyckel, men det minskar risken för fel.
Skapa namngivna tabeller
Innan vi skapar en pivottabell, låt oss skapa en tabell från varje ark.
Klicka tillbaka till Kundbord, klicka sedan var som helst i dataområdet. Gå till fliken Infoga på menyfliksområdet och klicka på ikonen Tabell.
Omvandla data i kalkylbladet genom att välja Infoga > TabellDialogrutan Skapa tabell definierar tabellområdet korrekt. Kryssrutan längst ner bör också identifiera att den första raden i tabellen är för rubriker. (Om inte, välj det här alternativet.)
Dialogrutan Skapa tabell bör gissa dataområdet korrektKlick OK, och nu har du ett bord med alternerande slag och filterknappar. Du kan klicka inuti för att avmarkera det om du vill se det bättre (klicka bara inte utanför bordet). Bandpanelen visar också en flik Design för bordet. På vänster sida av tejpen i lådan Tabellnamn det tillfälliga namnet Tabell1 visas. Ta bort den och ring den Kundinformation(använd understreck istället för blanksteg). Klick Skriva in.
Använd ett namn på varje tabellUpprepa dessa steg för beställningsinfo- och betalningsinfobladen. Namnge tabellerna Order_Info Och Betalningsinformation.
Nu är vi redo att infoga pivottabellen.
Infoga en pivottabell
Se till att markören är någonstans i tabellen på betalningsinformationsbladet. Återgå till flik Infoga band och klicka på ikonen Pivottabell(detta är den allra första ikonen).
Använd markören inuti en av tabellerna och välj Infoga > PivottabellI dialogrutan som visas måste du definiera tabellen korrekt och välja att låta pivottabellen gå till det nya kalkylbladet. Markera rutan nedan Lägg till dessa data till datamodellen. Klick OK.
Att lägga till data till datamodellen är det som gör att kopplingarna fungerarDu kommer nu att ha pivottabellen på ett nytt ark, ett aktivitetsfält på höger sida av skärmen och en Analys-flik på menyfliksområdet.
Aktivitetsfältet visar endast tabellen och fälten för det aktiva kalkylbladet, så klicka ALLA för att se alla tabeller du har skapat. Men innan vi kan använda dem måste vi koppla dem till varandra, och det innebär att skapa relationer. Klicka på knappen Relationer på bandpanelen.
Skapa tabellrelationer
Genom att klicka på den här knappen visas dialogrutan Hantera relationer. Klicka på knappen Ny och dialogrutan Skapa relation visas. Vi kommer att skapa två relationer med fältet Order# som en koppling.
Välj från rullgardinslistorna Betalningsinformation för tabellen och välj bredvid den Beställa# i rullgardinsmenyn Kolumn. Välj på den andra raden Kundinformation från rullgardinsmenyn Relaterad tabell och välj bredvid den Beställa# från rullgardinsmenyn Relaterad kolumn.
Det finns tre tabeller, så skapa två relationerDetta innebär att tabellerna Payment_Info och Customer_Info är relaterade till varandra genom att matcha ordernummer.
Klicka på knappen OK och vi kommer att se dessa relationer listade i fönstret Hantera relationer.
Upprepa denna process för att skapa en koppling mellan Payment_Info och Order_Info, även genom att använda fältet Order #. Nu ser fönstret Hantera relationer ut så här:
Dialogrutan Hantera relationer visar de relationer du skaparObservera att det inte finns något behov av att skapa en relation mellan Order_Info- och Customer_Info-tabellerna, eftersom de automatiskt sammanfogas via Payment_Info-tabellen.
Klicka på knappen Nära längst ner i fönstret. Nu kan vi äntligen dra fält till pivottabellen.
Infogar fält i pivottabell
I avsnittet ALLA i aktivitetsfältet klickar du på de små pilarna för att expandera de tre tabellerna för att se deras fält. Dra fält till pivottabellområdet enligt följande:
- Ange Och Månad till linjer
- Produkt i kolumner
- $försäljning i mening
- Status till filter
Nu kan du använda och ändra den som vilken annan pivottabell som helst.
Slutsats
Med den nya objektdatamodellfunktionen i Excel 2013 kan du välja rosa fält från flera ark för att skapa en enda pivottabell. Tänk på att raderna i varje tabell måste vara relaterade till varandra på något sätt. Du har större chans att lyckas när tabeller har ett gemensamt fält med unika värden.
Om du letar efter bra sätt att presentera din data har Envato Market ett bra urval, liksom för att konvertera Excel-data till webbformat och vice versa.
En pivottabell är ett kraftfullt verktyg för att beräkna, sammanfatta och analysera data som gör det enkelt att hitta jämförelser, mönster och trender.
Sätta upp en pivottabell
Förberedelse
Om du inte har tillräckligt med erfarenhet av pivottabeller eller inte vet var du ska börja, är det bäst att använda rekommenderad pivottabell. Excel bestämmer lämplig layout genom att matcha data med de områden som passar bäst i pivottabellen. Detta ger en utgångspunkt för ytterligare experiment. När du har skapat den rekommenderade pivottabellen kan du utforska olika orienteringar och ordna om fälten för att få de resultat du vill ha.
Högst upp i området fältnamn Markera rutan för fältet du vill lägga till i pivottabellen. Som standard läggs icke-numeriska fält till i omfånget rader, datum- och tidsfält läggs till i området kolumner, och numeriska fält läggs till i området värden. Du kan också manuellt dra vilket tillgängligt objekt som helst till valfritt pivottabellfält, eller om du inte längre vill använda ett objekt i pivottabellen, dra det helt enkelt från fältlistan eller avmarkera rutan. Möjligheten att ändra ordningen på fältobjekt är en av pivottabellfunktionerna som gör det lättare att snabbt ändra dess utseende.
Värden i pivottabell
Uppdatering av pivottabeller
När du lägger till ny data till en källa måste du uppdatera eventuella pivottabeller baserat på den. För att uppdatera en pivottabell kan du högerklicka var som helst inom dess räckvidd och välj kommandot Uppdatera. Om du har flera pivottabeller, välj först valfri cell i valfri pivottabell och välj sedan tejpaöppna fliken Pivottabellanalys klickar du på pilen under knappen Uppdatera och välj ett lag Uppdatera alla.
Ta bort en pivottabell
Om du har skapat en pivottabell och bestämmer dig för att du inte längre behöver den, kan du helt enkelt välja hela intervallet för pivottabellen och sedan trycka på Radera. Detta kommer inte att påverka andra data, pivottabeller och diagram runt den. Om pivottabellen finns på ett separat blad som inte längre innehåller de data du behöver, kan du helt enkelt ta bort det arket. Detta är det enklaste sättet att bli av med ett pivotbord.
Du kan nu infoga en pivottabell i ett kalkylblad i Excel för webben.
Arbeta med en lista med pivottabellfält
Högst upp i området Pivottabellfält Markera rutan för fältet du vill lägga till i pivottabellen. Som standard läggs icke-numeriska fält till i " rader", datum- och tidsfält läggs till i området kolumner, och numeriska fält går till området " värden". Du kan också manuellt dra vilket tillgängligt objekt som helst till valfritt fält i pivottabellen, eller om du inte längre vill använda ett objekt i pivottabellen, dra det helt enkelt från fältlistan eller avmarkera rutan. Möjligheten att ändra ordningen på fältobjekt är en av funktionerna i pivottabellen, vilket gör det bekvämare att snabbt ändra utseende.
Arbeta med pivottabellvärden
Uppdatering av pivottabeller
När du lägger till ny data till en källa måste du uppdatera eventuella pivottabeller baserat på den. För att uppdatera en pivottabell kan du högerklicka var som helst i dess intervall och välja Uppdatera
Ta bort en pivottabell
Om du skapade en pivottabell och bestämmer dig för att du inte längre behöver den, kan du helt enkelt välja hela pivottabellsintervallet och sedan trycka på DELETE. Detta kommer inte att påverka andra data, pivottabeller och diagram runt den. Om pivottabellen finns på ett separat blad som inte längre innehåller de data du behöver, kan du helt enkelt ta bort det arket. Detta är det enklaste sättet att bli av med ett pivotbord.
Mer information
Du kan alltid ställa en fråga från Excel Tech Community, be om hjälp i Answers-communityt eller föreslå en ny funktion eller förbättring av webbplatsen
Mycket ofta, när du genererar rapporter (analyser) baserade på stora mängder data, måste du kombinera information från flera ark i en Excel-bok till en tabell.
Helst, för att samla in och analysera information från olika Excel-ark, är det lämpligt att göra en pivottabell som tar data från flera källor (bokens ark).
Denna tabell kan skapas på två olika sätt.
Låt oss överväga den första metoden.
Skapa en pivottabell från olika ark med hjälp av standardfunktioner och verktyg.
Steg ett.
Du måste lägga till en PivotTable och PivotChart Wizard-knapp i verktygsfältet.
För att göra detta, högerklicka på menyfliksområdet (verktygsfältet) och välj "Anpassa band" från rullgardinsmenyn.
eller gå in på fliken
“File” => “Alternativ” => “Anpassa band”.
Därefter, i menyfliksinställningarna, från rullgardinsmenyn under "Välj kommandon", välj "Alla kommandon". Fönstret nedan kommer att visa alla möjliga kommandon som kan placeras på panelen, i alfabetisk ordning.
Från listan väljer du "Pivottabell och diagramguide"
I det högra fönstret, med hjälp av knappen "Skapa grupp", skapar vi en ny grupp verktyg. Du kan välja ett namn som passar dig för gruppen. Till exempel "Egen grupp". Du kan välja på vilken flik gruppen ska skapas. I mitt exempel valde jag fliken Hem.
När gruppen har skapats, välj den med markören, markera "Pivottabeller och diagramguiden" i det vänstra fönstret och klicka på knappen "Lägg till >>".
Klicka sedan på "Ok".
Verktyget Pivottabell och diagramguide finns nu på huvudfliken i verktygsfältet.
Steg två. Bygga en pivottabell från flera datakällor.
Andra sättet.
Skapa en tabell som tar data från flera ark med hjälp av en fråga som genereras i Power Query-tillägget.
Denna metod innebär att du använder en Power Query-tilläggsfråga.
Skapa en Power Query för att kombinera flera sidor i en bok till en tabell.
Steg ett.
Du måste skapa två frågor, som var och en tar information från en separat tabell.
Steg två.
För att göra detta, på fliken Power Query, måste du klicka på knappen "Från tabell" och ange intervallet - datakällan - i fönstret som visas. Klicka sedan på "Ok".
Steg tre.
När den andra frågan skapas måste du klicka på knappen "Sammanfoga fråga" på fliken Power Query och konfigurera utseendet på den resulterande gemensamma tabellen i fönstret som visas.
Vad är Excel-pivottabeller? Förmodligen kommer en oinitierad person att anta det mest uppenbara - det här är tabeller i vilka data från flera andra olika källor samlas in (kombineras) för att svara på en viss begäran. Faktum är att denna slutsats inte är helt korrekt.
Excel Pivot Tables är ett verktyg som samlar in information, oftast från en databastabell! (Möjligheten att använda flera konsolideringsintervall som källa finns, men kommer inte att diskuteras i den här artikeln.)
Genom att använda pivottabeller från en kaotisk uppsättning databasrader kan du på sekunder extrahera de nödvändiga, skär bort onödig information från dem, lägg till de saknade och presentera dem för användaren i en vacker och lättläst form.
Om du ständigt arbetar i MS Excel med stora mängder information och inte vet något om pivottabeller, tänk då på att du "hamrar spikar" med en miniräknare utan att veta dess verkliga syfte!
När ska du använda pivottabeller?
För det första när man arbetar med en stor mängd statistisk data, vilket är mycket arbetskrävande att analysera med hjälp av sortering och filter.
För det andra, när det är nödvändigt att upprepa (beräkna om) rapporter med vissa intervall på grund av ändringar i källan - databastabellen.
För det tredje, när man analyserar förändringar i databasdata över olika tidsperioder eller andra kriterier.
Det här är de viktigaste situationerna där den verkligt magiska kraften i Excel Pivot Table-verktyget avslöjas.
Att skapa pivottabeller och arbeta med dem påverkar inte på något sätt innehållet i källan - databasen!!! Pivottabeller Excellåter dig titta på denna data från olika vinklar utan att ändra något i databasen!!!
Detta är den sista och viktigaste artikeln i recensionsserien om lagra information och hantera stora datavolymer i tabellform.
I de tidigare artiklarna i serien har vi skapat, lärt oss, tillämpat och introducerat.
Skapa en pivottabellmall.
Vi kommer att skapa en pivottabell i samma arbetsbokExcel, där informationskällan finns - en databastabell, men på ett annat ark.
Vi fortsätter att arbeta med den pedagogiska minibasen DB2 "Produktion av metallstrukturer enligt sektion nr 2", som vi arbetade med i alla artiklar i den här serien.
1. Öppna filen i MS Excel.
2. Vi aktiverar ("klicka med musen") vilken cell som helst i databastabellen.
3. Vi utför kommandot i huvudmenyn i programmet "Data" - "Pivottabell...". Detta kommando startar minitjänsten Pivot Table Wizard.
4. Utan att tänka länge på alternativen för att välja omkopplarpositioner i rullgardinsfönstren "Wizards...", ställer vi in dem (mer exakt, vi rör dem inte) som visas nedan i skärmdumparna, och flyttar mellan fönster med hjälp av "Nästa"-knapparna.
I det andra steget kommer "Wizard..." att välja själva intervallet, om du har förberett databasen korrekt och slutfört steg 2 i detta avsnitt av artikeln.
5.
I det tredje steget i "Wizards..." trycker vi på knappen "Slutför". Pivottabellmallen genereras och placeras på ett nytt ark av samma fil där databasen finns!
Förutom den fortfarande tomma mallen, på det nyskapade arket i Excel-filen ser du panelen "Pivottabeller" med dess verktyg och fönstret "Lista över pivottabellfält" som visas när du aktiverar valfri cell inuti pivottabellen. De dyker upp automatiskt, jag placerade dem helt enkelt bredvid mallen och anpassade storlekarna. Om du inte ser panelen "Pivottabeller" ringer du upp den genom huvudmenyn med kommandot "Visa" - "Verktygsfält" - "Pivottabell"
I MS Excel 2007 och nyare versioner har "Wizard..." avskaffats eftersom 99 % av användarna aldrig ändrar de föreslagna växlingsinställningarna och går igenom dessa tre steg, helt enkelt accepterar de föreslagna alternativen (vi gjorde samma sak).
I MS Excel 2007, med hjälp av kommandot "Pivot Table", visas dialogrutan "Skapa en pivottabell", där du bara behöver ange datakällan för analys och platsen för pivottabellen som ska skapas.
Skapa fungerande Excel-pivottabeller.
Den skapade mallen har fyra zoner där du för att skapa en pivottabell ska placera namnen på kolumnfälten i databastabellen genom att dra dem med vänster musknapp från fönstret "Lista över pivottabellfält". Låt mig påminna dig om att i en databas är ett fält en kolumn med en titel.
Uppmärksamhet!!!
Elementen i fönstret "Lista över pivottabellfält" är rubrikerna för databasfälten!
1. Om du placerar ett av PivotTable Field List-elementen i den allra översta zonen i mallen, där det blir "Sidfält", då får vi ett bekvämt filtrera poster för detta fält.
2. Om du drar ett element (eller två, mer sällan - tre eller fler) i "Lista över pivottabellfält" till den vänstra zonen i mallen, där det blir "Strängfält" radrubriker pivottabell.
3. Om du drar ett element (eller två, mer sällan - tre eller fler) i "Lista ..." till den övre zonen av mallen, där det blir "Kolumnfält", då får vi från posterna för detta fält i databastabellen kolumnrubriker pivottabell.
4. Om du drar ett element (eller två, mer sällan - tre eller fler) i "listan ..." till det centrala området i mallen, där det blir "Dataelement", då får vi från posterna i detta databasfält värden pivottabell.
Värdena - dataelement - kommer att placeras i strikt enlighet med reglerna för tvådimensionella tabeller, det vill säga i skärningspunkten mellan motsvarande radrubriker och kolumnrubriker!
Inte särskilt tydligt? Låt oss gå vidare till praktiska exempel - allt kommer att bli klart!
Uppgift #9:
Hur många ton metallstrukturer producerades för varje beställning?
För att svara på denna fråga kommer vi att utföra bara två steg! Diagrammet över dessa åtgärder visas i föregående figur med blå pilar.
1. Dra elementet "Order" från fönstret "Lista över pivottabellfält" till zonen "Radfält" i den tomma mallen.
2.
Använd musen och dra elementet "Total massa, t" från fönstret "Lista över pivottabellfält" till zonen "Dataelement" i mallen. Resultatet visas i skärmdumpen till vänster. Jag tror att det inte behövs någon förklaring.
Rubriken för "Dataelement" "Summa efter fält Total massa, t" finns högre rubriken "Radmarginaler" och till vänster om platsen där rubriken "Kolumnmarginaler" kan placeras. Var uppmärksam på detta!
Uppgift #10:
Hur många ton metallstrukturer producerades för varje beställning efter datum?
Vi fortsätter att jobba.
3. För att besvara frågan om uppgift nr 10, lägg bara till elementet "Datum" i vår pivottabell från fönstret "Lista över pivottabellfält" i "Kolumnfält"-zonen i mallen.
4.
Poster kan till exempel grupperas efter månad. För att göra detta, i panelen "Pivottabeller", klicka på fliken "Pivottabell" och välj "Grupp och struktur" - "Grupp ...". I fönstret "Gruppering" gör vi inställningarna i enlighet med skärmdumpen till vänster.
Eftersom alla poster i vår databas gjordes i april, ser vi efter gruppering bara två kolumner - "Apr" och "Totalt". När poster daterade i maj och juni visas i källdatabasen läggs motsvarande två kolumner till i pivottabellen. 
Uppgift #11:
När och hur många ton och bitar frimärken tillverkades enligt order nr 3?
5. Låt oss först dra fältet "Order" från fönstret "Lista över pivottabellfält" eller direkt från själva pivottabellen till toppen av arket till zonen "Sidfält" i mallen.
7. Lägg till fältet "Antal, st" i området "Dataelement".
Den Excel-pivottabellkonfiguration som krävs för att svara på problemfrågan har skapats.
8. Klicka på nedåtpilen i cell B1 och i rullgardinsmenyn, istället för "(Alla)", välj "3", motsvarande beställning nr 3 av intresse för oss och stäng listan genom att klicka på " OK”-knappen.
Svaret på problemfrågan finns i skärmdumpen under denna text.
Formatera en pivottabell.
Om du behöver skriva ut en pivottabell och visa den för någon, eller bara för att glädja dig själv lite, måste den formateras - ge den ett anständigt utseende och omformulera några "otillräckliga" rubriker.
Rubrikerna "Summa per fält Totalvikt, t" och "Summa för fält Kvantitet, st" verkar förståeliga, men på något sätt inte på ryska. Låt oss döpa om dem till de mer välljudande "Total massa produkter, t" och "Antal produkter, st."
1. Låt oss fortsätta arbeta med den skapade tabellen. Aktivera cell B5 med musen.
2. I verktygsfältet "Pivottabell", klicka på knappen "Fältalternativ" (markerad till höger i bilden nedan).
3. I fönstret "Pivottabellfältberäkning" som dyker upp, ändra namnet och klicka på knappen "OK".
4.
Med en liknande algoritm byter vi namn på den andra fälthuvudet, först "stående" med musen på cell B6.
5. I panelen "Pivottabell", klicka på knappen "Rapportformat" (markerad till vänster i bilden ovan - ovanför punkt 3).
6. I fönstret "AutoFormat" som visas väljer du bland de föreslagna formateringsalternativen, till exempel "Rapport 6" och klickar på "OK".
Utseendet på vårt pivotbord har förändrats avsevärt.
Slutsats.
Jag skulle vilja uppmärksamma er på flera mycket viktiga punkter!
När källan ändras (till exempel lägga till ytterligare en post i databasen) kommer ändringar inte att ske automatiskt i själva pivottabellen!!!
För att ändringar i databasen ska återspeglas i pivottabellen måste du manuellt klicka på knappen "Uppdatera data" varje gång (knappikonen är " ! ) i panelen "Pivottabeller"!!!
Placera fält i området Sidfält som innehåller textinformation, vilket kan bli titel för den genererade pivottabellen.
I områdena "Radfält" och "Kolumnfält" placerar du fält som innehåller textinformation, vilket kan bli rubriker för rader och kolumner den skapade pivottabellen.
I området "Dataelement", placera i första hand numerisk information.
Fortsätt genom att ordna fälten som du skulle göra på ett papper! Var inte rädd för att göra misstag. Allt kan enkelt fixas.
Pivottabellerna som skapades med två eller tre musrörelser i ett av de tidigare avsnitten i denna artikel besvarade mycket snabbt några mycket svåra frågor! Om beställningar produceras under flera månader och antalet varumärken överstiger flera tusen, hur lång tid tar det då att lösa problemen som diskuterats ovan? Timme? Dag? Excel-pivottabeller gör detta omedelbart, upprepade gånger och utan fel!!!
Många bra "tjocka" och "tunna" böcker har skrivits om Excel-pivottabeller. Först och främst är det här böcker av Bill Jelen ("Mr. Excel") och Michael Alexander. För den som är intresserad av detta ämne rekommenderar jag att läsa dem.
Naturligtvis är det väldigt svårt, nästan omöjligt, att prata i detalj om detta underbara verktyg i en kort artikel. Å ena sidan ville jag beröra mer praktiskt viktiga aspekter, men å andra sidan, i den inledande artikeln ville jag inte "sätta mig i ogräset", och skrämma bort användare med informationens volym och komplexitet. Om detta "magiska verktyg" i MS Excel väcker intresse bland bloggläsare, kommer detta ämne i framtiden att fortsätta genom att släppa en liten broschyr med viktiga och värdefulla praktiska rekommendationer.
Var noga med att hämta Excel-pivottabeller! Ditt värde som specialist kommer att öka många gånger om!
Detta avslutar en serie om sex artiklar som ger en allmän översikt över ämnet bearbeta stora mängder information i Excelavslutad.
Behaga respektfullt författarens verk prenumerera för artikelmeddelanden i fönstret som finns i slutet av varje artikel eller i fönstret högst upp på sidan!
Kära läsare, lämna frågor, recensioner och kommentarer i kommentarerna längst ner på sidan.
