Dynamische Arrays in Excel

Inhalte

Was sind dynamische arrays

Im September 2018 hat Microsoft ein Update veröffentlicht, das Microsoft Excel um ein völlig neues Tool erweitert: Dynamische Arrays und 7 neue Funktionen, um damit zu arbeiten. Diese Dinge ändern ohne Übertreibung die gesamte übliche Technik der Arbeit mit Formeln und Funktionen radikal und betreffen buchstäblich jeden Benutzer.

Betrachten Sie ein einfaches Beispiel, um das Wesentliche zu erklären.

Angenommen, wir haben eine einfache Tabelle mit Daten zu Stadtmonaten. Was passiert, wenn wir eine leere Zelle rechts auf dem Blatt auswählen und eine Formel eingeben, die nicht mit einer Zelle, sondern sofort mit einem Bereich verknüpft ist?

In allen früheren Versionen von Excel nach dem Klicken auf Enter wir würden den Inhalt von nur einer ersten Zelle B2 erhalten. Wie sonst?

Nun, oder es wäre möglich, diesen Bereich in eine Art Aggregationsfunktion wie = SUMME (B2: C4) zu packen und eine Gesamtsumme dafür zu erhalten.

Wenn wir komplexere Operationen als eine primitive Summe benötigen würden, wie z. B. das Extrahieren von eindeutigen Werten oder Top 3, dann müssten wir unsere Formel als Array-Formel über eine Tastenkombination eingeben Ctrl+Shift+Enter.

Jetzt ist alles anders.

Jetzt können wir nach Eingabe einer solchen Formel einfach auf klicken Enter – und erhalten als Ergebnis sofort alle Werte, auf die wir uns bezogen haben:

Das ist keine Zauberei, sondern die neuen dynamischen Arrays, die Microsoft Excel jetzt hat. Willkommen in der neuen Welt 🙂

Merkmale der Arbeit mit dynamischen Arrays

Technisch gesehen ist unser gesamtes dynamisches Array in der ersten Zelle G4 gespeichert und füllt die erforderliche Anzahl von Zellen rechts und unten mit seinen Daten. Wenn Sie eine andere Zelle im Array auswählen, ist der Link in der Bearbeitungsleiste inaktiv und zeigt an, dass wir uns in einer der „untergeordneten“ Zellen befinden:

Ein Versuch, eine oder mehrere „untergeordnete“ Zellen zu löschen, führt zu nichts – Excel berechnet und füllt sie sofort neu.

Gleichzeitig können wir in anderen Formeln sicher auf diese „untergeordneten“ Zellen verweisen:

Wenn Sie die erste Zelle eines Arrays kopieren (z. B. von G4 nach F8), bewegt sich das gesamte Array (seine Referenzen) in die gleiche Richtung wie in regulären Formeln:

Wenn wir das Array verschieben müssen, reicht es aus, es zu verschieben (mit der Maus oder einer Kombination aus Ctrl+X, Ctrl+V), wieder nur die erste Hauptzelle G4 – danach wird sie an einen neuen Ort verlegt und unser gesamtes Array wird erneut erweitert.

Wenn Sie an einer anderen Stelle auf dem Blatt auf das erstellte dynamische Array verweisen müssen, können Sie das Sonderzeichen # („Pfund“) nach der Adresse der führenden Zelle verwenden:

Zum Beispiel können Sie jetzt einfach eine Dropdown-Liste in einer Zelle erstellen, die auf das erstellte dynamische Array verweist:

Dynamische Array-Fehler

Aber was passiert, wenn nicht genügend Platz vorhanden ist, um das Array zu erweitern, oder wenn bereits Zellen auf seinem Pfad von anderen Daten belegt sind? Lernen Sie eine grundlegend neue Art von Fehlern in Excel kennen – #ÜBERWEISEN! (#VERSCHÜTTEN!):

Wenn wir wie immer auf das Symbol mit einer gelben Raute und einem Ausrufezeichen klicken, erhalten wir eine genauere Erklärung der Problemquelle und können störende Zellen schnell finden:

Ähnliche Fehler treten auf, wenn das Array das Blatt verlässt oder auf eine verbundene Zelle trifft. Wenn Sie das Hindernis entfernen, wird alles sofort im laufenden Betrieb korrigiert.

Dynamische Arrays und intelligente Tabellen

Wenn das dynamische Array auf eine „intelligente“ Tabelle zeigt, die durch eine Tastenkombination erstellt wurde Ctrl+T oder von Startseite – Als Tabelle formatieren (Home — Als Tabelle formatieren), dann erbt es auch seine Haupteigenschaft – Auto-Sizing.

Wenn Sie neue Daten unten oder rechts hinzufügen, werden die intelligente Tabelle und der Dynamikbereich ebenfalls automatisch gedehnt:

Es gibt jedoch eine Einschränkung: Wir können keine dynamische Bereichsreferenz in Foren innerhalb einer intelligenten Tabelle verwenden:

Dynamische Arrays und andere Excel-Funktionen

Okay, sagst du. All dies ist interessant und lustig. Keine Notwendigkeit, wie zuvor, die Formel mit einem Verweis auf die erste Zelle des ursprünglichen Bereichs nach unten und nach rechts und all das manuell zu strecken. Und das ist alles?

Nicht ganz.

Dynamische Arrays sind nicht nur ein weiteres Tool in Excel. Jetzt sind sie in das Herz (oder Gehirn) von Microsoft Excel eingebettet – seine Berechnungsmaschine. Damit unterstützen nun auch andere uns bekannte Excel-Formeln und -Funktionen das Arbeiten mit dynamischen Arrays. Werfen wir einen Blick auf einige Beispiele, um Ihnen eine Vorstellung von der Tiefe der Veränderungen zu geben, die stattgefunden haben.

Transponieren

Um einen Bereich zu transponieren (Zeilen und Spalten vertauschen) hatte Microsoft Excel schon immer eine eingebaute Funktion TRANSP (TRANSPOSIEREN). Um es zu verwenden, müssen Sie jedoch zuerst den Bereich für die Ergebnisse richtig auswählen (wenn die Eingabe beispielsweise ein Bereich von 5 × 3 war, müssen Sie 3 × 5 ausgewählt haben), dann die Funktion eingeben und die Taste drücken Kombination Ctrl+Shift+Enter, da es nur im Matrixformelmodus funktionieren konnte.

Jetzt können Sie einfach eine Zelle auswählen, dieselbe Formel eingeben und auf die Normale klicken Enter – dynamisches Array erledigt alles von selbst:

Multiplikationstabelle

Dies ist das Beispiel, das ich früher gegeben habe, als ich gebeten wurde, die Vorteile von Matrixformeln in Excel zu visualisieren. Um nun die gesamte pythagoräische Tafel zu berechnen, reicht es aus, in die erste Zelle B2 zu stehen, dort eine Formel einzugeben, die zwei Reihen (vertikaler und horizontaler Zahlensatz 1..10) multipliziert, und einfach auf zu klicken Enter:

Verleimung und Gehäusekonvertierung

Arrays können nicht nur multipliziert, sondern auch mit dem Standardoperator & (kaufmännisches Und) verklebt werden. Angenommen, wir müssen den Vor- und Nachnamen aus zwei Spalten extrahieren und den springenden Fall in den Originaldaten korrigieren. Wir tun dies mit einer kurzen Formel, die das gesamte Array bildet, und wenden dann die Funktion darauf an PROPNACH (RICHTIG)Um die Kasse aufzuräumen:

Fazit Top 3

Angenommen, wir haben eine Reihe von Zahlen, aus denen wir die drei besten Ergebnisse ableiten möchten, indem wir sie in absteigender Reihenfolge anordnen. Jetzt geschieht dies mit einer Formel und wieder ohne Ctrl+Shift+Enter wie früher:

Wenn Sie möchten, dass die Ergebnisse nicht in einer Spalte, sondern in einer Zeile stehen, dann reicht es aus, in dieser Formel den Doppelpunkt (Zeilentrenner) durch ein Semikolon (Elementtrenner innerhalb einer Zeile) zu ersetzen. In der englischen Version von Excel sind diese Trennzeichen Semikolons bzw. Kommas.

SVERWEIS, das mehrere Spalten gleichzeitig extrahiert

Funktionen VPR (SVERWEIS) Jetzt können Sie Werte nicht aus einer, sondern aus mehreren Spalten auf einmal ziehen – geben Sie einfach ihre Nummern (in beliebiger Reihenfolge) als Array im dritten Argument der Funktion an:

OFFSET-Funktion, die ein dynamisches Array zurückgibt

Eine der interessantesten und nützlichsten (nach SVERWEIS) Funktionen für die Datenanalyse ist die Funktion VERFÜGUNG (OFFSET), dem ich einmal ein ganzes Kapitel in meinem Buch und einen Artikel hier gewidmet habe. Die Schwierigkeit, diese Funktion zu verstehen und zu beherrschen, bestand schon immer darin, dass sie als Ergebnis ein Array (einen Bereich) von Daten zurückgab, aber wir konnten es nicht sehen, weil Excel immer noch nicht wusste, wie man mit Arrays arbeitet.

Dieses Problem gehört nun der Vergangenheit an. Sehen Sie, wie Sie jetzt mit einer einzigen Formel und einem dynamischen Array, das von OFFSET zurückgegeben wird, alle Zeilen für ein bestimmtes Produkt aus jeder sortierten Tabelle extrahieren können:

Schauen wir uns ihre Argumente an:

A1 – Startzelle (Referenzpunkt)
ПОИСКПОЗ(F2;A2:A30;0) – Berechnung der Verschiebung von der Startzelle nach unten – zum ersten gefundenen Kohl.
0 – Verschiebung des „Fensters“ nach rechts relativ zur Ausgangszelle
СЧЁТЕСЛИ(A2:A30;F2) – Berechnung der Höhe des zurückgegebenen „Fensters“ – die Anzahl der Zeilen, in denen es Kohl gibt.
4 — Größe des „Fensters“ horizontal, dh Ausgabe 4 Spalten

Neue Funktionen für dynamische Arrays

Zusätzlich zur Unterstützung des dynamischen Array-Mechanismus in alten Funktionen wurden mehrere völlig neue Funktionen zu Microsoft Excel hinzugefügt, die speziell für die Arbeit mit dynamischen Arrays geschärft wurden. Dies sind insbesondere:

GRADE (SORTIEREN) – sortiert den Eingabebereich und erzeugt ein dynamisches Array für die Ausgabe
SORTPO (SORTIEREN NACH) – kann einen Bereich nach Werten aus einem anderen sortieren
FILTER (FILTER) – Ruft Zeilen aus dem Quellbereich ab, die die angegebenen Bedingungen erfüllen
UNIK (EINZIGARTIG) – extrahiert eindeutige Werte aus einem Bereich oder entfernt Duplikate
SLMASSIV (RANDARRAY) – generiert ein Array von Zufallszahlen einer bestimmten Größe
NACHGEBURT (REIHENFOLGE) — bildet ein Array aus einer Folge von Zahlen mit einem bestimmten Schritt

Mehr dazu – etwas später. Sie sind einen separaten Artikel wert (und keinen einzigen) für ein durchdachtes Studium 🙂

Schlussfolgerungen

Wenn Sie alles oben Geschriebene gelesen haben, dann ist Ihnen, glaube ich, bereits das Ausmaß der Veränderungen bewusst, die stattgefunden haben. So viele Dinge in Excel können jetzt einfacher, einfacher und logischer erledigt werden. Ich muss zugeben, dass ich ein wenig schockiert bin, wie viele Artikel jetzt hier, auf dieser Seite und in meinen Büchern korrigiert werden müssen, aber ich bin bereit, dies leichten Herzens zu tun.

Die Ergebnisse zusammenfassend, плюсы dynamischen Arrays können Sie Folgendes schreiben:

Die Kombination kannst du vergessen Ctrl+Shift+Enter. Excel sieht jetzt keinen Unterschied zwischen „normalen Formeln“ und „Array-Formeln“ und behandelt sie gleich.
Über die Funktion SUMMENPRODUKT (SUMMENPRODUKT), die zuvor verwendet wurde, um Matrixformeln ohne einzugeben Ctrl+Shift+Enter Sie können auch vergessen – jetzt ist es ganz einfach SUM и Enter.
Intelligente Tabellen und bekannte Funktionen (SUMME, WENN, VLOOKUP, SUMIFS usw.) unterstützen jetzt auch dynamische Arrays vollständig oder teilweise.
Es besteht Abwärtskompatibilität: Wenn Sie eine Arbeitsmappe mit dynamischen Arrays in einer alten Excel-Version öffnen, werden diese zu Array-Formeln (in geschweiften Klammern) und funktionieren im „alten Stil“ weiter.

Habe irgendeine Nummer gefunden Minuspunkte:

Sie können einzelne Zeilen, Spalten oder Zellen aus einem dynamischen Array nicht löschen, dh es lebt als einzelne Entität.
Sie können ein dynamisches Array nicht auf die übliche Weise durchsortieren Daten – Sortierung (Daten — Sortieren). Dafür gibt es jetzt eine spezielle Funktion. GRADE (SORTIEREN).
Ein dynamischer Bereich kann nicht in eine intelligente Tabelle umgewandelt werden (aber Sie können einen dynamischen Bereich basierend auf einer intelligenten Tabelle erstellen).

Natürlich ist dies nicht das Ende, und ich bin sicher, dass Microsoft diesen Mechanismus in Zukunft weiter verbessern wird.

Wo kann ich herunterladen?

Und zum Schluss die Hauptfrage 🙂

Microsoft hat erstmals im September 2018 auf einer Konferenz eine Vorschau auf dynamische Arrays in Excel angekündigt und gezeigt Ignite. In den nächsten Monaten gab es zunächst ein gründliches Testen und Einspielen neuer Features ~~Katzen~~ Mitarbeiter von Microsoft selbst, dann auf freiwillige Tester aus dem Kreis der Office Insider. In diesem Jahr wurde das Update, das dynamische Arrays hinzufügt, schrittweise für reguläre Office 365-Abonnenten eingeführt. Ich habe es zum Beispiel erst im August mit meinem Office 365 Pro Plus (Monthly Targeted)-Abonnement erhalten.

Wenn Ihr Excel noch keine dynamischen Arrays hat, Sie aber unbedingt damit arbeiten möchten, dann gibt es folgende Möglichkeiten:

Wenn Sie ein Office 365-Abonnement haben, können Sie einfach warten, bis dieses Update Sie erreicht. Wie schnell dies geschieht, hängt davon ab, wie oft Updates an Ihr Büro geliefert werden (einmal im Jahr, einmal alle sechs Monate, einmal im Monat). Wenn Sie einen Firmen-PC haben, können Sie Ihren Administrator bitten, Aktualisierungen so einzurichten, dass sie häufiger heruntergeladen werden.
Sie können sich in die Riege dieser Test-Freiwilligen von Office Insiders einreihen – dann erhalten Sie als Erster alle neuen Features und Funktionen (aber es besteht natürlich die Chance auf erhöhten Buggy in Excel).
Wenn Sie kein Abonnement, sondern eine Box-Standalone-Version von Excel haben, müssen Sie mindestens bis zur Veröffentlichung der nächsten Version von Office und Excel im Jahr 2022 warten. Benutzer solcher Versionen erhalten nur Sicherheitsupdates und Fehlerbehebungen, und alle neuen „Goodies“ gehen jetzt nur noch an Office 365-Abonnenten. Traurig aber wahr 🙂

Auf jeden Fall, wenn dynamische Arrays in Ihrem Excel auftauchen – nach diesem Artikel sind Sie bereit dafür 🙂

Was sind Matrixformeln und wie werden sie in Excel verwendet?
Fenster-(Bereichs-)Summierung unter Verwendung der OFFSET-Funktion
3 Möglichkeiten, eine Tabelle in Excel zu transponieren