Bindung Text no Konditioun

Ech hu scho geschriwwen iwwer wéi Dir Text aus e puer Zellen séier an eng kleeft an am Géigendeel e laangen Textstring an Komponenten parséieren. Loosst eis elo eng enk, awer e bësse méi komplex Aufgab kucken - wéi Text aus e puer Zellen gekollt gëtt wann e bestëmmte spezifizéierte Konditioun erfëllt ass. 

Loosst eis soen datt mir eng Datebank vu Clienten hunn, wou ee Firmennumm mat verschiddenen E-Maile vu senge Mataarbechter entsprieche kann. Eis Aufgab ass et all Adressen no Firmennamen ze sammelen an se zesummenzekréien (getrennt duerch Kommaen oder Semikolonen) fir zum Beispill eng Mailinglëscht fir Clienten ze maachen, dh d'Ausgab esou eppes wéi:

An anere Wierder, mir brauchen e Tool dat den Text no der Bedingung kleet (link) - en Analog vun der Funktioun SUMMESLI (SUMIF), mee fir Text.

Method 0. Formel

Net ganz elegant, awer am einfachste Wee. Dir kënnt eng einfach Formel schreiwen, déi iwwerpréift ob d'Firma an der nächster Zeil vun der viregter ënnerscheet. Wann et net ënnerscheet, da gekollt déi nächst Adress mat engem Komma getrennt. Wann et ënnerscheet, da "zrécksetzen" mir de cumuléierten, erëm ufänken:

D'Nodeeler vun dëser Approche sinn offensichtlech: aus all Zellen vun der zousätzlech Kolonn kritt, brauche mir nëmmen déi lescht fir all Firma (giel). Wann d'Lëscht grouss ass, da fir se séier ze wielen, musst Dir eng aner Kolonn mat der Funktioun addéieren DLSTR (LENG), d'Längt vun de akkumuléierte Saiten iwwerpréift:

Elo kënnt Dir déi ausfilteren a kopéieren déi néideg Adressverlebung fir weider ze benotzen.

Method 1. Macrofunction vun gluing duerch eng Konditioun

Wann d'Original Lëscht net no Firma zortéiert ass, da funktionnéiert déi uewe genannte einfache Formel net, awer Dir kënnt ganz einfach mat enger klenger personaliséierter Funktioun an VBA ëmgoen. Öffnen de Visual Basic Editor andeems Dir eng Tastatur Ofkiirzung dréckt Alt + F11 oder benotzt de Knäppchen visuell Grondleegend Reiter Entwéckler (Entwéckler). An der Fënster déi opmaacht, gitt en neien eidele Modul duerch de Menü Insert - Modul a kopéiert den Text vun eiser Funktioun do:

Funktioun MergeIf(TextRange As Range, SearchRange As Range, Condition As String) Dim Delimeter As String, i As Long Delimeter = ", " gluings sinn net gläich mateneen - mir ginn mat engem Feeler eraus Wann SearchRange.Count <> TextRange.Count Dann MergeIf = CVErr(xlErrRef) Exit Funktioun End Wann 'duerch all d'Zellen goen, kontrolléiert d'Konditioun a sammelt den Text an der Variabel OutText Fir i = 1 To SearchRange. Cells.Count If SearchRange.Cells(i) Like Condition Then OutText = OutText & TextRange.Cells(i) & Delimeter Next i 'display results without last delimiter MergeIf = Left(OutText, Len(OutText) - Len(Delimeter)) End Funktioun  

Wann Dir elo zréck op Microsoft Excel kënnt, dann an der Lëscht vun de Funktiounen (Knäppchen fx an der Formelbar oder Tab Formelen - Insert Funktioun) wäert et méiglech sinn eis Funktioun ze fannen MergeIf an der Kategorie Benotzerdefinéiert (Benotzerdefinéiert). D'Argumenter fir d'Funktioun sinn wéi follegt:

Method 2. Zesummenhang Text vun onexakt Zoustand

Wa mir den éischte Charakter an der 13. Zeil vun eisem Macro ersetzen = zu der ongeféier Match Bedreiwer wëll, da wäert et méiglech sinn d'Kleimung duerch en onexakt Match vun den initialen Donnéeën mam Selektiounskriterium auszeféieren. Zum Beispill, wann de Firmennumm a verschiddene Varianten geschriwwe ka ginn, da kënne mir se all mat enger Funktioun kontrolléieren a sammelen:

Standard Wildcards ginn ënnerstëtzt:

• Asterisk (*) - bezeechent all Zuel vun all Zeechen (inklusiv hir Fehlen)
• Fro Zeechen (?) - steet fir all eenzel Charakter
• Pound Zeechen (#) - steet fir all Ziffer (0-9)

Par défaut ass de Like Bedreiwer Fall-sensibel, also versteet zum Beispill "Orion" an "orion" als verschidde Firmen. Fir de Fall ze ignoréieren, kënnt Dir d'Linn am Ufank vum Modul am Visual Basic Editor addéieren Optioun Vergläichen Text, déi wiesselt Wéi fir Fall-onsensibel ze sinn.

Op dës Manéier kënnt Dir ganz komplex Masken komponéieren fir Konditiounen ze kontrolléieren, zum Beispill:

• ?1##??777RUS - Auswiel vun allen Nummerplacke vun der 777 Regioun, ugefaange mat 1
• LLC * - all Firmen deenen hiren Numm mat LLC ufänkt
• ##7## - all Produkter mat engem fënnef-Zifferen digitale Code, wou déi drëtt Ziffer 7 ass
• ???? - all Nimm vu fënnef Buschtawen, etc.
  

Method 3. Macro Funktioun fir Klebstoff Text ënner zwee Konditiounen

An der Aarbecht kann et e Problem ginn, wann Dir den Text méi wéi eng Bedingung verbënnt. Zum Beispill, loosst eis virstellen datt an eiser viregter Tabell eng méi Kolonn mat der Stad bäigefüügt gouf, an d'Lämmung sollt net nëmme fir eng bestëmmte Firma gemaach ginn, awer och fir eng bestëmmte Stad. An dësem Fall muss eis Funktioun liicht moderniséiert ginn andeems en en anere Rangecheck derbäi bäidréit:

Funktioun MergeIfs(TextRange As Range, SearchRange1 As Range, Condition1 As String, SearchRange2 As Range, Condition2 As String) Dim Delimeter As String, i As Long Delimeter = ", " 'Delimiter Charaktere (kann duerch Raum oder ; etc. ersat ginn) e.) 'wann d'Validatiouns- a Kleeberäicher net matenee gläich sinn, gitt mat engem Feeler eraus Wann SearchRange1.Count <> TextRange.Count Oder SearchRange2.Count <> TextRange.Count Then MergeIfs = CVErr(xlErrRef) Exit Function End If 'Gitt duerch all Zellen, kontrolléiert all Bedéngungen a sammelt den Text an d'Variabel OutText For i = 1 To SearchRange1.Cells.Count Wann SearchRange1.Cells(i) = Condition1 And SearchRange2.Cells(i) = Condition2 Dann OutText = OutText & TextRange.Cells(i) & Delimeter End If Next i 'display results without last delimiter MergeIfs = Left(OutText, Len(OutText) - Len(Delimeter)) End Funktioun  

Et gëtt genee déiselwecht applizéiert - nëmmen Argumenter mussen elo méi spezifizéiert ginn:

Method 4. Gruppéieren an Kleeblatt an Power Query

Dir kënnt de Problem ouni Programméiere an VBA léisen, wann Dir de gratis Power Query Add-in benotzt. Fir Excel 2010-2013 kann et hei erofgeluede ginn, an an Excel 2016 ass et scho Standard gebaut. D'Sequenz vun Aktiounen wäert wéi follegt sinn:

Power Query weess net wéi een mat regelméissegen Dëscher schafft, also ass den éischte Schrëtt eisen Dësch an e "Smart" ze maachen. Fir dëst ze maachen, wielt et a dréckt op d'Kombinatioun Ctrl+T oder wielt aus dem Tab Home - Format als Dësch (Home - Format als Table). Op der Tab déi dann erschéngt Builder (Design) Dir kënnt den Dësch Numm setzen (ech hunn de Standard verlooss Table 1):

Loosst eis elo eisen Dësch an de Power Query Add-in lueden. Fir dëst ze maachen, op der Tab Daten (wann Dir Excel 2016 hutt) oder op der Power Query Tab (wann Dir Excel 2010-2013 hutt) klickt op Vum Dësch (Daten - Vun Dësch):

An der Query Editor Fënster déi opmaacht, wielt d'Kolonn andeems Dir op den Header klickt Company an Press de Knäppchen uewen Group (Grupp vun). Gitt den Numm vun der neier Kolonn an d'Aart vun der Operatioun an der Gruppéierung - All Linnen (All Reihen):

Klickt OK a mir kréien e Mini-Tabell vu gruppéierte Wäerter fir all Firma. Den Inhalt vun den Dëscher ass kloer ze gesinn wann Dir op de wäissen Hannergrond vun den Zellen lénks klickt (net op den Text!) An der resultéierender Kolonn:

Loosst eis elo nach eng Kolonn addéieren, wou mir mat der Funktioun d'Inhalter vun den Adresssäulen an all Mini-Tabellen pechen, getrennt vu Kommaen. Fir dëst ze maachen, op der Tab Füügt Kolonn mir drécken Benotzerdefinéiert Kolonn (Füügt Kolonn - Benotzerdefinéiert Kolonn) an an der Fënster déi erschéngt, gitt den Numm vun der neier Kolonn an der Kopplungsformel an der M Sprooch agebaut an Power Query:

Bedenkt datt all M-Funktioune Fall sensibel sinn (am Géigesaz zu Excel). Nodeems Dir op klickt OK mir kréien eng nei Kolonn mat gekollt Adressen:

Et bleift déi schonn onnéideg Kolonn ze läschen Dësch Adressen (riets klickt op den Titel) Kolonn läschen) an lued d'Resultater op de Blat erop andeems Dir op de Tab klickt Home - Zoumaachen an eroflueden (Doheem - Zoumaachen a lueden):

Wichteg Nuance: Am Géigesaz zu de fréiere Methoden (Funktiounen), Tabelle vu Power Query ginn net automatesch aktualiséiert. Wann et an der Zukunft Ännerungen an de Quelldaten wäerte sinn, da musst Dir iwwerall an der Resultater Tabelle mat der rietser Maustast klickt a wielt de Kommando Update & späicheren (Erfrëschen).

• Wéi eng laang Textstring an Deeler opzedeelen
• Verschidde Weeër fir Text aus verschiddenen Zellen an een ze pechen
• Benotzt de Like Bedreiwer fir Text géint eng Mask ze testen