Material Excel 2007 Ing C Corso 190607

UNIVERSIDAD TECNOLÓGICA NACIONAL - FACULTAD CÓRDOBA Departamento Ingeniería Industrial Asignatura: Informática I

Ing. Cynthia Corso

Unidad 3: Planilla de Cálculo Excel. Temas: - Fórmulas y Funciones. - Filtros o AutoFiltros. - Gráficos. Tipos. Para explicar y desarrollar los temas enunciados, nos basaremos en la siguiente planilla de cálculo.

Esta

planilla pertenece a una agencia de viajes y refleja una proyección de los

ingresos en el tiempo para el primer semestre del año 2008. Fórmulas y Funciones. Una “fórmula” es una ecuación que calcula un valor nuevo a partir de los valores existentes. Las fórmulas pueden contener números, operadores matemáticos o referencias a celdas. Lo que debemos tener en cuenta antes de plantear una fórmula. •

Siempre empiezan con el signo = colocado en la celda donde quiere que aparezca el resultado de la fórmula aplicada.

•

Cada fórmula utiliza uno o más operadores aritméticos.

•

Cada fórmula incluye 2 o más valores que combinan mediante operadores aritméticos.

En el caso de nuestra planilla podemos necesitar averiguar, cual sería el total de ingresos para el mes de Enero. Página-1-

UNIVERSIDAD TECNOLÓGICA NACIONAL - FACULTAD CÓRDOBA Departamento Ingeniería Industrial Asignatura: Informática I

Ing. Cynthia Corso

Entonces para realizar esto, debemos seguir los siguientes pasos: 1- Posicionarse en una celda disponible (donde queremos obtener ese resultado). 2- Una vez posicionado agregamos el signo igual, como se muestra a continuación: Ejemplo1: =B5+B6+B7+B8+B9+B10+B11 Esto obtiene la sumatoria de los ingresos para el mes de Enero, por medio de una fórmula. Ejemplo 2: = (B5:B11) *2 Este es un ejemplo que involucra un rango de datos, de la celda B5 a la B11 y lo multiplica por 2. Es importante destacar que se puede incluir una amplia gama de variantes para escribir fórmulas, por medio de los operadores aritméticos. Funciones. Una “función” es una fórmula predefinida por Excel que opera sobre uno o más valores y devuelve un resultado que aparecerá directamente en la celda donde se introdujo. La sintaxis de cualquier función es: =nombre_función(argumento1;argumento2;...;argumentoN)

Para poder hacer uso de cualquier función en Excel, debemos respetar las siguientes pautas: Siempre se utiliza la dirección de la celda (ejemplo A1, F14 o N32) para la construcción de la función. Esto le da carácter universal a la función,

Página-2-

UNIVERSIDAD TECNOLÓGICA NACIONAL - FACULTAD CÓRDOBA Departamento Ingeniería Industrial Asignatura: Informática I

Ing. Cynthia Corso

permitiendo la automatización inmediata del resultado en caso de modificar alguno de los datos contenidos en la celda. Los argumentos o valores de entrada van siempre entre paréntesis. No se debe dejar espacios antes o después de cada paréntesis. Los argumentos pueden ser valores constantes (número o texto), fórmulas o funciones. Los argumentos deben de separarse por ";" (esto puede variar de acuerdo a la configuración regional del sistema operativo). Por ejemplo si quisiéramos obtener la sumatoria de los ingresos para el mes de Enero por medio de una función en lugar de una fórmula, como se hizo en la sección anterior. Deberíamos usar una de las funciones que se encuentra en la categoría de las Funciones Matemáticas. Esta función se denomina SUMA y implementa de la siguiente manera. Nos posicionamos en una celda libre, donde queremos visualizar este resultado y tipeamos lo siguiente: =SUMA(B5:B11) El operador " : " identifica un rango de celdas, así B5:B11 indica todas las celdas incluidas entre las celdas B5 y la B11. así la función anterior sería equivalente a: =B5+B6+B7+B8+B9+B10+B11 Si nos fijamos en ambas expresiones fórmula y función planteadas aquí, permiten obtener el mismo resultado. Para este caso es mucho más cómodo hacer uso de una función por lo abreviado de la expresión con respecto a la fórmula. Existen muchos tipos de funciones dependiendo del tipo de operación o cálculo que realizan. Así hay funciones matemáticas y trigonométricas, estadísticas, financieras, de texto, de fecha y hora, lógicas, de base de datos, de búsqueda y referencia y de información.

Página-3-

UNIVERSIDAD TECNOLÓGICA NACIONAL - FACULTAD CÓRDOBA Departamento Ingeniería Industrial Asignatura: Informática I

Ing. Cynthia Corso

Acá detallaremos el uso de las siguientes funciones (PROMEDIO, MAX, MIN, SI) que aplicaremos a nuestro ejemplo original. Una función se puede escribir directamente en la celda, pero Excel dispone de una ayuda o asistente para utilizarlas, y así resultará más fácil trabajar con ellas.

Para su inserción debemos respetar los siguientes pasos: Sitúese en la celda donde se quiere introducir la función. Seleccione el menú Insertar. Elija la opción Función... O bien, hacer clic sobre el botón

de la barra

de herramientas. Aparecerá el cuadro de diálogo mostrado a continuación.

En el recuadro Categoría de la función muestra la categoría a la que pertenece la función que va a usar. Si no se está seguro de la categoría a la que pertenece la función requerida se recomienda elegir “Todas”. En el recuadro Nombre de la función hay que elegir la función que se desea haciendo clic sobre ésta. Conforme se selecciona una función, en la parte inferior aparecen los distintos argumentos y una breve descripción de ésta.

Página-4-

UNIVERSIDAD TECNOLÓGICA NACIONAL - FACULTAD CÓRDOBA Departamento Ingeniería Industrial Asignatura: Informática I

Ing. Cynthia Corso

Hacer clic sobre el botón Aceptar. Aparecerá el cuadro que se muestra a continuación. En este ejemplo se eligió la función SUMA.

En el recuadro Número1 hay que indicar el primer argumento o la referencia de la celda donde este el primer dato a ser sumado. En el recuadro Número 2 habrá que indicar cuál será el segundo argumento. Si se introduce un segundo argumento, aparecerá otro recuadro para el tercero, y así sucesivamente. Sin embargo, para nuestro ejemplo, es preferible englobar todos los argumentos de la suma usando el separador de los (:). Por ejemplo: B5:B1. Cuando tenga introducidos todos los argumentos, haga clic sobre el botón Aceptar. Tomando como base nuestro ejemplo, supongamos que deseamos averiguar el valor máximo y mínimo del ingreso para el mes de Enero. Para resolver esto usaremos la función MAX y MIN, que se encuentra dentro de la categoría Estadísticas. Para poder obtener el valor de ingreso máximo implementamos la función de la siguiente forma:

Página-5-

UNIVERSIDAD TECNOLÓGICA NACIONAL - FACULTAD CÓRDOBA Departamento Ingeniería Industrial Asignatura: Informática I

Ing. Cynthia Corso

Posicionados en una celda libre (lugar donde aparecerá el resultado), tipeamos lo siguiente o lo hacemos por medio del Asistente de Funciones, como se explicó en la sección anterior: =MAX(B5:B11) El valor de B5:B11 representa el rango de valores a considerar para obtener el valor máximo de una serie. Cuando presionamos Enter obtendremos el valor máximo de ingreso para el Mes de Enero. Para obtener el valor mínimo siguiendo el mismo procedimiento que en el caso anterior. =MIN(B5:B11) Otro caso de funciones que es bastante útil a la hora de obtener algún tipo de información adicional sobre una tabla, son las funciones que están incluidas en la categoría de “Lógicas”. Una de las funciones lógicas que usaremos en este ejemplo es la función SI. Esta función tiene la siguiente sintaxis para poder expresarla: SI(prueba_logica;valor_si_verdadero;valor_si_falso) Supongamos que hemos incorporado a nuestra tabla original la columna Saldo, cada celda contiene la sumatoria de todos los ingresos para todos los meses considerados. Sobre la base de esto deseamos averiguar lo siguiente. Si el saldo es mayor 80000, colocaremos “Regular” sino “No Regular”. Para ello debemos insertar la función haciendo uso del asistente o tipeandola desde la celda que seleccionemos. Por ejemplo: =SI(N5>80000;"Regular";"No Regular")

Página-6-

UNIVERSIDAD TECNOLÓGICA NACIONAL - FACULTAD CÓRDOBA Departamento Ingeniería Industrial Asignatura: Informática I

Ing. Cynthia Corso

N5: representa la celda que contiene el saldo para ese determinado ingreso. Una vez que se presione Enter aparecerá Regular o No Regular, según se cumpla la condición por verdadero o falso. FILTROS o AUTOFILTROS Este recurso provisto por Excel se utiliza fundamentalmente para optimizar el proceso de consultas que podemos obtener de una tabla con información. Aplicando esta herramienta se muestran las filas que cumplen una determinada condición. Puede elegir dos o más criterios al mismo tiempo, seleccionándolos en las flechas desplegables de las diferentes columnas. Los datos se irán filtrando de acuerdo a los criterios seleccionados. A continuación siguiendo el ejemplo del comienzo, si queremos consultar los ingresos que en Marzo tienen un valor igual a 10.000. Seguimos los siguientes pasos: 1. Seleccionamos “toda la tabla”, incluido las

columnas.(Ingresos, Enero,

Febrero..etc). IMPORTANTE PARA APLICAR FILTRO 2. Luego nos vamos al menú y elegimos la opción Datos

Filtro

AutoFiltro.

3. Una vez que seleccionamos esta opción, automáticamente nuestra tabla cambia de apariencia como se muestra a continuación. Figura A

Página-7-

UNIVERSIDAD TECNOLÓGICA NACIONAL - FACULTAD CÓRDOBA Departamento Ingeniería Industrial Asignatura: Informática I

Ing. Cynthia Corso

Como se muestra en la figura en las columnas de la tabla, se ha incorporado una flecha. Para poder consultar los ingresos del mes de Marzo con un valor igual a 10.000, hacemos un clic con el mouse sobre la flecha de la columna Marzo. Como se muestra en la figura a continuación, nos aparecen una serie de opciones. Figura B

Para nuestro caso debemos seleccionar la opción 10000. En ese caso nos quedarán las filas que cumplan con la condición. Figura C

Si nos fijamos en la figura en la columna que hemos aplicado el filtro esta remarcada con una flecha de color azul.

Es importante recordar que se puede aplicar el filtro en más de una columna. Y en el caso de no encontrar la opción(como se muestra en la Figura B), según lo que deseamos filtrar debemos elegir la opción Personalizar. Que se detalla en la sección AutoFiltro Avanzado.(Pág 9)

Página-8-

UNIVERSIDAD TECNOLÓGICA NACIONAL - FACULTAD CÓRDOBA Departamento Ingeniería Industrial Asignatura: Informática I

Ing. Cynthia Corso

Para poder quitar el filtro y visualizar la tabla que teníamos en la versión original, debemos destildar la opción AutoFiltro. Es decir nos vamos a Datos

Filtro

AutoFiltro (Desactivamos). AUTOFILTRO PERSONALIZADO Si desea adaptar a sus necesidades la consulta, Excel le ofrece el autofiltro personalizado, cuyo objetivo es establecer criterios no especificados en la flecha desplegable. Supongamos que necesitamos consultar las filas de los ingresos cuyo valor es inferior a 15000, para el mes de Marzo. Para esto, despliegue el filtro de la columna Marzo y elegimos la opción (Personalizar...), debe aparecer el siguiente cuadro de diálogo:

En el cuadro de diálogo que se muestra a continuación, en la primera lista de opciones seleccionamos la opción que deseamos, en nuestro caso menor igual que y en el cuadro de al lado debemos colocar el valor que debe ser menor igual que, que es 15000. Es importante mencionar que también se puedan implementar más condiciones, usando los operadores relacionales Y u O. Una vez aplicado este filtro personalizado, sólo quedarán en la tabla las filas que satisfagan esa condición.

Página-9-

UNIVERSIDAD TECNOLÓGICA NACIONAL - FACULTAD CÓRDOBA Departamento Ingeniería Industrial Asignatura: Informática I

Ing. Cynthia Corso

Para sacar el filtro avanzado seguimos la misma operatoria que con la herramienta de AutoFiltro. GRÁFICOS Excel nos brinda un recurso que nos permita representar los datos almacenados en Excel en forma de gráficos. Para ello dispone de un Asistente para Gráficos

, representada por este icono.

El mismo nos guía paso a paso para finalizar un determinado gráfico. Es importante destacar que realizar un gráfico haciendo uso del asistente es una tarea simple, lo más importante como futuros profesionales, es saber “interpretar” y/o “predecir” comportamiento de variables, en forma de conclusiones. Los tipos de gráficos que podemos realizar son: 1. Barras/Columnas: Este gráfico sirve para comparar datos entre diferentes segmentos (sectores, empresas, periodos de tiempo...). Ejemplo:

2. Líneas: Ayudan a visualizar la evolución de los datos. Por lo general se usan para mostrar un mismo tipo de dato y su evolución (valor de la acción y el tiempo, numero de ventas y precio). Ejemplo:

Página-10-

UNIVERSIDAD TECNOLÓGICA NACIONAL - FACULTAD CÓRDOBA Departamento Ingeniería Industrial Asignatura: Informática I

Ing. Cynthia Corso

3. Gráfico de Torta: Aquí podemos ver la contribución de cada parte a un total. Este gráfico se puede utilizar de forma creativa comparando el tamaño de las tartas entre sí y el contenido de las mismas. Ejemplo:

4. Radar: En el radar podemos ver la superficie creada por varias variables y así poder comparar entidades (dos productos que presentan varias características pueden ser comparados en su totalidad usando esta gráfica). Ejemplo:

5. Superficie: Este gráfico se suele usar para ver la evolución de un dato sujeto a 3 variables. Por ejemplo la dureza de un material dependiendo de la temperatura, densidad y volumen. Ejemplo:

Es oportuno mencionar que más variedad de tipos de gráficos de los que aquí se han explicado. Acá he detallado los más significativos. Tomando como base nuestra planilla original, necesitamos realizar un gráfico de torta que nos represente los saldos por categoría de ingreso en el tiempo.

Página-11-

UNIVERSIDAD TECNOLÓGICA NACIONAL - FACULTAD CÓRDOBA Departamento Ingeniería Industrial Asignatura: Informática I

Ing. Cynthia Corso

Para ello debemos seguir los siguientes pasos: 1. Debo seleccionar las columnas de la información que queremos representar. En este caso la columna de Ingresos y la columna de Saldos.(Solo esas dos) 2. Luego presionamos en el icono que representa el Asistente de Gráficos. 3. Una vez que ingresamos al asistente, lo primero que nos solicita el asistente es el tipo de gráfico. En nuestro caso gráfico de tortas. 4. Luego el mismo asistente nos irá guiando, pero en principio no deberíamos hacer ninguna modificación, con respecto a la serie de datos que hemos seleccionado en principio. 5. Si es importante personalizar cuando el asistente nos solicite, como por ejemplo el título del gráfico, la leyenda de los valores a mostrar. Estos detalles facilitan la interpretación del gráfico. 6. Una vez configurado esto nos solicita determinar sí el gráfico realizado lo inserta en una hoja nueva o en la misma hoja donde esta la tabla de datos original. Seleccionamos la opción de insertar en una Nueva Hoja. El gráfico nos queda representado como se muestra a continuación:

Página-12-

UNIVERSIDAD TECNOLÓGICA NACIONAL - FACULTAD CÓRDOBA Departamento Ingeniería Industrial Asignatura: Informática I

Ing. Cynthia Corso

Conclusión del Gráfico En este gráfico el mayor porcentaje de los ingresos en el futuro está en conceptos de las comisiones, las demás categorías se comportan de una forma proporcional excepto la categoría de ingreso por intereses. Por lo tanto deberían tomarse o adoptarse políticas en la agencia de viaje que permitan que estos ingresos aumente para el periodo de tiempo considerado.

Página-13-

UNIVERSIDAD TECNOLÓGICA NACIONAL - FACULTAD CÓRDOBA Departamento Ingeniería Industrial Asignatura: Informática I

Ing. Cynthia Corso

Bibliografía. Enlace Internet: http://www.ice.urv.es/abcnet/curso_excel/formulas.htm#inicio Curso Gratuito de Excel. Tema Funciones y Formulas. AulaCilc.Enero 2005 Enlace Internet: http://sabanet.unisabana.edu.co/cursos/excel/AutoFiltro.html Todo Excel en un solo libro. Gabriel Strizinec. Editorial GYR S.R.L

Página-14-