• Author / Uploaded
• José Melara

Citation preview

Loas filtros sirven para obtener una lista mas simple a partir de otra mas compleja, esto se hace eliminando filas que cumplen con determinadas condiciones o criterios, por ejemplo si tenemos la siguiente tabla

podría interesarnos saber los alumnos que tuvieron un porcentaje de asistencia del 70 % por lo tanto eliminaremos todas las filas de alumnos que no cumplan con ese criterio. Para nuestro objetivo, introducimos la tabla en una Hoja de Excel 2007, seleccionamos cualquier celda de lista y vamos a la pestaña "Datos" en el panel "Ordenar y filtrar" donde pulsamos en el ícono señalado por la flecha (que se pone de color anaranjado)

automáticamente aparecen dos controles en las etiquetas como se muestra

si oprimimos en control que muestra la flecha, aparece un menú desplegable que nos permite establecer los criterios que nos interesan

para nuestro objetivo nos interesa la sección recuadrada en rojo, que tiene los porcentajes de asistencia con una casilla de verificación a la izquierda, para nuestro propósito, deberemos destildar todos los porcentajes distintos al 70%

después de aceptar obtenemos la tabla que nos interesa

Este es un ejemplo muy simple que nos sirve para introducir el tema, solo agregaremos que hay dos tipos de filtros:

1. Autofiltros Son del tipo que acabamos de describir, como se vio, los criterios de filtrado se pueden seleccionar de un menú desplegable.. 2. Filtros avanzados En estos filtros, los criterios de filtrado pueden ser mas complejos. y tambien funcionan en forma diferente

Una de las diferencias entre ambos tipos de filtros se puede apreciar con la simple inspección de la siguiente imagen, que es la tabla final vista en una Hoja de Excel

se puede ver que las filas no estan en forma correlativa, con lo que se infiere que las supuestas filas filtradas en realidad estan ocultas y si, por ejemplo, quisiéramos contarlas con la función CONTAR el resultado daría la totalidad de las filas pues se contarían las ocultas también, en algunos problemas puede ser un inconveniente que se supera con los Filtros Avanzados ya que con estos se filtran realmente( y no se ocultan)las filas que cumplen con lo criterios.

FILTROS AVANZADOS INTRODUCCION:

Vamos a explicar el funcionamiento de los Filtros avanzados utilizando la misma tabla que se empleo para AUTOFILTROS .

En los filtros avanzados se utilizan criterios lógicos para filtrar las filas, en este caso, se debe especificar el rango de celdas donde se ubican los mismos, veamos como se procede. En la cinta de opciones debemos ir a la pestaña "Datos" y luego al panel "Ordenar y filtrar" donde oprimimos el botón "Avanzadas" luego aparece el panel "Filtro avanzado"

Veamos que significan cada uno de las acciones que se pueden tomar:

1. Filtrar la lista sin moverla a otro lugar: se filtran los datos en el mismo lugar donde se encuentra la tabla. 2. Copiar a otro lugar: la tabla filtrada puede aparecer en un lugar especificado de la misma Hoja o en otra Hoja de cálculo. 3. Rango de la lista : automáticamente Excel coloca el rango done esta la lista 4. Rango de criterios: es el rango elegido por el usuario para ubicar los criterios de filtrado. 5. Copiar a: esta opción queda habilitada cuando se marca la casilla del punto 2, en cuyo caso deberemos especificar el lugar sonde queremos que aparezca la tabla filtrada, para esto solo es necesario especificar donde estarán los rótulos. 6. Sólo registros únicos: en el caso de haber registros duplicados, mostrar solo uno de ellos.

Para dar un ejemplo simple filtraremos las mismas filas que en la introducción de AUTOFILTROS sin moverla a otro rango (recordemos que en este caso se filtraban todas las filas que no tuvieran un porcentaje del 70%), para hacer esto marcamos en la casilla de verificación de Filtrar la lista sin moverla a otro lugar y luego elegimos 2 celdas, una para el rótulo y otra para el criterio a cumplir, como se muestra en el recuadro rojo(%D%1:%D%2)

el panel queda como se muestra

al aceptar nos queda la tabla filtrada

en este caso las filas se han ocultado como en el caso de autofiltros, para solucionar el problema debemos copiar la tabla filtrada a otro lugar, por ejemplo al rango $E$1:$F$1, quedando el panel emergente como se ve

y la tabla en su nuevo lugar se ve como en la figura

aquí se puede ver que las filas están en forma correlativa y por lo tanto se pueden aplicar funciones, por ejemplo si quisiéremos contar el número de alumnos con un porcentaje de asistencia del 70%, utilizaríamos la función CONTAR y como se puede ver se obtiene el resultado correcto que es 8

Vamos a dar otro ejemplo con dos condiciónes. Supongamos que queremos saber cuantos alumnos tuvieron menos de 70% y mas de 85% de asistencias, en este caso debemos poner 2 condiciónes, por lo que necesitaremos una celda mas en el rango de criterios

y el panel Filtro avanzado se configura como sigue

al aceptar obtenemos la tabla

donde al aplicar la función CONTAR vemos que los alumnos que cumplen los criterios son 6.

TABLAS DINAMICAS INTRODUCCIÓN:

Las Tablas Dinámicas son una forma alternativa de presentar o resumir los datos de una lista, es decir, una forma de ver los datos desde puntos de vista diferentes. El nombre Tabla Dinámica se debe a que los encabezados de fila y columna de la lista pueden cambiar de posición y también pueden ser filtrados. Con las Tablas Dinámicas también podremos preparar los datos para ser utilizados en la confección de gráficos. La comprensión cabal de este tema se obtiene con la práctica y es así como se verá que es uno de los tópicos mas potentes de Excel, principalmente en las versiones mas recientes. Empezaremos con un ejemplo: Una empresa de exportación de máquinas agrícolas tiene la siguiente tabla en una Hoja de Excel 2007.donde figuran los datos del 1º trimestre del año.

a partir de ella se quiere crear una nueva tabla en la que se informe la cantidad de maquinarias exportadas y el detalle de cuantas se vendieron de cada una. Para crear la tabla que nos responda a estas preguntas, nos ubicamos en cualquier celda de la tabla, luego vamos a la pestaña "insertar" panel "Tablas"

en el que pulsamos en "Tabla dinámica", al hacer esto aparece un menú en el que tenemos las opciones de "Gráfico dinámico" y "Tabla dinámica" , pulsaremos en este último como se puede ver

a continuación aparece el panel "Crear tabla dinámica"

en este caso el rango de la tabla base (tabla de partida) queda automáticamente seleccionado, incuyendo los rótulos, también seleccionamos "Nueva hoja de cálculo" y pulsamos en aceptar y automáticamente se crea la Hoja4 en la que se destacan:

1. Una nueva cinta de opciones denominada "Herramienta de tabla dinámica"

2. Un panel llamado "Lista de campos de tabla dinámica" que es una novedad de Excel 2007 y que tiene un rectángulo en la parte superior, donde se ubican los campos o rótulos de la tabla de origen, también hay cuatro rectángulos, en la parte inferior, denominados " Filtro de informe", "Rótulos de columna", "Rótulos de fila" y "Valores" donde irán apareciendo los rótulos de la tabla a medida que los seleccionemos en la parte suprior en forma de botones como el que se nuestra

Los botones se pueden arrastrar de un rectángulo a otro aunque los rótulos que tienen valores numéricos, siempre aparecen en rectángulo "Valores".

como se ve, hasta este momento, tiene las casillas de verificación de rótulos sin marcar , pues bien, es justamente seleccionar las casillas "MAQUINA" y "CANTIDAD" lo debemos hacer en el próximo paso

Observar que aparecen automáticamente 2 botones. Listo ya tenemos la primera tabla con las respuestas pedidas recuadradas en rojo

si nos interesara saber solamente el dato de cuantas fertilizadora y sembradora se exportaron, junto con su total, tenemos un comando de filtrado en la parte superior y así obtenemos la siguiente tabla

luego de aplicar el filtro. Este es un ejemplo didáctico para hacer una introducción, pero se le puede sacar mucho mas el jugo a esta herramienta.

Podemos querer saber el detalle de las máquinas que fueron exportadas y por cual vendedor. En este caso tendremos que seleccionar la casilla del rótulo VENDEDOR y en la nueva Hoja aparece una tabla y el panel "Lista de campos de tabla dinámica"

se ve que en los rectángulos, mas precisamente en el llamado Rótulo de fila, aparece un nuevo botón, el botón VENDEDOR en forma simultánea a la la selección de la casilla de verificación VENDEDOR. La tabla responde a lo que queremos saber, pero le podemos dar otro aspecto arrastrando el botón VENDEDOR al rectángulo "Rótulo de columna"

y la tabla queda como la que esta abajo , luego de haberle dado algo de formato

En esta tabla se puede ver, por ejemplo, que Peña vendió 16 fertilizadoras y un tractor. Sería interesante saber el número de maquinarias exportadas a que país y por cual vendedor. Para hacer lo , verificamos la casilla "País" y filtramos MAQUINA

y se genera la tabla

donde se puede ver que Chuan le vendió 30 máquinas a China. Hasta ahora nuestra tabla dinámica efectúa sumas, pero puede hacer otras operaciones tales como porcentajes, máximos, mínimo y otras mas que iremos viendo. Podemos preguntarnos cual fue la máxima cantidad de maquinarias que vendió Peña. Para hacer esto nos ubicamos en una celda cualquiera de la tabla de arriba y apretando el botón derecho del mouse aparece el siguiente menú emergente

en el que pulsamos en "Configuración de campo de valor", como indica la flecha, con lo que aparece el panel también llamado "Configuración de campo de valor"

en el que si vamos a la pestaña "Resumen por" están las opciones de resumen en las que elegiremos Máx y luego de aceptar, la tabla se transforma en

que nos dice que la cantidad Máxima de maquinarias que vendió Peña es 9, como se ve en el recuadro rojo, en forma adicional podemos ver que esta cantidad fue vendida a Brasil ( verificar con la tabla de partida o tabla base) Este resultado se puede ver con una simple inspección de los datos, que en este caso son tres, pero cuando estos aumentan es donde vemos la utilidad del cálculo de un máximo.

TABLAS DINAMICAS CON RANGOS DINAMICOS Una aplicación de los RANGOS VARIABLES, es cuando trabajamos con TABLAS DINAMICAS, ya que podemos agregar o quitar elementos de la tabla origen de datos (tabla base)sin necesidad de actualizar la referencia al rango en forma manual, o sea que se hace en forma automática. Para hacer esto vamos a utilizas NOMBRES , pero no le vamos a dar un nombre a un rango, le daremos un nombre a una fórmula ( Excel considera a las fórmulas como si fueran rangos)dicha fórmula sera el ANIDAMIENTO entre las funciones DESREF Y CONTARA

como ya se vio, la sintaxis de DESREF es DESREF(referencia ;filas;columnas;alto;ancho)

donde los argumentos serán: referencia: la celda en el ángulo superior izquierdo de la tabla base (A1 si consideramos la tabla del tutorial TABLAS DINAMICAS filas:para este caso es 0 columnas: para este caso es 0 alto: la cantidad de filas en nuestra tabla base ancho: la cantidad de columnas en nuestra tabla base Esta fórmula se anidara con CONTARA para que DEREF se transforme en dinámica quedando =DESREF(Hoja1!$A$1,0,0,CONTARA(Hoja1!$A:$A),CONTARA(Hoja1!$1:$1))

a esta fórmula le daremos el nombre tabla_base_dinamica

con este nombre creamos la tabla dinámica

en este momento ya estamos en condiciones de agregar datos a la tabla base original

si con ella hubiéramos confeccionado la siguiente tabla dinámica

y agregamos otro país, por ejemplo Italia

esta misma tabla se actualiza incorporando este país

luego de ir al panel "DATOS" de la pestaña "OPCIONES" y pulsar en actualizar

FUNCION SI La función SI sirve para tomar decisiones de acuerdo a una condición, por eso podríamos decir que es una función condicional,siendo la condición el resultado de la evaluación de una proposición lógica ( VERDADERO o FALSO), es decir; si el resultado es VERDADERO se hace una cosa, y si es FALSO se hace otra.. Esta función tiene 3 argumentos

Ejemplo: De acuerdo a un informe volcado a una tabla una empresa quiere saber en que meses tuvo pérdidas o ganancias

para lo cual estos datos se ponen en una Hoja de Excel y se usa la función SI de la siguiente manera

arrastrando la función hasta completar, el resultado final es