Administración de horas de trabajo en Excel.

Supongo que si se hiciera una estadística sobre los usos de Excel, las herramientas para la administración de horas de trabajo figurarían entre los primeros lugares.
Excel está "hecho a medida" para estas tareas, pero para construir este tipo de herramientas tenemos que comprender primero como maneja Excel el tema de las fechas y las horas.
En esta nota haremos una revisión integral del tema y daremos algunos ejemplos prácticos.

Excel considera las fechas y las horas como números sucesivos de una serie. Ésta comienza el 1ro. de enero de 1900, fecha a la Excel le asigna el número 1. El 2 de enero está representado por el número 2 y así sucesivamente.
Las horas son la parte decimal del número. Un día completo son 24 horas, de manera que las 12 del mediodía está representado por el número 0.5; las seis de la mañana por el 0.25, las 6 de la tarde por el 0.75, etc. En este momento es el 9 de febrero a las 9:50 de la mañana. El resultado de la función AHORA() es 09/02/2008 09:50:36



Si cambiamos el formato de la celda a "General" veremos el número 39487.4101446759



donde 39487 es el número de días transcurrido desde el 01-01-1900 hasta hoy y 0.4101446759 resulta de dividir 35346 segundos (el equivalente de 9 horas, 50 minutos y 36 segundos) por 86400 (la cantidad de segundos que hay en un día).

Otro aspecto importante a tomar en cuenta, antes de abocarnos a la tarea de construir herramientas para administrar horas, es el formato de números en Excel y el de las fechas y horas en particular. Cambios de formato no alteran el número sino como es representado por Excel en la pantalla. Supongamos una tabla donde ponemos el comienzo de una tarea, las horas a trabajar y el resultado será la hora de finalización



Para ver el resultado, debemos cambiar el formato de la celda, que ha sido "heredado" de la celda A2, por el formato hh:mm



Extraño, ¿no? Para entender este resultado volvemos a cambiar el formato de las celdas a "General"



Sucede que cuando ingresamos 15:30 en la celda A2, Excel la interpreta como el 1ro. de enero de 1900 a las 15:30. Al poner 8 en la celda B2, por ser un número entero, es interpretado como 8 días. El resultado en la celda C2 será el 8 de enero de 1900 a las 15:30. Esto lo podemos ver cambiando el formato de las celdas a dd/mm/yyyy hh:mm



Para evitar resultados erróneos debemos ingresar los datos de horas con formato horario.

En este ejemplo hemos ingresado el dato en la celda B2 como 08:00, lo que Excel interpreta como 8 horas, y por lo tanto el resultado es el esperado



Hay otras cuestiones a considerar cuando trabajamos con horas y fechas en Excel, como el manejo de resultados negativos, suma de horas por encima de 24 horas, distintos sistemas de fechas y más. Todo esto ha sido tratado en distintas notas del blog. Todas las notas sobre fechas y horas pueden verse pulsando el enlace de las etiquetas en la columna izquierda del blog



Consideremos ahora este ejemplo



Todas las celdas de la tabla tienen el formato hh:mm. El Total Bruto es la cantidad de horas transcurridas desde el ingreso hasta la salida, sin tomar en cuenta el descanso. La fórmula de Total bruto es =(E5-B5)+(E5<B5)*24. El objetivo de la expresión (E5<B5)*24 es corregir el resultado cuando la hora del final es menor que la del principio. La explicación completa de esta fórmula la encontrarán en la nota sobre diferencia de horas en Excel. Más adelante veremos que esta fórmula tiene un serio inconveniente.

La fórmula de horas trabajadas es =F5-((D5-C5)+(D5<C5)*24), donde nuevamente usamos la expresión (D5<C5)*24 para corregir el error que se genera cuando la salida sucede al día siguiente de la entrada.
La columna Horas Regulares la calculamos con esta fórmula: =MIN(G5,$E$1) donde $E$1 es la celda que contiene la cantidad de horas regulares (en nuestro caso 8). La función MIN nos da el mínimo entre la jornada regular y las horas trabajadas.
Para calcular las horas extras usamos la fórmula =MAX(0,G5-H5). Es decir, el máximo entre 0 y la diferencia entre las horas trabajadas y las regulares.

Supongamos ahora que queremos totalizar el total de horas del día. Como ya hemos explicado, las celdas del total tendrán el formato [h]:mm.



Como ven, los resultados parecen incorrectos! Esto se debe a que nuestra fórmula de corrección no nos sirve en este caso. En lugar de la fórmula =(E5-B5)+(E5<B5)*24 en Total Bruto, usaremos la fórmula =RESIDUO(E5-B5,1) en la columna de Total bruto y la fórmula =F5-(RESIDUO(D5-C5,1)) en la Horas trabajadas.
Ahora los totales son correctos




Technorati Tags: MS Excel

Comentarios en celdas de Excel sin nombre de usuario.

En una nota anterior sobre comentarios en celdas, mostramos las ventajas de usar la barra de herramientas Revisión.
Al insertar un comentario, Excel pone automáticamente el nombre del usuario en el formulario de la nota. Este nombre es el que figura en la pestaña General del menú Opciones




A veces queremos usar el comentario sin que aparezca el nombre del usuario. Por lo general este dato no tiene ninguna importancia y sólo ocupa lugar en el formulario.
Si quitamos el nombre del usuario en el menú Opciones, descubriremos que Excel trae el nombre de usuario que se ha asignado en Windows.
Borramos el nombre de usuario, dejando la casilla en blanco y pulsamos Aceptar



Al poner un comentario veremos que aparece un nombre de usuario en el formulario



Si abrimos nuevamente el menú de Opciones, veremos que Excel se ha preocupado de insertar el nombre de usuario de Windows.

Lo que podemos hacer es recurrir a una macro. Programamos dos eventos, a nivel del cuaderno (ThisWorkbook). Para hacer el trabajo más eficiente, programamos el evento BeforeDoubleClick, pero a nivel del cuaderno para que esté disponible para todas las hojas

Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As
Range, Cancel As Boolean)
   With Target
     .AddComment
     .Comment.Visible = True
     .Comment.Shape.Select
   End With
End Sub

Esta macro abre un comentario en la celda activa al hacer doble clic con el botón izquierdo del mouse.
Para evitar tener que cerrar el comentario manualmente, programamos, también a nivel del cuaderno, el evento SelectionChange,


Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
   Application.DisplayCommentIndicator = xlCommentIndicatorOnly
End Sub


Los comentarios se abrirán ahora sin nombre de usuario.

Technorati Tags: MS Excel

Agregar datos en la primer fila libre de la tabla

Excel no es la herramienta más adecuada para manejar bases de datos. Pero cuando se trata de cantidades limitadas de datos y sin vínculos complicados, Excel puede ayudarnos.

Por lo general, lo que hacemos es construir en una hoja una tabla (o lista) a la cual iremos agregando datos. También podemos crear una especia de formulario en otra hoja usando controles y también usar validación de datos.

Un ejemplo de este tipo de "aplicación" ya hemos mostrado en la nota Agrupar controles botones de opción en hojas Excel.

Una de las consultas que recibo con cierta frecuencia es: "cómo hago para que al agregar datos a mi base de datos, lo haga en la primer fila libre de la tabla?"

El archivo del ejemplo de la nota mencionada incluye una macro (de hecho dos, una para cada método de crear el formulario) que realiza esa tarea.

La macro es muy sencilla y fácil de adaptar a todo tipo de aplicaciones. Para los lectores sin experiencia en Vba, daremos aquí una explicación de la macro.


Sub clientes_form()
    Dim linea_libre As Long

    Application.ScreenUpdating = False

    Sheets("Clientes").Select
       linea_libre = WorksheetFunction.CountA(Range("A:A")) + 1
       Cells(linea_libre, 1).Value = Sheets("Formularios").[I2]
       Cells(linea_libre, 2).Value = Sheets("Formularios").[I3]
       Cells(linea_libre, 3).Value = Sheets("Formularios").[I4]
    Sheets("Formularios").Select

    Application.ScreenUpdating = True

    MsgBox "Se ha agregado el cliente " & Sheets("Formularios").[I2]

End Sub

Empezamos declarando una variable que contendrá el número de la primer fila disponible

Dim linea_libre As Long

Para calcular cuál es la primer fila libre, seleccionamos la hoja Clientes

Sheets("Clientes").Select

y usamos en el código la función CONTARA de Excel para establecer el valor de la variable

linea_libre = WorksheetFunction.CountA(Range("A:A")) + 1

Luego fijamos los valores de las celdas en clientes

Cells(linea_libre, 1).Value = Sheets("Formularios").[I2]
Cells(linea_libre, 2).Value = Sheets("Formularios").[I3]
Cells(linea_libre, 3).Value = Sheets("Formularios").[I4]


y volvemos a la hoja Formulario

Sheets("Formularios").Select

Para evitar que Excel "parpadee" durante la corrida de la macro, usamos la orden Application.ScreenUpdating = False y al final de la macro, antes de la aparición el mensaje, reponemos Application.ScreenUpdating = True.

Esta macro está ligada al botón "Agregar Cliente" en la hoja Formulario.

Technorati Tags: MS Excel

Operaciones con colores en Excel

En la nota Usando funciones XLM (Excel 4) en hojas de cálculo, mostraba cómo se podía obtener el número de color del fondo de una celda. Esto implicaba el uso de "macrofunciones" (funciones del lenguaje de macro XLM que existió hasta la versión 4) dentro de nombres. Esta técnica nos permitía realizar operaciones como sumar o contar, basándonos en los colores del fondo de un rango de celdas.
No tenía intenciones de volver sobre el tema, hasta que hace unos días un compañero de trabajo me manda un cuaderno Excel con una lista de cerca de 4.000 clientes de la empresa. La tabla estaba ordenada alfabéticamente y cada nombre de cliente tenía un color distinto de acuerdo a las condiciones de crédito (al contado: azul; 30 días: verde; 60 días: amarillo; etc.). Su tarea era sumar los saldos de los clientes por condiciones de crédito y calcular el promedio, para lo cual había que contar el número de clientes en cada grupo.
Después de señalarle que esa hoja era uno de los mejores ejemplos de lo que no se debe hacer en Excel (preferir la estética a la utilidad) intenté explicarle la técnica a usar con las macrofunciones. Como podrán imaginar mi compañero no estaba del mejor ánimo para explicaciones, después de haber invertido horas en poner fondos de color por tipo de crédito para descubrir al final que no puede hacer nada con la lista.
Decidí que lo mejor sería escribir unas UDF (funciones definidas por el usuario) que hagan la tarea.
Empezamos por una función que de cómo resultado el color del fondo de la celda:

Function extraer_color(miCelda As Range)
    extraer_color = miCelda.Interior.ColorIndex
End Function

Aplicamos la fórmula a algunas celdas con fondo de color

La celda A6 no tiene ningún fondo y de ahí el resultado. Podemos cambiar el código de esta manera para que en caso de no haber fondo el resultado sea 0

Function extraer_color(miCelda As Range)
   Select Case miCelda.Interior.ColorIndex
     Case xlNone
       extraer_color = 0
     Case Else
       extraer_color = miCelda.Interior.ColorIndex
   End Select
End Function

Nuestra próxima función nos permitirá contar por color:

Function contar_por_color(RangoColor As Range, CeldaColor As Range)
 Dim rngCelda As Range

 For Each rngCelda In RangoColor
  If rngCelda.Interior.ColorIndex = CeldaColor.Interior.ColorIndex Then
   contar_por_color = contar_por_color + 1
  End If
 Next
End Function

La función tiene dos variables: RangoColor, que es el rango dónde queremos contar por color y CeldaColor, que es la celda que contiene el color del criterio.

Finalmente, una función para contar por color:

Function sumar_por_color(RangoColor As Range, CeldaColor As Range, RangoSumar As Range)
Dim rngCelda As Range
Dim colOffset As Long

colOffset = RangoSumar.Column - RangoColor.Column

For Each rngCelda In RangoColor
If rngCelda.Interior.ColorIndex = CeldaColor.Interior.ColorIndex Then
sumar_por_color = sumar_por_color + rngCelda.Offset(0, colOffset).Value
End If
Next
End Function



En esta función usamos un tercer argumento para el rango que contiene los valores a sumar. Los rangos RangoColor y RangoSumar deben tener la filas en común.
La variable colOffset calcula la distancia, en número de columnas, entre el rango con los valores y el rango con los fondos de color. Naturalmente, este número puede ser positivo, si los números están a la derecha de los colores, o negativo si lo están a la izquierda.

El cuaderno con las funciones puede descargarse aquí

Technorati Tags: MS Excel

Programación de eventos en Excel – Segunda Nota

En la primera nota sobre programación de eventos en Excel vimos la importancia y el poder de esta herramienta. En esta nota daremos algunos ejemplos, poniendo el énfasis al aspecto práctico. Pero empecemos por señalar algunas características importantes de los eventos.
Existe una jerarquía de eventos:

Eventos de la aplicación (Application events)

Eventos del cuaderno (Workbook events)

Eventos de la hoja (Worksheets events)

En el tope de la jerarquía están los eventos de la aplicación, luego los del cuaderno y finalmente los de la hoja. Cada objeto contiene sus propios eventos y los de de los objetos que se encuentran por debajo de él en la jerarquía. Por ejemplo el cuaderno tiene un evento Worksheet_Change que responde a cada cambio en una celda de la hoja. El cuaderno, a su vez, tiene un evento Workbook_SheetChange que responde a un cambio en una celda de cualquiera de las hojas del cuaderno. Ante un cambio en una celda, los eventos de los tres niveles entran en acción.

Ciertas acciones disparan más un evento, pero en cierto orden preestablecido. Por ejemplo, agregar una nueva hoja a un cuaderno (la acción) dispara una serie de eventos exactamente en este orden:

Workbook_SheetDeactivate

Workbook_SheetActivate

Workbook_NewSheet

Un tercer punto, y el más importante del punto de vista práctico, es cómo evitar generar "loops" infinitos al programar eventos. Por ejemplo, consideremos este código

Private Sub Worksheet_Change(ByVal Target As Range)
Target.Value = Target.Value + 1
End Sub

Ante un cambio en la hoja, el valor de la celda activa (Target) aumenta en 1. Esta acción en sí misma constituye un cambio, lo que produce un evento Change que vuelva a agregar 1 al valor de la celda. Y así sucesivamente hasta el infinito (o hasta que pulsemos Ctrl+Break).
Si queremos ejecutar este código sin entrar en un "loop" infinito, debemos usar la propiedad EnableEvents de la Aplicación. En nuestro ejemplo:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Target.Value = Target.Value + 1
Application.EnableEvents = True
End Sub

Primero llevamos el valor de la propiedad a False, ejecutamos la (o las) línea y volvemos a poner el valor de EnableEvents a True. Esto es importante, ya que el valor de la propiedad no vuelve al valor de defecto (True) una vez concluido el código.

Ya hemos visto algunos ejemplos de eventos en la nota anterior y también en la nota sobre el numerador automático de facturas.

Ejemplos de eventos de cuaderno (Workbook events):

Seleccionar la celda A1 al activar una hoja

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
   [A1].Select
End Sub

Dado que usamos un evento a nivel de cuaderno, al seleccionar cualquier hoja, la celda A1 será la celda activa (excepto que seleccionemos una hoja de gráfico, en cuyo caso ocurrirá un error).

Al agregar una nueva hoja al cuaderno, le fecha y hora aparecen en la celda A1

Private Sub Workbook_NewSheet(ByVal Sh As Object)

If TypeName(Sh) = "Worksheet" Then
   [A1] = "La hoja fue agregada el " & Now
End If

End Sub

Ejemplos de eventos de hoja

En la nota anterior mostramos un ejemplo de Worksheet_Change. Otro ejemplo interesante de este evento es el siguiente que pone un fondo de color a la fila y la columna de la celda activa

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Cells.Interior.ColorIndex = xlNone
 With Target
   .EntireRow.Interior.ColorIndex = 35
   .EntireColumn.Interior.ColorIndex = 35
 End With
End Sub

Otros ejemplos pueden encontrarse en notas anteriores en este blog, como Limitar el área de trabajo en Excel o en cómo crear un menú en Excel.

Technorati Tags: MS Excel

Programación de Eventos en Excel - Primera Nota

En mi nota sobre cómo crear un numerador automático para factura en Excel mostraba el uso de eventos en esta tarea. Ya en notas anteriores había mostrado o mencionado el uso de esta herramienta y también prometido que escribiría una nota sobre el tema. Aquí está la nota.

Debemos distinguir entre eventos y programación de eventos. Evento es, como su nombre lo indica, algo que ocurre en Excel. Programar un evento significa que hemos escrito una rutina que será ejecutada cuando ocurra el evento.

Excel monitorea constantemente lo que ocurre en la aplicación. Cuando existe un código asociado a un evento, este es disparado al darse las condiciones del evento.

Por ejemplo, en la nota mencionada más arriba, hacíamos uso del evento BeforePrint. A este evento le habíamos asociado una macro que colocaba un número consecutivo cuando el usuario decidía imprimir la factura.
En ese ejemplo el evento es iniciado por el usuario. Eventos pueden ser iniciados también por macros.

Existen eventos para casi todos los objetos de Excel. Podemos clasificar los eventos de la siguiente manera:

• Eventos del cuaderno (Workbook events): responden a acciones en un cuaderno en particular. Por ejemplo Workbook_Open, que ocurre cuando abrimos un cuaderno.

• Eventos de las hojas (Worksheet events): responden a acciones en una hoja en particular. Por ejemplo Worksheet_Calculate, que ocurre cuando la hoja es recalculada.

• Eventos de gráficos (Charts events): responden a acciones en un gráfico en particular.

• Eventos de la aplicación (Application events): responden a acciones a nivel de la aplicación (es decir, Excel). Un ejemplo es WorkbookBeforeClose, que ocurre cuando iniciamos el proceso de cerrar un cuaderno.

• Eventos de formularios/controles (Userform events): por ejemplo, el botón de la barra de formulario tiene un evento Button_Click.

Existen distintas formas de investigar qué eventos existen para cada objeto. Por ejemplo, al poner un botón de la barra de formularios en una hoja de Excel se abre el diálogo Asignar macro

Si apretamos Aceptar, Excel abrirá el editor de Vb con una rutina Botón2_AlHacerClic() sin líneas.



Si escribimos esta macro



cada vez que apretemos el botón ("al hacer clic"), el valor de la celda C1 se incrementa en 1.

En este caso la macro está ubicada en un módulo corriente. Más adelante veremos que no siempre es así.

Hay varias formas de investigar qué eventos existen para cada objeto. Por ejemplo, podemos usar el Examinador de Objetos (en el editor de Vb, apretar F2). En la ventana Clases elegimos el objeto que queremos investigar (Worksheet en nuestro caso) y en la ventana Miembros de Worksheet nos deslizamos hasta que vemos nombres con un rayo amarillo a la izquierda



Esto nos permite ver los eventos disponibles para la hoja.

Otra forma es seleccionar el objeto en la ventana Explorador de proyectos con un doble clic. Si hacemos esto con la Hoja1, se abre un módulo especial asociado con esta hoja. Abrimos la lista de la ventanilla donde aparece General



y elegimos Worksheet. Excel pone automáticamente una Sub Worksheet_SelectionChange(ByVal Target As Range) sin líneas. Ahora podemos abrir la lista de la ventanilla de los eventos y elegir alguno de ellos



Señalemos que los nombres y los parámetros de los eventos están predeterminados y no deben ser cambiados.

Veamos ahora algunas técnicas útiles. Queremos que cada vez que el usuario abre un determinado cuaderno aparezca un mensaje que le recuerde realizar cierta tarea. Obviamente, este evento pertenece al libro (Workbook). En el editor de Vb, apretamos el icono ThisWorkbook y elegimos el evento Open. Escribimos una rutina como ésta



Guardamos el cuaderno. Al abrirlo, veremos el mensaje



Varios de los eventos de la hoja tienen una variable llamada Target. De acuerdo al evento, esta variable está definida como Rango, como en el evento Change


Sub Worksheet_Change(ByVal Target As Range)

o como enlace (Hyperlink)

Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)

e inclusive como tabla dinámica

Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable).

En el caso de rangos, Target se refiere a cualquier celda de la hoja. Por eso, cuando trabajamos con alguno de estos eventos, surge la necesidad de definir cuál es el rango relevante.
Supongamos que en una hoja tenemos un rango llamado Ventas, B2:B13. Queremos que cada vez que el usuario ingrese las ventas de un mes, aparezca un mensaje que le muestre el total acumulado. Esto significa que el evento debe reaccionar cuando cambian los datos en el rango.
En este caso usamos el evento Change, al que le asociamos este código

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim rngVentas As Range, vntTotal As Double

  Set rngVentas = Range("Ventas")
  vntTotal = WorksheetFunction.Sum("Ventas")

    If Union(Target, rngVentas).Address = rngVentas.Address Then
      MsgBox "Tottal acumulado de ventas " & vntTotal
    End If

End Sub

Cada vez que ingresamos un dato en el rango B2:B13, recibiremos un mensaje como este



La clave en esta rutina es el uso de la función Union. Esta función evalúa si la dirección de Target (la celda activa donde introducimos el dato) coincide con alguna de las direcciones del rango Ventas. En caso afirmativo, se dispara el evento que calcula el total y muestra el mensaje.
En la línea Set rngVentas = Range("B2:B13") es más conveniente usar la sintaxis Set rngVentas = Range("ventas"), usando el nombre definido "Ventas".

En la próxima nota tocaremos otros aspectos de los eventos, como jerarquía de eventos, cómo evitar "loops" y agregaremos algunos ejemplos más.

Technorati Tags: MS Excel

Distribuir datos en hojas Excel con macros.

En la nota anterior sobre cómo distribuir datos de una hoja a otras hojas de un cuaderno Excel, vimos como hacerlo con fórmulas.

En esa nota señalaba que el método con fórmulas es útil sólo si nuestro cuaderno contiene pocos datos. Uno de mis lectores intentó usar la solución con fórmulas pero, en sus porpias palabras "no he podido concluir mi base de datos porque la cantidad de data es mucha y con el peso de las formulas todas las maquinas se cuelgan".

Este mismo lector me pedía que le enseñara la forma de hacerlo con macros. En esta nota mostraré una solución posible con macros al mismo caso que mostrábamos en la nota anterior.
Debo aclarar que esta solución no es óptima desde el punto de vista de programación, sino que está orientada al usuario promedio cuyo objetivo es crear herramientas para el trabajo diario y no desarrollo de implementaciones en Excel.

El ejemplo se basa en el mismo archivo de la nota anterior. El archivo con las macros puede descargarse ">aquí.

Empezamos por agregar el encabezamiento "Transferido".

Esta columna nos servirá para chequear si la línea de la hoja Datos ha sido transferida o no.

En esta solución usaremos dos macros. Una de tipo evento y otra que hará el trabajo de copiar los datos a la hoja correspondiente.
La idea es que, una vez agregados los datos en la hoja "Datos", haciendo doble-clic en la celda correspondiente de la columna E, los datos sean transferidos a la hoja adecuada. Además queremos evitar que por error los datos sean transferidos más de una vez. Para eso usaremos el mecanismo de poner, automáticamente, la palabra "SI" en la columna Transferido, después de haber copiado los datos. Una vez que aparece la palabra SI en la columna E de la línea, la macro no permitirá volver a copiarlos.

Para disparar la macro que copie los datos, usaremos el evento BeforeDoubleClick de la hoja "Datos". Empezamos por abrir el editor de Vba y haciendo doble-clic en el icono de la hoja Datos, abrimos el módulo de Vb para el evento



En el módulo del evento ponemos este código


Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

Dim rngAnotar As Range, Resp, Fila

Set rngAnotar = [E:E]

If IsEmpty(Target) = False Then
MsgBox "La línea ya fue transferida"
Exit Sub
End If

If Union(Target, rngAnotar).Address = rngAnotar.Address Then
Resp = MsgBox(prompt:="Transferir datos?", Buttons:=vbYesNo, _
Title:="Transferir Datos")
If Resp = vbNo Then
Cancel = True
Exit Sub
End If
Fila = Target.Row
Call dist_hojas
Target.Value = "SI"
End If
End Sub


Después de declara las variables y definir el rango E, empezamos por comprobar si la línea a sido transferida en el pasado. Si la palabra SI aparece en la celda correspondiente de la línea que queremos copiar, aparece un mensaje informándonos que ya hemos transferido los datos y la macro se interrumpe.
El resto del código produce un mensaje preguntándonos si queremos copiar los datos. Sólo si apretamos NO, la macro se interrumpe y no habrá ningún cambio en el cuaderno.



Si aceptamos, los datos serán copiados y en la celda de la columna E aparecerá la palabra SI



Si intentamos volver a copiar los datos de la línea, aparece este mensaje



El trabajo de copiar a la hoja correspondiente lo hace esta macro, que ponemos en un módulo corriente (Módulo1)


Sub dist_hojas()
Dim LastRow As Long, Hoja As String
Dim rngCopiar As Range

Hoja = "Ruta " & ActiveCell.Offset(0, -1).Value 'definir nombre de hoja

Set rngCopiar = Range(Cells(ActiveCell.Row, 1), Cells(ActiveCell.Row, 3))

Application.ScreenUpdating = False

With Sheets(Hoja)
.Activate
LastRow = WorksheetFunction.CountA([A:A]) + 1
End With

rngCopiar.Copy Sheets(Hoja).Cells(LastRow, 1)
Sheets("Datos").Select

Application.ScreenUpdating = True

End Sub


Después de definir las variable, la primer línea del código "arma" el nombre de la hoja uniendo a la palabra "Ruta" el número que aparece en la columna D, con el operador "&".
La línea siguiente determina cuál es el rango a copiar en "Datos".
El paso siguiente es determinar cuál es la primer fila libre en la hoja en la que vamos a pegar los datos.
Una vez hecho esto, volvemos a "Datos", copiamos el rango adecuado y lo pegamos en la hoja correspondiente.

En este modelo no hemos puesto ningún método automático para borrar lpineas que han sido copiadas y cancelar la señal SI en la hoja Datos. Esto se deberá hacer manualmente.

Technorati Tags: MS Excel

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

JLD en Castellano – Balance del segundo año

Hace un año atrás, hacía el balance del primer año de este blog. Ahora ha llegado el momento de presentarles el balance del segundo año.
Durante el 2007 he estado utilizando los servicios (gratuitos) de StatCounter. La ventaja de este servicio es que permite extraer datos históricos con facilidad. Sencillamente se introducen las fechas de inicio y fin del período y el sitio genera una tabla con las estadísticas diarias. El sitio también nos permite descargar la tabla a un archivo en formato Excel o csv.
Esto que parece tan trivial, se convierte en un verdadero ejercicio de manipulación de datos, cuando nuestro sistema está instalado "en castellano". En una próxima nota estaré describiendo las técnicas que tuve que utilizar con los datos de StatCounter para poder generar la información y los gráficos que muestro aquí.

Durante el 2007 han visitado mi este blog 222.328 lectores, de los cuales 171.176 lo hacían por primera vez (First Time Visitors - 77%) y 51.152 ya lo habían hecho en el pasado (Returning Visitors - 23%).

Estos 222.328 visitantes leyeron 533.567 páginas, es decir un promedio de 2.4 páginas por visitante.

Dicho en términos diarios: 609 visitantes por día, de los cuales 469 lo hacían por primera vez, y 1.462 páginas leídas por día.




En forma gráfica:






El 25 de octubre fue el día record de visitas, con 1.419 visitantes y 3.219 páginas.

En el 2006, el primer año de este blog, hubo cerca de 58.000 visitante y 163.000 páginas leídas. Es decir un crecimiento del 383% en la cantidad de visitantes y del 327% en la de páginas leídas.

A todo esto habría que sumar las suscripciones del feed del blog, que también han crecido a buen ritmo. Al final del 2006 contaba con 33 suscriptores. De acuerdo a las estadísticas de Feedburner, a fin del 2007 este blog tenía 257, es decir casi 8 veces más.

En el resumen del 2006 declaraba cuáles eran mis planes para el 2007: reunificar el blog de gráficos y presentación de datos en este blog, crear un sitio independiente que de solución a la descarga de archivos con ejemplos, mejorar la interfaz del blog y empezar a publicar una serie de guías prácticas en castellano.

Debo reconocer que de todos los planes sólo he cumplido el de mejorar, no dramáticamente debo reconocer, la interfaz del blog.
Las guías y el sitio pasan a ser las buenas intenciones para el 2008. En cuanto al blog de gráficos, sigo dudando qué hacer con él. Por ahora seguirá siendo un blog independiente.

En resumen, 2007 ha sido un año de buen crecimiento para el blog, por lo que le estoy agradecido a mis lectores. Ha sido un año en el que sentido la gratitud de muchos de ustedes, también en los momentos difíciles que me han tocado vivir en mi vida personal.

A todos, muchas gracias y mis mejores deseos de un feliz y próspero 2008.

Technorati Tags: MS Excel