modulo 3 - ms excel

12
Universidad Católica Santa María SEDELSUR CURSO BÁSICO DE INFORMÁTICA MÓDULO III MS EXCEL Docente: José Luis Ponce Segura 1 de 12 e-mail: [email protected] Cel. 952636911 www.redtacna.net GENERALIDADES MS EXCEL.- Microsoft Excel es una potente herramienta de hoja electrónica que permite la creación de hojas de cálculo, gráficos, base de datos y macros de forma rápida y sencilla. Libro de trabajo.- Es aquel archivo en que se trabaja y almacenan datos. Todo libro de trabajo tiene por defecto la extensión XLS. Cada libro de trabajo está constituido por hojas, que se pueden agregar, quitar o cambiar de lugar. Hoja de cálculo.- Se compone de columnas y filas. Cada columna está identificada con un nombre compuesto por una o dos letras, de la siguiente forma: A, B, C,... Z, AA, AB,... AZ, BA, BB,... IV. En total 256 columnas. Cada fila está identificada con un número comenzando por el 1 y finalizando en 65.536. Celda.- La intersección de una fila con una columna se define como celda. La celda es el elemento básico de información en una hoja de cálculo. Cada celda se identifica por su dirección la cual se forma con el nombre de la columna seguido por el nombre de la fila. Ej. A1, IV234. Rango.- Es un conjunto de celdas adyacentes que forman un rectángulo y se lo referencia mencionando la celda que está en la esquina superior izquierda y luego la que está en la esquina inferior derecha separadas por dos puntos. INGRESAR A EXCEL Haga clic en el botón [Inicio], luego en [Programas], luego en [Microsoft Office], finalmente en [Microsoft Office Excel 2007], a continuación veremos el entorno de Excel: Unidad I Objetivos: Crear y editar hojas de cálculo Dar formato a las hojas de cálculo. Referencias Relativas, Absolutas, Mixtas. Utilizar operadores y funciones básicas.

Upload: jlponcesg

Post on 28-Jun-2015

501 views

Category:

Documents


9 download

TRANSCRIPT

Page 1: Modulo 3 - MS Excel

Universidad Católica Santa María SEDELSUR

CURSO BÁSICO DE INFORMÁTICA MÓDULO III – MS EXCEL

Docente: José Luis Ponce Segura 1 de 12 e-mail: [email protected] Cel. 952636911 www.redtacna.net

GENERALIDADES

MS EXCEL.- Microsoft Excel es una potente herramienta de hoja electrónica que permite la creación de

hojas de cálculo, gráficos, base de datos y macros de forma rápida y sencilla.

Libro de trabajo.- Es aquel archivo en que se trabaja y almacenan datos. Todo libro de trabajo tiene por

defecto la extensión XLS. Cada libro de trabajo está constituido por hojas, que se pueden agregar, quitar o cambiar de lugar.

Hoja de cálculo.- Se compone de columnas y filas. Cada columna está identificada con un nombre

compuesto por una o dos letras, de la siguiente forma: A, B, C,... Z, AA, AB,... AZ, BA, BB,... IV. En total

256 columnas. Cada fila está identificada con un número comenzando por el 1 y finalizando en 65.536. Celda.- La intersección de una fila con una columna se define como celda. La celda es el elemento básico

de información en una hoja de cálculo. Cada celda se identifica por su dirección la cual se forma con el

nombre de la columna seguido por el nombre de la fila. Ej. A1, IV234. Rango.- Es un conjunto de celdas adyacentes que forman un rectángulo y se lo referencia mencionando

la celda que está en la esquina superior izquierda y luego la que está en la esquina inferior derecha

separadas por dos puntos.

INGRESAR A EXCEL Haga clic en el botón [Inicio], luego en [Programas], luego en [Microsoft Office], finalmente en

[Microsoft Office Excel 2007], a continuación veremos el entorno de Excel:

Unidad I

Objetivos:

Crear y editar hojas de cálculo

Dar formato a las hojas de cálculo.

Referencias Relativas, Absolutas, Mixtas.

Utilizar operadores y funciones básicas.

Page 2: Modulo 3 - MS Excel

Universidad Católica Santa María SEDELSUR

CURSO BÁSICO DE INFORMÁTICA MÓDULO III – MS EXCEL

Docente: José Luis Ponce Segura 2 de 12 e-mail: [email protected] Cel. 952636911 www.redtacna.net

En la que se distinguen las siguientes partes:

1. Barra de Inicio Rápido (Nueva característica en Office 2007). 2. Barra de Título de la ventana de Excel, incluye el nombre del libro abierto.

3. Botones clásicos de las ventanas de Windows (minimizar, maximizar y cerrar). 4. Agrupación de todas las Barras de Herramientas de Excel. Esta forma de presentar las barras de herramientas

es otra de las nuevas características en Office 2007. Se dividen en fichas o etiquetas, la activa en la figura es la

opción “Inicio” en la cual se incorporan todas las funciones referidas al formato. Con un doble clic sobre cualquiera de las etiquetas se ocultan las herramientas.

5. Conjunto de Herramientas agrupadas dentro de las fichas. En el caso de la figura se ven todas las herramientas “Formato” incorporadas dentro de la ficha “Inicio”.

6. Columnas de la hoja. 7. Filas de la hoja.

8. Celda activa.

9. Indica la celda activa 1. 10 Asistente para funciones.

10. Hojas del libro, mediante estos controles podemos desplazarnos rápidamente por las hojas del libro e insertar nuevas hojas al libro.

11. Barra de desplazamiento horizontal que permite desplazar el contenido de de forma lateral.

12. Zoom sobre la hoja. Estos controles son nuevos y permiten ampliar o reducir el porcentaje de “zoom” de forma rápida y precisa.

13. Controles para cambiar la vista de la hoja. 14. Barra de estado. Muestra los mensajes que corresponden a lo que se está realizando. En este caso aparece

listo, lo cual indica que el programa está preparado para que el usuario elija un comando o escriba datos en

una celda. Además informa sobre el estado de las teclas de bloqueo de mayúsculas, bloqueo de números, etc.

EJERCICIO Nº 01

a) Cambie el nombre de la Hoja1 por ALMACEN b) En ésta hoja proceda a diseñar la tabla siguiente, en las celdas que se indican a continuación:

c) Los Datos a ingresar en la Tabla serán: Nro, Artículo, Marca, Cantidad Vendida, Precio Unitario.

d) Los Datos correspondientes a Importe Bruto, Descuento e Importe Bruto serán calculados

Page 3: Modulo 3 - MS Excel

Universidad Católica Santa María SEDELSUR

CURSO BÁSICO DE INFORMÁTICA MÓDULO III – MS EXCEL

Docente: José Luis Ponce Segura 3 de 12 e-mail: [email protected] Cel. 952636911 www.redtacna.net

Una referencia relativa indica la posición relativa de una celda con respecto a la que contiene la fórmula y

cuando se copia en otra posición, modifica y actualiza las posiciones. Ejemplo: E8.

e) Para Calcular:

Importe Bruto = Cantidad Vendida * Precio Unitario

Solución: En la Celda G8, la fórmula será: = E8 * F8

Descuento = 6% del Importe Bruto

Importe Neto = Importe Bruto – Descuento

SubTotal = Sumatoria del Importe Neto de cada Producto

IGV = 19% del SubTotal

Importe Total = SubTotal + IGV

f) GUARDAR EL LIBRO

Haga un clic en el botón office , luego en [Guardar como…] En el cuadro de diálogo Guardar Como, en Nombre de Archivo escriba: Excel - Unidad1

EJERCICIO Nº 02

a) Cambie el nombre de la Hoja2 por TIPO_CAMBIO

b) En ésta hoja proceda a diseñar la tabla siguiente, en las celdas que se indican a continuación:

c) Con el uso de las referencias absolutas y mixtas calcule los precios equivalentes de los Artículos, basándose en el precio en soles.

Referencia Absoluta es una introducción explícita y única de otra celda en un cálculo, no de su posición

relativa. Ejemplo: $B$5

Nótese que en la fórmula, está haciendo referencia a las celdas E8 y F8 en forma relativa Lo cual quiere decir, que al copiar la fórmula hacia abajo, esta se actualizará automáticamente su posición (E9*F9, E10*F10,……E17*F17)

Page 4: Modulo 3 - MS Excel

Universidad Católica Santa María SEDELSUR

CURSO BÁSICO DE INFORMÁTICA MÓDULO III – MS EXCEL

Docente: José Luis Ponce Segura 4 de 12 e-mail: [email protected] Cel. 952636911 www.redtacna.net

Por ejemplo para calcular el precio del artículo Casaca en Dólares:

En la celda D9, la fórmula será: = C9 / $B$5

Resumiendo: El signo $ fija la coordenada de la celda que lleve inmediatamente detrás. Ejemplos: Referencia del tipo $B$8 se fija totalmente la celda horizontal y verticalmente. Referencia del tipo $C4 estamos fijando la celda a la columna C pero permitimos su desplazamiento

vertical por la misma. ($C4, $C5, $C6, $C7, $C8, ….) Referencia del tipo C$4 fijamos la celda a la fila 4 pero dejamos libertad para desplazarla por el resto

de las columnas. (C$4, D$4, E$4, F$4, G$4, ….)

Nótese que en la fórmula, está haciendo referencia a las celdas E8 en forma Relativa y $B$5 en forma absoluta. Lo cual quiere decir, que al copiar la fórmula hacia abajo, esta se actualizará automáticamente sólo la celda C9, más no $B$5 ya que ésta es en forma absoluta, es decir: (C9*$B$5, C10*$B$5,…. C13*$B$5)

Page 5: Modulo 3 - MS Excel

Universidad Católica Santa María SEDELSUR

CURSO BÁSICO DE INFORMÁTICA MÓDULO III – MS EXCEL

Docente: José Luis Ponce Segura 5 de 12 e-mail: [email protected] Cel. 952636911 www.redtacna.net

Unidad II

FUNCIONES

Una función es una fórmula ya preparada por Excel, que permite

ahorrar tiempo y cálculos, y que produce un resultado. Por ejemplo, imaginemos que tenemos que sumar una columna de datos numéricos:

En el ejemplo de la izquierda podríamos colocar en la celda A10 la

fórmula: =A3+A4+A5+A6+A7+A8, pero esto mismo resultaría

horrible si en lugar de 5 celdas hubiese que sumar 100.

En lugar de esa fórmula, utilizamos la función =SUMA(A3:A8 ) que realizará exactamente la misma operación; sumar el rango de celdas

A3:A8.

Las funciones aceptan unos valores (en este caso el rango de celdas)

llamados argumentos.

CREAR UN NUEVO LIBRO

Haga un clic en el botón office , luego en [Nuevo], seguidamente [Libro en blanco] y por último en [Crear]

También para crear un nuevo libro puede presionar CTRL + U

GUARDAR EL LIBRO

Haga un clic en el botón office , luego en [Guardar como…] En el cuadro de diálogo Guardar Como, en Nombre de Archivo escriba: Excel – Unidad2

EJERCICIO Nº 03:

a) Cambie el nombre de la Hoja1 por CONSOLIDADO b) En ésta hoja proceda a diseñar la tabla siguiente, en las celdas que se indican a continuación:

c) Los Datos a ingresar en la Tabla serán: Código, Descripción, Tipo, Costo Directo, Costo Indirecto, %

Utilidad y Stock Actual. d) Los Datos correspondientes a Costo Unidad, Margen Ganancia, Precio Lista, Total Costo, Total

Margen Ganancia serán calculados. Así también el Consolidado.

Objetivos:

Funciones en Excel

Funciones: Suma, Promedio, Máximo, Mínimo Funciones: Contar, Contar.SI, Sumar.SI

Page 6: Modulo 3 - MS Excel

Universidad Católica Santa María SEDELSUR

CURSO BÁSICO DE INFORMÁTICA MÓDULO III – MS EXCEL

Docente: José Luis Ponce Segura 6 de 12 e-mail: [email protected] Cel. 952636911 www.redtacna.net

e) Para Calcular:

Costo Unidad = Costo Directo + Costo Indirecto

Margen Ganancia = Redondear ( Costo Unidad * % Utilidad, 2 )

Función Redondear: Redondea un número a determinados decimales.

Sintaxis: REDONDEAR (número, num_decimales)

Precio Lista = Costo Unidad + Margen Ganancia

Total Costo = Costo Unidad * Stock Actual

Total Margen Ganancia = Margen Ganancia * Stock Actual

f) Para el Consolidado:

Utilice las siguientes funciones:

MAX(número1;número2; ...) Devuelve el valor máximo de un conjunto de valores.

CONTAR.SI(rango;criterio) Cuenta las celdas, dentro del rango, que no están en blanco y que

cumplen con el criterio especificado. SUMAR.SI(rango;criterio;rango_suma) Suma las celdas en el rango que coinciden con el

argumento criterio.

Aplique las funciones como sigue: El Mayor margen de ganancia: = MAX(J7:J18)

Número total de productos tipo A1: = CONTAR.SI(D7:D18; “A1”)

Número total de productos tipo A2: =

Suma total margen de ganancia productos tipo A1: = SUMAR.SI(D7:D18; “A1”;M7:M18)

Suma total margen de ganancia productos tipo A2: =

Para sumar un rango de celdas continuas, ejemplo: E7:E18, utilice la función SUMA( )

=SUMA(E7:E18)

Page 7: Modulo 3 - MS Excel

Universidad Católica Santa María SEDELSUR

CURSO BÁSICO DE INFORMÁTICA MÓDULO III – MS EXCEL

Docente: José Luis Ponce Segura 7 de 12 e-mail: [email protected] Cel. 952636911 www.redtacna.net

g) Otras funciones relacionadas:

SUMA.CUADRADOS(número1;número2; ...) Devuelve la suma de los cuadrados de los argumentos.

CONTAR.BLANCO(rango) Cuenta el número de celdas en blanco dentro de un rango.

CONTAR(ref1;ref2;...) Cuenta el número de celdas que contienen números, además de los números

dentro de la lista de argumentos. Utilice CONTAR para obtener el número de entradas en un campo numérico de un rango o de una matriz de números.

CONTARA(valor1;valor2;...) Cuenta el número de celdas que no están vacías y los valores que hay

en la lista de argumentos. Use CONTARA para contar el número de celdas que contienen datos en un rango o matriz.

PROMEDIO(número1;número2;...) Devuelve el promedio (media aritmética) de los argumentos.

EJERCICIO Nº 04: HÁGALO USTED:

a) Cambie el nombre de la hoja2 por evaluaciones

b) En ésta hoja proceda a diseñar la tabla siguiente, en las celdas que se observan según la imagen e ingrese los datos según corresponda.

c) Luego de ingresado los datos, deberá calcular la columna correspondiente a promedio y por último la tabla

resumen de la parte inferior.

Page 8: Modulo 3 - MS Excel

Universidad Católica Santa María SEDELSUR

CURSO BÁSICO DE INFORMÁTICA MÓDULO III – MS EXCEL

Docente: José Luis Ponce Segura 8 de 12 e-mail: [email protected] Cel. 952636911 www.redtacna.net

Escriba las fórmulas según corresponda:

Para calcular Promedio:

Para calcular Mayor Promedio:

Para calcular Menor Promedio:

Para calcular Suma de todas las cuotas:

Para calcular Cantidad de alumnos del aula B:

Para calcular Cantidad de alumnos del aula A:

Page 9: Modulo 3 - MS Excel

Universidad Católica Santa María SEDELSUR

CURSO BÁSICO DE INFORMÁTICA MÓDULO III – MS EXCEL

Docente: José Luis Ponce Segura 9 de 12 e-mail: [email protected] Cel. 952636911 www.redtacna.net

CREAR UN NUEVO LIBRO

Haga un clic en el botón office , luego en [Nuevo], seguidamente [Libro en blanco] y por último en [Crear]

También para crear un nuevo libro puede presionar CTRL + U

GUARDAR EL LIBRO

Haga un clic en el botón office , luego en [Guardar como…] En el cuadro de diálogo Guardar Como, en Nombre de Archivo escriba: Excel – Unidad3

EJERCICIO Nº 05:

a) Cambie el nombre de la Hoja1 por

GENERA_CODIGO

b) En ésta hoja proceda a diseñar la tabla

siguiente, en las celdas que se indican a continuación:

c) El código del trabajador se debe generar en base a sus datos

personales: 1. Dos primeras letras del Apellido Paterno. (Función Izquierda)

2. Dos últimas letras del Apellido Materno. (Función Derecha)

3. Primera y Tercera letra del Nombre. (Función Extrae) 4. Dos últimos dígitos del año de la Fecha de Nacimiento. (Función Derecha)

Funciones utilizadas:

IZQUIERDA(texto;núm_de_caracteres) : Devuelve el primer carácter o caracteres de una cadena

de texto, según el número de caracteres que especifique el usuario. DERECHA(texto;núm_de_caracteres) : Devuelve el último carácter o caracteres de una cadena de

texto, según el número de caracteres que el usuario especifica.

EXTRAE(texto;posición_inicial;núm_de_caracteres) : Devuelve un número específico de

caracteres de una cadena de texto, comenzando en la posición que especifique y en función del

número de caracteres que especifique. Solución: Se recomienda para éstos casos hacer en forma independiente para luego juntarla en una sola

“Concatenar” Para 1: =IZQUIERDA(C3;2)

Para 2: =DERECHA(C4;2)

Para 3: =IZQUIERDA(C5;1)

=EXTRAE(C5;3;1)

Para 4: =DERECHA(AÑO(C6);2)

Solución Final para obtener la fórmula: En la celda C7 escribir:

= IZQUIERDA(C3;2) & DERECHA(C4;2) & IZQUIERDA(C5;1) & EXTRAE(C5;3;1) & DERECHA(AÑO(C6);2)

Unidad III

Objetivos:

Usar funciones de texto (CONCATENAR, DERECHA, IZQUIERDA, EXTRAE)

Funciones de búsqueda: BUSCARV, BUSCARH

Introducción a la Función SÍ

Page 10: Modulo 3 - MS Excel

Universidad Católica Santa María SEDELSUR

CURSO BÁSICO DE INFORMÁTICA MÓDULO III – MS EXCEL

Docente: José Luis Ponce Segura 10 de 12 e-mail: [email protected] Cel. 952636911 www.redtacna.net

EJERCICIO Nº 06

a) Cambie el nombre de la Hoja2 por ESCALAFON b) En ésta hoja proceda a diseñar la tabla siguiente, en las celdas que se indican a continuación:

c) Ingrese los datos en ambas tablas a excepción del Sueldo que será calculado

Lo que se quiere es calcular el Sueldo, éste deberá buscar su categoría a la que pertenece en empleado

en la 2da. Tabla: Categorías y obtener el Sueldo correspondiente

Para ello haga lo siguiente:

PRIMERO: Establecer un nombre al rango de celdas a la matriz donde se buscará.

Seleccione los datos de la tabla categoría “sólo datos, NO

encabezados”.

Luego en el cuadro de nombre escriba SUELDOS y presionar ENTER

SEGUNDO: Utilizar la función BUSCARV, para buscar la categoría en la matriz SUELDOS y obtener

el sueldo respectivo.

En la Celda H6, = BUSCARV (G6 ; $C$19:$D$26 ; 2 ; FALSO)

Función Buscarv

Sintaxis: =BuscarV(valor_buscado; matriz_buscar_en; indicador_columnas; ordenado)

Page 11: Modulo 3 - MS Excel

Universidad Católica Santa María SEDELSUR

CURSO BÁSICO DE INFORMÁTICA MÓDULO III – MS EXCEL

Docente: José Luis Ponce Segura 11 de 12 e-mail: [email protected] Cel. 952636911 www.redtacna.net

EJERCICIO Nº 07

a) Cambie el nombre de la Hoja1 por MEDICAMENTOS b) En ésta hoja proceda a diseñar la tabla siguiente:

c) Cambie el nombre de la Hoja2 por FACTURA d) En ésta hoja proceda a diseñar la tabla siguiente:

e) Escriba la función BUSCARV en el primer dato de las columnas DETALLE y P.U. Considere que el VALOR BUSCADO siempre será CÓDIGO.

Al escribir un CÓDIGO que está en la lista de la hoja MEDICAMENTOS, debe observar los datos

correspondientes en cada columna que tiene la función BUSCARV. Ingresar CANTIDAD y determinar VALOR VENTA para cada uno de los productos (VALOR VENTA=

CANT. * P.U.)

SUB TOTAL es la sumatoria de la columna PRECIO VENTA.

Calcular IGV que es el 19% del SUB TOTAL.

TOTAL DE VENTA= IGV + SUB TOTAL.

f) Guardar nuevamente el libro.

Page 12: Modulo 3 - MS Excel

Universidad Católica Santa María SEDELSUR

CURSO BÁSICO DE INFORMÁTICA MÓDULO III – MS EXCEL

Docente: José Luis Ponce Segura 12 de 12 e-mail: [email protected] Cel. 952636911 www.redtacna.net

EJERCICIO Nº 08

a) En la Hoja 5, cambie el nombre por DEPA_EMPLEADO, luego realizar el siguiente cuadro

b) Usando la función BUSCARV y los tres primeros caracteres del Código, visualizar el Dpto. de trabajo (considere en el argumento Ordenado: Verdadero). Reemplace las celdas en los argumentos de la función:

=BuscarV(Código; Matríz: Departamentos de Trabajo; 2; VERDADERO) c) Calcular el sueldo: (utilizar función Si)

Si la Categoría es "A" el sueldo será: 1200; en caso contrario el sueldo será: 975

d) Descuentos (Dsctos): (utilizar función Si) Si el Empleado tiene más de 15 de años de servicio tendrá un descuento del 7.5% del Sueldo,

caso contrario tendrá un descuento del 9% del Sueldo

e) Neto a Pagar = Sueldo – Descuentos.

Función SI()

Sintaxis:

=SI(Condición; Verdadero; Falso)

Solución para el caso del Sueldo (c): Escribir la formula en F5 =SI(D5=”A”; 1200; 975)