Bendroji formulė
=FILTERXML(""&SUBSTITUTE(A1,",","")&"","//y")
Santrauka
Norėdami suskaidyti tekstą su skyrikliu ir paversti rezultatą masyvu, galite naudoti funkciją FILTERXML naudodami funkcijas PAKEITIMAS ir TRANSPOSAS. Parodytame pavyzdyje formulė D5 yra:
=TRANSPOSE(FILTERXML(""&SUBSTITUTE(B5,",","")&"","//y"))
Pastaba: „FILTERXML“ negalima „Excel“ sistemoje „Mac“ arba „Excel Online“.
Pastaba: aš išmokau šio triuko iš Billo „MrExcel“ vaizdo įraše.
Paaiškinimas
„Excel“ neturi funkcijos, skirtos tekstui padalyti į masyvą, panašios į PHP explode funkciją arba „Python“ padalijimo metodą. Norėdami išspręsti problemą, galite naudoti funkciją FILTERXML, prieš tai pridėję XML žymėjimą prie teksto.
Parodytame pavyzdyje turime keletą kableliais atskirtų teksto eilučių:
"Jim,Brown,33,Seattle,WA"
Tikslas yra padalinti informaciją į atskirus stulpelius, naudojant kablelį kaip atskyriklį.
Pirmoji užduotis yra pridėti XML žymėjimą prie šio teksto, kad jį būtų galima analizuoti kaip XML su funkcija FILTERXML. Kiekvieną teksto lauką savavališkai padarysime elementu, pridėtu pirminiu elementu. Mes pradedame funkciją PAKEISTI čia:
SUBSTITUTE(B5,",","")
Rezultatas iš „SUBSTITUTE“ yra tokia teksto eilutė:
"JimBrown33SeattleWA"
Norėdami užtikrinti gerai suformuotas XML žymas ir apvynioti visus elementus pagrindiniame elemente, mes pridedame ir pridedame daugiau tokių XML žymų:
""&SUBSTITUTE(B5,",","")&""
Taip gaunama tokia teksto eilutė (pridėtos eilutės pertraukos, kad būtų lengviau skaityti)
" Jim Brown 33 Seattle WA "
Šis tekstas pateikiamas tiesiai į funkciją FILTERXML kaip xml argumentą su Xpath išraiška „// y“:
FILTERXML("JimBrown33SeattleWA","//y")
„Xpath“ yra analizuojama kalba ir „// y“ parenka visus elementus. „FILTERXML“ rezultatas yra toks vertikalus masyvas:
("Jim";"Brown";33;"Seattle";"WA")
Kadangi šiuo atveju norime horizontalaus masyvo, funkciją TRANSPOSE apvyniojame aplink FILTERXML:
=TRANSPOSE(("Jim";"Brown";33;"Seattle";"WA"))
Rezultatas yra toks horizontalus masyvas:
("Jim","Brown",33,"Seattle","WA")
kuris „Excel 365“ patenka į D5: H5 diapazoną.
