martes, 10 de agosto de 2010

Ejemplo de factura vinculada a hoja de almacen

En artículos anteriores hemos visto cómo realizar una factura en excel 2010, guardarla como plantilla y colocar las fórmulas adecuadas para calcular totales, iva, descuentos, etc.

Vamos a darle ahora una pequeña vuelta de tuerca a este tipo de facturas vinculándolas con una hoja de excel donde tengamos los datos de los productos de nuestro almacén.

Partiremos de una factura y de una tabla de artículos como se ve en las imagenes:




La tabla de productos está en el mismo libro que la factura y la hemos llamado Almacen.

De lo que se trata es de, en la factura, no tener que escribir la descripción y el precio, no sólo por comodidad sino para, también, evitar errores. Lo primero que haremos en nuestra plantilla de factura es añadirle una columna antes de las unidades vendidas, donde escribiremos el código del producto vendido.

Insertaremos una columna en la tabla de la factura (OJO: no en la hoja completa, ya que se nos descuadraría todo el formato). Para ello seleccionamos la columna unidades de la tabla (o la columna delante de la que queremos insertar la columna), damo al botón derecho del ratón y en el menú contextual que aparece le damos a Insertar...

Se nos muestra la ventana Insertar Celdas, donde elegiremos la opción "desplazar las celdas a la derecha", tal como muestra la figura:


De este modo, tenemos liberada la columna A. En la celda A10, escribimos el título referencia, y nos queda de este modo:


Así pues, ahora tenemos que colocar las fórmulas necesarias para que escribiendo en la columna "REFERENCIA" el código del producto vendido, en las columnas "DESCRIPCION" y "PRECIO UNITARIO", aparezcan la descripción del producto y el precio del mismo, respetivamente.

Para ello emplearemos la función =CONSULTAV, en la que el valor a buscar, estará en la columna "REFERENCIA"; el rango de búsqueda será la tabla de productos de la hoja Almacén; y las columnas a rescatar serán la 2 para al descripción y la 3 para el precio.

Así pues, en la celda C12 (descripcion) pondremos la siguiente fórmula:

=CONSULTAV(Factura!A12;Almacen!$A$2:$C$11;2)

y en la D12 (precio unitario):

=CONSULTAV(Factura!A12;Almacen!$A$2:$C$11;3)

Copiamos estas fórmulas a todas las celdas de sus respectivas columnas y guardamos como plantilla este nuevo modelo de factura.

Ahora pongamos algunos datos ficticios. Supongamos que hemos vendido 5 productos de referencia SX-369, 7 de SX-402 y 1 de SX-381.

Vemos que automáticamente se rellenan las casilla correspondientes a la descripción y el precio quedando como sigue:



Como vemos, se nos llena la factura de #N/A y no nos calcula los totales. Eso es debido a que la función CONSULTAV devuelve el mensaje de error #N/A si la casilla donde debería estar el dato de búsqueda está vacía.

Para evitar esto, aplicaremos la función CONSULTAV, sólo cuando la casilla de REFERENCIA no esté vacía. Echaremos mano de la función SI, a que le anidaremos la fórmula anterior, quedando como sigue:

=SI(A12<>"";CONSULTAV(A12;Almacen!$A$2:$C$11;2);0), para las descripciones, y

=SI(A12<>"";CONSULTAV(A12;Almacen!$A$2:$C$11;3);0), para los precios.

Quedando ya de este modo:



martes, 3 de agosto de 2010

Ejemplo de factura en Excel 2010

Una de las tareas más para las que el uso de Excel puede resultar más práctico es para la confección de facturas. Se trata de cálculos sencillos (sumas y productos, aplicación de descuentos y porcentajes), pero generalmente en gran cantidad.

Debido a su frecuente uso es muy útil diseñarse un modelo (aunque la bilioteca de plantillas de Excel trae una cuantas) y guardarlo como nuestra propia plantilla para reutilizarlo una y otra vez.

Hay algunos datos que son imprescindibles para que una factura pueda ser considerada como tal: el nombre y número de identificación fiscal tanto del cliente como del emisor de la factura, el número de la misma y la fecha. Todos esos datos los pondremos en la parte superior en dos columnas:

Al lado, escribiremos el IVA aplicable (D7), y el Dto que haremos (D8).

A continuación va el encabezado de las cuatro columnas que van a constituir nuestra factura propiamente dicha: Unidades, Descripción, Precio Unitario e Importe.

En la columna Unidades, pondremos el número de las unidades vendidas; en Descripción, una reseña del producto; en Precio Unitario, el importe por unidad y en Importe, el precio total por producto, antes de impuestos y descuentos.

Aplicado un formato de negritas y bordeados queda una cosa así:


Al final de la columna PRECIO UNITARIO, pondremos el rótulo SUBTOTAL, y debajo de éste, todos aquellos descuentos, gastos e impuestos aplicables, en la práctica: DTO e IVA.


Finalmente, el TOTAL.

Hasta aquí, tenemos hecha la estructura. Vamos a ver las fórmulas que tenemos que colocar.

En la columna IMPORTE colocamos la fórmula: Unidades vendidas X Precio unitario, o sea, en la celda D12, pondremos: = A12*C12. Como en esa fórmula se emplean referencias relativas, la copiaremos hasta la celda D29.

Para calcular el subtotal (celda D30), simplemente le damos al botón autosuma y marcamos el rango, o bien, escribimos = SUMA(D12:D30).

En el descuento, multiplicamos el subtotal por el porcentaje de descuento aplicar: = D30*D8.

La base imponible la obtenemos restándole al subtotal el descuento: = D30-D31.

Sobre la base imponible, calcularemos el iva de manera análoga al descuento: =D32*D7.

Para otener el total simplemente sumamos las celdas D2 y D33, la base imponible más el IVA.

Acabamos aplicando a todas aquellas celdas que contengan cantidades monetarias el formato moneda: seleccionamos el rango D12:D34 y, dejando pulsada la tecla CTRL, el C12:C29; y desde el menú inicio > número, seleccionamos el formato Moneda.




Ya sólo quedaría rellenar la factura con los datos del vendedor y del comprador, así como, de los productos vendidos.


martes, 27 de julio de 2010

La funcion ConsultaV

Excel dispone de funciones de búsqueda y referencia para poder consultar valores de otras tablas para nuestros cálculos.

Imaginemos que diponemos de una tabla en la que tenemos almacenados los datos de nuestros productos:



Esta tabla la tenemos en una hoja aparte llamada artículos.

Ahora si queremos elaborar facturas, hojas de pedido o cualquier otro tipo de informe no nos hace falta copiar el precio "a mano", o saber en qué celda está ya que podemos usar una función de búsqueda de la que Excel dispone llamada ConsultaV (en versiones anteriores era BuscarV).

Veamos primero lo que queremos conseguir:




Se trata de una factura en la que los valores calculados están con fondo amarillo, y los únicos datos que hemos introducido son el código los productos y el número de unidades vendidas.


La función que hemos escrito para obtener la descripción de los productos, celda B2, es la función =ConsultaV, que nos sirve para buscar un valor en la primera columna de una tabla y obtener el valor de su misma fila y en la columna especificada. Es decir, busco en la tabla de la figura 1, los productos por referecias y puedo extraer los datos de descripción, precio y stock.


Su sintaxis es la siguiente:

= ConsultaV(Valor Buscado; Rango de búsqueda; indicador de columna)

En nuestro ejercicio, pues, pondríamos:
= ConsultaV(SX-3313; tabla de hoja Artículos; 2)

que pasado a celdas y rango queda:
= ConsultaV(B4;Artículos!$B$3:$E$13;2) .- para la descripción, y
= ConsultaV(B4;Artículos!$B$3:$E$13;3) .- para el precio.

Es importante observar que hemos especificado el rango con referencias absolutas y el artículo con referencias relativas, para de este modo, escribir las fórmulas en la primera fila y poder copiarlas en las demás.

En el indicador de columnas hemos puesto un 2 para la descripción y un 3 para el precio, ya que la columna 1 es la referencia, la 2 es la descripción, 3 precio y 4 stock.

Para obtener el importe tan sólo hemos mutiplicado el precio por el número de unidades vendidas ( = C2*D2) y la función suma para obtener el importe total ( = SUMA(E2:E4)).

Para el cálculo del IVA simplemente hemos multiplicado el importe total por 18% (=E11*18%) y para el TOTAL, = E11 + E12.

martes, 20 de julio de 2010

Las funciones SI, Y y O

Con este artículo acabamos la serie de cuatro tutoriales dedicados a la función SI.

En el primero de ellos, vimos las generalidades de la función SI, su uso más básico. En el segundo artículo estudiamos el uso de SI con varias condiciones, el uso de funciones SI anidadas. En el artículo anterior vimos el uso conjunto de las funciones SI e Y, con un ejercicio de gestión de stocks. Hoy completaremos dicho ejercicio con el uso de las funciones SI, Y y la función O.

Supongamos que vamos a gestionar nuestro stock de almacén con una política de precios más agresiva que en el ejercicio anterior: ahora aplicaremos el descuento del 50% siempre que se cumpla AL MENOS UNA DE LAS CONDICIONES, que el producto venga de España o que haya vendido menos de 100 unidades o que sea de una fecha anterior a 2010. Si no se cumple ninguna aplicamos un descuento del 20%.

Empleamos la función O, cuya sintaxis es exactamente igual que la de la función Y:

= O(condición 1, condición 2, ...., condición n)

En nuestro caso:

= O(C4="España";D4<100;e4<2010) style="TEXT-ALIGN: center; MARGIN: 0px auto 10px; WIDTH: 581px; DISPLAY: block; HEIGHT: 95px; CURSOR: hand" id="BLOGGER_PHOTO_ID_5486325961792082818" border="0" alt="" src="http://4.bp.blogspot.com/_QiCa_HtqEag/TCNYZPqe04I/AAAAAAAAACk/bxvKnJZysP4/s400/1.jpg"> Aplicada en la tabla del ejercicio anterior obtenemos:


Como podemos observar, aplicaremos siempre el descuento del 50% ya que al menos alguna de las tres condiciones se cumple siempre. Como ejercicio, pruebe usted a modificar alguno de los valores de la tabla. Por ejemplo, al producto SH-329, proveniente de Argentina póngale unas ventas mayores de 100. De este modo no cumplirá ninguna de las opciones.

Podemos darle otra vuelta de tuerca más al ejercicio: hagamos que se le aplique un 50% si se cumplen las tres condiciones, un 25% si se cumple una de las tres y un 10% si no se cumple ninguna.

Tendremos que anidar dos funciones SI. La condición de la primera llevará una función Y con las tres condiciones del ejemplo. La primera opción, el caso en que se cumpla, llevará el 50%. La segunda opción, llevará una nueva función SI en la que la condición es ahora la función O, y los dos parámetros siguientes serían 25% y 10%.

Es decir:

= SI(Y(condicion1; condición2, condición3);50%;SI(O(condición1; condición2; condición3);25%;10%))

siendo:

  • condición1.-procedencia = "España"

  • condición2.- ventas menor que 100

  • condición3.- fecha menor que 2010

O sea:

El resultado final sería el que se ve en la siguiente tabla. Nótese que el número de unidades vendidas del producto SH-329 ha sido modificado para que se puedan ver las tres posibilidades.


Como siempre, le animamos a que realice este ejercicio completo, partiendo de los datos de las celdas con fondo blanco.

martes, 13 de julio de 2010

Uso de las funciones SI e Y

Hemos visto en entradas anteriores, el uso básico de la función SI y el uso de la función SI con varias condiciones (funciones anidadas). En esta entrada veremos el uso de la función SI combinado con la función Y.

Como siempre veamos un ejemplo:

Supongamos que tenemos un stock de productos en almacén y queremos modificar nuestra política de precios.


La tabla a rellenar será la siguiente:






Si el producto proviene de España, es más antiguo de 2010 y hemos vendido menos de 100 unidades, aplicaremos un descuento del 50%. Ojo: TIENEN QUE CUMPLIRSE LAS TRES CONDICIONES A LA VEZ. Si alguna o todas, no se cumplen, le aplicamos un descuento del 20%.

Usaremos la funcion SI, conjuntamente con la función Y. La función Y, nos devuelve el valor VERDADERO si se cumplen todas las condiciones que hay entre paréntesis separadas por punto y coma.

Su sintaxis es: = Y(condición 1, condición 2, ..., condición n)

En el caso que nos ocupa sustituiremos la condición de la función SI, por la función Y, con las tres condiciones como parámetros.


Nos quedaría así:

Para calcular el nuevo precio basta restarle al precio base el producto del descuento por el precio base. Obteniendo la tabla que vemos:



Las celdas con fondo amarillo son los valores calculados.

martes, 6 de julio de 2010

Uso de la función SI con varias condiciones

Ya hemos visto el uso de la función SI, cuando le aplicamos una sóla condición. Pero el poder de la función SI no acaba ahí, sino que permite (en combinación con otras funciones o anidándola) su aplicación con varias condiciones.

La mejor forma de entenderlo será mediante una ejemplo:

La tabla de cotizaciones del IRPF en España (un impuesto que grava el rendimiento del trabajo en función de la cuantía de éste) es como sigue:


  • Ingresos inferiores a 9.843 € están exentos.

  • Ingresos entre 9.843€ y 17.707€ sufren un gravamen del 24%.
  • Ingresos comprendidos entre 17.707€ y 33.007€ están gravados con un 28%.

  • Ingresos mayores que 33.007€ y menores de 53.407€ tienen un tipo del 37%.

  • Ingresos que sobrepasen los 53.407€ cotizan al 43%.

Vamos a ver cómo, a partir de un valor dado y la función SI, podemos ir determinado los distintos gravámenes.

Usaremos funciones SI anidadas de la siguiente forma:

  1. La primera condición será preguntarnos si el ingreso es menor que 9.843. Si lo es, el tipo es cero. SI NO LO ES, aplicamos la segunda condición.

  2. Sabiendo que el valor es mayor que 9.843, cuestionamos si es menor de 17.707. Si lo es, el tipo es del 24%. SI NO LO ES aplicamos la tercera condición.

  3. La tercera condición es si el ingreso es menor que 33.007. Si lo es, el tipo es 28%. Si no lo es, cuarta condición.

  4. La cuarta condición nos cuestiona si el ingreso es menor que 53.407. En caso afirmativo, el tipo es del 37%. En caso contrario el tipo es el 43%.

Traslademos los puntos anteriores a la función SI (suponemos los ingresos en B3):

No dude en realizar una tabla con diferentes ingresos, tal como la de la figura:

(los datos están en la columna ingresos, y la columna tipos IRPF hay que obtenerla mediante la función SI anidada)

martes, 29 de junio de 2010

Uso de la función SI

Cuando comenzamos a realizar cálculos avanzados, según vamos poco a poco comprendiendo más y más la potencia del Excel, comenzamos a demandar funciones más complicadas que hagan tal o cual cosa... y generalmente ¡existen!.
Recientemente en un curso que impartí en una universidad para el cuerpo de administrativos me preguntaron como calificar automáticamente las notas de los alumnos. No me refiero, claro, a que excel corrigiera los exámenes, sino a que una vez evaluados y con los valores numéricos de las calificaciones en una tabla, automáticamente nos indicara si el alumno estaba aprobado o no.

Para ello utilizaremos la función SI().

La sintaxis de la función SI() es la siguiente: =SI(Condición;Operación si Verdadero; Operación Si Falso)

Es decir, escribimos la palabra SI, abrimos paréntesis y escribimos la condición que, dependiendo de si se cumple o no, se realizará una operación u otra. En el ejemplo de las notas de los alumnos, si la nota es mayor o igual que cinco, mostrará el mensaje "APROBADO" y, si no lo es, mostrará el mensaje "SUSPENSO".

Veamos como sería la función que nos hiciese esa operación. Asumiendo que la nota del alumno esté en la celda A3, la fórmula quedaría como sigue:

=SI ( A3>=5;"APROBADO";"SUSPENSO")

En las condiciones pueden usarse los siguiente operadores:

  • > Mayor que

  • <>
  • >= Mayor o igual que

  • <= Menor o igual que

  • = Igual que

  • <> Distinto de

En la función, en la zona Operación si Verdadero y Operación si Falso, no tiene que ir forzosamente un texto escrito, sino que puede ir otra fórmula o función. Si la función que va en esa parte es otra función SI, se lo que se conoce como funciones anidadas.

Veamos un ejemplo.

Supongamos que si el alumno aprueba, se le otorga una beca del 10% del pago de la matrícula y si suspende se le incrementa en un 10% esas tasas. Para ello, y suponiendo que la nota está en la celda A3 y el precio base de la matrícula en B2, la función quedaría como sigue:

= SI(A3>=5;B2-B2*10%;B2+B2*10%)

Para afianzar lo aquí expuesto, le recomendamos que realice en su hoja de cálculo el siguiente ejercicio:


Partiendo de las celdas con fondo amarillo, las notas de los alumnos, sus nombres, las cabeceras de categorías y el valor base de la matrícula, obtener si el alumno está aprobado o suspenso y el precio de la matrícula acorde a sus calificaciones.