jueves, 23 de julio de 2009

Tipificar Errores en la Introducción de Datos


"En nuestra empresa utilizamos un modelo sencillo desarrollado en Excel para realizar presupuestos para los clientes en base a los datos que éstos nos proporcionan. Uno de estos datos es la cantidad de unidades que solicitan. Es muy habitual que las personas que preparan el presupuesto cometan algún error a la hora de introducir el dato correspondiente a la cantidad ¿Hay alguna manera de que excel nos avise de que estamos cometiendo un error y de qué error se trata?"

La respuesta a esta pregunta es, como casi siempre, que sí. Precisamente este tema es tratado en los cursos que imparto en el Instituto de Empresa y al que doy mucha importancia porque nos puede ahorrar muchos disgustos... Veamos un ejemplo y diversas soluciones para este problema.

Supongamos que tenemos la siguiente entrada de datos:

Queremos que en la celda D4 nos aparezca un texto avisándonos del error que estamos cometiendo o nada en el caso de que introduzcamos correctamente el dato. Lo primero que hacemos es tipificar los posibles errores, a saber:
a) No introducir la cantidad.
b) Introducir un texto.
c) Introducir decimales (en el caso de que las unidades hayan de ser enteras).
d) Introducir un número negativo o inferior al pedido mínimo.
e) Combinación de c y d

Para resolver este problema tendremos que utilizar condicionales anidados. Vamos a comenzar resolviendo los apartados a, b, c y d. Para ello nos situamos en la celda D4 y escribimos la siguiente fórmula (que "troceo" en varias lineas debido a su longitud):

La primera linea comprueba si C4 está en blanco. En tal caso le indicamos que escriba el texto "Introduzca la Cantidad". Si C4 no está en blanco entonces pasa a leer la siguiente linea de la fórmula.

La siguiente linea utiliza la función ESTEXTO. Esta función se encuentra dentro de la categoría Información. ESTEXTO comprueba si el valor de una celda es un texto, en cuyo caso devuelve el resultado VERDADERO, o FALSO en el caso contrario (en el caso en que el valor de la celda indicada no sea un texto).

La tercera linea comprueba si hay decimales o no. Para ello utilizamos la función ENTERO, que nos proporciona la parte entera de un número. Si la parte entera del número introducido en C4 es igual al número introducido en C4 es porque no hay decimales.

La cuarta y última linea comprueba si la cantidad introducida en C4 es inferior al pedido mínimo (C2). Si no cometemos ninguno de estos errores entonces cabe suponer que hemos introducido correctamente el dato y por ello le indicamos que no haga nada (""). Una vez realizada la fórmula es bueno -imprescindible- comprobar que ésta funciona correctamente:





Dado que excel lee los condicionales de izquierda a derecha y cuando se cumple alguna de las cuestiones comprobadas detiene la lectura y ejecuta la orden establecida (si por ejemplo introduce un número con decimales excel llegará hasta el tercer condicional y devolverá el texto "ERROR. Ha introducido decimales" y no seguirá leyendo el resto de la fórmula) si queremos comprobar si una entrada comete dos errores a la vez (apartado e) deberemos utilizar la función Y en el sitio adecuado. En nuestro ejemplo será en el tercer condicional. La fórmula definitiva quedaría como sigue:


Y la comprobación correspondiente:


sábado, 11 de julio de 2009

Escribir/Formular en Varias Hojas a la Vez



"Tenemos doce sucursales y cada una me remite el reporte en una cuenta de resultados estándar. Para realizar el cálculo de diversos ratios ¿es necesario que haga las fórmulas en una hoja y luego las copie y pegue en todas y cada una de las hojas correspondientes a las sucursales?"

La respuesta es que se puede hacer de una forma muy sencilla y de una sola atacada. Para simplificar veámoslo con 3 sucursales:




Los ratios que queremos calcular son los que se muestran en la columna D, y queremos hallarlos en la columna C. Para ello hacemos lo siguiente:
1. Seleccionamos la pestaña referente a la primera sucursal: Sucursal Norte.
2. Pulsamos la tecla Shift (Mayusc.) y manteniéndola pulsada hacemos clic en la última sucursal, en nuestro caso Sucursal Este.
3. A partir de este momento todo lo que hagamos en la primera hoja (en la Sucursal Norte) lo estaremos haciendo igualmente en el resto de hojas seleccionadas. Procedemos a realizar las fórmulas cuyo detalle se puede ver en la siguiente imagen:

4. Una vez terminado debemos desactivar la selección, para lo que podemos hacer clic en la hoja Total o pulsar nuevamente Shift y hacer clic en la hoja a la que queramos ir.

Puede comprobar ahora que en todas las hojas marcadas se han incluido las fórmulas planteadas. Lo mismo ocurriría si cambia algún formato o escribe algún texto.

Como ejercicio le propongo que intente calcular los ratios totales de las tres sucursales utilizando este método y la suma tridimensional vista en el artículo "Cálculos con Rangos Tridimensionales".

Cambiar Formato (Fecha) Americano a Español



"Todas las semanas me llega un informe con diversos datos y en la primera columna me encuentro con fechas en formato americano y, además, con formato de texto. Me gustaría poder convertirlas a formato español y poder operar con ellas".

Vamos a solucionar este problema con las funciones FECHA, IZQUIERDA, DERECHA y EXTRAE. Supongamos que tenemos la siguiente tabla con fechas, que además nos llegan con formato texto, en formato americano (mes,día,año) y queremos convertirlas en formato español y numérico (día,mes,año):

Vamos a "trocear" el problema para que se entienda mejor la solución:
1. Nos situamos en C3 y escribimos la fórmula:
=EXTRAE(B3;4;2)
La función EXTRAE nos permite extraer parte de un texto desde una posición inicial que le debemos indicar. En nuestro caso le estamos pidiendo que del texto que hay en B3 y desde la posición inicial 4 (4 caracteres) extraiga 2 caracteres. De esta manera obtendremos los dos caracteres referente al día.
2. Nos situamos en la celda D3 y escribimos la siguiente fórmula:
=IZQUIERDA(B3;2)
Esta función extrae dos caracteres de la celda B3 empezando por la izquierda.
3. Nos situamos en E3 y escribimos la fórmula:
=DERECHA(B3;4)
Esta función extrae cuatro caracteres de la celda B3 empezando por la derecha.

Ya hemos conseguido separar los caracteres referentes al día, mes y año:


Sólo nos queda "reunirlos" con formato de fecha válido para poder operar después con ellas. Esta labor la realiza la función FECHA:
4. Nos situamos en la celda F3 y escribimos:
=FECHA(E3;D3;C3)

Seleccionando ahora el rango C3:F3 y haciendo doble clic en la parte inferior derecha de la selección terminaríamos nuestro trabajo:

Podemos resumir todos estos pasos en una sola fórmula, a saber:
=FECHA(DERECHA(B3;4);IZQUIERDA(B3;2);EXTRAE(B3;4;2))


lunes, 6 de julio de 2009

Herramienta Subtotales



Hoy voy a explicar el uso de la Herramienta Subtotales, que puede encontrar en el menú Datos/Subtotales, y que le recomiendo comience a utilizar lo antes posible (si es que no la utiliza ya).
Supongamos que tenemos un listado con los nombres y apellidos de los empleados de una empresa; el departamento al que pertenecen; su sueldo bruto; etcétera:


Queremos saber cuál es el coste total de cada Departamento; cuál el promedio; y cuánto es el máximo y el mínimo que se gana en cada uno. Con la herramienta Subtotales no debería de llevarnos más de 30 segundos...

1. Vamos a clasificar la información a partir del campo Departamento. Lo primero que debemos hacer es ordenar este campo (de mayor a menor o de menor a mayor, da igual). Para ello nos situamos en D2 y pulsamos el icono Orden ascendente. El resultado será el siguiente:


2. Vamos al menú Datos/Subtotales.
3. Se abre una ventana en donde debemos indicar qué resultados queremos obtener y su clasificación:

Para cada cambio en: aquí seleccionamos el campo por el que queremos clasificar la información. En nuestro caso Departamento.
Usar función: Aquí debemos indicar la función que queremos aplicar. En nuestro caso empezaremos por la SUMA para hallar el coste total de cada departamento.
Agregar subtotal a: Aquí indicamos el campo al que aplicaremos la función. En nuestro caso será el bruto anual, ya que queremos que sume los distintos sueldos brutos de cada departamento.

4. Desactivamos la casilla de verificación Reemplazar subtotales actuales. Esto lo hacemos para que cuando añadamos nuevos cálculos de subtotales no borre los anteriores. Pulsamos Aceptar. Obtendremos el resultado mostrado en la siguiente imagen:

Como puede comprobar excel habilita un esquema con la información solicitada. En la parte superior izquierda de la hoja encontrará los 3 niveles de esquema disponibles. El 1 corresponde al total general (mínimo desglose). El 2 corresponde al total general y a los totales parciales (por departamento). Y el 3 corresponde a toda la información y sus correspondientes subtotales (máximo desglose):

Si pulsa, por ejemplo, el nivel 2 la información se mostrará como sigue:

Fíjese que a la izquierda de la imagen tiene símbolos + que puede utilizar para personalizar el nivel de detalle.

5. Para obtener el resto de la información (promedio, máximo, mínimo) Sólo tiene que volver a entrar en Datos/Subtotales y sustituir Usar función SUMA por Usar función PROMEDIO. Lo mismo deberá hacer con la función MAX y MIN. Una vez hecho esto la información que le aparecerá en pantalla será la siguiente:


Para borrar todos los subtotales sólo tiene que entrar nuevamente en Datos/Subtotales y pulsar el botón Quitar todos.

domingo, 5 de julio de 2009

Análisis Sensibilidad Multifórmula



En este nuevo post vamos a ver cómo podemos llevar a cabo un análisis de sensibilidad de una variable aplicado a varias fórmulas a la vez (multifórmula). Para ello voy a recuperar el ejemplo que utilicé en el artículo "Análisis de Sensibilidad con Dos variables" y que reproduzco a continuación:

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á:


A continuación queremos calcular cómo afectaría, por ejemplo, el precio de la matrícula a los ingresos, al margen bruto y al beneficio bruto. Lo haremos de una sola "atacada"...
1. Generamos el rango de precios que deseamos considerar:


Fíjese que hemos añadido en la tabla los campos relativos a los ingresos (columna E), al margen bruto (columna F) y al beneficio bruto (columna G) y que hemos dejado una fila en blanco en el rango D3:G3
2. Nos situamos en E3 y le indicamos dónde se encuentra la fórmula que queremos calcular. La fórmula que queremos calcular es la de los ingresos y se encuentra en B16. Por lo tanto en E3 escribimos =B16.
3. Hacemos lo mismo en F3 y G3. En F3 queremos calcular el margen bruto y éste se encuentra en B22, En G3 calcularemos el beneficio bruto que se encuentra en B24:


4. Seleccionamos el rango D3:G20 y vamos al menú Datos/Tabla. En la ventana que se abre escribimos lo siguiente y pulsamos Aceptar:

La tabla se rellenará automáticamente calculando para los precios planteados los valores correspondientes a las tres fórmulas indicadas:


jueves, 2 de julio de 2009

Crear Rangos con Datos en Distintas Hojas



¿Se puede dar nombre a un rango de datos que se encuentran en distintas hojas? Yes, we can. Supongamos que tenemos las ventas mensuales de distintas zonas (Zona A, B y C) en varias hojas:

Zona B
Zona C
Podemos dar nombre a un rango cuyos datos se encuentren en distintas hojas de la siguiente manera:
1. Vamos al menú Insertar/Nombre/Definir.
2. En el recuadro Nombres en el libro escribimos el nombre que le queramos dar al rango, por ejemplo Ventas_Consolidadas (fíjese que ambas palabras están separadas por un guión bajo y no por un espacio debido a que excel no permite insertar espacios al crear nombres).
3. Hacemos clic en el recuadro Se refiere a y a continuación pulsamos la pestaña de la primera hoja (ZonaA). Una vez hecho esto pulsamos la tecla Shift y con ella pulsada hacemos clic encima de la última hoja de nuestro rango (ZonaC). Seleccionamos el rango B3:B14 en la hoja en la que nos encontremos y pulsamos Aceptar.

A partir de este momento ya habremos creado un rango tridimensional que nos permitirá realizar cálculos directos. Para comprobarlo en la primera hoja, denominada Resumen, vamos a calcular las ventas totales y el promedio de las tres zonas:

Como puede comprobar en la imagen sólo necesitamos situarnos en B2 y escribir la fórmula:
=SUMA(Ventas_Consolidadas)
y para el promedio en B3
=PROMEDIO(Ventas_Consolidadas)