Podjela ljepljivog teksta pomoću funkcije FILTER.XML

sadržaj

Nedavno smo raspravljali o korišćenju funkcije FILTER.XML za uvoz XML podataka sa Interneta – što je glavni zadatak za koji je ova funkcija, zapravo, namenjena. Usput se, međutim, pojavila još jedna neočekivana i lijepa upotreba ove funkcije – za brzo dijeljenje ljepljivog teksta na ćelije.

Recimo da imamo kolonu podataka ovako:

Podjela ljepljivog teksta pomoću funkcije FILTER.XML

Naravno, radi praktičnosti, želio bih ga podijeliti u zasebne kolone: ​​naziv kompanije, grad, ulica, kuća. To možete učiniti na mnogo različitih načina:

  • upotreba Tekst po kolonama sa kartice podaci (Podaci — Tekst u kolone) i idi tri koraka Parser teksta. Ali ako se podaci sutra promijene, morat ćete ponoviti cijeli proces.
  • Učitajte ove podatke u Power Query i podijelite ih tamo, a zatim ih učitajte nazad na list, a zatim ažurirajte upit kada se podaci promijene (što je već lakše).
  • Ako trebate ažurirati u hodu, onda možete napisati neke vrlo složene formule za pronalaženje zareza i izdvajanje teksta između njih.

I možete to učiniti elegantnije i koristiti funkciju FILTER.XML, ali kakve to veze ima s tim?

Funkcija FILTER.XML prima kao svoj početni argument XML kod — tekst označen posebnim oznakama i atributima, a zatim ga analizira u njegove komponente, izdvajajući fragmente podataka koji su nam potrebni. XML kod obično izgleda otprilike ovako:

Podjela ljepljivog teksta pomoću funkcije FILTER.XML

U XML-u, svaki element podataka mora biti zatvoren u tagovima. Oznaka je neki tekst (u primjeru iznad to je menadžer, ime, profit) zatvoren u uglastim zagradama. Oznake uvijek dolaze u paru – otvaranje i zatvaranje (sa kosom crtom dodanom na početku).

Funkcija FILTER.XML može lako izdvojiti sadržaj svih oznaka koje su nam potrebne, na primjer, imena svih menadžera, i (što je najvažnije) prikazati ih sve odjednom na jednoj listi. Dakle, naš zadatak je da izvornom tekstu dodamo oznake, pretvarajući ga u XML kod pogodan za kasniju analizu pomoću funkcije FILTER.XML.

Ako uzmemo prvu adresu sa naše liste kao primjer, onda ćemo je morati pretvoriti u ovu konstrukciju:

Podjela ljepljivog teksta pomoću funkcije FILTER.XML

Pozvao sam globalnu oznaku za otvaranje i zatvaranje teksta t, a oznake koje uokviruju svaki element su s., ali možete koristiti bilo koje druge oznake – nije važno.

Ako iz ovog koda uklonimo uvlake i prijelome redaka - potpuno, usput, neobavezno i ​​dodano samo radi jasnoće, onda će se sve ovo pretvoriti u red:

Podjela ljepljivog teksta pomoću funkcije FILTER.XML

I već se relativno lako može dobiti sa izvorne adrese zamjenom zareza u njoj s nekoliko oznaka koristeći funkciju SUBSTITUTE (ZAMJENA) i lijepljenje sa simbolom & na početku i na kraju uvodnih i završnih oznaka:

Podjela ljepljivog teksta pomoću funkcije FILTER.XML

Za horizontalno proširenje rezultujućeg raspona koristimo standardnu ​​funkciju TRANSP (TRANSPONIRAJ), umotavajući našu formulu u to:

Podjela ljepljivog teksta pomoću funkcije FILTER.XML

Važna karakteristika cijelog ovog dizajna je da u novoj verziji Office 2021 i Office 365 s podrškom za dinamičke nizove nisu potrebni posebni pokreti za unos – samo unesite i kliknite na ući – sama formula zauzima potreban broj ćelija i sve radi sa praskom. U prethodnim verzijama, gdje još nije bilo dinamičkih nizova, morat ćete prvo odabrati dovoljan broj praznih ćelija prije unosa formule (možete sa marginom), a nakon kreiranja formule, pritisnite prečicu na tipkovnici Ctrl+smjena+ućida ga unesete kao formulu niza.

Sličan trik se može koristiti kada se tekst koji se zaglavio u jednu ćeliju odvaja prelom reda:

Podjela ljepljivog teksta pomoću funkcije FILTER.XML

Jedina razlika u odnosu na prethodni primjer je u tome što umjesto zareza ovdje zamjenjujemo nevidljivi znak Alt + Enter za prijelom reda, koji se može specificirati u formuli pomoću funkcije CHAR sa kodom 10.

  • Suptilnosti rada s prijelomima redaka (Alt + Enter) u Excelu
  • Podijelite tekst po stupcima u Excelu
  • Zamjena teksta sa SUBSTITUTE

Ostavite odgovor