Hur man använder Power Query och Power Pivot i Excel som ett proffs

Om du vill vara en riktig kalkylarksanvändare måste du lära dig om funktionerna Power Query och Power Pivot i Excel. Även om du kan få mycket gjort med Excel enbart, kommer du att använda dessa inbyggda "Power"-funktioner för att göra dig till en avancerad professionell Excel-användare. I den här guiden lär du dig hur du använder Power Query för att importera potentiellt hundratals olika filer med miljontals rader och använd Power Pivot för att generera komplexa analyser på de massiva datamodellerna du importera.

Vill du skydda ditt arbete från nyfikna ögon? Lära sig hur lägg till ett lösenord till din Excel-arbetsbok.

1. Importera data från flera filer

Med Power Query kan du importera data från olika filer i en enda mapp. Var dock uppmärksam, eftersom du fortfarande bör se till att all data följer samma format. Se till att antalet kolumner och kolumnrubrikernas namn är desamma och se till att datatypen för värdena i varje kolumn är konsekvent.

För den här guiden använder vi fem filer som omfattar ~100 miljoner rader med data från Kaggle. Ladda ner dem om du vill följa med eller använda din egen data. Med det i åtanke, här är stegen för att importera data:

1. Klicka på fliken "Data" i menyfliksområdet.
2. Välj "Hämta data -> Från fil -> Från mapp", bläddra sedan till din mapp och välj den.

1. Du bör se ett nytt fönster. I vårt fall har vi fem testfiler som är namngivna efter deras storlek: "adult10m" har 10 miljoner rader med data, "adult1m" har 1 miljon rader, "adult100k" har 100 000 rader, och så vidare. Klicka på "Transformera data" för att öppna Power Query Editor.

2. Filtrera de importerade filerna och raderna

I Power Query Editor kan du använda filter för att importera endast de filer som matchar kriterierna du ställer in, vilket är mycket användbart för att eliminera datakällor du inte vill ha.

1. Vi har fyra filer utan filändelse, men en fil med filtillägget .DATA. Låt oss ta bort den udda genom att klicka på pilen bredvid "Extension".

1. Vi kan se en lista över alla våra filers filtillägg. I det här exemplet är dessa bara (tom) och .data. Avmarkera de tillägg du inte vill ha.

1. Varje filter vi tillämpade kommer att visas under avsnittet "Tillämpade steg".
2. Vi kan också filtrera efter filnamn genom att klicka på pilen bredvid "Namn".

1. Låt oss säga att vi ville filtrera bort alla filer med "k" i dem, som "adult100k", eftersom vi inte vill arbeta med några små filer som innehåller mindre än en miljon rader. Vi kan använda ett textfilter. Det finns många alternativ, men för våra ändamål är filtret "Innehåller inte" det rätta.

1. Skriv in texten som du inte vill se i filnamnet.

1. Bläddra lite till höger tills du kan se kolumnerna "Datum modifierat" och "Datum skapat". Låt oss filtrera för att bara välja filer skapade inom ett visst tidsintervall. Klicka på pilen bredvid Skapat datum.

1. Välj "Datum/Time Filters -> Between."

1. Skriv in tidsvärden för de två villkoren och bekräfta genom att trycka på "OK".

1. Vi har slutfört filtreringen av vår data på fil-för-fil-nivå.
2. Låt oss kombinera våra återstående filer så att vi kan filtrera själva data. Klicka på dubbelpilarna bredvid rubriken "Innehåll".

1. Nu när du kan se alla värden för enskilda rader har varje rad en extra kolumn, "Källa. Namn”, som anger filen där den kommer från.

1. För att ytterligare förbättra våra data: eftersom våra CSV-filer ursprungligen inte hade några kolumnrubriknamn, kan du högerklicka på några av kolumnrubrikerna och klicka på "Byt namn" för att ge dem ett lämpligt namn.

1. Filter fungerar här också. Vi kan använda ett nummerområdesfilter. Klicka till exempel på pilen bredvid kolumnrubriken "Ålder" och välj sedan "Antalfilter -> Större än."

1. Ange ett nummer som 30 och klicka sedan på "OK". Alla rader med åldrar under det angivna antalet kommer att exkluderas.

Dricks: när du arbetar med kalkylblad i Microsoft Excel kan det vara praktiskt att veta hur man flyttar en kolumn.

3. Laddar filtrerade data till en datamodell

Du kan fortsätta att rensa och filtrera data, men låt oss gå vidare och ladda in den i en datamodell för att börja analysera den.

1. Avsluta med Power Query genom att klicka på "Stäng och ladda -> Stäng och ladda till."
2. I popup-fönstret som öppnas, välj "Skapa endast anslutning" och markera "Lägg till denna data till datamodellen."

1. Vänta tills data laddas. Detta kan ta några minuter om du har en stor mängd data.

Använda Power Pivot-datamodellen i Excel

1. När dataladdningen är klar kan vi göra ändringar (som att lägga till nya kolumner) i datamodellen genom att klicka på "Data -> Dataverktyg -> Hantera datamodell."

1. Du bör se Power Pivot-fönstret. Låt oss lägga till en kolumn med en DAX-formel (Data Analysis Expressions). DAX-formler är mycket lika Excel-formler som du förmodligen redan är bekant med. Bläddra åt höger tills du ser den sista kolumnen med dina data, klicka sedan på "Lägg till kolumn".
2. Låt oss skriva en formel för denna kolumn som tillämpar en enkel beräkning på varje rad. Jämfört med Excel-formler är DAX-syntaxen något annorlunda. Till exempel kan vi lägga till en kolumn som heter "Beskrivning" och mata in formeln =KONKATERA([Kolumn9],[Kolumn10]). Notera användningen av hakparenteser ([ ]) och åtkomsten via kolumnrubrikens namn.

1. Om du vill kan du också göra ytterligare ändringar som att byta namn på eller ta bort kolumner genom att högerklicka på kolumnrubriken.

4. Analysera data med pivottabell

När du är klar med ändringar i datamodellen är det dags att analysera data i ett Excel-kalkylblad med pivottabell.

1. Klicka på "Hem -> Pivottabell" på menyfliksområdet i Power Pivot-fönstret för att öppna fönstret "Skapa pivottabell".

1. Välj "Nytt arbetsblad" och tryck på "OK".

1. Panelen Pivottabellfält öppnas i ett nytt ark i huvudfönstret i Excel. I rutan under sökfältet bör du se dina datakällor listade, "kaggle_adult_csv" i det här exemplet. Klicka på den för att expandera den och visa alla dess kolumner.

1. Låt oss göra en analys där vi räknar antalet av varje "beskrivning" i vår data. Vi kan välja de kolumner vi vill arbeta med genom att klicka på kryssrutan bredvid deras namn. Välj "Beskrivning" och lägg märke till hur fokus automatiskt skiftar till området "Rader" nedan. Det här är Power Pivot som visar sin intelligens – den kan exakt gissa det lämpliga området för urval. Eftersom vi utgår från en tom pivottabell är det vettigt att vårt första val är rader.

1. Dra den markerade "Beskrivning"-kolumnen till det tomma utrymmet i "Värden". Observera att Power Pivot återigen korrekt antar att vi vill ha en "Beskrivningsräkning" på grund av att datatypen är text. Vår tabell har också uppdaterats med en kolumn som visar antalet för varje förekomst av varje "Beskrivning"-värde.

1. Vi kan lägga till ytterligare en dimension till vår räkning. Dra till exempel kolumnen "Ålder" till det tomma området "Kolumner" för att lägga till mer detaljer i tabellen genom att räkna antalet beskrivningar efter varje ålder.

1. Du kan också lägga till en filtervalsförmåga till tabellen genom att dra kolumnen "WorkClass" till området "Filters". Lägg märke till den nya interaktiva cellen som visas ovanför vår tabell.

1. Klicka på pilen bredvid "Alla" och välj sedan "Aldrig fungerat". Detta kommer att ändra tabellen till att endast inkludera personer med en WorkClass på "Aldrig arbetat" i beräkningarna. Du kan ändra filtret när som helst för att se tabellen genom en annan lins.

Har att göra med många dubbletter i din Excel-arbetsbok? Lär dig hur du hittar och tar bort dubbletter och rengör dina kalkylark.

5. Presentera dina data med PowerChart

Du kan skapa fler kalkylblad för att analysera din data på olika sätt. Den här gången, låt oss skapa ett mer visuellt imponerande resultat med hjälp av PowerChart.

1. Återgå till fönstret "Power Pivot for Excel". Om den är stängd kan du öppna den från Excel-fönstret genom "Data -> Dataverktyg -> Hantera datamodell."
2. Klicka på "Pivottabell -> Pivotdiagram -> Nytt kalkylblad."

1. I panelen till höger klickar du på datakällan ("kaggle_adult_csv") för att expandera den och visa alla dess kolumner.
2. Dra och släpp kolumnerna från datakällan till ett av de fyra områdena nedan för att bygga diagrammet. Flytta kolumnen "OccupationType" till området "Axis (Categories)".

1. Dra "OccupationType" igen och släpp den denna gång i området "Värden". Du kommer omedelbart att märka att ett stapeldiagram genereras automatiskt. Den visar antalet för varje typ av yrke.

1. Du kan också dra kolumnen "Etnicitet" till området "Legend (Serie)". Du kommer omedelbart att se ett mer detaljerat diagram som jämför hur många av varje etnicitet som utgör varje yrke.

1. Därefter kan vi tillämpa filter på vår Legend för att förenkla vårt diagram och bara titta på vissa värden. Flytta musen över "Etnicitet" så att en pil visas på höger sida. Klicka på pilen.

1. Klicka på kryssrutorna bredvid de värden du vill ta bort, och lämna bara två.

1. Låt oss prova något nytt. Under avsnittet "Värden", klicka på "Antal yrkestyp" och klicka sedan på "Ta bort fält." Dra och släpp kolumnen "Ålder" för att ersätta den.

1. Du kommer att märka att värdet automatiskt definieras som "Sum of Age", men det är inte särskilt användbart i den verkliga världen. Låt oss ändra på det. Klicka på den och välj "Värdefältsinställningar".

1. Eftersom ålder är en siffra finns det många beräkningar som vi kan tillämpa på detta avsnitt. Försök att välja "Genomsnitt" till exempel.

1. Det visuella kommer att uppdateras för att visa medelåldern för varje yrke efter specifika etniciteter (som vi filtrerade efter).

Vill du fräscha upp grunderna i Excel? Vi har en omfattande guide på alla kortkommandon för Excel.

Vanliga frågor

Vad är ursprunget till Power Pivot?

Microsoft introducerade Power Pivot som en separat tillägg tillhandahålls av Microsofts SQL Server 2008 R2, som släpptes 2010. Då hette den "PowerPivot" utan utrymme. 2013 döptes det om till "Power Pivot." Det blev först en inbyggd Excel-funktion från och med 2016.

Vad är dataanalysuttryck och hur skiljer de sig från Excel-formler?

Data Analysis Expressions är ett formelspråk som används av Power Pivot i Excel och av Power BI. I huvudsak är det hur du skriver formler för beräkningar i ovannämnda applikationer: de satser, syntax och funktioner du använder.

DAX: s främsta fördel gentemot Excel-formler är att den är utformad för att arbeta på aggregerad data. Standard Excel-formler kan utföra beräkningar på rad-för-rad-basis (som att beräkna summan av flera värden i rad), men DAX formler kan fungera på en kolumn-för-kolumn basis (som att räkna antalet av ett specifikt värde i en kolumn), eller till och med på en hel tabell.

Hur fixar jag "Den här tabellen skapades av en fråga. För att ändra den här tabellen, ändra frågan istället" error?

Det här felet i Excel Power Pivot inträffar när du försöker ändra en datamodell i Power Pivot när den datamodellen ursprungligen laddades via Power Query. Du kan inte göra detta, så du måste ändra det via den ursprungliga inläsningsmetoden: Power Query.

På höger sida av Excel, klicka på ikonen "Frågor och anslutningar", leta reda på och dubbelklicka på din frågedatakälla för att öppna fönstret där du kan ändra tabellen.

Bildkredit: Pexels. Alla skärmdumpar av Brandon Li.