Noskatieties video - kā izvilkt skaitļus no teksta virknes programmā Excel (izmantojot formulu un VBA)
Programmā Excel nav iebūvētas funkcijas, lai izvilktu skaitļus no virknes šūnā (vai otrādi - noņemiet ciparu daļu un izvelciet teksta daļu no burtciparu virknes).
Tomēr to var izdarīt, izmantojot Excel funkciju kokteili vai vienkāršu VBA kodu.
Vispirms ļaujiet man parādīt, par ko es runāju.
Pieņemsim, ka jums ir datu kopa, kā parādīts zemāk, un vēlaties izvilkt skaitļus no virknes (kā parādīts zemāk):
Izvēlētā metode būs atkarīga arī no izmantotās Excel versijas:
- Versijām pirms Excel 2016 ir jāizmanto nedaudz garākas formulas
- Programmai Excel 2016 varat izmantot tikko ieviesto funkciju TEXTJOIN
- VBA metodi var izmantot visās Excel versijās
Noklikšķiniet šeit, lai lejupielādētu parauga failu
Skaitļu izvilkšana no virknes programmā Excel (Excel 2016 formula)
Šī formula darbosies tikai programmā Excel 2016, jo tā izmanto nesen ieviesto funkciju TEXTJOIN.
Arī šī formula var iegūt skaitļus, kas atrodas teksta virknes sākumā, beigās vai vidū.
Ņemiet vērā, ka šajā sadaļā aprakstītā TEXTJOIN formula sniegtu visas ciparu rakstzīmes kopā. Piemēram, ja teksts ir “10 biļešu cena ir USD 200”, rezultāts būs 10200.Pieņemsim, ka jums ir datu kopa, kā parādīts zemāk, un vēlaties iegūt skaitļus no katras šūnas virknēm:
Zemāk ir formula, kas dos jums skaitlisku daļu no virknes programmā Excel.
= TEXTJOIN ("", TRUE, IFERROR ((MID (A2, ROW (INDIRECT ("1:" & LEN (A2))), 1)*1), ""))
Šī ir masīva formula, tāpēc jums jāizmanto “Control + Shift + Enter“Tā vietā, lai izmantotu taustiņu Enter.
Ja teksta virknē nav ciparu, šī formula atgriež tukšu (tukšu virkni).
Kā šī formula darbojas?
Ļaujiet man lauzt šo formulu un mēģināt izskaidrot, kā tā darbojas:
- Rinda (netieša (“1:” & LEN (A2))) - šī formulas daļa sniegtu skaitļu sēriju, sākot no viena. Funkcija LEN formulā atgriež virknes kopējo rakstzīmju skaitu. “Izmaksas ir USD 100” gadījumā tas atgriezīs 19. Tādējādi formulas kļūs par ROW (NETIEŠI (“1:19”). Funkcija ROW pēc tam atgriež skaitļu sēriju - {1; 2; 3 ; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14; 15; 16; 17; 18; 19}
- (MID (A2, ROW (NETIEŠS (“1:” & LEN (A2)))), 1)*1) - šī formulas daļa atgriež masīvu #VALUE! kļūdas vai skaitļi, pamatojoties uz virkni. Visas virknes teksta rakstzīmes kļūst par #VALUE! kļūdas un visas skaitliskās vērtības paliek tādas, kādas tās ir. Tas notiek, kad esam reizinājuši MID funkciju ar 1.
- IFERROR ((MID (A2, ROW (NETIEŠS (“1:” & LEN (A2))))), 1)*1), ””) - Ja tiek izmantota funkcija IFERROR, tā noņemtu visu #VALUE! kļūdas un paliks tikai skaitļi. Šīs daļas iznākums izskatītos šādi - {“”; ””; ””; ””; ”” ”;” ”;” ”; ""; ""; ""; 1; 0; 0}
- = TEXTJOIN (“”, TRUE, IFERROR ((MID (A2, ROW (NETIEŠS (“1:” & LEN (A2))), 1)*1), ””)) - TEXTJOIN funkcija tagad vienkārši apvieno virkņu rakstzīmes kas paliek (tie ir tikai skaitļi) un ignorē tukšo virkni.
Lejupielādējiet parauga failu
To pašu loģiku var izmantot arī, lai izvilktu teksta daļu no burtciparu virknes. Zemāk ir formula, kas iegūtu teksta daļu no virknes:
= TEXTJOIN ("", TRUE, IF (ISERROR (MID (A2, ROW (NETIEŠS ("1:" & LEN (A2))), 1)*1), MID (A2, ROW (NETIEŠS ("1:" & LEN) (A2))), 1), ""))
Nelielas izmaiņas šajā formulā ir tas, ka IF funkcija tiek izmantota, lai pārbaudītu, vai masīvs, ko iegūstam no MID funkcijas, ir kļūdas. Ja tā ir kļūda, tā saglabā vērtību, aizstājot to ar tukšu.
Tad TEXTJOIN tiek izmantots, lai apvienotu visas teksta rakstzīmes.
Uzmanību!: Lai gan šī formula darbojas lieliski, tajā tiek izmantota gaistoša funkcija (funkcija NETIEŠA). Tas nozīmē, ka, ja jūs to izmantojat ar milzīgu datu kopu, rezultātu iegūšana var aizņemt kādu laiku. Vislabāk ir izveidot dublējumu, pirms izmantojat šo formulu programmā Excel.Skaitļu izvilkšana no virknes programmā Excel (programmai Excel 2013/2010/2007)
Ja jums ir programma Excel 2013. 2010. vai 2007. gads, nevarat izmantot formulu TEXTJOIN, tāpēc, lai to paveiktu, jums būs jāizmanto sarežģīta formula.
Pieņemsim, ka jums ir datu kopa, kā parādīts zemāk, un vēlaties izvilkt visus skaitļus virknē katrā šūnā.
Zemāk esošā formula to veiks:
= JA (SUM (LEN (A2) -LEN (AIZSTĀJIS (A2, {"0", "1", "2", "3", "4", "5", "6", "7", " 8 "," 9 "}," ")))> 0, SUMPRODUCT (MID (0 & A2, LARGE (INDEX (ISNUMBER (-MID (A2, ROW (INDIRECT (" $ 1: $ "& LEN (A2)))))), 1)) * ROW (NETIEŠS ("$ 1: $" & LEN (A2))), 0), ROW (NETIEŠS ("$ 1: $" & LEN (A2)))))+1,1) * 10^ROW (NETIEŠS ("$ 1: $" un LEN (A2)))/10), "")
Ja teksta virknē nav skaitļa, šī formula atgriež tukšu (tukša virkne).
Lai gan šī ir masīva formula, jūs nevajag lai to izmantotu, nospiediet taustiņu kombināciju “Control-Shift-Enter”. Šai formulai darbojas vienkārša ievadīšana.
Pateicība šai formulai tiek piešķirta pārsteidzošajam Excel kunga forumam.
Atkal šī formula iegūs visus virknes ciparus neatkarīgi no pozīcijas. Piemēram, ja teksts ir “10 biļešu cena ir USD 200”, rezultāts būs 10200.
Uzmanību!: Lai gan šī formula darbojas lieliski, tajā tiek izmantota gaistoša funkcija (funkcija NETIEŠA). Tas nozīmē, ka gadījumā, ja izmantojat šo informāciju ar milzīgu datu kopu, rezultātu iegūšana var aizņemt kādu laiku. Vislabāk ir izveidot dublējumu, pirms izmantojat šo formulu programmā Excel.Atdaliet tekstu un ciparus programmā Excel, izmantojot VBA
Ja teksta un ciparu atdalīšana (vai skaitļu izvilkšana no teksta) jums bieži jādara, varat izmantot arī VBA metodi.
Viss, kas jums jādara, ir izmantot vienkāršu VBA kodu, lai programmā Excel izveidotu pielāgotu lietotāja definētu funkciju (UDF), un pēc tam garu un sarežģītu formulu vietā izmantojiet šo VBA formulu.
Ļaujiet man parādīt, kā VBA izveidot divas formulas - vienu, lai izvilktu ciparus, bet otru, lai izvilktu tekstu no virknes.
Skaitļu izvilkšana no virknes programmā Excel (izmantojot VBA)
Šajā daļā es parādīšu, kā izveidot pielāgotu funkciju, lai no virknes iegūtu tikai skaitlisko daļu.
Zemāk ir VBA kods, ko mēs izmantosim, lai izveidotu šo pielāgoto funkciju:
Funkcija GetNumeric (šūnaRef kā virkne) GetNumeric = Rezultāta beigu funkcija
Tālāk ir norādītas darbības, lai izveidotu šo funkciju un pēc tam to izmantotu darblapā.
- Dodieties uz cilni Izstrādātājs.
- Noklikšķiniet uz Visual Basic (varat izmantot arī īsinājumtaustiņu ALT + F11)
- Atvērtajā VB redaktora aizmugurējā vidē ar peles labo pogu noklikšķiniet uz jebkura darbgrāmatas objekta.
- Dodieties uz Ievietot un noklikšķiniet uz moduļa. Tas ievietos darbgrāmatas moduļa objektu.
- Logā Moduļa kods nokopējiet un ielīmējiet iepriekš minēto VBA kodu.
- Aizveriet VB redaktoru.
Tagad darblapā varēsit izmantot funkciju GetText. Tā kā mēs esam veikuši visu smago celšanu pašā kodā, viss, kas jums jādara, ir izmantot formulu = GetNumeric (A2).
Tādējādi jūs uzreiz iegūsit tikai virknes ciparu daļu.
Ņemiet vērā: tā kā darbgrāmatā tagad ir VBA kods, tā jāsaglabā ar paplašinājumu .xls vai .xlsm.
Lejupielādējiet parauga failu
Ja šī formula ir jāizmanto bieži, varat to saglabāt arī savā personīgajā makro darbgrāmatā. Tas ļaus jums izmantot šo pielāgoto formulu jebkurā Excel darbgrāmatā, ar kuru strādājat.
Izvilkt tekstu no virknes programmā Excel (izmantojot VBA)
Šajā daļā es parādīšu, kā izveidot pielāgotu funkciju, lai no virknes iegūtu tikai teksta daļu.
Zemāk ir VBA kods, ko mēs izmantosim, lai izveidotu šo pielāgoto funkciju:
Funkcija GetText (šūnaRef kā virkne) ) Tālāk i GetText = Rezultāta beigu funkcija
Tālāk ir norādītas darbības, lai izveidotu šo funkciju un pēc tam to izmantotu darblapā.
- Dodieties uz cilni Izstrādātājs.
- Noklikšķiniet uz Visual Basic (varat izmantot arī īsinājumtaustiņu ALT + F11)
- Atvērtajā VB redaktora aizmugurējā vidē ar peles labo pogu noklikšķiniet uz jebkura darbgrāmatas objekta.
- Dodieties uz Ievietot un noklikšķiniet uz moduļa. Tas ievietos darbgrāmatas moduļa objektu.
- Ja jums jau ir modulis, veiciet dubultklikšķi uz tā (nav nepieciešams ievietot jaunu, ja tas jau ir).
- Ja jums jau ir modulis, veiciet dubultklikšķi uz tā (nav nepieciešams ievietot jaunu, ja tas jau ir).
- Logā Moduļa kods nokopējiet un ielīmējiet iepriekš minēto VBA kodu.
- Aizveriet VB redaktoru.
Tagad darblapā varēsit izmantot funkciju GetNumeric. Tā kā mēs esam veikuši visu smago celšanu pašā kodā, viss, kas jums jādara, ir izmantot formulu = GetText (A2).
Tādējādi jūs uzreiz iegūsit tikai virknes ciparu daļu.
Ņemiet vērā: tā kā darbgrāmatā tagad ir VBA kods, tā jāsaglabā ar paplašinājumu .xls vai .xlsm.
Ja šī formula ir jāizmanto bieži, varat to saglabāt arī savā personīgajā makro darbgrāmatā. Tas ļaus jums izmantot šo pielāgoto formulu jebkurā Excel darbgrāmatā, ar kuru strādājat.
Ja izmantojat Excel 2013 vai iepriekšējās versijas un jums tās nav
