Sådan rydder du op i data i Excel med formler

Næsten ingen data kommer ind i Excel klar til brug. En kundeliste eksporteret fra et CRM-system har navne med store bogstaver, koder med mellemrum til sidst og telefonnumre i fem forskellige formater. En rapport fra økonomisystemet har tal, som SUM nægter at lægge sammen. En tilmeldingsformular har den samme person med tre gange. Før du kan summere, slå op eller lave en opsummering, skal der ryddes op.

Du kan rydde op i hånden, én celle ad gangen, og til ti rækker er det fint. Til tusind rækker, eller til en eksport, du får igen næste måned, er formler hurtigere, mere pålidelige og til at gentage. Denne guide gennemgår de oprydningsopgaver, der dukker op oftest, formlerne, der løser hver af dem, og en rækkefølge at gøre det i, så du ikke skaber nye problemer, mens du løser de gamle.

Før du går i gang: behold originalen

Den første regel for oprydning er, at du ikke skriver hen over de oprindelige data. Lad den rå eksport være i fred, enten på sit eget ark eller i de oprindelige kolonner, og ryd op i hjælpekolonner ved siden af: en kolonne med formler, der læser den rodede værdi og returnerer den rene.

Hjælpekolonner har tre fordele frem for at rette direkte i cellerne:

• Du kan se, hvad der er ændret. Den rå og den rene værdi står side om side, så du kan tjekke enhver række.
• Intet går tabt. Viser en formel sig at være forkert, retter du den og kopierer den ned igen. Originalen er der stadig.
• Det virker igen. Næste måned sætter du den nye eksport ind i de rå kolonner, og hjælpekolonnerne rydder op på samme måde.

Når du er tilfreds med resultatet, kan du lave formlerne om til faste værdier med Indsæt speciel og vælge Værdier, og derefter slette de rå kolonner, hvis du ikke har brug for dem længere. Gør det til sidst, ikke først.

Find problemerne først

Du kan ikke rette det, du ikke har set, og rodede data ser tit pæne ud. Et par hurtige tjek viser dig, hvad du har med at gøre:

• Åbn filterlisten. Slå filtre til med Ctrl+Shift+L, og åbn rullelisten på en kolonne med kategorier. Hver stavemåde står for sig, så "København", "København " og "københavn" viser sig som tre forskellige.
• Tæl tegnene. LÆNGDE returnerer antallet af tegn i en celle, mellemrum medregnet. En værdi, der er længere, end den ser ud, har skjulte mellemrum:

• Se på justeringen. Medmindre nogen har ændret den, står tal og datoer i højre side af cellen og tekst i venstre side. En kolonne med beløb, hvor nogle få står til venstre, har tal gemt som tekst.
• Sammenlign TÆL med TÆLV. På en kolonne, der kun skulle indeholde tal, tæller =TÆL(B2:B500) tallene, og =TÆLV(B2:B500) tæller alt, der ikke er tomt. Er de forskellige, er nogle af værdierne tekst.
• Tæl de tomme. =ANTAL.BLANKE(B2:B500) fortæller dig, hvor mange værdier der mangler. Opgaven med ANTAL.BLANKE træner det.

Fem minutters tjek fortæller dig, hvilke af afsnittene nedenfor du har brug for.

Overflødige mellemrum: FJERN.OVERFLØDIGE.BLANKE

Overflødige mellemrum er det mest almindelige problem i eksporterede data og den mest almindelige grund til, at et opslag giver #I/T for en værdi, du kan se. "A-104 " med et mellemrum til sidst matcher ikke "A-104".

FJERN.OVERFLØDIGE.BLANKE fjerner alle mellemrum i starten og slutningen af en tekst og gør flere mellemrum mellem ord til ét:

Øv det i fjern overflødige mellemrum. Du kan også sætte funktionen ind i en anden formel i stedet for i en hjælpekolonne, for eksempel =LOPSLAG(FJERN.OVERFLØDIGE.BLANKE(E1);A2:C50;3;FALSK), når det kun er værdien, du slår op, der er rodet.

To slags "mellemrum" fjerner funktionen ikke:

• Hårde mellemrum, der kommer fra hjemmesider og nogle systemer. De ser helt ud som et almindeligt mellemrum, men de er et andet tegn, nummer 160. Lav dem om til et almindeligt mellemrum først, og fjern derefter de overflødige: =FJERN.OVERFLØDIGE.BLANKE(UDSKIFT(A2;TEGN(160);" ")).
• Linjeskift og andre usynlige tegn, der kommer fra tekst kopieret fra mails eller formularfelter med flere linjer. RENS fjerner dem: =FJERN.OVERFLØDIGE.BLANKE(RENS(A2)).

Ser funktionen ikke ud til at virke, kan LÆNGDE før og efter fortælle dig, om en af de to er årsagen.

Store og små bogstaver: STORT.FORBOGSTAV, STORE.BOGSTAVER og SMÅ.BOGSTAVER

Eksporter har ofte navne med store bogstaver eller med den tilfældige blanding, folk nu har tastet. Tre funktioner retter det:

• STORT.FORBOGSTAV giver hvert ord stort forbogstav og gør resten små: mette bruun bliver til Mette Bruun.
• STORE.BOGSTAVER gør alt stort, og det passer til koder og ID'er.
• SMÅ.BOGSTAVER gør alt småt, og det passer til mailadresser.

De passer godt sammen med FJERN.OVERFLØDIGE.BLANKE. Sæt den ind inderst, så mellemrummene er væk, før bogstaverne bliver rettet:

STORT.FORBOGSTAV er ikke perfekt til navne. Den laver anne-mette om til Anne-Mette, hvilket er rigtigt, men også LARS VON TRIER om til Lars Von Trier og MCDONALD'S om til Mcdonald'S, fordi den giver stort bogstav efter hver bindestreg, apostrof og mellemrum. Til en kundeliste er det som regel godt nok; til alt, der skal trykkes på et kursusbevis eller en kuvert, så tjek undtagelserne i hånden. Øv funktionerne i ret navne med STORT.FORBOGSTAV og ryd op i en kode med STORE.BOGSTAVER.

Del tekst op på faste pladser: VENSTRE, HØJRE og MIDT

Koder har ofte en struktur: De første tre tegn er en afdeling, de sidste fire et årstal, og et varenummer står i midten. Når pladserne altid er de samme, skærer tre funktioner teksten op:

• VENSTRE tager tegn fra starten: =VENSTRE(A2;3).
• HØJRE tager tegn fra slutningen: =HØJRE(A2;4).
• MIDT tager tegn fra midten, fra en startplads: =MIDT(A2;5;4) tager fire tegn fra og med det femte.

De tre opgaver med VENSTRE, HØJRE og MIDT starter med præfikset og fortsætter med de sidste cifre og et årstal midt i teksten.

Én ting skal du vide: Alle tre returnerer tekst, også når tegnene er cifre. =HØJRE(A2;4) på KBH-2044 returnerer teksten "2044", ikke tallet. Skal du regne med det, så pak det ind i VÆRDI: =VÆRDI(HØJRE(A2;4)).

Del tekst op ved et tegn: FIND og SØG

Navne, mailadresser og mange koder har ikke faste pladser. Fornavnet er alt før det første mellemrum, uanset hvor det står; domænet er alt efter @. Her finder du først placeringen af tegnet og skærer derefter ved det.

FIND returnerer placeringen af én tekst i en anden: =FIND("@";A2) er 12 for [email protected], fordi @ er det 12. tegn. Kombinér den med VENSTRE for at tage alt før tegnet:

-1 er vigtig: Uden den ville VENSTRE tage mellemrummet med. Vil du have alt efter et tegn, så kombinér FIND med MIDT, og start ét tegn senere:

De 100 betyder bare "flere tegn, end der kan være". MIDT stopper ved tekstens slutning, så ethvert tal, der er stort nok, virker. Det er nogle af de mest brugte tekstformler i kontorarbejde, og opgaverne find @ i en mailadresse, navnet før @ og domænet efter @ tager dig gennem dem i den rækkefølge.

SØG gør det samme som FIND, men skelner ikke mellem store og små bogstaver og forstår jokertegn. Brug FIND, når tegnet er præcist, som et mellemrum, en bindestreg eller et @, og SØG, når du leder efter et ord, der kan være skrevet med store eller små bogstaver.

TEKSTFØR og TEKSTEFTER

Excel til Microsoft 365 har to funktioner, der finder og skærer i ét trin: =TEKSTFØR(A2;" ") returnerer teksten før det første mellemrum, og =TEKSTEFTER(A2;"@") teksten efter @. De er lettere at læse og lettere at få rigtige. Ulempen er den samme som for alle nye funktioner: Ældre versioner viser #NAVN?. Øv dem i hent fornavnet med TEKSTFØR og vend "Efternavn, Fornavn" med TEKSTEFTER.

Del op uden formler

Til en oprydning, du kun skal lave én gang, kan to indbyggede værktøjer være hurtigere end formler:

• Tekst til kolonner (fanen Data) deler en kolonne ved et tegn, for eksempel et komma eller et mellemrum, ud i flere kolonner i én omgang.
• Hurtigudfyld (Ctrl+E) ser dig skrive resultatet for den første række eller to og udfylder resten ved at kopiere mønsteret.

Begge ændrer data én gang. De opdaterer ikke, når kilden ændrer sig, og Hurtigudfyld kan gætte mønsteret forkert på usædvanlige rækker, så tjek resultatet. Til data, du rydder op i hver måned, er formler den bedste investering.

Erstat og fjern tegn: UDSKIFT

UDSKIFT erstatter hver forekomst af én tekst med en anden. Erstat den med ingenting, "", så fjerner den tegnene:

Øv det i fjern bindestregerne med UDSKIFT. Skal du fjerne flere forskellige tegn, så sæt én UDSKIFT ind i en anden: =UDSKIFT(UDSKIFT(A2;"(";"");")";"") fjerner begge slags parenteser.

UDSKIFT skelner mellem store og små bogstaver og erstatter alle forekomster, medmindre du giver den et fjerde argument for, hvilken forekomst der skal erstattes. Søg og erstat gør det samme uden en formel direkte i cellerne, så brug det på en kopi.

Saml tekst: & og TEKST.KOMBINER

Oprydning handler lige så ofte om at sætte tekst sammen som om at dele den op: et fulde navn af for- og efternavn, en adresse på én linje eller en nøgle bygget af to kolonner til et opslag.

Operatoren & sætter tekster sammen: =A2&" "&B2 sætter et mellemrum mellem fornavn og efternavn. Øv det i sæt for- og efternavn sammen med &. Den er fin til to eller tre dele. Til flere dele, eller når nogle af dem kan være tomme, er TEKST.KOMBINER bedre. Den tager et skilletegn, SAND eller FALSK for, om tomme celler skal springes over, og de celler, der skal sættes sammen:

Med & ville den tomme etage have efterladt et dobbelt komma: "Nørregade 12, , 5000 Odense C". Opgaven med adressen træner TEKST.KOMBINER med en tom del.

Tal gemt som tekst

Det er det dyreste dataproblem, fordi det giver forkerte totaler uden nogen fejl. Systemer eksporterer tit tal som tekst, og det samme sker, når du kopierer fra en hjemmeside eller en PDF. Cellen viser 350, men Excel behandler det som et ord:

Tegn på, at en kolonne har tal gemt som tekst: Værdierne står i venstre side af cellen, der er en lille grøn trekant i cellens hjørne, TÆL er lavere end TÆLV, og SUM, SUM.HVIS og opslag giver resultater, der er en smule ved siden af.

Sådan retter du dem:

• Advarselsmenuen. Markér cellerne med den grønne trekant, klik på advarselsikonet, og vælg Konvertér til tal. Hurtigt til én kolonne.
• Tekst til kolonner. Markér kolonnen, vælg Data > Tekst til kolonner, og klik på Udfør uden at ændre noget. Excel læser hver værdi igen og laver dem, der ligner tal, om til tal.
• VÆRDI i en hjælpekolonne. =VÆRDI(B3) returnerer tallet. Den virker også på tekst, der først skal renses, for eksempel =VÆRDI(FJERN.OVERFLØDIGE.BLANKE(B3)).
• En beregning. At gange med 1 eller lægge 0 til laver også et teksttal om, så =B3*1 virker, men VÆRDI siger tydeligere, hvad du mener.

Hold øje med decimaltegnene. Tekst som 1,250.50 fra et engelsk eller amerikansk system er ikke et tal for dansk Excel, og VÆRDI giver #VÆRDI!. Byt om på skilletegnene med UDSKIFT først, eller importér filen med den rigtige landestandard.

Datoer har samme problem og de samme tegn. Vores guide til datoer i Excel viser, hvordan du bygger en tekstdato op igen med DATO, VENSTRE, MIDT og HØJRE.

Dubletter

Den samme kunde to gange, den samme faktura importeret to gange, den samme mailadresse tilmeldt via tre formularer. Dubletter puster antal og totaler op, og et opslag på en nøgle, der står flere gange, returnerer det første match uden at advare dig.

Den sikreste måde at finde dem på er en hjælpekolonne med TÆL.HVIS, der tæller, hvor mange gange hver værdi står i hele kolonnen:

$-tegnene holder området fast, når du kopierer ned, mens A4 flytter sig til hver række. Filtrér hjælpekolonnen efter værdier over 1, så ser du alle dubletter med resten af rækken ved siden af og kan beslutte, hvilken du vil beholde.

To andre værktøjer:

• Data > Fjern dubletter sletter dubletrækker på stedet og beholder den første. Det er hurtigt og permanent, så brug det på en kopi, og tjek det antal, Excel melder tilbage.
• ENTYDIGE i Excel 365 returnerer listen uden dubletter som et nyt område: =ENTYDIGE(A2:A500). Den er ideel til at lave en ren liste med kategorier eller kunder at opsummere efter.

Ryd op i kolonnen, før du leder efter dubletter. "[email protected]" og "[email protected] " er ikke dubletter for TÆL.HVIS, før FJERN.OVERFLØDIGE.BLANKE og SMÅ.BOGSTAVER har gjort dem ens.

Kategorier, der ikke stemmer overens

En kolonne med "Kbh.", "København" og "Kbh" er tre forskellige byer for SUM.HVIS og en pivottabel. FJERN.OVERFLØDIGE.BLANKE og STORT.FORBOGSTAV retter mellemrum og bogstaver, men de kan ikke vide, at Kbh. betyder København.

Til det laver du en lille oversættelsestabel: alle varianterne i én kolonne og det rene navn i den næste. Et opslag erstatter så hver variant med det rene navn: =XOPSLAG(FJERN.OVERFLØDIGE.BLANKE(B2);$H$2:$H$20;$I$2:$I$20;B2). Det sidste argument returnerer den oprindelige værdi, når den ikke står i tabellen, så nye varianter dukker op i stedet for at forsvinde. Hver gang filterlisten viser en ny stavemåde, tilføjer du den til tabellen. Vores guide til opslag forklarer XOPSLAG og alternativerne.

En rækkefølge, der virker

Rækkefølgen betyder noget, fordi nogle trin afhænger af andre. Dubletter kan ikke findes, før mellemrummene er væk, og tal kan ikke laves om, mens de stadig indeholder valutategn. Den her rækkefølge virker til de fleste eksporter:

1. Kopiér de rå data til deres eget ark, og lad dem være i fred.
2. Fjern usynlige tegn og overflødige mellemrum med RENS, UDSKIFT for TEGN(160) og FJERN.OVERFLØDIGE.BLANKE.
3. Ret bogstaverne med STORT.FORBOGSTAV, STORE.BOGSTAVER eller SMÅ.BOGSTAVER.
4. Del kolonner op, der indeholder mere end én ting, for eksempel fulde navne, koder med flere dele eller postnummer og by i samme celle.
5. Lav tal og datoer gemt som tekst om til rigtige tal og datoer.
6. Ensret kategorier til én stavemåde med en oversættelsestabel.
7. Find og håndtér dubletter, nu hvor ens værdier faktisk er ens.
8. Tjek resultatet: TÆL mod TÆLV, en total mod kildesystemet og et par rækker i hånden.
9. Sæt ind som værdier, hvis de rensede data skal videre et andet sted hen, og gem projektmappen med formlerne til næste gang.

Oprydning i Excel-test

Oprydning i data er en fast del af test til stillinger som analytiker, administrativ medarbejder og i drift, fordi det er det, de job går ud på hver dag. Typiske opgaver:

• "Fjern de overflødige mellemrum fra navnene i kolonne A." FJERN.OVERFLØDIGE.BLANKE i en hjælpekolonne, kopieret ned.
• "Vis alle navne med stort forbogstav." STORT.FORBOGSTAV, ofte med FJERN.OVERFLØDIGE.BLANKE inderst.
• "Hent fornavnet" eller "domænet fra hver mailadresse". VENSTRE eller MIDT med FIND, eller TEKSTFØR og TEKSTEFTER, hvis testens Excel har dem.
• "Lav et ID af regionskoden og ordrenummeret." & eller TEKST.KOMBINER.
• "Hvorfor er den her total forkert?" Tal gemt som tekst eller en dubletrække. Tjek med TÆL, TÆLV og TÆL.HVIS.

Test tjekker som regel hver række, og de rodede rækker er sat ind med vilje: et navn uden mellemrum, en mailadresse med store bogstaver eller en kode, der er ét tegn kortere end de andre. Før du går videre, så se på yderpunkterne i dit resultat, ikke kun den første række. Testsiderne nedenfor har oprydningsspørgsmål i det format, testen til dataanalyse bruger.

Opsummering

Ryd op i hjælpekolonner ved siden af de oprindelige data, så du kan se hver ændring og gentage den næste måned. Find problemerne først med filtre, LÆNGDE, TÆL og TÆLV. FJERN.OVERFLØDIGE.BLANKE fjerner mellemrum, STORT.FORBOGSTAV, STORE.BOGSTAVER og SMÅ.BOGSTAVER retter bogstaverne, VENSTRE, HØJRE og MIDT skærer på faste pladser, og FIND eller SØG fortæller dig, hvor du skal skære, når pladserne varierer. UDSKIFT fjerner tegn, & og TEKST.KOMBINER sætter tekst sammen, og VÆRDI laver tekst om til tal. Find dubletter med TÆL.HVIS, når værdierne er rene, og sæt kun ind som værdier til allersidst.

Opgaverne nedenfor gennemgår hver af dem på små regneark, der med vilje er rodede, i den rækkefølge guiden bruger dem.