Esta entrada se publicó en Sistemas de Información. a las 10:48:06 03/02/2009

Programa de Factura en Excel… (Nivel Usuario)

Hola que tal, aquí les publico una programita en Excel que les puede servir a algunos, yo lo hice porque me vi en la necesidad y cuando estaba buscando información me encontré en muchos foros donde se enredaban todos y no sabían que hacer… no es que mi programa este hecho súper perfecto pero pues si saca del apuro.. Tratare de explicar solamente los puntos más importantes y si le quieren hacer mejoras adelante que si se les puede hacer y muchísimas la verdad… entonces me pongo a explicarles para no darle más vuelta al asunto

Primeramente vamos a suponer en este ejemplo que es para una juguetería “NIÑO FELIZ”:

Abrimos un documento nuevo de Excel (para este ejercicio voy a usar la versión Office 2007)

Lo primero que podemos hacer es crear una página de inicio que nos enlace a las demás partes de nuestro programa, para una mayor facilidad de navegación colocamos unos iconos (estos los podemos conseguir en cualquier parte en internet pero les dejo este enlace donde hay muchos y muy buenos http://www.iconspedia.com/

Ahora vamos a crear la hoja donde vamos a agregar a nuestros clientes.

Seleccionamos de la celda A1 a la celda G1 y vamos a Inicio en la ficha estilos damos clic en “dar formatos como tablas” y seleccionamos el que más nos agrade (=$A$1:$G$1)

Editamos los campos que nos pone por default de columna 1 columna 2 etc.… y colocamos los datos que nos va a pedir la factura por ejemplo...

ID (esto nos va a servir para identificar a nuestro cliente en la factura)NOMBREDIRECCIONCIUDADR.F.C.TELEFONONOTA

Para los productos hacemos algo similar solo que con diferentes campos claro y en diferente hoja

Para ellos yo use los siguientes campos ID (esto nos va a servir para identificar a nuestros productos en la factura)DESCRIPCIONPRESENTACIONCOSTO

Ok, una vez teniendo esto pues la verdad no hemos realizado nada del otro mundo, pero todo lo que podría decirse difícil (que realmente esta fácil) lo vamos a hacer en la hoja donde estará nuestra factura.

Abrimos una nueva Hoja le ponemos factura (los campos los voy a colocar en base a un modelo de factura que tengo a la mano) ya ustedes los configuran en base a su plantilla de factura ok. Igual los márgenes de impresión, como todas son diferentes así que ese negocio ustedes lo resuelven.

Colocamos la fecha en la parte superior derecha normalmente ahí la ponen… nos posicionamos en la celda y en insertar función colocamos =HOY() para que siempre este la fecha del dia (agarra la fecha de nuestro ordenador cuidado con no tener la fecha bien actualizada nuestro pc)

Ahora para agarrar los datos de un cliente ya registrado y trasladarlos a la factura con solamente el numero ID que le asignamos (para esto ya debimos de haber agregado de perdida a un cliente con su respectivo ID)

Aquí podemos hacer dos cosas, colocarlo en un campo dentro del margen de la factura y luego ocultarlo para que no imprima con nuestra factura o colocarlo fuera del margen de la factura que es lo que voy hacer más o menos nos quedara así: OJO EL ID QUEDO EN J3

Ahora si un poco de lo bueno… para que nosotros cuando coloquemos el ID de nuestro cliente en el campo J3 y automáticamente nos coloque todos los datos correspondientes de este mismo los que ocupamos realmente vamos a hacer lo siguiente:

Para ello vamos a usar la función BUSCARV que este busca un valor específico en la primer columna de

una matriz de tabla y devuelve, en la misma fila, un valor de otra columna de dicha matriz de tabla. La V de

BUSCARV significa vertical. La sintaxis sería la siguiente

BUSCARV(valor_buscado;matriz_buscar_en;indicador_columnas;ordenado), entonces explico cómo está

la cosa en base a nuestro ejemplo… el valor buscado esta en J3 porque es el identificador de nuestro cliente,

y que pasa con la matriz? Vamos hacer algo más fácil para no enredarnos con matrices $A$1:$G$500 o

algo así, nos vamos a nuestra hoja de clientes y seleccionamos el rango donde estarán nuestros

clientes (creo que hasta ahora solo tenemos 1 verdad sino es que un adelantado ya metió toda su

cartera de clientes jajaj está bien)

Algo así será el seleccionar nuestro rango ahora una vez seleccionado (seleccionen hasta abajo como por el número 500 porque si una vez que agreguen a un cliente y queda fuera de este rango no les va a jalar la información) ok. Está seleccionado aun presionamos las teclas Control + F3 y nos abre un administrador de nombres le damos clic en nuevo u en Nombre: ponemos clientes para identificarlo más abajo nos coloca el rango que tenemos seleccionado le damos Aceptar y cerramos el administrador

Ahora si ya tenemos un rango de nuestra búsqueda para la matriz llamada clientes, así va nuestra función ahora =BUSCARV(J3;clientes…. Pero nos falta ahora que campo vamos a jalar de esa

matriz encontrada… para jalar solamente el nombre va a quedar de la siguiente manera =BUSCARV(K4;clientes;2) Porque 2? Porque dentro de nuestra matriz nuestro nombre ocupa la posición 2 ya que el Id es el 1) entonces ya podemos ponernos a jalar los demás datos de nuestro cliente, para el nombre yo lo coloque en la celda A5 y quedo de la siguiente manera ya con todo y formula.

Y así van colocando las demás formulas en las celdas según la factura que van a configurar.para dirección seria así: =BUSCARV(J3;clientes;3) para la ciudad =BUSCARV(J3;clientes;4)para el R.F.C =BUSCARV(J3;clientes;5) (claro si tienen otros datos solamente lo adaptan y listo)

Algo así va a quedarles ya que le coloquemos algún id a nuestro buscador

Con esto estará listo los datos de nuestros clientes.. ahora apara nuestros productos vamos hacer lo mismo, solo que colocaremos en distintas celdas, primeramente vamos a seleccionar el rango claro en nuestra hoja de productos y le damos F3 y lo guardamos con el nombre de productos, es lo mismo que hicimos con clientes pero ahora en productos, ya que tengamos nuestor nombre del rango colocamos la formula que nos quedaría más o menos así con el formato.

Formula =BUSCARV(A12;productos;2)

Y para el precio unitario colocamos la siguiente formula =BUSCARV(A12;productos;4) 4 porque el costo está colocado ahí.

NOTA: Lee todo el párrafo antes de hacer lo que diga el párrafo.

Ahora para no estar colocando la formula en cada celda agarramos primero una por una ok, primero la de descripción y la arrastramos hacia abajo para que sola nos coloque con las celdas correspondientes y luego con la de precio unitario (ya saben cómo se hace eso se posicionan en la celda donde contiene la formula ponemos el cursor en la esquina inferior derecha hasta que nos salga el signo de mas y así lo arrastramos hasta abajo en donde queramos nuestros productos. Bien ya se darán cuenta de que si no colocamos ningún id nos pone en la celda #N/A y no queremos que se nos imprima así la factura para ello lo vamos arreglar de la siguiente manera (esto lo explico paso por paso para que conozcan los problemas que se les pueden presentar)

Para la descripción

=SI(A12="";"";BUSCARV(A12;productos;2))

Habla por sí sola la formula si A12 está vacía entonces que me ponga vacía “” sino pues ya hace la función que venimos utilizando (sencillo verdad)lo mismo para precio unitario

=SI(A12="";"";BUSCARV(A12;productos;4))

Y ahora sí arrastramos las formulas para abajo como se indica arriba.

Ahora para que nos dé el total en la celda correspondiente pues vamos a colocar una función básica de Excel multiplicamos las celdas =B12*E12 (esto ya deben de saber cómo hacerlo ok no lo explicare más) igual lo arrastran la fórmula para abajo para ahorrar tiempo.

Ahora colocaremos las celdas también con formulas básicas en sub-total el iva y el total (el iva lo tengo con el 15% que se usa en México en zonas que no son fronteras) ustedes le colocan el impuesto que quieran.

Ahora algo muy importante de las facturas que es que nuestro total en este caso los 690 pesos nos lo convierta en letras

Para ello quiero aclarar que no me voy a otorgar el crédito del siguiente código que voy a poner aquí, yo hice uno pero la verdad lo vendí porque estaba más adaptado a las necesidades y no lo pongo por lo mismo por respeto a mi cliente, pero aquí está este igual de bueno con la misma función según leí en la web de donde lo saque que el autor es Saúl López con una pequeña modificación de ByPaco² para que aparezca Pesos y M.N., y ahí mismo se le hizo una pequeña modificación al código para que aparezca “SON:” como se usa aquí en México. El código es el siguiente:

Function PesosMN(tyCantidad As Currency) As StringDim lyCantidad As Currency, lyCentavos As Currency, lnDigito As Byte, lnPrimerDigito As Byte, lnSegundoDigito As Byte, lnTercerDigito As Byte, lcBloque As String, lnNumeroBloques As Byte, lnBloqueCeroDim laUnidades As Variant, laDecenas As Variant, laCentenas As Variant, I As Variant 'Si esta como Option ExplicittyCantidad = Round(tyCantidad, 2)lyCantidad = Int(tyCantidad)lyCentavos = (tyCantidad - lyCantidad) * 100laUnidades = Array("UN", "DOS", "TRES", "CUATRO", "CINCO", "SEIS", "SIETE", "OCHO", "NUEVE", "DIEZ", "ONCE", "DOCE", "TRECE", "CATORCE", "QUINCE", "DIECISEIS", "DIECISIETE", "DIECIOCHO", "DIECINUEVE", "VEINTE", "VEINTIUN", "VEINTIDOS", "VEINTITRES", "VEINTICUATRO", "VEINTICINCO", "VEINTISEIS", "VEINTISIETE", "VEINTIOCHO", "VEINTINUEVE")

laDecenas = Array("DIEZ", "VEINTE", "TREINTA", "CUARENTA", "CINCUENTA", "SESENTA", "SETENTA", "OCHENTA", "NOVENTA")laCentenas = Array("CIENTO", "DOSCIENTOS", "TRESCIENTOS", "CUATROCIENTOS", "QUINIENTOS", "SEISCIENTOS", "SETECIENTOS", "OCHOCIENTOS", "NOVECIENTOS")lnNumeroBloques = 1DolnPrimerDigito = 0lnSegundoDigito = 0lnTercerDigito = 0lcBloque = ""lnBloqueCero = 0For I = 1 To 3lnDigito = lyCantidad Mod 10If lnDigito <> 0 ThenSelect Case ICase 1lcBloque = " " & laUnidades(lnDigito - 1)lnPrimerDigito = lnDigitoCase 2If lnDigito <= 2 ThenlcBloque = " " & laUnidades((lnDigito * 10) + lnPrimerDigito - 1)ElselcBloque = " " & laDecenas(lnDigito - 1) & IIf(lnPrimerDigito <> 0, " Y", Null) & lcBloqueEnd IflnSegundoDigito = lnDigitoCase 3lcBloque = " " & IIf(lnDigito = 1 And lnPrimerDigito = 0 And lnSegundoDigito = 0, "CIEN", laCentenas(lnDigito - 1)) & lcBloquelnTercerDigito = lnDigitoEnd SelectElselnBloqueCero = lnBloqueCero + 1End IflyCantidad = Int(lyCantidad / 10)If lyCantidad = 0 ThenExit ForEnd IfNext ISelect Case lnNumeroBloquesCase 1PesosMN = lcBloqueCase 2PesosMN = lcBloque & IIf(lnBloqueCero = 3, Null, " MIL") & PesosMNCase 3PesosMN = lcBloque & IIf(lnPrimerDigito = 1 And lnSegundoDigito = 0 And lnTercerDigito = 0, " MILLON", " MILLONES") & PesosMNEnd SelectlnNumeroBloques = lnNumeroBloques + 1Loop Until lyCantidad = 0PesosMN = "SON: (" & PesosMN & IIf(tyCantidad > 1, " PESOS ", " PESO ") & Format(Str(lyCentavos), "00") & "/100 M.N.)"End Function

Esto lo vamos a colocar en nuestro editor de Visual basic, abrimos con ALT + F11

Nos vamos a Insertar luego a Modulo y pegamos ahí nuestro código y cerramos el editor.

Ahora para colocar esta función en nuestra factura colocamos lo siguiente fórmula en la celda donde queramos que nos aparezcan las letras:

=PesosMN(F48)

Donde F48 es la celda que contiene el numero a convertir

Y listo!!! Por el momento ahora el demás trabajo de configurarlo les toca a ustedes, estuvo sencillo verdad, solo que lo explique como dice el titulo a nivel usuario para que pudieran comprender muchas cosas y puse algunas pantallas para facilitarles el trabajo también, pues ya saben que lo pueden mejorar muchísimo pero por algo pueden empezar para aquellos que andan muy perdidos, cualquier duda sugerencia o comentario ya me lo pueden dejar aquí en el blog o me mandan un mail a angel.garibay@gmail.com con gusto les responderé..

Suerte!!!!

NOTA: les dejo el archivo ya terminado y el código aparte por si solamente lo quieren usar eso… ok.

Un saludo que estén bien