Sadržaj
Već sam pisao o tome kako možete brzo zalijepiti tekst iz nekoliko ćelija u jednu i, obrnuto, raščlaniti dugačak tekstualni niz na komponente. Sada pogledajmo 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, gdje jedno ime tvrtke može odgovarati nekoliko različitih e-mailova njenih zaposlenika. Naš zadatak je prikupiti sve adrese po nazivima tvrtki i spojiti ih (odvojene zarezom ili točkom i zarezom) kako bismo napravili npr. mailing listu za kupce, tj. dobili ispis otprilike:
Drugim riječima, potreban nam je alat koji će lijepiti (linkati) 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 razlikuje li se tvrtka u sljedećem retku od prethodne. Ako se ne razlikuje, zalijepite sljedeću adresu odvojenu zarezom. Ako se razlikuje, tada "resetiramo" akumulirano, počevši ispočetka:
Nedostaci ovog pristupa su očiti: od svih dobivenih ćelija dodatnog stupca trebamo samo posljednje za svaku tvrtku (žuto). Ako je popis velik, da biste ih brzo odabrali, morat ćete dodati još jedan stupac pomoću funkcije DLSTR (LEN), provjeravajući duljinu akumuliranih nizova:
Sada možete filtrirati one i kopirati potrebno lijepljenje adresa za daljnju upotrebu.
Metoda 1. Makrofunkcija lijepljenja jednim uvjetom
Ako izvorni popis nije razvrstan po tvrtki, tada gornja jednostavna formula ne radi, ali se lako možete snaći s malom prilagođenom funkcijom u VBA. Otvorite Visual Basic Editor pritiskom na tipkovni prečac Alt + F11 ili pomoću gumba Visual Basic kartica razvijač (Programer). U prozoru koji se otvori umetnite novi prazan modul kroz izbornik Umetak – Modul i tamo kopirajte tekst naše funkcije:
Funkcija MergeIf(TextRange As Range, SearchRange As Range, Condition As String) Dim Delimeter As String, i As Long Delimeter = ", " lijepljenja nisu međusobno jednaka - izlazimo s pogreškom If SearchRange.Count <> TextRange.Count Tada MergeIf = CVErr(xlErrRef) Izlaz iz funkcije End If 'proći kroz sve ćelije, provjeriti uvjet i prikupiti tekst u varijabli OutText For 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(Delimeter)) End funkcija
Ako se sada vratite u Microsoft Excel, tada na popisu funkcija (gumb fx u traci formule ili kartici Formule – Umetni funkciju) bit će moguće pronaći našu funkciju SpojiIf u kategoriji Korisnik definiran (Korisnik definiran). Argumenti funkcije su sljedeći:
Metoda 2. Spajanje teksta prema netočnom uvjetu
Ako zamijenimo prvi znak u 13. retku našeg makroa = operatoru približnog podudaranja Like, tada će biti moguće izvršiti lijepljenje netočnim podudaranjem početnih podataka s kriterijem odabira. Na primjer, ako se naziv tvrtke može napisati u različitim varijantama, tada ih možemo sve provjeriti i prikupiti jednom funkcijom:
Podržani su standardni zamjenski znakovi:
- zvjezdica (*) – označava bilo koji broj bilo kojih znakova (uključujući njihov nedostatak)
- upitnik (?) – označava bilo koji pojedinačni znak
- znak funte (#) – označava bilo koju jednu znamenku (0-9)
Prema zadanim postavkama, operator Like razlikuje velika i mala slova, tj. razumije npr. “Orion” i “orion” kao različite tvrtke. Da biste zanemarili velika i mala slova, možete dodati redak na samom početku modula u uređivaču Visual Basica Opcija Usporedi tekst, koji će prebaciti Like da ne razlikuje velika i mala slova.
Na ovaj način možete sastaviti vrlo složene maske za provjeru uvjeta, na primjer:
- ?1##??777RUS – izbor svih registarskih tablica regije 777, počevši od 1
- LLC* – sve tvrtke čiji naziv počinje s LLC
- ##7## – svi proizvodi s peteroznamenkastim digitalnim kodom, gdje je treća znamenka 7
- ?????? – sva imena od pet slova itd.
Metoda 3. Makro funkcija za lijepljenje teksta pod dva uvjeta
U radu se može pojaviti problem kada trebate povezati tekst s više od jednog uvjeta. Na primjer, zamislimo da je u našoj prethodnoj tablici dodan još jedan stupac s gradom, a lijepljenje treba izvršiti ne samo za određenu tvrtku, već i za određeni grad. U ovom slučaju, našu će funkciju morati malo modernizirati dodavanjem još jedne provjere raspona:
Funkcija MergeIfs(TextRange kao raspon, SearchRange1 kao raspon, Condition1 kao niz, SearchRange2 kao raspon, Condition2 kao niz) Dim Delimeter kao niz, i kao dugi razdjelnik = ", " 'znakovi razdjelnika (mogu se zamijeniti razmakom ili ; itd.) e.) 'ako rasponi provjere valjanosti i lijepljenja nisu međusobno jednaki, izađite s pogreškom If SearchRange1.Count <> TextRange.Count Or SearchRange2.Count <> TextRange.Count Then MergeIfs = CVErr(xlErrRef) Exit Function End If 'prođite kroz sve ćelije, provjerite sve uvjete i sakupite tekst u varijablu OutText For i = 1 To SearchRange1.Cells.Count If SearchRange1.Cells(i) = Condition1 And SearchRange2.Cells(i) = Condition2 Then OutText = OutText & TextRange.Cells(i) & Delimeter End If Next i 'prikaži rezultate bez posljednjeg graničnika MergeIfs = Left(OutText, Len(OutText) - Len(Delimeter)) End Function
Primijenit će se na potpuno isti način – samo argumente sada treba dodatno navesti:
Metoda 4. Grupiranje i lijepljenje u Power Queryju
Problem možete riješiti bez programiranja u VBA, ako koristite besplatni dodatak Power Query. Za Excel 2010-2013 može se preuzeti ovdje, au Excel 2016 već je ugrađen po defaultu. Redoslijed radnji bit će sljedeći:
Power Query ne zna raditi s običnim tablicama, pa je prvi korak pretvoriti našu tablicu u “pametnu”. Da biste to učinili, odaberite ga i pritisnite kombinaciju Ctrl+T ili odaberite s kartice Početna – Format kao tablica (Početna — Formatiraj kao tablicu). Na kartici koja se tada pojavljuje Konstruktor (Oblikovati) možete postaviti naziv tablice (napustio sam standard Tablica 1):
Učitajmo sada našu tablicu u dodatak Power Query. Da biste to učinili, na kartici Datum (ako imate Excel 2016) ili na kartici Power Query (ako imate Excel 2010-2013) kliknite Sa stola (Podaci — iz tablice):
U prozoru uređivača upita koji se otvori odaberite stupac klikom na zaglavlje O nama i pritisnite gornji gumb Klan (Grupiraj po). Unesite naziv novog stupca i vrstu operacije u grupiranje – Sve linije (Svi redovi):
Kliknite OK i dobit ćemo mini tablicu grupiranih vrijednosti za svaku tvrtku. Sadržaj tablica jasno je vidljiv ako kliknete lijevom tipkom miša na bijelu pozadinu ćelija (ne na tekst!) u dobivenom stupcu:
Dodajmo sada još jedan stupac, gdje pomoću funkcije lijepimo sadržaje stupaca Adresa u svaku od mini-tablica, odvojene zarezima. Da biste to učinili, na kartici Dodajte stupac pritišćemo Prilagođeni stupac (Dodaj stupac — prilagođeni stupac) i u prozor koji se pojavi unesite naziv novog stupca i formulu spajanja na M jeziku ugrađenom u Power Query:
Imajte na umu da sve M-funkcije razlikuju velika i mala slova (za razliku od Excela). Nakon klika na OK dobivamo novi stupac sa zalijepljenim adresama:
Ostaje ukloniti već nepotrebni stupac Adresa tablice (desni klik na naslov) Izbriši stupac) i prenesite rezultate na list klikom na karticu Početna — Zatvori i preuzmi (Početna — Zatvori i učitaj):
Važna nijansa: Za razliku od prethodnih metoda (funkcija), tablice iz Power Queryja se ne ažuriraju automatski. Ako u budućnosti dođe do bilo kakvih promjena u izvornim podacima, morat ćete desnom tipkom miša kliknuti bilo gdje u tablici rezultata i odabrati naredbu Ažurirajte i spremite (Osvježiti).
- Kako podijeliti dugačak tekstualni niz na dijelove
- Nekoliko načina lijepljenja teksta iz različitih ćelija u jednu
- Korištenje operatora Like za testiranje teksta u odnosu na masku