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.

Suma de Filas Impares (o Pares)

Descargar el Archivo

En un artículo anterior vimos como se podía dar formato a filas y/o columnas pares (o impares) de una tabla. En esta ocasión me habéis planteado el siguiente caso:

"Tengo una tabla donde registro las ventas relativas a más de 150 referencias que se venden en 10 áreas. Debajo de la cifra de ventas de cada referencia tengo el porcentaje que supone respecto a las ventas totales por área. El problema es que precisamente para calcular las ventas totales no puedo aplicar la Autosuma porque sólo me interesaría sumar las filas donde están las cifras de venta y no las de los porcentajes".

Resumiendo el problema en 10 referencias y 3 áreas (la solución es exactamente la misma), la situación de partida sería la siguiente:

Y queremos calcular las ventas totales de cada área y colocarlas, por ejemplo, en B25:D25.

1. Nos situamos en E5 y escribimos la siguiente fórmula:

=RESIDUO(FILA(B5);2)

La función FILA devuelve el número de fila de la celda en cuestión. FILA(B5) devolverá 5 que es el número de fila de dicha referencia. La función RESIDUO, por su parte, calcula el resto resultante de dividir un número (que en nuestro caso será el número de fila) y el divisor (que en nuestro caso es 2). El resto de cualquier número impar dividido por 2 siempre es 1. Si el número es par el resto será siempre 0.

2. Copiamos la fórmula de E5 hasta E24 (o utilizamos el copiado inteligente y hacemos doble clic en la parte inferior derecha de la celda E5).

3. Ya tenemos identificadas las filas pares y las impares. Nos situamos en B25 y escribimos la siguiente fórmula:

=SUMAR.SI($E$5:$E$24;1;B5:B24)

De esta manera estamos comprobando que celdas del rango E5:E24 valen 1 (que serán precisamente las filas impares) y le estamos pidiendo que sume las filas del rango B5:B24 que cumplan previamente este criterio.

4. Copiamos la fórmula de B25 en C25 y D25.

5. Ocultamos la columna E para que no se vean los unos y ceros que hemos utilizado para identificar las filas pares e impares. El resultado será el siguiente:

El 100% que aparece en el rango B26:D26 es la suma de las filas pares. Puede calcularlo copiando las fórmulas de B25:D25 hacia abajo y sustituyendo el criterio con valor cero por un uno.

Si además quiere calcular el sumatorio de cada referencia para las tres áreas puede resolverlo directamente con el copiado inteligente por bloques (que ya vimos en otro artículo). Para ello debe hacer lo siguiente:

1. Nos situamos en la celda F5 y calculamos la primera suma, es decir, =SUMA(B5:D5)

2. Seleccionamos las celdas F5:F6 y hacemos doble clic encima del pequeño cuadrado negro que aparecerá en la parte inferior derecha de dicha selección y... problema resuelto:

Automatizar Textos-Datos de un Informe

Descargar el Archivo

En numerosísimas ocasiones en nuestra labor diaria utilizamos informes, plantillas, formularios... que contienen textos que a su vez contienen datos que introducimos de forma manual cuando, en realidad, podríamos ahorrarnos dicha tarea rutinaria que no aporta gran valor (más bien aporta errores cuando nos olvidamos de actualizar correctamente dichos datos). Por ejemplo, es muy habitual tener que introducir la "caducidad" de una oferta comercial; o el nombre del responsable de dicha oferta; etcétera. Veámoslo con un ejemplo sencillo. Supongamos que tenemos el siguiente formulario que, toda vez cubierto, enviamos al cliente que nos ha solicitado el presupuesto.

Los círculos naranja destacan aquella información que debemos introducir (fecha validez oferta; comercial responsable de la oferta; y porcentaje de iva a aplicar) y que, en muchas ocasiones, introducimos a mano cada vez que preparamos una oferta. Para automatizar estas tareas debemos hacer lo siguiente:

1. Planteamos la entrada de datos que se muestra a continuación:

2. Nos situamos en C2 y escribimos, por ejemplo, 15, con lo que indicamos que la vigencia de la oferta será de 15 días a contar desde hoy (desde la fecha de emisión de la propuesta).

3. Nos ponemos en C3 y vamos al menú Datos/Validación datos y en Permitir seleccionamos Lista y en el cuadro Origen le indicamos el rango G3:G7, que es donde tenemos la lista de comerciales de la empresa. Pulsamos Aceptar.

4. En C4 introducimos 16%, que será el tipo de iva aplicable.

5. Nos situamos en C6 y escribimos la fórmula:

=HOY()

Esta función no tiene argumentos porque simplemente nos proporciona la fecha del día en curso (que será la fecha desde la que comenzaremos a contar la vigencia de la propuesta).

Concluidos estos pasos tendremos lo siguiente:

6. Nos situamos en B8 y escribimos:

="Este presupuesto tiene validez hasta el "&TEXTO(C6+C2;"dd/mm/aaaa")

Fíjese que el texto inicial, "Este presupuesto tiene validez hasta el ", está entre comillas. Esto es necesario porque vamos a concatenar más elementos a dicho texto. La segunda parte de la fórmula, &TEXTO(C6+C2;"dd/mm/aaaa") concatena al texto el cálculo de la suma de 15 días naturales respecto a la fecha en curso. Como ya comentamos en el artículo "Cálculos Simultáneos en una Celda", al utilizar la función CONCATENAR perdemos los formatos específicos y excel aplica el formato General. En este ejemplo, si no utilizáramos la función TEXTO, el resultado devuelto sería: Este presupuesto tiene validez hasta el 39991. Para dar el formato correcto a este número (39991), que es el número de serie correspondiente a la fecha 27/06/2009, introducimos la función TEXTO.

7. Nos situamos en la celda B9 y escribimos la fórmula:

="Comercial responsable: "&C3

8. Nos situamos en la celda D20 y escribimos:

="iva "&TEXTO(C4;"0%")

De esta manera si cambiamos el porcentaje de iva a aplicar en la celda C4 el texto de D20 se actualizará automáticamente.

Cálculos Simultáneos en una Celda

Descargar el Archivo

Hoy vamos a ver cómo se pueden realizar cálculos simultáneos en una única celda. Supongamos que tenemos los ingresos y gastos de diversas áreas de un negocio y queremos calcular el margen y el porcentaje que representa dicho margen sobre los ingresos. Pero queremos que muestre ambos resultados (el margen en Euros y el porcentaje sobre ingresos) en la misma celda.

Partiendo del ejemplo mostrado en la imagen, queremos realizar en E4 los cálculos citados. Para ello necesitaremos utilizar la función CONCATENAR o, lo que es lo mismo pero más corto, &. Nos situamos en E4 y escribimos la siguiente fórmula:

=C4-D4&" "&(C4-D4)/C4

C4-D4 calcula el margen en euros

&" " une al resultado calculado tres espacios en blanco (para separar del siguiente cálculo)

&(C4-D4)/C4 une a todo lo anterior el cálculo del porcentaje que supone el margen sobre el ingreso.

El resultado de esta fórmula será:

Aunque el resultado numérico es correcto, creo que estará de acuerdo conmigo en que el formato presentado no es el más conveniente... Cuando realicemos cálculos y utilicemos la función CONCATENAR nos encontraremos con este problema de formatos. Para solucionarlo podemos acudir a la función TEXTO.

TEXTO(Valor;Formato)

Esta función convierte un valor en texto con un formato determinado. El argumento Valor es un valor numérico o una fórmula que nos proporciona un valor numérico. El argumento Formato es el formato que queremos darle al Valor en cuestión. El formato elegido debemos introducirlo entre comillas y podemos indicar cualquiera de las posibilidades que se pueden encontrar en el menú Formato/Celdas/Numero.

Nos situamos nuevamente en E4 y procedemos a aplicar esta función a nuestra fórmula inicial:

=TEXTO(C4-D4;"0.00€")&" "&TEXTO((C4-D4)/C4;"0,00%")

Fíjese que hemos añadido la función TEXTO para conseguir que el primer cálculo aparezca con formato de moneda (0.00€) y el segundo cálculo con formato porcentual (0,00%). Una vez hecha esta fórmula y copiada en el rango E5:E11 el resultado será:

Si queremos añadir el margen total y el porcentaje sobre los ingresos totales deberemos realizar una nueva fórmula en E12. A saber:

=TEXTO(SUMA(C4:C11)-SUMA(D4:D11);"0.00€")&" "&TEXTO((C12-D12)/C12;"0,00%")

Cálculo de Días Laborables

Descargar el Archivo

Hoy vamos a ver un pequeño modelo para calcular la fecha de incorporación al trabajo después de unas benditas vacaciones. Para ello utilizaremos la función DIA.LAB

Comenzamos creando la entrada de datos:

Como se puede observar, en la celda C3 he introducido un Control de número que nos permitirá incrementar o disminuir el número de días laborables para nuestro cálculo (puede consultar cómo introducir dicho Control de número en el artículo Análisis de Sensibilidad con Controles de Número).

Lo que queremos conseguir es automatizar el cálculo de la fecha en la que deberíamos incorporarnos después de, por ejemplo, disfrutar de un periodo de vacaciones. Para ello tendremos que tener en cuenta los sábados y domingos y los días festivos. La función DIA.LAB nos ayudará en esta labor. 

Nota: La función DIA.LAB no aparece por defecto en la categoría Fecha y Hora de Excel (en versiones anteriores a Excel 2007). Para añadirla debe hacer lo siguiente: vaya al menú Herramientas/Complementos y active la casilla de verificación Herramientas para análisis. Pulse Aceptar y nuevas funciones, incluida la que nos ocupa, le aparecerán en las distintas categorías.

DIA.LAB(Fecha_inicial;días_lab;festivos)

Esta función devuelve el número de serie de una fecha anterior o posterior a la fecha inicial según determinemos el número de días laborables. DIA.LAB considera los sábados como no laborables.

Como se puede adivinar, observando los argumentos de esta función, vamos a necesitar un listado de los días festivos del periodo a analizar. En nuestro ejemplo hemos introducido una lista de las fechas festivas en 2009 y 2010 (calendario que debe actualizarse y completarse con festivos locales):

Una vez preparado el listado sólo tenemos que hacer lo siguiente:

1. Seleccionamos el rango E2:E25 y vamos al menú Insertar/Nombre/Crear y seleccionamos Crear nombre en Fila superior. De esta manera nuestra lista (rango) de días festivos pasará a denominarse Fiestas.

2. Nos situamos en la celda B6 y escribimos la siguiente fórmula:

=DIA.LAB(C2;C3;Fiestas)

De esta manera le estamos pidiendo que a la fecha inicial 21/12/2009 le añada 12 días laborables. DIA.LAB tiene en cuenta como no laborable tanto la fecha inicial como el último día que calcula, por lo que nos devuelve la fecha inmediata posterior, que es la correcta. En nuestro ejemplo nos devuelve el 11 de enero de 2010, que sería la fecha de incorporación (no el último día de vacaciones). Puede añadir una salida de datos de Días Naturales transcurridos (=B6-C2) para ver la diferencia: 

Una función similar a la vista es DIAS.LAB

Esta función calcula el número de días laborables transcurridos entre dos fechas indicadas. Su sintaxis es: DIAS.LAB(Fecha_inicial;Fecha_final;Festivos)

Pruebe a calcular con esta función el número de días laborables transcurridos entre el 21/12/2009 y el 11/01/2010 y comprobará que le devuelve precisamente 12.

Reorganizar Listados de Nombres y Apellidos

Una pregunta que me habéis realizado de manera recurrente es si se puede "trocear" en distintas celdas un listado de nombres y apellidos en el que tenéis dichos nombres y apellidos en celdas únicas. La solución es muy sencilla pero deduzco que poco conocida por el número de veces que he tenido que contestar esta pregunta... 

Existen varias formas (además de hacerlo a mano...) de solucionar este problema. En este caso vamos a resolverlo utilizando la herramienta de Texto en Columnas. Una vez concluyamos el "troceado" trabajaremos en el "reorganizado" de los nombres y apellidos.

A) TROCEADO

 Supongamos que tenemos el siguiente listado de empleados:

Queremos conseguir separar los nombres y apellidos en distintas columnas. Para ello haremos lo siguiente:

1. Seleccionamos el rango A4:A9 y vamos al menú Datos/Texto en columnas... Se abrirá la siguiente ventana:

2. Como se muestra en la imagen, en Tipo de los datos de origen seleccionamos la opción Delimitados y pulsamos Siguiente. Aparecerá esta ventana:

3. El tipo de Separador que tenemos en nuestro listado es Espacios, por lo que marcamos dicha casilla de verificación. Además, dejamos marcada la opción Considerar separadores consecutivos como uno solo. Esta opción permitirá que si existe más de un espacio entre nombres y apellidos los considere -los espacios- como uno solo, evitando así que inserte columnas en blanco inútiles. Pulsamos Siguiente y se abrirá esta ventana:

4. en el cuadro Destino seleccionamos la celda a partir de la cual queremos que aparezcan separados en columnas los nombres y apellidos de nuestro listado (en nuestro ejemplo B4). Si no modificamos esta opción Excel sobreescribirá el resultado sobre nuestro listado original. Pulsamos Finalizar y obtenemos el siguiente resultado:

B) REORGANIZACIÓN

Vamos a ver ahora cómo podemos reorganizar, en función de nuestras necesidades, estos nombres y apellidos que hemos separado en distintas columnas.

Supongamos que queremos conseguir el formato: Apellidos, Nombre/s. El primer problema que nos vamos a encontrar a la hora de realizar la fórmula es que existen registros con dos nombres (María Teresa) y registros con nombre único (Ignacio). Empezamos solucionando, para facilitar la comprensión, los registros con nombre único:

1. Nos situamos en F5 y escribimos la fórmula:

=C5&" "&D5&", "&B5

Fíjese que hemos utilizado la función CONCATENAR -&- (ya explicada en artículos anteriores que puede localizar en las Etiquetas de Funciones y Herramientas de este blog) para unir los apellidos y el nombre. Además hemos dejado espacios entre estos y una coma antes del nombre. El problema de esta fórmula, como ya advertí, es que sólo nos valdrá para los registros con un único nombre.

2. Para los registros con dos nombres la fórmula a aplicar sería (nos situamos en F4):

=D4&" "&E4&", "&B4&" "&C4

Resueltos los dos casos posibles sólo nos queda detectar cuál debemos aplicar a cada caso. La forma es muy sencilla. En el rango E4:E9 sólo habrá contenido en los casos en los que tengamos dos nombres. Así las cosas sólo tenemos que comprobar si E4 se encuentra vacío o no para aplicar una u otra fórmula. Nos situamos en F4 y escribimos la siguiente fórmula (que luego copiaremos hasta F9):

=SI(E4="";C4&" "&D4&", "&B4;D4&" "&E4&", "&B4&" "&C4)

Si queremos actuar sobre este listado (por ejemplo ordenándolo alfabéticamente) es conveniente convertir en valores las fórmulas realizadas. Para ello seleccione el rango F4:F9 pulse el icono de Copiar y vaya al menú Edición/Pegado especial y seleccione la opción Valores.