Sådan fryser du en celle i en Excel-formel

Ofte står brugere over for behovet for at fastgøre en celle i en formel. Det sker for eksempel i situationer, hvor man vil kopiere en formel, men så linket ikke bevæger sig op og ned i det samme antal celler, som det blev kopieret fra sin oprindelige placering.

I dette tilfælde kan du rette cellereferencen i Excel. Og det kan gøres på flere måder på én gang. Lad os se nærmere på, hvordan man opnår dette mål.

Hvad er et Excel-link

Arket består af celler. Hver af dem indeholder specifikke oplysninger. Andre celler kan bruge det i beregninger. Men hvordan forstår de, hvor de skal hente data fra? Det hjælper dem med at lave links.

Hvert link angiver en celle med et bogstav og et tal. Et bogstav repræsenterer en kolonne og et tal repræsenterer en række. 

Der er tre typer links: absolut, relativ og blandet. Den anden er indstillet som standard. En absolut reference er en, der har en fast adresse på både en kolonne og en kolonne. Derfor er blandet den, hvor enten en separat kolonne eller en række er fastgjort.

1 metoden

Følg disse trin for at gemme både kolonne- og rækkeadresser:

1. Klik på cellen, der indeholder formlen.
2. Klik på formellinjen for den celle, vi skal bruge.
3. Tryk på F4.

Som en konsekvens vil cellereferencen ændres til absolut. Det kan genkendes på det karakteristiske dollartegn. For eksempel, hvis du klikker på celle B2 og derefter klikker på F4, vil linket se sådan ud: $B$2.

Hvad betyder dollartegnet før den del af adressen pr. celle?

1. Hvis det er placeret foran et bogstav, angiver det, at kolonnereferencen forbliver den samme, uanset hvor formlen er blevet flyttet.
2. Hvis dollartegnet er foran tallet, indikerer det, at strengen er fastgjort. 

2 metoden

Denne metode er næsten den samme som den forrige, kun du skal trykke på F4 to gange. for eksempel, hvis vi havde celle B2, så bliver det derefter til B$2. Med enkle ord lykkedes det på denne måde at rette linjen. I dette tilfælde ændres kolonnens bogstav.

Det er meget praktisk, for eksempel i tabeller, hvor du skal vise indholdet af den anden celle fra toppen i den nederste celle. I stedet for at lave en sådan formel mange gange, er det nok at rette rækken og lade kolonnen ændre sig.

3 metoden

Dette er fuldstændig det samme som den forrige metode, kun du skal trykke på F4-tasten tre gange. Så vil kun referencen til kolonnen være absolut, og rækken forbliver fast.

4 metoden

Antag, at vi har en absolut reference til en celle, men her var det nødvendigt at gøre den relativ. For at gøre dette skal du trykke på F4-tasten så mange gange, at der ikke er nogen $-tegn i linket. Så bliver det relativt, og når du flytter eller kopierer formlen, ændres både kolonneadressen og rækkeadressen.

Fastgørelse af celler til et stort område

Vi ser, at ovenstående metoder overhovedet ikke giver nogen vanskeligheder at udføre. Men opgaverne er specifikke. Og for eksempel, hvad skal vi gøre, hvis vi har flere dusin formler på én gang, hvor links skal omdannes til absolutte. 

Desværre vil standard Excel-metoder ikke nå dette mål. For at gøre dette skal du bruge en speciel tilføjelse kaldet VBA-Excel. Den indeholder mange ekstra funktioner, der giver dig mulighed for at udføre almindelige opgaver med Excel meget hurtigere.

Den indeholder mere end hundrede brugerdefinerede funktioner og 25 forskellige makroer, og den opdateres også jævnligt. Det giver dig mulighed for at forbedre arbejdet med næsten alle aspekter:

1. Celler.
2. Makro.
3. Funktioner af forskellige typer.
4. Links og arrays.

Især giver denne tilføjelse dig mulighed for at rette links i et stort antal formler på én gang. For at gøre dette skal du udføre følgende trin:

1. Vælg et område.
2. Åbn fanen VBA-Excel, der vises efter installationen. 
3. Åbn menuen "Funktioner", hvor indstillingen "Lås formler" er placeret.
   

   

4. Derefter vises en dialogboks, hvor du skal angive den ønskede parameter. Denne tilføjelse giver dig mulighed for at fastgøre en kolonne og en kolonne separat sammen og også fjerne en allerede eksisterende pinning med en pakke. Når den ønskede parameter er valgt ved hjælp af den tilsvarende alternativknap, skal du bekræfte dine handlinger ved at klikke på "OK".

Eksempel

Lad os tage et eksempel for at gøre det mere klart. Lad os sige, at vi har oplysninger, der beskriver varernes omkostninger, dens samlede mængde og salgsindtægter. Og vi står over for opgaven med at få tabellen ud fra mængden og omkostningerne til automatisk at bestemme, hvor mange penge vi nåede at tjene uden at fratrække tab.

I vores eksempel skal du indtaste formlen =B2*C2. Det er ret simpelt, som du kan se. Det er meget nemt at bruge hendes eksempel til at beskrive, hvordan du kan rette adressen på en celle eller dens individuelle kolonne eller række. 

Selvfølgelig kan du i dette eksempel prøve at trække formlen ned ved hjælp af autofyldmarkøren, men i dette tilfælde vil cellerne automatisk blive ændret. Så i celle D3 vil der være en anden formel, hvor tallene vil blive erstattet af henholdsvis 3. Yderligere vil formlen ifølge skemaet – D4 – have formen = B4 * C4, D5 – på samme måde, men med nummer 5 og så videre.

Hvis det er nødvendigt (i de fleste tilfælde viser det sig), så er der ingen problemer. Men hvis du skal rette formlen i én celle, så den ikke ændrer sig, når du trækker, så bliver det noget sværere. 

Antag, at vi skal bestemme dollarindtægten. Lad os sætte det i celle B7. Lad os blive lidt nostalgiske og angive prisen på 35 rubler per dollar. For at bestemme omsætningen i dollar er det derfor nødvendigt at dividere beløbet i rubler med dollarkursen.

Sådan ser det ud i vores eksempel.

Hvis vi i lighed med den tidligere version forsøger at foreskrive en formel, så mislykkes vi. På samme måde vil formlen ændres til den passende. I vores eksempel vil det være sådan her: =E3*B8. Herfra kan vi se. at den første del af formlen er blevet til E3, og vi stiller os denne opgave, men vi behøver ikke at ændre den anden del af formlen til B8. Derfor er vi nødt til at gøre referencen til en absolut en. Du kan gøre dette uden at trykke på F4-tasten, blot ved at sætte et dollartegn.

Efter at vi forvandlede referencen til den anden celle til en absolut, blev den beskyttet mod ændringer. Nu kan du trygt trække det ved hjælp af autofyldhåndtaget. Alle faste data forbliver de samme, uanset formlens placering, og ikke-forpligtede data vil ændre sig fleksibelt. I alle celler vil omsætningen i rubler beskrevet i denne linje blive divideret med den samme dollarkurs.

Selve formlen vil se sådan ud:

=D2/$B$7

OBS! Vi har angivet to dollartegn. På den måde viser vi programmet, at både kolonnen og rækken skal rettes.

Cellereferencer i makroer

En makro er en underrutine, der giver dig mulighed for at automatisere handlinger. I modsætning til standardfunktionaliteten i Excel giver en makro dig mulighed for straks at indstille en specifik celle og udføre visse handlinger på blot et par linjer kode. Nyttigt til batchbehandling af information, for eksempel, hvis der ikke er nogen måde at installere tilføjelser på (f.eks. bruges en firmacomputer, ikke en personlig).

Først skal du forstå, at nøglekonceptet for en makro er objekter, der kan indeholde andre objekter. Workbooks-objektet er ansvarlig for den elektroniske bog (det vil sige dokumentet). Det inkluderer Sheets-objektet, som er en samling af alle ark i et åbent dokument. 

Derfor er celler et Cells-objekt. Det indeholder alle cellerne i et bestemt ark.

Hvert objekt er kvalificeret med argumenter i parentes. I tilfælde af celler henvises til dem i denne rækkefølge. Rækkenummeret vises først, efterfulgt af kolonnenummeret eller bogstavet (begge formater er acceptable).

For eksempel vil en kodelinje, der indeholder en reference til celle C5, se sådan ud:

Arbejdsbøger(“Book2.xlsm”).Sheets(“List2”).Cells(5, 3)

Arbejdsbøger(“Book2.xlsm”).Sheets(“List2”).Cells(5, “C”)

Du kan også få adgang til en celle ved hjælp af et objekt Ryddelig. Generelt er det meningen at give en reference til et område (hvis elementer i øvrigt også kan være absolutte eller relative), men du kan blot give et cellenavn, i samme format som i et Excel-dokument.

I dette tilfælde vil linjen se sådan ud.

Projektmapper(“Book2.xlsm”).Sheets(“List2”).Range(“C5”)

Det kan se ud til, at denne mulighed er mere praktisk, men fordelen ved de to første muligheder er, at du kan bruge variabler i parentes og give et link, der ikke længere er absolut, men noget i retning af et relativt, hvilket vil afhænge af resultaterne af beregningerne.

Makroer kan således effektivt bruges i programmer. Faktisk vil alle referencer til celler eller områder her være absolutte og kan derfor også rettes med dem. Sandt nok er det ikke så praktisk. Brugen af ​​makroer kan være nyttig, når du skriver komplekse programmer med et stort antal trin i algoritmen. Generelt er standardmåden at bruge absolutte eller relative referencer meget mere bekvem. 

konklusioner

Vi fandt ud af, hvad en cellereference er, hvordan den virker, hvad den er til. Vi forstod forskellen mellem absolutte og relative referencer og fandt ud af, hvad der skal gøres for at gøre en type til en anden (i enkle ord, ret adressen eller frigør den). Vi fandt ud af, hvordan du kan gøre det med det samme med et stort antal værdier. Nu har du fleksibiliteten til at bruge denne funktion i de rigtige situationer.