Përmbajtje
Unë kam shkruar tashmë se si mund të ngjitni shpejt tekstin nga disa qeliza në një dhe, anasjelltas, të analizoni një varg të gjatë teksti në përbërës. Tani le të shohim një detyrë të ngushtë, por pak më komplekse - si të ngjitni tekstin nga disa qeliza kur plotësohet një kusht i caktuar i specifikuar.
Le të themi se kemi një bazë të dhënash të klientëve, ku një emër kompanie mund të korrespondojë me disa emaile të ndryshme të punonjësve të saj. Detyra jonë është të mbledhim të gjitha adresat sipas emrave të kompanive dhe t'i bashkojmë ato (të ndara me presje ose pikëpresje) në mënyrë që të krijojmë, për shembull, një listë postimesh për klientët, dmth. të marrim një rezultat si:
Me fjalë të tjera, na duhet një mjet që do të ngjisë (lidhë) tekstin sipas kushtit - një analog i funksionit SUMMESLI (SUMIF), por për tekstin.
Metoda 0. Formula
Jo shumë elegante, por mënyra më e lehtë. Ju mund të shkruani një formulë të thjeshtë që do të kontrollojë nëse kompania në rreshtin tjetër ndryshon nga ajo e mëparshme. Nëse nuk ndryshon, atëherë ngjitni adresën tjetër të ndarë me presje. Nëse ndryshon, atëherë ne "rivendosim" të grumbulluarit, duke filluar përsëri:
Disavantazhet e kësaj qasjeje janë të dukshme: nga të gjitha qelizat e kolonës shtesë të marrë, na duhen vetëm ato të fundit për secilën kompani (të verdha). Nëse lista është e madhe, atëherë për t'i zgjedhur shpejt ato, do të duhet të shtoni një kolonë tjetër duke përdorur funksionin DLSTR (LEN), duke kontrolluar gjatësinë e vargjeve të grumbulluara:
Tani mund t'i filtroni ato dhe të kopjoni ngjitjen e adresave të nevojshme për përdorim të mëtejshëm.
Metoda 1. Makrofunksioni i ngjitjes me një kusht
Nëse lista origjinale nuk është renditur sipas kompanisë, atëherë formula e mësipërme e thjeshtë nuk funksionon, por lehtë mund të lëvizni me një funksion të vogël personal në VBA. Hapni redaktorin e Visual Basic duke shtypur një shkurtore të tastierës Alt + F11 ose duke përdorur butonin Visual Basic tab zhvillues (Zhvilluesi). Në dritaren që hapet, futni një modul të ri bosh përmes menysë Insert – Moduli dhe kopjoni tekstin e funksionit tonë atje:
Funksioni MergeIf(TextRange As Range, Search Range As Range, Condition as String) Dim Delimeter as String, i As Long Delimeter = ", " ngjitjet nuk janë të barabarta me njëra-tjetrën - dalim me një gabim nëse Search Range.Count <> TextRange.Count Pastaj MergeIf = CVERr(xlErrRef) Funksioni Dil Fundi Nëse 'kaloni nëpër të gjitha qelizat, kontrolloni gjendjen dhe mblidhni tekstin në variablin OutText For i = 1 në Search Range. Cells.Count If Search Range.Cells(i) Like Condition then OutText = OutText & TextRange.Cells(i) & Delimeter Tjetra i 'shfaq rezultatet pa delimituesin e fundit MergeIf = Left(OutText, Len(OutText) - Len(Delimeter)) Fund funksionin
Nëse tani ktheheni në Microsoft Excel, atëherë në listën e funksioneve (butoni fx në shiritin ose skedën e formulave Formulat – Funksioni i Futjes) do të jetë e mundur të gjejmë funksionin tonë MergeNëse në kategori Perdorues i percaktuar (Perdorues i percaktuar). Argumentet e funksionit janë si më poshtë:
Metoda 2. Lidhni tekstin sipas gjendjes së pasaktë
Nëse zëvendësojmë karakterin e parë në rreshtin e 13-të të makros tonë = te operatori i përafërt i ndeshjes Like, atëherë do të jetë e mundur të kryhet ngjitja me një përputhje të pasaktë të të dhënave fillestare me kriterin e përzgjedhjes. Për shembull, nëse emri i kompanisë mund të shkruhet në variante të ndryshme, atëherë ne mund t'i kontrollojmë dhe t'i mbledhim të gjitha me një funksion:
Shenjat standarde të karakterit mbështeten:
- ylli (*) - tregon çdo numër të çdo karakteri (përfshirë mungesën e tyre)
- pikëpyetja (?) - qëndron për çdo karakter të vetëm
- Shenja paund (#) - qëndron për çdo shifër të vetme (0-9)
Si parazgjedhje, operatori Like është i ndjeshëm ndaj shkronjave të vogla, dmth kupton, për shembull, "Orion" dhe "orion" si kompani të ndryshme. Për të injoruar rastet, mund të shtoni rreshtin në fillim të modulit në redaktorin e Visual Basic Opsioni Krahasoni Tekstin, i cili do të ndërrojë Like për të qenë i pandjeshëm.
Në këtë mënyrë, ju mund të kompozoni maska shumë komplekse për kontrollin e kushteve, për shembull:
- ?1##??777RUS – përzgjedhja e të gjitha targave të rajonit 777, duke filluar me 1
- LLC* – të gjitha kompanitë emri i të cilave fillon me SH.PK
- ##7## – të gjitha produktet me një kod dixhital pesëshifror, ku shifra e tretë është 7
- ????? – të gjithë emrat e pesë shkronjave, etj.
Metoda 3. Funksioni makro për ngjitjen e tekstit në dy kushte
Në punë mund të ketë një problem kur duhet të lidhni tekstin më shumë se një kusht. Për shembull, le të imagjinojmë që në tabelën tonë të mëparshme, është shtuar një kolonë më shumë me qytetin, dhe ngjitja duhet të kryhet jo vetëm për një kompani të caktuar, por edhe për një qytet të caktuar. Në këtë rast, funksioni ynë do të duhet të modernizohet pak duke shtuar një kontroll tjetër të diapazonit:
Funksioni MergeIfs (Text Range si Range, Search Range1 si Range, Condition1 as String, Search Range2 as Range, Condition2 as String) Dim Delimeter as String, i As Long Delimeter = ", " 'karakteret kufizues (mund të zëvendësohen me hapësirë ose ; etj.) e.) 'nëse diapazoni i vlefshmërisë dhe ngjitjes nuk janë të barabarta me njëri-tjetrin, dilni me një gabim nëse SearchRange1.Count <> TextRange.Count Ose SearchRange2.Count <> TextRange.Count Pastaj MergeIfs = CVERr(xlErrRef) Dilni nga funksioni Fundi nëse 'kaloni nëpër të gjitha qelizat, kontrolloni të gjitha kushtet dhe mblidhni tekstin në variablin OutText For i = 1 Tek SearchRange1.Cells.Count If Search Range1.Cells(i) = Condition1 And Search Range2.Cells(i) = Condition2 Pastaj OutText = OutText & TextRange.Cells(i) & Delimeter Fund If Next i 'shfaq rezultatet pa delimituesin e fundit MergeIfs = Left(OutText, Len(OutText) - Len(Delimeter)) Funksioni Fund
Do të zbatohet saktësisht në të njëjtën mënyrë - vetëm argumentet tani duhet të specifikohen më shumë:
Metoda 4. Grupimi dhe ngjitja në Power Query
Ju mund ta zgjidhni problemin pa programuar në VBA, nëse përdorni shtesën falas të Power Query. Për Excel 2010-2013 mund të shkarkohet këtu, dhe në Excel 2016 është tashmë i integruar si parazgjedhje. Sekuenca e veprimeve do të jetë si më poshtë:
Power Query nuk di të punojë me tabela të rregullta, kështu që hapi i parë është ta kthejmë tabelën tonë në një "të zgjuar". Për ta bërë këtë, zgjidhni atë dhe shtypni kombinimin Ctrl+T ose zgjidhni nga skeda Faqja kryesore – Formatoni si tabelë (Shtëpia - Formatoni si tabelë). Në skedën që shfaqet më pas konstruktor (Dizajn) mund të vendosni emrin e tabelës (kam lënë standardin Tabela 1):
Tani le të ngarkojmë tabelën tonë në shtesën Power Query. Për ta bërë këtë, në skedën Data (nëse keni Excel 2016) ose në skedën Power Query (nëse keni Excel 2010-2013) klikoni Nga tavolina (Të dhënat - Nga tabela):
Në dritaren e redaktuesit të pyetjeve që hapet, zgjidhni kolonën duke klikuar në kokë kompani dhe shtypni butonin e mësipërm Grup (Grup sipas). Shkruani emrin e kolonës së re dhe llojin e veprimit në grupim - Të gjitha linjat (Të gjitha rreshtat):
Klikoni OK dhe marrim një mini-tabela me vlera të grupuara për secilën kompani. Përmbajtja e tabelave është qartë e dukshme nëse klikoni me të majtën në sfondin e bardhë të qelizave (jo në tekst!) në kolonën që rezulton:
Tani le të shtojmë një kolonë tjetër, ku, duke përdorur funksionin, ngjisim përmbajtjen e kolonave të Adresës në secilën nga mini-tabelat, të ndara me presje. Për ta bërë këtë, në skedën Shtoni kolonën shtypim Kolona e personalizuar (Shto kolonë - kolonë e personalizuar) dhe në dritaren që shfaqet, futni emrin e kolonës së re dhe formulën e bashkimit në gjuhën M të integruar në Power Query:
Vini re se të gjitha funksionet M janë të ndjeshme ndaj shkronjave të vogla (ndryshe nga Excel). Pasi të klikoni mbi OK marrim një kolonë të re me adresa të ngjitura:
Mbetet për të hequr kolonën tashmë të panevojshme Adresat e Tabelave (klikoni me të djathtën mbi titull) Fshi kolonën) dhe ngarkoni rezultatet në fletë duke klikuar në skedën Faqja kryesore — Mbylle dhe shkarko (Shtëpia - Mbyll dhe ngarko):
Nuancë e rëndësishme: Ndryshe nga metodat (funksionet) e mëparshme, tabelat nga Power Query nuk përditësohen automatikisht. Nëse në të ardhmen do të ketë ndonjë ndryshim në të dhënat e burimit, atëherë do t'ju duhet të klikoni me të djathtën kudo në tabelën e rezultateve dhe të zgjidhni komandën Përditëso & Ruaj (Rifresko).
- Si të ndani një varg të gjatë teksti në pjesë
- Disa mënyra për të ngjitur tekstin nga qeliza të ndryshme në një
- Përdorimi i operatorit Like për të testuar tekstin kundrejt një maske