EJERCICIOS DE CASOS DE ESTUDIO PARA SOLUCIONAR CON EXCEL CASO 1. Las calificaciones finales En el “Colegio Nueva Generación” del estado de Hidalgo, se califica a sus estudiantes de secundaria en bimestres, para obtener su calificación del año (en total 6 bimestres). Cada calificación bimestral se compone de tres rubros para obtenerse: 20% 40% 40% 100%
Actitudes y Valores Conocimientos Habilidades Calificación bimestral
Así pues, al final de las seis calificaciones bimestrales del estudiante, se calcula la calificación final promediándolas. El Director General del “Colegio Nueva Generación” desea que entregues una muestra de cómo obtener las calificaciones bimestrales y finales de sus estudiantes de secundaria, para ello te entrega en la siguiente tabla las calificaciones de los 12 alumnos del grupo “1ero. B” de la asignatura de Química: NL 1
2 3 4
5
6
7 8 9 10 11
12
Estudiante ALVARADO MARTINEZ PATRICIA BADILLO ALBINO ISRAEL BADILLO DIAZ JAZMIN BADILLO GALVAN MARIA FELIX BIAIS BADILLO RUBEN CRUZ ESTRELLA SOFIA ESTRELLA ANGELES SANDRA IXBA TREJO RICARDO LAZARO VELAZQUEZ ANA LEDESMA TREJO SONIA LINARES FLORES RAQUEL MAQUEDA OCAMPO DANIELA
A
Ago-Sep C H
A
Oct-Nov C H
A
Dic-Ene C H
A
Feb-Mar C H
A
Abr-May C H
A
Jun-Jul C H
8
9
10
1
9
8
10
10
2
10
3
0
4
9
9
0
3
0
1
10
3
8
2
0
7
8
7
1
8
7
7
6
5
10
9
9
7
8
6
3
4
5
2
9
1
0
3
5
4
7
7
9
10
5
1
7
1
5
1
2
10
7
5
5
4
3
0
4
6
2
9
3
5
0
3
10
1
4
2
4
2
2
7
1
3
8
4
7
5
5
6
4
9
6
7
5
1
9
4
6
1
3
8
10
2
2
4
3
8
7
10
10
9
6
8
8
2
1
8
4
9
10
9
5
0
8
7
9
4
7
8
1
5
3
8
7
1
5
1
1
3
8
5
8
6
3
3
9
10
4
0
5
6
9
9
6
0
2
0
8
9
9
4
3
4
7
6
9
1
5
6
3
10
7
0
9
8
2
9
0
7
6
7
2
4
10
2
0
2
2
8
3
1
7
2
5
9
1
2
4
3
2
5
7
7
7
8
1
1
0
10
1
7
0
0
0
Ing. Hugo Miguel Castañeda Díaz
Calificación Final
Página 1 de 8
Ahora, realiza lo mencionado en la lista de cotejo siguiente: Actividad 1.
Inserta una columna entre cada bimestre, para obtener la calificación bimestral de cada estudiante, según los porcentajes antes mencionados. 2. Calcula la calificación de cada estudiante promediando sus seis calificaciones bimestrales. 3. Promedia las calificaciones grupales de Actitudes y Valores de cada bimestre. 4. Promedia las calificaciones grupales de Conocimientos de cada bimestre. 5. Promedia las calificaciones grupales de Habilidades de cada bimestre. 6. Obtén el promedio final del grupo por todo el año escolar. 7. Inserta a la hoja una gráfica de burbuja 3D, con Diseño 3, para visualizar los promedios de los estudiantes junto con la línea de la media de calificaciones. 8. Inserta una hoja con una gráfica de columna 3D, con diseño Columnas 3D, para cada bimestre (seis gráficas en total), que muestre las calificaciones de Actitudes y Valores, Conocimientos, y Habilidades de cada estudiante. 9. Inserta una hoja con una gráfica radial, con diseño Radial Relleno, para comparar las calificaciones finales de los estudiantes. 10. Mejora la presentación de la tabla de datos y las gráficas para que puedan imprimirse en hojas tamaño carta, con el membrete oficial del Colegio Nueva Generación. TOTAL (Calificación de la actividad)
Valor (Puntos)
Realizó (Sí / No)
12 12 6 6 6 5 5 30 5 13 _____ de 100
CASO 2. Clientela del negocio El centro de cómputo “Coatepec Cyber-Café”, se dedica a la compra-venta, reparación, mantenimiento y renta de equipo de cómputo. Su dueño cuenta con mucha experiencia en el negocio que apenas fundó en el portón de su casa, sin embargo, cree que la razón por la que en estos tres meses no ha despuntado el negocio es por falta de publicidad, por lo que creó una estrategia para aumentar su clientela. La estrategia consiste en: Repintar la fachada de su local, con pintura llamativa y rótulos más visibles. Mandar crear propaganda a base de volantes para entregarlos en las casas, negocios y escuelas más cercanas; informando el nombre, servicios que se ofrecen, costos, dirección y teléfono, del negocio. Contratar a una botarga con disfraz de computadora para entregar los volantes a los transeúntes que se acerquen, los días sábados y domingos. Rentar un equipo de sonido que toque música actual, haciendo notar el local de entre los demás. Este mismo equipo de sonido le permitirá a la botarga bailar. Comprar un inflable en la entrada de su local, en forma de memoria USB, para llamar la atención de las personas que transitan por el lugar, cuando la botarga no se encuentre trabajando. Colocar luces de neón intermitentes sobre el local para resaltar el nombre de su negocio en las tardes y noches.
Obviamente, la publicidad requiere de cierta cantidad de dinero, tiempo y otros gastos; por lo que Octavio Guiot (el dueño del negocio), decidió crear una encuesta rápida a sus clientes para decidir si continuar o mejorar su estrategia de publicidad. Al recopilar la información se tuvo como resultado la tabla siguiente, que solo considera la renta de equipos de cómputo:
Ing. Hugo Miguel Castañeda Díaz
Página 2 de 8
Clientela
Clientes habituales Clientes que llegaron gracias a la nueva fachada Clientes que llegaron gracias a los volantes Clientes que llegaron gracias a la botarga Clientes que llegaron gracias a la música Clientes que llegaron gracias al inflable Clientes que llegaron gracias a las luces de neón
Mes anterior
Mes actual
15 5 3 5 6 5 2
15 4 10 5 9 3 8
Porcentaje de aumento o disminución
Ahora que se tienen dichos datos, se deberán obtener otros datos para establecer la estadística. Por lo tanto, realiza lo indicado en la lista de cotejo siguiente para terminar la tabla, y así el dueño podrá tomar decisiones adecuadas: Actividad 1. 2. 3. 4. 5. 6. 7. 8. 9.
Calcula la cantidad de clientes que se obtuvo el mes anterior. Calcula la cantidad de clientes que ingresaron al local este mes. Calcula el porcentaje de aumento o disminución de cada tipo de cliente, de un mes al otro. Calcula el promedio de cada tipo de clientes que entran al local. Señala mediante una función, cuál es el tipo de propaganda que más ayudó al local cada mes. Inserta una gráfica circular que indique el número de clientes de cada tipo para el mes anterior. Inserta una gráfica circular que indique el número de clientes de cada tipo para el mes actual. Inserta una hoja con una gráfica de columnas que indique el aumento o disminución porcentual de cada tipo de clientes, de un mes al otro. Mejora la presentación de la tabla de datos y las gráficas para que puedan imprimirse en una hoja tamaño carta, con el logotipo del negocio “Coatepec Cyber-Café”. TOTAL (Calificación de la actividad)
Valor (Puntos) 7 7 15 15 10
Realizó (Sí / No)
10 10 10 16 _____ de 100
CASO 3. El buffet jurídico En el buffet jurídico “Jaramillo y Asociados” se cuenta con una lista de clientes atendidos para solucionar sus problemas legales. En dicha lista se tiene un código que permite identificar el licenciado que llevó su caso. La tabla se muestra a continuación:
Clave_Cliente C0032 C0043 C0122 C0188 C0200 C0201 C0202 C0264 C0268 C0269
Nombre_Cliente José Miguel Cuevas Tapia Mariana Pérez Sánchez Juan Carlos Carrillo Ledesma Pedro Antonio Ruiz Mendoza Carlos Alberto Martínez Martínez José Antonio Tapia Reyes Ranulfo Pérez Juárez Remedios Sánchez Martínez Karla Guadalupe Castillo Bautista Yadira Paola Sánchez Carreño
Caso_Número RHK23-490-01 HFG12-255-01 JDA12-986-65 KYR94-900-02 KLF26-589-23 YTE36-945-03 HDF23-577-02 YER78-965-02 POY96-658-01 EWQ89-532-00
Clave_Licenciado Lic002 Lic003 Lic001 Lic001 Lic002 Lic002 Lic002 Lic001 Lic003 Lic002
Horas_Totales 50 31 15 89 56 14 65 75 65 85
En una segunda tabla se tiene la lista de licenciados que trabajan en el buffet jurídico, con la información de los honorarios que cobran a sus clientes por cada hora de trabajo, de acuerdo a sus años de experiencia. La información se muestra en la tabla siguiente:
Ing. Hugo Miguel Castañeda Díaz
Página 3 de 8
Clave_Licenciado Lic001 Lic002 Lic003 Lic004
Nombre_Licenciado Juan Salvador Jaramillo Andrade Alejandra Veais Martínez Mayra Morán Rojo Patricia Rubí Adán Jaramillo
Honorarios_por_Hora $ 150.00 $ 100.00 $ 85.00 $ 80.00
El Lic. Jaramillo, accionista principal de “Jaramillo y Asociados”, te ha solicitado la incorporación de una tercer tabla, donde se pueda calcular el total a facturar por los casos legales ya finalizados, considerando los honorarios del abogado que llevó su caso, un cobro de excedentes por conceptos varios y un descuento especial del caso otorgado por el mismo licenciado del caso. Es decir, deberás crear una tabla con la siguiente información: Clave_Cliente
Clave_Licenciado
C0032 C0043 C0122 C0188 C0200 C0201 C0202 C0264 C0268 C0269
Lic002 Lic003 Lic001 Lic001 Lic002 Lic002 Lic002 Lic001 Lic003 Lic002
Honorarios_por_Hora $ 100.00 $ 85.00 $ 150.00 $ 150.00 $ 100.00 $ 100.00 $ 100.00 $ 100.00 $ 85.00 $ 100.00
Horas_Totales
50 31 15 89 56 14 65 75 65 85
Excedente $ 230.50 $ 0.00 $ 15.00 $ 500.00 $ 45.50 $ 1.00 $ 56.50 $ 100.00 $ 230.00 $ 21.00
Descuento 13% 0% 0% 20% 10% 0% 0% 10% 0% 5%
Total_a_Facturar
Ahora, realiza lo que se indica en la siguiente la lista de cotejo: Actividad 10. 11. 12. 13. 14. 15.
Calcula el Total a Facturar. Suma el total de horas trabajadas en total por el buffet. Suma el total de Excedentes. Suma el Total a Facturar. Obtén el total de horas que trabajó cada licenciado Inserta una hoja con una gráfica de barras que compare los honorarios por hora de cada abogado. 16. Inserta una hoja con una gráfica de columnas que muestre los totales a facturar a cada cliente. 17. Inserta una hoja con una gráfica circular que indique el total de horas que trabajó cada licenciado. 18. Mejora la presentación de la tabla de datos y las gráficas para que puedan imprimirse en hojas tamaño carta, con el membrete del buffet jurídico “Jaramillo y Asociados”. TOTAL (Calificación de la actividad)
Valor (Puntos) 10 5 5 5 20
Realizó (Sí / No)
20 10 10 15 _____ de 100
CASO 4. Si (atendemos a los pacientes) Entonces… En la clínica privada “Clínica Santa Virgen Morena” se lleva el control de los pacientes que se encuentran internados en sus instalaciones, para dar un seguimiento adecuado. A continuación se te entrega la tabla con la información de los treinta pacientes que a la fecha de hoy se encuentran en las habitaciones:
Ing. Hugo Miguel Castañeda Díaz
Página 4 de 8
Estado_salud
Dieta
Días_Hospitalizado
grave
especial
3
F0002
pediatricos
10
femenino
delicado
normal
5
F0003
adolescentes
15
masculino
delicado
normal
4
F0004
adolescentes
16
masculino
estable
normal
6
F0005
pediatricos
8
femenino
grave
especial
8
F0006
R/N
0
femenino
estable
especial
9
F0007
R/N
0
femenino
estable
especial
2
F0008
pediatricos
5
masculino
delicado
especial
5
F0009
adolescentes
17
masculino
delicado
normal
5
F0010
pediatricos
4
masculino
estable
normal
6
F0011
adolescentes
16
masculino
delicado
normal
3
F0012
R/N
0
masculino
estable
especial
7
F0013
pediatricos
4
masculino
delicado
especial
1
F0014
adolescentes
16
masculino
grave
especial
2
F0015
R/N
0
masculino
grave
especial
5
F0016
pediatricos
6
femenino
grave
especial
6
F0017
R/N
0
femenino
grave
especial
3
F0018
adolescentes
15
femenino
grave
especial
8
F0019
adolescentes
16
femenino
delicado
normal
4
F0020
pediatricos
8
masculino
estable
normal
6
F0021
R/N
0
femenino
delicado
especial
5
F0022
pediatricos
5
masculino
estable
normal
8
F0023
R/N
0
masculino
delicado
especial
9
F0024
adolescentes
17
femenino
estable
normal
5
F0025
adolescentes
17
femenino
estable
normal
1
F0026
pediatricos
5
femenino
estable
especial
10
F0027
R/N
0
masculino
estable
normal
8
F0028
pediatricos
5
femenino
delicado
normal
7
F0029
R/N
0
masculino
grave
especial
8
F0030
adolescentes
17
masculino
estable
normal
25
Costo_hospitalización_total
Sexo masculino
Costo_hospitalización_diaria
Edad 0
Costo_atención_diaria
Tipo_Paciente R/N
Costo_dieta_diaria
Folio_Paciente F0001
El Director General de la clínica, el Dr. Gustavo Sánchez, desea que le apoyes para predecir el cálculo del total que la secretaria debe cobrar a cada paciente por su estancia en las instalaciones, al momento de facturarles. Para esto, deberás considerar lo siguiente:
Ing. Hugo Miguel Castañeda Díaz
Página 5 de 8
a) El costo de la dieta diaria depende del tipo de dieta; si la dieta es especial entonces se le cobran $50, si es normal se le cobra $30. b) El costo de la atención diaria es de $100 para pacientes graves, $85 para pacientes delicados y $30 para pacientes estables. c) El costo de la hospitalización diaria dependerá de las edades de los pacientes. Todo paciente adolescente genera un cobro de $20 diarios, los niños (pediátricos) generan un costo de $55, y a los recién nacidos se les cobra $100 por los cuidados y la atención continua que se les da. d) Para calcular el costo de hospitalización diaria, dependerá de los tres costos anteriores multiplicados por el número de días que el paciente permaneció internado. Como una nota adicional, recuerda que los pacientes estables pronto serán dados de alta, por lo que los conceptos del nuevo paciente podrían cambiar, así que deberás utilizar la función =SI() de Excel para que las fórmulas y funciones cambien automáticamente una vez que se interne a un nuevo paciente. Ahora, realiza lo mencionado en la lista de cotejo siguiente: Actividad 1. 2. 3. 4. 5. 6. 7. 8. 9.
Calcula con la función =SI( ) la columna Costo_dieta_diaria Calcula con la función =SI( ) la columna Costo_atención_diaria Calcula con la función =SI( ) la columna Costo_hospitalización_diaria Calcula con una fórmula la columna Costo_hospitalización_total Obtén el Total a Cobrar a la fecha de hoy, sumando los Costos de dieta diaria y multiplicando por el número de días totales. Obtén el Total a Cobrar a la fecha de hoy, sumando los Costos de atención diaria y multiplicando por el número de días totales. Obtén el Total a Cobrar a la fecha de hoy, sumando los Costos de hospitalización diaria y multiplicando por el número de días totales. Obtén el Total a Cobrar a la fecha de hoy, sumando los Costos de Hospitalización Total. Mejora la presentación de la tabla de datos para que pueda imprimirse en una hoja tamaño carta, con el membrete de la Clínica Sagrado Corazón. TOTAL (Calificación de la actividad)
Valor (Puntos) 10 10 10 10
Realizó (Sí / No)
10 10 10 10 20 _____ de 100
CASO 5. Mejorando archivo de la clínica Como una nota adicional, el archivo a utilizarse es el archivo original Hospital.xlsx, el mismo del Caso 4. Es decir, antes de las modificaciones. En el Hospital Privado “Santa Sofía”, se cuenta con un archivo en Excel con los datos del Edificio de Hospitalización. En dicho archivo (Hospital.xlsx) se cuentan con registros de 30 pacientes actualmente internados, así como cuatro columnas vacías que requieren fórmulas para la obtención de los datos, acordes a la información original. Los doctores y enfermeras contratadas cuentan con poco conocimiento en Hojas de Cálculo, por lo que se te ha encomendado la tarea de apoyarles en el cálculo de lo siguiente: La columna “Costo_dieta_diaria” es la cantidad de dinero que le cuesta al hospital el dar de comer a sus pacientes internados, de acuerdo a su dieta. Para calcular dicho costo, se considera lo siguiente: Si su dieta es “especial” se multiplica su edad por 0.45 y se le suma 7, el resultado se multiplica por 10. Pero si su dieta es “normal” se multiplica la edad por 0.32 y se le suma 2, el resultado se multiplica por 10. Si la dieta es especial: (EDAD * 0.45 + 7) * 10 Si la dieta es normal: (EDAD * 0.32 + 2) * 10
Ing. Hugo Miguel Castañeda Díaz
Página 6 de 8
La columna “Costo_atención_diaria” trata sobre el dinero que gasta el hospital en atender a sus pacientes delicados, graves y estables. Es decir, para atender a un paciente “grave”, al hospital le cuesta la edad del paciente, multiplicada por 0.222 más 20, y el resultado es multiplicado por 10; pero si el paciente es “delicado” o “estable”, la edad por 0.13 y se le suma 10, al resultado obtenido se multiplica por 10. Si es un paciente grave: (EDAD * 0.222 + 20) * 10 Si es un paciente delicado: (EDAD * 0.13 + 10) * 10 Si es un paciente estable: (EDAD * 0.13 + 10) * 10 La columna “Costo_hospitalización_diaria” es la cantidad de dinero que le cuesta al hospital atender a cada paciente, considerando su dieta y su atención, por lo que dicha columna solo se calcula sumando la columna “Costo_dieta_diaria” más “Costo_atención_diaria”. CHD = CDD + CAD La columna “Costo_hospitalización_total” es lo que se ha gastado por cada paciente según todos los días que ha estado hospitalizado; es decir, el “Costo_hospitalización_diaria” por el número de días que ha estado hospitalizado el paciente. CHT = CHD * Días_Hospitalizado El gerente del hospital, quiere aprovechar tu apoyo, para contar con lo siguiente: a) Al final de las columnas H, I, J y K de la hoja de cálculo se necesita la suma de cada una de las cantidades calculadas, en un renglón que se llame TOTAL. b) En una segunda hoja de cálculo, se desea que calcules mediante funciones, lo necesario para contestar las siguientes preguntas, esperando que si se modifica la información, automáticamente los valores cambien: ¿Cuántos Pacientes Graves se tienen hospitalizados? ¿Cuántas dietas especiales se sirven diariamente? ¿Cuál es el promedio de edad de los pacientes? ¿Cuántos Recién Nacidos (R/N) están en el hospital? ¿Cuál es la edad máxima de los hospitalizados? c) Con una gráfica de columnas en 3D, representa el número de días que ha permanecido hospitalizado cada paciente. Dicha gráfica deberá colocarse en una hoja nueva. d) Mejora la imagen de la hoja DATOS_Hospital (donde se encuentra toda la información de los pacientes) para que se vea mejor. Utiliza colores de relleno de celdas, tipos de letra, bordes, etc. Inserta en un lugar adecuado una imagen de un doctor para complementar la vista de la información. Sé creativo, pero en ningún momento borres la información del Hospital. e) Configura la hoja DATOS_Hospital para mandar a imprimir en papel tamaño carta. f)
Guarda el archivo resultante con el nombre Hospital_Final., para que el gerente del Hospital Privado “Santa Sofía”, revise el nuevo archivo para su aprobación.
Antes de enviar tu archivo al hospital, verifica tu trabajo con la siguiente lista de cotejo:
Ing. Hugo Miguel Castañeda Díaz
Página 7 de 8
Actividad 1. La columna “Costo_dieta_diaria” se calcula adecuadamente, haciendo uso de la función =SI(condición,RespuestaVerdadera,RespuestaFalsa) de acuerdo a las condiciones señaladas. 2. La columna “Costo_atención_diaria” se calcula adecuadamente, haciendo uso de la función =SI(condición,RespuestaVerdadera,RespuestaFalsa) de acuerdo a las condiciones señaladas. 3. La columna “Costo_hospitalización_diaria” se calcula adecuadamente, sumando las columnas anteriores. 4. La columna “Costo_hospitalización_total” se calcula adecuadamente, multiplicando columna anterior por el número de días de hospitalización. 5. Las celdas A32 hasta G32 se encuentran “combinadas y centradas”, pues es donde se escribe el título “TOTAL”. 6. En las celdas H32 hasta K32 se tiene la suma por cada columna. 7. Se inserta una segunda hoja, llamada “Preguntas frecuentes”. 8. Se calcula en la segunda hoja la pregunta de Pacientes Graves, gracias a la función =Contar.Si(rango,condición). 9. Se calcula en la segunda hoja la pregunta de Dietas Especiales, gracias a la función =Contar.Si(rango,condición). 10. Se calcula en la segunda hoja la pregunta del Promedio de Edad, gracias a la función =Promedio(rango). 11. Se calcula en la segunda hoja la pregunta de Recién Nacidos, gracias a la función =Contar.Si(rango,condición). 12. Se calcula en la segunda hoja la pregunta de Edad Máxima, gracias a la función =Max(rango). 13. Grafica por medio de columnas en 3D, el número de días que se encuentra cada paciente hospitalizado. 14. La gráfica se encuentra en una tercer hoja, llamada Días de Hospitalización. 15. Se formatea con color y tipos, los datos de la hoja DATOS_Hospital. 16. Se inserta una imagen representativa de un doctor, a la hoja DATOS_Hospital, en un lugar adecuado, sin eliminar ni tapar información. 17. La hoja DATOS_Hospital está configurada adecuadamente para su impresión total sobre un solo papel tamaño carta. 18. El archivo “Hospital Final.xlsx” es enviado por correo con el asunto “Hospital Santa Sofía”, a la dirección que le fue indicado. 19. El correo no cuenta con faltas de ortografía en la descripción del archivo adjunto.
TOTAL (Calificación de la actividad)
Ing. Hugo Miguel Castañeda Díaz
Valor (Puntos)
Realizó (Sí / No)
10 10 7 7 3 3 3 5 5 3 5 3 3 3 7 3 5 10 5
_____ de 100
Página 8 de 8