Ein Blick in die Zukunft: Dynamische Arrayfunktionen 19

Artikelbild-231
Der Umgang mit Arrays wird sich in Excel grundlegend ändern. Hier gibt es einen kleinen Ausblick.
 

Der heutige Artikel fällt etwas aus der Reihe, denn ich werde dir Dinge in Excel zeigen, die noch nicht sehr viele Menschen gesehen haben! Ich möchte mit dir einen Blick in die Excel-Zukunft werfen, wo vieles von dem, was wir bisher von Tabellenfunktionen gekannt haben, auf den Kopf gestellt wird.

Bist du neugierig geworden? Gut so! Dann lehn‘ dich entspannt zurück und freue dich auf die Lektüre.

Ach ja, noch ein kleiner Hinweis für die ganz Misstrauischen unter uns: Nein, auch wenn es das Datum vielleicht vermuten lässt, ist in diesem Artikel KEIN Aprilscherz versteckt.

Und los geht’s:

Ein Wort der Warnung

Die Funktionen, die ich in diesem (und einem der nächsten) Artikel vorstellen werde, sind bisher noch in keiner regulären Excel-Version enthalten. Du wirst also mit extrem hoher Wahrscheinlichkeit die Beispiel nicht selbst nachstellen können und daher nur auf die Rolle des Zuschauers beschränkt sein.

Die im folgenden gezeigten dynamischen Arrayfunktionen gibt es weder in Excel 2016 noch im neuesten Excel 2019. Sie sind nur in Office 365 verfügbar und dort zumindest im Moment auch nur dann, wenn man am sogenannten Office-Insider-Programm von Microsoft teilnimmt. Dort erhält man einen Vorab-Einblick auf Funktionen, die in absehbarer Zeit der „normalen“ Office-365-Öffentlichkeit zur Verfügung gestellt werden.

Array-Funktionen komplett neu gedacht

Microsoft wirft 7 neue Array-Funktionen in den Ring, von denen ich dir heute 3 vorstellen werden. Gleichzeitig wird sich die Art und Weise ändern, wie man mit Formeln und Funktionen auf Tabellenbereiche zugreift. Und damit fange ich gleich mal an:

Excel-Formeln, die mehrere Ergebnisse zurückliefern (sogenannte Arrays), werden zukünftig in benachbarte Zellen „überlaufen“ (englisch: spill). Wie kann man sich das vorstellen?

Wenn man bisher in einer Zelle Bezug genommen hat auf einen ganzen Zellbereich, gab es einen #WERT-Fehler:

Bisheriges Ergebnis bei Array-Bezügen

Bisheriges Ergebnis bei Array-Bezügen


Ist ja auch irgendwie klar, denn es werden ja mehrere Werte zurückgeliefert, die nicht alle in eine Zelle passen.

Zukünftig wird das jedoch so aussehen:

Der zukünftige Umgang mit Array-Bezügen

Der zukünftige Umgang mit Array-Bezügen

Excel lässt die Werte einfach in die benachbarten Zellen „überlaufen“ und nimmt dabei automatisch soviel Platz in Anspruch, wie eben benötigt wird. Bisher musste man dazu den Zielbereich komplett markieren und die Formel umständlich mit Strg+Umschalt+Eingabe abschließen, um per Array-Formel das gleiche Ergebnis zu erzielen!
Trotzdem wird in jeder Zelle die gleiche Formel angezeigt. Solange sich die aktive Zelle innerhalb des Arrays befindet, wird um den ganzen Bereich herum ein blauer Rahmen angezeigt, so dass man auf einen Blick erkennt, das es sich tatsächlich um einen zusammengehörigen Bereich handelt.

In der Bearbeitungszeile sieht man noch einen kleinen Unterschied: Nur in der linken oberen Zelle wird die Formel schwarz angezeigt, in allen anderen Zellen ist sie in einem blasseren Hellgrau:

Nur die erste Zelle ist änderbar

Nur die erste Zelle ist änderbar


Alle anderen Zellen sind unveränderlich

Alle anderen Zellen sind unveränderlich


Wenn man versucht, einen einzelnen Wert innerhalb des Arrays zu entfernen oder zu ändern, wird Excel dies kommentarlos verweigern. Nur in der linken oberen Ecke ist das möglich. Ein Druck auf die Entfernen-Taste löscht das komplette Array, wie man oben in der Animation auch erkennen kann.

Die Funktion SORTIEREN

Das was ich eben beschrieben habe, ist nur die Basis für ein paar völlig neue Tabellenfunktionen. Eine davon heißt SORTIEREN und macht genau das, was der Name sagt. Damit lässt sich eine Tabelle oder ein Bereich daraus supereinfach und ohne irgendwelchen umständlichen Kniffe per Formel sortieren.

Die neue SORTIEREN-Funktion

Die neue SORTIEREN-Funktion

Das ist schon extrem beeindruckend, oder? Und selbst wenn in der Quelltabelle neue Datensätze eingefügt werden oder sich die Werte verändern, nach denen sortiert wurde, passt sich das Ergebnis sofort automatisch an!

Die Funktion FILTER

In eine ähnliche Richtung geht die neue FILTERN-Funktion. Damit lassen sich nun auch dynamisch Daten filtern, ohne jedes mal über das Menü zu gehen oder komplexe Matrixformeln zu entwickeln:

Die neue FILTER-Funktion

Die neue FILTER-Funktion

Die Funktion EINDEUTIG

Diese neue Funktion liefert aus einer Liste entweder nur die eindeutigen Werte, also auch jeden mehrfach vorkommenden Wert nur genau einmal. Oder alternativ nur solche Werte, die nur ein einziges Mal vorkommen.

Die neue EINDEUTIG-Funktion

Die neue EINDEUTIG-Funktion

Wer das bisher mit Formeln lösen wollte, musste ein paar knackige Tricks anwenden.

Es gibt noch ein paar weitere dieser neuen dynamischen Array-Funktionen, aber für heute möchte ich es mit dem Ausblick in die (nahe) Excel-Zukunft belassen. In einem späteren Artikel werde ich auch die anderen Funktionen vorstellen.

Ja, vieles davon war jetzt auch schon möglich. Allerdings wesentlich umständlicher und nicht so dynamisch.
Ja, in den Genuss dieser Funktionen werden in absehbarer Zeit nur Office-365-Abonnenten kommen (es sei denn, du wartest auf die nächste reguläre Excel-Version 2022 oder 2025).
Und ja, damit wird die Kompatibilität und der Datenaustausch mit anderen Anwendern (noch) komplizierter werden.

Trotzdem bin ich sicher, dass diese extrem mächtigen Funktionen schnell ihren Platz und ihre Fans finden werden und sich deren Anwendungsgebiete im Augenblick nur ansatzweise erahnen lassen.

Wie denkst du darüber?

 

Wenn dir der Artikel gefallen hat: Bitte weitersagen!
Das könnte dich auch interessieren:
Und immer daran denken: Excel beißt nicht!

P.S. Die Lösung ist immer einfach. Man muss sie nur finden.
(Alexander Solschenizyn)

P.P.S. Das Problem sitzt meistens vor dem Computer.



Avatar-Foto

Über Martin Weiß

Er ist das Gesicht hinter dem Blog "Der Tabellenexperte". Seit 2013 veröffentlicht er hier Beiträge zu seinem Lieblingsprogramm: Microsoft Excel. Martin Weiß ist zertifizierter Microsoft Excel Expert und verdient sein Geld als selbständiger Excel-Berater, -Entwickler und -Trainer.

Schreibe einen Kommentar zu Gerhard Anzill Antworten abbrechen

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert

19 Gedanken zu “Ein Blick in die Zukunft: Dynamische Arrayfunktionen

  • Avatar-Foto
    Friedrich

    Moin.

    Ein spannender Einblick. Danke dafür!

    PS:

    Gibt es einen guten Grund für das Anführungszeichen in „lehn‘“, obwohl ein Apostroph zu sätzen wäre?
    Liegt es am verwendeten Programm, dass von sich den Wert &#8216 ausgewählt hat?

    Gruß

    Friedrich

    • Avatar-Foto
      Martin Weiß

      Hallo Friedrich,

      das sollte auch ein Apostroph sein, es liegt tatsächlich an WordPress. Hier gibt es leider immer wieder mal Schwierigkeiten mit der Darstellung von Apostrophen und Anführungszeichen.

      Schöne Grüße,
      Martin

  • Avatar-Foto
    Axel

    Die neue Filterfunktion ist klasse! Sie ist eine konsequente Weiterführung der index/vergleich-Kombination, die man – natürlich – als separate Funktion entwickeln musste.

  • Avatar-Foto
    Martin Maul

    Hallo Martin.
    Super Ausblick, habe mich schon immer gefragt, wann der lästige Abschluss der Array-Formeln endlich wegfällt. Hatte diese daher meist gemieden. Auch O365 stehe ich noch sehr skeptisch gegenüber, aber mit solchen Formeln in der Zukunft… da werde ich dann wohl auch zum Abonnenten werden (müssen).
    Danke, es Grüßt Dein Namens-Vetter
    Martin

    • Avatar-Foto
      Martin Weiß

      Hallo Martin,

      ja, O365 wird von manchen geliebt und von anderen gehasst. Aber es ist ganz offensichtlich, dass Microsoft langfristig alles daran setzen wird, die Anwender auf Office 365 zu bringen. Denn damit wird schließlich auch das Geld verdient…

      Schöne Grüße,
      Martin

  • Avatar-Foto
    Andreas Unkelbach

    Guten Morgen Martin,

    auch für mich ist der Ausblick auf die Funktion FILTER mit viel Hoffnung versehen. Derzeit filtere ich eine Tabelle und füge diese per Inhalte einfügen -> Verknüpfung ein, aber per Filter ist dieses wesentlich interssanter (insbesondere wenn mehr als ein Filter verwendet werden kann. Ist dir zufällig bekannt, ob beim Office Insider Programm dieses für das gesamte Microsoft Konto oder für die lokale Installation altivierbar ist?

    Unabhängig davon aber vielen Dank für den Blick in die Zukunft.

    Viele Grüße
    Andreas

    • Avatar-Foto
      Martin Weiß

      Hallo Andreas,

      soweit ich weiß, bezieht sich das Office Insider Programm nur auf die jeweilige lokale Installation, auf der sie aktiviert wurde. Also nicht auf das gesamte Microsoft Konto. Aber das ist ohne Gewähr, bitte nagle mich nicht darauf fest 🙂

      Schöne Grüße,
      Martin

  • Avatar-Foto
    Gerhard Anzill

    Wow – die neuen Funktionen sind sehr hilfreich – die Frage die sich mir noch stellt ist wie können die neuen Funktionen auch so einfach mit VBA angewandt werden.

    • Avatar-Foto
      Martin Weiß

      Hallo Gerhard,

      ja, VBA wird in dem Zusammenhang sicherlich ein interessantes Thema sein.

      Schöne Grüße,
      Martin

  • Avatar-Foto
    Dirk Martens

    Spannedes Thema, danke! Ich suche schon seit langem nach einer Lösung für folgendes Problem:
    Ich eine Liste mit Namen von Studenten in einer Spalte, in einer anderen Spalte steht eine Ziffer, die für ein Referatsthema steht, für das sie sich entschieden haben, pro Student genau ein Thema. In einer weiteren Liste steht die Referatsnummer und das Thema. Dahinter sollen nun die Namen der Studenten (0..n) erscheinen, die das betreffende Thema ausgewählt haben, idealerweise aber nicht über mehrere Zellen überlaufend, sondern innerhalb einer Zelle. Beispiel:
    Nr. Thema Referent(en)
    1 (Beschreibung) Klaus, Liese, Erwin
    2 (Beschreibung) Susi, Edith
    3 (Beschreibung) Karl-Heinz

    Sollte ich damit auf die dynamischen Funktionen warten, wenn es mit denen überhaupt geht, oder ist das doch eher ein Fall für VBA?
    Über einen kurzen Ratschlag würde ich mich sehr freuen!

    • Avatar-Foto
      Martin Weiß

      Hallo Dirk,

      wenn du bereits Office 365 nutzt (oder Excel 2019), dann geht es auch mit der TEXTVERKETTEN-Funktion. Man muss sie allerdings als Array-Funktion eingeben. Das heißt, mit Strg+Umschalt+Eingabe abschließen.
      Textverketten

      Schöne Grüße,
      Martin

      • Avatar-Foto
        Dirk Martens

        Hallo Martin,
        ich bin vor Erfurcht erstarrt und bin begeistert, dass es am Ende eine so knappe Formel ist!
        Vielen Dank sagt ein fröhlicher Excel-Nutzer!

  • Avatar-Foto
    Rainer Stahl

    Interessante Ausblicke. Es stört mich allerdings etwas, dass ich den Eindruck gewann, dass bewährte Konzepte damit etwas durcheinander geraten, z.B. absolute und relative Verweise, etc. Von daher bin ich gespannt, inwieweit Excel damit konzeptionell „aus einem Guß“ bleibt.
    Weiterhin die Fragen:
    * wird eine Mappe, in der diese Funktionen verwendet werden, zur xlsm- oder xlsb-Datei, oder ist sie nach wie vor als .xlsx-Datei speicherbar?
    * wie sind solche Dateien auf anderen Plattformen (Mac, Android,…) nutzbar?
    * kann ich sowas dann auch in der Online-Version von Excel nutzen?
    und, zuletzt etwas off topic: was passiert mit den XL4-Macrofunktionen (Zelle.zuordnen, etc.)? Die bieten immerhin nach wie vor Möglichkeiten, die mit den ‚Standard‘-Funktionen nicht oder nur umständlich erreichbar sind.
    Gruß,
    Rainer

    • Avatar-Foto
      Martin Weiß

      Hallo Rainer,

      ja, es muss sich noch zeigen, wohin sich Excel langfristig entwickeln wird. Was absolute und relative Bezüge angeht: Die werden sicher auf absehbare Zeit noch erhalten bleiben. Denn die neuen Array-Formeln sind ja nur ein kleiner, wenn auch sehr interessanter Aspekt in der gesamten Funktionalität. Und es gibt ja noch extrem viele andere Tabellenfunktionen, die mit diesen Bezügen arbeiten.
      Im Mappenformat gibt es mit diesen Array-Funktionen keine Besonderheiten, Sie sind ganz normal als *.xlsx speicherbar. Und sie funktionieren auch mit der Online-Version in Office 365.

      Wie lange die alten XL4-Makrofunktionen noch erhalten bleiben, weiß nur Microsoft. Im Augenblick funktionieren sie noch und darüber bin ich auch sehr froh 🙂

      Schöne Grüße,
      Martin

  • Avatar-Foto
    Lars Antagon

    Ist es möglich die Filter Funktion innerhalb einer abelle zu nutzen ohne den Fehler #Überlauf zu erhalten?

  • Avatar-Foto
    Oliver Engelke

    Hallo Martin,

    diese Array-Funktionen sind schon nützlich, aber leider kann man damit so manches wohl noch nicht realisieren.
    Als Beispiel die Funktion Eindeutig, klar man bekommt eine Liste angezeigt und man kann auch diese auch fix woanders einfügen mit z.B. A1#, oder nur den ersten Eintrag eben mit A1. Aber wie kann man sich aus dieser Liste nun z.B. nur Nummer 2 oder 5 oder 10 in einer anderen Zelle anzeigen lassen? Gibt es da schon irgendeinen Trick für? Oder ist das von MS derzeit nicht gewollt?

    Schönen Abend.

    • Avatar-Foto
      Martin Weiß

      Hallo Oliver,

      du kannst auf diese Arrays mit jeder anderen Excel-Funktion zugreifen, wie auf jede „normale“ Tabelle oder Liste auch.
      Wenn also beispielsweis das Array in A1 beginnt (und sich in A2, A3 usw. fortsetzt), kannst du so auf den 2. Wert zugreifen:
      =INDEX(A1#;2)

      Schöne Grüße,
      Martin