Innhold
Har du tabeller med data i Excel som kan endres størrelse, dvs. antall rader (kolonner) kan øke eller reduseres i løpet av arbeidet? Hvis tabellstørrelsene "flyter", må du hele tiden overvåke dette øyeblikket og korrigere det:
- lenker i rapportformler som refererer til tabellen vår
- innledende serier av pivottabeller som er bygget i henhold til tabellen vår
- innledende utvalg av diagrammer bygget i henhold til tabellen vår
- områder for rullegardinmenyene som bruker tabellen vår som datakilde
Alt dette totalt vil ikke la deg kjede deg 😉
Det vil være mye mer praktisk og riktig å lage et dynamisk "gummi"-område, som automatisk justeres i størrelse til det faktiske antallet rader og kolonner med data. For å implementere dette er det flere måter.
Metode 1. Smart bord
Marker celleområdet ditt og velg fra fanen Hjem – Formater som tabell (Hjem – Formater som tabell):
Hvis du ikke trenger det stripete designet som er lagt til bordet som en bieffekt, kan du slå det av på fanen som vises Konstruktør (design). Hvert bord som er opprettet på denne måten får et navn som kan erstattes med et mer praktisk på samme sted på fanen Konstruktør (design) i felten Tabellnavn (Tabellnavn).
Nå kan vi bruke dynamiske lenker til vår "smarttabell":
- Tabell 1 – lenke til hele tabellen bortsett fra overskriftsraden (A2:D5)
- Tabell 1[#Alle] – lenke til hele tabellen (A1:D5)
- Tabell 1[Peter] – referanse til en områdekolonne uten den første celleoverskriften (C2:C5)
- Tabell 1[#Overskrifter] – lenke til "overskriften" med navnene på kolonnene (A1:D1)
Slike referanser fungerer utmerket i formler, for eksempel:
= SUM (Tabell 1[Moskva]) – beregning av summen for kolonnen "Moskva"
or
=VPR(F5;Tabell 1;3;0) – søk i tabellen for måneden fra celle F5 og utsted St. Petersburg-summen for den (hva er VLOOKUP?)
Slike lenker kan brukes med hell når du oppretter pivottabeller ved å velge på fanen Sett inn – Pivottabell (Sett inn – Pivottabell) og skriv inn navnet på den smarte tabellen som datakilde:
Hvis du velger et fragment av en slik tabell (for eksempel de to første kolonnene) og lager et diagram av hvilken som helst type, vil de automatisk legges til i diagrammet når du legger til nye linjer.
Når du oppretter nedtrekkslister kan ikke direkte lenker til smarte tabellelementer brukes, men du kan enkelt komme deg rundt denne begrensningen ved hjelp av et taktisk triks – bruk funksjonen INDIREKTE (INDIREKTE), som gjør teksten til en lenke:
De. en lenke til en smart tabell i form av en tekststreng (i anførselstegn!) blir til en fullverdig lenke, og rullegardinlisten oppfatter det normalt.
Metode 2: Dynamisk navngitt område
Hvis det av en eller annen grunn er uønsket å gjøre dataene dine om til en smart tabell, kan du bruke en litt mer komplisert, men mye mer subtil og allsidig metode – lag et dynamisk navngitt område i Excel som refererer til tabellen vår. Deretter, som i tilfellet med en smart tabell, kan du fritt bruke navnet på det opprettede området i alle formler, rapporter, diagrammer osv. La oss starte med et enkelt eksempel:
Oppgave: lag et dynamisk navngitt område som vil referere til en liste over byer og automatisk strekke og krympe i størrelse når du legger til nye byer eller sletter dem.
Vi trenger to innebygde Excel-funksjoner tilgjengelig i alle versjoner − POICPOZ (KAMP) for å bestemme den siste cellen i området, og INDEX (INDEKS) for å lage en dynamisk kobling.
Finne den siste cellen ved å bruke MATCH
MATCH(oppslagsverdi; område; samsvarstype) – en funksjon som søker etter en gitt verdi i et område (rad eller kolonne) og returnerer ordenstallet til cellen der den ble funnet. For eksempel vil formelen MATCH(“Mars”;A1:A5;0) returnere tallet 4 som et resultat, fordi ordet “Mars” er plassert i den fjerde cellen i kolonne A1:A5. Det siste funksjonsargumentet Match_Type = 0 betyr at vi leter etter et eksakt samsvar. Hvis dette argumentet ikke er spesifisert, vil funksjonen bytte til søkemodus for nærmeste minste verdi - dette er nøyaktig hva som kan brukes til å finne den siste okkuperte cellen i matrisen vår.
Essensen av trikset er enkelt. MATCH søker etter celler i området fra topp til bunn, og skal i teorien stoppe når den finner den nærmeste minste verdien til den gitte. Hvis du spesifiserer en verdi som åpenbart er større enn noen tilgjengelig i tabellen som ønsket verdi, vil MATCH nå helt til slutten av tabellen, finne ingenting og gi sekvensnummeret til den sist fylte cellen. Og vi trenger det!
Hvis det bare er tall i matrisen vår, kan vi spesifisere et tall som ønsket verdi, som åpenbart er større enn noen av de i tabellen:
For en garanti kan du bruke tallet 9E + 307 (9 ganger 10 i potens av 307, dvs. 9 med 307 nuller) – det maksimale antallet Excel kan jobbe med i prinsippet.
Hvis det er tekstverdier i kolonnen vår, kan du sette inn konstruksjonen REPEAT(“i”, 255) som ekvivalent med størst mulig tall – en tekststreng bestående av 255 bokstaver “i” – den siste bokstaven i alfabetet. Siden Excel faktisk sammenligner tegnkoder når du søker, vil enhver tekst i tabellen vår teknisk sett være "mindre" enn en så lang "yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy"-linjen:
Generer en lenke ved å bruke INDEX
Nå som vi vet plasseringen til det siste ikke-tomme elementet i tabellen, gjenstår det å danne en kobling til hele utvalget vårt. Til dette bruker vi funksjonen:
INDEX(område; radnummer; kolonnenummer)
Den gir innholdet i cellen fra området etter rad- og kolonnenummer, dvs. for eksempel vil funksjonen =INDEKS(A1:D5;3;4) i tabellen vår med byer og måneder fra forrige metode gi 1240 – innholdet fra 3. rad og 4. kolonne, dvs. cellene D3. Hvis det bare er én kolonne, kan nummeret utelates, dvs. formel INDEX(A2:A6;3) vil gi "Samara" i det siste skjermbildet.
Og det er en ikke helt åpenbar nyanse: Hvis INDEKSEN ikke bare legges inn i cellen etter =-tegnet, som vanlig, men brukes som den siste delen av referansen til området etter kolon, så gir den ikke lenger ut innholdet i cellen, men dens adresse! Dermed vil en formel som $A$2:INDEX($A$2:$A$100;3) gi en referanse til området A2:A4 ved utgangen.
Og det er her MATCH-funksjonen kommer inn, som vi setter inn i INDEX for dynamisk å bestemme slutten av listen:
=$A$2:INDEX($A$2:$A$100; MATCH(REP(“I”;255);A2:A100))
Opprett et navngitt område
Det gjenstår å pakke det hele i en enkelt helhet. Åpne en fane formel (Formler) Og klikk på Navnesjef (navneansvarlig). Klikk på knappen i vinduet som åpnes Opprett (ny), skriv inn vårt områdenavn og formel i feltet Område (Henvisning):
Det gjenstår å klikke på OK og klarområdet kan brukes i alle formler, rullegardinlister eller diagrammer.
- Bruke VLOOKUP-funksjonen til å koble tabeller og oppslagsverdier
- Hvordan lage en rullegardinliste for automatisk fylling
- Hvordan lage en pivottabell for å analysere en stor mengde data