Inhalte
Haben Sie Tabellen mit Daten in Excel, die in der Größe verändert werden können, dh die Anzahl der Zeilen (Spalten) kann sich im Laufe der Arbeit vergrößern oder verkleinern? Wenn die Tabellengrößen „schweben“, müssen Sie diesen Moment ständig überwachen und korrigieren:
- Links in Berichtsformeln, die auf unsere Tabelle verweisen
- Anfangsserien von Pivot-Tabellen, die nach unserer Tabelle aufgebaut sind
- Anfangsbereiche von Diagrammen, die gemäß unserer Tabelle erstellt wurden
- Bereiche für Dropdowns, die unsere Tabelle als Datenquelle verwenden
All das in Summe lässt keine Langeweile aufkommen 😉
Es ist viel bequemer und korrekter, einen dynamischen „Gummi“ -Bereich zu erstellen, dessen Größe automatisch an die tatsächliche Anzahl von Zeilen und Spalten mit Daten angepasst wird. Um dies zu implementieren, gibt es mehrere Möglichkeiten.
Methode 1. Intelligente Tabelle
Markieren Sie Ihren Zellbereich und wählen Sie auf der Registerkarte aus Home – Als Tabelle formatieren (Home – Als Tabelle formatieren):
Wenn Sie das Streifendesign, das der Tabelle als Nebeneffekt hinzugefügt wird, nicht benötigen, können Sie es auf der angezeigten Registerkarte deaktivieren Konstrukteur (Entwurf). Jede so erstellte Tabelle erhält einen Namen, der an gleicher Stelle auf der Registerkarte durch einen bequemeren ersetzt werden kann Konstrukteur (Entwurf) im Bereich Tabellenname (Tabellenname).
Jetzt können wir dynamische Links zu unserem „intelligenten Tisch“ verwenden:
- Tabelle 1 – Link zur gesamten Tabelle außer der Kopfzeile (A2:D5)
- Tabelle1[#Alle] – Link zur gesamten Tabelle (A1:D5)
- Tisch1[Peter] – Verweis auf eine Bereichsspalte ohne den ersten Zellenkopf (C2:C5)
- Tabelle1[#Kopfzeilen] – Link zum „Header“ mit den Namen der Spalten (A1:D1)
Solche Verweise funktionieren hervorragend in Formeln, zum Beispiel:
= SUMME (Tabelle1[Moskau]) – Berechnung der Summe für die Spalte „Moskau“
or
=VPR(F5;Tabelle 1;3;0) – suchen Sie in der Tabelle nach dem Monat ab Zelle F5 und geben Sie die St. Petersburger Summe dafür aus (was ist SVERWEIS?)
Solche Verknüpfungen können beim Erstellen von Pivot-Tabellen erfolgreich verwendet werden, indem Sie auf der Registerkarte auswählen Einfügen – Pivot-Tabelle (Einfügen – Pivot-Tabelle) und als Datenquelle den Namen der Smart Table eintragen:
Wenn Sie ein Fragment einer solchen Tabelle auswählen (z. B. die ersten beiden Spalten) und ein beliebiges Diagramm erstellen, werden diese beim Hinzufügen neuer Zeilen automatisch zum Diagramm hinzugefügt.
Beim Erstellen von Dropdown-Listen können keine direkten Links zu intelligenten Tabellenelementen verwendet werden, aber Sie können diese Einschränkung mit einem taktischen Trick leicht umgehen – verwenden Sie die Funktion INDIREKT (INDIREKT), wodurch der Text in einen Link umgewandelt wird:
Diese. ein Link auf eine intelligente Tabelle in Form einer Textzeichenfolge (in Anführungszeichen!) wird zu einem vollwertigen Link, der normalerweise von der Dropdown-Liste wahrgenommen wird.
Methode 2: Dynamischer benannter Bereich
Wenn es aus irgendeinem Grund unerwünscht ist, Ihre Daten in eine intelligente Tabelle umzuwandeln, können Sie eine etwas kompliziertere, aber viel subtilere und vielseitigere Methode verwenden – erstellen Sie einen dynamischen benannten Bereich in Excel, der auf unsere Tabelle verweist. Dann können Sie den Namen des erstellten Bereichs wie bei einer intelligenten Tabelle in beliebigen Formeln, Berichten, Diagrammen usw. frei verwenden. Beginnen wir mit einem einfachen Beispiel:
Aufgabe: Erstellen Sie einen dynamischen benannten Bereich, der sich auf eine Liste von Städten bezieht und automatisch gedehnt und verkleinert wird, wenn neue Städte hinzugefügt oder gelöscht werden.
Wir benötigen zwei eingebaute Excel-Funktionen, die in jeder Version verfügbar sind − POICPOZ (PASSEN) um die letzte Zelle des Bereichs zu bestimmen, und INDEX (INDEX) um einen dynamischen Link zu erstellen.
Finden der letzten Zelle mit MATCH
MATCH(Suchwert, Bereich, Übereinstimmungstyp) – eine Funktion, die nach einem bestimmten Wert in einem Bereich (Zeile oder Spalte) sucht und die Ordnungszahl der Zelle zurückgibt, in der sie gefunden wurde. Beispielsweise gibt die Formel MATCH(“März”;A1:A5;0) als Ergebnis die Zahl 4 zurück, da sich das Wort „März” in der vierten Zelle in Spalte A1:A5 befindet. Das letzte Funktionsargument Match_Type = 0 bedeutet, dass wir nach einer exakten Übereinstimmung suchen. Wenn dieses Argument nicht angegeben wird, wechselt die Funktion in den Suchmodus für den nächstkleineren Wert – genau das kann erfolgreich verwendet werden, um die letzte belegte Zelle in unserem Array zu finden.
Die Essenz des Tricks ist einfach. MATCH sucht nach Zellen im Bereich von oben nach unten und sollte theoretisch aufhören, wenn es den kleinsten Wert findet, der dem gegebenen am nächsten liegt. Wenn Sie als gewünschten Wert einen Wert angeben, der offensichtlich größer ist als jeder in der Tabelle verfügbare Wert, dann gelangt MATCH ganz ans Ende der Tabelle, findet nichts und gibt die fortlaufende Nummer der letzten gefüllten Zelle aus. Und wir brauchen es!
Wenn unser Array nur Zahlen enthält, können wir als gewünschten Wert eine Zahl angeben, die offensichtlich größer ist als alle in der Tabelle:
Zur Sicherheit können Sie die Zahl 9E + 307 (9 mal 10 hoch 307, also 9 mit 307 Nullen) verwenden – die maximale Zahl, mit der Excel prinzipiell arbeiten kann.
Wenn in unserer Spalte Textwerte vorhanden sind, können Sie als Äquivalent zur größtmöglichen Zahl die Konstruktion REPEAT („i“, 255) einfügen – eine Textzeichenfolge, die aus 255 Buchstaben besteht „i“ – der letzte Buchstabe von das Alphabet. Da Excel bei der Suche tatsächlich Zeichencodes vergleicht, ist jeder Text in unserer Tabelle technisch „kleiner“ als eine so lange „yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy“-Zeile:
Generieren Sie einen Link mit INDEX
Nun, da wir die Position des letzten nicht leeren Elements in der Tabelle kennen, bleibt es, eine Verbindung zu unserem gesamten Sortiment herzustellen. Dazu verwenden wir die Funktion:
INDEX(Bereich; Zeilennummer; Spaltennummer)
Sie gibt den Inhalt der Zelle aus dem Bereich nach Zeilen- und Spaltennummer an, dh zum Beispiel die Funktion =INDEX(A1:D5;3;4) in unserer Tabelle mit Städten und Monaten aus der vorherigen Methode ergibt 1240 – den Inhalt aus der 3. Zeile und 4. Spalte, dh Zellen D3. Wenn es nur eine Spalte gibt, kann deren Nummer weggelassen werden, dh die Formel INDEX(A2:A6;3) ergibt im letzten Screenshot „Samara“.
Und es gibt eine nicht ganz offensichtliche Nuance: Wenn der INDEX nicht wie üblich einfach in die Zelle nach dem =-Zeichen eingetragen wird, sondern als abschließender Teil des Verweises auf den Bereich nach dem Doppelpunkt verwendet wird, gibt er nicht mehr her der Inhalt der Zelle, sondern ihre Adresse! Daher gibt eine Formel wie $A$2:INDEX($A$2:$A$100;3) einen Verweis auf den Bereich A2:A4 bei der Ausgabe.
Und hier kommt die MATCH-Funktion ins Spiel, die wir innerhalb des INDEX einfügen, um das Ende der Liste dynamisch zu bestimmen:
=$A$2:INDEX($A$2:$A$100; MATCH(REP(“I”;255);A2:A100))
Erstellen Sie einen benannten Bereich
Es bleibt, alles in ein einziges Ganzes zu packen. Öffnen Sie eine Registerkarte Formel (Formeln) Und klicke auf das Name Manager (Namensmanager). Klicken Sie im sich öffnenden Fenster auf die Schaltfläche Erstellen (Neu), geben Sie unseren Bereichsnamen und unsere Formel in das Feld ein Abdeckung (Referenz):
Es bleibt zu klicken OK und der fertige Bereich kann in beliebigen Formeln, Dropdown-Listen oder Diagrammen verwendet werden.
- Verwenden der SVERWEIS-Funktion zum Verknüpfen von Tabellen und Nachschlagewerten
- So erstellen Sie eine automatisch ausgefüllte Dropdown-Liste
- So erstellen Sie eine Pivot-Tabelle, um eine große Datenmenge zu analysieren