sadržaj
Već sam pisao o tome kako možete brzo zalijepiti tekst iz nekoliko ćelija u jednu i, obrnuto, raščlaniti dugi tekstualni niz na komponente. Pogledajmo sada bliži, ali malo složeniji zadatak – kako zalijepiti tekst iz nekoliko ćelija kada je ispunjen određeni uvjet.
Recimo da imamo bazu podataka kupaca, gde jedno ime kompanije može odgovarati nekoliko različitih mejlova njenih zaposlenih. Naš zadatak je da prikupimo sve adrese po nazivima kompanija i spojimo ih (odvojene zarezima ili tačkom i zarezom) kako bismo napravili, na primjer, mailing listu za kupce, odnosno dobili izlaz nešto poput:
Drugim riječima, potreban nam je alat koji će zalijepiti (povezati) tekst prema uvjetu – analog funkcije SUMMESLI (SUMIF), ali za tekst.
Metoda 0. Formula
Nije baš elegantan, ali najlakši način. Možete napisati jednostavnu formulu koja će provjeriti da li se kompanija u sljedećem redu razlikuje od prethodnog. Ako se ne razlikuje, zalijepite sljedeću adresu odvojenu zarezom. Ako se razlikuje, tada "resetujemo" akumulirano, počevši iznova:
Nedostaci ovog pristupa su očigledni: od svih ćelija dobijene dodatne kolone potrebne su nam samo zadnje za svako preduzeće (žuto). Ako je lista velika, da biste ih brzo odabrali, morat ćete dodati još jednu kolonu pomoću funkcije DLSTR (LEN), provjeravanje dužine akumuliranih nizova:
Sada možete filtrirati one i kopirati potrebno lijepljenje adresa za dalju upotrebu.
Metoda 1. Makrofunkcija lepljenja po jednom uslovu
Ako originalna lista nije sortirana po kompaniji, gornja jednostavna formula ne radi, ali možete lako zaobići malom prilagođenom funkcijom u VBA. Otvorite Visual Basic Editor pritiskom na prečicu na tastaturi Alt + F11 ili pomoću dugmeta Visual Basic tabulator Developer (programer). U prozoru koji se otvori ubacite novi prazan modul kroz meni Umetak – Modul i kopirajte tekst naše funkcije tamo:
Funkcija MergeIf(TextRange As Range, SearchRange As Range, Condition As String) Dim Delimeter As String, i As Long Delimeter = ", " lijepljenja nisu jednaka jedno drugom - izlazimo s greškom If SearchRange.Count <> TextRange.Count Zatim MergeIf = CVERr(xlErrRef) Izlaz iz funkcije Kraj Ako 'prođite kroz sve ćelije, provjerite uvjet i prikupite tekst u varijablu OutText Za i = 1 To SearchRange. Cells.Count If SearchRange.Cells(i) Like Condition Then OutText = OutText & TextRange.Cells(i) & Delimeter Next i 'prikaži rezultate bez posljednjeg graničnika MergeIf = Left(OutText, Len(OutText) - Len(Delimetar)) End funkcija
Ako se sada vratite u Microsoft Excel, onda na listi funkcija (dugme fx u traci formule ili kartici Formule – Umetanje funkcije) bit će moguće pronaći našu funkciju MergeIf u kategoriji Definisano od strane korisnika (Definisano od strane korisnika). Argumenti funkcije su sljedeći:
Metoda 2. Spojite tekst prema netačnom stanju
Ako zamijenimo prvi znak u 13. redu našeg makroa = operatoru približnog podudaranja kao, tada će biti moguće izvršiti lijepljenje netačnim podudaranjem početnih podataka sa kriterijem odabira. Na primjer, ako naziv kompanije može biti napisan u različitim varijantama, možemo ih sve provjeriti i prikupiti jednom funkcijom:
Podržani su standardni zamjenski znakovi:
- zvjezdica (*) – označava bilo koji broj znakova (uključujući njihovo odsustvo)
- upitnik (?) – označava bilo koji pojedinačni znak
- znak funte (#) – označava bilo koju cifru (0-9)
Operator Like podrazumevano je osetljiv na velika i mala slova, tj. razume, na primer, „Orion“ i „orion“ kao različite kompanije. Da zanemarite velika i mala slova, možete dodati red na sam početak modula u uređivaču Visual Basica Opcija Uporediti tekst, koji će prebaciti Like na velika i mala slova.
Na ovaj način možete sastaviti vrlo složene maske za provjeru uslova, na primjer:
- ?1##??777RUS – izbor svih registarskih tablica regije 777, počevši od 1
- DOO* – sve kompanije čije ime počinje sa DOO
- ##7## – svi proizvodi sa petocifrenim digitalnim kodom, gde je treća cifra 7
- ????? – sva imena od pet slova, itd.
Metoda 3. Makro funkcija za lijepljenje teksta pod dva uslova
U radu može doći do problema kada treba da povežete tekst sa više uslova. Na primjer, zamislimo da je u našoj prethodnoj tabeli dodana još jedna kolona sa gradom, a lijepljenje treba izvršiti ne samo za datu kompaniju, već i za dati grad. U ovom slučaju, naša funkcija će se morati malo modernizirati dodavanjem još jedne provjere raspona:
Funkcija MergeIfs(TextRange As Range, SearchRange1 kao Range, Condition1 As String, SearchRange2 As Range, Condition2 As String) Dim Delimeter As String, i As Long Delimeter = ", " 'znakovi za razdvajanje (mogu se zamijeniti razmakom ili ; itd.) e.) 'ako rasponi validacije i lijepljenja nisu jednaki jedan drugom, izađite s greškom Ako SearchRange1.Count <> TextRange.Count Ili SearchRange2.Count <> TextRange.Count Onda MergeIfs = CVERr(xlErrRef) Izađite iz funkcije Kraj ako 'prođite kroz sve ćelije, provjerite sve uvjete i sakupite tekst u varijablu OutText Za i = 1 Za SearchRange1.Cells.Count Ako SearchRange1.Cells(i) = Condition1 i SearchRange2.Cells(i) = Condition2 Tada OutText = OutText & TextRange.Cells(i) & Delimeter End If Next i 'prikaži rezultate bez posljednjeg graničnika MergeIfs = Lijevo(OutText, Len(OutText) - Len(Delimetar)) Funkcija kraja
Biće primenjen na potpuno isti način – samo argumente sada treba više specificirati:
Metod 4. Grupisanje i lijepljenje u Power Queryju
Problem možete riješiti bez programiranja u VBA, ako koristite besplatni Power Query dodatak. Za Excel 2010-2013 može se preuzeti ovdje, au Excel 2016 je već ugrađen po defaultu. Redoslijed radnji će biti sljedeći:
Power Query ne zna da radi sa regularnim tabelama, pa je prvi korak da našu tabelu pretvorimo u „pametnu“. Da biste to učinili, odaberite ga i pritisnite kombinaciju Ctrl+T ili izaberite sa kartice Početna – Formatirajte kao tabelu (Početna — Format kao tabela). Na kartici koja se tada pojavljuje konstruktor (Dizajn) možete podesiti ime tabele (napustio sam standardni Tabela 1):
Sada učitajmo našu tabelu u Power Query dodatak. Da biste to učinili, na kartici podaci (ako imate Excel 2016) ili na kartici Power Query (ako imate Excel 2010-2013) kliknite Sa stola (Podaci — iz tabele):
U prozoru uređivača upita koji se otvori odaberite kolonu klikom na zaglavlje Kompanija i pritisnite dugme iznad grupa (Grupiraj po). Unesite naziv nove kolone i tip operacije u grupisanju – Sve linije (Svi redovi):
Kliknite OK i dobićemo mini tabelu grupisanih vrednosti za svaku kompaniju. Sadržaj tabela je jasno vidljiv ako kliknete lijevom tipkom miša na bijelu pozadinu ćelija (ne na tekst!) u rezultirajućem stupcu:
Sada dodajmo još jednu kolonu, gdje, koristeći funkciju, lijepimo sadržaj stupaca Address u svaku od mini tabela, odvojenih zarezima. Da biste to učinili, na kartici Dodajte stupac pritisnemo Prilagođena kolona (Dodaj kolonu — Prilagođena kolona) i u prozoru koji se pojavi unesite naziv nove kolone i formulu za spajanje na M jeziku ugrađenom u Power Query:
Imajte na umu da su sve M-funkcije osjetljive na velika i mala slova (za razliku od Excela). Nakon klika na OK dobijamo novu kolonu sa zalijepljenim adresama:
Ostaje ukloniti već nepotreban stupac TableAddresses (desni klik na naslov) Izbriši kolonu) i učitajte rezultate na listu klikom na karticu Početna — Zatvorite i preuzmite (Početna — Zatvori i učitaj):
Važna nijansa: Za razliku od prethodnih metoda (funkcija), tabele iz Power Queryja se ne ažuriraju automatski. Ako u budućnosti bude bilo kakvih promjena u izvornim podacima, morat ćete kliknuti desnom tipkom miša bilo gdje u tablici rezultata i odabrati naredbu Ažurirajte i sačuvajte (Osvježiti).
- Kako podijeliti dugački tekstualni niz na dijelove
- Nekoliko načina za lijepljenje teksta iz različitih ćelija u jednu
- Korištenje operatora Like za testiranje teksta u odnosu na masku