PowerPoint Presentation

Gestin de bases de datos, Filtros Y Tablas DinmicasMauricio Henrquez P.mhenriquez@udec.clIntroduccinUno de los principales usos de Excel a nivel mundial es para la gestin de listas (de telfonos, de transacciones, de tareas, etc), y sus operaciones relacionadas tales como :

Creacin, mantenimiento y manejo de listas.

Caractersticas de las listas:

La primera fila debe constar de rtulos y estos deben ser nicos Cada columna debe contener la misma clase de informacinCada tipo de informacin que se desee utilizar para ordenar, buscar o manipular de forma individual debe estar en una columna separada.Sera ideal que una lista ocupara una hoja de clculo slo para ella.

Empleado.xls

Informtica I2Excel reconoce la fila superior como una fila de encabezado. Si ordena la lista, Excel no tocar la fila de encabezado al reorganizar el resto de la lista.

Validacin de la entrada de datos:

La validacin de datos consiste en asegurarse de que las entradas de los datos satisfacen ciertos criterios tales como: Tipo de dato permitido en la celda (nmeros, fechas, horas o texto) Rango de valores permitido o aceptables (Rut, Edad, codigo_seccion) Lista desplegable de valores aceptables (sexo, ciudad, nacin, codigo_dpto)

Las reglas de validacin pueden ser obligatorias o de advertencia, en esta ltima Excel mostrar un cuadro de dialogo con un mensaje y dar al usuario una oportunidad para rehacer la entrada de celda.

Se puede especificar un mensaje que aparezca siempre que este seleccionada la celda :

Ejemplo :

Para definir criterios de validacin de datos, seleccione el rango de celdas en el que desea aplicar el criterio. Luego seleccione del men datos, validacin.

En la celda depto del libro empleados.xls definir la regla de validacin EL rango de posibles entrada es de la : A-E.

Especificacin del tipo de DatoAbra la lista desplegable Permitir de la ficha configuracin del men validacin . Seleccione uno de los tipos en el men desplegable.

Listas de Valores desplegables : Seleccione Lista y luego el rango de la hoja que contiene los valores aceptables .Uso de una formula para validar la entrada:

Ejercicios de Validacin :

Crear un criterio de Validacin para todo los registros del Campo fecha que permita almacenar slo aquellos empleados nacidos desde el 1-1-65 en adelanteSe pide Crear una nueva columna llamada Estado Civil y que de solamente la opcin de almacenar Soltero y Casado.Se pide Crear una nueva columna llamada Calificacin que acepte datos entre 1,0 y 7,0Validar todo los registros del campo sueldo con tal que solo permita almacenar sueldos entre 5000 y 100000 dlares. Adems deber mostrar un mensaje de entrada cuyo ttulo ser Sueldo Mnimo y deber mostrar El rango de Sueldo permitido es de 5000 y 100000 dlares y por ltimo un mensaje de error con ttulo Dato no Valido y que deber especificar El Sueldo esta fuera del Rango permitido.

Ordenar por una nica Columna Para ordenar por una nica columna seleccione una celda cualquiera de dicha columna (Ej: Apellido o salario). Luego clic en el botn Orden Ascendente o en el bot orden descendente.

Ordenar por ms de una columna Puede ordenar por hasta tres columnas a la vez, rellene uno o dos de los cuadros. Ej: Ordene la lista primero por orden descendente de salario y luego por orden ascendente de Apellido

Filtrado de una ListaEl filtrado de una lista es el proceso de ocultar todas las filas salvo aquellas que cumplen unos criterios especficos. Excel proporciona dos comandos de filtrado:Autofiltro : Para criterios simplesFiltrado avanzado : Para criterios ms complejos

Comando AutofiltroSe debe seleccionar una nica celda dentro de su lista. Entonces elegir Datos-Filtro-Autofiltro. Excel muestra flechas desplegables al lado de cada encabezado de la columna de su lista. Haciendo clic en la flecha aparece una lista de valores nicos de esa columna que puede usar para especificar criterios de filtrado.

Ejercicios: Desplegar slo aquellas filas en las que el Dpto. sea B. del archivo empleadosPara generar este subconjunto de datos deber: Posicionarse en la ceda inicial de Dpto. Elija datos, Filtro, Autofitro. Luego, seleccione B, en la lista desplegable.Uso de Autofiltro para encontrar las 10 msLa idea es encontrar los n elementos mayores o menores de una columna numrica, o aquellos elementos que conformen el porcentaje n superior o inferior de un total de columna. Haga clic en la fecha desplegable de la columna y luego seleccionar las 10 ms en la lista.

Ejercicio: Listar los 3 empleados con mayor sueldo de la Empresa. Listar los 4 empleados con mayor edad de la Empresa. Listar los 15 empleados ms recientemente contratados en la Empresa. Listar los 8 empleados ms antiguos de la empresa. Listar los 5 empleados ms jvenes de la empresa Mostrar el 7% de los mejores sueldosUso de la opcin Personalizar para especificar criterios ms complejosCon la opcin Personalizar puede filtrar en funcin de una comparacin de desigualdad o encontrar filas que estn dentro de un rango de valores.Ejercicio:Se necesita desplegar todos los miembros del personal con sueldos mayores o iguales a 90.000. As como aquellos con sueldos menores o iguales a 30.000.

Seleccione el botn de opcin O entre las dos condiciones

Bsqueda de un rango alfabtico de valores de texto

Para ello especifique dos criterios unidos por Y. Ejemplo: Para encontrar todos los apellidos que comienzan por B, C o D. Deber filtrar por la columna Apellido y especifique es mayor o igual que B y es menor que E.Desplegar todos los empleados que nacieron entre el 30-11-50 y el 24-06-73EjerciciosSe pide desplegar en una sola consulta todos los empleados contratados antes del 15-4-1975, as como aquellos que fueron contratados despus del 17-4-1998.Filtrar todos los empleados que nacieron en la dcada del 70Desplegar todos los empleados que estn contratados por los Departamento A y E.Listar todos los empleados cuyos apellidos terminen con la consonante n.Listas todos los empleados cuyos nombres sea David o Peter

Uso de Comodines dentro de los criterios PersonalizadosLa opcin personalizado acepta dos tipos de criterios comodn. Se puede usar un asterisco (*) en representacin de cualquier secuencia de caracteres o una interrogacin (?) para representar un nico carcter.

Ejercicios:

Listar todos los apellidos que comienzan con B. ( es igual a B*)Listar todos los apellidos que terminen en SListar todos los apellidos cuyo segundo carcter sea AListar todas los apellidos cuyo tercer carcter sea RListar todos los nombres que comienzan con w Listar todos los nombres que comienzan con S y que terminen en N El comando filtro avanzado le permite realizar lo siguiente: Especificar criterios que involucren a dos o ms columnas y la conjuncin O Especificar tres o ms criterios para una columna en particular, donde est involucrada, al menos una conjuncin O. Especificar criterios calculados (Ej: Podra listar slo aquellos empleados cuyos sueldos estn por encima del 25% del sueldo promedio).

Cmo especificar un Rango de criterios El comando Filtro avanzado requiere que especifique los criterios de filtrado dentro de un rango de la hoja separado de la lista. Como hay filas enteras que quedan ocultas cuando se ejecuta el filtro, es conveniente colocar el rango de criterios encima de la lista o en otra hoja.Un rango de criterios debe constar de al menos 2 filas.Introduzca uno o ms encabezados de columnas en la fila superior y sus criterios de filtrado en la segunda y sucesivas filas.

Uso del Comando Filtro AvanzadoLa siguiente figura muestra una lista de casas en venta . Suponga que esta interesado en casas con un tamao de parcela (Columna H) con un mnimo de 2 hectreas. Tambin considerar las casas de parcela ms pequeas si estn en la zona de enseanza primaria U (columna J). Para filtrar la lista de modo que se muestren las casas que cumplen alguno de los dos criterios comience creando el rango de criterios como muestra la sgte figura.Un Ejemplo que usa dos columnas unidas por OSe ha creado este rango de criterios encima de la lista . Observe que ambos criterios estn en filas separadas. Esto muestra las filas que cumplen uno de ambos criteriosLuego, elija Datos, filtro, filtro avanzado e introduzca en el cuadro de dilogo la sgte informacin

Nota : En el ejercicio anterior observamos dos criterios que estaban en lneas separadas asemejando el uso de la condicin O.

Si tuviramos dos criterios en la misma fila se considera que estn unidos por una Y, es decir, el filtro ser de solo aquellas filas que cumplan los dos criterios.

El valor U bajo el encabezado Prim indica a Excel que localice cualquier fila q comience con la letra U (Hay implcitamente un carcter comodn asterisco tras la U).UN EJEMPLO QUE USA TRES O EN UNA COLUMNA

Ahora supongamos que desea filtrar la lista para mostrar slo las casas de tres zonas de escuela primaria (U,F o T) en este caso tendramos que incluir slo el encabezado Prim en su rango de criterios, e introducir las letras U,F y T en las filas de abajo del encabezado.

Un ejemplo que usa tanto Y como O

Se desea ver todas las casas de la zona secundaria T o J que tienen al menos 650 metros cuadrados.

EjerciciosSi desea ver todas las casas de la zona superior N o S.

Se desea filtrar de la lista todas las casas de tamao de parcelas mayor a 2,75 hectreas y que se encuentren en una zona primaria S

Se desea ver todas las casas de estilo Rancho o Cortijo

Se desea filtrar todas las casas de la zona primaria S o F y que tengan un Estilo de Bungalow.

Se desea ver todas las casas que posean menos de 100 metros cuadrados y adems aquellas casas de ms de 300 metros cuadrados.

Se desea ver todas las casas que posean menos de 100 metros cuadrados y adems aquellas casas de ms de 300 metros cuadrados y que estas sean de la zona primaria U.

Filtre todas las casas con precios mayores o iguales a 90000 dlares y que tengan un estilo de Rancho.

8 Filtre todas las casas que tengan un Estilo Rancho o Cortijo que se encuentren en la zona primaria(Prim) H y que su precio sean mayores o iguales a 55000 dlares

TABLAS DINMICAS EN EXCEL 2010Tablas DinmicasLas Tablas Dinmicas son una solucin para Bases de Datos Extensas, de las cuales se necesita obtener resmenes de informacin.

Un informe de Tabla dinmica es una tabla interactiva que combina y compara grandes volmenes de datos.

Podr girar las filas y columnas para ver diferentes resmenes de los datos de origen.Para entender este concepto consider la siguiente planilla

Dada esta Planilla, podremos obtener mucha informacin resumida. Supongamos que esta lista es el movimiento de cada venta que se realiza, incluyendo datos como: Vendedor, Mes, Producto, etc.Podramos saber cunto vendi el seor Garca, ordenado por mes y con el detalle de cada producto.Crear una Tabla dinmicaPara Ejecutar el asistente de Tablas dinmicas:Activar cualquier celda de la Lista de datos De la Ficha Insertar, seleccionar la opcin Tabla dinmicaElija dnde desea colocar el informe de tabla dinmica La Hoja se mostrar de la Siguiente Forma, usted deber arrastrar los campos a las reas: Filas, Columnas, Valores o Filtros

Ejemplo de Vista Tabla dinmica

Entendamos mejor la estructura de una Tabla Dinmica:Campo de Columna: Se le asigna una orientacin de Columna en el informe dinmicoCampo de Fila: Se le asigna una Orientacin de Fila en el Informe dinmicoCampo de Datos: Suele contener datos numricos que se resumen en un informe dinmico Campo Filtro: Sirve por ejemplo para ver la informacin de ventas de una sucursal o de un vendedor en particular, acta como campo FiltradorCambiar la Forma de Resumir los Datos

Para cambiar la Funcin en que los datos se procesan, slo debe seleccionar la funcin aplicada por defecto (suma), y luego con el botn derecho seleccionar la opcin Resumir valores por, y elegir la funcin a aplicarCambiar Formato de los valores en la TablaPara cambiar el formato de los datos de la tabla dinmica deber seleccionar la funcin aplicada y luego con el botn derecho seleccionar la opcin Formato Nmero, y elegir el formato deseado.

Cambie adems las Etiquetas de la Fila y ColumnaRevise las posibilidades de configuracin que le entrega el men: opciones de tabla dinmica En la Seccin Mostrar datos como: se puede Escoger las siguientes alternativasDiferencia de :Muestra todos los datos en el rea de datos como una diferencia entre el campo base y el elemento base especificados.

% de:Muestra todos los datos en el rea de datos como un porcentaje del campo base y del elemento base especificados.

% de la Diferencia de:Muestra todos los datos en el rea de datos utilizando

Total en:Muestra los datos de elementos sucesivos como un Total Actual. Deber seleccionar el campo cuyos elementos desee mostrar en un Total Actual

% de la Fila: Muestra los datos en cada Fila como un porcentaje del Total de la Fila% de la Columna:Muestra todos los datos de cada Columna como un porcentaje del Total de la Columna.

% del Total:Muestra los datos en el rea de datos como un porcentaje del total general de todos los datos de la tabla dinmica.Cambie el Estilo de la Tabla DinmicaGrfico Dinmico

Para Generar un Grfico dinmico, solo debe estar en alguna celda de la tabla dinmica y luego ir la Ficha del Men Opciones y presionar el botn Grfico Dinmico.Formulas dentro de una Tabla dinmicaAl seleccionar la tabla dinmica y pinchar la ficha opciones podremos encontrar los trminos campo calculado y elemento calculado, en el botn Clculos.

Al dar clic en Campo calculado aparecer el siguiente cuadro:

Formulas dentro de una Tabla dinmicaAc deber elegir el campo e insertar la formula:

Dar un Clic al botn Sumar para que se agregue el campo calculado a la lista de campos y luego aceptar

Formulas dentro de una Tabla dinmicaAl igual que el Ejercicio anterior, que pasara si existieran bonos diferenciados segn el Departamento.Tendremos que ir a la opcin Elemento Calculado

Nuestra nueva Tabla dinmica quedara :

FIN DE LA PRESENTACINMuchas Gracias