Indhold
Har du tabeller med data i Excel, der kan ændres størrelse, dvs. antallet af rækker (kolonner) kan stige eller falde i løbet af arbejdet? Hvis bordstørrelserne "flyder", bliver du nødt til konstant at overvåge dette øjeblik og rette det:
- links i rapportformler, der henviser til vores tabel
- indledende serier af pivotborde, der er bygget i henhold til vores tabel
- indledende rækker af diagrammer bygget i henhold til vores tabel
- intervaller for rullemenuer, der bruger vores tabel som datakilde
Alt dette i alt vil ikke lade dig kede dig 😉
Det vil være meget mere praktisk og korrekt at skabe et dynamisk "gummi"-område, som automatisk justerer i størrelse til det faktiske antal rækker og kolonner med data. For at implementere dette er der flere måder.
Metode 1. Smart bord
Fremhæv dit celleområde, og vælg fra fanen Hjem – Formatér som tabel (Hjem – Formater som tabel):
Hvis du ikke har brug for det stribede design, der er tilføjet til bordet som en bivirkning, så kan du slå det fra på den fane, der vises Konstruktør (design). Hver tabel, der er oprettet på denne måde, får et navn, der kan erstattes med et mere bekvemt på samme sted på fanen Konstruktør (design) i marken Tabelnavn (Tabelnavn).
Nu kan vi bruge dynamiske links til vores "smart table":
- Tabel 1 – link til hele tabellen undtagen overskriftsrækken (A2:D5)
- Tabel 1[#Alle] – link til hele tabellen (A1:D5)
- Tabel 1[Peter] – reference til en områdekolonne uden den første celleoverskrift (C2:C5)
- Tabel 1[#Overskrifter] – link til "headeren" med navnene på kolonnerne (A1:D1)
Sådanne referencer fungerer godt i formler, for eksempel:
= SUM (Tabel 1[Moskva]) – beregning af summen for kolonnen "Moskva"
or
=VPR(F5;Tabel 1;3;0) – søg i tabellen for måneden fra celle F5 og udsted St. Petersborg-summen for den (hvad er VLOOKUP?)
Sådanne links kan med succes bruges, når du opretter pivottabeller ved at vælge på fanen Indsæt – Pivottabel (Indsæt – Pivottabel) og indtast navnet på den smarte tabel som datakilden:
Hvis du vælger et fragment af en sådan tabel (for eksempel de to første kolonner) og opretter et diagram af enhver type, vil de automatisk blive tilføjet til diagrammet, når du tilføjer nye linjer.
Når du opretter rullelister, kan direkte links til smarte tabelelementer ikke bruges, men du kan nemt komme uden om denne begrænsning ved hjælp af et taktisk trick – brug funktionen INDIREKTE (INDIREKTE), som gør teksten til et link:
De der. et link til en smart tabel i form af en tekststreng (i anførselstegn!) bliver til et fuldgyldigt link, og rullelisten opfatter det normalt.
Metode 2: Dynamisk navngivet område
Hvis det af en eller anden grund er uønsket at omdanne dine data til en smart tabel, så kan du bruge en lidt mere kompliceret, men meget mere subtil og alsidig metode – skab et dynamisk navngivet område i Excel, der refererer til vores tabel. Så kan du, som i tilfældet med en smart tabel, frit bruge navnet på det oprettede område i alle formler, rapporter, diagrammer osv. Lad os starte med et simpelt eksempel:
Opgaver: lav et dynamisk navngivet område, der vil referere til en liste over byer og automatisk strække og krympe i størrelse, når du tilføjer nye byer eller sletter dem.
Vi skal bruge to indbyggede Excel-funktioner, der er tilgængelige i enhver version - POICPOZ (MATCH) for at bestemme den sidste celle i området, og INDEX (INDEKS) for at skabe et dynamisk link.
Find den sidste celle ved hjælp af MATCH
MATCH(opslagsværdi; interval; matchtype) – en funktion, der søger efter en given værdi i et område (række eller kolonne) og returnerer ordenstallet for den celle, hvor den blev fundet. For eksempel vil formlen MATCH(“March”;A1:A5;0) returnere tallet 4 som et resultat, fordi ordet “March” er placeret i den fjerde celle i kolonne A1:A5. Det sidste funktionsargument Match_Type = 0 betyder, at vi leder efter et eksakt match. Hvis dette argument ikke er angivet, vil funktionen skifte til søgetilstand for den nærmeste mindste værdi - det er præcis, hvad der med succes kan bruges til at finde den sidst besatte celle i vores array.
Essensen af tricket er simpelt. MATCH søger efter celler i området fra top til bund og skulle i teorien stoppe, når den finder den nærmeste mindste værdi til den givne. Hvis du angiver en værdi, der åbenbart er større end nogen tilgængelig i tabellen, som den ønskede værdi, så vil MATCH nå helt til slutningen af tabellen, ikke finde noget og give sekvensnummeret for den sidst udfyldte celle. Og vi har brug for det!
Hvis der kun er tal i vores array, så kan vi angive et tal som den ønskede værdi, som naturligvis er større end nogen af dem i tabellen:
Som garanti kan du bruge tallet 9E + 307 (9 gange 10 i potens af 307, altså 9 med 307 nuller) – det maksimale antal, Excel i princippet kan arbejde med.
Hvis der er tekstværdier i vores kolonne, så kan du som ækvivalent med det størst mulige tal indsætte konstruktionen REPEAT(“i”, 255) – en tekststreng bestående af 255 bogstaver “i” – det sidste bogstav i alfabetet. Da Excel faktisk sammenligner tegnkoder, når du søger, vil enhver tekst i vores tabel teknisk set være "mindre" end sådan en lang "yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy" linje:
Generer et link ved hjælp af INDEX
Nu hvor vi kender placeringen af det sidste ikke-tomme element i tabellen, er det tilbage at danne et link til hele vores sortiment. Til dette bruger vi funktionen:
INDEX(område; række_nummer; kolonnenummer)
Den giver cellens indhold fra området efter række- og kolonnenummer, dvs. f.eks. vil funktionen =INDEX(A1:D5;3;4) i vores tabel med byer og måneder fra den forrige metode give 1240 – indholdet fra 3. række og 4. kolonne, dvs. celler D3. Hvis der kun er én kolonne, kan dens nummer udelades, dvs. formel INDEX(A2:A6;3) vil give “Samara” i det sidste skærmbillede.
Og der er en ikke helt åbenlys nuance: Hvis INDEX ikke bare indtastes i cellen efter = tegnet, som sædvanligt, men bruges som den sidste del af referencen til området efter kolon, så giver det ikke længere ud indholdet af cellen, men dens adresse! Således vil en formel som $A$2:INDEX($A$2:$A$100;3) give en reference til området A2:A4 ved udgangen.
Og det er her MATCH-funktionen kommer ind, som vi indsætter i INDEX for dynamisk at bestemme slutningen af listen:
=$A$2:INDEX($A$2:$A$100; MATCH(REP(“I”;255);A2:A100))
Opret et navngivet område
Det er tilbage at pakke det hele i en enkelt helhed. Åbn en fane Formula (Formler) Og klik på Navn Manager (navneadministrator). Klik på knappen i det vindue, der åbnes Opret (ny), indtast vores områdenavn og formel i feltet Range (Reference):
Det er tilbage at klikke på OK og klarområdet kan bruges i alle formler, rullelister eller diagrammer.
- Brug af VLOOKUP-funktionen til at sammenkæde tabeller og opslagsværdier
- Sådan opretter du en rulleliste med automatisk udfyldning
- Sådan opretter du en pivottabel til at analysere en stor mængde data