Werrej
Diġà ktibt dwar kif tista 'malajr twaħħal it-test minn diversi ċelloli f'waħda u, għall-kuntrarju, teżamina sekwenza twila ta' test f'komponenti. Issa ejja nħarsu lejn kompitu mill-qrib, iżda kemmxejn aktar kumpless - kif teħel test minn diversi ċelloli meta tiġi sodisfatta ċerta kundizzjoni speċifikata.
Ejja ngħidu li għandna database ta' klijenti, fejn isem wieħed ta' kumpanija jista' jikkorrispondi għal diversi emails differenti tal-impjegati tagħha. Il-kompitu tagħna huwa li niġbru l-indirizzi kollha bl-ismijiet tal-kumpaniji u nikkonkatenawhom (separati b'virgoli jew punt u virgola) sabiex nagħmlu, pereżempju, lista tal-posta għall-klijenti, jiġifieri nġibu xi ħaġa bħal:
Fi kliem ieħor, neħtieġu għodda li twaħħal (link) it-test skont il-kundizzjoni - analogu tal-funzjoni SUMMESLI (SUMIF), iżda għat-test.
Metodu 0. Formula
Mhux eleganti ħafna, iżda l-eħfef mod. Tista 'tikteb formula sempliċi li se tivverifika jekk il-kumpanija fir-ringiela li jmiss hijiex differenti minn dik ta' qabel. Jekk ma tvarjax, imbagħad waħħal l-indirizz li jmiss separat b'virgola. Jekk tkun differenti, allura aħna "reset" l-akkumulat, nibdew mill-ġdid:
L-iżvantaġġi ta 'dan l-approċċ huma ovvji: miċ-ċelloli kollha tal-kolonna addizzjonali miksuba, għandna bżonn biss l-aħħar għal kull kumpanija (isfar). Jekk il-lista hija kbira, allura sabiex tagħżelhom malajr, ikollok iżżid kolonna oħra billi tuża l-funzjoni DLSTR (LEN), iċċekkja t-tul tal-kordi akkumulati:
Issa tista 'tiffiltra dawk u tikkopja l-inkullar tal-indirizz meħtieġ għal aktar użu.
Metodu 1. Makrofunzjoni tal-inkullar b'kundizzjoni waħda
Jekk il-lista oriġinali mhix magħżula skont il-kumpanija, allura l-formula sempliċi ta 'hawn fuq ma taħdimx, iżda tista' faċilment tiċċaqlaq b'funzjoni żgħira tad-dwana f'VBA. Iftaħ l-Editur Visual Basic billi tagħfas shortcut tat-tastiera Alt + F11 jew billi tuża l-buttuna Visual Basic tab iżviluppatur (Żviluppatur). Fit-tieqa li tiftaħ, daħħal modulu vojt ġdid permezz tal-menu Daħħal – Modulu u kkopja t-test tal-funzjoni tagħna hemmhekk:
Funzjoni MergeIf(TextRange As Range, SearchRange As Range, Condition As String) Dim Delimeter As String, i As Long Delimeter = ", " inkullar mhumiex ugwali għal xulxin - noħorġu bi żball Jekk SearchRange.Count <> TextRange.Count Imbagħad MergeIf = CVERrr(xlErrRef) Tmiem tal-Funzjoni tal-Ħruġ Jekk 'għaddi miċ-ċelloli kollha, iċċekkja l-kundizzjoni u iġbor it-test fil-varjabbli OutText For i = 1 To SearchRange. Cells.Count Jekk SearchRange.Cells(i) Like Condition Imbagħad OutText = OutText & TextRange.Cells(i) & Delimeter Li jmiss i 'wiri riżultati mingħajr l-aħħar delimitatur MergeIf = Left(OutText, Len(OutText) - Len(Delimeter)) Tmiem funzjoni
Jekk issa terġa 'lura għal Microsoft Excel, imbagħad fil-lista ta' funzjonijiet (buttuna fx fil-formula bar jew tab Formuli - Funzjoni Daħħal) ikun possibbli li nsibu l-funzjoni tagħna MergeJekk fil-kategorija Iddefinit mill-Utent (Iddefinit mill-Utent). L-argumenti għall-funzjoni huma kif ġej:
Metodu 2. Ikkonkatena test b'kundizzjoni mhux eżatta
Jekk nissostitwixxu l-ewwel karattru fit-13-il linja tal-makro tagħna = lill-operatur approssimattiv tat-tqabbil bħall, allura jkun possibbli li titwettaq inkullar b'taqbila mhux eżatta tad-dejta inizjali mal-kriterju tal-għażla. Pereżempju, jekk l-isem tal-kumpanija jista 'jinkiteb f'varjanti differenti, allura nistgħu niċċekkjaw u niġbruhom kollha b'funzjoni waħda:
Il-wildcards standard huma appoġġjati:
- asterisk (*) - jindika kwalunkwe numru ta' xi karattri (inkluż l-assenza tagħhom)
- marka tal-mistoqsija (?) – tirrappreżenta kwalunkwe karattru wieħed
- sinjal tal-lira (#) - tirrappreżenta kwalunkwe ċifra waħda (0-9)
B'mod awtomatiku, l-operatur Like huwa sensittiv għall-każi, jiġifieri jifhem, pereżempju, "Orion" u "orion" bħala kumpaniji differenti. Biex tinjora l-każ, tista 'żżid il-linja fil-bidu nett tal-modulu fl-editur Visual Basic Għażla Qabbel it-Test, li se jaqilbu Like to be case insensitive.
B'dan il-mod, tista 'tikkomponi maskri kumplessi ħafna għall-iċċekkjar tal-kundizzjonijiet, pereżempju:
- ?1##??777RUS – għażla tal-pjanċi tal-liċenzja kollha tar-reġjun 777, li tibda b'1
- LLC* – il-kumpaniji kollha li isimhom jibda b'LLC
- ##7## - il-prodotti kollha b'kodiċi diġitali b'ħames ċifri, fejn it-tielet ċifra hija 7
- ????? – l-ismijiet kollha ta’ ħames ittri, eċċ.
Metodu 3. Funzjoni makro għall-inkullar tat-test taħt żewġ kundizzjonijiet
Fix-xogħol jista 'jkun hemm problema meta jkollok bżonn torbot it-test aktar minn kundizzjoni waħda. Pereżempju, ejja nimmaġinaw li fit-tabella preċedenti tagħna, ġiet miżjuda kolonna oħra mal-belt, u l-inkullar għandu jsir mhux biss għal kumpanija partikolari, iżda wkoll għal belt partikolari. F'dan il-każ, il-funzjoni tagħna se jkollha tiġi mmodernizzata kemmxejn billi żżid kontroll ta' firxa oħra magħha:
Funzjoni MergeIfs(TextRange As Range, SearchRange1 As Range, Condition1 As String, SearchRange2 As Range, Condition2 As String) Dim Delimeter As String, i As Long Delimeter = ", " 'karattri delimitaturi (jistgħu jiġu sostitwiti bi spazju jew ; eċċ.) e.) 'jekk il-firxiet ta' validazzjoni u inkullar mhumiex ugwali għal xulxin, oħroġ bi żball Jekk SearchRange1.Count <> TextRange.Count Or SearchRange2.Count <> TextRange.Count Imbagħad MergeIfs = CVERrr(xlErrRef) Ħruġ Funzjoni Tmiem Jekk 'għaddi miċ-ċelloli kollha, iċċekkja l-kundizzjonijiet kollha u iġbor it-test fil-varjabbli OutText Għal i = 1 Biex SearchRange1.Cells.Count Jekk SearchRange1.Cells (i) = Condition1 U SearchRange2.Cells (i) = Condition2 Imbagħad OutText = OutText & TextRange.Cells(i) & Delimeter End If Next i 'wiri riżultati mingħajr l-aħħar delimitatur MergeIfs = Left(OutText, Len(OutText) - Len(Delimeter)) End Function
Se jiġi applikat eżattament bl-istess mod – l-argumenti biss iridu jiġu speċifikati aktar:
Metodu 4. Raggruppament u inkullar f'Power Query
Tista' ssolvi l-problema mingħajr ma tipprogramma f'VBA, jekk tuża l-add-in b'xejn Power Query. Għal Excel 2010-2013 jista 'jitniżżel hawn, u f'Excel 2016 huwa diġà mibni b'mod awtomatiku. Is-sekwenza tal-azzjonijiet se tkun kif ġej:
Power Query ma jafx kif taħdem ma 'tabelli regolari, għalhekk l-ewwel pass huwa li nbiddlu t-tabella tagħna f'waħda "intelliġenti". Biex tagħmel dan, agħżelha u agħfas il-kombinazzjoni Ctrl+T jew agħżel mit-tab Home – Format bħala tabella (Id-dar — Format bħala Tabella). Fuq it-tab li mbagħad tidher kostruttur (Disinn) tista 'tissettja l-isem tal-mejda (ħallejt l-istandard Tabella 1):
Issa ejja tagħbija t-tabella tagħna fil-Power Query add-in. Biex tagħmel dan, fuq it-tab Data (jekk għandek Excel 2016) jew fuq it-tab Power Query (jekk għandek Excel 2010-2013) ikklikkja Mill-mejda (Data — Mit-Tabella):
Fit-tieqa tal-editur tal-mistoqsijiet li tiftaħ, agħżel il-kolonna billi tikklikkja fuq l-intestatura kumpanija u agħfas il-buttuna hawn fuq Grupp (Grupp minn). Daħħal l-isem tal-kolonna l-ġdida u t-tip ta’ operazzjoni fil-grupp – Il-linji kollha (Ringieli kollha):
Ikklikkja OK u jkollna mini-tabella ta 'valuri raggruppati għal kull kumpanija. Il-kontenut tat-tabelli huwa viżibbli b'mod ċar jekk tikklikkja bil-buttuna tax-xellug fuq l-isfond abjad taċ-ċelloli (mhux fuq it-test!) Fil-kolonna li tirriżulta:
Issa ejja nżidu kolonna oħra, fejn, bl-użu tal-funzjoni, inkollna l-kontenut tal-kolonni tal-Indirizz f'kull waħda mit-tabelli żgħar, separati b'virgoli. Biex tagħmel dan, fuq it-tab Żid kolonna aħna nagħfas Kolonna tad-dwana (Żid kolonna — Kolonna personalizzata) u fit-tieqa li tidher, daħħal l-isem tal-kolonna l-ġdida u l-formula tal-akkoppjar fil-lingwa M mibnija fil-Power Query:
Innota li l-funzjonijiet M kollha huma sensittivi għall-każ (b'differenza minn Excel). Wara li tikklikkja fuq OK nikbru kolonna ġdida b'indirizzi inkollati:
Fadal li titneħħa l-kolonna diġà bla bżonn TableAddresses (ikklikkja bil-lemin fuq it-titlu) Ħassar kolonna) u ittella’ r-riżultati fuq il-folja billi tikklikkja fuq it-tab Home — Agħlaq u niżżel (Id-dar — Agħlaq u tagħbija):
Sfumatura importanti: B'differenza mill-metodi (funzjonijiet) preċedenti, it-tabelli minn Power Query mhumiex aġġornati awtomatikament. Jekk fil-futur se jkun hemm xi tibdil fid-dejta tas-sors, imbagħad ikollok bżonn tikklikkja bil-lemin kullimkien fit-tabella tar-riżultati u tagħżel il-kmand Aġġorna u Issejvja (Aġġorna).
- Kif taqsam sekwenza ta' test twil f'partijiet
- Diversi modi biex twaħħal test minn ċelloli differenti f'wieħed
- Uża l-operatur Like biex tittestja test kontra maskra