COORDINACIÓN GENERAL DE VINCULACIÓNDIRECCIÓN GENERAL DE EDUCACIÓN CONTINUA

CURSO TALLER: MICROSOFT EXCEL INTERMEDIO

OBJETIVO GENERALAl finalizar el evento el participante debe ser capaz de aplicar los conocimientos y habilidades intermedias en el manejo de la hoja de cálculo Microsoft Excel aumentando su productividad que les permitirá mejorar en el ambiente laboral

INSTRUCTORMIGUEL ANGEL RODRÍGUEZ ORTIZIngeniero en sistemas computacionales, cuenta con amplia experiencia en la impartición de cursos a nivel nacional, se ha desempeñado como coordinador de desarrollo de aplicaciones del Centro Universitario de Producción de Medios Didácticos, así como profesor por asignatura en la Facultad de Telemática de la Universidad de Colima

DIRIGIDO APersonal Administrativo y Secretarial del Partido Acción Nacional

DURACIÓN10 horas contacto

FECHAS HORARIOSábado 27 de septiembre y 4 de octubre de 2008, en horario de 9:00 a 14:00 horas

LUGARCentro Universitario de Producción de Medios Didácticos, campus Colima.

INDICE DE CONTENIDO

1. Manejo de la hoja1.1: Inmovilización de paneles.......................................................................51.2: Proteger celdas, hojas de cálculo, y libros.............................................71.3: Aplicar opciones de seguridad al libro..................................................121.4: Compartir libros....................................................................................131.5: Combinar libros.....................................................................................14

2 Manejo de listas2.1: Manejo de listas como base de datos .................................................152.2: Creación de una lista ...........................................................................162.3: Ordenamiento de una lista ...................................................................202.4: Filtración de datos de una lista ............................................................222.5: Creación de totales y subtotales...........................................................25

3. Formulas3.1: Creación de una formula......................................................................303.2: Utilización de operadores.....................................................................323.3: Direccionamiento de celdas..................................................................35

4 Funciones4.1: Importancia de las funciones................................................................394.2: El asistente para funciones...................................................................404.3: Otras funciones.....................................................................................43

EJERCICIOS.........................................................................................................48

BIBLIOGRAFÍA.....................................................................................................50

2

PRESENTACION

La Universidad de Colima dado su liderazgo en la entidad genera los espacios que brindan oportunidades de crecimiento y desarrollo de sus egresados, de los sectores social y productivo, a partir de eventos académicos que tienen como premisa principal “ la promoción de una cultura de la educación para toda la vida”, lo cual se articula con la promoción y generación de mejores oportunidades de crecimiento, desarrollo de habilidades y competencias que orienten a obtener conocimientos teóricos para su formación profesional, así como también enfocándose al desarrollo humano, de valores, entre otros, que nos permiten ser cada día mejores, buscando por tanto impulsar modalidades de enseñanza-aprendizaje no formales.

Por ello, la coordinación General de Vinculación, a través de la Dirección General de Educación Continua (DGEC) para el programa Atención al Sector Productivo el CURSO TALLER: EXCEL NIVEL INTERMEDIO que permitirá al participante aplicar y utilizar las herramientas de excel en la organización en el trabajo, sabemos que además estamos contribuyendo a los compromisos que hemos adquirido con la sociedad

Con este evento académico, la Universidad de Colima participa con los sectores de la sociedad en la promoción de la actualización y formación permanente así como la renovación de los vínculos que existen entre la educación formal y el mundo del trabajo, con la participación directa del personal Administrativo y Secretarial del Partido Acción Nacional

Bienvenidos a la Universidad de Colima

Dra. Susana Aurelia Preciado JiménezDirectora General

3

INTRODUCCIÓN

Excel es una aplicación del tipo hoja de cálculo, integrada en el entorno Windows y desarrollada por Microsoft, en la cual se combinan las capacidades de una hoja de cálculo normal, base de datos, programa de gráficos bidimensionales y tridimensionales, lenguaje propio de programación y generación de macros; todo dentro de la misma aplicación. Las hojas de cálculo son, junto a los procesadores de texto, una de las aplicaciones informáticas de uso más general y extendido. La versión que se va a presentar a continuación es la versión Excel 2007

Además contiene un gran número de utilidades para introducir, formatear y presentar total o parcialmente el contenido de las hojas de cálculo.

Excel permite establecer relaciones entre los valores de distintas celdas y hojas, para realizar análisis de sensibilidad de forma rápida, recalculando toda la hoja al mínimo cambio que se efectúe en alguna de las celdas relacionadas. Permite asimismo generar aplicaciones a la medida del usuario (MACROS), ya que cuenta con un lenguaje propio de programación (Excel Visual Basic).

4

Introducción: Hay varias maneras diferentes  de ver las hojas de trabajo en Excel. Cada una de ellas lo ayuda a trabajar en distintas situaciones como cuando introducimos una gran cantidad de datos en una hoja, ésta aumentará progresivamente su tamaño y la búsqueda de una celda concreta resultará enormemente difícil.

1.1: Inmovilización de paneles cuando las planillas son demasiado grandes...Si introducimos una gran cantidad de datos en una hoja, ésta aumentará progresivamente su tamaño y la búsqueda de una celda concreta resultará enormemente difícil. Para solucionar este problema, Excel proporciona dos herramientas: la división de ventanas y la inmovilización de los paneles.

La opción dividirLa opción Dividir, alojada en el menú Ventana, permite segmentar una ventana en diversos paneles. Esto facilitará mucho el trabajo en ella, especialmente si se está operando con una planilla que, por contener gran cantidad de datos, no puede visualizarse completamente en pantalla. Una vez que se haya ejecutado el comando Dividir, la ventana se segmentará en cuatro partes, cada una de las cuales corresponderá a un área distinta de la misma planilla. Para variar el tamaño de los paneles creados, simplemente hacemos un drag and drop sobre sus separadores. También podemos segmentar la ventana arrastrando las pequeñas línea que aparecen ubicadas al lado de cada una de las barras de desplazamiento.

5

1 MANEJO DE LA HOJA

Objetivo: Que el participante conozca la utilidad de inmovilizar paneles y la importancia en el manejo de la seguridad al compartir libros.

1. Desde las pestaña seleccionar Vista | Dividir.Se crea una Vista Dividida de 4 paneles. Puede arrastrar las barras para redimensionar los paneles.

2. Remover las divisiones con el comando del menú Ventana | Quitar División

3. Arrastrar los Cuadros Divididos en las barras de desplazamiento vertical y horizontal para crear otras vistas divididas. (Cuidado! Hay un cuadro similar al final de la hoja con las etiquetas, que redimensiona el espacio para las etiquetas.)

Para cada división aparecen nuevas barras de desplazamiento. Esas divisiones son útiles cuando se necesitan ver al mismo tiempo, secciones de la hoja muy separadas entre sí. Una celda o rango seleccionado se verá como seleccionado en cualquier panel en el que aparezca.

4. Probar cada barra de desplazamiento para ver qué paneles se desplazan y con qué barra de desplazamiento.

5. Remover las divisiones arrastrando los cuadros separados, nuevamente a sus posiciones originales.

Los paneles inmovilizados no se desplazan, mientras si lo hace el resto de la hoja. Este efecto es usado primariamente para mantener en su lugar las etiquetas de las filas y columnas, mientras se desplaza hacia la parte de la hoja en la que está interesado. Sin las etiquetas, podría resultar difícil decir qué es lo que se está buscando.

1. Seleccionar fila 5. Después, Seleccionar  Vista  | Inmovilizar Paneles  | Inmovilizar Paneles. Las filas de arriba de la fila seleccionada son inmovilizadas. Aparece una línea oscura en el borde del panel inmovilizado.   

6

2. Desplazarse hacia arriba y abajo. Las cuatro filas de arriba se quedan en su sitio.

3. Movilizar el panel con Vista  | Inmovilizar Paneles  | movilizar Paneles.

4. Seleccionar la Columna B y después Vista  | Inmovilizar Paneles. Las columnas a la izquierda de la columna seleccionada quedan inmovilizadas.

5. Desplazar hacia la izquierda y la derecha. La primer columna permanece en su lugar.

6. Movilizar el panel.

7. Seleccionar la celda B5 y la Vista  | Inmovilizar Paneles. Las filas de arriba y la columna a la izquierda quedan inmovilizadas.

8. Desplazar hacia arriba y hacia abajo y también hacia la izquierda y la derecha. La parte superior y de la izquierda de la hoja, permanecen en su lugar de manera que se pueden ver las etiquetas. Esto es muy útil para las hojas muy grandes.

9. Movilizar los paneles.

1.2 Proteger celdas, hojas de cálculo y librosAdemás de la protección mediante contraseñas para los libros de trabajo, Excel2008 ofrece varias órdenes para proteger las celdas del libro. Para ello tenemos que realizar dos operaciones: la primera que consiste en proteger las celdas que no queremos que sufran variaciones, y la segunda que consiste en proteger la hoja. Cuando una celda está bloqueada no podrá sufrir variaciones. Realmente por defecto todas las celdas están protegidas o bloqueadas para que no sufran cambios, pero no nos damos cuenta ya que la hoja no está protegida. Por lo tanto, lo que se debe hacer es desbloquear las celdas que queremos variar en algún momento. Para ello:

7

1. Seleccionar el rango de celdas que queremos desbloquear para poder realizar variaciones.

2. Seleccionar la pestaña Inicio.

3. Elegir la opción Formato.

4. Elegir Formato de Celda

Aparecerá el cuadro de diálogo de la derecha:

5. Hacer clic sobre la pestaña Proteger.

6. Desactivar la casilla Bloqueada.

7. Hacer clic sobre el botón Aceptar.

Si se activa la casilla Oculta, lo que se pretende es que la fórmula o el valor de la celda no se pueda visualizar en la barra de fórmulas.

8

Las operaciones de la ficha Proteger no tienen efecto si no protegemos la hoja de cálculo, por lo tanto a continuación tendremos que realizar los siguientes pasos:

1. Seleccionar la pestaña Revisar.

2. Seleccionar la opción Proteger hoja.

Aparecerá el cuadro de diálogo de la derecha:

3. Dejar activada la casilla Proteger hoja y contenido de celdas bloqueadas para proteger el contenido de las celdas de la hoja activa.

4. Si queremos asignar una contraseña para que solamente pueda desproteger la hoja la persona que sepa la contraseña, escribirla en el recuadro Contraseña para desproteger la hoja, en caso de querer asignar permisos especiales debemos activar la casilla correspondiente de las opciones presentadas.

5. Hacer clic sobre el botón Aceptar.

6. Si hemos puesto contraseña nos pedirá confirmación de contraseña, por lo tanto tendremos que volver a escribirla y hacer clic sobre el botón Aceptar

A partir de ahora la hoja activa se encuentra protegida, por lo que no se podrán modificar aquellas celdas bloqueadas en un principio.

Si queremos desproteger la hoja, volveremos a realizar los mismos pasos que en la protección, es decir:

1. Seleccionar la pestaña Revisar.

2. Seleccionar la opción Desproteger hoja.

3. Si habíamos asignado una contraseña nos la pedirá, por lo que tendremos que escribirla y hacer clic sobre el botón Aceptar. Si no había contraseña asignada, automáticamente la desprotege.

Protección de Libro

El bloquear y ocultar una celda no basta para que éstas estén ocultas o protegidas. Una vez que se seleccionaron alguna de estas dos opciones se debe proteger la hoja como ya se vio, de la misma forma se puede proteger el libro completo por medio de la pestaña Revisar seleccionando el botón Proteger Libro escogiendo la opción Proteger estructura y ventanas.

9

Se puede escribir una Contraseña pero es opcional.

Después de realizar esta acción, las celdas que se hayan marcado como bloqueadas no se podrán modificar. Las que se hayan marcado como ocultas, no aparecerán.

La Contraseña funciona como en el comando anterior. Con la opción Estructura seleccionada, se protege contra borrado de hojas, cambios de lugar o de nombre, en general se cuida la estructura del libro. Con Ventanas se evita que se cierren, redimensionen o muevan las ventanas.

Se puede tener tanto la hoja como el libro protegidos, o sólo uno o ninguno.

Protección de libros de trabajo

Existen dos tipos de contraseñas:

Contraseña de protección: para que sólo puedan acceder al libro aquellas personas que conocen la contraseña.

Contraseña contra escritura: para que cualquiera pueda acceder al libro de trabajo, pero solamente lo puedan modificar aquellas personas que conozcan la contraseña.

Las contraseñas pueden tener como máximo 15 caracteres, distinguiendo entre mayúsculas y minúsculas.

Si deseas proteger el acceso a libros de trabajo, seguir los siguientes pasos:

1. Seleccionar el menú

2. Elegir la opción Guardar como.

3. Hacer clic sobre la flecha de la derecha del botón

4. Elegir la opción Opciones generales.

Aparecerá el cuadro de diálogo de la derecha.

10

5. Escribir la contraseña en el recuadro Contraseña de protección o en el recuadro Contraseña contra escritura dependiendo del tipo de contraseña.

Al escribir la contraseña aparecerán ***** para que nadie pueda verla.

6. Hacer clic sobre el botón Aceptar

Aparecerá el cuadro de diálogo de la derecha para confirmar la contraseña.

7. Volver a escribir la contraseña en el recuadro.

8. Hacer clic sobre el botón Aceptar para salir de la confirmación.

Si no hemos escrito las dos veces la misma contraseña, Excel2007 nos pedirá volver a introducirla.

9. Hacer clic sobre el botón Guardar para cerrar el cuadro de guardar como. El efecto de la protección al acceso de libros de trabajo, se comprueba a la hora de abrirlo.

- Si hemos utilizado una contraseña de protección, aparecerá el cuadro de diálogo de la derecha.

Si conocemos la contraseña, escribirla en el recuadro y Aceptar.

Excel2007 recuperará el libro para poder realizar cualquier modificación sobre éste.

- Si no conocemos la contraseña, Excel2007 no nos dejará abrirlo.

Si hemos utilizado una contraseña contra escritura, aparecerá el cuadro de diálogo de la derecha.

Si conocemos la contraseña, escribirla en el recuadro y Aceptar.

Excel2007 recuperará el libro para poder realizar cualquier modificación sobre éste.

Si no conocemos la contraseña, podremos pulsar el botón Sólo lectura en cuyo caso Excel2000 lo abrirá pero cualquier modificación se tendrá que guardar con otro nombre.

11

Si queremos borrar una contraseña, tenemos que abrir el libro con la contraseña para tener la posibilidad de modificarlo, y a continuación realizar los mismos pasos que a la hora de ponerla pero borrando lo que hay en el recuadro de contraseña.

12

1.3: Aplicar opciones de seguridad al libro.

Proteger elementos de la hoja de cálculo de todos los usuarios

1. Cambie a la hoja de cálculo que desee proteger.2. Desbloquee las celdas que desee que puedan modificar los usuarios:

seleccione cada celda o rango, haga clic en la pestaña Inicio, presione en Formato después Formato de celda, haga clic en la ficha Proteger y desactive la casilla de verificación Bloqueada.

3. Oculte todas las fórmulas que no desee que estén visibles: seleccione las celdas con las fórmulas, haga clic en la pestaña Inicio, presione en Formato después Formato de celda, haga clic en la ficha Proteger y desactive la casilla de verificación Oculta.

4. Desbloquee los objetos gráficos que desee que los usuarios puedan cambiar.

Como:No hace falta que desbloquee los botones o los controles para que los usuarios puedan hacer clic en ellos y usarlos. Puede desbloquear gráficos incrustados, cuadros de texto y otros objetos creados con herramientas de dibujo que desee que los usuarios puedan modificar. Para ver qué elementos de una hoja de cálculo son objetos gráficos, En la pestaña inicio en el botón Buscar y seleccionar haga clic en Ir a, elija Especial y, a continuación, haga clic en Objetos.

1. Presione y mantenga presionada la tecla CTRL y haga clic en cada objeto que desee desbloquear.

2. De clic derecho. Y seleccione Tamaño y propiedades, y elija la pestaña Propiedades.

3. Desactive la casilla de verificación Bloqueada y, si está presente, desactive la casilla de verificación Bloquear texto.

1. En la pestaña Revisar, elija Proteger hoja.2. Escriba una contraseña para la hoja.

Nota  La contraseña es opcional; sin embargo, si no proporciona una contraseña cualquier usuario podrá desproteger la hoja y cambiar los elementos protegidos. Asegúrese de elegir una contraseña que pueda recordar, ya que si pierde la contraseña no podrá tener acceso a los elementos protegidos de la hoja de cálculo.

3. En la lista Permitir a todos los usuarios de esta hoja de cálculo, seleccione los elementos que desee que los usuarios puedan cambiar.

Haga clic en Aceptar y, si se solicita, vuelva a escribir la contraseña.

13

Proteger un archivo de libro para que no se pueda ver ni modificar

1. En el menú , haga clic en Guardar como.2. En el menú Herramientas, haga clic en Opciones generales.3. Siga uno de estos procedimientos o ambos:

o Si desea que los usuarios escriban una contraseña antes de poder ver el libro, escriba una contraseña en el cuadro Contraseña de apertura y haga clic en Aceptar.

o Si desea que los usuarios escriban una contraseña antes de poder guardar los cambios en el libro, inserte una contraseña en el cuadro Contraseña de escritura y haga clic en Aceptar.

4. Cuando se solicite, vuelva a escribir las contraseñas para confirmarlas.5. Haga clic en Guardar.6. Si se le pide, haga clic en Sí para reemplazar el libro existente.

1.4: Compartir libros

Crear un libro compartidoEl autor original del libro lo prepara para compartirlo escribiendo los datos que deben estar presentes y aplicándoles formato. El libro se guarda como compartido y se coloca en una ubicación de la red (no en un servidor Web) disponible para todos sus usuarios.

Tenga en cuenta las funciones no disponibles      Dado que algunas funciones de Microsoft Excel se pueden ver o utilizar pero no cambiar una vez que el libro esté compartido, es preciso definirlas antes de compartir el libro.Las siguientes funciones no se pueden modificar después de compartir el libro: celdas combinadas, formatos condicionales, validación de datos, gráficos, imágenes, objetos (incluidos los de dibujo), hipervínculos, escenarios, esquemas, subtotales, tablas de datos, informes de tabla dinámica, protección de libros y de hojas de cálculo, y macros.

14

Un libro compartido permite que varias personas lo modifiquen simultáneamente. Resulta especialmente útil para administrar listas que cambian con frecuencia.

Por ejemplo, si las personas del grupo de trabajo gestionan varios proyectos cada una y necesitan saber en qué situación están los demás, el grupo podría utilizar una lista incluida en un libro compartido, en la que cada persona escriba y actualice una fila de información por cada proyecto.

Defina la configuración que afecta a todos los usuarios      Cuando se comparte el libro, es posible personalizar algunas funciones compartidas. Por ejemplo, puede decidir si desea controlar los cambios durante los 30 días predeterminados o durante un período de tiempo mayor o menor.

Trabajar en un libro compartido

Después de abrir un libro compartido, puede introducir y cambiar los datos igual que en un libro normal. Sin embargo, hay algunos aspectos distintos del trabajo con un libro normal.

Posibilidad de ver los cambios de los demás usuarios      Cada vez que se guarda el libro compartido, se actualizan los cambios que los demás hayan guardado desde la última vez que se guardó. Si desea dejar abierto el libro compartido para realizar un seguimiento de los progresos, puede hacer que Microsoft Excel actualice los cambios automáticamente, a intervalos de tiempo determinados, tanto si guarda el libro personalmente como si no.Resolución de conflictos      Al guardar cambios en un libro compartido, puede que otra persona que esté modificando el libro en ese momento haya guardado cambios que afecten a las mismas celdas. Si es el caso, los cambios entrarán en conflicto y aparecerá el cuadro de diálogo de resolución de conflictos, en el que podrá decidir qué cambios desea conservar.

Conservar las vistas y configuraciones personales      Excel guarda una vista personalizada del libro compartido que incluye, por ejemplo, la hoja de cálculo que ha mostrado y el zoom aplicado. De forma predeterminada, la vista incluye todos los filtros y la configuración de impresión aplicados; también puede especificar que desea utilizar el filtro y la configuración de impresión originales. Cada vez que se abre el libro compartido, Excel lo muestra con la vista de los usuarios, de modo que cada uno de ellos puede disponer de su propia configuración.

1.5: Combinar libros

1. Asegúrese de que las copias del libro compartido que desea combinar están almacenadas en la misma carpeta. Para ello, debe dar a cada copia un nombre diferente.

2. Abra la copia en la que desea combinar los cambios. 3. En el menú Herramientas, haga clic en Comparar y combinar libros. 4. Si se solicita, guarde el libro. 5. En el cuadro de diálogo Seleccione archivos para combinar en el libro

actual, haga clic en una copia que tenga cambios para combinar y, a continuación, haga clic en Aceptar.

Para combinar más de una copia a la vez, mantenga presionada la tecla CTRL o MAYÚS mientras hace clic en los nombres de archivo y, a continuación, haga clic en Aceptar.

15

Conclusión del tema:

La inmovilización de paneles es una herramienta muy útil en el uso de grandes listas en las cuales se requiere mantener visibles encabezados. La protección de componentes nos ayuda a dar más seguridad a nuestros trabajos.

16

Introducción: Las listas son otra de las características de Excel que nos ayudan a hacer más fácil nuestro trabajo. Una lista es una serie de valores que tienen una relación entre sí, por ejemplo, números correlativos (1, 2, 3, 4, ...), números pares (2, 4, 6, 8, ...), días de la semana (lunes, martes, miércoles, jueves, ...) . Excel es capaz de identificar estas listas y también nos permite añadir listas nuevas personalizadas.

2.1: Manejo de listas como bases de datos

Excel no es un programa de base de datos, pero funciona de tal manera que usted no necesitará un programa de base de datos para su sistema.Una lista de Datos no es más que un rango de celdas de la hoja de cálculo. La primera fila del rango, que puede estar situada en cualquier fila de la hoja de cálculo, contiene los rótulos de las columnas e inmediatamente debajo de ella, sin ninguna fila en blanco, las filas con la información deseada.

Los rótulos de los campos se recomienda que sean cortos, aunque pueden estar formados por cualquier combinación de caracteres, incluidos espacios en blanco, hasta un total de 32.000, y ocupar más de una línea. No pueden usarse como nombres de campos de funciones o fórmulas que produzcan valores numéricos, pero sí texto.

Una vez diseñada la estructura de la lista de Datos, se procede a introducir los registros. Cada columna de la fila puede contener texto, números fórmulas y funciones, al igual que sucedía en la hoja de cálculo, pero un registro no puede ocupar más de un línea.

La información se introduce, alinea formatea de la misma forma que en la hoja de Datos. Lo mismo sucede cuando hay que borrar o insertar filas o columnas.

2. 2: Creación de una lista

Una vez que se ha planeado la estructura de la lista, la secuencia de campos y cualquier formato apropiado, se necesitará crear nombres de campo.

Suponiendo que la lista a crear contendrá los datos personales de los nuevos clientes de una empresa, los pasos para la creación de una lista son los siguientes:

1. Iniciar Excel y asignar un nombre al libro y a la hoja donde vamos a crear la nueva lista

17

2 MANEJO DE LISTAS

Objetivo: Que el alumno comprenda el uso de una lista de datos y que aprenda a visualizar datos y crear informes a partir de la lista.

2. Empezando en la celda A1 y moviéndose de manera horizontal, escribir el nombre de cada campo en una celda separada como se muestra a continuación:

3. Selecciona los encabezados de campo en el rango A1:H1, luego has clic en el botón Negritas de la barra de herramientas Formato; con el rango A1:H1 todavía seleccionado y agregarle un borde inferior grueso.

4. Introduce la información hasta completar por lo menos 3 registros5. Seleccionar la el rango A1:H1 y asignar el formato de Autoajustar.

Cambiar automáticamente el ancho de la columna de esta manera resulta más rápido que hacer doble clic en las líneas divisorias de columna ente cada par de columnas.

Directrices para asignación de nombres de campoDirectriz Explicación

Utiliza rótulos en campos de nombre Los números pueden interpretarse como nombres de formulas

No utilizar nombres de campo duplicados

Duplicar nombres de campo puede causar que la información se introduzca mal

Da formato a los nombres de campo para Destacarlos de los datos de la lista

Utiliza un tipo de letra, alineación, un formato, asigna patrones y bordes o estilo de mayúsculas para los rótulos de columna que sean diferentes del formato de los datos de tu lista

Utiliza nombres descriptivos Evita nombres que podrían confundirse con direcciones de celda, como Q4

Como agregar registros con el formulario de datosPuedes agregar registros a una lista escribiendo datos directamente en las celdas. Una vez que los nombres de campos se crean, también puedes utilizar el formulario de datos como método fácil y rápido de entrada de datos. Un Formulario de Datos es un cuadro de diálogo que despliega un registro a la vez. Al asignar nombre a un rango de lista, en el cuadro de nombre, puedes seleccionar la lista en cualquier momento y todos los registros nuevos que agregues se incluirán en el rango de la lista.Pasos para utlizar el formulario de datos:

18

1. Asegurarnos que el libro este abierto y que contenga datos capturados a manera de lista.

2. Seleccionar una celda de la lista que contenga un dato capturado3. De click al boton Formulario de la barra de acceso rapido

Si no lo ve, agregelo dando clic en icono (flecha abajo) de la izquierda de la barra de acceso rapido y seleccione Más comandos, aparecerá el siguiente cuadro de dialogo:

En la lista comandos disponibles seleccione la segunda opcion: Comandos que no están en la cinta de opciones, busque la opcion de formulario de agreguela y de clic en Aceptar

4. Has clic en nuevo. Aparece un formulario de datos en blanco con el punto de inserción en el primer campo

5. Escribe los datos que quieras capturar y podrás moverte a traves de los campos orpimiendo ta tecla [TAB]

19

Para visualizar los registros capturados previamente, podemos hacerlo con las opciones Buscar anterior y Buscar siguiente

20

La utilidad Autocompletar Cuando se introduce información en una hoja de cálculo, Excel examina todos los Datos de la misma columna y comprueba si alguno de los caracteres de la entrada actual coincide con algunas entradas existentes en esa columna. Si existe algún carácter igual, lo propone como entrada. En caso de ser correcto pulse Intro para aceptarlo; en caso contrario, siga escribiendo.

La utilidad Autocompletar se puede activar o desactivar de la siguiente forma:

Seleccione del menú , Opciones de Excel, Avanzadas. Active o desactive la caja Habilitar Autocompletar para valores de celda.

2.3: Ordenamiento de una lista

Ordenar una lista de datos

En una lista de Datos, las filas se muestran en el mismo orden en que se han introducido. La mayoría de las veces ese orden no guarda ninguna relación respecto a ninguna columna, lo que dificulta localizar una información correcta. En cambio, si los Datos están ordenados por alguna columna, encontrar un dato resulta mucho más fácil.

Ordenar una lista por una columna Active una celda perteneciente a la columna por la cual se desea llevar a

cabo la ordenación Haga clic en el botón Orden ascendente o el botón Orden

descendente del botón Ordenar y Filtrar de la pestaña Inicio.

Si la columna contiene entradas numéricas y de tipo texto, Excel, en una ordenación ascendente, sitúa todas las entradas numéricas antes que las de tipo texto.

Si un dato contiene todo tipo de entradas, en una ordenación ascendente Excel sigue este orden: números, textos, valores lógicos (primero Falso, luego verdadero), valores de error, celdas en blanco. La ordenación descendente es la inversa a la anterior, excepto que en ambos órdenes las celdas en blanco son siempre las últimas.

Ordenar la lista por dos o más columnas Active una celda de las lista de Datos a ordenar Seleccione del menú Datos la opción ordenar. Aparece el siguiente cuadro

de diálogo:

21

En el área la lista tiene fila de encabezamiento, active la opción sí, en el caso

En el área Ordenar Por seleccione la columna principal por la que se desea llevar a cabo la ordenación.

Indique el tipo de ordenación deseada, Ascendente o descendente. En el área Luego por seleccione la columna deseada como segundo

criterio de ordenación.

Excel sólo permite ordenar Datos por tres columnas. Cuando se desea ordenar por más de tres columnas, por ejemplo por cinco, primero se ordenan por las dos últimas columnas en orden de importancia (como primer y segundo criterio) y luego se vuelve a ordenar por las otras tres columnas.

En Opciones aparece la siguiente caja de diálogo:

En Primer criterio de ordenación usted puede ordenar una lista de Datos según las series personalizadas creadas en la fecha Listas personalizadas que aparece al ejecutar herramientas, opciones.

Para que la ordenación se realice por columnas, active la opción Ordenar de arriba abajo o Ordenar de izquierda a derecha.

22

2.4: Filtración de datos de una lista

Filtrar una lista Con un filtro Excel visualiza las filas que cumplen unas determinadas condiciones, mientras que oculta las demás. Para poder filtrar una lista, ésta debe poseer rótulos de columna. Las filas filtradas se pueden copiar en otra hoja de cálculo del libro de trabajo actual, si se desea.

Los filtros se pueden aplicar con las opciones: Autofiltro. Filtro Avanzado.

Autofiltro Para poder aplicar un filtro, primero hay que identificar la lista que se desea filtrar, y a continuación aplicarlo.

Active una celda de la lista de Datos; no es necesario que sea un rótulo. El rango adyacente a la celda seleccionada es asumido como una lista de Datos en la cual se va a realizar el filtrado de los Datos.

Ejecute Datos, Filtro, Autofiltro. Junto a los campos de la lista de Datos aparecen unas flechas para poder hacer el autofiltrado, como en la siguiente figura:

Haga clic en el menú desplegable del rótulo de campo en el cual se va a especificar la condición de filtrado. Aparece una lista con todas las ocurrencias de esa columna, ordenadas por orden al alfabético o numérico.

Al hacer clic sobre la opción deseada, Excel muestra las filas que cumplen la condición. El rótulo del campo por el que se ha llevado a cabo el filtrado y las filas visualizadas aparecen de color azul.

23

El filtro las 10 más visualiza las filas indicadas o el porcentaje de filas situadas entre los límites superior o inferior. Al hacer clic sobre esta opción podemos ver la siguiente caja de diálogo:

En la primera caja, indique si desea ver los valores superiores o inferiores. En la siguiente caja, indique el número que desea ver. En la siguiente caja, indique si el número se refiere al número de elementos o al tanto por ciento.

Al seleccionar la opción Personalizada podemos observar la siguiente caja de diálogo:

En el menú desplegable situado al lado izquierdo podemos seleccionar un operador: mayor o igual que, es igual a, termina con, entre otros. En el menú desplegable de la derecha se pueden seleccionar entradas de esa columna. Puede seleccionar Y (filas que a la vez cumplen ambos criterios) y O (filas que cumplen uno u otro criterio) según convenga. Dentro de los criterios personalizados se pueden emplear los comodines * y . El símbolo asterisco (*) sustituye a cualquier número de caracteres.

Filtro Avanzado La orden filtro avanzado permite filtrar una lista por medio de criterios más complejos, para lo cual es necesario crear un rango de criterios.

Un rango de criterios es una rango de celdas formado por una fila de rótulos de criterios y al menos una fila para especificar las condiciones de filtrado. Para crear un rango de criterios se procede así:

24

Se recomienda insertar una serie de filas en blanco encima de la lista de Datos.

Copie o escriba uno o más nombres de rótulos de columnas en una de esas filas vacías. Los nombres de los rótulos del rango de criterios deben ser iguales a los de la lista de Datos, por lo que se recomienda copiarlos.

Escriba la condición del criterio en la fila situada debajo de los rótulos.

Filtre la lista con la orden Datos, filtro, Filtro avanzado, inmediatamente aparecerá la siguiente caja de diálogo:

En el rango de criterios se puede especificar: Criterios calculados Criterios de comparación múltiples o combinados. Permiten seleccionar filas

por más de una condición, para lo cual es necesario especificar la entradas en más de una celda situada debajo de los rótulos de las columnas en el rango de criterios. Los criterios combinados pueden ser:

Criterios Y lógicosCriterios O lógicosCriterios Y y O lógicos

En la caja Rango de la lista aparece el rango de la lista.

En la caja de Rango de criterios, especifique el rango de celda donde está situado el rótulo y la condición de búsqueda deseada.

La opción Filtrar la lista sin moverla a otro lugar permite que Excel Visualice la fila en la lista de Datos, ocultando las que no cumplan la condición.

La opción Copiar a otro lugar permite copiar las filas filtradas en otra posición dentro de la misma hoja de Datos, pero no en otra hoja del mismo libro de trabajo.

Criterios Y lógicos Cuando el rango de criterios se realizan dos o más entradas en la misma fila, Excel selecciona las filas que cumplan todas las condiciones. Las condiciones deben estar en columnas contiguas.

25

A veces puede suceder que interese establecer criterios combinados en base al mismo campo y como sólo hay una columna con dicho campo, es necesario añadir otra con igual nombre de campo al rango de criterios para acoger a la segunda condición.

Criterios O lógicos Cuando las condiciones en el rango de criterios se escriben en dos o más filas, Excel visualiza las filas que cumplan algunas de las condiciones especificadas en el rango de criterios. Los rótulos de criterios deben estar en columnas contiguas.

Para establecer criterios combinados con base en el mismo rótulo, se escriben las dos condiciones en filas contiguas pero en la misma columna.

Criterios Y o O lógicos Estos criterios se pueden combinar para dar lugar a condiciones que utilizan más de una entrada en una o más filas.

Como criterio de selección también se pueden especificar referencias a celdas que están situadas fuera del rango de la lista de Datos. También se pueden especificar fórmulas.

2.5: Creación de totales y subtotales

Resumen de datos de una Lista Algunas veces es necesario resumir la información de una lista de Datos por una columna concreta.

Excel permite añadir filas de Subtotales de columnas a una lista de Datos sin necesidad de insertar filas o de tener que escribir fórmulas complicadas por medio de la orden Subtotales del menú Datos. Antes de usar la orden Subtotales es necesario hacer los siguientes pasos:  

1. Ordenar la lista de Datos por la columna que va a servir para crear los Subtotales.

2. Ejecutar Datos, Subtotales aparece la siguiente caja de diálogo:

26

En Cada cambio en, aparecen los nombres de los rótulos las columnas. En la caja Usar función, seleccione la función deseada. En la caja Agregar subtotal a, indique la columna sobre la cual se va a calcular el subtotal.       Tablas La orden tabla del menú datos toma como referencia una fórmula y un conjunto de variables, por lo que es capaz de calcular de manera automática una serie de datos. Una tabla es un rango de celdas que se tratan como una unidad, las tablas pueden tener una o dos variables que se llaman entradas.

Tablas de una variable Para construirla se necesita: Una celda variable con el valor que se desea modificar. Una fila o columna con los valores que se van a aplicar a la celda de entrada.

Una fila o columna con las fórmulas a utilizar para producir los valores.

Por ejemplo supongamos que se desea saber cuanto pagar al mes por una deuda por 10 millones de pesos al 10 por 100 de interés en un plazo de diez años.  En la hoja se introducen éstos datos y como fórmula para calcular la cuota mensual se usa la función financiera PAGO así: =pago(B2/12;B3;-B1).

Supongamos que se va a modificar el tipo de interés: Copie la fórmula a una celda separada Introduzca en un rango los diferentes tipos de interés Seleccione el rango donde se crea la tabla Ejecute Datos, tabla.  aparece el cuadro de diálogo tabla Defina el rango, una vez hecho haga clic en contraer cuadro Clic en aceptar para que Excel calcule los datos

Si se activa una celda de la tabla se puede observar que aparece entre llaves ({}).  estas llaves representan la forma que tiene Excel de indicar que ésta celda pertenece a una tabla, lo que significa que no se puede modificar o borrar la forma individual pero si se pueden borrar como conjunto.

El cuadro de diálogo tabla tiene las siguientes opciones:

27

Celda de entrada fila.  Referencia de la celda variable si los valores de entrada están situados en una fila.

Celda de entrada columna.  Referencia de la celda variable si los valores de entrada están situados en una columna.

Tablas de una variable con más de una fórmula En una tabla de una variable se pueden introducir tantas fórmulas como se desee. Si el rango de entrada es una columna, la segunda fórmula se introduce a la derecha de la celda con la fórmula principal; la tercera formula, a la derecha de la segunda; y así sucesivamente.

Supongamos que una vez elegido el interes se desea saber la cantidad que corresponde a los intereses y el capital que se amortiza. Para ello, además de la función PAGO, se utilizan las funciones PAGOINT y PAGOPRIN :        En una determinada celda  escriba la fórmula: = PAGOINT(B2/12;B4;B3;-B1).      En otra escriba : = PAGOPRIN(B2/12;B4;B3;-B1).

     Seleccione el rango de la tabla

     Ejecute datos, tabla.      En la caja celdas de entrada columna, introduzca la referencia a la celda.

     Haga clic en el botón aceptar para que Excel calcule los datos:

Tablas de dos variables Las tablas de datos anteriores pueden tener múltiples fórmulas, pero sólo una variable. No obstante, Excel permite diseñar tablas de datos que tienen dos variables, pero sólo una fórmula. Son las llamadas tablas de variables.

Para construir una tabla de variables se necesita: Dos celdas  variables con los valores que se van a modificar.Una fila con los valores que desea aplicar a una variable. Una columna con los valores que desea aplicar a la otra variable. Una celda con la fórmula, situada en la intersección entre las variables de fila y columnas, que nos permita obtener los resultados en función de ambas variables.

28

Por ejemplo, supongamos que se desea construir una tabla que calcule las cuentas mensuales de un préstamo  de diez millones de pesos, en función del número de meses al que se solicita el préstamo y el tanto por ciento de interés.

Introduzca los tipos de Interés deseados, por ejemplo del 10 al 14 por 100, en un rango fijo.

Introduzca los meses, por ejemplo del 120 a 156, en otro rango.

Escriba la fórmula (pago),  en la celda intersección de la fila y columna con los dos conjuntos de valores de entrada.

Seleccione el rango de la tabla de datos . Ejecute Datos, Tabla. En la caja Celda de entrada columna. Haga clic en el botón de Aceptar.

 

Modificaciones de las Tablas En una tabla de datos sólo se pueden modificar los datos de entrada o la fórmula empleada para generar la tabla, pero no se pueden modificar los resultados obtenidos en las celdas. Si se intenta editar alguna de sus celdas, Excel muestra el mensaje "Imposible cambiar parte de una tabla".

Para copiar los resultados de una tabla de datos a otra zona de la hoja de cálculo se procede igual que cuando se copian celdas. Una vez borrada la tabla, los números de la tabla han dejado de ser fórmulas  para convertirse en valores numéricos constantes que se pueden modificar y/o borrar de forma individual.

Vínculos Una de las opciones más importantes de Excel es la posibilidad de establecer vínculos dinámicos, canales de comunicación, entre diferentes hojas de cálculo. Varias hojas de cálculo están vinculadas cuando entre ellas existen referencias, es decir, una hoja opera y obtiene información con los datos de otra hoja. Los vínculos proporcionan grandes ventajas como la de dividir modelos grandes o complicados en otros más sencillos. Por ejemplo, los presupuestos de una compañía pueden estar separados por departamentos en lugar de estar en una sola hoja, con la posibilidad de ser manipulados por terceras personas.

Los vínculos se pueden establecer entre dos hojas de calculo Excel o vincular una hoja Excel con otra aplicación, por ejemplo, Microsoft Word. Esa aplicación deberá estar siendo ejecutada bajo el entorno Windows y, al mismo tiempo, deberá ser compatible con el intercambio dinámico de datos, DDE, o la vinculación e incrustación de objetos, OLE.

29

Al vincular hojas de cálculo se suele usar la siguiente terminología: Referencia externa.  Referencia a una celda, un rango de celdas o un

nombre de celdas de otra hoja de cálculo. Fórmula de referencia externa.  Una fórmula que contiene una referencia

externa. Hoja dependiente o vinculada.  Una hoja que depende de la información de

otra hoja. Hoja soporte o de apoyo o fuente.  Hoja que contiene datos que son usados

por una fórmula de referencia externa.

CONCLUSIÓN:

El manejo de bases de datos como listas, en la actualidad es una de las principales actividades en la vida cotidiana, el conocer su manejo y administración es una gran ventaja para el usuario

30

Introducción:Una fórmula es una operación de cálculo, referencia, deducción o interpretación de las celdas de una hoja. El verdadero potencial de una hoja de cálculo es el manejo de las fórmulas, y aunque muchas de las operaciones que se hacen con las fórmulas se pueden hacer con una calculadora típica, en una hoja de cálculo se tiene la ventaja de que se dispone de todos los datos al mismo tiempo y que la operación se indica una sola vez y después se indica que se realice a los datos deseados.

3.1: Creación de una fórmula

Una fórmula se captura en la misma forma en que se captura cualquier otro tipo de información en una celda. Por ejemplo, si tuviéramos la siguiente tabla:

 La columna de TOTAL es el resultado de la multiplicación de PRECIO por CANTIDAD. Por lo tanto, es aquí donde se puede crear una fórmula en lugar de hacer varias multiplicaciones.

Para este ejemplo se escribe en la celda E2 porque es donde va a estar el resultado de la operación, es decir el total y se tiene que hacer la multiplicación de la celda C2 por la celda D2, porque son las celdas donde se encuentran los valores de PRECIO y CANTIDAD. La forma de escribir esa fórmula es =C2*D2. Se puede escribir directamente, pero Excel brinda la facilidad de hacerlo de la siguiente forma:

1o) Siempre para comenzar una fórmula se debe hacer con el signo igual (=), y con esto le indicamos a Excel que lo que viene a continuación es el contenido de la fórmula.2o) Después, se debe escribir el contenido de la fórmula, es decir, sus referencias y operaciones. Se puede escribir directamente la celda, pero

31

3 FORMULAS

Objetivo: Que el participante obtenga los conocimientos teóricos y prácticos que le permitan crear e identificar una formula en hoja de cálculo Microsoft Excel.

con la ayuda del mouse se le puede dar un clic a la celda deseada. La celda escogida tendrá una línea punteada, si hubo un error y esa no era la celda, tan sólo de un clic en la celda deseada.

3o) Después de que la celda ya está escrita, se escribe el signo, que en este caso es *, y se repite el paso 2 para la siguiente celda.

4o) La fórmula ya está lista, solo falta aceptarla. Se puede aceptar con la tecla <Intro> o con el botón  de la barra de fórmula.

En la celda aparece el resultado de la operación que se indica en la fórmula, pero sin embargo, sabemos que es una fórmula porque en la barra de fórmulas sigue apareciendo la fórmula tal y como se escribió.

Esa fórmula que se escribió en el renglón 2, también puede servir para los siguientes renglones sin necesidad de volverla a escribir en cada renglón. Tan sólo tiene que copiarse hacia abajo con cualquiera de los métodos vistos en la sección 2, pero el más rápido y sencillo es con el apuntador del mouse, y copiando rápidamente el contenido hacia abajo.

Al copiar una fórmula automáticamente van a cambiar los renglones si se copia hacia arriba ó abajo y sus columnas si se copia hacia la izquierda o derecha. Por lo que en cada renglón o columna se tendría la fórmula correspondiente y sería equivalente a escribir la fórmula celda por celda, sólo que más rápido y más sencillo.

32

De esta forma, se realizó la operación deseada rápidamente y además se tienen otras ventajas. La primera es que si se hacen cambios en las celdas de PRECIO o CANTIDAD, automáticamente la operación de la fórmula se actualiza.

La otra ventaja, es que las celdas que contienen fórmulas pueden ser usadas como referencias para más operaciones en fórmulas de otras celdas. Por ejemplo, si agregáramos una columna más de DESCUENTO, tendríamos lo siguiente:

Se pueden hacer cuantas combinaciones se deseen simplemente introduciendo la fórmula adecuada.

3.2: Utilización de operadores y direccionamiento de celdas

Los operadores especifican el tipo de cálculo que se desea realizar con los elementos de una fórmula. Microsoft Excel incluye cuatro tipos diferentes de operadores de cálculo: aritmético, comparación, texto y referencia.

Tipos de operadores

Operadores aritméticos Para ejecutar las operaciones matemáticas básicas como suma, resta o multiplicación, combinar números y generar resultados numéricos, utilice los siguientes operadores aritméticos.

33

Operadores de comparaciónSe pueden comparar dos valores con los siguientes operadores. Cuando se comparan dos valores utilizando estos operadores, el resultado es un valor lógico: VERDADERO o FALSO.

Operador de concatenación de textoUtilice el signo (&) para unir o concatenar una o varias cadenas de texto con el fin de generar un solo elemento de texto.

Operadores de referenciaCombinan rangos de celdas para los cálculos con los siguientes operadores.

Orden en que Excel ejecuta las operaciones en las fórmulas

Las fórmulas calculan los valores en un orden específico. Las fórmulas de Excel siempre comienzan por un signo igual (=). El signo igual indica a Excel que los caracteres siguientes constituyen una fórmula. Detrás del signo igual están los elementos que se van a calcular (los operandos), separados por operadores de cálculo. Excel calcula la fórmula de izquierda a derecha, según el orden específico de cada operador de la fórmula.

34

Prioridad de operadores

Si se combinan varios operadores en una única fórmula, Excel ejecutará las operaciones en el orden que se indica en la tabla a continuación. Si una fórmula contiene operadores con la misma prioridad (por ejemplo, si una fórmula contiene un operador de multiplicación y otro de división), Excel evaluará los operadores de izquierda a derecha.

Uso de paréntesis

Para cambiar el orden de evaluación, escriba entre paréntesis la parte de la fórmula que se calculará en primer lugar. Por ejemplo, la siguiente fórmula da un resultado de 11 porque Excel calcula la multiplicación antes que la suma. La fórmula multiplica 2 por 3 y, a continuación, suma 5 al resultado.

=5+2*3

Por el contrario, si se utilizan paréntesis para cambiar la sintaxis, Excel sumará 5 y 2 y, a continuación, multiplica el resultado por 3, con lo que se obtiene 21.

=(5+2)*3

En el siguiente ejemplo, los paréntesis que rodean la primera parte de la fórmula indican a Excel que calcule B4+25 primero y después divida el resultado por la suma de los valores de las celdas D5, E5 y F5.

=(B4+25)/SUMA(D5:F5)

Cuando se realizan varias operaciones, el orden en el que serán realizadas es:

1o) Multiplicaciones y Divisiones 2o) Sumas y restas

35

Según el orden, las operaciones se van a agrupar con los paréntesis para obtener el resultado deseado. Por ejemplo:  

Fórmula Resultado

=2+4*5 22

=(2+4)*5 30

=5+6/4+3*2 12.5

=(5+6)/(4+3)*2 3.14285714

=((5+6)/(4+3))*2 3.14285714

3.3: Direccionamiento de celdas Como se vio anteriormente en esta sección, al copiar una fórmula hacia la derecha o izquierda, cambian las columnas, y al copiar una fórmula hacia arriba o abajo, cambian las filas. Pero esto en ciertos casos puede ser una desventaja, porque se desea tener algún dato constante y la referencia de su celda no debe cambiar. Una solución a esto podría ser que se hiciera una fórmula por cada celda, pero no es una solución eficiente, porque mientras más datos se tengan más larga será esta tarea.

Por ejemplo, en la siguiente tabla se desea calcular el cambio de pesos a dólares:

36

La operación es muy sencilla, una simple división. Pero ¿qué sucede cuando esa fórmula se copia hacia abajo?.

Como se copio hacia abajo, los valores de las filas cambiaron. En la barra de fórmulas se puede ver que el contenido de la celda D10 es la fórmula que divide a C10 entre C7, no es el resultado esperado, la operación adecuada debería ser C10 entre C3. Sin embargo, podemos ver que la operación realizada en la celda D6 está correcta, la fórmula en esta celda es C6 entre C3. Todos los valores en este caso, deben de multiplicarse por C3 que es el valor del dólar, por lo tanto, la celda que no debe cambiar es la C3.

La fórmula que está en la celda D6 está correcta, lo único que debemos hacer es indicarle que no cambie su columna o renglón al momento de copiarse y la forma de hacerlo es anteponiendo el signo $ antes de la referencia de la celda. 

Celda Copiando / Copiando /

C2 B2 / D2 C1 / C3

$C$2 $C$2 / $C$2 $C$2 / $C$2

C$2 B$2 / D$2 C$2 / C$2

$C2 $C2 / $C2 $C1 / $C3

La misma fórmula se puede editar con la tecla <F2> y posteriormente con la tecla <F4> Excel automáticamente proporciona las combinaciones de símbolos $ según se desee. Se puede seguir presionando la tecla <F4> hasta obtener la combinación deseada.

37

Si utilizamos la primera combinación, tendríamos lo siguiente:

Ahora, vamos a incluir otros datos:

Copiando la fórmula que tenemos en D6, no va a cambiar ni la columna ni el renglón. Se puede hacer otra fórmula más, pero se puede aprovechar la que ya se tiene, simplemente con hacerle un cambio.

La fórmula de D6 es =C6/$C$3 que quiere decir que no va a cambiar la columna ni la fila, pero como lo que nos interesa que se mantenga constante es la fila, podemos cambiar la fórmula a =C6/C$3.

Si copiamos está fórmula hacia abajo y hacia la derecha, los cálculos son correctos y tan sólo utilizamos una fórmula:

38

Y podríamos seguir añadiendo datos y se va a seguir utilizando la misma fórmula:

Podemos tener referencias absolutas cuando no va a cambiar ni la columna ni la fila (como $C$6), referencias relativas cuando solo va a cambiar o una columna o una fila (como C6 o C6) y referencias mixtas cuando se combinan relativas con absolutas (como $C6*C$7). Las referencias absolutas, relativas y mixtas ($), se pueden utilizar con cualquier tipo de fórmulas. Esto nos puede ahorrar mucho trabajo y tiempo al momento de realizar operaciones con los datos.  

CONCLUSIÓN

El uso de formulas es la herramienta más común y poderosa en Excel para ahorrarnos tiempo y facilitarnos las tareas cotidianas, como obtener promedios, sumatorias, etc.

39

Introducción Introducción:Ya se explicó la importancia, ventajas y usos de las fórmulas. Las fórmulas son fundamentales para el manejo de una hoja de cálculo y son su herramienta principal. Además de las fórmulas que Excel permite que el usuario cree según sus necesidades, Excel cuenta con una herramienta más que son las Funciones. Las funciones, son fórmulas simplificadas de las operaciones que se realizan comúnmente, como una sumatoria, un promedio, etc. Además Excel cuenta con funciones financieras, estadísticas, matemáticas y trigonométricas, etc. que resuelven las necesidades más frecuentes de distintos disciplinas.

4.1: Importancia de las funciones

Por ejemplo, si tenemos los siguientes datos:

La fórmula para sumar estos datos, como se muestra en la barra de fórmula es =C10+C9+C8+C7+C6. Si existieran más filas, la fórmula seguiría creciendo y creciendo hasta contemplar todas las filas. Además, es muy fácil cometer un error mientras mayor sea el número de filas a sumar. Ahora, veamos el mismo grupo de datos pero ahora utilizando una función para realizar la operación:

Aquí se utilizó la función SUMA que es una de las más usadas, y suma series de números. Nótese como para escribir esta fórmula el número de letras es mucho menor al del ejemplo anterior. Este tamaño varía muy poco aunque se trate de 10 filas o 20 filas o 40 etc., porque se está haciendo referencia a un rango de celdas.

40

4 FUNCIONES

Objetivo: Que el participante aprecie la importancia y uso de las funciones.

Una función, es una serie de fórmulas pero en forma simplificada, más sencilla y más fácil de escribirse. Las funciones resuelven problemas que también se pueden resolver con fórmulas

Al escribir C6:C10 la operación a realizar considera de la celda C6 a la celda C10 incluyendo todas sus celdas intermedias.

4.2: El asistente para funciones

Para incluir una función dentro de la hoja de cálculo se pueden utilizar cualquiera de los siguientes métodos:

Seleccionamos del menú Insertar el comando Función.

Presionamos simultáneamente <Mayús> + <F3>.

Damos un clic sobre este icono  en la barra de herramientas.

Damos un clic sobre este icono  en la barra de fórmula.Cualquiera de los tres métodos nos lleva a la siguiente caja de diálogo:

El Asistente para funciones, nos ayuda ha colocar la función y los valores adecuados, llevándonos parte por parte para la creación de la fórmula. En Categorías de funciones, está la lista de las agrupaciones de funciones que existen y éstas se pueden ver en Nombre de la función. En la parte inferior de la caja, se encuentra el formato de la función, es decir, que componentes debe llevar para que sea empleada correctamente. También hay una pequeña descripción de lo que hace esa función.

41

Una vez elegida la función, presione Siguiente y aparece una caja de diálogo

estará directamente asociada a la función que utilizamos. A continuación aparece

la caja de diálogo correspondiente a la función SUMA.

Existen ciertos elementos constantes en todas las funciones como es el Valor que es un cálculo momentáneo de la función conforme se va creando. La caja número1 va a contener la referencia o rango de celdas de la función. En esta caja se puede escribir la referencia o rango de las celdas, pero además Excel provee la capacidad en ese momento de indicarle el rango con el ratón. Para hacerlo damos un clic sobre la celda deseada o un clic sostenido sobre el área del rango deseado.

42

Al señalar el rango o referencia en la hoja se coloca una línea punteada sobre la selección y la celda o rango automáticamente se escriben, con todo y los signos de puntuación y separación que necesiten. Se pueden incluir más números, dando un clic sobre la caja de número2 y realizando el mismo procedimiento. Automáticamente se van generando las caja número3, número4, etc. Una vez terminada la función y hechos todos los cambios, se presiona el botón Terminar y la función queda insertada. El Asistente para funciones es un método para crear las funciones, pero también se pueden escribir directamente con todos sus elementos. Para iniciar la función, recuerde escribir siempre el signo igual (=) como se hace con las fórmulas para iniciar la función.

La función SUMA, por ser una de las más usadas, se puede insertar directamente

con el icono  de la barra de herramientas. Además utilizando el icono, Excel automáticamente detecta cuáles son las celdas que se van a incluir en la función. Como generalmente se realiza esta operación en la parte inferior de una tabla, toma todos los valores que se encuentran hacia arriba de esta celda. Si no encuentra valores hacia arriba, busca hacia la izquierda.

Si no se desean estos valores, simplemente se escriben las celdas deseadas o se seleccionan con el ratón.

Las funciones, se comportan como fórmulas normales en cuanto a su manejo. Se pueden agrupar con paréntesis, se pueden hacer combinaciones y se pueden anidar una dentro de otra sin que sea necesario que sean del mismo tipo.

43

Ejemplos de combinaciones de funciones válidas: =SUMA(C1:C5) *5

=SUMA(C6,D6,C7,D7:D9)*SUMA(C6:C7,C9:C10)

=SUMA(MAX(A1:C5),MIN(A1:C5))

Además de la función SUMA podemos usar la función =PROMEDIO(C1:C5) para encontrar el promedio de ventas

Además de la función SUMA, existen más de doscientas funciones de diferentes categorías y dependiendo de las necesidades específicas del usuario podrá seleccionar funciones de tipo:

Financieras Búsqueda y Referencia Fecha y hora Bases de Datos Matemáticas y Trigonométricas Texto Estadísticas Información Lógicas

4.3: Otras funciones

Manejo de la función SI Manejo de la función SI anindada Manejo de la función como Y,O

Uso de la función SI Devuelve un valor si la condición especificada es VERDADERO y otro valor si dicho argumento es FALSO.

Utilice SI para realizar pruebas condicionales en valores y fórmulas.

Sintaxis Para hojas de cálculo y de macros SI(prueba_lógica;valor_si_verdadero;valor_si_falso) Prueba_lógica es cualquier valor o expresión que pueda evaluarse como VERDADERO o FALSO.

44

Valor_si_verdadero es el valor que se devolverá si prueba_lógica es

VERDADERO. Si prueba_lógica es VERDADERO y se omite el argumento valor_si_verdadero, la función devuelve VERDADERO. Valor_si_verdadero puede ser otra fórmula.

Valor_si_falso es el valor que se devolverá si prueba_lógica es FALSO. Si prueba_lógica es FALSO y se omite el argumento valor_si_falso, la función devuelve FALSO. Valor_si_falso puede ser otra fórmula.

Es posible anidar hasta siete funciones SI como argumentos valor_si_verdadero y valor_si_falso para construir pruebas más elaboradas. Cuando los argumentos valor_si_verdadero y valor_si_falso se evalúan, la función SI devuelve el valor devuelto por la ejecución de las instrucciones.

Si uno de los argumentos de la función SI es una matriz, cada elemento de la matriz se evaluará cuando se ejecute la instrucción SI. Cuando alguno de los argumentos valor_si_verdadero o valor_si_falso es una función que ejecuta una acción, se ejecutan todas las acciones.

Ejemplos:

En el siguiente ejemplo, si el valor en la celda A:10 es 100, prueba_lógica será VERDADERO y se calculará el valor total del rango B5:B15. De lo contrario, prueba_lógica será FALSO y se devolverá una cadena de texto vacía ("") que borrará el contenido de la celda que contenga la función SI. SI(A10=100;SUMA(B5:B15);"")

45

En la gráfica se muestra un ejemplo clásico de uso del SI , en el se compara si la cantidad sobrepasa o es igual a 5 se cobra a 250 la unidad en caso contrario la unidad vale 300.

Manejo de la función SI anidada Es posible anidar hasta siete funciones SI como argumentos valor_si_verdadero y valor_si_falso para construir pruebas más elaboradas.

Cuando los argumentos valor_si_verdadero y valor_si_falso se evalúan, la función SI devuelve el valor devuelto por la ejecución de las instrucciones.

Si uno de los argumentos de la función SI es una matriz, cada elemento de la matriz se evaluará cuando se ejecute la instrucción SI. Supongamos que desea calificar con letras los números de referencia con el nombre Promedio.

Si Promedio es La función devuelve Mayor que 89 = A De 80 a 89 = B De 70 a 79 = C De 60 a 69 = D Menor que 60 = F

46

Se podría utilizar la siguiente función anidada SI: =SI(A1>89,"A",SI(A1>79,"B",SI(A1>69,"C",SI(A1>59,"D","F"))))

En el ejemplo anterior, la segunda instrucción SI representa también el argumento valor_si_falso de la primera instrucción SI. De manera similar, la tercera instrucción SI es el argumento valor_si_falso de la segunda instrucción SI. Por ejemplo, si el primer argumento prueba_lógica (A1>89) es VERDADERO, se devuelve el valor "A". Si el primer argumento prueba_lógica es FALSO, se evalúa la segunda instrucción SI y así sucesivamente.   Manejo de la función como Y,O Y Devuelve VERDADERO si todos los argumentos son VERDADERO; devuelve FALSO si uno o más argumentos son FALSO.

Sintaxis    Y(valor_lógico1;valor_lógico2; ...)

Valor_lógico1;valor_lógico2; ...   son de 1 a 30 condiciones que desea comprobar y que pueden ser VERDADERO o FALSO. Los argumentos deben evaluarse como valores lógicos (VERDADERO O FALSO), o los argumentos deben ser matrices o referencias que contengan valores lógicos.

  Si un argumento matricial o de referencia contiene texto o celdas vacías, esos valores se pasan por alto. Si el rango especificado no contiene valores lógicos, la función Y devuelve el valor de error #¡VALOR!

Ejemplos: Y(VERDADERO; VERDADERO) es igual a VERDADERO Y(VERDADERO; FALSO) es igual a FALSO Y(2+2=4; 2+3=5) es igual a VERDADERO Si B1:B3 contiene los valores VERDADERO, FALSO y VERDADERO, entonces: Y(B1:B3) es igual a FALSO Si B4 contiene un número entre 1 y 100, entonces: Y(1<B4; B4<100) es igual a VERDADERO Supongamos que desea mostrar la celda B4 sólo si contiene un número entre 1 y 100, y que desea mostrar un mensaje si no lo contiene. Si B4 contiene 104, entonces: SI(Y(1<B4; B4<100); B4; "El valor queda fuera del rango") es igual a "El valor queda fuera del rango." Si B4 contiene 50, entonces: SI(Y(1<B4; B4<100); B4; "El valor queda fuera del rango") es igual a 50

O Devolverá VERDADERO si alguno de los argumentos es VERDADERO; devolverá FALSO si todos los argumentos son FALSO.

47

Sintaxis O(valor_lógico1;valor_lógico2; ...) Valor_lógico1; valor_lógico2; ...   son entre 1 y 30 condiciones que se desean comprobar y que pueden ser VERDADERO o FALSO.

Los argumentos deben ser valores lógicos como VERDADERO O FALSO, o matrices o referencias que contengan valores lógicos.

Si un argumento matricial o de referencia contiene texto o celdas vacías, dichos valores se pasarán por alto.

Si el rango especificado no contiene valores lógicos, O devolverá el valor de error #¡VALOR!

O(VERDADERO) es igual a VERDADERO O(1+1=1;2+2=5) es igual a FALSO

Si el rango A1:A3 contiene los valores VERDADERO, FALSO y VERDADERO, entonces: O(A1:A3) es igual a VERDADERO

48

E j e r c i c i o s¿Cómo puedo convertir un texto a minúsculas?

De vez en cuando recibimos una lista de lugares o de nombres en puras mayúsculas y necesitamos convertirlas a minúsculas para hacerlas parte de una carta, o si es un nombre propio, acentuar la primera letra del mismo. 

¿Cómo puedo trabajar con números romanos?Los números en Excel se pueden representar de muchas maneras con solo cambiar su formato. Por ejemplo, los días y las horas son solo números, que pueden tener formato general o formato de fecha y hora. También los decimales se pueden mostrar como fracciones.

¿Cómo puedo contar cuántos productos de cierta clase hay en una lista muy larga? 

Cuando existe una lista larga de artículos, con algunos de ellos repetidos (por ejemplo, una lista de pedido, o el resumen de ventas) a veces es necesario contar cuántas veces aparece determinado producto en la lista. 

¿Cómo puedo sumar cuántos productos de cierta clase vendí si tengo una relación de ventas muy larga?

Cuando existe una lista larga de artículos, con algunos de ellos repetidos (por ejemplo, una detalle de ventas mensual) a veces es necesario sumar cuántas unidades se han vendido de cada producto. 

49

Conclusión El uso de funciones es muy similar al uso de formulas, en la cual la gran diferencia es que las funciones son formulas predefinidas que ejecutan cálculos utilizando valores específicos, denominados argumentos, en un orden determinado o estructura.

CONCLUSIÓN FINAL

Durante todo este curso has ejercitado numerosas técnicas para trabajar con Microsoft Excel. Al diseñar este curso, he hecho un esfuerzo para facilitarte el trabajo, proporcionándote herramientas de trabajo y la mayoría de las soluciones, además de distintas visiones de la integración de las Tecnologías de la Información y de la comunicación para que te vayas poniendo al día.

Has aprendido Excel intermedio que era el objetivo principal de este curso, si es cierto que la creatividad es el resultado de encontrar relaciones entre experiencias que antes no las tenían, lo que te pido es que conjugues lo aprendido en este curso y tu conocimiento y experiencia de la materia en la que te desarrollas para facilitarte tus tareas cotidianas

Mide la utilidad en provecho propio: vas a dedicar unas horas a este trabajo, no las tires por la borda y piensa en qué situación de tus tareas cotidianas puedes usar la hoja de cálculo y diseña la práctica, la unidad didáctica, la evaluación, el cuestionario.

50

BIBLIOGRAFÍA O LECTURAS DE APOYO

Microsoft Excel ( 2000). 9.0.6926 SP-3

Fernández, Gonzalo Héctor MS-Excel (2001) gonzalo@fernandez.sh

Jones Edward, Aplique dBase III Plus (1988) McGraw-Hill ISBN 968-422-203-3

Gold LauraMaery y Dan Post Excel Para Windows 95 fácil ! (1995),¡ (Prentice-Hall Hispanoamericana ISBN 968-880-603-X

Kraynak Joe Prentice Hall, Microsoft Office 2000 (serie fácil) (1999) ISBN 970-17-0315-4

Todos los derechos reservadosNo se permite reproducir este manual

Ni total ni parcialmenteSin previa autorización por escrito

de la Universidad de Colima

Impreso en la Universidad de ColimaEn los talleres de la Dirección General de Educación Continua

Elaborado por: Miguel ángel Flores M. y Miguel Ángel Rodríguez OrtizPara fines de enseñanza y uso exclusivo de l apropia DGEC

Tercera impresión, septiembre de 2008

51