Contents [show]
Het jy tabelle met data in Excel wat verander kan word, maw die aantal rye (kolomme) kan toeneem of verminder in die loop van werk? As die tabelgroottes "dryf", moet u hierdie oomblik voortdurend monitor en dit regstel:
- skakels in verslagformules wat na ons tabel verwys
- aanvanklike reekse van spiltafels wat volgens ons tabel gebou is
- aanvanklike reekse van kaarte gebou volgens ons tabel
- reekse vir aftrekkies wat ons tabel as 'n databron gebruik
Dit alles in totaal sal jou nie laat verveeld raak nie 😉
Dit sal baie meer gerieflik en korrek wees om 'n dinamiese "rubber" reeks te skep, wat outomaties in grootte sal aanpas by die werklike aantal rye en kolomme data. Om dit te implementeer, is daar verskeie maniere.
Metode 1. Slim tafel
Merk jou reeks selle en kies op die oortjie Tuis – Formateer as tabel (Tuis – Formateer as tabel):
As jy nie die gestreepte ontwerp nodig het wat as 'n newe-effek by die tabel gevoeg is nie, dan kan jy dit afskakel op die oortjie wat verskyn Konstrukteur (Ontwerp). Elke tabel wat op hierdie manier geskep word, ontvang 'n naam wat vervang kan word met 'n geriefliker een op dieselfde plek op die oortjie Konstrukteur (Ontwerp) in die veld Tafelnaam (Tabel Naam).
Nou kan ons dinamiese skakels na ons "slim tafel" gebruik:
- Tabel 1 – skakel na die hele tabel behalwe die kopry (A2:D5)
- Tabel 1[#Alle] – skakel na die hele tabel (A1:D5)
- Tabel 1[Peter] – verwysing na 'n reekskolom sonder die eerste selopskrif (C2:C5)
- Tabel 1[#Opskrifte] – skakel na die “header” met die name van die kolomme (A1:D1)
Sulke verwysings werk uitstekend in formules, byvoorbeeld:
= SUM (Tabel 1[Moskou]) - berekening van die som vir die kolom "Moskou"
or
=VPR(F5;Tabel 1;3;0) – soek in die tabel vir die maand vanaf sel F5 en reik die St. Petersburg-som daarvoor uit (wat is VLOOKUP?)
Sulke skakels kan suksesvol gebruik word wanneer spilpunttabelle geskep word deur op die oortjie te kies Invoeg – Spiltabel (Voeg in – Spiltabel) en voer die naam van die slim tabel as die databron in:
As jy 'n fragment van so 'n tabel kies (byvoorbeeld die eerste twee kolomme) en 'n diagram van enige tipe skep, sal hulle outomaties by die diagram gevoeg word wanneer nuwe lyne bygevoeg word.
Wanneer u aftreklyste skep, kan direkte skakels na slimtafelelemente nie gebruik word nie, maar u kan hierdie beperking maklik omseil deur 'n taktiese truuk te gebruik - gebruik die funksie INDIREKTE (INDIREK), wat die teks in 'n skakel verander:
Dié. 'n skakel na 'n slim tabel in die vorm van 'n teksstring (in aanhalingstekens!) verander in 'n volwaardige skakel, en die aftreklys neem dit normaalweg waar.
Metode 2: Dinamiese benoemde reeks
As dit om een of ander rede ongewens is om jou data in 'n slim tabel te verander, kan jy 'n effens meer ingewikkelde, maar baie meer subtiele en veelsydige metode gebruik – skep 'n dinamiese genoemde reeks in Excel wat na ons tabel verwys. Dan, soos in die geval van 'n slim tabel, kan jy vrylik die naam van die geskepte reeks gebruik in enige formules, verslae, grafieke, ens. Kom ons begin met 'n eenvoudige voorbeeld:
Taak: maak 'n dinamiese benoemde reeks wat sal verwys na 'n lys van stede en outomaties strek en krimp in grootte wanneer nuwe stede bygevoeg of hulle uitgevee word.
Ons sal twee ingeboude Excel-funksies benodig wat in enige weergawe beskikbaar is − POICPOZ (MATCH) om die laaste sel van die reeks te bepaal, en INDEKS (INDEKS) om 'n dinamiese skakel te skep.
Vind die laaste sel met MATCH
MATCH(soekwaarde, reeks, pasmaattipe) – 'n funksie wat na 'n gegewe waarde in 'n reeks (ry of kolom) soek en die ranggetal van die sel waar dit gevind is, terugstuur. Byvoorbeeld, die formule MATCH(“March”;A1:A5;0) sal die getal 4 as gevolg daarvan terugstuur, want die woord “March” is in die vierde sel in kolom A1:A5 geleë. Die laaste funksie-argument Match_Type = 0 beteken ons soek 'n presiese passing. As hierdie argument nie gespesifiseer is nie, sal die funksie oorskakel na die soekmodus vir die naaste kleinste waarde – dit is presies wat suksesvol gebruik kan word om die laaste besette sel in ons skikking te vind.
Die essensie van die truuk is eenvoudig. MATCH soek na selle in die reeks van bo na onder en behoort in teorie te stop wanneer dit die naaste kleinste waarde aan die gegewe een vind. As jy 'n waarde spesifiseer wat natuurlik groter is as enige beskikbaar in die tabel as die verlangde waarde, dan sal MATCH heel aan die einde van die tabel bereik, niks vind nie en die rynommer van die laaste gevulde sel gee. En ons het dit nodig!
As daar net getalle in ons skikking is, kan ons 'n getal as die verlangde waarde spesifiseer, wat natuurlik groter is as enige van die in die tabel:
Vir ’n waarborg kan jy die getal 9E + 307 (9 maal 10 tot die mag van 307, maw 9 met 307 nulle) gebruik – die maksimum getal waarmee Excel in beginsel kan werk.
As daar tekswaardes in ons kolom is, kan jy as die ekwivalent van die grootste moontlike getal die konstruksie REPEAT(“i”, 255) invoeg – ’n teksstring wat uit 255 letters “i” bestaan – die laaste letter van die alfabet. Aangesien Excel eintlik karakterkodes vergelyk wanneer jy soek, sal enige teks in ons tabel tegnies "kleiner" wees as so 'n lang "yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy"-reël:
Genereer 'n skakel met behulp van INDEX
Noudat ons die posisie van die laaste nie-leë element in die tabel ken, bly dit om 'n skakel na ons hele reeks te vorm. Hiervoor gebruik ons die funksie:
INDEX(reeks; ry_getal; kolom_getal)
Dit gee die inhoud van die sel uit die reeks volgens ry- en kolomnommer, maw byvoorbeeld, die funksie =INDEX(A1:D5;3;4) in ons tabel met stede en maande van die vorige metode sal 1240 gee – die inhoud vanaf die 3de ry en 4de kolom, dws selle D3. As daar net een kolom is, kan die nommer daarvan weggelaat word, dws formule INDEX(A2:A6;3) sal “Samara” in die laaste kiekie gee.
En daar is een nie heeltemal duidelike nuanse nie: as die INDEKS nie net soos gewoonlik in die sel na die =-teken ingevoer word nie, maar as die laaste deel van die verwysing na die reeks na die dubbelpunt gebruik word, gee dit nie meer uit nie. die inhoud van die sel, maar sy adres! Dus, 'n formule soos $A$2:INDEX($A$2:$A$100;3) sal 'n verwysing na die reeks A2:A4 by die uitset gee.
En dit is waar die MATCH-funksie inkom, wat ons in die INDEX invoeg om die einde van die lys dinamies te bepaal:
=$A$2:INDEX($A$2:$A$100; MATCH(REP(“I”;255);A2:A100))
Skep 'n benoemde reeks
Dit bly om dit alles in 'n enkele geheel te pak. Maak 'n oortjie oop formule (Formules) En klik op die Naam Bestuurder (Naambestuurder). Klik op die knoppie in die venster wat oopmaak Skep (Nuwe), voer ons reeksnaam en formule in die veld in Range (Verwysing):
Dit bly om op te klik OK en die gereed reeks kan in enige formules, aftreklyste of grafieke gebruik word.
- Gebruik die VLOOKUP-funksie om tabelle en opsoekwaardes te koppel
- Hoe om 'n outo-invul-aftreklys te skep
- Hoe om 'n spiltabel te skep om 'n groot hoeveelheid data te ontleed