Har du inte Power Pivot? Spelar ingen roll. Det mesta av Power Pivot är inbyggt i Excel 2013 och ännu mer i Excel 2016. I dag är vårt tips från Ash att gå med i tabeller i en pivottabell.
Varje onsdag i sju veckor presenterar jag ett av favorittipsen från Ash Sharma. Ash är en produktchef i Excel-teamet. Hans team ger dig svängbord och många andra bra saker. Idag är Ashs favoritfunktion att gå med i flera datamängder med hjälp av Relations och datamodellen.
Säg att din IT-avdelning ger dig den datamängd som visas i kolumner A: D. Det finns fält för kund och marknad. Du måste kombinera vissa marknader i regioner. Varje kund tillhör en sektor. Region och sektor finns inte i originaldata, men du har uppslagstabeller för att tillhandahålla denna information.
Normalt skulle du platta ut data genom att använda VLOOKUP för att dra data från de orange och gula tabellerna till den blå tabellen. Men eftersom nyckelfältet inte finns till vänster om varje tabell, måste du antingen byta till INDEX och MATCH eller ordna om uppslagstabellerna.
Från och med Excel 2013 kan du lämna uppslagstabellerna där de är och kombinera dem i själva pivottabellrapporten.
För att denna teknik ska fungera måste alla tre tabellerna formateras som en tabell. Välj en cell i varje datamängd och välj Hem, Formatera som tabell eller tryck på Ctrl + T. De tre tabellerna kommer initialt att kallas Tabell 1, Tabell 2 och Tabell 3. Jag använder fliken Design för tabellverktyg i menyfliksområdet och namnge varje tabell. Jag ändrar också färgen på varje tabell. I det här exemplet heter den blå tabellen Data. Det orange bordet är RegionTable. Det gula bordet är SectorTable.
Notera
Vissa kommer att säga att du bör använda nördiga namn som Fact, TblSector och TblRegion. Om någon besvärar dig så här, stjäl bara deras fickskydd och låt dem veta att du föredrar engelska klingande namn.
För att byta namn på en tabell, skriv ett nytt namn i rutan till vänster på fliken Design för tabellverktyg. Tabellnamn bör inte ha mellanslag.
När de tre tabellerna har definierats går du till fliken Data och klickar på Relationer.
Klicka på Nytt i dialogrutan Hantera relationer. I dialogrutan Skapa relation anger du att datatabellens kundfält är relaterat till Sektortabells kundfält. Klicka på OK.
Definiera ytterligare en ny relation mellan fältet Marknad i fältet Data och RegionTable. När du har definierat båda relationerna ser du dem i dialogrutan Hantera relationer.
Grattis: du har precis byggt en datamodell i din arbetsbok. Det är dags att bygga ett pivottabell.
Välj den tomma cellen där du vill att din pivottabell ska visas. Som standard väljer dialogrutan Skapa pivottabell Använd den här arbetsbokens datamodell. Pivottabellens plats är som standard den cell du valde. Klicka på OK.
Listan över pivottabellfält visar alla tre tabellerna. Använd triangeln till vänster om en tabell för att expandera tabellnamnet så att du visar fälten.
Expandera datatabellen. Välj fältet Intäkter. Den flyttas automatiskt till området Värden. Expandera Sektortabellen. Välj fältet Sektor. Det kommer att flytta till raderna. Utöka RegionTable. Dra regionfältet till området Kolumner. Du kommer nu att ha en pivottabell som sammanfattar data från de tre tabellerna.
Notera
I varje bok som jag har skrivit tidigare idag använder jag en annan teknik för att bygga denna rapport. Efter att ha definierat de tre tabellerna väljer jag cell A1 och Infoga, pivottabell. Jag markerar rutan för Lägg till dessa data i datamodellen. I listan för pivottabellfält väljer du Alla högst upp i listan. Välj fält för rapporten och definiera sedan relationerna efter faktum. Tekniken som beskrivs ovan verkar smidigare och innebär faktiskt lite planering framåt. De som använder Option Explicit i sin VBA-kod skulle definitivt vilja ha den här metoden.
Relationerna i datamodellen gör att Excel känns mer som Access eller SQL Server, men med all godhet i Excel.
Jag älskar att be Excel-teamet om deras favoritfunktioner. Varje onsdag kommer jag att dela ett av deras svar. Tack till Ash Sharma för att ge denna idé.
Excel-tanke på dagen
Jag har frågat mina Excel Master-vänner om deras råd om Excel. Dagens tanke att fundera på:
"Sök inte om du är i ett förhållande"
John Michaloudis
