Microsoft Excel Basico e Intermedio

Microsoft Excel Básico-Intermedio

Tecnologías de la Información y Comunicación: área Sistemas Informáticos (TIC-S I). Edgar León Olivares, Director de la carrera. Hugo Alberto Xochicale Rojas, Instructor.

Universidad Tecnológica de Izúcar de Matamoros

1

Microsoft Excel Básico-Intermedio Instructor: Hugo Alberto Xochicale Rojas

Objetivo general Al término del curso el alumno habrá adquirido las habilidades necesarias para el diseño y creación de hojas de cálculo en Microsoft Excel mediante la comprensión y aplicación de diferentes herramientas y características de dicha aplicación.

Prefacio • • •

•

•

•

•

•

I Introducción o Entorno de Microsoft Excel y su funcionalidad. II Operaciones Básicas o Operaciones más utilizadas con libros, hojas, filas, columnas y celdas. III Fórmulas o Objetivo y aplicación de fórmulas, cálculos en las hojas de un libro; cómo generar una fórmula, las fórmulas que hacen referencia a otras celdas de la hoja e inclusive del libro. IV Funciones o Funciones con las que se facilita cualquier cálculo en un libro (se explican a detalle solo las básicas). V Datos o Manejo de grandes cantidades de datos (bases de datos), es imprescindible manipular los mismos de una manera sencilla y segura. Operar con ellos para filtrar información, ordenarlos según se requiera, realizar un tipo de consulta con alguna información en específico, etc. VI Gráficos o Para un análisis gráfico-visual de la información que se tenga capturada en una hoja de cálculo es de gran ayuda la creación de gráficos que representen dicha información. VII Macros o Aquellas tareas repetitivas comúnmente utilizadas en un libro de Excel, una macro es la solución para ahorrar trabajo y tiempo (introducción a las macros). APÉNDICES o Apéndice A. Ejercicios del curso para reforzamiento del conocimiento. Apéndice B. Propuesta de examen fi nal.


2

Índice

I. INTRODUCCIÓN

4

Entorno de Excel y su funcionalidad I.1. Características I.2. Ayuda en Excel

4 5

II. OPERACIONES BÁSICAS

7

Operaciones más utilizadas con libros, hojas, filas, columnas y celdas. II.1. II.2. II.3. II.4. II.5. II.6.

Celdas Filas y Columnas Hojas Libros Hipervínculos Pegado Especial

7 10 12 15 16 17

III. FÓRMULAS

19

Cálculos en las hojas de un libro; como generar una fórmula, las fórmulas que hacen referencia a otras celdas de la hoja, inclusive del libro. III.1. Creación de una fórmula III.2. Operadores III.3. Referencias Relativas , Absolutas y Mixtas

IV. FUNCIONES

19 19 20

V.2. V.3. V.4. V.5. V.6.

Buscar y Reemplazar Nombres para un rango de celdas Bases de datos Filtros Tablas Dinámicas

VI. GRÁFICOS

28 30 30 31 34

39

Representación visual gráficamente de la información que se tenga capturada en una hoja de cálculo. VI.1. Crear un Gráfico 39 VI.2. Tipos de Gráficos 43 VI.3. Modificar Gráficos 44

VII. MACROS

49

Aquellas tareas repetitivas, y comúnmente utilizadas en un libro de Excel, una macro es la solución para ahorrar trabajo y tiempo (solo introducción a las macros) VII.1. Introducción VII.1. Como crear una macro VII.3. Modificar una macro

49 49 50

APÉNDICES

49

A. Ejercicios. B. Propuesta de examen final.

51 70

21

Funciones con las que se facilita cualquier cálculo en un libro. IV.1. Introducción IV.2. Ejemplos de funciones

V. DATOS

21 21

27

Manejo de grandes cantidades de datos (bases de datos), manipulación de los mismos de una manera sencilla y segura, operar con ellos para filtrar información, ordenarlos según se requiera, realizar un tipo de consulta con alguna información en específico. V.1. Ordenar Datos Universidad Tecnológica de Izúcar de Matamoros

27

3

I. INTRODUCCIÓN

Objetivo. Conocer el entorno y las características principales de la hoja de cálculo de Excel. ¿Qué es Microsoft Excel? Microsoft Excel es una aplicación bastante amplia que nos permitirá hacer desde las operaciones más sencillas como sumar o guardar datos, hasta hacer complejas funciones, gráficos, estadísticas, podremos incluso hacer pequeñas aplicaciones para la vida cotidiana.

I.1. Características Microsoft Excel tiene compatibilidad con las demás aplicaciones de Office lo cual nos será de gran ayuda (más no necesario) saber previamente una de estas aplicaciones como Word, PowerPoint, Access, etc. Todas estas aplicaciones comparten unas características únicas. Para ejecutar Microsoft Excel pulsamos el botón Inicio y luego la opción Programas/Microsoft Excel. Los archivos de Microsoft Excel se definen como libros, dentro de los cuales hay hojas de cálculo, éstas hojas se componen de celdas. Las filas y columnas de cada hoja tienen una nomenclatura para que cada celda se pueda identificar con coordenadas, las filas se identifican con números del 1 hasta la 65536 y las columnas se identifican con letras desde la A hasta la IV. Entonces la identificación de una celda sería (columna, fila), por ejemplo la celda B5 corresponde a la columna B y a la fila 5. Cada celda tiene sus propiedades, que se pueden cambiar individualmente de acuerdo a nuestras necesidades. Las principales características de la interfaz de Microsoft Excel se muestran en la siguiente imagen:


4

I.2. Ayuda en Excel La Ayuda en Microsoft Excel la podemos encontrar a modo de respuestas a una pregunta formulada a través del menú Ayuda (que aparece en la Barra de menús representado por una “?”). A través del comando Ayuda de Microsoft Excel, se abre el cuadro ¿Qué desea hacer?. Realizar en esta celda la pregunta y entonces la aplicación mostrará los temas de Ayuda que coincidan con la pregunta formulada en un cuadro de lista desplegable. Al hacer clic sobre alguno de los temas que propone cualquiera de los dos cuadros, se abre la ventana Ayuda de Microsoft Excel, que muestra toda la información sobre el tema elegido. A la izquierda se encuentran tres fichas que ayudarán a moverse por todos los temas de Ayuda, referencias para la búsqueda de temas de Ayuda mediante palabras clave o unidades temáticas o directamente una nueva búsqueda con el Asistente para Ayuda, que posibilita también una búsqueda en el Web. En la parte superior de la ventana hay algunos botones que ayudarán a gestionarla. Así, el botón Mosaico Automático, permite ver la ventana de Ayuda junto con el documento que se esté editando; con otros botones se pueden Ocultar las fichas de ayuda, Imprimir el tema de Ayuda, ir hacia Atrás/Adelante, o acceder a cualquiera de las acciones anteriores con el botón de lista desplegable Opciones. Otra manera de entrar a la ayuda es mediante la tecla donde también aparece este cuadro.


5

Ayudante de Office. En el menú Ayuda se encuentra el comando Mostrar/Ocultar el Ayudante de Office (por defaul siempre se mostrará a menos que se deshabilite). Al hacer clic sobre él, se muestra el Ayudante de Office. Éste aparece como una animación que ofrece temas de ayuda y sugerencias en el cuadro ¿Qué desea hacer?. Dentro de este cuadro se encuentra el botón Opciones que muestra un cuadro de dialogo con dos fichas en las que se puede configurar el Ayudante o elegir la animación que lo represente. Al hacer clic con el botón secundario del ratón sobre el Ayudante de Office se ofrece un menú contextual donde también se puede acceder al comando Opciones para configurar al Ayudante, Animar, Ocultar o Elegir Ayudante.


6

II. OPERACIONES BÁSICAS Objetivo. Aplicar las operaciones básicas con celdas, filas, columnas, hojas y libros en Excel.

II.1. Celdas Copiar Copiar una celda es duplicar su contenido y formato a otra celda, hay muchas formas para copiar una celda o varias, una de las formas más sencillas es: 1. Hacer clic con el Mouse sobre la celda que vamos a copiar, (lo que estamos haciendo es seleccionar la celda) y quedará marcada por un borde negro, si lo que se quiere es copiar varias celdas a la vez, hay varias formas de hacerlo, una es haciendo clic sostenido sobre las celdas, o podemos recurrir a la tecla “control” (sosteniendo dicha tecla) y haciendo clic en cada una de las celdas que queremos copiar. 2. En la barra de menús hacemos clic en Edición/Copiar. 3. Damos clic en la celda de destino, es decir a donde se va a copiar la celda 4. En la barra de menús hacemos clic en Edición/Pegar. Mover Mover una o varias celdas es cortar su contenido y formato y colocarlo en otra(s) celda(s) respectivamente, hay dos formas de mover celdas, una de ellas es repitiendo los mismos pasos que se hicieron para copiar, pero en lugar de pulsar Copiar, pulsar Cortar. Y la otra forma es la siguiente: 1. Seleccionar la(s) celda(s) que se moverán. 2. Hacer un clic sostenido en el borde negro que las rodea (cambiara el apuntador del mouse a una cruz), y luego arrastrar hasta el lugar donde se quiera mover. Formato de Celdas El formato de las celdas es la configuración que ésta tendrá, como tipo de fuente, colores, bordes, tipo de datos, etc. Para acceder a este cuadro de Formato de Celdas, Universidad Tecnológica de Izúcar de Matamoros 7

se hace clic derecho en la(s) celda(s) que se desea cambiar el formato, y después en el menú que aparece pulsar en la opción Formato de Celdas.... Se describe cada apartado a continuación: Número Este apartado nos permite darle una categoría a la celda, según el tipo de datos que valla a contener la misma, puede seleccionarse un tipo de configuración adecuado, por ejemplo; si lo que se desea guardar es una fecha, entonces se selecciona Fecha en el cuadro de Categoría y después en el cuadro Tipo seleccionamos el formato que más convenga.

Alineación: Aquí se puede manipular la posición de los datos en la celda. Este cuadro se divide en tres zonas: 1. Alineación del texto: Sirve para centrar, justificar, etc. horizontalmente y verticalmente. 2. Orientación: Donde se define la inclinación del texto en la celda. 3. Control del texto: La opción más común de estos es "Combinar celdas" la cual nos sirve para fusionar varias celdas a una sola. Las otras dos opciones sirven para que el texto se distribuya bien en una celda


8

Fuente: En este apartado se manipula el tipo de fuente, estilo, tamaño, color y otras cosas más, además puede verse la vista previa de los cambios que se realicen.

Insertar comentarios Cada celda puede contener su propio comentario, según sus necesidades. Para insertar un cometario, se hace un clic derecho en la celda, seguido de seleccionar la opción Insertar comentario, después de escribir el comentario podemos presionar la tecla ESC o simplemente hacer clic fuera del comentario para que éste quede guardado, la celda quedará con un triángulo de color rojo en la parte superior derecha, esto indica que hay un comentario guardado, para poder leerlo hacemos ponemos el cursor del mouse sobre el triángulo rojo e inmediatamente aparecerá el comentario en pantalla. Formato condicional


9

Esta herramienta sirve para dar formato a las celdas, según se cumplan ciertas condiciones. El formato, como ya se ha visto antes, se compone del color de la fuente y fondo, bordes, tipo de fuente, tamaño, entre otros. Por ejemplo si se tiene una celda que muestra el saldo de la cuenta corriente, se le puede aplicar el formato condicional a ésta para que cuando tenga un valor negativo el valor se muestre en color rojo. Para aplicar formato condicional: primero seleccionar las celdas, luego: seleccionar la opción Formato condicional en el menú Formato, nos aparecerá la ventana de Formato condicional, la primera lista desplegable de la izquierda es para indicar a que se le aplicará el formato condicional, puede ser a la fórmula o al valor. En la segunda se selecciona el operador, puede ser igual, mayor que, no igual, etc. y finalmente en el siguiente cuadro de texto se digita el valor que servirá como base en la condición (o la referencia a una celda). Hasta aquí hemos realizado la condición, por último falta indicar que formato se aplicará a la celda en caso de que la condición se cumpla, para ello se pulsa en el botón Formato y aparecerá la ventana donde se especificará el formato que se quiera. Si se requiere aplicar varias condiciones a las mismas celdas, pulsar el botón Agregar y se desplegará una nueva línea para la nueva condición.

II.2. Filas y Columnas Las hojas de Microsoft Excel están compuestas por filas y columnas, filas son aquellos grupos de celdas horizontales que están identificadas con un número, y columnas son las que están verticalmente agrupadas y se representan por medio de letras. Copiar Primero seleccionar la fila o columna que se desee copiar. Para seleccionar una fila debe de hacer clic en la parte donde se muestra el número de dicha fila, si lo que se desea es copiar una columna, hacer clic en la parte donde se muestra la letra.


10

Después de seleccionar la(s) fila(s) o columna(s), pulsar sobre Edición > Copiar, luego se debe seleccionar la fila o columna según corresponda y pulsamos Edición > Pegar, el contenido se deberá de haber copiado a dichas celdas de una forma equivalente a como estaban originalmente. Mover Para mover debemos de seleccionar las filas o columnas como en la explicación anterior en Copiar, luego hacer clic sostenido en el borde y mover el cursor hasta la fila o columna a donde se desea poner el contenido, si en dicha fila o columna hay otro contenido, aparecerá una ventana preguntando que si se desea reemplazar el contenido, ya que el Excel elimina todo el contenido de la fila o columna para poner el nuevo contenido.

Insertar También es posible insertar filas o columnas, que pueden ser sin contenido o con contenido, para insertar una fila o columna con contenido, primero debe copiarse dicha Universidad Tecnológica de Izúcar de Matamoros

11

fila o columna que contiene el contenido y luego seleccionar la fila o columna según corresponda y se pulsa Insertar > Celdas copiadas, hay que tener en cuenta que no se puede insertar fila cuando hay conte nido en la última fila de la hoja, lo mismo ocurre con las columnas. Eliminar Para eliminar una fila o columna basta con seleccionarla y pulsar la opción Edición > Eliminar. Pero hay que tener cuidado porque si lo único que se quiere eliminar es el contenido, no hace falta eliminar la fila o columna, solo se selecciona la (s) filas y/o columna (s) y se oprime la tecla Suprimir o la opción Edición > Borrar y luego seleccionar lo que desea borrar, que puede ser: el contenido, comentarios, formatos o todo. Ocultar A veces es necesario no mostrar algunas filas o columnas ya sea porque existen datos que no se deben de modificar ni de ver, pero que son utilizados por formulas o simplemente no son útiles en ese momento. Entonces puede ocultárseles de la siguiente manera: Seleccionar la(s) fila(s) o columna(s) y luego sobre el área seleccionada pulsar el botón derecho del mouse y hacer clic en la opción Ocultar. Mostrar filas ocultas Por ejemplo; si se tiene oculta la fila 4, deben seleccionarse las filas 3 y 5, luego pulsar el botón derecho del mouse sobre el área seleccionada y hacer clic en Mostrar. La fila 4 debería de aparecer de nuevo entre las filas 3 y 5.

II.3. Hojas Las hojas de cálculo de Microsoft Excel son bastante grandes, ya que cuentan con 16.777.216 celdas. Además están compuestas por filas y columnas, por defecto solo hay 3 hojas cuando se crea un libro, pero se pueden insertar otras nuevas (hasta 255), eliminar y cambiar el nombre de dichas hojas.


12

Se puede cambiar el numero de hojas en los libros nuevos (en los documentos de Excel nuevos que se generen) en: Herramientas > Opciones > General y luego se cambia el valor que hay en "Número de hojas en nuevo libro". Insertar Si es necesario una Hoja de cálculo más en el libro de trabajo, solo se tiene que ir a Insertar > Hoja de cálculo. O hacer clic derecho en el nombre de una hoja y pulsar la opción Insertar, seguido de seleccionar hoja de cálculo y clic en aceptar. Eliminar Para eliminar una Hoja solo se debe hacer clic derecho en el nombre de dicha Hoja y pulsar la opción Eliminar.


13

Cambiar nombre El nombre de la Hoja de cálculo lo puede cambiarse por uno más significativo, sólo debe hacerse doble clic sobre el nombre y de esta manera podrá editarse, culminando su edición con la tecla Enter. Mover Para mover una Hoja, basta con hacer un clic sostenido sobre el nombre de la hoja y desplazarla hasta el lugar deseado. Proteger Significa que es posible bloquear la Hoja de cálculo para que no se puedan hacer cambios en las celdas, Esta opción solo tendrá efecto en las celdas que estén bloqueadas, por defecto todas las celdas de la hoja lo están. Si se desea desbloquear alguna celda basta con hacer clic derecho sobre ella, luego seleccionar la opción Formato de celdas, abrir el apartado Proteger y desactivar la casilla con el nombre Bloqueada. Después cuando ya tengamos las celdas correctamente bloqueadas, podemos proceder a proteger la hoja de la siguiente forma: Hacer clic en Herramientas > Proteger > Hoja, en la ventana que vemos a continuación debemos indicar que vamos a proteger en la hoja. Contenido: Impide que se hagan cambios en las celdas de la hoja de cálculo y en los elementos de los gráficos. Objetos: Impide que otros usuario eliminen, muevan, modifique o cambien el tamaño de los objetos gráficos de una hoja de cálculo o de un gráfico.


14

Escenario: Impide que se hagan cambios en las definiciones de los escenarios de una hoja de cálculo. Opcionalmente puede digitarse una contraseña para mayor seguridad. Finalmente pulsar la opción Aceptar.

II.4. Libros Los libros de Microsoft Excel son los mismos archivos que la misma aplicación crea con extensión .xls, así como los archivos de Word se les llama documento o a los de PowerPoint se les llama Presentación, en Excel se llaman libros, los cuales se componen de varias hojas de cálculo. Crear Para crea un nuevo libro; pulsar Archivo > Nuevo en la ventana que aparece, puede especificarse que tipo de libro se requiere. Para crear un libro limpio, debe seleccionarse Libro, pero también hay otros tipos de libros predefinidos en el apartado Soluciones de hoja de cálculo. Guardar Para guardar un libro solo se debe seleccionar Archivo > Guardar, en la ventana que nos aparece, debe especificarse en que carpeta se guardará y además el nombre del mismo, luego pulsar el botón Guardar. Abrir Abrir un libro ya existente es muy sencillo, solo se requiere hacer clic en el menú Archivo y luego pulsar la opción Abrir, en la ventana que aparece hay que especificar la ubicación del libro y por último pulsar sobre el botón Abrir. Proteger


15

Proteger un libro resulta muy útil cuando se desea asegurar que la estructura o la ventana en que se ve el libro no cambie, es decir que no se puedan eliminar hojas u ocultar el libro. Para lograr esto solo se debe pulsar Herramientas > Proteger > Proteger libro, en la ventana que se muestra a continuación seleccionar las opciones que mas convenga: Estructura: Protege la estructura de un libro para que no se pueda borrar, mover, ocultar, mostrar o cambiar el nombre de las hojas ni insertar hojas nuevas. Ventana: Protege las ventanas de un libro para que no se pueda mover, cambiar de tamaño, ocultar, mostrar o cerrar. Opcionalmente puede protegerse con una contraseña, y pulsamos el botón Aceptar.

II.5. Hipervínculos ¿Qué es? Un hipervínculo en Microsoft Excel tiene el mismo concepto que en las páginas web, sirve para ir a otra parte del libro o incluso, enviar un email, abrir una página web o abrir cualquier otro tipo de archivo. Los hipervínculos pueden hacerse en una celda, una imagen, un gráfico o cualquier otro objeto que tengamos en el libro. Crear La manera de hacer un hipervínculo a una celda, imagen o cualquier otro objeto es la misma, lo primero que se debe de hacer es pulsar clic derecho en el objeto que se le aplicará el hipervínculo, luego seleccionar Hipervínculo y aparecerá la ventana donde se especifican la opciones del nuevo hipervínculo.


16

Tipos de hipervínculos. Archivo o página web existente: Sirve para hacer el vínculo a una página web de las que se halla visitado previamente o también puede agregarse la dirección de otra web. Lugar de este documento: Aquí es posible hacer un link a otra hoja de libro, incluso especificar la celda. La sintaxis se puede poner en el cuadro que se llama texto: nombre de hoja! celda, ejemplo: para que se desplace a la celda G6 de la hoja 2, se tendría que poner: hoja2!G6. Crear nuevo documento: Sirve para que crear un nuevo de Office incluso páginas web, debe pulsarse en el botón Cambiar e indicar la ruta y nombre que tendrá el nuevo archivo. Dirección de correo electrónico: Por último, se tiene la opción de hacer un hipervínculo a un correo electrónico, solo se debe incluir la dirección de e-mail del destinatario (Hay que tener en cuenta que esto solo funciona cuando tenemos instalado algún programa de correo electrónico, como por ejemplo el Outlook.)

II.6. Pegado Especial ¿Qué es? Esta opción permite pegar una celda, grafico, tabla, etc. como se hace con la opción Pegar convencional, a diferencia que éste permite seleccionar que es lo que se quiere pegar exactamente, además de tener la opción de pegar el vínculo lo cual significa que cuando cambia el objeto de original en la copia se reflejan los cambios pertinentes. Universidad Tecnológica de Izúcar de Matamoros 17

Pegado especial de Excel a Excel Primero que todo, debe seleccionarse las celdas, luego seleccionar copiar y después ejecutar Edición > Pegado especial, aparecerá la siguiente ventana.

En el apartado de "pegar" se indica que es lo que se desea copiar de las celdas, puede ser todo, o solo las fórmulas, formatos, comentarios, etc. En el apartado de "operación" es posible elegir que operación se va aplicar a las celdas entre si (si el contenido de las celdas es numérico). Por Ejemplo si en la celda A1 tenemos el número 5 y en B2 hay un 3, podemos copiar la celda A1 y hacerle un pegado especial a la celda B2 y en operación se selecciona suma entonces el resultado que se obtendrá será B2 = 8. También puede copiarse el vínculo presiona ndo en la misma ventana sobre el botón Pegar vínculos, esto significa que siempre se mostrará lo mismo que hay en la celda original, es una especie de espejo o copia que se actualiza a cada cambio que ocurre en la celda original. Pegado especial de Excel a otra aplicación Office Cuando se realiza este tipo de pegado se tienen dos opciones principales; Pegar y Pegar vínculo. En "Pegar" se logra pegar el contenido con el formato que seleccionemos en el cuadro de la derecha. Con "Pegar vínculo" se consigue pegar el contenido con la ventaja que se actualizará si el origen cambia, adicionalmente es posible elegir el tipo de formato con que se visualizará. Para realizar un vínculo es necesario que la aplicación donde se creó permita que existan éstos. Además, se debe guardar el archivo que contiene la información que se está vinculando, para que si se modifica el archivo origen se puedan actualizar los cambios en el destino. Universidad Tecnológica de Izúcar de Matamoros

18

III. FÓRMULAS Objetivo. Aplicar diferentes fórmulas para realizar cálculos con los datos contenidos en una hoja de cálculo. Las Fórmulas en Microsoft Excel, permiten calcular un valor a partir de una serie de datos que se introduzcan o los ya existentes en otras celdas que harán referencia a la fórmula a modo de ecuación. Esto permitirá que se modifiquen los valores que contengan ciertas celdas y se actualicen.

III.1. Creación de una fórmula Para la creación de Fórmulas hay que situarse en la celda en la que se va a introducir la Fórmula , teclear el signo (=) y escribir la expresión. Ésta podrá ser un grupo de datos formado por referencias a celdas o rango de celdas, valores numéricos, operadores aritméticos y Funciones, las cuales se verán más adelante. Un ejemplo de expresión sería el siguiente: (=5*8.3/3 ) ó con referencias a otras celdas: (=C3+C4*5). Es posible que al teclear la Fórmula se desee revisar o corregir. Esto se hará seleccionando la celda que contenga la Fórmula , haciendo clic en la barra de fórmulas (en la que se muestra la Fórmula) y modificándola. Después de la modificación se actualizará el resultado de la misma. Si los valores de las celdas referenciadas en la Fórmula se modifican o eliminan, el resultado de la misma también se actualizará.

III.2. Operadores Aritméticos Las Fórmulas Numéricas serán aquellas en las que, además de referenciar celdas que contienen números, se incluyen valores numéricos o sólo estos. Los Operadores Aritméticos van a permitir realizar cálculos dentro de las Fórmulas. Los Operadores básicos son: suma (+), diferencia (-), multiplicación (*), división (/), exponenciación (^). El orden de ejecución de estos operadores básicos será el de operaciones entre paréntesis, exponenciación, multiplicación y división de izquierda a derecha y, por último, la suma y diferencia de izquierda a derecha. Lógicos Las Formulas Lógicas serán aquellas que al referenciar valores de celda comparándolos con los Operadores Lógicos, van a devolver criterios de VERDADERO o FALSO. Universidad Tecnológica de Izúcar de Matamoros

19

Los Operadores Lógicos que se pueden usar en este tipo de Fórmulas son: igual (=), menor que (<), mayor que (>), mayor o igual que (>=), menor o igual que (<=) y distinto (<>).

III.3. Referencias relativas , absolutas y mixtas Casi todas las fórmulas hacen referencia a alguna celda, rango de celdas o a sus nombres. Una referencia indica a Excel en qué celdas debe buscar los valores o datos que desea utilizar en una fórmula. Las referencias permiten utilizar en una fórmula datos de distintas partes de la hoja de cálculo. También puede hacer referencia a celdas de otras hojas del mismo libro y a otros libros. Las referencias a celdas de otros libros se denominan vínculos. Es posible hacer alusión a una celda refiriéndonos a su posición en la hoja y en el libro; de todos modos existen cuatro estilos diferentes de referencias: Referencia absoluta: en este caso, cuando se copia la fórmula, la referencia a la celda no cambia. Para especificar que independientemente de dónde se copie la fórmula la celda continuará siendo la misma, se utilizará el signo dólar ($), por ejemplo: $A$1. Referencia relativa: en este caso, cuando se copia la fórmula, la referencia de la celda se ajusta a la nueva posición de la fórmula. Excel asume por defecto esta referencia cuando se escribe A1. Fila absoluta: en este caso una parte de la referencia es relativa y otra, la fila, es absoluta; por tanto, cuando se copie la fórmula la parte correspondiente a la columna se ajustará a la nueva ubicación de la fórmula y en cambio se mantiene la fila de origen, por ejemplo cuando se copia una fórmula que contiene la referencia A$1 siempre se referirá a la fila 1, independientemente de dónde se copie la fórmula. Columna absoluta: es igual que el estilo anterior pero a la inversa, es decir, que en este caso es la columna la que permanece constante independientemente de dónde se ubique la fórmula al copiarla. Ejemplo: $A1. Para crear una referencia absoluta debe hacerlo manualmente añadiendo el signo de dólar ($), porque si lo hace señalando la celda, Excel creará automáticamente una referencia rela tiva. Realmente el único objetivo de utilizar los distintos estilos de referencia es que no se desvirtúen las fórmulas cuando se copian. Una vez que introduzca la fórmula en la primera celda de la tabla puede copiarla en todas las demás. Si usara sólo las referencias relativas, al copiarlas incurriría en errores. En las Referencias Mixtas sólo se bloquea una fila o una columna como en las Referencias Absolutas, así que sólo se actualizará la parte correspondiente al copiar o mover la Fórmula.


20

IV. FUNCIONES Objetivo. Emplear diversas funciones de Excel en una hoja de cálculo.

IV.1. Introducción ¿Qué es? Las hojas de cálculo de Microsoft Excel permiten una gran cantidad de funciones divididas en categorías, quiere decir que permite hacer cálculos financieros, manipular fechas y horas, realizar operaciones matemáticas y trigonométricas, como también hacer estadísticas, realizar búsquedas de datos en las hojas, manejar bases de datos y por último tratamiento de textos, funciones lógicas y obtener información de las celdas. La forma de utilizar cada función puede variar mucho pero todas tienen unos elementos básicos y comunes, por esta razón se limitará a explicar solo las más fundamentales, ya que Microsoft Excel cuenta con más de 200 funciones. Sintaxis básica Todas las funciones empiezan con el signo igual seguido por el nombre de la función en letras mayúsculas, finalmente están los parámetros necesarios para dicha función en medio de paréntesis. Ejemplo: =nombre_funcion(parámetros) ¿Cómo hago una función? Hay dos formas de crear una función: La primera es la manual, esta forma requiere el conocimiento previo de la sintaxis que se va a utilizar, consta de posicionarse en la celda donde quiere crear la función y luego escribir la función manualmente empezando con el signo igual, luego el nombre de la función que quiere utilizar y por último los parámetro que ésta requiere. La otra forma es mucho más sencilla, utilizando el asistente para funciones, el cual se encuentra el en menú Insertar > Función.

IV.2. Ejemplos de funciones Funciones básicas Universidad Tecnológica de Izúcar de Matamoros

21

Dada la gran cantidad de funciones que tiene Microsoft Excel, sólo se explicaran las más básicas e imprescindibles. Función Suma Esta es una de las funciones más fáciles de usar e importantes a pesar de su simplicidad, la manera de hacer ésta función se puede aplicar a la resta, multiplicación, división y muchas otras más. Puede aplicarse a sumar filas, columnas o incluso celdas que no estén continuas, eso si en todos los casos el resultado es automático, es decir en el momento en que se cambie cualquier valor, el resultado se actualizará automáticamente. Para cualquiera de los casos debemos primero seleccionar la celda en la que queremos que se muestre el resultado, luego abrimos el asistente de funciones Insertar -> Función.

En el cuadro de Categoría seleccionamos Matemáticas y luego en el cuadro de la derecha buscamos la función Suma, hecho esto nos aparecerá otra ventana solicitando los datos necesarios para completar la operación:


22

En el caso de que se desee sumar una fila o una columna, deberá digitarse en la celda Numero1 el rango que comprende dicha fila o columna, el rango se compone de la celda inicial y la celda final, por ejemplo si vamos a sumar una fila de celdas que empieza en la celda A9 y termina en D9, Excel lo representa como: A9:D9. Para representar el rango hay dos formas: Manualmente: Escribiendo el rango en la celda Número1 por ejemplo A9:D9. Seleccionando: Es la forma más sencilla, haciendo clic en el icono que aparece al final de la celda, y luego seleccionando las cedas que formarán el rango, luego se vuelve a presionar es mismo icono y veremos que en la celda Número1 estará el rango escrito que hallamos seleccionado. Abajo de la ventana nos aparece el resultado de la suma. Finalizamos haciendo clic en Aceptar. Función Si Ésta es una función lógica, devuelve un valor determinado si la condición que digitamos es verdadera o devuelve otro valor si la condición es falsa, la estructura de los parámetros es diferente al de la función Suma, para empezar ejecutemos Insertar -> Función, e el cuadro de Categoría seleccionar Lógicas y en el cuadro de la derecha seleccionamos la función SI, aparecerá una ventana parecida a la siguiente:


23

En la primer celda digitar la condición que se desee, la condición se compone de mínimo dos valores lo cuales se comparan mediante signos de igualación, mayor que, menor que, etc. Para empezar haremos una condición de solo dos valores, los valores pueden ser explícitamente números o texto, pero también pueden ser celdas o el resultado de otra función, la condición que aparece en la ventana anterior significa "Si el valor de B11 es mayor que 0", a continuación debemos introducir el valor que se quiera que aparezca cuando esta condición sea verdadera, y en la tercera celda pondremos el valor que aparecerá cuando la condición sea falsa. Funciones anidadas Una función anidada quiere decir que contiene otra función dentro de sí misma, esto puede verse más claro con el siguiente ejemplo donde además se utiliza la función promedio:

Donde “Suma!”, se refiere a otra hoja del libro. Universidad Tecnológica de Izúcar de Matamoros

24

A9:D9 es el rango del cual se hace el promedio Nota: Recordar que el nombre de las funciones se realiza en mayúsculas (aunque en caso de digitar en minúsculas el cambio lo hará automático). En la ventana anterior tenemos dos funciones que intervienen, la función SI y PROMEDIO, el resultado de ésta, tiene como objetivo informar con un mensaje al usuario el promedio de los valores de un conjunto de celdas de la hoja Suma. Para hacer esto primero debemos ir a Insertar > Función y luego seleccionar la función SI, debe de aparecer una ventana como la anterior pero sin nada en las celdas, después en la celda de Prueba_lógica hacemos un clic para luego insertar allí la función PROMEDIO, para ello seleccionamos dicha función la lista desplegable que aparece a la izquierda de la barra de fórmulas y seleccionamos la función PROMEDIO (seleccionar - mas funciones…- si no existe en la lista la función deseada y buscarla).

Luego de esto nos aparecerá la ventana correspondiente a la función PROMEDIO, donde tenemos que indicar a cuales celdas se le calculará el promedio, en este caso será a el rango de A9 a D9 de la hoja Suma, para hacer esto se puede digitar manualmente lo que sale en la celda Numero1 o hacer clic en el botón que aparece a la derecha de ésta y a continuación ir a la hoja Suma y seleccionar dichas celdas y por último presionar ENTER. Hasta aquí la función PROMEDIO estará concluida, ahora solo presionamos el botón aceptar para regresar a la función SI.

Ahora tendremos una ventana parecida a la siguiente, con la función PROMEDIO dentro de la celda Prueba_lógica de la función SI. Pero para que la condición esté completa hay que comparar el valor que devolverá la función PROMEDIO con otro valor en este caso 400, quiere decir que "si el valor que devuelve la función PROMEDIO es mayor o igual que 400".


25

Por último falta escribir los valores que la función SI devolverá en caso de que la condición sea FALSA o VERDADERA. Hasta aquí todo quedará como la ventana que se visualiza al principio de ésta explicación. Esto explica la forma de anidar función pero no precisamente se tiene que limitar a solo dos funciones pueden ser más, además también se pueden agregar más funciones en las celdas Valor_si_verdadero y Valor_si_falso.


26

V. DATOS Objetivo. Manipular los datos existentes en una hoja de cálculo.

V.1. Ordenar datos Microsoft Excel también nos permite ordenar alfabéticamente un conjunto de datos. Para ordenar una columna basta con seleccionarla y pulsar en Datos > Ordenar. Para ordenar toda una tabla de forma correcta debemos seleccionar todas las celdas empezando desde el el extremo superior izquierdo de la tabla de datos (sin incluir el título de las columnas), después pulsar en el menú Datos > Ordenar.

a continuación aparecerá la siguiente ventana…


27

En las cajas de Ordenar por y Luego por se selecciona de la lista desplegable el nombre del campo con el que se quiere ordenar y según la prioridad. MS Excel permite manejar hasta tres prioridades como se puede ver con los criterios. Cada criterio se puede ordenar Ascendente o Descendentemente. Si se manejan encabezados en la selección se determina en La lista tiene fila de encabezamiento. Al presionar el botón Opciones aparece la siguiente caja de diálogo:

Las opciones permiten especificar un orden específico y opciones adicionales para el orden. En primer criterio de ordenación se elige alguna de las opciones que MS Excel tiene creadas. Se pueden crear más criterios, que se revisarán posteriormente. También se puede determinar que sea sensible a las letras mayúsculas en Distinguir mayúsculas/ minúsculas. Según se tengan acomodados los datos se debe seleccionar la Orientación ya sea Ordenar de arriba hacia abajo o de izquierda a derecha. También se pueden ordenar directamente ascendente o descendentemente, con un solo criterio utilizando los siguientes iconos: Ascendentemente Descendentemente Para utilizar la opción de los íconos es necesario colocarse en algún registro del campo que se desea tomar como criterio y presionar el icono

V.2. Buscar y reemplazar Buscar A veces puede ser de gran utilidad buscar un dato de forma automática debido al tamaño tan grande que tienen las hojas de cálculo y a la gran cantidad de datos que puede contener. Para buscar un datos en la hoja de cálculo primero debe abrirse dicha hoja y pulsar en Edición > Buscar, aparecerá una ventana como la siguiente.


28

En la celda buscar se introduce la información que se desea buscar, aquí pueden utilizarse comodines, por ejemplo el signo de interrogación (?) sirve para reemplazar un caracter y el (*) para reemplazar una cadena de caracteres. Por ejemplo, ca?a encuentra "casa", "caja", etc. y *este encuentra "sudeste", noreste", etc. En la parte de abajo de la ventana se puede especificar el orden en que se buscará la información que puede ser con filas y columnas; también se puede especificar en donde se buscará la información, en las fórmulas de las celdas, en los valores que éstas formulas devuelven o en los comentarios. Si activa la casilla Coincidir mayúsculas y minúsculas significa que se buscará exactamente como se haya digitado en la celda de buscar, es decir es, diferente casa que Casa. Para empezar la búsqueda pulsamos en el botón Buscar y cuando se encuentre la primer celda que coincida dicha celda se seccionará y para continuar la búsqueda se presiona el botón Buscar siguiente. Reemplazar Sirve para reemplazar la información, por ejemplo puede hacerse que todas las celdas que tengan el número 5 se remplacen por un 10. Esta operación es similar a buscar con a única diferencia que hay que indicar el nuevo valor, solo se tiene que presionar el botón Reemplazar de la ventana Buscar, y aparecerá una ventana como la siguiente:


29

En la primer celda se introduce el valor que se va a reemplazar y en la segunda celda el nuevo valor. Luego se presiona el botón Reemplazar para reemplazar el primer valor que coincida con la búsqueda o Reemplazar todas para reemplazar todas las coincidencias de la hoja.

V.3. Nombres para un rango de celdas Para simplificar el trabajo a posibles usuarios de su hoja de cálculo, puede asignarse un nombre a los rangos de celda. Para esto se utiliza el llamado Cuadro de nombres. Una vez seleccionado un rango, se desplaza con el cursor hasta el Cuadro de Nombres, y se introduce un nombre para su rango. De esta manera cada vez que se desee introducir datos en este rango bastará escoger en dicho cuadro el nombre asociado al rango de celdas.

V.4. Bases de datos Introducción Cuando se tienen varios datos ordenados y clasificados adecuadamente, se tiene una base de datos. Por lo tanto, una base de datos es un conjunto de datos que pertenecen a una misma entidad, objeto o individuo. Por ejemplo:

Cada renglón es un registro y cada columna un campo. Los datos de la misma fila, que son un registro, pertenecen a la misma persona. Cada columna es un campo, contiene todos los datos necesarios para formar un registro y permite clasificarlos y ordenarlos. Con una base de datos se pueden realizar diferentes operaciones tales como ordenar datos, buscar, reemplazar, filtros etc.


30

V.5. Filtros Una de las operaciones más comunes que se hacen con las bases de datos es la consulta. Cuando se hace una consulta, normalmente se están buscando registros que coincidan con algún valor o criterio. Existen varias formas de buscar esa información, los Filtros son una de ellas. Para aplicar un filtro a una base de datos, seleccione del menú Datos el comando Filtro y del submenú Autofiltro. El resultado será similar al siguiente:

El comando Autofiltro, aplica flechas para abrir menús colgantes en el encabezado de la base de datos. Con estos menús colgantes, se pueden elegir las opciones de consulta.

Según el campo, aparecen las posibles combinaciones de los registros que se pueden buscar. La opción (Todas), va a mostrar todos los registros sin importar ningún criterio. La opción (Vacías) muestra los registros que están vacíos en ese campo. La opción (No vacías) realiza la acción contraria. Al seleccionar (Personalizar) aparece la siguiente caja de diálogo:


31

Con el Autofiltro personalizado se pueden determinar que registros exactamente se están buscando. Se pueden utilizar operadores de comparación mostrados en la siguiente tabla.

> < >= <= <>

mayor que menor que mayor o igual que menor o igual que diferente a

Con el operador se selecciona el registro con el que se está comparando también de la lista. MS Excel XP permite utilizar comodines que son: (?) para representar un carácter individual y (*) para representar cualquier serie de caracteres. Con las opciones Y y O se pueden establecer cierto criterios. Con la opción Y los criterios se suman, es decir que se tienen que cumplir con ambos criterios para que encuentre los registros deseados. Con la opción O va a buscar cualquiera de los registros que cumplan con los criterios establecidos, no tiene que cumplir con ambas. Ya que se estableció un filtro la pantalla se puede ser similar a ésta:

Los botones de los campos donde se establecieron filtros y los encabezados de los renglones cambian de color. En cada uno de los campos se pueden establecer filtros y en este caso se suman los criterios, es decir que se debe de cumplir con cada uno de los criterios que se marquen en los filtros de los campos. Para deshabilitar todos los filtros, seleccione en el menú Datos el comando Filtros, Autofiltro. Filtros avanzados A pesar de que sea muy útil y sencillo el manejo los filtros automáticos, están limitados a sólo uno o dos criterios por cada campo. Por esto, se tienen los Filtros avanzados cuyo manejo es similar al de los filtros automáticos pero con mayores posibilidades de criterios. Los criterios se definen en la misma hoja de cálculo. Los criterios de filtro avanzado pueden incluir vanas condiciones aplicadas a una sola columna, varios criterios aplicados a varias columnas y condiciones creadas como resultado de una fórmula Algunos de los posibles criterios aparecen ejemplificados a continuación: Nombre Federico Raúl Universidad Tecnológica de Izúcar de Matamoros

32

Nombre Federico

Edad >30

Cuando definimos un criterio a través de una fórmula no debemos utilizar el rótulo de la columna. =G18>PROMEDIO(G4:G12)

Para utilizar los filtros avanzados, seleccione del menú Datos el comando Filtros, Filtros avanzados. Al hacerlo aparece la siguiente caja de diálogo:

Lo primero que nos permite esta opción es Filtrar la lista sin moverla a otro lugar como se hace con los filtros automáticos, permaneciendo en el mismo lugar o también Copiar a otro lugar el resultado del filtro. Si se elige la opción Copiar a otro lugar es necesario que en la caja Copiar a se especifique un rango, ya sea escribiendo el rango o con un clic en la hoja donde se desea que comience el rango. En la caja Rango de la lista, aparece la referencia de las celdas que contienen la lista. MS Excel automáticamente detecta el rango de la lista cuando está el cursor colocado en ella, pero con un clic; en la caja se puede escribir un nuevo rango o cambiar el actual. También se puede utilizar el ratón y dar un clic sostenido sobre el área de la hoja y la referencia de la celda se escribe automáticamente en la caja. Una vez establecido el rango de la lista, es necesario determinar el Rango de criterios. Este rango debe ser otra área de celdas donde se determine el criterio, debe ser un área con el mismo número de columnas que la base de datos y por lo menos con una fila que contenga los criterios escritos que se están buscando. Con los criterios se pueden utilizar signos de comparación y comodines como se vio con los filtros automáticos. Universidad Tecnológica de Izúcar de Matamoros

33

La opción Sólo registros únicos muestra un solo registro cuando encuentre registros que están duplicados.

V.6. Tablas dinámicas ¿Qué es? Comprende una serie de Datos agrupados en forma de resumen que agrupan aspectos concretos de una información global. Es decir con una Tabla Dinámica se pueden hacer resúmenes de una Base de Datos, utilizándose para, promediar, o totalizar datos. Debe ser muy importante la cantidad de información a manejar para que el uso de la tabla dinámica se justifique. Para su utilización, se debe recurrir a Menú Datos > Informe de Tablas y gráficos dinámicos Crear una tabla dinámica La creación de una tabla dinámica se realiza a través del asistente y en varios pasos. 1.- Colocar el cursor en cualquier celda de la Tabla

2.- Elegir la opción Menú > Datos > Informe de tablas y gráficos dinámicos, y aparecerá el cuadro: Universidad Tecnológica de Izúcar de Matamoros

34

1er . Paso. a) ¿Dónde están los datos que desea analizar? Marcar opción : Lista o base de datos de Microsoft Excel b) ¿ Que tipo de informe desea crear? Marcar la opción: Tabla dinámica c) clic en Siguiente 2°. Paso. Rango de datos a) Seleccionar el rango de la tabla, incluyendo la fila de título. b) Siguiente

3er . Paso. Ubicación de la tabla


35

a) Se adoptó ubicar la tabla en la misma hoja de cálculo, determinando la celda de comienzo de ejecución de la misma. b) Dar clic en Diseño y se inicia el diseño de la tabla.

a) Arrastrar el cuadrito de Semana a Columna b) Arrastrar el cuadrito de Vehículo a Fila c) Arrastrar el cuadrito de Cantidad a Datos d) Aceptar. Aparece la tabla dinámica diseñada, acompañada por una barra de herramientas especial que permite filtrar datos por despliegue de las mismas. Universidad Tecnológica de Izúcar de Matamoros

36

Aplicar Filtro a la Tabla

Resultado de la tabla al aplicarle los filtros correspondientes: En primer lugar se despliega la lista en semana y se desactiva la 1° y 2°.


37

En segundo lugar se despliega la lista en vehículo y se desactivan las opciones Camión, Camioneta, Moto. Cambiar el diseño de la tabla Siguiendo el mismo procedimiento se puede cambiar el diseño de la tabla, por ejemplo: e) Arrastrar el cuadrito de Vehículo a Columna f) Arrastrar el cuadrito de Semana a Fila g) Arrastrar el cuadrito de Cantidad a Datos h) Aceptar. Obteniéndose el siguiente el siguiente resultado.

Aplicar Filtros a la Tabla Siguiendo con el mismo criterio, aplicando en Vehículos se dejan activados Auto y Camión. En Semana se deja activada solamente la 1° y 3° Semana. De lo ejecutado surge el siguiente resultado.

Obtención de Subtablas Asiendo doble clic sobre una celda , en este caso por ejemplo la segunda de Auto, donde figura la cantidad 122350, Excel automáticamente produce un detalle en hoja aparte según lo siguiente.


38

VI. GRÁFICOS Objetivo. Elaborar gráficos de información contenida en una hoja de cálculo.

VI.1. Crear un gráfico ¿Qué es un gráfico? En Excel, un gráfico es la mane ra de representar visualmente unos datos numéricos. Esa representación visual puede realizarse de muy diversas formas, pero el objetivo consiste siempre en que la persona que ve el gráfico pueda darse cuenta de una sola mirada de la información más importante contenida en los números de los que el gráfico procede. Si comparas una tabla de datos con un gráfico creado a partir de ella, podrás comprobar que esto es cierto: mirar el gráfico es una forma mucho más rápida de conocer la información más relevante contenida en unos datos. Nota: Para trabajar con gráficos necesitamos entender claramente dos conceptos, serie de datos y punto de datos. Una serie de datos es un conjunto de uno o más valores numéricos relacionados entre sí. En el ejemplo siguiente cada apartado de gastos representa una serie de datos, (sueldos, alquiler, publicidad....). A los datos que forman una serie de datos se les llama puntos de datos. Si una serie de datos consiste en seis cifras de gastos para los meses de Enero a Junio, la serie tendrá seis puntos de datos. Crear un gráfico rápidamente Crear un gráfico en Excel es un procedimiento asombrosamente sencillo y rápido. En primer lugar selecciona los datos que quieras representar en el gráfico y luego pulsa la tecla F11. Tras un instante, Excel te muestra una hoja nueva que contiene un gráfico de columnas, el cual representa los datos que has seleccionado en la hoja. A este gráfico automático que crea Excel con sólo pulsar una tecla se le denomina gráfico básico.


39

Las partes de un gráfico Los gráficos están compuestos de diversas partes, las cuales puedes modificar a tu gusto a partir del gráfico básico que crea Excel. Por tanto, para poder manipular gráficos es imprescindible conocer cuáles son esas partes. Las partes de un gráfico son: a) El gráfico en sí es la representación visual de los datos de la hoja de cálculo. Además, hay distintos tipos de gráficos. b) El título del gráfico es un texto que lo encabeza. c) Una marca de datos es el objeto que se utiliza para representar los datos. Por ejemplo, en un gráfico de columnas, las marcas de datos son las columnas; es decir, hay una columna por cada número representado. d) Las líneas de división son unas líneas horizontales y/o verticales que aparecen al fondo del gráfico y que sirven para apreciar con más facilidad los valores que alcanzan las marcas de datos. e) El texto puede ser cualquier palabra o frase que aparezca en el gráfico. El texto asignado está unido a un objeto del gráfico, mientras que el texto no asignado es libre. f) La leyenda es un cuadro en el que se identifican las marcas de datos (habitualmente, mediante colores). Universidad Tecnológica de Izúcar de Matamoros

40

g) Los ejes son unas líneas perpendiculares que marcan la referencia para el gráfico. Normalmente hay dos ejes (en los gráficos tridimensionales hay tres): el eje de abscisas, eje x o eje de categorías, y el eje de ordenadas, eje y o eje de valores. En el eje de abscisas se representan las distintas categorías de los datos. En el eje de ordenadas se representan los valores de los datos. h) Las marcas de graduación aparecen en los ejes y sirven para determinar el valor del eje en cada punto del mismo. i) El área de trazado es la zona del gráfico donde aparecen las marcas de datos y los ejes. j) Las series de datos son los conjuntos de datos que se representan en un gráfico. Cómo seleccionar el rango para crear el gráfico El primer paso para crear un gráfico consiste en seleccionar los datos que se quieren representar. Además, no sólo hay que seleccionar datos numéricos sino también texto, ya que Excel sabe cómo poner ese texto en el gráfico. Excel considera que determinados datos del rango que selecciones van a representar determinados rótulos en el gráfico. De esto puedes deducir que la forma resultante del gráfico dependerá mucho de cómo selecciones los datos. Para que Excel muestre los datos tal y como tú desees, debes seleccionar el rango teniendo en cuenta lo siguiente: a) Las celdas de la columna que esté a la izquierda de los datos que selecciones deberán contener texto. Esas celdas se convertirán en los rótulos del eje de categorías. b) Las celdas de la fila superior del rango que selecciones también deberán contener texto. Esas celdas se convertirán en los rótulos de las series de datos en la leyenda del gráfico. c) El resto de la selección deberán ser los datos numéricos, NOTA: Debes tener en cuenta que no es necesario que el rango seleccionado sea contiguo. Puedes seleccionar rangos discontinuos, tal y como verás en el siguiente apartado. Crear un segundo gráfico Puedes crear tantos gráficos como quieras a partir de una sola hoja de cálculo. Para ello sólo tienes que seleccionar los nuevos datos (o los mismos que antes) que quieras representar y volver a pulsar F 11. Excel colocará cada gráfico que crees en una hoja de gráficos independiente.


41

NOTA: Evidentemente, si no te gusta algún gráfico puedes eliminarlo: selecciona la hoja en la que esté (haciendo clic en su pestaña) y ejecuta el comando Edición, Eliminar hoja; en cuanto confirmes el borrado, Excel eliminará la hoja que contiene el gráfico. La relación entre los datos y el gráfico Cuando se crea un gráfico, Excel vincula automáticamente los datos de la hoja de cálculo y el gráfico que se ha creado. Esto significa que si cambias los datos en la hoja de cálculo (ya sean valores o rótulos), esos cambios se reflejarán automáticamente en el gráfico: este también cambiará. Incrustar un gráfico Hasta ahora hemos trabajado con gráficos que aparecían en hojas de cálculo independientes. Pero puede ocurrir que en determinadas ocasiones quieras que el gráfico aparezca en la misma hoja de cálculo en la que estén los datos que lo originaron. Excel permite que los gráficos aparezcan no solo en las hojas de gráficos, sino también en las hojas de cálculo, para lo cual es necesario incrustarlos. Por tanto, Incrustar un gráfico no es más que crear un gráfico en la propia hoja de cálculo, en lugar de hacerlo en una hoja de gráficos independiente. Para crear un gráfico incrustado debes pasar a la hoja de cálculo que contenga los datos que quieres representar en el gráfico y, a continuación seguir estos pasos: Selecciona el rango de datos de la misma manera que has hecho hasta ahora. Ejecuta el comando Insertar > Gráfico. Entonces aparecerá el cuadro de diálogo Asistente para gráficos - paso 1 de 4. Haz clic en Terminar para dar por buenas todas las opciones por omisión que ofrece el Asistente. El gráfico aparecerá incrustado en la hoja de cálculo . Cambiar el tamaño y la posición de un gráfico incrustado Es probable que cuando crees un gráfico incrustado, éste no aparezca ni con el tamaño ni en la posición idónea dentro de la hoja. Para cambiar el tamaño de un gráfico incrustado, en primer lugar debes seleccionarlo haciendo clic sobre él. Cuando el gráfico esté seleccionado, aparecerán los cuadros de selección a su alrededor. Apunta con el ratón a uno de los cuadros de selección, haz clic manteniendo pulsado el botón de] ratón y arrástralo para cambiar el tamaño del gráfico. Cuando estés satisfecho con el tamaño del gráfico, suelta el botón del ratón. Cambiar la posición del gráfico en la hoja de cálculo sigue un procedimiento idéntico, sólo que, en lugar de arrastrar uno de los cuadros de selección del gráfico incrustado, debes arrastrar el propio gráfico. Para ello apunta al interior del gráfico, haz clic y arrastra el gráfico a su nueva posición. Por último suelta el botón del ratón. Universidad Tecnológica de Izúcar de Matamoros

42

Imprimir el gráfico Imprimir un gráfico es similar a imprimir una hoja de cálculo. En primer lugar es conveniente que realices una presentación preliminar del gráfico para ver el aspecto que tendrá una vez impreso. En la pantalla de la Presentación preliminar puedes hacer clic en el botón Configurar para pasar al cuadro de diálogo Preparar página. Ajusta la página y selecciona las opciones de impresión que desees y luego haz clic en Aceptar. Cuando estés de nuevo en la pantalla de la Presentación preliminar, puedes imprimir el gráfico como cualquier hoja de cálculo: haz clic en el botón Imprimir y luego en el botón Aceptar del cuadro de diálogo Imprimir.

VI.2. Tipos de gráficos Tipos y subtipos de gráficos Hasta ahora sólo hemos utilizado un tipo de gráfico: el gráfico de columnas. Sin embargo, Excel dispone de muchos más tipos de gráficos. Cada tipo de gráfico se diferencia de los demás por la clase de marcas de datos que utiliza. Por ejemplo, el gráfico de columnas utiliza columnas como marcas de datos; el de líneas, utiliza líneas; etc. El motivo de que haya tantos tipos de gráficos diferentes en Excel no es solamente estético. Cada uno de los tipos de gráficos está especialmente indicado para representar los datos de una manera distinta. Si quieres obtener la máxima eficacia al crear tus gráficos debes tener esto muy en cuenta. Además de todos los tipos de gráficos, Excel dispone de varios subtipos para cada tipo. Un subtipo de gráfico es una variación del tipo. Cada subtipo puede ser muy diferente de los demás (o muy parecido a otros), pero todos los subtipos de un tipo siempre mantendrán en común el tipo de marca de datos que se utilice. Por ejemplo, el tipo de gráfico de áreas tiene seis subtipos distintos. Algunos son casi iguales y otros son completamente distintos, pero todos utilizan áreas para representa r los datos. Cambiar el tipo y el subtipo de un gráfico Para cambiar el tipo y el subtipo de un gráfico primero debes seleccionar el gráfico que quieres modificar haciendo clic en la pestaña de su hoja. Luego ejecuta el comando Gráfico, Tipo de gráfico. Excel te mostrará el cuadro de diálogo Tipo de gráfico. A la izquierda de este cuadro de diálogo puedes ver la lista Tipo de gráfico. En esa lista puedes seleccionar el tipo de gráfico que quieras usar. En la parte derecha del cuadro de diálogo hay una cuadrícula denominada Subtipo de gráfico. Cada una de las casillas de la cuadrícula contiene un subtipo del tipo seleccionado (también puede haber algunas casillas en blanco). Universidad Tecnológica de Izúcar de Matamoros

43

Para cambiar el tipo y el subtipo del gráfico sólo tienes que seleccionar las opciones que prefieras en este cuadro de diálogo. Cuando lo hayas hecho, haz clic en Aceptar.

VI.3. Modificar gráficos Añadir texto al gráfico Excel permite añadir al gráfico dos tipos de texto: texto asignado y texto no asignado. La diferencia entre ambos consiste en que el texto asignado está unido siempre a un objeto del gráfico, mientras que el texto no asignado es completamente libre. Texto asignado Todo el texto asignado de un gráfico se puede introducir a través de las distintas pestañas del cuadro de diálogo Opciones de gráfico que aparece cuando se ejecuta el comando Gráfico, Opciones de gráfico de una hoja que contenga un gráfico. El texto asignado que puedes añadir y la manera de añadirlo es la siguiente:


44

* Los títulos del gráfico, del eje x y del eje y. Selecciona la pestaña Títulos del cuadro de diálogo Opciones de gráfico. Introduce los títulos en sus respectivos recuadros y, por último, haz clic en Aceptar. Si quieres cambiar el texto de un título ya introducido, debes seleccionar el título (haciendo clic sobre él). Luego escribe el nuevo texto y pulsa Intro.

* Los rótulos de las marcas de datos. Este texto aparece junto a las marcas de datos y sirve bien para identificarlas, bien para señalar su valor exacto. Para añadir este texto al gráfico, selecciona la pestaña Rótulos de datos del cuadro de diálogo Opciones de gráfico. Escoge la opción Mostrar valor para que aparezca el valor exacto de cada Universidad Tecnológica de Izúcar de Matamoros

45

marca de datos, o Mostrar rótulos para que aparezca el nombre de la serie de datos junto a cada marca. Por último haz clic en Aceptar.

* Los rótulos de los ejes x e y. Este texto aparece a lo largo de los ejes y sirve para señalizar las categorías (si es el eje x) o los valores (si es el eje y). Para añadir este texto al gráfico (que ya aparece por omisión en los gráficos estándar de Excel), selecciona la pestaña Ejes del cuadro de diálogo Opciones de gráfico. Selecciona los ejes en los que quieres que aparezcan los rótulos y haz clic en Aceptar.

* La leyenda. La leyenda es un recuadro que aparece en el gráfico y que contiene una indicación del significado de las marcas de datos. Si quieres que aparezca la leyenda Universidad Tecnológica de Izúcar de Matamoros 46

en el gráfico (cosa que ocurre por omisión) selecciona la pestaña Leyenda del cuadro de diálogo Opciones de gráfico y escoge su posición en la pantalla después de activar la opción Mostrar leyenda. Si quieres hacerla desaparecer, selecciónala y pulsa la tecla SUPR.

NOTA: El procedimiento para hacer desaparecer el texto asignado es el mismo que el utilizado para añadirlo, pero deseleccionando las opciones en lugar de seleccionarlas o borrando el contenido de los cuadros que contengan texto (como el caso de los títulos). Texto no asignado Habrá ocasiones en las que querrás dirigir la atención del lector hacia un aspecto concreto del gráfico. La mejor forma de hacerlo consiste en añadir al gráfico texto no asignado. Cuando vayas a añadir texto no asignado pulsa ESC para deseleccionar cualquier objeto del gráfico que tengas seleccionado. Ahora comienza a escribir e introduce toda la frase que quieras que aparezca como texto no asignado. Cuando termines de escribir pulsa Intro. Excel insertará un recuadro de texto no asignado en el centro del gráfico. Ahora puedes mover y cambiar el tamaño del texto, Para moverlo, apunta al borde del cuadro con el puntero del ratón, pulsa y mantén pulsado el botón izquierdo del ratón y arrástralo a su nueva posición. Para cambiar el tamaño o la forma del texto no asignado, arrastra con el ratón cualquiera de los cuadros de selección. Editar el texto Si te equivocas o si cambias de idea sobre el texto que tengas en el gráfico, es sumamente sencillo editar ese texto. El procedimiento para modificar el texto de un gráfico es el mismo, tanto si el texto es asignado como si no: Universidad Tecnológica de Izúcar de Matamoros

47

Selecciona el objeto de texto. Por ejemplo, si deseas cambiar el título del gráfico, selecciona el título (haciendo clic sobre él), y si deseas cambiar un texto no asignado, selecciona ese texto no asignado. Sitúa el puntero del ratón dentro del objeto de texto. Observa que se transforma en un punto de inserción. Apunta cuidadosamente y haz clic para situar el punto de inserción en el lugar que desees. Modifica el texto como si fuera el de cualquier celda de la hoja de cálculo. Pulsa ESC para introducir el texto modificado. Se debe pulsar ESC (y no Intro) para introducir un texto que se haya editado. Si después de editar el texto prefieres dejarlo como estaba, pulsa Esc para introducir el texto modificado y utiliza luego el comando Edición > Deshacer (o pulsa Ctrl+Z) para deshacer la operación. Dar formato al texto Una vez introducido un texto en un gráfico, puedes darle formato para modificar su estética o su importancia en el conjunto del gráfico. En primer lugar debes seleccionar el objeto de texto haciendo clic sobre él. Cuando aparezcan los cuadros de selección que indican que el objeto está seleccionado, utiliza la barra de herramientas Formato de la misma manera que si estuvieras dando formato a una celda cualquiera de la hoja de cálculo. Cuando estés satisfecho con el resultado, pulsa ESC para deseleccionar el objeto. Añadir bordes y sombreado al texto En Excel es posible poner un borde alrededor de cualquier objeto de texto (como si fuera una celda normal de la hoja de cálculo) e, incluso, modificar su formato o añadirle un sombreado. Para cambiar el borde de un objeto de texto debes seleccionarlo primero. Luego ejecuta el comando Formato, Objeto seleccionado (donde objeto es el objeto de texto que tengas seleccionado) y haz clic en la pestaña Tramas del cuadro de diálogo Formato del objeto que aparece. Ahora puedes elegir entre seleccionar un borde Automático (es decir, el que Excel añade por omisión), seleccionar la opción Ninguno para que el objeto no tenga borde, o seleccionar la opción Personalizado y determinar exactamente el borde que desees. También puedes lograr que el objeto aparezca sombreado seleccionando la opción Sombreado.


48

VII. MACROS Objetivo. Implementar una macro en un libro de Excel.

VII.1. Introducción Una Macro son una serie de acciones que se almacenan y se pueden activar posteriormente en otra hoja o en otro libro. Por ejemplo, todos los días puede suceder que se emplee frecuentemente en una hoja de calculo los mismos pasos: Tamaño de Fuente(Tamaño de la letra), Negrita, Fuente(Tipo de letra) y Color de Fuente(Color de Letra), para no estar repitiendo estos pasos pueden almacenarse en una macro y cuando se ejecute dicha macro los pasos antes mencionados se ejecutaran las veces se desee.

VII.1. Como crear una macro Para crear una macro solo es necesario pulsar sobre el menú Herramientas > Macro > Grabar nueva macro y a continuación aparecerá una ventana como la siguiente:

En la cual se especifica el nombre que llevará la macro, el Método abreviado (teclas con las que se ejecutara automáticamente dicha macro, una vez grabada), y el lugar donde se guardará. Hecho esto, dar clic en Aceptar y entonces empezara a grabar “todas” las acciones que ejecutemos a continuación, hasta dar clic en Detener grabación en la pequeña ventana emergente que aparece. Proseguir entonces a realizar todas las acciones que se hacen repetidamente, mismas que ejecutará la macro en forma automática. Terminando con un clic en Detener grabación. Ya hasta este punto, tendremos ya grabada y guardada nuestra macro. Universidad Tecnológica de Izúcar de Matamoros

49

Ejecutando una macro Para ejecutar una macro hacer seleccionar, y a continuación aparecerá una ventana con las macros existentes en el libro (o libros que tengamos abiertos), acto seguido se debe seleccionar la macro deseada y hacer clic en Ejecutar y entonces dicha macro se ejecutará en la hoja de calculo sobre la cual estemos del libro de Excel.

VII.3. Modificar una macro Para modificar una macro se debe seleccionar Herramientas > Macro > Macros seguido de seleccionar la macro donde se desea realizar cambios y a continuación clic en Modificar . Esta acción abrirá la aplicación Visual Basic, incluida en MS Office, junto con el código en dicho leng uaje de programación referente a la macro en cuestión. Para realizar los cambios a la macro es necesario conocer un poco de programación en este lenguaje de programación.


50

Apéndice A Ejercicios 1. Con la siguiente hoja de cálculo, realizar lo siguiente: a. Encontrar promedio por alumno: =PROMEDIO(E2:G2) b. Encontrar promedio por materia: =PROMEDIO(E2:E11) c. Promedio general del grupo: =PROMEDIO(H2:H11) d. Encontrar descuento de acuerdo a lo siguiente Si el promedio va de 85 a 89 25% de descuento Si el promedio va de 90 a 94 50% de descuento Si el promedio va de 95 a 100 75% de descuento =SI(Y(H2>=85,H2<=89),0.25,SI(Y(H2>=90,H2<=94),0.5,SI(Y(H2>=95,H2<=100),0.75,0)))

NO. DE LISTA

No. CONTROL

1

02292003

ALVA CAZARES

ARELYN

2

02292005

ANGUILU GARCÍA

3

02292011

BARRANCO ARENAS

4

02292015

5 6 7

APELLIDOS

NOMBRE

MATEMÁTICAS II

INFORMÁTICA II PROGRAMACIÓN

76.66666667

96

70

JAEL AIME

74

81.33333333

70

ANA DELIA

78.33333333

82.66666667

70

BRAVO ESPÍNOSA

YANÍN

81.33333333

91.33333333

70

02292018

CARDOSO ESCUDERO

GUADALUPE REY

70

95

90.33333333

02292019

CARREÓN LÓPEZ

OBETH

76

91.66666667

71.66666667

02292023

CONDE REYES

FLOR CRISTELA

70

88.33333333

70


PROMEDIO GENERAL

DESCUENTO

51

8

02292026

CORONA LÓPEZ

JUAN CARLOS

100

100

90.33333333

9

02292028

CRUZ GONZÁLEZ

JUAN CARLOS

80.33333333

92.66666667

100

10

02292030

DÍAZ CAMPOS

ADRIANA RUFINA

73.66666667

91.66666667

70

2. Con la siguiente hoja de cálculo, realizar lo siguiente: a. Dar formato a los números b. Poner en rojo aquellos datos menores a 70 (Formato/Formato Condicional) c. Poner “NA” en promedio si tan solo tiene un dato menor a 70 =SI(O(E2<70,F2<70,G2<70),"NA",PROMEDIO(E2:G2)) d. Elaborar gráfico de cada calificación y promedio NO. DE LISTA

No. CONTROL

1

02292003

ALVA CAZARES

ARELYN

2

02292005

ANGUILU GARCÍA

JAEL AIME

3

02292011

BARRANCO ARENAS

4

02292015

5

02292018

6

APELLIDOS

NOMBRE

MATEMÁTICAS II

INFORMÁTICA II

PROGRAMACIÓN

76.66666667

96

20

74

81.33333333

70

ANA DELIA

78.33333333

82.66666667

70

BRAVO ESPÍNOSA

YANÍN

81.33333333

91.33333333

70

CARDOSO ESCUDERO

GUADALUPE REY

50

95

90.33333333

02292019

CARREÓN LÓPEZ

OBETH

76

91.66666667

71.66666667

7

02292023

CONDE REYES

FLOR CRISTELA

70

69

70

8

02292026

CORONA LÓPEZ

JUAN CARLOS

91.66666667

100

90.33333333

9

02292028

CRUZ GONZÁLEZ

JUAN CARLOS

80.33333333

92.66666667

45

10

02292030

DÍAZ CAMPOS

ADRIANA RUFINA

73.66666667

91.66666667

70

MENSAJE

PROMEDIO GENERAL

3. Con la siguiente hoja de cálculo, realizar lo siguiente: a. Encontrar los días vividos hasta el día de hoy de cada alumno =HOY()-E2 b. Separar en otra columna día =SI(DIA(E2)<10,CONCATENAR("0",DIA(E2)),DIA(E2)) c. Separar en otra columna mes =SI(MES(E2)<10,CONCATENAR("0",MES(E2)),MES(E2)) d. Separar en otra columna año =SI(AÑO(E2)<10,CONCATENAR("0",AÑO(E2)),AÑO(E2)) Universidad Tecnológica de Izúcar de Matamoros

52

Nota: No olvides dar formato al rango que contendrá los días vividos a número. También el día y mes tendrá que mostrarse en dos dígitos. No.

ap1

ap2

nom

fecha_nac

1

BALBUENA

ORTA

ANSONIT LEVI

10/07/1983

2

CABRERA

RAMÍREZ

ARMANDO

27/08/1977

3

CALIXTO

MARCOS

HELEODORO

25/02/1982

4

CAMPANO

GONZÁLEZ

FEDERICO

26/02/1982

5

CAPILLA

PÉREZ

JOSÉ ANGEL

15/09/1976

6

COHETZALTITLA COHETZALTITLA MARCIAL

días vividos

día

mes

año

28/02/1982

4. Completar la siguiente tabla. No olvide que Excel no maneja grados sino radianes. =RADIANES(A2), =SENO(B2), =COS(B2) Grados

Radianes

Seno

Coseno

0 1 2 3 …. …. …. 359 360

5. Completar la siguiente tabla encontrando el área del: Rectángulo: =B3*C3 Triángulo: =B4*C4/2 Círculo: =PI()*POTENCIA(B6,2) Universidad Tecnológica de Izúcar de Matamoros

53

Cálculo de áreas Rectángulo Triángulo Círculo

Base 5 8 Radio 6 6

Altura 6 9

Área

Área

6. De la siguiente hoja de cálculo, encontrar la solución para las ecuaciones de segundo grado con una incógnita y con los valores dados. Solución 1: =((-B4-RAIZ(POTENCIA(B4,2)-4*A4*C4))/(2*A4)) Solución 2: =((-B4+RAIZ(POTENCIA(B4,2)-4*A4*C4))/(2*A4))

Solución a ecuaciones de segundo grado con una incógnita

ax2 + bx + c = 0 Valor cuadrático 3 1 3 4 -2

Valor lineal 8 -5 7 3 -2

Término independiente 4 2 0 -4 3

Solución 1

Solución 2

7. Con la hoja de cálculo anterior, para validar cuando la raíz es negativa =SI(Y((POTENCIA(B4,2)-4*A4*C4)>=0,A4<>0),((-B4-RAIZ(POTENCIA(B4,2)-4*A4*C4))/(2*A4)),"Datos no válidos")

=SI(Y((POTENCIA(B4,2)-4*A4*C4)>=0,A4<>0),((-B4+RAIZ(POTENCIA(B4,2)-4*A4*C4))/(2*A4)),"Datos no válidos") Universidad Tecnológica de Izúcar de Matamoros

54

8. Con la siguiente hoja de cálculo, calcular la velocidad para cada tiempo t y graficar la velocidad. Para velocidad: =A$6*B6, y para hacer la gráfica elegir un gráfico de Dispersión XY.

Velocidad en un tiempo t para una aceleración constante Aceleración (m/seg 2)

Tiempo (seg.)

2

0.00 0.50 1.00 … … .. 14.50 15.00

Velocidad (m/seg.)

v=a*t

9. Con la siguiente hoja de cálculo, encontrar la fuerza resultante del sistema de fuerzas expresado en dicho archivo. Recordar que para encontrar la componente X es coseno y para la componente Y es seno. Para componente X: =COS(RADIANES(B4))*A4 Para componente Y: =SENO(RADIANES(B4))*A4 Para sumatoria X: =SUMA(C4:C8) Para sumatoria Y: =SUMA(D4:D8) Para Fuerza Resultante: =RAIZ(POTENCIA(C9,2)+POTENCIA(D9,2)) =RAIZ(SUMA.CUADRADOS(celda de sumatoria x, celda de sumatoria y)) Para ángulo resultante: =GRADOS(ATAN(D9/C9))


55

Sistema de fuerzas en el plano X Y Fuerza (N) Ángulo (grados) 400.0 5.0 200.0 90.0 350.0 185.0 220.0 250.0 100.0 295.0 Suma de Fuerzas para X y Y

Componente X

Componente Y

Fuerza Resultante Ángulo de la Fuerza Resultante 10. Con la siguiente hoja de cálculo, convertir números arábigos a romanos. =numero.romano(a3) Arábigo

Romano

5 12 256 300 452 196 987 368 125 4326 1256 Universidad Tecnológica de Izúcar de Matamoros

56

11. Con la siguiente hoja de cálculo, encontrar el promedio para cada alumno, no olvide redondear el promedio. Para encontrar el promedio con redondeo: =REDONDEAR(PROMEDIO(E10:K10),0) Para encontrar el promedio con solo la parte entera:


ARELYN JAEL AIME ANA DELIA YANÍN GUADALUPE REY OBETH FLOR CRISTELA JUAN CARLOS JUAN CARLOS

88 83

78 87

75 79

78 81

83 91

70 70

83 84

93 95

79 81

75 83

70 76

95 92

90 72

84 82

98 85

83 100 81 85

70 92 80

88 100 93

70 90 73

74 83 80 100 87 100 99 100 84 90 78 92

OBSERVACIONES

79 83

PROMEDIO GENERAL

IDIOMA EXTRANJERO P/ING I

70 70

FORMACIÓN SOCIOCULTURAL II EXPRESIÓN ORAL Y ESCRITA II

97 81

INFORMÁTICA II

76 74

MATEMÁTICAS II

REDES DE ÁREA LOCAL

CONDE REYES 7 02292023 8 02292026 CORONA LÓPEZ 9 02292028 CRUZ GONZÁLEZ

PROGRAMACIÓN

1 02292003 ALVA CAZARES 2 02292005 ANGUILU GARCÍA BARRANCO 3 02292011 ARENAS 4 02292015 BRAVO ESPÍNOSA CARDOSO 5 02292018 ESCUDERO 6 02292019 CARREÓN LÓPEZ

NOMBRE

APELLIDOS

No. CONTROL

NO. DE LISTA

=(TRUNCAR(E10)+TRUNCAR(F10)+TRUNCAR(G10)+TRUNCAR(H10)+TRUNCAR(I10)+TRUNCAR(J10)+TRUNCAR(K10))/7

57

12. Con la hoja de cálculo anterior, encontrar el promedio para cada alumno, si algún dato es menor a 70 el promedio no se debe calcular y deberá poner “NA”. No olvide redondear el promedio. Solución: =SI(O(E10<70,E10<70,G10<70,H10<70,I10<70,J10<70,K10<70),"NA",REDONDEAR(PROMEDIO(E10:K10),0)) 13. Con la siguiente hoja de cálculo, determinar el número de hombres y mujeres. Hombres: =SI(E2="H",1,0), =CONTAR.SI(E2:E166,"H") Mujeres: =SI(F2=1,0,1), =CONTAR.SI(E2:E166,"M") No.

ap1

ap2

nombre

sexo

1

SILVA

OMAÑA

JESÚS RAYMUNDO

H

2

TAPIA

AGUILAR

JOSÉ JUAN

H

3

VEGA

POBLANO

HUMBERTA

M

4

ZÁRATE

MARTÍNEZ

JOSÉ CLAUDIO

H

5

BERMEJO

GARCÍA

ELIA

M

6

CASARRUBIAS

HERNÁNDEZ

JUANA GRISEL

M

7

CEDILLO

OLIVEROS

CONCEPCION

M

8

DOMÍNGUEZ

GARCÍA

MARTÍN FRANCISCO

H

9

ESPINOSA

CAMPOS

SELITA

M

10

FLORES

ROSAS

JONI

H

14. Con la siguiente hoja de cálculo, encontrar la media, mediana y moda. Media: =PROMEDIO(A4:G28) Mediana: =MEDIANA(A4:G28) Moda: =MODA(A4:G28)


58

88 82 84 82 91 98 86 91 76 83 80 77

CALIFICACIONES DE ALUMNOS 93 97 92 100 94 91 92 86 93 90 90 88 88 90 96 90 93 86 85 90 96 97 93 85 96 99 95 88 87 89 92 90 89 92 90 98 92 89 95 87 89 89 82 95 77 84 86 74 86 77 87 89 85 96 82 84 84 87 100 80

MEDIA

MEDIANA

MODA

92 76 81 76 88 100 83 82 71 71 81 78

15. Con la siguiente hoja de cálculo, graficar la función: y = raiz(25 - x2). No olvide que una raíz tiene dos valores, un positivo y un negativo, además el valor mínimo para x es –5 y el máximo 5, definiéndose de esa forma el rango. Utilice gráfica de dispersión. =RAIZ(25-POTENCIA(A4,2)) =-RAIZ(25-POTENCIA(A4,2))

y = raiz(25 - x2) x

y1

y2


59

16. Con la siguiente hoja de cálculo, graficar la función: y = 3x2 + 9x –12 Observe que es una ecuación de segundo grado con una incógnita, por lo tanto al graficar dicha ecuación se encontrará analíticamente sus raíces, siendo estas, los valores de x cuando y es cero. El rango recomendado es de –10 a 10, utilice una gráfica de dispersión. =3*POTENCIA(A3,2)+9*A3-12

y = 3x2 + 9x -12 x -5 -4 -3 -2 -1 0 1 2 3 4 5

y

17. Un negocio de equipo de cómputo desea saber a que proveedor va a comprar, de acuerdo al mejor precio. Elabore un cuadro comparativo para saber mejor precio y proveedor al que se comprará. Utilice la siguiente hoja de cálculo. Mejor Precio: =MIN(B5:D5) Proveedor: =SI(E5=B5,B$4,SI(E5=C5,C$4,D$4)) Nota: Recuerde utilizar una referencia absoluta para el caso de los proveedores. Universidad Tecnológica de Izúcar de Matamoros

60

Para validar cuando ningún proveedor cotiza.

=SI(Y(B6=0,C6=0,D6=0),"NO HAY PROVEEDOR",SI(E6=B6,B$4,SI(E6=C6,C$4,D$4))) o =SI(Y(B6>0,C6>0,D6>0), SI(E6=B6,B$4,SI(E6=C6,C$4,D$4)), "NO HAY PROVEEDOR",)


61

Cuadro Comparativo con tres proveedores Artículo

Proveedor1

$ 320.00 $ Mouse 40.00 $ Teclados 90.00 $ Monitor 1,320.00 $ MousePad 17.90 $ Impresora 600.00 $ Scanner 1,145.00

Proveedor2 $ 318.00 $ 28.00 $ 110.00 $ 1,280.00 $ 18.00 $ 590.00 $ 1,150.00

CD

Proveedor3

Mejor Precio

Proveedor

$ 325.00 $ 35.00 $ 95.00 $ 1,275.00 $ 18.00 $ 595.00 $ 1,170.00

18. Con la siguiente hoja de cálculo , extraer los años de nacimiento de cada registro, unir Apellidos y Nombre. Poner los resultados en la hoja Resumen del libro. Nombre Completo: =CONCATENAR(Datos!B4," ",Datos!C4," ",Datos!D4) Año de Nacimiento: =EXTRAE(Datos!E4,5,2) No. 1 2 3 4

Ap

Am

Nombre

CURP

BALBUENA

ORTA

ANSONIT LEVI

BAOA830710HPLLRN04

CABRERA

RAMÍREZ

ARMANDO

CARA770827HPLBMR05

CALIXTO

MARCOS

HELEODORO

CAMH820225HPLLRL08

CAMPANO

GONZÁLEZ

FEDERICO

CAGF820226HMSMND04


62

5 6 7 8 9 10

CAPILLA

PÉREZ

JOSÉ ANGEL

CAPA760915HPLPRN03

COHETZALTITLA

COHETZALTITLA

MARCIAL

COCM820228HPLHHR04

DOMÍNGUEZ

FLORES

JOAQUIN

DOFJ800107HNEMLQ04

HERNÁNDEZ

CORTÉS

JORGE

HECJ830423HPLRRR07

HERNÁNDEZ

MIRANDA

NICOLAS

HEMN810909HPLRRC02

HERNÁNDEZ

VENANCIO

YAHIR

HEVY820306HPLRNH07

19. Construir una macro que permita poner color de fondo, borde y color de texto a la celda activa. Sub Macro1() ActiveCell.Interior.ColorIndex = 6

ActiveCell.Borders.ColorIndex = 4 ActiveCell.Font.ColorIndex = 3 End Sub

20. Construir una macro que permita poner color de fondo, borde y color de texto a un rango. Sub Rango() Range("a1:g10").Interior.ColorIndex = 6 Range("a1:g10").Borders.ColorIndex = 4 Range("a1:g10").Font.ColorIndex = 3 End Sub

21. Construir una macro que asigne un valor a la celda activa Sub Asigna() ActiveCell.Value = 20 End Sub Universidad Tecnológica de Izúcar de Matamoros

63

22. Construir una macro que tome el valor de una celda, lo eleve al cuadrado y lo asigne en otra celda. Sub Asigna() Dim x As Double Dim y As Double x = ActiveCell.Value y=x*x Range("b1").Value = y End Sub Nota: Utilizar el valor asignado en la celda A1 por comodidad, toda vez que el valor al cuadrado es asignado a la celda B1

23. Construir una macro para introducir un dato en la celda activa con InputBox. Sub EntrarDato() ActiveCell.Value = InputBox("Introducir Dato: ", "Captura Datos") End Sub

24. Construir una macro para encontrar porcentaje de descuento y total a pagar de un precio dado. La condición es que se el precio es mayor igual a 1000 el descuento es 10%, si es menor no tiene descuento. Sub Macro1() Range("a2").Value = 0 Range("b2").Value = 0 Range("c2").Value = 0 Range("a2").Value = Val(InputBox("Precio:", "Entrar Precio")) If (Range("a2").Value >= 1000) Then Range("b2").Value = 0.1 Universidad Tecnológica de Izúcar de Matamoros

64

Range("c2").Value = Range("a2") - Range("a2").Value * Range("b2").Value Else Range("b2").Value = 0 End If End Sub

25. Asignar a un botón una macro Private Sub CommandButton1_Click() ActiveCell.Value = "Hola" End Sub

26. Construir un traductor sencillo. Utilice la Hoja2 para tener la definición de datos, en la Hoja1 deberá tener un botón que llame a la macro, y desde un InputBox leer la palabra a traducir, dicha traducción deberá aparecer en la celda A1 de la Hoja1. Sub Traductor() Dim texto1 As String Dim texto2 As String texto1 = UCase(Trim(InputBox("Palabra:"))) Worksheets("Hoja2").Select 'Puede utilizar Activate Range("a1").Select 'Puede utilizar Activate Do While Not IsEmpty(ActiveCell.Value) '<> "" If texto1 = Trim(ActiveCell.Value) Then texto2 = ActiveCell.Offset(0, 1).Value Universidad Tecnológica de Izúcar de Matamoros

65

Exit Do End If ActiveCell.Offset(1, 0).Select Loop Worksheets("Hoja1").Select 'Puede utilizar Activate Range("a1").Value = texto2 End Sub NOTA: Si se utiliza un botón para llamar a la macro, el código del botón deberá como código en la hoja1, y el código del Procedimiento Traductor en un módulo.

Private Sub CommandButton1_Click() Call Traductor End Sub 27. Construir una macro que permita capturar la información de la siguiente encuesta. Los datos deberán ser capturados utilizando InputBox y tendrá que permitir la captura de manera indefinida hasta que se pulse la tecla ESC.

1

Sexo

2

¿Qué carrera estudia?


1. Hombre

2. 1. 2. 3. 4. 5.

Mujer Informática Administración Tecnología de Alimentos Agro biotecnología Informática (Área Electrónica)

66

1. Muy satisfecho Indique el grado de satisfacción 2. Algo satisfecho con la calidad de la educación 3. Algo insatisfecho superior que realiza 4. Muy insatisfecho actualmente. 5. Ns/Nc

3

Private Sub CommandButton1_Click() Call Capturar End Sub NOTA: Recuerde que el código del botón va como código en Hoja1 y el código de Capturar va en Módulo Sub Capturar() Dim sexo As Integer Dim carrera As Integer Dim calidad As Integer Dim i As Integer i=1 Range("a4").Select

Do ActiveCell.Value = i ActiveCell.Offset(0, 1).Select ActiveCell.Value = Val(Trim(InputBox("1: Hombre 2: Mujer", "Sexo"))) ActiveCell.Offset(0, 1).Select ActiveCell.Value = Val(Trim(InputBox("1:Info 2:Mujer 3:Admon 4:TecAlim 5:Agro 6:InfoElec", "Carrera")))

ActiveCell.Offset(0, 1).Select ActiveCell.Value = Val(Trim(InputBox("1:MuySatis 2:AlgoSatis 3:AlgoInsatis 4:MuyInsatis 5:Ns/Nc", "Satisfacción"))) i=i+1 Universidad Tecnológica de Izúcar de Matamoros

67

ActiveCell.Offset(1, -3).Select Loop While (MsgBox("¿Desea continuar?", vbYesNo) = vbYes)

End Sub

28. Construir una macro que permita sumar números desde 1 hasta n, según se indique. Utilice un InputBox para leer el dato hasta donde se desea la sumatoria. Private Sub CommandButton1_Click() Dim num As Integer Dim contador As Integer Dim suma As Double suma = 0 num = Val(InputBox("Hasta donde quieres sumar?", "Captura de dato")) For contador = 1 To num suma = suma + contador Range("D1").Value = suma Next

End Sub 29. Con los datos que se muestran en la siguiente hoja de cálculo, graficar los precios de la papaya maradol desde 1998 y hasta 2003. Universidad Tecnológica de Izúcar de Matamoros

68

Nota: Los precios no se pueden graficar ya que algunos datos contienen espacios en blanco al inicio o final, por lo tanto son texto y no números. Construir una macro para eliminar dichos espacios en blanco.

Sub QuitaBlancos() Dim fila As Integer Dim col As Integer Range("B3").Select fila = ActiveCell.Row 'Número de fila col = ActiveCell.Column 'Número de columna Do While Not IsEmpty(ActiveCell) Do While Not IsEmpty(ActiveCell) ActiveCell.Value = Val(Trim(ActiveCell.Value)) ActiveCell.Offset(1, 0).Select Loop col = col + 1 'Incrementa en una la columna ActiveSheet.Cells(fila, col).Select 'Activa C3,D4,etc. Loop End Sub Nota: El siguiente código es el que corresponde al botón que llama a la macro, ubicarlo en Hoja1

Private Sub CommandButton1_Click() Call QuitaBlancos End Sub


69

Movimiento de Precios de la Papaya Maradol desde 1998 hasta 2003 1998 1999 2000 2001 4.50 4.80 4.00 4.00 8.00 4.80 4.32 4.12 7.50 4.80 4.76 4.20 7.30 4.80 4.80 4.74 7.00 4.30 4.74 5.80 4.80 6.50 6.50 4.20 4.50 6.16 5.50 4.80 4.44 5.64 5.00 4.80 4.20 4.84 4.50 5.00 4.32 4.38 4.50 5.20

Fecha 2-1 9-1 16-1 23-1 30-1 6-2 13-2 20-2 27-2 6-3

2002 4.80 5.80 5.50 5.50 5.80 5.50 5.20 5.20 5.20 5.20

2003 6.00 6.00 6.50 8.00 8.20 8.20 6.50 5.20 5.00 4.80

30. En el ejercicio anterior, el recorrido de las celdas se hizo en columna, realice lo mismo pero ahora por fila. Sub QuitaBlancos() Dim fila As Integer Dim col As Integer Range("B3").Select fila = ActiveCell.Row 'Número de fila col = ActiveCell.Column 'Número de columna Do While Not IsEmpty(ActiveCell) Do While Not IsEmpty(ActiveCell) ActiveCell.Value = Val(Trim(ActiveCell.Value)) ActiveCell.Offset(0, 1).Select Loop Universidad Tecnológica de Izúcar de Matamoros

70

fila = fila + 1 'Incrementa en una la columna ActiveSheet.Cells(fila, col).Select 'Activa C3,D4,etc. Loop End Sub Nota: El siguiente código es el que corresponde al botón que llama a la macro

Private Sub CommandButton1_Click() Call QuitaBlancos End Sub

31. Macros 1. Extraer de una lista aquellos alumnos que tienen al menos una materia reprobada 2. Diseñar una macro que imprima la tabla de multiplicación de N número 3. Diseñar una macro que elimine registros que cumpla o no cumplan cierta condición (semejante al de los sinodales, también se puede basar en test3.xls) 4. Encontrar RFC, con funciones y con una macro


71

Apéndice B

Propuesta de examen final 1.

Realizar una hoja de cálculo con el nombre de OPINIÓN con los siguientes encabezados: NºControl, Edad, Sexo, EdoCivil, Religión, Actitud, Hijos, Trabaja. Capturarle al menos 5 personas y darle formato como se indica a continuación.

2.

Asignar según corresponda en EDOCIVIL 1)Soltero, 2)Casado y 3)Divorciado

3.

Asignar como desee en RELIGIÓN 4)otra

4.

Dar formato a la tabla de datos OPINIÓN según sus gustos (letra, bordes,


1)Católico, 2)Cristiano, 3)Protestante y

72

relleno, centrar títulos de las columnas, ancho de columnas, etc.) 5.

Insertar 5 hojas en e l libro y ponerles nombre (consulta1, consulta2, etc)

6.

Realice las siguientes consultas utilizando auto filtro, copie y pegue (incluyendo los títulos de las columnas) los resultados de cada consulta en cada una de las hojas que inserto en el punto ante rior, según corresponda. a)

Número de personas entre 20 y 25 años

b)

Número de mujeres que tengan mas de 2 hijos

c)

Número de personas protestantes felices

d) Promedio de hijos de las personas que trabajan y son católicas (utilice la función promedio) e) Promedio de edad de hombres casados y felices (utilice la función promedio) 7.

Inserte otra hoja y asignarle por nombre: Hombres casados

8.

En la hoja que acaba de insertar copie y pegue el resultado de una última consulta: de los hombres casados, y en esta tabla mediante auto filtros obtenga:

9.

¿Cuantos hay felices?

10.

¿Cuantos hay indiferentes?

11.

¿Cuantos hay contentos?

12.

¿Cuantos hay molestos?

13.

Con los resultados obtenidos en el punto anterior realizar una tabla (columnas: actitud, cantidad) en otra hoja que se llame (datos de gráfico)


73

14.

Con los datos obtenidos en la tabla anterior INSERTAR UN GRÁFICO circular (pastel) donde se muestre el % de hombres casados en los diferentes estados de ánimo, debe llevar como título ACTITUD DE LOS HOMBRES CASADOS.

15.

Proteger la hoja de las instrucciones con la clave "Excel"

16.

Guardar el documento con el nombre: "su nombre"_examenfinalexcel


74

Microsoft Excel Basico e Intermedio

Recommend Documents