Kako izvleči številko iz niza v Excelu

Kako

Avtor:Adam Davidson

Objavljeno7. junij 2023

Excel je zmogljiva preglednica, vendar ne more delati čudežev. Če uvažate podatke in so ti podatki v zmešnjavi, Excel z njimi ne bo mogel narediti veliko. Na primer, če imate vrednosti, ki so pripete drugim znakom kot del niza, na njih ne boste mogli izvesti izračunov, razen če jih najprej izvlečete.

K sreči je mogoče narediti natanko to, čeprav je lahko bolj zapleteno, kot bi si sprva mislili. Tukaj je opisano, kako izvlečete številko iz niza v Excelu.

Kako izvleči številko s konca niza z uporabo Flash Fill

Hiter in enostaven način za pridobivanje števila s konca niza je uporaba Excela Flash Fill funkcija. Ta funkcija vam omogoča hitro zapolnjevanje obsega celic na podlagi vzorca, zaznanega v določeni celici. Slaba stran te metode je, da morate sami narediti prvo ekstrakcijo in če želite številko izvleči samo iz enega niza, ne bo koristilo.

V večini primerov lahko Flash Fill pravilno izvleče številko s konca niza, čeprav ni nezmotljiv, zato vedno natančno preverite rezultate. Če ne deluje pravilno, poskusite ročno izpolniti prvi dve ali tri celice in ne le prve.

Če želite izvleči številko s konca niza z uporabo Flash Fill:

1. Izberite celico poleg prve celice, ki vsebuje vaše nize.
   
2. Vnesite številko, ki se pojavi na koncu niza.
   
3. Če so vsi vaši podatki v neprekinjenem stolpcu brez vrzeli, izberite celico neposredno pod tisto, v katero ste pravkar vnesli številko.
   
4. Če so vaši podatki v nekaterih vrsticah vrzeli, izberite prvo celico in vse celice pod njimi, iz katerih želite izvleči številke, vključno s praznimi vrsticami.
   
5. Pritisnite Ctrl+E na Windows oz Cmd+E na Macu za zagon funkcije Flash Fill.
6. Vaše preostale celice bodo samodejno zapolnjene glede na vzorec, ki ga je opazil Excel. V večini primerov bodo številke s konca niza pravilno izluščene.
   

Kako izluščiti število s konca niza s formulo

Slaba stran uporabe metode Flash Fill je ta, da morate sami izvleči številke iz niza za prvo vrstico. S formulo je mogoče izluščiti število s konca niza, čeprav je formula precej zapletena.

Če želite izvleči število s konca niza s formulo:

1. Kliknite celico, kjer želite, da se prikaže izvlečeno število.
2. Vnesite naslednjo formulo in zamenjajte »B2« s celico, ki vsebuje vaš niz:

   =VREDNOST(DESNO(B2,LEN(B2)-MAX(ČE(JEŠTEVILO(SREDINA(B2,VRST(INDIREKT("1:"&LEN(B2))),1)*1)=NAPAKA, VRST(INDIREKT(" 1:"&LEN(B2))),0))))

3. Pritisnite Vnesite.
4. Številko je treba izluščiti iz niza.
5. Če želite uporabiti formulo za vse preostale celice, kliknite ročico za vlečenje v spodnjem desnem kotu celice.
   
6. Povlecite navzdol čez celice, kjer želite, da se formula ponovi.
   
7. Spustite miško in formula bo kopirana v izbrane celice.
8. Te celice bi zdaj morale vsebovati vaše ekstrahirane številke.
   
9. Vse celice brez niza za ekstrahiranje bodo vrnile napako. Če želite namesto tega vrniti prazno celico, lahko namesto tega uporabite formulo:

   =IFERROR(VALUE(RIGHT(B2,LEN(B2)-MAX(IF(ISNUMBER(MID(B2,ROW(INDIRECT("1:"&LEN(B2))),1)*1)=FALSE, ROW(INDIRECT ("1:"&LEN(B2))),0)))),"")

10. Vse prazne celice bodo zdaj vrnile prazen prostor.
    

Morda se sprašujete, kaj ta formula počne. Na kratko, ustvari matriko, ki je enake dolžine kot niz. Vse znake iz niza postavi v ta niz po vrstnem redu. Nato ovrednoti vsakega od teh znakov, da ugotovi, ali je število ali ne. Vsi znaki, ki so številke, se pretvorijo v nič. Vsi znaki, ki niso številke, se pretvorijo v številko, ki predstavlja njihov položaj v nizu.

Na primer, AB5HG23 bi se pretvoril v 1, 2, 0, 4, 5, 0, 0. Nato poišče najvišjo vrednost na tem seznamu, ki je položaj zadnjega znaka, ki ni številka. Na koncu vrne vse znake za to točko, ki so številke na koncu niza. Funkcija VALUE nato to pretvori nazaj v število. Preprosto, kajne?

Kako izvleči številko z začetka niza z uporabo Flash Fill

Uporabite lahko tudi Flash Fill, da izvlečete številko z začetka niza, tako da sami izpolnite prvi primer in dovolite Excelu, da ustavi vzorec.

Če želite izvleči številko z začetka niza z uporabo Flash Fill:

1. Izberite celico desno od prvega niza s številko, ki jo želite izvleči.
2. Vnesite številko, ki se pojavi na začetku niza.
   
3. Če so vsi vaši podatki v neprekinjenem stolpcu brez vrzeli, izberite celico neposredno spodaj.
   
4. Če so v vaših podatkih vrzeli, izberite prvo celico in vse celice pod njimi, iz katerih želite izvleči številke, vključno s praznimi vrsticami.
   
5. Pritisnite Ctrl+E na Windows oz Cmd+E na Macu, da zaženete Flash Fill.
6. Celice, ki ste jih izbrali, bi morale zdaj prikazati izvlečene številke.
   

Kako izluščiti število z začetka niza s formulo

Uporabite lahko tudi formulo, da izvlečete število z začetka niza. To deluje na podoben način kot formula v prejšnjem razdelku, vendar najde položaj prvega neštevilskega znaka in vrne vse znake pred tem.

Če želite izvleči število z začetka niza s formulo:

1. Izberite celico, v kateri želite, da se prikaže ekstrahirana številka.
2. Vnesite naslednjo formulo in zamenjajte »B18« s celico, ki vsebuje vaš niz:

   =VREDNOST(LEVO(B18,UJEMANJE(NAPAKA, ISŠTEVILO(SREDINA(B18,VRSTICA(POSREDNO("1:"&LEN(B18)+1)),1)*1),0)-1))

3. Pritisnite Vnesite in številka bo ekstrahirana.
   
4. Ti lahko uporabite samodejno izpolnjevanje v Excelu da kopirate formulo v vse druge celice, ki jih potrebujete.
5. Če želite, da katera koli prazna vrstica vrne prazen rezultat, namesto tega uporabite naslednjo formulo:

   =ČENAPAKA(VREDNOST(LEVO(B18,UDAJANJE(NAPAKA, JEŠTEVILO(SREDINA(B18,VRSTICA(POSREDNO("1:"&LEN(B18)+1)),1)*1),0)-1))," ")

Kako ekstrahirati številke od koder koli v nizu z uporabo Flash Fill

Kot smo že omenili, Flash Fill morda ne bo deloval, če se prva celica ne ujema z obliko nekaterih drugih celic. V tem primeru boste morda morali ročno izračunati dve ali tri celice, preden vam Flash Fill da rezultat, ki ga potrebujete.

Če želite črpati številke od koder koli v nizu z uporabo Flash Fill:

1. V celico poleg prvega niza ročno vnesite številke, ki so v nizu.
   
2. Če so vsi vaši podatki v neprekinjenem stolpcu, izberite celico neposredno spodaj.
   
3. Če imajo vaši podatki nekaj vrzeli, izberite prvo celico in vse celice pod njimi, iz katerih želite izvleči številke, vključno s praznimi vrsticami.
   
4. Pritisnite Ctrl+E na Windows oz Cmd+E na Macu, da zaženete Flash Fill.
5. Celice bi se zdaj morale napolniti z izvlečenimi številkami.
   

Kako izluščiti števila od koder koli v nizu s pomočjo formule

S formulo lahko izvlečete vsa števila iz niza, ne glede na to, kje se pojavijo.

Če želite s formulo izvleči števila od koder koli v nizu:

1. Izberite celico, v kateri želite, da se prikaže ekstrahirana številka.
2. Vnesite naslednjo formulo in zamenjajte »B9« s celico, ki vsebuje vaš niz:

   =SUMPRODUCT(MID(0&B9,LARGE(INDEX(ISNUMBER(--MID(B9,ROW(INDIRECT("1:"&LEN(B9))),1))*ROW(INDIRECT("1:"&LEN(B9) )),0),ROW(INDIRECT("1:"&LEN(B9))))+1,1)*10^ROW(INDIRECT("1:"&LEN(B9)))/10)

3. Vaša formula mora izvleči vse številke iz niza.
   
4. Če želite, da prazne celice vrnejo prazno celico in ne nič, namesto tega uporabite to formulo:

   =IFERROR(SUMPRODUCT(MID(0&B9,LARGE(INDEX(ISNUMBER(--MID(B9,ROW(INDIRECT("1:"&LEN(B9))),1))*ROW(INDIRECT("1:"&LEN( B9))),0),ROW(INDIRECT("1:"&LEN(B9))))+1,1)*10^ROW(INDIRECT("1:"&LEN(B9)))/10)," ")

5. Ti lahko uporabite samodejno izpolnjevanje da uporabite formulo za vse druge celice v preglednici.
6. Vsi nizi, ki ne vsebujejo številk, bodo vrnili ničlo. Lahko se naučite, kako pretvorite ničlo v pomišljaj v Excelu če želite to spremeniti.

Razširite svoje znanje Excela

Če se naučite izvleči številko iz niza v Excelu, vam lahko pomaga hitro izvleči podatke, ki jih potrebujete, s seznama nizov. Čeprav so zahtevane formule lahko zapletene, vam ni treba natančno razumeti, kako delujejo; samo to počnejo!

Obstaja več uporabnih Excelovih trikov, ki se jih lahko naučite, da svoje spretnosti dvignete na višjo raven. Lahko se naučiš kako prešteti celice z besedilom v njih, ali kako izračunajte starost osebe glede na datum rojstva. Lahko celo ustvarite generator naključnih števil v Excelu.