Calcular registros únicos en rangos extensos - otra versión

Supongamos que tenemos una tabla de datos de ventas donde cada registro (fila) nos muestra el país, la ciudad, el cliente, el producto, la cantidad y, por suspuesto, el importe.
Ahora supongamos que nuestro jefe (o jefa) nos pide saber cuantos clientes hay en cada país. Esto significa hacer un recuento de valores únicos.

Cuando se trata de rangos extensos nada mejor que usar tablas dinámicas. Hemos mostrado en el pasado cómo hacerlo, como pueden ver en esta nota.

Excel 2013 cuenta con la función incorparada para registros únicos tal como mostré en esta nota.

Si todavía usamos Excel 2010 y la tabla de datos es extensa, por ejemplo 500 K filas, las técnicas que usan CONTAR.SI son prácticamente inútiles por el tiempo de proceso que demandan. Tampoco Filtro Avanzado con la opción Registro Únicos ofrece una solución eficiente.

El MVP Roger Govier propuso una solución que consiste en construir una tabla dinámica cuya base de datos es otra tabla dinámica; es decir, pivotear una pivot table.

Veamos como es esta técnica. Esta es nuestra tabla de datos

Como nos piden el "recuento distinto", cuántos clientes hay en cada país, construimos esta tabla dinámica

Para que la etiqueta del país se repita en todas las filas usamos Configuración de campo - Diseño e Impresión - Repetir etiquetas de elementos

Otros detalles importantes:

• dar a la tabla formato tabular (por defecto la tabla tiene el formato Compacto);
• quitar los Subtotales del campo País;
• quitar los Totales de las filas y las columnas.

Ahora vamos a construir una segunda tabla dinámica basada en la primera

No es indispensable ubicar la segunda tabla dinámica en la misma hoja como la primera, pero lo hacemos por comodidad.
En esta tabla dinámica ponemos el campo País en el área de las filas y el campo Cliente en el área de los datos. Como Clliente no es un campo numérico, Excel usa la función Cuenta lo que nos da el número de clientes únicos por país

Para completar nuestro modelo debemos agregarle dinamismo, es decir, la capacidad de ampliarse dinámicamente (en ingles: "scalabilty", la capacidad de ir acomodándose a incrementos de datos; la palabra "escalabilidad" no existe en castellano, por lo menos por ahora).
Para hacerlo tenemos que crear un rango dinámico que se refiera a la primer tabla dinámica. Si intentamos definir la tabla dinámica como Tabla (Insertar-Tabla) veremos que esto no es posible. Así que echaremos manos a las técnicas "tradicionales", creando un nombre que se refiera al rango en forma dinámica usando la INDICE y CONTARA. En nuestro ejemplo definimos el nombre "rngTablaDatos" que se refiere a esta fórmula:

=Hoja1!$A$3:INDICE(Hoja1!$C:$C,CONTARA(Hoja1!$A:$A)+2)

Otra mejora posible es crear un evento que actualize la tabla cada vez que se genera un cambio en la base de datos, como muestro en esta nota.

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.

Lista desplegable con contenido condicional - versión con Vba

Supongamos este escenario:

tenemos una lista que asocia nombres con valores

donde los nombres se repiten.

Queremos crear una lista desplegable que muestre los valores asociados al nombre que eliljamos en la celda G2 de este ejemplo

Vamos a mostrar como está construido este modelo.

Comenzamos por mostrar la columna A que están oculta. Esta columna contiene un rango dinámico donde ponemos los valores asociados al nombre que aparece en la celda G2.

El rango D1:E14 que contiene los nombres y sus valores está definido como Tabla.
La columna A contiene los valores asociados al nombre introducido en la celda G2.
Para poner estos valores usamos un evento de tipo Change

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Address = "$G$2" Then
        Range("tblNombreValor[#All]").AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=Range("G1:G2"), CopyToRange:=Range("A1"), Unique:=False
    End If
 
End Sub

El código usa la funcionalidad Filtro Avanzado (Datos - Ordenar y Filtrar - Avanzadas) que nos permite filtrar una tabla y copiar los resultados a otro rango.

Definimos un nombre que se refiere a este rango dinámicamente con esta fórmula

=Hoja1!$A$2:INDICE(Hoja1!$A:$A,CONTARA(Hoja1!$A:$A))

Ahora definimos una validación de datos en la celda H2 con la opción Lista y el nombre definido ListValores en la casilla Origen

Con esto concluimos la construcción del modelo. El archivo del ejemplo se puede descargar aquí.

En cuanto al formato condicional para señalar las filas del nombre introducido en G2, usamos la opción Formula con esta definición

Gráficos Excel - Fijar máximos del eje vertical con macro

En la nota anterior sobre el tema mencionamos que la solución tradicional para coordinar el máximo del eje de las Y (eje de los valores en términos de Excel) es usar una macro.
El método manual, con fórmulas y series auxiliares, tiene la ventaja que puede ser usado también en entornos donde las macros están bloqueadas, por ejemplo,  por el administrador de la red. Las deventajas son menores pero existen:

• el valor máximo es determinado por el algoritmo de Excel, por lo que siempre será cercano al valor deseado pero no siempre exactamente igual;
• hay que modificar cada uno de las gráficos que queremos coordinar.

Las macros son más flexibles y eficientes, ya que podemos establecer exactamente el valor que queremos que aparezca como máximo y podemos aplicarlas a todos los gráficos de la hoja.

El código es muy sencillo y podemos usarlo de dos maneras distintas: como sub (macro común) o como evento. Al usarlo como sub, tenemos que crear también la forma de disparar el código, por ejemplo, ligando la macro a un botón; al usar eventos podemos hacer que la macro corra cuando, por ejemplo, el usuario cambia algún dato en la tabla de los datos.

Como en el ejemplo de la nota anterior, tenemos dos gráficos basados en dos tablas de datos y en la celda A3 calculamos el valor máximo de ambas tablas (hemos creado el nombre "cellValMax" que se refiere a la celda A3)

Como podemos ver en la imagen, la escala del eje de las Y es diferente en cada gráfico y por lo tanto la comparación visual es engañosa.
Como mencionamos más arriba podemos usar el código como sub (macro) o como evento; el código es el mismo pero la forma de hacerlo correr es distinta.

Uso del código como sub (macro)

En un módulo compun del editor de Vb, ponemos este código

Sub coordinar_max_graficos()
   Dim dbMax As Double
   Dim objCht As ChartObject

   dbMax = Range("cellValMax").Value
  
   For Each objCht In ActiveSheet.ChartObjects
      With objCht.Chart.Axes(xlValue)
        .MaximumScale = dbMax
        .MinimumScale = 0
      End With
   Next objCht
End Sub

En la hoja que contiene los gráficos agregamos un botón ligado a la macro




Uso del código como evento

Para usarlo como evento el código debe ir en un módulo de la hoja que contiene los gráficos. Una de las formas de acceder a este módulo es apunta con el mouse a la pestaña con el nombre de la hoja y activar la opción Ver código

En nuestro caso vamos a crear un evento de manera que cuando el usuario haga algún cambio en la hoja, se dispare el código. Para esto usamos un evento del tipo Change

Como puede apreciarse, el código es el mismo que en la rutina sub.

Fechas en combobox

Ya hemos tratado en este blog sobre la posibilidad de incrustar controles directamente en hojas de Excel. En particular hemos mostrado las bondades de usar cuadros combinados de la colección de controles ActiveX (combobox) para crear listas desplegables.

Ciertos problemas surgen cuando queremos usar una combobox incrustada en la hoja para desplegar fechas. Veamos este ejemplo: en la hoja tenemos un rango con fechas al que le hemos asignado un nombre (fechas); hemos incrustado un cuadro combinado (combobox) para que el usuario elija una de esas fechas y ésta aparezca en la celda E5. También nos hemos preocupado de darle a E5 el formato de fecha

Al desplegar las fechas esto es lo que veremos

pero al elegir la fecha las cosas se complican

El formato de fecha se ha perdido tanto en la celda ligada como en el cuadro combinado. Lo que vemos ahora es el número de serie que representa la fecha.

Pero si miramos con un poco más de atención veremos que hay un segundo problema. El valor aparece alineado a la izquierda, lo que nos sugiere que se trata de un valor de texto, no numérico. Efectivamente, el valor que pasa de la combobox a la celda es textual. Para remediar esta situación tendremos que programar un evento de la combobox.

El código del evento debe ir en el módulo de la hoja que contiene el cuadro combinado. Podemos acceder al módulo desde el editor de Vba seleccionando el objeto Sheet correspondiente

o seleccionando la combobox en la hoja y seleccionando Ver Código en el menú contextual (para poder seleccionar el objeto debemos activar la opción Modo Diseño en Programador-Controles)

En el módulo ponemos este código

Private Sub ComboBox1_Change()
    ComboBox1.Value = CDate(ComboBox1.Text)
End Sub

Ahora veremos el formato adecuado en la celda y en el cuadro combinado. Pero si prestamos atención veremos que el valor sigue siendo texto.

Si no queremos realizar ninguna operación con el valor que pasamos a la celda ligada, podemos terminar aquí nuestra tarea. Pero en caso contrario tendremos que convertir el texto en valor numérico. Recordemos que la celda ligada ya tiene formato de fecha.

Para que esto suceda agregamos una línea de código en el evento

Private Sub ComboBox1_Change()
    ComboBox1.Value = CDate(ComboBox1.Text)
    Range(ComboBox1.LinkedCell).FormulaR1C1 = CDate(ComboBox1.Text)
End Sub

Con este código el valor en la celda ligada será numérico.

Todo sobre listas desplegables en Excel, técnicas avanzadas y descarga gratuita de ejemplos  en la Caja de Herramientas Excel - Listas Desplegables de JLD. Ver la nota o ir a la página de descarga de la guía.

Lista desplegable con combobox dinámico

En el pasado mostramos que una de las limitaciones de crear listas desplegables con Validación de Datos es la falta de la propiedad "autocompletar". En una nota del año 2008 mostramos como usar un cuadro combinado (combobox) de la colección de controles ActiveX para superar esta limitación.

Desde entonces he recibido muchas consultas sobre el tema. La más común es cómo hacer que el control aparezca en la celda activa y desaparezca después de haber elegido el valor.

Es decir, queremos mimetizar el comportamiento de las listas creadas con validación de datos, pero usando el cuadro combinado para disfrutar de la propiedad "autocompletar".

La técnica para hacerlo incluye, obviamente programar eventos (Vba). Mostraré aquí un ejemplo sencillo, que puede aplicarse a todo modelo. En nuestro ejemplo definimos un rango que contiene los nombres de los días de la semana, que será la fuente de los valores del combobox, y un rango en la hoja donde queremos utilizar el control.

Los pasos son los siguientes:

1 – Creamos un nombre definido que se refiere al rango que contiene los días de la semana

En este ejemplo usamos el cuadro de nombres para crear el nombre con facilidad. El rango está en la hoja "valores".

2 – En la hoja "lista" incrustamos un control cuadro combinado (combobox) de la colección de controles ActiveX

Ubicamos el control en el lugar deseado (en nuestro caso sobre la celda B2) asegurándonos que ocupe toda la celda.
En la propiedad ListFillRange ponemos el nombre definido que se refiere a la lista de valores (dia_semana).

De ser necesario cambiamos la definición de la fuente a un tamaño adecuado cambiando la definición de la propiedad Font.

Al finalizar el proceso desactivamos el botón "Modo Diseño" (lo controles no funcionan cuando Excel se encuentra en modo de diseño).

3 – Definimos el rango de la hoja "Lista"", donde queremos que aparezcan los controles cuando seleccionamos alguna celda del rango. En nuestro ejemplo el rango es B2:B20 ("rngDia")

4 – Programamos el evento Worksheet_SelectionChange en la hoja "lista" de manera que cuando el usuario seleccione alguna celda del rango "rngDia", el cuadro combinado aparezca sobre esa celda. El código, que va en el módulo de la hoja, es el siguiente

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Union(Target, Range("rngDia")).Address = Range("rngDia").Address Then
        With ComboBox1
            .Visible = True
            .Top = ActiveCell.Top
            .LinkedCell = ActiveCell.Address
        End With
    Else
        ComboBox1.Visible = False
    End If
End Sub

Lo que hace este código lo siguientes:
- Vuelve visible el control (cuando la celda activa no está en el rango deseado, el control es invisible, lo que hacemos definiendo la propiedad Visible como False)
- Definimos la propiedad Top del control de manera que coincida con el ángulo superior izquierdo de la celda activa
- Definimos que celda debe recibir el valor elegido (la celda activa).
La última línea del código oculta el control si la celda activa no pertenece al rango donde queremos que aparezca la lista desplegable.

El modelo funciona así

Podemos mejorar este código de manera que al alto y ancho del control se adapten dinámicamente el alto y ancho de la celda, definiendo las propiedades Height y Wide en el código del evento

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Union(Target, Range("rngDia")).Address = Range("rngDia").Address Then
        With ComboBox1
            .Visible = True
            .Top = ActiveCell.Top
            .Height = ActiveCell.Height
            .Width = ActiveCell.Width
            .LinkedCell = ActiveCell.Address
        End With
    Else
        ComboBox1.Visible = False
    End If
End Sub

De la misma manera podemos definer dinámicamente otras propiedades como el contenido de la lista (ListFillRange), el número de valores a mostrar en el cuadro (ListRows), etc.

Todo sobre listas desplegables en Excel, técnicas avanzadas y descarga gratuita de ejemplos  en la Caja de Herramientas Excel - Listas Desplegables de JLD. Ver la nota o ir a la página de descarga de la guía.

Una alternativa al control Calendario en Excel 2010 – Monthview

Como mencionamos en el pasado el control Calendar (mscal.ocx) fue removido del paquete Office 2010. Esto genera problemas en aplicaciones y modelos de Excel que fueron desarrollados en versiones anteriores y queremos usarla en Excel 2010.

En la nota mencionada sugerimos tres alternativas posibles: usar el Data Picker, el complemento desarrollado por Sam Radakovitz o descargar e instalar el control. Esta última alternativa implica tener que registrar el control para poder usarlo. El problema con esta última solución es que no podemos registrar el control en Windows 8.

Para los que, como a mí, no les gusta el Data Picker, existe la posibilidad de usar el control Monthview (para las versiones 2007 y 2010 de Excel).

¿Cuándo queremos usar el control? Cuando necesitamos asegurarnos que el usuario ingrese fechas en el rango y en el formato necesarios, librándolo al mismo tiempo de tener que ingresar los separadores (”/” o “-“) de la fecha.

En esta nota mostraré como implementar un modelo sencillo: al elegir una celda determinada aparece el calendario y al elegir una fecha en él, ésta aparece en la celda y el calendario desaparece

Paso 1 – Crear un Userform

En el editor de VB (Alt+F11) creamos un userform que contendrá el control

Cambiamos la propiedad Name a “UfFecha” (no es indispensable pero es una buena práctica) y cambiamos Caption a “Elija una fecha”.

Paso 2 - Insertar el control Monthview

Apuntamos al cuadro ToolBox y abrimos el menú contextual con un clic en el botón derecho del mouse

Elegimos al opción Aditional Controls y marcamos el Microsoft Monthview Control

Este aparecerá ahora en la parte inferior del cuadro ToolBox. Con un clic lo seleccionamos y lo insertamos en el Userform

Paso 3 – Definiciones del control

Al seleccionar el control podemos ver el cuadro de propiedades en el editor. Esto nos permite definir su apariencia y comportamiento. Por ejemplo, definimos la propiedad ShowTodat como True, de manera que al abrirse el control muestre la fecha corriente.

Paso 4 – Códigos de los controles.

Ahora necesitamos escribir los códigos que manejen el uso del control. En nuestro modelo queremos que cuando el usuario seleccione la celda C3 (hemos creado un nombre definido que se refiere a la celda: “clFecha”) aparezca el calendario. Esto lo hacemos usando el evento Worksheet_SelectionChange de la hoja

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    If Union(Target, Range("clFecha")).Address = Range("clFecha").Address Then
        ufFecha.Show
    End If

End Sub

El código lo ponemos en el módulo de la hoja (abrimos el módulo de la hoja con la opción Ver Código del menú contextual que se abre al hacer clic derecho en la pestaña de la hoja o con un doble clic sobre el icono de la hoja en el editor de Vb)

Ahora necesitamos un código para pasar el valor elegido en el calendario a la celda

Private Sub MonthView1_DateClick(ByVal DateClicked As Date)
    ActiveCell.Value = DateClicked
    Unload ufFecha
End Sub

Este código va en el módulo del Userform que se abre haciendo un doble clic en el Userform (o apretando Ctrl+Tab hasta llegar al módulo)

Para que el control aparezca junto a la celda de la fecha y no en el centro de la hoja, agregamos el evento UserForm_Activate

Private Sub UserForm_Activate()
    With Me
        .Left = ActiveCell.Left + ActiveCell.Width + 25
        .Top = ActiveCell.Top + 150
    End With
End Sub

El modulo del Userform se verá así

Un tip para el final: si hacemos unn clic sobrel el nombre del mes en el control aparecerán unas flechas que nos permiten saltar de año en año

El archivo con el ejemplo puede descargarse aquí.

Registrar fecha y hora de una entrada en Excel con eventos

En la nota anterior vimos como registrar la fecha y hora de una entrada (timestamp) usando fórmulas.

Por supuesto puede hacerse también con macros, más precisamente, programando un evento.

El código del evento, que ponemos en el módulo de la hoja correspondiente, es el siguiente:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rngColumnaNombre As Range
   
    Set rngColumnaNombre = Range("A:A")
   
    If Union(Target, rngColumnaNombre).Address = rngColumnaNombre.Address Then
        Select Case Len(Target)
            Case Is > 0
                Target.Offset(0, 1) = Now
            Case Else
                Target.Offset(0, 1).ClearContents
        End Select
    End If
      
End Sub

En nuestro ejemplo la columna A contiene los nombres y la columna B el registro de la fecha. Si borramos un nombre, también la fecha es eliminada.

El código evalúa la celda activa cuando se produce un cambio en la hoja; si la celda activa está en la columna A y no esta vacía, se registra la fecha y hora en la celda paralela de la columna B.

En este código definimos una variable de tipo rango

Set rngColumnaNombre = Range("TablaNombres").Columns(1)

La variable se refiere a la primera columna de la tabla “TablaNombres”

Esto nos permite crear una rango dinámico de manera que valores introducidos fuera de la tabla no disparan el evento.

Existen, por supuesto, otras formas de crear rangos dinámicos en el código.

1 – crear un nombre que se refiera al rango dinámico usando una fórmula con las funciones DESREF o INDICE; por ejemplo

=Hoja1!$A$2:INDICE(Hoja1!$A:$A,CONTARA(Hoja1!$B:$B)+1)

El código es

Private Sub Worksheet_Change(ByVal Target As Range)
   
    If Union(Target, Range("TablaNombres")).Address = Range("TablaNombres").Address Then
        Select Case Len(Target)
            Case Is > 0
                Target.Offset(0, 1) = Now
            Case Else
                Target.Offset(0, 1).ClearContents
        End Select
    End If
      
End Sub

2 – definir el rango en el código

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim TablaNombres As Range
   
    Set TablaNombres = Range(Range("A2"), Cells(Range("A2").End(xlDown).Row + 1, 1))
    If Union(Target, TablaNombres).Address = TablaNombres.Address Then
        Select Case Len(Target)
            Case Is > 0
                Target.Offset(0, 1) = Now
            Case Else
                Target.Offset(0, 1).ClearContents
        End Select
    End If
      
End Sub