Richtlinien und Beispiele für Arrayformeln

Richtlinien und Beispiele für Arrayformeln

Wichtig :  Dieser Artikel wurde maschinell übersetzt. Bitte beachten Sie den Haftungsausschluss. Die englische Version des Artikels ist als Referenz hier verfügbar: hier.

Wenn Sie eine Excel-Poweruser angezeigt werden, müssen Sie wissen, wie Sie Matrixformeln, verwenden Sie die Berechnungen ausführen können, die Sie können mithilfe von nicht-Arrayformeln. Im folgende Artikel basiert auf einer Reihe von Excel-Poweruser-Kolumnen von Colin Wilcox geschrieben und aus Kapitel 14 und 15 des Excel 2002-Formeln, einem Buch von John Walkenbach, eine Excel-MVP geschrieben angepasst.

Informationen zu Arrayformeln

Arrayformeln werden häufig auch als CSE-Formeln bezeichnet, da zum Vervollständigen der Formel nicht einfach die Eingabetaste, sondern die Tastenkombination STRG+UMSCHALT+EINGABETASTE (CTRL+SHIFT+ENTER, CSE) gedrückt wird.

Warum sollten Arrayformeln verwendet werden?

Wenn Ihnen der Umgang mit Formeln in Excel vertraut ist, wissen Sie bereits, dass Sie damit einige ziemlich komplexe Operationen ausführen können. So können Sie beispielsweise die Gesamtkosten eines Kredits über eine bestimmte Anzahl von Jahren berechnen. Mit Arrayformeln können Sie z. B. die folgenden komplexen Aufgaben ausführen:

  • Die Anzahl von Zeichen zählen, die in einem Bereich von Zellen enthalten sind.

  • Nur Zahlen addieren, die bestimmte Bedingungen erfüllen, z. B. die niedrigsten Werte in einem Bereich oder Zahlen, die zwischen einem oberen und unteren Grenzwert liegen.

  • Jeden n-ten Wert in einem Wertebereich addieren.

Kurze Einführung in Arrays und Arrayformeln

Bei einer Arrayformel handelt es sich um eine Formel, mit der mehrere Berechnungen für ein oder mehrere Elemente in einem Array ausgeführt werden können. Sie können sich ein Array als eine Zeile von Werten, eine Spalte von Werten oder eine Kombination aus Zeilen und Spalten von Werten vorstellen. Arrayformeln können entweder mehrere Ergebnisse oder ein einzelnes Ergebnis zurückgeben. Sie können beispielsweise eine Arrayformel in einem Zellbereich erstellen und die Formel zum Berechnen einer Spalte oder einer Zeile mit Teilergebnissen verwenden. Sie können eine Arrayformel auch in eine einzelne Zelle einfügen und einen einzelnen Betrag berechnen. Eine Arrayformel, die mehrere Zellen umfasst, wird als Mehrfachzellen-Arrayformel bezeichnet, und eine Arrayformel in einer einzelnen Zelle wird als Einzelzellen-Arrayformel bezeichnet.

Die Beispiele im folgenden Abschnitt zeigen, wie Mehrfachzellen- und Einzelzellen-Arrayformeln erstellt werden.

Probieren Sie es aus!

In dieser Übung wird gezeigt, wie Mehrfachzellen- und Einzelzellen-Arrayformeln zum Berechnen einer Reihe von Umsatzzahlen verwendet werden. In der ersten Gruppe von Schritten wird eine Mehrfachzellenformel zum Berechnen einer Reihe von Teilergebnissen verwendet. In der zweiten Gruppe von Schritten wird eine Einzelzellenformel zum Berechnen einer Gesamtsumme verwendet.

Mehrfachzellen-Arrayformel

Nachfolgend ist eine Arbeitsmappe im Browser eingebettet. Diese enthält zwar Beispieldaten, doch können Sie Arrayformeln in einer eingebetteten Arbeitsmappe weder erstellen noch ändern. Dazu benötigen Sie das Programm Excel. Die Antworten und erläuternder Text zur Funktionsweise der Arrayformel sind angegeben, doch um die Vorteile von Arrayformeln wirklich kennenzulernen, müssen Sie die Arbeitsmappe in Excel anzeigen.

Erstellen einer Mehrfachzellen-Arrayformel
  1. Kopieren Sie die gesamte folgende Tabelle, und fügen Sie sie in Zelle A1 eines leeren Arbeitsblatts in Excel ein.

    Verkäufer
     

    Fahrzeugtyp
     

    Anzahl verkaufter
    Fahrzeuge

    Einzelpreis
     

    Umsätze
    gesamt

    Bott

    Limousine

    5

    33000

    Coupé

    4

    37000

    Inger

    Limousine

    6

    24000

    Coupé

    8

    21000

    Jordan

    Limousine

    3

    29000

    Coupé

    1

    31000

    Probst

    Limousine

    9

    24000

    Coupé

    5

    37000

    Sanchez

    Limousine

    6

    33000

    Coupé

    8

    31000

    Formel (Gesamtsumme)

    Gesamtsumme

    '=SUMME(C2:C11*D2:D11)

    =SUMME(C2:C11*D2:D11)

  2. Um die gesamten Umsätze für Coupés und Limousinen jedes Verkäufers anzuzeigen, markieren Sie E2:E11, geben Sie die Formel =C2:C11*D2:D11 ein, und drücken Sie dann STRG+UMSCHALT+EINGABETASTE.

  3. Um die Gesamtsumme aller Umsätze anzuzeigen, markieren Sie die Zelle F11, geben Sie die Formel =SUMME(C2:C11*D2:D11) ein, und drücken Sie dann STRG+UMSCHALT+EINGABETASTE.

Sie können diese Arbeitsmappe herunterladen, indem Sie auf die grüne Excel-Schaltfläche in der schwarzen Leiste am unteren Rand der Arbeitsmappe klicken. Anschließend können Sie die Datei in Excel öffnen, die Zellen mit den Arrayformeln markieren und STRG+UMSCHALT+EINGABETASTE drücken, um die Formel zum Einsatz zu bringen.

Vergewissern Sie sich beim Arbeiten in Excel, dass Tabelle1 aktiv ist, und markieren Sie dann die Zellen E2:E11. Drücken Sie F2, und geben Sie dann die Formel =C2:C11*D2:D11 in die aktuelle Zelle (E2) ein. Wenn Sie die EINGABETASTE drücken, wird die Formel nur in die Zelle E2 eingetragen, und es wird "165000" angezeigt. Drücken Sie nach dem Eingeben der Formel statt der EINGABETASTE die Tastenkombination STRG+UMSCHALT+EINGABETASTE. Nun werden Ergebnisse in den Zellen E2:E11 angezeigt. Außerdem wird die Formel in der Bearbeitungsleiste als "{=C2:C11*D2:D11}" angezeigt. Dadurch wissen Sie, dass es sich um eine Arrayformel handelt, wie in der folgenden Tabelle dargestellt.

Wenn Sie STRG+UMSCHALT+EINGABETASTE drücken, schließt Excel die Formel in geschweifte Klammern ({ }) ein und fügt eine Instanz der Formel in jede Zelle des ausgewählten Bereichs ein. Dies geschieht sehr schnell, und es wird Ihnen in Spalte E der Gesamtbetrag des Umsatzes für jeden Fahrzeugtyp und jeden Verkäufer angezeigt. Wenn Sie E2 und dann E3, E4 usw. auswählen, können Sie feststellen, dass jeweils dieselbe Formel angezeigt wird: {=C2:C11*D2:D11}.

Die Summen in Spalte E werden mithilfe einer Arrayformel berechnet

Erstellen einer Einzelzellen-Arrayformel

Geben Sie in Zelle F10 der Arbeitsmappe die folgende Formel ein, und drücken Sie dann STRG+UMSCHALT+EINGABETASTE:

=SUMME(C2:C11*D2:D11)

In diesem Fall multipliziert Excel die Werte im Array (Zellbereich C2 bis D11) und verwendet anschließend die Funktion SUMME, um die Ergebnisse zu addieren. Das Gesamtergebnis ist ein Umsatz von 1.590.000. Dieses Beispiel zeigt, wie leistungsfähig diese Art von Formel sein kann. Wenn Sie z. B. über 1.000 Datenzeilen verfügen, können Sie die Daten teilweise oder insgesamt addieren, indem Sie eine Arrayformel in einer einzelnen Zelle erstellen, anstatt die Formel über die 1.000 Zeilen nach unten zu ziehen.

Beachten Sie auch, dass die Einzelzellenformel in Zelle G11 vollkommen unabhängig von der Mehrfachzellenformel (die Formel in den Zellen E2 bis E11) ist. Dies ist ein weiterer Vorteil von Arrayformeln: Flexibilität. Sie können die Formeln in Spalte E ändern oder die Spalte insgesamt löschen, ohne dass dies Auswirkungen auf die Formel in G11 hat.

Arrayformeln bieten darüber hinaus folgende Vorteile:

  • Konsistenz    Wenn Sie auf eine der Zellen unterhalb von Zelle E2 klicken, wird die gleiche Formel angezeigt. Durch diese Konsistenz kann größere Genauigkeit gewährleistet werden.

  • Sicherheit    Eine Komponente einer Mehrfachzellen-Arrayformel kann nicht überschrieben werden. Wenn Sie z. B. auf die Zelle E3 klicken und ENTF drücken, müssen Sie entweder den gesamten Zellbereich (E2 bis E11) auswählen und die Formel für das gesamte Array ändern oder das Array unverändert lassen. Als zusätzliche Sicherheitsmaßnahme müssen Sie STRG+UMSCHALT+EINGABETASTE drücken, um die Änderung der Formel zu bestätigen.

  • Geringere Dateigrößen    Sie können häufig eine einzelne Arrayformel anstelle mehrerer Formeln für Zwischenschritte verwenden. So wird beispielsweise in der Arbeitsmappe eine einzige Arrayformel zum Berechnen der Ergebnisse in Spalte E verwendet. Bei Verwendung von Standardformeln (z. B. =C2*D2;C3*D3;C4*D4…) hätten Sie 11 verschiedene Formeln zur Berechnung der gleichen Ergebnisse verwendet.

Syntax von Arrayformeln

Allgemein verwenden Arrayformeln die standardmäßige Formelsyntax. Die Formeln beginnen alle mit einem Gleichheitszeichen (=), und Sie können die meisten der integrierten Excel-Funktionen in Arrayformeln verwenden. Der Hauptunterschied besteht darin, dass Sie bei Verwendung einer Arrayformel die Tastenkombination STRG+UMSCHALT+EINGABETASTE drücken, um die Formel einzugeben. Wenn dies geschieht, schließt Excel die Arrayformel in geschweifte Klammern ein. Wenn Sie die Klammern manuell eingeben, wird die Formel in eine Textzeichenfolge umgewandelt und funktioniert nicht.

Arrayfunktionen stellen eine wirklich effiziente Möglichkeit zum Erstellen einer komplexen Formel dar. Die Arrayformel =SUMME(C2:C11*D2:D11) entspricht Folgendem: =SUMME(C2*D2;C3*D3;C4*D4;C5*D5;C6*D6;C7*D7;C8*D8;C9*D9;C10*D10;C11*D11).

Eingeben und Ändern von Arrayformeln

Wichtig    Drücken Sie immer STRG+UMSCHALT+EINGABETASTE, wenn Sie eine Arrayformel eingeben oder bearbeiten möchten. Dies gilt sowohl für Einzelzellen- als auch für Mehrfachzellenformeln.

Beim Arbeiten mit Mehrfachzellenformeln ist außerdem Folgendes zu beachten:

  • Wählen Sie den Zellbereich für die Ergebnisse aus, bevor Sie die Formel eingeben. Dies war beim Erstellen der Mehrfachzellen-Arrayformel der Fall, als Sie die Zellen E2 bis E11 markierten.

  • Sie können den Inhalt einer einzelnen Zelle in einer Arrayformel nicht ändern. Probieren Sie dies einmal aus, indem Sie die Zelle E3 in der Arbeitsmappe markieren und ENTF drücken. Es wird eine Meldung in Excel angezeigt, die Ihnen mitteilt, dass Teile eines Arrays nicht geändert werden können .

  • Sie können eine gesamte Arrayformel, jedoch nicht einen Teil der Formel verschieben oder löschen. Mit anderen Worten: Zum Verkleinern einer Arrayformel löschen Sie zunächst die vorhandene Formel, und beginnen Sie dann erneut.

  • Zum Löschen einer Arrayformel markieren Sie die gesamte Formel (z. B. =C2:C11*D2:D11), drücken Sie ENTF, und drücken Sie dann STRG+UMSCHALT+EINGABETASTE.

  • Sie können bei Mehrfachzellen-Arrayformeln keine leeren Zellen einfügen oder Zellen aus der Formel löschen.

Erweitern einer Arrayformel

Manchmal kann es der Fall sein, dass Sie eine Arrayformel erweitern müssen. Das Verfahren ist nicht kompliziert, doch beachten Sie die oben genannten Anweisungen.

Bei diesem Arbeitsblatt wurden einige Zeilen mit Umsatzwerten, die Zeilen 12 bis 17, hinzugefügt. Nun sollen die Arrayformeln so geändert werden, dass diese zusätzlichen Zeilen einbezogen sind.

Diese Schritte müssen im Excel-Desktopprogramm ausgeführt werden (nachdem Sie die Arbeitsmappe auf Ihren Computer heruntergeladen haben).

Erweitern einer Matrixformel
  1. Kopieren Sie die gesamte folgende Tabelle, und fügen Sie die Tabelle in Zelle A1 eines Excel-Arbeitsblatts ein.

    Verkäufer
     

    Fahrzeugtyp
     

    Anzahl verkaufter
    Fahrzeuge

    Einzelpreis
     

    Umsätze
    gesamt

    Bott

    Limousine

    5

    33000

    165000

    Coupé

    4

    37000

    148000

    Inger

    Limousine

    6

    24000

    144000

    Coupé

    8

    21000

    168000

    Jordan

    Limousine

    3

    29000

    87000

    Coupé

    1

    31000

    31000

    Probst

    Limousine

    9

    24000

    216000

    Coupé

    5

    37000

    185000

    Sanchez

    Limousine

    6

    33000

    198000

    Coupé

    8

    31000

    248000

    Torner

    Limousine

    2

    27000

    Coupé

    3

    30000

    Wang

    Limousine

    4

    22000

    Coupé

    1

    41000

    Young

    Limousine

    5

    32000

    Coupé

    3

    36000

    Gesamtsumme

  2. Markieren Sie Zelle E18, geben Sie die Formel für die Gesamtsumme =SUMME(C2:C17*D2:D17) in Zelle A20 ein, und drücken Sie STRG+UMSCHALT+EINGABETASTE.
    Das Ergebnis sollte 2.131.000 lauten.

  3. Markieren Sie den Zellbereich, der die aktuelle Arrayformel enthält (E2:E11), sowie die leeren Zellen (E12:E17), die sich neben den neuen Daten befinden. Markieren Sie also die Zellen E2:E17.

  4. Drücken Sie F2, um in den Bearbeitungsmodus zu wechseln.

  5. Ändern Sie in der Bearbeitungsleiste C11 in C17 und D11 in D17, und drücken Sie dann STRG+UMSCHALT+EINGABETASTE.
    Excel aktualisiert die Formel in den Zellen E2 bis E11 und fügt eine Instanz der Formel in die neuen Zellen E12 bis E17 ein.

  6. Geben Sie die Arrayformel =SUMME(C2:C17*D2*D17) in Zelle F17 ein, damit sie auf die Zellen von Zeile 2 bis Zeile 17 verweist, und drücken Sie STRG+UMSCHALT+EINGABETASTE, um die Arrayformel einzugeben.
    Die neue Gesamtsumme sollte nun 2.131.000 lauten.

Nachteile der Verwendung von Arrayformeln

Arrayformeln sind großartig, doch können sie auch einige Nachteile aufweisen:

  • Sie können gelegentlich vergessen, STRG+UMSCHALT+EINGABETASTE zu drücken. Das kann auch dem erfahrensten Excel-Benutzer passieren. Denken Sie beim Eingeben oder Bearbeiten einer Arrayformel immer daran, diese Tastenkombination zu drücken.

  • Andere Benutzer der Arbeitsmappe verstehen Ihre Formeln möglicherweise nicht. Im Allgemeinen werden Arrayformeln in einem Arbeitsblatt nicht erläutert. Daher sollten diese Formeln in dem Fall, dass andere Benutzer Ihre Arbeitsmappen ändern müssen, entweder vermieden werden, oder es sollte sichergestellt sein, dass diese Benutzer über Arrayformeln informiert sind und wissen, wie diese Formeln bei Bedarf geändert werden.

  • Je nach Verarbeitungsgeschwindigkeit und Speicherkapazität des Computers können große Arrayformeln Berechnungen verlangsamen.

Seitenanfang

Informationen zu Arraykonstanten

Arraykonstanten sind eine Komponente von Arrayformeln. Sie erstellen Arraykonstanten, indem Sie eine Liste von Elementen eingeben und diese dann manuell in geschweifte Klammern ({ }) einschließen. Beispiel:

={1.2.3.4.5}

Sie wissen bereits, dass Sie beim Erstellen von Arrayformeln STRG+UMSCHALT+EINGABETASTE drücken müssen. Da Arraykonstanten eine Komponente von Arrayformeln sind, schließen Sie die Konstanten durch manuelle Eingabe von geschweiften Klammern ein. Dann verwenden Sie STRG+UMSCHALT+EINGABETASTE, um die gesamte Formel einzugeben.

Wenn Sie die Elemente durch Kommas trennen, erstellen Sie ein horizontales Array (eine Zeile). Wenn Sie die Elemente durch Semikolons trennen, erstellen Sie ein vertikales Array (eine Spalte). Zum Erstellen eines zweidimensionalen Arrays trennen Sie die Elemente in jeder Zeile durch Kommas und jede Zeile durch Semikolons.

Ein Array in einer einzelnen Zeile lautet: {1.2.3.4}. Ein Array in einer einzelnen Spalte lautet: {1;2;3;4}. Ein Array mit zwei Zeilen und vier Spalten lautet: {1.2.3.4;5.6.7.8}. Bei dem Array mit zwei Zeilen umfasst die erste Zeile 1, 2, 3 und 4, und die zweite Zeile umfasst 5, 6, 7 und 8. Die beiden Zeilen werden durch ein einzelnes Semikolon zwischen 4 und 5 getrennt.

Wie Arrayformeln können Sie auch Arraykonstanten für die meisten der integrierten Funktionen in Excel verwenden. In den folgenden Abschnitten wird beschrieben, wie die einzelnen Arten von Konstanten erstellt und für Funktionen in Excel verwendet werden.

Seitenanfang

Erstellen ein- und zweidimensionaler Konstanten

Die folgenden Verfahren dienen als Übung zum Erstellen horizontaler, vertikaler und zweidimensionaler Konstanten.

Erstellen einer horizontalen Konstante

  1. Verwenden Sie die Arbeitsmappe aus den vorherigen Beispielen, oder erstellen Sie eine neue Arbeitsmappe.

  2. Markieren Sie die Zellen A1 bis E1.

  3. Geben Sie in der Bearbeitungsleiste die folgende Formel ein, und drücken Sie dann STRG+UMSCHALT+EINGABETASTE:

    ={1.2.3.4.5}

    In diesem Fall müssen Sie die geschweiften Klammern({ }) eingeben.

    Das folgende Ergebnis wird angezeigt.

    Horizontale Arraykonstante in einer Formel

Erstellen einer vertikalen Konstante

  1. Markieren Sie in der Arbeitsmappe eine Spalte mit fünf Zellen.

  2. Geben Sie die folgende Formel in die Bearbeitungsleiste ein, und drücken Sie dann STRG+UMSCHALT+EINGABETASTE:

    ={1;2;3;4;5}

    Das folgende Ergebnis wird angezeigt.

    Vertikale Arraykonstante in einer Arrayformel

Erstellen einer zweidimensionalen Konstante

  1. Markieren Sie in Ihrer Arbeitsmappe einen Block von Zellen mit einer Breite von vier Spalten und einer Höhe von drei Zeilen.

  2. Geben Sie in der Bearbeitungsleiste die folgende Formel ein, und drücken Sie dann STRG+UMSCHALT+EINGABETASTE:

    ={1.2.3.4;5.6.7.8;9.10.11.12}

    Das folgende Ergebnis wird angezeigt:

    Zweidimensionale Arraykonstante in einer Arrayformel

Verwenden von Konstanten in Formeln

Es folgt ein einfaches Beispiel, bei dem Konstanten verwendet werden:

  1. Erstellen Sie ein neues Arbeitsblatt in der Beispielarbeitsmappe.

  2. Geben Sie in Zelle A1 den Wert 3, dann in B1 den Wert 4, in C1 den Wert 5, in D1 den Wert 6 und in E1 den Wert 7 ein.

  3. Geben Sie in Zelle A3 die folgende Formel ein, und drücken Sie dann STRG+UMSCHALT+EINGABETASTE:

    =SUMME(A1:E1*{1.2.3.4.5})

    Beachten Sie, dass Excel die Konstante in einen weiteren Satz geschweifter Klammern einschließt, da Sie diese als eine Arrayformel eingegeben haben.

    Arrayformel mit Arraykonstante

    In der Zelle A3 wird der Wert 85 angezeigt.

Im nächsten Abschnitt wird die Funktionsweise der Formel erläutert.

Syntax von Arraykonstanten

Die gerade verwendete Formel umfasst mehrere Teile.

Syntax einer Arrayformel mit Arraykonstante

1. Funktion

2. Gespeichertes Array

3. Operator

4. Arraykonstante

Bei dem letzten Element innerhalb der Klammer handelt es sich um die Arraykonstante {1.2.3.4.5}. Wie Sie wissen, schließt Excel Arraykonstanten nicht in geschweifte Klammern ein, sondern sie werden von Ihnen eingegeben. Denken Sie auch daran, dass Sie nach dem Hinzufügen einer Konstante zu einer Arrayformal STRG+UMSCHALT+EINGABETASTE drücken, um die Formel einzugeben.

Da Excel zuerst Operationen für Ausdrücke ausführt, die in Klammern eingeschlossen sind, handelt es sich bei den nächsten beiden Elementen, die ins Spiel kommen, um die in der Arbeitsmappe gespeicherten Werte (A1:E1) und den Operator. An diesem Punkt multipliziert die Formel die Werte im gespeicherten Array mit den entsprechenden Werten in der Konstante. Dies entspricht Folgendem:

=SUMME(A1*1;B1*2;C1*3;D1*4;E1*5)

Schließlich werden mit der Funktion SUMME die Werte addiert, und die Summe 85 wird in Zelle A3 angezeigt.

Um die Verwendung des gespeicherten Arrays zu vermeiden und die Operation vollständig im Speicher zu belassen, ersetzen Sie das gespeicherte Array durch eine andere Arraykonstante:

=SUMME({3.4.5.6.7}*{1.2.3.4.5})

Zum Ausprobieren kopieren Sie die Funktion, markieren Sie eine leere Zelle in Ihrer Arbeitsmappe, fügen Sie die Formel in die Bearbeitungsleiste ein, und drücken Sie dann STRG+UMSCHALT+EINGABETASTE. Es wird dasselbe Ergebnis wie in der vorherigen Übung angezeigt, in der die folgende Arrayformel verwendet wurde:

=SUMME(A1:E1*{1.2.3.4.5})

Elemente, die in Konstanten verwendet werden können

Arraykonstanten können Zahlen, Text, logische Werte (z. B. WAHR und FALSCH) und Fehlerwerte (z. B. #NV) enthalten. Bei Zahlen kann es sich um ganze Zahlen, Dezimalzahlen und Zahlen in wissenschaftlichen Formaten handeln. Wenn Sie Text einfügen, müssen Sie diesen in Anführungszeichen (") einschließen.

Arraykonstanten können keine zusätzlichen Arrays, Formeln oder Funktionen enthalten. Sie können also nur Text oder Zahlen enthalten, die durch Kommas oder Semikolons getrennt sind. Excel zeigt eine Warnmeldung an, wenn eine Formel wie "{1.2.A1:D4}" oder "{1.2.SUMME(Q2:Z8)}" eingegeben wird. Zudem können numerische Werte keine Prozentzeichen, Dollarzeichen, Kommas oder Klammern enthalten.

Benennen von Arraykonstanten

Eine der besten Möglichkeiten, Arraykonstanten zu verwenden, ist deren Benennung. Benannte Konstanten können wesentlich einfacher verwendet werden, und mit ihnen kann ein Teil der Komplexität von Arrayformeln für andere Benutzer verborgen bleiben. Um eine Arraykonstante zu benennen und in einer Formel zu verwenden, gehen Sie folgendermaßen vor:

  1. Klicken Sie auf der Registerkarte Formeln in der Gruppe Definierte Namen auf Namen definieren.
    Das Dialogfeld Namen definieren wird angezeigt.

  2. Geben Sie im Feld Name die Zeichenfolge Quartal1 ein.

  3. Geben Sie im Feld Bezieht sich auf die folgende Konstante ein (denken Sie daran, die geschweiften Klammern manuell einzugeben):

    ={"Januar"."Februar"."März"}

    Der Inhalt des Dialogfelds sieht jetzt folgendermaßen aus:

    Dialogfeld 'Name bearbeiten' mit Formel

  4. Klicken Sie auf OK, und markieren Sie dann eine Zeile mit drei leeren Zellen.

  5. Geben Sie die folgende Formel ein, und drücken Sie dann STRG+UMSCHALT+EINGABETASTE:

    =Quartal1

    Das folgende Ergebnis wird angezeigt.

    Benanntes Array als Formel eingegeben

Wenn Sie eine benannte Konstante als eine Arrayformel verwenden, denken Sie daran, das Gleichheitszeichen einzugeben. Wenn dies nicht geschieht, interpretiert Excel das Array als eine Textzeichenfolge, und die Formel funktioniert nicht wie erwartet. Sie sollten auch beachten, dass Sie Kombinationen von Text und Zahlen verwenden können.

Problembehandlung bei Arraykonstanten

Sollten die Arraykonstanten nicht funktionieren, prüfen Sie diese auf folgende Probleme:

  • Möglicherweise sind einige Elemente nicht durch das richtige Zeichen getrennt. Wenn Sie ein Komma oder Semikolon auslassen oder an der falschen Stelle einfügen, wird die Arraykonstante möglicherweise nicht richtig erstellt oder eine Warnmeldung angezeigt.

  • Möglicherweise haben Sie einen Zellbereich ausgewählt, der nicht der Anzahl der Elemente in Ihrer Konstante entspricht. Wenn Sie z. B. eine Spalte mit sechs Zellen zur Verwendung für eine fünfzellige Konstante ausgewählt haben, wird der Fehlerwert "#NV" in der leeren Zelle angezeigt. Wenn Sie hingegen zu wenige Zellen auswählen, lässt Excel die Werte aus, für die keine entsprechende Zelle vorhanden ist.

Arraykonstanten in Aktion

In den folgenden Beispielen sind einige Möglichkeiten dargestellt, wie Sie Arraykonstanten in Arrayformeln einsetzen können. Bei einigen Beispielen wird die Funktion MTRANS zum Konvertieren von Zeilen in Spalten und umgekehrt verwendet.

Multiplizieren jedes Elements in einem Array

  1. Erstellen Sie ein neues Arbeitsblatt, und markieren Sie dann einen Block leerer Zellen mit einer Breite von vier Spalten und einer Höhe von drei Zeilen.

  2. Geben Sie die folgende Formel ein, und drücken Sie dann STRG+UMSCHALT+EINGABETASTE:

    ={1.2.3.4;5.6.7.8;9.10.11.12}*2

Quadrieren der Elemente in einem Array

  1. Markieren Sie einen Block leerer Zellen mit einer Breite von vier Spalten und einer Höhe von drei Zeilen.

  2. Geben Sie die folgende Arrayformel ein, und drücken Sie dann STRG+UMSCHALT+EINGABETASTE:

    ={1.2.3.4;5.6.7.8;9.10.11.12}*{1.2.3.4;5.6.7.8;9.10.11.12}

    Alternativ können Sie diese Arrayformel eingeben, bei der das Caretzeichen (^) als Operator verwendet wird:

    ={1.2.3.4;5.6.7.8;9.10.11.12}^2

Transponieren einer eindimensionalen Zeile

  1. Markieren Sie eine Spalte mit fünf leeren Zellen.

  2. Geben Sie die folgende Formel ein, und drücken Sie dann STRG+UMSCHALT+EINGABETASTE:

    =MTRANS({1.2.3.4.5})

Obwohl Sie eine horizontale Arraykonstante eingegeben haben, wandelt die MTRANS-Funktion die Arraykonstante in eine Spalte um.

Transponieren einer eindimensionalen Spalte

  1. Markieren Sie eine Zeile mit fünf leeren Zellen.

  2. Geben Sie die folgende Formel ein, und drücken Sie dann STRG+UMSCHALT+EINGABETASTE:

    =MTRANS({1;2;3;4;5})

Obwohl Sie eine vertikale Arraykonstante eingegeben haben, wandelt die MTRANS-Funktion die Konstante in eine Zeile um.

Transponieren einer zweidimensionalen Konstante

  1. Markieren Sie einen Block von Zellen mit einer Breite von drei Spalten und einer Höhe von vier Zeilen.

  2. Geben Sie die folgende Konstante ein, und drücken Sie dann STRG+UMSCHALT+EINGABETASTE:

    =MTRANS({1.2.3.4;5.6.7.8;9.10.11.12})

Die MTRANS-Funktion wandelt jede Zeile in eine Reihe von Spalten um.

Seitenanfang

Arbeiten mit grundlegenden Arrayformeln

Dieser Abschnitt enthält Beispiele für grundlegende Arrayformeln.

Erstellen von Arrays und Arraykonstanten aus vorhandenen Werten

Das folgende Beispiel erläutert, wie Arrayformeln zum Erstellen von Verknüpfungen zwischen Zellbereichen in verschiedenen Arbeitsblättern verwendet werden. Es veranschaulicht außerdem, wie eine Arraykonstante aus demselben Satz von Werten erstellt wird.

Erstellen eines Arrays aus vorhandenen Werten

  1. Markieren Sie in einem Arbeitsblatt in Excel die Zellen C8:E10, und geben Sie die folgende Formel ein:

    ={10.20.30;40.50.60;70.80.90}

    Geben Sie unbedingt { (eine öffnende geschweifte Klammer) vor dem Eingeben von "10" und } (eine schließende geschweifte Klammer) nach dem Eingeben von "90" ein, weil Sie ein Array von Zahlen erstellen.

  2. Drücken Sie STRG+UMSCHALT+EINGABETASTE. Dadurch wird dieses Array von Zahlen in den Zellbereich C8:E10 mithilfe einer Arrayformel eingegeben.
    Auf Ihrem Arbeitsblatt sollten die Zellen C8 bis E10 wie folgt aussehen:

    10

    20

    30

    40

    50

    60

    70

    80

    90

  3. Markieren Sie den Zellbereich C1 bis E3.

  4. Geben Sie die folgende Formel in die Bearbeitungsleiste ein, und drücken Sie dann STRG+UMSCHALT+EINGABETASTE:

    =C8:E10

    Ein Array von 3 x 3 Zellen wird in den Zellen C1 bis E3 angezeigt und enthält dieselben Werte wie C8 bis E10.

Erstellen einer Arraykonstante aus vorhandenen Werten

  1. Markieren Sie die Zellen C1:C3, und drücken Sie F2, um in den Bearbeitungsmodus zu wechseln.
    Die Arrayformel sollte noch immer "=C8:E10" lauten.

  2. Drücken Sie F9, um die Zellbezüge in Werte umzuwandeln.
    Excel wandelt die Werte in eine Arraykonstante um. Die Formel sollte nun "={10.20.30;40.50.60;70.80.90}" lauten, entsprechend C8:E10.

  3. Drücken Sie STRG+UMSCHALT+EINGABETASTE, um die Arraykonstante als eine Arrayformel einzugeben.

Zählen der Anzahl von Zeichen in einem Zellbereich

Das folgende Beispiel zeigt, wie die Anzahl der Zeichen, einschließlich Leerzeichen, in einem Zellbereich gezählt wird.

  1. Kopieren Sie die gesamte folgende Tabelle, und fügen Sie sie in Zelle A1 eines Arbeitsblatts ein.

  2. Markieren Sie Zelle A9, und drücken Sie dann STRG+UMSCHALT+EINGABETASTE, um die Gesamtzahl der Zeichen in den Zellen A2:A6 (66) anzuzeigen.

  3. Markieren Sie Zelle A12, und drücken Sie dann STRG+UMSCHALT+EINGABETASTE, um den Inhalt der längsten Zelle im Bereich A2:A6 (Zelle A3) anzuzeigen.

Daten

Dies ist eine

Gruppe von Zellen,

die zusammen

einen Satz

bilden.

Gesamtzahl der Zeichen in A2:A6

=SUMME(LÄNGE(A2:A6))

Inhalt der längsten Zelle (A3)

=INDEX(A2:A6;VERGLEICH(MAX(LÄNGE(A2:A6));LÄNGE(A2:A6);0);1)

Die folgende Formel wird in Zelle A9 verwendet und zählt die Gesamtzahl der Zeichen (60) in den Zellen A2 bis A6.

=SUMME(LÄNGE(A2:A6))

In diesem Fall gibt die Funktion LÄNGE die Länge der einzelnen Textzeichenfolgen in jeder Zelle im Bereich zurück. Die Funktion SUMME addiert dann diese Werte und zeigt das Ergebnis (60) in der Zelle mit der Formel, also in A9, an.

Suchen der n kleinsten Werte in einem Bereich

Dieses Beispiel zeigt, wie nach den drei kleinsten Werten in einem Zellbereich gesucht wird.

  1. Markieren Sie die Zellen A16 bis A18.
    Diese Gruppe von Zellen wird die von der Arrayformel zurückgegebenen Ergebnisse enthalten.

  2. Geben Sie die folgende Formel in die Bearbeitungsleiste ein, und drücken Sie dann STRG+UMSCHALT+EINGABETASTE:

    =KKLEINSTE(A5:A14;{1;2;3})

Die Werte 400, 475 und 500 werden in den entsprechenden Zellen A16 bis A18 angezeigt.

Diese Formel verwendet eine Arraykonstante, um die Funktion KKLEINSTE dreimal auszuwerten und das kleinste (1), das zweitkleinste (2) und das drittkleinste (3) Element im Array zurückzugeben, das in den Zellen A1:A10 enthalten ist. Für die Suche nach weiteren Werten fügen Sie zusätzliche Argumente zur Konstante und eine entsprechende Anzahl von Ergebniszellen zum Bereich A12:A14 hinzu. Sie können auch zusätzliche Funktionen wie SUMME oder MITTELWERT in dieser Formel verwenden. Beispiel:

=SUMME(KKLEINSTE(A 5 :A1 4 ;{1;2;3}))

=MITTELWERT(KKLEINSTE(A 5 :A1 4 ;{1;2;3}))

Suchen der n größten Werte in einem Bereich

Um nach den größten Werten in einem Bereich zu suchen, können Sie die Funktion KKLEINSTE durch die Funktion KGRÖSSTE ersetzen. Zusätzlich werden im folgenden Beispiel die Funktionen ZEILE und INDIREKT verwendet.

  1. Markieren Sie die Zellen A1 bis A3.

  2. Geben Sie die folgende Formel in die Bearbeitungsleiste ein, und drücken Sie dann STRG+UMSCHALT+EINGABETASTE:

    =KGRÖSSTE(A5:A14;ZEILE(INDIREKT("1:3")))

Die Werte 3200, 2700 und 2000 werden in den entsprechenden Zellen A1 bis A3 angezeigt.

An diesem Punkt können Kenntnisse über die Funktionen ZEILE und INDIREKT hilfreich sein. Sie können die Funktion ZEILE verwenden, um ein Array aufeinander folgender Ganzzahlen zu erstellen. Markieren Sie z. B. eine leere Spalte mit 10 Zellen in Ihrer Übungsarbeitsmappe, geben Sie die folgende Arrayformel in die Zellen A5:A14 ein, und drücken Sie dann STRG+UMSCHALT+EINGABETASTE:

=ZEILE(1:10)

Die Formel erstellt eine Spalte mit 10 aufeinander folgenden Ganzzahlen. Um ein mögliches Problem zu veranschaulichen, fügen Sie eine Zeile oberhalb des Bereichs ein, der die Arrayformel enthält (d. h. über Zeile 1). Excel passt die Zeilenbezüge an, und die Formel generiert Ganzzahlen von 2 bis 11. Um dieses Problem zu beheben, fügen Sie die Funktion INDIREKT zur Formel hinzu:

=ZEILE(INDIREKT("1:10"))

Die Funktion INDIREKT verwendet Textzeichenfolgen als Argumente (weshalb der Bereich 1:10 in doppelte Anführungszeichen eingeschlossen ist). Excel passt Textwerte beim Einfügen von Zeilen oder sonstigem Verschieben der Arrayformel nicht an. Daher wird mit der Funktion ZEILE immer das gewünschte Array von Ganzzahlen generiert.

Sehen wir uns nun die zuvor verwendete Formel (=KGRÖSSTE(A5:A14;ZEILE(INDIREKT("1:3")))) beginnend bei den inneren Klammern nach außen an: Die Funktion INDIREKT gibt eine Gruppe von Textwerten zurück. Dies sind in diesem Fall die Werte 1 bis 3. Die Funktion ZEILE wiederum generiert ein dreizelliges Spaltenarray. Die Funktion KGRÖSSTE verwendet die Werte im Zellbereich A5:A14 und wird dreimal ausgewertet, einmal für jeden von der Funktion ZEILE zurückgegebenen Bezug. Die Werte 3200, 2700 und 2000 werden in das dreizellige Spaltenarray zurückgegeben. Wenn Sie nach weiteren Werten suchen möchten, fügen Sie einen größeren Zellbereich zur Funktion INDIREKT hinzu.

Sie können diese Formel auch noch mit anderen Funktionen verwenden, z. B. SUMME und MITTELWERT.

Suchen der längsten Textzeichenfolge in einem Zellbereich

Diese Formel funktioniert nur, wenn ein Datenbereich eine einzelne Spalte von Zellen enthält. Geben Sie auf dem Arbeitsblatt "Tabelle3" in Zelle A16 die folgende Formel ein, und drücken Sie STRG+UMSCHALT+EINGABETASTE:

=INDEX(A6:A9;VERGLEICH(MAX(LÄNGE(A6:A9));LÄNGE(A6:A9);0);1)

Der entsprechende Text wird in Zelle A16 angezeigt.

Sehen wir uns nun die Formel beginnend bei den inneren Elementen nach außen genauer an. Die Funktion LÄNGE gibt die Länge der einzelnen Elemente im Zellbereich A6:A9 zurück. Die Funktion MAX berechnet den größten Wert unter diesen Elementen, der der längsten Textzeichenfolge entspricht, die sich in Zelle A7 befindet.

Nun wird es etwas komplexer. Die Funktion VERGLEICH berechnet den Versatz (die relative Position) der Zelle mit der längsten Textzeichenfolge. Dazu werden drei Argumente benötigt: ein Suchwert, ein Sucharray und ein Vergleichstyp. Die Funktion VERGLEICH durchsucht das Sucharray nach dem angegebenen Suchwert. In diesem Fall ist der Suchwert die längste Textzeichenfolge:

(MAX(LÄNGE( A6 : A9 ))

und diese Zeichenfolge befindet sich in folgendem Array:

LÄNGE( A6:A9 )

Das Argument für den Vergleichstyp lautet 0. Der Vergleichstyp kann den Wert "1", "0" oder "-1" aufweisen. Wenn Sie "1" eingeben, gibt VERGLEICH den größten Wert zurück, der kleiner oder gleich dem Suchwert ist. Wenn Sie "0" angeben, gibt VERGLEICH den ersten Wert zurück, der dem Suchwert genau entspricht. Wenn Sie "-1" eingeben, gibt VERGLEICH den kleinsten Wert zurück, der größer oder gleich dem angegebenen Suchwert ist. Wenn Sie keinen Vergleichstyp angeben, verwendet Excel den Wert "1".

Schließlich übernimmt die Funktion INDEX diese Argumente: ein Array und eine Zeilen- und Spaltennummer innerhalb dieses Arrays. Der Zellbereich A6:A9 stellt das Array bereit, die Funktion VERGLEICH stellt die Zelladresse bereit und das letzte Argument (1) gibt an, dass der Wert aus der ersten Spalte im Array stammt.

Seitenanfang

Arbeiten mit erweiterten Arrayformeln

Dieser Abschnitt enthält Beispiele für erweiterte Arrayformeln.

Addieren eines Bereichs, der Fehlerwerte enthält

Die Funktion SUMME in Excel funktioniert nicht, wenn Sie einen Bereich addieren möchten, der einen Fehlerwert enthält, z. B. #NV. Dieses Beispiel zeigt, wie Sie die Werte in einem Bereich mit dem Namen "Daten" addieren, der Fehler enthält.

=SUMME(WENN(ISTFEHLER(Daten);"";Daten))

Die Formel erstellt ein neues Array mit den ursprünglichen Werten ohne alle Fehlerwerte. Beginnend mit den inneren Funktionen nach außen durchsucht zunächst die Funktion ISTFEHLER den Zellbereich (Daten) nach Fehlern. Die Funktion WENN gibt einen bestimmten Wert zurück, wenn eine angegebene Bedingung als WAHR bewertet wird, und einen anderen Wert, wenn sie als FALSCH bewertet wird. In diesem Fall werden leere Zeichenfolgen ("") für alle Fehlerwerte zurückgegeben, da sie als WAHR bewertet werden, und die übrigen Werte aus dem Bereich (Daten) zurückgegeben, da diese als FALSCH bewertet werden, d. h., dass keine Fehlerwerte enthalten sind. Die Funktion SUMME berechnet dann die Summe für das gefilterte Array.

Zählen der Anzahl von Fehlerwerten in einem Bereich

Dieses Beispiel ähnelt der vorherigen Formel, doch wird die Anzahl der Fehlerwerte in einem Bereich mit dem Namen "Daten" zurückgegeben, statt diese herauszufiltern.

=SUMME(WENN(ISTFEHLER(Daten);1;0))

Mit dieser Formel wird ein Array erstellt, das den Wert 1 für die Zellen aufweist, die Fehler enthalten, und den Wert 0 für die Zellen, die keine Fehler enthalten. Sie können die Formel vereinfachen und dasselbe Ergebnis erzielen, wenn Sie das dritte Argument für die Funktion WENN wie folgt entfernen:

=SUMME(WENN(ISTFEHLER(Daten);1))

Wird das Argument nicht angegeben, gibt die WENN-Funktion eine Bewertung als FALSCH zurück, wenn eine Zelle keinen Fehlerwert enthält. Sie können die Formel sogar noch weiter vereinfachen:

=SUMME(WENN(ISTFEHLER(Daten)*1))

Diese Version funktioniert, da WAHR*1=1 und FALSCH*1=0.

Addieren von Werten basierend auf Bedingungen

Möglicherweise müssen Sie Werte auf Grundlage von Bedingungen addieren. Mit dieser Arrayformel werden beispielsweise nur die positiven Ganzzahlen in einem Bereich mit dem Namen "Umsatz" addiert:

=SUMME(WENN(Umsatz>0;Umsatz))

Die WENN-Funktion erstellt ein Array positiver Werte und falscher Werte. Die SUMME-Funktion ignoriert die falschen Werte, da 0+0=0. Der Zellbereich, den Sie in dieser Formel verwenden, kann aus einer beliebigen Anzahl von Zeilen und Spalten bestehen.

Sie können auch Werte addieren, die mehr als eine Bedingung erfüllen. Diese Arrayformel berechnet z. B. Werte größer als 0 und kleiner als oder gleich 5:

=SUMME((Umsatz>0)*(Umsatz<=5)*(Umsatz))

Beachten Sie, dass diese Formel einen Fehler zurückgibt, wenn der Bereich eine oder mehrere nicht numerische Zellen enthält.

Sie können auch Arrayformeln erstellen, die eine Art von ODER-Bedingung verwenden. So können Sie beispielsweise Werte addieren, die kleiner als 5 und größer als 15 sind:

=SUMME(WENN((Umsatz<5)+(Umsatz>15);Umsatz))

Die WENN-Funktion sucht nach allen Werten, die kleiner als 5 und größer als 15 sind, und übergibt diese Werte an die Funktion SUMME.

Sie können die Funktionen UND und ODER nicht direkt in Arrayformeln verwenden, da diese Funktionen ein einzelnes Ergebnis zurückgeben, entweder WAHR oder FALSCH, und Arrayfunktionen Ergebnisarrays benötigen. Sie können das Problem umgehen, indem Sie die in der vorherigen Formel dargestellte Logik verwenden. Mit anderen Worten: Sie führen mathematische Operationen wie eine Addition oder Multiplikation für Werte aus, die der ODER- oder UND-Bedingung entsprechen.

Berechnen eines Mittelwerts unter Ausschluss von Nullen

Dieses Beispiel zeigt, wie Nullen aus einem Bereich entfernt werden, wenn ein Mittelwert für die Werte in diesem Bereich berechnet werden muss. Für die Formel wird ein Datenbereich mit dem Namen "Umsatz" verwendet:

=MITTELWERT(WENN(Umsatz<>0;Umsatz))

Die WENN-Funktion erstellt ein Array von Werten, die ungleich 0 sind, und übergibt diese Werte dann an die Funktion MITTELWERT.

Zählen der Anzahl von Unterschieden zwischen zwei Zellbereichen

Diese Arrayformel vergleicht die Werte in zwei Zellbereichen mit den Namen "EigeneDaten" und "FremdeDaten" und gibt die Anzahl der Unterschiede zwischen den beiden Bereichen zurück. Wenn die Inhalte der beiden Bereiche identisch sind, gibt die Formel den Wert "0" zurück. Um diese Formel verwenden zu können, müssen die Zellbereiche die gleiche Größe und die gleiche Dimension aufweisen (wenn z. B. "EigeneDaten" einen Bereich von 3 Zeilen mal 5 Spalten umfasst, muss auch "FremdeDaten" einen Bereich von 3 Zeilen mal 5 Spalten umfassen):

=SUMME(WENN( EigeneDaten =FremdeDaten;0;1))

Die Formel erstellt ein neues Array mit der gleichen Größe wie die Bereiche, die verglichen werden. Die WENN-Funktion füllt das Array mit dem Wert "0" und dem Wert "1" ("0" für unterschiedliche und "1" für identische Zellen). Die Funktion SUMME gibt dann die Summe der Werte im Array zurück.

Sie können die Formel folgendermaßen vereinfachen:

=SUMME(1*( EigeneDaten <> FremdeDaten ))

Wie die Formel, mit der Fehlerwerte in einem Bereich gezählt werden, funktioniert auch diese Formel, da WAHR*1=1 und FALSCH*1=0.

Suchen der Position des maximalen Werts in einem Bereich

Diese Arrayformel gibt die Zeilennummer des maximalen Werts in einem einspaltigen Bereich mit dem Namen "Daten" zurück:

=MIN(WENN(Daten=MAX(Daten);ZEILE(Daten);""))

Die WENN-Funktion erstellt ein neues Array, das dem Bereich "Daten" entspricht. Wenn eine entsprechende Zelle den maximalen Wert im Bereich enthält, enthält das Array die Zeilennummer. Andernfalls enthält das Array eine leere Zeichenfolge (""). Die Funktion MIN verwendet das neue Array als zweites Argument und gibt den kleinsten Wert zurück, der der Zeilennummer des maximalen Werts in "Daten" entspricht. Wenn der Bereich "Daten" identische maximale Werte enthält, gibt die Formel die Zeile des ersten Werts zurück.

Wenn Sie die eigentliche Zelladresse eines maximalen Werts zurückgeben möchten, verwenden Sie die folgende Formel:

=ADRESSE(MIN(WENN(Daten=MAX(Daten);ZEILE(Daten);""));SPALTE(Daten))

Seitenanfang

Benötigen Sie weitere Hilfe?

Sie können jederzeit einen Experten in der Excel Tech Community fragen, Unterstützung in der Answers Community erhalten oder aber ein neues Feature oder eine Verbesserung auf Excel User Voice vorschlagen.

Hinweis : Haftungsausschluss für maschinelle Übersetzungen: Dieser Artikel wurde mithilfe eines Computersystems und ohne jegliche Bearbeitung durch Personen übersetzt. Microsoft bietet solche maschinellen Übersetzungen als Hilfestellung für Benutzer ohne Englischkenntnisse an, damit Sie von den Informationen zu Produkten, Diensten und Technologien von Microsoft profitieren können. Da es sich bei diesem Artikel um eine maschinelle Übersetzung handelt, enthält er möglicherweise Fehler in Bezug auf (Fach-)Terminologie, Syntax und/oder Grammatik.

Siehe auch

Übersicht über Formeln

Ihre Fähigkeiten erweitern
Schulung erkunden
Neue Funktionen als Erster erhalten
An Office Insider teilnehmen

War diese Information hilfreich?

Vielen Dank für Ihr Feedback!

Vielen Dank für Ihr Feedback. Es klingt, als ob es hilfreich sein könnte, Sie mit einem unserer Office-Supportmitarbeiter zu verbinden.

×