Imagen 1. Tipos de referencias, (2015). Elaboración propia.

Referencias relativas

Las referencias a celdas de manera predeterminada son referencias relativas. Lo que significa que si la fórmula se copia o se mueve a otra ubicación, la referencia cambiará automáticamente, ajustándose al nuevo lugar. Las referencias de filas y columnas cambiarán si se copia la fórmula en otra celda, o sea se adapta a su entorno ya que las referencias las crea con respecto a la distancia entre la fórmula y las celdas que integran parte de la fórmula.

Analiza el siguiente ejemplo: Imagina que copias la fórmula =B2+B3, de la celda B4 a la celda C4, de acuerdo a lo que acabas de mencionar automáticamente la fórmula en C4 se cambiará quedando como =C2+C3.

Por esta razón, una referencia relativa está basada en la posición relativa de la celda que tiene la fórmula y de la celda a la que se hace referencia. Lo mismo pasa si se copia la fórmula en filas o columnas, es decir, la referencia se ajusta automáticamente.

No olvides que de forma predeterminada, las fórmulas utilizan referencias relativas.

En la siguiente figura se muestra una fórmula que utiliza la referencia F4 en la celda A1, observa lo que sucede si copias esa fórmula hacia abajo en la columna A:

Imagen 2. Ejemplo de copiado de fórmulas con referencias relativas, adaptado de Ms Excel, (2013). Elaboración propia.

Como puedes observar, la celda F4 cambia sucesivamente hasta llegar a F10, en este caso se utilizó la opción de autorelleno.

Ahora observa lo que pasa cuando copias la misma fórmula hacia la derecha llenando el rango de celdas B1:B7:

Imagen 3. Ejemplo de copiado de fórmulas con referencias relativas, adaptado de Ms Excel, (2013). Elaboración propia.

En este caso, en cada celda cambia la referencia de la columna F por columna G y el número de fila se mantiene igual, para realizar esta acción primero se seleccionó con el ratón el rango de celdas A1:A7, ubica el puntero del mouse en la esquina inferior derecha de la selección y arrastre hacia la derecha con la opción autorelleno, que es la figura que toma el puntero del mouse en forma de cruz negra al colocarlo en la esquina inferior derecha de la selección.

Referencias absolutas

:

Observa un ejemplo: Si copias la fórmula =$A$2+$A$3, de la celda A4 a la celda B4, entonces la fórmula en B4 será también =$A$2+$A$3.

Por lo tanto, una referencia absoluta siempre hace referencia a una celda en una ubicación específica. Aunque cambie la posición de la celda que contiene la fórmula, la referencia absoluta no cambia.

En la siguiente figura se muestra una fórmula que utiliza la referencia $E$4 en la celda A1, observa lo que sucede si copias esa fórmula hacia abajo en la columna A:

Imagen 4. Ejemplo de copiar hacia abajo una referencia absoluta, adaptado de Ms Excel, (2103). Elaboración propia.

Como puedes observar, la referencia E4 se mantiene en todas las celdas.

Ahora observa lo que pasa cuando copias la misma fórmula hacia la derecha llenando el rango de celdas B1:B7:

Imagen 5. Ejemplo de copiar hacia la derecha una referencia absoluta, adaptado de Ms Excel, (2103). Elaboración propia.

Para este caso, la referencia E4 también se mantiene en todas las celdas.

Para poder trabajar sin problemas con las planillas de cálculo es necesario tener muy claro cómo funcionan las referencias a celdas y/o rangos. Ahora ya sabes que siempre que si cambia la posición de la celda que contiene la fórmula, la referencia absoluta permanece invariable. Si se copia la fórmula en filas o columnas, la referencia absoluta no se ajusta. De forma predeterminada, las nuevas fórmulas utilizan referencias relativas y es necesario cambiarlas a referencias absolutas.

Referencias mixtas

Una referencia mixta tiene una parte absoluta y otra parte relativa, tal y como se muestra en el siguiente ejemplo: La referencia $B3 es una referencia mixta en donde la columna B es absoluta y la fila 3 es relativa. Similarmente, la referencia B$3 es una referencia mixta en donde la columna B es relativa y la fila 3 es absoluta.

En conclusión, una referencia mixta tiene una columna absoluta y una fila relativa, por ejemplo, $B4, o viceversa, una fila absoluta y una columna relativa, por ejemplo B$4.
Cuando mueves o copias una referencia mixta, la parte que tiene el signo de pesos $ nunca va a cambiar y la parte que no lo tiene el signo de pesos $ cambia ajustándose automáticamente a su nueva posición.

En la siguiente figura se muestra una fórmula que utiliza la referencia $E$4 en la celda A1, observa lo que sucede si copias esa fórmula hacia abajo en la columna A:

Imagen 6. Ejemplo de copiar hacia abajo una referencia mixta, adaptado de Ms Excel, (2103). Elaboración propia.

Como puedes observar, la referencia de la columna se mantiene y la referencia de la fila cambiaría consecutivamente.

Ahora observa lo que pasa cuando copias la misma fórmula hacia la derecha llenando el rango de celdas B1:B7:

Imagen 7. Ejemplo de copiar hacia la derecha una referencia mixta, adaptado de Ms Excel, (2103). Elaboración propia.

En este caso, ambas referencias se mantienen sin cambios.

Del mismo modo, llena con la fórmula que se encuentra en la celda A1 de la siguiente figura en el rango de celdas A2:A7 y B1:B7:

Imagen 8. Ejemplo de copiar hacia la derecha una referencia mixta, adaptado de Ms Excel, (2103). Elaboración propia.

Como puedes observar, se mantiene la referencia E4 en la columna A, sin embargo, la referencia de la columna en la columna B cambia a F manteniendo sin cambios la referencia de la fila en la misma columna B, por lo que solamente la parte relativa cambia, es decir, la referencia de la columna se sustituye.

Para complementar este tema revisa algunas funciones financieras básicas, la primera es: =PAGO(Tasa,Nper,VA)

Es común que cuando se pide dinero prestado y el pago del préstamo se hará en varios pagos, normalmente se pague una tasa de interés.

Por ejemplo, si pides prestados $100 y los pagarás en 10 pagos iguales, cada uno de los pagos definitivamente no serían de $10, si no de un poco más, esto dependerá directamente de la tasa de interés. Si, por ejemplo, el interés por periodo es del 2%, entonces cada uno de los 10 pagos sería de $11.13.

La función PAGO calcula el monto de cada uno de los pagos de un préstamo con cierta tasa de interés fija, a un número determinado de pagos. Esta función necesita varios argumentos, pero sólo los primeros 3 son necesarios:

Imagen 9. Cuadro de diálogo de Argumentos de función PAGO, adaptado de Ms Excel, (2103). Elaboración propia.

Como puedes observar en la pantalla, el resultado es un valor negativo. Recuerda que en un flujo de caja se consideran positivos los valores que entran,  o sea, los ingresos y negativos los que salen, es decir, los egresos o gastos. En el ejemplo, los $100 que pides prestados entran a tu flujo de caja, por eso son positivos. Los $11.13  que debes pagar cada periodo, salen de tu flujo de caja, por eso son negativos.

=VA(tasa, nper, pago)

La función VA (Valor actual) es casi igual a la función PAGO, pero ésta se utiliza si se conoce el monto a pagar y se desconoce el valor del préstamo inicial.
Con el mismo ejemplo del caso anterior, puedes utilizar la función VA si sabes que tienes que pagar $11.13 durante 10 periodos y sabes que la tasa de interés es del 2%, pero desconoces el valor actual del préstamo.

Esta función necesita varios argumentos, pero al igual que la función PAGO sólo los primeros tres son necesarios:

Imagen 10. Cuadro de diálogo de Argumentos de función VA, adaptado de Ms Excel, (2103). Elaboración propia.

=NPER(Tasa, Pago, Va)

La función NPER es similar a la función PAGO, pero se aplica cuando lo que no se conoce es el número de pagos que hay que realizar.

Continuando con el mismo ejemplo, se utiliza la función NPER si sé que tengo que pagar $11.13 por un préstamo inicial de $100 y la tasa de interés es del 2%, pero desconozco la cantidad de pagos que hay que realizar.

Los tres argumentos necesarios de esta función son los siguientes:

=TASA (Nper, Pago, VA)

La función TASA, o sea, tasa de interés,  es similar a la función PAGO, pero se aplica cuando lo que no se conoce es la tasa de interés que se paga.

Continuando con el ejemplo, se utiliza la función TASA si sé que tengo que pagar $11.13 por un préstamo inicial de $100, durante 10 periodos, pero no conozco la tasa de interés que aplica.
Los tres argumentos necesarios de esta función son los siguientes:

=VF(Tasa, Nper, Pago, VA, Tipo)

La función VF significa Valor futuro, se utiliza para calcular el valor futuro de una inversión, basado en pagos periódicos y constantes con una tasa de interés fija.

Un ejemplo típico del uso de esta función es cuando ahorras dinero en un banco. Puedes utilizar la función VF si deseas saber cuánto dinero tendrás en una cuenta después de ahorrar 10 meses, la cantidad de $100 cada mes. Como el banco da una tasa de interés a los ahorradores, la cantidad acumulada después de los 10 meses será mayor a $1000, dependiendo de la tasa de interés, supón que la tasa de interés fuera del 4%, al final de los 10 periodos tendrías $1200.61.

Imagen 11. Cuadro de diálogo de Argumentos de función Valor Futuro, adaptado de Ms Excel, (2103). Elaboración propia.

La función requiere varios argumentos, pero sólo los primeros tres son indispensables.

Cuando trabajas en Excel y haces uso de las fórmulas y funciones es común que incluyas referencias a celdas o a un conjunto de celdas que no son propiamente la misma celda donde tienes la fórmula.  Como te pudiste dar cuenta, las referencias son enlaces a un lugar, es decir, cuando en una fórmula escribimos =PROMEDIO(A1;B1), se está refiriendo a que promedie el contenido de A1 y el contenido de B1.

En Excel también es posible hacer referencia a otros libros, pero este tema lo verás a continuación.