Innehåll
För att underlätta beräkningar med formler i automatiskt läge används referenser till celler. Beroende på typ av skrift är de indelade i tre huvudtyper:
- Relativa länkar. Används för enkla beräkningar. Att kopiera en formel innebär att koordinaterna ändras.
- Absoluta länkar. Om du behöver göra mer komplexa beräkningar är det här alternativet lämpligt. För fixering använd symbolen "$". Exempel: $A$1.
- blandade länkar. Denna typ av adressering används i beräkningar när det är nödvändigt att fixa en kolumn eller rad separat. Exempel: $A1 eller A$1.
Om det är nödvändigt att kopiera data från den angivna formeln används referenser med absolut och blandad adressering. Artikeln kommer med exempel att avslöja hur beräkningar görs med olika typer av länkar.
Relativ cellreferens i Excel
Detta är en uppsättning tecken som definierar platsen för en cell. Länkar i programmet skrivs automatiskt med relativ adressering. Till exempel: A1, A2, B1, B2. Om du flyttar till en annan rad eller kolumn ändras tecknen i formeln. Till exempel startposition A1. Att flytta sig horisontellt ändrar bokstaven till B1, C1, D1, etc. På samma sätt sker förändringar när man flyttar längs en vertikal linje, bara i detta fall ändras siffran – A2, A3, A4, etc. Om det är nödvändigt att duplicera en beräkning av samma typ i en intilliggande cell, en beräkning utförs med hjälp av en relativ referens. Följ några steg för att använda den här funktionen:
- Så snart informationen matats in i cellen, flytta markören och klicka med musen. Att markera med en grön rektangel indikerar aktiveringen av cellen och beredskapen för vidare arbete.
- Genom att trycka på tangentkombinationen Ctrl + C kopierar vi innehållet till klippbordet.
- Vi aktiverar cellen som du vill överföra data till eller en tidigare skriven formel.
- Genom att trycka på kombinationen Ctrl + V överför vi data som sparats i systemets urklipp.
Expertråd! För att utföra samma typ av beräkningar i tabellen, använd ett life hack. Markera cellen som innehåller den tidigare angivna formeln. Håll markören över en liten fyrkant som visas i det nedre högra hörnet och håll nere vänster musknapp, dra till den nedersta raden eller extremkolumnen, beroende på vilken åtgärd som utförs. Genom att släppa musknappen utförs beräkningen automatiskt. Detta verktyg kallas för automatisk fyllningsmarkör.
Relativ länk exempel
För att göra det tydligare, överväg ett exempel på en beräkning med en formel med en relativ referens. Antag att ägaren till en sportbutik efter ett års arbete behöver beräkna vinsten från försäljningen.
Handlingsordningen:
- Exemplet visar att kolumn B och C användes för att fylla i mängden sålda varor och dess pris. Följaktligen, för att skriva formeln och få svaret, välj kolumn D. Formeln ser ut så här: = B2 *C
Var uppmärksam! För att underlätta processen att skriva en formel, använd ett litet knep. Sätt "="-tecknet, klicka på kvantiteten sålda varor, ställ in "*"-tecknet och klicka på priset på produkten. Formeln efter likhetstecknet skrivs automatiskt.
- För det sista svaret, tryck på "Enter". Därefter måste du beräkna det totala beloppet för vinst från andra typer av produkter. Tja, om antalet rader inte är stort, kan alla manipulationer utföras manuellt. För att fylla i ett stort antal rader samtidigt i Excel finns det en användbar funktion som gör det möjligt att överföra formeln till andra celler.
- Flytta markören över det nedre högra hörnet av rektangeln med formeln eller det färdiga resultatet. Utseendet på ett svart kors fungerar som en signal om att markören kan dras nedåt. Således görs en automatisk beräkning av vinsten som erhålls för varje produkt separat.
- Genom att släppa den nedtryckta musknappen får vi rätt resultat i alla rader.
Genom att klicka på cell D3 kan du se att cellkoordinaterna har ändrats automatiskt och ser nu ut så här: =B3 *C3. Av detta följer att länkarna var relativa.
Möjliga fel vid arbete med relativa länkar
Utan tvekan förenklar denna Excel-funktion beräkningarna avsevärt, men i vissa fall kan svårigheter uppstå. Låt oss överväga ett enkelt exempel på att beräkna vinstkoefficienten för varje vara:
- Skapa en tabell och fyll i: A – produktnamn; B – såld kvantitet; C – kostnad; D är det mottagna beloppet. Anta att det bara finns 11 artiklar i sortimentet. Därför, med hänsyn till beskrivningen av kolumnerna, fylls 12 rader och den totala vinsten är D
- Klicka på cell E2 och skriv in =D2 /D3100.
- Efter att ha tryckt på "Enter"-knappen visas koefficienten för den relativa andelen av försäljningen av den första artikeln.
- Sträck kolonnen nedåt och vänta på resultatet. Systemet ger dock felet "#DIV/0!"
Orsaken till felet är användningen av en relativ referens för beräkningar. Som ett resultat av att formeln kopieras ändras koordinaterna. Det vill säga, för E3 kommer formeln att se ut så här =D3 /D3100. Eftersom cell D13 inte är fylld och teoretiskt har ett nollvärde kommer programmet att ge ett fel med informationen att division med noll är omöjlig.
Viktigt! För att korrigera felet är det nödvändigt att skriva formeln på ett sådant sätt att D13-koordinaterna är fixerade. Relativ adressering har ingen sådan funktion. För att göra detta finns det en annan typ av länkar – absolut.
Hur gör du en absolut länk i Excel
Tack vare användningen av $-symbolen blev det möjligt att fixa cellkoordinaterna. Hur detta fungerar kommer vi att överväga vidare. Eftersom programmet använder relativ adressering som standard, för att göra den absolut, måste du utföra ett antal åtgärder. Låt oss analysera lösningen på felet "Hur man hittar koefficienten från försäljningen av flera varor", utför beräkningen med absolut adressering:
- Klicka på E2 och ange koordinaterna för länken =D2 /D3100. Eftersom länken är relativ måste en symbol ställas in för att fixa data.
- Fixa koordinaterna för cell D För att utföra den här åtgärden, före bokstaven som anger kolumnen och radnumret genom att placera ett "$"-tecken.
Expertråd! För att underlätta uppgiften att gå in räcker det att aktivera cellen för att redigera formeln och trycka på F4-tangenten flera gånger. Tills du får tillfredsställande värden. Den korrekta formeln är följande: =D2/$D$13.
- Tryck på "Enter"-knappen. Som ett resultat av de utförda åtgärderna bör det korrekta resultatet visas.
- Dra markören till den nedre raden för att kopiera formeln.
På grund av användningen av absolut adressering i beräkningarna kommer slutresultaten i de återstående raderna att vara korrekta.
Hur man lägger en blandad länk i Excel
För formelberäkningar används inte bara relativa och absoluta referenser utan även blandade. Deras särdrag är att de fixerar en av koordinaterna.
- För att till exempel ändra positionen för en linje måste du skriva $-tecknet framför bokstavsbeteckningen.
- Tvärtom, om dollartecknet skrivs efter bokstavsbeteckningen, kommer indikatorerna i raden att förbli oförändrade.
Av detta följer att för att lösa det tidigare problemet med att bestämma koefficienten för försäljning av varor med hjälp av blandad adressering, är det nödvändigt att fixa radnumret. Det vill säga, $-tecknet placeras efter kolumnbokstaven, eftersom dess koordinater inte ändras ens i en relativ referens. Låt oss ta ett exempel:
- Ange för korrekta beräkningar =D1/$D$3 och tryck på "Enter". Programmet ger det exakta svaret.
- För att flytta formeln till efterföljande celler ner i kolumnen och få korrekta resultat, dra handtaget till den nedre cellen.
- Som ett resultat kommer programmet att ge de korrekta beräkningarna.
Observera! Om du ställer in $-tecknet framför bokstaven, kommer Excel att ge ett felmeddelande "#DIV/0!", vilket innebär att denna operation inte kan utföras.
"SuperAbsolute" adressering
Till sist, låt oss titta på ett annat exempel på en absolut länk – "SuperAbsolute"-adressering. Vilka är dess egenskaper och skillnader. Ta det ungefärliga talet 30 och ange det i cell B2. Det är detta nummer som kommer att vara det viktigaste, det är nödvändigt att utföra en serie åtgärder med det, till exempel för att höja det till en makt.
- För korrekt utförande av alla åtgärder, skriv in följande formel i kolumn C: =$B$2^$D2. I kolumn D anger vi värdet på graderna.
- Efter att ha tryckt på "Enter"-knappen och aktiverat formeln sträcker vi markören ner i kolumnen.
- Vi får rätt resultat.
Summan av kardemumman är att alla utförda åtgärder hänvisade till en fast cell B2, därför:
- Att kopiera en formel från cell C3 till cell E3, F3 eller H3 kommer inte att ändra resultatet. Det kommer att förbli oförändrat – 900.
- Om du behöver infoga en ny kolumn kommer koordinaterna för cellen med formeln att ändras, men resultatet kommer också att förbli oförändrat.
Detta är det speciella med länken "SuperAbsolute": om du behöver flytta kommer resultatet inte att förändras. Det finns dock situationer när data infogas från tredjepartskällor. Således flyttas kolumnerna åt sidan, och data ställs in på det gamla sättet i kolumn B2. Vad händer i det här fallet? När den blandas ändras formeln enligt den åtgärd som utförs, det vill säga den kommer inte längre att peka på B2, utan till C2. Men eftersom insättningen gjordes i B2 blir slutresultatet fel.
Referens! För att kunna infoga makron från tredjepartskällor måste du aktivera utvecklarinställningar (de är inaktiverade som standard). För att göra detta, gå till Alternativ, öppna menyfliksinställningarna och markera rutan i den högra kolumnen mittemot "Utvecklare". Därefter öppnas åtkomst till många funktioner som tidigare var dolda för den genomsnittliga användarens ögon.
Detta väcker frågan: är det möjligt att modifiera formeln från cell C2 så att det ursprungliga numret samlas in från cell B, trots infogningen av nya datakolumner? För att säkerställa att ändringar i tabellen inte påverkar bestämningen av summan vid installation av data från tredjepartskällor, måste du utföra följande steg:
- Istället för koordinaterna för cell B2 anger du följande indikatorer: =INDIREKT(“B2”). Som ett resultat kommer formuleringskompositionen att se ut så här efter att ha flyttats: =INDIREKT(“B2”)^$E2.
- Tack vare denna funktion pekar länken alltid till kvadraten med koordinaterna B2, oavsett om kolumner läggs till eller tas bort i tabellen.
Det måste förstås att en cell som inte innehåller några data alltid visar värdet "0".
Slutsats
Tack vare användningen av tre varianter av de beskrivna länkarna dyker det upp en hel del möjligheter som gör det lättare att arbeta med beräkningar i Excel. Därför, innan du börjar arbeta med formler, läs först länkarna och reglerna för deras installation.