Ocultar filtros en tablas dinámicas

Cuando creamos una tabla dinámica Excel instala filtros en los distintos campos (filas, columnas y filtro del informe)

Si por algún motivo queremos ocultar las flechas de los filtros, por ejemplo para evitar que un usuario cambie el contenido del informe, podemos usar la opción "Encabezados de campos" en el grupo Mostrar en las Opciones de las tablas dinámicas

Pero esta opción tiene un problema: no sólo quita las flechas sino también los encabezados

y como bono adicional, no quita el encabezado ni la flecha del filtro del informe. Los encabezados Ciudad, Agente y Año han desaparecido del informe dejando a nuestro desprevenido y poco informado usuario cavilando sobre si Janet Leverling es un agente de ventas o tal vez un cliente.

Para quitar las flechas de los filtros sin quitar los encabezados podemos usar esta macro

Sub ocultar_Filtros()
Dim ptbl As PivotTable
Dim pfld As PivotField
    Set ptbl = ActiveSheet.PivotTables(1)
      For Each pfld In ptbl.PivotFields
          pfld.EnableItemSelection = False
      Next pfld
End Sub

Esta animación muestra el resultado

Para volver a mostrar los filtros usamos esta macro, igual a la anterior pero con la propiedad EnableItemSelection con el valor True

Sub mostrar_Filtros()
Dim ptbl As PivotTable
Dim pfld As PivotField
    Set ptbl = ActiveSheet.PivotTables(1)
      For Each pfld In ptbl.PivotFields
          pfld.EnableItemSelection = True
      Next pfld
End Sub

De la misma manera podemos ocultar el filtro de un determinado campo. Para evitar que el usuario pueda filtrar el informe por Agente usamos esta macro

Sub ocultar_Item()
Dim ptbl As PivotTable
Dim pfld As PivotField

    Set ptbl = ActiveSheet.PivotTables(1)
    ptbl.PivotFields("Agente").EnableItemSelection = False
  
End Sub

Para volver a mostrar el filtro del campo cambiamos el valor de la propiedad EnableItemSelection a True.

Formato numérico de campos de datos en tablas dinámicas

Podemos considerar dos formas de organizar datos en una hoja de Excel: en forma plana ("flat file") y en forma tabular ("tabular dataset").  Supongamos una de tabla de ventas que muestra las cantidades vendidas de distintos productos por año. Si organizamos la tabla en forma plana tendremos algo así

En cambio si organizamos los datos en forma tabular, tendremos esta tabla

Esta última forma donde todos los valores (datos numéricos) están en una única columna (un solo único campo numérico) es la más eficiente para trabajar con tablas dinámicas.

Pero si tenemos que crear una tabla dinámica a partir de una matriz de datos plana descubriremos que cada campo de valor (las columnas 2010, 2011, etc, en el primer ejemplo) debe ser arrastrado individualmente al área de los datos. Y lo mismo cuenta para el formato de los números. Excel da por defecto formato "General" a los datos numéricos.

Al crear esta tabla dinámica

si queremos cambiar el formato de los valores tendremos que hacerlo campo por campo, cinco veces en nuestro caso. En nuestro auxilio vendrán las macros, como cada vez que tenemos que queremos automatizar una tarea repetitiva.

Si tenemos una única tabla dinámica en la hoja activa podemos usar esta macro

Sub format_NUM_1()
    Dim strFormatSelected As String
    Dim oPTable As PivotTable
    Dim oPField As PivotField
    Dim iPTCount As Integer
 
    iPTCount = ActiveSheet.PivotTables.Count
    If iPTCount = 0 Then
        MsgBox "No se encontraron tablas dinamicas en la hoja", _
                    vbInformation, _
                    "Formato numerico"
        Exit Sub
    End If

    Set oPTable = ActiveSheet.PivotTables(1)

    Application.Dialogs(xlDialogFormatNumber).Show

    strFormatSelected = ActiveCell.NumberFormat

    For Each oPField In oPTable.DataFields
        oPField.NumberFormat = strFormatSelected
    Next oPField
 
End Sub

Usamos el método Application.Dialogs(xlDialogFormatNumber).Show para abrir el diálogo de formato de números, capturamos la elección de usuario y con el loop For Each...Next lo aplicamos a todos los campos de datos de la tabla.

Este video muestra el funcionamiento



Si hay más de una tabla dinámica en la hoja activa tendremos que complicar un poco nuestro código

Sub format_NUM_all()
    Dim strFormatSelected As String
    Dim oPTable As PivotTable
    Dim oPField As PivotField
    Dim iPTCount As Integer
    Dim iX As Integer

    iPTCount = ActiveSheet.PivotTables.Count
    If iPTCount = 0 Then
        MsgBox "No se encontraron tablas dinamicas en la hoja", _
                    vbInformation, "Formato numerico"
        Exit Sub
    End If
    
    With Application
        .ScreenUpdating = False
        .Dialogs(xlDialogFormatNumber).Show
        strFormatSelected = ActiveCell.NumberFormat

        For iX = 1 To iPTCount
            For Each oPField In ActiveSheet.PivotTables(iX).DataFields
                oPField.NumberFormat = strFormatSelected
            Next oPField
        Next iX
        .ScreenUpdating = True
    End With


Podemos llevar nuestra macro un paso más adelante y dar al usuario la posibilidad de elegir que tabla dinámica formar de las que se encuentran en la hoja activa.

En este caso tendremos que agregar un Userform con una combobox, que contendrá los nombres de las tablas dinámicas presentes en la hoja activa (un evento crea la lista dinámicamente de acuerdo a la hoja), y una rutina que recibe como variable el nombre de la tabla elegida, abre el diálogo de formato numérico y aplica el formato elegido a la tabla.

Podemos reunir todas las macros en un complemento (Add in) e instalarlo de manera que podamos usarlo en todo cuaderno activo de Excel.
Otra ventaja del complemento es que agregará una pestaña en la cinta de comandos para activar las macros con facilidad.

El complemento se puede descargar sin cargo aquí

Después de descargar y guardar el complemento lo instalamos usando el menú Programador-Complementos (en caso de ser necesario usamos el botón Examinar para encontrar la ubicación del complemento)

En caso de recibir una advertencia de seguridad aceptamos la opción "Habilitar contenido".

Este video muestra la instalación y el funcionamiento de la macro



Los códigos pueden verse con el editor de Vba (el complemento no está protegido con contraseña).

Algunas observaciones:

• como norma de buena práctica es recomendable reemplazar el nombre por defecto de la tablas dinámica (Tabla dinámica1, Tabla dinámica2, etc.) por algo más significativo

• podemos convertir matrices planas a matrices tabulares usando el Power Query o la opción de consolidación de rangos múltiples en el viejo asistente de tablas dinámicas.

Rangos con Tablas en listas desplegables y comboboxes

No me avergüenzo de decir que soy un fanático de las Tablas. Una de las mejores herramientas de Excel, la mejor, tal vez, después de las tablas dinámicas, el Power Query y el PowerPivot.
Una de las mejores características de las tablas es que crean rangos dinámicos en todo objeto que dependa de ellas. Por ejemplo, si creamos un gráfico basado en una tabla cada cambio se reflejará automáticamente en el gráfico

Al crear una tabla Excel le asigna un nombre, por defecto Tabla1, que podemos cambiar para usar algo más significativo. Por ejemplo, rebautizamos a nuestra tabla de ventas con  "tblVentas"

También veremos que Excel la incluye en administrador de nombres como un nombre definido que se refiere al rango de la tabla

Esto nos lleva a concluir que podemos crear rangos dinámicos, como aquellos que usamos en listas desplegables, sin necesidad de echar mano a fórmulas con las funciones DESREF o INDICE. Pero para poder usar las tablas o las columnas de una tabla como rangos dinámicos tendremos primero que crear nombres definidos que se refieran a esos rangos.

A los efectos del ejemplo supongamos dos tablas de datos. Una contiene nombres de continentes y la otra contiene una columna por cada continente donde se encuentran los países del continente

A la tabla de los continentes le damos el nombre "Continente"; a la segunda tabla le damos el nombre "Paises". Para poder usar la columna de los continentes en una lista desplegable con validación de datos tenemos que crear un nombre definido que se refiera al rango de la columna

Hemos creado el nombre definido "lstContinente" que se refiere a la tabla Continente usando el lenguaje estructural de las tablas: =Continente[Continente] (en este caso el nombre de la tabla y el de la única columna coinciden).
Ahora para definir la lista desplegable con validación de datos en la celda B2 usamos el nombre definido "lstContinente"

Para crear la lista desplegable dependiente tendremos que referirnos a la columna de la tabla Paises que coincide con el continente elegido en B2. Para eso creamos el nombre definido "PaisSelec" que se refiere a esta fórmula

=INDIRECTO("Paises["&valdat!$B$2&"]")

donde "valdat" es el nombre de la hoja; es decir, creamos una cadena de texto con el operador & que la función INDIRECTO convierte en rango.

El archivo se puede descargar aquí.

Si queremos evitar los espacios en blanco al final de algunas de las listas (el rango se determina según el tamaño de la tabla, no de una columna en particular), tendremos que crear una Tabla para cada continente. En este caso sólo necesitamos crear el nombre definido que se refiere al rango de la tabla de continentes.

La lista desplegable en la celda B2 la creamos como en el caso anterior. Para la validación de datos en la celda B3 usamos la fórmula =INDIRECTO(B2).

El ejemplo puede descargarse aquí.

También podemos usar esta técnica para poblar comboboxes y listboxes. En este ejemplo creamos un Userform con dos combobox, una para los continentes y el segundo combobox para los países cuyos valores dependerán del continente elegido. Como base vamos a usar el modelo con tablas separadas por continentes.

Creamos el Userform y agregamos dos comboboxes. La lista de valores del primer combobox  (el que muestra los continentes) lo definimos directamente en el cuadro de propiedades del objeto

Como puede verse, sencillamente ponemos el nombre definido que se refiere a la tabla de continentes.
La lista de valores del segundo combobox debe depender del valor seleccionado en el combobox de continentes para lo cual debemos definir un evento Change del combo de continentes.
Hacemos un doble clic al combobox de los continentes lo que abre el módulo del userform y agrega, por defecto, el evento Change del objeto donde ponemos este código

Private Sub cbxContinentes_Change()
    With Me
        .cbxPaises.RowSource = .cbxContinentes.Value
    End With
End Sub

Ahora podemos probar el funcionamiento del Userform y las cos comboboxes seleccionando el Userform en el editor de VB y apretando F5



Ahora que vemos que nuestro código funciona vamos a mejorarlo agregando una línea para limpiar el valor del combobox de países si el usuario cambia el continente antes de cerrar el Userform

Private Sub cbxContinentes_Change()
    With Me
        .cbxPaises.Value = ""
        .cbxPaises.RowSource = .cbxContinentes.Value
    End With
End Sub

Descargar el archivo del ejemplo.

Copiar suma de varias celdas a una única celda como valor

Cada post que publico lleva por lo menos una etiqueta que identifica el contenido. A lo largo del tiempo he creado muchas etiquetas, como puede verse en la nube de etiquetas en la columna derecha del blog (o izquierda, si es que se me da por cambiar la plantilla).
Para este post debiera haber creado una nueva etiqueta, algo así como "técnicas complicadas para resolver problemas no tan graves existiendo otras técnicas más sencillas". Pero como resulta un poco largo seguramente terminaré endosándole la lacónica etiqueta "Macros".

La situación es la siguiente: en una hoja de Excel hay datos numéricos "desparramados" en varias celdas; la misión es sumar todos los valores y poner el resultado en una única celda. Esta celda debe contener un valor constante, no una fórmula. A los efectos del ejemplo uso una única hoja; el problema real comprendía varias hojas.

¿Cuáles son las opciones?

1. Tomar un papel y un lápiz, hacer la cuenta y teclear el resultado.
2. Totalizar los datos en una celda, copiar el resultado y pegarlo en la celda indicada como valor.
3. Usar una macro para agilizar el proceso.

La primera opción es una broma. El problema con la segunda opción es lo tedioso del proceso.

Nos queda así la opción de la macro, que es la que utilice al enfrentarme con el problema.

El código de la macro es la siguiente

Sub copy_and_sum()
    Dim rngCell As Range, dblTemp As Double
    Dim dblTotSum As New DataObject

    For Each rngCell In Selection
        If IsNumeric(rngCell) Then
            dblTemp = dblTemp + rngCell
        End If
    Next rngCell


    With dblTotSum
        .SetText dblTemp
        .PutInClipboard
    End With

End Sub

Para utilizar la macro comenzamos por seleccionar las celdas que queremos totalizar (clic a la primer celda y Ctrl-Clic a las restantes); luego activamos la macro (con ALT-F8 o un atajo de teclado); la macro suma los valores numéricos de las celdas y copia el total al Clipboard. Finalmente usamos Ctrl-V o Pegar para pegar el valor en la celda correspondiente.

Esta macro usa el objeto DataObject por lo que hay que activar la referencia al Microsoft Forms 2.0 Object Library.

A pesar de que este objeto está definido para copiar texto al Clipboard, podemos ver que también lo hará con valores numéricos y lo que es más importante, nos permitirá ir sumándolos.

En la primera parte del código usamos un loop For Each Next para asignar los valores numéricos a la variable dblTemp e ir sumándolos. En la segunda parte del código empezamos por asignar el valor de dblTemp al objeto dblTotSum para luego copiarlo al Clipboard con el método PutinClipboard.

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. 

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.

Tip para escribir macros eficientes en Excel

Hay muchas normas de buenas prácticas y tips para escribir macros eficientes. Una posibilidad raramente mencionada es usar en nuestros códigos métodos incorporados de Excel que podemos grabar con la grabadora de macros.

Un ejemplo puede ser la macro que propuse para importar fechas de un archivo .csv. La macro usa el loop For Each - Next para convertir fechas en formato mes/día/año (como en los Estados Unidos) al formato día/mes/año en uso en la mayoría de los países de habla hispana.

En lugar del código podemos usar la grabadora de macros para usar el método Texto en Columnas (Datos-Texto en Columnas) en nuestro código en lugar del loop For Each - Next. La ventaja inmediata de la grabadora de macros es que nos exime de tener que conocer la sintaxis del método. Además, como veremos más adelante, esta funcionalidad incorporada de Excel es mucho más eficiente.

No nos limitaremos a grabar la macro sino que eliminaremos las partes innecesarias; luego agregaremos variables para que nuestro código sea lo más flexible posible (al contrario del código que resulta de la grabadora de macros).

Supongamos que hemos importado un archivo .csv que contiene 400000 registros de fechas. Como el archivo fue originado en los Estados Unidos, tenemos que cambiar el formato de los datos, tal como explicamos en la nota mencionada.

Después de importar el archivo, activamos la grabadora de macros y usamos Texto en Columnas para transformar las fechas

El código resultante es el siguiente

Eliminamos la primer línea del código, suponiendo que el usuario activará la macro después de haber elegido el rango a convertir. También podemos eliminar las propiedades definidas por defecto, es decir, aquellas que no hemos cambiado (como norma, aquellas donde el valor de la propiedad es False)

Podemos dar un paso más adelante y permitir al usuario definir donde pegar el resultado (en el código de arriba Destination:=Range("A1")); además queremos verificar que el usuario haya elegido un rango que contengo por lo menos dos celdas

Ahora podemos verificar cuál es el código que corre más rápido: el que usa el loop For each - Next, de la nota mencionada, o éste basado en el método incorporado de Excel.

En mi máquina (Dell Latitude E5540 con procesador Intel Core i5-4300, 8 GB RAM, Excel 2010 64-bit), la macro que usa Texto en Columnas tomó 4.3 segundos en convertir las 400 mil fechas. La misma tarea con el loop For Each - Next tomó 180 segundos.

Conclusión: siempre considerar usar los métodos incorporados en Excel en nuestras macros.

Importar fechas de un archivo .csv a una hoja de Excel

Ya hemos tocado en el pasado los problemas que pueden surgir cuando abrimos en forma directa archivos texto .csv en hojas de Excel. Por "forma directa" me refiero a archivos .csv abiertos con un doble click o usando Abrir. Excel interpreta los datos de acuerdos a ciertas reglas y pueden producir cambios indeseados. Por ejemplo el texto "012345" que representa, digamos, un número de catálogo será transformado en "12345".

El problema es particularmente grave cuando los datos importados son fechas. Supongamos que recibimos un archivo .csv con una lista de fechas que nos envía una empresa de los Estados Unidos. En los Estados Unidos se usa el formato mes/día/año mientras que en la mayoría de los países hispanoparlantes el formato de fecha es día/mes/año. Al abrir el archivo .csv directamente, los valores que Excel no interpreta como fechas de acuerdo a las definiciones regionales serán transformados en texto.Veamos este ejemplo:

Podemos ver que algunas fechas están alineadas a la izquierda y otras a la derecha.  Los valores alineados a la derecha han sido importados como fechas (al ser números Excel los alinea a la derecha), mientras que los valores alineados a la izquierda son texto. Esto se debe que Excel no puede interpretar  esos valores como fecha siguiendo el formato regional día/mes/año (por ejemplo en la celda A2, donde el número de mes sería 23).

El valor en la celda A3 nos muestra el problema más grave que se puede generar cuando importamos archivos .csv con fechas. La fecha, siguiendo el formato de los Estados Unidos, es el 6 de Octubre pero Excel la ha transformado en el 10 de Junio.

Señalemos que los datos importados deben ser fechas, de manera que podamos realizar operaciones con ellos.

Podemos transformar los textos en fechas usando una fórmula como

=SI(ESTEXTO(A3),VALOR(EXTRAE(A3,4,2)&"/"&IZQUIERDA(A3,2)&"/"&DERECHA(A3,4)),VALOR(TEXTO(A3,"mm/dd/yyyy")))

Podemos ver que el valor de la celda A3 es transformado correctamente por la fórmula en 06/10/2012.

Otra solución es usar una macro para forzar la transformación. La ventaja de la macro consiste en que no debemos crear las fórmulas para cada hoja; podemos guardar la macro en el cuaderno Personal y usarla en cada hoja que necesitemos sin necesidad de cargarla con fórmulas.

El código básico de la macro es

Sub USDdate_to_EURdate()
    Dim rngcell As Range

    On Error Resume Next
    For Each rngcell In Selection
        rngcell = CDate(Format(rngcell, "mm/dd/yyyy"))
    Next rngcell
    On Error GoTo 0

End Sub

Esta macro reemplaza los valores en el rango seleccionado por fechas con formato (dd/mm/yyyy).

Este código más elaborado nos permite elegir el rango donde copiar los resultados

Sub USDdate_to_EURdate_2()
    Dim rngcell As Range
    Dim rngOrigin As Range, rngDest As Range
    Dim iX As Long

    'seleccionar el rango a transformar
    Set rngOrigin = Application.InputBox(prompt:="Seleccione el rango a transformar", _
                                        Title:="Rango a transformar", _
                                        Type:=8)

    'comprobar si el rango elegido es vertical/columna
    If rngOrigin.Columns.Count > 1 Then
        MsgBox "El rango seleccionado debe contener solo una columna", vbCritical, "Error en la seleccion"
        Exit Sub
    End If

    'seleccionar la primer celda del destino
    Set rngDest = Application.InputBox(prompt:="Seleccione la primer celda del rango del destino", _
                                        Title:="Destino", _
                                        Type:=8)
    'comprobar que se haya elegido una sola celda
    If rngDest.Count > 1 Then
        MsgBox "Debe seleccionar solo una celda", vbCritical, "Error en la seleccion"
        Exit Sub
    End If

    Application.ScreenUpdating = False
    On Error Resume Next 'en caso de haber celdas vacias o valores no validos en el rango elegido
    For iX = 0 To rngOrigin.Count - 1
        rngDest.Offset(iX, 0) = CDate(Format(rngOrigin(iX + 1), "mm/dd/yyyy"))
    Next iX
    On Error GoTo 0
    Application.ScreenUpdating = True
 
End Sub

Contar registros únicos en rangos grandes

En la nota anterior sobre registros únicos vimos cómo encarar el recuento usando tablas dinámicas. Esta vez Eduardo, el de la nota anterior, apareció en mi oficina y sin mucha ceremonia se sentó del otro lado del escritorio mirándome con el ceño fruncido.

- ¿Te acordás del asunto de contar registros únicos con tablas dinámicas?
- Si, seguro. Publiqué una nota en el blog.
- La leí, pero tengo otro problema.
- Si...
- Tengo una tabla con ventas a clientes. Cada cliente aparece muchas veces. Quiero poner en una celda cuántos clientes hay la lista, es decir, sin repeticiones.
- Ah! podés leer mi nota sobre contar valores únicos en Excel.
- La leí y apliqué la fórmula, pero lo lleva mucho tiempo calcular.
- ¿Cuántos registros hay en tu tabla?
- Más o menos, cuatrocientos mil.
- ¿¡¡Cua-tro-cientos-mil!!?, respondí pronunciando cada una de las sílabas por separado para enfatizar.
- Si, y cada mes agrego más.

Lo que Eduardo descubrió es que CONTAR.SI no es la más veloz de las funciones de Excel. Aplicar CONTAR.SI a un rango de 400 mil celdas es una de las mejores maneras de explicar el concepto de eternidad.

Como Eduardo insiste en no usar Filtro Avanzado o Quitar Duplicados y, además, está prohibido mencionar Access en su presencia, tuve que buscar alguna otra solución.

La fórmulas que intentaba usar Eduardo son:

=SUMA(1/CONTAR.SI(miRango,miRango)) en forma matricial
=SUMAPRODUCTO((miRango<>"")/CONTAR.SI(miRango,miRango))

Como puede observarse, ambas fórmulas utilizan la función CONTAR.SI, por lo que la solución que le propuse fue esta UDF (función definida por el usuario)

Function contar_unicos(rngSeleccion As Range)

    Dim collUnicos As New Collection
    Dim rngCell As Range


    On Error Resume Next
    For Each rngCell In rngSeleccion
        collUnicos.Add rngCell, CStr(rngCell)
    Next rngCell
    On Error GoTo 0

    contar_unicos = collUnicos.Count

End Function

Usando una macro que Charles Williams de Decisions Models tuvo la gentileza de colgar en el sitio de artículos técnicos del Office Dev Center, medí el tiempo de cálculo de las distintas fórmulas.

Para investigar el tiempo de cálculo de las distintas opciones usé un ejemplo con un rango de 20 mil celdas conteniendo números aleatorios. El examen del tiempo de cálculo de las fórmulas lo hice usando rangos de 5 mil, 10 mil y 20 mil registros

El valor en la celda G2 permite controlar el tamaño del rango; la celda G3 contiene la fórmula a examinar y la celda G4 recibe el valor del tiempo de cálculo hecho con la macro (apretando el botón "Tiempo de cálculo").

En mi máquina (Dell Latitude E5540 con procesador Intel Core i5-4300, 8 GB RAM, Excel 2010 64-bit), estos fueron los resultados en segundos:

Podemos ver que la UDF Contar_Unicos es mucho más rápida que las que usan CONTAR.SI. Además, la diferencia crece con la cantidad de celdas a procesar. Con 5 mil celdas, Contar_Unicos es casi 33 veces más rápida que las otras; con 10 mil celdas la diferencia llega a 78 veces y con 20 mil celdas 160 veces.

Otro detalle interesante es que el tiempo de cálculo de las fórmulas no es proporcional a la cantidad de registros. Al aumentar la cantidad de registros en un 100% (de 5000 a 10000), el tiempo de cálculo de las fórmulas con CONTAR.SI crece en un 300%; un aumento del 300% en los registros (de 5000 a 20000 celdas) resulta en un aumento del 1500% en el tiempo de cálculo.

El tiempo de cálculo de la UDF con 400 mil registros fue 3 segundos.

La última actualización de Excel deshabilita los controles ActiveX

A los usuarios de Excel que hayan instalado la actualización del Office del 09/dec/2014 les espera una desagradable sorpresa: Excel no permite incrustar controles ActiveX en las hojas

Al intentarlo recibimos este aviso: No se puede insertar el objeto

Para quien, como yo, use estos controles en sus modelos (por ejemplo, en gráficos animados o en dashboards), esta situación es un verdadero dolor de cabeza.

Para nuestra fortuna el MVP RoryA publicó esta solución en su sitio :

• Cerrar todos los programas del Office;
• usando el Windows Explorer o cualquier otra aplicación (personalmente prefiero el Total Commander) buscar todos los archivos *.exd (no confundir con *.exe) y borrarlos o, preferentemente, cambiarles el nombre.

• Volver a abrir Excel y probar si todo funciona ahora normalmente. También se puede realizar reboot del computador.

En mi caso, ésto solucionó el problema, pero de acuerdo a las conversaciones el foro Technet en ciertos casos el problema persiste. De acuerdo a Rory, los técnicos de Microsoft conocen el problema y es de esperar una corrección en breve.

Señalemos que el problema no se limita a la incapacidad de incrustar controles ActiveX en una hoja de Excel. Controles existentes dejar de funcionar y son convertidos en imágenes.

La función FORMULATEXTO y Excel 2010

Una de las nuevas funciones en Excel 2013 es FORMULATEXTO. Esta función, disponible solamente en Excel 2013 y Excel 365, transforma la fórmula de una celda en texto.

¿Para que sirve?, me preguntarán Básicamente, para documentar fórmulas, como la que aparece en la imagen. La fórmula calcula la fecha del tercer lunes de un mes determinado (la nota sobre el tema la publicaré próximamente).

En algunas de mis notas suelo incluir el texto de la fórmula. Para obternerlo suelo copiar la fórmula directamente de la barra de las fórmulas y pegarla en una celda previamente formada como Texto o pegarla quitando el símbolo "=" para que Excel no la interprete como fórmula.

Dado que la mayor parte de mis notas las desarrollo con Excel 2010, decidí que podría crear una UDF (función definida por el usuario) que imite el funcionamiento de FORMULATEXTO para poder usarla en versiones de Excel anteriores a Excel 2013.

El código de la función es el siguiente


Function formulaText(rcell As Range, vType As Boolean) As String

    If rcell.HasFormula = False Then
        formulaText = "#NA!"
        Exit Function
    End If
  
    Select Case vType
        Case Is = False
            formulaText = rcell.FormulaLocal
        Case Is = True
            formulaText = Mid(rcell.FormulaLocal, 2, Len(rcell.FormulaLocal) - 1)
    End Select
    
End Function


La función tiene dos argumentos:

• rcell: es la celda que contiene la fórmula

• vType: que puede ser 0 o FALSO (incluye el símbolo "=" al principio de la fórmula) o 1  o VERDADERO (el texto no muestra el símbolo "=")

Agregar controles en una hoja de Excel usando Vba

Los controles (casillas de verificación, cuadros combinados, botones de opción, etc.) dan un "toque profesional" a la hoja pero no siempre son la mejor opción. En  general podemos encontrar soluciones más prácticas usando, por ejemplo, validación de datos y/o funciones SI.

Lo más corriente es agregar controles en la hoja en forma manual, usando el menú Desarrollador-Controles-Insertar. Existen dos colecciones de controles: Formulario y ActiveX.
En esta nota veremos como insertar controles ActiveX usando Vba.

Supongamos esta tabla de facturas con sus fechas de vencmientos

En el campo "Pagada" (columna E) anotamos "SI" cuando la factura ha sido pagada. Esto nos permite crear el informe que nos muestra los totales de facturas atrasadas, pagadas y a vencer.
Las fórmulas en el informe son:

celda H4:

=SUMAPRODUCTO((tblFacturas[Fecha Vencimiento]<H3)*(tblFacturas[Pagada]<>"SI")*tblFacturas[Importe])

celda H5:

=SUMAR.SI(tblFacturas[Pagada],"SI",tblFacturas[Importe])

celda H6: 

=SUMAPRODUCTO((tblFacturas[Fecha Vencimiento]>=H3)*(tblFacturas[Pagada]<>"SI")*tblFacturas[Importe])

la Tabla "tblFacturas" se refiere al rango B2:E16 (supongo que la mayoría de mis lectores ya hayan adoptado la sana costumbre de usar Tablas para organizar matrices de datos).

Y ahora vayamos a la cuestión de los controles incrustados en hojas de Excel. En nuestro ejemplo queremos usar casillas de verificación para señalar que una factura ha sido pagada  en lugar de un "plebeyo" SI.

Queremos que nuestro informe se vea así:

Si nuestra tabla tiene pocas filas podemos simplemente agregar los controles manualmente. Las casillas están definidas sin texto, ligadas a ka celda sobre la cual están ubicadas y el valor es FALSO. Todo esto tenemos que definirlo cambiando las propiedades por defecto de la casilla. Para ahorrarnos el trabajo de hacerlo cada vez que queremos agregar una casilla podemos usar esta macro:

Sub insert_one()
   
    With ActiveSheet.OLEObjects.Add(classtype:="Forms.Checkbox.1", _
         Top:=ActiveCell.Top + 1, Left:=ActiveCell.Left + 15, _
         Height:=ActiveCell.Height, Width:=ActiveCell.Width * 0.5)
         .Object.Caption = ""
         .LinkedCell = ActiveCell.Address
         .Object.Value = False
         .Object.BackStyle = fmBackStyleTransparent
     End With
    
End Sub

El código agrega la casilla de verificación en la celda (con el método Add); luego definimos algunas propiedades:
Caption = "" para que la casilla no contenga ningún texto;

LinkedCell = Activecell.Address para ligar la casilla a la celda; esto es necesario para poder luego utilizar el valor de la casilla en nuestras fórmulas.

Value = False para que éste sea el valor por defecto en la celda vinculada a la casilla de verificación.

En la tabla de las facturas cambiamos el color de la fuente en las celdas de la columna Pagadas a blanco, para que el valor de la casilla en la celda vinculada no sea visible.

Dado que en el campo Pagada tenemos ahora valores FALSO o VERDADERO (cuando la casilla a sido marcada), tenemos que modificar nuestras fórmulas

celda H4:

=SUMAPRODUCTO((tblFacturas3[Fecha Vencimiento]<H3)* (tblFacturas3[Pagada]=FALSO())*tblFacturas3[Importe])

celda H5:

=SUMAR.SI(tblFacturas3[Pagada],"VERDADERO",tblFacturas3[Importe])

celda H6: 

=SUMAPRODUCTO((tblFacturas3[Fecha Vencimiento]>=H3)*(tblFacturas3[Pagada]=FALSO())*tblFacturas3[Importe])

Si tenemos una tabla con muchas filas podemos y queremos agregar las casillas en un única operación podemos usar esta macro (igual a la anterior a la que le hemos agregado un loop):

Sub insert_check()
    Dim rngCell As Range
 
    Application.ScreenUpdating = False
 
    For Each rngCell In Selection
        rngCell.Select
        With ActiveSheet.OLEObjects.Add(classtype:="Forms.Checkbox.1", _
             Top:=ActiveCell.Top + 1, Left:=ActiveCell.Left + 15, _
             Height:=ActiveCell.Height, Width:=ActiveCell.Width * 0.5)
             .Object.Caption = ""
             .LinkedCell = ActiveCell.Address
             .Object.Value = False
         End With
     Next rngCell
    
     Application.ScreenUpdating = True
    
End Sub
Sub del_all_cb()

Pasar datos en filas o columnas a una matriz

En un post del año 2008 mostré como pasar los datos de una matriz (tabla bi-dimensional) a una fila o columna. Inmediatamente surge la pregunta: ¿cómo hacemos para pasar los datos de una columna o fila a una matriz de tamaño determinado?

Consideremos este ejemplo:

Queremos tomar los datos en el rango B2:B16 y pasarlos a una matriz de 5 filas y tres columnas tal como vemos en el rango E6:G10.

Si bien podemos hacerlo con fórmulas, vamos a mostrar una solución con macros, solución más flexible y práctica (podemos guardar la macro en el libro Personal y usarla en cualquier cuaderno sin necesidad de recrear las fórmulas en cada oportunidad).

Este video muestra como funciona la macro



El código de la macro es el siguiente:

Sub make_matrix()
    Dim Arr_1()
    Dim lnumRows As Long, lnumCols As Long
    Dim rngDestRange As Range, rngCellDest As Range
    Dim i As Long, j As Long, x As Long
    Dim lCounter As Long
 
     
    If Selection.Count < 4 Then
        MsgBox "Debe seleccionar un rango de por lo menos cuatro celdas", vbExclamation
        Exit Sub
    End If
 
    lnumRows = Application.InputBox("Cuantas filas en la matriz?", "Filas", , , , , , 2)
    lnumCols = Application.InputBox("Cuantas columnas en la matriz?", "Columnas", , , , , , 2)
 
    ReDim Arr_1(1 To lnumCols, 1 To lnumRows)
 
    Set rngCellDest = Application.InputBox("Posicion de la primera celda de la matriz", "Copiar matriz", , , , , , 8)
    Set rngDestRange = rngCellDest.Range(Cells(1, 1), Cells(lnumRows, lnumCols))
 
    lCounter = 0
 
    For i = 1 To lnumCols
        For j = 1 To lnumRows
            Arr_1(i, j) = Selection.Item(lCounter + 1)
            lCounter = lCounter + 1
        Next j
    Next i
 
    rngDestRange.Value = WorksheetFunction.Transpose(Arr_1)
 
 
End Sub

Antes de aplicar la macro debemos seleccionar el rango de las celdas que queremos convertir en matriz. Este rango debe ser unidimensional, fila o columna.

Una vez seleccionado accionamos la macro. En primer lugar la macro comprueba que se haya elegido un rango con por lo menos cuatro celdas (el amable lector me disculpará si no explico la razón de esta condición).

El segundo paso es determinar las dimensiones de la matriz, cuántas filas y cuantas columnas. En nuestro ejemplo el rango contiene 15 celdas, por lo que hemos definido una matriz de 5 x 3. El número de elementos en la matriz equivale al múltiplo de las dimensiones. Así por ejemplo, si definimos en nuestro ejemplo una matriz de 4 x 2, sólo los primeros 8 datos serán pasados a la matriz.

El tercer paso es determinar la posición de la primer celda de la matriz.

Una vez que hemos definido todos los parámetros usamos la orden

ReDim Arr_1(1 To lnumCols, 1 To lnumRows)

para definir un array que contenga los datos que queremos que aparezcan en la hoja.

Luego usamos este loop para introducir los datos en el array

    For i = 1 To lnumCols
        For j = 1 To lnumRows
            Arr_1(i, j) = Selection.Item(lCounter + 1)
            lCounter = lCounter + 1
        Next j
    Next i

El último paso es pasar los datos a la hoja:

rngDestRange.Value = WorksheetFunction.Transpose(Arr_1)

Nótese que usamos Transpose par invertir los datos del array que pasamos a la matriz en la hoja.

El cuaderno con el ejemplo y la macro puede descargarse aquí.