Subtotales con Resultado en la Celda Derecha

Descargar el Archivo

Vamos a ver cómo resolver el cálculo de sumas parciales separados por celdas en blanco y dejando el resultado a la derecha del último número de cada rango. En la imagen puede ver distintas cifras y queremos realizar una única fórmula que sume cada grupo de cifras y coloque el resultado en las celdas que aparecen sombreadas en naranja:

Para ello sitúese en la celda C3 y escriba la siguiente fórmula:

=SI(B4="";SUMA($B$3:B3)-SUMA($C$2:C2);"")

Una vez realizada esta fórmula cópiela hasta la última celda en la que tenga una cifra (en nuestro ejemplo C19)

Fíjese que lo que estamos haciendo es comprobar en cada celda del rango C3:C19 si la celda inmediata inferior izquierda está en blanco. En tal caso le pedimos que realice una SUMA Acumulada. Por otro lado, y para evitar duplicar los subtotales, le pedimos que reste el rango donde vamos calculando los anteriores subtotales hasta justo la celda inmediata superior donde realiza la suma.

Para añadir automáticamente el sombreado naranja haga lo siguiente:

1. Seleccione el rango C3:C19

2. Vaya al menú Formato/Formato condicional y seleccione los valores mostrados en la siguiente figura:

Valor de la celda no igual a =""

Suma Columnas Pares con Rango Dinámico

Descargar Archivo

Por acabar de "rizar el rizo" vamos a ver como podemos sumar el contenido de las columnas pares en base al mes que introduzcamos en nuestra entrada de datos. Siguiendo con el ejemplo del artículo anterior tenemos las ventas mensuales y el porcentaje que representan sobre el presupuesto semestral. Queremos calcular la suma acumulada hasta el mes que le indiquemos.

Haciendo "zoom":

Como se puede apreciar en la imagen, he puesto como entrada de datos el mes (B3) hasta el cuál queremos calcular la suma acumulada de las columnas pares. Hecho esto nos situamos en O6 y escribimos la siguiente fórmula matricial:

{=SUMA((RESIDUO(COLUMNA(DESREF(B6;;;;$B$3*2));2)=0)*(DESREF(B6;;;;$B$3*2)))}

Como se puede ver he introducido como dinámico el argumento de la función COLUMNA, ya que la suma que debe realizar dependerá del mes que introduzcamos en B3. En la siguiente imagen puede comprobar que cuando modificamos el número del mes en B3 e introducimos, por ejemplo, el 3 (marzo) el cálculo del total acumulado cambia también adaptándose al nuevo rango:

Suma de Columnas Pares con Fórmula Matricial

A raíz del artículo "Suma de filas Impares" me habéis preguntado en varias ocasiones si se puede resolver dicho problema con una única fórmula. La respuesta es sí. Para ello debemos hacer uso de las fórmulas matriciales. Supongamos que tenemos el siguiente modelo:

Para que se vea mejor hagamos "zoom" sobre las primeras celdas:

Como se puede comprobar, tenemos la cifra de ventas de cada mes del primer semestre y a continuación el porcentaje que representa sobre el presupuesto para dicho semestre ¿Podemos realizar una única fórmula en O4 que realice el sumatorio de los seis meses? Sí. A saber:

{=SUMA((RESIDUO(COLUMNA(B4:M4);2)=0)*(B4:M4))}

Recuerde que por tratarse de una fórmula matricial al acabar de escribir dicha fórmula NO debemos pulsar Enter sino Ctrl+Shift+Enter.

Evidentemente esta misma fórmula la podemos aplicar para sumar columnas impares sustituyendo el cero por un uno como argumento de la función RESIDUO.

Copiar sólo Celdas Visibles

Hoy una tarea sencilla pero que me preguntáis de manera recurrente: "en muchas ocasiones cuando por ejemplo filtro una lista o trabajo con subtotales y quiero copiar y pegar un rango, me pega toda la tabla (y no las celdas que a mi me interesan y que estoy viendo en pantalla)". Veámoslo con un ejemplo.

Supongamos que tenemos la siguiente información:

Queremos "reclasificar" la información por zonas. Para ello utilizamos la herramienta Subtotales (esta herramienta la explicaré más a fondo en el próximo artículo). Lo primero que debemos hacer es ordenar de mayor a menor o de menor a mayor el campo con el que vamos a trabajar, esto es, Zona:

1. Nos situamos en B3 y pulsamos el icono AZ (orden ascendente).

2. Una vez ordenado este campo vamos al menú Datos/Subtotales y definimos como se muestra en la imagen:

3. Pulsamos Aceptar y el resultado será:

4. Si ahora copiamos esta tabla y la pegamos, por ejemplo, a partir de la celda A25, nos encontraremos con el siguiente resultado:

5. Para solucionar este problema debemos seleccionar el rango A2:C18.

6. Vamos al menú Edición/Ir a/Especial.

7. Seleccionamos la opción Sólo celdas visibles y pulsamos Aceptar.

8. Pulsamos el icono Copiar.

9. Nos situamos en la celda en la que queramos pegar, por ejemplo, A25 y pulsamos Pegar. El resultado será el deseado:

Cálculos con Rangos Tridimensionales

Descargar el Archivo

En el artículo de hoy vamos a ver cómo podemos trabajar con lo que se denomina Rangos Tridimensionales en Excel. Un Rango Tridimensional lo podemos definir como aquel rango que se extiende a varias hojas. Supongamos, por ejemplo, que tenemos en varias hojas las ventas relativas a varios comerciales en distintas zonas:

En la hoja Zona1 tenemos:

En la hoja Zona2 tenemos:

En la hoja Zona3 tenemos:

En la hoja Zona4 tenemos:

Finalmente tenemos una hoja RESUMEN donde queremos recoger las ventas totales de cada comercial y el promedio de cada uno de ellos:

Para realizar estos cálculos NO es necesaria la siguiente fórmula en B3:

=Zona1!B3+Zona2!B3+Zona3!B3+Zona4!B3

Para solucionar este cálculo de una manera más sencilla y rápida debe hacer lo siguiente:

1. Nos situamos en la hoja RESUMEN en la celda B3 y escribimos =SUMA(

2. A continuación hacemos clic en la hoja Zona1 y en la celda B3.

3. A continuación pulsamos SHIFT (Mayús) y manteniendo dicha tecla pulsada hacemos clic en la última hoja, es decir, en la hoja Zona4, y pulsamos enter.

El resultado es la siguiente fórmula:

=SUMA(Zona1:Zona4!B3)

Como puede comprobar estamos sumando un rango que se extiende a las hojas zona1, zona2, zona3 y zona4. Esto es lo que se denomina un rango tridimensional en excel.

4. Nos situamos en C3 de la hoja RESUMEN y escribimos =PROMEDIO( y repetimos los mismos pasos que en el caso anterior para solucionar esta fórmula:

=PROMEDIO(Zona1:Zona4!B3)

5. Seleccionamos B3 y C3 y hacemos doble clic en la parte inferior derecha de dicha selección (copiado inteligente) para rellenar todas las fórmulas:

La ayuda de Excel nos indica qué funciones pueden utilizarse con referencias 3D. A saber: "Pueden utilizarse referencias 3D a las celdas de otras hojas para definir nombres y crear fórmulas mediante las siguientes funciones: SUMA, PROMEDIO, PROMEDIOA, CONTAR, CONTARA, MAX, MAXA, MIN, MINA, PRODUCTO, DESVEST, DESVESTA, DESVESTP, DESVESTPA, VAR, VARA, VARP y VARPA. No pueden utilizarse referencias 3D en fórmulas matriciales".

Como es habitual en las "ayudas" de excel no nos cuenta toda la verdad... De hecho hay muchas más funciones que permiten referencias 3D. Estas son algunas que he comprobado personalmente (y seguro que hay más) y que no aparecen en el listado de la ayuda:

CUARTIL, K.ESIMO.MENOR, K.ESIMO.MAYOR, JERARQUÍA, Y, O, PERCENTIL.