Excel 2010 Colegio de La Inmaculada

Área académica de Informática

2013

Fórmulas básicas y funciones

Recordando

¿Qué es una fórmula en Excel?

Una fórmula es una ecuación que calcula un valor nuevo a partir de los valores existentes. El resultado será asignado a la celda en la cual se introduce dicha fórmula. En Excel puede crearse una amplia diversidad de fórmulas, desde fórmulas que ejecuten una simple operación aritmética hasta fórmulas que analicen un modelo complejo de fórmulas. Una fórmula puede contener números, operadores matemáticos, referencias a celdas o incluso funciones.

¿Qué es una función?

Excel es una hoja de cálculo que nos permite poder realizar muchas operaciones de forma sencilla, mediante la identificación de las celdas mediante filas y columnas.

Una de las grandes herramientas que existen en Excel son las funciones. Estas funciones nos permiten realizar operaciones más complejas y de forma sencilla, tanto con valores numéricos o valores de texto.

Todas las funciones tienen en común, el nombre de la función la cual la identifica y una serie de argumentos, todos ellos separados por puntos y comas o comas, todos ellos dentro de paréntesis. Veremos que puede haber alguna función que no tenga ningún tipo de argumento, pero siempre existirán los paréntesis después del nombre de la función.

Para poder trabajar con las funciones es importante conocerlas todas por separado ya que cada una de ellas nos ofrece un resulta -do diferente. Sabiendo lo que deseamos y conociendo las funciones es más fácil poder encontrar la combinación de funciones con las que podemos encontrar el resultado deseado.

Más adelante podrás ver el nombre de las funciones, la explicación de la estructura con los argumentos y para que sirve cada una de ellas y un pequeño ejemplo de cómo utilizar cada función.

Conceptos y ayudas

Celda : Intersección de una columna y fila. Ejemplo: A1

Columna : Celdas consecutivas en forma vertical. La columna lleva de título una letra.

Fila : Celdas consecutivas en forma horizontal. La fila lleva de título un número.

Rango : Grupo de celdas consecutivas. Ejemplo: B5:C10 (todas las celdas desde B5 hasta C10)

Referencia : Cualquier rango o celda.

Referencias en las fórmulas

Para diferenciar los tipos de fórmulas Estáticas de Dinámicas, además de manejar referencias en las fórmulas lo haremos en forma practica, siguiendo las siguientes pautas:

1. En la celda A1 digita =3+2

2. Ubícate en la celda A1 y observa la fórmula del tipo =3+2 que se denomina Fórmula Estática, porque su trabajo no puede cambiar a no ser que la edites y modifiques manualmente los operadores o los operandos.

3. Ingresa en la celda A2 el dato 3 y en la celda B2 el dato 2, luego en la celda C2 ingresa la siguiente fórmula =A2+B2 y observa el resultado.

Página 2

4. Ubícate en la celda C2 y observa la fórmula del tipo =A2+B2 que se denomina Fórmula Dinámica, porque su resultado sí puede cambiar sin tu intervención directa. Esto se consigue introduciendo en las fórmulas referencias a otras celdas, es decir, los nombre de otras celdas.

5. Ten en cuenta que las referencias de celdas son los nombres de las celdas y se dan en forma de una letra y un número, la letra correspondiente a la columna y el número de línea en que se ubica el dato.

6. Debes saber que las referencias a celdas pueden ser de tres tipos:

Relativas :A1

Absolutas : $A$1

Mixtas : A$1 ó $A1

7. Y saber que el símbolo $ en las referencias absolutas o mixtas, sirve para fijar el elemento (línea o columna) delante de ésta.

Veamos:

La fórmula Relativa, ubicada en C2 tiene como referencia a las celdas A2 y B2 (datos ubicados a la izquierda de la fórmula). Si copias la fórmula ubicada en C2 a la celda D2, el resultado será 7 puesto que estarías tomando como referencia las celdas

B2 y C2, cuyos datos son 2 y 5 Para que el resultado sea el mismo se debe fijar la columna, recurriendo al símbolo de referencia $”, por lo tanto la fórmula

ubicada en C2 sería =$A2+$B2 a la que llamaremos Fórmula Mixta. Si copias la fórmula, teniendo fijada la columna en la celda C2 (=$A2+$B2) a la celda D3 el resultado será 0, puesto que falta-

ría fijar la fila por lo tanto la fórmula origen deberá ser: =$A$2+$B$2 a la que llamaremos “Fórmula absoluta”.

Para usar Excel correctamente y poder hacer uso de la ayuda, toma en cuenta lo siguiente:

El separador de listas o argumentos que utilizarás al momento de realizar las fórmulas puede ser una coma (,) o un punto y coma (;) depende de la configuración de Windows. Un truco para saber cual utilizar es que al empezar a escribir una función, saldrá la ayuda contextual (un letrero en amarillo) si el separador es una coma, deberás utilizar la coma para tus fórmulas, si sale punto y coma, entonces eso será lo que deberás usar.

Según la figura el separador de argumentos es una coma

Es bueno que tomes en cuenta que toda la ayuda de Excel: F1, con el asistente o con el menú.

Cuando en Excel haces fórmulas donde utilizas funciones, salen las ayudas contextuales con la sintaxis de la función. Dentro de los paréntesis de la función aparecen los argumentos, estos pueden ser varios.

Fórmulas con funciones

Para introducir una fórmula, cuenta con dos opciones:

1. Puede escribir la formula directamente en la celda que mostrará el resultado. Si el nombre de la formula es correcto Excel indicará que hace la función y al abrir el paréntesis para ingresar los argumentos de la misma, su mostrará la sintaxis.

2. Puede utilizar la ayuda de Excel (cuadro de diálogo Insertar función), este asistente mostrará el nombre de la función, los argumentos, además una descripción de la función y de cada argumento.

Reglas a seguir:

Si la función va al comienzo de una fórmula debe empezar por el signo =. Los argumentos o valores de entrada van siempre entre paréntesis. No dejes espacios antes o después de cada paréntesis. Los argumentos pueden ser valores constantes (número o texto), fórmulas o funciones. Los argumentos deben de separarse por un punto y coma (;) o coma (,)

Ejemplo: =SUMA(A1:C8)

Tenemos la función SUMA() que devuelve como resultado la suma de sus argumentos. El operador " :" nos identifica un rango de celdas, así A1:C8 indica todas las celdas incluidas entre la celda A1 y la C8, así la función anterior sería equivalente a:

=A1+A2+A3+A4+A5+A6+A7+A8+B1+B2+B3+B4+B5+B6+B7+B8+C1+C2+C3+C4+C5+C6+C7+C8

En este ejemplo se puede apreciar la ventaja de utilizar la función.

Las fórmulas pueden contener más de una función, y pueden aparecer funciones anidadas dentro de la fórmula.

Ejemplo: =SUMA(A1:B4)/SUMA(C1:D4)

Página 3

Existen muchos tipos de funciones dependiendo del tipo de operación o cálculo que realizan. Así hay funciones matemáticas y trigonométricas, estadísticas, financieras, de texto, de fecha y hora, lógicas, de base de datos, de búsqueda y referencia y de infor -mación.

Para introducir una fórmula debe escribirse en una celda cualquiera tal cual introducimos cualquier texto, precedida siempre del signo =.

Insertar función

Para insertar cualquier otra función, también podrás utilizar el asis-tente. Si quieres introducir una función en una celda:

Ubícate en la celda donde quieras introducir la función.

Haz clic en la pestaña Fórmulas Elige la opción Insertar función del grupo Biblioteca de funcio-

nes o desde (barra de fórmulas).

Aparecerá el siguiente cuadro de diálogo Insertar fun-ción:

Excel 2010 nos permite buscar la función que necesita-mos escribiendo una breve descripción de la fun-ción necesitada en el recuadro Buscar una función: y a

continuación clic sobre el botón , de esta forma no es necesario conocer cada una de las funciones que incorpora Excel ya que nos mostrará en el cuadro de lista Seleccionar una función: las funciones que tienen que ver con la descripción escri -ta.

Para que la lista de funciones no sea tan extensa podemos seleccionar previamente una categoría del cuadro combinado O se-leccionar una categoría:, esto hará que en el cuadro de lista sólo aparezcan las funciones de la categoría elegida y reduz-ca por lo tanto la lista. Si no estamos muy seguros de la categoría podemos elegir Todas.

En el cuadro de lista Seleccionar una función: hay que elegir la función que deseamos haciendo clic sobre ésta.

Observa como conforme seleccionamos una función, en la parte inferior nos aparecen los distintos argumentos y una breve descrip-ción de ésta. También disponemos de un enlace Ayuda sobre esta función para obtener una descripción más completa de dicha función.

A final, hacer clic sobre el botón Aceptar.

La ventana cambiará al cuadro de diálogo Argumentos de función, donde nos pide introducir los argumentos de la función: Este cuadro variará según la función que hayamos elegido, en nuestro caso se eligió la función SUMA ().

Página 4

En el recuadro Número1 hay que indicar el primer argumento que generalmente será una celda o rango de celdas por

ejemplo A1:B4 . Para ello, hacer clic sobre le botón para que el cuadro se haga más pequeño y podamos ver toda la hoja de cálculo, a continuación seleccionar el rango de celdas o la celda deseadas como primer argumento (para seleccionar un rango de celdas haz clic con el botón izquierdo del ratón sobre la primera celda del rango y sin soltar el botón arrástralo hasta la última celda del rango) y pulsar la tecla ENTER para volver al cuadro de diálogo.

En el recuadro Número2 habrá que indicar cuál será el segundo argumento. Sólo en caso de que existiera.

Si introducimos segundo argumento, aparecerá otro recuadro para el tercero, y así sucesivamente.

Cuando tengamos introducidos todos los argumentos, hacer clic sobre el botón Aceptar.

Si por algún motivo insertáramos una fila en medio del rango de una función , Excel expande auto-máticamente el rango incluyendo así el valor de la celda en el rango. Por ejemplo: Si tenemos en la celda A5 la función =SUMA(A1:A4) e insertamos un fila en la posición 3 la fórmula se expandirá automáticamente cambiando a =SUMA(A1:A5).

Excel permite que en una función tengamos como argumentos expresiones, por ejemplo la suma de dos celdas (A1+A3). El orden de ejecución de la función será primero resolver las expresiones y después ejecutar la función sobre el resultado de las expresio -nes.

Por ejemplo, si tenemos la siguiente función =Suma((A1+A3);(A2-A4)) donde:

A1 vale 1A2 vale 5A3 vale 2A4 vale 3

Excel resolverá primero las expresiones (A1+A3) y (A2-A4) por lo que obtendremos los valores 3 y 2 respectivamente, después realizará la suma obteniendo así 5 como resultado.

Sigamos aprendiendo

Funciones de ExcelEn la sintaxis y ejemplos se utilizará la coma (,) como separador de argumentos y el punto (.)como separador decimal.

Son los valores que aparecen en el rango seleccionado en una hoja de Excel

Página 5

Empieza a trabajar:

- Abre el archivo E01.- Graba el archivo con el nombre 2?XXbim3-E01 (?=sección y XX= Nº de orden) para

hacer los ejercicios que vienen más adelante.

Nota: El separador de argumentos puede ser(;) y el separador decimal la (.), según configuración. Ingresa un espacio en la celda B3

Recordemos practicando con la hoja Operadores del archivo 2?XXbim3-E01 (?=sección y XX= Nº de orden)

Operadores aritméticos

+ Suma =A1+15

- Resta =A2-B1

* Multiplicación =2*C4

/ División =A1/B3

^ Potenciación =D5^2

^ Raiz =B5^(1/2) es √B5

Operadores lógicos

< Menor que =10<A2

> Mayor que =2>A4

<= Mayor o igual que =10>=(D1+D1)

= Igual que =2=(C6/B1)

<> Diferente que =<>A2

Importante:

Todas las fórmulas empiezan con el signo igual (=) Nunca escribas espacios en blanco dentro de las fórmulas (a menos que estés escribiendo un texto entre comillas).

Funciones matemáticas y trigonométricasTrabaja con la Hoja Funciones del archivo 2?XXbim3-E01 (?=sección y XX= Nº de orden)

ENTERO

Devuelve sólo la parte entera de un valor, los decima-les se pierden.Sintaxis: ENTERO(Valor)

Ejemplo: Si escribes la fórmula =ENTERO(5.4) obtendrás como resultado el valor 5. Es importante notar que cualquier cálculo que hagas con esta función no considera los decimales.

=Entero(suma(A1:E1)/5)

Respuesta: 10

=Entero(E5) el número es 10.9

Respuesta:10

Ejemplo:

=Entero(Suma(E4:E6)) en este caso, SUMA(A4:E6) da como resultado el valor 31.8

Respuesta:31

Todas las fórmulas se realizarán con los datos que muestra la imagen.

Página 6

POTENCIADevuelve el resultado de elevar el número a una potencia

Sintaxis: POTENCIA(número,Potencia)

Ejemplo:

=POTENCIA(C6,2)

Es lo mismo que hacer: =C6^2

Respuesta: 64

PRODUCTO

Multiplica todos los números de uno o varios rangos y devuelve el producto.

Sintaxis: PRODUCTO(número1,[número2],…)

Ejemplo:

=Producto(B1:B4,D1)

Es lo mismo que hacer =B1*B2*D1 (Excel omite las celdas vacías y no las incluye en la fórmula)

Respuesta 20

RAIZDevuelve la raíz cuadrada de un número

Sintaxis: RAIZ(número)

Hallar la raíz cuadrada de 4.

Ejemplo:

=RAIZ(B1) Es lo mismo que hacer: =B1^(1/2)

Respuesta: 2

REDONDEARNos devuelve un número con una cantidad de decimales que nosotros le indiquemos. Este valor estará redondeado.

Sintaxis: REDONDEAR(número;núm_decimales)

Ejemplo:Si escribimos esta función: =REDONDEAR(4,6365;2) obtendremos como resultado: 4,64. Observa que el tercer seis ha hecho redondear el 3 a un 4. Es importante hacer notar que esta función recorta decimales y a la vez redondea.

=Redondear(E3,1)

Respuesta: 10.5

Ejemplo:

=Redondear(Promedio(E3:E6),0) PROMEDIO(E3:E6) da como resultado el valor 10.5732

Raíz aritmética

Página 7

Respuesta: 11

Ejemplo:

=Redondear(suma(A1:A3),2) El resultado sale sin decimales porque Suma(A1:A3) no tiene decimales en la respuesta:

Respuesta: 36

Uso del botón Autosuma y función sumaEs una herramienta de Excel que permite generar la suma de los datos que se encuentran en una fila o columna determinada.

Sigamos trabajandoa) Ingresa a partir de la celda A4 hacia abajo los si-

guientes datos: 14, 10, 50 y 20b) Ubícate en la celda A8c) Pulsa el botón Autosuma ubicado en el grupo

Modificar de la ficha Inicio. Observa el resultado.d) Ahora nuevamente da clic en el botón Autosuma y

verás el resultado final.e) Acabas de sumar un conjunto de datos numéricos.f) Ahora da clic en la celda A8 y observa la Barra de

fórmulas.Nota.- Para realizar la suma, también podrías haberte ubicado en la celda A8 y digitar:

=SUMA(A4:A7)

Observa en la imagen la sintaxis de la función SUMA.

Entonces podrías realizar una fórmula de la siguiente manera: =suma(A4:A7,B1:B4) y de esa manera estarías sumando todos los valores del rango A4:A7 (14,10,50 y 20) y los valores del rango B1:B4 (14, 11, 24 y 6) que darían un resultado de 149.

Trabaja con la Hoja Suma del archivo 2?XXbim3-E01 (?=sección y XX= Nº de orden)

AHORA

En la Hoja Suma del archivo 2?XX-E01-Bim3 (?=Sección / XX= Nº de orden), sigue las siguientes indicaciones:

Ubica el cursor en la celda F4, para hallar el Puntaje x Asig. de los cuatro bimestres por cada curso con el botón

Autosuma . Ejecuta el mismo proceso para las siguientes celdas de la columna F hasta la celda F13.

Realiza fórmulas aritméticas para obtener el Promedio X Asig., Puntaje final y el promedio final con decimales de las columna B, C, D, E, F y G.

Página 8

FUNCIONES ESTADÍSTICASTrabaja con la Hoja Funciones del archivo 2?XX-bim3-E01 (?=sección y XX= Nº de orden)

CONTARCuenta todas las celdas ocupadas por números( o fórmulas que dan como resultado números) de uno o varios rangos.

Sintaxis: CONTAR(valor1,[valor2],…)

Ejemplo:=CONTAR(A1:A6,C1:C6)Respuesta:10

CONTAR.BLANCOCuenta todas las celdas en blanco (vacías) de un rango. También cuenta las celdas que como resultado de una fórmula dan vacío (“””).

Sintaxis: CONTAR.BLANCO(rango)

Ejemplo:=contar.blanco(A1:E6)Respuesta:2Nota: si borras el carácter espacio ubicado en la celda B3, el resultado será 3

CONTAR.SICuenta todas las celdas de un rango que cumplan con un criterio. El criterio es una característica que se debe cumplir para que una celda sea considerada en el contero. Los criterios se escriben entre comillas.

Sintaxis: CONTAR.SI(rango;criterio)

Ejemplo:Imagina que de una lista de datos en los que aparecen los días de la semana queremos saber cuantos de estos se producen los lunes. Para ello simplemente deberemos introducir la función: =CONTAR.SI(A3:A9;"Lunes") donde se especifica que de la A3 a la A9 tengo todos los datos y en el siguiente argumento lo que deseamos contar si estos datos son Lunes.=CONTAR.SI(A1:B6,"<>10")Respuesta: 11

Nota: Cuenta todas las celdas diferentes, incluyendo la celda vacía.

=Contar.si(A1:E6,”=10”) Contar todas las celdas que sean igual a 10Respuesta: 2=Contar.si(A1:A6,”Hola”) Contar todas las celdas que tengan la palabra holaRespuesta: 1

Observa el resultado

Página 9

=Contar.si(A1:E6,”=h*”) Contar todas las celdas que tengan palabras que empiezan con h (el asterisco funciona como comodín)Respuesta: 1=Contar.si(E34:E4,”<>0”) Contar todas las celdas que tengan números que no sean ceroRespuesta: 2

CONTARACuenta todas las celdas ocupadas de uno o varios rangos.(Recuerda que si escribes en una celda espacios con la barra espaciadora y presionas ENTER, la celda estará ocupada de espa-cios en blanco y la función CONTARA la incluirá en la cuenta)

Sintaxis: CONTARA(valor1,[valor2],…)

Ejemplo:=contara(A1:B6,E1:E6)Respuesta:16Nota: la celda B3 esta ocupada por un espacio.

MAXEncuentra el número mayor de uno o varios rangos.

Sintaxis: MAX(número1,[número2],…)

Ejemplo:=Max(A1:E3,A6)Respuesta:50

MINEncuentra el número menor de uno o varios rangos.

Sintaxis: MAX(número1,[número2],…)

Ejemplo:=Min(A1:E3,A6)Respuesta:-10

PROMEDIO

Devuelve la media aritmética de la lista de valores.

Sintaxis: PROMEDIO(número1;[número2];...)

Ejemplo: =PROMEDIO(5;5;2) devuelve 4=Promedio(A1:A3)

Respuesta:12

=promedio(C1:C3)

Respuesta:0.66666667

=promedio(A1:E6)

Respuesta: 12.2803846

Nota: Ver más en: http://conociendoexcel2010.jimdo.com

Página 10

Página 11