Begrepene "Power Query", "Power Pivot", "Power BI" og andre "krefter" dukker i økende grad opp i artikler og materialer om Microsoft Excel. Min erfaring er at ikke alle helt klart forstår hva som ligger bak disse konseptene, hvordan de henger sammen og hvordan de kan hjelpe en enkel Excel-bruker.
La oss avklare situasjonen.
Strømforespørsel
Tilbake i 2013 ga en spesiallaget gruppe utviklere i Microsoft ut et gratis tillegg for Excel. Strømforespørsel (andre navn er Data Explorer, Get & Transform), som kan gjøre mange ting nyttig for hverdagsarbeid:
- Last opp data i Excel fra nesten 40 forskjellige kilder, inkludert databaser (SQL, Oracle, Access, Teradata...), bedriftens ERP-systemer (SAP, Microsoft Dynamics, 1C...), Internett-tjenester (Facebook, Google Analytics, nesten alle nettsteder).
- Samle inn data fra Filer alle hoveddatatyper (XLSX, TXT, CSV, JSON, HTML, XML...), både enkeltvis og i bulk – fra alle filer i den angitte mappen. Fra Excel-arbeidsbøker kan du automatisk laste ned data fra alle ark samtidig.
- Rydd opp mottatte data fra "søppel": ekstra kolonner eller rader, repetisjoner, tjenesteinformasjon i "overskriften", ekstra mellomrom eller ikke-utskrivbare tegn, etc.
- Ta med data inn rekkefølge: korriger store og små bokstaver, tall-som-tekst, fyll ut hull, legg til riktig "hette" i tabellen, analyser "klebrig" tekst i kolonner og lim den tilbake, del datoen inn i komponenter osv.
- på alle mulige måter forvandle tabeller, og bringe dem inn i ønsket form (filtrere, sortere, endre rekkefølgen på kolonner, transponere, legge til totaler, utvide krysstabeller til flate og skjule tilbake).
- Erstatt data fra en tabell til en annen ved å matche en eller flere parametere, dvs. fin erstatningsfunksjon VPR (VISNING) og dets analoger.
Power Query finnes i to versjoner: som et eget tillegg for Excel 2010-2013, som kan lastes ned fra det offisielle Microsoft-nettstedet, og som en del av Excel 2016. I det første tilfellet, etter installasjon, vises en egen fane i Utmerke:
I Excel 2016 er all funksjonaliteten til Power Query allerede innebygd som standard og er på fanen Data (Dato) som en gruppe Hent og konverter (Få og transformer):
Mulighetene for disse alternativene er helt identiske.
Den grunnleggende egenskapen til Power Query er at alle handlinger for å importere og transformere data lagres i form av en spørring - en sekvens av trinn i det interne Power Query-programmeringsspråket, som kort og godt kalles "M". Trinn kan alltid redigeres og spilles av et hvilket som helst antall ganger (oppdateringsspørring).
Hovedvinduet Power Query ser vanligvis omtrent slik ut:
Etter min mening er dette det mest nyttige tillegget som er oppført i denne artikkelen for et bredt spekter av brukere. Mange oppgaver som du enten måtte forvrenge med formler eller skrive makroer for, er nå enkelt og vakkert gjort i Power Query. Ja, og med påfølgende automatisk oppdatering av resultatene. Og med tanke på at det er gratis, når det gjelder forhold mellom pris og kvalitet, er Power Query rett og slett ute av konkurranse og et absolutt must for enhver middels avansert Excel-bruker i disse dager.
Powerpivot
Power Pivot er også et tillegg for Microsoft Excel, men designet for litt andre oppgaver. Hvis Power Query er fokusert på import og prosessering, er Power Pivot nødvendig hovedsakelig for kompleks analyse av store datamengder. Som en første tilnærming kan du tenke på Power Pivot som en fancy pivottabell.
De generelle prinsippene for å jobbe i Power Pivot er som følger:
- Vi er den første laster inn data i Power Pivot – 15 forskjellige kilder støttes: vanlige databaser (SQL, Oracle, Access …), Excel-filer, tekstfiler, datastrømmer. I tillegg kan du bruke Power Query som datakilde, noe som gjør analysen nærmest altetende.
- Deretter mellom innlastede tabeller tilkoblinger er konfigurert eller, som de sier, er skapt Datamodell. Dette vil tillate i fremtiden å bygge rapporter på alle felt fra eksisterende tabeller som om det var én tabell. Og ingen VPR igjen.
- Om nødvendig legges ytterligere beregninger til Datamodellen vha beregnede kolonner (ligner på en kolonne med formler i en "smart tabell") og målinger (en analog til det beregnede feltet i sammendraget). Alt dette er skrevet i et spesielt Power Pivot internt språk kalt DAX (Data Analysis eXpressions).
- På Excel-arket, i henhold til Datamodellen, er rapportene av interesse for oss bygget i skjemaet svingbord og diagrammer.
Hovedvinduet i Power Pivot ser omtrent slik ut:
Og slik ser datamodellen ut, dvs. alle nedlastede tabeller med opprettede relasjoner:
Power Pivot har en rekke funksjoner som gjør den til et unikt verktøy for enkelte oppgaver:
- I Power Pivot ingen linjegrense (som i Excel). Du kan laste bord i alle størrelser og enkelt jobbe med dem.
- Power Pivot er veldig god på komprimere data når du laster dem inn i modellen. En original tekstfil på 50 MB kan enkelt bli til 3-5 MB etter nedlasting.
- Siden "under panseret" Power Pivot faktisk har en fullverdig databasemotor, takler den store mengder informasjon veldig fort. Trenger du å analysere 10-15 millioner poster og bygge et sammendrag? Og alt dette på en gammel bærbar PC? Ikke noe problem!
Dessverre er Power Pivot ennå ikke inkludert i alle versjoner av Excel. Hvis du har Excel 2010, kan du laste det ned gratis fra Microsofts nettsted. Men hvis du har Excel 2013-2016, så avhenger alt av lisensen din, fordi. i noen versjoner er det inkludert (Office Pro Plus for eksempel), og i noen er det ikke (Office 365 Home, Office 365 Personal osv.) Du kan lese mer om dette her.
Power Maps
Dette tillegget dukket først opp i 2013 og ble opprinnelig kalt GeoFlow. Den er beregnet for visualisering av geodata, dvs. numerisk informasjon på geografiske kart. De første dataene for visning er hentet fra den samme Power Pivot-datamodellen (se forrige avsnitt).
Demoversjonen av Power Map (nesten ikke forskjellig fra den fullstendige, forresten) kan lastes ned helt gratis igjen fra Microsofts nettsted. Fullversjonen er inkludert i noen Microsoft Office 2013-2016-pakker sammen med Power Pivot – i form av en knapp 3D-kart tab innfelt (Sett inn - 3D-kart):
Hovedfunksjoner i Power Map:
- Kart kan være både flat og voluminøs (klode).
- Du kan bruke flere forskjellige visualiseringstyper (histogrammer, boblediagrammer, varmekart, områdefyllinger).
- Du kan legge til tidsmåling, dvs. animere prosessen og se den utvikle seg.
- Kart lastes fra tjenesten Bing Maps, dvs. Du trenger en veldig rask internettforbindelse for å se. Noen ganger er det vanskeligheter med riktig gjenkjenning av adresser, fordi. navnene i dataene samsvarer ikke alltid med navnene til Bing Maps.
- I den fullstendige (ikke-demo)-versjonen av Power Map kan du bruke din egen nedlastbare kart, for eksempel å visualisere besøkende på et kjøpesenter eller priser på leiligheter i et bolighus rett på byggeplanen.
- Basert på de opprettede geovisualiseringene kan du lage videoer direkte i Power Map (eksempel) for å dele dem senere med de som ikke har tillegget installert eller inkludere dem i en Power Point-presentasjon.
strømvisning
Dette tillegget ble først introdusert i Excel 2013, og er designet for å bringe dataene dine til live med interaktive grafer, diagrammer, kart og tabeller. Noen ganger brukes begrepene for dette. dashbord (dashbord) or dashbord (poengkort). Poenget er at du kan sette inn et spesielt ark uten celler i Excel-filen din – et Power View-lysbilde, hvor du kan legge til tekst, bilder og mange forskjellige typer visualiseringer basert på dataene dine fra Power Pivot-datamodellen.
Det vil se slik ut:
Nyansene her er:
- De første dataene er hentet fra samme sted – fra Power Pivot-datamodellen.
- For å jobbe med Power View må du installere Silverlight på datamaskinen din – Microsofts analoge av Flash (gratis).
På Microsoft-nettstedet er det forresten et veldig anstendig kurs om Power View i .
Power BI
I motsetning til de forrige er ikke Power BI et tillegg for Excel, men et eget produkt, som er et helt sett med verktøy for forretningsanalyse og visualisering. Den består av tre nøkkelelementer:
1. Power BI Desktop – et program for å analysere og visualisere data, som blant annet inkluderer all funksjonaliteten til Power Query og Power Pivot tilleggene + forbedrede visualiseringsmekanismer fra Power View og Power Map. Du kan laste ned og installere det gratis fra Microsofts nettsted.
I Power BI Desktop kan du:
- Last inn data fra over 70 forskjellige kilder (som i Power Query + ekstra kontakter).
- binde tabeller å modellere (som i Power Pivot)
- Legg til flere beregninger til data med målinger и beregnede kolonner på DAX (som i Power Pivot)
- Lag vakre databaserte interaktive rapporter med forskjellige typer visualiseringer (svært lik Power View, men enda bedre og kraftigere).
- Publiser opprettet rapporter på Power BI Service-nettstedet (se neste punkt) og dele dem med kolleger. Dessuten er det mulig å gi forskjellige rettigheter (lesing, redigering) til forskjellige personer.
2. Power BI online tjeneste – for å si det enkelt er dette et nettsted hvor du og hver bruker i bedriften din vil ha sin egen "sandbox" (arbeidsområde) hvor du kan laste opp rapporter som er opprettet i Power BI Desktop. I tillegg til visning lar den deg til og med redigere dem, og reproduserer nesten all funksjonaliteten til Power BI Desktop online. Du kan også låne individuelle visualiseringer fra andres rapporter her, og samle dine egne forfatters dashboards fra dem.
Det ser noe ut som dette:
3. Power BI Mobile er en applikasjon for iOS / Android / Windows for å koble til Power BI-tjenesten og enkelt vise (ikke redigere) de opprettede rapportene og dashbordene rett på skjermen på telefonen eller nettbrettet. Du kan laste den ned (helt gratis) her.
På en iPhone, for eksempel, ser rapporten generert ovenfor slik ut:
Og alt dette mens du opprettholder interaktivitet og animasjon + fengslet for berøring og tegning på skjermen med en penn. Veldig komfortabelt. Dermed blir business intelligence tilgjengelig for alle nøkkelpersoner i selskapet når som helst og hvor som helst – kun Internett-tilgang er nødvendig.
Prisplaner for Power BI. Power BI Desktop og Mobile er gratis ut av esken, og de fleste Power BI Service-funksjonene er også gratis. Så for personlig bruk eller bruk i et lite selskap, trenger du ikke betale en krone for alle de ovennevnte, og du kan trygt holde deg på planen Gratis. Hvis du vil dele rapporter med kolleger og administrere deres tilgangsrettigheter, må du gå til BESS ($10 per måned per bruker). Er det noen flere Premium – for store selskaper (> 500 brukere) som krever separat lagrings- og serverkapasitet for data.
- Prosjekt Gantt-diagram i Excel med Power Query
- Hvordan lage en database i Excel ved hjelp av Power Pivot
- Visualisering av bevegelse langs ruten på kartet i Power Map