Descripción completa
Views 251 Downloads 68 File size 10MB
Introducción al Excel 2013 El objetivo de esta sesión de aprendizaje es el de conocer el entorno y los fundamentos de manejo de la hoja de cálculo, ingresar correctamente los diferentes tipos de datos, así como aplicar los formatos de celda.
Competencias a desarrollar: Conoce el entorno de Excel. Crea un libro y lo organiza en hojas Selecciona celdas adyacentes y dispersas Inserta correctamente los tipos de datos Se desplaza correctamente por la hoja Aplica formatos de celda
Sesión – 01
MOSM - Ing. Edilberto Chávez Fernández
3
MICROSOFT EXCEL 2013 Excel es un programa para analizar, compartir y administrar información para la toma de decisiones, el cual consiste en una hoja de cálculo, utilizada para realizar fórmulas matemáticas y cálculos aritméticos, o bases de datos. Es un componente de la Suite Office que genera archivos llamados Libros compuestos por objetos de cálculo llamados Hojas. Una hoja de cálculo es muy versátil e indispensable en las tareas de oficina porque automatiza los procesos, analiza visualmente el comportamiento de los datos, manipula grandes volúmenes de información y permite desarrollar aplicaciones personalizadas para propósitos especiales.
Ventajas: •
Aumentar la productividad individual y maximizar el impacto personal en los resultados empresariales.
•
Simplificar el trabajo en equipo con independencia del tiempo y del lugar.
•
Optimizar los procesos diarios y la administración de contenido empresarial.
•
Generalizar y mejorar el conocimiento de la actividad empresarial en toda la organización.
Funcionalidad de Microsoft Excel 2013 * Hoja Electrónica de cálculo * Elaboración de gráficos * Gestión de base de datos
Sesión – 01
MOSM - Ing. Edilberto Chávez Fernández
4
INICIAR SESION EN MS-EXCEL 2013 Iniciar / Programas / Microsoft Office / Microsoft Excel 2010
ENTORNO Su interfaz está configurado de acuerdo a la imagen siguiente: Barra acceso rápido
Cinta de opciones
Barra de fórmulas Celda activa
Hoja de cálculo
Hojas
Sesión – 01
Barra de estado
Control deslizante del zoom
MOSM - Ing. Edilberto Chávez Fernández
5
LA CINTA DE OPCIONES La Cinta de Opciones, está organizado en Grupos y estos contienen los diferentes comandos y herramientas Ficha
Ficha Archivo Archivo
Comando
Grupos 1. Fichas: Hay siete en la parte superior y cada una de ellas representa una de las tareas básicas que se hacen en Excel.
2. Grupos: Cada ficha tiene grupos que muestran elementos relacionados entre sí. 3. Comandos: Son los diferentes comando y herramientas correspondientes a cada grupo
ADMINISTRACIÓN DE LIBROS 1.
El primer libro que abre se denomina Libro1. Este nombre aparece en la barra de título situada en la parte superior de la ventana hasta que guarda el libro con su propio nombre.
2.
Fichas de hojas en la parte inferior de la ventana del libro. Cuando inicia Excel, abre un archivo que se denomina libro. Cada libro nuevo tiene tres hojas por defecto. Cada hoja tiene un nombre en su ficha situada en la parte inferior izquierda de la ventana del libro: Hoja1, Hoja2 y Hoja3. Para ver una hoja de cálculo, tiene que hacer clic en la ficha correspondiente.
Sesión – 01
MOSM - Ing. Edilberto Chávez Fernández
6
1
2
ELEMENTOS DEL ENTORNO DE UNA HOJA DE CÁLCULO La hoja de cálculo está formada por columnas y filas, la intersección forma las celdas.
1. Las columnas se extienden de arriba abajo en la hoja de cálculo, es decir, verticalmente. 2. Las filas se extienden de izquierda a derecha en la hoja de cálculo, es decir, horizontalmente. 3. Las celdas Es la intersección de filas y columnas, es el espacio donde se insertan los datos.
1
Columna
2 Fila
Sesión – 01
Celda Activa
3
MOSM - Ing. Edilberto Chávez Fernández
7
Columnas Tiene un título alfabético en la parte superior. Las 1ras 26 columnas tienen las letras desde A hasta Z. Cada hoja de cálculo contiene 16,384 columnas en total, por lo que, a continuación de la Z, las letras comienzan de nuevo en parejas, es decir, de AA a AZ …. hasta XFD. Formando 1.048,576 filas Filas También tiene un título. Los títulos de las filas son números, de 1 a 1, 048,576. Los títulos alfabéticos de las columnas junto con los títulos numéricos de las filas le indican en qué celda de la hoja de cálculo está situado cuando hace clic en una de ellas. Los títulos se combinan para formar la dirección de celda, también denominada referencia de celda.
Celdas Es la intersección de la columna y la fila, son los espacios donde están los datos. Cuando abre un libro nuevo, la primera celda es la celda activa. Tiene un contorno de color negro. Nombre de celda
Celda activa: B2
CELDA ACTIVA Cuando selecciona una celda, se convierte en la celda activa. Es la celda que representa el cursor en la hoja sobre las cuales se aplican las acciones de edición y formatos. Se distingue por los bordes y el botón de relleno. Cada elemento de la celda activa realiza diversas tareas sobre los datos que contenga.
Sesión – 01
MOSM - Ing. Edilberto Chávez Fernández
8
Nota: Al ubicarse sobre cada elemento de la celda activa, el puntero toma las formas señaladas en la figura
Acciones rápidas
Borde
Área
MOVER DATOS
Arrastrar el BORDE hacia otra posición de la hoja
COPIAR DATOS
Pulsar CTRL.+ Arrastrar el BORDE hasta posición deseada
SELECCIONAR DATOS
Arrastra sobre las AREAS de celdas deseadas
Relleno RELLENAR DATOS
El botón de relleno produce una acción distinta dependiendo del valor contenido en la celda: VALOR Texto, número, celda con formato
ACCION DE RELLENO Repite esos valores
Fecha
Reproduce la serie cronológica
Serie combinada o Serie de
Reproduce la serie
lista
cíclicamente
Rango numérico
Proyecta dichos valores Copia la estructura y cambia
Fórmula
las referencias (Vertical: Fila/ Horizontal: Columnas)
SELECCIONAR CELDAS Toda acción o formato afectará a las celdas que estén seleccionadas. Cuando selecciona celdas mientras edita una fórmula se generan sus referencias individuales o grupales locales o remotas. Ejemplo: =B4, =SUMA(B5:B8).
Sesión – 01
MOSM - Ing. Edilberto Chávez Fernández
9
Formas básicas de selección Una celda: Mouse
: Un clic en celda deseada
Teclado
: Pulsar teclas de dirección
Lista de nombres
: Escribir la columna y número de fila
Ejercicio: Seleccionar B2
Celdas adyacentes: Mouse
: Arrastrar entre las áreas de las celdas deseadas
Teclado
: Pulsar SHIFT sostenido y pulsar teclas de dirección
Lista de nombres
: Escribir Referencia inicial y final separada por “:”
Ejercicio: Seleccionar desde B2 hasta C5
Celdas dispersas: Mouse
: Seleccionar una celda o bloque adyacente inicial
Teclado
: Pulsarla tecla Control (CTRL) sostenido (sin soltar) Arrastrar los demás bloques de celdas.
Lista de nombres
: Escribir la referencia de celda o rangos adyacentes separado por “,” (o “;” según la configuración)
Sesión – 01
MOSM - Ing. Edilberto Chávez Fernández
10
Ejercicio: Seleccionar tres bloques dispersos de celdas adyacentes
DESPLAZARSE DENTRO DE UNA HOJA DE CÁLCULO Formas: 1. Utilizando las barras de desplazamiento y el clic del mouse
Nos desplazamos con las barras de desplazamiento dentro de la hoja, y hacemos clic en la celda P3
2. Utilizando el cuadro de nombres
3. Utilizando el teclado
Sesión – 01
MOSM - Ing. Edilberto Chávez Fernández
11
EDICIÓN Y CONTROL DE HOJAS DE CÁLCULO Cambiar el nombre de una hoja de cálculo 1.
Haga clic con el botón secundario del mouse (ratón) en la Hoja1 situada en la parte inferior de la ventana y, a continuación, haga clic en Cambiar nombre. Clic derecho
2.
Escriba el nuevo nombre: Ejercicio y, a continuación, presione ENTRAR.
Insertar una hoja de cálculo Hay 2 formas: 1. Desde la cinta de opciones, en la ficha Inicio, en el grupo Celdas, haga clic en la flecha situada al lado de Insertar y, luego, en Insertar hoja. Se insertará una nueva hoja de cálculo.
2. Desde las fichas de Hojas de cálculo: Clic en insertar hoja
Eliminar una hoja de cálculo
Sesión – 01
MOSM - Ing. Edilberto Chávez Fernández
12
Haga clic derecho en la ficha Hoja1 situada en la parte inferior de la hoja de cálculo, luego hacer clic en Eliminar. Se eliminará la Hoja1. Opciones del clic derecho que permiten, insertar, eliminar y cambiar nombre a la hoja de cálculo
Clic derecho
Asignar nombres a las celdas Se puede hacer de 2 formas 1. Usando el comando Asignar nombre de la Ficha Formulas
Escribimos el nombre del rango de celdas
Referencia a la Hoja 2, celda B1
2. Utilizando el Cuadro de Nombres Coloque el punto de inserción en el Cuadro de nombres situado en la esquina superior izquierda de la ventana justo encima de la celda A1. En el cuadro se mostrará "B1".
Sesión – 01
MOSM - Ing. Edilberto Chávez Fernández
13
Escriba INTERES en el Cuadro de nombres y, a continuación, presione ENTRAR. Ahora, la celda B1 cambiará con INTERES
TIPOS DE DATOS 1.
Texto:
Cadena de caracteres (Palabras, títulos, nombres,...)
Ejercicio:
2.
Valores: • Números: Enteros, decimales, moneda, científica, fracción, porcentaje • Fechas: Fecha y hora Ejercicio:
Hora
Fecha
Sesión – 01
Entero
Decimal
Moneda
MOSM - Ing. Edilberto Chávez Fernández
14
3.
Fórmulas: Son expresiones que devuelven un resultado. Las fórmulas se pueden aplicar sobre datos de cualquiera de los 3 tipos. Ejercicio:
Fórmula del IMC
FORMATO DE CELDAS Es un conjunto de características que afectan la fuente, alineación, colores de las celdas. Optimiza la forma de presentar los datos sin alterar su verdadero valor. Pasos: •
Activar la celda deseada
•
Seleccione la ficha Inicio/ grupo Fuente/botón de Cuadro de dialogo.
•
Seleccionar ficha adecuada (Número, Alineación, Fuente, Bordes, Relleno, Proteger).
•
Asignar los valores adecuados.
APLICANDO FORMATOS A. Ficha Número: Esta ficha. Nos permite dar formatos correspondientes a Número, Fecha, Moneda, porcentaje, personalizada y otros. El formato de Número se puede aplicar desde: La ventana de Formato o desde el Grupo Número en la Cinta de Opciones, Grupo Número
Sesión – 01
MOSM - Ing. Edilberto Chávez Fernández
15
Ejercicio: Aplicando formatos de Número, bordes y relleno a los datos ingresados.
B. Ficha Alineación: Afecta la orientación del valor con respecto a los límites de la celda que lo contiene. Ejercicio: Aplicar formatos de alineación a los siguientes datos
1. Aplicando formatos desde la Ficha Alineación de la ventana Formato de celdas
Aplicando la Alineación y orientación de texto
2. Aplicando desde el comando Orientación de la Cinta de Opciones
Sesión – 01
MOSM - Ing. Edilberto Chávez Fernández
16
Comando Orientación
Resultado:
C. Ficha Fuente: Afecta la naturaleza y el estilo del tipo de letra Puede aplicarse de 2 formas: 1. Desde los comandos del Grupo Fuente en la Ficha Inicio
2. Utilizando la ventana Formato de Celda
Sesión – 01
MOSM - Ing. Edilberto Chávez Fernández
17
Combinar y Centrar:
Une las celdas seleccionadas en una celda de mayor tamaño y centra el contenido en la nueva celda.
Aplicando formato de bordes 1. Seleccionar un rango de celdas 2. Ir a la ficha Inicio 3. En el grupo Fuente, seleccione el borde adecuado Puede las
usar paletas
para modificar color
Eliminando un formato 1. Seleccione la celda o rango a eliminar el formato 2. Ir a la Ficha Inicio, Modificar, Borrar formatos.
Sesión – 01
MOSM - Ing. Edilberto Chávez Fernández
18
FORMATOS PERSONALIZADOS Formatos fecha y hora: •
Para escribir una fecha debe utilizar una barra diagonal o un guión para separar las partes: 16/7/2010 o 16-julio-2010. Excel reconocerá esto como una fecha.
•
Si necesita escribir una hora, escriba los números, un espacio y "a" o "p": por ejemplo, 9:00p. Si sólo pone el número, Excel reconoce una hora y la escribe como a.m.
Formato personalizado de fechas
SÍMBOLO
SIGNIFICADO
d
Día en uno o dos dígitos (1 – 21)
dd
Día en dos dígitos (01 – 21)
ddd
Día de la semana con tres letras (lun - mar - mie - jue - vie - sab - dom)
dddd
Día de la semana completo (lunes-martes-miércoles-jueves-viernes)
m mm mmm aa
Mes en uno o dos dígitos (2 – 12) Mes en dos dígitos (02 – 12) Mes con tres letras (ene-feb-mar-abr-may-jun-jul-ago-sep-oct-nov-dic) Año con dos dígitos (08-09-10)
Ejemplos:
Formatos numéricos: Excel alinea los números al lado derecho de las celdas. Ejemplos:
Sesión – 01
MOSM - Ing. Edilberto Chávez Fernández
19
Formato personalizado de números Ejercicio: Acompañar un texto a un número sin convertirlo en texto. Visualiza la palabra SI cuando el valor sea 1 y NO si es 0. Datos Iniciales
=HOY()
=SUMA(C6:C9)
En
la
celda
E3
En la celda C3
En la celda C6:C9 En la celda D6:D9
Sesión – 01
MOSM - Ing. Edilberto Chávez Fernández
20
Datos Finales
Ejercicios: Al escribir este formato al rango C3:C7, este se verá en azul los positivos y el rojo los negativos [Azul]#, ##0.00; [Rojo] (#, ##0.00)
Formato con ceros y textos [Azul]#,##0.00;[Rojo](#,##0.00);0;”Error” Columna D con formato personalizado En el ejemplo, los números positivos saldrán en color Azul y con dos decimales Los negativos en color rojo entre paréntesis y con dos decimales Si el valor de la celda es Cero, se mostrará un solo dígito En caso que el contenido de la celda sea un texto se mostrará la palabra Error
En este ejemplo sólo se especifica 3 secciones incluyendo la sección para el cero que al no tener ningún código, no se muestra. 00;00; Formato personalizado en la columna D 00;00;
Sesión – 01
MOSM - Ing. Edilberto Chávez Fernández
21
Los números positivos se verán con 2 dígitos Los negativos no se mostraran El punto y coma al final sin especificar el formato de Ceros, logra que el cero no se visualice
Sesión – 01
MOSM - Ing. Edilberto Chávez Fernández
22
Borrar Formato de Número 1. Seleccione la celda o rango de celdas cuyo formato desea borrar. 2. En la ficha Número, elija la categoría General y aceptar Copiar Formatos
:
La forma más sencilla de copiar los formatos aplicados a una celda es a través del botón Copiar Formato. Así, usted no tendrá que aplicar nuevamente el formato a cada uno de los rangos de su tabla.
Formas rápidas de escribir datos A continuación se muestran dos funciones ahorradoras de tiempo para escribir datos: •
Autorrellenar: Escriba los meses del año, los días de la semana, múltiplos de 2 o de 3, u otros datos de una serie. Escriba una o más entradas y, a continuación, se
extenderá la serie.
•
Autocompletar
Si las primeras letras que escribe en una celda coinciden con las de una
entrada que ya ha realizado en esa columna, Excel escribirá automáticamente los caracteres restantes. Sólo tiene que presionar ENTRAR cuando vea que se han agregado. Esto funciona para texto o para texto con números. No funciona sólo para números, ni para fechas u horas.
Sesión – 01
MOSM - Ing. Edilberto Chávez Fernández
23
Referencias y Fórmulas El objetivo de esta sesión de aprendizaje es el aprender a efectuar todo tipo de cálculos, utilizando las fórmulas, haciendo referencia a las celdas de la misma hoja de cálculo, o de otros libros.
Competencias a desarrollar: Identifica las referencias relativas y absolutas. Aplica correctamente los diferentes operadores aritméticos Asigna nombre a los rangos de celda Construye y aplica correctamente las fórmulas Aprende a proteger elementos
Sesión – 01
MOSM - Ing. Edilberto Chávez Fernández
24
REFERENCIAS Y FORMULAS REFERENCIAS Es una expresión que identifica la ubicación exacta de un valor en la hoja de cálculo de cualquier libro abierto o cerrado creado en Excel. Son utilizados para automatizar los cálculos ya que al referirse a la posición y no al valor, cualquier cambio en el valor actualiza el resultado.
UTILIZANDO REFERENCIAS DE CELDAS Referencias de celdas A3
Hacen referencia a los valores de: La celda de la columna A y la fila 3
B6,B9
La celda B6 y la celda B9
D4:D9
El rango de celdas de la columna D desde la fila 4 a la 9
C12:E12 F4:H9
El rango de celas de la fila 12 desde la columna C a la E El rango de celdas desde la columna F a la H y desde la fila 4 a la 9
Nota: Se puede combinar referencias de bloques adyacentes y/o dispersas indistintamente Ejercicio 01: Realizar un ejemplo para cada caso expuesto.
Sesión – 01
MOSM - Ing. Edilberto Chávez Fernández
25
TIPOS DE REFERENCIAS Existen 2 tipos de referencias: Relativas y Absolutas Las referencias relativas cambian cuando se copian. =C4*$D$9 se copia de una fila en otra, las referencias relativas cambian de C4 a C5 y a C6. Las referencias absolutas permanecen iguales cuando se copian. =C4*$D$9 se copia de una fila en otra, la referencia de celdas absoluta permanece como $D$9.
Referencias Relativas: Las referencias de celdas relativas de una fórmula cambian automáticamente cuando la fórmula se copia en toda una columna o en toda una fila (con arrastre desde el controlador de relleno). Ejercicio: Calcular la columna Puntaje
Nota: Observa como las celdas de la fórmula inicial =B2+C2, cambian su posición al calcular el puntaje del resto de alumnos con el copiado de formulas.
Referencias Absolutas: Una referencia de celdas absoluta es fija. Las referencias absolutas no cambian al copiar una fórmula de una celda en otra. Las referencias absolutas tienen signos de dólar ($) como: $D$9.
Sesión – 01
MOSM - Ing. Edilberto Chávez Fernández
26
Nota: Observa como una de las celdas de la fórmula =B4*$B$1 mantiene su posición ($B$1) ya que es la misma celda referencial que deben usar todos los docentes para calcular su sueldo.
Ejercicio: Se tiene un paquete con cupones de descuento que ofrecen el 7% de descuento en el alquiler de vídeos, películas y CD. Se desea saber cuánto puede ahorrar al mes si utiliza los descuentos Solución: •
Escribir la tasa de descuento en la celda D9
•
Escribir una fórmula en la celda D4, empezando por =C4*, después escriba $D para realizar una referencia absoluta a la columna D, y $9 para realizar una referencia absoluta a la fila 9. Esta fórmula se multiplica por el valor de la celda C4.
•
Copie la fórmula desde la celda D4 a D6 desde el controlador de relleno
. Cuando la
fórmula se copia, la referencia de celdas relativa cambia de C4 a C6, mientras que la referencia absoluta al descuento de D9 no cambia; permanece como $D$9 en todas las filas donde se copia.
AMBITO DE LAS REFERENCIAS A. Referencias de celdas en la misma hoja de cálculo: Hace referencia a celdas que se encuentran en la misma hoja de cálculo. Ejercicio 01: Multiplicar 2 valores (B2 *C2) 1. Seleccione una celda o un rango de celdas de la misma hoja de cálculo (C1). 2. Dentro de la celda seleccionada escriba = (signo de igual)
Sesión – 01
MOSM - Ing. Edilberto Chávez Fernández
27
3. Seleccione las celdas que van a intervenir en la operación (B3 * C3) 4. Las referencias de celda y los bordes que rodean las celdas correspondientes tienen colores asignados para facilitar el trabajo con ellas.
La primera referencia de celda es B3, el color azul y el rango de celda tiene un borde azul con esquinas cuadradas. La segunda referencia de celda es C3, el color verde y el rango de celda tiene un borde verde con esquinas cuadradas. Ejercicio 02: Calcular el total vendido Para encontrar el Total, se hace referencia a la celda B2 (cantidad) y a la celda C2 (precio)
B. Referencias de celdas desde otra hoja de cálculo: Se puede hacer referencia a celdas que se encuentran en otras hojas de cálculo. La referencia de celda va precedida del nombre de la hoja de cálculo seguido de un signo de exclamación (!). Por ejemplo, la función de la hoja de cálculo PROMEDIO calcula el valor promedio del rango B1:B10 en la hoja de cálculo denominada Marketing del mismo libro.
Sesión – 01
MOSM - Ing. Edilberto Chávez Fernández
28
Se refiere a la hoja de cálculo Marketing Se refiere al rango de celdas entre B1 y B10, ambas incluidas Separa la referencia de hoja de cálculo de la referencia del rango de celda
Sesión – 01
MOSM - Ing. Edilberto Chávez Fernández
29
Ejercicio: En la Hoja Resumen, calcular las ventas Totales, tomado los datos de las hojas Enero y Febrero respectivamente.
=ENERO!B7+FEBRERO!B7
C. Referencias de celdas externas o desde otro libro: Cuando el libro de origen está abierto, la referencia externa incluye el nombre del libro entre corchetes, seguido del nombre de la hoja de cálculo, un signo de exclamación (!) y las celdas de las que depende la fórmula. Ejercicio: Los datos de las celdas B3 y B4 de la Hoja Balances del Libro2, los obtiene del Libro1, de las hojas Ingresos y Egresos respectivamente.
Sesión – 01
Libro2.xlsx
MOSM - Ing. Edilberto Chávez Fernández
30
=[Libro1.xlsx]Egresos!$B$7
Libro1.xlsx
Libro1.xlsx
NOMBRES DE CELDAS Facilitan la ubicación de referencias locales y externas en los Libros de Excel.
Crear nombres de celdas: Forma 1: 1. Seleccionar un rango deseado (A1:B5) 2. En el cuadro de nombres, escribir un nombre válido (sin espacios): Tasas Interes 3. Pulsar ENTER para aceptar el nombre
Ejercicio: Asignar el nombre Tasas Interés a los datos de las celdas A1:B5
Forma 2: 1. En la ficha Fórmulas / grupo Nombres definidos, haga clic en Asignar nombre
Sesión – 01
MOSM - Ing. Edilberto Chávez Fernández
31
2. En el cuadro de diálogo Nombre nuevo, escriba el Nombre que desee utilizar para la referencia. Los nombres pueden tener una longitud máxima de 255 caracteres. 3. En el mismo cuadro, escriba el nombre definido que desee crear (Comentario). 4. Para especificar el ámbito del nombre, en el cuadro de lista desplegable Ámbito, seleccione Libro o el nombre de una hoja de cálculo del libro.
Administrar nombres de celdas: 1. Seleccione la ficha Formulas/grupo Nombres definidos/botón Administrador de nombres. 2. Puede usar el botón: Nuevo (para crear un nuevo nombre de celdas), Editar (para modificar el nombre de celda seleccionado) y Eliminar (para borrar el nombre de celdas seleccionado).
Ejercicio: Eliminar el nombre de celdas: Tasas_Interes 1. Ingrese al Administrador de nombres
Sesión – 01
MOSM - Ing. Edilberto Chávez Fernández
32
2. Seleccione el nombre de celdas a borrar y haga clic en el botón Eliminar.
Sesión – 01
MOSM - Ing. Edilberto Chávez Fernández
33
FÓRMULAS Las fórmulas son ecuaciones que efectúan cálculos con los valores de las respectivas celdas dentro de la hoja de cálculo. Una fórmula comienza por un signo igual (=). Por ejemplo, la siguiente fórmula divide al resultado de 10 elevado al cuadrado y le suma 20 al resultado. = 20+(10^2)/2
Características básicas de fórmulas •
Comienzan con el signo igual (=)
•
Combina referencias, operadores, constantes y funciones
•
Muestran un resultado
Ejercicio: =SUMA (B10:B14)*10% + SI (Y (C2>12, C2, (signo mayor que)
Mayor que
A1>B1
< (signo menor que)
Menor que
A1= (signo mayor o igual que)
Mayor o igual que
A1>=B1