"Trucos" con la Función SUMA

Este mes va de felicitaciones, pero es que hoy, 15 de Mayo, es un día tremendamente especial para mi porque es el cumpleaños de mi queridísima madre y, además, hoy han nacido mi segundo y tercer hijo: Enrique y Luca (ahora sí voy a necesitar milagros con la hoja de cálculo para el control de presupuestos...).

En un día tan señalado, me gustaría presentar en sociedad a la función SUMA. Digo presentar porque probablemente SUMA sea una de las funciones más conocida por cualquier usuario pero, como veremos, también una de las menos aprovechadas.

Comencemos poniendo un sencillo ejemplo. En una hoja tenemos los siguientes rangos y cifras,  que puede ver en la siguiente imagen: 

SUMA de un Rango Continuo: Si queremos sumar independientemente cada uno de estos rangos y colocar el resultado justo debajo simplemente seleccione el rango, por ejemplo B3:B9 y pulse el icono de Autosuma. Excel calculará el resultado automáticamente en la celda B10. La fórmula que aparecerá en dicha celda será =SUMA(B3:B9)

SUMA de Rangos Discontinuos: Supongamos que queremos sumar los tres rangos. Por desgracia, y créame que si lo digo es por algo, la solución que se suele proponer es la siguiente:

=SUMA(B3:B9)+SUMA(D3:D4)+SUMA(D9:D12)   

Digo por desgracia porque no es necesario realizar una fórmula tan larga e ineficiente. Pruebe a hacer lo siguiente:

1. Sitúese en la celda donde quiera realizar el cálculo y pulse el icono Autosuma. Seleccione el rango B3:B9. Hasta aquí nada nuevo. A continuación pulse la tecla Ctrl (Control) y manteniendo dicha tecla pulsada seleccione el siguiente rango, es decir, D3:D4. Nuevamente manteniendo la tecla Ctrl pulsada seleccione el último rango, es decir, D9:D12. Tras pulsar Enter la fórmula que obtendrá será:

=SUMA(B3:B9;D3:D4;D9:D12)

SUMA Acumulada: Supongamos ahora que deseamos realizar una suma acumulada en el Rango1. Es decir, queremos que en C3 aparezca el valor de B3; en C4 la suma de B3 más B4; en C5 la suma de B3 más B4 más B5; y así sucesivamente. La solución es muy sencilla. Nos situamos en la celda C3 y escribimos la siguiente fórmula:

=SUMA($B$3:B3)

Tras pulsar Enter copiamos C3 y pegamos en el rango C4:C9 (para profundizar en este tipo de fórmula véase el artículo "Cálculo de Acumulados con Referencias Absolutas")

SUMA de Rangos separados entre si por una Fila (o Columna): Para explicar esta funcionalidad o "truco" necesitaré un nuevo ejemplo:

Fíjese que tenemos tres grupos de números separados cada uno por una fila en blanco. Si lo que quiere es sumar cada grupo y colocar debajo el resultado sólo tiene que hacer lo siguiente: seleccione el rango B2:B4. Pulse la tecla Ctrl y, manteniéndola pulsada, seleccione ahora el rango B6:B7. Haga lo mismo con el rango B9:B13. El aspecto de su selección debería ser el siguiente:

Ahora pulse el icono Autosuma y Excel añadirá automáticamente el resultado en las filas en blanco (en B5, B8 y B14 respectivamente).

SUMA de Subtotales: Aprovechando lo que acabamos de hacer, suponga que ahora quiere calcular el sumatorio total de estas cifras. Para resolverlo seleccione directamente el rango B2:B14 y pulse el icono Autosuma. Excel sumará los Subtotales calculados y los colocará en la celda B15.

SUMA de Rangos separados entre si por más de una Fila (o Columna): Supongamos finalmente que tenemos nuestros grupos de cifras dispuestos como se presenta en la imagen, es decir, separados por varias filas (concretamente, en el primer caso por 2 y en el segundo por 5):

 

¿Podemos hacer lo mismo que si estuvieran separados por una única fila? Sí. La única matización necesaria es que lo que no podremos hacer posteriormente es el cálculo de la SUMA de Subtotales.

Importar Rangos Completos con INDIRECTO y Fórmula Matricial

Descargar el Archivo

Hoy, jueves 14 de mayo, comienzo este artículo felicitando a mi queridísimo amigo, y también uno de mis "mentores" -junto a mi también queridísimo amigo Julián de Cabo-, Enrique Dans que está de cumpleañitos. Sí, Enrique Dans el del Blog de Enrique Dans... Enriquiño mi más cariñosa felicitación.

Dicho lo cuál nos ponemos manos a la obra para dar respuesta a una pequeña consulta que me habéis realizado y que paso a describir. La empresa X maneja un pequeño cuadro de mando, de periodicidad mensual, donde se recogen distintos datos relativos a distintas sucursales de dicha empresa. En concreto el cuadro original contempla 56 sucursales y 18 medidores. En nuestro ejemplo trabajaremos con 12 sucursales y 4 medidores pero, como ya se imagina, la solución es exactamente la misma. El cuadro de mando, que se puede descargar en el vínculo del comienzo del artículo, es el siguiente:

Este cuadro se encuentra en una hoja denominada DATOS. Tenemos tres hojas más denominadas Enero, Febrero y Marzo con la información relativa a dichos meses:

Hoja denominada Enero:

Hoja denominada Febrero:

Hoja denominada Marzo:

Lo que queremos conseguir es que seleccionado un mes en de la lista desplegable que montaremos en la celda B1 de la hoja DATOS, automáticamente "me traiga" a esta hoja y dentro de esta tabla toda la información del mes solicitado. Los pasos a seguir son los siguientes:

1. Escribimos los nombres de los meses que queremos que aparezcan en nuestra lista desplegable. Dentro de la hoja DATOS nos situamos, por ejemplo, en la celda A21 y escribimos Enero; en A22 Febrero; y en A23 Marzo. Evidentemente, lo normal será que tenga 12 hojas con los 12 meses y que, en consecuencia, tenga que escribir la lista completa con los 12 meses. En nuestro ejemplo utilizaremos sólo 3.

2. Una vez introducidos los nombres de los 3 meses nos situamos en la celda B1. Abrimos el menú Datos/Validación y seleccionamos Permitir/Lista.

En el cuadro Origen escribimos =A21:A23 y,  finalmente, pulsamos Aceptar. Con esto ya tendremos nuestra lista desplegable con los meses en B1.

3. Vamos a la hoja denominada Enero, seleccionamos el rango B3:E14 y hacemos clic en el Cuadro de nombres (el que se encuentra a la izquierda de la barra de fórmulas y que puede ver en la siguiente imagen). Escribimos el nombre enero y pulsamos Enter.

4. Repetimos el paso 3 con las hojas denominadas Febrero y Marzo (seleccionando sus correspondientes tablas y dándoles el correspondiente nombre del mes).

5. Nos situamos en la hoja DATOS y seleccionamos el rango B5:E16 y escribimos la siguiente y única fórmula:

=INDIRECTO(B1) pero NO PULSAMOS ENTER. Pulsamos la combinación de teclasCtrl+Shift+Enter  para que, como ya hemos visto en diversos artículos, Excel lo trate como una entrada matricial. La fórmula resultante será:

{=INDIRECTO(B1)}

Fíjese que en B1 tendremos el nombre del mes que hemos asociado a su correspondiente tabla. Con la función INDIRECTO conseguimos que el nombre del mes sea una referencia válida para Excel. Pero como dicho nombre hace referencia a una tabla (o matriz) necesitamos concluir nuestra fórmula convirtiéndola en una entrada matricial. De esta sencilla manera habrá conseguido "importar" toda la tabla correspondiente al mes seleccionado en la celda B1 de una sola "atacada".

Crear Escenarios y Resúmenes Automáticos

Descargar el Archivo

Continuamos con el ejemplo de la cuenta de resultados para aplicar otra herramienta que, en mi opinión, es tan útil como desconocida: el Administrador de Escenarios. Una vez desarrollada la cuenta de resultados previsional  es muy típico el tener que plantear diversas situaciones de negocio para analizar cuál sería el resultado obtenido. Así las cosas, y como suelo decirles a mis alumnos, llega el momento de empezar a "plantar setas" en nuestro libro de Excel. El usuario acostumbrado a hacer lo que buenamente puede suele resolver esta tarea generando tantas hojas (setas) como escenarios quiere plantear; copiando y pegando el modelo original en dichas hojas; modificando en cada nueva hoja los datos que quiere analizar; y, finalmente, creando una última hoja, que suele denominar Resumen o Total, y en donde "transfiere" (copia-pega) los resultados totales para realizar el resumen de los distintos escenarios planteados... Si se identifica con todo o con parte de lo expuesto le invito a que lea con detenimiento lo que sigue...

Recordemos nuestro sencillo modelo de partida (puede trabajar directamente con el archivo de Excel descargándoselo desde el vínculo que se encuentra al comienzo de este artículo):

Supongamos que queremos analizar cuál sería el beneficio bruto en los siguientes escenarios:

Escenario A: Precio de Matrícula 1.000€; Nº de Alumnos 20; Coste Hora Lectiva 350€; Nº de horas 16; Nº de días 2; Resto de datos constantes.

Escenario B: Precio de Matrícula 1.100€; Nº de Alumnos 20; Coste Hora Lectiva 400€; Nº de horas 32; Nº de días 4; Resto de datos constantes.

Escenario C: Precio de Matrícula 1.200€; Nº de Alumnos 20; Coste Hora Lectiva 400€; Nº de horas 24; Nº de días 3; Coste de Aula/día 200€; Gastos fijos 450€ Resto de datos constantes.

Los pasos que debe seguir son los siguientes:

1. Seleccionamos el rango A3:B4 y vamos al menú Insertar/Nombre/Crear. En la ventana que se abre aceptamos la opción que aparece por defecto (Nombres en columna izquierda).

2. Seleccionamos el rango A6:B12 y vamos al menú Insertar/Nombre/Crear. En la ventana que se abre aceptamos la opción que aparece por defecto (Nombres en columna izquierda).

3. Vamos al menú Herramientas/Escenarios. Se abrirá la siguiente ventana:

4. Pulsamos el botón Agregar. Se abrirá la siguiente ventana:

5. Lo primero que debemos hacer siempre cuando vayamos a generar varios escenarios (o al menos eso le recomiendo) es comenzar "grabando" el escenario del que partimos. Para ello en el cuadro Nombre del escenario escribimos, por ejemplo, Inicial (en este cuadro es donde escribiremos los nombres que queramos dar a los distintos escenarios que generemos). En el cuadro Celdas cambiantes introducimos B3:B4;B6:B12 (puede introducirlo escribiéndolo o seleccionando el primer rango -B3:B4- y pulsando después la tecla control a la vez que selecciona el segundo rango -B6:B12-). El cuadro Celdas cambiantes es donde le indicamos qué celdas serán susceptibles de ser modificadas para generar el escenario que estamos agregando. Finalmente pulsamos Aceptar y se abrirá la siguiente ventana:

Fíjese que aparecen los nombres de las entradas de datos gracias a haber realizado los pasos 1 y 2 (de no realizarlos aparecerían las referencias de las celdas donde se encuentran dichos datos).

6. Como se trata de los datos de partida no modificamos ninguno y directamente pulsamos Agregar.

7. Al pulsar Agregar nos vuelve a aparecer la siguiente ventana:

8. Introducimos el nombre del nuevo escenario, a saber, Escenario A y en Celdas cambiantes mantenemos las que aparecen por defecto (las mismas que utilizamos en el escenario inicial -B3:B4;B6:B12-). En Comentarios puede escribir el texto que desee (yo lo he utilizado para indicar los valores que voy a considerar en este escenario):

9. Pulsamos Aceptar y se abrirá nuevamente la ventana Valores del escenario. Es aquí donde debemos modificar los valores que queremos que tome este escenario (Escenario A: Precio de Matrícula 1.000€; Nº de Alumnos 20; Coste Hora Lectiva 350€; Nº de horas 16; Nº de días 2; Resto de datos constantes).

10. Una vez introducidos estos valores en los campos correspondientes pulsaremos Agregar y procederemos de la misma forma con el Escenario B y con el Escenario C.

11. Al concluir el último escenario (el C) pulsaremos Aceptar en vez de Agregar (ya que no vamos a agregar más escenarios). La ventana que se nos abre será la siguiente:

Con el trabajo realizado hasta aquí ya puede comprobar que seleccionando cualquier escenario de los que aparecen en la lista y pulsando posteriormente la opción Mostrar la entrada de datos cambiará automáticamente y tomará los valores "grabados" en el escenario en cuestión (para ver cada escenario también puede simplemente hacer doble clic encima de cualquiera de la lista). Lógicamente al cambiar la entrada cambiará también la salida de datos. Pero esto no es todo... Veamos ahora la opción Resumen. 

12. Pulsamos el botón Resumen y se abrirá la siguiente ventana:

13. Dejamos seleccionada la opción Resumen y en Celdas de resultado seleccionamos la celda B24, ya que lo que queremos es un resumen de los distintos escenarios creados y cómo afectan al beneficio bruto, que se encuentra en la celda B24. Al pulsar Aceptar Excel generará automáticamente una hoja nueva, denominada Resumen de Escenario, con toda la información requerida:

Es importante destacar que estos informes son estáticos, es decir, si una vez generado el resumen modifica cualquier valor en la entrada de datos o en los escenarios, este informe no variará. En tal caso deberá pedirle que cree un nuevo resumen para recoger las modificaciones realizadas.

Formato Condicional en el Análisis de Sensibilidad

Descargar el Archivo

En el anterior artículo desarrollamos un análisis de sensibilidad para estudiar el impacto de dos variables sobre el beneficio bruto de un proyecto. Para enriquecer dicho análisis vamos a incorporar al resultado un mapa de colores en función de los objetivos que deseamos destacar. En concreto queremos que destaque en color naranja aquellas combinaciones que no sean rentables para la empresa; en azul las que proporcionen un beneficio bruto entre 6.000 y 12.000€; y en amarillo las que superen los 12.000€.

1. Nos situamos en la celda E26 y E27 y escribimos los rótulos de nuestros objetivos y en F26 y F27 los valores de los mismos (6.000€ y 12.000€)

2. Seleccionamos el rango F4:I24 y vamos al menú Formato/Formato condicional.

3. En el primer cuadro dejamos valor de la celda y a la derecha seleccionamos menor que y escribimos cero. Pulsamos el botón Formato y seleccionamos Trama naranja. Aceptamos y pulsamos Agregar.

4. En la segunda condición dejamos también en el primer cuadro valor de la celda y a la derecha seleccionamos entre y en los dos recuadros que se abren escribimos F26 y F27 respectivamente. Pulsamos el botón Formato y seleccionamos Trama azul. Aceptamos y pulsamos Agregar.

5. En la tercera condición dejamos en el primer cuadro valor de la celda y a la derecha seleccionamos mayor que. En el recuadro de la derecha escribimos F27. Pulsamos el botón Formato y seleccionamos Trama amarilla. Aceptamos y volvemos a pulsar Aceptar.

De esta manera habremos construido, por medio de la herramienta de Formato condicional, un "mapa de colores" con nuestros objetivos. Fíjese que si cambia cualquier dato en la entrada de datos la tabla se recalcula automáticamente y, por supuesto, también el mapa de colores:

Análisis de Sensibilidad con dos Variables

Descargar el Archivo

La empresa Educando dedica su actividad a realizar cursos de formación para directivos. El Director de Marketing necesita realizar una cuenta de resultados previsional para un nuevo tipo de cursos cortos que desean desarrollar. Los datos de partida son los que se muestran en la imagen, y que se corresponden con la entrada de datos del modelo:

Quiere desarrollar una cuenta de resultados previsional con el siguiente detalle para analizar el margen y beneficio bruto que se podrían alcanzar:

La formulación de este modelo no es ningún problema ya que se trata de fórmulas muy sencillas que detallo en la siguiente imagen:

Y el resultado de aplicar dichas fórmulas será:

Hasta aquí ningún problema. Una vez desarrollada la cuenta de resultados inicial queremos realizar un análisis de sensibilidad para ver, por ejemplo, cómo afecta al Beneficio Bruto distintos números de alumnos y distintos precios de matrícula. Evidentemente podríamos resolver el análisis por la "vía dura", a saber, introduciendo distintos valores en la entrada de datos y anotando los resultados... ¡Por el amor de Dios no haga esto! (y si lo hacía por favor no lo haga más...).

Excel nos proporciona una herramienta denominada Tabla que resolverá por nosotros esta tediosa labor. Los pasos que debemos seguir son los siguientes:

1. Construimos la tabla con los valores que queremos analizar, como se muestra en la imagen:

Fíjese que hemos dispuesto en una columna (E) distintos números de alumnos (desde 10 hasta 30) y en una fila (3) distintos precios de matrícula (800€, 1.000€, 1.200€ y 1.400€). 

2. Nos situamos en la celda del vértice superior izquierdo de la tabla, es decir, en E3 y escribimos la fórmula: =B24

Esta celda (el vértice superior izquierdo de la tabla que generemos) se denomina celda de enlace y la dedicaremos siempre a indicar qué fórmula deseamos analizar. Es decir, como vamos a ver cómo afecta al BENEFICIO BRUTO distintos números de alumnos y precios de matrículas en esta celda escribiremos =B24 porque es dónde se encuentra (en nuestro modelo) la fórmula que queremos calcular (Beneficio Bruto).

3. Seleccionamos toda la tabla incluyendo la celda de enlace, es decir, E3:I24 (los rótulos no) y vamos al menú Datos/Tabla... Se nos abrirá la siguiente ventana:

4. En Celda de entrada (fila) debemos hacer mención a los datos que hemos dispuesto en nuestra tabla en una fila, es decir, el precio de la matrícula. En la entrada de datos de nuestro modelo el precio de la matrícula lo tenemos en B3. En Celda de entrada (fila) seleccionamos B3.

5. En Celda de entrada (columna) debemos hacer mención a los datos que hemos dispuesto en nuestra tabla en una columna, esto es, el número de alumnos. Tal dato lo tenemos en B4 por lo que en Celda de entrada (columna) escribimos B4:

6. Sólo nos queda pulsar Aceptar y Excel se encargará de rellenar la tabla con todas las combinaciones requeridas:

Esta herramienta nos permite además modificar los datos que queremos analizar. Pruebe a cambiar algunos (o todos) los números de alumnos y/o los precios de matrícula en la tabla y verá que automáticamente se recalcula el beneficio bruto resultante.

En próximas "entregas" verá que a este sencillo modelo le podemos sacar todavía muchísimo más partido...