Cuando trabajamos de manera frecuente con Excel 2016 tenemos al frente una poderosa herramienta de la suite de Office 2016 la cual nos brindará ayuda valiosa y, sin ninguna duda, será un tesoro para todo el tema relacionado con la gestión de datos usando sus fórmulas y múltiples atajos.
Quizás muchos escuchamos términos asociados a Excel 2016 y quedamos en un punto que no sabemos a que hacen referencia y uno de estos términos frecuentes son las referencias absolutas y relativas. En este tutorial vamos a comprender que son las referencias absolutas y relativas en Excel 2016.
1. Referencias relativas
Son el tipo de referencia más usada en Excel ya que nos permite guardar una relación entre la columna y fila donde se encuentra la referencia, esto implica que Excel 2016 de manera automáticamente ajustara su columna y su fila
Tenemos los siguientes datos:
En este caso tenemos una referencia relativa a la celda A1 la cual estamos multiplicando por 2. Nuestra referencia es =A1*2 lo cual nos indica que una referencia relativa es la combinación de una letra (A) y un número (1) sin espacio entre ellas. Para ver el funcionamiento de la referencia relativa serán visibles cuando copiemos dichos valores en otra celda. Para ello copiaremos la formula a las siguientes celdas arrastrando hacia abajo la esquina inferior derecha de la celda B1:
Podemos ver que la función de la referencia relativa es aplicada al ajustar de manera automática la fila de la columna A. En este caso podemos ver que la fórmula es =A3*2, en este ejemplo la referencia relativa a aumentado el número de la fila según lo hemos indicado al copiar la formula.
Otro ejemplo es con la siguiente información:
En esta ocasión estamos sumando los valores de las ventas diarias en la celda B6. En este caso la referencia relativa está tomando los valores del rango B2:B5 Si copiamos esta fórmula hacia la derecha la referencia relativa se encarga de aumentar el número de columna para ejecutar la respectiva suma:
Vemos como la referencia relativa a modificado las diferentes columnas para ejecutar el proceso correctamente.
2. Referencias absolutas
Con las referencias absolutas Excel 2016 no tendrá la posibilidad de modificar las columnas o filas indicadas permitiendo que estos valores permanezcan fijos sin importar donde sean copiados.
Para crear una referencia absoluta debemos anteponer el símbolo $ antes de la columna fila o ambas. Podemos usar la tecla F4 para este proceso así:
Nos ubicamos en la celda donde se establecerá la referencia absoluta, en este ejemplo B2, y al pulsar F4 una vez veremos lo siguiente:
- =$A$1*3 (Hace fija la columna y la fila)
- =A$1*3 (Haremos fija únicamente la fila)
- =$A1*3 (Haremos fija únicamente la columna)
- =A1*3
Notemos que hemos definido que la celda A1 permanezca fija (=$A$1*3) y en este ejemplo hemos multiplicado el valor de la celda A1 por 3. Ahora si copiamos esta misma fórmula en las celdas inferiores veremos el siguiente resultado:
Podemos comprobar que el resultado es el mismo ya que no ha sido modificado el valor ni de la columna ni de la fila. Las referencias absolutas son vitales cuando debemos ejecutar una tarea donde tengamos como referencia una única celda, como un impuesto, un descuento, etc.
3. Combinación de referencias absolutas y relativas
Dentro de las grandes ventajas que nos ofrece Excel 2016 es la posibilidad de combinar ambos tipos de referencias en una sola tarea. Para este ejemplo tenemos los siguientes datos:
En este ejemplo queremos conocer el precio de cada producto en pesos tomando como referencia la tasa de cambio del Euro. Si usamos una referencia relativa ingresaríamos la siguiente formula en la celda C2:
- =B2*F1
Hasta este punto tenemos el valor deseado pero si copiamos la formula a las demás celdas veremos lo siguiente:
Vemos que no obtenemos resultado ya que la referencia relativa ajusta de manera automática la fila de la celda, en este caso por ejemplo =B4*F3 donde en la celda F3 no existe ningún valor. Ahora podemos implementar una referencia absoluta para que obtengamos el valor deseado, para ello ingresaremos la siguiente formula en la celda C2:
- =B2*$F$1
Notamos que hemos obtenido la conversión de los precios de manera correcta. Así cuando ajustemos el valor de la celda F1 de manera automática todos los cambios se verán actualizados al nuevo cambio. Debemos señalar que la principal diferencia entre ambas referencias es que la referencia absoluta emplea el símbolo $ para indicar que permanecerá el valor fijo.
4. Cómo usar la función COINCIDIR
Con la función COINCIDIR tenemos una gran ayuda ya que esta nos permite encontrar un valor dentro de una lista y nos devuelve la posición (Fila) donde se encuentra ubicado y en caso de no hallar el valor devolverá un error. Para comprender el uso de esta función tenemos los siguientes datos:
Básicamente la forma como está compuesta la función COINCIDIR es la siguiente:
- El primer argumento indica el valor buscado
- El segundo argumento indica el rango de celdas donde buscaremos ese valor
- El tercer argumento indica el tipo de búsqueda a realizar donde tenemos los siguientes valores: 1 (Menor que); 0 (Exacta); -1 (Mayor que)
En este ejemplo usaremos la siguiente sintaxis:
- =COINCIDIR(E1;A2:A9;0)
En este ejemplo hemos pedido buscar el valor 5413645 el cual como vemos la función COINCIDR nos indica que se encuentra en la posición 2 (Fila 2) Con la función COINCIDIR lo más recomendable es usar el argumento 0 ya que realiza una búsqueda exacta del valor buscado y en caso de no encontrarlo nos devolverá un error:
De esta forma podemos usar esta función para conocer con exactitud donde se encuentra un valor determinado.