Innehåll
Har du tabeller med data i Excel som kan storleksändras, dvs antalet rader (kolumner) kan öka eller minska under arbetets gång? Om tabellstorlekarna "flyter", måste du ständigt övervaka detta ögonblick och korrigera det:
- länkar i rapportformler som hänvisar till vår tabell
- inledande serier av pivottabeller som är byggda enligt vår tabell
- initiala intervall av diagram byggda enligt vår tabell
- intervall för rullgardinsmenyer som använder vår tabell som datakälla
Allt detta totalt låter dig inte bli uttråkad 😉
Det kommer att vara mycket bekvämare och mer korrekt att skapa ett dynamiskt "gummi"-intervall, som automatiskt anpassar sig i storlek till det faktiska antalet rader och kolumner med data. För att genomföra detta finns det flera sätt.
Metod 1. Smart bord
Markera ditt cellområde och välj från fliken Hem – Formatera som tabell (Hem – Formatera som tabell):
Om du inte behöver den randiga designen som läggs till på bordet som en bieffekt kan du stänga av den på fliken som visas Konstruktör (Design). Varje tabell som skapas på detta sätt får ett namn som kan ersättas med ett bekvämare på samma plats på fliken Konstruktör (Design) på fältet Tabellnamn (Tabellnamn).
Nu kan vi använda dynamiska länkar till vårt "smarta bord":
- Tabell 1 – länk till hela tabellen förutom rubrikraden (A2:D5)
- Tabell 1[#Alla] – länk till hela tabellen (A1:D5)
- Tabell 1[Peter] – referens till en intervallkolumn utan den första cellrubriken (C2:C5)
- Tabell 1[#Headers] – länk till "huvudet" med namnen på kolumnerna (A1:D1)
Sådana referenser fungerar utmärkt i formler, till exempel:
= SUMMA (Tabell 1[Moskva]) – beräkning av summan för kolumnen "Moskva"
or
=VPR(F5;Tabell 1;3;0) – sök i tabellen för månaden från cell F5 och utfärda S:t Petersburg-summan för den (vad är VLOOKUP?)
Sådana länkar kan användas framgångsrikt när du skapar pivottabeller genom att välja på fliken Infoga – Pivottabell (Infoga – Pivottabell) och ange namnet på den smarta tabellen som datakälla:
Om du väljer ett fragment av en sådan tabell (till exempel de två första kolumnerna) och skapar ett diagram av valfri typ, kommer de automatiskt att läggas till i diagrammet när du lägger till nya rader.
När du skapar rullgardinslistor kan direktlänkar till smarta tabellelement inte användas, men du kan enkelt komma runt denna begränsning med ett taktiskt knep – använd funktionen INDIREKT (INDIREKT), som gör texten till en länk:
De där. en länk till en smart tabell i form av en textsträng (inom citattecken!) förvandlas till en fullvärdig länk, och rullgardinsmenyn uppfattar det normalt.
Metod 2: Dynamiskt namngivet intervall
Om det av någon anledning inte är önskvärt att förvandla din data till en smart tabell, kan du använda en lite mer komplicerad, men mycket mer subtil och mångsidig metod – skapa ett dynamiskt namngivet intervall i Excel som refererar till vår tabell. Sedan, som i fallet med en smart tabell, kan du fritt använda namnet på det skapade intervallet i alla formler, rapporter, diagram, etc. Låt oss börja med ett enkelt exempel:
uppgift: gör ett dynamiskt namngivet område som skulle referera till en lista över städer och automatiskt sträcka ut och krympa i storlek när du lägger till nya städer eller tar bort dem.
Vi kommer att behöva två inbyggda Excel-funktioner tillgängliga i alla versioner − POICPOZ (MATCH) för att bestämma den sista cellen i intervallet, och INDEX (INDEX) för att skapa en dynamisk länk.
Hitta den sista cellen med MATCH
MATCH(uppslagsvärde, intervall, matchningstyp) – en funktion som söker efter ett givet värde i ett intervall (rad eller kolumn) och returnerar ordningsnumret för cellen där det hittades. Till exempel kommer formeln MATCH("Mars";A1:A5;0) att returnera siffran 4 som ett resultat, eftersom ordet "Mars" finns i den fjärde cellen i kolumn A1:A5. Det sista funktionsargumentet Match_Type = 0 betyder att vi letar efter en exakt matchning. Om detta argument inte är specificerat, kommer funktionen att växla till sökläget för närmaste minsta värde – det är exakt vad som framgångsrikt kan användas för att hitta den senast upptagna cellen i vår array.
Kärnan i tricket är enkelt. MATCH söker efter celler i intervallet från topp till botten och bör i teorin sluta när den hittar det närmaste minsta värdet till det givna. Om du anger ett värde som uppenbarligen är större än något tillgängligt i tabellen som önskat värde, kommer MATCH att nå slutet av tabellen, hitta ingenting och ge sekvensnumret för den senast fyllda cellen. Och vi behöver det!
Om det bara finns siffror i vår matris kan vi ange ett nummer som det önskade värdet, vilket uppenbarligen är större än någon av de i tabellen:
För en garanti kan du använda siffran 9E + 307 (9 gånger 10 i potensen 307, dvs 9 med 307 nollor) – det maximala antalet som Excel kan arbeta med i princip.
Om det finns textvärden i vår kolumn, kan du, som motsvarighet till största möjliga antal, infoga konstruktionen REPEAT(“i”, 255) – en textsträng bestående av 255 bokstäver “i” – den sista bokstaven i alfabetet. Eftersom Excel faktiskt jämför teckenkoder vid sökning, kommer all text i vår tabell tekniskt sett att vara "mindre" än en så lång "yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy"-raden:
Skapa en länk med hjälp av INDEX
Nu när vi vet positionen för det sista icke-tomma elementet i tabellen återstår det att bilda en länk till hela vårt sortiment. För detta använder vi funktionen:
INDEX(intervall; radnummer; kolumnnummer)
Den ger innehållet i cellen från intervallet efter rad- och kolumnnummer, dvs t.ex. funktionen =INDEX(A1:D5;3;4) i vår tabell med städer och månader från föregående metod ger 1240 – innehållet från 3:e raden och 4:e kolumnen, dvs celler D3. Om det bara finns en kolumn kan dess nummer utelämnas, dvs formeln INDEX(A2:A6;3) ger "Samara" i den sista skärmdumpen.
Och det finns en inte helt uppenbar nyans: om INDEX inte bara skrivs in i cellen efter =-tecknet, som vanligt, utan används som den sista delen av referensen till intervallet efter kolon, så ger det inte längre ut innehållet i cellen, men dess adress! Således kommer en formel som $A$2:INDEX($A$2:$A$100;3) att ge en referens till området A2:A4 vid utgången.
Och det är här MATCH-funktionen kommer in, som vi infogar i INDEX för att dynamiskt bestämma slutet av listan:
=$A$2:INDEX($A$2:$A$100; MATCH(REP("Jag";255);A2:A100))
Skapa ett namngivet intervall
Det återstår att packa allt i en enda helhet. Öppna en flik formeln (Formler) Och klicka på Namnhanterare (namnhanterare). Klicka på knappen i fönstret som öppnas Skapa (ny), ange vårt sortimentsnamn och formel i fältet Mätområde (Referens):
Det återstår att klicka på OK och det färdiga området kan användas i alla formler, rullgardinslistor eller diagram.
- Använda funktionen VLOOKUP för att länka tabeller och uppslagsvärden
- Hur man skapar en rullgardinsmeny som fylls i automatiskt
- Hur man skapar en pivottabell för att analysera en stor mängd data