3 Komplekse Excel-ekstraktionsproblemer løst og forklaret

  • Joseph Goodman
  • 0
  • 2616
  • 217
Reklame

Mange mennesker kæmper med at udtrække oplysninger fra komplekse celler i Microsoft Excel. De mange kommentarer og spørgsmål som svar på min artikel om Sådan udpakkes et nummer eller en tekst fra Excel med denne funktion Sådan udpakkes et nummer eller en tekst fra Excel med denne funktion Sådan udpakkes et nummer eller tekst fra Excel med denne funktion Blanding af tal og tekst i et Excel-regneark kan introducere udfordringer. Vi viser dig, hvordan du ændrer formateringen af ​​dine celler og adskiller numre fra teksten. beviser det. Det er tilsyneladende ikke altid klart, hvordan man isolerer de ønskede data fra et Excel-ark.

Vi har valgt et par af spørgsmålene fra denne artikel til at gå igennem her, så du kan se, hvordan løsningerne fungerer. Læring af Excel hurtigt 8 tip til hvordan man lærer Excel hurtigt 8 tip til hvordan man lærer Excel hurtigt Ikke så behageligt med Excel, som du gerne vil? Start med enkle tip til tilføjelse af formler og styring af data. Følg denne vejledning, så kommer du hurtigt på kort tid. er ikke let, men at bruge virkelige problemer som disse hjælper meget.

1. Ekstraktion ved hjælp af en separator

Læser Adrie stillede følgende spørgsmål:

Jeg vil gerne udtrække det første sæt numre fra en liste, dvs. (122,90,84,118.4,128.9)
Eventuelle ideer til, hvilken formel jeg kan bruge?

COIL112X2.5
COIL90X2.5
COIL84X2.0
COIL118.4X1.8
COIL128.9X2.0

Det faktum, at numrene, der skal udvindes, er forskellige længder gør dette lidt mere kompliceret end bare at bruge MID-funktionen, men ved at kombinere et par forskellige funktioner kan vi slippe af med bogstaverne til venstre såvel som “x” og numrene til højre og kun efterlade de ønskede numre i en ny celle.

Her er formlen, vi bruger til at løse dette problem:

= VALUE (LEFT ((RIGHT (A1, (LEN (A1) -4))), FIND ( "X", A1) -5))

Lad os starte i midten og arbejde os ud for at se, hvordan det fungerer. Først starter vi med FIND-funktionen. I dette tilfælde bruger vi FIND (“x”,A1). Denne funktion ser gennem teksten i celle A1 for bogstavet X. Når den finder et X, returnerer den den position, den er i. For den første post, COIL112X2.5, for eksempel returnerer den 8. For den anden post, den vender tilbage 7.
Lad os derefter kigge på LEN-funktionen. Dette returnerer blot længden på strengen i cellen minus 4. Ved at kombinere den med RIGHT-funktionen får vi strengen fra cellen minus de første fire tegn, der fjerner “COIL” fra begyndelsen af ​​hver celle. Formlen til denne del ser sådan ud: HØJRE (A1, (LEN (A1) -4)).

Det næste niveau ud er VENSTRE funktion. Nu hvor vi har bestemt X-positionen med FIND-funktionen og fjernet “COIL” med HØJRE-funktionen returnerer LEFT-funktionen, hvad der er tilbage. Lad os nedbryde dette lidt længere. Her er hvad der sker, når vi forenkler disse to argumenter:

= VENSTRE ("112X2,5", (8-5))

VENSTRE-funktionen returnerer de tre mest venstre tegn i strengen ( “-5” i slutningen af ​​argumentet sikrer du, at det rigtige antal tegn fjernes ved at bogføre de første fire tegn i strengen).

Endelig sikrer VALUE-funktionen, at det returnerede nummer formateres som et nummer i stedet for som tekst. Dette eksempel er ikke så sjovt som at opbygge et arbejdsspil med Tetris i Excel 7 Sjove og underlige ting, du kan oprette med Microsoft Excel 7 Sjove og underlige ting, du kan oprette med Microsoft Excel Forestil dig, at Excel var sjovt! Excel tilbyder masser af muligheder for projekter, der går ud over dens tilsigtede brug. Den eneste grænse er din fantasi. Her er de mest kreative eksempler på, hvordan folk bruger Excel. , men det tjener som et godt eksempel på, hvordan man kombinerer et par funktioner til at løse et problem.

2. Træk numre ud af blandede strenge

Et lignende spørgsmål blev stillet af læseren Yadhu Nandan:

Jeg vil gerne vide, hvordan man skiller numeriske og alfabeter.

Eksempel er - 4552dfsdg6652sdfsdfd5654

Jeg vil have 4552 6652 5654 i forskellige celler

En anden læser, Tim K SW, leverede den perfekte løsning på netop dette problem:

Forudsat at 4552dfsdg6652sdfsdfd5654 er i A1, og du vil have, at disse numre udvindes i 3 forskellige celler. 4452 i nævnt celle B1 og 6652 i C1 og 5654 i D1 vil du bruge disse.

B1:
= MID (A1,1,4)

C1:
= MID (A1,10,4)

D1:
= MID (A1,21,4)

Formlerne er forholdsvis enkle, men hvis du ikke er bekendt med MID-funktionen, forstår du muligvis ikke, hvordan den fungerer. MID tager tre argumenter: en celle, det karakternummer, hvor resultatet starter, og antallet af tegn, der skal trækkes. MID (A1,10,4), for eksempel, beder Excel om at tage fire tegn, der starter ved det tiende tegn i strengen.

Brug af de tre forskellige MID-funktioner i tre celler trækker numrene ud af denne lange streng med tal og bogstaver. Naturligvis fungerer denne metode kun, hvis du altid har det samme antal tegn - både bogstaver og tal - i hver celle. Hvis antallet af hver varierer, har du brug for en meget mere kompliceret formel.

3. Få et nummer fra en blandet streng med mellemrum

En af de vanskeligste anmodninger, der blev sendt på artiklen, var denne fra læseren Daryl:

Hej, jeg vil bare spørge, hvordan jeg adskiller meget uformateret post som:

Rp. 487.500 (Netto 50% FRA)
Rp 256.500 Nett 40% FRA
Rp99.000
Rp 51.000 / orang nett (50% rabat)

Jeg brugte nogen tid på at arbejde på denne, og var ikke i stand til selv at komme med en løsning, så jeg tog til Reddit. Bruger UnretiredGymnast leverede denne formel, der er lang og meget kompleks:

= SUMPRODUCT (MID (0 & VENSTRE (A1, IFERROR (SEARCH ( "%", A1) -4, LEN (A1))), LARGE (INDEX (ISNUMBER (- MID (LEFT (A1, IFERROR (SEARCH ( "%" , A1) -4, LEN (A1))), RÆKKE ($ 1: $ 99), 1)) * RÆKKE ($ 1: $ 99), 0), RÆKKE ($ 1: $ 99)) + 1,1) * 10 ^ ROW ($ 1: $ 99) / 10)

Som du kan se, tager det en lang tid at afkode denne formel og kræver en temmelig solid viden om en masse Excel-funktioner 16 Excel-formler, der hjælper dig med at løse virkelige problemer 16 Excel-formler, der hjælper dig med at løse virkelige problemer Det rigtige værktøj er halvdelen af ​​arbejdet. Excel kan løse beregninger og behandle data hurtigere, end du kan finde din lommeregner. Vi viser dig centrale Excel-formler og demonstrerer, hvordan du bruger dem. . For at hjælpe med at finde ud af, hvad der sker her, skal vi se på et par funktioner, som du måske ikke er bekendt med. Den første er IFERROR, der får en fejl (normalt repræsenteret med et pundskilt, som # N / A eller # DIV / 0) og erstatter det med noget andet. I ovenstående ser du IFERROR (SØG (“%”,A1) -4, LEN (A1)). Lad os nedbryde dette.

IFERROR ser på det første argument, der er SØG (“%”,A1) -4. Så hvis “%” vises i celle A1, dens placering returneres, og fire trækkes fra den. Hvis “%” gør ikke vises i strengen, Excel opretter en fejl, og længden på A1 returneres i stedet.

De andre funktioner, som du måske ikke er bekendt med, er lidt enklere; SUMPRODUCT multipliserer for eksempel og tilføjer derefter elementer af arrays. LARGE returnerer det største antal i et interval. ROW kombineret med et dollartegn giver en absolut henvisning til en række.

Det er ikke let at se, hvordan alle disse funktioner fungerer sammen, men hvis du starter midt i formlen og arbejder udad, vil du begynde at se, hvad det gør. Det vil tage et stykke tid, men hvis du er interesseret i at se nøjagtigt, hvordan det fungerer, anbefaler jeg at sætte det i et Excel-ark og lege med det. Det er den bedste måde at få en idé om, hvad du arbejder med.

(Desuden er den bedste måde at undgå et problem som dette at importere dine data mere pænt. Sådan importeres data til dine Excel-regneark den pæne og lette måde Sådan importeres data til dine Excel-regneark den pæne og lette måde Har du nogensinde kæmpet med importerer eller eksporterer data til et regneark? Denne tutorial hjælper dig med at mestre kunsten at flytte data mellem Microsoft Excel, CSV, HTML og andre filformater. - Det er ikke altid en mulighed, men det skal være dit første valg, når det er. )

Et skridt ad gangen

Som du kan se, er den bedste måde at løse ethvert Excel-problem et trin ad gangen: start med det, du ved, se hvad det får dig, og gå derfra. Nogle gange ender du med en elegant løsning, og nogle gange får du noget, der er virkelig langt, rodet og komplekst. Men så længe det fungerer, er det lykkedes dig!

Og glem ikke at bede om hjælp Har du brug for at lære Excel? 10 eksperter lærer dig gratis! Brug for at lære Excel? 10 eksperter lærer dig gratis! Det kan være svært at lære, hvordan man bruger Excel's mere avancerede funktioner. For at gøre det lidt lettere har vi sporet de bedste Excel-guruer, der kan hjælpe dig med at mestre Microsoft Excel. . Der er nogle ekstremt talentfulde Excel-brugere derude, og deres hjælp kan være uvurderlig til at løse et hårdt problem.

Har du nogle forslag til løsning af hårde ekstraktionsproblemer? Kender du til andre løsninger på de problemer, vi har løst ovenfor? Del dem og eventuelle tanker i kommentarerne nedenfor!




Endnu ingen kommentarer

Om moderne teknologi, enkel og overkommelig.
Din guide i en verden af moderne teknologi. Lær hvordan du bruger de teknologier og gadgets, der omgiver os hver dag, og lær, hvordan du finder interessante ting på Internettet.