Universidad de El Salvador

Facultad de Ingeniería y Arquitectura

Escuela de Ingeniería de Sistemas Informáticos
Ofimática y Software para Ingeniería Industrial.

Ciclo I-2019
Guía No.1 De LABORATORIO
Ejercicios de aplicación Excel Unidad 3 y 4.
Ejercicios Formato condicional, validaciones, tablas
dinámicas, funciones de búsqueda y referencia y
formularios en Excel.

Introducción
Esta guía consta de tres partes. En la primera parte se desarrolla siete ejercicios completos dos
sobre formato condicional, uno sobre validaciones de celdas y cuatro ejercicios sobre tablas
dinámicas, la segunda parte trata sobre los ejercicios referidos al tema de funciones de búsqueda
y referencia, en la tercera parte se realizará el ejercicio correspondiente al tema formularios en
Excel, desarrollado en el Editor de Visual Basic incorporado en Excel, dichos ejercicios propuestos
son los que el estudiante desarrollará como actividad evaluada de laboratorio y serán entregados
en el aula virtual en la fecha correspondiente que se especifica en este documento.

Objetivo General:
● Adquirir el conocimiento y la habilidad para desarrollar los ejercicios de practica de
laboratorio referidos a los temas de la unidad 3 y unidad 4.

Objetivos Específicos:
● Conocer, comprender y aplicar los conocimientos adquiridos sobre el desarrollo de
ejercicios en Excel sobre formato condicional.
● Conocer, comprender y aplicar los conocimientos adquiridos sobre el desarrollo de
ejercicios en Excel para aprender a validar los datos contenidos en celdas, aplicando
diferentes tipos de validaciones.
● Aplicar los conocimientos adquiridos para poder interpretar las solicitudes de información
requeridas, dado un conjunto de datos, para luego crear una serie de tablas dinámicas
que permitan analizar la información y presentarla de manera resumida y organizada y
así poder tomar decisiones en base al análisis de dichos datos.
● Utilizar las funciones de búsqueda y referencia para así poder recuperar un conjunto de
datos dado un parámetro de búsqueda que el usuario ingresará.
● Creación y programación de un formulario VBA en Excel para poder trabajar con los datos
de una hoja de cálculo electrónica de manera más eficiente, aplicando los conocimientos
de diseño de interfaces, programación, uso de macros y funciones en lenguaje Visual Basic
para Aplicaciones para realizar un formulario que permita ingresar datos, realizar
cálculos, así como trasladar los resultados a una hoja de cálculo de Excel.
 UNIVERSIDAD DE EL SALVADOR EN LÍNEA
FACULTAD DE INGENIERÍA Y ARQUITECTURA
OFIMATICA Y SOFTWARE PARA INGENIERIA INDUSTRIAL

Equipo, Materiales y Herramientas:

● Computadora Personal con Windows.
● Conexión a Internet para entregar la actividad.
● Microsoft Excel.
● Uso de un documento de Excel habilitado para macros (.xlsm).

Metodología. El estudiante DEBE desarrollar los ejercicios planteados en la Parte I, II y III de esta
guía de laboratorio. Además, recuerde que el día de su tutoría previa a la fecha límite de entrega
de esta actividad, usted como estudiante podrá consultarle a su tutor alguna duda que tenga con
respecto al laboratorio 1.

Parte I. Resuelva en el archivo de Excel proporcionado los ejercicios de aplicación formato

condicional, validaciones y tablas dinámicas.

Ejercicios de formato condicional

Ejercicio 1: En la hoja de cálculo llamada Ejercicio 1 realizar el ejercicio siguiente:

En una hoja de cálculo una empresa lleva los nombres de sus vendedores y las ventas que han
realizado en cada uno de los primeros tres meses del año. También lleva en la misma hoja el
salario base y las comisiones que perciben por las ventas realizadas, (20%)
1. Calcula el total de ventas por cada trimestre y, la comisión de cada mes. El máximo, mínimo
y media de las ventas y de las comisiones.
 Comisiones: (venta del mes correspondiente) x comisión
 Salario del mes correspondiente: salario base + comisión del mes correspondiente
2. Introduce cada formula solo una vez, aunque tengas repetir el cálculo varias veces
3. Para calcular la comisión y el salario de cada mes utiliza referencias absolutas o relativas
(según corresponda)
4. Introduce el siguiente formato condicional:
a) Celdas Máximo ventas: Si es superior a $5,550: letra color rojo negrita y fondo amarillo.
b) Celdas Mínimo Ventas: Si es menor o igual a $ 4,000: letra color negro, fondo rosado
c) Celdas Promedio ventas: si es superior a $ 5.000, letra color negra, en negrita, fondo
rojo
d) Celdas Máximo Comisión: si es mayor de $1,000, letra color rojo negrita, fondo morado
e) Celdas mínimo comisión: si es inferior o igual a $800, letra color blanco negrita, fondo
azul.
f) Celdas Promedio Comisión: si es mayor de $900, letra blanca negrita, fondo negro

2
 UNIVERSIDAD DE EL SALVADOR EN LÍNEA
FACULTAD DE INGENIERÍA Y ARQUITECTURA
OFIMATICA Y SOFTWARE PARA INGENIERIA INDUSTRIAL

3
 UNIVERSIDAD DE EL SALVADOR EN LÍNEA
FACULTAD DE INGENIERÍA Y ARQUITECTURA
OFIMATICA Y SOFTWARE PARA INGENIERIA INDUSTRIAL

Ejercicio 2: En la hoja de cálculo llamada Ejercicio 2 realizar el ejercicio siguiente:

Dada la siguiente tabla, hacerla en Excel y aplicar lo siguiente.

 Calcule el 40% de la nota teórica y el 60% para el práctico, así como el promedio de teórico
y practica que se calcula así: =e5 + g5
 Aplicar formato condicional en la columna de nota de examen teórico, para qué muestre
barra de datos roja.
 En la columna de nota de examen práctico, aplicar formato condicional fondo color verde
si la nota es > 7
 En la columna de nota de examen práctico, aplicar formato condicional fondo naranja
para las notas que estén entre 6 y 7.
 En la columna examen práctico, aplicar formato condicional, fondo rojo claro para las
notas < 6.
 En la columna nota promedio colocar iconos de flecha de la manera siguiente: color verde
para mayores que 7, iconos naranjas para mayores o iguales a 6 y menor a 7, iconos rojos
para menores que 6.

4
 UNIVERSIDAD DE EL SALVADOR EN LÍNEA
FACULTAD DE INGENIERÍA Y ARQUITECTURA
OFIMATICA Y SOFTWARE PARA INGENIERIA INDUSTRIAL

Ejercicios de validaciones.

Ejercicio 3: En la hoja de cálculo llamada Ejercicio 3, Dada la siguiente tabla en Excel. Aplicar los
formatos condicionales que se piden a continuación.

Se pide:
1. Calcular el importe que se saca de: Cantidad * precio.
2. Calcular el 13% del IVA a la venta efectuada.
3. Calcular el total a cancelar.
4. Se pide aplicar las validaciones a los datos siguientes:
a. En la columna FABRICANTE, crear una lista de los fabricantes dados en la tabla del
rango K10:K12 (Samsung, iPhone y Sony), se debe de seleccionar el fabricante de
una lista desplegable, si se escribe otro tipo de fabricante que muestre una
ventana informándole que no puede hacer el ingreso.
b. En la Columna MODELO, crear una lista con los modelos de teléfonos que se
muestran en la tabla H10:I15 (Galaxy s4, Galaxy j5, iPhone 8, iPhone 9, Xperia xyz,
Xperia pro). Si se escribe otro tipo de modelo que muestre una ventana de error.
c. En la columna Precio, crear una lista de los precios establecidos, igualmente se
debe de proteger de no ingresar otro tipo de dato. Mostrar una ventana de error
sobre el ingreso de otro tipo de dato.

En la columna cantidad solo se puede pedir un mínimo de 3 y un máximo de 4. Restringir la

entrada y que muestre un mensaje de error si ingresa otro tipo de cantidad.

Ejercicios de tablas dinámicas.

Trabaje los ejercicios 4 y 5 utilizando el origen de datos de la hoja de cálculo Datos1, y los
ejercicios 6 y 7 Utilizando el origen de datos de la hoja Datos2, Para todos los campos
relacionados con dinero, aplicar el formato de Moneda, con el símbolo de dólar ($) y dos
posiciones decimales.

5
 UNIVERSIDAD DE EL SALVADOR EN LÍNEA
FACULTAD DE INGENIERÍA Y ARQUITECTURA
OFIMATICA Y SOFTWARE PARA INGENIERIA INDUSTRIAL

Se tienen los datos de ventas de la empresa X, se necesita construir en las hojas de cálculo
ejercicio 4 y 5, a partir de los datos de la hoja de cálculo llamada Datos1 dos tablas dinámicas que
muestren la siguiente información:

Ejercicio 4: Crear una tabla dinámica que muestre la suma total de unidades vendidas por cada
canal de distribución por cada producto del catálogo (N° Catalogo)

Ejercicio 5: Mostrar el monto total de ventas realizado en cada año para cada tipo de cliente
además mediante un campo calculado calcule el IVA 13% de ese monto de venta, llame al campo
calculado IVA.

Ejercicios de tablas dinámicas a elaborar con origen de datos de la hoja Datos2, trabajar estos
ejercicios en las hojas de cálculo Ejercicio 7 y 8 respectivamente:

Ejercicio 6: Cree una tabla dinámica que muestre el total de unidades producidas por cada país
agrupado por año.

Ejercicio 7: Cree una tabla dinámica que muestre cuanto es la producción total para cada ciudad
de cada país. Agregue a este ejercicio un gráfico dinámico.

Parte II. Resuelva en el archivo de Excel proporcionado los ejercicios de aplicación de funciones
de búsqueda y referencia.

Ejercicios de funciones de búsqueda.

Ejercicio 8:

El club de playa “Las palmeras” está conformado por muchos asociados, y está interesado en
celebrarles su cumpleaños a cada uno de los socios, para ello necesita crear un documento en
Excel, donde se pueda saber por medio del número de registro de asociado, los datos personales
de cada socio, incluyendo su fecha de cumpleaños.
Se pide:

 Haciendo uso de la función BuscarV o ConsultaV según como le aparezca en su versión

de Excel, encuentre la información correspondiente en las celdas C7, C8, C9, C10, C11,
C12, basándose en el número de registro del socio en el club de la hoja de cálculo
Ejercicio8.
Nota: En el archivo de Excel, la hoja llamada “Ejercicio 8” se encuentra la tabla donde se debe
encontrar los datos de cada socio (es decir en donde resolverá el ejercicio) y en esa misma hoja
los datos personales de los socios del club.

6
 UNIVERSIDAD DE EL SALVADOR EN LÍNEA
FACULTAD DE INGENIERÍA Y ARQUITECTURA
OFIMATICA Y SOFTWARE PARA INGENIERIA INDUSTRIAL

Recomendación: Cuando construya las formulas asegúrese que en la celda B4 haya un

número de registro valido para poder previsualizar los datos de respuesta cuando vaya
ingresando cada formula en las celdas C7, C8, C9, C10, C11, C12.

Parte III. Resuelva en el archivo de Excel proporcionado el siguiente ejercicio sobre formularios
en Excel.

Ejercicios de creación y programación de un formulario en Excel.

Ejercicio 9: Haciendo uso del Editor de Visual Basic para Aplicaciones disponible en Excel, crear
el formulario siguiente:

Se pide:

 Crear el diseño del formulario (llámelo FormularioPagos), los cuadros de texto deberán
llamarse txtNombre, txtDui, txtMonto, txtFecha respectivamente.
 Se agregarán dos cuadros combinados, con los nombres ComboBoxArea y
ComboBoxGasto
 Los botones de comando deberán llamarse:
 BotonRegistrarpago, con la propiedad caption Registrar.
 BotonNuevo con la propiedad caption Nuevo.
 BotonSalir con la propiedad caption Salir.
 Programar el botón BotonRegistrarPago para que realice las acciones siguientes:
1. Seleccionar la hoja de cálculo llamada Ejercicio9.
2. Desplazarse a la fila 2, específicamente a la celda A2.
3. Insertar una nueva fila.

7
 UNIVERSIDAD DE EL SALVADOR EN LÍNEA
FACULTAD DE INGENIERÍA Y ARQUITECTURA
OFIMATICA Y SOFTWARE PARA INGENIERIA INDUSTRIAL

4. Posicionar el cursor en la celda A2 y escribir en ella el dato contenido en

txtNombre.
5. Posicionar el cursor en la celda B2 y escribir en ella el dato contenido en txtDui.
6. Posicionar el cursor en la celda C2 y escribir en ella el dato contenido en
ComboBoxArea.
7. Posicionar el cursor en la celda D2 y escribir en ella el dato contenido en
ComboBoxGasto.
8. Posicionar el cursor en la celda E2 y escribir en ella el dato contenido en txtMonto
9. Posicionar el cursor en la celda F2 y escribir en ella el dato contenido en txtFecha
10. Presentar un mensaje al usuario que diga “Datos guardados con éxito”.

 Programar el botón BotonNuevo para que realice las acciones siguientes:

1. Limpiar los cuadros de texto del formulario.
2. Poner el cursor en txtNombre.
 Programar el botón BotonSalir para que realice las acciones siguientes:
1. Presentar un mensaje que diga “Hasta pronto”.
2. Cerrar el formulario.

NOTA: Los posibles valores de la lista desplegable de área (ComboBoxArea) son los siguientes:
Transporte, Hospedaje, Alimentación, Materiales, Otros. Y para la lista desplegable de gastos
(ComboBoxGasto) los siguientes: Finanzas, Tecnología, Planeamiento, Legal, Proyectos.

Una vez terminado el formulario, debe de insertar un botón de comando a la hoja de cálculo que
diga en su propiedad caption: “Ver Formulario”, el cual debe ser programado para que abra el
formulario al darle clic.

INDICACIONES DE ENTREGA

1. Realizar el estudio de los contenidos de la Unidad 3 y 4 de la materia.

2. Siga las indicaciones en este archivo llamado Guía - Laboratorio OFI115 para realizar los
ejercicios planteados.
3. Descargue el archivo de prácticas en Excel: CARNET_PracticaLaboratorio.xlsm, el enlace
de descarga está en la pestaña evaluaciones, dentro de la consigna de entrega del
Laboratorio evaluado.
4. Descargue también la Rúbrica de evaluación para laboratorio ya que en ella se les da
indicaciones sobre que parámetros se les va a evaluar en el laboratorio.
5. Las indicaciones u orientaciones para la resolución de los ejercicios serán consultados con
su tutor en el foro de dudas en caso de tener una duda con su interpretación.
6. Presentar su laboratorio en un documento de Excel que previamente descargó y trabajó
conteniendo su solución de los ejercicios establecidos en la Guía de laboratorio.

8
 UNIVERSIDAD DE EL SALVADOR EN LÍNEA
FACULTAD DE INGENIERÍA Y ARQUITECTURA
OFIMATICA Y SOFTWARE PARA INGENIERIA INDUSTRIAL

7. Deberá constatar con sus soluciones trabajadas presentando el archivo generado con su
número de carnet como nombre, por ejemplo CC18001_PracticaLaboratorio.xlsm, con
su número de carnet en mayúsculas.
8. Suba su archivo CARNET_PracticaLaboratorio.xlsm en el enlace designado para subir
laboratorio #1 ubicado en la pestaña evaluaciones.
9. Esta actividad es de carácter INDIVIDUAL, cualquier intento de fraude, copia con
trabajos duplicados tendrá una nota de 1.0 automáticamente.

En el nombre del archivo reemplazará la palabra CARNET por su número de carnet en

mayúsculas, trabaje cada uno de los ejercicios en su hoja de cálculo correspondiente según las
indicaciones de dichos ejercicios.

1. Documento: Deberá de contemplar lo siguiente:

 Escribir sus datos personales en la hoja de cálculo Alumno.
 Desarrollo de los ejercicios (para cada uno de los nueve ejercicios)

El nombre del documento será CARNET_PracticaLaboratorio.xlsm con su número de carnet.

FECHA ASIGNACION: 29 de abril de 2019 8:00 am

FECHA ENTREGA: 12 de mayo de 2019 11:55 pm

MEDIO: Enlace en aula virtual llamado Laboratorio Evaluado, ubicado en la pestaña

evaluaciones.

IMPORTANTE.
Por ningún motivo se recibirán trabajos por correo electrónico, ni en formato
impreso ni fuera de la fecha límite de entrega.
Se le sugiere no esperar los últimos minutos del día final de entrega para subir la
tarea, pues cualquier problema de carácter técnico podría presentarse, lo que
podría impedirle entregar el laboratorio.

Esta actividad es de carácter INDIVIDUAL, cualquier intento de fraude, copia con

trabajos duplicados tendrá una nota de 1.0 automáticamente.

El laboratorio tiene una ponderación de 30% de la nota final de la

asignatura.