Capítulo 5. Utilizar fórmulas y funciones

. . . . . . . . . . . .

Esta parte trata de temas relacionados con fórmulas y funciones. Las fórmulas permiten calcular resultados a partir de los datos almacenados en la hoja de cálculo, lo que hace de las hojas unas herramientas muy útiles. Las funciones son herramientas integradas o personalizadas que se utilizan en las fórmulas. Hacen que las fórmulas realicen operaciones muy eficaces que te ahorrarán un valioso tiempo.

En esta parte. . .

• Utilizar referencias absolutas, relativas y mixtas

• Convertir fórmulas en valores

• Introducir funciones en fórmulas

• Hacer referencia a celdas o rangos en otras hojas o libros

. . . . . . . . . . . .

Referencias absolutas, relativas y mixtas

Si copias una fórmula con una referencia absoluta, Excel no ajusta la referencia en la celda copiada (una referencia absoluta utiliza dos signos de dólar en su dirección, uno para la columna y otro para la fila). Por otra parte, Excel ajusta las referencias relativas tal y como has copiado la fórmula.

Puedes utilizar también las referencias mixtas, en las que solo una parte de la dirección es absoluta. La tabla siguiente resume los posibles tipos de referencias de celda y muestra cómo cambia una referencia si la copias una celda arriba o abajo. En cualquier caso, la parte absoluta de la referencia no cambia.

__

Tipo	Ejemplo	Abajo	Arriba
Referencia relativa	=A1	=A2	=B1
Referencia absoluta	=$A$1	=$A$1	=$A$1
Referencia mixta (la columna es absoluta)	=$A1	=$A2	=$A1
Referencia mixta (la fila es absoluta)	=A$1	=A$1	=B$1

__

Para cambiar el tipo de referencia de celda:

1. Haz un doble clic en la celda con la fórmula (o pulsa F2) para pasar al modo de edición.

2. En la celda o en la barra de fórmulas, clica con el puntero del ratón en la referencia de celda.

3. Pulsa F4 varias veces para recorrer todos los tipos de referencia de celda posibles. Para cuando la referencia de celda muestre el tipo que estás buscando.

4. Pulsa Entrar para completar la operación.

Fundamentos básicos de las fórmulas

Las fórmulas de Excel empiezan siempre con un signo igual. Una fórmula puede tener hasta 8192 caracteres e incluir cualquiera de los elementos siguientes:

• Operadores como + (para sumar) y * (para multiplicar)

• Referencias absolutas, relativas y mixtas, como $A$1 o B2:G10

• Referencias nombradas, como Ingresos o Ganancias

• Valores, texto o valores lógicos

• Funciones de hojas de cálculo (como SUMA o PROMEDIO)

En las versiones anteriores al Excel 2007, una fórmula no tenía más de 1024 caracteres.

Una vez que has introducido una fórmula en una celda aparece el resultado. Al activar la celda, la fórmula se ve en la barra de fórmulas (la barra de fórmulas está debajo de la Cinta de opciones).

La precedencia de operador es el conjunto de reglas que utiliza Excel para efectuar los cálculos en una fórmula. La siguiente tabla muestra la lista de operadores que pueden utilizarse en las fórmulas y señala la precedencia de cada operador.

__

Operador	Nombre	Precedencia
^	Potenciación (elevado a un exponente)	1
*	Multiplicación	2
/	División	2
+	Suma	3
–	Resta	3
&	Concatenación (une texto)	4
=	Igual a	5
>	Mayor que	5
<	Menor que	5

__

La tabla muestra que la potenciación tiene la máxima precedencia (es decir, se realiza en primer lugar) y las comparaciones lógicas tienen la prioridad más baja. Si dos operadores tienen la misma precedencia, Excel calcula de izquierda a derecha.

Recuerda: Puedes modificar la precedencia de un operador mediante el uso de paréntesis en la fórmula. En la fórmula =(Ingresos–Gastos)*TipoImpositivo, por ejemplo, a los Ingresos se le restan los gastos y el resultado se multiplica por el TipoImpositivo.

Cambiar cuando la fórmula ya está calculada

Cuando el modo de cálculo de Excel está en automático (predeterminado), si cambias las celdas de la fórmula, aparece automáticamente un nuevo resultado.

Para establecer el modo de cálculo manual, clica en la ficha Fórmulas de la Cinta de opciones y, después, en el botón Opciones para el cálculo en el grupo Cálculo. Selecciona Manual en el menú.

Recuerda: Si estás trabajando en el modo manual de cálculo, Excel muestra en la barra de estado Calcular si hay fórmulas que aún no se han calculado. Las siguientes acciones sirven para volver a calcular las fórmulas:

• Clica en la ficha Fórmulas de la Cinta de opciones, después en el botón Calcular ahora en el grupo Cálculo o pulsa F9. Las fórmulas en todos los libros abiertos se calculan al instante.

• Clica en la ficha Fórmulas de la Cinta de opciones, después en el botón Calcular hoja en el grupo Cálculo o pulsa Mayús+F9. Se calculan inmediatamente las fórmulas en la hoja de cálculo activa La operación no se produce en las demás hojas del libro.

Recuerda: El modo de cálculo de Excel no corresponde a una hoja de cálculo en particular. Si cambias el modo de cálculo, el cambio afecta a todos los libros abiertos y no solo al libro activo.

Para volver al modo de cálculo automático, clica en la ficha Fórmulas de la Cinta de opciones y, después, en el botón Opciones para el cálculo en el grupo Cálculo. Selecciona Automático en el menú.

Convertir fórmulas en valores

Puede que te interese convertir la fórmula a su valor actual (quitar la fórmula y dejar solo el resultado). Por ejemplo, si quieres evitar futuros cambios en el valor de una celda a consecuencia del cambio de las celdas de la fórmula. Para convertir una fórmula a su valor actual:

1. Selecciona la celda que tiene la fórmula. Para convertir varias fórmulas, puedes seleccionar un rango.

2. Clica en el botón Copiar en la ficha Inicio de la Cinta de opciones. (También puedes pulsar Crtl+C o hacer un clic derecho en la celda y seleccionar Copiar en el menú contextual).

3. Clica en la parte de la flecha del botón Pegar en la ficha Inicio de la Cinta de opciones y selecciona una opción en la sección Pegar valores de la galería Opciones de pegado.

4. Pulsa Entrar o Esc para salir del modo de copia.

Recuerda: Este procedimiento sobrescribe las fórmulas. Para poner los valores actuales de las fórmulas en otra área (vacía) de la hoja de cálculo, selecciona una celda nueva o la celda superior izquierda de un rango antes del tercer paso.

Editar las funciones de las fórmulas

Después de crear una fórmula con una o más funciones, quizá quieras cambiar más adelante los argumentos de las funciones. Hay varios métodos para editar una función. El método seleccionado es una elección personal y depende de la complejidad de la función.

Cualquiera de estas técnicas te sirve para editar una función:

• Si la fórmula tiene una sola función o si la función que quieres modificar es la última en la fórmula, clica en el botón Insertar función en la barra de fórmulas (o en la ficha Fórmulas de la Cinta de opciones), o pulsa Mayús+F3 para mostrar el cuadro de diálogo Insertar función.

• Si la fórmula tiene más de una función, pulsa F2 o haz doble clic en la celda de la fórmula. Coloca el cursor dentro de la función que se va a modificar y clica en el botón Insertar función, o pulsa Mayús+F3.

• La forma más eficaz de editar funciones simples (es decir, con pocos argumentos) es hacerlo de forma manual. Las funciones tienen una información de pantalla (texto flotante) que ayuda a identificar los nombres y ordena los argumentos de la función.

Lee el apartado siguiente “Introducir funciones en fórmulas”.

Introducir funciones en fórmulas

Excel ofrece más de 300 funciones integradas con las que puedes realizar operaciones muy eficaces que te ahorrarán un valioso tiempo. Las funciones realizan las tareas siguientes:

• Simplifican las fórmulas.

• Hacen que las fórmulas realicen cálculos que de cualquier otro modo serían imposibles.

• Permiten la ejecución condicional de fórmulas dándoles cierta capacidad básica de toma de decisiones.

La mayoría de las funciones de una hoja de cálculo utilizan uno o varios argumentos entre paréntesis. Un argumento es la información que aclara lo que tiene que hacer la función. Por ejemplo, la siguiente función (que redondea el número de la celda A1 con dos decimales) utiliza dos argumentos:

=REDONDEAR(A1,2)

Recuerda: Puedes anidar una función dentro de otra. La fórmula =SUMA(MAX(B1:B6),REDONDEAR(A1,2)), por ejemplo, anida las funciones MAX y REDONDEAR en la función SUMA. En este ejemplo se suma el valor máximo del rango B1:B6 al valor de la celda A1 y se redondea con dos decimales.

Lee el apartado anterior “Editar las funciones de las fórmulas”.

Introducir funciones manualmente

Si no estás familiarizado con las funciones, escribe en primer lugar el nombre de la función y la función Autocompletar fórmula desplegará una lista de posibles coincidencias, como puedes ver en la figura 5-1.

Figura 5-1

Si sigues escribiendo sin seleccionar un nombre de la lista, Excel reduce las opciones disponibles. Si quieres seleccionar un nombre de función de la lista, puedes hacer doble clic con el ratón o resaltar el nombre (con el ratón o las teclas de flecha) y pulsar la tecla Tab. Excel inserta la función en la fórmula e incluye un paréntesis de apertura. El programa facilita ayuda para las funciones en una lista desplegable en forma de información en pantalla (mira la figura 5-1). En cada una hay un resumen del uso de la función.

Después de escribir el nombre de la función manualmente (incluidos los paréntesis de apertura) o de seleccionar la función en la lista desplegable, aparece más ayuda. Esta ayuda te permite determinar el número y orden de los argumentos en la función. Aparece una información en pantalla por debajo de la función que estás escribiendo. La ayuda en pantalla incluye el nombre de la función seguido de los nombres de todos los argumentos obligatorios y opcionales entre paréntesis. Para abrir una ventana de Ayuda para la función, clica en el nombre de la función en la ayuda en pantalla.

Lee en el capítulo 4 el apartado “Introducir fórmulas manualmente”.

Utilizar la herramienta Autosuma

La herramienta Autosuma facilita un método rápido para añadir las funciones más comunes.

Para utilizar esta herramienta:

1. Clica en una celda situada debajo o a la derecha de un rango de números que haya que sumar.

2. Y después:

• Para sumar el rango, clica en el botón Autosuma en la ficha Inicio de la Cinta de opciones o en la ficha Fórmulas. Excel coloca una marquesina (también conocida como las hormiguitas) alrededor de las celdas que piensa que hay que sumar. Si el rango de celdas enmarcado no es el que quieres sumar, utiliza el ratón para resaltar otro rango. Vuelve a clicar en el botón Autosuma o pulsa Entrar para terminar la operación.

• Para calcular la media, contar números o determinar el máximo y el mínimo del rango, clica en la flecha del botón Autosuma para ver un menú con esas operaciones. Clica en la que quieras realizar y sigue el procedimiento descrito para sumar un rango después de que Excel coloque la marquesina alrededor de las celdas.

Excel coloca automáticamente una marquesina alrededor de la primera región que esté encima o a la izquierda de la celda de la fórmula. Una región es un rango con celdas que no están en blanco. Para seleccionar varias regiones al utilizar la herramienta Autosuma en el segundo paso, mantén pulsada la tecla Ctrl mientras arrastras el ratón sobre cada región. Se explica cómo en el capítulo 4, en el apartado “Seleccionar celdas y rangos”.

Utilizar la Biblioteca de funciones

La Biblioteca de funciones permite escribir fácilmente una función y sus argumentos. Es muy útil cuando no sabes exactamente qué función estás buscando y quieres explorar las diversas opciones. Para facilitar la búsqueda, Excel agrupa las funciones disponibles en categorías fáciles de reconocer, como financieras o texto.

Para añadir una función mediante la Biblioteca de funciones:

1. Activa la celda en la que pondrás la fórmula. Si añades la función a una fórmula existente, clica en el lugar de la fórmula en el que haya que añadir la función.

2. Si añades una función al lado de otra función en una fórmula, tienes que añadir un operador (por ejemplo, +, –, o *) o una coma en ese punto para delimitar las dos funciones.

3. Clica en la ficha Fórmulas de la Cinta de opciones y, después, en una categoría de función en el grupo Biblioteca de funciones. Clica en el botón Más funciones si no encuentras la categoría que buscabas en el grupo Biblioteca de funciones.

4. Selecciona la función buscada en el menú. Aparece el cuadro de diálogo Argumentos de función, como puedes ver en la figura 5-2. Este cuadro de diálogo explica cada argumento de función seleccionado. Puedes añadir argumentos manualmente o señalarlos en la hoja de cálculo si son referencias de celda. El cuadro de diálogo Argumentos de función muestra el resultado.

Para obtener ayuda con la función, clica en Ayuda sobre esta función en el cuadro de diálogo Argumentos de función. Aparece la ventana de Ayuda para la función.

5. Una vez concretados todos los argumentos, clica en Aceptar.

Figura 5-2

Cambiar una referencia de rango utilizada en una función

Si editas una celda con una fórmula, Excel pone códigos de colores a las referencias de la fórmula y un contorno alrededor de cada celda o rango a la que haga referencia la fórmula. El color del contorno corresponde al color que aparece en la fórmula. Cada celda o rango resaltado tiene controladores de relleno (un cuadradito en cada esquina de la celda o rango resaltado). Lo verás en la figura 5-3.

Figura 5-3

Si la fórmula tiene una función que utiliza un argumento de rango, puedes modificar fácilmente el rango de referencia así:

1. Para empezar a editar la fórmula, pulsa F2 o haz un doble clic en la celda. Te ayudará leer en el capítulo 4 el apartado “Editar el contenido de una celda”.

2. Busca el rango que utiliza la función (el rango aparece con un contorno).

3. Arrastra un controlador de relleno para ampliar o reducir el rango. También puedes clicar en un borde del rango destacado y mover el contorno a un rango nuevo. En cualquier caso, Excel cambia la referencia de rango en la fórmula.

4. Pulsa Entrar.

Recuerda: Las fórmulas que tienen referencias de nombre ponen un contorno de color alrededor de la celda o rango mencionado. El contorno no tiene controladores de relleno, pero puedes mover este tipo de referencia con un clic en el borde del contorno.

Hacer referencia a celdas o rangos en otros libros

Las referencias externas a otros libros también reciben el nombre de vínculos. El libro con las fórmulas de vínculo (fórmulas con referencias externas) es el libro de destino. El libro de donde provienen los datos utilizados por la fórmula de referencia externa es el libro de origen. El libro de origen no tiene que estar abierto mientras se trabaja con el libro de destino.

Hacer referencia a celdas de otros libros

Si la fórmula tiene que hacer referencia a una celda de otro libro, utiliza el formato siguiente en la fórmula:

=[NombreLibro]NombreHoja!DirecciónCelda

La dirección de la celda está precedida por el nombre del libro (entre paréntesis), el nombre de la hoja y un signo de exclamación. Esta fórmula se conoce a veces como fórmula de vínculo o referencia externa.

Recuerda: Si el nombre del libro o de la hoja de cálculo tiene espacios, hay que ponerlos entre comillas simples. Por ejemplo, la siguiente fórmula hace referencia a una celda en la Hoja1 del libro Presupuesto 2005:

=A1*‘[Presupuesto 2005]Hoja1’!A1

Si una fórmula hace referencia a celdas de otro libro, este no tiene que permanecer abierto. Si el libro está cerrado, hay que añadir la ruta completa a la referencia. Por ejemplo:

=A1* ‘C:\Mis Documentos\Excel\[Presupuesto 2005]Hoja1’!A1

Lo más fácil para introducir fórmulas con vínculos es señalar. Para ello, los libros de origen tienen que estar abiertos. Lee en el capítulo 2, el apartado “Organización automática de las ventanas” y el apartado “Introducir fórmulas señalando” en el capítulo 4.

Para evitar problemas en las fórmulas del libro de destino al insertar o eliminar filas, columnas o celdas o al mover celdas o rangos vinculados de los libros de origen, es recomendable que utilices los nombres de celdas o rangos de los libros de origen.

Administrar vínculos

Al trabajar con referencias externas en una hoja de cálculo, a veces es necesario realizar algunas tareas de administración. Para ello está el cuadro de diálogo Modificar vínculos.

Para abrirlo (mira la figura 5-4), clica en el botón Modificar vínculos en la ficha Datos de la Cinta de opciones. El botón está desactivado si el libro no tiene vínculos (referencias externas) a otros libros.

Figura 5-4

La ventana del cuadro de diálogo Modificar vínculos incluye todos los libros de origen a los que está conectado el libro de destino. Puedes realizar las operaciones siguientes en este cuadro de diálogo:

• Actualizar valores. Con este botón puedes actualizar los valores de los libros externos durante la sesión de trabajo.

Selecciona el libro o libros de origen en la ventana y clica en Actualizar valores.

• Cambiar origen. Si cambia la ubicación o el nombre de un libro de origen hay que actualizar la información del vínculo.

• En la ventana, selecciona el libro de origen cuyo nombre haya cambiado y clica en Cambiar origen. En el cuadro de diálogo Cambiar origen, navega hasta la ubicación del nuevo archivo de origen, selecciónalo y clica en Aceptar.

• Abrir origen. El cuadro de diálogo Modificar vínculos es el lugar adecuado para abrir libros de origen.

• En la ventana, selecciona el libro o libros que quieras abrir y clica en Abrir origen. Se abren todos los libros seleccionados.

• Romper vínculo. Para mantener los valores actuales (no las fórmulas) de uno o más libros de origen y deshacer los vínculos a los libros de origen.

• En la ventana, selecciona el libro o libros y clica en Romper vínculo. El programa te pedirá confirmación ya que esta operación no puede deshacerse. Clica Romper vínculo en el mensaje de confirmación para romper definitivamente el enlace.

• Comprobar estado. Al abrir el cuadro de diálogo, este estado aparece siempre como Desconocido. El estatus de los vínculos no aparece automáticamente.

Clica en el botón Comprobar estado para ver el estatus de los vínculos. Un estatus, El origen está abierto, o Valor actualizado de <nombre del archivo> indica que no tienes que hacer nada. Sin embargo, si aparece un mensaje de advertencia o de error, debes actualizar los valores, cambiar o abrir el origen. El mensaje de advertencia o de error indica lo que tienes que hacer.

• [Cambiar] Pregunta inicial. Si los libros no están abiertos, al abrir uno con referencias externas (el libro de destino) aparece de forma predeterminada una ventana para actualizar los vínculos (se actualizan automáticamente al abrir los libros de origen). Pero puedes controlar esta función.

• Clica en el botón Pregunta inicial. Aparece un cuadro de diálogo con estas tres opciones:

• Permitir que los usuarios elijan mostrar o no la alerta. Después de seleccionar esta opción, el usuario tiene que activar o desactivar la advertencia: clica en la ficha Archivo y, después, en el botón Opciones. En el cuadro de diálogo Opciones de Excel, clica en la categoría Avanzadas y baja hasta la sección General. Por último, activa o desactiva la casilla Consultar al actualizar vínculos automáticos.

• Si desactivas esta casilla los vínculos se actualizan automáticamente sin que aparezca un mensaje de confirmación al abrir un libro dependiente. Esta opción se aplica exclusivamente al usuario actual y a los libros dependientes que abra. No afecta a los demás usuarios con los que compartas el libro.

• No mostrar la alerta ni actualizar los vínculos automáticos. Esta opción se explica por sí misma y afecta a todos los usuarios que compartan el libro.

• No mostrar la alerta y actualizar vínculos. Esta opción se explica por sí misma y afecta a todos los usuarios que compartan el libro.

Hacer referencia a celdas o rangos en otras hojas de cálculo

Si la fórmula tiene que hacer referencia a una celda en otra hoja de cálculo del mismo libro, utiliza el formato siguiente en la fórmula:

Nombre Hoja!Dirección Celda

La dirección de la celda está precedida por el nombre de la hoja de cálculo con un signo de exclamación.

Recuerda: Si el nombre de la hoja de cálculo tiene espacios, tienes que ponerlo entre comillas simples. La siguiente fórmula hace referencia a una celda de la hoja “Todos los departamentos”:

=A1*‘Todos los departamentos’!A1

Una manera fácil de introducir una referencia de rango en otra hoja de cálculo es señalar con el ratón. Te será útil leer en el capítulo 4 el apartado “Introducir fórmulas señalando”.

Si la referencia es a un rango 3-D, el formato es el siguiente:

=NombreFunción(PrimeraHoja:ÚltimaHoja!ReferenciaRango)

Por ejemplo:

=SUMA(Hoja2:Hoja4!$A$1:$A$10)

Las reglas siguientes se aplican a las referencias de rango 3-D:

• El rango incluye todas las hojas entre los primeros y últimos nombres, ambos incluidos.

• Si añades una hoja entre la primera y la última, se incluirá automáticamente en la referencia.

• En el rango de referencia puedes utilizar referencias absolutas o relativas.

• Puedes utilizar cualquier nombre válido para las hojas, pero si hay un espacio, debes utilizar comillas simples para delimitar los nombres de la referencia. Por ejemplo

=SUMA(‘Dep Ingeniería:Dep RRHH’!$A$1:$A$10)

Recuerda: Las referencias a rangos 3-D son compatibles con menos del 10 % de las funciones de Excel y la mayoría de ellas son de la categoría de estadísticas.

Puedes utilizar el ratón para señalar y crear una referencia 3-D. Primero coloca el puntero del ratón en la función que recibirá la referencia. A continuación, selecciona la celda o el rango en la primera hoja que se incluirá en la referencia. Después, mantén pulsada la tecla Mayús y clica en la ficha de la última hoja de la referencia. Pulsa Entrar para completar el procedimiento.