15

Factura: modelo de factura con fórmulas que buscarán artículos en la anterior hoja

Abrir el archivo Ej8.xls. La primera hoja tiene la siguiente lista de artículos:

Evidentemente, cada uno diseñará su formato de factura como mejor le convenga. En este caso, el diseño deja mucho que desear, pero lo importante son las fórmulas que se van a utilizar. Observar la hoja: las fórmulas se introducirán en las celdas sombreadas. Se escribirá un código de artículo de la hoja anterior y aparecerá su descripción automáticamente en la celda de la derecha. También aparecerá el precio unitario. Luego se introducirá la cantidad deseada y Excel calculará el total de cada fila, y el total de toda la columna. En la celda E12 puede aparecer un descuento de la factura sólo si en la celda B13 se escribe la palabra CONTADO. En ese caso, la fórmula de la celda E12 efectuará un 5% de descuento del total de la celda E11.

Celda	Fórmula	Comentario
B6	=BUSCARV(A6;Hoja1!A2:B6;2)	Buscar el código en la Hoja 1 y sale su descripción.
D6	=BUSCARV(A6;Hoja1!A2:D6;4)	Igual para que aparezca el precio unitario
E6	=C6*D6	Calcula el precio del artículo según la cantidad
E11	=SUMA(E6:E10)	Suma la columna de los datos inmediatamente superiores
B13	(Escribir o no)	Aquí se puede escribir la palabra CONTADO o no (opcional)
E12	=SI(B13="CONTADO";E11*5%;0)	En caso de que en la celda B13 exista la palabra CONTADO, se calcula el 5% de lo que hay en E11. En caso de que en B13 no esté la palabra CONTADO, en la celda actual aparecerá un cero.
E13	=(E11-E12)*16%	Se calcula el 16% de IVA de la diferencia del precio menos el descuento
E15	=E11-E12+E13	Cálculo del precio final

Si se venden 200 tornillos, 1.500 arandelas, 10 destornilladores, 5.000 tuercas y 20 tenazas, el aspecto de la factura quedaría más o menos así:

En este capítulo se va a construir y mantener una cartera de acciones con información antigua y actual de las mismas. A continuación se pueden extraer valores que cumplan unos criterios, como por ejemplo, qué acciones han dado más resultados, etc.

En principio la hoja a construir es sencilla (Ej9.xls). Sólo es preciso comentar las fórmulas incluidas.

Las fórmulas se encuentran situadas en las celdas que aparecen en negrita (excepto los títulos) y son éstas:

F6: =B6*D6 da el valor actual en el mercado multiplicando las acciones por su precio de mercado

J6: =(PROMEDIO(L6:M6)/D6)^0,25-1 genera primero el promedio de las estimaciones Alta y Baja, y lo divide por el precio de mercado. Luego se eleva el resultado a la potencia 0,25 y se le resta 1, lo cual dará el porcentaje anual para un período de cuatro años.

Esta función devuelve el resto de una división. De esta forma el resto de 14 entre 5 sería 4. Observa la siguiente división:

RESIDUO(número;núm_divisor) donde número es la cantidad que se quiere dividir y núm_divisor es la cantidad por la que se divide.

En el ejemplo anterior sería de la siguiente manera: número = 14 y núm_divisor = 5 dando así un RESIDUO = 4.

Esta función redondea un número hasta el entero inferior. De forma que el Entero de 4'3 será 4 al igual que el entero de 4'8.

La sintaxis de esta función es: ENTERO(número) donde número podrá ser cualquier valor.

Ejemplo. Una vez presentadas estas dos nuevas funciones, crear un pequeño ejemplo donde se utilizan estas dos instrucciones de forma combinada.

Crear una hoja de Excel donde se pueda desglosar una cantidad dada en Euros en sus diferentes monedas y billetes, de forma que se tenga que utilizar el menor número posible de cada uno de ellos. Antes de empezar con la creación de la hoja de Excel es necesario explicar los pasos a seguir para conseguir ese propósito.

Para saber en cuantos billetes se necesitan, lo único que se debe hacer, en un principio, es dividir esta cantidad por el valor del billete. Por ejemplo, si se quiere saber cuantos billetes de 500 Euros se necesitan para tener 1500 Euros. Para saberlo se divide 1500 entre 500 dando como resultado 3.

Ahora veremos que es lo que pasa cuando se tiene una cantidad de Euros que al dividirla por un billete da decimales. En este caso se precisa utilizar la función ENTERO, para saber el número entero inferior más próximo. Por ejemplo, si se quiere saber cuantos billetes de 500 Euros se necesitan para tener 1700 Euros, se dividiría 1700 entre 500, dando como resultado 3'4. Como no se puede tener 3'4 billetes de 500 Euros, será necesario convertir el resultado en un entero, dando como resultado 3 billetes. En este último ejemplo, todavía se tiene dinero de los 1700 Euros. Para saber cuanto dinero falta, se puede utilizar el resto de la división (RESIDUO), dando como resultado 200 Euros restantes. Pues bien, ahora lo único que quedaría sería coger el siguiente tipo de billete y dividirlo entre el resto de la operación anterior y así sucesivamente hasta llegar al último tipo de moneda de Euro.

Las celdas anteriores son los títulos de las columnas que se van a utilizar en el ejemplo. Debajo de Cantidad en Euros colocar la cantidad en Euros que se quiere desglosar, esta cantidad tendrá un formato numérico con 2 dígitos decimales.

Debajo de Monedas y billetes colocar todos los valores de los billetes y monedas que se tienen en Euros. Suponer que se tienen billetes de 500, 200, 100, 50, 20, 10, 5 Euros y monedas de 2, 1, 0'50, 0'20, 0'10, 0'05, 0'02 y 0'01Euros. Estos valores se pondrán de la siguiente forma en la hoja:

Por último debajo de Cantidades de monedas y billetes será donde deberá aparecer el resultado de la búsqueda. Deberá aparecer cuántos billetes o monedas de cada tipo son necesarios, como mínimo, para tener la cantidad buscada.

En la celda C4 poner la siguiente formula =ENTERO(A4*100/(B4*100)) para calcular cuantos billetes de 500 Euros son necesarios. Observar que se ha multiplicado la cantidad a buscar de la celda A4 por 100 al igual que el valor del billete para no tener que trabajar con cantidades decimales.

Ahora lo único que será necesario es calcular el resto de la división. Al terminar la creación de la página se puede ocultar esta columna, ya que el resto de la división solo se necesita para poder seguir haciendo cálculos. Esta columna del resto se puede situar a partir de la celda D4. En esta celda se pone la siguiente fórmula =RESIDUO(A4*100;(B4*100)). Ahora ya es posible calcular cuántos billetes de 500 Euros serán necesarios como mínimo para una cantidad que se desee saber y cuál es la cantidad que sobra.

En una segunda parte será necesaria la operación con el resto de las cantidades que van sobrando. Para ello bastará con poner dos formulas más que después se podrán copiar hacia abajo. En la casilla C5 poner la siguiente función =ENTERO(D4/(B5*100)) donde se divide el resto de la cantidad anterior por el valor del billete que toca en este momento, 200 Euros. Recordar que el valor de los billetes y monedas siempre se multiplicará por 100 para evitar trabajar con decimales. Y por último solo se tendrá que escribir en la celda D5 la siguiente fórmula para calcular el residuo de esta división =RESIDUO(D4;(B5*100)).

Una vez escritas estas fórmulas lo único que hay que hacer es seleccionar las celdas C5 y D5 y copiar su fórmula hacia abajo hasta llegar a la fila 18, inclusive, donde se encuentra el último valor de moneda que se puede utilizar.

De esta forma ya está la hoja preparada, ya se puede ocultar la columna de los cálculos de los restos y ya es posible comenzar a utilizarla. Probar a poner un valor en la celda A4 para ver en qué monedas y billetes se puede desglosar como mínimo, recordando la equivalencia aproximada de Euros a Pesetas y viceversa.

	Ejemplo: Factura
	Ejemplo: Gestión de una cartera de valores
	Ejemplo: Desglose de Euros en monedas y billetes