Hvordan fryse en celle i en Excel-formel

Ofte står brukere overfor behovet for å feste en celle i en formel. Det oppstår for eksempel i situasjoner der du ønsker å kopiere en formel, men slik at lenken ikke beveger seg opp og ned samme antall celler som den ble kopiert fra den opprinnelige plasseringen.

I dette tilfellet kan du fikse cellereferansen i Excel. Og dette kan gjøres på flere måter samtidig. La oss se nærmere på hvordan du kan nå dette målet.

Hva er en Excel-lenke

Arket består av celler. Hver av dem inneholder spesifikk informasjon. Andre celler kan bruke det i beregninger. Men hvordan forstår de hvor de skal hente dataene fra? Det hjelper dem å lage lenker.

Hver lenke angir en celle med én bokstav og ett tall. En bokstav representerer en kolonne og et tall representerer en rad. 

Det er tre typer lenker: absolutt, relativ og blandet. Den andre er satt som standard. En absolutt referanse er en som har en fast adresse for både en kolonne og en kolonne. Følgelig er blandet den der enten en separat kolonne eller en rad er fast.

1 metoden

Følg disse trinnene for å lagre både kolonne- og radadresser:

1. Klikk på cellen som inneholder formelen.
2. Klikk på formellinjen for cellen vi trenger.
3. Trykk på F4.

Som en konsekvens vil cellereferansen endres til absolutt. Det kan gjenkjennes på det karakteristiske dollartegnet. For eksempel, hvis du klikker på celle B2 og deretter klikker på F4, vil koblingen se slik ut: $B$2.

Hva betyr dollartegnet før delen av adressen per celle?

1. Hvis den er plassert foran en bokstav, indikerer den at kolonnereferansen forblir den samme, uansett hvor formelen er flyttet.
2. Hvis dollartegnet står foran tallet, indikerer det at strengen er festet. 

2 metoden

Denne metoden er nesten den samme som den forrige, bare du trenger å trykke på F4 to ganger. for eksempel, hvis vi hadde celle B2, vil den etter det bli B$2. Med enkle ord, på denne måten klarte vi å fikse streken. I dette tilfellet vil bokstaven i kolonnen endres.

Det er veldig praktisk, for eksempel i tabeller der du må vise innholdet i den andre cellen fra toppen i den nederste cellen. I stedet for å gjøre en slik formel mange ganger, er det nok å fikse raden og la kolonnen endre seg.

3 metoden

Dette er helt det samme som den forrige metoden, bare du trenger å trykke på F4-tasten tre ganger. Da vil bare referansen til kolonnen være absolutt, og raden forblir fast.

4 metoden

Anta at vi har en absolutt referanse til en celle, men her var det nødvendig å gjøre den relativ. For å gjøre dette, trykk F4-tasten så mange ganger at det ikke er noen $-tegn i lenken. Da blir det relativt, og når du flytter eller kopierer formelen vil både kolonneadressen og radadressen endres.

Feste celler for et stort område

Vi ser at metodene ovenfor ikke byr på noen vanskeligheter i det hele tatt å utføre. Men oppgavene er spesifikke. Og for eksempel, hva skal vi gjøre hvis vi har flere dusin formler samtidig, koblingene der må gjøres om til absolutte. 

Dessverre vil ikke standard Excel-metoder oppnå dette målet. For å gjøre dette, må du bruke et spesielt tillegg kalt VBA-Excel. Den inneholder mange tilleggsfunksjoner som lar deg utføre vanlige oppgaver med Excel mye raskere.

Den inneholder mer enn hundre brukerdefinerte funksjoner og 25 forskjellige makroer, og den oppdateres også jevnlig. Den lar deg forbedre arbeidet med nesten alle aspekter:

1. Celler.
2. Makro.
3. Funksjoner av forskjellige typer.
4. Lenker og arrays.

Spesielt lar dette tillegget deg fikse lenker i et stort antall formler samtidig. For å gjøre dette, må du utføre følgende trinn:

1. Velg et område.
2. Åpne VBA-Excel-fanen som vises etter installasjonen. 
3. Åpne "Funksjoner"-menyen, der alternativet "Lås formler" er plassert.
   

   

4. Deretter vises en dialogboks der du må spesifisere den nødvendige parameteren. Dette tillegget lar deg feste en kolonne og en kolonne separat sammen, og også fjerne en allerede eksisterende pinning med en pakke. Etter at den nødvendige parameteren er valgt med den tilsvarende alternativknappen, må du bekrefte handlingene dine ved å klikke på "OK".

Eksempel

La oss ta et eksempel for å gjøre det mer tydelig. La oss si at vi har informasjon som beskriver varekostnaden, dens totale mengde og salgsinntekter. Og vi står overfor oppgaven med å få tabellen, basert på mengde og kostnad, til automatisk å bestemme hvor mye penger vi klarte å tjene uten å trekke fra tap.

I vårt eksempel, for dette må du skrive inn formelen =B2*C2. Det er ganske enkelt, som du kan se. Det er veldig enkelt å bruke eksemplet hennes til å beskrive hvordan du kan fikse adressen til en celle eller dens individuelle kolonne eller rad. 

Selvfølgelig, i dette eksemplet, kan du prøve å dra formelen ned ved å bruke autofyll-markøren, men i dette tilfellet vil cellene automatisk endres. Så i celle D3 vil det være en annen formel, der tallene vil bli erstattet med henholdsvis 3. Videre, i henhold til skjemaet – D4 – vil formelen ha formen = B4 * C4, D5 – på samme måte, men med nummer 5 og så videre.

Hvis det er nødvendig (i de fleste tilfeller viser det seg), er det ingen problemer. Men hvis du trenger å fikse formelen i en celle slik at den ikke endres når du drar, vil dette være noe vanskeligere. 

Anta at vi må bestemme dollarinntektene. La oss legge det i celle B7. La oss bli litt nostalgiske og angi kostnadene på 35 rubler per dollar. Følgelig, for å bestemme inntektene i dollar, er det nødvendig å dele beløpet i rubler med dollarkursen.

Slik ser det ut i vårt eksempel.

Hvis vi, på samme måte som den forrige versjonen, prøver å foreskrive en formel, vil vi mislykkes. På samme måte vil formelen endres til den riktige. I vårt eksempel vil det være slik: =E3*B8. Herfra kan vi se. at den første delen av formelen har blitt til E3, og vi satte oss denne oppgaven, men vi trenger ikke å endre den andre delen av formelen til B8. Derfor må vi gjøre referansen om til en absolutt. Du kan gjøre dette uten å trykke på F4-tasten, bare ved å sette et dollartegn.

Etter at vi gjorde referansen til den andre cellen til en absolutt, ble den beskyttet mot endringer. Nå kan du trygt dra den ved hjelp av autofyllhåndtaket. Alle faste data vil forbli de samme, uavhengig av plasseringen av formelen, og ikke-forpliktet data vil endres fleksibelt. I alle celler vil inntekten i rubler beskrevet i denne linjen bli delt med samme dollarkurs.

Selve formelen vil se slik ut:

=D2/$B$7

Oppmerksomhet! Vi har angitt to dollartegn. På denne måten viser vi programmet at både kolonnen og raden må fikses.

Cellereferanser i makroer

En makro er en subrutine som lar deg automatisere handlinger. I motsetning til standardfunksjonaliteten til Excel, lar en makro deg umiddelbart sette en bestemt celle og utføre visse handlinger på bare noen få linjer med kode. Nyttig for batchbehandling av informasjon, for eksempel hvis det ikke er mulig å installere tillegg (for eksempel brukes en bedriftsdatamaskin, ikke en personlig).

Først må du forstå at nøkkelbegrepet til en makro er objekter som kan inneholde andre objekter. Workbooks-objektet er ansvarlig for den elektroniske boken (det vil si dokumentet). Det inkluderer Sheets-objektet, som er en samling av alle arkene i et åpent dokument. 

Følgelig er celler et Cells-objekt. Den inneholder alle cellene i et bestemt ark.

Hvert objekt er kvalifisert med argumenter i parentes. Når det gjelder celler, er de referert til i denne rekkefølgen. Radnummeret vises først, etterfulgt av kolonnenummeret eller bokstaven (begge formatene er akseptable).

For eksempel vil en kodelinje som inneholder en referanse til celle C5 se slik ut:

Arbeidsbøker(“Book2.xlsm”).Sheets(“List2”).Cells(5, 3)

Arbeidsbøker(“Book2.xlsm”).Sheets(“List2”).Cells(5, “C”)

Du kan også få tilgang til en celle ved hjelp av et objekt Ryddig. Generelt er det ment å gi en referanse til et område (hvis elementer forresten også kan være absolutte eller relative), men du kan ganske enkelt gi et cellenavn, i samme format som i et Excel-dokument.

I dette tilfellet vil linjen se slik ut.

Arbeidsbøker(“Book2.xlsm”).Sheets(“List2”). Range(“C5”)

Det kan virke som om dette alternativet er mer praktisk, men fordelen med de to første alternativene er at du kan bruke variabler i parentes og gi en kobling som ikke lenger er absolutt, men noe sånt som en relativ, som vil avhenge av resultatene av beregningene.

Dermed kan makroer effektivt brukes i programmer. Faktisk vil alle referanser til celler eller områder her være absolutte, og kan derfor også fikses med dem. Riktignok er det ikke så praktisk. Bruk av makroer kan være nyttig når du skriver komplekse programmer med et stort antall trinn i algoritmen. Generelt er standardmåten for å bruke absolutte eller relative referanser mye mer praktisk. 

Konklusjoner

Vi fant ut hva en cellereferanse er, hvordan den fungerer, hva den er for. Vi forsto forskjellen mellom absolutte og relative referanser og fant ut hva som må gjøres for å gjøre en type om til en annen (i enkle ord, fikse adressen eller løsne den). Vi fant ut hvordan du kan gjøre det umiddelbart med et stort antall verdier. Nå har du fleksibiliteten til å bruke denne funksjonen i de riktige situasjonene.