Dominio de Microsoft Excel: Casos Prácticos y Soluciones Técnicas

1. Vídeos Southridge

T1: Rellenar Serie de Previsiones

Enunciado: En la hoja “Ventas regionales”, en las celdas D4:F7, use la característica Rellenar serie para completar las previsiones de ventas usando una tasa de crecimiento lineal de 500,000 al año.

Desarrollo: En la hoja Ventas regionales, seleccione C4:F7, vaya a Inicio > Rellenar > Series y configure la serie en: Filas, Tipo: Lineal, Incremento: 500000 y Límite: vacío. Luego haga clic en Aceptar. No seleccione encabezados ni la fila Total.

T2: Formato Condicional de Valores Inferiores

Enunciado: En la hoja “Vídeos populares”, para las celdas B4:C17, cree una regla de formato condicional que muestre los cinco valores inferiores en fuente Rojo oscuro en negrita.

Desarrollo: En la hoja Vídeos populares, seleccione B4:C17, vaya a Inicio > Formato condicional > Reglas superiores e inferiores > 10 inferiores, cambie el número a 5. En el formato, elija Formato personalizado, configure la fuente en Rojo oscuro y active Negrita. Luego haga clic en Aceptar y nuevamente en Aceptar.

T3: Búsqueda Horizontal con Coincidencia Exacta

Enunciado: En la hoja “Empleados”, en la celda C4, escriba una fórmula que devuelva el número de teléfono de la oficina del rango de celdas “Información_del_Contacto” (J2:M4) con una coincidencia exacta con la “Región” de la columna B.

Desarrollo: En la hoja Empleados, seleccione la celda C4 y escriba la fórmula: =BUSCARH(B4;Información_del_Contacto;3;FALSO). Luego presione Enter. Esta fórmula busca la región de B4 en la primera fila del rango Información_del_Contacto y devuelve el teléfono de oficina desde la tercera fila, usando coincidencia exacta. Para verificar qué celdas componen “Información_del_Contacto”, vaya a Fórmulas > Administrador de nombres; allí podrá ver todos los nombres definidos del libro, sus celdas correspondientes y su ubicación.

T4: Grabación de Macro para Encabezado

Enunciado: En la hoja “Empleados”, cree una macro denominada “Encabezado”. Guarde la macro en el libro actual. Configure la macro de forma que muestre “Vídeos Southridge” en la celda de encabezado izquierdo de la hoja activa.

Desarrollo: En la hoja Empleados, vaya a Vista > Macros > Grabar macro, escriba el nombre Encabezado y en Guardar macro en seleccione Este libro. Luego vaya a Diseño de página > Configurar página, entre a Encabezado y pie de página > Personalizar encabezado, escriba Vídeos Southridge en la sección izquierda del encabezado, acepte los cambios y finalmente vaya a Vista > Macros > Detener grabación.

T5: Agrupación de Datos en Tabla Dinámica

Enunciado: En la hoja “Análisis de planes”, modifique la Tabla dinámica para agrupar los datos por los valores de la columna “Precio del paquete completo”. Agrupe los valores en incrementos de 100 empezando en 1 y terminando en 200.

Desarrollo: En la hoja Análisis de planes, haga clic en cualquier valor de la columna Precio del paquete completo de la tabla dinámica, luego vaya a Herramientas de tabla dinámica > Analizar > Agrupar campo. En la ventana Agrupar, escriba 1 en Comenzar en, 200 en Terminar en y 100 en Por. Finalmente, haga clic en Aceptar.

2. Editorial Lucerne

T1: Configuración de Seguridad de Macros

Enunciado: Configure Excel de forma que se desactiven todas las macros del libro sin que aparezca ninguna notificación.

Desarrollo: Vaya a Archivo > Opciones > Centro de confianza > Configuración del Centro de confianza > Configuración de macros, seleccione Deshabilitar todas las macros sin notificación, haga clic en Aceptar y nuevamente en Aceptar para guardar la configuración.

T2: Formato de Fecha Personalizado

Enunciado: En la hoja “Nuevos títulos”, para las celdas E4:E24, cree y aplique un formato de número personalizado que muestre las fechas en el formato “2020 enero 01”.

Desarrollo: En la hoja Nuevos títulos, seleccione E4:E24, presione Ctrl + 1 o vaya a Inicio > Número > Más formatos de número, entre a Personalizada y en Tipo escriba aaaa mmmm dd o yyyy mmmm dd. Luego haga clic en Aceptar.

T3: Función Lógica SI para Cálculo de Regalías

Enunciado: En la hoja “Regalías”, la fórmula de la columna “Derechos de autor adeudados” calcula las regalías adeudadas a cada autor. Modifique la fórmula para que se muestre la cantidad solo si es mayor que 25, y en caso contrario muestre 0.

Desarrollo: En la hoja Regalías, seleccione la primera celda de la columna Derechos de autor adeudados y modifique la fórmula por: =SI((C4*D4)-E4>25;(C4*D4)-E4;0). Luego presione Enter y verifique que la fórmula se aplique al resto de la columna.

T4: Segmentación de Datos

Enunciado: Aplicar segmentación por categoría y mostrar únicamente Psicología.

T5: Filtro en Gráfico Dinámico

Enunciado: En la hoja “Análisis de regalías”, añada el campo “Id. de título” como un filtro del gráfico dinámico. Aplique el filtro al gráfico para que muestre solo los resultados del ID de título “CO20”.

3. Parques Nacionales

T1: Configuración de Idioma

Enunciado: Agregar el idioma Albanés como idioma de edición.

Desarrollo: Vaya a Archivo > Opciones > Idioma. En Idiomas de edición, seleccione Albanés, haga clic en Agregar y luego en Aceptar.

T2: Formato Condicional con Fórmulas

Enunciado: En la hoja “Visitantes de 2019”, modifique la regla de formato condicional para formatear las filas de los parques con un “Tamaño” superior a 1000 kilómetros cuadrados.

Desarrollo: En la hoja Visitantes de 2019, vaya a Inicio > Formato condicional > Administrar reglas, seleccione la regla existente y haga clic en Editar regla. En la opción Utilice una fórmula que determine las celdas para aplicar formato, escriba =$E5>1000, mantenga el formato indicado y haga clic en Aceptar hasta cerrar las ventanas.

T3: Consolidación de Datos

Enunciado: En la hoja “Resumen”, empezando por la celda A4, consolide los datos de las hojas “2017 por región” y “2018 por región”. Muestre el número medio de “Visitas recreativas” de cada “Región”. Use etiquetas en la fila superior y la columna izquierda. Elimine la columna “Nombre del parque” vacía de los datos consolidados.

Desarrollo: En la hoja Resumen, seleccione A4 y vaya a Datos > Consolidar. En Función, elija Promedio. Agregue las referencias de las hojas 2017 por región y 2018 por región usando los rangos de datos completos, marque Fila superior y Columna izquierda, y haga clic en Aceptar. Luego elimine la columna vacía Nombre del parque de la tabla consolidada.

T4: Exploración de Datos en Gráfico Dinámico

Enunciado: En la hoja de gráfico “Análisis de voluntarios”, profundice en los datos para mostrar el número de horas de voluntarios de cada mes.

Desarrollo: En la hoja Análisis de voluntarios, seleccione el gráfico dinámico y use Herramientas del gráfico dinámico > Analizar > Explorar en profundidad hasta que el gráfico muestre los datos por Mes. Asegúrese de que el eje del gráfico quede desglosado por meses y no solo por año o trimestre.

4. Juguetes Tailspin

T1: Eliminación de Registros Duplicados

Enunciado: En la hoja “Inventario”, use una característica de Excel para quitar los registros duplicados del rango de celdas “Productos”.

Desarrollo: En la hoja Inventario, seleccione una celda dentro del rango Productos, vaya a Datos > Quitar duplicados, deje seleccionadas todas las columnas del rango y asegúrese de que esté marcada la opción Mis datos tienen encabezados. Luego haga clic en Aceptar. Nota: Puede verificar el rango en Fórmulas > Administrador de nombres.

T2: Búsqueda Vertical con Referencias Absolutas

Enunciado: En la hoja “Empleados”, en la celda F4, escriba una fórmula que devuelva la bonificación del empleado a partir de la tabla “Bonificación por años de servicio”. Ajuste la fórmula y cópiela en las celdas F5:F19.

Desarrollo: En la hoja Empleados, seleccione la celda F4 y escriba =BUSCARV(D4;$H$2:$I$7;2;VERDADERO). Luego presione Enter y copie la fórmula hasta F19. Use referencias absolutas en la tabla ($H$2:$I$7) para que no se mueva al copiarla.

T3: Auditoría de Fórmulas: Rastrear Dependientes

Enunciado: En la hoja “Operaciones minoristas”, muestre flechas que indiquen que los valores de las celdas dependen del valor de C4.

Desarrollo: En la hoja Operaciones minoristas, seleccione la celda C4, vaya a Fórmulas > Rastrear dependientes dentro del grupo Auditoría de fórmulas.

T4: Creación de Histograma

Enunciado: En la hoja “Nuevos productos”, cree un gráfico Histograma que muestre el “precio de venta al por menor” de los productos en rangos con anchos de $10.

Desarrollo: En la hoja Nuevos productos, seleccione los datos de la columna Precio de venta al por menor, vaya a Insertar > Insertar gráfico estadístico > Histograma. Luego haga clic derecho en el eje horizontal del gráfico, seleccione Dar formato al eje y configure el Ancho del intervalo en 10.

5. Cursos de Esquí

T1: Opciones de Cálculo

Enunciado: Cambiar el cálculo del libro a modo manual.

Desarrollo: Vaya a Archivo > Opciones > Fórmulas > Opciones de cálculo y seleccione Manual.

6. Vanarsdel Limited

T1: Protección de la Estructura del Libro

Enunciado: Exija que los usuarios escriban la contraseña “P@ssword” para poder realizar cambios estructurales en el libro.

Desarrollo: Vaya a Revisar > Proteger libro, marque la opción Estructura, escriba la contraseña P@ssword y haga clic en Aceptar. Confirme la contraseña escribiéndola de nuevo y haga clic en Aceptar.

T2: Validación de Datos con Mensaje de Error

Enunciado: En la hoja “Conferencia de ventas”, configure las celdas A4:A12 para permitir solo números enteros comprendidos entre el 1 y el 9. Si se escribe otro número, muestre un error con el título “No válido” y el mensaje “Del 1 al 9”.

Desarrollo: En la hoja Conferencia de ventas, seleccione A4:A12, vaya a Datos > Validación de datos. En Configuración, elija Permitir: Número entero, Datos: entre, Mínimo: 1 y Máximo: 9. En la pestaña Mensaje de error, escriba el título No válido y el mensaje Del 1 al 9. Haga clic en Aceptar.

T3: Función PROMEDIO.SI.CONJUNTO

Enunciado: En la hoja “Ventas anuales acumuladas”, inserte una fórmula en las celdas L5:L15 que muestre la media de “Ventas totales” para la “Región” correspondiente en la columna J y el “Representante” en la columna K.

Desarrollo: En la hoja Ventas anuales acumuladas, seleccione la celda L5 y escriba: =PROMEDIO.SI.CONJUNTO($H$4:$H$56;$B$4:$B$56;J5;$A$4:$A$56;K5). Luego presione Enter y copie la fórmula hasta L15.

T4: Cálculo de Días Laborables

Enunciado: En la hoja “Conferencia de ventas”, en la columna “Fecha límite del registro”, use una función para mostrar una fecha límite que sea 45 días laborables anterior a la fecha de la columna E, excluyendo los días del rango “Vacaciones”.

Desarrollo: En la hoja Conferencia de ventas, seleccione la celda D4 y escriba: =DIA.LAB.INTL(E4;-45;1;Vacaciones). El parámetro 1 indica fines de semana de sábado y domingo.

T5: Gráfico Combinado con Eje Secundario

Enunciado: En la hoja “Resumen de ventas”, cree un gráfico que muestre las “Ventas totales” como Área y el “Porcentaje del total general” como Línea en el eje secundario.

Desarrollo: Seleccione los datos de Representante, Ventas totales y Porcentaje del total general (A3:A12 y F3:G12). Vaya a Insertar > Gráfico combinado, configure Ventas totales como Área, Porcentaje del total general como Línea y marque Eje secundario para el porcentaje.

T6: Jerarquía en Tablas Dinámicas

Enunciado: En la hoja “Ventas regionales”, modifique la Tabla dinámica para mostrar las filas de “Territorio” dentro de cada región.

Desarrollo: Seleccione la tabla dinámica y, en el panel Campos de tabla dinámica, arrastre el campo Territorio al área Filas, situándolo debajo de Región.

7. Censo

T1: Modificación de Mensaje de Validación

Enunciado: Cambie el mensaje de error de validación de las celdas K4:K53 a “Escriba un número del 1 al 50”.

Desarrollo: Seleccione K4:K53, vaya a Datos > Validación de datos > Mensaje de error y actualice el texto. No altere la configuración de validación previa.

T2: Escala de Colores Condicional

Enunciado: En las celdas H4:H53, aplique una Escala de 3 colores: mínimo en amarillo, punto intermedio en verde claro y máximo en verde.

Desarrollo: Seleccione H4:H53, vaya a Inicio > Formato condicional > Escalas de color > Más reglas. Elija Escala de 3 colores y asigne los colores correspondientes.

T3: Búsqueda de Datos en Tablas

Enunciado: En la columna J, escriba una fórmula que devuelva la población de cada estado usando los datos de la hoja “2010”.

Desarrollo: En J4, escriba: =BUSCARV([@Estado];Tabla1[[Estado]:[Población]];2;FALSO).

T4: Campo Calculado en Tabla Dinámica

Enunciado: Cree un campo calculado llamado “Cambio” que muestre el aumento de población desde 1970 hasta 2000.

Desarrollo: En la tabla dinámica, vaya a Analizar > Campos, elementos y conjuntos > Campo calculado. Nombre: Cambio; Fórmula: ='Población en 2000'-'Población en 1970'.

8. Alquiler de Automóviles

T1: Desactivación de Macros

Desarrollo: Vaya a Archivo > Opciones > Centro de confianza > Configuración de macros y seleccione Deshabilitar todas las macros sin notificación.

T2: Protección Estructural

Desarrollo: En Revisar > Proteger libro, use la contraseña 123 para proteger la estructura.

T3: Promedio con Múltiples Criterios

Enunciado: Calcular el importe medio de “Total de la factura” para motores “Diésel” y facturas de tipo “Accidente”.

Desarrollo: Use la fórmula: =PROMEDIO.SI.CONJUNTO(J2:J219;D2:D219;"Diésel";G2:G219;"Accidente").

T4: Corrección de Errores de Fórmula

Desarrollo: Use Fórmulas > Comprobación de errores. En la celda I212, seleccione Copiar fórmula de arriba para corregir la inconsistencia del impuesto (10,25%).

T5: Segmentación de Tabla Dinámica

Desarrollo: Inserte una segmentación por Tipo de factura y filtre por Mantenimiento.

9. Centro de Esquí Alpine

T1: Uso de Subtotales

Enunciado: Calcular el número total de clases y el total adeudado por instructor.

Desarrollo: Ordene por Instructor. Vaya a Datos > Subtotal. Configuración: Para cada cambio en: Instructor, Usar función: Suma, Agregar subtotal a: Número de clases y Total adeudado.

T2: Edición de Regla de Formato Condicional

Desarrollo: En Administrar reglas, edite la regla para el texto “Todos los días”. Cambie el formato a Negrita Cursiva y establezca el relleno en Sin color.

T3: Función DIASEM

Enunciado: Calcular el día de la semana de la fecha en B13.

Desarrollo: En B14, escriba =DIASEM(B13). El formato de celda se encargará de mostrar el nombre del día.

T4: Gráfico Combinado en Eje Único

Desarrollo: Seleccione A3:C13. Inserte un gráfico combinado: Salario como Área e Ingresos generados como Línea. No active el eje secundario.

T5: Estilo y Diseño de Gráfico

Desarrollo: Aplique Diseño 3, Estilo 4, Paleta monocromática 8 y cambie el título a “Ingresos por clase”.

10. Viajes Margie

T1: Idiomas de Edición Adicionales

Desarrollo: Agregue Albanés en las opciones de idioma sin establecerlo como predeterminado.

T2: Limpieza de Duplicados en Rangos Específicos

Desarrollo: Seleccione A4:M48 y use Datos > Quitar duplicados. Desmarque Mis datos tienen encabezados.

T3: Conteo con Criterios de Texto y Valor

Desarrollo: En J4, use: =CONTAR.SI.CONJUNTO(C4:C109;"Verano";E4:E109;">7").

T4: Función NPER para Préstamos

Enunciado: Calcular el número de meses para pagar el préstamo.

Desarrollo: En B7, escriba =NPER(B4/B6;B5;B3).

T5: Gráfico de Embudo

Desarrollo: Seleccione las descripciones y los datos de junio. Inserte un gráfico de Embudo y titúlelo “Resultados de junio”.

T6: Gráfico Dinámico Circular

Desarrollo: Inserte un gráfico circular desde la tabla dinámica y aplique un filtro para mostrar solo Casa de campo.