Datarensning i Excel: sådan rydder du op i rodet data

Du åbner eksporten fra systemet. 4.000 rækker. Navne med et ekstra mellemrum bagi, datoer der er blevet til tekst, beløb med både komma og punktum, og en kolonne hvor halvdelen står med STORE BOGSTAVER. Du havde regnet med at være færdig inden frokost.

Så går der halvanden time med at rette i hånden. Og næste måned kommer den samme fil igen. Med de samme fejl.

Datarensning er den mest undervurderede disciplin i Excel. Ikke fordi den er svær, men fordi de fleste aldrig har lært andet end at rette det, de kan se. Her får du rækkefølgen, der forhindrer dig i at ødelægge dine egne data undervejs, de funktioner der klarer størstedelen af arbejdet, og den metode der gør oprydningen til noget, du laver én gang og genbruger hver eneste måned.

Rodet er ikke din skyld

Data bliver rodet af tre grunde, og ingen af dem handler om dig. Systemet, der eksporterer, er bygget til at gemme data, ikke til at aflevere dem pænt. Menneskene, der har tastet, har gjort det på hver deres måde gennem fem år. Og Excel selv har en kedelig vane med at gætte: et varenummer bliver til en dato, et telefonnummer mister sit nul foran, og et decimaltal skifter betydning, fordi filen kom fra et engelsksproget system.

Størrelsen gør det heller ikke lettere. Et enkelt regneark kan rumme 1.048.576 rækker og 16.384 kolonner, og hver celle kan indeholde op til 32.767 tegn. Der er med andre ord rigeligt med plads til at gemme rod, længe før nogen opdager det.

Derfor er den første færdighed ikke en formel. Det er at kigge på data, før du rører dem.

Ryd op i den rigtige rækkefølge

De fleste går i gang med den fejl, de lige har set. Det er derfor oprydningen tager så lang tid: du retter noget i kolonne D, som en senere handling i kolonne B alligevel laver om. Rækkefølgen betyder mere end funktionerne.

Punkt 6 er værd at dvæle ved. Hvis du fjerner dubletter, før du har renset mellemrum og store bogstaver, ser Excel Jens Hansen og jens hansen som to forskellige personer. Så beholder du begge, og dit tal er forkert hele vejen ned.

De funktioner, der gør mest arbejde

Du behøver ikke kunne hundrede funktioner. Du skal kunne syv, og du skal kunne dem godt. Danske Excel-installationer bruger danske navne, så her står begge dele:

• FJERN.OVERFLØDIGE.BLANKE (TRIM) fjerner mellemrum foran, bagved og dobbelte mellemrum inde i teksten. Den løser flere fejl end nogen anden enkeltfunktion.
• RENS (CLEAN) fjerner tegn, der ikke kan udskrives. De opstår typisk, når data har været igennem et ældre system eller en pdf.
• UDSKIFT (SUBSTITUTE) bytter ét tegn eller én tekststump ud med en anden. Perfekt til punktummer, der skal være kommaer, og til at fjerne bindestreger i cvr-numre.
• STORT.FORBOGSTAV (PROPER), STORE.BOGSTAVER (UPPER) og SMÅ.BOGSTAVER (LOWER) ensretter, hvordan navne og koder ser ud.
• VÆRDI (VALUE) laver tal, der står som tekst, om til rigtige tal, så du kan regne med dem.
• TEKST (TEXT) gør det modsatte og er din ven, når et varenummer skal beholde sine nuller foran.
• LOPSLAG (VLOOKUP) eller et opslag med indeks og sammenligning kobler de rensede data sammen med resten af din verden.

En ting ad gangen i hver sin hjælpekolonne. Det føles langsomt, men det er den eneste måde, du kan se, hvad der gik galt, når resultatet ser mærkeligt ud. På et Excel-kursus er det netop rækkefølgen og fejlfindingen, der giver mest på kontoen bagefter, ikke antallet af funktioner du kan remse op.

Når tal ikke er tal

Kender du følelsen af at lægge en kolonne sammen og få nul? Så står tallene som tekst. Det ses på, at de ligger til venstre i cellen i stedet for til højre, og ofte på en lille grøn trekant i hjørnet. Løsningen er sjældent at formatere cellen om, for formatering ændrer ikke indholdet. Du skal konvertere, enten med VÆRDI, med Tekst til kolonner, eller ved at gange med 1.

Lynudfyld og Fjern dubletter: to knapper, mange timer

Lynudfyld (Flash Fill) er den funktion, folk bliver glade for. Du skriver det ønskede resultat i den første celle ved siden af dine data, og Excel gætter mønstret for resten. Fornavn ud af et fuldt navn, domæne ud af en mailadresse, postnummer ud af en adresse. Det tager ti sekunder og erstatter en formel, du ellers skulle have bygget.

Fjern dubletter ligger på fanen Data og er lige så enkel. Vær opmærksom på, at den sletter for altid i det ark, du står i. Derfor trin 1 i tabellen ovenfor.

Begge dele har en begrænsning: de er engangshandlinger. Kommer filen igen næste måned, starter du forfra.

Power Query: oprydningen der husker sig selv

Her ligger den største tidsgevinst, og det er også her, de fleste aldrig når hen. Power Query, som hedder Hent og transformer i menuen, optager dine oprydningstrin som en opskrift. Næste gang filen kommer, trykker du opdater, og hele rensningen kører igen på de nye data.

Det betyder, at den time du bruger i dag, er den sidste time du bruger på den fil. Ikke den første af tolv. Power Query håndterer også de opgaver, der er klodsede med formler: sammenlægning af flere filer fra samme mappe, opdeling af kolonner med skiftende antal værdier, og transformation af et layout med måneder ud ad siden til en rigtig tabel med én række pr. observation.

Det er trinnet fra at kunne Excel til at bruge Excel som værktøj, og det er kernen i Videregående Excel. Skal du længere ned i motorrummet med datamodeller og mere komplekse beregninger, ligger Avanceret Excel et niveau over.

Sådan kommer du i gang

1. Find den fil, du håndterer oftest. Ikke den værste, men den mest gentagne. Gevinsten ligger i frekvensen, ikke i rodet.
2. Skriv ned, hvad du retter i hånden. Fem minutter med en blyant giver dig listen over de trin, du skal automatisere. Du bliver overrasket over, hvor kort den er.
3. Byg rensningen én gang med hjælpekolonner. Én operation pr. kolonne, i rækkefølgen fra tabellen ovenfor. Tjek resultatet mod rådata, før du går videre.
4. Flyt den samme rensning ind i Power Query. Samme trin, samme rækkefølge, men nu gemt som en opskrift, der kan genbruges.
5. Mål tiden næste gang. Det er det tal, du skal bruge, hvis du på et tidspunkt skal forklare din chef, hvorfor et kursus var en god idé.

Næste skridt

Datarensning er ikke det mest prestigefyldte, du kan lave i et regneark. Det er bare det, der afgør, om alt det andet virker. Et forkert tal i en ledelsesrapport kan altid spores tilbage til en kolonne, ingen fik renset.

Vil du have systematikken ind i fingrene, starter du på Excel-kurset og tager Videregående Excel, når rensningen skal kunne genbruges. Skal flere i afdelingen løftes ad gangen, giver Excel-uddannelsen det samlede forløb.