Kako izvući broj iz niza u Excelu

Kako da

PoAdam Davidson

Objavljeno7. lipnja 2023

Excel je moćna proračunska tablica, ali ne može činiti čuda. Ako uvozite podatke, a ti su podaci u neredu, tada Excel neće moći puno učiniti s njima. Na primjer, ako imate vrijednosti koje su pridružene drugim znakovima kao dio niza, nećete moći izvoditi izračune na njima osim ako ih prvo ne izdvojite.

Srećom, moguće je učiniti upravo to, iako može biti kompliciranije nego što mislite na prvi pogled. Evo kako izvući broj iz niza u Excelu.

Kako izvući broj s kraja niza pomoću Flash Filla

Brz i jednostavan način izdvajanja broja s kraja niza je korištenje programa Excel Flash Fill značajka. Ova vam značajka omogućuje brzo popunjavanje niza ćelija na temelju uzorka otkrivenog u jednoj određenoj ćeliji. Jedna loša strana ove metode je ta što zahtijeva da sami izvršite prvu ekstrakciju i ako želite izdvojiti samo broj iz jednog niza, to neće biti od koristi.

U većini slučajeva Flash Fill može ispravno izvući broj s kraja niza, iako nije nepogrešiv, stoga uvijek trebate pažljivo provjeriti rezultate. Ako ne radi ispravno, pokušajte ručno ispuniti prve dvije ili tri ćelije umjesto samo prve.

Za izdvajanje broja s kraja niza pomoću Flash Filla:

1. Odaberite ćeliju pored prve ćelije koja sadrži vaše nizove.
   
2. Unesite broj koji se pojavljuje na kraju niza.
   
3. Ako su svi vaši podaci u neprekidnom stupcu bez razmaka, odaberite ćeliju neposredno ispod one u koju ste upravo unijeli broj.
   
4. Ako vaši podaci imaju praznine u nekim recima, odaberite prvu ćeliju i sve ćelije ispod kojih želite izdvojiti brojeve, uključujući sve prazne retke.
   
5. Pritisnite Ctrl+E na Windowsima ili Cmd+E na Macu za pokretanje značajke Flash Fill.
6. Vaše preostale ćelije automatski će se ispuniti na temelju uzorka koji je Excel uočio. U većini slučajeva, brojevi s kraja niza bit će izdvojeni ispravno.
   

Kako izvući broj s kraja niza pomoću formule

Jedna loša strana korištenja metode Flash Fill je ta što zahtijeva da sami izvučete brojeve iz niza za prvi red. Moguće je izvući broj s kraja niza pomoću formule, iako je formula prilično složena.

Da biste izdvojili broj s kraja niza pomoću formule:

1. Pritisnite ćeliju u kojoj želite da se pojavi izdvojeni broj.
2. Unesite sljedeću formulu, zamjenjujući 'B2' ćelijom koja sadrži vaš niz:

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

3. Pritisnite Unesi.
4. Broj treba izdvojiti iz niza.
5. Da biste primijenili formulu na sve preostale ćelije, kliknite ručicu za povlačenje u donjem desnom kutu ćelije.
   
6. Povucite prema dolje preko ćelija u kojima želite da se formula replicira.
   
7. Otpustite miš i formula će se kopirati u odabrane ćelije.
8. Ove bi ćelije sada trebale sadržavati vaše izdvojene brojeve.
   
9. Sve ćelije bez niza iz kojeg se izdvaja vratit će pogrešku. Ako umjesto toga želite vratiti praznu ćeliju, umjesto toga možete upotrijebiti formulu:

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

10. Sve prazne ćelije sada će vratiti prazan prostor.
    

Možda se pitate čemu ova formula. Ukratko, generira niz koji je iste duljine kao niz. Smješta svaki od znakova niza redom u ovaj niz. Zatim procjenjuje svaki od tih znakova da vidi jesu li broj ili ne. Svi znakovi koji su brojevi pretvaraju se u nulu. Svi znakovi koji nisu brojevi pretvaraju se u broj koji predstavlja njihov položaj u nizu.

Na primjer, AB5HG23 bi se pretvorio u 1, 2, 0, 4, 5, 0, 0. Zatim traži najveću vrijednost na ovom popisu, što je položaj posljednjeg znaka koji nije broj. Konačno, vraća sve znakove nakon ove točke, koji su brojevi na kraju niza. Funkcija VALUE zatim to pretvara natrag u broj. Jednostavno, zar ne?

Kako izvući broj s početka niza pomoću Flash Filla

Također možete koristiti Flash Fill za izdvajanje broja s početka niza tako da sami ispunite prvi primjer i dopustite Excelu da zaustavi uzorak.

Za izdvajanje broja s početka niza pomoću Flash Filla:

1. Odaberite ćeliju s desne strane prvog niza koja ima broj koji želite izdvojiti.
2. Unesite broj koji se pojavljuje na početku niza.
   
3. Ako su svi vaši podaci u kontinuiranom stupcu bez razmaka, odaberite ćeliju neposredno ispod.
   
4. Ako vaši podaci imaju praznine, odaberite prvu ćeliju i sve ćelije ispod kojih želite izdvojiti brojeve, uključujući sve prazne retke.
   
5. Pritisnite Ctrl+E na Windowsima ili Cmd+E na Macu za pokretanje Flash Filla.
6. Ćelije koje ste odabrali sada bi trebale prikazivati ​​izdvojene brojeve.
   

Kako izvući broj s početka niza pomoću formule

Također možete koristiti formulu za izdvajanje broja s početka niza. Ovo funkcionira na sličan način kao formula u prethodnom odjeljku, ali pronalazi položaj prvog nenumeričkog znaka i vraća sve znakove prije toga.

Da biste izdvojili broj s početka niza pomoću formule:

1. Odaberite ćeliju u kojoj želite da se pojavi izdvojeni broj.
2. Unesite sljedeću formulu, zamjenjujući 'B18' ćelijom koja sadrži vaš niz:

   =VRIJEDNOST(LIJEVO(B18,podudaranje(LAŽ, JEBROJ(SREDINA(B18,RED(INDIREKT("1:"&LEN(B18)+1)),1)*1),0)-1))

3. Pritisnite Unesi i broj će biti izvučen.
   
4. Možeš koristite automatsko popunjavanje u Excelu kako biste kopirali formulu u sve druge ćelije koje su vam potrebne.
5. Ako želite da bilo koji prazni redak vrati prazan rezultat, umjesto toga upotrijebite sljedeću formulu:

   =IFERROR(VALUE(LEFT(B18,MATCH(FALSE, ISNUMBER(MID(B18,ROW(INDIRECT("1:"&LEN(B18)+1)),1)*1),0)-1))," ")

Kako izvući brojeve s bilo kojeg mjesta u nizu pomoću Flash Filla

Kao što je ranije spomenuto, Flash Fill možda neće raditi ako prva ćelija ne odgovara formatu nekih drugih ćelija. U tom slučaju, možda ćete morati ručno izračunati dvije ili tri ćelije prije nego što vam Flash Fill da željeni rezultat.

Za izdvajanje brojeva s bilo kojeg mjesta u nizu pomoću Flash Filla:

1. U ćeliju pored prvog niza ručno upišite brojeve koji se nalaze u nizu.
   
2. Ako su svi vaši podaci u neprekidnom stupcu, odaberite ćeliju neposredno ispod.
   
3. Ako vaši podaci imaju praznine, odaberite prvu ćeliju i sve ćelije ispod kojih želite izvući brojeve, uključujući sve prazne retke.
   
4. Pritisnite Ctrl+E na Windowsima ili Cmd+E na Macu za pokretanje Flash Filla.
5. Ćelije bi sada trebale biti ispunjene izdvojenim brojevima.
   

Kako izvući brojeve s bilo kojeg mjesta u nizu pomoću formule

Možete upotrijebiti formulu za izdvajanje svih brojeva iz niza, bez obzira gdje se pojavljuju.

Za izdvajanje brojeva s bilo kojeg mjesta u nizu pomoću formule:

1. Odaberite ćeliju u kojoj želite da se pojavi izdvojeni broj.
2. Unesite sljedeću formulu, zamjenjujući 'B9' ćelijom koja sadrži vaš niz:

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

3. Vaša bi formula trebala izdvojiti sve brojeve iz niza.
   
4. Ako želite da sve prazne ćelije vrate praznu ćeliju, a ne nulu, umjesto toga upotrijebite ovu formulu:

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

5. Možeš koristiti automatsko popunjavanje da primijenite formulu na sve druge ćelije u proračunskoj tablici.
6. Svaki niz koji ne sadrži brojeve vratit će nulu. Možete naučiti kako pretvorite nulu u crticu u Excelu ako želiš ovo promijeniti.

Proširite svoje Excel vještine

Učenje kako izvući broj iz niza u Excelu može vam pomoći da brzo izvučete podatke koji su vam potrebni s popisa nizova. Iako tražene formule mogu biti složene, ne morate točno razumjeti kako rade; upravo to rade!

Postoji više korisnih Excel trikova koje možete naučiti kako biste podigli svoje vještine na višu razinu. Možeš naučiti kako brojati ćelije s tekstom u njima, ili kako izračunati nečiju dob prema datumu rođenja. Možete čak stvoriti generator slučajnih brojeva u Excelu.