Неодамна, разговаравме за употребата на функцијата FILTER.XML за увоз на XML податоци од Интернет - главната задача за која всушност е наменета оваа функција. На патот, сепак, се појави уште една неочекувана и убава употреба на оваа функција - за брзо делење на леплив текст во ќелии.
Да речеме дека имаме колона со податоци како оваа:
Се разбира, за погодност, би сакал да го поделам во посебни колони: име на компанија, град, улица, куќа. Можете да го направите ова на еден куп различни начини:
- Користете Текст по колони од јазичето податоци (Податоци - Текст во колони) и оди три чекори Парсер на текст. Но, ако податоците се променат утре, ќе мора повторно да го повторите целиот процес.
- Вчитајте ги овие податоци во Power Query и поделете ги таму, а потоа поставете ги назад на листот, а потоа ажурирајте го барањето кога податоците ќе се променат (што е веќе полесно).
- Ако треба да се ажурирате веднаш, тогаш можете да напишете неколку многу сложени формули за да најдете запирки и да го извлечете текстот меѓу нив.
И можете да го направите тоа поелегантно и да ја користите функцијата FILTER.XML, но каква врска има тоа со тоа?
Функцијата FILTER.XML добива како свој почетен аргумент XML-код - текст означен со специјални ознаки и атрибути, а потоа го анализира во неговите компоненти, извлекувајќи ги фрагментите од податоци што ни се потребни. XML кодот обично изгледа вака:
Во XML, секој податочен елемент мора да биде затворен во ознаки. Ознака е некој текст (во примерот погоре е менаџер, име, профит) затворен во аголни загради. Ознаките секогаш доаѓаат во парови – се отвораат и затвораат (со коса црта додадена на почетокот).
Функцијата FILTER.XML лесно може да ја извлече содржината на сите ознаки што ни се потребни, на пример, имињата на сите менаџери и (што е најважно) да ги прикаже сите одеднаш во една листа. Значи, нашата задача е да додадеме ознаки на изворниот текст, претворајќи го во XML код погоден за последователна анализа со функцијата FILTER.XML.
Ако ја земеме првата адреса од нашата листа како пример, тогаш ќе треба да ја претвориме во оваа конструкција:
Ја повикав глобалната ознака за отворање и затворање на целиот текст t, и ознаките што го врамуваат секој елемент се s., но можете да користите какви било други ознаки - не е важно.
Ако ги отстраниме вовлекувањата и прекините на линиите од овој код - целосно, патем, опционални и додадени само за јасност, тогаш сето ова ќе се претвори во линија:
И веќе може релативно лесно да се добие од изворната адреса со замена на запирки во неа со неколку ознаки користејќи ја функцијата СУПСТИТУТ (ЗАМЕНА) и лепење со симболот & на почетокот и на крајот на ознаките за отворање и затворање:
За да го прошириме добиениот опсег хоризонтално, ја користиме стандардната функција ТРАНСП (ТРАНСПОЗИРАЈ), завиткувајќи ја нашата формула во неа:
Важна карактеристика на целиот овој дизајн е тоа што во новата верзија на Office 2021 и Office 365 со поддршка за динамички низи, не се потребни посебни гестови за внесување - само внесете и кликнете на Внесете – самата формула го зафаќа бројот на ќелии што и се потребни и сè работи со тресок. Во претходните верзии, каде што сè уште немаше динамички низи, ќе треба прво да изберете доволен број празни ќелии пред да ја внесете формулата (можете со маргина), а откако ќе ја креирате формулата, притиснете ја кратенката на тастатурата Ctrl+Префрлат+Внесетеза да го внесете како формула за низа.
Сличен трик може да се користи кога се одвојува текст заглавен заедно во една ќелија преку прекин на линијата:
Единствената разлика со претходниот пример е што наместо запирка, овде го заменуваме невидливиот знак за прекин на линијата Alt + Enter, кој може да се наведе во формулата користејќи ја функцијата CHAR со код 10.
- Суптилностите на работа со прекини на линии (Alt + Enter) во Excel
- Поделете го текстот по колони во Excel
- Замена на текстот со SUBSTITUTE