Ejemplo de factura vinculada a hoja de almacenEn artculos anteriores hemos visto cmo realizar una factura en excel 2010, guardarla como plantilla y colocar las frmulas adecuadas para calcular totales, iva, descuentos, etc. Vamos a darle ahora una pequea vuelta de tuerca a este tipo de facturas vinculndolas con una hoja de excel donde tengamos los datos de los productos de nuestro almacn. Partiremos de una factura y de una tabla de artculos 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 descripcin y el precio, no slo por comodidad sino para, tambin, evitar errores. Lo primero que haremos en nuestra plantilla de factura es aadirle una columna antes de las unidades vendidas, donde escribiremos el cdigo del producto vendido. Insertaremos una columna en la tabla de la factura (OJO: no en la hoja completa, ya que se nos descuadrara 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 botn derecho del ratn y en el men contextual que aparece le damos a Insertar... Se nos muestra la ventana Insertar Celdas, donde elegiremos la opcin "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 ttulo referencia, y nos queda de este modo:

As pues, ahora tenemos que colocar las frmulas necesarias para que escribiendo en la columna "REFERENCIA" el cdigo del producto vendido, en las columnas "DESCRIPCION" y "PRECIO UNITARIO", aparezcan la descripcin del producto y el precio del mismo, respetivamente. Para ello emplearemos la funcin =CONSULTAV, en la que el valor a buscar, estar en la columna "REFERENCIA"; el rango de bsqueda ser la tabla de productos de la hoja Almacn; y las columnas a rescatar sern la 2 para al descripcin y la 3 para el precio.

As pues, en la celda C12 (descripcion) pondremos la siguiente frmula: =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 frmulas 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 automticamente se rellenan las casilla correspondientes a la descripcin 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 funcin CONSULTAV devuelve el mensaje de error #N/A si la casilla donde debera estar el dato de bsqueda est vaca. Para evitar esto, aplicaremos la funcin CONSULTAV, slo cuando la casilla de REFERENCIA no est vaca. Echaremos mano de la funcin SI, a que le anidaremos la frmula 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:

Tutoriales ExcelMARTES 10 DE AGOSTO DE 2010

Ejemplo de factura vinculada a hoja de almacenEn artculos anteriores hemos visto cmo realizar una factura en excel 2010, guardarla como plantilla y colocar las frmulas adecuadas para calcular totales, iva, descuentos, etc. Vamos a darle ahora una pequea vuelta de tuerca a este tipo de facturas vinculndolas con una hoja de excel donde tengamos los datos de los productos de nuestro almacn. Partiremos de una factura y de una tabla de artculos 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 descripcin y el precio, no slo por comodidad sino para, tambin, evitar errores. Lo primero que haremos en nuestra plantilla de factura es aadirle una columna antes de las unidades vendidas, donde escribiremos el cdigo del producto vendido. Insertaremos una columna en la tabla de la factura (OJO: no en la hoja completa, ya que se nos descuadrara 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 botn derecho del ratn y en el men contextual que aparece le damos a Insertar... Se nos muestra la ventana Insertar Celdas, donde elegiremos la opcin "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 ttulo referencia, y nos queda de este modo:

As pues, ahora tenemos que colocar las frmulas necesarias para que escribiendo en la columna "REFERENCIA" el cdigo del producto vendido, en las columnas "DESCRIPCION" y "PRECIO UNITARIO", aparezcan la descripcin del producto y el precio del mismo, respetivamente. Para ello emplearemos la funcin =CONSULTAV, en la que el valor a buscar, estar en la columna "REFERENCIA"; el rango de bsqueda ser la tabla de productos de la hoja Almacn; y las columnas a rescatar sern la 2 para al descripcin y la 3 para el precio. As pues, en la celda C12 (descripcion) pondremos la siguiente frmula: =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 frmulas 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 automticamente se rellenan las casilla correspondientes a la descripcin 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 funcin CONSULTAV devuelve el mensaje de error #N/A si la casilla donde debera estar el dato de bsqueda est vaca. Para evitar esto, aplicaremos la funcin CONSULTAV, slo cuando la casilla de REFERENCIA no est vaca. Echaremos mano de la funcin SI, a que le anidaremos la frmula 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:

Publicado por vorgon en 23:34 2 comentarios Etiquetas: consultav, factura, funcion si, funciones anidadas MARTES 3 DE AGOSTO DE 2010

Ejemplo de factura en Excel 2010Una de las tareas ms para las que el uso de Excel puede resultar ms prctico es para la confeccin de facturas. Se trata de clculos sencillos (sumas y productos, aplicacin de descuentos y porcentajes), pero generalmente en gran cantidad.

Debido a su frecuente uso es muy til disearse 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 nmero de identificacin fiscal tanto del cliente como del emisor de la factura, el nmero 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 continuacin va el encabezado de las cuatro columnas que van a constituir nuestra factura propiamente dicha: Unidades, Descripcin, Precio Unitario e Importe.

En la columna Unidades, pondremos el nmero de las unidades vendidas; en Descripcin, una resea 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 rtulo SUBTOTAL, y debajo de ste, todos aquellos descuentos, gastos e impuestos aplicables, en la prctica: DTO e IVA.

Finalmente, el TOTAL.

Hasta aqu, tenemos hecha la estructura. Vamos a ver las frmulas que tenemos que colocar. En la columna IMPORTE colocamos la frmula: Unidades vendidas X Precio unitario, o sea, en la celda D12, pondremos: = A12*C12. Como en esa frmula se emplean referencias relativas, la copiaremos hasta la celda D29. Para calcular el subtotal (celda D30), simplemente le damos al botn 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 restndole al subtotal el descuento: = D30-D31. Sobre la base imponible, calcularemos el iva de manera anloga al descuento: =D32*D7. Para otener el total simplemente sumamos las celdas D2 y D33, la base imponible ms 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 > nmero, seleccionamos el formato Moneda.

Ya slo quedara rellenar la factura con los datos del vendedor y del comprador, as como, de los productos vendidos.

Publicado por vorgon en 02:41 0 comentarios Etiquetas: descuentos, factura, iva, plantillas MARTES 27 DE JULIO DE 2010

La funcion ConsultaVExcel dispone de funciones de bsqueda y referencia para poder consultar valores de otras tablas para nuestros clculos. 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 artculos. 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 funcin de bsqueda 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 estn con fondo amarillo, y los nicos datos que hemos introducido son el cdigo los productos y el nmero de unidades vendidas.

La funcin que hemos escrito para obtener la descripcin de los productos, celda B2, es la funcin =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 descripcin, precio y stock.

Su sintaxis es la siguiente: = ConsultaV(Valor Buscado; Rango de bsqueda; indicador de columna) En nuestro ejercicio, pues, pondramos: = ConsultaV(SX-3313; tabla de hoja Artculos; 2) que pasado a celdas y rango queda:

= ConsultaV(B4;Artculos!$B$3:$E$13;2) .- para la descripcin, y = ConsultaV(B4;Artculos!$B$3:$E$13;3) .- para el precio. Es importante observar que hemos especificado el rango con referencias absolutas y el artculo con referencias relativas, para de este modo, escribir las frmulas en la primera fila y poder copiarlas en las dems. En el indicador de columnas hemos puesto un 2 para la descripcin y un 3 para el precio, ya que la columna 1 es la referencia, la 2 es la descripcin, 3 precio y 4 stock. Para obtener el importe tan slo hemos mutiplicado el precio por el nmero de unidades vendidas ( = C2*D2) y la funcin suma para obtener el importe total ( = SUMA(E2:E4)). Para el clculo del IVA simplemente hemos multiplicado el importe total por 18% (=E11*18%) y para el TOTAL, = E11 + E12.Publicado por vorgon en 05:43 3 comentarios Etiquetas: busqueda, consulta v, factura, iva, referencia MARTES 20 DE JULIO DE 2010

Las funciones SI, Y y OCon este artculo acabamos la serie de cuatro tutoriales dedicados a la funcin SI. En el primero de ellos, vimos las generalidades de la funcin SI, su uso ms bsico. En el segundo artculo estudiamos el uso de SI con varias condiciones, el uso de funciones SI anidadas. En el artculo anterior vimos el uso conjunto de las funciones SI e Y, con un ejercicio de gestin de stocks. Hoy completaremos dicho ejercicio con el uso de las funciones SI, Y y la funcin O. Supongamos que vamos a gestionar nuestro stock de almacn con una poltica de precios ms 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 Espaa 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 funcin O, cuya sintaxis es exactamente igual que la de la funcin Y: = O(condicin 1, condicin 2, ...., condicin n) En nuestro caso: = O(C4="Espaa";D4=5;"APROBADO";"SUSPENSO")

En las condiciones pueden usarse los siguiente operadores:

> Mayor que >= Mayor o igual que =5;B2-B2*10%;B2+B2*10%) Para afianzar lo aqu expuesto, le recomendamos que realice en su hoja de clculo el siguiente ejercicio:

Partiendo de las celdas con fondo amarillo, las notas de los alumnos, sus nombres, las cabeceras de categoras y el valor base de la matrcula, obtener si el alumno est aprobado o suspenso y el precio de la matrcula acorde a sus calificaciones.Publicado por vorgon en 03:20 0 comentarios