FÓRMULAS Y FUNCIONES I

Una fórmula es esencialmente una secuencia de operadores, constantes, nombres, rangos de celdas, etc. que comienza con un signo (=) y produce un valor nuevo. Excel incluye varios cientos de fórmulas, denominadas Funciones, que se han diseñado para realizar muchos tipos de cálculos.

Algunos ejemplos:

=(B4/25)+100

=Ingresos-Gastos

=SUMA(A1:A5)

=SI(A1 > 100;A1+0,1*A1;A1+0,05*A1)

Las fórmulas en Excel comparten algunas propiedades básicas:

• Todas las fórmulas empiezan con un signo igual.
• Una vez hemos introducido una fórmula, el valor resultado debe aparecer en una celda.
• Cuando se selecciona una celda que contiene una fórmula, dicha fórmula aparece en la barra de fórmulas.

 Operadores

Un operador especifica el tipo de cálculo que se desea realizar con los elementos de una fórmula. Los hay de diferentes tipos: aritméticos, de comparación, de texto y referencia.

Operadores aritméticos

Los operadores aritméticos permiten realizar las operaciones matemáticas básicas.

Combinan valores numéricos y dan como resultado nuevos valores numéricos.

Los operadores son:

Operadores de comparación

Permiten comparar dos valores, produciendo como resultado VERDADERO o FALSO.

Los operadores habituales son:

Los operadores de comparación se pueden aplicar tanto a datos de tipo numérico como a datos de tipo texto y de fecha. Por ejemplo, podemos comparar dos cadenas de caracteres para ver cuál es mayor, o incluso comparar dos cadenas de caracteres para ver si son distintas.

Operador de texto

Se trata del operador &, que permite concatenar dos o más cadenas &, que concatenar dos o más cadenas de texto en una sola continua. Por ejemplo: =»Bienvenido» & «a» & «Excel» mostraría el texto Bienvenido a Excel.

Operadores de referencia

Hay tres operadores de referencia:

• Rango (dos puntos). Hace mención a todas las celdas entre dos referencias, incluyendo éstas. Por ejemplo, B2:E2 incluye las celdas B2,C2,D2 y E2.
• Unión (punto y coma). Produce una referencia única, resultado de unir otras dos. Por ejemplo, la suma de A1+A2+A3+B1+B2+B3 es equivalente a la suma de A1:A3;B1:B3.
• Intersección (espacio). Hace referencia a las celdas comunes a dos referencias. Por ejemplo, B7:D7 C6:C8 hace referencia a C7, celda resultado de la intersección.

Orden de evaluación de los operadores

Cuando se combinan varios operadores en la misma fórmula, Excel realiza las operaciones en el siguiente orden de prioridades:

Los operadores con la misma prioridad se evalúan de izquierda a derecha. Hay que recordar que para indicar números negativos en una fórmula no se deben usar paréntesis, sino el signo menos delante del número.

Siguiendo este orden de preferencia, podemos ver que el operador de multiplicación se evalúa antes que el operador de suma; así, la fórmula: =1+2*5, da como resultado 11 ( 1 más el resultado de multiplicar 2 por 5) y no 15 (la suma de 1 y 2, que es igual a 3, multiplicada por 5).

Para cambiar el orden de evaluación de una fórmula utilizaremos los paréntesis agrupando las expresiones dentro de una fórmula. De hecho, una expresión que esté entre paréntesis se evalúa antes que todos los operadores aritméticos. Por ejemplo:

=1+2*3                  Da como resultado 7

=(1+2)*3               Da como resultado 9

De esta forma, se pueden agrupar paréntesis dentro de otros paréntesis donde la fórmula se evaluará desde los paréntesis interiores a los exteriores.

Para identificar cada paréntesis con su pareja, cuando se está escribiendo la fórmula, Excel le asignará un color a cada pareja de paréntesis.

Fórmulas que producen valores de error

Excel, según el tipo de operadores que haya en una fórmula, espera un cierto tipo de valores. Si se introduce un valor de un tipo diferente al esperado, Excel intentará convertir el valor. En caso de que no lo logre, aparecerá un mensaje de error. Cuando Excel no es capaz de calcular correctamente el valor de una fórmula para una celda, presenta un valor de error en dicha celda. Los valores de error comienzan con el signo de número (#), y pueden ser:

#¡DIV/0!

#N/A

#¿NOMBRE?

#¡NULO!

#NUM!

#¡REF!

#¡VALOR!

######

Estos tipos de errores ya los hemos visto anteriormente.

Tenemos que tener en cuenta que si una fórmula incluye una referencia a una celda que contiene un valor de error dicha fórmula producirá también un valor de error.

Usar referencias en las fórmulas

Las referencias se basan en los encabezados de las columnas y filas de la hoja de cálculo. Permiten identificar celdas o grupos de celdas, y se pueden incluir en fórmulas.

La hoja de cálculo se encuentra dividida por columnas rotuladas con letras de la A a la XFD y 1.048.576 filas rotuladas con números del 1 al 1.048.576. Una celda vendrá especificada por la intersección de una fila con una columna. Esto se conoce como estilo de referencia A1. La referencia de la celda activa aparece en el cuadro de nombres en el extremo izquierdo de la barra de fórmulas, como se muestra en la siguiente figura:

Hay tres tipos de referencias: referencias relativas, referencias absolutas y referencias mixtas.

Referencias relativas

Cuando se utiliza una referencia en una fórmula, ésta puede ser relativa, es decir, indica a Excel la posición que la celda referenciada ocupa respecto a la celda donde se introduce la fórmula. Si se copia la fórmula en otra celda, las referencias relativas que contiene son actualizadas automáticamente respecto a la nueva posición. Por ejemplo, una fórmula situada en la celda A3 que sea =A1+A2, al copiarse en la celda B3 se convertirá en =B1+B2.

Referencias absolutas

Otra posibilidad para indicar en una fórmula la posición de una celda, es hacerlo de modo absoluto. Una referencia absoluta, como $A$1, indica a Excel cómo encontrar otra celda según su posición exacta en la hoja de cálculo. Estas referencias se designan añadiendo el signo del dólar ($) antes de la letra de la columna y del número de la fila, o bien pulsando la tecla F4 después de escribir la referencia en la fórmula.

De este modo, una fórmula situada en la celda A3 que sea =$A$1+$A$2, al copiarse en la celda B3 seguirá siendo =$A$1+$A$2.

Referencias mixtas

En una misma fórmula puede haber direcciones de celdas absolutas y relativas. En este caso, al copiarlas de una posición a otra, sólo variarán las direcciones relativas. Por ejemplo, si en la celda B5 tenemos la fórmula =$B$1*B3 y la copiamos a la celda C5, la fórmula cambiará a la siguiente =$B$1*C3, ya que la columna de datos B está en forma relativa.

También podemos dejar como dirección absoluta una fila o una columna de una determinada celda si escribimos el signo dólar sólo a uno de los dos datos que hacen referencia a una celda. Por ejemplo, si en la columna B escribimos $B5, hará referencia absoluta a la columna B, sin embargo la fila 5, variará en el momento en que copiemos en otro lugar el contenido de esta celda.

Podemos convertir una dirección de celda relativa en absoluta mediante la tecla F4. Al pulsarla, colocará los signos de dólar necesarios a la dirección de la celda sobre la que está el cursor. Es decir, si tenemos la fórmula =B1/B3+B2 y queremos que, al copiarla, la celda B1 no cambie, colocaremos el cursor sobre la B de B1, y pulsaremos la tecla F4. De este modo la fórmula quedaría =$B$1/B3+B2.

Si pulsamos sucesivamente la tecla F4 sobre una celda que contenga una fórmula, aparecerán los tres tipos de referencia. Es decir, si sobre B1 pulsamos F4, aparecerá $B$1 (referencia absoluta), si volvemos a pulsar F4 aparecerá B$1 (referencia mixta), si volvemos a pulsar F4 aparecerá $B1 (referencia mixta) y si por último volvemos a pulsar F4 aparecerá B1 (referencia relativa).

Para poder utilizar la tecla F4, lógicamente, es necesario tener la fórmula ya escrita.