sesión3filtroavanzado
TRANSCRIPT
"Todo lo que se hace se puede medir, sólo si se mide se puede controlar, sólo si se controla se puede dirigir y sólo si se dirige se puede mejorar"
Dr. Pedro Mendoza A.
Filtro Avanzado
Objetivo
Filtrar datos utilizando las opciones avazadas
Contenido
Filtros avanzados
Tablas de criterios
Uso de caracteres comodines: * e ?
Funciones de base de datos: BDsuma, BDpromedio, BDmax,
BDmin, Dcontar, BDcontara
Página: 2
Filtro avanzado
Instituto de Educación Superior Tecnológico Privado
1. Filtrado de una lista Como funciona una filtración
La filtración constituye un método fácil y rápido para encontrar subconjuntos de datos en una lista y trabajar con ellos.
Cuando se filtra una lista sólo visualizará las filas que cumplen un conjunto de condiciones de búsqueda llamado criterios.
A diferencia de la ordenación, la filtración no reorganiza las listas. La filtración oculta provisionalmente las filas que no desea mostrar.
Cuando Excel filtra las filas, la hoja de cálculo se coloca en el modo de filtración. En este modo se podrá editar, dar formato, efectuar representaciones gráficas e imprimir la lista de subconjuntos sin tener que reorganizarla o moverla.
¿Cómo aplica un filtro automático?
Ubique el puntero dentro de la lista
Seleccione Datos, Filtro, Autofiltro
Trabajaremos está aplicación sobre el archivo: CAJACHI1.XLS (Caja chica)
A B C D E F G
1
2 MUNICIPALIDAD DE LIMA METROPOLITANA
3 RENDICION DEL FONDO ASIGNADO PARA SUFRAGAR GASTOS DIRECC. PLANIFICACION
4
5 DOCUMENTO PARTID.
6 No. FECHA CLASE No D E T A L L E IMPORTE ESPEC.
7 1 22/4 FACT. 11229 PAPELERA EL INCA S.R.LTDA.hojas 3.90 02.06
Flechas desplegables para la selección de criterios de cada campo, haga un clic en la flecha para seleccionar criterio de filtro.
Seleccione las listas desplegables para especificar los registros que desea mostrar.
Vuelva a seleccionar Datos, filtro, filtro automático para anular el filtro automático.
Página: 3
Microsoft Excel Avanzado
1.2 Filtro personalizado Se utiliza para especificar condiciones utilizando operadores booleanos
Siga el proceso normal de creación de filtro automático.
Seleccione la opción Personalizar ... de la lista desplegable del campo a quién desea asignarle un filtro personalizado.
Operador lógico Criterios de filtro personalizado
Seleccione [Aceptar] cuando termine de definir sus criterios de filtro personalizado.
Sólo se puede utilizar una lista con filtro por cada hoja de cálculo.
1.3 Filtro avanzado Filtra la lista en su sitio, no visualiza la listas desplegables de las columnas. Se debe definir una tabla de criterios externa. Cuando se filtra la lista se oculta temporalmente todas las filas que no cumplen con los criterios especificados. No se puede realizar filtraciones sucesivas usando el comando Filtro avanzado. Si cambia los datos en la tabla de criterios y vuelve a filtrarlos, Excel aplicará los criterios tanto a las filas como a las filas mostradas que aparecen en la lista.
1.4 Tabla de Criterios Es un rango de celdas que incluye el nombre de campo en una fila (debe ser idéntico a los nombres de campo de la lista) y una condición en la siguiente fila, se puede utilizar varias filas de criterios. Es recomendable ubicarlas en la parte superior o inferior de la tabla para que no sean ocultadas cuando se ejecuta la filtración. Existen dos tipos de criterios avanzados: criterios de comparación y criterios calculados múltiples.
Página: 4
Filtro avanzado
Instituto de Educación Superior Tecnológico Privado
Tipos de criterios de comparación
Una serie de caracteres que desea hacer coincidir.
Para localizar datos que contengan un valor exacto, escriba el texto, número, fecha o los valores lógicos en la celda debajo del nombre de campo de la tabla de criterio. Si introduce texto como criterios, se localizará todos registros que el contenido del campo evaluado comiencen con ese texto. Para localizar solamente los que cumplan con el texto especificado, escriba la fórmula =‘=texto’ donde texto representa el dato que desea localizar
Ejemplos:
Tabla de criterios localiza
todos los registros cuya
FECHA sea igual a 26/4 y
ESPEC igual a 02.06
Tabla de criterios localiza todos los
registros cuya FECHA sea igual a 26/4
y ESPEC igual a 03.06
Varias condiciones en una sola columna
Si incluye dos o más condiciones en una sola columna, escriba los criterios en filas independientes, una directamente bajo otra. Por ejemplo, el siguiente rango de criterios presenta las filas que contienen "Davolio," "Buchanan" o "Suyama" en la columna Vendedor.
Vendedor
Davolio
Buchanan
Suyama
Una condición en dos o más columnas
Para buscar datos que cumplan una condición en dos o más columnas, introduzca todos los criterios en la misma fila del rango de criterios. Por ejemplo, el siguiente rango de criterios muestra todas las filas que contienen "Producto" en la columna Tipo, "Davolio" en la columna Vendedor y valores de ventas superiores a 1.000 $.
Escriba Vendedor Ventas
Generar Davolio >1000
J K
2 FECHA ESPE
C
3 26/4 02.06
J K
2 FECHA ESPEC
3 26/4 03.06
Página: 5
Microsoft Excel Avanzado
Una condición en una columna u otra
Para buscar datos que cumplan una condición de una columna o una condición de otra, introduzca los criterios en filas diferentes del rango. Por ejemplo, el siguiente rango de criterios muestra todas las filas que contienen "Producto" en la columna Tipo, "Davolio" en la columna Vendedor o valores de ventas superiores a 1.000 $.
Escriba Vendedor Ventas
Generar
Davolio
>1000
Uno de dos conjuntos de condiciones para dos columnas
Para buscar filas que cumplan uno de dos conjuntos de condiciones, donde cada conjunto incluye condiciones para más de una columna, introduzca los criterios en filas independientes. Por ejemplo, el siguiente rango de criterios muestra las filas que contienen "Davolio" en la columna Vendedor y valores de ventas superiores a 3.000 $ y también muestra las filas del vendedor Buchanan con valores de ventas superiores a 1.500 $.
Vendedor Ventas
Davolio >3000
Buchanan >1500
Más de dos conjuntos de condiciones para una columna
Para buscar filas que cumplan más de dos conjuntos de condiciones, incluya columnas múltiples con el mismo título. Por ejemplo, el siguiente rango de criterios muestra las ventas comprendidas entre 5.000 y 8.000 $ junto con aquellas inferiores a 500 $.
Ventas Ventas
>5000 <8000
<500
Condiciones creadas como resultado de una fórmula
Puede utilizar como criterio un valor calculado que sea el resultado de una fórmula (fórmula: secuencia de valores, referencias de celda, nombres, funciones u operadores de una celda que producen juntos un valor nuevo. Una formula comienza siempre con el signo igual (=).). Si emplea una fórmula para crear un criterio, no utilice un rótulo de columna como rótulo de criterios; conserve este rótulo vacío o utilice uno distinto a un rótulo de columna del rango. Por ejemplo, el siguiente rango de criterios muestra filas
Página: 6
Filtro avanzado
Instituto de Educación Superior Tecnológico Privado
que tienen un valor en la columna C mayor que el promedio de las celdas C7:C10.
=C7>PROMEDIO($C$7:$C$10)
Notas
La fórmula que utilice con el fin de generar una condición debe utilizar una referencia relativa (referencia relativa: en una fórmula, dirección de una celda basada en la posición relativa de la celda que contiene la fórmula y la celda a la que se hace referencia. Si se copia la fórmula, la referencia se ajusta automáticamente. Una referencia relativa toma la forma A1.) para hacer referencia al rótulo de columna (por ejemplo, Ventas) o al campo correspondiente del primer registro. Todas las demás referencias de la fórmula deben ser referencias absolutas (referencia de celda absoluta: en una fórmula, dirección exacta de una celda, independientemente de la posición de la celda que contiene la fórmula. Una referencia de celda absoluta tiene la forma $A$1.) y el resultado de la fórmula debe ser VERDADERO o FALSO. En el ejemplo, "C7" hace referencia al campo (columna C) del primer registro (fila 7) del rango.
En la fórmula puede utilizar un rótulo de columna en lugar de una referencia relativa a celda o un nombre de rango. Si Microsoft Excel presenta el error #¿NOMBRE? en la celda que contiene el criterio, no necesita tenerlo en cuenta, ya que no afecta a la forma en que se filtra el rango.
Cuando evalúa datos, Microsoft Excel no distingue entre caracteres en mayúscula y minúscula.
1.5 Uso de caracteres comodines: * e ? Los siguientes caracteres comodín pueden usarse como criterios (criterios: condiciones que se especifican para limitar los registros que se incluyen en el conjunto de resultados de una consulta o un filtro.) de comparación para filtros, así como para buscar y reemplazar contenido.
? Localiza un caracter único en la misma posición que el signo de interrogación.
* Localiza cualquier número de caracteres a partir de la posición que ocupa el asterisco.
~ seguido de ?,* o ~ Localiza un signo de interrogación, un asterisco o una tilde.
Página: 7
Microsoft Excel Avanzado
Ejemplos:
Tabla de criterios
localiza todos los
registros
cuya FECHA sea
igual a 26/4 y
ESPEC empiece
con los caracteres
02
Tabla de criterios localiza todos los
registros
cuya FECHA sea igual a 26/4 y
ESPEC empiece con los
caracteres 03
Una cantidad que desea comparar
Para mostrar sólo las filas comprendidas dentro de ciertos limites establecidos. Utilice un operador de comparación seguido de un valor en la celda debajo de los nombres de campos de la tabla de criterios.
Tabla de criterios
localiza todos los
registros cuya FECHA
sea 26/4 y que el importe
sea mayor que 10.
Tabla de criterios localiza todos los
registros cuyo IMPORTE sea mayor que
10 de todas las partidas especificas
Criterios calculados
Evalúan una columna seleccionada de la lista contra valores no contenidos en la lista. Visualizará el nombre lógico: VERDADERA O FALSA. La fórmula introducida debe referirse por lo menos a una columna de la lista. Ejemplo:
Tabla de criterios localiza todos los registros cuyo IMPORTE sea mayor que el importe promedio.
J K
2 FECHA ESPEC
3 26/4 03*
J K
2 FECH
A
ESPE
C
3 26/4 02*
M N
2 FECHA IMPORTE
3 26/4 >10
J K
2 IMPORTE ESPE
C
3 >10 *
M
2 IMPORTE
3 =G8>PROMEDIO($G$8:$G$43)
Página: 8
Filtro avanzado
Instituto de Educación Superior Tecnológico Privado
¿Cómo crea un filtro avanzado?
Cree la tabla de criterio, encima o debajo de la tabla.
Ubique el puntero dentro de la tabla.
Seleccione Datos, Filtros, Filtro avanzado...
Defina el rango de la lista.
Defina el rango de criterios.
Seleccione Aceptar para ejecutar el filtro avanzado.
Si desea volver a filtrar con otros criterios, modifique el criterio o créelo en
otro lugar de la hoja, y vuelva a ejecutar el mismo proceso.
¿Cómo eliminar un filtro?
Para volver a visualizar todos los registros.
Seleccione Datos, Filtro, Mostrar Todo
¿Cómo copiar datos a otro lugar?
Cree la tabla de criterios
Coloque el puntero dentro de la tabla
Seleccione Datos, Filtro, Filtro Avanzado
Defina Rango de la lista, Rango de criterios y luego seleccione Copiar a
Especifique una sola celda a partir de donde desea que se realice la copia
Seleccione ACEPTAR para ejecutar la filtración, excel copiará los nombres de campo y los registros que cumplen con los criterios especificados
Página: 9
Microsoft Excel Avanzado
1.6 Funciones de base de datos En esta sección se describen las 12 funciones para hojas de cálculo empleadas para
los cálculos de bases de datos (o listas) de Microsoft Excel. Cada una de estas
funciones, denominadas colectivamente funciones BD, usa tres argumentos:
base_de_datos, nombre_de_campo y criterios. Estos argumentos se refieren a los
rangos de la hoja de cálculo empleados en la función para base de datos.
Sintaxis: BDfunción(Base de datos;Campo;Criterios)
Cálculos con funciones de base de datos BD
En la siguiente ilustración se muestra una base de datos que contiene la
descripción y comportamiento de varios tipos de árboles. Cada registro contiene
información acerca de un árbol. El rango A5:E11 se denomina HUERTO y el
rango A1:F3 CRITERIOS.
BDCONTAR(HUERTO;"Edad";A1:F2) es igual a 1. Esta función examina los
registros de manzanos cuyo alto varía entre 10 y 16 metros y determina cuántos
campos Edad de esos registros contienen números.
BDCONTARA(HUERTO;"Ganancia";A1:F2) es igual a 1. Esta función examina
los registros de manzanos cuyo alto varía entre 10 y 16 metros, y determina el
número de campos Ganancia de esos registros que no están en blanco.
BDMAX(HUERTO;"Ganancia";A1:A3) es igual a 105.00 , la ganancia máxima
de manzanos y perales.
BDMIN(HUERTO;"Ganancia";A1:B2) es igual a 75.00 , la ganancia mínima de
Ejercicio de aplicación
A B C D E F
1 Arbol Alto Edad Rendimiento Ganancia Alto
2 Manzano >10 <16
3 Peral
4
5 Arbol Alto Edad Rendimiento Ganancia
6 Manzano 18 20 14 105.00S/.
7 Peral 12 12 10 96.00S/.
8 Cerezo 13 14 9 105.00S/.
9 Manzano 14 15 10 75.00S/.
10 Peral 9 8 8 76.80S/.
11 Manzano 8 9 6 45.00S/.
Página: 10
Filtro avanzado
Instituto de Educación Superior Tecnológico Privado
manzanos con un alto superior a 10 metros.
BDSUMA(HUERTO;"Ganancia";A1:A2) es igual a 225.00 , la ganancia total de
manzanos.
BDSUMA(HUERTO;"Ganancia";A1:F2) es igual a 75.00 ; la ganancia total de
manzanos con un alto entre 10 y 16.
BDPRODUCTO(HUERTO;"Rendimiento";A1:F2) es igual a 10, el producto del
rendimiento de los manzanos con un alto entre 10 y 16.
BDPROMEDIO(HUERTO;"Rendimiento";A1:B2) es igual a 12, el rendimiento
promedio de manzanos con un alto de más de 10 metros.
BDPROMEDIO(HUERTO;3;HUERTO) es igual a 13, la edad media de todos los
árboles en la base de datos.
BDDESVEST(HUERTO;"Rendimiento";A1:A3) es igual a 2.97; la desviación
estándar estimada en el rendimiento de manzanos y perales si los datos de la base
de datos son únicamente una muestra de la población total del huerto.
BDDESVESTP(HUERTO;"Rendimiento";A1:A3) es igual a 2.65; la desviación
estándar verdadera en el rendimiento de manzanos y perales si los datos de la base
de datos representan el conjunto de la población.
BDVAR(HUERTO;"Rendimiento";A1:A3) es igual a 8.8; la varianza estimada en
el rendimiento de manzanos y perales si los datos de la base de datos sólo
representan una muestra de la población total del huerto.
BDVARP(HUERTO;"Rendimiento";A1:A3) es igual a 7.04; la varianza real en el
rendimiento de manzanos y perales si los datos de la base de datos representan el
conjunto de la población del huerto.
BDEXTRAER(HUERTO;"Rendimiento”;CRITERIOS) devuelve el valor de error #¡NUM! porque