Dynamische Tabellen in Excel sind die Tabellen, in denen die Tabelle beim Einfügen eines neuen Werts ihre Größe selbst anpasst. Um eine dynamische Tabelle in Excel zu erstellen, haben wir zwei verschiedene Methoden, die einmal eine Tabelle der Daten aus dem erstellen Tabellenabschnitt, während ein anderer die Offset-Funktion verwendet, ändern sich in dynamischen Tabellen auch die Berichte und Pivot-Tabellen, wenn sich die Daten in der dynamischen Tabelle ändern.
Dynamische Tabellen in Excel
Dynamisch an sich bedeutet es ein Prozessorsystem, das für eine ständige Änderung oder eine Änderung der Aktivität gekennzeichnet ist. Ebenso kann in Excel, wenn wir Listen oder Daten in einer Arbeitsmappe erstellen und daraus einen Bericht erstellen, aber wenn wir Daten hinzufügen oder entfernen oder die Daten verschieben oder ändern, der gesamte Bericht ungenau sein. Excel hat eine Lösung dafür als dynamische Tabellen.
Nun stellt sich die Frage, warum wir Dynamic Range oder Dynamic Tables benötigen. Die Antwort darauf ist, dass bei jeder Aktualisierung oder Änderung einer Liste oder eines Datenbereichs nicht sicher ist, dass der Bericht gemäß der Datenänderung geändert wird.
Grundsätzlich gibt es zwei Hauptvorteile von dynamischen Tabellen:
- Ein dynamischer Bereich wird gemäß der Datenänderung automatisch erweitert oder verkleinert.
- Pivot-Tabellen, die auf der dynamischen Tabelle in Excel basieren, können automatisch aktualisiert werden, wenn der Pivot aktualisiert wird.
Wie erstelle ich dynamische Tabellen in Excel?
Es gibt zwei grundlegende Möglichkeiten, dynamische Tabellen in Excel zu verwenden: 1) Verwenden von Tabellen und 2) Verwenden der OFFSET-Funktion.
Sie können diese Excel-Vorlage für dynamische Tabellen hier herunterladen - Excel-Vorlage für dynamische Tabellen# 1 - Verwenden von Tabellen zum Erstellen dynamischer Tabellen in Excel
Mithilfe von Tabellen können wir eine dynamische Tabelle in Excel erstellen und einen Pivot über der dynamischen Tabelle erstellen.
Beispiel
Wir haben die folgenden Daten,
Wenn wir eine Pivot-Tabelle mit diesem normalen Datenbereich von A1: E6 erstellen, werden Daten in Zeile 7 nicht in die Pivot-Tabelle eingefügt.
Also werden wir zuerst einen Dynamikbereich erstellen.
# 1 - Wählen Sie die Daten aus, dh A1: E6.
# 2 - Klicken Sie in der Einfügung mit der Registerkarte auf Tabellen unter dem Abschnitt Tabellen.
# 3 - Ein Dialogfeld wird angezeigt.
Da unsere Daten Überschriften enthalten, aktivieren Sie das Kontrollkästchen "Meine Tabelle hat Überschriften" und klicken Sie auf "OK".
# 4 - Unser Dynamikbereich wird erstellt.
# 5 - Wählen Sie die Daten aus und klicken Sie auf der Registerkarte Einfügen im Abschnitt Excel-Tabellen auf Pivot-Tabellen.
# 6 - Während wir die Tabelle erstellt haben, nimmt sie einen Bereich wie Tabelle 2 an. Klicken Sie auf OK und ziehen Sie in den Pivot-Tabellen Produkt in Zeilen und Verkäufe in Werte.
# 7 - Jetzt in Blatt, wo wir unsere Tabelle haben, fügen Sie weitere Daten in 7 ein
Aktualisieren Sie in der Pivot-Tabelle die Pivot-Tabelle.
Unsere dynamische Pivot-Tabelle hat die Daten von Produkt 6 in der Pivot-Tabelle automatisch aktualisiert.
# 2 - Verwenden der OFFSET-Funktion zum Erstellen einer dynamischen Tabelle in Excel
Wir können auch die OFFSET-Funktion verwenden, um dynamische Tabellen in Excel zu erstellen. Schauen wir uns ein solches Beispiel an.
Beispiel
Ich habe eine Preisliste für meine Produkte, die ich für meine Berechnungen verwende.
Wählen Sie die Daten aus und geben Sie ihr einen Namen
Wenn ich jetzt auf die Preisliste des Datensatzes verweise, gehe ich zu den Daten im Bereich B2: C7, auf denen meine Preisliste steht. Wenn ich jedoch eine weitere Zeile mit den Daten aktualisiere, werde ich trotzdem in den Bereich B2: C7 versetzt, da unsere Liste statisch ist.
Wir werden die Versatzfunktion verwenden, um den Datenbereich so dynamisch wie möglich zu gestalten.
# 1 - Klicken Sie im definierten Bereich auf der Registerkarte Formeln auf Definierter Name. Ein Dialogfeld wird angezeigt.
# 2 - Geben Sie in das Namensfeld einen beliebigen Namen ein, ich werde den PriceA verwenden. Der Bereich ist die aktuelle Arbeitsmappe und bezieht sich derzeit auf die aktuell ausgewählte Zelle B2.
In Bezieht sich das Schreiben der folgenden Formel:
= Offset (Sheet2! $ B $ 2,1,0, counta (Sheet2! $ B: $ B) -1,2)
= Offset (
# 3 - Wählen Sie nun die Startzelle B2 aus.
# 4 - Jetzt müssen wir 1,0 eingeben, da es zählt, wie viele Zeilen oder Spalten noch übrig sind
# 5 - Jetzt müssen wir alle Daten in Spalte B zählen und als Anzahl der Zeilen verwenden. Verwenden Sie also die COUNTA-Funktion und wählen Sie Spalte B aus.
# 6 - Da wir nicht möchten, dass die erste Zeile, die der Produktheader ist, gezählt wird, wird (-) 1 davon gezählt.
# 7 - Jetzt ist die Anzahl der Spalten immer zwei. Geben Sie also 2 ein und klicken Sie auf OK.
# 8 - Dieser Datenbereich ist standardmäßig nicht sichtbar. Um dies zu sehen, müssen Sie auf der Registerkarte Formel auf Name Manager klicken und Produkt auswählen.
# 9 - Wenn wir auf verweisen, wird der Datenbereich angezeigt.
# 10 - Fügen Sie nun ein weiteres Produkt in die Tabelle Produkt 6 ein.
# 11 - Klicken Sie nun im Namensmanager auf Produkttabelle, die sich auch auf die neu eingefügten Daten bezieht.
Auf diese Weise können wir die Offset-Funktion verwenden, um dynamische Tabellen zu erstellen.
Dinge, an die man sich erinnern sollte
- Pivot-Tabellen, die auf dem Dynamikbereich basieren, werden beim Aktualisieren automatisch aktualisiert.
- Die Verwendung der Versatzfunktion in Definierte Namen kann im Namensmanager auf der Registerkarte "Formel" angezeigt werden.
