Kā izvilkt skaitli no virknes programmā Excel

Kā

AutorsĀdams Deividsons

Publicēts2023. gada 7. jūnijs

Excel ir spēcīga izklājlapa, taču tā nevar radīt brīnumus. Ja importējat datus un šie dati ir netīri, programma Excel nevarēs ar tiem daudz darīt. Piemēram, ja jums ir vērtības, kas ir pievienotas citām rakstzīmēm kā daļa no virknes, jūs nevarēsit ar tām veikt aprēķinus, ja vien tās vispirms neizņemsiet.

Par laimi, tas ir iespējams izdarīt tieši to, lai gan tas var būt sarežģītāk, nekā jūs varētu iedomāties. Tālāk ir norādīts, kā programmā Excel izvilkt skaitli no virknes.

Kā izvilkt skaitli no virknes beigām, izmantojot Flash Fill

Ātrs un vienkāršs veids, kā izvilkt skaitli no virknes beigām, ir izmantot Excel Flash Fill funkciju. Šī funkcija ļauj ātri aizpildīt šūnu diapazonu, pamatojoties uz vienā noteiktā šūnā konstatēto modeli. Viens no šīs metodes trūkumiem ir tāds, ka pirmā izvilkšana ir jāveic pašam, un, ja vēlaties izvilkt skaitli tikai no vienas virknes, tas nebūs lietderīgi.

Vairumā gadījumu Flash Fill var pareizi izvilkt skaitli no virknes beigām, lai gan tas nav nekļūdīgs, tāpēc jums vienmēr rūpīgi jāpārbauda rezultāti. Ja tas nedarbojas pareizi, mēģiniet manuāli aizpildīt pirmās divas vai trīs šūnas, nevis tikai pirmo.

Lai izvilktu skaitli no virknes beigām, izmantojot Flash Fill:

1. Atlasiet šūnu blakus pirmajai šūnai, kurā ir jūsu virknes.
   
2. Ievadiet skaitli, kas parādās virknes beigās.
   
3. Ja visi jūsu dati ir nepārtrauktā kolonnā bez atstarpēm, atlasiet šūnu tieši zem tās, kurā tikko ievadījāt numuru.
   
4. Ja jūsu datos dažās rindās ir nepilnības, atlasiet pirmo šūnu un visas zem tās esošās šūnas, no kurām vēlaties iegūt skaitļus, tostarp visas tukšās rindas.
   
5. Nospiediet Ctrl+E operētājsistēmā Windows vai Cmd+E operētājsistēmā Mac, lai palaistu Flash Fill funkciju.
6. Atlikušās šūnas tiks automātiski aizpildītas, pamatojoties uz Excel noteikto modeli. Vairumā gadījumu skaitļi no virknes beigām tiks izvilkti pareizi.
   

Kā izvilkt skaitli no virknes beigām, izmantojot formulu

Viens no Flash Fill metodes izmantošanas trūkumiem ir tāds, ka jums pašam ir jāizņem skaitļi no pirmās rindas virknes. Ir iespējams iegūt skaitli no virknes beigām, izmantojot formulu, lai gan formula ir diezgan sarežģīta.

Lai izvilktu skaitli no virknes beigām, izmantojot formulu:

1. Noklikšķiniet šūnā, kurā vēlaties parādīt izvilkto numuru.
2. Ievadiet šādu formulu, aizstājot “B2” ar šūnu, kurā ir jūsu virkne:

   =VĒRTĪBA(RIGHT(B2,LEN(B2)-MAX(IF(ISNUMURS(VIDUS(B2,RINDA)(INDIRECT("1:"&LEN(B2))),1)*1)=FALSE, ROW(INDIRECT(" 1:"&LEN(B2))),0)))

3. Nospiediet Ievadiet.
4. Skaitlis ir jāizņem no virknes.
5. Lai lietotu formulu visām atlikušajām šūnām, noklikšķiniet uz vilkšanas roktura šūnas apakšējā labajā stūrī.
   
6. Velciet uz leju pār šūnām, kur vēlaties replicēt formulu.
   
7. Atlaidiet peli, un formula tiks kopēta atlasītajās šūnās.
8. Šajās šūnās tagad ir jāiekļauj jūsu izvilktie skaitļi.
   
9. Visas šūnas bez virknes, no kuras izvilkt, atgriezīs kļūdu. Ja vēlaties atgriezt tukšu šūnu, tā vietā varat izmantot formulu:

   =IFERROR(VĒRTĪBA(RIGHT(B2,LEN(B2)-MAX(JA(ISNUMURS(VIDUS(B2,RINDA(INDIREKTS("1:"&LEN(B2))),1)*1)=FALSE, ROW(INDIRECT ("1:"&LEN(B2))),0)))),"")

10. Visas tukšās šūnas tagad atgriezīs tukšu vietu.
    

Jums var rasties jautājums, ko šī formula dara. Īsāk sakot, tas ģenerē masīvu, kura garums ir tāds pats kā virknei. Tas šajā masīvā secībā ievieto visas virknes rakstzīmes. Pēc tam tā novērtē katru no šīm rakstzīmēm, lai noskaidrotu, vai tie ir cipars. Visas rakstzīmes, kas ir cipari, tiek konvertētas uz nulli. Visas rakstzīmes, kas nav skaitļi, tiek pārveidotas par skaitļiem, kas apzīmē to pozīciju virknē.

Piemēram, AB5HG23 tiks pārveidots par 1, 2, 0, 4, 5, 0, 0. Pēc tam šajā sarakstā tiek meklēta lielākā vērtība, kas ir pēdējās rakstzīmes pozīcija, kas nav skaitlis. Visbeidzot, tas atgriež visas rakstzīmes pēc šī punkta, kas ir skaitļi virknes beigās. Pēc tam funkcija VALUE to pārvērš atpakaļ skaitļā. Vienkārši, vai ne?

Kā izvilkt skaitli no virknes sākuma, izmantojot Flash Fill

Varat arī izmantot Flash Fill, lai izvilktu skaitli no virknes sākuma, pats aizpildot pirmo piemēru un ļaujot programmai Excel apturēt modeli.

Lai izvilktu skaitli no virknes sākuma, izmantojot Flash Fill:

1. Atlasiet šūnu pa labi no pirmās virknes, kurā ir numurs, kuru vēlaties izvilkt.
2. Ievadiet numuru, kas parādās virknes sākumā.
   
3. Ja visi jūsu dati ir nepārtrauktā kolonnā bez atstarpēm, atlasiet šūnu tieši zemāk.
   
4. Ja jūsu datos ir nepilnības, atlasiet pirmo šūnu un visas zem tām esošās šūnas, no kurām vēlaties iegūt skaitļus, tostarp visas tukšās rindas.
   
5. Nospiediet Ctrl+E operētājsistēmā Windows vai Cmd+E operētājsistēmā Mac, lai palaistu Flash Fill.
6. Atlasītajām šūnām tagad vajadzētu parādīt izvilktos skaitļus.
   

Kā izvilkt skaitli no virknes sākuma, izmantojot formulu

Varat arī izmantot formulu, lai izvilktu skaitli no virknes sākuma. Tas darbojas līdzīgi kā formula iepriekšējā sadaļā, bet atrod pirmās rakstzīmes, kas nav ciparu, pozīciju un atgriež visas rakstzīmes pirms tam.

Lai izvilktu skaitli no virknes sākuma, izmantojot formulu:

1. Atlasiet šūnu, kurā vēlaties parādīt izvilkto numuru.
2. Ievadiet šādu formulu, aizstājot “B18” ar šūnu, kurā ir jūsu virkne:

   =VĒRTĪBA(KREISI(B18,ATTIECĪBA(FALSE, ISNUMURS(VIDUS(B18,RINDA(INDIREKTS("1:"&LEN(B18)+1)),1)*1),0)-1))

3. Nospiediet Ievadiet un numurs tiks izvilkts.
   
4. Jūs varat izmantojiet automātisko aizpildīšanu programmā Excel lai kopētu formulu uz citām nepieciešamajām šūnām.
5. Ja vēlaties, lai tukšas rindas atgrieztu tukšu rezultātu, izmantojiet šo formulu:

   =IFERROR(VĒRTĪBA(LEFT(B18,MATCH(FALSE, ISNUMBER(VID(B18,RINDA(INDIREKTS("1:"&LEN(B18)+1)),1)*1),0)-1)" ")

Kā izvilkt ciparus no jebkuras vietas virknē, izmantojot Flash Fill

Kā minēts iepriekš, Flash Fill var nedarboties, ja pirmā šūna neatbilst dažu citu šūnu formātam. Šādā gadījumā jums, iespējams, būs manuāli jāaprēķina divas vai trīs šūnas, lai Flash Fill sniegtu vajadzīgo rezultātu.

Lai izvilktu skaitļus no jebkuras vietas virknē, izmantojot Flash Fill:

1. Šūnā blakus pirmajai virknei manuāli ierakstiet virknē esošos skaitļus.
   
2. Ja visi jūsu dati ir nepārtrauktā kolonnā, atlasiet šūnu tieši zemāk.
   
3. Ja jūsu datos ir nepilnības, atlasiet pirmo šūnu un visas šūnas zem tām, no kurām vēlaties iegūt skaitļus, tostarp visas tukšās rindas.
   
4. Nospiediet Ctrl+E operētājsistēmā Windows vai Cmd+E operētājsistēmā Mac, lai palaistu Flash Fill.
5. Šūnām tagad vajadzētu aizpildīt ar iegūtajiem skaitļiem.
   

Kā iegūt skaitļus no jebkuras vietas virknē, izmantojot formulu

Varat izmantot formulu, lai no virknes izvilktu visus skaitļus neatkarīgi no to atrašanās vietas.

Lai iegūtu skaitļus no jebkuras vietas virknē, izmantojot formulu:

1. Atlasiet šūnu, kurā vēlaties parādīt izvilkto numuru.
2. Ievadiet šādu formulu, aizstājot “B9” ar šūnu, kurā ir jūsu virkne:

   =SUMPRODUKTS(VIDUS(0&B9,LIELS(INDEKSS(ISNUMURS(--VID(B9,RINDA(INDIREKTS("1:"&LEN(B9))),1))*RINDA(INDIREKTS("1:"&LEN(B9) )),0),ROW(INDIRECT("1:"&LEN(B9))))+1,1)*10^ROW(INDIRECT("1:"&LEN(B9)))/10)

3. Jūsu formulai ir jāizņem visi skaitļi no virknes.
   
4. Ja vēlaties, lai tukšas šūnas atgrieztu tukšu, nevis nulli, izmantojiet šo formulu:

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

5. Jūs varat izmantojiet automātisko aizpildīšanu lai lietotu formulu jebkurām citām izklājlapas šūnām.
6. Visas virknes, kurās nav skaitļu, atgriezīs nulli. Jūs varat uzzināt, kā pārvērst nulli par domuzīmi programmā Excel ja vēlaties to mainīt.

Paplašiniet savas Excel prasmes

Mācīšanās, kā programmā Excel izvilkt skaitli no virknes, var palīdzēt ātri iegūt vajadzīgos datus no virkņu saraksta. Lai gan nepieciešamās formulas var būt sarežģītas, jums nav precīzi jāsaprot, kā tās darbojas; tikai tā viņi dara!

Ir vēl vairāk noderīgu Excel triku, ko varat apgūt, lai paceltu savas prasmes uz nākamo līmeni. Jūs varat mācīties kā skaitīt šūnas ar tekstu tajos vai kā aprēķināt personas vecumu no dzimšanas datuma. Jūs pat varat izveidot nejaušu skaitļu ģeneratoru programmā Excel.