Pavisam nesen mēs apspriedām funkcijas FILTER.XML izmantošanu XML datu importēšanai no interneta – galvenais uzdevums, kuram šī funkcija patiesībā ir paredzēta. Tomēr pa ceļam ir parādījies vēl viens negaidīts un skaists šīs funkcijas lietojums – lipīga teksta ātrai sadalīšanai šūnās.
Pieņemsim, ka mums ir šāda datu kolonna:
Protams, ērtības labad es vēlētos to sadalīt atsevišķās kolonnās: uzņēmuma nosaukums, pilsēta, iela, māja. To var izdarīt vairākos dažādos veidos:
- lietošana Teksts pa kolonnām no cilnes Datums (Dati — teksts kolonnās) un ej trīs soļus Teksta parsētājs. Bet, ja dati mainīsies rīt, jums viss process būs jāatkārto vēlreiz.
- Ielādējiet šos datus programmā Power Query un sadaliet tos tur, un pēc tam augšupielādējiet tos atpakaļ lapā un pēc tam atjauniniet vaicājumu, kad dati mainās (tas jau ir vieglāk).
- Ja jums ir jāatjaunina lidojuma laikā, varat uzrakstīt dažas ļoti sarežģītas formulas, lai atrastu komatus un izvilktu tekstu starp tiem.
Un jūs varat to izdarīt elegantāk un izmantot FILTER.XML funkciju, bet kāds tam sakars?
Funkcija FILTER.XML kā sākotnējo argumentu saņem XML kodu — tekstu, kas atzīmēts ar īpašiem tagiem un atribūtiem, un pēc tam parsē to savos komponentos, iegūstot mums nepieciešamos datu fragmentus. XML kods parasti izskatās šādi:
XML formātā katrs datu elements ir jāiekļauj tagos. Tags ir teksts (iepriekš esošajā piemērā tas ir vadītājs, vārds, peļņa), kas ievietots leņķa iekavās. Tagi vienmēr ir pa pāriem – atverot un aizverot (sākumam pievienojot slīpsvītru).
Funkcija FILTER.XML var viegli izvilkt visu mums nepieciešamo tagu saturu, piemēram, visu pārvaldnieku vārdus, un (pats galvenais) tos visus uzreiz parādīt vienā sarakstā. Tātad mūsu uzdevums ir pievienot tagus avota tekstam, pārvēršot to par XML kodu, kas ir piemērots turpmākai analīzei, izmantojot funkciju FILTER.XML.
Ja mēs ņemam pirmo adresi no mūsu saraksta kā piemēru, tad mums tā būs jāpārvērš šādā konstrukcijā:
Es izsaucu globālo visa teksta atvēršanas un aizvēršanas tagu t, un katra elementa ierāmēšanas atzīmes ir s., bet varat izmantot jebkurus citus apzīmējumus – tas nav svarīgi.
Ja no šī koda noņemam atkāpes un rindiņu pārtraukumus – starp citu, neobligāti un pievienoti tikai skaidrības labad, tad tas viss pārvērtīsies rindā:
Un to jau var salīdzinoši viegli iegūt no avota adreses, aizstājot tajā komatus ar pāris tagiem izmantojot funkciju Aizstāt (AIZSTĀTĀJS) un līmēšana ar simbolu & sākuma un beigu tagu sākumā un beigās:
Lai paplašinātu iegūto diapazonu horizontāli, mēs izmantojam standarta funkciju TRANSPS (TRANSPOZĒT), iesaiņojot tajā mūsu formulu:
Svarīga visa šī dizaina iezīme ir tā, ka jaunajā Office 2021 un Office 365 versijā ar dinamisko masīvu atbalstu ievadei nav nepieciešami īpaši žesti – vienkārši ievadiet un noklikšķiniet uz ienākt – pati formula aizņem vajadzīgo šūnu skaitu un viss strādā ar āķīti. Iepriekšējās versijās, kur vēl nebija dinamisku masīvu, pirms formulas ievadīšanas vispirms būs jāatlasa pietiekams skaits tukšu šūnu (var ar piemali), un pēc formulas izveides nospiediet īsinājumtaustiņu Ctrl+maiņa+ienāktlai ievadītu to kā masīva formulu.
Līdzīgu triku var izmantot, atdalot tekstu, kas salīmēts vienā šūnā, izmantojot rindiņas pārtraukumu:
Vienīgā atšķirība ar iepriekšējo piemēru ir tāda, ka komata vietā šeit mēs aizstājam neredzamo Alt + Enter rindiņas pārtraukuma rakstzīmi, ko var norādīt formulā, izmantojot CHAR funkciju ar kodu 10.
- Darba ar rindiņu pārtraukumiem (Alt + Enter) smalkumi programmā Excel
- Sadaliet tekstu pa kolonnām programmā Excel
- Teksts tiek aizstāts ar SUBSTITUTE