Autofiltro de campos en tablas dinámicas

Supongamos esta tabla dinámica (basada en la tabla de facturas de la base de datos Northwind que viene con Office)

Si queremos mostrar sólo los datos de un determinado intervalo (digamos entre el 15/08/2008 y el 15/09/2008) podemos desplegar la lista de fechas y elegir las fechas una por una



Pero como estamos usando Excel 2007, la tarea es mucho más fácil. Sencillamente usamos la opción Filtros de fecha-Entre…



Como podemos ver, las posibilidades de filtrar por criterios de fechas son múltiples

Lo mismo es válido si los valores del campo de filas son texto

En Excel 97-2003 no existen, aparentemente, estas posibilidades.

Digo aparentemente porque con un pequeño truco podemos replicar la funcionalidad de autofiltro en tablas dinámicas que existe en Excel 2007.

Seleccionamos la celda adyacente al borde derecho de la tabla dinámica en la fila de los encabezamientos (en nuestro ejemplo C4) y aplicamos Autofiltro con el menú Datos-Filtro-Autofiltro. El resultado es que el Autofiltro es aplicado a las columnas de la tabla dinámica

Ahora disponemos de la funcionalidad "Personalizar", con la cual podemos aplicar el filtro con criterios personalizados

Extraer las iniciales de un nombre con Excel

Uno de mis lectores me consulta cómo extraer las iniciales de un nombre. En la columna A tenemos una lista de nombres (empleados de una empresa, invitados a un acontecimiento, etc.) y en la columna B queremos obtener las iniciales

En esta nota presentaré dos soluciones, una basada en funciones y la segunda con una UDF (función definida por el usuario).

Solución con funciones

Empecemos por el final: la fórmulas es (respirar hondo) ésta

=ELEGIR(LARGO(A2)-LARGO(SUSTITUIR(A2," ",""))+1,IZQUIERDA(A2,1),IZQUIERDA(A2,1)&EXTRAE(A2,HALLAR("#",SUSTITUIR(A2," ","#",1))+1,1),IZQUIERDA(A2,1)&EXTRAE(A2,HALLAR("#",SUSTITUIR(A2," ","#",1))+1,1)&EXTRAE(A2,HALLAR("#",SUSTITUIR(A2," ","#",2))+1,1),IZQUIERDA(A2,1)&EXTRAE(A2,HALLAR("#",SUSTITUIR(A2," ","#",1))+1,1)&EXTRAE(A2,HALLAR("#",SUSTITUIR(A2," ","#",2))+1,1)&EXTRAE(A2,HALLAR("#",SUSTITUIR(A2," ","#",3))+1,1))

¿Cómo funciona esta fórmula?

La expresión

LARGO(A2)-LARGO(SUSTITUIR(A2," ",""))+1

calcula de cuantas palabras está compuesto el nombre. La primer función LARGO da la cantidad de caracteres en la celda, incluidos los espacios. La segunda función LARGO calcula la cantidad de caracteres sin los espacios. La diferencia entre ambas más 1, es el número de palabras.

De acuerdo a este valor, la función ELEGIR activa la expresión adecuada para extraer las iniciales.

La primer inicial será siempre la primer letra del nombre, lo que logramos con la fórmula IZQUIERDA(A2,1) . Este es el primer caso de la función ELEGIR.

Para encontrar la segunda inicial tenemos que encontrar donde comienza la segunda palabra. El truco aquí es reemplazar el primer espacio con un carácter especial (por ejemplo #). Para sustituir el primer espacio con el carácter # usamos

SUSTITUIR(A2," ","#",1)

Para hallar donde se encuentra este carácter en el nombre usamos

HALLAR("#",SUSTITUIR(A2," ","#",1))+1

Y finalmente, para extraer la inicial usamos

EXTRAE(A2,HALLAR("#",SUSTITUIR(A2," ","#",1))+1,1)

Ahora tenemos que concatenarlo con la primer inicial lo que hacemos con el operador &

IZQUIERDA(A2,1)&EXTRAE(A2,HALLAR("#",SUSTITUIR(A2," ","#",1))+1,1)

Para el caso que el nombre tenga tres palabras usamos la misma fórmula concatenando a la anterior la expresión

EXTRAE(A2,HALLAR("#",SUSTITUIR(A2," ","#",2))+1,1)

Quedando

IZQUIERDA(A2,1)&EXTRAE(A2,HALLAR("#",SUSTITUIR(A2," ","#",1))+1,1)&EXTRAE(A2,HALLAR("#",SUSTITUIR(A2," ","#",2))+1,1)

Para el caso de cuatro palabras en el nombre concatenamos a la expresión anterior

EXTRAE(A2,HALLAR("#",SUSTITUIR(A2," ","#",3))+1,1)

Esta fórmula tiene algunas serias limitaciones:

• Puede extraer iniciales para nombres de hasta cuatro palabras (si bien podemos agregar otras expresiones y adaptarla para cinco o más palabras en el nombre)

• En caso de nombres que incluyen "de", la función falla ya que no distingue entre minúsculas y mayúsculas

• Otro problema surge si entre las palabras del nombre hay más de un espacio.

Una función definida por el usuario nos proporciona una solución más flexible (y elegante, en mi opinión).

Solución con una UDF

En un módulo común del editor de Vba (preferentemente en el Personal.xls) ponemos este código



Option Explicit

Function Iniciales(strText As String)
    Dim strLen As Long, iChr As Long
    Dim strTemp As String

    strLen = Len(strText)

    For iChr = 1 To strLen
        If Asc(Mid(strText, iChr, 1)) >= 65 And Asc(Mid(strText, iChr, 1))<= 90 Then
            strTemp = strTemp & Mid(strText, iChr, 1)
        End If
    Next iChr

    Iniciales = strTemp
    
End Function

Este código empieza por contar cuantos caracteres hay en la celda que contiene el nombre. Luego evalúa cada carácter y determina si es una letra mayúscula (Asc>=65 o Asc<=90). Si es mayúscula agrega el carácter a la variable strTemp. Al terminar la rutina, la función recibe el valor de strTemp.

Activar una macro desde un cuadro combinado (ComboBox)

En la nota anterior vimos como activar una macro a partir de la elección en una lista desplegable. Esta lista fue creada con validación de datos.

Podemos también crear listas desplegables usando el control cuadro combinado (ComboBox). Pero en ese caso tendremos que usar técnicas un poco distintas de las que mostramos ayer.

Empecemos por señalar que hay dos tipos de controles: los de la barra de formularios y los ActiveX (tema que ya fue tratado en este blog). Los controles de la barra de formularios son fáciles de usar pero no pueden ser programados. Los ActiveX al poder programarlos son más flexibles, pero requieren un poco más de conocimientos.

Siguiendo con el ejemplo de la nota anterior, veremos cómo implementarlo con ambos tipos de controles.


Cuadro combinado de la barra de formularios.

Empezamos por crear las tres macros en un módulo común del editor de Vba

Sub dia()
    MsgBox "Buenos dias"
End Sub
Sub tarde()
    MsgBox "Buenas tardes"
End Sub

Sub noche()
    MsgBox "Buenas noches"
End Sub

Luego creamos una lista con los nombres de las macros y le asignamos un nombre (en este caso usamos el cuadro de nombres para crearlo)

El próximo paso es pegar el cuadro combinado y definir sus propiedades. En la pestaña Programador

Una vez creado abrimos el menú de propiedades del control apretando el botón derecho del mouse.

En el rango de entrada ponemos el nombre que se refiere al rango con la lista de macros y en la casilla de vincular con celda ponemos alguna celda, no necesariamente en la misma hoja y no necesariamente visible

Ahora podemos ver que al elegir la opción "dia", la celda vinculada recibe el valor 1 (ya que "dia" es el primer elemento de la lista). Si elegimos "tarde" la celda vinculada recibe el valor 2.

El paso siguiente es crear una macro lo que haremos abriendo el menú contextual del control (clic con el botón derecho) y eligiendo la opción Asignar macro y apretando el botón Nuevo

En el editor de Vba veremos que Excel ha creado una macro, sin código en esta etapa

En esa macro ponemos este código

Sub Listadesplegable1_AlCambiar()
    Dim intCaseSel As Integer

    intCaseSel = Range("C8")

    Select Case intCaseSel
        Case 1: Call dia
        Case 2: Call tarde
        Case 3: Call noche
    End Select
      
    
End Sub


Cada vez que un valor del cuadro combinado es elegido, la instrucción Select activa la macro correspondiente según el valor de la celda vinculada al control.


Cuadro combinado ActiveX

Empezamos seleccionando el cuadro combinado de los controles ActiveX

y pegándolo en algún lugar de la hoja. En el momento en que terminamos de pegar el control, la hoja pasa al modo "Diseño", lo que nos permite definir las propiedades del control. Abrimos el menú de propiedades (no el de formato!) y definimos el rango de los valores de la lista (ListFillRange)

En el próximo paso abrimos el menú Ver código. Excel crea un evento Change para el control,

donde ponemos este código

Private Sub ComboBox1_Change()
    Application.Run ComboBox1.Value
End Sub

Nótese que el código está en el módulo de la hoja que contiene el control. El evento usa el método Run con el valor elegido en el cuadro combinado (ComboBox1.Value) para activar la macro adecuada.

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.