Enllaços a Excel: absoluts, relatius i mixts. Errors en treballar amb enllaços relatius a Excel

Per facilitar els càlculs mitjançant fórmules en mode automàtic, s'utilitzen referències a cel·les. Segons el tipus d'escriptura, es divideixen en tres tipus principals:

1. Enllaços relatius. S'utilitza per a càlculs senzills. Copiar una fórmula implica canviar les coordenades.
2. Enllaços absoluts. Si necessiteu fer càlculs més complexos, aquesta opció és adequada. Per arreglar, utilitzeu el símbol "$". Exemple: $A$1.
3. enllaços mixtes. Aquest tipus d'adreçament s'utilitza en els càlculs quan cal fixar una columna o una línia per separat. Exemple: $A1 o A$1.

Si cal copiar les dades de la fórmula introduïda, s'utilitzen referències amb adreçament absolut i mixt. L'article revelarà amb exemples com es fan els càlculs mitjançant diversos tipus d'enllaços.

Referència de cel·la relativa a Excel

Aquest és un conjunt de caràcters que defineixen la ubicació d'una cel·la. Els enllaços del programa s'escriuen automàticament amb adreçament relatiu. Per exemple: A1, A2, B1, B2. Moure's a una fila o columna diferent canvia els caràcters de la fórmula. Per exemple, la posició inicial A1. Moure's horitzontalment canvia la lletra a B1, C1, D1, etc. De la mateixa manera, els canvis es produeixen en moure's per una línia vertical, només en aquest cas el número canvia: A2, A3, A4, etc. Si és necessari duplicar un càlcul del mateix tipus en una cel·la adjacent, un càlcul es realitza mitjançant una referència relativa. Per utilitzar aquesta funció, seguiu uns quants passos:

1. Tan bon punt s'introdueixin les dades a la cel·la, moveu el cursor i feu clic amb el ratolí. Ressaltar amb un rectangle verd indica l'activació de la cel·la i la preparació per a un treball posterior.
2. En prémer la combinació de tecles Ctrl + C, copiem el contingut al porta-retalls.
3. Activem la cel·la a la qual voleu transferir dades o una fórmula prèviament escrita.
4. Prement la combinació Ctrl + V transferim les dades desades al porta-retalls del sistema.

Consell expert! Per dur a terme el mateix tipus de càlculs a la taula, utilitzeu un hack de vida. Seleccioneu la cel·la que conté la fórmula introduïda anteriorment. Passant el cursor per sobre d'un petit quadrat que apareix a l'extrem inferior dret i mantenint premut el botó esquerre del ratolí, arrossegueu a la fila inferior o a la columna extrema, segons l'acció realitzada. En deixar anar el botó del ratolí, el càlcul es realitzarà automàticament. Aquesta eina s'anomena marcador d'emplenament automàtic.

Exemple d'enllaç relatiu

Per fer-ho més clar, considereu un exemple de càlcul amb una fórmula amb una referència relativa. Suposem que el propietari d'una botiga d'esports després d'un any de treball ha de calcular el benefici de les vendes.

L'ordre de les accions:

1. L'exemple mostra que les columnes B i C es van utilitzar per completar la quantitat de mercaderies venudes i el seu preu. En conseqüència, per escriure la fórmula i obtenir la resposta, seleccioneu la columna D. La fórmula té aquest aspecte: = B2 *C

Presta atenció! Per facilitar el procés d'escriptura d'una fórmula, utilitzeu un petit truc. Posa el signe “=”, fes clic a la quantitat de mercaderies venudes, estableix el signe “*” i fes clic al preu del producte. La fórmula després del signe igual s'escriurà automàticament.

2. Per a la resposta final, premeu "Enter". A continuació, cal calcular l'import total dels beneficis rebuts d'altres tipus de productes. Bé, si el nombre de línies no és gran, llavors totes les manipulacions es poden realitzar manualment. Per omplir un gran nombre de files al mateix temps a Excel, hi ha una funció útil que permet transferir la fórmula a altres cel·les.
3. Mou el cursor per sobre de la cantonada inferior dreta del rectangle amb la fórmula o el resultat final. L'aparició d'una creu negra serveix com a senyal que el cursor es pot arrossegar cap avall. Així, es fa un càlcul automàtic del benefici rebut per a cada producte per separat.
4. En deixar anar el botó del ratolí premut, obtenim els resultats correctes en totes les línies.

En fer clic a la cel·la D3, podeu veure que les coordenades de la cel·la s'han canviat automàticament i ara es veuen així: =B3 *C3. Es dedueix que els enllaços eren relatius.

Possibles errors en treballar amb enllaços relatius

Sens dubte, aquesta funció d'Excel simplifica molt els càlculs, però en alguns casos poden sorgir dificultats. Considerem un exemple senzill de càlcul del coeficient de benefici de cada article de béns:

1. Creeu una taula i ompliu: A – nom del producte; B - quantitat venuda; C - cost; D és la quantitat rebuda. Suposem que només hi ha 11 articles a l'assortiment. Per tant, tenint en compte la descripció de les columnes, s'omplen 12 línies i l'import total del benefici és D
2. Feu clic a la cel·la E2 i entreu =D2/D13.
3. Després de prémer el botó "Enter", apareix el coeficient de la quota relativa de vendes del primer article.
4. Estireu la columna cap avall i espereu el resultat. Tanmateix, el sistema dóna l'error "#DIV/0!"

El motiu de l'error és l'ús d'una referència relativa per als càlculs. Com a resultat de copiar la fórmula, les coordenades canvien. És a dir, per a E3, la fórmula tindrà aquest aspecte =D3/D13. Com que la cel·la D13 no s'omple i teòricament té un valor zero, el programa donarà un error amb la informació que la divisió per zero és impossible.

Important Per corregir l'error, cal escriure la fórmula de manera que les coordenades D13 estiguin fixes. L'adreçament relatiu no té aquesta funcionalitat. Per fer-ho, hi ha un altre tipus d'enllaços: absoluts. 

Com es fa un enllaç absolut a Excel

Gràcies a l'ús del símbol $, va ser possible arreglar les coordenades de la cel·la. Com funciona això, ho considerarem més endavant. Com que el programa utilitza l'adreçament relatiu de manera predeterminada, en conseqüència, per fer-lo absolut, haureu de realitzar una sèrie d'accions. Analitzem la solució a l'error "Com trobar el coeficient de la venda de diversos articles de mercaderies", realitzant el càlcul mitjançant l'adreçament absolut:

1. Feu clic a E2 i introduïu les coordenades de l'enllaç =D2/D13. Com que l'enllaç és relatiu, cal establir un símbol per fixar les dades.
2. Fixeu les coordenades de la cel·la D Per realitzar aquesta acció, precediu la lletra que indica la columna i el número de fila col·locant un signe “$”.

Consell expert! Per facilitar la tasca d'entrada, n'hi ha prou amb activar la cel·la per editar la fórmula i prémer diverses vegades la tecla F4. Fins a obtenir uns valors satisfactoris. La fórmula correcta és la següent: =D2/$D$13.

3. Premeu el botó "Enter". Com a resultat de les accions realitzades, hauria d'aparèixer el resultat correcte.
4. Arrossegueu el marcador a la línia inferior per copiar la fórmula.

A causa de l'ús de l'adreçament absolut en els càlculs, els resultats finals de les files restants seran correctes.

Com posar un enllaç mixt a Excel

Per als càlculs de fórmules, no només s'utilitzen referències relatives i absolutes, sinó també mixtes. La seva característica distintiva és que fixen una de les coordenades.

• Per exemple, per canviar la posició d'una línia, heu d'escriure el signe $ davant de la designació de la lletra.
• Per contra, si el signe del dòlar s'escriu després de la designació de la lletra, els indicadors de la línia romandran sense canvis.

D'això se'n dedueix que per resoldre el problema anterior amb la determinació del coeficient de vendes de mercaderies mitjançant l'adreçament mixt, cal fixar el número de línia. És a dir, el signe $ es col·loca després de la lletra de la columna, perquè les seves coordenades no canvien ni tan sols en una referència relativa. Posem un exemple:

1. Per obtenir càlculs precisos, introduïu =D1/$D$3 i premeu "Enter". El programa dóna la resposta exacta.
2. Per moure la fórmula a les cel·les posteriors de la columna i obtenir resultats precisos, arrossegueu el mànec a la cel·la inferior.
3. Com a resultat, el programa donarà els càlculs correctes.

Atenció! Si poseu el signe $ davant de la lletra, l'Excel us donarà un error "#DIV/0!", la qual cosa significarà que aquesta operació no es pot realitzar.

Adreçament "superabsolut".

Al final, mirem un altre exemple d'enllaç absolut: l'adreçament "SuperAbsolute". Quines són les seves característiques i diferències. Agafeu el nombre aproximat 30 i introduïu-lo a la cel·la B2. És aquest número el que serà el principal, cal realitzar una sèrie d'accions amb ell, per exemple, per elevar-lo a una potència.

1. Per a l'execució correcta de totes les accions, introduïu la fórmula següent a la columna C: =$B$2^$D2. A la columna D introduïm el valor dels graus.

2. Després de prémer el botó "Enter" i activar la fórmula, estirem el marcador per la columna.
3. Obtenim els resultats correctes.

La conclusió és que totes les accions realitzades es refereixen a una cel·la fixa B2, per tant:

• Copiar una fórmula de la cel·la C3 a la cel·la E3, F3 o H3 no canviarà el resultat. Es mantindrà sense canvis: 900.
• Si necessiteu inserir una nova columna, les coordenades de la cel·la amb la fórmula canviaran, però el resultat també es mantindrà sense canvis.

Aquesta és la peculiaritat de l'enllaç "SuperAbsolute": si necessiteu moure el resultat no canviarà. Tanmateix, hi ha situacions en què s'insereixen dades de fonts de tercers. Així, les columnes es desplacen cap al costat i les dades s'estableixen de la manera antiga a la columna B2. Què passa en aquest cas? Quan es barreja, la fórmula canvia segons l'acció realitzada, és a dir, ja no apuntarà a B2, sinó a C2. Però com que la inserció es va fer a B2, el resultat final serà incorrecte.

Referència! Per poder inserir macros de fonts de tercers, heu d'activar la configuració del desenvolupador (estan desactivada per defecte). Per fer-ho, aneu a Opcions, obriu la configuració de la cinta i marqueu la casella de la columna dreta davant de "Desenvolupador". Després d'això, s'obrirà l'accés a moltes funcions que abans estaven amagades als ulls de l'usuari mitjà.

Això planteja la pregunta: és possible modificar la fórmula de la cel·la C2 de manera que el número original es reculli de la cel·la B, malgrat la inserció de noves columnes de dades? Per assegurar-vos que els canvis a la taula no afecten la determinació del total quan instal·leu dades de fonts de tercers, heu de realitzar els passos següents:

1. En lloc de les coordenades de la cel·la B2, introduïu els indicadors següents: =INDIRECT ("B2"). Com a resultat, després de moure la composició de formulació es veurà així: =INDIRECT(“B2”)^$E2.
2. Gràcies a aquesta funció, l'enllaç sempre apunta al quadrat amb les coordenades B2, independentment de si s'afegeixen o s'eliminen columnes a la taula.

S'ha d'entendre que una cel·la que no conté cap dada sempre mostra el valor “0”.

Conclusió

Gràcies a l'ús de tres varietats dels enllaços descrits, apareixen moltes oportunitats que faciliten el treball amb càlculs a Excel. Per tant, abans de començar a treballar amb fórmules, llegiu primer els enllaços i les regles per a la seva instal·lació.