Com congelar una cel·la en una fórmula d'Excel

Sovint, els usuaris s'enfronten a la necessitat de fixar una cel·la en una fórmula. Per exemple, es produeix en situacions en què es vol copiar una fórmula, però perquè l'enllaç no es mogui cap amunt i cap avall el mateix nombre de cel·les que es va copiar des de la seva ubicació original.

En aquest cas, podeu corregir la referència de la cel·la a Excel. I això es pot fer de diverses maneres alhora. Vegem més de prop com assolir aquest objectiu.

Què és un enllaç d'Excel

La làmina està formada per cèl·lules. Cadascun d'ells conté informació específica. Altres cel·les poden utilitzar-lo en càlculs. Però, com entenen d'on obtenir les dades? Els ajuda a fer enllaços.

Cada enllaç designa una cel·la amb una lletra i un número. Una lletra representa una columna i un número representa una fila. 

Hi ha tres tipus d'enllaços: absoluts, relatius i mixtos. El segon està configurat per defecte. Una referència absoluta és aquella que té una adreça fixa tant d'una columna com d'una columna. En conseqüència, mixt és aquell on es fixa una columna o una fila separades.

El mètode 1

Per desar les adreces de columna i fila, seguiu aquests passos:

1. Feu clic a la cel·la que conté la fórmula.
2. Feu clic a la barra de fórmules de la cel·la que necessitem.
3. Premeu F4.

Com a conseqüència, la referència de la cel·la canviarà a absoluta. Es pot reconèixer pel signe característic del dòlar. Per exemple, si feu clic a la cel·la B2 i després feu clic a F4, l'enllaç tindrà aquest aspecte: $B$2.

Què significa el signe del dòlar abans de la part de l'adreça per cel·la?

1. Si es col·loca davant d'una lletra, indica que la referència de la columna continua sent la mateixa, independentment d'on s'hagi mogut la fórmula.
2. Si el signe del dòlar està davant del número, indica que la cadena està fixada. 

El mètode 2

Aquest mètode és gairebé el mateix que l'anterior, només cal que premeu F4 dues vegades. per exemple, si tinguéssim la cel·la B2, després es convertirà en B$2. En paraules senzilles, d'aquesta manera vam aconseguir arreglar la línia. En aquest cas, la lletra de la columna canviarà.

És molt convenient, per exemple, a les taules on cal mostrar el contingut de la segona cel·la des de la part superior a la cel·la inferior. En lloc de fer aquesta fórmula moltes vegades, n'hi ha prou amb arreglar la fila i deixar que la columna canviï.

El mètode 3

És completament el mateix que el mètode anterior, només cal que premeu la tecla F4 tres vegades. Aleshores només la referència a la columna serà absoluta i la fila romandrà fixa.

El mètode 4

Suposem que tenim una referència absoluta a una cel·la, però aquí calia fer-la relativa. Per fer-ho, premeu la tecla F4 tantes vegades que no hi hagi cap signe $ a l'enllaç. Aleshores esdevindrà relatiu i, quan moveu o copieu la fórmula, tant l'adreça de la columna com l'adreça de la fila canviaran.

Fixació de cel·les per a un gran rang

Veiem que els mètodes anteriors no presenten cap dificultat per dur a terme. Però les tasques són específiques. I, per exemple, què fer si tenim diverses desenes de fórmules alhora, els enllaços en els quals cal convertir-los en absoluts. 

Malauradament, els mètodes estàndard d'Excel no aconseguiran aquest objectiu. Per fer-ho, heu d'utilitzar un complement especial anomenat VBA-Excel. Conté moltes funcions addicionals que us permeten realitzar tasques habituals amb Excel molt més ràpidament.

Inclou més d'un centenar de funcions definides per l'usuari i 25 macros diferents, i també s'actualitza regularment. Et permet millorar el treball amb gairebé qualsevol aspecte:

1. Cèl · lules.
2. macro.
3. Funcions de diferents tipus.
4. Enllaços i matrius.

En particular, aquest complement us permet arreglar enllaços en un gran nombre de fórmules alhora. Per fer-ho, heu de realitzar els passos següents:

1. Seleccioneu un interval.
2. Obriu la pestanya VBA-Excel que apareixerà després de la instal·lació. 
3. Obriu el menú "Funcions", on es troba l'opció "Bloquejar fórmules".
   

   

4. A continuació, apareixerà un quadre de diàleg en el qual haureu d'especificar el paràmetre requerit. Aquest complement us permet fixar una columna i una columna per separat, junts, i també eliminar una fixació ja existent amb un paquet. Després de seleccionar el paràmetre necessari mitjançant el botó d'opció corresponent, heu de confirmar les vostres accions fent clic a "D'acord".

exemple

Posem un exemple per fer-ho més clar. Suposem que tenim informació que descriu el cost dels béns, la seva quantitat total i els ingressos per vendes. I ens trobem davant la tasca de fer que la taula, en funció de la quantitat i el cost, determini automàticament quants diners hem aconseguit guanyar sense deduir pèrdues.

Al nostre exemple, per a això cal introduir la fórmula =B2*C2. És bastant senzill, com podeu veure. És molt fàcil utilitzar el seu exemple per descriure com podeu arreglar l'adreça d'una cel·la o la seva columna o fila individual. 

Per descomptat, en aquest exemple, podeu provar d'arrossegar la fórmula cap avall amb el marcador d'emplenament automàtic, però en aquest cas, les cel·les es canviaran automàticament. Per tant, a la cel·la D3 hi haurà una altra fórmula, on els nombres se substituiran, respectivament, per 3. A més, segons l'esquema - D4 - la fórmula tindrà la forma = B4 * C4, D5 - de la mateixa manera, però amb el número 5 i així successivament.

Si és necessari (en la majoria dels casos resulta), no hi ha problemes. Però si necessiteu arreglar la fórmula en una cel·la perquè no canviï en arrossegar, serà una mica més difícil. 

Suposem que hem de determinar els ingressos en dòlars. Posem-ho a la cel·la B7. Posem-nos una mica de nostàlgia i indiquem el cost de 35 rubles per dòlar. En conseqüència, per determinar els ingressos en dòlars, cal dividir la quantitat en rubles pel tipus de canvi del dòlar.

Aquí teniu el que sembla al nostre exemple.

Si, de manera similar a la versió anterior, intentem prescriure una fórmula, aleshores fallarem. De la mateixa manera, la fórmula canviarà a la corresponent. En el nostre exemple, serà així: =E3*B8. Des d'aquí podem veure. que la primera part de la fórmula s'ha convertit en E3, i ens hem proposat aquesta tasca, però no cal canviar la segona part de la fórmula per B8. Per tant, hem de convertir la referència en una absoluta. Podeu fer-ho sense prémer la tecla F4, només posant un signe de dòlar.

Després de convertir la referència a la segona cel·la en una d'absoluta, es va protegir dels canvis. Ara podeu arrossegar-lo amb seguretat mitjançant la nansa d'emplenament automàtic. Totes les dades fixes continuaran sent les mateixes, independentment de la posició de la fórmula, i les dades no compromeses canviaran de manera flexible. A totes les cel·les, els ingressos en rubles descrits en aquesta línia es dividiran pel mateix tipus de canvi del dòlar.

La fórmula en si serà així:

=D2/$B$7

Atenció! Hem indicat dos signes de dòlar. D'aquesta manera, mostrem al programa que cal arreglar tant la columna com la fila.

Referències de cel·les en macros

Una macro és una subrutina que permet automatitzar accions. A diferència de la funcionalitat estàndard d'Excel, una macro us permet establir immediatament una cel·la específica i realitzar determinades accions en poques línies de codi. Útil per al processament per lots d'informació, per exemple, si no hi ha manera d'instal·lar complements (per exemple, s'utilitza un ordinador de l'empresa, no un personal).

Primer cal entendre que el concepte clau d'una macro són els objectes que poden contenir altres objectes. L'objecte Workbooks és responsable del llibre electrònic (és a dir, el document). Inclou l'objecte Sheets, que és una col·lecció de tots els fulls d'un document obert. 

En conseqüència, les cèl·lules són un objecte Cells. Conté totes les cel·les d'un full concret.

Cada objecte està qualificat amb arguments entre parèntesis. En el cas de les cel·les, es fan referència en aquest ordre. Primer apareix el número de fila, seguit del número de la columna o la lletra (ambdós formats són acceptables).

Per exemple, una línia de codi que contingui una referència a la cel·la C5 seria així:

Llibres de treball ("Llibre2.xlsm"). Fulls ("Llista2"). Cel·les (5, 3)

Llibres de treball ("Llibre2.xlsm"). Fulls ("Llista2"). Cel·les (5, "C")

També podeu accedir a una cel·la mitjançant un objecte Endreçat. En general, es pretén donar una referència a un rang (els elements, per cert, també poden ser absoluts o relatius), però simplement es pot donar un nom de cel·la, en el mateix format que en un document Excel.

En aquest cas, la línia es veurà així.

Llibres de treball ("Llibre2.xlsm"). Fulls ("Llista2"). Interval ("C5")

Pot semblar que aquesta opció és més convenient, però l'avantatge de les dues primeres opcions és que podeu utilitzar variables entre parèntesis i donar un enllaç que ja no és absolut, sinó una cosa semblant a un relatiu, que dependrà dels resultats de els càlculs.

Així, les macros es poden utilitzar de manera efectiva als programes. De fet, totes les referències a cel·les o intervals aquí seran absolutes i, per tant, també es poden arreglar amb elles. És cert que no és tan convenient. L'ús de macros pot ser útil a l'hora d'escriure programes complexos amb un gran nombre de passos en l'algorisme. En general, la forma estàndard d'utilitzar referències absolutes o relatives és molt més convenient. 

Conclusions

Hem esbrinat què és una referència de cel·la, com funciona, per a què serveix. Vam entendre la diferència entre les referències absolutes i les relatives i vam esbrinar què cal fer per convertir un tipus en un altre (en paraules senzilles, arregla l'adreça o desenganxa-la). Hem descobert com podeu fer-ho immediatament amb un gran nombre de valors. Ara teniu la flexibilitat d'utilitzar aquesta funció en les situacions adequades.