3 Komplexa Excel-extraktionsproblem löses och förklaras

Många människor kämpar med att extrahera information från komplexa celler i Microsoft Excel. De många kommentarer och frågor som svar på min artikel om hur man extraherar ett tal eller en text från Excel med den här funktionen Hur man extraherar ett tal eller en text från Excel med denna funktion Hur man extraherar ett tal eller en text från Excel med denna funktion Blanda siffror och Text i ett Excel-kalkylblad kan introducera utmaningar. Vi visar hur du ändrar formateringen av dina celler och separata nummer från text. Läs mer visar det. Tydligen är det inte alltid tydligt hur man isolerar önskade data från ett Excel-ark.

Vi har valt några av frågorna från den artikeln att gå igenom här, så att du kan se hur lösningarna fungerar. Lär dig Excel snabbt 8 Tips för hur du lär dig Excel snabbt 8 Tips för hur du lär Excel snabbt Inte så bekväm med Excel som du vill? Börja med enkla tips för att lägga till formler och hantera data. Följ den här guiden, och du kommer snart att bli snabb. Läs mer är inte lätt, men med hjälp av verkliga problem som dessa hjälper mycket.

1. Extraktion med hjälp av en separator

Reader Adrie frågade följande fråga:

Jag skulle vilja extrahera den första uppsättningen tal från en lista, dvs (122,90,84,118,4,128,9)
Några idéer om vilken formel jag kan använda?

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

Att de siffror som ska extraheras har olika längder gör det lite mer komplicerat än att bara använda MID-funktionen, men genom att kombinera några olika funktioner kan vi ta bort bokstäverna till vänster såväl som “X” och siffrorna till höger, lämnar bara de önskade siffrorna i en ny cell.

Här är den formel som vi ska använda för att lösa detta problem:

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

Låt oss börja i mitten och arbeta oss ut för att se hur det fungerar. Först börjar vi med FIND-funktionen. I det här fallet använder vi FIND (“X”,A1). Denna funktion tittar igenom texten i cell A1 för bokstaven X. När den finner en X returnerar den den position som den är i. För den första posten returnerar COIL112X2.5 till exempel 8. För den andra posten returnerar 7.
Låt oss nu titta på LEN-funktionen. Detta returnerar helt enkelt strängens längd i cellen minus 4. Kombinera med RIGHT-funktionen får vi strängen från cellen minus de fyra första tecknen, som tar bort “SPOLE” från början av varje cell. Formeln för denna del ser så här ut: HÖGER (A1, (LEN (A1) -4)).

Nästa nivå ut är VÄNSTER funktionen. Nu när vi har bestämt X-positionen med FIND-funktionen och blivit av med “SPOLE” Med RIGHT-funktionen returnerar VÄNSTER-funktionen vad som är kvar. Låt oss bryta ner det lite längre. Här är vad som händer när vi förenklar dessa två argument:

= VÄNSTER ("112X2.5", (8-5))

VÄNSTER-funktionen returnerar de tre längsta vänstra tecknen i strängen (den “-5” i slutet av argumentet säkerställs att rätt antal tecken elimineras genom redovisning av de fyra första tecknen i strängen).

Slutligen säkerställer VALUE-funktionen att det återvände talet är formaterat som ett tal istället för som text. Detta exempel är inte så kul som att bygga ett arbetsspel av Tetris i Excel 7 Roliga och konstiga saker du kan skapa med Microsoft Excel 7 Roliga och konstiga saker du kan skapa med Microsoft Excel Tänk på Excel var kul! Excel erbjuder gott om utrymme för projekt som går utöver den avsedda användningen. Den enda gränsen är din fantasi. Här är de mest kreativa exemplen på hur människor använder Excel. Läs mer, men det fungerar som ett bra exempel på hur man kombinerar några funktioner för att lösa ett problem.

2. Dra ut siffror ur blandade strängar

En liknande fråga ställdes av läsaren Yadhu Nandan:

Jag vill veta hur man separerar de numeriska och alfabeterna.

Exempel är - 4552dfsdg6652sdfsdfd5654

Jag vill ha 4552 6652 5654 i olika celler

En annan läsare, Tim K SW, gav den perfekta lösningen på det här problemet:

Antag att 4552dfsdg6652sdfsdfd5654 är i A1 och du vill att dessa nummer extraheras i 3 olika celler. 4452 i säger cell B1 och 6652 i C1 och 5654 i D1 skulle du använda dessa.

B1:
= MID (A1,1,4)

C1:
= MID (A1,10,4)

D1:
= MID (A1,21,4)

Formlerna är ganska enkla, men om du inte är bekant med MID-funktionen kanske du inte förstår hur det fungerar. MID tar tre argument: en cell, teckennumret där resultatet börjar och antalet tecken som ska dras. MID (A1,10,4) berättar till exempel att Excel ska ta fyra tecken som börjar vid den tionde tecknen i strängen.

Med hjälp av de tre olika MID-funktionerna i tre celler drar siffrorna ut ur den här långa strängen av siffror och bokstäver. Självklart fungerar denna metod endast om du alltid har samma antal tecken - både bokstäver och siffror - i varje cell. Om antalet varier varierar behöver du en mycket mer komplicerad formel.

3. Hämta ett tal från en blandad sträng med mellanslag

En av de svåraste förfrågningarna som postades på artikeln var den här, från läsaren Daryl:

Hej, jag vill bara fråga hur man skiljer väldigt oformaterad post som:

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

Jag tillbringade tid på att arbeta med den här, och kunde inte själv hitta en lösning, så jag tog till Reddit. User UnretiredGymnast gav denna formel som är lång och mycket komplex:

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

Som du kan se, tar avkodning av denna formel ganska lång tid och kräver en ganska solid kunskap om många Excel-funktioner 16 Excel-formulär som hjälper dig att lösa verkliga problem 16 Excel-formulär som hjälper dig att lösa verkliga problem Det rätta verktyget är hälften av arbetet. Excel kan lösa beräkningar och bearbeta data snabbare än du kan hitta din räknare. Vi visar dig viktiga Excel-formulär och visar hur du använder dem. Läs mer . För att få reda på exakt vad som händer här måste vi titta på några funktioner som du kanske inte känner till. Den första är IFERROR, som fångar ett fel (vanligtvis representerad med ett pundtecken, som # N / A eller # DIV / 0) och ersätter det med något annat. I ovanstående ser du IFERROR (SEARCH (“%”,A1) -4, LEN (A1)). Låt oss bryta ner det här.

IFERROR tittar på det första argumentet, som är SEARCH (“%”,A1) -4. Så om “%” visas i cell A1, dess plats returneras och fyra subtraheras från den. Om “%” gör inte visas i strängen, kommer Excel att skapa ett fel, och längden på A1 returneras istället.

De andra funktionerna som du kanske inte känner till är lite enklare. SUMPRODUCT, till exempel, multiplicerar och lägger sedan till element i arrays. STORA returnerar det största antalet inom ett intervall. ROW, kombinerat med ett dollar tecken, returnerar en absolut referens till en rad.

Att se hur alla dessa funktioner fungerar tillsammans är inte enkla, men om du börjar mitt i formeln och arbetar utåt, börjar du se vad den gör. Det tar ett tag, men om du är intresserad av att se exakt hur det fungerar rekommenderar jag att du lägger in det på ett Excel-ark och spelar med dig. Det är det bästa sättet att få en uppfattning om vad du jobbar med.

(Det bästa sättet att undvika ett sådant problem är att importera dina data mer snyggt. Så här importerar du data till dina Excel-kalkylblad. Det snygga och enkla sättet att importera data till dina Excel-kalkylblad har det snygga och enkla sättet har kämpat med importera eller exportera data till ett kalkylblad? Denna handledning hjälper dig att behärska konsten att flytta data mellan Microsoft Excel, CSV, HTML och andra filformat. Läs mer - det är inte alltid ett alternativ, men det borde vara ditt första val när det är.)

Ett steg i taget

Som du kan se är det bästa sättet att lösa något Excel-problem ett steg åt gången: Börja med vad du vet, se vad det får du och gå därifrån. Ibland kommer du att sluta med en elegant lösning, och ibland får du något som är riktigt långt, rörigt och komplicerat. Men så länge det fungerar har du lyckats!

Och glöm inte att be om hjälp Behöver du lära dig Excel? 10 experter kommer att lära dig gratis! Behöver du lära dig Excel? 10 experter kommer att lära dig gratis! Att lära sig att använda Excels avancerade funktioner kan vara tufft. För att göra det lite enklare har vi spårat de bästa Excel-guruer som kan hjälpa dig att behärska Microsoft Excel. Läs mer . Det finns några extremt begåvade Excel-användare där ute, och deras hjälp kan vara ovärderlig för att lösa ett svårt problem.

Har du några förslag på att lösa tuffa utvinningsproblem? Känner du till några andra lösningar på de problem vi behandlat ovan? Dela dem och alla tankar du har i kommentarerna nedan!

Utforska mer om: Microsoft Excel, kalkylblad.