Saturs
Es jau rakstīju par to, kā jūs varat ātri salīmēt tekstu no vairākām šūnām vienā un, gluži pretēji, parsēt garu teksta virkni komponentos. Tagad apskatīsim tuvu, bet nedaudz sarežģītāku uzdevumu – kā salīmēt tekstu no vairākām šūnām, ja ir izpildīts noteikts nosacījums.
Teiksim, mums ir klientu datu bāze, kurā viens uzņēmuma nosaukums var atbilst vairākiem dažādiem tā darbinieku e-pastiem. Mūsu uzdevums ir apkopot visas adreses pēc uzņēmumu nosaukumiem un savienot tās (atdalot ar komatiem vai semikoliem), lai izveidotu, piemēram, klientiem adresātu sarakstu, ti, iegūtu izvadi, piemēram:
Citiem vārdiem sakot, mums ir nepieciešams rīks, kas salīmēs (saitē) tekstu atbilstoši nosacījumam - funkcijas analogs SUMMESLI (SUMIF), bet tekstam.
0. metode. Formula
Nav ļoti elegants, bet vienkāršākais veids. Varat uzrakstīt vienkāršu formulu, kas pārbaudīs, vai nākamajā rindā esošais uzņēmums atšķiras no iepriekšējās. Ja neatšķiras, tad līmējiet nākamo adresi, atdalot to ar komatu. Ja tas atšķiras, mēs “atiestatām” uzkrāto, sākot no jauna:
Šīs pieejas trūkumi ir acīmredzami: no visām iegūtajām papildu kolonnas šūnām mums ir vajadzīgas tikai pēdējās katram uzņēmumam (dzeltens). Ja saraksts ir liels, tad, lai tos ātri atlasītu, jums būs jāpievieno vēl viena kolonna, izmantojot funkciju DLSTR (LEN), pārbaudot uzkrāto virkņu garumu:
Tagad jūs varat izfiltrēt tos un kopēt nepieciešamo adrešu līmēšanu turpmākai lietošanai.
1. metode. Līmēšanas ar vienu nosacījumu makrofunkcija
Ja sākotnējais saraksts nav sakārtots pēc uzņēmuma, iepriekš minētā vienkāršā formula nedarbojas, taču jūs varat viegli apiet, izmantojot nelielu pielāgotu funkciju VBA. Atveriet Visual Basic redaktoru, nospiežot īsinājumtaustiņu Alt+F11 vai izmantojot pogu Visual Basic tab attīstītājs (Izstrādātājs). Atvērtajā logā izvēlnē ievietojiet jaunu tukšu moduli Ievietot - modulis un nokopējiet tur mūsu funkcijas tekstu:
Function MergeIf(TextRange As Range, SearchRange As Range, Condition As String) Dim Delimeter As String, i As Long Delimeter = ", " līmes nav vienādas viena ar otru - mēs izejam ar kļūdu If SearchRange.Count <> TextRange.Count Tad MergeIf = CVERr(xlErrRef) Iziet no funkcijas Beigas Ja 'iet cauri visām šūnām, pārbaudiet nosacījumu un savāciet tekstu mainīgajā OutText For i = 1 To SearchRange. Cells.Count Ja SearchRange.Cells(i) Like nosacījums then OutText = OutText & TextRange.Cells(i) & Delimeter Nākamais i 'parāda rezultātus bez pēdējā norobežotāja MergeIf = Left(OutText, Len(OutText) - Len(Delimeter)) End funkciju
Ja tagad atgriezīsities programmā Microsoft Excel, tad funkciju sarakstā (poga fx formulas joslā vai cilnē Formulas - Ievietot funkciju) būs iespējams atrast mūsu funkciju Apvienot, ja kategorijā Lietotājs definēts (Lietotājs definēts). Funkcijas argumenti ir šādi:
2. metode. Savienojiet tekstu pēc neprecīza nosacījuma
Ja mēs aizstājam pirmo rakstzīmi mūsu makro 13. rindā = aptuvenajam saspēles vadītājam tāpat, tad būs iespējams veikt līmēšanu, neprecīzi saskaņojot sākotnējos datus ar atlases kritēriju. Piemēram, ja uzņēmuma nosaukumu var rakstīt dažādos variantos, tad tos visus varam pārbaudīt un apkopot ar vienu funkciju:
Tiek atbalstītas standarta aizstājējzīmes:
- zvaigznīte (*) – apzīmē jebkuru jebkuru rakstzīmju skaitu (ieskaitot to neesamību)
- jautājuma zīme (?) – apzīmē jebkuru atsevišķu rakstzīmi
- mārciņas zīme (#) — apzīmē jebkuru vienu ciparu (0-9)
Pēc noklusējuma operators Like ir reģistrjutīgs, ti, saprot, piemēram, “Orion” un “orion” kā dažādus uzņēmumus. Lai ignorētu reģistru, Visual Basic redaktorā varat pievienot rindiņu pašā moduļa sākumā Opcija Salīdzināt tekstu, kas pārslēgs Patīk, lai nebūtu reģistrjutīgs.
Tādā veidā jūs varat izveidot ļoti sarežģītas maskas apstākļu pārbaudei, piemēram:
- ?1##??777RUS – visu 777 reģiona numura zīmju atlase, sākot ar 1
- LLC* – visi uzņēmumi, kuru nosaukums sākas ar LLC
- ##7## – visas preces ar piecu ciparu ciparu kodu, kur trešais cipars ir 7
- ?????? – visi piecu burtu nosaukumi utt.
3. metode. Makro funkcija teksta līmēšanai divos apstākļos
Darbā var rasties problēma, ja nepieciešams saistīt tekstu vairāk nekā vienu nosacījumu. Piemēram, iedomāsimies, ka mūsu iepriekšējā tabulā tika pievienota vēl viena kolonna ar pilsētu, un līmēšana jāveic ne tikai konkrētam uzņēmumam, bet arī konkrētai pilsētai. Šajā gadījumā mūsu funkcija būs nedaudz jāmodernizē, pievienojot tai vēl vienu diapazona pārbaudi:
Funkcija MergeIfs(Teksta diapazons kā diapazons, meklēšanas diapazons1 kā diapazons, nosacījums1 kā virkne, meklēšanas diapazons2 kā diapazons, nosacījums2 kā virkne) Dim Delimeter As String, i As Long Delimeter = ", " 'atdalīšanas rakstzīmes (var aizstāt ar atstarpi vai ; utt.). e.) 'ja validācijas un līmēšanas diapazoni nav vienādi, izejiet ar kļūdu If SearchRange1.Count <> TextRange.Count Or SearchRange2.Count <> TextRange.Count then MergeIfs = CVERr(xlErrRef) Iziet no funkcijas Beigas If Izejiet cauri visām šūnām, pārbaudiet visus nosacījumus un savāciet tekstu mainīgajā OutText For i = 1 To SearchRange1.Cells.Count Ja SearchRange1.Cells(i) = Condition1 Un SearchRange2.Cells(i) = Condition2 Tad OutText = OutText & TextRange.Cells(i) & Delimeter End If Next i 'parāda rezultātus bez pēdējā norobežotāja MergeIfs = Left(OutText, Len(OutText) - Len(Delimeter)) beigu funkcija
Tas tiks piemērots tieši tādā pašā veidā – tikai argumenti tagad ir jāprecizē:
4. metode. Grupēšana un līmēšana programmā Power Query
Problēmu var atrisināt bez programmēšanas VBA, ja izmantojat bezmaksas Power Query pievienojumprogrammu. Programmai Excel 2010–2013 to var lejupielādēt šeit, un programmā Excel 2016 tas jau ir iebūvēts pēc noklusējuma. Darbību secība būs šāda:
Power Query nezina, kā strādāt ar parastajām tabulām, tāpēc pirmais solis ir pārvērst mūsu tabulu par “gudru”. Lai to izdarītu, atlasiet to un nospiediet kombināciju Ctrl+T vai izvēlieties no cilnes Sākums — formatējiet kā tabulu (Sākums — formatēt kā tabulu). Cilnē, kas pēc tam tiek parādīta Konstruktors (Dizains) jūs varat iestatīt tabulas nosaukumu (es atstāju standartu Tabula 1):
Tagad ielādēsim tabulu Power Query pievienojumprogrammā. Lai to izdarītu, cilnē Datums (ja jums ir Excel 2016) vai cilnē Power Query (ja jums ir Excel 2010–2013) noklikšķiniet uz No galda (Dati — no tabulas):
Atvērtajā vaicājumu redaktora logā atlasiet kolonnu, noklikšķinot uz galvenes Uzņēmums un nospiediet augšējo pogu grupa (grupēt pēc). Grupācijā ievadiet jaunās kolonnas nosaukumu un darbības veidu - Visas līnijas (Visas rindas):
Noklikšķiniet uz Labi, un mēs saņemam mini tabulu ar grupētajām vērtībām katram uzņēmumam. Tabulu saturs ir skaidri redzams, ja iegūtajā kolonnā ar peles kreiso taustiņu noklikšķiniet uz šūnu baltā fona (nevis uz teksta!):
Tagad pievienosim vēl vienu kolonnu, kur, izmantojot funkciju, katrā minitabulā salīmējam ailes Adrese saturu, atdalot tos ar komatiem. Lai to izdarītu, cilnē Pievienot kolonnu mēs nospiežam Pielāgota kolonna (Pievienot kolonnu — pielāgota kolonna) un parādītajā logā ievadiet jaunās kolonnas nosaukumu un savienojuma formulu Power Query iebūvētajā M valodā:
Ņemiet vērā, ka visas M funkcijas ir reģistrjutīgas (atšķirībā no Excel). Pēc noklikšķināšanas uz OK mēs iegūstam jaunu kolonnu ar salīmētām adresēm:
Atliek noņemt jau nevajadzīgo kolonnu Tabulas adreses (ar peles labo pogu noklikšķiniet uz virsraksta) Dzēst kolonnu) un augšupielādējiet rezultātus lapā, noklikšķinot uz cilnes Sākums — aizveriet un lejupielādējiet (Sākums — aizvērt un ielādēt):
Svarīga nianse: atšķirībā no iepriekšējām metodēm (funkcijām), Power Query tabulas netiek atjauninātas automātiski. Ja nākotnē būs kādas izmaiņas avota datos, jums būs jānoklikšķina ar peles labo pogu jebkur rezultātu tabulā un jāizvēlas komanda Atjaunināt un saglabāt (Atjaunot).
- Kā sadalīt garu teksta virkni daļās
- Vairāki veidi, kā salīmēt tekstu no dažādām šūnām vienā
- Operatora Like izmantošana, lai pārbaudītu tekstu pret masku