Uno de los problemas más antigüos en Excel es trabajar sobre celdas combinadas.
A pesar de las grandes y largas charlas/recomendaciones para evitar su uso, incluso hoy en día nos encontramos cada vez más ficheros de trabajo donde se emplea esta herramienta de Combinar y centrar celdas.
Hace años expuse una possible solución para un caso concreto, con datos distribuidos uniformemente, empleando cierta combinación de funciones (ver aquí).
Hoy daremos una solución más general, aplicable a casi cualquier situación de trabajo con celdas combinadas.
Veamos nuestra situación de partida:
el objetivo es, a partir de los datos dispuesto por Comunidades Autónomas y Ciudades (columnas B,C y D), proceder al reparto del presupuesto asignado (columna E) según los porcentajes asignados en la columna F...
Con el problema de disponer de los datos fundamentales en celdas combinadas :'(
Lo que podría ser muy simple dispuesto en forma tabular, se convierte en un quebradero de cabeza...
Veamos un par de formas de solucionarlo.
En primer lugar de la forma clásica, como habitualmente se hacía... con una fórmula que trabaja por bloques de celdas combinadas.
Así en G3, y para trabajar sobre la celda combinada E3 (en realidad E3:E7) insertamos:
=PRODUCTO(BUSCAR(2;1/(E$3:E3<>"");E$3:E3);F3)
luego arrastramos desde G3 hasta G7... fíjate que el rango de la función BUSCAR es un 'rango corrido'.
La explicación o el detalle de la función sería:
la matriz (E$3:E3<>"") nos devuelve una matriz de VERDADEROS y FALSOS según haya o no datos en las celdas correspondientes a la celda combinada (E· (=E3:E7).
Esa matriz de V y F la convertimos en 1 o en un error de #¡DIV/0! al dividirlo entre 1:
1/(E$3:E3<>"")
Solo nos interesan los valores del 1.
La función BUSCAR localiza la última posición del 1 en la matriz anterior...
Es decir, el BUSCAR identifica y localiza siempre la posición de E3 en cualquiera de las celdas de nuestra fórmula (G3:G7).
Finalmente podremos multiplicar dicho valor por cada porcentaje del reparto, como veíamos con nuestra fórmula.
Habría que repetir, reajustando la fórmula y los rangos sobre los que trabajar, para cada una de las comunidades autónomas...
Lo que es, cuanto menos,tedioso ;-)
Así pues llegamos a una solución más estable y general, aprovechándonos del comportamiento desbordado de la versión 365 (o en du defecto ejecutando nuestra siguiente fórmula presionando Ctrl+Mayusc+Enter).
Comprobamos como con una única fórmula desbordada en la celda H3 completamos todo el rango:
=BUSCAR(FILA($E$3:$E$17);FILA($E$3:$E$17)/($E$3:$E$17<>"");$E$3:$E$17)*$F3:$F17
En esencia es la misma fórmula anterior... pero ya no buscamos solo la primera fila de un solo bloque, si no de todoso y cada uno de ellos...
Si ejecutamos por separado:
FILA($E$3:$E$17)/($E$3:$E$17<>"")
para nuestro ejemplo tendríamos la matriz:
{3;#¡DIV/0!;#¡DIV/0!;#¡DIV/0!;#¡DIV/0!;8;#¡DIV/0!;#¡DIV/0!;11;#¡DIV/0!;#¡DIV/0!;#¡DIV/0!;15;16;#¡DIV/0!}
Es decir, cada posición de celda combinada seguida de errores de #¡DIV/0! (que no nos interesan)... para seguidamente, con BUSCAR, localizar la coincidencia, obteniendo siempre los valores de esa celda :O
Llegando al mismo resultado con una única fórmula.
Un cálculo adicional sería ahora obtener el dato acumulado por categoría de ciudad (capital/otras) y por 'Comunidad autónoma'.
Igualmente tenemos el problema de trabajar sobre el rango B3:B17 compuesto de distintas celdas combinadas, y sobre el rango D3:D17 de celdas con tipo de ciudad (Capital/Otras).
Podremos optar por una solución clásica combinando SUMA y SI ejecutandolo matricialmente (presionar Ctrl + Mayusc + Enter), añadiendo en L4 la fórmula:
=SUMA(SI(BUSCAR(FILA($B$3:$B$17);FILA($B$3:$B$17)/($B$3:$B$17<>"");$B$3:$B$17)=$K4;SI($D$3:$D$17=L$3;$G$3:$G$17)))
O bien operando con la función SUMAPRODUCTO en L12:
=SUMAPRODUCTO((BUSCAR(FILA($B$3:$B$17);FILA($B$3:$B$17)/($B$3:$B$17<>"");$B$3:$B$17)=$K12)*($D$3:$D$17=L$11)*($G$3:$G$17))
Misma filosofía de trabajo en ambas funciones.
La clave entonces para trabajar con celdas combinadas se base, en todos los casos y momentos, en emplear esa combinación tan curiosa de
BUSCAR(FILA($B$3:$B$17);FILA($B$3:$B$17)/($B$3:$B$17<>"");$B$3:$B$17)
con el que recuperamos un vector 'autorellenado' con elementos en todas las posiciones de las celdas...
Muy interesante... espero os sea de utilidad ;-)
A pesar de las grandes y largas charlas/recomendaciones para evitar su uso, incluso hoy en día nos encontramos cada vez más ficheros de trabajo donde se emplea esta herramienta de Combinar y centrar celdas.
Hace años expuse una possible solución para un caso concreto, con datos distribuidos uniformemente, empleando cierta combinación de funciones (ver aquí).
Hoy daremos una solución más general, aplicable a casi cualquier situación de trabajo con celdas combinadas.
Veamos nuestra situación de partida:
el objetivo es, a partir de los datos dispuesto por Comunidades Autónomas y Ciudades (columnas B,C y D), proceder al reparto del presupuesto asignado (columna E) según los porcentajes asignados en la columna F...
Con el problema de disponer de los datos fundamentales en celdas combinadas :'(
Lo que podría ser muy simple dispuesto en forma tabular, se convierte en un quebradero de cabeza...
Veamos un par de formas de solucionarlo.
En primer lugar de la forma clásica, como habitualmente se hacía... con una fórmula que trabaja por bloques de celdas combinadas.
Así en G3, y para trabajar sobre la celda combinada E3 (en realidad E3:E7) insertamos:
=PRODUCTO(BUSCAR(2;1/(E$3:E3<>"");E$3:E3);F3)
luego arrastramos desde G3 hasta G7... fíjate que el rango de la función BUSCAR es un 'rango corrido'.
La explicación o el detalle de la función sería:
la matriz (E$3:E3<>"") nos devuelve una matriz de VERDADEROS y FALSOS según haya o no datos en las celdas correspondientes a la celda combinada (E· (=E3:E7).
Esa matriz de V y F la convertimos en 1 o en un error de #¡DIV/0! al dividirlo entre 1:
1/(E$3:E3<>"")
Solo nos interesan los valores del 1.
La función BUSCAR localiza la última posición del 1 en la matriz anterior...
Es decir, el BUSCAR identifica y localiza siempre la posición de E3 en cualquiera de las celdas de nuestra fórmula (G3:G7).
Finalmente podremos multiplicar dicho valor por cada porcentaje del reparto, como veíamos con nuestra fórmula.
Habría que repetir, reajustando la fórmula y los rangos sobre los que trabajar, para cada una de las comunidades autónomas...
Lo que es, cuanto menos,tedioso ;-)
Así pues llegamos a una solución más estable y general, aprovechándonos del comportamiento desbordado de la versión 365 (o en du defecto ejecutando nuestra siguiente fórmula presionando Ctrl+Mayusc+Enter).
Comprobamos como con una única fórmula desbordada en la celda H3 completamos todo el rango:
=BUSCAR(FILA($E$3:$E$17);FILA($E$3:$E$17)/($E$3:$E$17<>"");$E$3:$E$17)*$F3:$F17
En esencia es la misma fórmula anterior... pero ya no buscamos solo la primera fila de un solo bloque, si no de todoso y cada uno de ellos...
Si ejecutamos por separado:
FILA($E$3:$E$17)/($E$3:$E$17<>"")
para nuestro ejemplo tendríamos la matriz:
{3;#¡DIV/0!;#¡DIV/0!;#¡DIV/0!;#¡DIV/0!;8;#¡DIV/0!;#¡DIV/0!;11;#¡DIV/0!;#¡DIV/0!;#¡DIV/0!;15;16;#¡DIV/0!}
Es decir, cada posición de celda combinada seguida de errores de #¡DIV/0! (que no nos interesan)... para seguidamente, con BUSCAR, localizar la coincidencia, obteniendo siempre los valores de esa celda :O
Llegando al mismo resultado con una única fórmula.
Un cálculo adicional sería ahora obtener el dato acumulado por categoría de ciudad (capital/otras) y por 'Comunidad autónoma'.
Igualmente tenemos el problema de trabajar sobre el rango B3:B17 compuesto de distintas celdas combinadas, y sobre el rango D3:D17 de celdas con tipo de ciudad (Capital/Otras).
Podremos optar por una solución clásica combinando SUMA y SI ejecutandolo matricialmente (presionar Ctrl + Mayusc + Enter), añadiendo en L4 la fórmula:
=SUMA(SI(BUSCAR(FILA($B$3:$B$17);FILA($B$3:$B$17)/($B$3:$B$17<>"");$B$3:$B$17)=$K4;SI($D$3:$D$17=L$3;$G$3:$G$17)))
O bien operando con la función SUMAPRODUCTO en L12:
=SUMAPRODUCTO((BUSCAR(FILA($B$3:$B$17);FILA($B$3:$B$17)/($B$3:$B$17<>"");$B$3:$B$17)=$K12)*($D$3:$D$17=L$11)*($G$3:$G$17))
Misma filosofía de trabajo en ambas funciones.
La clave entonces para trabajar con celdas combinadas se base, en todos los casos y momentos, en emplear esa combinación tan curiosa de
BUSCAR(FILA($B$3:$B$17);FILA($B$3:$B$17)/($B$3:$B$17<>"");$B$3:$B$17)
con el que recuperamos un vector 'autorellenado' con elementos en todas las posiciones de las celdas...
Muy interesante... espero os sea de utilidad ;-)
No hay comentarios:
Publicar un comentario
Nota: solo los miembros de este blog pueden publicar comentarios.