Kamakailan lamang, tinalakay namin ang paggamit ng FILTER.XML function upang mag-import ng XML data mula sa Internet – ang pangunahing gawain kung saan ang function na ito, sa katunayan, ay nilayon. Sa kabilang banda, gayunpaman, ang isa pang hindi inaasahang at magandang paggamit ng function na ito ay lumitaw - para sa mabilis na paghahati ng malagkit na teksto sa mga cell.
Sabihin nating mayroon kaming column ng data tulad nito:
Siyempre, para sa kaginhawahan, nais kong hatiin ito sa magkakahiwalay na mga hanay: pangalan ng kumpanya, lungsod, kalye, bahay. Magagawa mo ito sa iba't ibang paraan:
- paggamit Teksto ayon sa mga hanay mula sa tab data (Data — Teksto sa mga hanay) at pumunta ng tatlong hakbang Pang-parse ng text. Ngunit kung magbago ang data bukas, kakailanganin mong ulitin muli ang buong proseso.
- I-load ang data na ito sa Power Query at hatiin ito doon, at pagkatapos ay i-upload ito pabalik sa sheet, at pagkatapos ay i-update ang query kapag nagbago ang data (na mas madali na).
- Kung kailangan mong mag-update sa mabilisang, maaari kang sumulat ng ilang napaka-kumplikadong mga formula upang makahanap ng mga kuwit at kunin ang teksto sa pagitan ng mga ito.
At maaari mo itong gawin nang mas elegante at gamitin ang FILTER.XML function, ngunit ano ang kinalaman nito?
Ang FILTER.XML function ay tumatanggap bilang paunang argumento nito ng XML code — text na minarkahan ng mga espesyal na tag at attribute, at pagkatapos ay i-parse ito sa mga bahagi nito, na kinukuha ang mga fragment ng data na kailangan namin. Karaniwang ganito ang hitsura ng XML code:
Sa XML, ang bawat elemento ng data ay dapat na nakapaloob sa mga tag. Ang tag ay ilang text (sa halimbawa sa itaas ito ay manager, pangalan, tubo) na nakapaloob sa mga anggulong bracket. Palaging magkapares ang mga tag – pagbubukas at pagsasara (na may idinagdag na slash sa simula).
Ang FILTER.XML function ay madaling makuha ang mga nilalaman ng lahat ng mga tag na kailangan namin, halimbawa, ang mga pangalan ng lahat ng mga tagapamahala, at (pinaka-mahalaga) ipakita ang mga ito nang sabay-sabay sa isang listahan. Kaya't ang aming gawain ay magdagdag ng mga tag sa pinagmulang teksto, ginagawa itong XML code na angkop para sa kasunod na pagsusuri ng FILTER.XML function.
Kung kukunin namin ang unang address mula sa aming listahan bilang isang halimbawa, kakailanganin namin itong gawing konstruksyon na ito:
Tinawag ko ang pandaigdigang pagbubukas at pagsasara ng lahat ng tag ng teksto t, at ang mga tag na nag-frame sa bawat elemento ay s., ngunit maaari kang gumamit ng anumang iba pang mga pagtatalaga - hindi mahalaga.
Kung aalisin namin ang mga indent at line break mula sa code na ito - ganap na opsyonal at idinagdag lamang para sa kalinawan, ang lahat ng ito ay magiging isang linya:
At ito ay medyo madaling makuha mula sa pinagmulang address sa pamamagitan ng pagpapalit ng mga kuwit dito ng ilang mga tag gamit ang function SUBSTITUTE (PALIT) at pagdikit ng simbolo & sa simula at dulo ng pambungad at pagsasara ng mga tag:
Upang palawakin ang resultang hanay nang pahalang, ginagamit namin ang karaniwang function TRANSP (TRANSPOSE), binabalot ang aming formula dito:
Ang isang mahalagang tampok ng buong disenyo na ito ay na sa bagong bersyon ng Office 2021 at Office 365 na may suporta para sa mga dynamic na array, walang mga espesyal na galaw ang kinakailangan para sa pag-input - ipasok lamang at i-click ang Magpasok – ang formula mismo ay sumasakop sa bilang ng mga cell na kailangan nito at lahat ay gumagana nang malakas. Sa mga nakaraang bersyon, kung saan wala pang mga dynamic na array, kakailanganin mo munang pumili ng sapat na bilang ng mga walang laman na cell bago ilagay ang formula (maaari kang may margin), at pagkatapos gawin ang formula, pindutin ang keyboard shortcut Ctrl+Ilipat+Magpasokupang ipasok ito bilang isang array formula.
Maaaring gamitin ang isang katulad na trick kapag pinaghihiwalay ang text na nakadikit sa isang cell sa pamamagitan ng isang line break:
Ang pagkakaiba lang sa nakaraang halimbawa ay sa halip na isang kuwit, dito namin pinapalitan ang hindi nakikitang Alt + Enter line break na character, na maaaring tukuyin sa formula gamit ang CHAR function na may code 10.
- Ang mga subtleties ng pagtatrabaho sa mga line break (Alt + Enter) sa Excel
- Hatiin ang text ayon sa mga column sa Excel
- Pinapalitan ang text ng SUBSTITUTE