Ejercicios de Funciones en EXCEL

EJERCICIOS DE EXCEL CON FUNCIONES

• Categoria de las funciones: Fecha y Hora ( Ahora, Año, Dia, Diasem, Fecha, Hora, Hoy, Mes, Minuto, Segundo )

• Categoria de las funciones: Matemáticas y Trigonométricas ( Abs, Aleatorio, Aleatorio.Entre, Cociente, Combinat, Entero, Fact, Grados, Log, MCD, MCM, Numero.Romano, Pi, Potencia, Producto, Radianes, Raiz, Redondear, Residuo, Signo, Suma, Sumar.Si, Truncar )

• Categoria de las funciones: Estadísticas ( Contar, Contar.si, Contara, Max, Min, Mediana, Moda, Permutaciones, Promedio )

1.- En la siguiente imagen aparecen los datos reales de las Elecciones Generales del año 2015 en España, para el Congreso de los Diputados ( http://resultadosgenerales2015.interior.es/congreso/#/ES201512-CON-ES/ES ). En la columna D calcula el número de votos por Diputado que ha necesitado cada partido obtener un escaño en el Congreso. En la columna E añade una celda en la que aparezca el partido al que le cuesta más obtener un Diputado y el partido al que le es más fácil obtenerlos.

2.- En la siguiente imagen aparecen los datos reales de las Elecciones Generales del 2015 para el Congreso de los Diputados en la provincia de Toledo ( http://resultadosgenerales2015.interior.es/congreso/#/ES201512-CON-ES/ES/CA08/45 ). En la columna D calcula cuántos votos ha necesitado cada partido para obtener un Diputado en ésta provincia.

3.- Los anteriores ejercicios muestran imágenes sobre resultados reales de las elecciones 2015 en España. Para calcular esos datos se usa el Método D'Hont. Busca en internet el funcionamiento de éste Método, y crea una hoja de cálculo en la que añadas tú los partidos en la columna A, en la columna B añadas el número de votos obtenidos por cada partido, y en la Columna C debe calcularse automáticamente el número de Diputados obtenidos por cada partido.

4.- Crea una hoja de cálculo como la siguiente en la que se muestre la temperatura media anual de los paises de Europa. Calcula las Medias por año, y las Medias de cada Pais, el máximo, mínimo y la Moda de las temperaturas.

• Categoria de las funciones: Busqueda y Referencia ( Buscar, BuscarV, BuscarH, Coincidir, Columna, Elegir, Fila )

1.- En la hoja de calculo del ejercicio1, poner en la celda E1 la palabra "NOMBRE" y en la celda E2 escribirás un nombre cualquiera de la lista. Tienes que hacer que en la celda E3 aparezca la posición dentro de la lista de nombres en la que se encuentra ese nombre que has escrito en la celda E2

2.- En una hoja de calculo crea una tabla de precios de un bar como la siguiente:

y crea otra tabla en la misma hoja de calculo como la siguiente:

Esta segunda tabla servirá al camarero para llevar la cuenta de lo que va pidiendo un cliente, o los clientes de una misma mesa del bar. En ella, el camarero escribirá el producto que ha pedido el cliente, también indicará la cantidad, y automáticamente se debe calcular en la columna G el precio de esa cantidad de productos.

Al final, en la celda TOTAL, se debe calcular el total a pagar por el cliente.

3.- Hay que hacer una hoja de cálculo parecida a la anterior, pero en este caso añadiremos una nueva columna a la primera tabla. Esa columna se llamará CODIGO, e indicará el codigo de cada uno de los productos que venderá el bar:

 Añadir también una nueva columna a la segunda tabla.

En este caso el camarero escribirá el CODIGO de cada PRODUCTO pedido por el cliente, y la Cantidad, y la hoja de cálculo debe calcular automáticamente el precio de cada uno de los productos y el total de lo que tiene que pagar el cliente.

• Categoria de las funciones: Texto ( Concatenar, Derecha, Encontrar, Espacios, Extrae, Igual, Izquierda, Largo, Mayusc, Minusc )

1.- En una hoja de calculo, poner en la columna A diez nombres de personas, en la columna B el primer apellido de cada persona, y en la columna C el segundo apellido de esa persona. Tienes que poner en la columna D automáticamente el nombre y los apellidos de cada persona, ordenados de la siguiente forma: "Apellido1 Apellido2 , Nombre".

2.- En una hoja de calculo, poner en la columna A diez resultados de partidos de futbol con el siguiente formato: "equipoA 2 - equipoB 3". Tienes que poner en la columna B usando funciones el nombre del primer equipo y sus goles, en la columna C el nombre del segundo equipo y sus goles correspondientes

---

FUNCIONES DE EXCEL QUE TENEMOS QUE APRENDERNOS:

MATEMATICAS:
ABS
ALEATORIO
ALEATORIO.ENTRE
COCIENTE
ENTERO
FACT
GRADOS
LOG
LOG10
M.C.D
M.C.M
NUMERO.ROMANO
PI
POTENCIA
PRODUCTO
RADIANES
RAIZ O RCUAD
REDONDEAR
RESIDUO O RESTO
SIGNO
SUMA
SUMAR.SI
TRUNCAR

FECHA Y HORA:
AHORA
AÑO
DIA
MES
DIASEM
FECHA
HORA
MINUTO
HOY
SEGUNDO

ESTADISTICAS:
CONTAR
CONTAR.BLANCO
CONTAR.SI
CONTARA
MAX
MEDIANA
MIN
MODA.UNO
MODA.VARIOS
PROMEDIO

TEXTO:
CARACTER o CAR
CODIGO
CONCATENAR
DERECHA
IZQUIERDA
ENCONTRAR
ESPACIOS o RECORTAR
EXTRAE o MED
HALLAR, IGUAL
LARGO
MAYUSC
MINUSC
TEXTO
VALOR

LOGICAS:
FALSO
VERDADERO
NO
O
Y
SI
SI.ERROR

BUSQUEDA Y REFERENCIA:
BUSCARH o CONSULTAH
BUSCARV o CONSULTAV
COINCIDIR
ELEGIR

EJERCICIOS GENERALES SOBRE LAS FUNCIONES QUE TENEMOS QUE APRENDERNOS:

FUNCIONES MATEMÁTICAS:

1.- En una hoja de Excel, desde la celda A1 hasta la A10, escribe varios números, unos positivos y otros negativos, y en las celdas B1 hasta B10 deberá aparecer el valor Absoluto de los números escritos en cada celda de la columna A. Ponle bordes a las celdas que tengan datos y colores de fondo para que quede bonita la hoja.

2.- En una hoja de Excel, desde la celda A1 hasta la A20 escribe números reales aleatorios ( entre 0 y 1 ). Éstos números los tiene que generar automáticamente Excel. Ponle bordes a las celdas que tengan datos y colores de fondo para que quede bonita la hoja.

3.- En la hoja anterior, en las celdas B1 hasta B20 se deberán generar aleatoriamente números reales entre 0 y 10. Los tiene que generar excel automáticamente y con DECIMALES. Ponle bordes a las celdas que tengan datos y colores de fondo para que quede bonita la hoja.

4.- En la hoja anterior, en las celdas C1 hasta C20 se deberán generar aleatoriamente números reales entre el 10 y el 20. Los tiene que generar excel automáticamente y con DECIMALES. Ponle bordes a las celdas que tengan datos y colores de fondo para que quede bonita la hoja.

5.- En la hoja anterior, en las celdas D1 hasta D20 se deberán generar aleatoriamente números ENTEROS, entre el 50 y el 100. Los tiene que generar excel automáticamente (sin decimales, claro). Ponle bordes a las celdas que tengan datos y colores de fondo para que quede bonita la hoja.

6.- Utiliza la función COCIENTE para realizar una división ENTERA con números fijos y con números guardados en celdas de una hoja de cálculo. Ten en cuenta que ésta Función no devuelve decimales en el Cociente, solo números Enteros.

7.- En la hoja de cálculo del ejercicio 5, en las celdas E1 hasta E20 hacer que Excel estraiga la parte entera de los números que hay en las celdas de la columna D.

8.- El factorial de un número entero es el resultado de multiplicar ése número por los más pequeños que él hasta llegar al 1. Por ejemplo: el factorial de 5 es :    5! = 5*4*3*2*1=120 .  Usa la función FACT para calcular los factoriales de los números: 1, 2, 3, 4, 5, 6 y 7.

9.- Utiliza la función GRADOS para convertir los siguientes Radianes en Grados sexagesimales:  3,1416  ,  1,57   y   6,28

10.- Calcula el logaritmo en base 10 de los números: 100  ,  1.000   y   1.000.000

11.- En una nueva hoja de cálculo, introduce en las celdas A1 hasta A5 números enteros del 1 al 100. En la celda B1 escribe MCD y en B2 calculas el Máximo Común Divisor de esos números. Ponle bordes a las celdas que tengan datos y colores de fondo para que quede bonita la hoja.

12.- En la hoja del ejercicio anterior, introduce en las celdas C1 hasta C5 números entre el 1 y el 10.

En la celda D1 escribe MCM y en D2 calculas el Mínimo Común Múltiplo de esos números. Ponle bordes a las celdas que tengan datos y colores de fondo para que quede bonita la hoja.

13.- En la hoja anterior, en las celdas F1 hasta F4000 escribe en orden los números desde el 1 al 4000. Y en la columna G, desde G1 hasta G4000 traduce esos números a números romanos automáticamente. Ponle bordes a las celdas que tengan datos y colores de fondo para que quede bonita la hoja.

14.- En una hoja nueva, en A1 escribe la palabra "RADIO", en A2 escribes el radio de un circulo (un número), en A3 escribes "Longitud Circunferencia", en A4 se debe calcular automáticamente la longitud de dicha circunferencia (el radio está en A2). En A5 escribes "Área del Circulo" y en A6 deberá aparecer el área que tiene el circulo cuyo radio es el que escribiste en A2. Comprueba después que si cambias el valor del radio, los valores de la Circunferencia y el Area cambiarán también. Ponle bordes a las celdas que tengan datos y colores de fondo para que quede bonita la hoja.

15.- Con la función POTENCIA se puede realizar potencias evidentemente. En la hoja anterior, en las celdas C1 hasta C5 usa ésta función para escribir varias potencias, por ejemplo 5 al cubo, 8 al cuadrado, etc... . Ponle bordes a las celdas que tengan datos y colores de fondo para que quede bonita la hoja.

16.- La función POTENCIA también me sirve para calcular raices (cuadradas, cúbicas, cuartas, quintas, etc....). Para calcular una raíz con ésta funcion, el exponente deberá ser 1 dividido entre el tipo de raíz que queremos calcular.  Por ejemplo:  para hacer una raíz cúbica, el exponente deberá ser 1/3;  para la raíz cuarta el exponente será  1/4 , y así sucesivamente;   En la hoja anterior, en la celda E1 escribe "Número", en F1 vas a introducir un número cualquiera. En E2 escribes "Raiz 2", y en las celdas de abajo vas poniendo "Raiz 3", "Raiz 4" , etc... hasta llegar a "Raiz 20",  y en las celdas correspondientes de la columna F, usa la función POTENCIA para calcular las raices cuadradas, cúbicas, cuartas, etc... del número que escribiste en la celda F1;  Comprueba después que si cambias el valor de F1 todas las raíces cambiarán. Observa también que las raices se irán acercando poco a poco al número 1, independientemente de si el número introducido es mayor o menor que 1.  Ponle bordes a las celdas que tengan datos y colores de fondo para que quede bonita la hoja.

17.- Utiliza la función producto para realizar varias multiplicaciones, ya sea con números fijos o con números guardados en celdas.

18.- Utiliza la funcion RADIANES para convertir grados Sexagesimales en radianes. Usa los siguientes valores:  90,  180,    270   y  360

19.- Utiliza la función RAIZ o RCUAD (dependiendo de la versión de tu OFFICE) para calcular raices cuadradas de distintos números.

20.- Cálculo automático de la HIPOTENUSA de un triángulo rectángulo: Los triangulos rectángulos tienen un angulo recto. Los lados que definen el ángulo recto se llaman CATETOS, y el lado más largo del triángulo se llama HIPOTENUSA. Hace miles de años, un filósofo griego llamado Pitágoras, descubrió que se puede calcular la longitud de una hipotenusa conociendo la longitud de los catetos. La formula es la siguiente:

En una hoja nueva de Excel, en A1 escribe "Cateto 1", en A2 vas a indicar la longitud de dicho cateto. En B1 escribe "Cateto 2" y en la celda de abajo indicas la longitud de dicho cateto. Y por último en C1 escribes "Hipotenusa" y en C2 deberá aparecer automáticamente la longitud de la hipotenusa. Para ello utiliza las funciones Excel y demás operaciones mátemáticas que necesites.  Ponle bordes a las celdas que tengan datos y colores de fondo para que quede bonita la hoja.

21.- Las ecuaciones de segundo grado tienen la siguiente forma:

donde a, b y c son números cualquiera. Estas ecuaciones tienen 2 soluciones: X1 y X2. Y para obtenerlas se utiliza la siguiente fórmula:

En una hoja nueva de Excel, en A1 escribes "a", en B1 escribes "b", y en C1 escribes "c". Debajo de cada una de éstas celdas indicarás los valores de a, b y c respectivamente; Y por último en D1 escribes "X1" y en E1 escribes "X2", y en sus respectivas celdas de abajo tienes que poner la formula matemática para que se calculen automáticamente las 2 soluciones: X1 y X2.  IMPORTANTE: ten en cuenta que NO TODAS las combinaciones de números a, b y c generan soluciones . Ponle bordes a las celdas que tengan datos y colores de fondo para que quede bonita la hoja.

22.- En una nueva hoja, introduce automáticamente números REALES aleatorios entre 100 y 500 desde las celdas A1 hasta la A20. A continuación REDONDEA dichos números para que tengan solamente 3 decimales cada uno, redondeando el ultimo de sus decimales. Lo puedes hacer todo en las mismas celdas A1:A20 , o usar otras celdas vacías que estimes oportunas. Ponle bordes a las celdas que tengan datos y colores de fondo para que quede bonita la hoja. Después en la columna C mete los mismos números que en la A, y a continuación TRUNCA dichos números, o bien en la misma celda o en otras celdas libres que tenga la hoja de cálculo.

23.- La operación matemática RESIDUO (también llamada Resto de la División entera, o Módulo de la División Entera), devuelve el resto de una división entera, es decir, una división cuyo Cociente no lleva decimales. Esta operación se puede utilizar por ejemplo para comprobar si un número es divisible entre otro, o dicho número es múltiplo del otro, que viene a se lo mismo. Para ello solamente hace falta dividir un número entre otro y si el RESIDUO o RESTO es 0, entonces esos números son divisibles entre sí. En una hoja nueva, introduce números ENTEROS aleatorios, desde A2 hasta a20; En A1 escribes "NUMERADOR"; en B1 escribes "DIVISOR", y desde B2 hasta B20 introduces números enteros en dichas celdas. Procura que los números de la columna B sean menores que los de la columna A; Y por último en C1 escribes "RESIDUO", y desde C2 hasta C20 deberá aparecer el RESIDUO o RESTO de la división entera entre los números de las columnas A y B. En aquellos casos en los que el RESIDUO sea 0, ya sabrás que los números correspondientes serán Divisibles entre sí, y aquellos cuyo RESIDUO sea diferente de 0 NO SERÁN Divisibles entre sí. Ponle bordes a las celdas que tengan datos y colores de fondo para que quede bonita la hoja.

24.- En una hoja nueva, en A1 escribes "NÚMERO", y desde A2 hasta A20 escribes números ENTEROS; En B1 escribes "PAR O IMPAR", y desde B2 hasta B20 deberá aparecer un 0 si el número de la celda A es PAR y un 1 si el número de la celda A es IMPAR. Utiliza lo del ejercicio anterior para realizar éste. Ponle bordes a las celdas que tengan datos y colores de fondo para que quede bonita la hoja.

25.- En la misma hoja anterior, en C1 escribes "MULTIPLO DE 3", y desde C2 hasta C20 deberá aparecer automáticamente un 0 en aquellas celdas cuyo número de la columna A sea múltiplo de 3, y para aquellos números que no lo sean aparecerá otro valor diferente. Ponle bordes a las celdas que tengan datos y colores de fondo para que quede bonita la hoja.

26.- En la misma hoja anterior, haz lo mismo que el ejercicio anterior, pero en la Columna D, con los múltiplos de 4. Ponle bordes a las celdas que tengan datos y colores de fondo para que quede bonita la hoja.

27.- En la misma hoja anterior, haz lo mismo que en el ejercicio anterior, pero en la Columna E, con los múltiplos de 5. Ponle bordes a las celdas que tengan datos y colores de fondo para que quede bonita la hoja.

28.- La función SIGNO devuelve 1 si el número introducido entre paréntesis es POSITIVO; devuelve un 0 si el número introducido es un 0; y devuelve -1 si el número es negativo. En una hoja nueva de Excel mete números REALES positivos y negativos en el rango A1:A40, y haz que en el rango B1:B40 salga un 1 , 0 ó -1 dependiendo del número correspondiente de la columna A. (1=positivo ;  0 = Cero ;  -1=negativo). Ponle bordes a las celdas que tengan datos y colores de fondo para que quede bonita la hoja.

29.- En  la hoja anterior, utiliza la función SUMA en cualquier celda libre para sumar todos los datos desde A1 hasta A40.

30.- Crea una hoja como la siguiente:

En la columna C meteis los valores numéricos que querais (pueden ser aleatorios).

Utilizando la funcion SUMAR.SI debeis calcular automáticamente las celdas E2, F2, G2 y H2; También debeis calcular automáticamente desde E5 hasta H5. Todo ello usando la función SUMAR.SI

FUNCIONES DE FECHA Y HORA:

PARA TODOS LOS EJERCICIOS: Ponle bordes a las celdas que tengan datos y colores de fondo para que queden bonita las hojas.

1.- Utiliza la función AHORA en alguna celda vacía y comprueba el resultado.

2.- En una hoja nueva, en A1 escribe "FECHAS", y desde A2 hasta A20 escribe diferentes fechas con el formato DD/MM/AAAA , como por ejemplo "10/06/1978". A continuación en B1 escribe "Día", en C1 escribe "Mes" y en D1 escribe "Año". A continuación desde B2 hasta B20 deberá aparecer el día correspondiente de la fecha indicada en la columna A; también desde C2 hasta C20 deberá aparecer el mes correspondiente de la fecha indicada en la columna A; y lo mismo en las celdas D2:D20, con el año. Todo ello lo debe hacer EXCEL de forma automática.

3.- En la hoja anterior, en la celda E1 escribir "Dia de la Semana", y desde E2 hasta E20 calcular automáticamente el día de la semana que fue la fecha indicada en la columna A. Ten en cuenta que un 1 es lunes, 2 es martes,..., y 7 es domingo.

4.- En alguna celda vacía, utiliza la función FECHA con 3 valores numéricos para que genere automáticamente una fecha a partir de dichos valores introducidos por tí.

5.- En alguna celda vacía, utiliza la función HOY para que escriba automáticamente la fecha de hoy. Fijate que esta función es similar a la función AHORA, excepto que no muestra la hora actual, solo la fecha.

6.- En una hoja nueva, en la celda A1 escribe "HORAS", y desde A2 hasta A20 escribe distintas horas con el siguiente formato "HH:MM:SS". A continuación en B1 escribe "Hora", y en B2:B20 deberá aparecer automáticamente los 2 dígitos de la hora correspondiente de la columna A; En C1 escribe "Minutos" y en la celda D1 escribe "Segundos", y en los rangos C2:C20 y D2:D20 deberán aparecer respectivamente los Minutos y Segundos, escritos en la columna A.

FUNCIONES ESTADISTICAS:

PARA TODOS LOS EJERCICIOS: Ponle bordes a las celdas que tengan datos y colores de fondo para que queden bonitas la hojas.

1.- En una hoja nueva, en A1 escribe "Números", y en el rango A2:A20 escribe números al azar, dejando varias celdas blancas, sin nada; en B1 escribe "Celdas con Numeros", y en B2 deberá aparecer automáticamente cuántas celdas del rango A2:A20 contienen números

2.- En la misma hoja anterior, en C1 escribe "Celdas en Blanco", y en C2 deberá aparecer automáticamente cuántas celdas del rango A2:A20 están en blanco

3.- En este ejercicio aplicarás la mayoría de las funciones estadísticas que hemos estudiado y practicado en clase.

En una hoja nueva crea la misma hoja que la siguiente imagen:

Puedes empezar en la fila 1;

Las notas de la columna B pueden ser las que tu quieras. Las celdas que contienen esas notas, deberán ponerse automáticamente en rojo cuando el valor de la celda sea menor que 5 (suspenso).

- Debajo de la celda "PRESENTADOS" deberá aparecer automáticamente cuántos alumnos tienen nota, porque se han presentado al examen.

- Debajo de la celda "NO presentados" aparecerá automáticamente cuántos alumnos no se han presentado al examen.

- Debajo de la celda "APROBADOS" deberá aparecer cuántos alumnos han aprobado el examen

- Debajo de la celda "SUSPENSOS" aparecerá el número de alumnos que han suspendido

- Debajo de la celda "MAYOR NOTA" aparecerá automáticamente la mayor nota de todas

- Debajo de la celda "MENOR NOTA" aparecerá automáticamente la menor nota de todas

- Debajo de la celda "MEDIA" deberá aparecer automáticamente la nota media de todas las notas.

- Debajo de la celda "MODA" aparecerán las notas más repetidas. Ten en cuenta que puede haber varias notas que se repitan el mismo número de veces. Por eso hay varias celdas debajo de la celda "MODA"

- Debajo de la celda "MEDIANA" aparecerá la mediana de todas las notas.

FUNCIONES DE TEXTO:

PARA TODOS LOS EJERCICIOS: Ponle bordes a las celdas que tengan datos y colores de fondo para que queden bonitas la hojas.

1.- En una hoja nueva, desde A1 hasta A256 escribe los números desde el 0 hasta el 255. En la columna B, al lado de cada celda con los números, utiliza la Función CARACTER o CAR para representar el carácter correspondiente del código ASCII. Ten en cuenta que los 32 primeros caracteres son no imprimibles o de control.

2.- En la misma hoja anterior, en las celdas D1:D8 escribe varios caracteres, y en las celdas E1:E8 deberá aparecer el código o número correspondiente de esos caracteres en el código ASCII

3.- En una hoja nueva, en A1 escribe "NOMBRE", en B1 escribe "Apellido1", en C1 escribe "Apellido2". Después, desde A2 hasta A10 escribe nombres de personas en cada celda (nombres de una sola palabra), y en los rangos B2:B10 y C2:C10 escribe apellidos simples (de una sola palabra);  cuando todas estas celdas las tengas rellenas con esos datos, a continuación en la celda D1 escribe "Nombre y apellidos", y en el rango "D2:D10" tienes que unir automáticamente el Nombre y Apellidos de cada una de las filas (p.ej: en D2 deberá aparecer juntos el contenido de A2, B2 y C2. Usa la función CONCATENAR.

4.- En el ejercicio anterior, habrás visto que los datos que has unido te salen todos juntos. En la celda E1 escribe lo mismo que D1, y en E2:E10 intenta que aparezcan separados los nombres y apellidos entre sí. Tienes que añadir espacios en la función CONCATENAR. Recuerda que un espacio es un caracter, que se representa así:   " "

5.- En la hoja anterior, en F1 escribe "Apellidos y Nombre", y en F2:F10 deberán aparecer en las celdas correspondientes los datos de ésta manera:  "Apellido1 Apellido2, Nombre". Usa la función Concatenar y tiene que haber espacios entre las palabras concatenadas o unidas.

6.- En la hoja anterior, en G1 escribe "Derecha", y en G2:G10 tienes que sacar los 5 caracteres que están a la derecha de cada celda correspondiente de la columna F. Usa la función DERECHA.

7.- En la hoja anterior, en H1 escribe "Izquierda", y en H2:H10 tienes que sacar los 5 caracteres que están a la izquierda de cada celda correspondiente de la columna F. Usa la función IZQUIERDA.

8.- En la hoja anterior, en "i1" escribe "Encontrar la coma", y en i2:i10 tienes que indicar en la posición en la que se encuentra colocado el caracter coma "," en las celdas de la columna F. Usa la función ENCONTRAR o la función HALLAR que son equivalentes.

9.- En una celda varía, escribe una frase con varias palabras y mete 8 espacios entre cada palabra. Comprobarás que las palabras de esa frase salen muy separadas entre sí. Ahora en la celda de debajo utiliza la funcíón ESPACIOS o RECORTAR para que aparezca la misma frase de arriba pero con solamente 1 espacio entre palabra y palabra.

10.- En la misma hoja que el ejercicio 8, en J1 escribe "EXTRAE o MED", y en J2:J10 deben aparecer los caracteres del medio de las celdas F2:F10, desde el caracter 4 hasta el caracter 12. Usa la función EXTRAE o MED.

11.- En una celda vacía escribe una palabra. En la celda de abajo escribe otra palabra. Ahora junta en una celda las 2 celdas de su derecha. En esa celda usa la función IGUAL para comparar si las dos palabras que has escrito son iguales entre sí. Prueba después a cambiar esas palabras para ver si la función igual funciona bien. Ten cuidado con los espacios que introduzcas en las celdas sin darte cuenta, puesto que eso mismo puede hacer que la función IGUAL no devuelva el resultado que tu esperas.

12.- En la misma hoja que el ejercicio 10, en K1 escribe "LARGO" y en K2:K10 debe aparecer la longitud de caracteres que tienen las celdas de la columna F (donde están: Apellido1 Apellido2, Nombre). Usa la función LARGO

13.- En la misma hoja anterior, en L1 escribe "Mayusculas", y en L2:L10 deberá aparecer el contenido de las celdas F2:F10 automáticamente todo en Mayusculas. Usa la función MAYUSC.

14.- En la misma hoja anterior, en M1 escribe "Minusculas", y en M2:M10 deberá aparecer el contenido de las celdas F2:F10 automáticamente todo en Minusculas. Usa la función MINUSC.

15.- En una celda vacía escribe un número de 3 cifras. Observa que EXCEL alinea ese número a la derecha. Ahora, en la celda de debajo, escribe el mismo número, pero cambia el formato de la celda a "Texto", observarás ahora que el contenido de esa celda se alinea a la izquierda; aunque aparentemente el contenido de las 2 celdas es el mismo, en la realidad no lo es, ya que la primera celda es un número y la segunda celda son 3 caracteres numéricos unidos, y eso para el ordenador son 2 cosas totalmente diferente. Las funciones TEXTO y VALOR nos sirven para poder convertir números en texto y viceversa. Practica con dichas funciones y varios valores o textos numéricos.

16.- En una hoja nueva, copia las 3 primeras columnas del ejercicio 3, en las que tienes el NOMBRE, APELLIDO1 y APELLIDO2 de 9 personas, en las columnas A, B y C. A continuación en la celda D1 escribe "Apellidos y Nombre" y en D2:D10 juntarás automáticamente los apellidos y nombre de esas 9 personas de la siguiente manera "apellido1 apellido2, nombre". Observa que los apellidos van separados con un espacio y entre el segundo apellido y el nombre hay una coma y un espacio (como hemos hecho ya en un ejercicio anterior). En E1 escribe "LARGO" y en E2:E10 deberá aparecer la longitud en caracteres de cada una de las celdas de la columna D; En F1 escribe "lugar del primer espacio", y en F2:F10 deberá aparecer la posición del primer espacio que aparece en las celdas de la columna D (el espacio que separa los 2 apellidos entre sí); En G1 escribe "lugar de la coma" y en G2:G10 deberá aparecer la posición de la coma en cada celda de la columna D; Una vez que tengas todos estos datos calculados en las celdas correspondientes tienes que hacer lo siguiente:

- En H1 escribe: "Apellido1" y en H2:H10 excel deberá extraer solamente el primer apellido de las celdas D2:D10. Puedes usar la función IZQUIERDA y alguno de los datos calculados en las columnas anteriores.

- En i1 escribe: "Apellido2" y en i2:i10 excel deberá extraer solamente el segundo apellido de las celdas D2:D10. Puedes usar la función EXTRAE o MED y varios de los datos calculados en las columnas anteriores. Con esos datos tendrás que hacer alguna operacion matemática para comprobar donde empieza y donde termina el segundo apellido de cada celda.

- En J1 escribe: "Nombre" y en J2:J10 excel deberá extraer solamente el NOMBRE de las celdas D2:D10. Puedes usar la función DERECHA y varios de los datos calculados en las columnas anteriores para calcular cuántos caracteres tiene cada nombre de las celdas D2:D10.

FUNCIONES LÓGICAS:

1.- Las funciones FALSO y VERDADERO no llevan ningún parametro entre los parentesis. Solamente devuelven los valores lógicos VERDADERO y FALSO. Estas funciones suelen utilizarse como funciones anidadas dentro de otras funciones. Prueba a utilizar estas dos funciones en algunas celdas vacías.

2.- La función NO cambia el valor lógico de lo que lleve entre paréntesis. Normalmente se le suele meter una condición en su interior y el resultado de esa condición se convierte en lo contrario. Prueba a utilizar esta funcion con una condición en su interior que sea verdadera, y luego con una que sea falsa, y comprueba el resultado.

3.- Las condiciones que devuelven valores lógicos se pueden unir con los operadores AND y OR, que en español son Y y O. En excel su funcion equivalente son las funciones Y(...) y la función O(...). Estas 2 funciones llevan entre los paréntesis varias condiciones lógicas separadas por caracteres punto y coma ";".

- La forma de funcionar de la función Y es la siguiente:

- si todas sus condiciones son verdaderas, el resultado final será verdadero.

- pero si una de sus condiciones es falsa, el resultado final será falso.

- La forma de funcionar de la función O es la siguiente:

- si todas sus condiciones son falsas, el resultado final será falso.

- pero si una de sus condiciones es verdadera, el resultado final será verdadero.

Comprueba esto en una hoja de cálculo nueva:

Las funciones O e Y no se suelen utilizar por si solas en las celdas, sino que suelen ir anidadas dentro de otras funciones, sobre todo dentro de las funciones SI , SUMAR.SI o CONTAR.SI, en el sitio de la condición de dichas funciones.

4.- La función SI es muy útil en EXCEL (es el equivalente a la estructura IF de los lenguajes de programación). Tiene 3 parámetros: el primero de ellos es una condición, el segundo parámetro es el valor o dato que hay que poner en la celda en el caso de que esa condición sea verdadera; el tercero de los parámetros es el valor que hay que poner en la celda si la condición comprobada es falsa. Crea una hoja como la siguiente:

- Las celdas de la columna nota se deberán poner automáticamente en color rojo si una nota es suspensa.

- En la columna "Aprobado o Suspenso" deberá aparecer automáticamente la palabra "APROBADO" si el alumno ha aprobado, o "SUSPENSO" si ha suspendido.

- En la columna "EDAD" sus celdas apareceran con fondo verde si el alumno es mayor de edad.

- En la columna "MAYOR DE EDAD" deberá aparecer la palabra "MENOR" si el alumno es menor de edad, o "Mayor de Edad" si el alumno fuese mayor de edad.

- En la columna "PREMIO" le vamos a dar un premio de 500€ a los alumnos que son Menores de edad Y han aprobado.

- En la columna "PREMIO" le vamos a dar un premio de 100€ a los alumnos Mayores de edad que hayan aprobado.

- En la columna "TOTAL" aparecerá el total de los premios que se lleva cada alumno.

5.- Hay veces que ejecutamos algunas funciones de excel y el resultado es un error de excel (por ejemplo:  #!VALOR!). Esto no significa que la función esté mal escrita, ni que Excel esté funcionando mal, sino que devuelve dicho error porque así está programada esa función en Excel. Prueba lo siguiente en una celda:

=HALLAR("E";"HOLA";1)

Podrás comprobar que el resultado de ésta funcion da error en la celda correspondiente, aunque la función está perfectamente escrita y utilizada. Si no queremos que aparezcan errores de este tipo en nuestras celdas, podemos utilizar la función SI.ERROR(...). Esta función lleva 2 parámetros:

- El primero es la función anidada que puede dar error. Si no genera error, se mostrará en la celda el valor de dicha función anidada

- El segundo parámetro es el valor que hay que poner en la celda en caso de que se haya generado un error de Excel.

Prueba la función HALLAR escrita anteriormente, dentro de la función SI.ERROR() para comprobar que ahora, en el caso de que se produzca un error de Excel, podemos poner en la celda el valor que estimemos oportuno (por ejemplo un 0  o un -1).

FUNCIONES DE BUSQUEDA Y REFERENCIA:

1.- La función BUSCARH o CONSULTAH (dependiendo de tu versión de Excel), busca un dato determinado en la primera fila dentro de un rango de celdas. Cuando encuentra ese dato, baja tantas celdas como le indiques dentro de dicho rango, teniendo en cuenta que la celda numero 1 está en la fila 1 del rango. Devuelve el valor que haya en dicha celda. El último parámetro que lleva ésta funcion es el parámetro ORDENADO, que es recomendable siempre ponerlo a FALSO, ya que si ponemos verdadero en la mayoría de las ocasiones no funcionará correctamente (si los datos de la primera fila del rango no están ordenados).

En una nueva hoja de cálculo Crea una tabla como la siguiente:

- Las cantidades en las celdas pueden ser las que tu quieras

Ahora crea las siguientes celdas:

- La celda A8 la configuras para que solamente en ella puedan ponerse los nombres de los meses tal y como los tienes escritos en las celdas B1:M1 (lo haces con Validación de Datos, que está en la pestaña DATOS)

- La celda A9 la configuras para que solamente en ella puedan ponerse los conceptos de gastos tal y como los tienes escritos en las celdas A2:A6 (lo haces con Validación de Datos, que está en la pestaña DATOS)

- Las celdas B8 y B9 las combinas en una sola. Y en esta nueva celda debe utilizar la función BUSCARH o CONSULTAH para que en ella aparezca el gasto del mes que elijas en A8 y en concepto estatico de TELEFONO.  (mas adelante veremos como podemos cambiar este valor a dinámico)

- Las celdas D8:D13 las copias igual, y en E8 tienes que poner el nombre de un mes (de igual forma que en A8), y a continuación en las celdas de abajo deberá aparecer automáticamente lo que has gastado en ese mes en: LUZ, AGUA, COMIDA, ... , usando en cada celda la función BUSCARH o CONSULTAH.

2.- La función BUSCARV o CONSULTAV es similar a BUSCARH: busca un dato en la primera columna de un rango de celdas, y una vez que encuentra dicho dato, se mueve tantas celdas a la derecha como tu le indiques, teniendo en cuenta que la celda 1 es la misma celda donde se ha encontrado el dato buscado.

En la misma hoja del ejercicio anterior, crea las siguientes celdas:

- En i8 debes escribir uno de los conceptos de gasto de la tabla.

- Desde J9 hasta J20 deberá aparecer automáticamente el gasto de cada uno de los meses en dicho concepto de gasto elegido. Usa la función BUSCARV o CONSULTAV

3.- En una hoja nueva, en A1:L1 escribe los meses del año empezando por enero. Lo puedes hacer facilmente con el controlador de relleno. Después A2 la vas a configuar para que solo se puedan elegir los valores de A1:L1 , y eliges uno de esos meses.  Y a continuación, en  B2 deberá aparecer automáticamente la posición en la que se encuentre el mes que has elegido dentro de la lista de meses de A1:L1. Usa la función COINCIDIR.

4.- En una hoja nueva copiate la tabla del ejercicio1. Las celdas A8 y A9 las pones así:

Configura dichas celdas para que puedas elegir en ellas solamente los meses del año y los conceptos de gasto, respectivamente. A continuación, combina en una sola las celdas B8:B9, y en esa celda tienes que hacer que aparezca automáticamente la cantidad de dinero que has gastado en el mes elegido y en el concepto elegido.

Utiliza la función COINCIDIR anidada adecuadamente dentro de BUSCARH para hacer este ejercicio.

5.- Copia la hoja del ejercicio anterior en una nueva, y modifica la celda combinada B8:B9 para que obtenga el dato correspondiente usando las funciones BUSCARV y COINCIDIR anidada dentro de la primera.

6.- En una hoja nueva, en las celdas A1:A20 escribe las palabras que tu quieras (por ejemplo nombres de personas). En B1 escribe "POSICION" y en B2 vas a escribir un número del 1 hasta el 20. En B3 deberá aparecer qué valor de la celda que indique el contenido de la celda B2 dentro de las celdas A1:20, como en la siguiente imagen:

Fijate que el número 4 de la lista de nombres es Lucía. Prueba a cambiar el valor de la celda B2 para comprobar que los nombres en B3 van cambiando. Intenta que en B3 las palabras aparezcan todas en Mayusculas, como vimos con una de las funciones de Texto. Usa la función ELEGIR para hacer este ejercicio.

EJERCICIOS VARIADOS CON FUNCIONES DE DIVERSAS CATEGORÍAS:

1.- ELECCIONES GENERALES EN ESPAÑA: Cuando se celebran cada 4 años las elecciones generales en España, cada votante tiene la oportunidad de votar por los candidatos a Diputados de la PROVINCIA donde esté censado. Cuando se cierra la votación y se cuenta el número de votos obtenidos por cada partido, se aplica el MÉTODO D'HONT para determinar cuántos diputados obtiene cada partido dependiendo del número de votos obtenidos.

El MÉTODO D'HONT sigue los siguientes pasos, que veremos en un ejemplo:

En la provincia de Toledo hay 6 plazas para Diputados en el Congreso de los Diputados:

y cada partido ha obtenido por ejemplo esa cantidad de votos que se ve reflejada en la tabla.

El siguiente paso es dividir el número de votos de cada partido entre 1 , entre 2 , entre 3 , ... y entre 6, como sale en la siguiente imagen:

Una vez realizada la tabla anterior, con los cocientes de las divisiones, se eligen las 6 celdas cuyo números son los mayores de todos (en la imagen son aquellas que están coloreadas de rojo). Podemos hacer que se coloreen en rojo utilizando el FORMATO CONDICIONAL --> Reglas Superiores e Inferiores --> 10 Superiores --> Poner un 6 --> Aceptar, que está en la Pestaña Inicio de Excel

Y ya sabremos cuántos Diputados ha obtenido cada Partido, contando el número de celdas en rojo que tiene cada uno de ellos.

Realiza una hoja de Excel como la anterior, con esas mismas tablas (los datos de la primera tabla pueden ser los que tu quieras, y el nombre de los partidos los puedes cambiar por nombres reales). Ten en cuenta que las celdas de la segunda tabla dependen directamente de los valores de la primera.

Utiliza la operación DIVISIÓN o la función COCIENTE para calcular las divisiones que producen los datos de la segunda tabla, y también el Formato Condicional indicado anteriormente para resaltar las 6 celdas con los valores mayores.

2.- Vamos a mejorar la hoja de cálculo anterior para que calcule automáticamente el número de Diputados de cada partido, a partir de las 2 tablas que ya tenemos creadas:

En la misma hoja del ejercicio anterior, crea una tabla como la siguiente:

Desde i11 hasta i16 deberán aparecer los 6 valores mayores de la tabla de las divisiones. Para ello utiliza la función K.ESIMO.MAYOR(rango;orden). Esta función busca en un rango de celdas con números aquellos números mayores y te devuelve el que tu quieras: el mayor de todos, el segundo mayor de todos, el tercero mayor, el cuarto mayor, etc... En la celda i13, por ejemplo, deberías poner lo siguiente:     K.ESIMO.MAYOR( B11:G15 ; 3 )   para que te busque el tercer número mayor de todos los de la tabla de las divisiones, y lo mismo para las demás celdas de ésta tabla que acabamos de crear.

Una vez que ya tengamos calculado los 6 valores máximos de las divisiones, vamos a crear una tabla como la siguiente:

Esta tabla es equivalente a la tabla que está en el rango A10:G15 . Las celdas blancas de momento dejalas vacías.

Ahora en esta última tabla en cada celda blanca, deberá aparecer un número del 1 al 6, indicando si en dicha celda, el valor que hay en la tabla equivalente superior, es el 1º mayor, el 2º mayor, ..., 6º mayor.