Office Q&A: Indbyggede Excel-funktioner, der eliminerer behovet for komplekse udtryk

Mange brugere ser Excel som en enkelt-metodeløsning - komplekse udtryk, funktioner og endda arrays. Udtryk kan være vanskelige at skrive og tage timer med test og fejlfinding, hvis du ikke er velbevandret i syntaks og forventninger. Ofte kan du få de samme resultater ved hjælp af en let at implementere funktion i stedet. Denne måned viste jeg to brugere, hvordan man kan eliminere komplekse udtryk ved hjælp af indbyggede funktioner.

Hvis du vil følge med, skal du downloade .xls- eller .xlsx-filer.

Tekst til kolonner til analyse

Bruce skal parse en streng, så han kan bruge de numeriske værdier i andre beregninger. Desværre kan han ikke kontrollere, hvordan dataene kommer til ham - han sidder fast med en lang række værdier, vist i figur A, der repræsenterer dimensionen af ​​en container. Bruce har prøvet et antal parsingudtryk ved hjælp af VENSTRE (), HØJRE (), MID (), LEN () og FIND (), og det er lykkedes ham at sprænge den første værdi ved hjælp af følgende udtryk:

= LEFT (A5, FIND ( "x", A5) -1)

Han kan ikke få funktionsblandingen lige til at analysere værdierne for den anden og tredje dimension.

Figur A

Excel importerer de tre dimensioner i en streng.

Bruce arbejder meget hårdere end nødvendigt. Excels funktion til tekst til kolonner analyserer værdierne hurtigt og nemt uden udtryk. Brug Bruce's ark, lad os arbejde gennem et hurtigt eksempel.

Før du begynder, skal du beslutte, om du vil beholde den originale streng. I så fald skal du kopiere strengværdierne og arbejde med kopien, fordi tekst til kolonner skriver over den originale streng. Figur B viser resultatet af kopiering af de originale strenge i A4: A14 til et nyt ark. Dette er datasættet, som vi faktisk analyserer.

Figur B

Det er en god ide at arbejde med en kopi.

Når en kopi er på plads, skal du aktivere funktionen Tekst til kolonner som følger:

  1. Vælg A2: A12 (det passende interval, der indeholder de strenge, du vil analysere).
  2. Klik på fanen Data.
  3. I gruppen Dataværktøjer skal du klikke på Tekst til kolonner. I Excel 2003 findes denne funktion i Data-menuen.
  4. I den resulterende dialog skal du beholde standardafgrænsningen og klikke på Næste for at fortsætte (vi bruger x-tegnet som en afgrænser).
  5. Marker Andet på listen Afgrænsere.
  6. Indtast et x i kontrollen til højre ( figur C ). Dette opdaterer eksempelvisningen nedenfor, som udelader x-tegn og viser værdierne, der er parset i tre kolonner.
    Fig
  7. Klik på Næste.
  8. I den næste rude kan du specificere hver kolonnedatatype. I dette eksempel er Excel standard for Generelt ( figur D ). Behold standard ved at klikke på Udfør.
    Figur D
  9. Når (hvis) Excel beder om at bekræfte, at det skriver over de originale strenge, skal du klikke på OK. Figur E viser de analyserede resultater.
    Figur E

Da Excel indstiller datatypen til Generelt, kan du bruge værdierne i beregninger. Det er muligt at få de samme resultater ved hjælp af udtryk, men denne metode er enkel og hurtig at implementere.

Flash Fill til 2013-brugere

Hvis strengværdierne er konsistente i struktur, kan du prøve Excel 2013's nye Flash Fill-funktion. I dette særlige tilfælde kunne jeg ikke få Flash Fill til nøjagtigt at analysere alle tredimensionelle værdier. Funktionen er dog ofte nyttig, så jeg vil nævne den.

Først har du brug for et sted til at rumme de tre værdikolonner, så indsæt tre kolonner til højre for de originale strenge (i kolonne A). Gør følgende for at bruge Flash Fill til hurtigt at analysere værdierne:

  1. Til højre for de originale strenge i B4: B6 skal du manuelt indtaste de første tre værdier, 22.1, 11.33 og 113, for at indstille mønsteret.
  2. Vælg den næste tomme celle i kolonnen (B7).
  3. Klik på fanen Data.
  4. I gruppen Data Tools skal du klikke på Flash Fill ( figur F ), og Excel forsøger at oprette listen i henhold til det mønster, der er angivet i trin 1.
    Figur F
  5. For at generere den anden liste (parsing af de midterste komponenter) skal du manuelt indtaste de første tre midtværdier i C4: C6 (med dette strengsæt var jeg ikke i stand til at etablere et mønster for den anden komponent, og Flash Fill mislykkedes).
  6. Vælg C7, og klik på Flash-udfyld for at oprette den anden liste over dimensioner.
  7. Gentag denne proces for at oprette den tredje liste over dimensioner i kolonne D.

Desværre giver Flash Fill ikke altid en perfekt liste, og den undlader ikke at leve op til netop denne opgave. Det er en god funktion at prøve, og det er derfor, jeg nævnte det. Du ønsker at kontrollere Flash Fill's resultater omhyggeligt for uoverensstemmelser. Du kan lære mere om denne nye funktion ved at læse "Excel 2013 Flash Fill forudser behov på farten."

Gennemsnitlige unikke værdier

Prakash vil gennemsnit en kolonne med værdier, men kun de unikke værdier. I dette tilfælde er den (næsten) udtryk-mindre løsning enkel - opret en liste med unikke værdier ved hjælp af Fjern duplikater. (Denne funktion er ikke tilgængelig i Excel 2003; brug indstillingen Avanceret filter, der er nævnt nedenfor).

For at illustrere denne enkle indbyggede løsning bruger vi den enkle liste vist i figur G. Det er let at se duplikaterne. I den virkelige verden har du måske tusinder af rækker at overveje.

Figur G

Denne enkle liste indeholder et par duplikater.

Hvis du ikke ønsker at fjerne de originale værdier permanent (og det vil du sandsynligvis ikke), skal du kopiere værdierne og oprette en unik liste fra kopien (hvilket er, hvad jeg vil gøre). Når du har en kopi på plads, skal du oprette en liste med unikke værdier som følger:

  1. Vælg værdikolonnen. I dette tilfælde er det B2: B10 (efter at have kopieret den originale liste fra kolonne A til kolonne B).
  2. Klik på fanen Data.
  3. I gruppen Data Tools skal du klikke på Fjern duplikater.
  4. I den resulterende dialog skal du ændre standardindstillingen ved at vælge Fortsæt med den aktuelle valgmulighed ( figur H ). I dette tilfælde evaluerer standardindstillingen begge kolonner A og B sammen, når du finder duplikater. Da kolonne B er en kopi af kolonne A, ville resultatet være det samme på begge måder, men dette er et foragtet eksempel, og det vil sjældent være tilfældet. Du skal forstå forskellen mellem de to muligheder.
    Figur H
  5. Klik på Fjern duplikater.
  6. Der er intet at ændre i den næste rude, så klik på OK.
  7. Når Excel bekræfter de fjernede duplikater, skal du klikke på OK. Resultaterne er vist i figur I.
    Figur I

Excel fjernede duplikaterne, som ændrer resultatet af AVERAGE () -funktionen i B11. Funktionen i B11 evaluerer stadig B2: B10, men AVERAGE () ignorerer tomme celler.

Hvis du stadig bruger Excel 2003, skal du bruge funktionen Avanceret filter til at oprette en unik liste. Læs "Hvordan returnerer jeg en unik liste i Excel?" - specifikt den første sektion, manuelt, den lette måde, til specifikke instruktioner.

Arbejd lettere

Det er ikke altid muligt at bruge en indbygget funktion i stedet for komplicerede udtryk. Men når som helst du kæmper for at få en løsning på plads, skal du tage et minut på at revurdere din strategi. Hvad har du virkelig brug for at se? Hvordan bruger du resultaterne? Ofte tilbyder Excel funktioner, der kan give de resultater, du har brug for uden alt det hårde arbejde! Hvis du har fjernet et sæt komplekse udtryk ved hjælp af en indbygget funktion, skal du dele din oplevelse i kommentarfeltet.

Send mig dit spørgsmål om Office

Jeg besvarer læsernes spørgsmål, når jeg kan, men der er ingen garanti. Når du kontakter mig, skal du være så specifik som muligt. For eksempel "Fejlfinding i min projektmappe og rettelse af hvad der er galt" får sandsynligvis ikke noget svar, men "Kan du fortælle mig, hvorfor denne formel ikke returnerer de forventede resultater?" magt. Jeg får ikke refunderet TechRepublic for min tid eller ekspertise, og jeg beder heller ikke om et gebyr fra læserne. Du kan kontakte mig på

© Copyright 2020 | mobilegn.com