domingo, 31 de mayo de 2009

Generar un Calendario Automático de Pagos



Hoy vamos a ver cómo podemos generar un calendario automático que nos indique, por ejemplo, cuándo debemos cobrar una venta a plazos. Partiremos de una fecha inicial, que será la fecha pactada con el cliente, además del número de años al que se va a realizar la operación y el número de pagos al año que el cliente debe atender. Con estos tres datos generaremos un calendario automático con las correspondientes fechas de pago.
Para desarrollar este modelo utilizaremos, entre otras, varias funciones de la categoría denominada Fecha y Hora. A saber: FECHA, FIN.MES, AÑO, MES, DIA. Nuestra entrada de datos será la siguiente:

Lo que queremos conseguir es que el calendario que se muestra en la siguiente imagen se rellene automáticamente con el número de pago (o cobro) a realizar y la fecha de vencimiento, en base a los datos introducidos en B2:B4. Más concretamente queremos que si la fecha de inicio coincide con el último día del mes los siguientes vencimientos también lo hagan. Por otro lado, si la fecha es cualquier otro día del mes queremos que mantenga dicho día para el resto de la operación.

Para resolver este "problema", sin hacer la demasiado habitual chapucilla (y por favor que no se me moleste nadie) de sumar 30 días al mes anterior o soluciones similares, es necesario manejar algunas funciones de la categoría Fecha y Hora que paso a describir:

FIN.MES(Fecha_inicial;Meses)   Devuelve la fecha (como número de serie) del último día del mes anterior o posterior, respecto al argumento Fecha_Inicial, en función de que el argumento Meses sea positivo o negativo.
AÑO(num_de_serie)   Devuelve el año correspondiente a una fecha.
MES(num_de_serie)   Devuelve el mes de una fecha.
DIA(num_de_serie)     Devuelve el día de una fecha.
FECHA(Año;Mes;Dia)  Tal y como indica la ayuda de Excel, devuelve el número de serie secuencial que representa una fecha determinada. Si el formato de celda era General antes de escribir la función, el resultado tendrá formato de fecha. Dentro de los argumentos de esta función es importante destacar dos cuestiones que nos indica la ayuda de Excel: Mes es un número que representa el mes del año. Si el mes es superior a 12, el mes agrega ese número de meses al primer mes del año especificado. Por ejemplo, FECHA(2008;14;2) devuelve el número de serie que representa la fecha 2 de febrero de 2009. Día es un número que representa el día del mes. Si el día es superior al número de días del mes especificado, día agrega ese número de días al primer día del mes. Por ejemplo, FECHA(2008;1;35) devuelve el número de serie que representa la fecha 4 de febrero de 2008.

Empezamos por generar el número de Pago:
1. Nos situamos en la celda A9 (en la celda A8 hemos dejado como dato fijo un uno porque como mínimo existirá un pago) y escribimos la siguiente fórmula:
=SI(A8<$B$3*$B$4;A8+1;"")

Fíjese que lo que estamos comprobando en A9 es si A8 (la celda inmediata superior) es menor que $B$*$B$4, es decir, que el número total de pagos a realizar. Si es menor le pedimos que sume uno. Si no es menor entonces es que la celda inmediata superior (en este caso A8) ya es igual que el número total de cuotas, en cuyo caso le ordenamos que no escriba nada en la celda (el doble par de comillas).

2. Copie la fórmula de A9 hasta la celda, por ejemplo, A43. La celda hasta la que debe copiar dicha fórmula dependerá de las opciones que usted maneje. En nuestro ejemplo he supuesto que el plazo máximo en años es de tres y el número máximo de pagos al año es de doce (3*12=36, que son las celdas en las que hemos copiado dicha fórmula). Con esta sencilla solución habremos conseguido que el número de pagos aparezca automáticamente en función de los datos que contemplemos en nuestra entrada de datos.

3. Nos situamos en la celda B8 y escribimos la fórmula =B2  para recuperar la fecha inicial de nuestra entrada de datos.

4. En la celda B9 escriba la siguiente fórmula:
=SI(A9="";"";SI(B8=FIN.MES(B8;0);FIN.MES(B8;12/$B$4);
FECHA(AÑO(B8);MES(B8)+12/$B$4;DIA(B8))))

Con el primer condicional comprobamos si en la celda de la izquierda, respecto a la que contiene esta fórmula) hay algo o no. En caso de que no haya nada (doble par de comillas) que no escriba nada. En caso contrario, es decir, que sí haya algo (un número de pago), comprobamos si la fecha inicial (que ya hemos trasladado a B8) es una fecha de fin de mes (31 de enero, 28 ó 29 de febrero, 31 de marzo, etcétera) o no. Si lo es, entonces le pedimos que genere la siguiente fecha sumándole a la fecha inicial el cociente entre 12/$B$4 ¿Qué significa esto? Significa que estamos calculando el número de meses de un año dividido por el número de pagos al año. Por ejemplo,  para pagos trimestrales (4 al año) la celda B4 contendrá un 4, con lo que el cálculo resultante será: 12/4=3  que es el número de meses que le sumará cada vez a la fecha inicial.

Finalmente (si la celda de la izquierda no está en blanco ni es una de las fechas de fin de mes del año) entonces le pedimos que  para componer la FECHA correcta utilice el AÑO de la celda anterior (inmediata superior); el MES de la celda anterior más 12/$B$4; y el DIA de la celda anterior. 

5. Copiamos esta fórmula y la pegamos hasta B43.

De esta manera, y como puede apreciar en las siguientes figuras, tanto el número de pago como el calendario de pagos aparecerán automáticamente en función de los datos que introduzcamos en nuestra entrada de datos:



Sugerencias:
* Como práctica puede intentar introducir Validación de datos en las entradas (rango B2:B4).
* También puede utilizar el artículo anterior si desea sombrear las filas pares.

Formato Condicional con Botones de Opción



Hoy vamos a ver como podemos resaltar, con la herramienta Formato Condicional vinculada aBotones de Opcióndiferentes registros dentro de una tabla

Tenemos una tabla con la siguiente información: Nombre del cliente; importe que se le ha facturado; la cantidad que ha pagado; y, finalmente, la cantidad que adeuda (Pendiente):

Queremos tener la opción de controlar visualmente los clientes que nos adeudan hasta un 50% del importe facturado y, por otro lado, los clientes que nos adeudan un 50% o más de dicho importe. Para conseguir este objetivo debemos seguir los siguientes pasos (doy por hecho que no es necesario explicar la fórmula del rango D9:D23):
1. En la celda C2 escribimos el rótulo Opción (más tarde utilizaremos la celda D2 para vincular los botones de opción). Utilizamos estas celdas para visualizar fácilmente el ejemplo pero este tipo de entrada se suele ocultar.
2. En la celda C3 escribimos el rótulo Límite e introducimos, en la celda D3, el valor 50%.
3. Abrimos el menú Ver/Barra de herramientas/Formularios.
4. Hacemos un clic encima del botón de opción (que es el que puede ver en la imagen):
5. Volvemos a la hoja y hacemos un clic y sin soltar el clic arrastramos el ratón hacia abajo y hacia la derecha. Al soltar el clic habrá creado el primer botón de opción.
6. Puede repetir el paso 5 dos veces más o copiar el botón de opción creado y pegarlo dos veces. De esta manera tendremos los tres botones de opción que vamos a necesitar.
7. Cambie los textos de los botones para dejarlos como se muestra en la imagen:


8. Haga clic con el botón derecho del ratón sobre el primer botón de opción denominado Ninguna, y seleccione la opción Formato de control. Dentro de la pestaña Control marque el Valor Sin activar y en el cuadro Vincular con la celda haga clic sobre D2 (le aparecerá como =$D$2). Pulse Aceptar. Dado que los botones de opción son excluyentes entre si no tendrá que repetir este paso para el resto de botones de opción que, automáticamente, quedarán vinculados también con la celda D2.
9. Seleccione el rango A9:D23 y vaya al menú Formato/Formato condicional.
10. En el primer cuadro seleccione Fórmula y en el cuadro de la derecha escriba lo siguiente:
=SI($D$2=1;1;0)
11. No seleccione ningún formato para esta condición. Con el uso de este condicional estamos verificando si la celda D2, que es la que hemos vinculado a los botones de opción, contiene el número 1. Si es así significará que hemos marcado el botón de opción Ninguna, y por lo tanto al pulsarlo no se resalta ninguna operación.
12. Dentro de la herramienta de Formato Condicional pulse el botón Agregar. Seleccione Fórmula en el primer cuadro y escriba lo siguiente:
=SI(Y($D$2=2;$D9/$B9<$D$3;$D9/$B9>0);1;0)
Fíjese que hemos utilizado, además de la función SI, la función Y. Como ya sabrá, esta función nos permite realizar varias pruebas a la vez, y deben cumplirse todas para que el resultado sea VERDADERO. En nuestro caso tenemos que comprobar tres cosas que deben cumplirse, a saber: a) si hemos seleccionado el botón de opción 2; b y c) si el porcentaje de pendiente sobre facturado es mayor que cero y menor que el 50%.
13. Pulse el botón Formato y seleccione Trama, por ejemplo, amarilla y pulse Aceptar.
14. Dentro de la herramienta de Formato Condicional pulse el botón Agregar. Seleccione Fórmula en el primer cuadro y escriba lo siguiente:
=SI(Y($D$2=3;$D9/$B9>=$D$3);1;0)
De esta manera estamos comprobando si el botón de opción pulsado es el tercero y si el ratio entre pendiente y facturado supera o no el 50%.
15. Pulse el botón Formato y seleccione Trama, por ejemplo, roja y pulse Aceptar.

Si ha seguido correctamente estos pasos la herramienta Formato Condicional mostrará el siguiente aspecto:

16. Pulse Aceptar.

Ahora ya puede probar esta solución:
a) Al pulsar el botón de opción Ninguna, la tabla se queda con el formato inicial.
b) Al pulsar el botón de opción Hasta el 50%, los registros de la tabla que presenten un ratio entre el campo Pendiente y el campo Facturado mayor que cero pero menor que el 50% se destacaran en amarillo.

c) Al pulsar el botón de opción 50% o más, los registros de la tabla que presenten un ratio entre el campo Pendiente y el campo Facturado mayor o igual que el 50% se destacaran en rojo.

sábado, 30 de mayo de 2009

Sombrear Filas y/o Columnas de una Tabla

Respondiendo a una pregunta que me habéis realizado, vamos a ver hoy como podemos "sombrear" las filas y/o columnas pares de una tabla para diferenciarlas visualmente de las impares. La solución es muy sencilla utilizando la herramienta Formato Condicional y las funciones SIRESIDUO, FILA, y COLUMNA.

Supongamos que tenemos la siguiente tabla, donde podemos encontrar el número de alumnos clasificados por ciudad y por trimestre:


Queremos que las filas pares de la tabla aparezcan sombreadas y que las impares se mantengan en el formato original. La solución de "andar por casa" será seleccionar manualmente las filas pares de la tabla y "colorearlas" como se desee. Esta solución será la más eficiente si tenemos una tabla pequeña con pocas filas. Pero qué ocurre si nuestra tabla tiene, por ejemplo, 200 filas...
para solucionar este planteamiento tendremos que seguir los siguientes pasos:
1. Seleccionamos el rango C5:F15
2. Vamos al menú Formato/Formato condicional.
3. En el primer cuadro seleccionamos Fórmula (en vez de valor de la celda).
4. En el siguiente cuadro escribimos la fórmula:
=SI(RESIDUO(FILA(C5);2);0;1)

La función Fila nos devuelve el número de fila de la celda que le indiquemos
La función Residuo tiene dos argumentos, a saber, número y número divisor. Esta función nos devuelve el resto entre número y número divisor. En nuestro caso el argumento número será el número de fila. El argumento número divisor será 2. De esta manera es muy fácil saber qué filas son pares y cuáles impares. Como ya se habrá dado cuenta, al utilizar como divisor el número 2, el resto será siempre cero cuando estemos en una fila par y 1 cuando estemos en una fila impar.
Anidando estas funciones dentro de un condicional (SI) el problema está resuelto:


Si lo que queremos conseguir es sombrear las columnas pares en vez de las filas, entonces la fórmula a aplicar sería:
=SI(RESIDUO(COLUMNA(C5);2);0;1)



sábado, 23 de mayo de 2009

Modificando Datos desde los Gráficos



Aviso para navegantes: el contenido de este artículo es aplicable sólo en versiones anteriores a Excel 2007. Seguro que Microsoft tendrá una explicación pintoresca para explicar por qué han omitido está funcionalidad...

Cualquier usuario "respetable" de Excel sabe que cuando generamos un gráfico y posteriormente modificamos los datos de los que depende dicho gráfico éste se actualiza automáticamente. Pero pocos, muy pocos, usuarios saben que esta misma labor la podemos realizar directamente desde los gráficos. Dicho de otra forma: al igual que podemos modificar los datos de entrada de un modelo para ver qué resultado obtenemos, podemos realizar la misma tarea directamente desde los gráficos sin necesidad de modificar celda alguna. Supongamos que tenemos la siguiente cuenta de resultados provisional con el desarrollo de fórmulas que se muestra en la segunda figura (las celdas en naranja son las fórmulas que hay que crear y que se pueden copiar):



Realizamos ahora un gráfico de columnas en 2D del, por ejemplo, beneficio bruto:
Cursiva
El gráfico creado es un gráfico de "salida de datos", ya que el origen de sus datos son fórmulas que calculan el beneficio bruto estimado para cuatro años. A continuación vamos a crear dos gráficos de entrada de datos. Por un lado un gráfico que contemple el porcentaje de Incremento de Ventas y el porcentaje de Coste Directo y, por otro lado, otro gráfico que contemple las Ventas del Año1 y los Gastos Fijos :

(Realizamos dos gráficos de entrada de datos por no complicarlo innecesariamente teniendo que crear un gráfico en dos ejes, ya que manejamos cifras en euros y porcentajes)
Si ahora quisiéramos comprobar cómo quedaría el beneficio bruto si modificáramos, por ejemplo, los Gastos Fijos, el 95% de usuarios irían a la celda que contiene dicho dato (los Gastos Fijos) y procederían a cambiar la cifra. Pero debe saber que esto puede hacerlo directamente desde el gráfico de entrada de datos. Para ello sólo tiene que hacer lo siguiente:
1. Haga un clic encima de la columna de Gastos Fijos en el gráfico. Le aparecerá seleccionada la serie, es decir, tanto la columna de ventas como la de gastos fijos. Para seleccionar sólo la de Gastos Fijos vuelva a hacer otro clic encima de dicha columna. Quedará de la siguiente forma:

2. Ahora estire el rectángulo hacia arriba hasta alcanzar la cifra deseada que se irá mostrando en un pequeño recuadro amarillo. Pruebe con estirar hasta los 2.250€ y suelte:

Al soltar el gráfico del beneficio bruto se habrá actualizado automáticamente para los nuevos valores. 


Si vuelve a la hoja y comprueba la celda de entrada de datos correspondiente a los Gastos Fijos verá que también ha cambiado tomando el valor 2.250€.

miércoles, 20 de mayo de 2009

Copiado Inteligente



Hoy explicaré una funcionalidad de Excel que, me temo, muchos usuarios habituales desconocen: el Copiado Inteligente. Como veremos a continuación, se trata de una funcionalidad muy sencilla pero tremendamente práctica. Partamos como siempre de un ejemplo:
Supongamos que tenemos los ingresos y gastos de una pequeña empresa durante los doce meses del año y queremos calcular el margen mensual en euros, en porcentaje y, finalmente, el margen trimestral (en euros): 

Estará de acuerdo conmigo que la formulación de este modelo no puede ser más sencilla:
1. Nos situamos en E3 y escribimos la fórmula:
=C3-D3
2. El siguiente paso que van a realizar un alto porcentaje de usuarios de Excel es situarse nuevamente en la celda E3 y colocar el puntero encima del pequeño cuadrado negro que se encuentra en la parte inferior derecha de la celda para "arrastrar" hasta E14 y de esta manera copiar la fórmula realizada. NO ES NECESARIO. Simplemente ponga el puntero encima del citado pequeño cuadrado negro y haga DOBLE CLIC encima de éste. Automáticamente Excel copiará su fórmula hasta E14. Esto es el Copiado Inteligente.


¿Cómo funciona el Copiado Inteligente?
Cuando usted hace el doble clic al que me acabo de referir ¿cómo sabe Excel hasta donde debe copiar? Realmente no lo sabe. Lo que hace es rellenar hacia abajo (la fórmula existente en la celda) hasta que en las celdas adyacentes de la izquierda aparezca una celda en blanco. En nuestro ejemplo, si no tuviéramos cifra de gastos en, por ejemplo, junio, Excel detendría el copiado en la celda E7, ya que en D8 se encontraría la primera celda adyacente a la izquierda en blanco:


Cuando la celda adyacente de la izquierda está en blanco, Excel "mira" a la celda adyacente de la derecha para utilizarla como guía. Pero siempre lo hace en este orden indicado: primero comprueba la celda adyacente de la izquierda y sólo si se encuentra en blanco se fija en la celda adyacente de la derecha. En caso de tener contenido ambas se seguirá fijando en la de la izquierda (y, por supuesto, si están ambas en blanco pues no hará nada). 

Ya ha visto como utilizar el copiado inteligente para rellenar con un doble clic una fórmula ¿Se podría utilizar para varias fórmulas a la vez? Sí. Haga lo siguiente para comprobarlo:
3. Borre el contenido del rango E4:E14 (mantenga la fórmula realizada en E3)
4. Vaya en la celda F3 y escriba la siguiente fórmula para calcular el margen en porcentaje:
=(C3-D3)/C3 
5. Ahora seleccione el rango E3:F3
6. Coloque el puntero encima del cuadrado negro de la parte inferior derecha de la selección

7. Haga doble clic y trabajo terminado...


Copiado Inteligentes "por bloques"
En la columna G de nuestro ejemplo queremos calcular el margen en euros obtenido en cada trimestre. Para calcularlo haremos lo siguiente:
1. Nos situamos en G5 y escribimos la fórmula:
=SUMA(E3:E5)
Hasta aquí nada nuevo. Pero en vez de ponerse a continuación a copiar esta fórmula cada tres meses, es decir, en G8, G11 y G14 (y no me diga que no lo iba a hacer así...) le propongo que haga lo siguiente:
2. Seleccione el rango G3:G5
3. Coloque el puntero en el dichoso cuadradito negro de la parte inferior derecha de la selección.


4. Haga doble clic encima.


Por utilizar uno de los "trucos" vistos en la función SUMA, si ahora desea calcular el total de las columnas C, D y E seleccione el rango C3:E14 y directamente pulse el icono de Autosuma. Automáticamente le aparecerán dichos totales en el rango C15:E15 (puede utilizar también otras funciones como PROMEDIO, MAX, MIN, etcétera, en vez de la SUMA)


domingo, 17 de mayo de 2009

"Trucos" para trabajar con Nombres de Rango


En muchos de los artículos publicados en este blog he insistido en la importancia de trabajar con nombres de rangos. Hoy veremos algunos trucos para trabajar con ellos (para ver cómo crearlos dispone de varios ejemplos que puede encontrar en Crear Nombres)

Ver los Rangos Definidos en una Hoja
Este primer truco es quizás el más desconocido. Se trata de utilizar el zoom de la barra de herramientas estándar para verlos. En la siguiente imagen puede ver dos tablas. La primera contiene las unidades vendidas en distintas tiendas a lo largo del año. La segunda es una lista con los nombres de los meses. Al rango C3:E14 le he dado el nombre Unidades. Al rango G3:G14 le he dado el nombre Meses.

Para ver los rangos que ha definido en esta hoja sólo tiene que cambiar el porcentaje del zoom,  que encontrará a la derecha de la barra de herramientas estándar, y ponerlo en una cifra inferior al 40%. Ponga el 39% y obtendrá lo siguiente:

Como puede comprobar, Excel nos muestra automáticamente los rangos definidos con sus nombres sobreimpresionados.

Lista de todos los Rangos Definidos en un Libro
Para obtener una lista de todos los rangos que ha definido en un libro de Excel sólo tiene que pulsar la tecla F3 y a continuación el botón Pegar Lista:

Excel pegará, a partir de la celda en la que se encuentre situado, una lista con los distintos nombres generados y la hojas y celdas a las que hagan referencia:
 

Crear Hipervínculos con los Nombres de Rango
En el punto anterior hemos visto cómo generar una lista con todos los nombres y direcciones de los rangos que tenemos en nuestro libro. Puede resultar útil el generar un hipervínculo, es decir, que al pulsar encima, por ejemplo, del nombre de cada rango nos lleve a éste. Para ello utilizaremos la función HIPERVINCULO que paso a explicar:.

La función HIPERVINCULO crea un acceso directo que abre un documento almacenado en un servidor de red o en Internet. Cuando haga clic en la celda que contenga la función HIPERVINCULO, Microsoft Excel abrirá el archivo almacenado en ubicación_del_vínculo. Veamos ahora la sintaxis de esta función:
HIPERVINCULO(ubicación_del_vínculo;nombre_descriptivo)

* Ubicación_del_vínculo    es la ruta completa del archivo que se desea abrir y debe especificarse como texto (entre comillas). Ubicación_del_vínculo puede hacer referencia a un lugar en un documento , como por ejemplo una celda específica o un rango con nombre en una hoja de cálculo o libro de Microsoft Excel, o a un marcador en un documento de Microsoft Word, etcétera. Ubicación_del_vínculo puede ser una cadena de texto encerrada entre comillas o una celda que contiene el vínculo como cadena de texto.

* Nombre_descriptivo    es el texto o valor numérico mostrado en la celda. El nombre_descriptivo se muestra en azul y está subrayado. Si contenido_de_celda se pasa por alto, la celda muestra ubicación_del_vínculo como texto de salto. Nombre_descriptivo puede ser un valor, una cadena de texto, un nombre o una celda que contiene el texto o valor al que se salta.


Explicada la función la aplicamos a nuestro ejemplo. Antes de realizar la fórmula es necesario indicar que el nombre del archivo sobre el que estoy trabajando es TrucosRangos.

1.Nos situamos en E18 y escribimos:
=HIPERVINCULO("[TrucosRangos.xls]"&C18;B18)
Fíjese que he puesto el nombre del archivo entre corchetes (es como lo reconoce Excel) y entre comillas. Además he añadido la función CONCATENAR, en su versión &, para unir al nombre del archivo la dirección concreta a la que queremos que nos lleve el vínculo. Es decir:
"[TrucosRangos.xls]"&C18 es lo mismo que "[TrucosRangos.xls]Hoja1!$G$3:$G$14" que es la ruta completa del vínculo que queremos crear. Finalmente hemos añadido B18 de tal manera que el nombre en azul y subrayado que aparecerá en E18 es el que tengamos en B18, es decir, Meses (que es precisamente el nombre del rango al que queremos llegar).
2. Copiamos la fórmula de E18 y la pegamos en C18.


En este ejemplo sólo hemos creado dos rangos en una hoja. Si tiene muchos nombres de rango y en diversas hojas esta fórmula puede ser muy útil para su rápida localización.