MANUAL DE MICROSOFT EXCEL 2007 Francisco Jiménez Bonilla
San José, CR: INA, Agosto 2008
Primera Edición Instituto Nacional de Aprendizaje San José, Costa Rica.
© Instituto Nacional de Aprendizaje, 2008 ISBN Hecho el depósito de ley Prohibida la reproducción parcial y total del contenido de este documento sin la autorización expresa del INA.
Impreso en Costa Rica
PRESENTACION
El siguiente material didáctico fue desarrollado a través de la recopilación de información de fuentes primarias con instructores de enseñanza del subsector de informática, y secundarias, entre bibliografía de trabajos de investigación, artículos y consulta digital en la red de Internet. Este material es producto de la necesidad de un instructivo de apoyo al servicio de capacitación y formación profesional, Introducción a la Computación, del programa Personal Calificado en Operación de Equipo de Cómputo como objetivo manipular la computadora personal y sus periféricos en ambiente gráfico.
El material está dirigido a todo participante de la formación y capacitación profesional del módulo de introducción a la computación en la modalidad presencial, para el apoyo del mismo SCFP y con la tutoría del profesional técnico de subsector de informática, de forma que el material didáctico describe el marco teórico y práctico de los contenidos, persiguiendo el alcance de los objetivos específicos y cumplimiento de los criterios de desempeño por cada uno de ellos.
El uso del material didáctico está diseñado conforme a los objetivos y contenidos del servicio de capacitación y formación profesional de la oferta curricular de introducción a la computación, por lo cual en el documento encontrará una estructura separados por capítulos, los cuales representan los objetivos específicos de módulo de introducción a la computación, así mismo cada capítulo, contiene los subtemas que representan los contenidos de cada objetivo, estos contenidos son desarrollados a nivel teórico y práctico, de forma que el participante pueda utilizar el material didáctico de una manera de conocimiento de la materia y ejecución de las tareas de configuración del equipo informático.
INSTITUTO NACIONAL DE APRENDIZAJE Manual de Hoja Electrónica Excel
4
INDICE PRESENTACION ................................................................................................................III CAPITULO 1......................................................................................................................... 6 ¿QUE ES MICROSOFT EXCEL? ..................................................................................... 7 1.1 DESCRIPCION DEL AMBIENTE DE TRABAJO............................................. 7 Elementos de la ventana de Excel................................................................................... 8 Botón del Office.............................................................................................................. 8 Barra de herramientas accesos rápidos ........................................................................... 9 1.2 MANIPULACION DE LIBROS ........................................................................ 12 Creación de un libro ...................................................................................................... 12 Guardar un libro ............................................................................................................ 12 Abrir un libro................................................................................................................. 13 Cerrar un libro ............................................................................................................... 13 1.3 MANIPULACION DE HOJAS DE CALCULO................................................ 13 ¿Qué es una hoja de cálculo?............................................................................................ 13 Manipulación de las hojas ................................................................................................ 14 Manipulación de filas y columnas ................................................................................... 15 1.4 MANIPULACION DE DATOS ......................................................................... 16 Creación de tabla de datos................................................................................................ 17 Trabajando con rangos ..................................................................................................... 19 manipulacion de informacion ........................................................................................... 20 apariencia de celdas y tablas............................................................................................. 21 formato condicional.......................................................................................................... 22 listas personalizadas ......................................................................................................... 23 comentarios....................................................................................................................... 24 Cambiar la apariencia de un cuadro comentario: .......................................................... 25 CAPITULO 2....................................................................................................................... 26 FÓRMULAS........................................................................................................................ 27 ¿Qué es una fórmula? ....................................................................................................... 27 Elementos básicos de las fórmulas ................................................................................... 27 Operadores matemáticos .................................................................................................. 28 Jerarquía de Operadores ................................................................................................... 28 Lenguaje de fórmulas ....................................................................................................... 29 Ingresando fórmulas ......................................................................................................... 29 Tipos de fórmulas ............................................................................................................. 30 Creación de Fórmulas....................................................................................................... 30 Copiar fórmulas a un rango de celdas .............................................................................. 32 Valor Absoluto .............................................................................................................. 32 FUNCIONES ....................................................................................................................... 33 ¿Qué son funciones?......................................................................................................... 33 Funciones Condicionales.................................................................................................. 36 Elementos de las funciones lógicas .................................................................................. 36
INSTITUTO NACIONAL DE APRENDIZAJE Manual de Hoja Electrónica Excel
5
Sintaxis de las funciones lógicas ...................................................................................... 36 Condición Lógica Simple ................................................................................................. 37 Condición Lógica Anidada............................................................................................... 37 Condición Lógica Compleja............................................................................................. 38 CAPITULO 3....................................................................................................................... 40 ¿Qué es configurar una hoja de cálculo? .......................................................................... 41 Pestaña página .................................................................................................................. 42 Pestaña Márgenes ............................................................................................................. 42 Pestaña Encabezado y pie de página ............................................................................. 43 Pestaña Hoja.................................................................................................................. 43 Impresión de hoja de cálculo......................................................................................... 44 CAPITULO 4....................................................................................................................... 45 ¿Qué son las bases de datos en Excel? ............................................................................. 46 Ordenamientos de Datos ............................................................................................... 46 Proceso para ordenar y reordenar listas ........................................................................ 46 Filtros Automáticos ....................................................................................................... 47 Búsquedas de registros:................................................................................................. 48 Filtros Avanzados.......................................................................................................... 49 Funciones con Bases de Datos ...................................................................................... 51 Buscarv.......................................................................................................................... 51 Bdcontar ........................................................................................................................ 53 Bdmax ........................................................................................................................... 54 Tablas Dinámicas .......................................................................................................... 55 Proceso Inicial de tabla dinámica.................................................................................. 56 Aplicación de función ................................................................................................... 60 Grafico Dinámico.......................................................................................................... 60 CAPITULO 5....................................................................................................................... 63 Barra de Dibujo ............................................................................................................. 64 Wordart ......................................................................................................................... 65 Imágenes ....................................................................................................................... 66 Insertar imágenes prediseñadas o de archivo. En una hoja de cálculo se pueden agregar imágenes de los dos tipos:............................................................................................. 66 Desde Archivo:............................................................................................................. 67 Gráficos ......................................................................................................................... 68 Partes de un gráfico....................................................................................................... 69 Terminología de gráficos .............................................................................................. 69 Creación de gráficos...................................................................................................... 70 CAPITULO 6....................................................................................................................... 75 ¿Qué son las Macros?.................................................................................................... 76 Ejecuctar una macro...................................................................................................... 77 Detener una macro ........................................................................................................ 77 Eliminar una macro ....................................................................................................... 77
INSTITUTO NACIONAL DE APRENDIZAJE Manual de Hoja Electrónica Excel
6
CAPITULO 1 Utilizar la hoja electrónica y sus operaciones para elaborar tablas de datos. • Descripción del Ambiente de Trabajo • Manipulación de libros • Manipulación de hojas de cálculo • Manipulación de datos • Trabajando con rangos • Manipulación de información • Apariencia de celdas y tablas • Formato condicional • Listas personalizadas • Comentarios
INSTITUTO NACIONAL DE APRENDIZAJE Manual de Hoja Electrónica Excel
7
¿QUE ES MICROSOFT EXCEL? Es un programa de oficina con su nueva apariencia en diseño que sirve para crear contabilidades y luego graficarlas en cualquier momento. Las versiones más usadas por parte de los usuarios son: Excel Xp 2003 y actualmente Excel 2007. También se le conoce como un libro electrónico que está compuesto por hojas de cálculo en donde se crean las diferentes tablas informativas aplicando formatos, fórmulas, funciones, y herramientas especiales. Los archivos de Excel con versión 2003 Xp tienen extensión .xls y los archivos de Excel con versión 2007 se les agrega una letra de mas .xlsX. Estos archivos se identifican por tener un icono de color verde.
1.1 DESCRIPCION DEL AMBIENTE DE TRABAJO Iniciar el programa buscándolo desde el menú inicio del escritorio de Windows. Luego abrir el programa Excel donde se observara la siguiente pantalla:
INSTITUTO NACIONAL DE APRENDIZAJE Manual de Hoja Electrónica Excel
8
Para salir del programa Excel se deben aplicar los siguientes pasos: 1. Clic al botón Office 2. Clic al botón Salir de Excel La manera rápida seria pulsando las teclas ALT+F4 Se recomienda tener los archivos de trabajo guardados antes de salir para que Excel no tenga que presentarle ninguna ventana de advertencia.
Elementos de la ventana de Excel Antes de trabajar es relevante conocer las principales partes que se van a manipular constantemente en los trabajos.
Botón del Office Es el que permite manipular las opciones más usadas por parte de un usuario al dar clic sobre él.
INSTITUTO NACIONAL DE APRENDIZAJE Manual de Hoja Electrónica Excel
9
Barra de herramientas accesos rápidos Es una barra que muestra las herramientas que el usuario puede estar usando en forma rápida. El icono que esta al final de la misma sirve para activar o desactivar la deseada.
Barra de título Es aquella que muestra el nombre del archivo de trabajo y el nombre del programa.
Cintas
Son las que muestran las diferentes herramientas que se pueden manipular en el programa. Su nombre brinda una idea general al usuario donde puede encontrar sus opciones preferidas. Antes conocido como menú en versiones anteriores.
Al dar clic a cada cinta se despliegan diversos botones que se usan de acuerdo al objetivo deseado.
INSTITUTO NACIONAL DE APRENDIZAJE Manual de Hoja Electrónica Excel
10
Barra de fórmulas Sirve para controlar las fórmulas y funciones que se hagan sobre una celda pidiéndola editar.
Celda actual Permite indicarle al usuario la celda actual donde se encuentra ubicado.
Cursor Permite ubicarnos entre las celdas de una hoja electrónica.
Celdas Es la combinación de una columna con una fila. Para desplazarse a una celda en forma rápida se debe pulsar la tecla F5 y escribir en el cuadro referencia la celda respectiva.
Columnas Son representadas por letras (A B C) Filas Son representadas por números (1 2 3) Ejemplo: Celda: C3 Columna: C Fila: 3
INSTITUTO NACIONAL DE APRENDIZAJE Manual de Hoja Electrรณnica Excel
11
Rango Es la selecciรณn de varias celdas.
Hojas de cรกlculo Sirven para crear tablas contables aplicando fรณrmulas, funciones y diferentes herramientas. Ademรกs permiten organizar los trabajos de una manera organizada. La configuraciรณn de una hoja de cรกlculo es diferente a las demรกs, son individuales.
Ademรกs se les puede cambiar el nombre con doble clic. Tambiรฉn
se les conoce como etiquetas.
Para navegar entre las hojas de cรกlculo se deben manipular los siguientes botones:
Observe un resumen rรกpido de las partes de una hoja de cรกlculo:
INSTITUTO NACIONAL DE APRENDIZAJE Manual de Hoja Electrónica Excel
12
1.2 MANIPULACION DE LIBROS Creación de un libro Al iniciar una sesión de Excel el mismo le provee un libro de trabajo en blanco, con 3 hojas de cálculo en forma automática. Los archivos en Excel son tratados como Libros, los cuales contienen hojas Sin embargo, cuando se encuentre trabajando en Excel y necesite crear un nuevo libro de trabajo, no es necesario salirse de Excel para crearlo. Aplicar los siguientes pasos: 1. Clic al botón Office 2. Clic Nuevo 3. Clic al icono Libro en blanco 4. Clic al botón Crear La manera rápida seria pulsando las teclas CTRL+U
Guardar un libro Cuando un Libro debe ser almacenado por primera vez, se procede a utilizar la opción Guardar como, pero si lo que necesita es actualizarlo para que almacene los últimos cambios generados, entonces proceda a utilizar la opción Guardar.
Aplicar los siguientes pasos para guardar el archivo por primera vez: 1. Clic al botón Office 2. Clic en Guardar como La manera rápida es pulsar la tecla F12 Aplicar los siguientes pasos para actualizar los cambios del archivo: 1. Clic al botón Office 2. Clic en Guardar
INSTITUTO NACIONAL DE APRENDIZAJE Manual de Hoja Electrónica Excel
13
Abrir un libro Una vez que se haya creado y guardado un archivo, el mismo puede ser accedido cuantas veces sea necesario en razón de nuestra necesidad como usuarios(as).
Aplicar los siguientes pasos para guardar el archivo por primera vez: 1. Clic al botón Office 2. Clic en Abrir La manera rápida es pulsar las teclas CTRL+A
Cerrar un libro Una vez que ya no se requiera un archivo de trabajo se puede cerrar para descargar un poco de memoria de la pc.
Aplicar los siguientes pasos para guardar el archivo por primera vez: 1. Clic al botón Office 2. Clic en Cerrar La manera rápida es pulsar CTRL+F4
1.3 MANIPULACION DE HOJAS DE CALCULO ¿Qué es una hoja de cálculo?
Es un conjunto de celdas que permiten llevar a cabo fórmulas, funciones, condiciones o herramientas de acuerdo a un objetivo especifico. Se les conoce como etiquetas. El nombre de las etiquetas de las hojas de un libro, puede ser modificado con el propósito de identificarlas mejor mediante el establecimiento de una relación entre el nombre que las mismas recibirán y el contenido almacenado en ellas.
INSTITUTO NACIONAL DE APRENDIZAJE Manual de Hoja Electr贸nica Excel
Ejemplos:
Manipulaci贸n de las hojas Las hojas de c谩lculo se pueden manipular de esta forma:
Insertar hojas -
Clic al icono
Renombrar hojas -
Doble clic sobre la hoja
Mover hojas:
-
Arrastrar la hoja al lugar donde se requiera.
Borrar hojas -
Clic derecho sobre la hoja
-
Eliminar
Al borrar una hoja tome en cuenta que no puede recuperar su contenido.
Color de etiquetas -
Clic derecho sobre la hoja
-
Color de etiquetas
Seleccionar hoja -
Clic a la hoja de c谩lculo.
14
INSTITUTO NACIONAL DE APRENDIZAJE Manual de Hoja Electrónica Excel
15
Manipulación de filas y columnas El ancho y el alto de las filas y las columnas pueden ser modificables en razón del espacio que necesitemos a la hora de ingresar una determinada información. Cuando usted ingresa a un libro de Excel, todas las columnas y las filas tienen el ancho estándar. El alto de la fila se ajusta automáticamente según el tamaño de fuente utilizada, mientras que el ancho de la columna debe ser ajustado, de acuerdo a lo que se necesite.
Ancho de columna Pasos para modificar el ancho de columna: Posicione el puntero del mouse en medio de 2 columnas. Cuando el puntero adquiera forma de flecha doble presione y arrastre el mouse. Ancho de fila Si bien es cierto que como se dijo anteriormente, Excel ajusta de forma automática el ancho de la fila, también es factible el ajuste de la misma forma en que se hace con las columnas.
Pasos para modificar el ancho de columna: Posicione el puntero del mouse en medio de 2 filas. Cuando el puntero adquiera forma de flecha doble presione y arrastre el mouse.
Agregar Filas/Columnas 1. Seleccione el o los encabezados de las filas o columnas a partir del lugar donde desea insertarla. 2. Despliegue el menú contextual y elija la opción Insertar. Las filas aparecerán en la parte superior, mientras que las columnas aparecerán en la parte izquierda.
INSTITUTO NACIONAL DE APRENDIZAJE Manual de Hoja Electrónica Excel
16
También se puede utilizar la cinta inicio y el botón insertar. Eliminar Filas/Columnas 1. Seleccione el o los encabezados de las filas o las columnas a partir del lugar donde desea eliminarla. 2. Despliegue el menú contextual y elija la opción Eliminar. También se puede utilizar la cinta inicio y el botón eliminar.
Ocultar Filas/Columnas 1. Seleccione el o los encabezados de la fila o columna que desea ocultar 2. Clic derecho y la opción ocultar. Mostrar Filas/Columnas 1. Seleccione los encabezados de fila(s) o columna(s) donde están las ocultas. 2. Clic derecho sobre las filas o columnas donde se ocultaron. 3. Clic derecho y la opción mostrar.
1.4 MANIPULACION DE DATOS Para agregar datos en las celdas de Excel se digita el dato ya sea numérico o textual y se pulsa la tecla Enter. Si el dato es una cantidad se recomienda escribirlo en forma directa, ejemplo 24000. Para el eliminar información se aplican los siguientes pasos: Ubicar el curso sobre la celda o seleccione el rango deseado. Presionar la tecla Supr. Excel puede trabajar con los siguientes tipos de datos: Textos, Números, Fórmulas, Funciones, Condiciones lógicas.
INSTITUTO NACIONAL DE APRENDIZAJE Manual de Hoja Electrónica Excel
17
Texto Puede ingresar la combinación de letras y números como texto. El texto es alineado automáticamente a la izquierda de la celda. Si ingresa sólo números y la celda tiene formato de texto mantendrá su valor.
Número Puede ingresar caracteres especiales como +,-, $,%, los cuales serán alineados a la derecha de la celda, es posible que incluya comas, decimales, puntos, signos de dólar y paréntesis entre otros.
Fórmulas y Funciones Nos permiten llevar a cabo cálculos matemáticos de forma eficaz en una forma automatizada.
Condiciones lógicas Nos permiten llevar a cabo razonamientos para la solución de determinados casos que dependen de varios criterios lógicos.
Creación de tabla de datos Una tabla de datos es un conjunto de celdas que tienen campos y registros. Los campos son categorías y los registros corresponden a los datos de los campos de acuerdo a su relación. Ejemplos de campos: Nombre, Apellidos, Teléfono, Lugar Ejemplos de registros: Francisco, Jiménez, 8376-2117, Santa Ana
Nombre Apellidos Francisco Maria Olga
Jimenez Rojas Hernandez
Telefono
Lugar
8376-2117 8395-0523 8765-2314
Santa Ana Heredia Alajuela
INSTITUTO NACIONAL DE APRENDIZAJE Manual de Hoja Electrónica Excel
18
A los campos y a los registros se les puede aplicar formatos de celda usando la cinta de inicio. El primer paso es seleccionar la celda deseada y luego clic a cualquiera de los siguientes botones:
Sirven para cambiar el tipo y el tamaño de letra.
Sirven para cambiarle la negrita, cursiva y subrayado.
Sirven para cambiar el relleno o fondo a una celda y el color de un texto o número:
Sirven para aumentar o disminuir el tamaño de una letra.
Además existe la posibilidad de usar el botón Fuente para aplicar formatos a celdas estando en un solo lugar.
A los campos y a los registros se les puede aplicar los siguientes tipos de alineamientos:
INSTITUTO NACIONAL DE APRENDIZAJE Manual de Hoja Electrónica Excel
19
Sirve para cambiar la orientación de textos o números en una celda.
Sirve para ubicar un nombre de campo cuando no alcanzo en una sola celda.
Sirven para establecer diversos estilos de moneda a rangos numéricos.
Además se pueden centrar títulos principales a lo ancho de una tabla aplicando el botón:
EMPLEADOS Nombre
Apellidos
Telefono
Lugar
Francisco
Jimenez
8376-2117
Santa Ana
Maria
Rojas
8395-0523
Heredia
Olga
Hernandez
8765-2314
Alajuela
TRABAJANDO CON RANGOS Un rango es la selección de varias celdas.
Seleccionar rangos continuos
1. Ubíquese en la celda inicio y arrastre el mouse de tal forma que se desplace hasta la celda final que necesita marcar. 2. Suelte el mouse
INSTITUTO NACIONAL DE APRENDIZAJE Manual de Hoja Electrónica Excel
20
Seleccionar rangos discontinuos
2. Seleccione el primer rango como lo hizo en el punto anterior 3. Presione la tecla CTRL y manténgala presionada. 4. Con ayuda del mouse, seleccione el segundo rango. 5. Suelte el mouse y la tecla CTRL
MANIPULACION DE INFORMACION Excel permite manipular la información utilizando las herramientas copiar, pegar y cortar información entre las diferentes hojas de cálculo. Copiar y pegar una tabla a otra hoja de cálculo 1. Seleccionar una tabla (campos y registros). 2. Pulsar las teclas CTRL+C 3. Clic a una hoja de cálculo vacía 4. Pulsar las teclas CTRL+V
Mover una tabla a una hoja de cálculo 1. Seleccionar la tabla 2. Pulsar las teclas CTRL+X 3. Clic a una hoja de cálculo vacía 4. Pulsar ENTER
Borrar una tabla 1. Seleccionar una tabla (campos y registros) 2. Pulsar la tecla Supr
Deshacer herramienta aplicada Pulsar las teclas CTRL+Z
INSTITUTO NACIONAL DE APRENDIZAJE Manual de Hoja Electrรณnica Excel
21
APARIENCIA DE CELDAS Y TABLAS De acuerdo a las siguientes herramientas se pueden dejar las celdas con mejor calidad de presentaciรณn a la hoja de imprimir un trabajo en una impresora a color. Aplicaciรณn de bordes Se puede aplicar un borde a las diferentes celdas:
Estilos de celda Se usan para darles un estilo de relleno interesante a las celdas.
Nombre
Apellidos
Telefono
Lugar
La tabla de datos se le puede cambiar la apariencia de una manera rรกpida y de la siguiente manera:
1. Seleccionar tabla (campos y registros). 2. Clic a la cinta Inicio 3. Clic al botรณn Dar formato como tabla 4. Elegir la apariencia deseada a la tabla.
INSTITUTO NACIONAL DE APRENDIZAJE Manual de Hoja Electr贸nica Excel
22
FORMATO CONDICIONAL Un formato condicional es aquel que permite agregar un formato especial a una o varias celdas de acuerdo a un criterio.
Veamos el siguiente ejemplo: Si se digita un valor mayor que 5000 debe salir en color rojo. 1000 2000 3000 4000 5000
Ejecute el siguiente procedimiento para aplicar un formato condicional: 1. Seleccionar un rango de celdas 2. Clic a la cinta Inicio 3. Clic al bot贸n Formato condicional
Aparecer谩 la siguiente pantalla: Elegir el tipo de criterio a establecer en el rango de celdas seleccionadas.
INSTITUTO NACIONAL DE APRENDIZAJE Manual de Hoja Electrónica Excel
23
Borrar formatos condicionales Para borrar formatos condicionales aplicados a celdas se deben ejecutar los siguientes pasos:
1. Seleccionar el rango de celdas 2. Clic al botón Formato condicional 3. Clic a borrar reglas… Elegir la opción deseada ya sea si se va a borrar las celdas seleccionadas o a todos los formatos condicionales de la hoja.
LISTAS PERSONALIZADAS
Se usan generalmente para agilizar la inserción de datos en las celdas. Veamos los siguientes ejemplos:
Copiar información Si quiero copiar el contenido de una celda posiciono el cursor en la esquina inferior derecha de la celda y cuando aparezca el cursor de cruz negra lo arrastro hacia abajo, haciendo una copia de la información.
Ver el siguiente ejemplo:
100 100 100 100 100
INSTITUTO NACIONAL DE APRENDIZAJE Manual de Hoja Electrónica Excel
24
Aumentar cantidades (Autollenado) Si quiero aumentar una cantidad se debe digitar 2 números con el margen para aumentar. Para ello debe seleccionar las celdas y luego posicionar el cursor en la esquina inferior derecha.
Generar Series de datos (listas de días y meses) Para aplicar una lista automática se digita el mes o el día, luego se ubica el cursor en la esquina inferior derecha para encontrar el curso de la cruz negra y arrastrar hasta localizar la información requerida.
Digite solo enero:
Digite solo lunes:
Enero Febrero Marzo Abril Mayo
Lunes Martes Miércoles Jueves Viernes
COMENTARIOS Sirven para hacer aclaraciones en las celdas de una hoja de cálculo. Existen 2 tipos de comentarios: Visibles e Invisibles. Los comentarios visibles son aquellos que se miran en la hoja de cálculo. Los comentarios invisibles son aquellos que no se miran en la hoja de cálculo. Ejemplo de comentario visible:
INSTITUTO NACIONAL DE APRENDIZAJE Manual de Hoja Electrónica Excel
25
El campo Apellido es el que tiene insertado el comentario, es invisible.
Insertar un comentario
Borrar un comentario
1. Ubicar el cursor en una celda
1. Ubicar el cursor en la celda del
2. Clic a la cinta Revisar
comentario
3. Clic en el botón Nuevo Comentario
2. Clic a la cinta Revisar
Escribir el comentario
3. Clic en el botón Eliminar
Modificar un comentario
Cambiar la apariencia de un cuadro comentario:
1. Ubicar el cursor en la celda del comentario
1. Tener el comentario visible.
2. Clic a la cinta Revisar
2. Clic derecho al cuadro del comentario
3. Clic en el botón Modificar
3. Clic a la opción Formato de
Comentario Escribir el comentario
comentario 4. Clic a la pestaña Colores y líneas y elegir del combo relleno un color.
Nota Importante: Otra manera de agregar comentarios es usar el clic derecho del mouse sobre una celda y manipular las diversas funciones sobre comentarios: insertar, modificar, eliminar y elegir el tipo de comentario.
INSTITUTO NACIONAL DE APRENDIZAJE Manual de Hoja Electrónica Excel
26
CAPITULO 2 Resolver situaciones específicas aplicando las operaciones y funciones básicas de la hoja electrónica.
• Descripción de fórmulas • Elementos básicos de fórmulas • Operadores matemáticos • Ingresando fórmulas • Tipos de fórmulas • Creación de fórmulas • Funciones básicas • Sintaxis de funciones • Funciones condicionales • Operadores lógicos
INSTITUTO NACIONAL DE APRENDIZAJE Manual de Hoja Electrónica Excel
27
FÓRMULAS ¿Qué es una fórmula? Una fórmula es una expresión matemática que involucra operadores matemáticos de acuerdo a interpretaciones de la vida real. Ejemplos: -
Una fórmula que saque un promedio de nota.
-
Una fórmula que saque una deducción.
-
Una fórmula que saque un salario neto
-
El resultado final debe ser exacto para copiarlo a otras celdas.
Resuelva las siguientes fórmulas de acuerdo a estos valores de celdas: A1=100 A2=2 A3=5 (a1*a2)*2= a1+(a2+a3+a1) a3-a2*(5+a1)
Elementos básicos de las fórmulas •
Las fórmulas inician con el signo =
•
Las fórmulas tienen un orden.
•
Los cálculos se realizan de manera lógica.
•
Cuando los operadores son iguales, Excel calcula de izquierda a derecha.
•
Los cálculos pueden involucrar referencias.
•
Los rangos pueden operar como celdas individuales.
INSTITUTO NACIONAL DE APRENDIZAJE Manual de Hoja Electrónica Excel
28
Operadores matemáticos Los operadores matemáticos se necesitan para confeccionar una fórmula en Excel, junto con el orden de prioridad que los mismos seguirán a la hora de ejecutarse la misma.
Los operadores deben respetar un orden específico tal y
como se presenta en el cuadro que se presenta en la parte inferior, en donde se observa cómo la multiplicación y la división tienen precedencia sobre la suma y la resta.
Además de los operadores básicos (+,-,*,/) existen otros operadores que controlan el orden en el cual se realizan los cálculos, tal y como se presenta a continuación.
Jerarquía de Operadores La Jerarquía de operadores establece el orden de prioridad en el que se aplican las operaciones sobre los valores, la cual puede ser variada por medio del uso de paréntesis.
Por ejemplo estas dos fórmulas dan diferente resultado según la
jerarquía de sus operadores y el uso de paréntesis: 25 + 32 * 2 = 25 + 64 = 89 (25 + 32) * 2 = 57 * 2 = 114
Operador
Símbolo
Prioridad
Cálculos entre paréntesis
()
1
Números negativos
-
2
Porcentajes
%
3
Exponentes
^
4
Multiplicación y división
*, /
5
Suma y Resta
+,-
6
Equivalencias
=, <>, >, <. >=, <=
7
INSTITUTO NACIONAL DE APRENDIZAJE Manual de Hoja Electrónica Excel
29
Lenguaje de fórmulas
Término
Definición
Dirección o referencia
Ubicación de una celda o de un grupo de celdas.
Argumento o factor
Parte de la ecuación que no es un operador. Comprende constantes, funciones, nombres, referencias de celda y valores.
Constante
Número o texto que usted teclea directamente en la fórmula y que no altera su valor.
Fórmula o ecuación
Computación matemática que comprende factores múltiples y que da como resultado un nuevo valor.
Función
Fórmula integrada.
Rótulo
Letreros en la parte superior de una columna o en el extremo izquierdo de una fila que describen el contenido de la columna o fila.
Nombre
Identificador de un rango en particular.
Operador
Indicadores que especifican la operación que se debe llevar a cabo. Los mismos incluyen Suma (+), Resta (-), División (/), Multiplicación (*), Porcentaje (%), Exponentes (^), y Equivalencias (igual [=], diferente [<>], mayor que [>], menor que [<]).
Rango
Una celda o un bloque de celdas.
Sintaxis
Reglas correctas de escritura de una fórmula. Ubicación correcta de los elementos que conforman la misma (paréntesis, operadores matemáticos, entre otros).
Valor
Números, no texto.
Ingresando fórmulas Las fórmulas se pueden digitar desde dos partes:
1. En la barra de fórmulas con un clic para habilitar dicha área y así poder digitar la fórmula.
INSTITUTO NACIONAL DE APRENDIZAJE Manual de Hoja Electrónica Excel
30
2. En una celda, partiendo de la misma acción ejecutada en el punto anterior. Es importante mencionar que lo que va a visualizar en la celda, es el número que se generó como resultado de la fórmula digitada. Si necesitara verificar la sintaxis de la misma, diríjase a la barra de fórmulas desde donde pueden hacerse correcciones y modificaciones de cualquier tipo, de acuerdo al tipo de datos ingresados. Si por alguna razón quisiera visualizar la fórmula desde la celda, entonces de un doble clic sostenido en la misma.
Los cambios realizados en las fórmulas se actualizan de forma automática, por lo que si se ha cambiado algún dato, basta con dar un ENTER en la barra de fórmulas o en la celda, en donde se encuentra la fórmula, de forma tal que cada vez que se ejecute una modificación, no será necesario borrar la fórmula para digitarla con la nueva información. Para editar una fórmula se puede pulsar F2 o hacer clic en la barra de fórmulas. Tipos de fórmulas Excel presenta 2 tipos de fórmulas:
-
Fórmulas de texto: No se llevan a cabo cálculos, sino que se encadenan entradas de celdas, por lo que el resultado que se obtendrá es una cadena de caracteres.
-
Fórmulas numéricas: Calculan valores numéricos dando como resultado un nuevo valor numérico. Los valores que estas fórmulas involucran pueden ser constantes o variables.
Creación de Fórmulas Ejemplo de formula de texto (concatenación): Para llevar a cabo la realización de fórmulas de texto siga los pasos que se le mencionan y tome en cuenta las siguientes notas:
INSTITUTO NACIONAL DE APRENDIZAJE Manual de Hoja Electrónica Excel
31
1. Digitar la tabla que a continuación se le presenta:
2. Dejar el cursor en la celda E1 y hacerla más ancha. 3. Aplicar la siguiente fórmula en la celda E1 =A1&" "&B1&" "&C1&" "&D1 4. El resultado de la formula es el siguiente:
5. El resultado se encuentra ubicado en la celda E1, en donde se hace visible la cadena de caracteres, procedente del contenido de la información ubicada en las celdas A1,B1,C1,D1. 6. Observe también que existe la presencia del símbolo amperson (&) para llevar a cabo la agrupación del texto en una única celda. Las comillas son utilizadas para establecer espacios entre las cadenas de caracteres. Pasos para crear fórmulas numéricas: 1. Digitar la siguiente tabla:
2. Seleccione la celda E2 para iniciar la formula. 3. Digite el signo igual ( = ) y ubique el cursor en la celda D2 luego pulso el operador *, luego clic a la celda C2. 4. Pulse ENTER para obtener el resultado. 5. El resultado debe aparecer en la celda respectiva.
INSTITUTO NACIONAL DE APRENDIZAJE Manual de Hoja Electrónica Excel
32
Copiar fórmulas a un rango de celdas Es un procedimiento que se debe hacer cuando existe la seguridad de un 100% que el resultado de la formula es totalmente correcto. No se debe alterar ningún resultado numérico. Además tiene la gran ventaja de no ir aplicando la formula celda por celda y perder un tiempo valioso. Imaginémonos que pasaría sacando una formula con 100 empleados de una empresa grande.
Proceso: Ubicar el cursor en la esquina inferior derecha de una celda y cuando aparece la cruz de color negra se arrastra hacia abajo hasta la última celda donde se va a generar los diferentes resultados de la formula. Si se usa la tabla anterior la misma quedaría lista con los siguientes resultados en las celdas que tiene color de relleno.
Valor Absoluto Es fijar una celda para que cuando se copia una formula no se alteren los resultados.
Para fijar una celda se debe pulsar la tecla F4 sobre ella.
Generalmente se fija cuando es un dato que esta fuera de una tabla y se debe utilizar en una fórmula.
En el ejemplo de abajo la celda que va a tener el valor
absoluto es F1. Veamos el siguiente ejemplo:
INSTITUTO NACIONAL DE APRENDIZAJE Manual de Hoja Electrónica Excel
33
En esta tabla la formula que se debe de plantear es obtener la conversión de dólares a colones para ello se debe de tener claro el tipo de cambio actual multiplicando los dólares por la celda que tiene el tipo de cambio F1, pero cuando se ubique en la celda F1 ahí es donde se debe pulsar la tecla F4. La interrogante que se da de acuerdo a este tema es cuando se debe aplicar el valor absoluto a una celda. Podrimos decir en forma menos técnica y más fácil de aprender que se aplica cuando un valor de una celda que esta fuera de la tabla principal se va a utilizar para una determinada formula. Si observamos el dato 530 de la celda F1 que es el tipo de cambio se encuentra en una posición fuera de la tabla y se debe usar obligatoriamente para crear la formula: colones * tipo de cambio (C4*$F1$). Se recomienda fijarla para evitar resultados incorrectos al copiar la formula a otras celdas.
Observe que la celda F1 esta antecedida del
signo dólar, ya que es el signo que representa el valor absoluto.
FUNCIONES ¿Qué son funciones? Las funciones son expresiones predeterminadas del programa y existen diversos tipos de funciones. Se identifican porque llevan un nombre de función después del igual. Las funciones básicas son las que se aplican con mayor frecuencia.
Generalmente la sintaxis de una función es: =nombre de función(parámetros)
Ejemplo: =suma(rango, celdas, o constantes) El signo igual significa el inicio de una función, el nombre de función dependerá del objetivo a obtener del resultado, y los parámetros pueden ser de tipo numérico o textual.
INSTITUTO NACIONAL DE APRENDIZAJE Manual de Hoja Electrónica Excel
34
Las funciones de Excel se clasifican por tipos y podemos citar: 1. Financieras 2. Lógicas 3. Fecha y hora 4. Búsqueda y referencia 5. Matemáticas y trigonométricas 6. Usadas recientemente
Excel tiene un asistente de funciones para obtener cálculos de una manera rápida sin aprenderse toda su sintaxis: Activar la cinta llamada Fórmulas para manipular el botón de funciones:
Luego aparecerá la siguiente ventana para elegir la función deseada:
INSTITUTO NACIONAL DE APRENDIZAJE Manual de Hoja Electrónica Excel
35
Excel cuenta con una función automática llamada Autosuma y se encuentra en la cinta Fórmulas. Ahora con la gran novedad de obtener rápidamente diferentes cálculos: suma, máximos, mínimos, promedio, contador con solo seleccionar el rango deseado.
Veamos un ejemplo: 100 200 300 400 500 1500 Suma
100 200 300 400 500 500 Maximo
100 200 300 400 500 100 Minimo
100 200 300 400 500 300 Promedio
100 200 300 400 500 5 Contar
Si quisiéramos implementar las funciones de una manera normal sería asi: Ubicarnos en la celda respectiva y luego digitar la función: =suma(parámetros)
=raíz(número)
Sirve para sumar los valores de un rango numérico.
Sirve para obtener la raíz cuadrada de un número.
=max(parámetros)
=min(parámetros)
Sirve para obtener el valor máximo de una colección de números.
Sirve para obtener el valor mínimo de una colección de números.
=producto(número)
=hoy()
Sirve para multiplicar los valores de un rango numérico.
Sirve para obtener la fecha actual del sistema de la pc.
=promedio(parámetros)
=moda(parámetros)
Sirve para obtener el promedio de una colección de números.
Sirve para obtener el valor que más se repite en un rango de números
=contar(parámetros)
=Redondear(número)
Sirve para contar los registros que coinciden en una tabla de datos.
Sirve para redondear un número, dando la cantidad de decimales.
INSTITUTO NACIONAL DE APRENDIZAJE Manual de Hoja Electrónica Excel
36
Funciones Condicionales Es una función que sirve para obtener resultados o realizar cálculos donde se debe evaluar una situación que puede tener sólo una de diversas soluciones. El nombre de esta función es SI.
Excel permite combinar la función SI en 3 tipos de condicionales, según se requiera : Simple Anidada Compleja Elementos de las funciones lógicas
Las funciones se encuentran constituidas por los siguientes elementos:
1. Una condición (condiciones): Las(s) cual(es) permite(n) evaluar el contenido de las celdas. 2. Un valor verdadero: Parte de la condición que se cumple. 3. Un valor falso: Parte de la condición que no se cumple. 4. Comillas: Se ingresan únicamente para el texto que deba incluirse dentro de la fórmula. Si no se ingresan, se generan errores de sintaxis.
Sintaxis de las funciones lógicas =Si(condición lógica, “Valor verdadero”; “Valor falso”) En donde las celdas recibirán la asignación del contenido del valor verdadero siempre y cuando se cumpla la condición lógica establecida. Es importante destacar que una operación matemática puede estar contemplada como Valor Verdadero o Valor Falso.
INSTITUTO NACIONAL DE APRENDIZAJE Manual de Hoja Electrónica Excel
37
Condición Lógica Simple Es aquella que tiene un solo SI en su sintaxis. Ejemplo: En una institución educativa se requiere obtener un resultado de acuerdo al siguiente caso: Si el promedio es mayor o igual a 70 ganó el curso en caso contrario perdió el curso. El resultado debe aparecer en la columna D para cada registro.
En la celda D4 se debe digitar la siguiente condición lógica para obtener el resultado.
=SI(D4>=70;"Ganó el curso";"Perdió el curso")
Condición Lógica Anidada Es aquella que tiene varios SI en su sintaxis.
Ejemplo:
Es una empresa se requiere clasificar a los usuarios por código de acuerdo a lo siguiente: Si el código es igual a A100 va a pertenecer a “Informática”, si el código es igual a “A200” va a pertenecer a “Financiero”, si el código es igual a “A300” va a pertenecer a “Ventas”, si el código es igual a “A400” va a pertenecer a
INSTITUTO NACIONAL DE APRENDIZAJE Manual de Hoja Electrónica Excel
38
“Información”, si el código es igual a “A500” va a pertenecer a “Recursos Humanos” en caso contrario No hay clasificación.
En la celda E4 se debe digitar la siguiente condición lógica para obtener el resultado.
=SI(A4="A100";"Informática";SI(A4="A200";"Financiero";SI(A4="A300";"Ventas";SI (A4="A400";"Información";SI(A4="A500";"Recursos
Humanos";"No
hay
clasificación")))))
Condición Lógica Compleja Es aquella donde se hacen uso de operadores lógicos Y-O respectivamente.
Ejemplos: Condición Lógica Operador Lógico Y
En una institución se requiere clasificar el rango de edades de las personas de la siguiente manera: Si la persona tiene una edad mayor o igual a 1 y menor igual a 15 es un niño; Si la persona tiene una edad mayor a 15 y menor o igual a 25 es un adolescente; Si la persona tiene una edad mayor a 25 y menor o igual a 60 es un adulto; Si la persona tiene una edad mayor a 60 y menor o igual a 80 es un adulto mayor. En caso contrario no existe clasificación.
INSTITUTO NACIONAL DE APRENDIZAJE Manual de Hoja Electrónica Excel
39
En la celda E4 se debe digitar la siguiente condición lógica para obtener el resultado.
=SI(Y(D4>=1;D4<=15);"Niño";SI(Y(D4>15;D4<=25);"Adolescente";SI(Y(D4>25;D4< =60);"Adulto";SI(Y(D4>60;D4<80);"Adulto Mayor";"Tercera Edad"))))
Condición Lógica Operador Lógico O
En una radioemisora se requiere rifar entre aficionados del fútbol un monto de dinero de acuerdo al siguiente caso: Si es aficionado de Alajuela o Saprissa recibirá un monto de 30.000 colones. Si es aficionado de Heredia o Cartago recibirá un monto de 20.000 colones. Si es aficionado de Pérez Zeledón o Brujas recibirá un monto de 10.000 colones.
En la celda E4 se debe digitar la siguiente condición lógica para obtener el resultado. =SI(O(C4="Saprissa";C4="Alajuela");30000;SI(O(C4="Heredia";C4="Cartago");200 00;SI(O(C4="Pérez Zeledón";C4="Brujas");10000;5000)))
INSTITUTO NACIONAL DE APRENDIZAJE Manual de Hoja Electrónica Excel
40
CAPITULO 3
Optimizar e imprimir las hojas electrónicas generadas por Excel.
• Configuración de hojas electrónicas • Manipulación de márgenes • Opciones de página y hoja • Uso de encabezados y pies de página • Impresión de hoja electrónica
INSTITUTO NACIONAL DE APRENDIZAJE Manual de Hoja Electrónica Excel
41
¿Qué es configurar una hoja de cálculo? Es preparar una hoja de cálculo para ser impresa y al mismo tiempo mejorar su presentación 100%. Además es relevante decir que cada hoja es individual para su configuración. Antes de imprimir es conveniente observar la hoja en vista preliminar
para
no
llevarse
ninguna
sorpresa al final.
Aparecerá la siguiente ventana:
Clic al botón Configurar pagina para que se observen las 4 pestañas siguientes: Pagina Margines Encabezado y pie de pagina Hoja
INSTITUTO NACIONAL DE APRENDIZAJE Manual de Hoja Electrónica Excel
42
Las opciones que se manipulan son las siguientes: Pestaña página Algunas funciones que se pueden aplicar en esta ventana son: Cambiar la orientación a una hoja tanto en forma vertical como horizontal. Imprimir datos
una tabla a
un
de
tamaño
personalizado. Elegir el tamaño del papel y la calidad de impresión.
Pestaña Márgenes Algunas funciones que se pueden aplicar en esta ventana son: Cambiar
los
márgenes
superior,
inferior,
izquierda,
derecha,
encabezado
y
pie
de
página de una hoja de cálculo. Centrar la tabla de datos en forma automática por medio de las opciones horizontalmente verticalmente.
y
INSTITUTO NACIONAL DE APRENDIZAJE Manual de Hoja Electrónica Excel
43
Pestaña Encabezado y pie de página Algunas funciones que se pueden aplicar en esta ventana son: Insertar y personalizar el encabezado-pie de página a la hoja de cálculo. Los
cuadros
en
permiten
blanco agregar
encabezados-pies página
tanto
de
a
páginas
pares e impares.
Pestaña Hoja Algunas funciones que se pueden aplicar en esta ventana son: Imprimir
líneas
de
división en la hoja. Imprimir
la
hoja
en
blanco y negro. Imprimir
la
hoja
en
calidad borrador. Agregar las letras de columnas
y
los
números de filas a la hoja al imprimirla. Establecer un área de impresión especifica.
INSTITUTO NACIONAL DE APRENDIZAJE Manual de Hoja Electrónica Excel
Impresión de hoja de cálculo Es el proceso final para enviar un trabajo ya configurado a hojas blancas. Ejecutar los siguientes pasos: 1. Clic al botón Office 2. Clic a Imprimir Luego aparecerá la siguiente ventana
En esta ventana se pueden elegir las siguientes opciones: •
El tipo de impresora y las características personalizadas en Propiedades
•
El intervalo de páginas a imprimir
•
La cantidad de copias a imprimir
•
Las paginas por hoja a imprimir
•
Configurar las Opciones de impresión
44
INSTITUTO NACIONAL DE APRENDIZAJE Manual de Hoja Electrónica Excel
45
CAPITULO 4
Utilizar funciones específicas y bases de datos en la solución de problemas planteados.
•
Descripción de bases de datos
•
Ordenamiento de datos
•
Filtros básicos
•
Filtros avanzados
•
Tablas dinámicas
INSTITUTO NACIONAL DE APRENDIZAJE Manual de Hoja Electrónica Excel
46
¿Qué son las bases de datos en Excel? Las bases de datos en Microsoft Excel están compuestas por campos y registros, se usan para aplicar diversas herramientas del programa con objetivos específicos. También se le conoce como una tabla de datos.
Algunas de las herramientas de Excel que se aplican sobre bases de datos son: Ordenamientos Formulario de datos Subtotales Filtros básicos Filtros avanzados Tablas dinámicas
Ordenamientos de Datos Sobre las listas de datos se puede aplicar la herramienta para ordenar y reordenar de acuerdo a diversos parámetros. El ordenamiento puede ser alfabético, por fechas o numérica, ascendente o descendente y pueden establecerse varios criterios de ordenamiento a la vez agregando niveles.
Proceso para ordenar y reordenar listas Pasos: 1. Seleccionar la tabla 2. Clic a la cinta Datos 3. En la parte central se encuentran 2 botones para ordenamiento rápido de una lista en forma ascendente (A-Z) y descendente (Z-A) según sea su gusto:
INSTITUTO NACIONAL DE APRENDIZAJE Manual de Hoja Electrónica Excel
47
O clic en el botón ordenar para aplicar el ordenamiento de una manera personalizada:
Si agrega niveles puede establecer criterios de ordenamiento diferentes para cada uno, además es posible ordenar de acuerdo a características de formato o personalizar el criterio dentro de un listado de opciones y crear su propia opción.
Filtros Automáticos Sirven para buscar información en una tabla de una manera rápida y sencilla. También se les conoce como Autofiltros. Veamos la siguiente tabla:
Nombre
Apellidos
Telefono
Lugar
Francisco Maria Olga
Jimenez Rojas Hernandez
8376-2117 8395-0523 8765-2314
Santa Ana Heredia Alajuela
Para agregar o quitar filtros a una tabla se deben ejecutar los siguientes pasos: 1. Seleccionar los campos de la tabla (Nombre-Apellidos-Teléfono-Lugar) 2. Clic a la cinta Datos 3. Clic al botón Filtros
INSTITUTO NACIONAL DE APRENDIZAJE Manual de Hoja Electrónica Excel
48
Así debe quedar la tabla:
N om b re
A pe llido s
Te lefon o
Lug ar
F ra nc isc o M a ria O lg a
Jim en ez R oja s H er na nd ez
837 6-2117 839 5-0523 876 5-2314
Sa n ta A na H ered ia A la juela
Ahora en la versión más actual de Excel se agregan en forma automática cuando se aplica un formato a una tabla de datos. Ejemplo:
Nombre
Apellidos
Telefono
Lugar
Francisco Maria Olga
Jimenez Rojas Hernandez
8376-2117 8395-0523 8765-2314
Santa Ana Heredia Alajuela
Si se requieren desactivar los filtros se ejecutan los mismos pasos anteriores.
Búsquedas de registros: Para buscar registros primeramente se debe de tener lista la búsqueda a realizar: Ejemplo: Para buscar los registros cuyos apellidos terminen con la letra Z. Clic a la flecha que apunta hacia abajo del campo Apellidos Luego aparecerá la siguiente ventana:
INSTITUTO NACIONAL DE APRENDIZAJE Manual de Hoja Electrónica Excel
49
Clic en la opción Filtros de texto y luego clic en Filtro Personalizado… De inmediato aparecerá la siguiente ventana: En nuestro caso se debe configurar de la siguiente manera por el criterio a buscar:
El resultado final de la búsqueda sería el siguiente:
Nombre
Apellidos
Teléfono
Lugar
Francisco Olga
Jiménez Hernández
8376-2117 8765-2314
Santa Ana Alajuela
Los filtros Automáticos permiten gran cantidad de posibilidades, sin embargo cuando deseamos filtrar información con un mayor grado de especificidad, se utilizan los filtros avanzados.
Filtros Avanzados Los filtros avanzados tienen cuatro aspectos que se deben tomar en cuenta: -
Rango de lista: Es seleccionar toda la tabla (registros y campos) al mismo tiempo.
-
Rango de criterios: Es seleccionar el criterio que va a tener el reporte.
-
Copiar a otro lugar: Es para activar la opción de copiar a otro lugar.
-
Copiar a: Sirve para elegir las celdas donde
INSTITUTO NACIONAL DE APRENDIZAJE Manual de Hoja Electrónica Excel
50
Veamos la siguiente tabla: Provincia San José Alajuela Cartago Alajuela San José Heredia Heredia Alajuela San José Alajuela
Vendedor Carlos Martín Martín Carlos Pamela Katherine Carlos Martin Pamela Carlos
Tipo de producto Verduras Frutas Frutas Verduras Verduras Frutas Verduras Verduras Frutas Frutas
Descripción Pepinos Mangas Melones Zanahoria Lechugas Manzanas Pepinos Zanahoria Melones Naranjas
Ventas Kg 200 1450 725 500 1260 180 320 1000 195 400
Se debe tener listos los criterios del reporte que se deseamos obtener: Ejemplo: Crear un reporte que muestre el tipo de producto Frutas Aplicar el siguiente proceso a ejecutar: 1. Seleccionar la tabla 2. Clic a la cinta Datos 3. Clic al botón Avanzadas Luego aparece la ventana: En esa ventana se debe elegir el rango de lista, el criterio del reporte y el lugar donde se va a copiar.
Debe existir un criterio para lograr el objetivo
INSTITUTO NACIONAL DE APRENDIZAJE Manual de Hoja Electrónica Excel
51
del reporte. La selección se debe hacer a ambas celdas ya que ese será el resultado final del reporte. Tipo de producto: Frutas
Observe el resultado final del reporte manual:
Provincia Alajuela Cartago Heredia San José Alajuela
Vendedor Martín Martín Katherine Pamela Carlos
Tipo de producto Frutas Frutas Frutas Frutas Frutas
Descripción Mangas Melones Manzanas Melones Naranjas
Ventas Kg 1450 725 180 195 400
Funciones con Bases de Datos Buscarv Sirve para buscar un dato en forma automática en una tabla con solo digitarlo en la celda correspondiente de búsqueda. En nuestro caso seria C14. Veamos la siguiente tabla:
INSTITUTO NACIONAL DE APRENDIZAJE Manual de Hoja Electrónica Excel
Se debe escribir un número de cedula en la celda C14 que tenga la tabla de datos. Ejemplo: 1-911-338 La sintaxis de la función es la siguiente:
Los significados de los argumentos son los siguientes:
El valor buscado es la celda que tiene el dato que se va a buscar en la
tabla. En este caso siempre va hacer C14 que tiene la cedula 1-911-338.
La matriz a buscar es la selección de toda la tabla (campos y registros).
El indicador de columnas es el número de columna donde está el dato a
buscar.
El ordenado se debe escribir siempre el número cero.
Posicionarse en la celda B16 y digitar la siguiente función: =BUSCARV(C14;A4:F10;2;0) Posicionarse en la celda B17 y digitar la siguiente función: =BUSCARV(C14;A5:F11;3;0) Posicionarse en la celda B18 y digitar la siguiente función: =BUSCARV(C14;A4:F10;5;0) Posicionarse en la celda B19 y digitar la siguiente función: =BUSCARV(C14;A4:F10;6;0)
52
INSTITUTO NACIONAL DE APRENDIZAJE Manual de Hoja Electrónica Excel
53
Ahora si se notará claramente la funcionalidad automática de esta función. Digite el número de cedula 3-911-338 para que despliegue los datos en forma rápida que corresponden a esa cedula. DIGITE LA CEDULA: Nombre
3-911-338 Ligia
Apellidos:
Nogales
Lugar
Cartago
Salario:
85000
Bdcontar Sirve para contar las celdas de una columna específica que cumpla con un criterio. Veamos la siguiente tabla con el criterio.
La sintaxis de la función es la siguiente:
INSTITUTO NACIONAL DE APRENDIZAJE Manual de Hoja Electrónica Excel
Los significados de los argumentos son los siguientes: La base de datos es la selección de toda la tabla. El nombre del campo es la posición de la columna a buscar. El criterio es la selección de las celdas B11 y B12. Posicionarse en la celda A12 y digitar la siguiente función: =BDCONTAR(A3:F9;6;B11:B12)
El resultado final es: Bdmax Sirve para obtener el valor máximo de acuerdo a un criterio. Veamos la siguiente tabla con el criterio.
La sintaxis de la función es la siguiente:
54
INSTITUTO NACIONAL DE APRENDIZAJE Manual de Hoja Electrónica Excel
55
Los significados de los argumentos son los siguientes: La base de datos es la selección de toda la tabla. El nombre del campo es la posición de la columna a buscar. El criterio es la selección de las celdas B11 y B12. Posicionarse en la celda A12 y digitar la siguiente función: =BDMAX(A3:F9;6;B11:B12)
El resultado final es:
Tablas Dinámicas Sirven para hacer resúmenes interactivos y dinámicos en una tabla de datos utilizando diversas herramientas para lograr un determinado objetivo. Observe la siguiente tabla:
Nombre
Apellidos
Teléfono
Lugar
Francisco
Jiménez
8376-2117
Santa Ana
María
Rojas
8395-0523
Heredia
Olga
Hernández
8765-2314
Alajuela
Salario 20000 30000 50000
INSTITUTO NACIONAL DE APRENDIZAJE Manual de Hoja Electrónica Excel
56
Proceso Inicial de tabla dinámica 1. Seleccionar toda la tabla 2. Clic a la cinta Insertar 3. Clic al botón Tabla Dinámica Aparecerá la ventana: Seleccionar todo el rango de la tabla y la opción Nueva hoja de cálculo para que la genere ahí.
De inmediato aparecerá en la hoja electrónica nueva la tabla dinámica.
INSTITUTO NACIONAL DE APRENDIZAJE Manual de Hoja Electrónica Excel
57
Clic a la tabla dinámica para activar el panel “Lista de campos de tabla dinámica” que aparece en la parte derecha de la ventana. Observar que aparecen los campos (Nombre, Apellidos, Teléfono, Lugar, Salario). Arrastrar el campo deseado a los cuadros blanco de abajo según sea el objetivo de la tabla a generar:
INSTITUTO NACIONAL DE APRENDIZAJE Manual de Hoja Electrónica Excel
58
En este caso los cuadros deben quedar así:
Finalmente la tabla queda con la siguiente estructura:
Observe que las celdas Todas, rótulos de columna, rótulos de fila aparecen con una flecha que apunta hacia abajo que lo conocemos como Filtros Automáticos o Autofiltros. Al dar clic a cada una de ellas se despliegan diversas opciones. En este caso se da clic al filtro del campo Rótulos de fila. Luego se puede activar o desactivar el nombre deseado para observar los datos individualizados.
INSTITUTO NACIONAL DE APRENDIZAJE Manual de Hoja Electrónica Excel
59
Desactivar los checks María y Olga para mostrar los siguientes resultados: Teléfono
(Todas)
Suma de Salario
Rótulos de columna
Rótulos de fila Francisco Jimenez Total general
Santa Ana
Total general 20000 20000 20000 20000 20000 20000
Por último se da clic al embudo del campo Rótulos de fila para retornar todos los datos de la tabla. Clic a seleccionar todo.
Si se desea manipular la tabla
dinámica se pueden aplicar diferentes herramientas como funciones o gráficos.
INSTITUTO NACIONAL DE APRENDIZAJE Manual de Hoja Electrónica Excel
60
Aplicación de función 1. Clic a una celda 2. Clic al botón “Herramientas tabla dinámica” que aparece en la parte superior de las cintas.
3. Ubicarse en la celda donde está el resultado final 100000. Luego clic al botón Configuración de campo para elegir otra función de Excel. En este caso Max.
Ahora observe el nuevo resultado de la celda que seria 50000 ya que es el valor máximo del rango numérico a totalizar de la tabla. Agregarle un formato de tabla usando la cinta Inicio. Grafico Dinámico Clic a una celda de la tabla dinámica. Clic al botón Herramientas tabla dinámica Clic al botón Grafico dinámico.
INSTITUTO NACIONAL DE APRENDIZAJE Manual de Hoja Electrónica Excel
61
Elegir el tipo de grafico requerido y dar clic al botón aceptar.
Luego mover el grafico a un mejor lugar de la hoja de cálculo.
También se puede variar el diseño del grafico para que el mismo quede mejor presentado. Para ello se debe tener marcado el grafico y en las opciones de la barra Herramientas del grafico dinámico manipular la caja de botones Diseños de grafico.
INSTITUTO NACIONAL DE APRENDIZAJE Manual de Hoja Electrónica Excel
62
Al dar clic al botón Herramientas del grafico dinámico aparecen varias pestañas que se pueden manipular para seguir mejorando la estructura del grafico. Marcar el grafico.
Ejemplos para aplicar: Poner los gráficos en 3D Agregar una tabla de datos Aplicar nuevos formatos a los gráficos Finalmente se puede optimizar el grafico usando la cinta inicio para aplicar tamaños y tipos de letra, colores entre otros formatos.
INSTITUTO NACIONAL DE APRENDIZAJE Manual de Hoja Electrónica Excel
63
CAPITULO 5
Generar
objetos
y
gráficos
con
la
Información de la hoja electrónica.
• Descripción de objetos • Uso de la barra de dibujo • Propiedades de los objetos • Agregando formas • Ingresando fórmulas • Manipulación de gráficos • Tipos de gráficos • Asistente de gráficos
INSTITUTO NACIONAL DE APRENDIZAJE Manual de Hoja Electrónica Excel
64
Barra de Dibujo La barra de dibujo son un conjunto de iconos que sirven para mejorar la apariencia de una hoja electrónica. Se le conocen como formas. Para dibujar formas se deben de aplicar los siguientes pasos: Clic a la cinta Insertar Clic al botón Formas Luego aparecerán todas las formas que se pueden incorporar a la hoja de cálculo. Seleccione la forma deseada y haga cklik en la hoja de trabajo arrastrando para definir el tamaño deseado.
INSTITUTO NACIONAL DE APRENDIZAJE Manual de Hoja Electrónica Excel
Para cambiar las propiedades de una forma se debe marcar la forma y luego dar doble clic sobre ella.
WORDART También se pueden incluir diversos tipos de rotulación utilizando WordArt: Pasos: 1. Clic a la cinta Insertar 2. Clic en el botón WordArt Luego aparecerán los estilos de rótulos. Dar clic a un estilo.
65
INSTITUTO NACIONAL DE APRENDIZAJE Manual de Hoja Electrónica Excel
66
Escriba el texto del rotulo:
Ejemplo:
Luego puede configurar a su gusto el rótulo
Imágenes Insertar imágenes prediseñadas o de archivo. En una hoja de cálculo se pueden agregar imágenes de los dos tipos:
Prediseñada: -
Clic a la cinta Insertar
-
Clic al botón Imágenes prediseñadas
En la parte derecha aparece una ventana. Clic en Organizar clips…
INSTITUTO NACIONAL DE APRENDIZAJE Manual de Hoja Electrónica Excel
67
Doble clic a la carpeta Colecciones de Office y luego clic en la categoría deseada para que aparezcan imágenes relacionadas al nombre de la categoría. Clic derecho sobre la imagen y clic en Copiar.
Para seguir usando la ventana e insertando imágenes debe minimizarla. Estando en la hoja de cálculo dar clic derecho y clic en Pegar.
Al final queda en la hoja electrónica así:
Desde Archivo: Clic a la cinta Insertar Clic al botón Imagen
INSTITUTO NACIONAL DE APRENDIZAJE Manual de Hoja Electrónica Excel
68
Aparece la siguiente ventana. Buscar la imagen de alguna carpeta en el disco duro, seleccionarla con clic y luego clic al botón Insertar.
Al final queda en la hoja electrónica así:
Gráficos Son representaciones gráficas de las estadísticas de acuerdo a los datos de una tabla o datos extraídos de la misma. Existen varios tipos de gráficos: Columnas Líneas Circular Barras Área Otros Un buen grafico es aquel que tiene una buena apariencia y sobre todo el que represente la información con solo observarlo.
INSTITUTO NACIONAL DE APRENDIZAJE Manual de Hoja Electrónica Excel
69
Partes de un gráfico Las principales partes de un grafico son las siguientes:
Terminología de gráficos Antes de ingresar a la creación de gráficos, se deben conocer términos importantes que se involucran en la elaboración de los mismos.
Serie de datos: Representan los valores que están graficados
INSTITUTO NACIONAL DE APRENDIZAJE Manual de Hoja Electrónica Excel
70
Ejes: Línea de referencia que toma como base el plano cartesiano. El eje X se presenta en dirección horizontal, y el eje Y se presenta en sentido vertical, el eje Z aparece sólo en los gráficos de tres dimensiones (es una línea vertical).
Marcadores: son los que representan las barras del gráfico, normalmente tienen características que permiten diferenciarlos unos de otros, tales como el color. Los marcadores pueden estar representados por barras, columnas, sectores o líneas entre otros.
Categorías: Identifican los encabezados de los datos de la hoja que se encuentran representados sobre el eje X.
Leyendas: Son símbolos utilizados parar identificar el color de marcador de datos. Muestran el nombre de las series que corresponden a cada marcador.
Creación de gráficos Para crear un gráfico primero se debe cumplir los siguientes aspectos:
Una tabla de datos a graficar: Nombre
Apellidos
Edad
Lugar
Frank Carlos Juan Karen Karla
Rojas Ledezma Nogales Arroyo Perez
32 30 25 28 35
Heredia Alajuela Heredia Heredia Puntarenas
Debe contener datos graficables debidamente categorizados Identificar el tipo de información que se desea representar y determinar la forma cómo se quiere representar ( si son datos absolutos o relativos, si desea realizar una comparación entre valores o representar la evolución en un período de tiempo).
INSTITUTO NACIONAL DE APRENDIZAJE Manual de Hoja Electrónica Excel
71
Plantear el caso para crear el grafico respectivo:
Ejemplo: Crear un grafico que permita representar las personas que tienen edades mayores o iguales a 30.
Quizá si se grafica toda la tabla el objetivo planteado no sale bien, por la cantidad de datos que tiene innecesarios, por eso es conveniente hacer una tabla de extracción de datos para que la estadística sea la ideal.
Nombre
Apellidos
Edad
Frank Carlos Karla
Rojas Ledezma Pérez
32 30 35
Observar que esta tabla extraída contiene solo los datos que pide el caso anterior.
Proceso creación de grafico
Seleccionar la tabla a graficar Clic a la cinta Insertar Observar que aparecen varios botones de los tipos de gráficos que tiene el programa:
Clic al botón Columna para elegir el tipo de grafico columna a crear:
INSTITUTO NACIONAL DE APRENDIZAJE Manual de Hoja Electr贸nica Excel
Clic al primer bot贸n de columna 3D
Luego aparecer谩 el grafico listo:
72
INSTITUTO NACIONAL DE APRENDIZAJE Manual de Hoja Electrónica Excel
73
El gráfico se puede manipular manipulando los siguientes botones cuando se marca el grafico: El diseño gráfico El estilo del gráfico Los datos estadísticos Mover el gráfico a otra hoja Luego de aplicar las herramientas anteriores observe que el grafico tiene una apariencia más profesional.
Sin embargo, todavía se pueden dar unos acabados como son: Ponerle un fondo de color Ponerle un fondo de imagen
Para lograr lo anterior debemos de marcar el
Aplicar un giro en 3D
grafico y dar clic derecho sobre el mismo para elegir la opción Formato del área grafico.
INSTITUTO NACIONAL DE APRENDIZAJE Manual de Hoja Electr贸nica Excel
74
En esta ventana se pueden seleccionar otros formatos para mejorar la apariencia del gr谩fico.
INSTITUTO NACIONAL DE APRENDIZAJE Manual de Hoja Electrónica Excel
75
CAPITULO 6
Automatizar procesos mediante las macros en la hoja electrónica.
• Descripción de macros • Grabación de macros • Ejecución de macros • Eliminación de macros
INSTITUTO NACIONAL DE APRENDIZAJE Manual de Hoja Electrónica Excel
76
¿Qué son las Macros? Las macros son rutinas que permiten agilizar varios procesos en uno solo. La automatización se hace en forma rápida, queda guardada en la computadora de modo que luego es fácil accederla por medio de la combinación de teclas o la asignación a un ícono.
Proceso: Clic a la cinta Vista Clic al botón Macros
Clic a la opción Grabar macro… Configurar la macro de esta manera: Escribir un nombre a la macro, aplicar una función del teclado y una breve descripción de la función que va a tener la macro. Clic al botón Aceptar.
INSTITUTO NACIONAL DE APRENDIZAJE Manual de Hoja Electrónica Excel
77
En estos momentos se deben aplicar los comandos o herramientas que va a tener grabada la macro. Tenga en cuenta que todos los procesos que realice quedarán grabados hasta que detenga la grabación.
Para detener la grabación clic al botón Macros y luego clic en Detener grabación.
Ejecuctar una macro Para ejecutar o probar una macro se debe aplicar los siguientes pasos: 1. Clic a la cinta Vista 2. Clic en el botón Macro… 3. Marcar el nombre de la marco y dar clic al botón Ejecutar. Detener una macro Clic al botón Detener macro…
Otra manera es dar clic a la cinta Vista y luego al botón Macro. Elegir la opción Detener. Eliminar una macro Marcar el nombre de la macro y dar clic al botón Eliminar. 1. Clic a la cinta Vista 2. Clic en el botón Macro… 3. Marcar el nombre de la marco y dar clic al botón Eliminar.