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

Extraer una muestra aleatoria de datos de una lista en Excel

En una nota anterior mostré una técnica para extraer muestras aleatorias de listas en Excel. Esta técnica incluía el uso de las funciones ALEATORIO.ENTRE, INDICE, CONTARA y una columna auxiliar con la función CONTAR.SI.

Deambulando por la Internet me topé con esta técnica sugerida por Bob Umlas que usa Filtro Avanzado y la función ALEATORIO.

Como ya hemos visto Filtro Avanzado acepta el uso de fórmulas en el área de criterios, lo que le da una gran flexibilidad.

La idea en la técnica de Umlas es usar la fórmula "=ALEATORIO()<x" como criterio, donde "x" es un número mayor que 0 y menor que 1.

Por ejemplo, si queremos extraer una muestra que represente el 10% de la lista usamos "=ALEATORIO()<0.1"

En este ejemplo tenemos una lista de 100 nombres y queremos extraer una muestra aleatoria de 10



Esta técnica tiene un inconveniente: no siempre el resultado será el número esperado. En el ejemplo arriba se puede apreciar que hay 12 nombres en la muestra. Si aplicamos varias veces el filtro veremos que el tamaño de la muestra puede ser mayor o menor que 10.

Para solucionar este problema podemos repetir la acción hasta que la muestra extraída tenga el tamaño deseado. Para automatizar el proceso usamos una macro simple. Grabamos con el grabador de macros las acciones y la macro resultante la asociamos a un botón



Para ahorrarnos el trabajo de contar cuantos nombres hay en la muestra podemos agregar una celda de control con la función CONTARA()

Informes con Filtro Avanzado de Excel

Una de las ventajas de Filtro Avanzado, comparado con Autofiltro, es la posibilidad de copiar las filas filtradas a otra ubicación automáticamente.

En el pasado he mostrado como podemos superar la aparente limitación que impide copiar el resultado del filtro a otra hoja.

Ese truco nos permite crear informes respetando la norma de separar entre datos, cálculos e informes. Principio de buena práctica que nunca me cansaré de enfatizar.

En esta nota daremos un paso adelante y mostraré como crear informes usando Filtro Avanzado en forma dinámica (sin macros).

Supongamos esta hoja que contiene las ventas de una empresa desde el 2006 hasta el 31/05/2008

Al rango de la base de datos (bd!A1:M2156) lo asignamos al nombre "rngFacturas"

Creamos una nueva hoja, "Informe 1" que contendrá un informe que muestre los siguientes campos:

• Cliente
• País
• Vendedor
• Fechas
• Total Facturado

filtrado por fechas.

En la celda B2 de la hoja "Informe 1" introducimos esta fórmula

=bd!H2>=FECHA(2008,5,1)

y en rango B5:F5 los encabezamientos de los campos (columnas) que queremos que aparezcan en el informe

Es importante notar que la celda del criterio (B2) no tiene encabezado; la celda B1 está vacía.

Ahora seleccionamos una celda de la hoja "Informe 1" tal que todas las celdas contiguas estén vacías (por ejemplo, B7); usamos Datos—Ordenar y Filtras—Avanzadas para abrir el menú de Filtro Avanzado

Hacemos clic en la ventanilla "Rango de la lista", apretamos F3 para abrir el menú "Pegar nombre" y seleccionamos "rngFacturas"; en "Rango de Criterios" seleccionamos B1:B2; marcamos la opción "Copiar a otro lugar" y en la casilla "Copiar a" seleccionamos el rango donde pusimos los encabezados de las columnas que queremos que aparezcan en el informe (en nuestro ejemplo B5:F5)

apretamos "Aceptar" y el informe aparece instantáneamente en la hoja

El inconveniente de esta método es que si queremos cambiar la fecha del informe, tenemos que editar la fórmula. Aparentemente podríamos crear una referencia a una celda que contenga la fecha, por ejemplo usar como criterio en la celda B2 esta fórmula: =bd!H2>=F2

El problema de esta técnica es que en el caso de Filtro Avanzado no funciona. El remedio es asignar un nombre a la celda F2, por ejemplo "fecha1"

Ahora la fórmula del criterio es =bd!H2>=fecha1 y Filtro Avanzado funciona.

Vamos a dar un paso más adelante. Usamos esta fórmula para extraer y copiar las ventas entre dos fechas

=Y(bd!H2>=fecha1,bd!H2<=fecha2)

Hemos definido un segundo nombre (fecha2) para la segunda fecha; las celdas del criterio de filtrado la ponemos en la columna A de manera que luego podamos ocultarla y al rango A1:A2, que contiene la fórmula, le asignamos el nombre "celCriterio".

Ahora podemos crear el informe de esta manera

Si queremos generar un nuevo informe, cambiamos los parámetros en las celdas D2 y D3 y aplicamos nuevamente Filtro Avanzado, seleccionando previamente alguna celda vacía. Excel elimina los datos existentes en el informe y los reemplaza con la nueva selección. El único inconveniente es que Filtro Avanzado copia también los formatos de la base de datos y éstos persisten aún en las filas que no contienen datos.

La solución es quitar todos los formatos en la base de datos.

El cuaderno con el ejemplo se puede descargar aquí.

Filtro Avanzado – valores únicos en otra hoja

Ya hemos mencionado en el pasado la posibilidad de extraer valores únicos de una lista con Filtro Avanzado.

En Excel 2007 usamos Datos-Ordenar y Filtrar-Avanzadas

El problema con este método, tanto con Excel Clásico como con Excel 2007, es que aparentemente podemos extraer la lista de valores únicos sólo a la hoja que contiene la lista

Pero si leemos con atención el mensaje de Excel, vemos que dice "Sólo se puede copiar los datos filtrados a la hoja activa". Y remarco "hoja activa", ya que esta es la clave para resolver el "problema".

Cuando queremos copiar valores únicos de una lista a una hoja distinta de la hoja que contiene los datos, sencillamente activamos el Filtro Avanzado desde la hoja que recibirá la lista de valores únicos. De esta manera la hoja activa es la que recibirá los valores únicos.

Veamos el caso en nuestro ejemplo. La lista de nombres, con varios valores repetidos, está en la hoja "lista"; nuestro objetivo es copiar la lista de valores únicos en la hoja "únicos".

Elegimos la celda de la hoja "únicos" donde queremos poner los valores únicos (A1 en nuestro caso) y activamos Filtro Avanzado y ponemos todas las definiciones excepto "Rango de la lista"

Ahora señalamos el rango de la lista

Al apretar "Aceptar" los valores únicos serán copiados al rango deseado.
Antes de descubrir esta solución (y supongo que no soy el primero en descubrirla) me había embarcado a escribir una macro para hacer la tarea. Moraleja: dedicar siempre unos segundos para evaluar si entendemos lo que nos dicen.

Filtro Avanzado en hojas protegidas – una rareza de Excel

Aplicamos Proteger a una hoja de Excel para impedir que el usuario haga cambios o modificaciones en la hoja. Excel nos permite definir excepciones. Por ejemplo, permitir que el usuario pueda usar Autofiltro (a condición que el autofiltro haya sido aplicado antes de proteger la hoja) u Ordenar

Si aplicamos Proteger Hoja sin excepciones, la opción Filtro y Mostrar Todo del menú Datos aparecen deshabilitadas



Si nos fijamos bien veremos que la opción Filtro Avanzado sigue activa. Los programadores de Excel se han olvidado de bloquear esta opción.

Así podemos aplicar Filtro avanzado para filtrar todas las filas donde el valor de la celda en la columna A sea mayor de 2500 a pesar de que la hoja está protegida



Uno de los problemas que surgen es que si bien la opción Filtro Avanzado sigue activa, la opción Mostrar Todo está bloqueada. Es decir, podemos filtrar, pero no podemos quitar el filtro.

Para mostrar las filas ocultas sin tener que cancelar la protección basta con abril el diálogo del Filtro Avanzado y borrar el rango de los criterios.

Microsoft conoce este problema desde la versión 2000, y en la última nota publicado en la base de conocimientos de Excel , Advanced Filter May Function on Protected Worksheet, mencionan que por el momento (noviembre del 2003!) no hay forma de impedir la aplicación de Filtro Avanzado en una hoja protegida.

Una solución posible es proteger todas las celdas y no permitir seleccionar todas las celdas donde pudieran definirse criterios para filtro avanzado. De esta manera, si bien el diálogo del filtro avanzado se abrirá, no habrá posibilidad de definir un rango de criterios, lo que hace que todas las filas queden visibles.

Technorati Tags: MS Excel

Filtrado de fechas en Excel con Autofiltro o Filtro Avanzado

Supongamos que queremos filtrar una lista de fechas de manera de dejar visibles sólo las fechas que caen en miércoles. Podemos hacer esto con Autofiltro o con Filtro Avanzado, pero cuál sería el criterio?
Si usamos Autofiltro necesitamos crear una columna auxiliar que nos dé el día de la semana para cada una de las fechas del rango. Luego filtramos de acuerdo a esta columna auxiliar.



¿Cómo calculamos el día de la semana para cada fecha? Dos posibilidades, una sencilla y otra complicada:

1 - La complicada, con funciones. Con la función DIASEM, por ejemplo

=DIASEM(A2,2) da 3.

Para transformar el resultado 3 en "miércoles" usamos la función ELEGIR de esta manera

=ELEGIR(DIASEM(A2,2),"lunes","martes","miércoles","jueves","viernes","sábado","domingo")

O con la función INDICE:
=INDICE(semana,DIASEM(A2,2)),

donde "semana" es un nombre que contiene un rango con los días de la semana o directamente los días de la semana.

2 - La posibilidad sencilla. Creamos en la columna auxiliar una referencia a la celda con la fecha (en B2 ponemos =A2) y cambiando el formato de B con el formato personalizado "dddd".

Si queremos usar Filtro Avanzado, tenemos que usar una fórmula lógica, es decir, que dé como resultado VERDADERO o FALSO. Agregamos algunas filas en blanco para poner las filas de criterios y en la celda A2 ponemos esta fórmula

=DIASEM(A5,2)=3



Luego usamos el menú de Filtro avanzado






La ventaja de usar Filtro Avanzado es que nos permite copiar los resultados a otro rango de la hoja o usar más de dos criterios para filtrar la lista.

Ahora veamos otros casos que se presentan.

Para dejar visibles sólo fechas que sean el primer día del mes usamos la fórmula

=A2=FECHA(AÑO(A2),MES(A2),1)

Si la fecha en A2 coincide con el primer día del mes el resultado será VERDADERO. En caso contrario, FALSO. Luego filtramos la lista usando como criterio VERDADERO en la columna auxiliar.



Si queremos usar Filtro Avanzado, usamos la misma fórmula como criterio






Para filtrar las fechas que sean el último día del mes usamos la fórmula

=A2=FECHA(AÑO(A2),MES(A2)+1,0)

Para filtrar el primer día hábil del mes usamos la fórmula

=A2=FECHA(AÑO(A2),MES(A2),1)+ELEGIR(DIASEM(FECHA(AÑO(A2),MES(A2),1),2),0,0,0,0,0,2,21

La primer parte de esta fórmula calcula la fecha del primer día del mes. En la segunda parte de la fórmula DIASEM calcula el número de día de la semana de esta fecha. Este resultado es usado como parámetro en la función ELEGIR, que agrega 0 (cero) si el día de semana cae entre lunes y viernes, suma 2 si el día de semana del primer día del mes cae un sábado o 1 si es domingo.

Para filtrar el último día hábil del mes usamos la fórmula

=A2=FECHA(AÑO(A2),MES(A2)+1,0)-(MAX(0,DIASEM(FECHA(AÑO(A2),MES(A2)+1,0),2)-5))

Como en los casos anteriores, si queremos usar Autofiltro usamos la fórmula para construir una columna auxiliar. Si queremos usar Filtro Avanzado usamos la fórmula en la celda de criterio.

Technorati Tags: MS Excel

Filtro Avanzado – otros usos

Continuando al nota anterior sobre Filtro Avanzado, en esta veremos otro uso interesante de esta herramienta.
Supongamos esta lista que muestra la fecha de recepción de las órdenes de nuestros clientes y la fecha en que se efectuó el despacho




Si queremos obtener una lista de los despachos que han tardado más de 7 días en salir, aplicamos Filtro Avanzado usando esta fórmula como criterio

=(C5-B5)>7



Como hemos explicado en la nota anterior, el encabezamiento distinto del rango de los criterios debe ser distinto al de las columnas de la tabla. También podemos prescindir de encabezamiento para el criterio.



El resultado es



También podemos usar funciones más avanzadas. Por ejemplo, si queremos ver todas las órdenes que se despacharon por lo menos 5 días hábiles después de recibidas, usamos la función NETWORKDAYS

Technorati Tags: MS Excel

Usando Filtro Avanzado en Excel

Autofiltro permite filtrar una lista de acuerdo a una serie de criterios. Por cada columna podemos elegir hasta dos criterios, combinándolos con los operadores Y (AND) u O (OR).
Autofiltro es una gran herramienta para filtrar datos, pero en ciertas ocasiones resulta limitada. En muchos de esos casos podemos utilizar Filtro Avanzado.




En una nota anterior en este blog he mostrado uno de los usos de Filtro Avanzado: extraer una lista de valores únicos.

En esta nota veremos otros usos de Filtro Avanzado. Empecemos por considerar esta lista de nombres con sus respectivas edades



Como pueden ver hemos dejado algunas líneas en blanco por encima de la lista. Esto es necesario para permitir introducir los criterios con los cuales queremos filtrar la lista.

Supongamos que queremos filtrar la lista de manera que vemos todas las personas mayores de 40 años o menores de 30 años. El primer paso consiste en copiar los encabezamientos de las columnas en la fila 1; luego escribimos los criterios en las celdas B2 y B3



Ahora abrimos el menú Datos—Filtro--Filtro Avanzado y señalamos la tabla de datos y el rango que contiene los criterios



Apretamos aceptar y Excel filtrará la lista



Para volver a mostrar todas las líneas usamos el menú Datos—Filtro—Mostrar todo



Al escribir los criterios en distintas líneas estamos indicando a Excel que queremos usar el operador O (OR). Si queremos usar el operador Y (AND) tenemos que poner los criterios en la misma línea. Por ejemplo, si queremos mostrar solo aquellos cuyo nombre empieza con la letra R y son mayores de 30 años






Nótese que usamos R* como criterio.

También podemos combinar operadores Y (AND) y O (OR)






También podemos usar fórmulas como criterio. Para esto deben cumplirse dos condiciones:
1 - que den como resultado un valor VERDADERO o FALSO.
2 – el encabezamiento del rango de criterios debe ser distinto del encabezamiento de la columna de datos

Por ejemplo, queremos mostrar sólo aquellos nombres cuya edad sea menor que la edad promedio de la lista. En el rango de criterios ponemos esta fórmula

=B6<PROMEDIO($B$6:$B$15)






Otro ejemplo sería mostrar aquellos nombres que aparecen más de una vez. Para eso usaremos la fórmula

=CONTAR.SI($A$6:$A$15,A6)>1






Otra gran ventaja de Filtro Avanzado es que nos permite copiar la lista filtrada a otra ubicación en la hoja. Volviendo sobre el ejemplo anterior, marcamos la posibilidad "Copiar a otro lugar", por ejemplo a D1






Como podemos ver, Filtro Avanzado permite filtrar listas de maneras que no serían posibles con Autofiltro.



Technorati Tags: MS Excel