2 Trabajando con fórmulas
- Trabajando con fórmulas
- Contenidos
- U1. Sintaxis de una función
- Operaciones con funciones
- Tipos de referencias
- U2. Funciones
- Ejemplos de funciones
- Propuestas didácticas
Trabajando con fórmulas
Las tablas creadas en el módulo 1 quedan muy vistosas, son fáciles de modificar... pero la verdadera utilidad de las hojas de cálculo, la da precisamente su nombre:
Calcular
Es decir, realizar operaciones con funciones, que automaticen los procesos y nos faciliten el trabajo cuando manejamos cantidades de datos elevadas.
Cuando en las operaciones que necesitamos intervienen pocos datos y además son operaciones sencillas, es decir, cuando realizamos sumas, restas, multiplicaciones o divisiones, podemos utilizar los caracteres +, -, * y / del teclado, siempre anteponiendo el signo "=" antes de la operación.
Lo primero que hay que comprender es que ralizaremos las operaciones, no con valores, sino con celdas. ¿Por qué se hace de esta manera? Porque de esta forma, en el caso de que los datos iniciales cambien, el programa recalcula automáticamente las soluciones.
Observa el siguiente ejemplo:
Si nos situamos en la celda B4, y escribimos =B2+B3 podemos ver como se suman las celdas B2 y B3. El resultado aparecerá donde escribimos la operación, es decir, en B4.
Además, vemos que la fórmula se escribe en la barra de fórmulas.
No debemos olvidarnos de añadir el signo = antes de la operación, porque sino el programa entiende los valores como datos y no como operaciones matemáticas.
Prueba ahora a cambiar el valor de la celda B2, escribe por ejemplo 46 en lugar de 25.
¿Qué ha pasado?
Exacto: automáticamente la solución en B4 ha pasado a ser 88.
danger
Importante
Otra forma más rápida de indicar las celdas que queremos que intervengan en una operación es señalarlas directamente con el ratón.
En el ejemplo anterior, nos situamos sobre la celda B4 y escribimos =
A continuación hacemos clic con el ratón en la celda B2
Escribimos el signo +
Por último hacemos clic con el ratón en la celda B3 y damos a Enter
info
Objetivos
En este segundo módulo los objetivos que queremos alcanzar son los siguientes:
- Entender la sintaxis de una fórmula.
- Diferenciar entre referencias absolutas y relativas.
- Saber utilizar correctamente funciones que hagan operaciones sencillas como sumar, promediar, raíces, máximos, mínimos, contar, operaciones lógicas, buscar entre los datos algunos que cumplan una determinada condición, etc.
Todo lo anterior en cualquiera de los tres programas que componen el curso, a tu elección.
Contenidos
- Unidad 1:Sintaxis de una fórmula
- Operaciones con funciones
- Tipos de referencias
- Unidad 2: Funciones
- Ejemplos de funciones
- Propuestas didácticas
U1. Sintaxis de una función
Ya hemos dicho en la introducción del módulo que las fórmulas más simples se suelen introducir manualmente.
Sin embargo, cuando la dificultad aumenta, se suelen utilizar Funciones que facilitan el proceso.
Una Función es una expresión que introducimos en una celda y que relaciona valores y fórmulas de otras celdas para producir un resultado. Una fórmula comienza siempre con el signo igual (=) y puede contener textos, números, referencias de celdas...
Independientemente del programa que estemos utilizando, el formato de una función en la barra de fórmulas es el siguiente:
info =FUNCION(Argumento1;Argumento2;….)
donde:
- Signo = ****(lo añadimos para indicar al programa que vamos a escribir una función y no un dato)
-
Función es la fórmula que necesitamos en cada caso (sumar, mediar, etc). Aunque la mayoría de las operaciones son iguales en todos los programas, si que cambia el nombre de la función asociada. Por ejemplo, para realizar la operación MEDIA ARITMÉTICA, se utilizan las siguientes funciones dependiendo del programa utilizado:
- =PROMEDIO en Excel
- =PROMEDIO en Calc
- =AVERAGE en Drive
-
Argumentos son los datos necesarios encerrados entre paréntesis (celdas o rangos).
Veamos un ejemplo:
En la celda que contiene la fórmula se visualiza siempre el resultado de la misma y la fórmula en sí se visualiza en la barra de fórmulas.
Además, aparece asociado cada argumento a un color, con lo que es más sencillo comprobar si la ecuación es correcta. En este ejemplo aparece el rango B2:D3 en verde y la celda B6 en rojo.
Los resultados de las funciones, al igual que el de las fórmulas, se actualizan automáticamente al modificar los datos de las celdas cuyas direcciones son referenciadas en la función.
info
Caso práctico
En la siguiente tarea vamos a practicar con los siguientes conceptos:
- Insertar datos
- Cambiar formato
- Introducir fórmula
En una hoja de cálculo del programa que decidas, introduce los siguientes datos:
| | 2º A | 2º B | 2º C | |:-----------------|:----:|:----:|:----:| | 1ª Evaluación | 5,6 | 6,7 | 5,1 | | 2ª Evaluación | 6,3 | 7,2 | 5,6 | | 3ª Evaluación | 6,1 | 6,8 | 5,4 | | Evaluación Final | | | ||
Añade las fórmulas necesarias para calcular las notas medias de la evaluación final para cada clase y además añade una columna a la derecha con las notas medias por evaluación.
%accordion%Solución%accordion%
%/accordion%
Operaciones con funciones
Muchas veces, al manejar grandes cantidades de información, la adición de fórmulas se convierte en un hecho reiterativo. En el ejemplo que has realizado en la página anterior, has tenido que escribir una fórmula prácticamente igual varias veces.
Para evitar tener que escribir la misma fórmula una vez tras otra, existe la posibilidad de copiar y pegar las funciones.
Al igual que ocurre con los datos, las funciones también se comportan de igual manera al copiarlas en otras ubicaciones, ya que se actualizan a la nueva posición, pudiendo aprovechar la definición de una función en otras estructuras similares que se construyan.
Para copiar y pegar fórmulas en otros lugares de la hoja de cálculo, independientemente del programa que utilicemos, tenemos varias posibilidades:
-
Seleccionar la celda que contiene la fórmula que deseamos copiar y desde el menú Edición (o Editar) elegir Copiar (o desde el teclado CTRL+C). A continuación situar el cursor en la celda donde queremos tener la nueva fórmula y desde el menú Edición (o Editar) elegir Pegar (o desde el teclado CTRL+V).
-
En los casos en que queramos copiar a celdas adyacentes puede utilizarse otro método: Seleccionar la celda que contenga la fórmula. Pulsar en la parte inferior derecha en el marco destacado que rodea la celda y mantener pulsado el botón del ratón. El puntero del ratón se convierte en un retículo. Mantener pulsado el botón del ratón y tirar hacia abajo o hacia la derecha sobre las celdas en las que desee copiar la fórmula. Soltar el botón del ratón. La fórmula se copiará en las celdas y se adaptará automáticamente, como se observa en la siguiente animación:
Pegado de funciones en una hoja de cálculo. Autora: Carmen Tobeña
Como has podido observar al copiar y pegar una fórmula automáticamente la función se ajusta a la nueva celda. Si te fijas en la barra de fórmulas del ejemplo anterior, la función inicial =A1+B1 se transforma en =A2+B2 al copiar en la segunda fila, y así sucesivamente hasta que al pegarla en la quinta fila aparece automáticamente =A5+B5.
En algunas ocasiones necesitamos pegar solamente una parte de toda la información que contiene la celda (formato, fórmulas, números...).
En estos casos utilizamos un tipo específico de pegado que se conoce como pegado especial, al que se accede debajo de la opción de pegado explicada anteriormente.
Todos los programas nos ofrecen varias posibilidades comunes a la hora de pegar funciones.
Rellenar huecos
Lea el párrafo que aparece abajo y complete las palabras que faltan.
{%fbq%} Si queremos que en una celda se pegue la fórmula pero sin el formato de la celda, utilizaremos un tipo de pegado especial que se llama pegar $$fórmulas##.
Si por el contrario queremos pegar las celdas pero intercambiando filas por columnas, utilizaremos la opción $$transponer##.
Cuando queremos evitar pegar las celdas de origen que estaban vacías, podemos utilizar el pegado especial $$saltar blancos## en Excel o $$ignorar celdas vacías## en Calc. {%endfbq%}
Tipos de referencias
Hasta ahora siempre nos hemos referido a las celdas como una combinación de una letra y un número, por ejemplo B6. Esta referencia se obtiene por defecto en cualquiera de las hojas de cálculo que estamos tratando, y se denomina referencia relativa de la celda.
Una referencia relativa a una celda o rango es aquella que, al copiar la celda donde está escrita y pegarla en otra ubicación, se ajusta automáticamente para hacer referencia a otras celdas.
Fíjate en el siguiente ejemplo:
Referencias absolutas y relativas. Autora: Carmen Tobeña
Como has podido observar, en algunas ocasiones necesitamos que las celdas permanezcan invariables aunque copiemos la función en otras posiciones. En esos casos se utilizan las llamadas referencias absolutas.
Una referencia absoluta a una celda o rango es aquella que, al copiar la celda donde está escrita y pegarla en otra ubicación, NO se ajusta y queda bloqueada haciendo referencia siempre a la misma celda.
Para hacer una referencia absoluta a una celda, se deben introducir los símbolos de dólar $ antes de la letra (con eso bloqueamos la columna) y antes del número (y con eso bloqueamos la fila) de una referencia normal. Es decir, una referencia absoluta a la celda B6, sería: $B$6
En muchas ocasiones solamente nos interesará dejar fijo, bien una columna, o bien una fila, utilizando entonces las llamadas referencias mixtas.
Una referencia mixta a una celda o rango es aquella que, al copiar la celda donde está escrita y pegarla en otra ubicación, ajusta sólo la letra o sólo el número de la referencia, quedando bloqueado sólo el número o sólo la letra respectivamente.
Por lo tanto, si queremos bloquear una columna, debemos colocar el símbolo de dólar "$" antes de la letra y si queremos bloquear una fila, debemos colocar el símbolo de dólar "$" antes del número.
tip
ParaSaberMas
Pulsando sucesivamente la combinación de teclas Mayúsculas + F4 alternamos entre el modo de referencia absoluta, referencia mixta (fijar fila), referencia mixta (fijar columna) o referencia relativa
Tarea interactiva
Lee y completa
Imagina que quiero copiar la celda C5.
{%fbq%} * Si necesito copiarla dejando igual la fila y la columna escribiré $$\$C\$5##. * Si solamente quiero que cambie la fila escribiré $$\$C5##. * Si quiero que solamente la fila permanezca igual escribiré $$C\$5## * Esto último es una referencia $$mixta##. {%endfbq%}
U2. Funciones
Todos los programas que estamos tratando incluyen una enorme cantidad de funciones agrupadas por categorías.
En todos ellos la manera de acceder a esas funciones es la misma:
- Situarnos en la celda donde queremos obtener el resultado
-
Hacer clic sobre el icono de *Insertar Función*. Lo único que cambia es la forma que tiene este icono en los tres programas, que es lo que vamos a ver a continuación.
-
En las versiones de Microsoft Excel, la pantalla tiene este aspecto:
-
En Calc de OpenOffice: puedes ver esta información en el siguiente enlace.
-
En Drive de Google ocurre exactamente lo mismo que en la versión 2003 de Excel. Podemos acceder desde el menú Insertar -- Función, o desde la barra de herramientas a través del icono Σ. La pantalla que aparece tiene el siguiente aspecto:
danger
Importante
Para obtener un listado de todas las funciones disponibles en cada programa puedes acceder a los siguientes enlaces:
- Para Microsoft Excel 2003 y 2007
- Para Microsoft Excel 2010
- Para Calc de Apache OpenOffice
- Para Drive de Google
tip
Para Saber Mas
La mayor parte de las funciones aparecen en los tres programas tratados en el curso.
Sin embargo Google Drive presenta algunas excepciones como son:
- =Googletranslate para traducir textos;
- =image() para insertar imágenes adaptadas al tamaño real de la celda
Ejemplos de funciones
Si has revisado el listado completo de funciones de alguno de los programas que estamos tratando, te habrás dado cuenta que se hacen interminables. A modo de curiosidad, decirte que soportan mas de 300 funciones.
Por eso en este apartado, vamos a introducir las funciones más utilizadas. Puedes ir practicando o simplemente léelas para que en algunos ejercicios puedas aplicarlas, seguro que te parecerán muy útiles para su aplicación en clase. En el tema siguiente las aplicaremos a ejemplos concretos.
Ten en cuenta, que aunque en la columna de ejemplos hagamos operaciones con números para que resulte más sencilla su comprensión, en la práctica, los números son en realidad celdas o rangos de celdas.
Asociadas a las fórmulas anteriores existe una orden muy útil en la elaboración de aplicaciones con hojas de cálculo. Se trata del formato condicional.Este comando permite cambiar el formato a aquellas celdas que cumplan una determinada condición que yo impongo. Es muy útil cuando queremos resaltar resultados que cumplan una o varias condiciones determinadas, por ejemplo, mostrar los resultados de alumnos con calificación mayor que 5 en color verde, y el resto en color rojo.Puedes aprender a utilizar esta orden en los siguientes enlaces:- Si utilizas la hoja de cálculo de Microsoft Excel 2007 o 2010.- Si utilizas la hoja de cálculo de Calc de Apache OpenOffice.- Si utilizas Drive de Google.
info
Caso práctico
Prueba todas las funciones vistas en este tema. Para ello utiliza los siguientes datos:
||A|B|C| |:--:|:--:|:--:|:--:| |1|10|25|36| |2|2|15|| |3|22|3|2| |4|5||| |5|8|51|6| |6||45|32| |7|19|3|6| |8|8|2|18| |9|13|12|1|
Realiza las operaciones siguientes:
- En D1 debe aparecer la suma del rango A3:C6
- En D2 debe aparecer la quinta potencia de la celda C3
- En D3 debe aparecer el resto de la división entre las celdas C1 y A8
- En D4 debe aparecer el valor mínimo de la columna B
- En D5 debe aparecer el número de veces que se repite el número 2 en toda la tabla
- En D6 debe aparecer la media aritmética de la primera columna
- En D7 debe aparecer la palabra MUCHO si el contenido de la celda A3 es mayor que 30 y debe aparecer la >palabra POCO si es menor
- En el rango A1:C9 debe aparecer las celdas con números mayores que 20 en verde y las menores o iguales >en rojo.
Solución
Propuestas didácticas
¿Te acuerdas de los ejemplos de aplicaciones de hoja de cálculo que te presentamos en el Módulo 1?
Pues todas esas actividades pueden realizarse con las fórmulas vistas en el apartado anterior. Vamos a analizar algunos ejemplos. En todos ellos vamos a utilizar el nombre de la función en castellano, por comodidad, pero tienes su conversión al inglés en el tema anterior, al describir ejemplos de funciones.
Muchas de las prácticas mostradas permiten la interactividad del alumnado a través de una retroalimentación que les muestra un mensaje indicando si han acertado o no. Fíjate en el ejemplo sobre instrumentos musicales:
Como has podido observar, además de darle el formato adecuado, para que aparezca automáticamente "acierto" o "error" hemos utilizado la función lógica SI. Esta función puede utilizarse con datos numéricos o con datos de texto, como en este caso. El único cambio es que tenemos que encerrar el texto entre comillas para que el programa entienda que lo que tiene que buscar es un texto.
Esta condición es muy útil cuando queremos crear aplicaciones para que el alumnado demuestre que conoce la ortografía correcta de las respuestas.
Además, para que el resultado aparezca en rojo o verde según se haya acertado o no, podemos utilizar el formato condicional, como ves en la imagen anterior.
Ahora solo restaría darle a la aplicación un formato adecuado y vistoso.
- En muchos ejemplos de los que hemos mostrado en el Módulo 1, las aplicaciones contienen un indicador de la nota del alumno en cada momento, que va variando al contestar cada pregunta. Una posible solución sería la mostrada en el siguiente ejemplo:
La fórmula utilizada para obtener la puntuación ha sido la siguiente: =CONTAR.SI(F3:F12;"CORRECTO") en la que se cuenta el número de veces que aparece la palabra “correcto” en el rango especificado [F3:F12]
En este caso cada acierto equivale directamente a un punto, pero si no fuera así, bastaría con multiplicar la función CONTAR por el valor de cada pregunta.
info
Importante
Imagina que queremos crear una hoja de cálculo para utilizarla en un cine que cuenta con 3 salas, con un >aforo de 100 personas cada una. Además de muchas otras operaciones, la hoja debe darnos un mensaje >dependiendo del número de entradas que queden por vender, de tal forma que nos dirá:
- VENTA DE ENTRADAS cuando se hayan vendido menos de 90
- ÚLTIMAS ENTRADAS cuando las ventas pasen de 90
- ENTRADAS AGOTADAS cuando se llegue a 100 vendidas
Tenemos que diseñar la hoja de cálculo que se aproxime al caso anterior, utilizando la siguiente tabla:
|VENTA DE ENTRADAS|ENTRADAS VENDIDAS|MENSAJE| |:---|:--:|:--:| |SALA 1| 55 || |SALA 2| 95 || |SALA 3| 100 ||
Como tienen que compararse tres condiciones, no puede utilizarse una función lógica SI como hasta ahora, sino una combinación de dos de ellas. Es esto lo que se conoce como FUNCIONES ANIDADAS, es decir, una función dentro de otra.
En la celda C2 escribiremos la siguiente función:
=SI(B2<90;"VENTA DE ENTRADAS";SI(B2<100;"ÚLTIMAS ENTRADAS";"ENTRADAS AGOTADAS"))
El DNI en España, consta de un número de 8 cifras y una letra, que puede obtenerse si se siguen los pasos siguientes:- Calcular el resto de dividir el número entre 23 (Se utilzan 23 letras por ser 23 un número primo, las letras que no se utilizan son I, Ñ, O, U. Cuando se planteó de que letras prescindir, se optaron por estas o bien por que no eran estándares del código ASCII (la Ñ) o bien por que se podrían confundir con otras letras o números (I, O, U)- El número obtenido está entre 0 y 22, y seleccionaremos la letra asociada a el número de DNI según la siguiente tabla:
|0|1|2|3|4|5|6|7|8|9|10|11|12|13|14|15|16|17|18|19|20|21|22| |--- |--- |--- |--- |--- |--- |--- |--- |--- |--- |--- |--- |--- |--- |--- |--- |--- |--- |--- |--- |--- |--- |--- | |T|R|W|A|G|M|Y|F|P|D|X|B|N|J|Z|S|Q|V|H|L|C|K|E|
¿Cómo podríamos obtener esta letra con una hoja de cálculo? hay varias maneras, aquí te explicamos cómo hacerlo utilizando las funciones RESIDUO y BUSCARV de forma anidada, como ves en la siguiente figura:
Esta combinación de funciones es muy útil siempre que queramos relacionar dos columnas entre sí: artículos en venta con precios; colores con valores, por ejemplo para hacer una hoja de cálculo de resistencias según sus colores; números con indicaciones, por ejemplo al interpretar encuestas que utilizan números en respuestas (0 poco, 1 medio, 2 mucho) o similares... pero mucho ojo, solamente responde cuando relacionamos columnas, no filas.
Como ves hay infinidad de posibilidades, solamente hay que pensar en la necesidad que quieres satisfacer y descubrir tranquilamente cómo puedes solucionarlo.
A partir de aquí ... todo lo que te atrevas a imaginar...