Enlaces en Excel – absolutos, relativos y mixtos. Errores al trabajar con enlaces relativos en Excel

Para facilitar los cálculos mediante fórmulas en modo automático, se utilizan referencias a celdas. Según el tipo de escritura, se dividen en tres tipos principales:

1. Enlaces relativos. Se utiliza para cálculos simples. Copiar una fórmula implica cambiar las coordenadas.
2. Enlaces absolutos. Si necesita producir cálculos más complejos, esta opción es adecuada. Para fijar utilice el símbolo “$”. Ejemplo: $A$1.
3. enlaces mixtos. Este tipo de direccionamiento se usa en cálculos cuando es necesario arreglar una columna o línea por separado. Ejemplo: $A1 o A$1.

Si es necesario copiar los datos de la fórmula ingresada, se utilizan referencias con direccionamiento absoluto y mixto. El artículo revelará con ejemplos cómo se realizan los cálculos utilizando varios tipos de enlaces.

Referencia de celda relativa en Excel

Este es un conjunto de caracteres que definen la ubicación de una celda. Los enlaces en el programa se escriben automáticamente con direccionamiento relativo. Por ejemplo: A1, A2, B1, B2. Mover a una fila o columna diferente cambia los caracteres en la fórmula. Por ejemplo, posición inicial A1. Moviéndose horizontalmente cambia la letra a B1, C1, D1, etc. De la misma manera, los cambios ocurren cuando se mueve a lo largo de una línea vertical, solo que en este caso cambia el número: A2, A3, A4, etc. Si es necesario duplicar un cálculo del mismo tipo en una celda adyacente, se realiza un cálculo utilizando una referencia relativa. Para usar esta característica, siga algunos pasos:

1. Tan pronto como se ingresen los datos en la celda, mueva el cursor y haga clic con el mouse. Resaltar con un rectángulo verde indica la activación de la celda y la preparación para seguir trabajando.
2. Al presionar la combinación de teclas Ctrl + C, copiamos el contenido al portapapeles.
3. Activamos la celda a la que desea transferir datos o una fórmula previamente escrita.
4. Al presionar la combinación Ctrl + V transferimos los datos guardados en el portapapeles del sistema.

¡Consejo experto! Para realizar el mismo tipo de cálculos en la tabla, use un truco de vida. Seleccione la celda que contiene la fórmula ingresada anteriormente. Al pasar el cursor sobre un pequeño cuadrado que aparece en la esquina inferior derecha y mantener presionado el botón izquierdo del mouse, arrastre a la fila inferior o columna extrema, según la acción realizada. Al soltar el botón del mouse, el cálculo se realizará automáticamente. Esta herramienta se llama marcador de relleno automático.

Ejemplo de enlace relativo

Para hacerlo más claro, considere un ejemplo de un cálculo utilizando una fórmula con una referencia relativa. Supongamos que el dueño de una tienda de deportes después de un año de trabajo necesita calcular la ganancia de las ventas.

El orden de las acciones:

1. El ejemplo muestra que las columnas B y C se usaron para completar la cantidad de bienes vendidos y su precio. En consecuencia, para escribir la fórmula y obtener la respuesta, seleccione la columna D. La fórmula se ve así: = B2 *C

¡Presta atención! Para facilitar el proceso de escribir una fórmula, utilice un pequeño truco. Coloque el signo "=", haga clic en la cantidad de bienes vendidos, configure el signo "*" y haga clic en el precio del producto. La fórmula después del signo igual se escribirá automáticamente.

2. Para la respuesta final, presione "Enter". A continuación, debe calcular la cantidad total de ganancias recibidas de otros tipos de productos. Bueno, si la cantidad de líneas no es grande, todas las manipulaciones se pueden realizar manualmente. Para completar una gran cantidad de filas al mismo tiempo en Excel, hay una función útil que permite transferir la fórmula a otras celdas.
3. Mueva el cursor sobre la esquina inferior derecha del rectángulo con la fórmula o el resultado final. La aparición de una cruz negra sirve como señal de que el cursor se puede arrastrar hacia abajo. Así, se realiza un cálculo automático del beneficio recibido por cada producto por separado.
4. Al soltar el botón presionado del mouse, obtenemos los resultados correctos en todas las líneas.

Al hacer clic en la celda D3, puede ver que las coordenadas de la celda se cambiaron automáticamente y ahora se ven así: =B3 *C3. De ello se deduce que los vínculos eran relativos.

Posibles errores al trabajar con enlaces relativos

Sin duda, esta función de Excel simplifica mucho los cálculos, pero en algunos casos pueden surgir dificultades. Consideremos un ejemplo simple de cálculo del coeficiente de beneficio de cada artículo de bienes:

1. Cree una tabla y complete: A – nombre del producto; B – cantidad vendida; C – costo; D es la cantidad recibida. Suponga que solo hay 11 artículos en el surtido. Por lo tanto, teniendo en cuenta la descripción de las columnas, se llenan 12 líneas y el monto total de la ganancia es D
2. Haga clic en la celda E2 e ingrese =D2 /D13.
3. Después de presionar el botón "Entrar", aparece el coeficiente de la participación relativa de las ventas del primer artículo.
4. Estire la columna hacia abajo y espere el resultado. Sin embargo, el sistema da el error “#DIV/0!”

El motivo del error es el uso de una referencia relativa para los cálculos. Como resultado de copiar la fórmula, las coordenadas cambian. Es decir, para E3, la fórmula se verá así =D3 /D13. Debido a que la celda D13 no está llena y teóricamente tiene un valor cero, el programa dará un error con la información de que la división por cero es imposible.

¡Importante! Para corregir el error, es necesario escribir la fórmula de tal manera que las coordenadas D13 sean fijas. El direccionamiento relativo no tiene tal funcionalidad. Para hacer esto, hay otro tipo de enlaces: absolutos. 

¿Cómo se hace un enlace absoluto en Excel?

Gracias al uso del símbolo $, fue posible fijar las coordenadas de las celdas. Cómo funciona esto, lo consideraremos más adelante. Dado que el programa utiliza el direccionamiento relativo de forma predeterminada, en consecuencia, para hacerlo absoluto, deberá realizar una serie de acciones. Analicemos la solución al error "Cómo encontrar el coeficiente de la venta de varios artículos", realizando el cálculo utilizando direccionamiento absoluto:

1. Haga clic en E2 e ingrese las coordenadas del enlace =D2 /D13. Dado que el enlace es relativo, se debe establecer un símbolo para fijar los datos.
2. Fijar las coordenadas de la celda D Para realizar esta acción, preceder la letra que indica la columna y el número de fila colocando un signo “$”.

¡Consejo experto! Para facilitar la tarea de ingresar, basta con activar la celda para editar la fórmula y presionar varias veces la tecla F4. Hasta obtener valores satisfactorios. La fórmula correcta es la siguiente: =D2/$D$13.

3. Presione el botón "Entrar". Como resultado de las acciones realizadas, debería aparecer el resultado correcto.
4. Arrastre el marcador hasta la línea inferior para copiar la fórmula.

Debido al uso de direccionamiento absoluto en los cálculos, los resultados finales en las filas restantes serán correctos.

Cómo poner un enlace mixto en Excel

Para los cálculos de fórmulas, no solo se utilizan referencias relativas y absolutas, sino también mixtas. Su característica distintiva es que fijan una de las coordenadas.

• Por ejemplo, para cambiar la posición de una línea, debe escribir el signo $ delante de la designación de la letra.
• Por el contrario, si el signo de dólar se escribe después de la designación de la letra, los indicadores en la línea permanecerán sin cambios.

De esto se deduce que para resolver el problema anterior con la determinación del coeficiente de ventas de bienes utilizando direccionamiento mixto, es necesario fijar el número de línea. Es decir, el signo $ se coloca después de la letra de la columna, porque sus coordenadas no cambian ni siquiera en una referencia relativa. Tomemos un ejemplo:

1. Para cálculos precisos, ingrese =D1/$D$3 y presione “Entrar”. El programa da la respuesta exacta.
2. Para mover la fórmula a las celdas siguientes de la columna y obtener resultados precisos, arrastre el controlador a la celda inferior.
3. Como resultado, el programa dará los cálculos correctos.

¡Atención! Si configura el signo $ delante de la letra, Excel dará un error "#DIV/0!", lo que significa que esta operación no se puede realizar.

Direccionamiento “superabsoluto”

Al final, veamos otro ejemplo de un enlace absoluto: direccionamiento "superabsoluto". Cuáles son sus características y diferencias. Tome el número aproximado 30 e ingréselo en la celda B2. Es este número el que será el principal, es necesario realizar una serie de acciones con él, por ejemplo, elevarlo a una potencia.

1. Para la correcta ejecución de todas las acciones, ingrese la siguiente fórmula en la columna C: =$B$2^$D2. En la columna D ingresamos el valor de los grados.

2. Después de presionar el botón "Entrar" y activar la fórmula, estiramos el marcador hacia abajo en la columna.
3. Obtenemos los resultados correctos.

La conclusión es que todas las acciones realizadas se referían a una celda fija B2, por lo tanto:

• Copiar una fórmula de la celda C3 a la celda E3, F3 o H3 no cambiará el resultado. Permanecerá sin cambios: 900.
• Si necesita insertar una nueva columna, las coordenadas de la celda con la fórmula cambiarán, pero el resultado también permanecerá sin cambios.

Esta es la peculiaridad del enlace "SuperAbsolute": si necesita mover el resultado no cambiará. Sin embargo, hay situaciones en las que se insertan datos de fuentes de terceros. Por lo tanto, las columnas se desplazan hacia un lado y los datos se establecen de la forma antigua en la columna B2. ¿Qué sucede en este caso? Al mezclar, la fórmula cambia según la acción realizada, es decir, ya no apuntará a B2, sino a C2. Pero como la inserción se hizo en B2, el resultado final será erróneo.

¡Referencia! Para poder insertar macros de fuentes de terceros, debe habilitar la configuración del desarrollador (están deshabilitadas de forma predeterminada). Para hacer esto, vaya a Opciones, abra la configuración de la cinta y marque la casilla en la columna derecha frente a "Desarrollador". Después de eso, se abrirá el acceso a muchas funciones que antes estaban ocultas a los ojos del usuario promedio.

Esto plantea la pregunta: ¿es posible modificar la fórmula de la celda C2 para que el número original se obtenga de la celda B, a pesar de la inserción de nuevas columnas de datos? Para asegurarse de que los cambios en la tabla no afecten la determinación del total al instalar datos de fuentes de terceros, debe realizar los siguientes pasos:

1. En lugar de las coordenadas de la celda B2, ingrese los siguientes indicadores: = INDIRECTO ("B2"). Como resultado, después de mover la composición de formulación se verá así: = INDIRECTO ("B2") ^ $ E2.
2. Gracias a esta función, el enlace siempre apunta al cuadrado con coordenadas B2, independientemente de si se agregan o eliminan columnas en la tabla.

Debe entenderse que una celda que no contiene ningún dato siempre muestra el valor “0”.

Conclusión

Gracias al uso de tres variedades de los enlaces descritos, aparecen muchas oportunidades que facilitan el trabajo con cálculos en Excel. Por lo tanto, antes de comenzar a trabajar con fórmulas, primero lea los enlaces y las reglas para su instalación.