FORMULAS Y FUNCIONES EN EXCEL

En una planilla, además de los datos, se pueden introducir fórmulas, que permiten establecer relaciones entre las celdas.

Una fórmula es una expresión que combina datos con operadores para determinar el contenido de una celda. Los operadores especifican el tipo de relación que se desea realizar con los operandos.

Hay cuatro tipos de operador: aritmético, de comparación, de texto y de referencia.

Los operadores aritméticos más comunes son los que se observan a continuación.

Los operadores de comparación son: =, >, <, >=, <= <> (distinto). Se usan para comparar dos expresiones y generan el valor lógico verdadero o falso.

Ejemplos: A3=5; B2>B3; C5<=C8; 5+D1<>D4.

Operador Operación Ejemplo

+ Adición 3+7

- Sustracción A9-4

* Multiplicación 2*B5

/ División C3/C10

% Porcentaje 45%

^ potenciación 2^3

El operador de texto & (concatenación) une dos datos de tipo alfanumérico.

Ejemplo: si el contenido de la celda A1 es agua y el de la celda G1 es tero, el resultado de la operación A1&G1 es aguatero.

Los operadores de referencia hacen alusión a un conjunto de celdas.

Ejemplos:

A2:B4 Los dos puntos (:) generan una referencia al conjunto de celdas incluidas entre dos celdas especificadas.

A1,B3,C1,C5,D2

La coma hace referencia a varias celdas.

Las fórmulas se usan con operandos constantes o bien hacen referencia al contenido de una celda. Si ésta varía, automáticamente se recalcula la salida según los nuevos datos de entrada.

En Excel, las fórmulas van precedidas por el signo igual (=). La celda que contiene la fórmula se denomina dependiente, ya que su valor depende de los contenidos de otras celdas.

En el ejemplo, se suma el valor de la celda B4 y 25 y, a continuación, el resultado se divide entre la suma de los valores de las celdas D5, E5 y F5.

Los paréntesis que rodean la primera parte de la fórmula indican a Excel que calcule B4+25 primero y después divida el resultado entre la suma de los valores de las celdas D5, E5 y F5.

=(B4+25)/SUMA(D5:F5)

Referencias de celda y rango

Una referencia identifica una celda o un rango de celdas en una hoja de cálculo e indica a Microsoft Excel en qué celdas debe buscar los valores o los datos que se desea utilizar en una fórmula o función. En las referencias se pueden utilizar datos de distintas partes de una hoja de cálculo en una fórmula o función o bien, utilizar el valor de una celda en varias fórmulas o funciones. También puede hacerse referencia a las celdas de otras hojas en el mismo libro, a otros libros y a los datos de otros programas. Las referencias a celdas de otros libros se denominan referencias externas. Las referencias a datos de otros programas se denominan referencias remotas.

Diferencia entre referencias relativas y absolutas

Cuando se crea una fórmula o función, normalmente las referencias de celda o de rango se basan en su posición relativa respecto a la celda que contiene la fórmula o función. En el siguiente ejemplo, la celda B6 contiene la fórmula=A5; Microsoft Excel buscará el valor una celda por encima y una celda a la izquierda de B6. Este método se denomina referencias relativas.

A B

5 100

6 200 =A5

7

Si se copia una fórmula que utiliza referencias relativas, se actualizarán las referencias en la fórmula pegada y se hará referencia a diferentes celdas relativas a la posición de la fórmula. En el siguiente ejemplo, la fórmula en la celda B6 se ha copiado en la celda B7. La fórmula en la celda B7 ha cambiado a =A6, que hace

A B5 100

6 200 =A5

7 =A6

referencia a la celda que está una celda por encima y a la izquierda de la celda B7.Si no desea que cambien las referencias cuando se copie una fórmula en una celda diferente, utilice una referencia absoluta. Por ejemplo, si la fórmula multiplica la celda A5 por la celda C1 (=A5*C1) y puede copiarse la fórmula en otra celda, cambiarán ambas referencias. Puede crearse una referencia absoluta a la celda C1 colocando un signo de pesos ($) delante de las partes de la referencia que no cambia. Por ejemplo, para crear una referencia absoluta a la celda C1, agregue signos de pesos a la fórmula como se indica a continuación:

=A5*$C$1

Hay otra forma de conseguir que aparezcan los símbolos $ sin necesidad de escribirlos cada vez. Podemos conseguir los símbolos $, primero escribimos la celda y después pulsamos la tecla F4, automáticamente aparecerán los símbolos $, delante de la fila y delante de la columna.

FuncionesLas funciones son fórmulas predefinidas que ejecutan cálculos por medio de valores específicos, denominados argumentos, y que, en general, devuelven un resultado.

El uso de funciones simplifica al mismo tiempo que potencia las posibilidades en cuanto al cálculo que ofrece la planilla.La sintaxis de una función comienza por su nombre, seguido de un paréntesis de apertura, los argumentos separados por comas y un paréntesis de cierre. Si la función inicia una fórmula, entonces debe ir precedida por un signo igual (=). En general, las funciones mantienen la siguiente sintaxis:

= NOMBRE ( argumento1; argumento2; argumento3; ...; argumento30 )

NOMBRE se refiere a la denominación de una función. Cada función tiene un conjunto de argumentos válidos, los cuales deben ingresarse respetando

el orden preestablecido. Los argumentos pueden ser: valores numéricos, alfanuméricos, direcciones de celdas, valores lógicos, etc., e incluso otras funciones. En éste último caso, se dice que las funciones están anidadas.

Los argumentos se separan por punto y coma (;). Los paréntesis indican donde comienzan y donde terminan los argumentos.

FuncionesLos argumentos pueden ser valores, direcciones de celdas o rangos de celdas.SUMA ( argumento1; argumento2; ...; argumento30 )Función matemática que devuelve el resultado de la adición de los argumentos. Si el contenido de la celda es de tipo alfanumérico, se ignora, no produce mensaje de error.PROMEDIO ( argumento1; argumento2; ...; argumento30 )Función estadística que devuelve el promedio o media aritmética de los argumentos especificados.CONTAR ( argumento1; argumento2; ...; argumento30 )Función estadística que cuenta el número de celdas que contienen valores numéricos. Los argumentos que son números, fechas o representaciones textuales de números se cuentan; los valores que son de error o bien un texto que no puede traducirse a números, se pasa por alto.MAX ( argumento1; argumento2; ...; argumento30 ) y MIN ( argumento1; argumento2; ...; argumento30 )Funciones estadísticas que devuelven, respectivamente, el máximo y el mínimo de un conjunto de valores o contenidos de celdas consignados en los argumentos. Se pueden especificar argumentos que sean números, celdas vacías, valores lógicos o representaciones de números en forma de texto. Los argumentos que sean valores de error o de texto que no se puedan traducir a números causan errores. Si los argumentos no contienen números ambas funciones devuelven 0.CONTAR.BLANCO ( rango )Cuenta el número de celdas en blanco dentro de un rango. Rango es el bloque de celdas dentro del cual se desea contar el número de celdas en blanco. Las celdas que contienen fórmulas que devuelven “” (texto vacío) también se cuentan, pero no así las celdas que contienen el valor 0.

RangoEs el rango dentro del cual desea contar el número de celdas que no están en blanco.

CONTARA ( argumento1; argumento2; ...; argumento30 )Cuenta el número de celdas que no están vacías, que contienen datos. Los argumentos pueden ser cualquier tipo de información, incluyendo texto vacío ("") pero excluyendo celdas vacías.CONTAR.SI ( rango; criterio )Cuenta las celdas, dentro del rango, que no están en blanco y que cumplen con el criterio especificado.

RangoEs el rango dentro del cual desea contar el número de celdas que no están en blanco.

Criterio Es el criterio en forma de número, expresión o texto, que determina las celdas que se van a contar. Por ejemplo, el argumento criterio puede expresarse como 32; "32"; ">32" o "manzanas".

SI ( condición; valor si la condición es V; valor si la condición es F )Condición es cualquier valor o expresión que puede evaluarse como VERDADERO o FALSO. Por ejemplo, A10=100 es una expresión lógica; si el valor de la celda A10 es igual a 100, la expresión se

evalúa como VERDADERO. De lo contrario, la expresión se evalúa como FALSO. Este argumento puede utilizar cualquier operador de comparación.Valor si la condición es V es el valor que se devuelve si el argumento condición es VERDADERO. Por ejemplo, si este argumento es la cadena de texto "Dentro de presupuesto" y el argumento condición se evalúa como VERDADERO, la función SI muestra el texto "Dentro de presupuesto". Si el argumento condición es VERDADERO y el argumento valor si la condición es V está en blanco, este argumento devuelve 0 (cero). Para mostrar la palabra VERDADERO, utilice el valor lógico VERDADERO para este argumento. valor si la condición es V puede ser otra fórmula.Valor si la condición es F es el valor que se devuelve si el argumento condición es FALSO. Por ejemplo, si este argumento es la cadena de texto "Presupuesto excedido" y el argumento condición se evalúa como FALSO, la función SI muestra el texto "Presupuesto excedido". Si el argumento condición es FALSO y se omite valor si la condición es F, (es decir, después de valor si la condición es V no hay ninguna coma), se devuelve el valor lógico FALSO. Si condición es FALSO y valor si la condición es F está en blanco (es decir, después de valor si la condición es V hay una coma seguida por el paréntesis de cierre), se devuelve el valor 0 (cero). valor si la condición es F puede ser otra fórmula.

ACTIVIDADESCopie la siguiente tabla en una Hoja de cálculo.

Alumno 1er Trim 2do Trim 3er Trim Nota EvaluaciónJavier Pérez 2,5 3 5Ana Valle 9,75 8 4,25Juan Clos 6 6,25 6José González 7 4 5,5Silvia Clos 1,5 9 6Luis Sol 6 5,5 8,5Joaquín Valle 4,5 3,75 9Elena Sánchez 9 6,75 4Antonio Valverde 0,5 6 2Isabel Santi 7 7,25 6

Promedio máxima 0 Cantidad Alumnos: 10Promedio mínima 0

Cantidad %AprobadosDesaprobados

Calcular:Nota: Promedio de las notas de los trimestres.Evaluación: Si el promedio de cada alumno es mayor o igual a 6 (seis), en la columna Evaluación escribir el mensaje “Aprobado”.

Sino: Escribir el mensaje “Desaprobado”%: Calcular el porcentaje de alumnos aprobados y desaprobados (% Aprobados = Cantidad total / cantidad aprobados)