Tio + ett bonus sätt att kvalitetssäkra dina kalkyler i Excel

Något ingen vill vara med om är att det upptäcks fel i kalkylerna, först när rapporten är klar.

Särskilt om beräkningarna är korrekta och felen i stället beror på bristande datakvalitet.

Här får du tio tips på hur du manuellt kan kvalitetssäkra dina arbetsböcker i Excel, till exempel via tillfälliga hjälpkolumner och i vilken ordning du bör göra det. Och ett extra bonus tips, om hur du kan automatisera alla stegen.

Börja med att kopiera underlaget till ett nytt tomt kalkylblad, så att du vid behov kan jämföra med originalet i efterhand.

Ladda ner exempelfilen här: Kvalitetssäkra kalkyler.xlsx

1. Delsummor

Kontrollera att inga totaler eller delsummor finns med i datakällan. Om totaler eller delsummor finns med kan totalsumman beräknas dubbelt.

2. Tomma rader

Radera alla eventuellt helt tomma rader. Om du använder knappen Autosumma [Autosum], finns risken att den markering programmet själv gör åt dig, stoppar vid tomma celler och alla värdena kommer inte med i summor och pivottabeller.

Tips!
Se även till att området med värden, inte innehåller sammanfogade celler.

3. Exceltabell

Konvertera området till en Exceltabell med ett bra namn, för att undvika risken att nya rader inte kommer med i framtida beräkningar, sorteringar och filtreringar av området.

4. Filter

Börja med att filtrera textkolumnen i tabellen, för att upptäcka vanliga problem, till exempel felstavningar, eller olika förkortningar av samma begrepp.

Om det bara finns några få fel, kan du snabbt rätta dem med Sök och ersätt.

Om felaktigheterna är många är det ofta effektivare att skapa en hjälptabell. Låt tabellen innehålla en kolumn med de unika felaktiga texterna och en kolumn med de korrekta versionerna. Använd sedan en leta upp-funktion i en hjälpkolumn, för att hämta rätt värde för varje rad.

När allt ser korrekt ut kan du kopiera resultaten, i hjälpkolumnen och klistra in dem som värden, över de ursprungliga uppgifterna med kortkommandot Ctrl+ Shift + V.

Tips!
Använd Dataverifiering [Data Validation] på denna typ av kolumner, för undvika möjligheten att fylla i vad som helst, framöver.

5. Fyll tomma celler

Kontrollera att inga kolumner av misstag innehåller tomma celler där värden saknas. Filtrera fram de tomma cellerna och fyll i korrekta uppgifter. I det här exemplet får tomma celler, värdet Obehandlad, så att de inte missas.

6. Extra mellanslag

Men funktionen RENSA [TRIM] i en extra hjälpkolumn, går det att städa bort inledande, dubbla och avslutande mellanslag i celler.

Mellanslag räknas också som tecken och kommer orsaka att det blir dubbla rader, i till exempel pivottabeller för samma leverantör, i detta fall.

Kopiera de städade texterna och klistra in dem som värden, över de ursprungliga uppgifterna, med kortkommandot Ctrl+ Shift + V

7. Visa formler

Använd kortkommandot Ctrl + § [Ctrl + ´] för att se formlerna bakom värdena. Då visas korrekta datum som siffror men felaktiga datum visas som text och om vissa siffor är lagrade som text, justeras de olika i cellerna. Du ser även om siffervärden är inmatade eller skapade via formler.

Konvertera felaktiga datum, genom att markera kolumnen och välj Text till kolumn [Text to Columns] på menyfliken Data [Data], gå vidare till steg tre och välj att kolumnen innehåller Datum [Date] och välj på vilket sätt datumen är uppställda. I detta exempel Dag, Månad och År för att ”vända” datumen, till ISO standarden År-Månad-Dag.

Använd Ctrl + § [Ctrl + ´] igen, för att visa datumen.

Tips!
Alternativet Visa formler [Show Formulas], finns även på menyfliken Formler [Formulas].

8. Konvertera till tal

Markera celler med siffror och klicka till på varningstriangeln, om den visas, för att få hjälp med att konvertera siffror, som Excel uppfattar som text, till tal.

Tips!
Konverteringen från text till tal, går även att göra via alternativet Text till kolumn [Text to Columns] på menyfliken Data [Data], om de små gröna trianglarna inte visas uppe till vänster i cellerna.

9. Kontrollera talen

Kontrollera gärna dubbelt att Excel verkligen uppfattar alla belopp som tal, genom att använda funktionen ÄRTAL [ISNUMBER], i en hjälpkolumn bredvid och korrigera vid behov.

10. Dubbletter

Kontrollera att det inte finns några identiska dubblettrader, i underlaget och ta i så fall bort dem via Ta bort dubbletter [Remove Duplicates] på menyfliken Tabelldesign [Table Design] eller menyfliken Data [Data].

Resultaten

I exemplet skiljer det nästan 35 000 mellan totalsummorna, före och efter städningen eller drygt 1 300, om den tomma raden med delsumman åtgärdats. Inget misstag någon vill råka ut för.

Om rapporten bara tas fram vid ett enstaka tillfälle, kan du gå igenom stegen manuellt, för att kvalitetssäkra underlaget. För återkommande rapporter är det däremot betydligt effektivare att städa med Power Query.

11. Power Query

Via Power Query-redigeraren kan du utföra tvättningen och städningen av datakällor en gång, för att nästa gång endast välja Uppdatera [Refresh] eller Uppdatera alla [Refresh all], om din kalkyl har flera datakällor.