Hur man fryser en cell i en Excel-formel

Ofta ställs användare inför behovet av att fästa en cell i en formel. Det förekommer till exempel i situationer där du vill kopiera en formel, men för att länken inte ska flytta upp och ner i samma antal celler som den kopierades från sin ursprungliga plats.

I det här fallet kan du fixa cellreferensen i Excel. Och detta kan göras på flera sätt samtidigt. Låt oss ta en närmare titt på hur man uppnår detta mål.

Vad är en Excel-länk

Arket består av celler. Var och en av dem innehåller specifik information. Andra celler kan använda det i beräkningar. Men hur förstår de var de ska få uppgifterna ifrån? Det hjälper dem att skapa länkar.

Varje länk betecknar en cell med en bokstav och en siffra. En bokstav representerar en kolumn och en siffra representerar en rad. 

Det finns tre typer av länkar: absoluta, relativa och blandade. Den andra är inställd som standard. En absolut referens är en som har en fast adress för både en kolumn och en kolumn. Följaktligen är blandad den där antingen en separat kolumn eller en rad är fixerad.

1-metoden

Följ dessa steg för att spara både kolumn- och radadresser:

1. Klicka på cellen som innehåller formeln.
2. Klicka på formelfältet för den cell vi behöver.
3. Tryck på F4.

Som en konsekvens kommer cellreferensen att ändras till absolut. Det kan kännas igen på det karakteristiska dollartecknet. Om du till exempel klickar på cell B2 och sedan klickar på F4, kommer länken att se ut så här: $B$2.

Vad betyder dollartecknet före delen av adressen per cell?

1. Om den placeras framför en bokstav indikerar den att kolumnreferensen förblir densamma, oavsett var formeln har flyttats.
2. Om dollartecknet står framför numret indikerar det att strängen är nålad. 

2-metoden

Denna metod är nästan densamma som den föregående, bara du behöver trycka på F4 två gånger. till exempel, om vi hade cell B2, så blir det B$2 efter det. Med enkla ord, på så sätt lyckades vi fixa linjen. I det här fallet kommer bokstaven i kolumnen att ändras.

Det är väldigt praktiskt, till exempel i tabeller där du behöver visa innehållet i den andra cellen från toppen i den nedre cellen. Istället för att göra en sådan formel många gånger räcker det att fixa raden och låta kolumnen ändras.

3-metoden

Detta är helt samma som den tidigare metoden, bara du behöver trycka på F4-tangenten tre gånger. Då är endast referensen till kolumnen absolut, och raden förblir fixerad.

4-metoden

Anta att vi har en absolut referens till en cell, men här var det nödvändigt att göra den relativ. För att göra detta, tryck på F4-tangenten så många gånger att det inte finns några $-tecken i länken. Då blir det relativt, och när du flyttar eller kopierar formeln ändras både kolumnadressen och radadressen.

Fästa celler för ett stort intervall

Vi ser att ovanstående metoder inte ger några som helst svårigheter att utföra. Men uppgifterna är specifika. Och till exempel, vad ska man göra om vi har flera dussin formler samtidigt, där länkarna måste omvandlas till absoluta. 

Tyvärr kommer standard Excel-metoder inte att uppnå detta mål. För att göra detta måste du använda ett speciellt tillägg som heter VBA-Excel. Den innehåller många ytterligare funktioner som gör att du kan utföra vanliga uppgifter med Excel mycket snabbare.

Den innehåller mer än hundra användardefinierade funktioner och 25 olika makron, och den uppdateras också regelbundet. Det låter dig förbättra arbetet med nästan alla aspekter:

1. Celler.
2. Macro.
3. Funktioner av olika slag.
4. Länkar och arrayer.

I synnerhet låter detta tillägg dig fixa länkar i ett stort antal formler samtidigt. För att göra detta måste du utföra följande steg:

1. Välj ett intervall.
2. Öppna VBA-Excel-fliken som visas efter installationen. 
3. Öppna menyn "Funktioner", där alternativet "Lås formler" finns.
   

   

4. Därefter visas en dialogruta där du måste ange önskad parameter. Det här tillägget låter dig fästa en kolumn och en kolumn separat, tillsammans, och även ta bort en redan befintlig pinning med ett paket. När den önskade parametern har valts med motsvarande alternativknapp måste du bekräfta dina åtgärder genom att klicka på "OK".

Exempelvis

Låt oss ta ett exempel för att göra det tydligare. Låt oss säga att vi har information som beskriver kostnaden för varor, dess totala kvantitet och försäljningsintäkter. Och vi står inför uppgiften att få tabellen, baserat på kvantitet och kostnad, automatiskt att avgöra hur mycket pengar vi lyckades tjäna utan att dra av förluster.

I vårt exempel måste du ange formeln =B2*C2. Det är ganska enkelt, som du kan se. Det är väldigt enkelt att använda hennes exempel för att beskriva hur du kan fixa adressen till en cell eller dess individuella kolumn eller rad. 

Naturligtvis kan du i det här exemplet försöka dra formeln nedåt med autofyllmarkören, men i det här fallet kommer cellerna att ändras automatiskt. Så i cell D3 kommer det att finnas en annan formel, där siffrorna kommer att ersättas med 3. Vidare, enligt schemat – D4 – kommer formeln att ha formen = B4 * C4, D5 – på liknande sätt, men med nummer 5 och så vidare.

Om det är nödvändigt (i de flesta fall visar det sig), så finns det inga problem. Men om du behöver fixa formeln i en cell så att den inte ändras när du drar, så blir detta något svårare. 

Anta att vi måste bestämma dollarintäkterna. Låt oss lägga det i cell B7. Låt oss bli lite nostalgiska och ange kostnaden för 35 rubel per dollar. Följaktligen, för att bestämma intäkterna i dollar, är det nödvändigt att dela beloppet i rubel med dollarkursen.

Så här ser det ut i vårt exempel.

Om vi, i likhet med den tidigare versionen, försöker ordinera en formel, kommer vi att misslyckas. På samma sätt kommer formeln att ändras till den lämpliga. I vårt exempel blir det så här: =E3*B8. Härifrån kan vi se. att den första delen av formeln har förvandlats till E3, och vi ställer oss denna uppgift, men vi behöver inte ändra den andra delen av formeln till B8. Därför måste vi göra referensen till en absolut sådan. Du kan göra detta utan att trycka på F4-tangenten, bara genom att sätta ett dollartecken.

Efter att vi gjorde referensen till den andra cellen till en absolut, blev den skyddad från förändringar. Nu kan du säkert dra den med autofyllhandtaget. Alla fasta data kommer att förbli desamma, oavsett placeringen av formeln, och oengagerad data kommer att förändras flexibelt. I alla celler kommer intäkterna i rubel som beskrivs i denna rad att delas med samma dollarkurs.

Formeln i sig kommer att se ut så här:

=D2/$B$7

Observera! Vi har angett två dollartecken. På så sätt visar vi programmet att både kolumnen och raden behöver fixas.

Cellreferenser i makron

Ett makro är en subrutin som låter dig automatisera åtgärder. Till skillnad från standardfunktionaliteten i Excel låter ett makro dig omedelbart ställa in en specifik cell och utföra vissa åtgärder på bara några rader kod. Användbar för batchbearbetning av information, till exempel om det inte finns något sätt att installera tillägg (till exempel en företagsdator används, inte en personlig).

Först måste du förstå att nyckelbegreppet för ett makro är objekt som kan innehålla andra objekt. Workbooks-objektet ansvarar för den elektroniska boken (det vill säga dokumentet). Det inkluderar Sheets-objektet, som är en samling av alla ark i ett öppet dokument. 

Följaktligen är celler ett Cells-objekt. Den innehåller alla celler i ett visst ark.

Varje objekt är kvalificerat med argument inom parentes. När det gäller celler refereras de till i denna ordning. Radnumret listas först, följt av kolumnnumret eller bokstaven (båda formaten är acceptabla).

Till exempel skulle en kodrad som innehåller en referens till cell C5 se ut så här:

Arbetsböcker(“Book2.xlsm”).Sheets(“List2”).Cells(5, 3)

Arbetsböcker(“Book2.xlsm”).Sheets(“List2”).Cells(5, “C”)

Du kan också komma åt en cell med hjälp av ett objekt Städa. I allmänhet är det tänkt att ge en referens till ett intervall (vars element för övrigt också kan vara absoluta eller relativa), men du kan helt enkelt ge ett cellnamn, i samma format som i ett Excel-dokument.

I det här fallet kommer linjen att se ut så här.

Arbetsböcker(“Book2.xlsm”).Sheets(“List2”).Range(“C5”)

Det kan tyckas att det här alternativet är bekvämare, men fördelen med de två första alternativen är att du kan använda variabler inom parentes och ge en länk som inte längre är absolut, utan något som liknar en relativ, vilket beror på resultaten av beräkningarna.

Således kan makron effektivt användas i program. Faktum är att alla referenser till celler eller intervall här kommer att vara absoluta och kan därför även fixas med dem. Det är sant att det inte är så bekvämt. Användningen av makron kan vara användbar när man skriver komplexa program med ett stort antal steg i algoritmen. I allmänhet är standardsättet att använda absoluta eller relativa referenser mycket bekvämare. 

Slutsatser

Vi kom på vad en cellreferens är, hur den fungerar, vad den är till för. Vi förstod skillnaden mellan absoluta och relativa referenser och kom på vad som måste göras för att förvandla en typ till en annan (i enkla ord, fixa adressen eller lossa den). Vi kom på hur du kan göra det direkt med ett stort antal värden. Nu har du flexibiliteten att använda den här funktionen i rätt situationer.