contingut
Ja vaig escriure sobre com podeu enganxar ràpidament text de diverses cel·les en una i, per contra, analitzar una cadena de text llarga en components. Vegem ara una tasca propera, però una mica més complexa: com enganxar text de diverses cel·les quan es compleix una determinada condició especificada.
Suposem que tenim una base de dades de clients, on el nom d'una empresa pot correspondre a diversos correus electrònics diferents dels seus empleats. La nostra tasca és recollir totes les adreces per noms d'empresa i concatenar-les (separades per comes o punt i coma) per tal de fer, per exemple, una llista de correu per als clients, és a dir, obtenir una sortida com:
En altres paraules, necessitem una eina que enganxi (enllaça) el text segons la condició: un anàleg de la funció SUMMESLI (SUMIF), però per text.
Mètode 0. Fórmula
No molt elegant, però de la manera més fàcil. Podeu escriure una fórmula senzilla que comprovarà si l'empresa de la fila següent difereix de l'anterior. Si no és diferent, enganxeu la següent adreça separada per una coma. Si és diferent, "reiniciem" l'acumulat, començant de nou:
Els inconvenients d'aquest enfocament són evidents: de totes les cel·les de la columna addicional obtingudes, només necessitem les últimes per a cada empresa (grogues). Si la llista és gran, per seleccionar-les ràpidament, haureu d'afegir una altra columna mitjançant la funció DLSTR (LEN), comprovant la longitud de les cadenes acumulades:
Ara podeu filtrar-los i copiar l'enganxament d'adreces necessaris per a un ús posterior.
Mètode 1. Macrofunció d'encolat per una condició
Si la llista original no està ordenada per empresa, aleshores la fórmula senzilla anterior no funciona, però podeu desplaçar-vos fàcilment amb una petita funció personalitzada a VBA. Obriu l'Editor de Visual Basic prement una drecera de teclat Alt + F11 o utilitzant el botó Visual Basic llengüeta revelador (Desenvolupador). A la finestra que s'obre, inseriu un nou mòdul buit a través del menú Inserir – Mòdul i copieu el text de la nostra funció allà:
Funció MergeIf(TextRange As Range, SearchRange As Range, Condition As String) Dim Delimeter As String, i As Long Delimeter = ", " els encolats no són iguals entre si; sortim amb un error Si SearchRange.Count <> TextRange.Count Aleshores MergeIf = CVErr(xlErrRef) Exit Function End Si 'passa per totes les cel·les, comproveu la condició i reculli el text a la variable OutText For i = 1 To SearchRange. Cells.Count If SearchRange.Cells(i) Like Condition Aleshores OutText = OutText & TextRange.Cells(i) & Delimeter A continuació, mostro els resultats sense l'últim delimitador MergeIf = Left(OutText, Len(OutText) - Len(Delimeter)) End funció
Si ara torneu a Microsoft Excel, a la llista de funcions (botó fx a la barra o pestanya de fórmules Fórmules – Funció d'inserció) serà possible trobar la nostra funció MergeIf en categoria Usuari definit (Usuari definit). Els arguments de la funció són els següents:
Mètode 2. Concatenar text per condició inexacta
Si substituïm el primer caràcter de la línia 13 de la nostra macro = a l'operador de concordança aproximada like, llavors es podrà dur a terme encolat mitjançant una concordança inexacta de les dades inicials amb el criteri de selecció. Per exemple, si el nom de l'empresa es pot escriure en diferents variants, podem comprovar-ho i recopilar-los tots amb una funció:
S'admeten els comodins estàndard:
- asterisc (*): indica qualsevol nombre de caràcters (inclosa la seva absència)
- signe d'interrogació (?): representa qualsevol caràcter
- signe de lliura (#): representa un dígit qualsevol (0-9)
Per defecte, l'operador Like distingeix entre majúscules i minúscules, és a dir, entén, per exemple, "Orion" i "orion" com a empreses diferents. Per ignorar majúscules i minúscules, podeu afegir la línia al principi del mòdul a l'editor de Visual Basic Opció Comparar text, que canviarà Like perquè no distingeixi entre majúscules i minúscules.
D'aquesta manera, podeu compondre màscares molt complexes per comprovar les condicions, per exemple:
- ?1##??777RUS: selecció de totes les matrícules de la regió 777, començant per 1
- LLC*: totes les empreses el nom de les quals comença per LLC
- ##7##: tots els productes amb un codi digital de cinc dígits, on el tercer dígit és 7
- ????? – tots els noms de cinc lletres, etc.
Mètode 3. Funció macro per enganxar text sota dues condicions
En el treball pot haver-hi un problema quan necessiteu enllaçar el text més d'una condició. Per exemple, imaginem que a la nostra taula anterior s'ha afegit una columna més amb la ciutat i s'hauria de fer encolat no només per a una empresa determinada, sinó també per a una ciutat determinada. En aquest cas, la nostra funció s'haurà de modernitzar lleugerament afegint-hi una altra comprovació d'interval:
Funció MergeIfs(TextRange As Range, SearchRange1 As Range, Condition1 As String, SearchRange2 As Range, Condition2 As String) Dim Delimeter As String, i As Long Delimeter = ", " 'Caràcters delimitadors (es poden substituir per espai o ; etc.) e.) 'si els intervals de validació i encolat no són iguals entre si, sortiu amb un error Si SearchRange1.Count <> TextRange.Count O SearchRange2.Count <> TextRange.Count Then MergeIfs = CVErr(xlErrRef) Exit Function End If 'passeu per totes les cel·les, comproveu totes les condicions i reculli el text a la variable OutText For i = 1 To SearchRange1.Cells.Count If SearchRange1.Cells(i) = Condition1 I SearchRange2.Cells (i) = Condition2 Then OutText = OutText & TextRange.Cells(i) & Delimeter End If Next, mostro els resultats sense l'últim delimitador MergeIfs = Left(OutText, Len(OutText) - Len(Delimeter)) Funció final
S'aplicarà exactament de la mateixa manera: ara només cal especificar més els arguments:
Mètode 4. Agrupació i encolat en Power Query
Podeu resoldre el problema sense programar a VBA, si feu servir el complement gratuït Power Query. Per a Excel 2010-2013 es pot descarregar aquí, i a Excel 2016 ja està integrat per defecte. La seqüència d'accions serà la següent:
Power Query no sap com treballar amb taules normals, així que el primer pas és convertir la nostra taula en una de "intel·ligent". Per fer-ho, seleccioneu-lo i premeu la combinació Ctrl+T o seleccioneu a la pestanya Inici – Format com a taula (Inici — Format com a taula). A la pestanya que després apareix constructor (Disseny) podeu definir el nom de la taula (he deixat l'estàndard Taula 1):
Ara carreguem la nostra taula al complement de Power Query. Per fer-ho, a la pestanya dades (si teniu Excel 2016) o a la pestanya Power Query (si teniu Excel 2010-2013), feu clic a De la taula (Dades: de la taula):
A la finestra de l'editor de consultes que s'obre, seleccioneu la columna fent clic a la capçalera Empresa i premeu el botó de dalt Group (Agrupar per). Introduïu el nom de la nova columna i el tipus d'operació a l'agrupació: Totes les línies (Totes les files):
Premeu D'acord i obtenim una minitaula de valors agrupats per a cada empresa. El contingut de les taules és clarament visible si feu clic amb el botó esquerre al fons blanc de les cel·les (no al text!) a la columna resultant:
Ara afegim una columna més, on, mitjançant la funció, enganxem el contingut de les columnes Adreça a cadascuna de les minitaules, separades per comes. Per fer-ho, a la pestanya Afegeix columna premem Columna personalitzada (Afegeix columna: columna personalitzada) i a la finestra que apareix, introduïu el nom de la nova columna i la fórmula d'acoblament en l'idioma M integrat a Power Query:
Tingueu en compte que totes les funcions M distingeixen entre majúscules i minúscules (a diferència d'Excel). Després de fer clic a OK obtenim una nova columna amb adreces enganxades:
Queda per eliminar la columna ja innecessària Adreces de taula (clic dret al títol) Suprimeix la columna) i carregueu els resultats al full fent clic a la pestanya Inici — Tanca i baixa (Inici — Tancar i carregar):
Matís important: A diferència dels mètodes (funcions) anteriors, les taules de Power Query no s'actualitzen automàticament. Si en el futur hi haurà canvis a les dades d'origen, haureu de fer clic amb el botó dret a qualsevol lloc de la taula de resultats i seleccionar l'ordre Actualitza i desa (Actualització).
- Com dividir una cadena de text llarga en parts
- Diverses maneres d'enganxar text de diferents cel·les en una sola
- Utilitzant l'operador M'agrada per provar el text amb una màscara