Las celdas del rango B3:B17 contienen texto que debemos agrupar en E2. Vamos a resolver el ejercicio sin utilizar macros.
El problema debe resolverse para cualquier texto y cualquier número de filas que pongamos en la columna B. Usando la función CONCATENAR podríamos pensar que una fórmula matricial del estilo, {=CONCATENAR("";DESREF($B$2;1;0;CONTARA(B:B)-1;1))}, nos acercaría a la solución, pero comprobamos que CONCATENAR no funciona bien si uno de los argumentos es una matriz.
En http://www.officefull.es/seccion/excel/107-hmo.html hay una solución al problema planteado utilizando macros de Excel 4, pero el objetivo es prescindir de las macros de cualquier versión y resolver el ejercicio, únicamente, con funciones de Excel.
Necesitamos un procedimiento repetitivo, algo parecido a las funciones FOR o WHILE de Visual Basic, para poder aplicar repetidamente la función CONCATENAR. Lo conseguiremos usando referencias circulares.
Vamos a comenzar contando los caracteres que hay en B3:B17 y el número de filas que tenemos que agrupar. Pondremos las siguientes fórmulas:
En E4:
=SUMAPRODUCTO(LARGO(DESREF(B2;1;0; CONTARA(B:B)-1;1))) [Resultado: 48]En E5:
=CONTARA(B:B)-1 [Resultado: 15]
Es el momento de permitir las referencias circulares. Nos bastarán 150 iteraciones.
Abrimos el menú Archivo y accedemos a Opciones. En la ficha Fórmulas, habilitamos el cálculo interactivo, ponemos 150 iteraciones y un cambio máximo de cero.
En E6:
=SI(E7>E5;0;1) [Resultado: 1]
En E7:
=(E7+1)*E6 [Resultado: 14]
Aquí hay una referencia circular; E6 utiliza el valor de E7 y E7 utiliza el de E6. Con esto conseguimos que en E6 haya siempre un 1 o un 0. Puesto que E7 incrementa su valor en una unidad en cada iteración, su valor va creciendo hasta llegar a 15 (valor de E5). En el momento que E7 supere 15, E6 valdrá 0 y E7 empezará de nuevo el ciclo desde 0. Pulsando reiteradamente F9 forzaremos las sucesivas iteraciones.
Ahora, vamos a ir, poco a poco, componiendo la fórmula hasta llegar al resultado deseado.
En E8:
=CONCATENAR($E$8;DESREF($B$2;$E$7;0;1;1))
Estudiemos esta fórmula. DESREF($B$2;$E$7;0;1;1) devuelve el valor de una celda de la columna B. Empieza en B2 (primer argumento) y salta a la fila señalada en E7 (segundo argumento). Este último valor, como hemos visto, está cambiando en cada iteración (de 0 a 15), por lo que la función devolverá los valores de las celdas B2 a B17. Se incluye el encabezado porque E6 empieza desde 1 pero en las siguientes iteraciones empieza desde 0. Por tanto, la fórmula devuelve todos los valores de la columna B.
Luego, con la función CONCATENAR, ponemos estos valores uno a continuación del otro. El primer valor es un 0, pero no importa, lo eliminaremos enseguida. A continuación, vienen todos los caracteres de B3:B17. En ese momento, E6 se pone a cero apuntando al encabezado de la columna B y, como se realizan 150 iteraciones, el siguiente bucle empieza en B2 (lo que no nos interesa) y se añade la columna B entera, incluido el encabezado. El proceso continúa varios ciclos hasta consumir el número de iteraciones fijado. Este es un problema que se debe resolver. Para ello, sustituiremos la fórmula por la siguiente:
En E8:
=SI(E6=0;"";IZQUIERDA(CONCATENAR($E$8;DESREF($B$2;$E$7;0;1;1));E4))
Para eliminar el texto extra de la celda E8, utilizamos la función IZQUIERDA, con la que extraemos los 48 primeros caracteres (celda E4). Además, con la función SI, eliminamos el 0 que nos aparecía al comienzo, empezando con un carácter nulo cuando E6 es cero.
El ejercicio aún no está terminado. Si pulsamos repetidamente F9 hasta que en E7 aparezca un número menor que 15 (número de filas de la lista), la fórmula de E8 concatenará el número de celdas que indica E7 y el texto no estará completo. La solución es bien sencilla y la pondremos en E2.
En E2:
=SI(LARGO(E8)=E4;E8;E2)
Ahora, funciona correctamente. Lo comprobamos añadiendo o eliminando datos en la columna B. Los valores de E6, E7 y E8 cambiarán cada vez que pulsemos F9 o modifiquemos el valor de una celda cualquiera de la hoja.
No hay comentarios:
Publicar un comentario