Maraton de Excel(1)

Ejercicios de Entrenamiento de Microsoft Excel

EJERCICIOS DE ENTRENAMIENTO CON MICROSOFT EXCEL (versiones 2007 y superiores)

Página 1 de 62


BLOQUE 1: SERIES Hoja 1. Genere una serie lineal que comience en -4, con c on pasos o saltos de 0,5 y llegue hasta 4 Hoja 2. Genere una serie geométrica que comience en 1, con pasos o saltos de 5 y llegue hasta 5800300

Hoja 3. Genere una serie de fecha que comience el 25/04/2006 y culmine el 25/05/2006 Hoja 4. Genere una serie de fecha que comience el 25/04/2006 y culmine el 25/05/2006 pero únicamente los días laborables

Hoja 5. Genere una serie lineal que comience en 1 y llegue hasta 20 val ores de capital, emplear Hoja 6. Encontrar el valor en dólares del interés para cada uno de los valores referencias: relativa, absoluta o m ixta. Transcriba para el efecto la siguiente tabla: tabla:

DESARROLLO DEL BLOQUE 1 Genere una serie lineal que comience en -4, con pasos o saltos de 0,5 y llegue hasta 4 Para generar la serie lineal lo primero que debemos hacer es digitar el primer valor, para el caso del ejercicio será -4, en la celda que queremos que comience a generar la serie, y presionamos la tecla enter, así:

Página 2 de 62


Ubicamos el cursor en la celda que digitamos el -4, es decir la celda A1 y en el menú Inicio(Home) en Edición (Editing) seleccionamos Series(Series)así:

Nos aparece la siguiente ventana en la cual procedemos a llenar con las opciones que nos han solicitado, es decir, vamos a generar en columna (Columns), elegimos Tipo Lineal (Linear), los saltos o pasos que va a dar (Step value) digitamos 0,5 y finalmente hasta donde va a llegar (Stop value) que para el ejercicio será 4, así:

Nota: Verificar en la máquina si los números fraccionarios se ingresan con punto o coma, ejemplo: 0.035 ó 0,035.

Página 3 de 62


Presionamos la tecla Aceptar (OK) y tendremos el siguiente resultado:

Genere una serie geométrica que comience en 1, con pasos o saltos de 5 y llegue hasta 5800300 Pro gr esión geo m é tri ca, en matemáticas, sucesión de números tales que la proporción entre cualquier término (que no sea el primero) y el término que le precede es una cantidad fija llamada razón. Por ejemplo, la secuencia de números 2, 4, 8, 16, 32, 64, 128 es una progresión geométrica con razón 2; y 1, y , ‚ , ˆ , , w , , ... ( y ) i , es una progresión geométrica con razón y . La primera es una progresión geométrica finita con siete términos; la segunda es una progresión geométrica infinita. En general, una progresión geométrica se puede describir utilizando la siguiente notación: a es el primer término, la razón es r y, en una progresión finita, n es el número de términos. Una progresión geométrica finita se escribe for malmente como: •

Para generar la serie geométrica lo primero que debemos hacer es digitar el primer valor, para el caso del ejercicio será 1, en la celda que queremos que comience a generar la serie, y presionamos la tecla enter, así:

Página 4 de 62


Ubicamos el cursor en la celda que digitamos el 1, es decir la celda A1 y en el menú Inicio (Home) seleccionamos la opción Edición (Editing) seleccionamos Series (Series), así:

Nos aparece la siguiente ventana en la cual procedemos a llenar con las opciones que nos han solicitado, es decir, vamos a generar en columna (Columns), elegimos Tipo Geométrica (Growth), los saltos o pasos que va a dar (Step value) digitamos 5 y finalmente hasta donde va a llegar (Stop value) que para el ejercicio será 5800300, así:

Presionamos la tecla Aceptar (OK) y tendremos el siguiente resultado: Página 5 de 62


OBSERVACIÓN: Note que la serie geométrica no llegó hasta el número máximo ( Stop value ) digitado que fue 5800300 porque la serie se va multiplicando por 5 hasta llegar a un valor aproximado del número que se puso como valor final.

Ejemplo:

Y así sucesivamente hasta completar la serie.

Genere una serie de fecha que comience el 30/03/2007y culmine el 30/04/2007 Para generar la serie de fecha lo primero que debemos hacer es digitar la primera fecha, para el caso del ejercicio será 25/04/2006, en la celda que queremos que comience a generar la serie, y presionamos la tecla enter, así:

Página 6 de 62


Ubicamos el cursor en la celda que digitamos 30/03/2007, es decir la celda A1 y en el menú Inicio (Home) seleccionamos la opción Edición (Editing) seleccionamos Series (Series), así:

Nos aparece la siguiente ventana en la cual procedemos a llenar con las opciones que nos han solicitado, es decir, vamos a generar en columna (Columns), elegimos Tipo Fecha (Date), los saltos o pasos que va a dar (Step value) digitamos 1 (porque vamos a generar fechas continuas) y finalmente hasta donde va a llegar (Stop value) que para el ejercicio será 25/05/2006, así:

Presionamos la tecla Aceptar (OK) y tendremos el siguiente resultado:

Página 7 de 62


Si nos fijamos únicamente están desplegados los días que corresponden de lunes a viernes.

Genere una serie lineal que comience en 1 y llegue hasta 20 Para generar la serie lineal lo primero que debemos hacer es digitar el primer valor, para el caso del ejercicio será 1, en la celda que queremos que comience a generar la serie, y presionamos la tecla enter, así:

Ubicamos el cursor en la celda que digitamos el 1, es decir la celda A1 y en el menú Inicio (Home) seleccionamos la opción Edición (Editing) y posteriormente seleccionamos Series (Series), así:

Página 8 de 62


Nos aparece la siguiente ventana en la cual procedemos a llenar con las opciones que nos han solicitado, es decir, vamos a generar en columna (Columns), elegimos Tipo Geométrica (Growth), los saltos o pasos que va a dar (Step value) digitamos 1 (porque la numeración va a ser continua) y finalmente hasta donde va a llegar (Stop value) que para el ejercicio será 20, así:

Presionamos la tecla Aceptar (OK) y tendremos el siguiente resultado

Página 9 de 62


Encontrar el valor en dólares del interés para cada uno de los valores de capital, emplear referencias: relativa, absoluta o mixta.

Lo primero que tenemos que hacer es ubicarnos en la celda correspondiente, es decir en la celda B2 y proceder a ingresar la fórmula que nos permitirá multiplicar el Capital por el Interés, así:

Página 10 de 10 de 62


Ahora, si copiamos la f órmula para el resto de la tabla, el resultado fin al será el siguiente:



BLOQUE 2: FUNCIONES Y FORMULAS Hoja 7. Usando la siguiente tabla de datos unifique en una columna el nombre y el apellido de los empleados.

Hoja 8. Usando las funciones de fecha, calcule la edad de los funcionarios f uncionarios que se encuentran en la siguiente tabla:

Hoja 9. El Gerente de la Empresa AAA, desea realizar un préstamo al Banco de 30.000 dólares, el Banco le concede dicho préstamo para que pague al 15% de interés a 6 años plazo, y debe pagar trimestralmente. Se necesita saber a cuánto asciende la cuota trimestral que debe cancelar el Gerente?. • Con los datos del del ejercicio anterior, se necesita saber cuánto de dinero está pagando el Sr.

Gerente por concepto de Capital y de Interés para el primer período.



Hoja 10.

Con la siguiente tabla de Notas se necesita saber a cuánto asciende la Nota T otal de cada uno de los estudiantes.

Hoja 11.

Con la siguiente tabla de datos, se necesita saber a cuánto ascienden las ventas por cada uno de los productos.

DESARROLLO DE LOS EJERCICIOS DEL BLOQUE 2 Usando la siguiente tabla de datos unifique en una columna el nombre y el apellido de los empleados. •

Para unificar el nombre y el apellido de cada uno de los empleados de la tabla, lo que debemos hacer Página 13 de 13 de 62


es usar el operador &, para ello ubicamos el cursor en la celda correspondiente y procedemos a insertar la fórmula, de la siguiente manera:

Cabe señalar que el operador & nos sirve para unificar y las comillas nos sirve para agregar un espacio en blanco entre el nombre y el apellido, caso contrario nos quedaría unido. Aplicando para toda la tabla tab la el resultado será el siguiente: sig uiente:

Usando las funciones de fecha, calcule la la edad de los funcionarios que que se encuentran en la siguiente tabla: •



Para realizar el cálculo de la edad de cada uno de los funcionarios, lo que debemos hacer es primero calcular la fecha actual utilizando la función correspondiente, así:

Como segundo paso, ubicamos el cursor en la celda correspondiente para calcular la edad y procederemos a restar la fecha actual - la fecha de nacimiento, así:

El resultado que se ha obtenido está expresado en días, ahora para transformarlo en años, lo que debemos hacer es dividir el número de días para los 365.24 que tiene un año (se le divide para Página 15 de 15 de 62


365.24 para considerar los años bisiestos), de la siguiente manera:

Para poder aplicar a toda la tabla, lo que debemos hacer es agregar referencia absoluta tanto en columna como en fila (Columna B, Fila 2) en la celda de la fecha actual porque esta celda tiene que ser constante para todo el cálculo, de la siguiente manera:

Aplicado para toda la tabla, el resultado final es el siguiente: s iguiente:



Cabe indicar que al resultado obtenido lo desplegaremos únicamente en años, por lo tanto, daremos formato a las celdas para eliminar los decimales, para ello, seleccionamos toda la columna y en el menú Inicio (Home), (Home) , opción Celdas (Cell), opción Format (Formato) (Formato) y opción Format Cells(Formato Cells(Formato Celda) de la siguiente manera:

Nos aparece la siguiente ventana, en la cual procederemos a elegir las opciones de Número (Number), Número de decimales (Decimal places): 0 Presionamos la tecla Aceptar (OK) y tendremos el siguiente resultado:

El Gerente de la Empresa AAA, desea realizar un préstamo al Banco de 30.000 dólares, el Banco le concede dicho préstamo para que pague al 15% de interés a 6 años plazo, y debe pagar trimestralmente. Se necesita saber a cuánto asciende la cuota trimestral que debe cancelar el Gerente? Página 17 de 17 de 62


El planteamiento del problema será de la siguiente manera:

Ahora para calcular a cuanto cuant o asciende lo que debe pagar el Gerente, Gere nte, usaremos usarem os la función correspondiente, de la siguiente manera:

La función Pago ( PMT ), nos sirve para calcular el pago, ahora dentro de la función lo que se está haciendo es: El Interés dividido para el número de trimestres (Porque el interés es cobrado trimestralmente), el plazo multiplicado por el número de trimestres que tiene el año (para obtener el número de pagos) y finalmente el préstamo, teniendo como resultado la cuota trimestral que debe cancelar el Gerente al Banco que asciende a USD. $ 1.917,57.

Con los datos del ejercicio anterior, se necesita necesita saber cuánto de dinero está pagando el Sr. Gerente por concepto de Capital y de Interés para el primer período. •

El planteamiento del problema será el siguiente:



Ahora aplicaremos la función f unción correspondiente para saber a cuanto asciende el valor va lor por concepto de capital que se encuentra pagando el Gerente de la Empresa AAA, de la siguiente manera:

Lo que se aplicó en la función fue: La función para obtener el pago de capital es Pagoprin (PPMT) y dentro de ella el interés dividido para el número de trimestres que tiene el año (porque el interés es pagado trimestralmente), luego el período de pago (que para el ejercicio es el período uno), luego el plazo multiplicado por el número de trimestres que tiene el año (para que nos dé como resultado el número total de pagos) y finalmente el préstamo. La fórmula nos dio un resultado que el Gerente debe cancelar solo por concepto de pago de capital la cantidad de USD. $ 792,57 trimestralmente. Ahora para calcular el interés usaremos usarem os la función correspondiente, de la siguiente siguient e manera:



Lo que se aplicó en la función fue: La función para obtener el pago de interés es Pagoint (IPMT) y dentro de ella el interés dividido para el número de trimestres que tiene el año (porque el interés es pagado trimestralmente), luego el período de pago (que para el ejercicio es el período uno), luego el plazo multiplicado por el número de trimestres que tiene el año (para que nos dé como resultado el número total de pagos) y finalmente el préstamo. La fórmula nos dio un resultado que el Gerente debe cancelar solo por concepto de pago de interés la cantidad de USD. $ 1.125,00 trimestralmente.

Con la siguiente tabla tabla de Notas se necesita saber a cuánto asciende la Nota Total de cada uno de los estudiantes. •

Para resolver el problema propuesto, ubicamos el cursor en la celda correspondiente y procedemos a usar la función Suma (Sum) que nos permitirá sumar los valores de Nota1, Nota2 y Nota3, de la siguiente manera:



Aplicado para toda la columna, colu mna, el resultado será el siguiente: si guiente:

Con la siguiente tabla tabla de datos, se necesita saber a cuánto ascienden las ventas por cada uno de los productos. •

Debido a que tenemos que realizar sumas usando un criterio de suma, plantearemos el problema de la siguiente forma: Página 21 de 21 de 62


Ahora realizarem os la suma del producto Camisa, Cam isa, en donde para poder sumar con un criterio cr iterio lo haremos con la función Sumar. si (Sumif), en donde primero debemos seleccionar el rango del cual vamos a obtener el criterio, seguido debemos poner el criterio que para el ejercicio será Camisa (El criterio si es texto deberá ir entre comillas) y posteriormente seleccionar el rango de suma que en este caso serán las ventas, de la siguiente manera:

Hacemos exactamente lo mismo con el producto pantalón, y el resultado es el siguiente:



Finalmente, realizamos la misma operación con el producto Corbata y el resultado final será el siguiente:

BLOQUE 3: Hoja 12.

Usando la función adecuada, evalúe la siguiente ecuación: Página 23 de 23 de 62


Hoja 13.

Usando la siguiente tabla de datos, obtenga la raíz cuadrada de las ventas.

Hoja 14.

Usando la siguiente tabla, y usando la función adecuada redondee, a 3 decimales, los valores de la raíz cuadrada que se obtuvo aplicándola a la columna ventas.



Hoja 15.

En la siguiente tabla de datos, genere números aleatorios comprendidos del 200 al 800 para los meses de Enero, Febrero y Marzo.



DESARROLLO DEL BLOQUE 3 Con la siguiente tabla tabla de datos, calcule las ventas de cada uno de los productos. •

Para calcular las ventas de cada uno de los productos, lo que tenemos que hacer es realizar una multiplicación de la Cantidad por el Valor Unitario usando la función que corresponda, en este caso será Producto (Product), por lo tanto, ubicamos el cursor en la celda que corresponda y posteriormente aplicamos la función, así:

Aplicado para toda la tabla tendremos el siguiente resultado: resulta do:



Usando la función adecuada, evalúe la siguiente ecuación:

Para realizar la evaluación de la ecuación presentada, ubicamos el cursor cursor en la primera celda correspondiente, y con el uso de la función Potencia (Power) procedemos a ir reemplazando los valores, de la siguiente manera: =POTENCIA ((A7^4+2);1/3)+5*A7 ó =POWER ((A7^4+2); 1/3) + 5*A7 Las fórmulas anteriores también podrían solucionarse así: =((B5^4+2)^(1/3))+5*(B5)



Aplicando para toda la tabla, tab la, tendremos tendrem os el siguiente resultado:

Ahora, podemos llegar al a l mismo resultado sin usar la función, únicam ente reemplazando los valores de la siguiente manera: Página 28 de 28 de 62


= ((A7^4+2)^(1/3))+5*(A7)

Aplicado para toda la tabla tendremos el siguiente resultado: resulta do:



•

Usando la siguiente tabla de datos, obtenga la raíz cuadrada de las ventas.

Para obtener la raíz cuadrada de las ventas, ubicamos el cursor en la celda correspondiente y aplicamos la función correspondiente para obtener la raíz cuadrada que es Sqrt (Sqrt), de la siguiente manera:

Aplicado para toda la tabla tendremos el siguient e resultado:

Usando la siguiente tabla, y usando la función adecuada ade cuada redondee los valores Página 30 de 30 de 62


de la raíz cuadrada con 3 decimales.

Para redondear los valores de la raíz cuadrada, ubicamos el cursor en la celda correspondiente y aplicamos la función que corresponde, en este caso será Redondear (Round), así:

Aplicando a toda la tabla tendremos el siguiente resultado:

Copiamos la misma fórmula para las demás celdas y tendremos el siguiente resultado: Página 31 de 31 de 62


Copiamos la misma fórmula para las demás celdas y tendremos el siguiente resultado:

Cuando se trabaja con la función Aleatorio.entre (Randbetween), y para que no se cambien los valores, lo que debemos hacer es copiar los valores (todas las celdas que tiene la función) y volver a pegarla como valor, de la siguiente manera:



Una vez que se ha copiado, en el menú Inicio (Home), elegimos la opción de Pegar (Paste) y Pegado Especial (Paste Special)

En la ventana que nos aparece, elegimos la opción Valor (Value), así:



Presionamos la tecla Aceptar (OK), y tendremos el siguiente resultado:



BLOQUE 4: Función Y (AND) Hoja 16.

En la columna K, discrimine la información i nformación de acuerdo a los siguientes criterios:

Si la Escuela es igual a Comercio y el Género es igual a Masculino y la Edad es mayor o igual a 20

Para discriminar la información de acuerdo a los criterios que solicita debemos usar la función Y (AND) que nos sirve cuando tenemos dos o más condiciones que queremos que obligatoriamente se cumplan. El formato de la función es: Y(condición1;condición2;..condiciónN)

AND(logical1;logical2;...logicalN) Aplicando a la tabla de datos dat os el resultado será:



El valor que devuelve será: Verdadero (True) si las condiciones especificadas se cumplen, caso contrario si no cumplen devolverá el valor lógica Falso (False), como se muestra a continuación:



Función O (OR) Hoja 17.

En la columna K, discrimine la información i nformación de acuerdo a los siguientes criterios:

Si la Escuela es igual a Comercio ó el Género es igual a Masculino ó la Edad es mayor o igual a 20

Para discriminar la información de acuerdo a los criterios que solicita debemos usar la función O (OR) que nos sirve cuando tenemos dos o más condiciones y queremos que obligatoriamente se cumpla al menos una condición, pero no todas las condiciones.

El formato de la función es:

O(condición1;condición2;..condiciónN) OR(logical1;logical2;...logicalN) Aplicando a la tabla de datos dat os el resultado será:



El valor que devuelve será: Verdadero (True) si alguna de las tres condiciones especificadas se cumple, caso contrario si no cumple ninguna condición devolverá el valor lógico Falso (False), como se muestra a continuación:



Función NO (NOT) Hoja 18.

En la columna K, discrimine la información i nformación de acuerdo a los siguientes criterios: Si la Escuela no es igual a Comercio

Para discriminar la información de acuerdo a los criterios que solicita debemos usar la función NO (NOT) que nos sirve cuando queremos validar alguna información, por ejemplo para el caso del ejemplo validaremos a aquellas personas que sean de la Escuela de Comercio. El formato de la función es:

NO(condición) NOT(logical) Aplicando a la tabla de datos dat os el resultado será:



El valor que devuelve será: Verdadero (True) si la condición especificada no se cumple, caso contrario si cumple la condición devolverá el valor lógico Falso (False), como se muestra a continuación:



Función SI (IF) Hoja 19.

Discrimine la inform ación de acuerdo acuerdo a:

Si la Nota Total es mayor igual a 15 por valor verdadero Aprobado, caso contrario Reprobado

Para discriminar la información de acuerdo a los criterios que solicita debemos usar la función SI (IF) que nos sirve cuando tenemos una condición y queremos que realice una operación o actividad si se cumple dicha condición. El formato de la función es:

SI(condición;Valor por verdad;Valor por falso) IF(logical_test;value_if_true;value_if_false) Aplicando a la tabla de datos dat os el resultado será:



El valor que devuelve será: Aprobado si la condición especificada se cumple, caso contrario si no cumple la condición devolverá el mensaje de Reprobado, como se muestra a continuación:



Funciones lógicas anidadas Hoja 20.

Usando las funciones lógicas determine lo siguiente:



Si Nota total es mayor igual a 15 entonces aprobado



Si Nota total es mayor igual a 10 y menor que 15 entonces entonces Suspenso



Si Nota total es menor que 10 entonces Reprobado

Usando lo aprendido anteriormente y unificando en una sola fórmula, el resultado que tendríamos sería el siguiente:



El valor que devuelve será: Si la primera condición es mayor igual a 15 entonces por valor verdadero devolverá el mensaje de Aprobado, por valor falso (como tenemos más condiciones), pregunta de nuevo, Si, Y, la nota es mayor igual a 10 y menor que 15, por valor verdadero devolverá el mensaje Suspenso, y por valor falso devolverá el mensaje de Reprobado (En este último caso no necesitamos añadir otro Si (IF) porque con los dos Si (If) está controlando todos los valores que se encuentran desde 10 hasta N)

Aplicando a toda la tabla, el e l resultado final será el siguiente: si guiente:





Función Estadística Contar (Count) Hoja 21.

Usando la siguiente tabla, determine cuantos alum nos existen: existen:

Para determinar cuántos alumnos existen debemos usar la función Contar (Count) que nos sirve para contar únicamente con campos numéricos. De la siguiente manera:



Función Estadística Contar C ontar.si .si (Countif) Hoja 22.

Resuelva los siguientes problemas en base a la tabla suministrada:



Contar los alumnos que pertenezcan a la Escuela de Informática



Contar los alumnos que pertenezcan a la Escuela de Comercio



Contar los alumnos que pertenezcan a la Escuela de Hotelería Contar los alumnos que pertenezcan a la Escuela de T urismo



Para determinar cuántos alumnos existen debemos usar la función Contar.si (Countif) que nos sirve para contar cuando tenemos un criterio. El formato de la función es:

Contar.si(Rango;Criterio) Countif(Range;Criteria) Aplicado tendremos tendrem os el siguiente resultado:



Aplicamos la misma m isma función para los alumnos alum nos de Comercio y el resultado será:



Aplicamos para los alum nos de la Escuela de Hotelería y tendremos el siguiente resultado:



Realizamos lo mismo con la Escuela de Turismo y el resultado será:



Los resultados finales serán:





Función Estadística Promedio (Average) Hoja 23.

Determine el promedio de la columna Valor m atrícula

Para determinar el promedio de un rango, lo que debemos usar es la función Promedio (Average), de la siguiente manera:



Función Estadística Desvest (Stdev) Hoja 24.

Determine la desviación Standard de la columna Valor matrícula.

Para determinar la Desviación Standard de un rango, lo que debemos usar es la función Desvest (Stdev), de la siguiente manera:





Función Estadística Max (Max) Hoja 25.

Determine el valor más alto de la columna Valor matrícula. m atrícula.

Para determinar el valor máximo de un rango, lo que debemos usar es la función Max (Max), de la siguiente manera:





Función Estadística Min (Min) Hoja 26.

Determine el menor valor de la columna Valor matrícula.

Para determinar el menor valor de un rango, lo que debemos usar es la función Min (Min), de la siguiente manera:



Función Estadística Mediana (Median) Hoja 27.

Determine la mediana de la columna Valor matrícula. m atrícula.



Para determinar la mediana de un rango, lo que debemos usar es la función Mediana (Median), de la siguiente manera:

Función Estadística Moda (Mode) Hoja 28.

Determine la moda de la columna Valor matrícula. m atrícula.



Considerando que la función Moda (Mode), despliega el valor que mayor número de veces se repite, se la usa de la siguiente manera:


Maraton de Excel(1)

Recommend Documents