Filtro avanzado

TEMAS

1. Filtros (Filter)

2. Validación de Datos (Data Validation)

3. Cuadro Combinado (Combo Box)

4. Formato Condicional (Conditional Formatting)

5. Formulas (Functions)

6. Tablas Dinámicas (Pivot Table)

7. Consultas (Query)

8. Solver

9. Macros

1 . Filtros 

El comando Filtro avanzado permite filtrar una lista en su lugar, como el comando Autofiltro, pero no muestra listas desplegables para las columnas. En lugar de ello, tiene que escribir los criterios según los cuales desea filtrar los datos en un rango de criterios independiente situado sobre la lista. Un rango de criterios permite filtrar criterios más complejos.

Ejemplos

Con base a la siguiente tabla realizar filtros avanzados

Filtrar por los códigos 300, 568 y 654

Código mayor de 800 o sean de Bogotá o sean del área de Gerencia

2. Validación de Datos 

Muchas veces requerimos garantizar que los datos que capturamos en una celda cumplan con ciertas condiciones por Ej.

a. Que las cantidades capturadas solo sean positivas

b. Que las cantidades capturadas estén dentro de un rango (Ej. Entre 10 y 50)

c. Que solo una lista con 5 nombres sea posible capturar en una celda.

d. Que las fechas capturadas en una celda solo estén dentro de un rango (Ej. Entre el 1 de abril 2002 y el 30 de agosto 2003)

e. Que una hora capturada solo esté entre un rango especifico del día (entre las 6:00 a.m. y las 6:00 p.m.)

f. Que el texto digitado dentro de una celda tenga una longitud determinada (Ej. Igual a 6 caracteres)

g. O que corresponda al comportamiento de una formula.

En general muchas son las razones por las cuales uno desea validar o restringir los datos que va a capturar en una celda, por facilidad en la captura, para conservar la integridad de los datos, para minimizar posibles errores en los cálculos etc. a continuación vamos a usar una herramienta que Excel ya trae incorporada para realizar todo lo anterior y mucho más.

Data Validation

Esta opción se encuentra en el menú de la siguiente forma:

Data  Validation...

Aparece el siguiente cuadro de dialogo:

Este cuadro de dialogo posee 3 pestañas que explico a continuación:

• La pestaña “Setting” donde se establece el criterio de validación que se quiere utilizar y posee lo siguiente:

De lo anterior vale aclarar:

Tipo de validación que se quiere realizar se encuentra las siguientes posibilidades:

Any Value: No se realiza validación

Whole Number: Para validad números enteros

Decimal: Para validar números decimales

List: Para validar cualquier tipo de dato contenido en una lista

Date: Para validar fechas

Time: Para validar horas

Text Long: Para validar longitud de un texto

Custom: Para personalizar la validación de acuerdo con una formula o función.

Los criterios de validación depende del tipo de validación que se quiere realizar

• La pestaña “Input Message”

El input message es opcional, y se utiliza si queremos desplegar un mensaje en el momento en que es seleccionada la celda que contiene la validación, el mensaje tiene dos elementos que son el título y el contenido del mensaje. Podríamos realizar el siguiente razonamiento para valorar o justificar el uso del mensaje de entrada... ¿para qué esperar a que una persona cometa un error al capturar un dato si antes de que lo escriba le podemos decir a través de un mensaje cuales son los valores válidos?

El mensaje desplegado se muestra a continuación:

• La pestaña “Error Alert”

El Error Alert también es opcional y se usa para desplegar un mensaje indicando que el dato capturado no es válido, el mensaje tiene dos componentes, el título y el contenido del mensaje, adicional tiene un “Style” que se usa para escoger cual icono desea desplegar con el cuadro de mensaje.(los iconos disponibles son Stop, Warning e Information)

El mensaje desplegado se muestra a continuación:

Si intentamos capturar el valor 80 se despliega el siguiente mensaje:

En este punto se decide si se desea corregir el valor o si ya no se desea capturar, en cuyo caso deja el valor inicial.

A continuación realizo los ejemplos que plantee al inicio de este documento. Para cada ejemplo muestro los pantallazos para cada pestaña.

En todos los ejercicios el primer paso es seleccionar la celda o el grupo de celdas a la cual se desea aplicar la validación de datos

a. Que las cantidades capturadas solo sean positivas (incluido el cero)

b. Que las cantidades capturadas estén dentro de un rango (Ej. Entre 10 y 50)

c. Que solo una lista con 5 nombres sea posible capturar en una celda

• Hay dos formas de incorporar la lista para la validación, la primera es digitando la lista directamente y la segunda es enlazando la lista en una hoja de Excel (para este caso hay una limitante, porque la lista debe estar en la misma hoja donde están los datos a validar), en este ejemplo voy a mostrar los pantallazos de los dos casos, con ambos se obtiene el mismo resultado final, pero enlazar con celdas es muy práctico especialmente para el caso de listas demasiada extensas, de todas formas usted decide cual le conviene mas.

Pantallazo cuando se introduce la lista directamente.

En la hoja de Excel veríamos..

Lo cual nos permite seleccionar un nombre de la lista, también es posible digitarlo directamente, siempre y cuando coincida con los existentes dentro de la lista.

Pantallazo enlazando la lista con una hoja de Excel.

Como se observa el source apunta a un rango de celdas.

Las demás pantallas quedarían así:

d. Que las fechas capturadas en una celda solo estén dentro de un rango (Ej. Entre el 1 de abril 2002 y el 30 de agosto 2003)

e. Que una hora capturada solo esté entre un rango especifico del día (entre las 6:00 a.m. y las 6:00 p.m.)

f. Que el texto digitado dentro de una celda tenga una longitud determinada (Ej. igual a 6 caracteres)

g. Que corresponda al comportamiento de una formula

Supongamos el siguiente caso: estamos elaborando un presupuesto para tres áreas, y nos dicen que la desviación con respecto al valor base no debe ser superior al 10% ( +/- 10% ). En este caso observamos que la entrada que se quiere validar depende de factores externos.

En este caso los valores que se quieren validar corresponde a las celdas resaltadas en amarillo, pero debe cumplirse que el valor absoluto de la desviación debe ser menor a la sumatoria de lo presupuestado para cada zona multiplicado por el 10%.

Los pantallazos quedarían así:

Como han observado durante todos los ejercicios, muchos valores o condiciones pueden estar directamente dentro del cuadro de dialogo o ligado a una celda, ¿cómo hacerlo? básicamente depende de las necesidades de cada caso y de que tanto pueden cambiar los criterios de validación en el tiempo.

3. Cuadro Combinado 

COMBO BOX, INDEX Y DATE

Vamos a realizar un pequeño ejercicio que utiliza estos tres elementos, pero antes de pasar al ejercicio miraremos cada uno de ellos.

COMBO BOX

El combo box es un control de formulario el cual se encuentra dentro de la barra de herramientas para formularios como se observa a continuación.

Para hacer visible esta barra, se debe hacer lo siguiente:

El combo box muestra una lista desplegable de elementos, cuando se selecciona un elemento de la lista, el combo lo muestra.

El control combo box tiene tres propiedades básicas:

• Input range: (Rango de entrada) Se refiere a una lista de valores en una hoja de Excel, estos valores corresponden a la lista que despliega el control cuando se da clic sobre él.

• Cell link: (Celda de enlace) Es una celda que retorna un valor que representa el elemento seleccionado en la lista. Este número puede ser usado

...