Contenido
Ya escribí sobre cómo puede pegar rápidamente texto de varias celdas en una sola y, a la inversa, analizar una cadena de texto larga en componentes. Ahora veamos una tarea final, pero un poco más compleja: cómo pegar texto de varias celdas cuando se cumple una determinada condición específica.
Digamos que tenemos una base de datos de clientes, donde el nombre de una empresa puede corresponder a varios correos electrónicos diferentes de sus empleados. Nuestra tarea es recopilar todas las direcciones por nombres de empresas y concatenarlas (separadas por comas o punto y coma) para hacer, por ejemplo, una lista de correo para clientes, es decir, obtener un resultado similar a:
En otras palabras, necesitamos una herramienta que pegue (vincule) el texto de acuerdo con la condición, un análogo de la función SUMMESLI (SUMIF), pero para texto.
Método 0. Fórmula
No muy elegante, pero la forma más fácil. Puede escribir una fórmula simple que verificará si la empresa en la siguiente fila difiere de la anterior. Si no difiere, pegue la siguiente dirección separada por una coma. Si difiere, entonces "restablecemos" el acumulado, comenzando de nuevo:
Las desventajas de este enfoque son obvias: de todas las celdas de la columna adicional obtenida, solo necesitamos las últimas para cada empresa (amarilla). Si la lista es grande, entonces para seleccionarlos rápidamente, deberá agregar otra columna usando la función DLSTR (LEN), comprobando la longitud de las cadenas acumuladas:
Ahora puede filtrarlos y copiar el pegado de direcciones necesario para su uso posterior.
Método 1. Macrofunción de pegar por una condición.
Si la lista original no está ordenada por compañía, entonces la fórmula simple anterior no funciona, pero puede moverse fácilmente con una pequeña función personalizada en VBA. Abra el Editor de Visual Basic presionando un atajo de teclado Alt + F11 o usando el botón Visual Basic de la pestaña. revelador (Desarrollador). En la ventana que se abre, inserte un nuevo módulo vacío a través del menú Insertar – Módulo y copie el texto de nuestra función allí:
Función MergeIf(TextRange As Range, SearchRange As Range, Condition As String) Dim Delimeter As String, i As Long Delimeter = ", " los encolados no son iguales entre sí; salimos con un error If SearchRange.Count <> TextRange.Count Luego MergeIf = CVErr(xlErrRef) Exit Function End If 'recorre todas las celdas, verifica la condición y recopila el texto en la variable OutText For i = 1 To SearchRange. Cells.Count If SearchRange.Cells(i) Like Condition Then OutText = OutText & TextRange.Cells(i) & Delimeter Next i 'muestra los resultados sin el último delimitador MergeIf = Left(OutText, Len(OutText) - Len(Delimeter)) End función
Si ahora regresa a Microsoft Excel, entonces en la lista de funciones (botón fx en la barra o pestaña de fórmulas Fórmulas – Función de inserción) será posible encontrar nuestra función fusionar si en categoría Definido por el usuario (Usuario definido). Los argumentos de la función son los siguientes:
Método 2. Concatenar texto por condición inexacta
Si reemplazamos el primer carácter en la línea 13 de nuestra macro = al operador de coincidencia aproximada Me gusta, entonces será posible realizar el encolado por una coincidencia inexacta de los datos iniciales con el criterio de selección. Por ejemplo, si el nombre de la empresa se puede escribir en diferentes variantes, podemos verificarlas y recopilarlas todas con una sola función:
Se admiten comodines estándar:
- asterisco (*): denota cualquier número de caracteres (incluida su ausencia)
- signo de interrogación (?) – representa cualquier carácter único
- signo de libra (#): representa cualquier dígito (0-9)
De forma predeterminada, el operador Me gusta distingue entre mayúsculas y minúsculas, es decir, entiende, por ejemplo, "Orion" y "orion" como empresas diferentes. Para ignorar mayúsculas y minúsculas, puede agregar la línea al principio del módulo en el editor de Visual Basic Opción Comparar texto, que cambiará Like para que no distinga entre mayúsculas y minúsculas.
De esta forma, puede componer máscaras muy complejas para verificar las condiciones, por ejemplo:
- ?1##??777RUS – selección de todas las matrículas de la región 777, comenzando con 1
- LLC*: todas las empresas cuyo nombre comienza con LLC
- ##7##: todos los productos con un código digital de cinco dígitos, donde el tercer dígito es 7
- ????? – todos los nombres de cinco letras, etc.
Método 3. Función macro para pegar texto bajo dos condiciones
En el trabajo puede haber un problema cuando se necesita vincular el texto a más de una condición. Por ejemplo, imaginemos que en nuestra tabla anterior, se agregó una columna más con la ciudad, y el pegado debe realizarse no solo para una empresa determinada, sino también para una ciudad determinada. En este caso, nuestra función deberá modernizarse ligeramente al agregarle otra verificació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 (se pueden reemplazar con espacios o ; etc.) e.) 'si los rangos de validación y pegado no son iguales entre sí, salga con un error If SearchRange1.Count <> TextRange.Count or SearchRange2.Count <> TextRange.Count Then MergeIfs = CVErr(xlErrRef) Exit Function End If 'revisar todas las celdas, verificar todas las condiciones y recopilar el texto en la variable OutText For i = 1 To SearchRange1.Cells.Count If SearchRange1.Cells(i) = Condition1 And SearchRange2.Cells(i) = Condition2 Then OutText = OutText & TextRange.Cells(i) & Delimeter End If Next i 'muestra los resultados sin el último delimitador MergeIfs = Left(OutText, Len(OutText) - Len(Delimeter)) End Function
Se aplicará exactamente de la misma manera; ahora solo es necesario especificar más los argumentos:
Método 4. Agrupación y pegado en Power Query
Puede resolver el problema sin programar en VBA, si usa el complemento gratuito Power Query. Para Excel 2010-2013 se puede descargar aquí, y en Excel 2016 ya está integrado de forma predeterminada. La secuencia de acciones será la siguiente:
Power Query no sabe trabajar con tablas regulares, por lo que el primer paso es convertir nuestra tabla en una “inteligente”. Para ello, selecciónalo y pulsa la combinación Ctrl+T o seleccione de la pestaña Inicio – Formato como tabla (Inicio — Formatear como tabla). En la pestaña que luego aparece Constructor (Diseño) puede establecer el nombre de la tabla (dejé el estándar Tabla 1):
Ahora carguemos nuestra tabla en el complemento de Power Query. Para ello, en la pestaña Datos (si tiene Excel 2016) o en la pestaña Power Query (si tiene Excel 2010-2013) haga clic en De la mesa (Datos — De la tabla):
En la ventana del editor de consultas que se abre, seleccione la columna haciendo clic en el encabezado Empresa y presiona el boton de arriba Grupo procesos (Agrupar por). Introduzca el nombre de la nueva columna y el tipo de operación en la agrupación – Todas las lineas (Todas las filas):
Hacemos clic en Aceptar y obtendremos una mini tabla de valores agrupados para cada empresa. El contenido de las tablas es claramente visible si hace clic izquierdo en el fondo blanco de las celdas (¡no en el texto!) en la columna resultante:
Ahora agreguemos una columna más, donde, usando la función, peguemos los contenidos de las columnas de Dirección en cada una de las mini tablas, separados por comas. Para ello, en la pestaña Añadir columna presionamos columna personalizada (Añadir columna — Columna personalizada) y en la ventana que aparece, ingrese el nombre de la nueva columna y la fórmula de acoplamiento en el lenguaje M integrado en Power Query:
Tenga en cuenta que todas las funciones M distinguen entre mayúsculas y minúsculas (a diferencia de Excel). Después de hacer clic en OK obtenemos una nueva columna con direcciones pegadas:
Queda por eliminar la columna ya innecesaria. Direcciones de tabla (clic derecho sobre el título) Eliminar columna) y suba los resultados a la hoja haciendo clic en la pestaña Inicio — Cerrar y descargar (Inicio — Cerrar y cargar):
Matiz importante: A diferencia de los métodos (funciones) anteriores, las tablas de Power Query no se actualizan automáticamente. Si en el futuro habrá algún cambio en los datos de origen, deberá hacer clic derecho en cualquier lugar de la tabla de resultados y seleccionar el comando Actualizar y guardar (Actualizar).
- Cómo dividir una cadena de texto larga en partes
- Varias formas de pegar texto de diferentes celdas en una
- Uso del operador Me gusta para probar el texto con una máscara