Hur man ställer in ett värdeintervall i Excel

När du använder Microsoft Excel vet användaren ofta inte i förväg hur mycket information som kommer att finnas i tabellen som ett resultat. Därför förstår vi inte i alla situationer vilket intervall som ska täckas. När allt kommer omkring är en uppsättning celler ett variabelt koncept. För att bli av med detta problem är det nödvändigt att göra intervallgenereringen automatisk så att den enbart baseras på mängden data som angavs av användaren.

Automatiskt ändra cellintervall i Excel

Fördelen med automatiska intervall i Excel är att de gör det mycket lättare att använda formler. Dessutom gör de det möjligt att avsevärt förenkla analysen av komplexa data som innehåller ett stort antal formler, som inkluderar många funktioner. Du kan ge detta intervall ett namn, och sedan uppdateras det automatiskt beroende på vilken data det innehåller.

Hur man gör automatisk intervalländring i Excel

Anta att du är en investerare som behöver investera i något objekt. Som ett resultat vill vi få information om hur mycket du kan tjäna totalt under hela den tid som pengarna kommer att fungera för detta projekt. Ändå, för att få denna information, måste vi regelbundet övervaka hur mycket total vinst detta objekt ger oss. Gör samma rapport som i den här skärmdumpen.

Vid första anblicken är lösningen uppenbar: du behöver bara summera hela kolumnen. Om poster visas i den kommer beloppet att uppdateras oberoende. Men denna metod har många nackdelar:

1. Om problemet löses på detta sätt kommer det inte att vara möjligt att använda cellerna som ingår i kolumn B för andra ändamål.
2. En sådan tabell kommer att förbruka mycket RAM, vilket gör det omöjligt att använda dokumentet på svaga datorer.

Därför är det nödvändigt att lösa detta problem genom dynamiska namn. För att skapa dem måste du utföra följande sekvens av åtgärder:

1. Gå till fliken "Formler", som finns i huvudmenyn. Det kommer att finnas en sektion "Definierade namn", där det finns en knapp "Tilldela ett namn", som vi måste klicka på.
2. Därefter visas en dialogruta där du måste fylla i fälten som visas på skärmdumpen. Det är viktigt att notera att vi måste tillämpa funktionen = FÖRVÄNDNING tillsammans med funktionen CHECKför att skapa ett intervall för automatisk uppdatering. 
3. Efter det måste vi använda funktionen SUMMA, som använder vårt dynamiska omfång som ett argument. 

Efter att ha slutfört dessa steg kan vi se hur täckningen av cellerna som tillhör "inkomst"-intervallet uppdateras när vi lägger till nya element där.

OFFSET-funktion i Excel

Låt oss titta på funktionerna som vi spelade in i fältet "intervall" tidigare. Använder funktionen FÖRFOGANDE vi kan bestämma mängden intervall givet hur många celler i kolumn B som är fyllda. Funktionsargumenten är följande:

1. Starta cell. Med detta argument kan användaren ange vilken cell i intervallet som kommer att betraktas som överst till vänster. Den kommer att rapportera ner och till höger.
2. Område förskjutet med rader. Med detta intervall ställer vi in ​​antalet celler med vilka en förskjutning ska ske från den övre vänstra cellen i intervallet. Du kan använda inte bara positiva värden, utan noll och minus. I detta fall kan förskjutningen inte inträffa alls, eller så kommer den att utföras i motsatt riktning.
3. Område förskjutet med kolumner. Denna parameter liknar den föregående, bara den låter dig ställa in graden av horisontell förskjutning av intervallet. Här kan du också använda både noll- och negativa värden.
4. Mängden räckvidd i höjd. I själva verket gör titeln på detta argument klart för oss vad det betyder. Detta är antalet celler som intervallet ska öka med.
5. Värdet på intervallet i bredd. Argumentet liknar det föregående, bara det gäller kolumnerna.

Du behöver inte ange de två sista argumenten om du inte behöver. I det här fallet kommer intervallvärdet endast att vara en cell. Till exempel om du anger formeln =OFFSET(A1;0;0), kommer den här formeln att referera till samma cell som den i det första argumentet. Om den vertikala offseten är inställd på 2 enheter, kommer cellen i detta fall att referera till cell A3. Låt oss nu beskriva i detalj vad funktionen betyder CHECK.

COUNT-funktionen i Excel

Använda funktionen CHECK vi bestämmer hur många celler i kolumn B vi har fyllt totalt. Det vill säga, med hjälp av två funktioner bestämmer vi hur många celler i intervallet som är fyllda, och baserat på den mottagna informationen bestämmer vi storleken på intervallet. Därför blir den slutliga formeln följande: =СМЕЩ(Лист1!$B$2;0;0;СЧЁТ(Лист1!$B:$B);1)

Låt oss ta en titt på hur man korrekt förstår principen för denna formel. Det första argumentet pekar på var vårt dynamiska intervall börjar. I vårt fall är detta cell B2. Ytterligare parametrar har nollkoordinater. Detta tyder på att vi inte behöver förskjutningar i förhållande till den övre vänstra cellen. Allt vi fyller i är den vertikala storleken på intervallet, som vi använde funktionen som CHECK, som bestämmer antalet celler som innehåller vissa data. Den fjärde parametern som vi fyllde i är enheten. Således visar vi att den totala bredden av intervallet bör vara en kolumn.

Alltså att använda funktionen CHECK användaren kan använda minnet så effektivt som möjligt genom att bara ladda de celler som innehåller vissa värden. Följaktligen kommer det inte att finnas några ytterligare fel i arbetet som är förknippat med den dåliga prestandan på datorn som kalkylarket kommer att fungera på.

Följaktligen, för att bestämma storleken på intervallet beroende på antalet kolumner, måste du utföra en liknande sekvens av åtgärder, bara i det här fallet måste du ange enheten i den tredje parametern och formeln i den fjärde CHECK.

Vi ser att man med hjälp av Excel-formler inte bara kan automatisera matematiska beräkningar. Det här är bara en droppe i havet, men i själva verket låter de dig automatisera nästan alla operationer som du tänker på.

Dynamiska diagram i Excel

Så i det sista steget kunde vi skapa ett dynamiskt område, vars storlek helt beror på hur många fyllda celler det innehåller. Nu, baserat på dessa data, kan du skapa dynamiska diagram som kommer att ändras automatiskt så snart användaren gör några ändringar eller lägger till ytterligare en kolumn eller rad. Handlingssekvensen i detta fall är följande:

1. Vi väljer vårt sortiment, varefter vi infogar ett diagram av typen "Histogram med gruppering". Du kan hitta det här objektet i avsnittet "Infoga" i avsnittet "Diagram - Histogram".
2. Vi gör ett vänsterklick på en slumpmässig kolumn i histogrammet, varefter funktionen =SERIES() visas i funktionsraden. På skärmdumpen kan du se den detaljerade formeln. 
3. Efter det måste vissa ändringar göras i formeln. Du måste byta ut intervallet efter "Sheet1!" till sortimentets namn. Detta kommer att resultera i följande funktion: =RAD(Ark1!$B$1;;Ark1!inkomst;1)
4. Nu återstår det att lägga till en ny post i rapporten för att kontrollera om diagrammet uppdateras automatiskt eller inte.

Låt oss ta en titt på vårt diagram.

Låt oss sammanfatta hur vi gjorde det. I föregående steg skapade vi ett dynamiskt område, vars storlek beror på hur många element det innehåller. För att göra detta använde vi en kombination av funktioner CHECK и FÖRFOGANDE. Vi gjorde det här intervallet namngivet, och sedan använde vi referensen till detta namn som intervallet för vårt histogram. Vilket specifikt intervall man ska välja som datakälla i det första skedet är inte så viktigt. Det viktigaste är att ersätta det med namnet på sortimentet senare. På så sätt kan du spara mycket RAM.

Namngivna intervall och deras användningsområden

Låt oss nu prata mer i detalj om hur man korrekt skapar namngivna intervall och använder dem för att utföra de uppgifter som är inställda för Excel-användaren.

Som standard använder vi vanliga celladresser för att spara tid. Detta är användbart när du behöver skriva ett intervall en eller flera gånger. Om det behöver användas hela tiden eller det behöver vara adaptivt, bör namngivna intervall användas. De gör det mycket lättare att skapa formler, och det kommer inte att vara så svårt för användaren att analysera komplexa formler som innehåller ett stort antal funktioner. Låt oss beskriva några av stegen som är involverade i att skapa dynamiska intervall.

Allt börjar med att namnge cellen. För att göra detta, välj helt enkelt det och skriv sedan namnet som vi behöver i fältet för dess namn. Det är viktigt att det är lätt att komma ihåg. Det finns några begränsningar att tänka på när du namnger:

1. Den maximala längden är 255 tecken. Detta är tillräckligt för att tilldela ett namn som ditt hjärta önskar.
2. Namnet får inte innehålla mellanslag. Därför, om det innehåller flera ord, är det möjligt att separera dem med hjälp av understreck.

Om vi ​​senare på andra ark i den här filen behöver visa detta värde eller använda det för att utföra ytterligare beräkningar, behöver vi inte byta till det allra första arket. Du kan bara skriva ner namnet på denna intervallcell.

Nästa steg är att skapa ett namngivet intervall. Förfarandet är i princip detsamma. Först måste du välja ett intervall och sedan ange dess namn. Därefter kan detta namn användas i alla andra dataoperationer i Excel. Namngivna intervall används till exempel ofta för att definiera summan av värden.

Dessutom är det möjligt att skapa ett namngivet intervall med hjälp av fliken Formler med hjälp av verktyget Ange namn. Efter att vi har valt det kommer ett fönster att visas där vi måste välja ett namn för vårt sortiment, samt ange det område som det kommer att utökas till manuellt. Du kan också ange var detta intervall ska fungera: inom ett enda ark eller genom hela boken.

Om ett namnintervall redan har skapats finns det en speciell tjänst som kallas namnhanterare för att kunna använda det. Det tillåter inte bara att redigera eller lägga till nya namn, utan också ta bort dem om de inte längre behövs.

Man bör komma ihåg att när man använder namngivna intervall i formler, kommer formlerna inte automatiskt att skrivas över med de korrekta värdena efter att de tagits bort. Därför kan fel uppstå. Innan du tar bort ett namngivet område måste du därför se till att det inte används i någon av formlerna.

Ett annat sätt att skapa ett namngivet intervall är att hämta det från en tabell. För att göra detta finns det ett speciellt verktyg som heter "Skapa från urval". Som vi förstår måste du först välja intervallet som vi ska redigera för att kunna använda det och sedan ange platsen där vi har rubrikerna. Som ett resultat, baserat på dessa data, kommer Excel automatiskt att bearbeta all data, och titlar tilldelas automatiskt.

Om titeln innehåller flera ord kommer Excel automatiskt att separera dem med ett understreck.

Således kom vi på hur man skapar dynamiska namngivna intervall och hur de låter dig automatisera arbete med stora mängder data. Som du kan se räcker det med att använda flera funktioner och programverktygen inbyggda i funktionaliteten. Det är inget komplicerat alls, även om det kan verka så för en nybörjare vid första anblicken.