Veel mensen worstelen met het extraheren van informatie uit complexe cellen in Microsoft Excel. De vele opmerkingen en vragen in antwoord op mijn artikel over Hoe een getal of tekst uit Excel extraheren met deze functie Een getal of tekst uit Excel extraheren met deze functie Een getal of tekst uit Excel extraheren met deze functie Mengenummers en tekst in een Excel-spreadsheet kan uitdagingen introduceren. We laten u zien hoe u de opmaak van uw cellen kunt wijzigen en hoe u getallen uit tekst kunt scheiden. Lees meer bewijst het. Blijkbaar is het niet altijd duidelijk hoe de gewenste gegevens van een Excel-sheet moeten worden geïsoleerd.
We hebben een paar vragen uit dat artikel gekozen om hier doorheen te lopen, zodat u kunt zien hoe de oplossingen werken. Excel snel leren 8 Tips voor snel leren van Excel 8 Tips voor het snel leren van Excel Werkt Excel niet zo comfortabel als u zou willen? Begin met eenvoudige tips voor het toevoegen van formules en het beheren van gegevens. Volg deze gids en u bent zo snel mogelijk op de hoogte. Meer lezen is niet gemakkelijk, maar het gebruik van echte problemen als deze helpt veel.
1. Extractie met behulp van een scheider
Reader Adrie stelde de volgende vraag:
Ik zou graag de eerste reeks getallen uit een lijst halen, dwz (122, 90, 84, 118, 4, 128, 9)
Om het even welke ideeën op welke formule ik kan gebruiken?COIL112X2.5
COIL90X2.5
COIL84X2.0
COIL118.4X1.8
COIL128.9X2.0
Het feit dat de te extraheren getallen verschillende lengtes hebben, maakt dit een beetje ingewikkelder dan alleen het gebruik van de MID-functie, maar door een paar verschillende functies te combineren, kunnen we de letters aan de linkerkant en de "X" en de cijfers aan de rechterkant, waarbij alleen de gewenste nummers in een nieuwe cel achterblijven.
Dit is de formule die we zullen gebruiken om dit probleem op te lossen:
= VALUE (LEFT ((RIGHT (A1 (LEN (A1) -4))) FIND ( "X", A1) -5))
Laten we in het midden beginnen en ons een weg banen om te zien hoe het werkt. Eerst zullen we beginnen met de FIND-functie. In dit geval gebruiken we FIND ("X", A1). Deze functie kijkt door de tekst in cel A1 voor de letter X. Wanneer een X wordt gevonden, wordt de positie waarin deze voorkomt X geretourneerd. Voor de eerste invoer retourneert COIL112X2.5 bijvoorbeeld 8. Voor de tweede invoer wordt geeft 7 terug.
Laten we vervolgens kijken naar de LEN-functie. Dit retourneert simpelweg de lengte van de string in de cel minus 4. Door dat te combineren met de functie RECHTS, krijgen we de tekenreeks uit de cel minus de eerste vier tekens, waardoor COIL uit het begin van elke cel wordt verwijderd. De formule voor dit onderdeel ziet er als volgt uit: RECHTS (A1, (LEN (A1) -4)).
Het volgende niveau is de LEFT-functie. Nu we de positie van de X hebben bepaald met de FIND-functie en de "COIL" met de RECHTER functie kwijt zijn, geeft de LEFT-functie terug wat er nog over is. Laten we dit een beetje verder afbreken. Dit is wat er gebeurt als we deze twee argumenten vereenvoudigen:
= LINKS ("112X2.5", (8-5)) De functie LINKS retourneert de drie meest linkse tekens van de tekenreeks (de "-5" aan het einde van het argument zorgt ervoor dat het juiste aantal tekens wordt geëlimineerd door de eerste vier tekens in de tekenreeks te verwerken).
Ten slotte zorgt de VALUE-functie ervoor dat het geretourneerde getal wordt opgemaakt als een getal, in plaats van als tekst. Dit voorbeeld is niet zo leuk als het bouwen van een werkgame van Tetris in Excel 7 Fun & bizarre dingen die je kunt maken met Microsoft Excel 7 Fun & bizarre dingen die je kunt maken met Microsoft Excel Stel je voor dat Excel leuk was! Excel biedt veel ruimte voor projecten die verder gaan dan het beoogde gebruik. De enige beperking is je verbeelding. Hier zijn de meest creatieve voorbeelden van hoe mensen Excel gebruiken. Read More, maar het is een goed voorbeeld van hoe je een paar functies kunt combineren om een probleem op te lossen.
2. Nummers uit gemengde strings trekken
Een soortgelijke vraag werd gesteld door lezer Yadhu Nandan:
Ik wil weten hoe ik de numerieke en alfabetten kan scheiden.
Voorbeeld is - 4552dfsdg6652sdfsdfd5654
Ik wil 4552 6652 5654 in verschillende cellen
Een andere lezer, Tim K SW, leverde de perfecte oplossing voor dit specifieke probleem:
Ervan uitgaande dat 4552dfsdg6652sdfsdfd5654 in A1 staat en u deze getallen in 3 verschillende cellen wilt laten uitpakken. 4452 in bijvoorbeeld cel B1 en 6652 in C1 en 5654 in D1 zou u deze gebruiken.
B1:
= MID (A1, 1, 4)C1:
= MID (A1, 10, 4)D1:
= MID (A1, 21, 4)
De formules zijn vrij eenvoudig, maar als u niet bekend bent met de MID-functie, begrijpt u wellicht niet hoe het werkt. MID neemt drie argumenten: een cel, het tekennummer waar het resultaat start en het aantal tekens dat moet worden getrokken. MID (A1, 10, 4) vertelt Excel bijvoorbeeld om vier tekens te nemen die beginnen bij het tiende teken in de tekenreeks.
Door de drie verschillende MID-functies in drie cellen te gebruiken, haalt u de getallen uit deze lange reeks cijfers en letters. Vanzelfsprekend werkt deze methode alleen als je altijd hetzelfde aantal tekens hebt - zowel letters als cijfers - in elke cel. Als het aantal van elk varieert, hebt u een veel ingewikkelder formule nodig.
3. Een nummer halen uit een gemengde reeks met spaties
Een van de moeilijkste verzoeken op het artikel was deze, van lezer Daryl:
Hallo, ik wil alleen vragen hoe ik zeer ongeformatteerde gegevens kan scheiden, zoals:
Rp. 487.500 (Nett 50% KORTING)
Rp 256.500 Nett 40% KORTING
Rp99.000
Rp 51.000 / orang Nett (50% korting)
Ik heb hier enige tijd aan gewerkt en was niet in staat zelf een oplossing te bedenken, dus ging ik naar Reddit. Gebruiker UnretiredGymnast gaf deze formule die lang en zeer complex is:
= SOMPRODUCT (MID (0 & LEFT (A1, IFERROR (SEARCH ( "%", A1) -4, LEN (A1))), LARGE (INDEX (ISNUMBER (- MID (LEFT (A1, IFERROR (SEARCH ( "%", A1) -4, LEN (A1))), ROW ($ 1: $ 99), 1)) * ROW ($ 1: $ 99), 0), ROW ($ 1: $ 99)) + 1, 1) * 10 ^ ROW ($ 1: $ 99) / 10)
Zoals u kunt zien, kost het decoderen van deze formule behoorlijk wat tijd en vereist een behoorlijk gedegen kennis van veel Excel-functies 16 Excel-formules die u kunnen helpen om problemen met het echte leven op te lossen 16 Excel-formules die u kunnen helpen om problemen met het echte leven op te lossen De juiste tool is de helft van het werk. Excel kan berekeningen verwerken en gegevens sneller verwerken dan u uw rekenmachine kunt vinden. We laten u de belangrijkste Excel-formules zien en laten zien hoe u ze kunt gebruiken. Lees verder . Om te weten wat hier precies gebeurt, moeten we een aantal functies bekijken die u misschien niet kent. De eerste is IFERROR, die een fout opvangt (meestal weergegeven met een hekje, zoals # N / A of # DIV / 0) en vervangt deze door iets anders. In het bovenstaande ziet u IFERROR (SEARCH ("%", A1) -4, LEN (A1)). Laten we dit opsplitsen.
IFERROR kijkt naar het eerste argument, dat is SEARCH ("%", A1) -4. Dus als "%" wordt weergegeven in cel A1, wordt de locatie geretourneerd en daarvan wordt er vier afgetrokken. Als "%" niet in de tekenreeks verschijnt, maakt Excel een fout en wordt in plaats daarvan de lengte van A1 geretourneerd.
De andere functies waarmee u mogelijk niet vertrouwd bent, zijn iets eenvoudiger; SOMPRODUCT vermenigvuldigt zich bijvoorbeeld en voegt vervolgens elementen van arrays toe. LARGE retourneert het grootste getal in een bereik. RIJ, gecombineerd met een dollarteken, geeft een absolute verwijzing naar een rij.
Het is niet eenvoudig om te zien hoe al deze functies samenwerken, maar als je midden in de formule begint en naar buiten werkt, begin je te zien wat het doet. Het zal een tijdje duren, maar als je wilt weten hoe het werkt, raad ik aan het in een Excel-sheet te plaatsen en ermee te spelen. Dat is de beste manier om een idee te krijgen van waar je mee werkt.
(De beste manier om een probleem als dit te voorkomen, is door uw gegevens beter te importeren. Gegevens importeren in uw Excel-spreadsheets de handige en eenvoudige manier Gegevens importeren in uw Excel-spreadsheets de nette en gemakkelijke manier Hebt u ooit moeite gehad met gegevens importeren of exporteren naar een spreadsheet? Deze zelfstudie helpt u de kunst van het verplaatsen van gegevens tussen Microsoft Excel, CSV, HTML en andere bestandsindelingen te beheersen Lees meer - het is niet altijd een optie, maar het zou uw eerste keuze moeten zijn wanneer het is.)
Eén stap tegelijk
Zoals je kunt zien, is de beste manier om elk Excel-probleem op te lossen één stap tegelijk: begin met wat je weet, kijk wat het voor je oplevert en ga vandaar verder. Soms krijg je een elegante oplossing en soms krijg je iets dat echt lang, rommelig en complex is. Maar zolang het werkt, is het je gelukt!
En vergeet niet om hulp te vragen Need to Learn Excel? 10 experts zullen je gratis leren! Wilt u Excel leren? 10 experts zullen je gratis leren! Leren hoe je de meer geavanceerde functies van Excel kunt gebruiken, kan moeilijk zijn. Om het een beetje gemakkelijker te maken, hebben we de beste Excel-goeroes gevonden die u kunnen helpen bij het beheersen van Microsoft Excel. Lees verder . Er zijn enkele zeer getalenteerde Excel-gebruikers die er zijn, en hun hulp kan van onschatbare waarde zijn bij het oplossen van een moeilijk probleem.
Heeft u suggesties voor het oplossen van moeilijke extractieproblemen? Kent u andere oplossingen voor de problemen die we hierboven hebben aangepakt? Deel ze en alle gedachten die je hebt in de comments hieronder!