Ligazóns en Excel: absolutas, relativas e mixtas. Erros ao traballar con ligazóns relativas en Excel

Para facilitar os cálculos mediante fórmulas en modo automático, utilízanse referencias a celas. Segundo o tipo de escritura, divídense en tres tipos principais:

1. Ligazóns relativas. Úsase para cálculos sinxelos. Copiar unha fórmula implica cambiar as coordenadas.
2. Ligazóns absolutas. Se precisa realizar cálculos máis complexos, esta opción é adecuada. Para arranxar use o símbolo "$". Exemplo: $A$1.
3. ligazóns mixtas. Este tipo de direccionamento emprégase nos cálculos cando é necesario fixar unha columna ou liña por separado. Exemplo: $A1 ou A$1.

Se é necesario copiar os datos da fórmula introducida, utilízanse referencias con direccionamento absoluto e mixto. O artigo revelará con exemplos como se fan os cálculos mediante varios tipos de ligazóns.

Referencia de cela relativa en Excel

Este é un conxunto de caracteres que definen a localización dunha cela. As ligazóns do programa escríbense automaticamente cun enderezo relativo. Por exemplo: A1, A2, B1, B2. Ao moverse a unha fila ou columna diferente cambian os caracteres da fórmula. Por exemplo, posición inicial A1. Moverse horizontalmente cambia a letra a B1, C1, D1, etc. Do mesmo xeito, os cambios ocorren ao moverse por unha liña vertical, só neste caso o número cambia: A2, A3, A4, etc. Se é necesario duplicar un cálculo do mesmo tipo nunha cela adxacente, un cálculo realízase utilizando unha referencia relativa. Para usar esta función, siga algúns pasos:

1. Axiña que os datos se introducen na cela, move o cursor e fai clic co rato. Resaltar cun rectángulo verde indica a activación da cela e a preparación para o traballo posterior.
2. Premendo a combinación de teclas Ctrl + C, copiamos o contido no portapapeis.
3. Activamos a cela á que desexa transferir datos ou unha fórmula previamente escrita.
4. Premendo a combinación Ctrl + V transferimos os datos gardados no portapapeis do sistema.

Consello de expertos! Para realizar o mesmo tipo de cálculos na táboa, use un truco de vida. Seleccione a cela que contén a fórmula introducida anteriormente. Pasando o cursor sobre un pequeno cadrado que aparece na esquina inferior dereita, e mantendo mantendo o botón esquerdo do rato, arrastra ata a fila inferior ou a columna extrema, dependendo da acción realizada. Ao soltar o botón do rato, o cálculo realizarase automaticamente. Esta ferramenta chámase marcador de recheo automático.

Exemplo de ligazón relativa

Para que quede máis claro, considere un exemplo de cálculo mediante unha fórmula cunha referencia relativa. Supoña que o propietario dunha tenda de deportes despois dun ano de traballo necesita calcular o beneficio das vendas.

Orde de actuacións:

1. O exemplo mostra que as columnas B e C foron utilizadas para cubrir a cantidade de mercadorías vendidas e o seu prezo. En consecuencia, para escribir a fórmula e obter a resposta, seleccione a columna D. A fórmula ten o seguinte aspecto: = B2 *C

Presta atención! Para facilitar o proceso de escritura dunha fórmula, use un pequeno truco. Pon o signo "=", fai clic na cantidade de produtos vendidos, establece o signo "*" e fai clic no prezo do produto. A fórmula despois do signo igual escribirase automaticamente.

2. Para a resposta final, prema "Intro". A continuación, cómpre calcular a cantidade total de beneficios recibidos doutros tipos de produtos. Ben, se o número de liñas non é grande, todas as manipulacións pódense realizar manualmente. Para cubrir un gran número de filas ao mesmo tempo en Excel, hai unha función útil que permite transferir a fórmula a outras celas.
3. Move o cursor sobre a esquina inferior dereita do rectángulo coa fórmula ou o resultado final. A aparición dunha cruz negra serve como sinal de que o cursor pode ser arrastrado cara abaixo. Así, realízase un cálculo automático do beneficio recibido por cada produto por separado.
4. Ao soltar o botón do rato, obtemos os resultados correctos en todas as liñas.

Ao facer clic na cela D3, podes ver que as coordenadas das celas cambiaron automaticamente e agora quedan así: =B3 *C3. Dedúcese que as ligazóns eran relativas.

Posibles erros ao traballar con ligazóns relativas

Sen dúbida, esta función de Excel simplifica moito os cálculos, pero nalgúns casos poden xurdir dificultades. Consideremos un exemplo sinxelo de cálculo do coeficiente de beneficio de cada artigo:

1. Crea unha táboa e enche: A – nome do produto; B - cantidade vendida; C - custo; D é a cantidade recibida. Supoñamos que só hai 11 elementos no surtido. Polo tanto, tendo en conta a descrición das columnas, enchéronse 12 liñas e o importe total do beneficio é D
2. Fai clic na cela E2 e ingresa =D2/D13.
3. Despois de premer o botón "Intro", aparece o coeficiente da participación relativa das vendas do primeiro artigo.
4. Estira a columna cara abaixo e agarda o resultado. Non obstante, o sistema dá o erro "#DIV/0!"

O motivo do erro é o uso dunha referencia relativa para os cálculos. Como resultado de copiar a fórmula, as coordenadas cambian. É dicir, para E3, a fórmula terá este aspecto =D3/D13. Dado que a cela D13 non está cuberta e teoricamente ten un valor cero, o programa dará un erro coa información de que a división por cero é imposible.

Importante! Para corrixir o erro, é necesario escribir a fórmula de tal xeito que as coordenadas D13 estean fixas. O enderezo relativo non ten tal funcionalidade. Para iso, hai outro tipo de ligazóns: absolutas. 

Como facer unha ligazón absoluta en Excel

Grazas ao uso do símbolo $, fíxose posible corrixir as coordenadas das celas. Como funciona isto, teremos máis en conta. Dado que o programa usa o enderezo relativo por defecto, polo tanto, para facelo absoluto, terá que realizar unha serie de accións. Analizemos a solución ao erro "Como atopar o coeficiente da venda de varios artigos de mercadoría", realizando o cálculo mediante o enderezo absoluto:

1. Fai clic en E2 e introduce as coordenadas da ligazón =D2/D13. Dado que a ligazón é relativa, debe establecerse un símbolo para corrixir os datos.
2. Corrixe as coordenadas da cela D Para realizar esta acción, debes preceder a letra que indica a columna e o número de fila colocando un signo “$”.

Consello de expertos! Para facilitar a tarefa de entrada, basta con activar a cela para editar a fórmula e premer a tecla F4 varias veces. Ata obter valores satisfactorios. A fórmula correcta é a seguinte: =D2/$D$13.

3. Prema o botón "Intro". Como resultado das accións realizadas, debería aparecer o resultado correcto.
4. Arrastre o marcador ata a liña inferior para copiar a fórmula.

Debido ao uso do direccionamento absoluto nos cálculos, os resultados finais nas filas restantes serán correctos.

Como poñer unha ligazón mixta en Excel

Para os cálculos de fórmulas, non só se utilizan referencias relativas e absolutas, senón tamén referencias mixtas. A súa característica distintiva é que fixan unha das coordenadas.

• Por exemplo, para cambiar a posición dunha liña, debes escribir o signo $ diante da designación da letra.
• Pola contra, se o sinal de dólar está escrito despois da designación da letra, entón os indicadores da liña permanecerán inalterados.

Diso dedúcese que para resolver o problema anterior coa determinación do coeficiente de vendas de mercadorías mediante o enderezo mixto, é necesario fixar o número de liña. É dicir, o signo $ colócase despois da letra da columna, porque as súas coordenadas non cambian nin sequera nunha referencia relativa. Poñamos un exemplo:

1. Para cálculos precisos, introduza =D1/$D$3 e prema "Intro". O programa dá a resposta exacta.
2. Para mover a fórmula ás celas posteriores abaixo da columna e obter resultados precisos, arrastre o controlador ata a cela inferior.
3. Como resultado, o programa dará os cálculos correctos.

Atención! Se estableces o signo $ diante da letra, Excel dará un erro "#DIV/0!", o que significará que esta operación non se pode realizar.

Enderezo "superabsoluto".

Ao final, vexamos outro exemplo de ligazón absoluta: o enderezo "SuperAbsolute". Cales son as súas características e diferenzas. Colle o número aproximado 30 e inscríbeo na cela B2. É este número o que será o principal, é necesario realizar unha serie de accións con el, por exemplo, para elevalo a un poder.

1. Para a correcta execución de todas as accións, introduza a seguinte fórmula na columna C: =$B$2^$D2. Na columna D introducimos o valor dos graos.

2. Despois de premer o botón "Intro" e activar a fórmula, estiramos o marcador pola columna.
3. Obtemos os resultados correctos.

A conclusión é que todas as accións realizadas se referían a unha cela fixa B2, polo tanto:

• Ao copiar unha fórmula da cela C3 á cela E3, F3 ou H3 non cambiará o resultado. Permanecerá sen cambios: 900.
• Se precisa inserir unha nova columna, as coordenadas da cela coa fórmula cambiarán, pero o resultado tamén permanecerá sen cambios.

Esta é a peculiaridade da ligazón "SuperAbsolute": se necesitas mover o resultado non cambiará. Non obstante, hai situacións nas que se insire datos de fontes de terceiros. Así, as columnas desprázanse cara ao lado e os datos fíxanse ao xeito antigo na columna B2. Que pasa neste caso? Cando se mestura, a fórmula cambia segundo a acción realizada, é dicir, xa non apuntará a B2, senón a C2. Pero dado que a inserción se fixo en B2, o resultado final será incorrecto.

Referencia! Para poder inserir macros de fontes de terceiros, cómpre activar a configuración do programador (están desactivadas por defecto). Para iso, vaia a Opcións, abra a configuración da cinta e marque a caixa na columna da dereita fronte a "Desenvolvedor". Despois diso, abrirase o acceso a moitas funcións que antes estaban ocultas aos ollos do usuario medio.

Isto xera a pregunta: é posible modificar a fórmula da cela C2 para que o número orixinal se recolla da cela B, a pesar da inserción de novas columnas de datos? Para garantir que os cambios na táboa non afectan á determinación do total ao instalar datos de fontes de terceiros, debes realizar os seguintes pasos:

1. En lugar das coordenadas da cela B2, introduza os seguintes indicadores: =INDIRECT ("B2"). Como resultado, despois de mover a composición de formulación terá o seguinte aspecto: =INDIRECT(“B2”)^$E2.
2. Grazas a esta función, a ligazón sempre apunta ao cadrado coas coordenadas B2, independentemente de que se engadan ou eliminen columnas da táboa.

Debe entenderse que unha cela que non contén ningún dato sempre mostra o valor "0".

Conclusión

Grazas ao uso de tres variedades das ligazóns descritas, aparecen moitas oportunidades que facilitan o traballo con cálculos en Excel. Polo tanto, antes de comezar a traballar con fórmulas, lea primeiro as ligazóns e as regras para a súa instalación.