Vinculación de texto por condición

Xa escribín sobre como podes pegar rapidamente texto de varias celas nunha soa e, pola contra, analizar unha cadea de texto longa en compoñentes. Agora vexamos unha tarefa próxima, pero un pouco máis complexa: como pegar texto de varias celas cando se cumpre unha determinada condición especificada. 

Digamos que temos unha base de datos de clientes, onde o nome dunha empresa pode corresponder a varios correos electrónicos diferentes dos seus empregados. A nosa tarefa é recoller todos os enderezos por nomes de empresas e concatenalos (separados por comas ou punto e coma) para facer, por exemplo, unha lista de correo para os clientes, é dicir, obter algo como:

Noutras palabras, necesitamos unha ferramenta que pegue (enlace) o texto segundo a condición: un análogo da función SUMMESLI (SUMIF), pero para texto.

Método 0. Fórmula

Non moi elegante, pero o xeito máis sinxelo. Podes escribir unha fórmula sinxela que comprobará se a empresa da seguinte fila difire da anterior. Se non difire, pega o seguinte enderezo separado por coma. Se difire, "restablecemos" o acumulado, comezando de novo:

As desvantaxes deste enfoque son obvias: de todas as celas da columna adicional obtidas, só necesitamos as últimas para cada empresa (amarelas). Se a lista é grande, para seleccionalas rapidamente, terás que engadir outra columna usando a función DLSTR (LEN), comprobando a lonxitude das cadeas acumuladas:

Agora podes filtrar os e copiar os enderezos necesarios para o seu uso posterior.

Método 1. Macrofunción de pegado por unha condición

Se a lista orixinal non está ordenada por compañía, a fórmula simple anterior non funciona, pero podes moverte facilmente cunha pequena función personalizada en VBA. Abre o Editor de Visual Basic premendo un atallo de teclado Alt + F11 ou usando o botón Visual Basic aba revelador (Desenvolvedor). Na xanela que se abre, insira un novo módulo baleiro a través do menú Inserir – Módulo e copia alí o texto da nosa función:

Función MergeIf(TextRange As Range, SearchRange As Range, Condition As String) Dim Delimeter As String, i As Long Delimeter = ", " os encolados non son iguais entre si - saímos cun erro Se SearchRange.Count <> TextRange.Count A continuación, MergeIf = CVErr(xlErrRef) Saír Función Final Se 'pasa por todas as celas, comprobe a condición e recolle o texto na variable OutText For i = 1 To SearchRange. Cells.Count If SearchRange.Cells(i) Like Condition Then OutText = OutText & TextRange.Cells(i) & Delimeter A continuación mostro os resultados sen o último delimitador MergeIf = Left(OutText, Len(OutText) - Len(Delimeter)) End función  

Se agora volves a Microsoft Excel, na lista de funcións (botón fx na barra de fórmulas ou na pestana Fórmulas: función de inserción) será posible atopar a nosa función CombinarSe na categoría Definido polo usuario (Definido polo usuario). Os argumentos da función son os seguintes:

Método 2. Concatenar texto por condición inexacta

Se substituímos o primeiro carácter da liña 13 da nosa macro = ao operador de coincidencia aproximada como, entón poderase realizar o encolado mediante unha coincidencia inexacta dos datos iniciais co criterio de selección. Por exemplo, se o nome da empresa pode escribirse en diferentes variantes, poderemos verificalos e recollelos todos cunha soa función:

Admítense os comodíns estándar:

• asterisco (*): indica calquera número de caracteres (incluída a súa ausencia)
• signo de interrogación (?): representa calquera carácter
• signo de libra (#): representa un díxitos (0-9)

Por defecto, o operador Like distingue entre maiúsculas e minúsculas, é dicir, entende, por exemplo, "Orion" e "orion" como empresas diferentes. Para ignorar maiúsculas e minúsculas, pode engadir a liña ao principio do módulo no editor de Visual Basic Opción Comparar texto, que cambiará a Like para que non distinga entre mayúsculas e minúsculas.

Deste xeito, podes compoñer máscaras moi complexas para comprobar as condicións, por exemplo:

• ?1##??777RUS: selección de todas as matrículas da rexión 777, comezando por 1
• LLC*: todas as empresas cuxo nome comeza por LLC
• ##7##: todos os produtos cun código dixital de cinco díxitos, onde o terceiro díxitos é 7
• ???? – todos os nomes de cinco letras, etc.
  

Método 3. Función de macro para pegar texto en dúas condicións

No traballo pode haber un problema cando necesites ligar o texto máis dunha condición. Por exemplo, imaxinemos que na nosa táboa anterior engadiuse unha columna máis coa cidade e o encolado debería realizarse non só para unha determinada empresa, senón tamén para unha determinada cidade. Neste caso, a nosa función terá que modernizarse lixeiramente engadindolle outra comprobación de rango:

Función MergeIfs(TextRange As Range, SearchRange1 As Range, Condition1 As String, SearchRange2 As Range, Condition2 As String) Dim Delimeter As String, i As Long Delimeter = ", " 'caracteres delimitadores (pódense substituír por espazo ou ; etc.) e.) 'se os intervalos de validación e de pegado non son iguais entre si, sae cun erro Se SearchRange1.Count <> TextRange.Count ou SearchRange2.Count <> TextRange.Count Then MergeIfs = CVErr(xlErrRef) Exit Function End If 'pase por todas as celas, comprobe todas as condicións e recolle o texto na variable OutText For i = 1 To SearchRange1.Cells.Count If SearchRange1.Cells(i) = Condition1 E SearchRange2.Cells(i) = Condition2 Entón OutText = OutText & TextRange.Cells(i) & Delimeter End If Next I 'mostra os resultados sen o último delimitador MergeIfs = Left(OutText, Len(OutText) - Len(Delimeter)) End Function  

Aplicarase exactamente do mesmo xeito; agora só hai que especificar máis os argumentos:

Método 4. Agrupación e pegado en Power Query

Podes resolver o problema sen programar en VBA, se usas o complemento gratuíto Power Query. Para Excel 2010-2013 pódese descargar aquí, e en Excel 2016 xa está integrado por defecto. A secuencia de actuacións será a seguinte:

Power Query non sabe como traballar con táboas normais, polo que o primeiro paso é converter a nosa táboa nunha "intelixente". Para iso, selecciónao e prema a combinación Ctrl+T ou seleccione na pestana Inicio - Formato como unha táboa (Inicio — Formato como táboa). Na pestana que aparece despois Construtor (Deseño) pode definir o nome da táboa (deixei o estándar Táboa 1):

Agora imos cargar a nosa táboa no complemento Power Query. Para iso, na pestana Data (se tes Excel 2016) ou na pestana Power Query (se tes Excel 2010-2013) fai clic Da mesa (Datos — Da táboa):

Na xanela do editor de consultas que se abre, seleccione a columna facendo clic na cabeceira compañía e prema o botón de arriba grupo (Agrupar por). Introduza o nome da nova columna e o tipo de operación na agrupación - Todas as liñas (Todas as filas):

Fai clic en Aceptar e obteremos unha mini-táboa de valores agrupados para cada empresa. O contido das táboas é claramente visible se fai clic co botón esquerdo no fondo branco das celas (non no texto!) na columna resultante:

Agora imos engadir unha columna máis, onde, mediante a función, pegamos o contido das columnas de Enderezo en cada unha das minitáboas, separadas por comas. Para iso, na pestana Engade columna presionamos Columna personalizada (Engadir columna — Columna personalizada) e na xanela que aparece, introduza o nome da nova columna e a fórmula de acoplamento na linguaxe M integrada en Power Query:

Teña en conta que todas as funcións M distinguen entre maiúsculas e minúsculas (a diferenza de Excel). Despois de facer clic en OK obtemos unha nova columna con enderezos pegados:

Queda por eliminar a columna xa innecesaria Enderezos da táboa (clic dereito no título) Eliminar columna) e carga os resultados na folla facendo clic na pestana Inicio — Pecha e descarga (Inicio — Pechar e cargar):

Matiz importante: A diferenza dos métodos (funcións) anteriores, as táboas de Power Query non se actualizan automaticamente. Se no futuro haberá algún cambio nos datos de orixe, terás que facer clic co botón dereito do rato en calquera lugar da táboa de resultados e seleccionar o comando Actualizar e gardar (Actualizar).

• Como dividir unha cadea de texto longa en partes
• Varias formas de pegar texto de diferentes celas nunha soa
• Usando o operador Gústame para probar o texto cunha máscara