2 Trabajando con fórmulas

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:

Figura 2_01 Captura de pantalla propia Operación Suma en una hoja de cálculo

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:

Todo lo anterior en cualquiera de los tres programas que componen el curso, a tu elección.

Contenidos

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:

Veamos un ejemplo:

Figura 2_02 Captura de pantalla propia Sintaxis de una función 

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:

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:

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:

  1. Situarnos en la celda donde queremos obtener el resultado

  2. 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.

  3. En las versiones de Microsoft Excel, la pantalla tiene este aspecto:

Figura 2_06 Captura de pantalla propia Insertar función en Excel 2003

Figura 2_07 Captura de pantalla propia Insertar función en Excel 2007 y 201

Figura 2_8 Captura de pantalla propia Insertar función en Google Drive

danger

Importante

Para obtener un listado de todas las funciones disponibles en cada programa puedes acceder a los siguientes enlaces:

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:

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:

Solución


Figura 2_9: Captura de pantalla propia Solución ejercicio

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:

Figura 2_10: Captura de pantalla propia Solución juego de los aciertos en el área de Música

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.

Utilización de la función CONTAR Autora: Carmen Tobeña

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á:

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"))

Figura 2_11: Captura de pantalla propia Funciones anidadas

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:

Figura 2_12: Captura de pantalla propia - Ejercicio resuelto sobre cálculo de letra DNI 

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...