Inhalter
Hutt Dir Tabelle mat Daten an Excel déi d'Gréisst änneren, dh d'Zuel vun de Reihen (Kolonnen) kann am Laf vun der Aarbecht erop oder erofgoen? Wann d'Tabellgréissten "schwiewen", da musst Dir dëse Moment konstant iwwerwaachen an et korrigéieren:
- Linken a Berichtformelen déi op eis Tabelle bezéien
- initial Gamme vu Pivot Dëscher déi no eisem Dësch gebaut ginn
- initial Gamme vu Charts gebaut no eisem Dësch
- Beräicher fir Dropdowns déi eisen Dësch als Datenquell benotzen
Dat Ganzt léisst Iech net langweilen 😉
Et wäert vill méi praktesch a korrekt sinn eng dynamesch "Gummi" Gamme ze kreéieren, déi automatesch d'Gréisst un d'tatsächlech Zuel vun de Reihen a Spalten vun Daten upassen. Fir dëst ëmzesetzen, ginn et verschidde Weeër.
Method 1. Smart Dësch
Markéiert Är Palette vun Zellen a wielt aus der Tab Home – Format als Table (Home – Format as Table):
Wann Dir de gesträifte Design net braucht, deen als Säit Effekt op den Dësch bäigefüügt gëtt, da kënnt Dir et op der Tab ausschalten déi erschéngt Konstrukteur (Design). All Dësch op dës Manéier erstallt kritt en Numm deen duerch e méi prakteschen op der selwechter Plaz op der Tab ersat ka ginn Konstrukteur (Design) am Feld Dësch Numm (Numm Dësch).
Elo kënne mir dynamesch Linken op eise "Smart Table" benotzen:
- Table 1 - Link op déi ganz Tabell ausser d'Header Zeil (A2:D5)
- Tabell 1[#All] – Link zum ganzen Dësch (A1:D5)
- Dësch 1 [Peter] - Referenz op eng Range-Kolonn ouni den éischten Zell-Header (C2:C5)
- Dësch 1[#Header] - Link op den "Header" mat den Nimm vun de Spalten (A1:D1)
Esou Referenze funktionnéieren super a Formelen, zum Beispill:
= SUM (Dësch 1 [Moskau]) - Berechnung vun der Zomm fir d'Kolonn "Moskau"
or
=VPR(F5;Table 1;3;0) - Sich an der Tabell fir de Mount aus der Zell F5 a gitt d'Sankt Petersburg Zomm dofir (wat ass VLOOKUP?)
Esou Linken kënnen erfollegräich benotzt ginn wann Dir Pivottabellen erstellt andeems Dir op der Tab auswielt Insert - Pivot Table (Insert - Pivot Table) a gitt den Numm vum Smart Dësch als Datenquell:
Wann Dir e Fragment vun esou enger Tabell auswielt (zum Beispill déi éischt zwou Kolonnen) an en Diagramm vun all Typ erstellt, dann wann Dir nei Zeilen bäidréit, ginn se automatesch an d'Diagramm bäigefüügt.
Wann Dir Dropdown-Lëschten erstellt, kënnen direkt Linken op intelligent Dëschelementer net benotzt ginn, awer Dir kënnt dës Limitatioun ganz einfach mat engem takteschen Trick ëmgoen - benotzt d'Funktioun INDIREKTE (INDIREKT), wat den Text an e Link mécht:
Déi. e Link op e Smart Dësch a Form vun engem Text String (an Zitat Zeechen!) gëtt zu engem voll-vollwäerteg Link, an der Dropdown-Lëscht normalerweis gesinn.
Method 2: Dynamic benannt Beräich
Wann Är Donnéeën an e Smart Dësch ëmgewandelt ass aus irgendege Grënn onerwënscht, da kënnt Dir e bësse méi komplizéiert, awer vill méi subtile a versatile Methode benotzen - eng dynamesch benannt Gamme an Excel erstellen déi op eis Tabelle bezitt. Dann, wéi am Fall vun engem Smart Dësch, kënnt Dir fräi den Numm vun der geschaf Gamme an all Formelen benotzen, Rapporten, Charts, etc. Loosst eis mat engem einfache Beispill ufänken:
Aufgab: Maacht eng dynamesch benannt Gamme, déi op eng Lëscht vu Stied referéiert an automatesch ausdehnen a schrumpfen wann Dir nei Stied bäidréit oder se läscht.
Mir brauchen zwou agebaute Excel Funktiounen, déi an all Versioun verfügbar sinn - POICPOZ (MATCH) déi lescht Zell vun der Gamme ze bestëmmen, an INDEX (INDEX) fir en dynamesche Link ze kreéieren.
Fannt déi lescht Zell mat MATCH
MATCH(lookup_value, range, match_type) - eng Funktioun déi no engem bestëmmte Wäert an enger Rei (Zeil oder Kolonn) sicht an d'Ordinale Zuel vun der Zell zréckginn wou se fonnt gouf. Zum Beispill gëtt d'Formel MATCH ("Mäerz"; A1: A5; 0) d'Nummer 4 als Resultat zréck, well d'Wuert "Mäerz" an der véierter Zell an der Kolonn A1: A5 läit. Dat lescht Funktiounsargument Match_Type = 0 heescht datt mir no engem genaue Match sichen. Wann dëst Argument net spezifizéiert ass, wiesselt d'Funktioun op de Sichmodus fir den nooste klengste Wäert - dat ass genau dat wat erfollegräich benotzt ka ginn fir déi lescht besat Zell an eisem Array ze fannen.
D'Essenz vum Trick ass einfach. MATCH sicht no Zellen am Beräich vun uewe bis ënnen an, an der Theorie, soll ophalen wann et de nooste klengste Wäert op dee gëtt. Wann Dir e Wäert uginn, dee selbstverständlech méi grouss ass wéi all verfügbar an der Tabell als gewënschten Wäert, da wäert MATCH um Enn vum Dësch erreechen, näischt fannen a gitt d'Sequenznummer vun der leschter gefëllter Zell. A mir brauchen et!
Wann et nëmmen Zuelen an eisem Array sinn, da kënne mir eng Zuel als de gewënschten Wäert spezifizéieren, deen offensichtlech méi grouss ass wéi eng vun deenen an der Tabell:
Fir eng Garantie kënnt Dir d'Zuel 9E + 307 benotzen (9 Mol 10 op d'Muecht vun 307, also 9 mat 307 Nullen) - déi maximal Zuel, mat där Excel am Prinzip schaffen kann.
Wann et Textwäerter an eiser Kolonn sinn, da kënnt Dir als Äquivalent vun der gréisst méiglecher Zuel d'Konstruktioun REPEAT ("i", 255) aginn - en Textstring besteet aus 255 Buschtawen "i" - de leschte Buschtaf vun d'Alphabet. Well Excel tatsächlech Zeechecodes vergläicht wann Dir sicht, gëtt all Text an eiser Tabell technesch "méi kleng" wéi sou eng laang "yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy" Linn:
Generéiere e Link mat INDEX
Elo wou mir d'Positioun vum leschten net eidelen Element an der Tabell kennen, bleift et fir e Link op eis ganz Gamme ze bilden. Fir dëst benotze mir d'Funktioun:
INDEX(Band; Rei_num; Kolumn_num)
Et gëtt den Inhalt vun der Zell aus dem Beräich vun der Zeil a Kolonnnummer, dh zum Beispill d'Funktioun =INDEX(A1:D5;3;4) an eiser Tabelle mat Stied a Méint vun der viregter Method gëtt 1240 - den Inhalt aus der 3. Zeil an 4. Kolonn, dh Zellen D3. Wann et nëmmen eng Kolonn ass, da kann hir Zuel ewech gelooss ginn, dh Formel INDEX(A2:A6;3) gëtt "Samara" am leschte Screenshot.
An et gëtt eng net ganz offensichtlech Nuance: wann den INDEX net nëmmen nom = Zeechen an d'Zelle agefouert gëtt, wéi gewinnt, mee als leschten Deel vun der Referenz op d'Beräich nom Colon benotzt gëtt, da gëtt et net méi eraus den Inhalt vun der Zell, mä seng Adress! Also, eng Formel wéi $A $ 2: INDEX ($ A $ 2: $ A $ 100; 3) gëtt eng Referenz op d'Beräich A2: A4 um Ausgang ginn.
An dat ass wou d'MATCH Funktioun erakënnt, déi mir am INDEX setzen fir dynamesch d'Enn vun der Lëscht ze bestëmmen:
=$A$2:INDEX($A$2:$A$100; MATCH(REP("ech";255);A2:A100))
Schafen eng benannt Gamme
Et bleift fir alles an een eenzegt Ganzt ze packen. Öffnen eng Tab Formule (Formelen) An klickt op Numm Manager (Numm Manager). An der Fënster déi opmaacht, klickt op de Knäppchen schafen (nei), gitt eise Range Numm a Formel am Feld Rei (Referenz):
Et bleift ze klicken OK an der prett Gamme kann an all Formulen benotzt ginn, Dropdown-Lëschte oder Charts.
- Benotzt d'VLOOKUP Funktioun fir Tabellen a Lookup Wäerter ze verbannen
- Wéi eng automatesch populéierend Dropdown Lëscht erstellen
- Wéi eng Pivot-Tabelle erstellen fir eng grouss Quantitéit un Daten ze analyséieren