Ejerciciosdeexcelporjessicanavarrovasquez

Ing Jessica Navarro Vásquez 1. TRABAJO CON LIBROS Y HOJAS  Insertar una hoja:

Pa ra insertar una nueva hoja en una archivo o libro de Excel, dar clic en el botón Insertar una hoja de cálculo, ubicado en la parte inferior de la ventana de Excel.

2

 Eliminar una hoja: Para eliminar una hoja anticlic sobre la hoja por eliminar, del menú contextual, dar clic en el comando Eliminar.

 Ocultar una hoja: Para ocultar una hoja o un grupo de hojas, seleccionar la hoja o las hojas (presionar la tecla ctrl y dar clic a cada hoja por seleccionar), anticlic sobre el nombre de la hoja, al observar el menú contextual, dar clic en el comando ocultar.

Celdas Seleccionadas

ďƒ˜ Mostrar hoja u hojas ocultadas.

4 Anticlic en el nombre de una de las hojas, del menú contextual que observará, clic en el comando Mostrar.

Visualizara un cuadro de diálogo, donde seleccionara la hoja por mostrar, luego clic en el botón Aceptar.

 Mover o copiar hojas Para colocar una hoja de un libro de Excel, como parte de otro libro de Excel. Primero dar anticlic sobre el nombre de la hoja, del menú contextual que visualizar, dar clic en el comando Mover o copiar Hoja. En el ejemplo la hoja fecha del archivo o libro de Excel Practica4.xlsx, será movido a otro Libro de Excel, para el ejemplo al Libro Class.xlsx

Hoja Archivo

6

Observara un cuadro de diálogo, en el cual seleccionar de la lista Al Libro: el nombre del libro en el cual, colocaremos la hoja antes seleccionada (Fecha), para nuestro ejemplo class.xlsx. En la zona Antes de la hoja, indicaremos el lugar ha colocar la hoja fecha, en este caso seleccionar Mover al final. En la parte inferior se muestra una casilla Crear una copia, la cual activaras en el caso que la hoja decidas copiarla (se mantendrá la hoja en el archivo Practica4.xlsx y en el archivo class.xlsx), finalmente clic en el botón Aceptar.

El resultado sería como se muestra en la imagen.

Hoja Copiada

2. PROTEGER UNA HOJA ďƒ˜ En la hoja por proteger, seleccionar la ficha Revisar, luego dar clic en el comando Proteger hoja

8

 Se observará un cuadro de diálogo, donde se solicita digitar una contraseña, la cual se recomienda recordar, en caso se necesite desproteger la hoja.

 Posteriormente, se mostrara otro cuadro de diálogo, donde debemos confirmar la contraseña antes digitada.

 Si se pretende cambiar los datos de la hoja protegida, se mostrara un mensaje, que nos indica que debemos desproteger la hoja, para realizar los cambios.

3. PROTEGER UN LIBRO  La protección de un libro de Excel, se realiza al momento de Guardar el archivo o libro.

Seleccionar el menú Archivo, luego clic en Guardar si es la primera vez que guardamos el libro o Guardar como si se guardara por segunda vez.  Al observar el cuadro de diálogo Guardar o Guardar como, dar clic en el botón, Herramientas, observara una lista de comandos, dar clic en el comando Opciones Generales.

10

 En el cuadro de diálogo opciones generales, digitar la contraseña de apertura y de escritura.

 Posteriormente, se mostrara dos cuadros más donde se debe confirmar ambas contraseñas.

 Luego de guardar el archivo y cerrarlo, abrir nuevamente el archivo, lo primero que observar, será un cuadro de diálogo, que nos pide la contraseña de juestro archivo.

4. SELECCIÓN DE RANGOS  Crear la tabla de multiplicar de la figura, luego, seleccionar las celdas desde D6:D15, para ello dar clic a la celdaD6, mantener el botón izquierdo del mouse presionado y a la vez mover el mouse hasta llegar a la celda D15, para seleccionar el cursor del mouse debe tomar la forma de una cruz blanca

12

 Ahora agregar a tu selección la celdas F6:F15, mantener presionada la tecla CTRL y seleccionar con el mouse las respectivas celdas.

5. AUTOLLENADO  Rellenaremos los datos de Radio, los cuales empiezan en 7, y aumentara de 4 en 4. Primero, digitar el número 7 en la celda D7 y en la celda D8 el número 11, luego seleccionar ambas celdas, ubicar el cursor del mouse en la parte inferior derecha, observara el cursor como una cruz negra delgada , mantener presionado el botón izquierdo del mouse y mover el mouse hacia abajo hasta llegar al dato 55.

 El resultado sería como se muestra en la imagen:

6.

FORMATO COLUMNA Y FILA  Ancho de columnas Seleccionar las columnas a las cuales aumentaremos su ancho.

14 Seleccionar la ficha Inicio, en la sección celdas, dar clic al botón Formato, se mostrara una lista de comandos, dar clic en Autoajustar ancho de columna ( el ancho de la columna aumentara de acuerdo al texto contenido en las celdas respectivas)

 Ocultar columnas Para ocultar un conjunto de columnas, primero debemos seleccionarlas, para el ejemplo, seleccionaremos desde la columna O hasta XFD (última columna).

Primero seleccionar la columna O (clic en la letra O), presionar las teclas CTRL  y la flecha .

Luego de seleccionar las columnas, dar anticlic, del menú contextual que observara, clic en el comando Ocultar.

Columnas ocultadas El resultado sería:

 Mostrar Columnas Para mostrar las columnas del ejercicio anterior, primero seleccionar la última columna visible, es decir, N, luego presionar las teclas  y la flecha .

Una vez seleccionadas las columnas, dar anticlic, del menú contextual, dar clic en Mostrar.

16

7. OPERADORES ARITMETICOS  En el ejercicio, debemos calcular el área del círculo, primero crear la tabla de la figura.

18

 Luego en la celda, ubicada debajo del título AREA, digitar el símbolo =, continuar digitando la función PI( ), luego el símbolo de la multiplicación * (asterico), dar clic al primer valor del radio ( es decir, la celda que contiene el valor 7), posteriormente digitar el símbolo de la potencia ^, si no se ubicara en el teclado presionar las teclas ALT 94, finalmente el número 2.  El resultado sería como se muestra.

8. REFERENCIAS DE CELDAS  En el ejercicio, crearemos una tabla de multiplicar, de tal manera que digitaremos el valor de un multiplicador, al costado de la celda INGRESAR EL MULTIPLICADOR, el cual se mostrara en las celdas respectivas, posteriormente calcularemos el resultado de la multplicación.  Primero diseñar la tabla como muestra la figura, para el ejemplo, el multiplicador que se digita es 30.

 Luego en la celda que indica la figura digitar = y dar clic a la celda donde ingresamos el dato del multiplicador, para el ejemplo G5, necesitamos fijar la celda, para ello presionar la tecla F4 , de esta manera se mostrara los símbolos de dólar, que son los que evitan que una celda varié.  El resultado será como se muestra en la figura.

 Luego, calcularemos el resultado de la multiplicación, digitar el símbolo = dar clic a la celda que contiene el primer multiplicador, en el ejemplo D7, digitar el asterisco ( teclas del lado derecho del teclado), clic en la celda que contiene el segundo multiplicador,F7, finalmente ENTER, como se muestra en la figura.

20

 El resultado de la tabla de multiplicar, quedaría como muestra en la figura :

 Se puede variar el multiplicador 30, digite otro número, y notara que los resultados, cambiaran.

9. APLICAR FORMATO DE CELDAS  Digitar los siguientes datos:

 Combinar y centrar el título cuadro de gastos: Seleccionar las celdas a combinar, clic en el botón combinar y centrar de la ficha Inicio.

 Aplicar borde a las celdas: Seleccionar las celdas, anticlic o clic derecho del mouse, del menú contextual, clic en formato de celda, seleccionar la ficha bordes. Primero seleccionar el estilo de línea, luego el color, posteriormente clic en los recuadros preestablecidos o borde por aplicar, finalmente clic en el botón Aceptar.

22

1 2 3 4

Otra forma es emplear el botón borde de la ficha Inicio.

 Aplicar relleno a las celdas: Para aplicar relleno a las celdas, seleccionar la ficha relleno. De la lista de colores dar clic al color por aplicar, finalmente clic en el botón Aceptar.

Otra manera de aplicar relleno a las celdas, es empleando el botón de relleno de la ficha Inicio.

24

 Aplicar fuente a las celdas: Para cambiar el tipo de letra, seleccionar la ficha fuente. Primero seleccionar el tipo de fuente, estilo, tamaño de fuente, luego el color, finalmente clic en el botón aceptar.

Otra forma de aplicar características al texto, es empleando los botones de la ficha inicio.

 Aplicar formato Moneda: Primero, seleccionar las celdas que contienen los datos numéricos, luego anticlic o clic derecho del mouse, del menú contextual, clic en formato de celda, seleccionar la ficha Número, observara una lista de categorías, de la lista dar clic en Moneda, en el panel derecho observará la zona de símbolo, en el cual debe mostrarse el símbolo de soles, sino fuera así, dar clic en la lista desplegable y buscar el símbolo pertinente, finalmente clic en el botón aceptar.

26  El resultado final:

10. APLICAR FORMATO CONDICIONAL  En el siguiente ejercicio, se tiene un conjunto de notas, aplicaremos el formato condicional, para poner las notas aprobadas en color azul y las notas desaprobadas en color rojo.  Primero, se deben seleccionar las celdas, posteriormente, dar clic en el botón formato condicional de la ficha Inicio.

 De la lista seleccionar la opción Nueva Regla. Observará el cuadro de diálogo Nueva regla de formato. Seleccionar la segunda regla: Aplicar formato únicamente a las celdas que contengan, en la parte inferior seleccionar la opción Mayor que y digitamos 13.4.

 Posteriormente dar clic al botón Formato, se observará la ficha Formato de Celdas, dar clic a la ficha Fuente, donde elegiremos el color Azul, luego clic en el botón Aceptar.

28



Sin quitar la selección, dar clic nuevamente a la opción Nueva regla de Formato condicional. Observará el cuadro de diálogo Nueva regla de formato.

Seleccionar la segunda regla: Aplicar formato únicamente a las celdas que contengan, en la parte inferior seleccionar la opción Menor o igual que y digitamos 13.4.

 Luego dar clic al botón Formato, se observará la ficha Formato de Celdas, dar clic a la ficha Fuente, donde elegiremos el color Rojo, finalmente, clic en el botón Aceptar.

30

 El resultado final se visualizaría así:

11. FUNCIONES BASICAS  En el cuadro de la figura se nos pide calcular el promedio de sueldos, suma de bonificaciones, número de trabajadores casados, total de trabajadores y suma de sueldos de los trabajadores solteros.

32  Calcularemos primero el promedio de los Sueldos, digitar, =PROMEDIO( , luego seleccionar las celdas de los sueldos, es decir H6:H25, digitar el paréntesis de cierre ), la función quedaría de la sgte. manera = PROMEDIO(H6:H25)

 Para calcular la suma de las bonificaciones, digitar, =SUMA(, luego seleccionar las celdas de los sueldos, es decir G6:G25, digitar el paréntesis de cierre ), la función quedaría de la siguiente manera = SUMA(G6:G25)



Para el Número de trabajadores casados, digitar la función =CONTAR.SI(, dar clic en fx, para trabajar con el asistente de funciones, se mostrara una ventana, donde observaremos los argumentos de la función, en RANGO, selecconar las celdas que contaremos, como nos piden solo casados, seleccionaremos las celdas, donde figuran los datos de estado civil, (E6:E25). En criterio o condición, digitar C, por ser la letra que identifica a los casados.

 Para el Total de trabajadores, digitar = CONTARA( , seleccionar las celdas de los códigos de los trabajadores, es decir C6:C25 y cerrar el paréntesis ).

34

 En el cálculo de la suma de los sueldos de los trabajadores solteros, digitar =SUMAR.SI(, dar clic en fx, para visualizar los argumentos que necesita la función, en rango, seleccionar las celdas donde se evalua la condición o criterio, es decir las celdas del estado civil, en criterio, digitar la S, que representa a los empleados solteros, y en rango suma, seleccionar las celdas que contiene los datos por sumar ( sueldo total).

12. FUNCIOMES DE TEXTO  En el cuadro, se pide, completar los datos, de posición del espacio, tamaño de cadena, separa nombre y apellidos, empleando para conseguir el propósito, funciones de texto.  En primer lugar crear el cuadro de la figura:

36  Posición del espacio; indicar en qué lugar, se ubica el espacio del texto JUAN TORRES, digitar = HALLAR(, luego dar clic a fx, para visualizar los argumento que requiere la función, en texto buscado digitar entre comillas un espacio, luego en, dentro del texto, seleccionar la celda donde se encuentra el texto donde buscaremos, es decir C28 (juan torres), finalmente en Num-Inicial, no colocar ningún dato, solo se emplearía, en el caso que desees empezar a buscar en una posición diferente de 1. Finalmente Enter.



En tamaño de la cadena , digitar = LARGO( y dar clic en la celda, donde se encuentra el texto, del cual se necesita saber, el número de caracteres que posee, es decir, C28, cerrar paréntesis

 En nombre, necesitamos solo los caracteres que formen parte del nombre, es decir, Juan, y luego poder copiar la función para el resto de datos, como el nombre se ubica al comienzo de la cadena, digitamos= IZQUIERDA(, dar clic en fx para visualizar los argumentos de la función, en texto, dar clic a la celda que contiene la cadena, de la cual extraeremos solo el nombre, es

decir, C28, en número de caracteres, da clic en el valor que obtuvimos de posición del espacio D28 y le restamos 1, finalmente Enter.

 En Apellidos, necesitamos solo los caracteres que formen parte del apellido, es decir, Torres, y luego poder copiar la función para el resto

de datos, como el nombre se ubica al final de la cadena, digitamos= DERECHA(, dar clic en fx para visualizar los argumentos de la función, en texto, dar clic a la celda que contiene la cadena, de la cual extraeremos solo el apellido, es decir, C28, en número de caracteres, da clic en el valor que obtuvimos de tamaño de la cadena y le restamos la posición del espacio, es decir, E28-D28, finalmente Enter.

38



El resultado final sería:

13. FUNCIONES DE FECHA Y HORA  Diseñar el siguiente cuadro:

 En fecha actual, digitar =HOY(), esta función, no necesita argumentos, devuelve como resultado la fecha del sistema.  En la celda ubicada a la derecha del texto Dia, debe mostrarse solo el día de la fecha actual, digitar =DIA(, dar clic a la celda que contiene la fecha, de la cual solo mostraremos el día, es decir D3, cerrar paréntesis.

 En la celda del lado derecho del texto Mes, mostrar solo el mes de una fecha determinada, digitar =MES(, dar clic a la celda que contiene la fecha, es decir D3, cerrar paréntesis.

 En la columna vencimiento, se debe calcular la fecha de vencimiento, (tiempo límite), se calcula restando fecha actual más días de préstamo; C6+D6.

40

 En la segunda columna vencimiento, se debe calcular, la fecha límite, en este caso a la fecha de préstamo, se le debe sumar el número de meses, para ello empleamos la función FECHA.MES, la cual tiene dos argumentos, la fecha inicial y el número de meses a sumar.

14. FUNCIONES CONDICIONALES

 Diseñar el cuadro que se muestra en la figura:

 En acción se debe mostrar un mensaje “ REPONER EL INVENTARIO” o el mensaje “NO REPONER”, lo cual depende de una condición, ( las existencias sean menores al stock). Quedaría de la siguiente manera:

42

 El resultado final, sería:

15. FUNCIONES LOGICAS  Crear el cuadro que se muestra en la figura; debemos calcular ESTADO, si las asistencias son mayores de 3, y el promedio final mayor de 13.4, se debe mostrar el mensaje “aprobado”, si no se cumplen las condiciones, el mensaje será “desaprobado”.

 Emplearemos la función si, en el argumento, prueba lógica, utilizaremos la función Y, para evaluar dos condiciones, como se muestra en la figura:

 El resultado final, quedaría como en la figura:

44

16. FUNCIONES DE BUSQUEDA

 Para el presente ejercicio. Emplearemos el cuadro creado en el tema de funciones lógicas (tabla auxiliar). Diseñar el cuadro que se muestra en la figura; el ejercicio consiste en digitar un código de algún alumno, que exista en la tabla auxiliar. En nombres y apellidos, promedio, asistencias e inasistencias, utilizaremos la función CONSULTAV, para mostrar los datos dependiendo del código ingresado.



En nombres y apellidos, emplear la función CONSULTAV, la cual requiere 4 argumentos: o Valor buscado, es la celda donde se digito el código o Matriz, seleccionar toda la tabla auxiliar o Indicador de columna, el número de la columna, que obtendremos como resultado, es decir el nombre y apellido.

46 o

Ordenado, indicar con verdadero (si los datos de la primera columna esta ordenada) o falso (si los datos de la primera columna de la tabla auxiliar esta desordenado).

 Para mostrar el promedio del alumno ( según el código ingresado), solo variaremos el número del indicador de columna, pues el promedio, se encuentra en la columna 15 de la tabla auxiliar.



El resultado final, quedaría como se muestra en la figura.

17.

ORDENACION DE DATOS

48  En la siguiente base de datos, ordenaremos la información, primero por el campo área y luego por apellidos de manera ascendente.



Primero seleccionar la ficha Datos, dar clic al botón Ordenar.

 Observará el cuadro de diálogo Ordenar, en la opción Ordenar por, seleccionar de la lista el campo área, luego dar clic al botón Agregar Nivel, se mostrara una nueva línea, Luego por, seleccionar de la lista el campo ApellidoPat, en la opción Criterio de selección, seleccionar de la lista la opción A a Z. Finalmente clic en el botón aceptar.

18. FILTROS  Para aplicar filtros, dar clic en el botón filtros de la ficha datos.



En los campos de la base de datos se visualizar unos botones, tal como se muestra en la figura:

50

 En el ejercicio, filtraremos la información, para mostrar solo los datos de los empleados casados y que pertenescan a las áreas AR01 y AR02.

Primero dar clic en el botón del campo área y seleccionar dando clic en AR01 y AR02, luego dar clic en el botón del campo estado civil y seleccionar C, como se muestra en las figuras respectivas.

 Como resultado, quedarían visibles, las filas que cumplen las condiciones especificadas anteriormente.

19. TABLAS DINAMICAS  Con la base de datos del ejercicio anterior, crear una tabla dinámica, que muestre la bonificación y los descuentos de los empleados del área ARE05. En primer lugar, dar clic en el botón Tabla dinámica, de la ficha Insertar.

 Observara un cuadro de diálogo, que nos muestra, primero los datos seleccionados, en la parte inferior, elegir el lugar donde se creara l tabla dinámica, para el ejemplo en Nueva hoja de cálculo. Finalmente dar clic en el botón aceptar.

52

 Visualizara una nueva hoja de cálculo, con dos paneles, el lado izquierdo, contiene el área donde se creara la tabla dinámica, y al lado derecho la lista de los campos de la base de datos.

PANEL IZQUIERDO

PANEL DERECHO

 En el panel derecho, arrastrar el campo bonificación y descuentos a la zona valores, el campo apellidopat a la zona etiqueta de fila, y el campo area a la zona filtro de informe, dar clic en el campo area y seleccionar solo el area AR05, los campos quedarían como se muestra en la figura:

 Finalmente la tabla dinámica quedaría así:

54

20.

GRAFICOS DINAMICOS Para crear un gráfico dinámico, primero se debe crear la tabla dinámica, para nuestro ejemplo, crearemos el grafico de la tabla del ejercicio anterior.  En primer lugar, al ubicar el cursor en cualquier celda de la tabla dinámica, visualizaras en la parte superior, una nueva ficha o menú llamada herramientas de tabla dinámica, el cual contiene dos fichas; opciones y diseño. En la ficha opciones dar clic al botón Gráfico dinámico.

 Automáticamente, se creará el grafico dinamico, como se muestra en la figura.

21. FUNCIONES FINANCIERAS

En el primer ejercicio, se nos pide calcular el tiempo límite para pagar una deuda, la función a emplear es NPER, los argumentos se encuentran ordenado en el cuadro de la figura, primero tasa, pago, va, vf y tipo, la función quedaría de la siguiente manera:

56

En el segundo ejercicio, se nos pide calcular el monto al final de un ahorro, realizado durante 4 a帽os, la funci贸n a utilizar es VF, cuyos argumentos se encuentran ordenado en el cuadro de la figura, primero tasa, periodo, pago, va y tipo, la funci贸n quedar铆a de la siguiente manera:

22. FORMULARIOS

El ejercicio consiste en datos de un Centro de esparcimiento AMAZING. Insertar un control cuadro combinado para Escoger el menĂş ( tabla auxiliar de menĂşs).

Insertar controles de casilla de verificaciĂłn para los platos adicional y parar las actividades que desarrollara.

58 En la celda subtotal menú y adicionales, calcular el monto por el menú escogido y adicionales.

En la celda subtotal de actividades, mostrar el monto por las actividades ha desarrollar En total: sumar ambos subtotales.

Utilizar las siguientes tablas auxiliares.

Primero diseñar el formulario, luego insertar los controles, los cuales se ubican en la ficha Programador, luego clic en el botón Insertar, los controles a emplear son los de la parte superior (controles de formulario)

En escoger menú, dibujar el control cuadro combinado, dar anticlic, observara el menú contextual, clic en el comando Formato de control. En el cuadro de diálogo, que visualizara, en rango de entrada, seleccionar las celdas donde aparecen los nombres de los menús, y en vincular con la celda, seleccionar una celda cercana al control cuadro combinado.

60

Posteriormente para actividades y adicionales, insertar el control casilla de verificación, (9 casillas de verificación), luego anticlic sobre cada control, seleccionar del menú contextual, el comando formato de control.

En el cuadro de diálogo, formato de control, en la opción vincular con la celda, seleccionar la celda, ubicadas al costado de sus nombres respectivos, en las tablas auxiliares. (acción a realizar para cada casilla de verificación)

En la celda subtotal menú y adicionales, se aplicara la función CONSULTAV, en la cual, valor buscado es la celda vinculada del control cuadro combinado, en matriz, seleccionar la tabla auxiliar y en columna resultado, digitar el numero 3.

MATRIZ COLUMNA RESULTADO

Ya tenemos el costo del menú, faltaría sumar los adicionales, digitar el signo + para especificar la suma, luego emplearemos la función SUMAR.SI , en rango seleccionar de la tabla auxiliar, las celdas vinculadas a cada casilla, en criterio, digitar la palabra verdadero, y en rango suma seleccionar las celdas, donde figuran los costos.

CELDAS VINCULADAS COSTOS

Las funciones en la celda debe quedar de la siguiente manera:

Para calcular el Subtotal actividades, emplearemos la función SUMAR.SI, en rango seleccionar las celda vinculadas de los controles, casillas de verificación, en criterio, digitar el texto verdadero, y en rango suma, seleccionar los costos.

62

Celdas vinculadas Costos

La función, quedaría como se muestra.

El Total, se calcula sumando las celdas, subtotal menú y adicionales más subtotal de actividades.

El formulario final quedaría como se muestra en la figura: