Ökning Referenser från Flera när du kopierar formler

April 28

Tim har ett kalkylblad där cell B1 innehåller formeln = SUMMA (A1: A7). Han vill kopiera denna formel ner och har sortimentet ökas med 7 rader, så att cell B2 skulle innehålla formeln = SUMMA (A8: A14), skulle cell B3 innehålla = SUMMA (A15: A21), Problemet är etc. som när han kopierar den, är varje "slutet" av intervallet endast ökas med 1, där det bör ökas med 7 för att uppfylla hans behov. Han undrar hur han kan göra Excel göra rätt uppräkning.

Du kan inte göra Excel gör rätt uppräkning med kopiera och klistra in; det bara inte kommer att göra det. Anledningen är enkel-det finns tillfällen då uppräkning med 1 vettigt ur ett formel perspektiv. Eftersom Excel inte kan läsa dina tankar (åtminstone fram till nästa version:>)), det gör antagandet att det bara skulle öka med 1.

Lösningen är att ändra din formel. Använda ett par kalkylbladsfunktioner kan du ha Excel konstruera det önskade området för summering. Tänk dig följande exempel på en formel som kommer att ge den önskade summan:

= SUMMA (INDIREKT ("A" & (RAD () - 1) * 7 + 1 & ": A" & (RAD () - 1) * 7 + 7))

Om du sätter denna formel i cell B1, det fungerar eftersom det tar en titt på radnumret (returneras av RAD funktion) på raden där formeln finns. Eftersom det är i rad 1, då formeln utvärderas på detta sätt av Excel:

= SUMMA (INDIREKT ("A" & (RAD () - 1) * 7 + 1 & ": A" & (RAD () - 1) * 7 + 7))
= SUMMA (INDIREKT ("A" & (1-1) * 7 + 1 & ": A" & (1-1) * 7 + 7))
= SUMMA (INDIREKT ("A" & 0 * 7 + 1 & ": A" & 0 * 7 + 7))
= SUMMA (INDIREKT ("A" & 0 + 1 & ": A" & 0 + 7))
= SUMMA (INDIREKT ("A" & 1 & ": A" & 7))
= SUMMA (INDIREKT ("A1: A7"))
= SUMMA (A1: A7)

Vad du sluta med i B1 är summan du önskar. (Den INDIREKT Funktionen använder värdet i strängen som om det var en riktig intervall, vilket är vad du vill.) När du kopierar formeln nedåt i kolumnen, som radnumret stegar formeln ger rätt steg om 7 på båda ändarna av intervallet.

Det finns andra varianter på denna teknik som du kan använda. Den enda skillnaden är att variationerna använda olika kalkylbladsfunktioner för att utföra samma uppgift. Till exempel, fortfarande använder följande variationen ROW funktion, men sedan slutligen förlitar sig på OFFSET funktion för att beräkna det önskade intervallet:

= SUMMA (FÖRSKJUTNING (A1, ((RAD () - 1) * 6), 0): OFFSET (A7, ((RAD () - 1) * 6), 0))

En kortare tillvägagångssätt som använder OFFSET är som följer:

= SUMMA (FÖRSKJUTNING ($ A $ 1, RAD () * 7-7,0,7,1))

Oavsett vilken metod, kan du nog säga att idén är att komma med en formel som använder den rad där formeln visas för att konstruera det område som du verkligen vill. Var och en av de exempel som hittills förutsätter att du startar i cell B1. Om du vill starta i cell B2, så måste du ändra formler för att redogöra för vad raden du startar på. För att ge dig bara en idé om hur detta fungerar, om du hade börjat i cell B2, i stället, de tre formler som presenteras i detta tips skulle ändras på följande sätt:

= SUMMA (INDIREKT ("A" & (RAD () - 2) * 7 + 2 & ": A" & (RAD () - 2) * 7 + 8))
= SUMMA (FÖRSKJUTNING (A2, ((RAD () - 2) * 6), 0): OFFSET (A8, ((RAD () - 2) * 6), 0))
= SUMMA (FÖRSKJUTNING ($ A $ 2, (RAD () - 1) * 7-7,0,7,1))

Börja på en annan plats, och du kommer att behöva göra ytterligare ändringar till den formel som du väljer att använda.

ExcelTips är din källa för kostnadseffektiv Microsoft Excel utbildning. Detta tips (8385) gäller för Microsoft Excel 97, 2000, 2002, och 2003. Du kan hitta en version av detta tips för menyfliksområdet i Excel (Excel 2007 och senare) här: Uppräkningen Referenser från Flera när du kopierar formler.