Numerador automático para facturas o recibos en Excel

Con Excel es fácil crear plantillas para facturas o recibos. Más aún, se pueden descargar gratuitamente de varios sitios. La plantilla del ejemplo que usaré en esta nota fue descargada del sitio de Microsoft.

Actualización 25/08/2014: nuevo modelo mejorado

Una de las consultas que recibo frecuentemente, es cómo crear un numerador automático para facturas o recibos en hojas de Excel. Si observan la plantilla de la factura, verán que la celda C5 contiene el número de factura.




Lo que queremos hacer es que este número se actualice cada vez que emitimos una factura, de manera que la siguiente tenga el número consecutivo (el archivo con el ejemplo se puede descargar aquí aunque recomiendo el nuevo modelo que incluye base de datos de las facturas producidas).

Esto se puede hacer de varias maneras, pero todas implican usar macros.

Una solución sencilla es agregar un botón al que le asociamos una macro. El botón lo creamos copiándolo de la barra de formularios.



Pulsamos el botón Nuevo, lo que abre un módulo de Vba con el evento Sub Botón3_AlHacerClic(). Aquí escribimos este código

[C5] = [C5] + 1



Ahora, cuando apretamos el botón el valor en la celda C5 se incrementará en 1.

Esta técnica es muy sencilla, pero no ofrece ninguna ventaja frente al sencillo método de cambiar el valor en la celda manualmente. Además, si nos olvidamos de presionar el botón, la próxima factura saldrá con el mismo número.

Una forma de lograr algún tipo de control es ligar el botón a la acción de sumar los importes de la factura. Es decir, hasta que no apretemos el botón no aparecerá la suma total de la factura en la celda correspondiente (la celda C35 en nuestra plantilla).

Empezamos por eliminar la fórmula en la celda C35. Teniendo en cuenta que el rango de los montos de las líneas de la factura es C18:C34, modificamos nuestro código, para que sume los valores de las celdas del rango y luego cambie el número de factura

Sub Botón3_AlHacerClic()
[C35] = WorksheetFunction.Sum(Range("C18:C34"))
[C5] = [C5] + 1
End Sub



Otra alternativa es que el número de factura cambie automáticamente en las circunstancias debidas, por ejemplo antes de imprimirla.

Para esto debemos usar un tipo de macro especiales llamadas "eventos".
Eventos son macros que actúan cuando, como su nombre lo sugiere, algo sucede en la hoja, o el objeto, al cual están ligadas. Los eventos serán tema de una futura nota, como vengo prometiendo. En esta nota nos limitaremos a un ejemplo práctico.

Para saber qué eventos existen ligados al objeto, en nuestro caso el cuaderno que contiene la hoja con la factura, pasamos al editor de Vba y hacemos doble clic al icono ThisWorkbook,



Abrimos la lista desplegable de la ventanilla donde aparece General y elegimos Workbook



Abrimos la ventanilla contigua para ver qué eventos están a nuestra disposición



Como podemos ver, existe un evento BeforePrint, es decir "antes de imprimir". Ponemos el código anterior en este evento

Private Sub Workbook_BeforePrint(Cancel As Boolean)

[C35] = WorksheetFunction.Sum(Range("C18:C34"))

[C5] = [C5] + 1

End Sub

Ahora, cuando queramos imprimir la factura, ya sea con el icono de impresión o con el menú Archivo-Imprimir, se disparará el evento, el número de la factura aumentara en 1 y el total será recalculado.
Para mejorar nuestro evento, podemos incluir la posibilidad de cancelar la impresión. Lo que haremos es agregar algunas líneas de código


Private Sub Workbook_BeforePrint(Cancel As Boolean)
    Dim Mensaje, Resp
    Dim dlgPrint As Boolean
    
    Mensaje = "El total es " & [C35] 'Total
    Mensaje = Mensaje & " Imprimir?"
    Resp = MsgBox(Mensaje, vbQuestion + vbYesNo)

    On Error GoTo errNoPrint

    If Resp = vbYes Then
    Application.EnableEvents = False
    [C5] = [C5] + 1
     dlgPrint = Application.Dialogs(xlDialogPrint).Show
        If dlgPrint = False Then
            [C5] = [C5] - 1
            Cancel = True
            Application.EnableEvents = True
            Exit Sub
        End If
    Else
        Cancel = True
        Application.EnableEvents = True
    End If

    Application.EnableEvents = True

    
    Exit Sub

errNoPrint:
[C5] = [C5] - 1
Cancel = True
Application.EnableEvents = True
End Sub


Primero calculamos el total; luego producimos un mensaje donde exponemos el total y damos la opción de cancelar la impresión



Si el usuario aprieta No, el proceso de impresión se detiene y el número de la factura no cambia.

Como se puede ver, con algunas líneas de código puestas en el lugar correspondiente, podemos lograr una aplicación bastante práctica. El cuaderno con la factura puede descargarse aquí

Si usamos el evento BeforePrint, el botón es innecesario y puede ser eliminado.



Technorati Tags: MS Excel

Distribuir datos en hojas Excel sin macros.

Supongamos esta situación: una empresa de transporte lleva el registro de los viajes un cuaderno Excel con varias hojas. En la primera se anotan los datos (fecha, destino, conductor y ruta). Por cada ruta hay una hoja. Nuestro objetivo es que al anotar los viajes en la primer hoja, éstos aparezcan automáticamente en la hoja correspondiente por ruta.

Esta es la tabla que aparece en la primer hoja (que llamaremos "Datos")

Queremos que cada línea aparezca en otra hoja. Los viajes de la ruta 1 en una hoja que se llamará "Ruta 1", los de la ruta 2 en "Ruta 2", etc.

Hay dos formas más o menos inmediatas de resolver el problema: macros y tablas dinámicas. Con tablas dinámicas no estaríamos distribuyendo los datos, pero podríamos generar con facilidad listas por ruta.

Pero el desafío es hacerlo con fórmulas. No es que esté aburrido y no tenga lo que hacer. Pero curiosamente he recibido varias veces esta consulta en las últimas semanas. A veces se trata de transporte por rutas, como en el ejemplo, a veces una escuela de fútbol que lleva un registro de alumnos por edad, a veces una tienda que quiere manejar el inventario por tipo de producto.

Finalmente decidí aceptar el desafío, a pesar que la solución que propondré más adelante no es eficiente como el uso de tablas dinámicas o macros.

El primer paso es crear tres hojas, una para cada ruta, donde pondremos las fórmulas que reflejaran los datos correspondientes de la hoja Datos. En estas hoja ponemos los mismos encabezamientos como en Datos, pero agregamos una columna, "Orden"



En la celda A2 ponemos esta fórmula: =SI(Datos!$D2=1,Datos!A2,"")
Esta fórmula trae el contenido de la celda A2 de Datos, si el valor de la columna D de Datos es 1, es decir, la ruta 1. En la Hoja Ruta 2 copiamos la misma fórmula, pero cambiamos el argumento Datos!$D2=1 por Datos!$D2=2.

Copiamos la fórmula en las celdas B2 y C2 (prestar atención a los símbolos $ en las direcciones de la celda) y luego al rango A3:C11.
Como se puede ver, los valores que aparecen en el rango son los de la ruta 1. Sino, aparece una celda en blanco.

En la celda D2 ponemos esta fórmula, que ya explicamos en la nota sobre ordenar texto en Excel con fórmulas,

=CONTAR.SI($A$2:$A$11,"<="&A2) y la copiamos al rango D3:D11.

Como se puede apreciar, esta fórmula hace las veces de la función JERARQUIA para textos. Si bien las fechas son números, y por lo tanto podríamos usar JERARQUIA, el problema surge con las celdas en blanco. Por ese motivo usamos la fórmula señalada.


Ahora creamos una segunda tablas en el rango F1:H11. En la primer fila copiamos los encabezamientos. En la celda F2 ponemos esta fórmula

=INDICE($A$2:$E$11,COINCIDIR(FILA()-1,$D$2:$D$11,0),COINCIDIR(F$1,$A$1:$C$1,0))

que copiamos al rango F2:H11



En la nueva tabla, las líneas que pertenecen a la ruta aparecen ordenadas por fecha, y las que pertenecen a otras rutas aparecen como #N/A. Para mejorar la apariencia de la lista podemos aplicar Formato Condicional



dándole color blanco a la fuente de la celda que cumple la condición, para hacer "desaparecer" el resulta #N/A. También hemos ocultado las columnas A:E.

¿Cómo funciona la fórmula con al función INDICE? Esta función da como resultado el valor de una matriz que se encuentra en la celda determinada por el valor del segundo argumento (fila) y del tercer argumento (columna). La matriz es el rango A2:E11, que contiene también la columna Orden.
La fila es determinada por la función COINCIDIR(FILA()-1,$D$2:$D$11,0), que encuentra en que lugar del rango D2:D11 se encuentra la fila con el número de orden 1, 2 etc. El número de orden es determinado por el número de fila de la celda que contiene la fórmula, menos 1. Así, en la fila 2 aparecerán los datos que tienen el número de orden 1, en la fila tres los que tienen el número de orden 2, y así sucesivamente.
La segunda función COINCIDIR, hace que los datos en la celda correspondan a la columna indicada.

Como verán, hemos encontrado una solución con fórmulas al problema. Pero esta solución tienen dos problemas importantes, si queremos trabajar con gran cantidad de datos:
- la función FILA(), es volátil y causa que cada cambio en la hoja provoque un recálculo de toda lo hoja;
- INDICE y COINCIDIR tienden a ser lentas cuando trabajamos con gran cantidad de datos.

El archivo se puede descargar aquí

Technorati Tags: MS Excel

Cálculos con pies (feet) y pulgadas (inches) en Excel.

Hace unos días me consultaba un lector sobre cómo hacer cálculos en pies (feet) y pulgadas (inches) con Excel.
Debo confesar que nunca me había planteado esta pregunta. Dado que despertó mi curiosidad, en definitiva en los Estados Unidos siguen usando estas medidas, empecé por informarme un poco sobre el tema.
Como casi siempre en estos casos, empezamos por Wikipedia. Así nos enteramos que 1 pie (foot) equivale a 12 pulgadas (inches), la que se suele escribir: 1' = 12". Las fracciones de pulgada más comunes son: 1/2", 1/4", 1/8", 1/16", 1/32" y 1/64". Ciertas fracciones comunes son expresadas en su forma reducida, por ejemplo 6/32" es presentada como 3/16".
Según la nota de Wikipedia, las equivalencias también se presentan en forma decimal. 1/2" = .5, 1/4"=.25, 1/8"=.125, 1/16"=.0625, 1/32"=.03125 y 1/64"=.015625; de ahí, el resto de las fracciones, por ejemplo: 3/8"=.375 ó 63/64"=.984375.

Volviendo a la consulta de mi lector, se trataba de sumar 3'-4 1/2"+ 2'-2 5/8. La primera dificultad para los que crecimos y vivimos en un mundo métrico, es entender la notación. El primer número en la suma es, en letras, tres pies y cuatro y media pulgadas; el segundo es dos pies con dos cinco y ochoavos pulgadas.

Tras una breve búsqueda en la Internet, llegué al sitio de John Lacher quien ofrece descargar una función UDF para convertir pies y pulgadas a medidas métricas.

Otra alternativa es usar funciones nativas de Excel y columnas auxiliares, como mostraremos en esta nota.

Para resolver el problema empezaremos por reducir todo a pulgadas, el mínimo común denominador. En el primer número 3 pies equivalen a 36 pulgadas; así que todo el número expresado en pulgadas es 40.5. El segundo equivales a 26.65 pulgadas (2*12 + 2 + 5/8 = 24+2+0.625).

Como vemos, la conversión no es problemática. El problema es cómo indicarle a que Excel cuál es la parte del número expresado en pies y cuál en pulgadas.

Para esta tarea tendremos que recurrir a funciones texto. Empezamos por construir una plantilla




En la celda B2 ponemos esta fórmula =IZQUIERDA(A2;ENCONTRAR("'";A2)-1) y la copiamos en la celda B3. El resultado es un texto. Para poder aplicar más adelante operaciones matemáticas los convertimos en número usado la función VALOR



Para extraer la parte de pulgadas usamos la función EXTRAE combinada con la función LARGO:

=VALOR(EXTRAE(A2;ENCONTRAR("-";A2)+1;LARGO(A2)-ENCONTRAR("-";A2)-1))



Finalmente, para calcular el total de pulgadas usamos en D2 la fórmula =B2*12+C2 y la copiamos a D3. Calculamos los totales para cada columna



Todo lo que nos queda es reducir el resultado 67,125 pulgadas a su expresión en pies y pulgadas. Para esto usamos las funciones ENTERO y RESIDUO y TEXTO. En la celda D5 ponemos la fórmula

=ENTERO(D4/12)&" - "&TEXTO(RESIDUO(D4;12);"# ??/??")

Usamos la función texto, ya que tenemos que unir el resultado de la función ENTERO, que es numérico, con la parte fraccional que es texto.



A quien tenga que hacer frecuentemente cálculos con pies y pulgadas, le convendrá sin dudas descargar alguna función de la Internet, como la que ofrece Lacher.

Technorati Tags: MS Excel