• Author / Uploaded
• jlponcesg

• Categories
• Microsoft Excel
• Hoja de cálculo
• Informática
• Tecnología
• Software

Citation preview

Universidad Católica Santa María SEDELSUR

CURSO BÁSICO DE INFORMÁTICA MÓDULO III – MS 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.

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:

Docente: José Luis Ponce Segura Cel. 952636911

1 de 12

e-mail: [email protected] www.redtacna.net

Universidad Católica Santa María SEDELSUR

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

En 1. 2. 3. 4.

la que se distinguen las siguientes partes: Barra de Inicio Rápido (Nueva característica en Office 2007). Barra de Título de la ventana de Excel, incluye el nombre del libro abierto. Botones clásicos de las ventanas de Windows (minimizar, maximizar y cerrar). 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) b) c) d)

Cambie el nombre de la Hoja1 por ALMACEN En ésta hoja proceda a diseñar la tabla siguiente, en las celdas que se indican a continuación: Los Datos a ingresar en la Tabla serán: Nro, Artículo, Marca, Cantidad Vendida, Precio Unitario. Los Datos correspondientes a Importe Bruto, Descuento e Importe Bruto serán calculados

Docente: José Luis Ponce Segura Cel. 952636911

2 de 12

e-mail: [email protected] www.redtacna.net

Universidad Católica Santa María SEDELSUR

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

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

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)

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 Docente: José Luis Ponce Segura Cel. 952636911

3 de 12

e-mail: [email protected] www.redtacna.net

Universidad Católica Santa María SEDELSUR

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

Por ejemplo para calcular el precio del artículo Casaca en Dólares: En la celda D9, la fórmula será:

= C9 / $B$5

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)

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, ….)

Docente: José Luis Ponce Segura Cel. 952636911

4 de 12

e-mail: [email protected] www.redtacna.net

Universidad Católica Santa María SEDELSUR

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

Unidad II Objetivos:   

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

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.

Docente: José Luis Ponce Segura Cel. 952636911

5 de 12

e-mail: [email protected] www.redtacna.net

Universidad Católica Santa María SEDELSUR

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

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

e) Para Calcular: 

Costo Unidad = Costo Directo + Costo Indirecto



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

=SUMA(E7:E18)

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: =

Docente: José Luis Ponce Segura Cel. 952636911

6 de 12

e-mail: [email protected] www.redtacna.net

Universidad Católica Santa María SEDELSUR

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

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.

Docente: José Luis Ponce Segura Cel. 952636911

7 de 12

e-mail: [email protected] www.redtacna.net

Universidad Católica Santa María SEDELSUR

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

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:

Docente: José Luis Ponce Segura Cel. 952636911

8 de 12

e-mail: [email protected] www.redtacna.net

Universidad Católica Santa María SEDELSUR

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

Unidad III Objetivos:   

Usar funciones de texto (CONCATENAR, DERECHA, IZQUIERDA, EXTRAE) Funciones de búsqueda: BUSCARV, BUSCARH Introducción a la Función SÍ

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)

Docente: José Luis Ponce Segura Cel. 952636911

9 de 12

e-mail: [email protected] www.redtacna.net

Universidad Católica Santa María SEDELSUR

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

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)

Docente: José Luis Ponce Segura Cel. 952636911

10 de 12

e-mail: [email protected] www.redtacna.net

Universidad Católica Santa María SEDELSUR

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

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. Docente: José Luis Ponce Segura Cel. 952636911

11 de 12

e-mail: [email protected] www.redtacna.net

Universidad Católica Santa María SEDELSUR

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

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)

Docente: José Luis Ponce Segura Cel. 952636911

12 de 12

e-mail: [email protected] www.redtacna.net