Rangos dinámicos con la función INDIRECTO de Excel.

Excel permite construir rangos dinámicos, tema que ya hemos tratado en diversas oportunidades en este blog. Rangos dinámicos son aquellos cuya referencia (dirección) se expande o contrae con los cambios en el número de miembros del rango. Estos rangos se definen con fórmulas, por lo general con la función DESREF. También hemos visto que podemos referirnos dinámicamente a un rango usando su nombre como argumento de la función INDIRECTO, por ejemplo cuando creamos listas desplegables dependientes. Pero esto genera un problema con los rangos dinámicos: INDIRECTO no acepta fórmulas como argumentos, sólo texto.
En esta nota veremos un rodeo sencillo a este problema, sin usar macros o funciones definidas por el usuario.




Por ejemplo, si queremos construir una lista desplegable que muestre las sucursales de red

Para crear la lista desplegable usamos validación de datos, con la opción Lista donde usamos como referencia el nombre Sucursales que contiene el rango D2:D13

La referencia al rango en el nombre es absoluta, por lo que si agregamos sucursales a continuación del último valor de la lista, deberemos editar el nombre y cambar la referencia.
Para lograr que la lista se actualice automáticamente al agregar nuevas sucursales tenemos que definir el rango como rango dinámico. Para esto usamos la función DESREF
=DESREF(Hoja2!$D$2,0,0,CONTARA(Hoja2!$D:$D),1)
Aquí pueden leer una explicación detallada sobre la función DESREF (OFFSET en la versión inglesa).
Ahora vamos a agrupar las sucursales por zonas

La idea es elegir una zona en B3 y que la lista desplegable en B4 muestre sólo las sucursales correspondientes. Para esto usaremos validación de datos con la opción lista y en Origen pondremos INDIRECTO(B3). Esto funciona bien si usamos referencias absolutas. Por ejemplo, definimos el nombre “absOeste” como $H$3:$H$5

Nótese que la fórmula en Origen es =INDIRECTO(“abs”&B3), es decir concatenamos el nombre de la zona en B3 con “abs” para obtener el nombre “absOeste” que le hemos puesto al rango.
Si agregamos la sucursal 13 en H6, ésta no queda incluida en el rango del nombre. Podemos usar la fórmula “tradicional” con DESREF para crear el nombre “dinOeste”

Al tratar de crear la lista desplegable con validación de datos recibimos esta advertencia

Como explicamos más arriba, INDIRECTO no puede evaluar fórmulas, sólo texto. En lugar de DESREF o fórmulas definidas por el usuario (macros) como sugieren algunos sitios y foros, podemos usar la funcionalidad Tablas en (Listas Excel Clásico).

Veamos el proceso, primero en Excel 2007 y luego en Excel Clásico.
En B2 creamos una lista desplegable con validación de datos poniendo los nombres de las zonas directamente en la ventanilla Origen

Para crear el rango dinámico Norte seleccionamos las celdas E2:E5, activamos la pestaña Insertar y pulsamos Tabla. Marcamos la opción “La tabla tiene encabezados” y pulsamos Aceptar

Seguidamente activamos Herramientas de Tablas y en Nombre de la tabla cambiamos el nombre por defecto por Norte

Repetimos el mismo proceso para las restantes tres zonas. Ahora en B4 ponemos una lista desplegable con Validación de Datos-Lista y la fórmula =INDIRECTO($B$3)

Las tablas se expanden automáticamente, por lo que al agregar la sucursal 13 en la zona Oeste, ésta aparecerá en la lista desplegable.
En Excel Clásico (versiones 97-2003) usamos la misma técnica pero con algunas diferencias.
En lugar de Tablas, la funcionalidad en Excel Clásico es Listas. Para convertir un rango en una lista usamos el menú Datos-Lista-Crear Listas.
En Excel Clásico no tenemos la posibilidad de darle un nombre a la lista, por lo que usaremos el menú Insertar-Nombre-Definir

Seleccionamos el rango F4:F6 y creamos el nombre Norte. De la misma manera creamos las listas y los nombres del resto de las zonas. Al agregar nuevas sucursales en las zonas, la lista se expande automáticamente

El archivo del ejemplo se puede descargar aquí.

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.

JLD en Castellano – balance del cuarto año

El tiempo vuela (o como diría mi profesora de Latín, tempus fugit). En unos días concluirá esta primera decena del siglo 21 y ha llegado el momento de presentar las estadísticas del blog a mis socios, sus lectores.

A pesar del título de la nota, sólo llevo estadísticas completas desde el año 2007 (gracias a los servicios de Statcounter), por lo que estaremos analizando estos últimos tres años.

En el 2009 470,000 lectores han visto casi 950,000 páginas. Esto significa un crecimiento de algo más del 11% en lectores y del 4% en las páginas vistas.

El blog ha seguido creciendo, conteniendo ahora 418 entradas que han merecido algo más de 3400 comentarios (incluyendo mis respuestas).

Estas estadísticas nos dan poca información así que decidí, siguiendo la corriente de mis últimas notas, organizar los datos en un dashboard

El gráfico en la sección izquierda del tablero nos permite comparar datos mensuales por año

La lista desplegable en el encabezado nos permite visualizar distintos tipos de datos y analizar sus tendencias.

Así vemos que el número de páginas vistas en el 2009 se ha mantenido estable, mientras que en el 2007 el crecimiento fue acelerado y la desaceleración empezó en el 2008.

Vemos que el número de visitantes se ha comportado de la misma manera

Otro efecto notable es que la cantidad de lectores y páginas vistas tiende a descender en los meses de julio y agosto (el verano en Europa), alcanza el máximo del año en Octubre para caer “en picada” en Noviembre y Diciembre.

Para agregar más información a partir de los mismos datos, he agregado cuatro gráficos, uno para cada tipo de datos, que muestran las tendencias de los tres años

He agregado líneas de tendencia para hacerlas más evidentes. En todo los casos vemos que la tendencia es ascendente pero también podemos observar que los últimos meses están por debajo de la línea de tendencia.
Podemos ver que el 2009 se diferencia en su comportamiento de los años anteriores y que ningún mes logro superar las cifras del 2008. Espero que esta tendencia se revierta en el año entrante.
Donde ha habido un gran crecimiento es en la cantidad de lectores del feed (de 30 en enero del 2007 a 1307 en diciembre del 2009).

Sólo me queda por desear a mis lectores lo mejor para el 2010 y agradecer a todos por el apoyo, por los halagos (a veces inmerecidos) y también por las (por lo general merecidas) observaciones.

Salud!

pd.: quien haya descargado alguno de los tableros anteriores (dashboard-1 o dashboard-2) y quiera recibir el archivo del tablero de esta nota sólo tiene enviarme un mail usando la misma dirección usada para la descarga.

Evitar borrar Validación de datos en Excel al pegar datos copiados

Empiezo con una aclaración: por primera vez he eliminado una entrada luego de haberla publicado. Se trata de la entrada donde mostraba un código para evitar el problema de la destrucción de la validación de datos al pegar un valor copiado de otra celda. Había demasiadas circunstancias que provocaban que el código no funcionara. Mea culpa!

Volviendo sobre el tema, una de las debilidades de Validación de Datos es que si un usuario pega un valor en una celda del rango validado en lugar de ingresarlo manualmente, las definiciones de la validación quedan eliminadas.

La única forma de evitar estas situaciones es usar macros, más precisamente eventos.



Buscando en la Internet encontré, entre otras, una solución propuesta por John Walkenbach. El problema con esta solución es que si el usuario en lugar de pegar el valor usa la opción Pegado Especial-Valores (o Fórmulas), el evento no responde y el valor es aceptado.

El código que propongo usa una parte del código de Walkenbach, para el caso que el usuario use la opción de pegado común, y parte de mi código para el caso que use pegar-valores, pegar-fórmulas, Insertar o arrastre el valor a la celda.

En el módulo de Vba de la hoja pegamos estos códigos

Private Sub Worksheet_Change(ByVal Target As Range)
'   rutina desarrollada por Jorge Dunkelman - JLD Excel Blog
'   parte del codigo tomado de la nota de John Walkenbach http://www.j-walk.com/ss/excel/tips/tip98.htm

    Dim rngValid As Range, cell As Range
    Dim Msg As String
    Dim codeValid As Variant

    Set rngValid = Range("rngValidado")

    On Error Resume Next

    If Not HasValidation(rngValid) Then

         Application.Undo
         MsgBox "Valor no válido", vbCritical
         Application.EnableEvents = False
         Target.ClearContents
         Application.EnableEvents = True
     End If

    For Each cell In Target
        If Union(cell, rngValid).Address = rngValid.Address Then
            codeValid = ActiveCell.Validation.Value
            If codeValid = True Then
                Exit Sub
            Else
                MsgBox "Valor no válido", vbCritical
                Application.EnableEvents = False
                cell.ClearContents
                cell.Activate
                Application.EnableEvents = True
            End If
        End If
    
    Next cell

    

End Sub

Private Function HasValidation(r) As Boolean
    Dim x

    On Error Resume Next
    x = r.Validation.Type
    If Err.Number = 0 Then HasValidation = True Else HasValidation = False
End Function


Nótese que estamos usando un evento Worksheet_Change y una función UDF.

Al rango B3:B12 de la hoja hemos aplicado validación de datos con la opción Lista. Los valores permitidos son a1, a2, a3 y a4.

Este video muestra cómo funciona la macro




El archivo con el ejemplo y el código puede descargarse aquí.