EXCEL PARA PROFESIONALES

Laboratorio 2

Laboratorio 2 Informática

Pág. 2 Dpto. de Informática

Microsoft Excel 2007 Creación de Fórmulas con diferentes categorías de Funciones

Dpto. de Informática Pág. 1

Hoja de trabajo # 4

1. Realizar el siguiente cuadro (Ud. Elija el tipo de letras y colores).

Dentro del cuadro usar las funciones Contar.Si, Contar, Contara y Sumar.Si, para hallar los siguientes datos.

a. Encontrar la cantidad de nombre obtenidos. b. Hallar cuantos apellidos faltan completar en el cuadro

c. Encontrar la cantidad de años de nacimiento que fueron escritos en el cuadro. d. Encontrar el número de hombres y el número de mujeres.

e. Hallar la sumatoria de todos los sueldos.

f. Hallar cuanto suman todos los sueldos superiores a 1000 soles

g. Hallar cuanto suman todos los sueldos menores de 600 soles.

h. Guardar como Hoja de Trabajo #5

Creación de Fórmulas con diferentes categorías de Funciones Microsoft Excel 2007

Pág. 2 Dpto. de Informática

Hoja de trabajo # 5

Procedimiento y Resultados: 2. Abrir el archivo Presupuesto.xlsx que se encuentra en la unidad de red. Uso de Fórmulas con Referencias

En la hoja: Proyección, utilice las Funciones de Texto más adecuadas, tenga en cuenta que deberá utilizar Referencias 3D:

a. En la celda A2 de la hoja Proyección, escriba la función que permita convertir en

mayúscula el texto que se encuentra en la celda A1 de la hoja Datos.

b. En la celda A7 de la hoja Proyección, escriba la función que permita convertir en

mayúscula la primera letra del texto que se encuentra en la celda A2 de la hoja

Datos. Complete los títulos hasta la celda I7.

c. Hallar Comisión (D8): Es el resultado de aplicar la Comisión Base (B33) sobre las

Ventas/mes, copie la fórmula. Vea modelo.

d. Hallar Total incentivo (E8): Es el resultado de aplicar el Incentivo % respectivo por

las Ventas/mes, copie la fórmula.

e. Hallar Renta 4ta (F8): Es el resultado de aplicar la Renta 4ta (B34) a las

Ventas/mes, copie la fórmula.

f. Hallar Pago Total: Es la suma de los ingresos del empleado menos sus descuentos.

g. Guarde su Archivo.

Uso de Funciones Matemáticas, Estadísticas y de Texto

En la misma hoja: Proyección, realice las operaciones necesarias, utilizando las Funciones Matemáticas y Estadísticas más adecuadas:

a. Hallar los Totales para las Ventas/mes, Comisión, Total incentivo, Renta 4ta. Bono

Cumpleaños y Pago Total, Promedio de ventas del mes (B24) y la Venta mínima (B25).

b. Encuentre en B27 la cantidad de vendedores que han superado las ventas en 17000000. Usar contar.si

c. Encuentre en B28 la cantidad de vendedores que han vendido menos de 18 . d. Modifique la fórmula de las columnas Comisión, Total Incentivo y Renta 4ta., para

que se muestren redondeados a 2 decimales. Guarde su Archivo. En la misma hoja: Proyección, realice las operaciones necesarias, utilizando las Funciones de Texto más adecuadas. a. En la celda B30 calcular el número de caracteres de la celda A13. b. En la columna Nombre Usuario, deberá escribir una función anidada (una función

dentro de otra), que permita obtener la primera letra del nombre (A8) y concatenarlo (&) con todo el apellido que se encuentra a partir de la celda A5 de la hoja Datos. Adicionalmente todo este Nombre de Usuario se debe mostrar en minúsculas.

Guarde su Archivo.

Microsoft Excel 2007 Creación de Fórmulas con diferentes categorías de Funciones

Dpto. de Informática Pág. 3

Uso de Funciones de Fecha/Hora y Financieras

En la hoja: Fechas, realizar lo siguiente: (3 puntos)

a. En la columna C, calcular la Edad del empleado. Debe ingresar una fórmula que calcule su edad, tome como referencia la fecha de nacimiento (Fnacimiento) y la fecha de hoy (utilice la función adecuada). Por lo tanto los resultados no serán iguales a los del modelo. Utilice un año de 360 días.

b. En la columna E: Día Ingreso, utilice la función adecuada para determinar el día de la semana correspondiente a la Fecha de ingreso.

c. En la columna H: H.Trabajo, calcule la cantidad de horas que trabaja cada empleado, que es la diferencia entre la hora de salida (H. salida) y la hora de entrada (H. entrada).

Guarde su Archivo.

Aplicación de Formato de Celdas y Estilos En la hoja: Fechas realizar los siguientes formatos de celdas:

a. La columna C: Edad, deberá mostrarla en años. El resultado debe aparecer en

formato numérico, con 2 decimales para ver su edad más real. Centrar los datos. b. En la columna E: Día Ingreso, para que se muestre como en el modelo, deberá

aplicarle el siguiente formato personalizado: dddd. Centrar esta columna. c. En las columnas F y G: H.Entrada y H.Salida, aplíquele los formatos personalizados

para que se muestre como en el modelo. d. La columna H: H.Trabajo los valores deberán estar centrados. e. Centrar los títulos (A4:H4), colocarle negrita y cursiva, decida un color de fuente y el

color de relleno. A todos los datos coloque bordes: contorno y verticales, como en el modelo.

f. Todas las columnas tendrán un ancho de 12. Ajuste los títulos y céntrelos verticalmente.

Guarde su Archivo.

Creación de Fórmulas con diferentes categorías de Funciones Microsoft Excel 2007

Pág. 4 Dpto. de Informática

En la hoja: Proyección aplicar los Estilos indicados:

a. A la celda A2 aplicar el Estilo de celda: Título. Combinar y centrar en función a las columnas inferiores, ver modelo.

b. A las celdas (A7:I7) aplicar estilo de celda: Énfasis1. Céntrelas, ajuste el texto y alinear al medio.

c. A las celdas (A22:H22) aplicar estilo de celda: Total. d. Eliminar las filas 7, 20 y 21. e. A las celdas: A21:A22, A24:A26, A27 y A29:A31, aplicar estilo: Énfasis 5. f. A las celdas: B21:B22, B24:B25, B27 y B29:B31, colocarle el Estilo: 20%-Énfasis 5. g. Crear el siguiente estilo:

Nombre: Su_Apellido. Formato: En Número: Contabilidad S/. y 2 posiciones decimales. Fuente:

Tahoma, color azul oscuro. Bordes: Líneas delgadas color rojo oscuro. Relleno: color celeste. Aplicar este estilo a las celdas: A7:I17.

h. Colocar a la columna Incentivo %, colocarlo en formato Porcentaje, 1 decimal y centrado.

Guarde su Archivo.

TECSUP FORMATOS AVANZADOS

Pág. 5

Modelo Final de la Hoja: Proyección

Creación de Fórmulas con diferentes categorías de Funciones Informática I

Pág. 6 Dpto. de Informática

Hoja de trabajo # 6

3. Abrir el archivo Alumnos_notas.xlsx. Importar Datos 4. Renombre la primera hoja del libro recién creado con el nombre Origen.

5. En la columna L: Promedio, encontrar el promedio de notas (promedio simple), redondearlo a 1 decimal.

6. Coloque a la tabla de datos el formato que crea conveniente. 7. Seleccione la columna Facultad y ordénela en forma ascendente. 8. Coloque un color de sombreado diferente a cada uno de los 4 grupos de alumnos

(facultades). Ejemplo:

Guarde su archivo.

Informática I Creación de Fórmulas con diferentes categorías de Funciones

Dpto. de Informática Pág. 7

Ordenamiento de Datos

9. Cambiar el nombre de la Hoja2 por: Orden x Facultad-Notas, copiar los datos de la hoja Origen y mostrar los datos en la nueva hoja en: Orden descendente por Facultad y mostrando la Nota Parcial de mayor a menor.

Guarde su archivo.

10. Cambiar el nombre de la Hoja3 por: Orden x Sexo - Promedio, copiar los datos de la hoja Origen y mostrar los datos en la nueva hoja en: Orden ascendente por Sexo. y mostrando la columna Promedio en forma ascendente.

Guarde su archivo.

Creación de Fórmulas con diferentes categorías de Funciones Informática I

Pág. 8 Dpto. de Informática

Filtrado de Datos 11. Insertar una nueva hoja y colocarle el nombre: Filtro x Facultad-Masculino, copiar los

datos de la hoja Origen y mostrar en la nueva hoja a todos los alumnos que sean de la Facultad de Biología y Química, pero de Sexo masculino.

Guarde su archivo.

12. Insertar una nueva hoja y colocarle el nombre: Filtro x Año, copiar los datos de la hoja Origen y mostrar en la nueva hoja a todos los alumnos que hayan nacido en los años 1992 o 1995, pero en los meses de Enero a Junio.

Guarde su archivo. 13. Insertar una nueva hoja y colocarle el nombre: Filtro x Nombre - Promedio, copiar los

datos de la hoja Origen y mostrar en la nueva hoja a todos los alumnos cuyos nombres empiece por A o J y que su Promedio sea mayor a 12.

Guarde su archivo.

Informática I Creación de Fórmulas con diferentes categorías de Funciones

Dpto. de Informática Pág. 9

Filtro avanzado 14. Insertar una nueva hoja y colocarle el nombre Filtro Avanzado, copiar los datos de la

hoja Origen. Realizar un filtro avanzado considerando a aquellas alumnas que hayan obtenido un promedio mayor a 13. El resultado se deberá ver en la misma hoja Filtro Avanzado pero seleccionando un rango distinto al de la tabla original.

Subtotales 15. Insertar una nueva hoja y colocarle el nombre: Subtotales copiar los datos de la hoja

Origen y calcular el promedio de los alumnos por Facultad mostrando el Promedio de todas sus Notas.

Las celdas de los títulos de Promedio Agronomía, etc. colocarlas con ajuste de texto.

Combine los textos de Promedios, como se muestra en el modelo.

A las celdas (títulos y valores de promedios) centrarlas verticalmente,

Amplíe la altura de las filas que contengan el promedio de cada Facultad, para que se muestre como en el modelo.

A los valores promedios de las notas colóqueles el formato necesario, redondeado a 2 decimales y mostrando 2 decimales.

A estos promedios colocarles el Estilo: Notas.

Colocar los bordes necesarios para que quede como en el modelo.

Guardar su archivo.

Creación de Fórmulas con diferentes categorías de Funciones Informática I

Pág. 10 Dpto. de Informática

Funciones de bases de datos 16. Insertar una nueva hoja y colocarle el nombre: Funciones, copiar los datos de la hoja

Origen, y realice las siguientes acciones utilizando la función de base de datos que crea conveniente. a. Se quiere saber la cantidad de alumnos que pertenecen a la Facultad de Biología y

que han obtenido un promedio entre 13 y 15.

Función Resultado

b. Encontrar la mayor nota de la columna Promedio para los alumnos de Economía.

Función Resultado

c. Hallar la menor nota de la columna Promedio para los alumnos de Química.

Función Resultado

d. Obtener el promedio de las Notas Finales de todos los alumnos de la facultad de

Agronomía.

Función Resultado

Guardar y cerrar su archivo.