3 Komplekse Excel-ekstraksjonsproblemer løst og forklart

Mange mennesker sliter med å utvinne informasjon fra komplekse celler i Microsoft Excel. De mange kommentarene og spørsmålene som svar på artikkelen min om hvordan å trekke ut et tall eller en tekst fra Excel med denne funksjonen. Hvordan trekke ut et tall eller en tekst fra Excel med denne funksjonen. Hvordan trekke ut et tall eller en tekst fra Excel med denne funksjonen. tekst i et Excel-regneark kan innføre utfordringer. Vi viser deg hvordan du endrer formatering av cellene dine og separate tall fra tekst. Les mer viser det. Tilsynelatende er det ikke alltid klart hvordan man kan isolere de ønskede dataene fra et Excel-ark.

Vi har valgt noen av spørsmålene fra denne artikkelen til å gå gjennom her, slik at du kan se hvordan løsningene fungerer. Lære Excel raskt 8 tips for hvordan du lærer Excel raskt 8 tips for hvordan du lærer Excel raskt Ikke så komfortabel med Excel som du vil? Start med enkle tips for å legge til formler og administrere data. Følg denne veiledningen, og du blir raskere på kort tid. Les mer er ikke lett, men å bruke virkelige problemer som disse hjelper mye.

1. Ekstraksjon ved hjelp av en separator

Reader Adrie spurte følgende spørsmål:

Jeg vil gjerne trekke ut det første settet av tall fra en liste, dvs. (122,90,84,118,4,128,9)
Eventuelle ideer om hvilken formel jeg kan bruke?

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

Det faktum at tallene som skal hentes ut, er forskjellige lengder, gjør dette litt mer komplisert enn å bare bruke MID-funksjonen, men ved å kombinere noen forskjellige funksjoner, kan vi kvitte seg med bokstavene til venstre, så vel som “X” og tallene til høyre, forlater bare de ønskede tallene i en ny celle.

Her er formelen vi skal bruke for å løse dette problemet:

= VERDI (VENSTRE ((HØYRE (A1, (LEN (A1) -4))), FINN ( "X", A1) -5))

La oss starte i midten og jobbe oss ut for å se hvordan det fungerer. Først begynner vi med FIND-funksjonen. I dette tilfellet bruker vi FIND (“X”,A1). Denne funksjonen ser gjennom teksten i celle A1 for bokstaven X. Når den finner en X, returnerer den posisjonen den er i. For den første oppføringen, COIL112X2.5, for eksempel, returnerer den 8. For den andre oppføringen, det returnerer 7.
La oss se på LEN-funksjonen. Dette returnerer bare lengden på strengen i cellen minus 4. Kombinere det med RIGHT-funksjonen, får vi strengen fra cellen minus de fire første tegnene, som fjerner “SPOLE” fra begynnelsen av hver celle. Formelen for denne delen ser slik ut: RIGHT (A1, (LEN (A1) -4)).

Neste nivå ut er LEFT-funksjonen. Nå som vi har bestemt posisjonen til X med FIND-funksjonen og blitt kvitt “SPOLE” Med RIGHT-funksjonen returnerer VENSTRE-funksjonen det som er igjen. La oss bryte ned dette litt lenger. Her er hva som skjer når vi forenkler disse to argumentene:

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

VENSTRE-funksjonen returnerer de tre lengste tegnene i strengen (den “-5” på slutten av argumentet sikrer at riktig antall tegn blir eliminert ved å regne med de første fire tegnene i strengen).

Endelig sikrer VALUE-funksjonen at nummeret som returneres er formatert som et tall, i stedet for som tekst. Dette eksemplet er ikke så gøy som å bygge et arbeidsspill av Tetris i Excel 7 Morsomme og rare ting du kan lage med Microsoft Excel 7 Morsomme og rare ting du kan lage med Microsoft Excel Imagine Excel var morsomt! Excel tilbyr rikelig med muligheter for prosjekter som går utover den tilsiktede bruken. Den eneste grensen er fantasien din. Her er de mest kreative eksemplene på hvordan folk bruker Excel. Les mer, men det tjener som et godt eksempel på hvordan du kan kombinere noen få funksjoner for å løse et problem.

2. Trekke tall ut av blandede strenger

Et lignende spørsmål ble spurt av leseren Yadhu Nandan:

Jeg vil vite hvordan å skille tall og alfabeter.

Eksempel er - 4552dfsdg6652sdfsdfd5654

Jeg vil ha 4552 6652 5654 i forskjellige celler

En annen leser, Tim K SW, ga den perfekte løsningen på dette problemet:

Forutsatt at 4552dfsdg6652sdfsdfd5654 er i A1, og du vil ha disse tallene hentet inn i 3 forskjellige celler. 4452 i si celle B1 og 6652 i C1 og 5654 i D1 vil du bruke disse.

B1:
= MID (A1,1,4)

C1:
= MID (A1,10,4)

D1:
= MID (A1,21,4)

Formlene er ganske enkle, men hvis du ikke er kjent med MID-funksjonen, forstår du kanskje ikke hvordan det fungerer. MID tar tre argumenter: en celle, tegnnummeret der resultatet starter, og antall tegn som skal trekkes. MID (A1,10,4) forteller for eksempel at Excel skal ta fire tegn som begynner på den tiende karakteren i strengen.

Ved å bruke de tre forskjellige MID-funksjonene i tre celler, trekkes tallene ut av denne lange strengen av tall og bokstaver. Selvfølgelig fungerer denne metoden bare hvis du alltid har samme antall tegn - både bokstaver og tall - i hver celle. Hvis antallet av hver varierer, trenger du en mye mer komplisert formel.

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

En av de vanskeligste forespørslene som ble lagt ut på artikkelen var denne, fra leseren Daryl:

Hei, jeg vil bare spørre hvordan å skille svært uformatert oppføring som:

Rp. 487.500 (Nett 50% AV)
Rp 256.500 Nett 40% AV
Rp99.000
Rp 51.000 / orang Nett (50% Off)

Jeg har brukt litt tid på å jobbe med denne, og kunne ikke komme med en løsning selv, så jeg tok til Reddit. Bruker UnretiredGymnast ga denne formelen som er lang og svært kompleks:

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

Som du ser kan dekoding av denne formelen ta en stund og krever en ganske solid kunnskap om mange Excel-funksjoner. 16 Excel-formler som vil hjelpe deg med å løse virkelige livsproblemer. 16 Excel-formler som vil hjelpe deg med å løse virkelige problemer. Det riktige verktøyet er halv arbeidet. Excel kan løse beregninger og behandle data raskere enn du kan finne kalkulatoren din. Vi viser deg viktige Excel-formler og demonstrerer hvordan du bruker dem. Les mer . For å finne ut hva som skjer her, må vi se på noen få funksjoner du kanskje ikke er kjent med. Den første er IFERROR, som fanger en feil (vanligvis representert med et pundskilt, som # N / A eller # DIV / 0) og erstatter det med noe annet. I det ovenstående vil du se IFERROR (SEARCH (“%”,A1) -4, LEN (A1)). La oss bryte dette ned.

IFERROR ser på det første argumentet, som er SEARCH (SØK)“%”,A1) -4. Så hvis “%” vises i celle A1, plasseringen er returnert og fire trekkes fra den. Hvis “%” gjør ikke vises i strengen, vil Excel opprette en feil, og lengden på A1 returneres i stedet.

De andre funksjonene du kanskje ikke er kjent med, er litt enklere; SUMPRODUCT, for eksempel, multipliserer og legger til elementer av arrays. STOR returnerer det største nummeret i en rekkevidde. ROW, kombinert med et dollarskilt, returnerer en absolutt referanse til en rad.

Å se hvordan alle disse funksjonene fungerer sammen er ikke lett, men hvis du starter i midten av formelen, og arbeider utover, begynner du å se hva den gjør. Det tar litt tid, men hvis du er interessert i å se nøyaktig hvordan det fungerer, anbefaler jeg at du legger det inn i et Excel-ark og spiller rundt med det. Det er den beste måten å få en ide om hva du jobber med.

(Den beste måten å unngå et slikt problem på er også å importere dataene dine mer pent. Slik importerer du data til Excel-regnearkene på en fin og enkel måte. Slik importerer du data i Excel-regnearkene. Den enkle og enkle måten har du noensinne slitt med importerer eller eksporterer data til et regneark? Denne veiledningen hjelper deg med å mestre kunsten å flytte data mellom Microsoft Excel, CSV, HTML og andre filformater. Les mer - det er ikke alltid et alternativ, men det bør være ditt førstevalg når det er.)

Ett skritt av gangen

Som du kan se, er den beste måten å løse eventuelle Excel-problemer, ett steg om gangen: start med det du vet, se hva det kommer til deg og gå derfra. Noen ganger vil du ende opp med en elegant løsning, og noen ganger får du noe som er veldig lenge, rotete og komplekse. Men så lenge det virker, har du lyktes!

Og ikke glem å be om hjelp Trenger du å lære Excel? 10 eksperter vil lære deg gratis! Trenger du å lære Excel? 10 eksperter vil lære deg gratis! Å lære å bruke Excels mer avanserte funksjoner kan være tøft. For å gjøre det litt lettere, har vi sporet de beste Excel-gurusene som kan hjelpe deg med å mestre Microsoft Excel. Les mer . Det er noen ekstremt talentfulle Excel-brukere der ute, og deres hjelp kan være uvurderlig for å løse et tøft problem.

Har du noen forslag til å løse vanskelige utvinningsproblemer? Kjenner du til andre løsninger på problemene vi har håndtert ovenfor? Del dem og eventuelle tanker du har i kommentarene nedenfor!

Utforsk mer om: Microsoft Excel, regneark.