När absoluta referenser inte fungerar kan INDIREKT vara lösningen
Jag fick följande från Theo:
Fråga: I kolumn E har jag en formel som räknar ut den procentuella förändringen mellan kolumnerna B och C. När jag sedan infogar en ny kolumn B vill jag att procentberäkningen fortfarande ska räkna på kolumnerna B och D (dvs den nya kolumnen B och gamla C ). Att använda $ gör att formeln flyttar med och att inte använda det gör att formeln flyttar med. R1C1-funkar inte heller vad jag förstår.
Mitt svar:
Absoluta referenser låser formlerna i en cell så att om cellen kopieras så förändras inte referensen i cellen, =$B$2 kommer alltid vara =$B$2 var den än kopieras.
Men… om du infogar en ny kolumn B, så att det som =$B$2 pekar på förskjuts till C2 så kommer =$B$2 följa med och bli =$C$2.
Så om du tex mäter förändring med formeln =C2/$B$2-1 och infogar en ny kolumn H så kommer formeln förskjutas och bli =D2/$C$2-1. Dvs det spelar ingen roll om referensen är absolut eller relativ.
Lösningen i detta fall är att använda den lite okända funktionen INDIREKT.
INDIREKT är en funktion som skapar en referens av en textsträng. =INDIREKT(”B2”) kommer t.ex. ge en referens till B2 och den påverkas varken av om den kopieras eller om det man kopierar flyttar. Den kommer alltid ge en referens till B2.
Så… Om jag t.ex. gör om min tillväxtformel till =C2/INDIREKT(”B2”)-1 så kommer du kunna infoga en ny kolumn B och din funktion kommer mäta förändringen mot det du skriver in i kolumn B (i stället för det som nu flyttats till kolumn C.
Ett följdproblem som kan uppstå när man använder INDIREKT är att den inte förändras relativt när den kopieras. Den är ju hela tiden statisk mot (i det här fallet) till H2. Vilket är vad vi vill att den skall vara när vi infogar en ny kolumn. Men säga att vi har 1000 rader som vi vill kopiera vår formel till.
Dvs vi vill ha:
=C2/INDIREKT(”B2”)-1
=C3/INDIREKT(”B3”)-1
=C4/INDIREKT(”B3”)-1
ända till
=C1000/INDIREKT(”B1000”)-1
Är det bara några få rader så är det ju inte något större problem att ändra i INDIREKT manuellt, men om det är många rader så tar det rätt lång tid.
En läsning på detta är att lägga in en räknare i INDIREKT som räknar upp raderna.
Enklast kan vara att använda funktionen RAD(). Funktionen RAD i Excel returnerar radnumret för en referens som du anger i en formel.
Om du t.ex. skriver in =RAD() i cell A1 så får du svaret 1.
Så om jag utvecklar min funktion till följande:
=C2/INDIREKT(”B”&RAD())-1
Så kommer INDIREKT ge referensen till B2 när den ligger på rad 2 och när den kopieras ner till fler rader så blir den B3, B4, B5 osv..
&-tecknet är ett tecken man använder när man vill sammanfoga text. I detta fall texten ”B” med resultatet av RAD()
Ett intressant problem som fick bli en film på min YouTube-kanal:
Om du inte redan prenumererar på min YouTube-kanal så kan du göra det genom att klicka här (kostar naturligtvis ingenting). http://www.youtube.com/user/andersexcel?sub_confirmation=1