Sådan indstilles en række værdier i Excel

Når du bruger Microsoft Excel, ved brugeren meget ofte ikke på forhånd, hvor meget information der vil være i tabellen som et resultat. Derfor forstår vi ikke i alle situationer, hvilken rækkevidde der skal dækkes. Et sæt celler er jo et variabelt begreb. For at slippe af med dette problem er det nødvendigt at gøre rækkegenereringen automatisk, så den udelukkende er baseret på mængden af ​​data, der blev indtastet af brugeren.

Automatisk ændring af celleområder i Excel

Fordelen ved automatiske intervaller i Excel er, at de gør det meget nemmere at bruge formler. Derudover gør de det muligt væsentligt at forenkle analysen af ​​komplekse data, der indeholder et stort antal formler, som omfatter mange funktioner. Du kan give dette område et navn, og så opdateres det automatisk afhængigt af hvilke data det indeholder.

Sådan laver du automatisk områdeændring i Excel

Antag, at du er en investor, der skal investere i et eller andet objekt. Derfor ønsker vi at få information om, hvor meget du kan tjene i alt for hele den tid, pengene skal bruges til dette projekt. Ikke desto mindre, for at få disse oplysninger, er vi nødt til regelmæssigt at overvåge, hvor meget samlet fortjeneste dette objekt bringer til os. Lav samme rapport som i dette skærmbillede.

Ved første øjekast er løsningen indlysende: du skal bare summere hele kolonnen. Hvis der vises poster i den, vil beløbet blive opdateret uafhængigt. Men denne metode har mange ulemper:

1. Hvis problemet løses på denne måde, vil det ikke være muligt at bruge cellerne i kolonne B til andre formål.
2. En sådan tabel vil forbruge meget RAM, hvilket vil gøre det umuligt at bruge dokumentet på svage computere.

Derfor er det nødvendigt at løse dette problem gennem dynamiske navne. For at oprette dem skal du udføre følgende rækkefølge af handlinger:

1. Gå til fanen "Formler", som er placeret i hovedmenuen. Der vil være en sektion "Definerede navne", hvor der er en knap "Tildel et navn", som vi skal klikke på.
2. Derefter vises en dialogboks, hvor du skal udfylde felterne som vist på skærmbilledet. Det er vigtigt at bemærke, at vi skal anvende funktionen = FORskydning sammen med funktionen KONTROLLEREfor at oprette et automatisk opdateringsområde. 
3. Derefter skal vi bruge funktionen SUM, som bruger vores dynamiske område som et argument. 

Efter at have gennemført disse trin, kan vi se, hvordan dækningen af ​​cellerne, der tilhører "indkomst"-området, opdateres, efterhånden som vi tilføjer nye elementer der.

OFFSET funktion i Excel

Lad os se på de funktioner, som vi registrerede i "område"-feltet tidligere. Brug af funktionen BORTSKAFFELSE vi kan bestemme mængden af ​​interval givet hvor mange celler i kolonne B der er udfyldt. Funktionsargumenterne er som følger:

1. Start celle. Med dette argument kan brugeren angive, hvilken celle i området, der vil blive betragtet som øverst til venstre. Det vil rapportere ned og til højre.
2. Rækkevidde forskudt af rækker. Ved at bruge dette område indstiller vi antallet af celler, som en forskydning skal ske med fra den øverste venstre celle i området. Du kan bruge ikke kun positive værdier, men nul og minus. I dette tilfælde forekommer forskydningen muligvis slet ikke, eller den vil blive udført i den modsatte retning.
3. Område forskudt af kolonner. Denne parameter ligner den forrige, kun den giver dig mulighed for at indstille graden af ​​vandret skift af området. Her kan du også bruge både nul- og negative værdier.
4. Mængden af ​​rækkevidde i højden. Faktisk gør titlen på dette argument det klart for os, hvad det betyder. Dette er antallet af celler, som området skal øges med.
5. Værdien af ​​området i bredden. Argumentet ligner det foregående, kun det vedrører kolonnerne.

Du behøver ikke angive de sidste to argumenter, hvis du ikke har brug for det. I dette tilfælde vil områdeværdien kun være én celle. For eksempel, hvis du angiver formlen =OFFSET(A1;0;0), vil denne formel referere til den samme celle som den i det første argument. Hvis den lodrette offset er sat til 2 enheder, vil cellen i dette tilfælde referere til celle A3. Lad os nu beskrive i detaljer, hvad funktionen betyder KONTROLLERE.

COUNT funktion i Excel

Brug af funktionen KONTROLLERE vi bestemmer, hvor mange celler i kolonne B vi har udfyldt i alt. Det vil sige, at vi ved hjælp af to funktioner bestemmer, hvor mange celler i intervallet, der er udfyldt, og baseret på den modtagne information bestemmer intervallets størrelse. Derfor vil den endelige formel være følgende: =СМЕЩ(Лист1!$B$2;0;0;СЧЁТ(Лист1!$B:$B);1)

Lad os tage et kig på, hvordan man korrekt forstår princippet i denne formel. Det første argument peger på, hvor vores dynamiske område starter. I vores tilfælde er dette celle B2. Yderligere parametre har nul koordinater. Dette tyder på, at vi ikke har brug for forskydninger i forhold til den øverste venstre celle. Det eneste, vi udfylder, er den lodrette størrelse af området, som vi brugte funktionen som KONTROLLERE, som bestemmer antallet af celler, der indeholder nogle data. Den fjerde parameter, som vi udfyldte, er enheden. Vi viser således, at områdets samlede bredde skal være én kolonne.

Altså ved at bruge funktionen KONTROLLERE brugeren kan bruge hukommelsen så effektivt som muligt ved kun at indlæse de celler, der indeholder nogle værdier. Derfor vil der ikke være yderligere fejl i arbejdet i forbindelse med den dårlige ydeevne af den computer, som regnearket vil fungere på.

For at bestemme størrelsen af ​​området afhængigt af antallet af kolonner skal du derfor udføre en lignende sekvens af handlinger, kun i dette tilfælde skal du angive enheden i den tredje parameter og formlen i den fjerde KONTROLLERE.

Vi ser, at man ved hjælp af Excel-formler ikke kun kan automatisere matematiske beregninger. Dette er kun en dråbe i havet, men faktisk giver de dig mulighed for at automatisere næsten enhver operation, du tænker på.

Dynamiske diagrammer i Excel

Så i det sidste trin var vi i stand til at skabe et dynamisk område, hvis størrelse helt afhænger af, hvor mange udfyldte celler det indeholder. Nu, baseret på disse data, kan du oprette dynamiske diagrammer, der automatisk ændres, så snart brugeren foretager ændringer eller tilføjer en ekstra kolonne eller række. Rækkefølgen af ​​handlinger i dette tilfælde er som følger:

1. Vi vælger vores sortiment, hvorefter vi indsætter et diagram af typen "Histogram med gruppering". Du kan finde dette element i sektionen "Indsæt" i sektionen "Charts - Histogram".
2. Vi laver et venstre museklik på en tilfældig kolonne i histogrammet, hvorefter funktionen =SERIES() vises i funktionslinjen. På skærmbilledet kan du se den detaljerede formel. 
3. Derefter skal der foretages nogle ændringer i formlen. Du skal erstatte området efter "Sheet1!" til navnet på området. Dette vil resultere i følgende funktion: =RÆKKE(Ark1!$B$1;;Ark1!indkomst;1)
4. Nu er det tilbage at tilføje en ny post til rapporten for at kontrollere, om diagrammet opdateres automatisk eller ej.

Lad os tage et kig på vores diagram.

Lad os opsummere, hvordan vi gjorde det. I det foregående trin oprettede vi et dynamisk område, hvis størrelse afhænger af, hvor mange elementer det indeholder. For at gøre dette brugte vi en kombination af funktioner KONTROLLERE и BORTSKAFFELSE. Vi lavede dette område navngivet, og så brugte vi referencen til dette navn som området for vores histogram. Hvilket specifikt område der skal vælges som datakilde i det første trin er ikke så vigtigt. Det vigtigste er at erstatte det med navnet på rækken senere. På denne måde kan du spare en masse RAM.

Navngivne områder og deres anvendelser

Lad os nu tale mere detaljeret om, hvordan man korrekt opretter navngivne områder og bruger dem til at udføre de opgaver, der er indstillet til Excel-brugeren.

Som standard bruger vi almindelige celleadresser for at spare tid. Dette er nyttigt, når du skal skrive et område en eller flere gange. Hvis det skal bruges hele tiden, eller det skal være adaptivt, skal der bruges navngivne områder. De gør det meget nemmere at oprette formler, og det vil ikke være så svært for brugeren at analysere komplekse formler, der indeholder en lang række funktioner. Lad os beskrive nogle af de trin, der er involveret i at skabe dynamiske områder.

Det hele starter med at navngive cellen. For at gøre dette skal du blot vælge det og derefter skrive det navn, vi har brug for, i feltet med dets navn. Det er vigtigt, at det er nemt at huske. Der er nogle begrænsninger at overveje, når du navngiver:

1. Den maksimale længde er 255 tegn. Dette er ganske nok til at tildele et navn, som dit hjerte ønsker.
2. Navnet må ikke indeholde mellemrum. Derfor, hvis det indeholder flere ord, så er det muligt at adskille dem ved hjælp af understregningstegnet.

Hvis vi senere på andre ark i denne fil skal vise denne værdi eller anvende den til at udføre yderligere beregninger, er der ingen grund til at skifte til det allerførste ark. Du kan bare skrive navnet på denne områdecelle ned.

Det næste trin er at oprette et navngivet område. Fremgangsmåden er grundlæggende den samme. Først skal du vælge et område og derefter angive dets navn. Derefter kan dette navn bruges i alle andre dataoperationer i Excel. For eksempel bruges navngivne områder ofte til at definere summen af ​​værdier.

Derudover er det muligt at oprette et navngivet område ved hjælp af fanen Formler ved hjælp af værktøjet Indstil navn. Når vi har valgt det, vises et vindue, hvor vi skal vælge et navn til vores sortiment, samt angive det område, som det vil udvides manuelt. Du kan også angive, hvor dette område skal fungere: inden for et enkelt ark eller gennem hele bogen.

Hvis der allerede er oprettet et navneområde, er der for at kunne bruge det en særlig tjeneste kaldet en navneadministrator. Det tillader ikke kun at redigere eller tilføje nye navne, men også slette dem, hvis de ikke længere er nødvendige.

Man skal huske på, at når man bruger navngivne områder i formler, vil formlerne ikke automatisk blive overskrevet med de korrekte værdier efter sletning. Derfor kan der opstå fejl. Før du sletter et navngivet område, skal du derfor sikre dig, at det ikke bruges i nogen af ​​formlerne.

En anden måde at oprette et navngivet område på er at hente det fra en tabel. For at gøre dette er der et specielt værktøj kaldet "Opret fra udvalg". Som vi forstår, for at bruge det, skal du først vælge det område, som vi vil redigere, og derefter angive det sted, hvor vi har overskrifterne. Som et resultat, baseret på disse data, vil Excel automatisk behandle alle data, og titler vil automatisk blive tildelt.

Hvis titlen indeholder flere ord, adskiller Excel dem automatisk med en understregning.

Således fandt vi ud af, hvordan du opretter dynamiske navngivne områder, og hvordan de giver dig mulighed for at automatisere arbejde med store mængder data. Som du kan se, er det nok at bruge flere funktioner og de indbyggede programværktøjer i funktionaliteten. Der er ikke noget kompliceret overhovedet, selvom det kan virke sådan for en begynder ved første øjekast.