Funciones en Excel

Usar
plantillas

Una plantilla es un modelo que puede servir
como base para muchas hojas de cálculo. Puede incluir
tanto datos como formatos.

Para crear un libro de trabajo utilizando
plantillas:

Seleccionar el Botón Office
.

Elegir la opción Nuevo…

Hacer clic sobre la categoría
Plantillas instaladas en el cuadro de diálogo Nuevo
Libro.

Aparecerán las plantillas
instaladas, si no lo estuviesen habría que volver a
instalar Excel2007 con las opciones de plantillas
activadas.

Seleccionar el tipo de plantilla deseada,
como Factura o Informe de Gastos.

Hacer clic sobre el botón
Crear.

Posiblemente nos avise de la
habilitación de macros junto con la plantilla, y al final
aparecerá una copia de nuestro modelo elegido.

Rellenar la plantilla.

Al guardar nuestro modelo, nos
aparecerá el cuadro Guardar como para tener siempre
la plantilla original.

 Crear
plantillas

Para crear una plantilla, seguir los
siguientes pasos:

Crear un libro de trabajo con todos los
datos y los formatos que serán comunes a todos los libros
de trabajo creados a partir de esta plantilla.

Seleccionar el Botón
Office.

Elegir la opción Guardar
como…

Escribir el nombre de la plantilla en el
recuadro Nombre de archivo.

En el recuadro Guardar como tipo, hacer
clic sobre la flecha de la derecha para que se abra la lista
desplegable y elegir la opción Plantilla de
Excel.

Hacer clic sobre el botón
Aceptar.

Excel2007 cambia automáticamente a
la carpeta de plantillas, para que tu nueva plantilla esté
siempre disponible al seleccionar la opción Nuevo del
Botón Office.

Fórmulas y
Funciones (I)

En la comprensión y manejo de las fórmulas
y funciones está la base de Excel. ¿Qué es
una hoja de cálculo sino una base de datos que utilizamos
con una serie de fórmulas para evitar tener que recalcular
por cada cambio que hacemos?

Introducir Fórmulas y Funciones

 Una función es una fórmula
predefinida por Excel 2007 (o por el usuario) que opera con
uno o más valores y devuelve un resultado que
aparecerá directamente en la celda o será utilizado
para calcular la fórmula que la contiene.

La sintaxis de cualquier función es:

nombre_función(argumento1;argumento2;…;argumentoN)

Siguen las siguientes reglas:

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

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)

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 información.

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

Operadores más utilizados en las
fórmulas y funciones

Excel permite que en una función tengamos varios
operadores para tratar los datos. Los operadores son
símbolos que identifica Excel con operaciones
aritméticas y es el enlace entre 2 argumentos.

En la tabla podemos ver los operadores más
utilizados.

En una fórmula o función pueden utilizarse
tanto operadores como sea necesario teniendo en cuenta siempre
que los operadores hacen siempre referencia a dos argumentos.
Pueden crearse fórmulas verdaderamente complejas. Veamos
un ejemplo:

=((SUMA(A1:A7)*SUMA(B1:B7)) /
(SUMA(C1:C7)*SUMA(D1:D7))) = (F8*SUMA(G1:G5))

 Precedencia de los
operadores

Hemos visto que una fórmula puede ser muy
compleja, utilizando multitud de operadores. Excel como cualquier
operador matemático tiene unas ciertas reglas para saber
que operaciones debe realizar primero para que el resultado
obtenido sea el correcto.

En la siguiente tabla mostramos las precedencias
establecidas por Excel.

Además de esta tabla de precedencias, la
precedencia máxima, es decir la operación que antes
se evalúa, es aquella que va entre
paréntesis.

Podemos que hay 10 operaciones

– 5 SUMAS

– 3 MULTIPLICACIONES

– 1 DIVISIÓN

– 1 COMPARACIÓN

Primero resolvería por separado las operaciones
de SUMA, después realizaría las operaciones
de MULTIPLICACIÓN, seguidamente realizaría
la DIVISIÓN y por último la
COMPARACIÓN.

Insertar funciones con el asistente

Una función como cualquier dato se puede escribir
directamente en la celda si conocemos su sintaxis, pero Excel
2007 dispone de una ayuda o asistente para utilizarlas,
así nos resultará más fácil trabajar
con ellas.

Si queremos introducir una función en una
celda:

Aparecerá el siguiente cuadro de diálogo
Insertar función:

Excel 2007 nos permite buscar la función que
necesitamos escribiendo una breve descripción de la
función necesitada en el recuadro

Para que la lista de funciones no sea tan extensa
podemos seleccionar previamente una categoría del cuadro
combinado O seleccionar una categoría:, esto hará
que en el cuadro de lista sólo aparezcan las funciones de
la categoría elegida y reduzca 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 descripció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.

Justo por debajo de la barra de fórmulas
aparecerá el 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 ().

En el recuadro Número1 hay que indicar el primer
argumento que generalmente será una celda o rango de
celdas tipo A1:B4 .

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
automá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).

Referencias

Cuando trabajamos en Excel y más concretamente
cuando hacemos usos de fórmulas y funciones casi es seguro
que pongamos referencias a celdas o conjunto de celdas que no son
propiamente la misma celda donde tenemos la
fórmula.

Las referencias son enlaces a un lugar, es decir, cuando
en una formula escribimos =SUMA(A1;B1) nos estamos refiriendo a
que sume el contenido de A1 y el contenido de
B1.

Existen 3 tipos de referencias:

• Referencia Relativa: Las referencias de filas
  y columnas cambian si se copia la fórmula en otra
  celda, es decir se adapta a su entorno porque las referencias
  las hace con respecto a la distancia entre la formula y las
  celdas que forman parte de la formula. Esta es la
  opción que ofrece Excel por defecto.

Supongamos el ejemplo:

Si ahora copiamos la celda A2 en B3, como la copiamos
una columna hacia la derecha y en una fila hacia abajo, la
fórmula cambiará por: =B2+2. Lo que
variará es la referencia a la celda A1, al copiarla
una columna hacia la derecha se incrementará el nombre de
la columna en uno, es decir, en vez de A pondrá B y al
copiarla una fila hacia abajo en vez de fila 1 pondrá 2 ,
resultado =B2+2 . Para mantener en la fórmula sumar 2 al
contenido de la celda superior.

• Referencia Absoluta: Las referencias de filas
  y columnas no cambian si se copia la fórmula a otra
  celda, las referencias a las celdas de la formula son
  fijas.

Supongamos el ejemplo:

Si ahora copiamos la celda A2 en B3 , aunque la copiemos
una columna hacia la derecha y en una fila hacia abajo, como
delante de la columna y delante de la fila encuentra en signo
$ no variará la fórmula y en B3
pondrá =$A$1+2.

• Referencia Mixta: Podemos hacer una
  combinación de ambas referencias, podemos hacer que
  las filas sean relativas y las columnas absolutas o
  viceversa.

Supongamos el ejemplo:

Si ahora copiamos la celda A2 en B3 , como hay un signo
$ delante de la columna aunque se copie una columna más a
la derecha ésta no variará, pero al no tener el
signo $ delante de la fila, al copiarla una fila hacia abajo la
fila cambiará por 2 en vez de 1 y el resultado
será  =$A2+2 .

Supongamos el ejemplo:

Si ahora copiamos la celda A2 en B3 , como hay un signo
$ delante de la fila aunque se copie una fila hacia abajo
ésta no variará, pero al no tener el signo $
delante de la columna, al copiarla una columna más a la
derecha la columna cambiará por B en vez de A y el
resultado será =B$1+2 .

Como cambiar el tipo de referencia

Una opción para cambiar el tipo de referencia una
vez sabemos distinguir entre los diferentes tipos de referencias
que existen y la que más nos interesa en cada momento es
hacerlo a mano.

Las referencias relativas se escriben tal cual vemos la
intersección de la celda con la columna y la fila (A2, B3,
D1…).

Para que la referencia sea absoluta, es decir que sea
fija, debemos anteponer a la columna y a la fila el signo $
($A$2, $B$3, $D$1…).

Para las referencias mixtas como hemos dicho puede ser
una mezcla entre relativa y absoluta por tanto pueden ser de este
tipo ($A2, B$3, $D1…).

Otra opción, en lugar de escribirlo a mano es
hacerlo cuando estemos editando la formula, en el momento en el
que se incluyan las celdas referenciadas podemos pulsar sobre la
tecla F4 y vemos que va cambiando a los posibles tipos de
referencias que podemos hacer con la celda.

Referencias a otras hojas o libros

Otra funcionalidad muy interesante de las referencias es
la posibilidad de escribir referencias a celdas que se encuentran
en otras hojas o incluso en otros libros.

• Referencia a otras hojas.

Para hacer referencia a celdas de otras hojas debemos
indicar el nombre de la hoja seguido del signo de
exclamación y el nombre de la celda.

Por ejemplo: Hoja2!A2 esta referencia está
diciendo que coja la celda A2 de la hoja Hoja2.

Si la hoja tuviera un nombre personalizado con espacios
incluidos, la referencia sería de este modo 'Nombre de la
hoja externa'!A2, habría que encerrar el nombre de la hoja
entre comillas simples ' '.

• Referencia a otros libros.

Para hacer referencia a celdas de otros libros debemos
indicar el nombre del libro entre corchetes y el resto como
acabamos de ver.

Por ejemplo: '[presupuesto 2007]Hoja1'!B2 esta
referencia indica que la celda se encuentra en el libro
"Presupuesto 2007", en la Hoja1 y en la celda B2.

 Muy importante: Fíjense bien que al
escribir una cadena de caracteres que incluya espacios debemos
ponerlo siempre entre comillas simples ' '.

Nosotros los humanos estamos más acostumbrados a
nombrar las cosas por su nombre que por números de
referencia, lo vemos todo mucho más claro e intuitivo.
Podemos ver una fórmula relativamente sencilla pero si
contiene muchas referencias nos puede costar
interpretarla.

Por eso Excel nos facilita un poco las cosas permitiendo
que podamos ponerles nombre a las celdas y a las fórmulas
para así identificarlas más
fácilmente.

• Para dar nombre a una celda, debemos lanzar
  el Administrador de Nombres haciendo clic en el
  botón Administrador de nombres de la
  pestaña Fórmulas, o pulsar la
  combinación de teclas Ctrl+F3.

Se abrirá el siguiente cuadro de
diálogo:

Aquí encontraremos un listado de todos los
nombres que hayamos creado y a qué celda o rango de celdas
hacen referencia.

Para crear un nuevo nombre sólo tienes que hacer
clic en el botón Nuevo.

Se abrirá el cuadro de diálogo Nombre
nuevo como vemos en la imagen.

En el recuadro Nombre: escribimos el nombre que
le queremos dar a la celda.

En Hace referencia a: escribimos la referencia de
la celda como vemos en la imagen.

Es importante escribir el signo igual y utilizar
referencias absolutas ($A$1).

Excel rellena de forma automática el cuadro
Hace referencia a: con la referencia de la celda activa,
por lo que es más cómodo posicionarse primero en la
celda a la cual queremos asignar un nombre y luego abrir el
cuadro de diálogo Definir nombre, así ya
tendremos el cuadro rellenado con la referencia
correcta.

• Crear una constante.

Una constante es un elemento que siempre tiene el mismo
valor, por ejemplo la constante matemática pi
siempre tiene el mismo valor " 3,1415…" o nuestro propio
nombre.

Podemos crear constantes sin necesidad de utilizar una
celda en nuestra hoja, y pueden ser constantes numéricas o
de texto.

Para crear una constante abrimos de igual modo el cuadro
de diálogo Nombre nuevo seleccionando la
opción Administrador de Nombres y luego haciendo
clic en Nuevo.

En el recuadro Nombre: escribimos el nombre que
le queremos dar a la constante.

En el recuadro Hace referencia a: escribimos el
valor de la constante sin incluir el signo =.

Para finalizar pulsamos sobre Aceptar.

• Del mismo modo podemos crear fórmulas
  con nombres, y sin necesidad de hacer uso de celdas para
  guardar esa fórmula. Abrimos de igual modo el cuadro
  diálogo Nombre nuevo, escribimos el nombre de
  la fórmula y en el campo Hace referencia a:
  escribimos la fórmula, en este caso sí es
  necesario el signo =.

Luego podremos utilizar ese nombre en vez de escribir la
fórmula.

También si tenemos fórmulas ya creadas en
celdas de la hoja podemos darles nombre, siguiendo los mismos
pasos que hemos visto y en campo Hace referencia a:
indicar dónde se encuentra la fórmula con la
nomenclatura
=Hojadondeseencuentra!Celda.

Después, para hacer referencia a esa
fórmula desde otra celda pondremos
=nombreformula.

Por ejemplo: Escribimos la fórmula
=SUMA(A1:A8) en la celda B1 y le damos el nombre
Sumar a la celda, después en la celda D5
podemos escribir simplemente =Sumar y
realizará la operación de la fórmula que
hemos definido, en este caso como hemos utilizado referencias
relativas, sumará de la celda C5 a la celda
C13.

Cuando asignamos nombres a diversas celdas, las
fórmulas que hagan referencia a estas celdas no se cambian
automáticamente sustituyendo la nomenclatura de
columna-fila por el nombre.

Por ejemplo: Si tenemos la formula
=A1+B1 y posteriormente asociamos la celda A1 con
el nombre Gasto1 y la celda B1 con el nombre Gasto2. En la
fórmula continuará saliendo
=A1+B1.

Para que los nombres se actualicen en todas las
referencias debemos ir a la pestaña Formulas,
desplegar el menú que se encuentra en la opción
Asignar un nombre a un rango y selecciona Aplicar
nombres….

En el cuadro de diálogo Aplicar nombres
que nos aparece debemos seleccionar los nombres que queramos
aplicar y hacemos clic en Aceptar.

Ahora en la fórmula veremos
=Gasto1+Gasto2, que queda bastante más
claro si sabemos a qué celdas hacen referencia los nombres
Gasto1 y Gasto2.

• Utilizar Expresiones como argumentos
  de las Funciones

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

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

A1 vale 1

A2 vale 5

A3 vale 2

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

• Utilizar Funciones como argumentos de las
  Funciones

Excel también permite que una función se
convierta en argumento de otra función, de esta forma
podemos realizar operaciones realmente complejas en una simple
celda. Por ejemplo =MAX(SUMA(A1:A4);B3) , esta
fórmula consta de la combinación de dos funciones,
la suma y el valor máximo. Excel realizará primero
la suma SUMA(A1:A4) y después calculará el
valor máximo entre el resultado de la suma y
la celda B3.

Dentro de todo el conjunto de funciones, se encuentran
las funciones dedicadas al tratamiento de fechas y
horas.

En varias funciones el argumento que se le pasa o el
valor que nos devuelve es un "número de serie".
Pues bien, Excel llama número de serie al número de
días transcurridos desde el 0 de enero de 1900 hasta la
fecha introducida, es decir coge la fecha inicial del sistema
como el día 0/1/1900 y a partir de ahí empieza a
contar, en las funciones que tengan núm_de_serie como
argumento, podremos poner un número o bien la referencia
de una celda que contenga una fecha.

Practicar el uso de las funciones de fechas y
horas en Excel2007.

• Vamos a calcular nuestra edad.

• 1. Sitúate en la celda
  D1 y escribe tu fecha de nacimiento en
  formato (dia/mes/año)

• 2. En la celda E1 escribe
  =HOY()

• 3. En la celda E2 selecciona la
  función DIAS360, como fecha inicial la
  celda D1 (fecha nacimiento), como fecha final
  E1 (el día de hoy) y en método
  escribe Verdadero. Como resultado nos aparece
  los días transcurridos desde la fecha D1 y la
  fecha E1.

• 4. Ahora en la celda F3 escribe
  =E2/360 para obtener los
  años.

El resultado aparece con decimales, para que nos salga
solo la parte entera podemos utilizar la función
=ENTERO(E2/360).

Hemos utilizado cuatro de las funciones más
utilizadas y que ofrecen muchas posibilidades.

• 5. Guarda el libro de trabajo en la carpeta
  Mis documentos del disco duro con el nombre
  de Funciones con fechas.

• 6. Cierra el libro de trabajo.

Una hoja de cálculo está pensada para
manejarse dentro del mundo de los números, pero Excel
también tiene un conjunto de funciones específicas
para la manipulación de texto. Estas son todas las
funciones de texto ofrecidas por Excel.

Practicar el uso de las funciones de texto en
Excel2007.

• Ahora vamos a crear una frase concatenando varias
  celdas.

• Vamos a utilizar ahora la Función
  NOMPROPIO() que también es de gran utilidad
  para formatear una cadena de texto.

• Debemos obtener en la celda G6 Pepe Gutierrez
  Ruiz. Nos ha puesto las iniciales en
  mayúsculas.

• 1. Guarda el libro de trabajo en la carpeta
  Mis documentos del disco duro con el nombre
  Funciones con texto.

• 2.  Cierra el libro de trabajo.

En una hoja de Excel es muy importante seleccionar o
buscar los datos correctos para trabajar con las fórmulas
diseñadas. Por eso existe una agrupación de
funciones específicas para realizar búsquedas de
datos.