3 Probleme complexe de extracție Excel rezolvate și explicate

Mulți oameni se luptă cu extragerea de informații din celule complexe în Microsoft Excel. Multe comentarii și întrebări ca răspuns la articolul meu despre Cum se extrage un număr sau un text din Excel cu această funcție Cum se extrage un număr sau un text din Excel cu această funcție Cum se extrage un număr sau un text din Excel cu această funcție Se amestecă numere și text într-o foaie de calcul Excel poate introduce provocări. Vă vom arăta cum să modificați formatarea celulelor și numerele separate de text. Citiți mai multe dovedește acest lucru. Se pare că nu este întotdeauna clar cum să se izoleze datele dorite dintr-o foaie Excel.

Am ales câteva întrebări din articolul respectiv pentru a merge aici, pentru a vedea cum funcționează soluțiile. Learning Excel rapid 8 Sfaturi pentru cum să învețe Excel rapid 8 Sfaturi pentru cum să învețe Excel rapid Nu la fel de confortabil cu Excel așa cum ați dori? Începeți cu sfaturi simple pentru a adăuga formule și a gestiona date. Urmați acest ghid și veți fi rapidi în cel mai scurt timp. Citește mai mult nu este ușor, dar folosirea unor probleme din lumea reală, cum ar fi acestea, ajută foarte mult.

1. Extragerea utilizând un separator

Reader Adrie a pus următoarea întrebare:

Aș dori să extrag primul set de numere dintr-o listă, adică (122,90,84,118,4,128.9)
Orice idei despre ce formula pot folosi?

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

Faptul că numerele care urmează a fi extrase sunt lungimi diferite face ca acest lucru să fie un pic mai complicat decât să folosească funcția MID, ci prin combinarea a câtorva funcții diferite, putem scăpa de literele din stânga, precum și de “X” și numerele din dreapta, lăsând numai numerele dorite într-o celulă nouă.

Iată formula pe care o vom folosi pentru a rezolva această problemă:

= VALUE (STÂNGA ((DREAPTA (A1, (LEN (A1) -4))), FIND ( "X", A1) -5))

Să începem în mijloc și să ne străduim să vedem cum funcționează. În primul rând, vom începe cu funcția FIND. În acest caz, folosim FIND (“X”,A1). Această funcție se uită prin textul din celula A1 pentru litera X. Când găsește un X, returnează poziția în care se află. Pentru prima intrare, COIL112X2.5, de exemplu, returnează 8. Pentru a doua intrare, aceasta revine 7.
Apoi, să examinăm funcția LEN. Aceasta returnează pur și simplu lungimea șirului în celulă minus 4. Combinând asta cu funcția DREAPTA, primim șirul din celulă minus primele patru caractere, ceea ce elimină “BOBINA” de la începutul fiecărei celule. Formula pentru această parte arată astfel: DREAPTA (A1, (LEN (A1) -4)).

Următorul nivel este funcția LEFT. Acum că am determinat poziția lui X cu funcția FIND și am scăpat “BOBINA” cu funcția DREAPTA, funcția LEFT returnează ceea ce a mai rămas. Haideți să ne descurcăm puțin mai departe. Iată ce se întâmplă atunci când simplificăm cele două argumente:

= STÂNGA ("112X2.5", (8-5))

Funcția LEFT returnează cele trei caractere din stânga ale șirului ( “-5” la sfârșitul argumentului se asigură că numărul corect de caractere este eliminat prin contabilizarea primelor patru caractere din șir).

În cele din urmă, funcția VALUE asigură că numărul returnat este formatat ca număr, nu ca text. Acest exemplu nu este la fel de distractiv ca construirea unui joc de lucru Tetris în Excel 7 Lucruri distractive și ciudate pe care le puteți crea cu Microsoft Excel 7 Lucruri distractive și ciudate pe care le puteți crea cu Microsoft Excel Imaginați-vă că Excel a fost distractiv! Excel oferă o mulțime de posibilități pentru proiecte care depășesc utilizarea dorită. Singura limită este imaginația ta. Iată cele mai creative exemple despre modul în care oamenii folosesc Excel. Citiți mai mult, dar nu reprezintă un exemplu excelent de combinare a câtorva funcții pentru a rezolva o problemă.

2. Tragerea numerelor din șiruri mixte

O întrebare similară a fost pusă de cititorul Yadhu Nandan:

Vreau să știu cum să separe numericul și alfabetele.

Exemplu este - 4552dfsdg6652sdfsdfd5654

Vreau 4552 6652 5654 în diferite celule

Un alt cititor, Tim K SW, a oferit soluția perfectă pentru această problemă:

Presupunând că 4552dfsdg6652sdfsdfd5654 este în A1 și doriți ca aceste numere să fie extrase în 3 celule diferite. 4452 în celulă B1 și 6652 în C1 și 5654 în D1 le folosești.

B1:
= MID (A1,1,4)

C1:
= MID (A1,10,4)

D1:
= MID (A1,21,4)

Formulele sunt destul de simple, dar dacă nu sunteți familiarizat cu funcția MID, este posibil să nu înțelegeți cum funcționează. MID are trei argumente: o celulă, numărul de caractere în care începe rezultatul și numărul de caractere care ar trebui trase. MID (A1,10,4), de exemplu, spune Excel să ia patru caractere începând cu al zecelea caracter din șir.

Folosind cele trei funcții MID diferite în trei celule, trageți numerele din acest șir lung de numere și litere. Evident, această metodă funcționează numai dacă aveți întotdeauna același număr de caractere - ambele litere și numere - în fiecare celulă. Dacă numărul variază, veți avea nevoie de o formulă mult mai complicată.

3. Obținerea unui număr dintr-un șir mixt cu spații

Una dintre cele mai dificile cereri postate pe articol a fost aceasta, din partea cititorului Daryl:

Bună, vreau doar să întreb cum să se separe intrarea foarte neformată cum ar fi:

Rp. 487.500 (Nett 50% OFF)
Rp 256.500 Nett 40% OFF
Rp99.000
Rp 51.000 / orang nett (50% oprit)

Am petrecut ceva timp lucrand la aceasta si nu am reusit sa vina cu o solutie, asa ca m-am dus la Reddit. User UnretiredGymnast a oferit această formulă, care este lungă și foarte complexă:

= SUMPRODUCT (MID (0 & LEFT (A1, IFERROR (SEARCH ( "%", A1) -4, LEN (A1))), MARI (INDEX (ISNUMBER (- MID (STÂNGA (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)

După cum puteți vedea, decodarea acestei formule durează destul timp și necesită o cunoaștere destul de solidă a multor funcții Excel 16 Formule Excel care vă vor ajuta să rezolvați problemele din viața reală 16 Formule Excel care vă vor ajuta să rezolvați problemele din viața reală Instrumentul potrivit este jumătate din muncă. Excel poate rezolva calculele și procesează datele mai repede decât vă puteți găsi calculatorul. Vă arătăm formule Excel cheie și demonstrați cum să le utilizați. Citeste mai mult . Pentru a vă ajuta să aflați exact ce se întâmplă aici, va trebui să vă uitați la câteva funcții despre care s-ar putea să nu fiți familiarizați. Primul este IFERROR, care surprinde o eroare (de obicei reprezentată cu un semn de literă, ca # N / A sau # DIV / 0) și o înlocuiește cu altceva. În cele de mai sus, veți vedea IFERROR (SEARCH (“%”,A1) -4, LEN (A1)). Hai să ne descurcăm.

IFERROR examinează primul argument, care este SEARCH (“%”,A1) -4. Astfel, dacă “%” apare în celula A1, locația sa este returnată și patru sunt scoase din ea. Dacă “%” nu apar în șir, Excel va crea o eroare, iar lungimea lui A1 va fi returnată.

Celelalte funcții pe care nu le cunoașteți sunt puțin mai simple; SUMPRODUCT, de exemplu, se înmulțește și apoi adaugă elemente ale matricelor. LARGE returnează cel mai mare număr într-un interval. ROW, combinat cu un semn de dolar, returnează o referință absolută la un rând.

Văzând cum funcționează toate aceste funcții nu este ușor, dar dacă începeți în mijlocul formulei și lucrați în exterior, veți începe să vedeți ce face. Va dura ceva timp, dar dacă sunteți interesat să vedeți exact cum funcționează, vă recomandăm să o puneți într-o foaie de calcul Excel și să jucați cu ea. Acesta este cel mai bun mod de a obține o idee despre ceea ce lucrați.

(De asemenea, cel mai bun mod de a evita o astfel de problemă este de a vă importa mai curând datele dvs. Cum să importați date în foi de calcul Excel Cum puteți să importați date în foi de calcul Excel Cum vă purtați vreodată importul sau exportul de date într-o foaie de calcul? Acest tutorial vă va ajuta să stăpânești arta de a muta date între Microsoft Excel, CSV, HTML și alte formate de fișiere.Citește mai mult - nu este întotdeauna o opțiune, dar ar trebui să fie prima alegere atunci când este.)

Cu pasi marunti

După cum puteți vedea, cel mai bun mod de a rezolva orice problemă din Excel este un pas la un moment dat: începeți cu ceea ce știți, vedeți ce vă primește și mergeți de acolo. Uneori veți ajunge la o soluție elegantă și uneori veți obține ceva care este foarte lung, dezordonat și complex. Dar atâta timp cât funcționează, ați reușit!

Și nu uitați să cereți ajutor Aveți nevoie să învățați Excel? 10 experți vă vor învăța gratuit! Aveți nevoie să învățați Excel? 10 experți vă vor învăța gratuit! Învățați cum să utilizați funcțiile Excel mai avansate poate fi dificilă. Pentru a face mai ușor, am urmărit cei mai buni guru Excel care vă pot ajuta să vă stăpâniți pe Microsoft Excel. Citeste mai mult . Există niște utilizatori excelenți Excel excelenți acolo și ajutorul lor poate fi de neprețuit în rezolvarea unei probleme dificile.

Aveți sugestii privind rezolvarea problemelor de extracție dificile? Știți despre alte soluții la problemele pe care le-am abordat mai sus? Împărtășește-i și orice gânduri pe care le ai în comentariile de mai jos!

Explorați mai multe despre: Microsoft Excel, Foaie de calcul.