lunes, febrero 09, 2015

Seleccionar celdas combinadas en una hoja de Excel

En más de un post me he explayado sobre ese mal hábito de usar "combinar y centrar" en las hojas de Excel. También he expuesto más de una vez las ventajas de usar "centrar en la selección" en su lugar.

No siempre podemos evitar el uso de "combinar y centrar", ya sea porque el rango a unificar es vertical (no exste la posibilidad de "centrar en la selección" verticalmente) o porque estamos lidiando con un cuaderno que recibimos de algún colega.

Si queremos "descombinar" todos los rangos combinados en una hoja, podemos seleccionar todas las celdas de la hoja (Ctrl-E o clic en el cuadrdado a la izquierda y arriba de A1) y pulsar el botón "Combinar y centrar". Si queremos reemplazar el "combinar y centrar" por el "centrar en la selección", podemos usar la macro que aparece en el post mencionado.

Si queremos marcar los rangos de celdas combinadas en una hoja de Excel, podemos usar esta macro. Las áreas combinadas aparecerán con fondo rojo; por supuesto, podemos adaptar el código para dar cualquier otro formato (por ejemplo, borde)

Sub mark_merged_cells()

    Dim rngSearchArea As Range, rngCell As Range

    Set rngSearchArea = Range(Cells(1, 1), _
                        Range(Cells.SpecialCells(xlCellTypeLastCell).Address))

    For Each rngCell In rngSearchArea
        If rngCell.MergeCells = True Then
            rngCell.Interior.Color = 190
        End If
    Next rngCell

End Sub
Sub select_merged_cells()




Si queremos sólo seleccionar las áreas combinadas, usamos esta macro

Sub select_merged_cells()
 
    Dim rngSearchArea As Range, rngCell As Range
    Dim rngTemp As Range
 
    Set rngSearchArea = Range(Cells(1, 1), _
                        Cells.SpecialCells(xlCellTypeLastCell).Address)
 
    For Each rngCell In rngSearchArea
        If rngCell.MergeCells = True Then
            If rngTemp Is Nothing Then
                Set rngTemp = rngCell
            Else
                Set rngTemp = Union(rngTemp, rngCell)
            End If
        End If
    Next rngCell
 
    rngTemp.Select
 
End Sub



En ambas macros definimos el área de búsqueda desde la celda A1 hasta la última celda usada en la hoja (para evitar que la macro corra por toda la eternidad) con 

Set rngSearchArea = Range(Cells(1, 1), Range(Cells.SpecialCells(xlCellTypeLastCell).Address))

Para seleccionar simultáneamente los distintos rangos en la segunda macro, usamos el método Union. 





jueves, enero 29, 2015

Cálculo de tiempos y formato personalizado en Excel

Ayer apareció por mi oficina Armando, del departamento de planificación. Me llamó la atención que llegara solo porque, por lo general, se lo ve con Raquel, la del departamento de producción (las malas lenguas dicen que...).

- Como seguramente sabés, los datos de uso de las máquinas están en minutos.
- Si, lo se.
- ¿Cómo hago para mostrarlos en días, horas y minutos?
- Poniendo el formato correspondiente. Digamos "dd hh:mm"
- No sabía que existía ese formato, no aparece en la lista.
- Es un formato personalizado.
- Ah!, gracias.

Como era de esperar, Armando apareció de nuevo a los pocos minutos.

- Mirá, tu método no funciona; en lugar de 1 día, 22 horas y 40 minutos, me da este resultado


- ¿Te acordás lo que te explique sobre cómo Excel calcula fechas y horas? Tenes que dividir los 2800 minutos por 1440.

- Pero, ¿por qué 1440?
- Porque tu dato está en  minutos, y en un día hay 1440 minutos (60 x 24). Como explicaba en esa nota, que parece ser no leiste, los días en Excel están representados por una serie de números enteros y las horas, minutos y segundos por la parte decimal. Así 1 es un día, una hora es 1/24 (0.466667), un minuto es 1/1440 y un segundo es 1/86400 (24 x 60 x 60). Fijate en esta tabla


- Entendido, gracias. Pero cómo hago para que se entienda que se trata de días/horas/minutos. Si te fijas en la celda D3 de tu tabla parece ser 1 hora, no un día.
- Agregando texto en el formato personalizado; por ejemplo dd "dias" hh "horas y" mm "minutos". Fijate en esta tabla


- ¿Y si quiero que cuando sea un día aparezca "01 día" y no en plural?
- Usando formato condicional con esta regla



El formato condicional se suma al formato de la celda. Si queremos aplicar lo mismo para las horas y los minutos tenemos que crear un formato condicional para cada caso.

miércoles, enero 28, 2015

Funcionalidades de Excel en macros

En la nota anterior vimos un ejemplo de las ventajas de usar funcionalidades nativas de Excel en nuestras macros. En ese caso usamos Texto en Columnas para transformar fechas en formato mes/días/año al formato día/mes/año.

Otra funcionalidad nativa de Excel que conviene considerar en nuestras macros es Quitar Duplicados



Supongamos que para nuestro proyecto de Vba (macro) necesitamos un código que elimine los duplicados de una lista como ésta (la lista completa incluye 10774 registros con sólo 9 registros únicos)



Como no es obligatorio inventar la rueda cada vez que escribimos código, hacemos una búsqueda en Google (recomendablemente en inglés, para obtener más resultados).

Probablemente encontraremos códigos ineficientes como éste de VBA Express

Sub DeleteDups()
'VBA Express - Jacob Hilderbrand

    
    Dim x As Long
    Dim LastRow As Long
    
    LastRow = Range("A65536").End(xlUp).Row
    For x = LastRow To 1 Step -1
        If Application.WorksheetFunction.CountIf(Range("A1:A" & x), Range("A" & x).Text) > 1 Then
            Range("A" & x).EntireRow.Delete
        End If
    Next x
    
End Sub


que podemos mejorar en algo de esta manera

Sub DeleteDups_modified()

'VBA Express - Jacob Hilderbrand
'modificada por Jorge Dunkelman
    
    Dim x As Long
    Dim LastRow As Long


    LastRow = Application.Intersect(ActiveSheet.UsedRange, _
                    ActiveSheet.Columns(ActiveCell.Column)).Rows.Count

    Debug.Print LastRow

    Application.ScreenUpdating = False
    For x = LastRow To 1 Step -1
        If Application.WorksheetFunction.CountIf(Range("A1:A" & x), Range("A" & x).Text) > 1 Then
            Range("A" & x).EntireRow.Delete
        End If
    Next x
    Application.ScreenUpdating = True

End Sub
Public Sub DeleteDuplicateRows()


o códigos profesionalmente desarrollados como éste de Chip Pearson

Public Sub DeleteDuplicateRows()
'origen:http://www.cpearson.com/excel/deleting.htm

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' DeleteDuplicateRows
' This will delete duplicate records, based on the Active Column. That is,
' if the same value is found more than once in the Active Column, all but
' the first (lowest row number) will be deleted.
'
' To run the macro, select the entire column you wish to scan for
' duplicates, and run this procedure.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Dim R As Long
Dim N As Long
Dim V As Variant
Dim Rng As Range


On Error GoTo EndMacro
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual


Set Rng = Application.Intersect(ActiveSheet.UsedRange, _
                    ActiveSheet.Columns(ActiveCell.Column))


Application.StatusBar = "Processing Row: " & Format(Rng.Row, "#,##0")

N = 0
For R = Rng.Rows.Count To 2 Step -1
If R Mod 500 = 0 Then
    Application.StatusBar = "Processing Row: " & Format(R, "#,##0")
End If

V = Rng.Cells(R, 1).Value
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Note that COUNTIF works oddly with a Variant that is equal to vbNullString.
' Rather than pass in the variant, you need to pass in vbNullString explicitly.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
If V = vbNullString Then
    If Application.WorksheetFunction.CountIf(Rng.Columns(1), vbNullString) > 1 Then
        Rng.Rows(R).EntireRow.Delete
        N = N + 1
    End If
Else
    If Application.WorksheetFunction.CountIf(Rng.Columns(1), V) > 1 Then
        Rng.Rows(R).EntireRow.Delete
        N = N + 1
    End If
End If
Next R


EndMacro:

Application.StatusBar = False
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
MsgBox "Duplicate Rows Deleted: " & CStr(N)

End Sub


Siguiendo la técnica que mostré en la nota anterior podemos generar código que use la funcionalidad Quitar Duplicados. Activamos la grabadora de macros para grabar las acciones de eliminar los duplicados. El código generado es el siguiente

Sub Macro1()
'

    ActiveSheet.Range("$A$1:$A$10775").RemoveDuplicates _
                            Columns:=1, Header:=xlYes
End Sub


Mejoramos este código de la siguiente manera

Sub remove_dups_1()
  
    Selection.RemoveDuplicates Columns:=1, Header:=xlGuess
      
End Sub


Como podemos ver, un código muy compacto y claro. La pregunta ahora es: ¿cuál es el código que corre más rápido?.  En mi máquina los resultados fueron los siguientes:
  • DeleteDups: 78 segundos
  • DeleteDups_modified: 7.5 (mejora como consecuencia de usar Application.ScreenUpdating = True)
  • DeleteDuplicateRows: 7.0 segundos
  • remove_dups_1: 0.047 segundos
Resumiendo: DeleteDups_modified y DeleteDups son aproximadamente 11 veces más rápidas que la infeciente DeleteDups; pero remove_dups_1, basada en la funcionalidad Remover Duplicados, es casi 150 veces más rápida que DeleteDups_modified y DeleteDups y 1660 veces más rápida que la ineficiente DeleteDups.

Como en la nota anterior concluimos: siempre conviene considerar el uso de funcionalidades nativas de Excel en nuestros códigos.