Slik angir du et verdiområde i Excel

Når du bruker Microsoft Excel, vet brukeren ofte ikke på forhånd hvor mye informasjon som vil være i tabellen som et resultat. Derfor forstår vi ikke i alle situasjoner hvilken rekkevidde som bør dekkes. Tross alt er et sett med celler et variabelt konsept. For å bli kvitt dette problemet, er det nødvendig å gjøre rekkeviddegenereringen automatisk slik at den er basert utelukkende på mengden data som ble lagt inn av brukeren.

Automatisk endring av celleområder i Excel

Fordelen med automatiske områder i Excel er at de gjør det mye enklere å bruke formler. I tillegg gjør de det mulig å betydelig forenkle analysen av komplekse data som inneholder et stort antall formler, som inkluderer mange funksjoner. Du kan gi dette området et navn, og deretter oppdateres det automatisk avhengig av hvilke data det inneholder.

Slik gjør du automatisk områdeendring i Excel

Anta at du er en investor som trenger å investere i et eller annet objekt. Som et resultat ønsker vi å få informasjon om hvor mye du kan tjene totalt for hele tiden pengene skal fungere for dette prosjektet. Ikke desto mindre, for å få denne informasjonen, må vi regelmessig overvåke hvor mye total fortjeneste dette objektet gir oss. Lag samme rapport som i dette skjermbildet.

Ved første øyekast er løsningen åpenbar: du trenger bare å summere hele kolonnen. Hvis oppføringer vises i den, vil beløpet bli oppdatert uavhengig. Men denne metoden har mange ulemper:

1. Hvis problemet løses på denne måten, vil det ikke være mulig å bruke cellene i kolonne B til andre formål.
2. Et slikt bord vil forbruke mye RAM, noe som vil gjøre det umulig å bruke dokumentet på svake datamaskiner.

Derfor er det nødvendig å løse dette problemet gjennom dynamiske navn. For å lage dem må du utføre følgende handlingssekvens:

1. Gå til "Formler" -fanen, som ligger i hovedmenyen. Det vil være en seksjon "Definerte navn", der det er en knapp "Tilordne et navn", som vi må klikke på.
2. Deretter vises en dialogboks der du må fylle ut feltene som vist på skjermbildet. Det er viktig å merke seg at vi må bruke funksjonen = FORSKYTTELSE sammen med funksjonen KRYSS AVfor å opprette et område for automatisk oppdatering. 
3. Etter det må vi bruke funksjonen SUM, som bruker vårt dynamiske område som et argument. 

Etter å ha fullført disse trinnene, kan vi se hvordan dekningen av cellene som tilhører «inntekt»-området oppdateres etter hvert som vi legger til nye elementer der.

OFFSET-funksjon i Excel

La oss se på funksjonene som vi registrerte i "område"-feltet tidligere. Bruker funksjonen FJERNING vi kan bestemme mengden av rekkevidde gitt hvor mange celler i kolonne B som er fylt. Funksjonsargumentene er som følger:

1. Start celle. Med dette argumentet kan brukeren indikere hvilken celle i området som anses øverst til venstre. Den vil rapportere ned og til høyre.
2. Område forskjøvet av rader. Ved å bruke dette området angir vi antallet celler som en forskyvning skal skje med fra den øverste venstre cellen i området. Du kan bruke ikke bare positive verdier, men null og minus. I dette tilfellet kan det hende at forskyvningen ikke oppstår i det hele tatt, eller den vil bli utført i motsatt retning.
3. Område forskjøvet med kolonner. Denne parameteren ligner den forrige, bare den lar deg angi graden av horisontal forskyvning av området. Her kan du også bruke både null og negative verdier.
4. Mengden rekkevidde i høyden. Faktisk gjør tittelen på dette argumentet det klart for oss hva det betyr. Dette er antallet celler som området skal økes med.
5. Verdien av området i bredden. Argumentet ligner det forrige, bare det gjelder kolonnene.

Du trenger ikke spesifisere de to siste argumentene hvis du ikke trenger det. I dette tilfellet vil områdeverdien bare være én celle. For eksempel hvis du angir formelen =OFFSET(A1;0;0), vil denne formelen referere til den samme cellen som den i det første argumentet. Hvis den vertikale forskyvningen er satt til 2 enheter, vil cellen i dette tilfellet referere til celle A3. La oss nå beskrive i detalj hva funksjonen betyr KRYSS AV.

COUNT-funksjon i Excel

Bruke funksjonen KRYSS AV vi bestemmer hvor mange celler i kolonne B vi har fylt totalt. Det vil si at vi ved hjelp av to funksjoner bestemmer hvor mange celler i området som er fylt, og basert på informasjonen som mottas, bestemmer vi størrelsen på området. Derfor vil den endelige formelen være følgende: =СМЕЩ(Лист1!$B$2;0;0;СЧЁТ(Лист1!$B:$B);1)

La oss ta en titt på hvordan du forstår prinsippet til denne formelen riktig. Det første argumentet peker på hvor det dynamiske området vårt starter. I vårt tilfelle er dette celle B2. Ytterligere parametere har null koordinater. Dette antyder at vi ikke trenger forskyvninger i forhold til øverste venstre celle. Alt vi fyller ut er den vertikale størrelsen på området, som vi brukte funksjonen som KRYSS AV, som bestemmer antall celler som inneholder noen data. Den fjerde parameteren som vi fylte inn er enheten. Dermed viser vi at den totale bredden av området skal være én kolonne.

Dermed bruker funksjonen KRYSS AV brukeren kan bruke minnet så effektivt som mulig ved å laste bare de cellene som inneholder noen verdier. Følgelig vil det ikke være ytterligere feil i arbeidet knyttet til den dårlige ytelsen til datamaskinen som regnearket skal fungere på.

Følgelig, for å bestemme størrelsen på området avhengig av antall kolonner, må du utføre en lignende sekvens av handlinger, bare i dette tilfellet må du spesifisere enheten i den tredje parameteren, og formelen i den fjerde KRYSS AV.

Vi ser at ved hjelp av Excel-formler kan du ikke bare automatisere matematiske beregninger. Dette er bare en dråpe i havet, men faktisk lar de deg automatisere nesten alle operasjoner du tenker på.

Dynamiske diagrammer i Excel

Så i det siste trinnet var vi i stand til å lage et dynamisk område, hvis størrelse helt avhenger av hvor mange fylte celler den inneholder. Nå, basert på disse dataene, kan du lage dynamiske diagrammer som automatisk endres så snart brukeren gjør endringer eller legger til en ekstra kolonne eller rad. Handlingssekvensen i dette tilfellet er som følger:

1. Vi velger vårt utvalg, hvoretter vi setter inn et diagram av typen "Histogram med gruppering". Du kan finne dette elementet i "Sett inn"-delen i "Charts - Histogram"-delen.
2. Vi gjør et venstre museklikk på en tilfeldig kolonne i histogrammet, hvoretter funksjonen =SERIES() vises i funksjonslinjen. På skjermbildet kan du se den detaljerte formelen. 
3. Etter det må det gjøres noen endringer i formelen. Du må erstatte området etter "Sheet1!" til navnet på området. Dette vil resultere i følgende funksjon: =RAD(Ark1!$B$1;;Ark1!inntekt;1)
4. Nå gjenstår det å legge til en ny post i rapporten for å sjekke om diagrammet oppdateres automatisk eller ikke.

La oss ta en titt på diagrammet vårt.

La oss oppsummere hvordan vi gjorde det. I forrige trinn opprettet vi et dynamisk område, hvis størrelse avhenger av hvor mange elementer det inneholder. For å gjøre dette brukte vi en kombinasjon av funksjoner KRYSS AV и FJERNING. Vi gjorde dette området navngitt, og deretter brukte vi referansen til dette navnet som området for histogrammet vårt. Hvilket spesifikt område som skal velges som datakilde i det første trinnet er ikke så viktig. Det viktigste er å erstatte det med navnet på området senere. På denne måten kan du spare mye RAM.

Navngitte områder og deres bruk

La oss nå snakke mer detaljert om hvordan du oppretter navngitte områder på riktig måte og bruker dem til å utføre oppgavene som er satt for Excel-brukeren.

Som standard bruker vi vanlige celleadresser for å spare tid. Dette er nyttig når du trenger å skrive et område én eller flere ganger. Hvis det må brukes hele tiden eller det må være adaptivt, bør navngitte områder brukes. De gjør det mye enklere å lage formler, og det vil ikke være så vanskelig for brukeren å analysere komplekse formler som inkluderer et stort antall funksjoner. La oss beskrive noen av trinnene som er involvert i å lage dynamiske områder.

Det hele starter med å navngi cellen. For å gjøre dette, bare velg det, og skriv deretter navnet vi trenger i feltet med navnet. Det er viktig at det er lett å huske. Det er noen begrensninger å vurdere når du navngir:

1. Maksimal lengde er 255 tegn. Dette er nok til å tildele et navn som hjertet ditt ønsker.
2. Navnet må ikke inneholde mellomrom. Derfor, hvis den inneholder flere ord, er det mulig å skille dem ved å bruke understrekingstegnet.

Hvis vi senere på andre ark i denne filen må vise denne verdien eller bruke den for å utføre ytterligere beregninger, er det ikke nødvendig å bytte til det aller første arket. Du kan bare skrive ned navnet på denne områdecellen.

Det neste trinnet er å lage et navngitt område. Fremgangsmåten er i utgangspunktet den samme. Først må du velge et område, og deretter angi navnet. Etter det kan dette navnet brukes i alle andre dataoperasjoner i Excel. For eksempel blir navngitte områder ofte brukt til å definere summen av verdier.

I tillegg er det mulig å lage et navngitt område ved å bruke Formler-fanen ved å bruke verktøyet Set Name. Etter at vi har valgt det, vises et vindu der vi må velge et navn for utvalget vårt, samt spesifisere området som det skal utvides til manuelt. Du kan også spesifisere hvor dette området skal fungere: innenfor et enkelt ark eller gjennom hele boken.

Hvis et navneområde allerede er opprettet, er det en spesiell tjeneste som kalles en navnebehandler for å kunne bruke den. Det lar deg ikke bare redigere eller legge til nye navn, men også slette dem hvis de ikke lenger er nødvendige.

Det bør huskes at når du bruker navngitte områder i formler, vil formlene ikke automatisk bli overskrevet med de riktige verdiene etter sletting. Derfor kan det oppstå feil. Derfor, før du sletter et navngitt område, må du sørge for at det ikke brukes i noen av formlene.

En annen måte å lage et navngitt område på er å hente det fra en tabell. For å gjøre dette er det et spesielt verktøy som heter "Opprett fra utvalg". Som vi forstår, for å bruke det, må du først velge området som vi vil redigere, og deretter angi stedet der vi har overskriftene. Som et resultat, basert på disse dataene, vil Excel automatisk behandle alle dataene, og titler vil automatisk bli tildelt.

Hvis tittelen inneholder flere ord, vil Excel automatisk skille dem med et understrek.

Dermed fant vi ut hvordan du lager dynamiske navngitte områder og hvordan de lar deg automatisere arbeid med store datamengder. Som du kan se, er det nok å bruke flere funksjoner og programverktøyene som er innebygd i funksjonaliteten. Det er ikke noe komplisert i det hele tatt, selv om det kan virke slik for en nybegynner ved første øyekast.