Pivottabeller har traditionellt konstruerats med hjälp av OLAP-kuber och andra komplexa datakällor som redan har omfattande kopplingar mellan tabeller. Men i Excel kan du importera flera tabeller och skapa egna anslutningar mellan tabeller. Även om denna flexibilitet är kraftfull, gör den det också enkelt att sammanföra data som inte är relaterade, vilket leder till konstiga resultat.
Har du någonsin skapat en pivottabell som den här? Du ville skapa en uppdelning av inköp per region och släppte därför ett fält för inköpsbelopp i området Värden och sedan ett fält för försäljningsregion i området Kolumnetiketter . Men resultaten är felaktiga.
Hur kan du åtgärda detta?
Problemet är att fälten som du har lagt till i pivottabellen kanske finns i samma arbetsbok, men tabellerna som innehåller varje kolumn är inte relaterade. Du kan till exempel ha en tabell som visar varje försäljningsregion och en annan tabell som visar inköp för alla regioner. För att skapa pivottabellen och få rätt resultat måste du skapa en relation mellan de två tabellerna.
När du har skapat relationen kombineras data från inköpstabellen med listan över regioner korrekt i pivottabellen, och resultatet ser ut så här:
Excel innehåller teknik som utvecklats av Microsoft Research (MSR) för att automatiskt identifiera och åtgärda relationsproblem som det här.
Använda automatisk identifiering
Automatisk identifiering kontrollerar nya fält som du lägger till i en arbetsbok som innehåller en pivottabell. Om det nya fältet inte är relaterat till kolumn- och radrubrikerna i pivottabellen visas ett meddelande i meddelandefältet högst upp i pivottabellen om att en relation kan behövas. Excel analyserar även nya data för att hitta potentiella relationer.
Du kan fortsätta att ignorera meddelandet och arbeta med pivottabellen. Men om du klickar på Skapa börjar algoritmen arbeta och analyserar dina data. Beroende på värdena i nya data och pivottabellens storlek och komplexitet samt de relationer du redan har skapat kan processen ta upp till flera minuter.
Processen består av två faser:
- Identifiering av relationer. Du kan granska listan med föreslagna relationer när analysen är klar. Om du inte avbryter går Excel automatiskt vidare till nästa steg i skapandet av relationerna.
- Skapande av relationer. När relationerna har kopplats visas en bekräftelsedialogruta och du kan klicka på länken Information för att visa en lista med de relationer som har skapats.
Du kan avbryta identifieringsprocessen, men du kan inte avbryta skapandeprocessen.
MSR-algoritmen söker efter den "bästa möjliga" uppsättningen relationer för att ansluta tabellerna i din modell. Algoritmen identifierar alla möjliga relationer för nya data, med hänsyn tagen till kolumnnamn, datatyper i kolumner, värden i kolumner och kolumner som finns i pivottabeller.
Excel väljer sedan den relation som har högst kvalitetspoäng, enligt intern heuristik. Mer information finns i Översikt över relationer och Felsöka relationer.
Om den automatiska identifieringen inte ger dig rätt resultat kan du redigera relationer, ta bort dem eller skapa nya manuellt. Mer information finns i Skapa en relation mellan två tabeller eller Skapa relationer i diagramvyn
Tomma rader i pivottabeller (okänd medlem)
Eftersom en pivottabell sammanför relaterade datatabeller måste data hanteras på något sätt om en tabell innehåller data som inte kan relateras med hjälp av en nyckel eller ett matchande värde. I flerdimensionella databaser kan du hantera felmatchade data genom att tilldela alla rader som inte har något matchande värde till den okända medlemmen. Den okända medlemmen visas i en pivottabell med en tom rubrik.
Om du till exempel skapar en pivottabell som ska gruppera försäljning efter butik, men vissa poster i tabellen Försäljning inte har något butiksnamn, grupperas alla poster utan ett giltigt butiksnamn tillsammans.
Om du får tomma rader har du två alternativ. Du kan antingen definiera en fungerande tabellrelation, kanske genom att skapa en kedja av relationer mellan flera tabeller, eller så kan du ta bort fält från pivottabellen där de tomma raderna uppstår.