Vispārēja formula
=FILTERXML(""&SUBSTITUTE(A1,",","")&"","//y")
Kopsavilkums
Lai sadalītu tekstu ar atdalītāju un pārveidotu rezultātu masīvā, varat izmantot funkciju FILTERXML ar funkciju SUBSTITUTE un TRANSPOSE palīdzību. Parādītajā piemērā formula D5 ir:
=TRANSPOSE(FILTERXML(""&SUBSTITUTE(B5,",","")&"","//y"))
Piezīme: FILTERXML nav pieejams programmā Excel Mac datorā vai programmā Excel Online.
Piezīme: Šo triku es uzzināju no Bila Jelena MrExcel videoklipā.
Paskaidrojums
Programmā Excel nav funkcijas, kas veltīta teksta sadalīšanai masīvā, līdzīgi kā funkcija PHP explode vai Python split. Kā risinājumu varat izmantot funkciju FILTERXML pēc tam, kad vispirms XML marķējums ir pievienots tekstam.
Parādītajā piemērā mums ir vairākas ar komatiem atdalītas teksta virknes:
"Jim,Brown,33,Seattle,WA"
Mērķis ir sadalīt informāciju atsevišķās kolonnās, izmantojot komatu kā norobežotāju.
Pirmais uzdevums ir pievienot šim tekstam XML marķējumu, lai to varētu parsēt kā XML ar funkciju FILTERXML. Katru teksta lauku mēs patvaļīgi izveidosim par elementu, kas pievienots vecāka elementam. Mēs sākam ar funkciju SUBSTITUTE šeit:
SUBSTITUTE(B5,",","")
SUBSTITUTE rezultāts ir šāda teksta virkne:
"JimBrown33SeattleWA"
Lai nodrošinātu labi izveidotus XML tagus un visus elementus iesaiņotu vecāka elementā, mēs iepriekš pievienojam un pievienojam vairāk šādus XML tagus:
""&SUBSTITUTE(B5,",","")&""
Tādējādi tiek iegūta šāda teksta virkne (lasāmībai pievienoti rindiņu pārtraukumi)
" Jim Brown 33 Seattle WA "
Šis teksts tiek piegādāts tieši funkcijai FILTERXML kā arguments xml ar Xpath izteiksmi "// y":
FILTERXML("JimBrown33SeattleWA","//y")
Xpath ir parsēšanas valoda, un "// y" atlasa visus elementus. Rezultāts no FILTERXML ir šāds vertikāls masīvs:
("Jim";"Brown";33;"Seattle";"WA")
Tā kā šajā gadījumā mēs vēlamies horizontālu masīvu, mēs TRANSPOSE funkciju aptinam ap FILTERXML:
=TRANSPOSE(("Jim";"Brown";33;"Seattle";"WA"))
Rezultāts ir šāds horizontāls masīvs:
("Jim","Brown",33,"Seattle","WA")
kas izplūst diapazonā D5: H5 programmā Excel 365.
