Facturas con bases de datos en excel (clientes)

Uno de los temas más recurrentes en el uso práctico de excel es la elaboración de facturas debido, fundamentalmente, a la facilidad del cálculo del totales de precios, importes, IVA, descuentos, etc.

En un videotutorial anterior ya vimos un ejemplo de factura en excel 2010.

Lo que pretendemos hacer hoy es vincular una factura a otra hoja donde tendremos guardados los datos de los clientes, de manera que sólo con escribir su código de identificación (CIF, NIF, DNI, etc.) nos aparezcan todos los datos a cumplimentar en la factura. Para ello utilizaremos la función CONSULTAV().

Partiremos pues de una plantilla de factura como la que se ve en la figura:

 Facturas con bases de datos en excel (clientes) plantilla factura excel2

En el encabezado de la misma, en la parte izquierda, tenemos los datos del emisor de la factura. Serán los datos fijos de nuestra tienda, empresa o, si somos autónomos, nuestros propios datos fiscales.

En la parte derecha del encabezado irán los datos del cliente, donde colocaremos las fórmulas necesarias para que, sólo escribiendo el código fiscal aparezcan el resto de datos.

En la parte inferior del encabezado tenemos una casilla para la fecha de emisión de la factura. Si queremos automatizarla usaremos la función HOY(). En B9 escribimos =HOY(), y aparece la fecha actual. Así, al emitir una factura automáticamente tenemos la fecha correspondiente.

Debajo de la fecha, hemos de poner el número de factura. Las facturas han de ir numeradas con números correlativos y únicos. Cada año se reinicia la numeración.

En la parte derecha tenemos el valor de IVA y del descuento a aplicar (si no queremos aplicar descuento lo dejamos vacío).

En el cuerpo de la factura tenemos una primera columna con las unidades vendidas, a continuación la descripción del producto o servicio, después el precio unitario y, por último, el importe, resultado de multiplicar las unidades por el precio unitario. En E13, por ejemplo, tenemos la fórmula = A13*D13

El subtotal se obtiene sumando todos los importes, =SUMA(E12:E29).

El descuento se aplica sobre el subtotal =E30*E9

La base imponible (sobre la que aplicamos los impuestos) sale de restarle al subtotal el descuento =E30-E31

El IVA lo calculamos sobre la base imponible (=E32*E8) y el total de la factura resulta de sumar ambos valores =E33+E32

En otra hoja del mismo libro (o de otro), llamada clientes tenemos una tabla como esta:

 Facturas con bases de datos en excel (clientes) tabla clientes1

Hemos usado una tabla con muy poquitos clientes por motivos de simplicidad, pero no hay impedimento en tener una con cientos o miles de clientes.

En la celda D2 de la hoja Factura escribiremos el código del cliente según la tabla. En D1, usando la función CONSULTAV debe aparecer el nombre del cliente. La fórmula sera: =CONSULTAV(D2;Clientes!A2:E10;2;FALSO), donde D2 es la celda donde escribimos el código fiscal de identificación del cliente, Clientes!A2:E10 es el rango donde tenemos los datos de los clientes, 2 es el indicador de columna a contar desde la izquierda (columna 1 – código fiscal, columna 2 – nombre, columna 3 – dirección, etc.), y por último, FALSO indica que buscamos el valor exacto del código fiscal, no uno aproximado.

Atención: Si tienes instalado el Service Pack 1 de Office 2010, deberás usar la función BUSCARV, que es exactamente igual que la CONSULTAV, según este comunicado oficial de Microsoft

Para el resto de datos actuamos de forma completamente análoga, es decir, las fórmulas son las mismas modificando el valor de la columna correspondiente.

Así, para la dirección (D3): =CONSULTAV(D2;Clientes!A2:F9;3;FALSO)

para el código postal (D4): =CONSULTAV(D2;Clientes!A2:F9;4;FALSO)

para el código postal (D4): =CONSULTAV(D2;Clientes!A2:F9;4;FALSO)

para la ciudad (E4): =CONSULTAV(D2;Clientes!A2:F9;5;FALSO)

para el país (D5): =CONSULTAV(D2;Clientes!A2:F9;6;FALSO)

 

VN:F [1.9.22_1171]
Rating: 9.0/10 (1 vote cast)

CONSULTAV vuelve a llamarse BUSCARV en Excel 2010

Con la llegada de Excel 2010 muchas funciones cambiaron de nombre. El cambio más notorio fue el de la función BUSCARV que pasó a llamarse CONSULTAV. Todo era igual, misma sintaxis, mismos parámetros pero distinto nombre.

Las quejas y comentarios de los usuario de Office llegaron a los oídos de la gente de Microsoft que, en la nueva actualización Service Pack 1 de Office 2010 (a finales de 2011), han vuelto a dejar los nombre originales.

 CONSULTAV vuelve a llamarse BUSCARV en Excel 2010 cambios nombres funciones sp1

Así, de nuevo vuelve a existir la función BUSCARV, desaparenciendo la función CONSULTAV. Lo mismo ha pasado con otras, tal y como se ve en la tabla, como NSHORA, BUSCARH, RAÍZ, etc.

Por lo tanto, usaremos BUSCARV para versiones anteriores a la 2010. CONSULTAV en la versión 2010 sin Service Pack 1, y BUSCARV otra vez en Excel 2010 + Service Pack 1.

Aquí te dejo un enlace al Service Pack 1 para que puedas instalarlo y la explicación que ha dado Microsoft..

VN:F [1.9.22_1171]
Rating: 7.0/10 (1 vote cast)

Funciones de búsqueda y referencia en Excel 2010: indice y coincidir

Excel dispone de algunas funciones que nos permiten realizar acciones más propias de bases de datos, como son las denominadas funciones de búsqueda y referencia.

En otros tutoriales hemos visto la función CONSULTAV, que reemplaza en excel 2010 a la función BUSCARV de versiones anteriores, y la importancia del parámetro ordenado en esta misma función.

En el artículo de hoy ahondaremos en estas funciones de búsqueda y referencia con el estudio de las funciones INDICE y COINCIDIR.

Hasta cierto punto podrían considerarse que se trata de funciones inversas, ya que la función COINCIDIR nos devuelve la posición de un determinado valor dentro de una tabla, mientras que la función INDICE nos da el valor que está almacenado en una cierta posición.

La sintaxis de la función COINCIDIR es:

=COINCIDIR(valor_buscado; rango_busqueda; coincidencia)

Donde:

  • valor_buscado, es el dato a buscar.
  • rango_busqueda, es el conjunto de valores donde buscaremos
  • coincidencia, puede tomar los valores 1, 0 y -1.
    •  Con el tipo de coincidencia 1 (por defecto) busca el valor más próximo por defecto, por lo que los datos deberán estar ordenados de menor a mayor.
    • Con el tipo de coincidencia 0 busca el valor exacto, por lo que no precisa que los datos estén ordenados.
    • Con el tipo de coincidencia -1 busca el valor por exceso, por lo que los datos tendrán que estar ordenados de mayor a menor.

La sintaxis de la función INDICE es:

=INDICE(rango;fila;columna)

Donde:

  • rango es tabla en la que buscaremos
  • fila es la coordenada de la fila que buscamos
  • columna es la coordenada de la columna

Es decir, la función INDICE extrae un dato de una tabla usando un sistema cartesiano de coordenadas.

Expliquemos estas dos funciones con un ejemplo ilustrativo.

Partimos de la tabla de clasificación de la WTA siguiente:

 Funciones de búsqueda y referencia en Excel 2010: indice y coincidir top15 ranking wta oct12

Queremos determinar (usando la función COINCIDIR) en qué posición del ranking se encuentra la primera jugadora rusa (país = RUS):

= COINCIDIR(“RUS”;D3:D17;0) devolviendo un 3.

Nótese que hay más jugadoras rusas, pero coge la primera coincidencia.

También queremos saber en qué posición estaría una jugadora con 5.000 puntos. Como quiera que no hay ninguna jugadora con 5.000 puntos exactos y la tabla está ordenada de mayor puntuación a menor, usaremos el tipo de coincidencia -1.

=COINCIDIR(5000;E3:E17;-1) devolviendo un 7.

Ahora, usando la función INDICE, queremos saber los puntos que tiene la jugadora que está en el puesto 12.

= INDICE(B3:F17;12;4) devolviendo 3.119

12 es la posición que buscamos, y 4 es la columna donde están los puntos.

Como se ve claramente, la potencia de estas funciones reside en su uso combinado. Por ejemplo, queremos saber el nombre de la primera jugadora rusa:

=INDICE(B3:F17;COINCIDIR(“RUS”;D3:D17;0);2) devolviendo Sharapova, Maria

O, necesitamos los tours que ha jugado la jugadora que tiene 3.500 puntos

=INDICE(B3:F17;COINCIDIR(3500;E3:E17;-1);5) devolviendo 29

VN:F [1.9.22_1171]
Rating: 9.0/10 (1 vote cast)

El parametro ordenado de CONSULTAV en Excel.

Como hemos comentado en otras ocasiones la función CONSULTAV, viene a sustituira a la función BUSCARV, existente hasta la versión excel 2007; en la versión excel 2010 ya no está.

En cualquier caso, el uso de una u otra función es el mismo: ambas sirven para hacer consultas sobre una tabla de datos y su sintaxis es la misma.

Repasando la sintaxis de ambas funciones, nos encontramos con que el primer parámetro es lo que queremos buscar, el segundo es dónde lo queremos buscar y el tercero el número de columna donde se halla el dato a extraer. Hasta aquí todo más o menos fácil.

El problema viene con el cuarto parámetro, opcional, que unos llaman ordenado y otros tipo de coincidencia.

Vamos a explicar claramente para que sirve ese parámetro.

Si el parámetro tiene el valor VERDADERO (o no existe) asume que la tabla está ordenada y que, además, la distribución de los valores es contínua. La función devuelve el mismo valor o, si no lo encuentra, el menor valor más próximo al buscado.

Veamos un ejemplo:

Tenemos una tabla de materiales de aislamiento térmico, en la que se indica que material utilizar en función de la temperatura:

 El parametro ordenado de CONSULTAV en Excel. tabla temperaturas

Como vemos cada producto es válido para un rango de temperaturas. Usando la función CONSULTAV, podemos determinar que material nos hará falta dependiendo de la temperatura que tengamos, para ello tendremos que poner como parámetro tipo de coincidencia, VERDADERO.

La función quedaría de esta manera: = CONSULTAV(Temperatura; A2:B7;2;VERDADERO)

Para los valores de Temperatura, comprendidos entre -15 y 5, la función devuelve PLX-356; para los valores entre 5 y 10, devuelve PLX-356; etc.

Es decir, para que la función sea válida, no es necesario que el valor de Temperatura coincida con uno de los de la tabla, sino que la función aproxima al más cercano por defecto.

Si para este caso en la función CONSULTAV, le ponemos el valor FALSO al último parámetro, nos devuelve un mensaje de error, ya que en ese caso buscaría el valor EXACTO de Temperatura, pero no existe.

Cuando sólo nos sirven valores exactos, usaremos el valor FALSO en Tipo de Coincidencia.

Veamos ahora un ejemplo, en el que es aplicable el valor FALSO al parámetro tipo de coincidencia de CONSULTAV.

Tenemos un listado de productos que se guardan en una tabla según van entrando en nuestro almacén. Llevan un código asociado único para cada producto y, como llegan al almacén en cualquier momento, no están ordenados.

En este caso usaremos el valor FALSO, ya que sólo nos sirve el valor exacto del código, un valor aproximado, quizá no diera mensaje de error, pero lo más probable es que nos devolviese un valor erróneo.

 El parametro ordenado de CONSULTAV en Excel. tabla almacen

La fórmula a usar para extraer al descripción sería: = CONSULTAV(Referencia;A2:D7;2;FALSO)

VN:F [1.9.22_1171]
Rating: 10.0/10 (1 vote cast)

La funcion CONSULTAV

Con Excel pueden realizarse tareas propias de bases datos como por ejemplo, obtener datos de otra tabla para realizar cálculos o, simplemente, un resumen para manejar una tabla más pequeña.

Como ejemplo, partamos de la tabla de valores de la Bolsa del Ibex 35.

 La funcion CONSULTAV cotizacion acciones bolsa

Fuente: bolsamadrid.es  (click para ampliar)

Ahora, imaginemos que disponemos de una cartera con las acciones que se muestran en la tabla:

 La funcion CONSULTAV tabla ejemplo

y queremos saber su valoración actual.

Se trata pues de rellenar esta tabla:

 La funcion CONSULTAV tabla ejemplo 37382527

Para ello, la operación es, en principio, muy simple: bastaría multiplicar el número de acciones por la cotización en cada momento. El problema viene al tener que buscar digamos “a mano” los valores en la enorme tabla principal.

Pues bien, con Excel ese problema ha desaparecido, ya que dispone de funciones de búsqueda para consultar valores de otras tablas para nuestros cálculos.

En la celda C2 ha de aparecer el valor de la cotización actual de las acciones de GAMESA. En la C3 de ABEGOA, en la C4 de IBERDROLA, etc. Para ello usaremos la función CONSULTAV, que en versiones anteriores a EXCEL 2010 se llamaba BUSCARV.

Su sintaxis es la siguiente:

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

donde

  • ValorBuscado sería el nombre de la empresa de la cual tenemos acciones,
  • Rango de Busqueda sería la tabla de las cotizaciones,
  • El indicador de columna sería el número de la columna en la cual se encuentra el dato que queremos obtener, comenzando a contar por la columna más a la izquierda.
  • Tipo de coincidencia: Si se pone VERDADERO o se omite, usará el valor más aproximado (por lo que los valores de la tabla deberán estar ordenados); si se pone FALSO, usará el valor exacto.

Para nuestro ejemplo, y suponiendo que la tabla de valores del IBEX35 comienza en la celda A1 de la hoja Tabla Bolsa, la función quedaría:

= CONSULTAV(A2,’ Tabla Bolsa’!$A$1:$I$36, 2, FALSO)

Hemos utilizado el tipo de coincidencia FALSO, ya que necesitamos exactamente el mismo nombre de la empresa. No nos vale una empresa que se llame parecida.

Se han empleado para el rango de búsquedas las referencias absolutas y para el valor buscado las referencias relativas, para poder escribir las fórmulas en la primera fila y poder copiarlas en las demás.

Para calcular el valor simplemente multiplicamos la cotización por el número de acciones. Para la primera fila quedaría así:

= B2*D2

Luego basta copiar la misma fórmula para el resto de filas.

Nuestra tabla de valores quedaría así:

 La funcion CONSULTAV tabla ejemplo 37382649

 

 

 

 

VN:F [1.9.22_1171]
Rating: 10.0/10 (1 vote cast)