CFAF
Hoja de Cálculo
EXCEL Curso 2021-22 IES LAS FUENTEZUELAS - JAEN
Actividades Excel Profesor: Jacinto García Torres
CONCEPTOS BÁSICOS DE EXCEL Los documentos se Excel se denominan “libros” y están divididos en “hojas”. La hoja de cálculo es el espacio de trabajo. Excel comparte la misma interfaz de otros programas del paquete Office. Elementos de una hoja de cálculo:
4 5
3
2 6
1
7 8
1) Filas: espacios horizontales de la hoja de cálculo. Se identifican con números.
Cada hoja de cálculo de Excel 1.048.576 filas.
2) Celda: es la unidad de trabajo de la hoja de cálculo. Es la intersección de una columna con una fila. Se identifica con la letra de la columna y el número de la fila, como, por ejemplo, A1.
3) Cuadro de nombres: muestra el nombre de la celda activa.
4) Columnas: espacios verticales de la hoja de cálculo. Se identifican con letras. Cada hoja de cálculo de Excel tiene 16.384 columnas.
Actividades Excel Profesor: Jacinto García Torres
5) Barra de fórmulas: muestra el contenido de la celda activa (celda seleccionada). A la izquierda de la Barra de fórmulas se encuentra un asistente para insertar funciones.
6) Controlador de relleno: es un pequeño punto de color negro que se encuentra en la esquina inferior derecha de la celda seleccionada. Cuando acercamos el mouse al controlador de relleno, el puntero toma la forma de una cruz negra fina y pequeña. El controlador de relleno es muy útil para copiar fórmulas y rellenar rápidamente datos en una planilla.
7) Etiquetas: las etiquetas identifican a las hojas de cálculo. Si hacemos clic con el botón secundario del mouse sobre la etiqueta podemos cambiarle el nombre, el color, y otras acciones que veremos más adelante.
8) Insertar hoja de cálculo: de forma predeterminada, Excel presenta 3 hojas de cálculo, pero desde este ícono podemos agregar más.
OTROS CONCEPTOS BÁSICOS
• Rango de celdas: cuando seleccionamos más de una celda hablamos de un “rango”, por ejemplo, A1:B5 significa que estamos afectando un rango formado por 10 celdas, ya que los dos puntos (:) significan “hasta”. En cambio, si nombramos a un rango A1;B5 afectamos solo 2 celdas (A1 y B5), ya que el punto y coma (;) significa “y”. Más adelante, cuando veamos funciones, comprenderemos mejor este concepto.
• Alineación predeterminada de texto y números: de forma predeterminada, los
caracteres de texto se alinean a la izquierda, y los números a la derecha. Excel reconoce como números a los números enteros, decimales, fechas y horas. Es decir, que pueden ser utilizados en operaciones de cálculo.
Actividades Excel Profesor: Jacinto García Torres
OPERACIONES DE CÁLCULO EN EXCEL Para Excel, una fórmula es una ecuación que nos permite realizar cálculos con los valores que tenemos introducidos en la hoja de cálculo. Para que Excel identifique que estamos solicitando que realice un cálculo, toda fórmula debe comenzar con el signo = (igual). Para relacionar en una fórmula los distintos valores que vamos a utilizar en un cálculo, tenemos que usar operadores. Los operadores básicos de Excel son: + SUMA ‐ RESTA * MULTIPLICACIONUMA / DIVISION En una fórmula podemos usar valores constantes, como, por ejemplo, =5+2. El resultado será, por supuesto,7; sin embargo, si tuviéramos que cambiar esos valores, el resultado será siempre 7. En cambio, si en la fórmula utilizamos referencias a las celdas que contienen los valores, el resultado se modificará automáticamente cada vez que cambiemos alguno o ambos valores.
Por ejemplo, si en las celdas A1 y B1 introducimos valores constantes y los utilizamos en una fórmula para calcular la suma, podemos escribir =A1+B1 y de este modo, si modificamos cualquiera de esos valores, el resultado se ajustará automáticamente a los valores que encuentre en las celdas a las que se hace referencia en la fórmula. Cálculos combinados Cuando en una misma fórmula tenemos que realizar diferentes tipos de cálculo, Excel resolverá las operaciones dentro de la fórmula con un determinado orden de prioridad, siguiendo el criterio matemático de separación en términos. De este modo, el resultado de =3+4+5/3 es 8,67 que resulta de:
Actividades Excel Profesor: Jacinto García Torres
Si necesitamos obtener otro tipo de resultado, podemos introducir paréntesis en la fórmula, para indicarle a Excel que primero debe realizar los cálculos que se encuentran dentro de ellos. De este modo, el resultado de = (3+4+5)/3 es 4, que resulta de: Orden de prioridad El orden de prioridad que aplica Excel para realizar los cálculos básicos que encuentra en una misma fórmula es:
1º) Cálculos entre paréntesis 2º) Multiplicación y división 3º) Suma y resta
Actividades Excel Profesor: Jacinto García Torres
EJERCICIO 1: OPERACIONES DE CALCULO Realice las siguientes operaciones de cálculo:
EJERCICIO 2: OPERACIONES DE CALCULO Completa la tabla que sigue hallando el crecimiento natural mediante la siguiente referencia: CRECIMIENTO NATURAL = TASA DE NATALIDAD ‐TASA DE MORTALIDAD
CRECIMIENTO NATURAL DE ESPAÑA
TASAS AÑO 1930 AÑO 1960 AÑO 1980 AÑO 1990 AÑO 2000 AÑO 2015 NATALIDAD 35,10 25,50 22,70 20,90 25,50 21,40 MORTALIDAD 15,50 9,00 8,60 8,40 8,00 8,60 CRECIMIENTO NATURAL
? ? ? ? ? ?
Actividades Excel Profesor: Jacinto García Torres
EJERCICIO 3: OPERACIONES DE CALCULO Completa la tabla que sigue hallando el PRECIO TOTAL mediante la siguiente referencia: PRECIO TOTAL = CANTIDAD * PRECIO
CANTIDAD PRODUCTO PRECIO UNITARIO
PRECIO TOTAL
2 LAPICERAS 5 ? 7 CARPETAS 12 ? 9 RESMAS 10 ? 5 MARCADOR 8 ?
EJERCICIO 4: OPERACIONES DE CALCULO Completa la tabla que sigue hallando las densidades de población mediante la siguiente referencia: DENSIDAD = TOTAL DE HABITANTES / SUPERFICIE
PROVINCIA HABITANTES SUPERFICIE km2 DENSIDAD
hab./km2
Cáceres 406.267 19.868 ? Ciudad Real 513.713 19.813 ? Zaragoza 956.006 17.274 ? Cuenca 203.841 17.141 ? Huesca 222.909 15.626 ? León 479.395 15.570 ? Toledo 693.371 15.370 ? Albacete 394.580 14.918 ? Teruel 138.932 14.918 ? Sevilla 1.941.480 14.036 ? Burgos 364.002 14.022 ? Córdoba 795.611 13.771 ? Jaén 654.170 13.489 ? Granada 917.297 12.647 ?
Actividades Excel Profesor: Jacinto García Torres
EJERCICIO 5: OPERACIONES DE CALCULO Completa la tabla:
1. Completar los códigos de artículo como serie. Inserte AR1 y luego arrastre desde el controlador de relleno.
2. Calcular el SUBTOTAL multiplicando la cantidad vendida por el precio unitario. 3. Calcular el IVA multiplicando el subtotal por 21% 4. Calcular el TOTAL sumando el subtotal + el IVA
LIBRERÍA "EL ESTUDIANTE"
Artículos
Descripción
Cantidad vendida
Precio unitario
Subtotal
IVA
TOTAL
Goma 10 1,50 €
Lápiz 20 2,25 €
Birome 145 3,40 €
Cuaderno 15 10,50 €
EJERCICIO 6: OPERACIONES DE CALCULO Completa la tabla:
1. Completar la columna PERÍODO como serie cronológica con valor inicial ENERO y límite JUNIO.
2. Calcular el SALDO restando INGRESOS – Gastos. 3. El TOTAL DEL PRIMER SEMESTRE es la suma de los saldos. 4. Calcular la comisión multiplicando el total del primer semestre por 6%.
INFORME DEL PRIMER SEMESTRE
Período Ingresos Gastos SALDO
450.230,00 € 125.600,00 €
325.987,00 € 122.350,00 €
125.687,00 € 97.500,00 €
98.700,00 € 84.900,00 €
85.230,00 € 42.300,00 €
45.890,00 € 35.400,00 €
TOTAL DEL PRIM ER SEM ESTRE
Comisión: 6%
Actividades Excel Profesor: Jacinto García Torres
EJERCICIO 7: OPERACIONES DE CALCULO Resolver las siguientes situaciones aplicando cálculos combinados
1) Notas de un alumno:
NOTA DEL EJERCICIO
Ejercicio 1 7
Ejercicio 2 8
Ejercicio 3 9
Ejercicio 4 5
Ejercicio 5 6
Ejercicio 6 4
NOTA MEDIA: ?
2) Una persona tiene por día los siguientes gastos:
Desayuno 2,50€ Almuerzo 14,50€ Merienda 3,00€ Cena 10,50€
Plantear en una sola fórmula el gasto semanal (todos los días gasta lo mismo)
GASTO SEMANAL: ?
Actividades Excel Profesor: Jacinto García Torres
EJERCICIO 8: OPERACIONES DE CALCULO Completa la tabla:
1. Completa los días como serie lineal con valor inicial 1 e incremento 1 2. Suma los importes de contado y tarjetas. 3. Calcula el importe total por día: Total contado + Total tarjetas. 4. Suma los totales de cada columna.
VENTAS DEL DIA
COMESTIBLES PERFUMERIA TOTALES
DIAS CONTADO TARJETA CONTADO TARJETA CONTADO TARJETA TOTAL por DIA
300,00 500,00 250,00 450,89 ? ? ?
846,27 287,97 375,28 816,37 ? ? ?
648,71 189,67 0,51 268,49 ? ? ?
918,93 996,41 994,46 782,35 ? ? ?
334,51 444,46 214,22 16,94 ? ? ?
485,34 698,55 635,69 288,19 ? ? ?
182,47 244,44 831,95 820,93 ? ? ?
629,37 253,62 14,07 382,79 ? ? ?
517,97 204,17 319,78 725,52 ? ? ?
790,08 559,10 141,32 128,57 ? ? ?
910,25 731,37 28,63 350,79 ? ? ?
233,99 242,97 463,43 559,66 ? ? ?
404,92 947,56 231,80 723,36 ? ? ?
159,82 852,32 845,68 632,55 ? ? ?
928,22 247,59 799,53 404,09 ? ? ?
TOTAL ? ? ? ?
Actividades Excel Profesor: Jacinto García Torres
REFERENCIAS RELATIVAS Y ABSOLUTAS Una referencia de celda es el conjunto de coordenadas que ocupa una celda en una hoja de cálculo. Por ejemplo, la referencia de la celda que aparece en la intersección de la columna B y la fila 3 es B3. Se refiere a una celda o un rango de celdas en una hoja de cálculo y se puede usar en una fórmula de manera que Microsoft Office Excel pueda encontrar los valores o datos que desea que calcule la fórmula. Una referencia relativa en una fórmula, como A1, se basa en la posición relativa de la celda que contiene la fórmula, de modo tal que si cambia la posición de la celda que contiene la fórmula, se cambia la referencia. Si se copia la fórmula en filas o columnas, la referencia se ajusta automáticamente. Por ejemplo: Una referencia absoluta de celda en una fórmula, como $A$1, siempre hace referencia a una celda en una ubicación específica. Si cambia la posición de la celda que contiene la fórmula, la referencia absoluta permanece invariable. Si se copia la fórmula en filas o columnas, la referencia absoluta no se ajusta. De forma predeterminada, las nuevas fórmulas utilizan referencias relativas y es necesario cambiarlas a referencias absolutas. Por ejemplo:
CON LA TECLA F4 FIJAMOS LA REFERENCIA ABSOLUTA
Actividades Excel Profesor: Jacinto García Torres
EJERCICIO 9: REFERENCIAS RELATIVAS Y ABSOLUTAS: Realiza el ejercicio utilizando referencias absolutas:
Solución:
Actividades Excel Profesor: Jacinto García Torres
EJERCICIO 10: REFERENCIAS RELATIVAS Y ABSOLUTAS: Realiza el ejercicio utilizando referencias absolutas:
1. Copie la tabla (NO COPIE LAS LLAMADAS QUE APARECEN DEBAJO DE LAS 3 ÚLTIMAS COLUMNAS)
2. Aplique Negrita en los datos que correspondan (Inicio/Fuente)
3. Para aplicar color a las celdas, vaya a Inicio/Fuente/Color de relleno
4. Para aplicar bordes a la planilla, vaya a Inicio/Fuente/Bordes/Todos los bordes
5. Para colocar los valores en €, vaya a Inicio/Número/Formato de
número/Moneda.
1. Resuelva las columnas COSTE TOTAL SEMANA, COSTE TOTAL EN AUTOBUS y COSTE TOTAL EN AVIÓN de acuerdo a las consignas de las llamadas.
VIAJE DE JAEN A ITALIA
AGENCIA PRECIO
HOTEL DIA PRECIO COMIDA
EXCURSIONES OTROS EXTRAS
COSTE TOTAL
SEMANA
COSTE TOTAL EN AUTOBUS
COSTE TOTAL EN AVION
HALCON 110,00 € 90,00 € 130,00 € 80,00 € ? ? ?
MELIA 120,00 € 110,00 € 120,00 € 70,00 € ? ? ?
EL CORTE INGLES 150,00 € 120,00 € 150,00 € 100,00 € ? ? ?
AUTOBUS 120,00 €
AVION 400,00 €
SE DEBE SUMAR LOS IMPORTES DIARIOS Y MULTIPLICAR POR 7 (EN LA MISMA FORMULA)
SE DEBE SUMAR EL COSTE TOTAL SEMANAL MAS EL IMPORTE DEL AUTOBUS
SE DEBE SUMAR EL COSTE TOTAL SEMANAL MAS EL IMPORTE DEL AVION
Actividades Excel Profesor: Jacinto García Torres
EJERCICIO 11: REFERENCIAS RELATIVAS Y ABSOLUTAS: Realiza el ejercicio utilizando referencias absolutas: Después de copiar los datos:
1. Aplique los formatos para que su planilla se vea similar a la del ejemplo.
2. Complete los artículos desde el controlador de relleno de la celda.
3. Para calcular los porcentajes correspondientes a Recargo por pago con tarjeta y Descuento por pago contado debe multiplicar el PRECIO por el porcentaje correspondiente en cada caso (debe combinar referencias relativas y absolutas, según corresponda).
4. El PRECIO FINAL CON TARJETA se obtiene sumando el PRECIO + el resultado
obtenido en RECARGO POR PAGO CON TARJETA.
5. El PRECIO FINAL AL CONTADO se obtiene restando el PRECIO ‐ el resultado obtenido en DESCUENTO POR PAGO CONTADO.
LISTA DE PRECIOS
Recargo por tarjeta crédito
10%
Descuento Contado
5%
Artículo Precio Recargo pago con tarjeta
Descuento pago contado
Precio final con tarjeta
Precio final al contado
Art1 120,00 €
50,00 €
75,00 €
240,00 €
310,00 €
25,00 €
130,00
Actividades Excel Profesor: Jacinto García Torres
FORMATOS Excel permite darles distinto formato a nuestros datos, para poder presentarlos mejor. Algunos de esos formatos son similares al procesador de textos Word y otros son específicos de este programa. Veamos el siguiente ejemplo:
NOTAS DE OPI
ALUMNOS Práctica 1 Práctica 2 Práctica 3 Nota Media
Juan García 8 6,5 7 7,17
Pedro Ruiz 4 5,5 3 4,17
María Olmo 2 1 2,5 1,83
Luisa Ruíz 10 9 8 9
Todas las opciones para trabajar con formato en Excel se encuentran en los distintos grupos de la ficha Inicio.
1) En el grupo Portapapeles encontramos la herramienta Copiar formato, que nos
permite copiar solamente el conjunto de formatos que hayamos aplicado a una celda, en otra diferente. Este comando copiará solo los formatos, no el contenido de la celda. Para copiar el formato debemos:
Seleccionar la celda que contiene el conjunto de formatos que queremos aplicar en otra.
Hacer clic en Copiar formato.
El puntero del mouse adoptará la forma de un “pincel”. Hacemos un clic sobre la celda nueva.
2) Grupo Fuente: en este grupo se encuentran todas las herramientas necesarias
para cambiar el tipo y tamaño de letra, aplicarle negrita, cursiva o subrayado y color de la letra. También encontramos los comandos para aplicar relleno de color a la celda, aplicarle bordes y Aumentar tamaño y Disminuir tamaño, que nos permitirán modificar el tamaño de la fuente.
Actividades Excel Profesor: Jacinto García Torres
3) Grupo Alineación: en este grupo encontramos los comandos necesarios para ajustar la alineación del contenido dentro de una celda o rango de celdas.
También podemos modificar la orientación del texto dentro de la celda o rango de celdas:
Actividades Excel Profesor: Jacinto García Torres
También encontramos dos herramientas muy útiles para ajustar texto de titulares o encabezados de planillas:
Combinar y centrar: las celdas C3; D3, E3 y F3 se combinaron formando una sola, y el texto quedó centrado.
Ajustar texto: cuando el contenido de una celda sobrepasa el ancho de la misma, podemos hacer más ancha la columna, o podemos mantener el ancho predeterminado, y hacer que ese contenido se ajuste a ese ancho.
Actividades Excel Profesor: Jacinto García Torres
4) Grupo Número: en este grupo encontramos todas las opciones para dar formatos a los números:
Por ejemplo:
Actividades Excel Profesor: Jacinto García Torres
También dentro de este grupo encontramos dos herramientas para controlar la cantidad de decimales con la que presentaremos un número: Aumentar decimales y Disminuir decimales.
5) Grupo Estilos: desde este grupo podemos aplicar diferentes conjuntos de
formatos:
Formato condicional: aplica un conjunto de formatos que dependerán del contenido de la celda (lo veremos en un apartado siguiente).
Actividades Excel Profesor: Jacinto García Torres
Dar formato como tabla: nos permite aplicar rápidamente un conjunto de formatos predeterminados a toda una planilla. Por ejemplo:
Estilos de celdas: aplica un conjunto de formatos predeterminado a una celda o a un rango de celdas seleccionado.
Actividades Excel Profesor: Jacinto García Torres
6) Dentro del grupo Celdas encontramos el menú Formato, que nos permite
modificar, entre otras cosas, el alto de una fila o el ancho de una columna.
Si queremos quitar los formatos que hemos aplicado, podemos ir a Inicio/editar, y desplegamos el menú Borrar para elegir la opción Borrar formatos.
También podemos acceder a las opciones de formato y a otras opciones que no están visibles en la Cinta de opciones desde el iniciador de cuadro de diálogo de los grupos Fuente, Alineación o Número. Este indicador es un punto que aparece en la esquina inferior derecha de cada uno de estos grupos y nos permite acceder al cuadro de diálogo Formato de celdas. Desde las diferentes solapas de este cuadro de diálogo podemos acceder a los diferentes comandos para aplicar formatos.
Actividades Excel Profesor: Jacinto García Torres
EJERCICIO 12: FORMATO
¡¡Recuerde que debe escribir los números sin el separador de miles!!
1. Al rango de celdas B1:E1, aplicarle:
Combinar y centrar.
Fuente Arial, tamaño 14, negrita, cursiva.
Color de fuente: Azul, Énfasis 1, Oscuro 50%.
Bordes/Todos los bordes.
2. A la fila de títulos A2:G2, aplicarle:
Fuente en negrita.
Alineación Centrar y Alinear en el medio.
Ajustar texto.
Relleno de color Azul, Énfasis 1, Claro 40%. 3. Alinear a la derecha la palabra Totales y colocarla en negrita y cursiva. 4. Al rango de celdas A2:G10, aplicarle bordes (desplegar el menú Bordes/Más bordes):
Contorno doble de color verde.
Interior punteado de color verde. ACLARACIÓN: Para marcar bordes con estilos específicos, primero se deben seleccionar los estilos y luego presionar ´contorno´ o ´interior´, según corresponda. si no aparece en la vista previa, no se aplicará en la hoja.
5. Calcular los totales por columna. 6. Calcular los totales anuales (por fila) 7. La columna porcentaje se calcula dividiendo cada celda del anual por el total obtenido en la celda F10 (esta última con referencia absoluta) por ejemplo: =F3/$F$10). 8. Colocar los resultados obtenidos en la columna Porcentajes en formato Porcentaje. 9. Colocar el resto de los números en formato moneda, con 2 decimales.
Actividades Excel Profesor: Jacinto García Torres
EJERCICIO 13: FORMATO
1. Copie los datos del ejemplo y complete la columna Nº de vend. Utilizando el controlador de relleno + la tecla Ctrl. 3. Seleccione la fila de títulos y aplíquele:
Ajustar texto
Centrar
Alinear en el medio. 4. Coloque los valores en formato Moneda 5. Seleccione todo el rango y vaya a Inicio/Estilos/Dar formato como tabla. Elija algún formato de la galería. 6. Aparecerá la ventana:
Presione Aceptar 7. En la Cinta de opciones aparecerá la ficha Herramientas de tabla, con sus grupos y comandos. Como no nos interesa trabajar con una tabla (solo queríamos sus formatos; más adelante veremos qué es una tabla para Excel y cómo utilizarlas), vamos a hacer un clic en Convertir en rango:
Actividades Excel Profesor: Jacinto García Torres
EJERCICIO 14: FORMATO
1. Repita los pasos 1 a 4 del ejercicio anterior. 2. Seleccione la fila de títulos de ejercicio, vaya a Inicio/Estilos/Estilos de celda y seleccione Énfasis 6. Después aplique negrita. 3. Seleccione los demás datos, vaya a Inicio/Estilos/Estilos de celda y seleccione Neutral.
Actividades Excel Profesor: Jacinto García Torres
FORMATO CONDICIONAL Un formato condicional cambia el aspecto de un rango de celdas en función de una condición (o criterio). Si se cumple esa condición, el rango de celdas tendrá el formato indicado; si la condición no se cumple, el rango de celdas no tendrá ese formato. Veamos el siguiente ejemplo:
En este caso, quisimos destacar a los alumnos que no han aprobado, obteniendo un promedio menor a 5. El formato condicional se utiliza para destacar datos específicos en una planilla de cálculo, para facilitar la lectura e interpretación de los mismos. Para aplicar un formato condicional a un rango de celdas, en primer lugar, debemos seleccionar ese rango y luego vamos a Inicio/Estilos y desplegamos el menú Formato condicional.
Actividades Excel Profesor: Jacinto García Torres
EJERCICIO 15: FORMATO CONDICIONAL
NOTAS DE OPI
ALUMNOS Práctica 1 Práctica 2 Práctica 3 Nota Media
Juan García 8 6,5 7 7,17
Pedro Ruiz 3 5,5 3 3,83
Luis Moreno 5 4 2 3,67
Inés Luque 10 8 10 9,33
María Olmo 2 1 2,5 1,83
Luisa Ruíz 10 9 8 9,00
1. Copie los datos y aplique los formatos necesarios para que se vea igual al modelo. 2. Para aplicar el formato condicional a los promedios:
Seleccione los promedios.
Vaya a Inicio/Estilos/Formato condicional y elija la opción Resaltar reglas de celdas >> Es mayor que.
3. En el cuadro de texto escriba 5, y seleccione Relleno rojo claro con texto rojo oscuro. EJERCICIO 16: FORMATO CONDICIONAL
CAJA RURAL DE JAEN
Sucursal Cantidad de depósitos
1º Semestre 2º Semestre Totales
VILCHEZ 4500 544.000 590.000 SILES 1100 254.000 222.000 MOGON 2650 490.000 515.000
CAMBIL 1700 158.000 210.000
1. Copie los datos plantilla y aplique los formatos necesarios para que se vea igual
al modelo. La columna TOTALES se obtiene sumando los datos del rango de celdas correspondiente.
2. Seleccione los datos de la columna TOTALES y vaya a Inicio/Estilos/Formato
condicional y elija la opción Barras de datos/Barra de datos naranja.
3. El resultado será similar al de un gráfico de barras, que muestra con distintas intensidades de color la proporción entre los distintos valores.
Actividades Excel Profesor: Jacinto García Torres
EJERCICIO 17: FORMATO CONDICIONAL
1. Copie la plantilla del ejercicio anterior.
2. Seleccione los datos de la columna TOTALES (sin el título) y vaya a Inicio/Modificar/Borrar/Borrar formatos.
3. Con este mismo rango seleccionado, vaya a Inicio/Estilos/Formato condicional
y elija la opción Escalas de color/Escala de colores verde, amarillo y rojo.
4. El resultado será que, tomando el código universal de los colores del semáforo, destacará en verde a los números mayores, en amarillo a los intermedios y en rojo a los menores.
Pruebe a cambiar los valores numéricos, y verá cómo se modifican automáticamente los colores. EJERCICIO 18: FORMATO CONDICIONAL
1. Copie la plantilla del ejercicio anterior.
2. Seleccione los datos de la columna TOTALES (sin el título) y vaya a Inicio/Edición/Borrar/Borrar formatos.
3. Con este mismo rango seleccionado, vaya a Inicio/Estilos/Formato condicional
y elija la opción Conjunto de íconos. Seleccione alguno de los modelos propuestos.
Actividades Excel Profesor: Jacinto García Torres
FUNCIONES En Excel, una función es una fórmula que utiliza palabras específicas (previamente programadas) en una estructura determinada. Las funciones se utilizan para simplificar los procesos de cálculo. Existen muchos tipos de funciones en Excel, para resolver distintos tipos de cálculos, pero todas tienen la misma estructura:
El argumento de una función es el ámbito de aplicación de la misma. Puede ser un rango de celdas, comparaciones de celdas, valores, texto, otras funciones, dependiendo del tipo de función y situación de aplicación. Excel clasifica a las funciones por categorías: Fecha, Matemáticas, Estadísticas, Lógicas, Financieras, etc. Independientemente de esa clasificación, las iremos viendo en grado creciente de complejidad de acuerdo al tipo de argumento. Funciones sin argumento: =HOY (): devuelve la fecha actual (del sistema) =AHORA (): devuelve la fecha y la hora actuales (del sistema) Funciones cuyo argumento es un rango de celdas: =SUMA (A1:B15): suma TODOS los valores que se encuentran en las celdas especificadas en el rango. =SUMA (A1;B15): suma SOLO los valores que se encuentran en las dos celdas especificadas.
Actividades Excel Profesor: Jacinto García Torres
Por ser una de las funciones más utilizadas en cualquier hoja de cálculo, tiene un ícono que la representa en la Cinta de opciones pestaña Inicio / grupo Editar:
=PROMEDIO (A1:B15): calcula el promedio de las celdas especificadas en el rango. =MAX (A1:B15): devuelve el MAYOR valor numérico que encuentra en el rango especificado. =MIN (A1:B15): devuelve el MENOR valor numérico que encuentra en el rango especificado.
Actividades Excel Profesor: Jacinto García Torres
EJERCICIO 19: FUNCIONES BÁSICAS
NOTAS DE OPI
ALUMNOS
TRABAJOS PRÁCTICOS
EVALUACIÓN
PROMEDIO
ABÁLSAMO, Elena 7 7 ?
ALETTO, Emiliano 8 7 ?
MARTÍNEZ, Fernando 8 4 ?
VARANGOT, Juan 6 4 ?
VIDELA, Fernanda 9 8 ?
Mayor promedio: ?
Menor promedio: ?
1. Copie la plantilla del ejemplo y aplique los formatos necesarios para que se vea similar a la del modelo.
2. Complete las celdas indicadas con ? utilizando las funciones que correspondan
en cada caso.
INSERTA LA FECHA DE HOY
Actividades Excel Profesor: Jacinto García Torres
EJERCICIO 20: FUNCIONES BÁSICAS
AUTOMÓVILES
MARCA PRECIO IVA 21% PRECIO
CONTADO INTERÉS 10%
PRECIO CON INTERÉS
VALOR EN 24
CUOTAS
VALOR EN 36
CUOTAS
Chevrolet 20.000 € ? ? ? ? ? ? Citroën C4 21.000 € ? ? ? ? ? ? Fiat Punto 16.000 € ? ? ? ? ? ? Fiat Siena 12.000 € ? ? ? ? ? ? Ford Kuga 24.000 € ? ? ? ? ? ? Ford Fiesta 12.000 € ? ? ? ? ? ? Peugeot 506 18.000 € ? ? ? ? ? ? Renault Laguna 23.000 € ? ? ? ? ? ? Suzuki 17.000 € ? ? ? ? ? ? Volkswagen Golf 22.000 € ? ? ? ? ? ? Seat León 19.000 € ? ? ? ? ? ?
TOTALES ? ? ? ? ? ? ?
Mayor precio con interés ?
Promedio valor en 24 cuotas ?
Promedio valor en 36 cuotas ?
1. IVA 21%: Multiplicar el PRECIO por el 21%.
2. PRECIO CONTADO: Precio + IVA.
3. INTERÉS 10%: 10% del PRECIO CONTADO multiplicando el precio por 10%.
4. PRECIO CON INTERÉS: Sumarle el PRECIO CONTADO + el Interés.
5. VALOR EN 24 CUOTAS: Dividir el PRECIO CON INTERÉS por 24.
6. VALOR EN 36 CUOTAS: Dividir el PRECIO CON INTERÉS por 36.
7. TOTALES: sumar los totales de cada columna (función SUMA o Autosuma).
8. MAYOR PRECIO CON INTERÉS: calcular mediante la función MAX.
9. PROMEDIO VALOR EN 24 CUOTAS: aplicar la función PROMEDIO.
10. PROMEDIO VALOR EN 36 CUOTAS: ídem anterior.
Actividades Excel Profesor: Jacinto García Torres
EJERCICIO 21: FUNCIONES BÁSICAS
Provincia de Málaga: Turismos en vacaciones
Ciudad Julio Agosto Septiembre Total
por ciudad Promedio por ciudad
Marbella 1.370.500 1.100.600 800.670
Fuengirola 650.460 550340 300.420
Torremolinos 200.320 290.760 50.600
Estepona 1.100.530 1.000.800 500.880
Torrox 650.880 490.850 100.950
Málaga 1.210.300 1.150.150 1.090.850
Nerja 110.890 900.740 600.980
Total Mensual Promedio Máximo Mínimo
Aplicar los siguientes Formatos:
Combinar y centrar el texto comprendido en el rango A3:F3.
Fila de títulos: Centrar / Alinear en el medio / Ajustar texto /Negrita / Relleno a gusto.
Aplicar bordes a toda la tabla de datos.
Fórmulas:
Obtener el total y el promedio por ciudad.
Obtener el total por mes.
Obtener el promedio por mes.
Obtener el máximo mensual.
Obtener el mínimo mensual.
Inserte la fecha de hoy
Actividades Excel Profesor: Jacinto García Torres
FUNCIONES DE TEXTO Las funciones de texto en Excel permiten concatenar cadenas de caracteres, remover los espacios en blanco, reemplazar ciertos caracteres por otros y muchas cosas más que te permitirán manipular las cadenas de texto para obtener los resultados deseados. Por lo general utilizamos Excel para procesar y evaluar datos numéricos, pero en ocasiones es necesario manipular o editar celdas que contienen texto, para ello podemos usar estas funciones: IZQUIERDA, DERECHA, EXTRAE, ENCONTRAR, LARGO, TEXTO y REEMPLAZAR, CONCATENAR. EJERCICIO 22: FUCIONES DE TEXTO Realiza las siguientes funciones, cada una, en una fila diferente: IZQUIERDA Devuelve las primeras n letras a partir de la izquierda del texto.
DERECHA Devuelve las primeras n letras a partir de derecha a izquierda del texto.
EXTRAE Devuelve n letras de un texto a partir de la posición que indiquemos.
ENCONTRAR Esta función encuentra un texto dentro de otro texto y regresa la posición en la cual fue encontrado.
Actividades Excel Profesor: Jacinto García Torres
Nota: La búsqueda hace diferencia entre letras mayúsculas y minúsculas. LARGO Devuelve la cantidad de letras de un texto, la función recibe como único parámetro el texto a evaluar.
TEXTO Esta función se aplica a números y les da el formato que le indiquemos, esta función es muy útil para presentar números de una mejor forma.
Nota: Los números del texto van a reemplazar a los caracteres de numeral (#) dentro el formato indicado. REEMPLAZAR Esta función nos permite reemplazar o insertar textos dentro de otro texto.
CONCATENAR Concatenar es una palabra que se utilizar en programación para nombrar la acción de unir una serie de valores en un solo texto. Esta función en Excel nos permite unir en una celda uno o más valores. La función recibe de 1 a 30 parámetros cada uno con el valor que deseamos unir al texto final.
Nota: Puedes unir o concatenar textos usando el operador &, por ejemplo el mismo resultado anterior se obtiene con la formula =A2&B2.
Actividades Excel Profesor: Jacinto García Torres
EJERCICIO 23: FUCIONES DE TEXTO Sitúate en la celda B2 y escribe "Hoy es ". Fíjate en dejar los espacios en blanco. Sitúate en la celda B3 y escribe " Enero ". Fíjate en dejar los espacios en blanco. En la celda B4 escribe 22 y en la celda B5 escribe 2019. Ahora vamos a crear una frase concatenando varias celdas.
Sitúate en la celda C5 y pulsa sobre , selecciona la Categoría Texto Selecciona la función CONCATENAR() y como argumentos escribe en el primer argumento B2, en el segundo B4, como tercer argumento escribe " de", como cuarto argumento escribe B3, como quinto argumento escribe "de " y como último argumento escribe B5. Para que nos indique la fecha en una sola celda la función debe quedar así =CONCATENAR(B2;B4;" de";B3;"de ";B5). Vamos a utilizar ahora la Función NOMPROPIO() que también es de gran utilidad para formatear una cadena de texto. Sitúate en la celda A6 y escribe "pepe" en la celda B6 escribe "Gutiérrez" y en la celda C6 escribe "Ruiz". No escribas mayúsculas.
Ahora en la celda D6 pulsa sobre , selecciona la categoría de Texto, selecciona la Función CONCATENAR() pulsa Aceptar y escoge como parámetros A6, B6 y C6. Debemos añadir los espacios para separar el nombre completo. La fórmula debe quedar así =CONCATENAR(A6;" ";B6;" ";C6). Ya tenemos el nombre completo en una celda, ahora sí podemos hacer uso de la Función NOMPROPIO(). Sitúate en la celda G6 y selecciona la Función NOMPROPIO() y pásale como parámetro la celda D6. Debemos obtener en la celda G6 Pepe Gutiérrez Ruiz. Nos ha puesto las iniciales en mayúsculas. Guarda el libro.
Actividades Excel Profesor: Jacinto García Torres
EJERCICIO 24: FUCIONES DE TEXTO
Actividades Excel Profesor: Jacinto García Torres
EJERCICIO 25: FUCIONES DE FECHA Y HORA
Sitúate en la celda A1 y pulsa sobre , selecciona la categoría de fecha y hora y elige la función AHORA(). Pulsa el botón Aceptar. Aparece un cuadro de diálogo indicando que la función no tiene argumentos. Pulsa de nuevo sobre Aceptar. Sitúate en la celda B2 y escribe el día de hoy en número. Por ejemplo: 13 Sitúate en la celda B3 y escribe el mes actual en número. Por ejemplo: 4 Sitúate en la celda B4 y escribe el año actual en número. Por ejemplo: 2019
Sitúate en la celda C5 y pulsa sobre , escoge la función FECHA() y pulsa el botón Aceptar. Selecciona como argumentos las celdas B4 ‐‐> para año, B3 ‐‐> para mes y B2 ‐‐> para día, pulsa Aceptar. Vamos a calcular nuestra edad. Sitúate en la celda D1 y escribe tu fecha de nacimiento en formato (día/mes/año) En la celda E1 escribe =HOY() En la celda E2 selecciona la función DIAS360, como fecha inicial la celda D1 (fecha nacimiento), como fecha final E1 (el día de hoy) y en método escribe Verdadero. Como resultado nos aparece los días transcurridos desde la fecha D1 y la fecha E1. Ahora en la celda F3 escribe =E2/360 para obtener los años. El resultado aparece con decimales, para que nos salga solo la parte entera podemos utilizar la función =ENTERO(E2/360). Hemos utilizado cuatro de las funciones más utilizadas y que ofrecen muchas posibilidades. Guarda el libro de trabajo en la carpeta Mis documentos del disco duro con el nombre de Funciones con fechas. Guarde el libro.
Actividades Excel Profesor: Jacinto García Torres
EJERCICIO 26: FUCIONES DE FECHA Y HORA
Actividades Excel Profesor: Jacinto García Torres
FUNCIONES PARA CONTAR DATOS En Excel encontramos un grupo de funciones que se utilizan para contar datos, es decir, la cantidad de celdas que contienen determinados tipos de datos. Estas funciones son:
Se utiliza para conocer la cantidad de celdas que contienen datos numéricos
Se utiliza para conocer la cantidad de celdas que contienen datos alfanuméricos (letras, símbolos, números, cualquier tipo de carácter). Dicho de otra manera, se utiliza para conocer la cantidad de celdas que no están vacías.
Se utiliza para conocer la cantidad de celdas “en blanco”. Es decir, la cantidad de celdas vacías.
Se utiliza para contar la cantidad de celdas que cumplen con una determinada condición. Es decir, si se cumple la condición especificada en el argumento, cuenta la cantidad de celdas, excluyendo a las que no cumplen con esa condición. El argumento de esta función tiene dos partes:
Rango de celdas
que debe contarCondición (siempre
colocada entre comillas)
Actividades Excel Profesor: Jacinto García Torres
Ejemplo: La siguiente tabla contiene diferentes tipos de datos:
A B C
1 perro 70% 15/09/2017
2 casa sopa
3 5 28
4 ??? cena 0,5
Cantidad De celdas que contiene datos numéricos
5 = CONTAR (A1:C4). Devuelve 5
Cantidad de celdas que contienen datos alfanuméricos
10 =CONTARA (A1:C4). Devuelve 10
Cantidad de celda en blanco (vacías)
2 CONTAR.BLANCO (A1:C4). Devuelve 2
Cantidad de números menores a 10
2 CONTAR.SI (A1:C4;”<10”). Devuelve 2
Cantidad de palabras que empiezan con c
2 CONTAR.SI(A1:C4;”c*”). Devuelve 2
Actividades Excel Profesor: Jacinto García Torres
EJERCICIO 27: FUNCIONES PARA CONTAR
Nº Apellidos y nombre
Sector Cargo Sueldo Estado civil
hijos
1 Alberto Duarte
MKT Director 3.500 € Casado 3
Liliana López
ADM Secretaria 1.800 € Casada 2
Raúl Martínez
MKT Diseñador 1.750 € Soltero
Cecilia Núñez
RRHH Director 3.500 € Soltera
Daniel Pérez
ADM Auxiliar 1.000 € Casado
Laura Ramírez
MKT Auxiliar 1.000 € Soltera 1
Carlos Suarez
RRHH Secretario 1.800 € Casado 4
Cantidad de empleados sin hijos
Cantidad de empleados con hijos
Cantidad de empleados del sector Marketing
Cantidad de empleados con sueldo superior a 1.000 €
Cantidad total de empleados
Nº Total de sueldos
1. Aplicarle a la tabla de datos formatos a elección.
2. Completar el Nº (con ayuda de la tecla Ctrl).
3. Colocar los sueldos en formato Moneda, con dos decimales.
4. Informar lo que se pide en cada caso, aplicando la función que corresponda.
Actividades Excel Profesor: Jacinto García Torres
EJERCICIO 28: FUNCIONES PARA CONTAR
1. Aplicarle a la tabla de datos formatos a elección.
2. Resolver mediante la función que corresponda en cada caso
CAMPAMENTO DE VERANO
EDAD (años)
Actividad
8 Equitación
12 Natación
9 Tenis
11 Tenis
7 Equitación
12 Tenis
11 Tenis
9 Tenis
5 Equitación
12 Tenis
10 Natación
8 Equitación
12 Tenis
8 Natación
10 Natación
7 Natación
12 Tenis
1 Equitación
26 Tenis
5 Tenis
10 Equitación
5 Tenis
12 Equitación
11 Equitación
12 Tenis
12 Tenis
5 Natación
11 Equitación
Actividades Excel Profesor: Jacinto García Torres
EJERCICIO 29: FUNCIONES PARA CONTAR
ALUMNO NOTA 1 NOTA 2 NOTA 3 PROMEDIO
ARANA, Facundo 9 7 8
ECHARRI, Pablo 5 7 6
FRANCHELA, Guillermo 7 5 6
FURRIEL, Joaquín 5 9 8
KRUM, Paola 8 10 9
LOPILATO, Darío 7 7 7
LOPILATO, Luisana 3 4 5
OREIRO, Natalia 6 7 8
PEÑA, Florencia
SACONE, Viviana 9,5 10 9
Cantidad de alumnos:
Cantidad alumnos sin notas:
Cantidad alumnos aprobados:
Cantidad alumnos suspensos:
3. Aplicarle a la tabla de datos formatos a elección.
4. Calcular los promedios de cada alumno (función PROMEDIO).
5. Resolver mediante la función que corresponda en cada caso.
Actividades Excel Profesor: Jacinto García Torres
FUNCIONES LÓGICAS: SI La función SI es una función lógica que, tal como su nombre lo indica, implica condiciones. Es decir que frente a una situación dada (condición) aparecen dos alternativas posibles:
• SI se cumple la condición, la función debe devolver algo (un número o una palabra)
• SI no se cumple la condición, la función debe devolver otra cosa (un número o una palabra.
Estructura de la función SI:
Pero muchas veces, dentro de la función SI debemos hacer algún tipo de cálculo, porque necesitamos que devuelva un número. Vamos a analizar el siguiente ejemplo: Una agencia de viajes ofrece un descuento de 100 € sobre el costo total del viaje si el destino es Miami
A B C D 1 DESTINO COSTO DEL VIAJE PRECIO FINAL CONDICION 2 Canada 2.265 € 2.265 € =SI(A2="Miami";B2‐100;B2) 3 Cancún 1.670 € 1.670 € =SI(A3="Miami";B3‐100;B3) 4 Miami 1.800 € 1.700 € =SI(A4="Miami";B4‐100;B4)
Analizamos:
1. Se plantea la condición. Como se trata de comparar una palabra solo admite el signo igual y el texto va entre comillas.
2. Al costo del viaje se le restan 100, que es lo que debe hacer si la condición se cumple.
3. Si la condición no se cumple (es decir, si no encuentra la palabra "Miami") se repite el valor de la celda del costo del viaje.
Actividades Excel Profesor: Jacinto García Torres
EJERCICIO 30: FUNCION SI
ALUMNO NOTA 1 NOTA 2 NOTA 3 PROMEDIO PROMOCIONA
A B C D E F G
Mayor calificación:
Menor calificación:
Darle formato a la tabla.
Inventa tres notas para cada alumno.
Hallar los promedios, el promedio más alto y el más bajo, utilizando las funciones que correspondan en cada caso.
La columna PROMOCIONA se completa mediante una función SI, que debe devolver:
“Aprobado”, a quienes tienen un promedio mayor o igual a 5.
“Recupera”, al resto.
Actividades Excel Profesor: Jacinto García Torres
EJERCICIO 31: FUNCION SI Una agencia de turismo ofrece comisiones a sus vendedores, de acuerdo al sector que pertenezcan. Completar la tabla de datos de acuerdo a las consignas que aparecen debajo de la misma.
VIAJES EL CORTE INGLES
Sector Comisión
Internacional 5%
Nacional 3%
EMPLEADOS SECTOR IMPORTE DE VENTAS
COMISION
Luis García INT 25.600,00 €
Elena Mora NAC 12.890,00 €
Juan Coria INT 32.000,00 €
Ana Torres NAC 8.950,00 €
Mario Gómez NAC 9.600,00 €
Daniel Casas INT 16.890,00 €
Sivia López NAC 5.980,00 €
Mateo Ortiz INT 39.000,00 €
Totales ...
Número de empleados
Nº de empleados del Sector Internacional
Nº de empleados del Sector Nacional
Promedio de ventas
Mayor comisión obtenida
Menor comisión obtenida
1. Dale un formato similar al de la tabla.
2. COMISIÓN: utilizando la función SI, calcular la comisión que corresponda de acuerdo al sector que pertenezca.
3. TOTALES: calcular el total del importe de ventas y el total de comisiones.
4. Resolver lo pedido en cada caso en las celdas posteriores.
Actividades Excel Profesor: Jacinto García Torres
EJERCICIO 32: FUNCION SI Una empresa lleva en una planilla de Excel el registro de sus ventas. Completar la planilla utilizando las funciones y fórmulas necesarias en cada caso.
Recargo Tarjeta 10%
Descuento Contado 5%
Artículo Precio
Forma de pago
Precio de venta
A1 50,00 € TARJETA
A2 32,00 € CONTADO
A3 18,00 € CONTADO
A4 125,00 € TARJETA
A5 230,00 € TARJETA
A6 48,00 € TARJETA
A7 44,00 € CONTADO
A8 20,00 € CONTADO
A9 12,00 € CONTADO
A10 140,00 € TARJETA
El precio de venta se obtiene mediante una función SI, que debe calcular el porcentaje de recargo correspondiente sobre el precio, si la forma de pago es TARJETA; y el porcentaje de descuento correspondiente sobre el precio, si la forma de pago es CONTADO. EJERCICIO 33: FUNCION SI
Rellena las celdas que quedan por calcular. En rentabilidad, escribir "rentable" si él % de ocupación supera el 80%, y "no rentable" en caso contrario.
Actividades Excel Profesor: Jacinto García Torres
FUNCIONES ANIDADAS Muchas veces, para resolver algún tipo de cálculo o situación en una planilla de Excel, no nos resulta suficiente una función, sino que debemos utilizar varias funciones, una dentro de otra. Cuando utilizamos una función dentro de otra, tenemos una función anidada. Las funciones que “metemos dentro” de otra función actúan como argumentos de la función principal. Ejemplo:
La segunda función SI es parte del argumento de la función SI principal Cuando frente a una condición tenemos dos o más posibilidades, tenemos que anidar una función SI dentro de otra función SI. En el ejemplo, la situación es la siguiente:
Si el alumno obtiene una calificación mayor o igual a 7 puntos, está aprobado.
Pero si el alumno obtiene una calificación menor a 7, se presentan dos situaciones:
‐ Si obtiene una calificación mayor o igual a 4 puntos, debe hacer el
examen en el turno de diciembre.
‐ Si obtiene una calificación menor a 4 puntos, debe hacer el examen en el turno de febrero.
Cada una de las funciones SI (la principal y la anidada) conservan su estructura, con tres elementos en el argumento.
Actividades Excel Profesor: Jacinto García Torres
EJERCICIO 34: FUNCIONES ANIDADAS En Jaén se llevó a cabo un campeonato de futbol. Esta es la tabla con los equipos y los resultados obtenidos:
En la columna RESULTADOS DEL GRUPO 1 debe aparecer:
"Ganó" si la cantidad de goles es mayor a la del equipo del grupo 2.
"Empató", si la cantidad de goles es igual a la del equipo del grupo 2.
"Perdió", si la cantidad de goles es menor a la del equipo del grupo 2.
GRUPO 1 GOLES GRUPO 2 GOLES RESULTADOS DEL
GRUPO 1
Jaén Atlético 1 La Loma 3
La Gloria 0 San Juan 0
El Valle 4 La Merced 1
Real Jaén 2 Bulevard 1
Ciudad Jardín 2 Peñamefecit 2
Actividades Excel Profesor: Jacinto García Torres
EJERCICIO 35: FUNCIONES ANIDADAS Se realizó una exposición de perros y se obtuvieron los siguientes resultados:
RAZA VALOR DEL PERRO RESULTADO NUEVO VALOR DEL
PERRO
LABRADOR 1.500,00 € 2
RETRIEVER 1.700,00 € 1
GOLDEN RETRIEVER 1.900,00 € 3
ROTTWHILLER 1.800,00 € 6
DOGO 1.400,00 € 4
DOBERMAN 1.600,00 € 5
PITTBULL 1.300,00 € 7
De acuerdo al resultado obtenido en la tabla el valor del animal se incrementa de acuerdo a los siguientes parámetros:
Si obtuvo el 1 puesto, el valor se duplica.
Si obtuvo entre el 2 y 3, el valor se incrementa en 300 €.
Si obtuvo entre 4 y 7, el valor no se incrementa. Completar la columna NUEVO VALOR DEL PERRO utilizando una función SI ANIDADA en la que considere los datos ofrecidos anteriormente.
Actividades Excel Profesor: Jacinto García Torres
EJERCICIO 36: FUNCIONES ANIDADAS En un colegio el valor de la cuota es de 150 €, si ésta es abonada hasta día 10 inclusive.
Si la cuota se abona hasta el día 4 inclusive, se le hace un descuento del 5%.
Si se abona después del día 10, se le aplica un recargo del 5%. Completar los días del mes como serie lineal de incremento 1 y límite 31 Calcular el valor de las cuotas en los diferentes días de un mes:
VALOR DE LA CUOTA:
150,00 €
MES DE MAYO
FECHA DE PAGO
CUOTA
Actividades Excel Profesor: Jacinto García Torres
EJERCICIO 37: FUNCIONES ANIDADAS
Recargo Tarjeta crédito 10%
Recargo Tarjeta debito 5%
Contado Sin recargo
Artículo Precio Forma de pago
Precio de venta
A1 50,00 € TARJETA CREDITO
A2 32,00 € CONTADO
A3 18,00 € TARJETA DEBITO
A4 125,00 € TARJETA CREDITO
A5 230,00 € TARJETA DEBITO
A6 48,00 € TARJETA DEBITO
A7 44,00 € CONTADO
A8 20,00 € CONTADO
A9 12,00 € CONTADO
A10 140,00 € TARJETA CREDITO
El precio de venta se obtiene mediante la función SI anidada, la cual debe calcular el precio con el porcentaje de recargo correspondiente en cada caso.
Actividades Excel Profesor: Jacinto García Torres
FUNCIONES LÓGICAS: Y / O FUNCIÓN Y La función Y devuelve VERDADERO si se verifican todas las condiciones planteadas en su argumento. Si se verifica una sola de las condiciones o ninguna, devuelve FALSO. Ejemplo:
ALUMNOS % DE
INASISTENCIAS NOTA
Y
(Se deben cumplir ambas condiciones)
A 25 7 VERDADERO B 30 3 FALSO C 12 10 VERDADERO D 10 4 VERDADERO E 45 5 FALSO F 8 2 FALSO G 20 5 VERDADERO
Para aprobar un curso los alumnos deben considerar dos variables:
1. que el % de inasistencias sea menor o igual a 25 Y.
2. que la nota obtenida sea mayor o igual a 4. La sintaxis de esta función es:
=Y(B2<=25;C2>=4)
Actividades Excel Profesor: Jacinto García Torres
FUNCIÓN O La función O devuelve VERDADERO si se verifica una sola de las condiciones planteadas en su argumento. Si no se verifica ninguna condición, devuelve FALSO. Ejemplo:
ALUMNOS % DE
INASISTENCIAS NOTA
Y
(Se deben cumplir ambas condiciones)
A 25 7 VERDADERO B 30 3 FALSO C 12 10 VERDADERO D 10 4 VERDADERO E 45 5 VERDADERO F 8 2 VERDADERO G 20 5 VERDADERO
Para aprobar un curso los alumnos deben considerar dos variables:
1. que el % de inasistencias sea menor o igual a 25 o 2. que la nota obtenida sea mayor o igual a 4.
La sintaxis de esta función es:
=O(B2<=25;C2>=4) Las funciones lógicas Y – O se utilizan anidadas dentro de la función SI, para obtener unresultado con sentido.
ALUMNOS % DE
INASISTENCIAS NOTA
SI ‐ Y (Se deben cumplir
ambas condiciones)
SI ‐ O (Se deben cumplir
ambas condiciones)
A 25 7 APROBADO APROBADO B 30 3 SUSPENSO APROBADO C 12 10 APROBADO APROBADO D 10 4 APROBADO APROBADO E 45 5 SUSPENSO APROBADO F 8 2 APROBADO APROBADO G 20 5 APROBADO APROBADO
La función Y y la función O se utilizan como condición en el argumento de la función SI: Sintaxis:
=SI(Y(B8<=25;C8>=4);"APROBADO";"SUSPENSO")
=SI(O(B8<=25;C8>=4);"APROBADO";"SUSPENSO")
Actividades Excel Profesor: Jacinto García Torres
EJERCICIO 38: FUNCIONES LÓGICAS ANIDADAS (Y/O) Los empleados de una agencia de turismo estudiantil vendieron viajes durante el mes de marzo. Se analizan dos variables:
1. Que la cantidad de viajes vendidos sea mayor a 100 Y 2. Que los precios sean mayores a 500 €
VENDEDORES VIAJES
VENDIDOS PRECIOS
SI ‐ Y ( se debe cumplir ambas condiciones)
A 150 15.000,00 €
B 70 400,00 €
C 170 17.000,00 €
D 30 3.000,00 €
E 50 5.000,00 €
F 25 250,00 €
G 200 20.000,00 €
SI CUMPLEN AMBAS CONDICIONES, EL EMPLEADO RECIBE COMO PREMIO UN VIAJE. EN CASO CONTRARIO, NADA (" ")
Actividades Excel Profesor: Jacinto García Torres
EJERCICIO 39: FUNCIONES LÓGICAS ANIDADAS (Y/O) Una agencia de viajes otorgará un premio a sus vendedores teniendo en cuenta las siguientes condiciones:
1. Que la cantidad de viajes vendidos sea superior a 25 O 2. Que los viajes hayan sido pagados en su totalidad
VENDEDORES VIAJES
VENDIDOS SITUACION PREMIO
A 150 PAGADOS
B 70 PENDIENTES
C 170 PAGADOS
D 30 PAGADOS
E 50
F 25
G 200 PENDIENTES
H 40 PAGADOS
I 60 PENDIENTES
Cantidad de vendedores que no obtuvieron premio
Cantidad de vendedores que obtuvieron premio
Aplicar las funciones necesarias para que la columna PREMIO devuelva "1 viaje a Cancún" si el vendedor cumple uno de los dos requisitos.
Aplicar formatos para mejorar la presentación.
Colocar los valores en formato Moneda (€)
Actividades Excel Profesor: Jacinto García Torres
FUNCIONES DE BÚSQUEDA La función BUSCARV relaciona dos tablas de datos, estableciendo una correspondencia entre las mismas. Busca un valor específico en una columna de una tabla y devuelve, en la misma fila, un valor de otra columna de otra tabla, denominada matriz de datos. NOTA: la letra V del nombre de la función significa vertical, es decir, busca los datos que se encuentran en columnas. Veamos el siguiente ejemplo: Un profesor tiene la calificación de sus alumnos en notas numéricas, pero el instituto en la que trabaja le pide que muestre las notas, de acuerdo a los siguientes criterios:
Debe completar su plantilla de seguimiento para adecuarla a los requisitos del instituto para completar la columna CALIFICACIÓN CONCEPTUAL va a utilizar una función BUSCARV que le permita relacionar los datos de su planilla con los datos de la matriz.
1. Indica la celda que contiene el dato que quiero comparar.
Actividades Excel Profesor: Jacinto García Torres
2. Indica el rango de celdas que contiene la matriz de datos. En este caso se usó
referencia absoluta, para poder completar automáticamente el resto de la segunda tabla.
3. Indica la columna que se desea comparar; en este caso, es la segunda columna.
4. El valor VERDADERO indica que se busca un valor aproximado. Si no se coloca
este último elemento, se asume que es verdadero. Si se coloca FALSO, buscará el valor exacto.
Actividades Excel Profesor: Jacinto García Torres
EJERCICIO 40: FUNCIÓN BUSCAR Una estación de servicio ofrece premios por puntos acumulados cada vez que se carga combustible. La tabla de premios es la siguiente:
Nº de puntos Premio
500 Una camiseta
1.000 Un pendriver
2.000 Un reloj
4.000 Una mochila
Completar la segunda tabla con la función BUSCARV tomando los datos de la matriz.
Ganador Nº de puntos Premio
Antonio Fernández 600 Catalina Lago 1.200 Roberto Vega 900 Luis Ferrer 2.100 Ana Sánchez 500 José Alonso 4.050
EJERCICIO 41: FUNCIÓN BUSCAR
Actividades Excel Profesor: Jacinto García Torres
EJERCICIO 42: FUNCIÓN BUSCAR Completar las columnas vacías atendiendo a las consignas que aparecen en los recuadros.
Barco
Duración del viaje
Titanic 2 14
Ocean Princess 11
Código de reserva
Barco deseado Días que dura el viaje
Cantidad de
pasajeros
¿Se puede efectuar la reserva?
131 Titanic2 2
Ocean Princess 4
Ocean Princess 3
Titanic2 2
Ocean Princess 1
Titanic2 5
Titanic2 3
Ocean Princess 2
Ocean Princess 3
Titanic2 2
Ocean Princess 1
Titanic2 4
Titanic2 3
1. Código de reserva: Completa como serie lineal con incremento de 1. 2. Días que dura el viaje: resolver mediante la función BURCARV. 3. ¿Se puede efectuar la reserva?: Sí o No solo se podrá efectuar la reserva si el
barco deseado es el Ocean Princess y la cantidad de pasajeros es menor de 4. En caso contrario debe aparecer no.
Actividades Excel Profesor: Jacinto García Torres
EJERCICIO 43: FUNCIÓN BUSCAR A partir de los datos de la matriz, se busca completar el recuadro de modo tal que poniendo el código del empleado se completen automáticamente los otros datos de cada empleado.
1. Aplica los formatos necesarios para que sus tablas de datos se vean similares a las del modelo.
2. El valor a buscar será la celda de color azul (B11).
3. Tanto para NOMBRE Y APELLIDOS como para SUELDO utiliza la matriz de datos
A3:C7. Lo que cambia en cada caso es la columna a comparar, de acuerdo con lo que se pida en cada caso.
4. A COBRAR: se resuelve sumándole al sueldo, que aparece en la celda B13, el cálculo del porcentaje B13*A9.
Una vez realizadas las funciones y formulas correspondientes solo habrá que poner el código en la casilla B11 y los demás datos aparecerán automáticamente.
A B C
1 EMPLEADO
2 CODIGO NOMBRE Y APELLIDO SUELDO
3 1000 Antonio Ortiz 1.500,00 €
4 1001 Alejandro Orta 980,00 €
5 1002 Laura Mesa 760,00 €
6 1003 Silvia Reche 1.200,00 €
7 1004 Pedro Soler 890,00 €
8 PREMIO: 9 10% 10 11 INTRODUCE EL CODIGO: 12 NOMBRE Y APELLIDO: 13 SUELDO: 14 A COBRAR:
Actividades Excel Profesor: Jacinto García Torres
EJERCICIO 44: FUNCIÓN BUSCAR
CÓDIGOTOUR
DESCRIPCIÓN TOUR
CÓDIGO
OPERADOR
OPERADOR
RESPONSABLE
CANTIDAD PASAJEROS
TIPO DE
CONTINGENTE
IMPORTE RECAUDADO POR SALIDA
115 ? ? ? 35 ? ? 220 ? ? ? 22 ? ? 120 ? ? ? 18 ? ? 310 ? ? ? 10 ? ? 320 ? ? ? 32 ? ? 115 ? ? ? 18 ? ? 310 ? ? ? 10 ? ? 310 ? ? ? 8 ? ? 115 ? ? ? 15 ? ?
Tablas auxiliares (matrices de datos):
1. DESCRIPCIÓN TOUR: debe usar función BUSCARV. El dato en común es el Código de Tour.
2. CÓDIGO OPERADOR: ídem anterior. La función BUSCARV debe devolver la letra
correspondiente al código.
3. OPERADOR RESPONSABLE: ahora, el dato en común entre dos tablas es el Código de Operador (la función BUSCARV debe devolver el nombre del operador responsable).
4. TIPO DE CONTINGENTE: se resuelve mediante una función SI ANIDADA. Si la cantidad de pasajeros es mayor a 20, debe devolver "Numeroso"; si la cantidad de pasajeros es mayor a 10, debe devolver "Regular"; en caso contrario, debe devolver "Pequeño".
5. IMPORTE RECAUDADO POR SALIDA: esta es un poco difícil, pero posible. El valor del
viaje (o "costo x pax") se obtiene mediante la función BUSCARV. Luego, en la misma
fórmula, hay que obtener el total recaudado.
Actividades Excel Profesor: Jacinto García Torres
FUNCIONES FINANCIERAS
Las funciones financieras de Excel te ayudarán en cálculos como el de amortización, la tasa de interés anual efectiva, el interés acumulado, la tasa nominal entre otros cálculos que te ayudarán en tus modelos financieros. EJERCICIO 45: FUCIONES FINANCIERA Vamos a desarrollar un ejercicio práctico para calcular el pago de un préstamo basándonos en pagos constantes y una tasa de interés constante. Para realizar este ejercicio haremos uso de la función PAGO. La sintaxis de la función PAGO es PAGO(tasa;nper;va;vf;tipo), vamos a describir cada uno de sus parámetros:
tasa = es el tipo de interés del préstamo.
nper = es el número total de pagos del préstamo.
va = es el valor actual del total de pagos
vf = es el valor futuro o un saldo en efectivo que se desea lograr tras el último pago. Si vf se omite, se asume que vale 0, es decir, tras el último pago no queda ningún saldo pendiente lo que ocurre cuando se trata de un préstamo.
tipo = indica el vencimiento de pagos. ‐ (tipo = 0) ‐‐> al final del período ‐ (tipo = 1) ‐‐> al inicio del período
Vamos a pedir un préstamo de 100,000 €, en un período de 30 años, es decir 30*12=360 nº de pagos mensuales, a un interés del 6%.
1. Sitúate en la celda A1 y escribe Préstamo 2. Sitúate en la celda A2 y escribe Tasa 3. Sitúate en la celda A3 y escribe Nº Pagos 4. Sitúate en la celda A4 y escribe Tipo 5. Sitúate en la celda A5 y escribe Cuota Mensual 6. Sitúate en la celda B1 y escribe 100.000 € 7. Sitúate en la celda B2 y escribe 6 % 8. Sitúate en la celda B3 y escribe 360 9. Sitúate en la celda B4 y escribe 0 10. Sitúate en la celda B5 y escribe =PAGO(B2/12;B3;B1;0;B4)
Con esta función indicamos que el vencimiento del pago se realiza al final del período y que no existen cuotas al finalizar los pagos. Como resultado debemos obtener ‐599,55 €, que será la cuota mensual. El número sale negativo porque el efectivo que se paga, por ejemplo depósitos en cuentas de ahorros, cuotas de un préstamo, se representa con números negativos; el efectivo que se recibe, se representa con números positivos.
Actividades Excel Profesor: Jacinto García Torres
Con la función PAGO también podemos calcular qué cuota mensual debemos ingresar para ahorrar una cantidad de dinero en X años. Vamos a calcular cómo podemos ahorrar 30.000 € en 5 años, con un interés del 6%.
1. Sitúate en la celda C1 y escribe Ahorro 2. Sitúate en la celda C2 y escribe Tasa Anual 3. Sitúate en la celda C3 y escribe Años 4. Sitúate en la celda C4 y escribe Ingresos Mensuales 5. Sitúate en la celda D1 y escribe 30.000 € 6. Sitúate en la celda D2 y escribe 6% 7. Sitúate en la celda D3 y escribe 5 8. Sitúate en la celda D4 y escribe =PAGO(D2/12;D3*12;0;D1)
Como resultado debemos obtener en la celda D4 la cantidad de ‐429,98 €. Vamos a calcular ahora los intereses pagados en un período de tiempo por un préstamo. Por ejemplo, los intereses del primer ejemplo. Para realizar este ejercicio utilizaremos la función PAGOINT Esta función tiene la siguiente sintaxis PAGOINT(tasa;periodo;nper;va;vf;tipo)
tasa = es el tipo de interés del préstamo.
período = es el período para el que se desea calcular el interés y debe estar entre 1 y el parámetro nper
nper = es el número total de pagos del préstamo.
va = es el valor actual del total de pagos
vf = es el valor futuro o un saldo en efectivo que se desea lograr trás el último pago. Si vf se omite, se asume que vale 0, es decir, trás el último pago no queda ningún saldo pendiente.
tipo = indica el vencimiento de pagos. ‐ (tipo = 0) ‐‐> al final del período ‐ (tipo = 1) ‐‐> al inicio del período
1. Sitúate en la celda E1 y escribe Préstamo 2. Sitúate en la celda E2 y escribe Tasa Anual 3. Sitúate en la celda E3 y escribe Interés en la Cuota Nº 4. Sitúate en la celda E4 y escribe Cantidad de Cuotas 5. Sitúate en la celda E5 y escribe Interés 6. Sitúate en la celda F1 y escribe 100.000 € 7. Sitúate en la celda F2 y escribe 6% 8. Sitúate en la celda F3 y escribe 1 9. Sitúate en la celda F4 y escribe 360 10. Sitúate en la celda F5 y escribe =PAGOINT(F2/12;F3;F4;F1)
Actividades Excel Profesor: Jacinto García Torres
Esta función nos debe devolver ‐500,00 € que es el interés pagado en la primera cuota del préstamo. Cambiando el valor en F3 podrás ver el interés pagado en cada caso. Vamos a calcular ahora las cuotas amortizadas para un préstamo. Seguimos basándonos en el primer ejercicio. Para realizar este ejercicio, utilizaremos la función PAGOPRIN Esta función tiene la siguiente sintaxis PAGOPRIN(tasa;periodo;nper;va;vf;tipo)
tasa = es el tipo de interés del préstamo.
período = es el período para el que se desea calcular la amortización y debe estar entre 1 y el parámetro nper
nper = es el número total de pagos del préstamo.
va = es el valor actual del total de pagos
vf = es el valor futuro o un saldo en efectivo que se desea lograr tras el último pago. Si vf se omite, se asume que vale 0, es decir, tras el último pago no queda ningún saldo pendiente.
tipo = indica el vencimiento de pagos. ‐ (tipo = 0) ‐‐> al final del período ‐ (tipo = 1) ‐‐> al inicio del período
1. Sitúate en la celda A8 y escribe Préstamo 2. Sitúate en la celda A9 y escribe Tasa Anual 3. Sitúate en la celda A10 y escribe Cálculo amortización en cuota nº 4. Sitúate en la celda A11 y escribe Cuotas Totales 5. Sitúate en la celda A12 y escribe Amortizado 6. Sitúate en la celda B8 y escribe 100.000 € 7. Sitúate en la celda B9 y escribe 6 % 8. Sitúate en la celda B10 y escribe 1 9. Sitúate en la celda B11 y escribe 360 10. Sitúate en la celda B12 y escribe =PAGOPRIN(B9/12;B10;B11;B8)
Como resultado debemos obtener en la celda B12 la cantidad de ‐99,55 € que, si nos fijamos, es el resultado de la diferencia de quitar los intereses a la cuota total del préstamo. Como vemos en la primera cuota de la amortización, los intereses son más del 80% y la amortización propia del préstamo no llega al 20%.
Actividades Excel Profesor: Jacinto García Torres
EJERCICIO 46: FUCIONES FINANCIERA
Vamos a pedir un préstamo de 60.000 € a pagar en 5 años con un interés fijo del 9 % a una entidad bancaria y queremos saber el desembolso mensual que deberemos efectuar y el desglose en intereses y amortización
Copia los datos que ves en la imagen. Todos excepto los que están en color rojo.
Calcula mediante funciones los valores para Cuota (función PAGO), Interés (función PAGOINT) y Amortización(función PAGOPRIN).
Lo más cómodo es que realices las funciones para las celdas C7 a E7 y que luego las copies para completar el resto del rango (celdas C8:E12).
Al ir introduciendo las fórmulas observarás que los valores aparecen en rojo automáticamente. Éste es un formato automático que se da a los valores negativos.
Escribe en la celda C7, la fórmula =PAGO($C$3/12;$C$4;$C$1).
En este caso utilizamos la función PAGO, dividimos el interés por 12 ya que en C3 tenemos el interés anual y debemos poner el interés mensual. Utilizamos referencias absolutas para no tener problemas cuando copiemos las celdas.
Escribe en la celda D7 la fórmula =PAGOINT($C$3/12;B7;$C$4;$C$1). Aquí dejamos B7 como referencia relativa para que al copiar coja los sucesivos números de vencimiento.
En este caso, utilizamos la función PAGOINT.
Escribe en la celda E7, la fórmula =PAGOPRIN($C$3/12;B7;$C$4;$C$1).
Actividades Excel Profesor: Jacinto García Torres
GRAFICOS
Para crear un gráfico de una manera sencilla hay que seleccionar los datos y elegir el tipo de gráfico que desea utilizar en la cinta de opciones (ficha Insertar, grupo Gráficos).
Después de crear un gráfico, se puede modificar. Solo hay que seleccionar y elegir una de las nuevas fichas (Diseño, Presentación y Formato) que aparecen en la cinta de opciones (herramientas de gráfico):
Para modificar un gráfico, podemos:
Cambiar la presentación de los ejes del gráfico. Puedes especificar la escala de los ejes y ajustar el intervalo entre los valores o categorías que se muestran.
Agregar títulos y rótulos de datos a un gráfico Para que la información que aparece en el gráfico sea más clara. Puedes agregar un título de gráfico, títulos de eje y rótulos de datos (etiqueta de datos: etiqueta que proporciona información adicional acerca de un marcador de datos, que representa un solo punto de datos o valor que se origina a partir de una celda de una hoja de datos.).
Agregar una leyenda o una tabla de datos. Puedes mostrar u ocultar una leyenda (leyenda: cuadro que identifica los diseños o colores asignados a las series de datos o categorías de un gráfico.) o cambiar su ubicación. En algunos gráficos, puede mostrar también una tabla de datos (tabla de datos: rango de celdas que muestra los resultados de sustituir diferentes valores en una o más fórmulas).
Aplicar opciones especiales en cada tipo de gráfico. Puedes aplicar líneas de tendencia (representación gráfica de tendencias en series de datos, como una línea inclinada ascendente para representar el aumento de ventas a lo largo de un período de meses), barras de error y otras opciones para los diferentes tipos de gráficos.
Actividades Excel Profesor: Jacinto García Torres
EJERCICIO 47: GRAFICOS
Introduce los siguientes datos utilizando la fuente Century Gothic, tamaño 10.
Seleccionar los rangos, A5:A8, D5:D8, F5:F8 y H5:H8 y hacer clic en insertar / gráfico / columna agrupada / Aceptar. Hacer clic en Diseño / Seleccionar datos. Después haz clic en Datos de serie y cambiar <serie1> por <Abril>, <serie2> por <Mayo> y <serie3> por <Junio>.
Hacer clic en Agregar elementos del gráfico y poner como título de gráfico: vendedores. Eje horizontal primario: Empleados. Leyenda: Derecha.
Actividades Excel Profesor: Jacinto García Torres
EJERCICIO 48: GRAFICOS
Abre un libro nuevo.
Escribe la tabla tal y como se muestra en la figura y representa los datos de la tabla en un gráfico de sectores 3D.
Cierra y guarda los cambios.
Actividades Excel Profesor: Jacinto García Torres
EJERCICIO 49: GRAFICOS
Utiliza la fuente Century Gothic, tamaño 10 y realiza la siguiente tabla:
Crea un gráfico con columnas agrupadas con efecto 3D, para las horas por día, y otro a tu elección para el sueldo a cobrar y como queda en la figura.
Actividades Excel Profesor: Jacinto García Torres
EJERCICIO 50: GRAFICOS
Crea un libro nuevo. El libro debe tener cuatro hojas nombradas de la siguiente forma: la primera, Participación, la segunda, Rendimiento1, la tercera, Rendimiento2, y la cuarta, Resultados.
A continuación, puedes ver cómo deben quedar las cuatro hojas, los tipos de fuente, tamaños y colores deben ser parecido a lo que ves.
Cierra y guarda los cambios.
Actividades Excel Profesor: Jacinto García Torres
Actividades Excel Profesor: Jacinto García Torres
DATOS: ORDENAR Y FILTRAR
Una de las funcionalidades más útiles de Excel es la herramienta Datos. Con ella puedes obtener información de forma rápida sobre diferentes datos de forma separada y así tener siempre listos las cifras para tus informes.
En Excel puedes realizar un filtrado de datos totalmente personalizado proporcionando los criterios que deseas aplicar a la información. Este tipo de filtrado es conocido como Filtro avanzado.
Actividades Excel Profesor: Jacinto García Torres
EJERCICIO 51: ORDENAR Y FILTRAR
Crea la siguiente tabla de datos y darle un formato a tu elección:
1. Ordena de la A a la Z los nombres de los vendedores, para ello sitúate en la columna C y haz clic en el icono AZ de la pestaña datos.
2. Ordena las cifras de ventas de mayor a menor con el mismo icono que antes.
3. Realiza un filtro de las ventas por vendedor de manera que obtengas en
pantalla las realizadas por Rosa. Hazlo desde el grupo de trabajo Ordenar y filtrar, luego pulsa en el icono Filtro, despliega el triángulo negro que aparece a la derecha de la palabra Vendedor y por último desmarca los vendedores restantes y pulsa en Aceptar
4. Filtra ahora para que aparezcan en pantalla las ventas tanto de Rosa como de
Jonatán, hazlo del mismo modo que en el apartado anterior.
5. Borra el filtro anterior: despliega el triángulo negro que aparece a la derecha de la palabra Vendedor y haz clic sobre Borrar filtro de Vendedor
6. Obtén las cifras de ventas que sean superiores o iguales a 1.300 €, desde la
misma ventana anterior, en Filtros de número, elige Mayor o igual que…
7. Filtra los datos de las empresas con importes superiores a 1.500 €.
8. Realiza un filtro condicional. Desde el grupo de trabajo Estilos de la pestaña Inicio. Selecciona desde D1 a D11 incluidos, vete a Inicio, Formato condicional, Escalas de color, haz clic sobre la primera opción de las que te aparecen a la derecha. En la hoja obtendrás en colores verdes las cifras más altas, en rojo las más bajas y en amarillo y naranja las intermedias. Si quieres puedes optar por otras gamas de colores o crear las tuyas
Código Cliente Empresa Cliente Vendedor Ventas del mes
1 Vallalimp Josefa 1.200,00 €
2 MSDSystem Pedro 1.515,00 €
3 ROMBO2 Alfonso 1.559,00 €
4 TECON2000 Jonatan 1.423,00 €
5 Formadatas Rosa 1.220,00 €
6 Pluscuam Pedro 1.513,00 €
7 SolySombra Alfonso 1.052,00 €
8 PSIM Rosa 1.113,00 €
9 RECDATA Jonatan 1.246,00 €
10 SIOCA Rosa 1.321,00 €
Actividades Excel Profesor: Jacinto García Torres
EJERCICIO 52: FILTROS AVANZADOS
Crea la siguiente tabla de datos:
a) Filtra los registros del departamento de Finanzas. Para ello coloqué los criterios por arriba de la tabla de datos, aunque realmente su ubicación no es de importancia, si bien deben especificarse dentro de celdas de la misma hoja.
Antes de aplicar el filtro avanzado debo seleccionar la tabla de datos (A4:D13) y posteriormente ir a la ficha Datos y pulsar el botón Avanzadas que se encuentra en el grupo Ordenar y filtrar. Se mostrará el cuadro de diálogo Filtro avanzado.
En el cuadro de texto para Rango de criterios se selecciona el rango que contiene las condiciones del filtro avanzado necesarias.
Actividades Excel Profesor: Jacinto García Torres
Solamente resta pulsar el botón Aceptar para aplicar el filtro.
b) Especificar condiciones para una segunda columna.
Ahora vamos a filtrar la información de las personas que tienen apellido Hernández y que además pertenecen al departamento de Finanzas. Para filtrar los datos por estos criterios debo agregar dichas condiciones en otra celda.
Seguidamente vamos a filtro avanzado En el cuadro de texto para Rango de criterios se selecciona el rango que contiene las condiciones del filtro avanzado necesarias. Observa cómo el Rango de la lista es el mismo que en el caso anterior ya que son los mismos datos, pero el Rango de criterios ahora abarca también las celdas que contienen el criterio para el Apellido.
Actividades Excel Profesor: Jacinto García Torres
Al aceptar los cambios Excel aplicará el filtro avanzado.
c) Especificar un criterio para cada columna.
Hasta ahora solamente se ha especificado una sola condición por columna, pero ahora vamos a agregar a los resultados del filtro la información del departamento de Informática. Para ello solamente agregaré una fila adicional al rango de criterios de la siguiente manera.
Actividades Excel Profesor: Jacinto García Torres
Al aceptar los cambios Excel filtrará la información de los empleados de Finanzas con apellido Hernández y además mostrará la información de los empleados del departamento de Informática sin importar el apellido que tengan.
Actividades Excel Profesor: Jacinto García Torres
EJERCICIO 53: ORDENAR Y FILTRAR
El departamento de personal tiene la siguiente base de datos que contiene la información sobre empleados de la empresa.
Código Nombre Apellido Departamento Cargo Fecha alta Sueldo
12 Ana Antón Administración Administrativa 09/12/1989 811,37 €
16 Diego Martín Administración Administrativo 10/02/1998 841,42 €
5 Lourdes Merino Administración Administrativa 14/01/1991 781,32 €
2 Jorge Rico Administración Director financiero 05/07/1990 2.404,05 €
14 Elena Casado Comercial Directora comercial 01/01/1996 2.554,30 €
1 Cristina Martínez Comercial Comercial 10/12/1988 1.262,13 €
15 Nuria Pérez Comercial Comercial 10/02/1998 901,52 €
8 Eva Esteve Almacén Encargado 25/12/1995 1.652,78 €
4 Oscar Cortina Gerencia Director general 05/06/1989 3.005,06 €
10 Merche Torres Gerencia Secretaria 12/01/1993 661,11 €
17 Lucas LLauradó Gerencia Presidente 12/01/2003 1,00 €
11 Jordi Fontana Informática Director Informática 06/06/1996 1.502,53 €
13 Sergio Galindo Márketing Jefe producto B 29/03/1990 1.352,28 €
9 Federico García Márketing Director márketing 11/04/1995 2.404,05 €
3 Luis Guerrero Márketing Jefe producto A 07/08/1993 1.502,53 €
7 José Bonaparte Personal Director personal 27/11/1992 1.803,04 €
6 Jaime Sánchez Producción Director producción 02/02/1986 1.803,04 €
Se pide: (Utilizando la herramienta Datos de Excel)
1. Ordenar la lista por apellido. 2. Ordenar por departamento como criterio primario y por apellido como criterio
secundario. 3. Ordenar por antigüedad en la empresa. 4. Ordenar de mayor a menor sueldo. 5. Visualizar el personal de la empresa que trabaja en el departamento de
márketing. 6. Visualizar el personal de la empresa que trabaja en el departamento de
Administración y gana menos de 812 €. 7. Visualizar el personal cuyo sueldo sea mayor de 1.200 € y trabaje en el
departamento de Administración o Comercial. 8. Visualizar los empleados cuyo apellido comience por G o M. 9. Extraer el personal que gane menos de 1.200 € y su fecha de alta esté entre los
años 1990 y 1993. 10. Extraer el personal cuyo sueldo oscile entre 900 y 1.200 € (ambos inclusive). 11. Visualizar las 10 personas de mayor sueldo en la empresa. 12. Visualizar el personal directivo de la empresa. 13. Insertar automáticamente subtotales de forma que visualicemos los costes de
personal por departamento.
Actividades Excel Profesor: Jacinto García Torres
EJERCICIO 54: ORDENAR Y FILTRAR
A partir de la información contenida en la siguiente tabla:
Contestar las siguientes preguntas utilizando filtros:
1. Cuántos coches hay de tracción delantera (D) 2. Cuántos coches pesan menos de 1000 kg. 3. Cuántos coches hay de tracción delantera y que pesen menos de 1000 kg. 4. Cuántos coches superan los 225 km/h 5. Cuántos coches hay que tienen una velocidad entre 250 y 300 km/h 6. Cuál es el precio medio de los coches de la marca Jaguar 7. Cuál es el precio medio de los coches de la marca Honda de tracción
delantera 8. Cuántos coches hay que superen la cilindrada de 2000 y su peso sea superior
a 1500 kg. o que sean de tracción total (T) y su velocidad sea superior a los 250 km/h
9. Cuántos coches hay que su precio sea inferior a los 30.000 € y su tracción sea total, o que su velocidad no exceda de los 200 km/h.
Cod. Marca Modelo Precio Cilindrada Tracción Peso Velocidad
1 AUDI A2 1.4 8.451,07 € 1390 D 895 173
2 AUDI A4 1.8 Turbo 28.300,00 € 1781 D 1375 222
3 AUDI RS4 2.7 Quatro 74.525,50 € 2671 IP 1620 250
4 AUDI TT 1.8T Coupé 32.870,00 € 1781 D 1205 228
5 BMW 316i 24.010,43 € 1895 T 1285 200
6 BMW 330Xi 39.576,65 € 2979 IP 1520 247
7 BMW 330 Ci Cabrio 44.745,35 € 2979 T 1585 247
8 BMW Z3 M Roadster 57.396,66 € 3201 T 1350 250
9 CHRYSLER Stratus 2.5 L C 37.782,02 € 2497 D 1635 208
10 CHRYSLER Viper Coupé 93.912,95 € 7990 T 1535 298
11 CHRYSLER G Voyager 3,3 LE 32.322,43 € 3301 D 1855 177
12 HONDA Legend 3.5i v6 47.780,46 € 3474 D 1670 215
13 HONDA Prelude 2.2i VE 28.728,38 € 2157 D 1670 223
14 HONDA NSX 3.2i V6 VT 96.161,94 € 3179 T 1410 275
15 JAGUAR S Type 3.0 V6 41.229,43 € 2967 T 1628 234
16 JAGUAR Sovereing 4.0 68.365,13 € 3996 T 1730 240
Actividades Excel Profesor: Jacinto García Torres
EJERCICIO 55: ORDENAR Y FILTRAR
A partir de la información contenida en la siguiente tabla:
id SECTOR NOMBRE EMPRESA CIERRE RENTABILIDAD VARIACIÓN ÍBEX
1 Servicios ENAGÁS 5,40 € ‐16,62 ‐14,95 €
2 Servicios IBERIA 1,31 € 14,21 ‐38,21 €
3 Telecomunicaciones TELEFÓNICA MÓVILES 6,21 € ‐43,55 ‐39,95 €
4 Construcción ACS 29,70 € 150,56 ‐20,66 €
5 Construcción ACERALIA 13,57 € ‐0,86 ‐13,74 €
6 Telecomunicaciones TERRA NETWORKS 4,21 € ‐64,35 ‐45,75 €
7 Banca BBVA 8,32 € ‐36,65 ‐44,50 €
8 Telecomunicaciones DEUTSCHE TELECOM 9,61 € ‐72,24 0,00 €
9 Comunicación PRISA 7,69 € ‐62,51 ‐45,28 €
10 Comunicación SOGECABLE 9,40 € ‐60,00 ‐42,72 €
11 Telecomunicaciones INDRA 5,45 € 23,07 ‐42,13 €
12 Banca BSCH 5,54 € ‐47,15 ‐47,02 €
13 Construcción FERROVIAL 25,90 € 14,16 ‐42,50 €
14 Servicios GAS NATURAL 18,13 € 12,19 2,28 €
15 Banca BANCO POPULAR 39,25 € ‐25,36 ‐12,35 €
16 Construcción FCC 20,47 € ‐10,12 0,00 €
17 Banca BANKINTER 23,62 € ‐2,44 ‐9,36 €
Contestar las siguientes preguntas utilizando filtros:
1. Cuántas empresas hay del sector Banca. 2. Cuántas empresas hay con un cierre superior a 6 €. 3. Cuántas empresas hay con una rentabilidad positiva. 4. Cuántas empresas hay del sector Telecomunicaciones que posean una
rentabilidad positiva. 5. Cuál es el precio medio en € de cierre de las empresas. 6. Cuáles son las 10 empresas con el precio de cierre más alto.
Actividades Excel Profesor: Jacinto García Torres
EJERCICIO 56: ORDENAR Y FILTRAR
A partir de la información contenida en la siguiente tabla:
Código Nombre Apellido Lugar Puntuación Puntuación acumulada
1 María Merino 5 10 25
2 Sergio Galindo 12 3 10
3 José Fuentes 1 14 28
4 Xavier Sardá 8 7 19
5 Alex Gómez 6 9 23
6 Susana Midas 14 1 5
7 Pep Sánchez 7 8 20
8 Núria Tarrida 2 13 30
9 Eugenia Torres 10 5 15
10 Jaume Sánchez 3 12 32
11 Jordi Fontana 9 6 18
12 Elena Casado 11 4 13
13 Patricia García 4 11 26
14 Albert Miró 13 2 9
Realiza las siguientes actividades utilizando filtros:
1. Ordenar la lista por apellido. 2. Ordenar por puntuación acumulada como criterio primario y por puntuación
como criterio secundario, de mayor a menor. 3. Ordenar por lugar ascendente. 4. Ordenar de mayor a menor puntuación acumulada. 5. Mostrar las personas que el nombre empieza por M. 6. Mostrar las personas que el apellido empiece por S. 7. Mostrar las personas que tengan una puntuación acumulada mayor o igual a
25. 8. Mostrar personas que su nombre empiece por J o su apellido empiece por F. 9. Mostrar los 5 primeros clasificados (lugar). 10. Número de personas que su lugar sea superior a 10 y su puntación oscile entre
15 a 25.
Actividades Excel Profesor: Jacinto García Torres
ANALISIS DE HIPOTESIS: ESCENARIOS Y TABLAS DE DATOS.
Los escenarios son especialmente útiles en los llamados análisis “Y Si...” (o “¿Qué
pasa si...?”). Es decir, aquellos análisis en los que se desea comparar los resultados
obtenidos al cambiar un determinado factor del problema.
El administrador de escenarios de Excel nos facilita la realización de distintos
supuestos, y la comparación de los resultados de éstos. Cada uno de estos supuestos
es lo que se llama escenario. Podemos hacer distintos escenarios, guardarlos,
visualizarlos, obtener informes con ellos, etc.
Dos notas previas:
Los escenarios tienen una limitación: no puede usar más de 32 celdas cambiantes. Por lo tanto, si definimos cuatro escenarios, tendremos ocho celdas cambiantes para cada uno de ellos y así sucesivamente. Sinceramente,
yo creo que nos podremos “apañar” 🙂 Será muy conveniente dar nombres con significado a las celdas cambiantes.
Hará mucho más fácil nuestro trabajo.
Las tablas de datos forman parte de las herramientas de Análisis de hipótesis que nos permiten cambiar el valor de algunas celdas para ver cómo es afectado el resultado original. Una tabla de datos analiza un conjunto de valores y determina posibles resultados. La única desventaja es que solamente se puede hacer el análisis de dos variables como máximo. En caso de que te encuentres en una situación que requiera de un análisis de tres o más variables se recomienda utilizar los Escenarios o Excel Solver.
Actividades Excel Profesor: Jacinto García Torres
EJERCICIO 57: ESCENARIOS
Copia los siguientes datos:
Nuestras variables o celdas cambiantes están en B2:B4. A cada una de ellas le he
asignado un nombre. El modelo que he preparado toma esa información y, para cada
producto, calcula las unidades vendidas, beneficio neto de cada producto y el beneficio
total. Ya tenemos las variables y el modelo.
Lo que queremos saber es qué ocurrirá con el beneficio en euros en cada uno de los
escenarios que he identificado y que se muestran en la siguiente tabla:
Vamos a la pestaña Datos y en el grupo Herramientas de datos elegimos Análisis de
hipótesis…
Actividades Excel Profesor: Jacinto García Torres
… nos parecerá una lista en la que elegimos el Administrador de escenarios.
Aquí, en el cuadro Escenarios he añadido cada uno de los que había identificado. Para
hacer esto, lo que tenemos que hacer es presionar el botón Añadir y accedemos a la
siguiente ventana:
Lo único que tendremos que hacer es rellenar los cuadros con nuestra información: un
nombre de escenario, referencias a las celdas cambiantes, podremos incluir un
comentario (se puede sobre escribir o dejar en blanco), y elegir las opciones de
Protección que vemos (sólo surtirán efecto si protegemos la hoja o el libro). Una vez
rellenada esta información, el botón Aceptar se activa y, al presionarlo, accedemos a la
ventana Valores del escenario.
Actividades Excel Profesor: Jacinto García Torres
Las etiquetas que aparecen a la izquierda de cada cuadro son los nombres de celda
que di a cada una de las celdas cambiantes. Por defecto, Excel pone en cada cuadro los
valores que tenemos en nuestro modelo por lo que los sustituiremos por los que
hemos identificado para nuestro escenario.
MOSTRAR ESCENARIOS
Si abrimos el administrador de escenarios, seleccionamos uno en el cuadro Escenarios
y presionamos en el botón Mostrar, Excel sustituye en el modelo los valores de las
celdas cambiantes por las que hemos informado para ese escenario y veremos en
nuestro modelo el resultado de ese conjunto de valores.
MODIFICAR ESCENARIOS
Para modificar un escenario, simplemente deberemos ir al Administrador de
escenarios y presionar el botón Modificar. A partir de ahí, podemos cambiar cualquier
información que deseemos de cualquier escenario.
GENERAR UN INFORME DE RESUMEN
El administrador de escenarios puede generar dos tipos de informes: un resumen y
una tabla dinámica. Estos informes presentan los valores de cada una de las celdas
cambiantes que hemos definido en Definir Escenarios y aquellos valores de resultados
que hallamos elegido.
Entra en el Administrador de escenarios y presiona el botón Resumen:
Actividades Excel Profesor: Jacinto García Torres
Hay dos tipos de informes que podemos seleccionar:
Seleccionamos el siguiente:
Actividades Excel Profesor: Jacinto García Torres
EJERCICIO 58: ESCENARIOS
Imaginemos que se nos ha encargado la organización de un evento musical en un local. Queremos vender tres tipos distintos de entradas, cada una de las cuales tendrá un precio diferente. La organización de dicho evento conlleva una serie de gastos fijos de iluminación, etc. Estamos barajando varias posibilidades en cuanto al precio a asignar a cada entrada y nos gustaría hacer una comparativa de las ganancias en función de éste. De esta manera, sabremos nuestras ganancias y será más fácil ajustar los otros conceptos.
Empezamos por realizar una hoja con los distintos conceptos y ponemos unos posibles valores para las entradas (que llamamos A, B y C para distinguirlas). El precio que pondremos es 100 € para el tipo A, 80 € para el tipo b y 50 € para el tipo C.
Hacemos las correspondientes fórmulas y para estos precios ganamos un total de 13.500 €. Vemos en la imagen a continuación, las fórmulas que se usaron:
Actividades Excel Profesor: Jacinto García Torres
Bien, suponiendo que estos precios son los actuales, los queremos comparar con otros posibles precios.
Seguiremos los pasos siguientes:
1. Seleccionamos la opción Análisis de hipótesis/Administrador de escenarios dentro en la pestaña Datos.
2. En el cuadro que aparecerá pulsamos el botón Agregar. 3. A continuación, damos un nombre a este escenario, el que queramos, podemos
llamarle Precio medio, para acordarnos. 4. En celdas cambiantes daremos las celdas que contendrán los valores, en este caso,
de los precios de las distintas entradas. Sería el rango C3:C5. 5. Damos a aceptar 6. Ahora nos pide los valores para las celdas anteriores, como ya los teníamos puestos
en la hoja de cálculo, aparecerán por defecto. Por lo tanto, solo será necesario dar al botón aceptar.
7. Vuelve a aparecer la pantalla del punto número 2, pero esta vez observamos que ya tiene un escenario llamado “precio medio” (el que acabamos de añadir).
8. Ahora repetiremos los pasos, desde el número 2, por cada uno de los escenarios posibles. Pondremos un escenario que llamaremos precio barato y con los precios de las entradas A, B y C a 90, 60 y 35 euros respectivamente, Y otro escenario al que llamamos precio caro en el que las entradas valgan 120, 90 y 60. Por cada uno de ellos, repetimos los pasos, los rangos de las celdas cambiantes son los mismos, lo único que cambiaremos será el valor que les asignamos y que se nos pedirá al final, serán los que acabamos de comentar.
Una vez realizado todos los pasos, en el administrador de escenarios tendremos los siguientes:
Si hacemos doble clic sobre alguno de los escenarios creados, veremos que los valores en la hoja de cálculo cambian y se calculan las fórmulas conforme a los valores del escenario elegido.
Actividades Excel Profesor: Jacinto García Torres
Seguidamente a las celdas C3, C4, C5 y D12 que ahora mismo aparecen con sus coordenadas, les asignamos previamente un nombre de rango. Las llamaremos: Precio_A, Precio_B, Precio_C y Ganancias, respectivamente. Una vez nombrados los rangos, debemos generar el resumen dándole al botón resumen y seleccionando la celda D12 como celda de resultados.
Pulsamos el botón llamado Resumen y veremos algo parecido a esto:
Actividades Excel Profesor: Jacinto García Torres
EJERCICIO 59: ESCENARIOS
Crea una tabla similar a la que figura más abajo.
Las únicas celdas de la tabla de abajo que contienen valores (no fórmulas) son las de
ingresos y gastos del año 1.
Imaginemos que el director financiero o el gerente de la empresa necesita valorar
distintas situaciones que se pudieran dar debido a la coyuntura económica y/o a la
propia empresa, variando los valores de algunos parámetros.
Supongamos que las tablas que presentamos anteriormente conforman el escenario
actual y el gerente necesita estudiar otros posibles escenarios (uno optimista y
otro pesimista) para poder prever una toma de decisiones en todos los casos:
Hagamos clic en la "Ficha Datos", después en el botón "Análisis de hipótesis" y después
en "Administrador de escenarios...".
Obtendremos el cuadro de diálogo donde tendremos que agregar uno a uno, los
"escenarios" que queremos estudiar.
Actividades Excel Profesor: Jacinto García Torres
En el "Nombre de escenario" pondremos optimista.
En "Celdas cambiantes", una vez borramos la referencia que aparece por defecto,
presionamos CTRL y con el ratón seleccionamos el rango de celdas F3:F5 y, sin haber
dejado de hacer presión sobre CTRL, seleccionamos el rango de celdas B8:B9. El
resultado que obtenemos son 2 rangos separados por punto y coma (Excel pone
automáticamente el signo punto y coma y los signos de $).
3) Aceptamos y obtenemos el siguiente cuadro:
Introducimos los datos para las celdas cambiantes correspondientes al escenario
optimista. Y aceptamos.
Repetimos los pasos, pero en el "Nombre de escenario”, ponemos "Escenario
pesimista".
Ya tenemos todos los escenarios configurados.
Ahora seleccionamos resumen y introducimos como celdas de resultados las
siguientes:
Actividades Excel Profesor: Jacinto García Torres
Finalmente pulsamos aceptar y obtendremos el siguiente resumen:
Actividades Excel Profesor: Jacinto García Torres
EJERCICIO 60: ESCENARIOS
Copia la siguiente tabla:
Queremos hacer un seguimiento de las ventas de un determinado producto a lo largo
del primer semestre, comparando el importe de las ventas de mes en mes.
En este caso, existe una serie de factores que no varían:
El nombre del artículo
El precio unitario del artículo
El tipo de IVA aplicable
En cambio, existen tres factores que sí varían:
El mes analizado
La cantidad de unidades vendidas del artículo.
El descuento. La pregunta aquí es: ¿cuál será el importe total de las ventas si cambia la cantidad
vendida y el descuento?
Actividades Excel Profesor: Jacinto García Torres
Celdas cambiantes:
El resumen resultante será el siguiente:
Actividades Excel Profesor: Jacinto García Torres
EJERCICIO 61: ESCENARIOS
Para este ejemplo utilizaremos el caso de un préstamo personal en donde nos interesa conocer la cantidad que debo pagar dependiendo el plazo elegido. Para ello utilizo la función PAGO y en las celdas superiores he colocado los argumentos de dicha función de la siguiente manera:
Ahora me interesa saber cómo cambia la mensualidad si cambio el plazo de pago. Vamos a crear cuatro posibles escenarios: 18 meses, 24 meses, 36 meses y 48 meses.
El resumen resultante será el siguiente:
Actividades Excel Profesor: Jacinto García Torres
EJERCICIO 62: ESCENARIOS
Realice las siguientes tablas y asígnale nombre de rango a las celdas C2:C7. (Los nombres de las celdas B2:B7):
Estamos planeando nuestras vacaciones y tenemos un presupuesto de 10.000 €.
La agencia Viajes Veloz nos ofrece cuatro posibles alternativas: La Habana, Cancún, Acapulco y Rivera Maya.
Vamos a crear cuatro posibles escenarios con las siguientes celdas cambiantes (Presupuesto, traslado, hospedaje y alimentos):
Ahora, nos vamos a la pestaña DATOS, y seleccionamos la opción ADMINISTRACION DE ESCENARIOS). Damos un clic en AGREGAR.
Aparece otro cuadro de dialogo que nos pregunta cómo se llamara este escenario. Y las celdas cambiantes. Tecleamos DESTINO1; Celdas cambiantes: C2:C5; Aceptar.
Actividades Excel Profesor: Jacinto García Torres
Después, aparece una caja para indicar los valores de las celdas cambiantes, indicamos las siguientes:
Y damos ACEPTAR. Nuestro primer escenario ha sido creado.
Una vez terminado, repetimos los pasos anteriores con los datos del viaje a Acapulco, Riviera Maya y La Habana, poniéndole los nombres de DESTINO 2, DESTINO 3 y DESTINO 4.
Por último, generaremos un informe resumen para poder comparar los escenarios posibles.
Actividades Excel Profesor: Jacinto García Torres
EJERCICIO 63: TABLA DE DATOS
Las tablas de datos forman parte de las herramientas de Análisis de hipótesis que nos permiten cambiar el valor de algunas celdas para ver cómo es afectado el resultado original. Una tabla de datos analiza un conjunto de valores y determina posibles resultados.
Tabla de datos de una variable
El ejemplo más sencillo de una tabla de datos es aquél que utiliza una variable para realizar los cálculos. Supongamos el siguiente escenario:
En este ejemplo estoy realizando una proyección de ventas para el año 2020 basándome en las ventas del año 2019 y esperando una tasa de crecimiento del 2,5%. Lo que deseo hacer es saber cuál sería la proyección de ventas para el 2020 si la tasa de crecimiento fuera diferente. Para hacer este análisis colocaré las tasas de crecimiento que deseo utilizar de la siguiente manera:
Actividades Excel Profesor: Jacinto García Torres
Para crear la tabla de datos debo seleccionar el rango de celdas tal como se muestra en la imagen anterior y entonces ir a la ficha Datos, y dentro del grupo Herramientas de datos pulsar el botón Análisis de hipótesis para posteriormente seleccionar la opción Tabla de datos.
Se mostrará el cuadro de diálogo Tabla de datos y en la caja de texto Celda de entrada (columna) se debe seleccionar la celda B2 que es la celda que contiene el porcentaje de crecimiento.
Al hacer clic en el botón Aceptar se llenarán las celdas contiguas a las tasas de crecimiento con el valor de la proyección de ventas correspondiente a cada una de las tasas.
Excel ha creado la tabla de datos en el rango B5:B13 y de esta manera puedo analizar las diferentes proyecciones de ventas para una tasa de crecimiento diferente. Una vez que he terminado de analizar la información, si intento eliminar alguna de las celdas pertenecientes al rango de la tabla de datos, Excel desplegará un mensaje advirtiendo que no se puede cambiar parte de una tabla de datos. Si deseas eliminar la tabla de datos deberás primero seleccionar el rango completo antes de oprimir la tecla suprimir.
Actividades Excel Profesor: Jacinto García Torres
EJERCICIO 64: TABLA DE DATOS
El escenario es el siguiente. Supongamos que quiero solicitar un préstamo personal a un banco, estoy haciendo una investigación con varios bancos y cada uno de ellos me ha dado una tasa de interés diferente, así como diversos plazos de pago. El primero de los casos que investigué es el siguiente:
Tabla de datos de dos variables
En lugar de hacer el cálculo para cada opción de tasa de interés y plazo, acomodaré los datos adecuadamente para formar una tabla de datos. En las filas especificaré las diferentes tasas de interés y en las columnas los posibles plazos.
Para crear la tabla de datos de dos variables seleccionaré el rango B4:G11 y debo ir a la ficha Datos y pulsar el botón Análisis Y si para seleccionar la opción Tabla de datos. Dentro del cuadro de diálogo deberé elegir cada una de las celdas de entrada para la tabla de datos de la siguiente manera:
Al hacer clic en el botón Aceptar se generará la tabla de datos correspondiente:
Actividades Excel Profesor: Jacinto García Torres
En esta tabla de datos podemos observar lo que esperábamos, entre mayor es el plazo los pagos mensuales se reducen y entre mayor es la tasa de interés el pago mensual se incrementa. Con esta información puedo analizar y elegir adecuadamente la opción que más se acomoda a mis posibilidades de pago mensual.
Las tablas de datos son una herramienta que nos ayuda en el análisis de nuestra información, la única desventaja es que solamente se puede hacer el análisis de dos variables como máximo. En caso de que te encuentres en una situación que requiera de un análisis de tres o más variables recomiendo utilizar los Escenarios o también utilizar Excel Solver.
Actividades Excel Profesor: Jacinto García Torres
EJERCICIO 65: BUSCAR OBJETIVOS
Buscar objetivo es una técnica utilizada para encontrar fácilmente el número que cumple las condiciones necesarias para alcanzar un objetivo.
Esta herramienta te ayudará a hacer muchas pruebas de valores en una fórmula hasta encontrar el valor exacto que cumpla con las condiciones establecidas. Es decir, si sabemos el resultado de una fórmula, pero no estamos seguros del valor de uno de sus argumentos, entonces Buscar objetivo nos ayudará a probar diversos escenarios hasta encontrar el valor exacto que necesitamos.
Supongamos que tenemos el siguiente problema.
Tengo un número que multiplicado por otro número X me deberá dar el resultado mostrado. Empezaré por definir una fórmula importante. En la celda C3 colocaré la fórmula que se deberá cumplir para obtener el resultado deseado, es decir, =B1*B2 deberá dar como resultado 439.482.
Ahora deberás hacer clic en la ficha Datos y seleccionar Análisis hipótesis donde se desplegará un menú de opciones y deberás elegir Buscar objetivo.
El resultado final será el siguiente:
Actividades Excel Profesor: Jacinto García Torres
EJERCICIO 66: BUSCAR OBJETIVOS
Copie la tabla siguiente:
Se pide:
a) Calcular que cantidad se necesitaría para obtener unos beneficios de 55.000 €. b) Calcular que precio de venta, tendría que haber para conseguir un beneficio de
120.000 €.
c) Calcular a qué precio se tendría que comprar, para obtener unos beneficios de 90.000 €.
Pasos a seguir: 1. Active opción Buscar Objetivo.
2. En el cuadro de diálogo que aparece, en caja de texto Definir la celda: ponga la
casilla en la que quiera un determinado resultado, en este caso la $B$6, Beneficios. En este apartado, sólo se pueden utilizar casillas que contengan fórmula.
3. En la siguiente caja de texto, Con el valor: ponga el valor que quiera obtener (55.000).
4. Cambiar celda: esta caja de texto sirve para definir que casilla se puede ajustar para conseguir el resultado en la casilla indicada en Definir la celda.
5. Recuerde que aquí sólo puede indicar casillas con valores numéricos y que intervengan directa o indirectamente en el resultado final. Ponga la casilla donde hay la cantidad D2.
6. Pulse sobre Aceptar.
7. Repita los pasos para el caso b y c.
Actividades Excel Profesor: Jacinto García Torres
INTRODUCCIÓN A SOLVER En esta introducción a Solver, veremos con la ayuda de un sencillo ejemplo, de qué manera esta potente tecnología incluida en Excel nos puede ayudar a resolver algunos problemas con los que nos podemos encontrar. De nuevo aquí lo importante será entender bien la cuestión que debe resolverse y plantearla correctamente en un modelo. Excel se encargará de hallar la respuesta. Ya sabemos que algunas preguntas admiten varias respuestas por lo que será necesario comprobar la que Solver ha calculado antes de dar como resuelta la cuestión. Utilizando la funcionalidad Buscar objetivo, Excel puede calcular el valor necesario de una celda cambiante para obtener el valor de la celda de resultado que queremos. Extendiendo este concepto, Solver permite:
Especificar varias celdas que se puede modificar.
Especificar limitaciones sobre los valores que las celdas cambiantes pueden tener.
Generar una solución que maximice o minimice una celda de hoja particular.
Generar múltiples soluciones a un problema.
Así, los problemas apropiados para Solver son:
Por lo general, se quiere maximizar o minimizar la celda objetivo o igualarla a un valor.
La celda objetivo depende de un grupo de celdas cambiantes que Solver puede ajustar.
La solución tiene que cumplir ciertas limitaciones.
Actividades Excel Profesor: Jacinto García Torres
EJERCICIO 67: SOLVER Se quiere calcular cuál debería ser la producción óptima de tres productos que maximice el beneficio. Nos dan los siguientes datos: (Tabla de la práctica 57).
La compañía desea saber cuántas unidades a de vender de cada producto para alcanzar el máximo beneficio aplicando las siguientes restricciones:
El total de horas de producción ha de ser de 200.000 horas.
El número máximo de unidades vendidas será de 40.000.
Los beneficios netos de los productos 1, 2 y 3 han de ser mayores o iguales 0. Si ya tenemos instalado en nuestro Excel Solver lo encontramos en la pestaña Datos, grupo Análisis. Si no lo activaremos en: Archivo/Opciones/Complementos/Administrar/Ir) Hacemos clic en él y tenemos la siguiente pantalla:
Actividades Excel Profesor: Jacinto García Torres
En el cuadro Establecer objetivo, haremos referencia a la celda de nuestro modelo en la que tenemos el beneficio en euros que queremos obtener (C19). En la zona Para, seleccionaremos Max puesto que lo que queremos es maximizar el valor de la celda objetivo. En Cambiando las celdas de variables, seleccionamos las celdas en las que tenemos las unidades que queremos fabricar de cada producto (B8 a D8). Restricciones:
1. Total horas de producción, E14 = 200.000. 2. Total Uds. vendidas, E9 <= 40.000. 3. Beneficio producto 1 >= 0. 4. Beneficio producto 1 >= 0. 5. Beneficio producto 1 >= 0.
Para introducir nuestras restricciones bastará con seleccionar los botones Añadir.
Actividades Excel Profesor: Jacinto García Torres
Al presionar el botón Resolver, Solver nos presenta una ventana…
Pulsamos aceptar y mostrara la solución dada por Sover. También podremos pulsar restaurar valores originales o guardar escenario.
Actividades Excel Profesor: Jacinto García Torres
EJERCICIO 68: SOLVER Pizzería JAEN ofrece dos tipos de pizza tradicionales, Pepperoni (30€) y Vegetariana (35€) además de la pizza especial Suprema (45€). Se pretenden maximizar el importe total de las ventas, pero teniendo en cuenta las siguientes condiciones: Dada nuestra capacidad de producción solamente podemos elaborar 150 pizzas al día. Otra condición es que no podemos exceder de 90 pizzas tradicionales (Pepperoni y Vegetariana) y además, al no haber muchos vegetarianos en el área, estimamos vender un máximo de 25 pizzas vegetarianas al día. Otra condición a considerar es que solamente podemos comprar los ingredientes necesarios para producir 60 pizzas Suprema por día.
Con esta información elaboraré la siguiente hoja de Excel:
Utilice el solver para obtener el siguiente resultado final:
Actividades Excel Profesor: Jacinto García Torres
EJERCICIO 69: SOLVER Realiza una tabla similar a la siguiente:
Los cálculos anteriores muestran que para pagar el Ordenador con lo que se ahorra actualmente, se tardarán 500 semanas. Utilice el solver para obtener un resultado final entre 40 y 52 semanas. Las casillas variables serán las correspondientes al número de Refrescos, No Refrescos y Pizzas. Las restricciones son:
‐ Refrescos >=3 ‐ No Refrescos >=2 ‐ Pizzas, hamburguesas, … >=2 ‐ Total Semanas >=45 y <=52
Actividades Excel Profesor: Jacinto García Torres
EJERCICIO 70: SOLVER Realiza una tabla similar a la siguiente:
Utilice el solver, para encontrar el beneficio máximo estableciendo las restricciones siguientes:
Los gastos de alimentación serán superiores a 200.
Los gastos de luz y teléfono serán superiores a 180.
El plan de pensiones será una cantidad entre 300 y 350.
La cantidad destinada mensualmente a vacaciones tiene que ser más grande o igual a 250.
El porcentaje dedicado al plan de pensiones tiene que ser superior o igual al 10% de ingresos.
Los beneficios serán menores o iguales a 2800. Se pueden cambiar los valores de las casillas Alimentación; luz y teléfono; % Plan de pensiones; % Vacaciones.
Actividades Excel Profesor: Jacinto García Torres
VALIDACIÓN DE DATOS EN EXCEL La validación de datos en Excel es una herramienta que no puede pasar desapercibida por los analistas de datos ya que nos ayudará a evitar la introducción de datos incorrectos en la hoja de cálculo de manera que podamos mantener la integridad de la información en nuestra base de datos. De manera predeterminada, las celdas de nuestra hoja están listas para recibir cualquier tipo de dato, ya sea un texto, un número, una fecha o una hora. Sin embargo, los cálculos de nuestras fórmulas dependerán de los datos contenidos en las celdas por lo que es importante asegurarnos que el usuario ingrese el tipo de dato correcto. Por ejemplo, en la siguiente imagen puedes observar que la celda C5 muestra un error en el cálculo de la edad ya que el dato de la celda B5 no corresponde a una fecha válida.
Este tipo de error puede ser prevenido si utilizamos la validación de datos en Excel al indicar que la celda B5 solo aceptará fechas válidas. Una vez creada la validación de datos, al momento de intentar ingresar una cadena de texto, obtendremos un mensaje de advertencia como el siguiente:
Actividades Excel Profesor: Jacinto García Torres
EJERCICIO 71: VALIDACION DE DATOS Confecciona una hoja como la siguiente:
Deseamos que las celdas de la columna Talla en la tabla de Existencias de almacén se rellenen con una de las opciones de la lista Talla del área Listas, seleccionamos todas las celdas de la columna en la tabla y aplicamos Validación de datos.
Actividades Excel Profesor: Jacinto García Torres
Ahora aplicaremos Validación de datos a las celdas de la columna Color y Materias Primas de la tabla Existencias de almacén. Pero en este caso, queremos que, si la celda correspondiente en la columna Talla está vacía, no exista posibilidad de seleccionar un valor de la Lista Color del área Listas, sino que quede forzosamente en blanco.
Para conseguirlo utilizaremos una función SI. Seleccionamos sólo la celda D3 y aplicamos Validación de datos. En la propiedad Origen del diálogo insertaremos la siguiente fórmula: =SI(C3="";C3;$H$3:$H$9)
La fórmula obliga a Excel a que, si la celda C3 está vacía, la lista presente una celda vacía (puede ser cualquiera, pero mejor si es la propia C3) y si no, la lista presentará el rango de celdas I3:I9. Aplicamos el mismo principio a la celda E3.
Actividades Excel Profesor: Jacinto García Torres
Ahora sólo nos queda seleccionar el rango de celdas C3:E3 y extender la selección (o copiar‐pegar, o copiar‐pegar formato) al rango de celdas C34:E22. para que a todas las celdas de las dos columnas se les aplique la Validación de datos definida.
Actividades Excel Profesor: Jacinto García Torres
EJERCICIO 72: VALIDACION DE DATOS Comenzamos creando una hoja semejante a siguiente, que contendrá las fórmulas de validación a otras 2 hojas de datos:
Actividades Excel Profesor: Jacinto García Torres
Ahora necesitaremos crear otra hoja, la cual llamaremos DATOS y contendrá lo siguiente:
Y una tercera hoja llamada CLIENTES que contendrá:
El problema de la práctica es que, en la primera hoja de FACTURA, vinculemos y validemos los datos que se extraigan de las otras dos hojas, de tal forma que, al introducir el código del cliente, automáticamente nos de los demás datos del cliente. La celda fecha contendrá una función que nos dé la fecha actual siempre. Validemos que no pongan cantidades negativas y que, al introducir el artículo, nos dé el precio, además de calcular el total, base imponible, IVA y total factura..
Top Related