Auf dem Weg zu den höheren Excel-Weihen führt kein Weg daran vorbei: Du musst den Sprung in die Matrix wagen. Nein, die Rede ist nicht vom Film-Klassiker mit Keanu Reeves. Du bist hier schließlich auf einem Excel-Blog gelandet. Ich spreche von Matrixformeln, auch Array-Formeln genannt.
Was bei dem unbedarften Excel-Anwender ein fragendes Stirnrunzeln auslöst, jagt manch anderem einen Schauer des Entsetzens über den Rücken. Haftet den Array-Formeln vielleicht nicht der Ruf von schwarzer Magie an, so handelt es sich dabei doch zumindest um schwer zu durchschauendes Teufelswerk. Oder?
Und genau mit diesem Vorurteil möchte ich mit der heutigen kleinen Einführung aufräumen.
Eine kleine Einführung
Ein Array ist ganz allgemein gesprochen eine Sammlung von Datenelementen. In Excel also eine Zelle oder ein Zellbereich.
Mit einer „normalen“ Excel-Funktion kann man Berechnungen mit einem solchen Zellbereich durchführen und erhält ein einzelnes Ergebnis zurück (zum Beispiel mit der SUMME-Funktion).
Array- oder Matrixformeln hingegen können auf einen solchen Zellbereich mehrere Berechnungen gleichzeitig durchführen oder mehrere Zellbereiche in der Berechnung kombinieren. Das Ergebnis kann ebenfalls ein einzelner Wert (in einer Zelle) oder auch mehrere Werte (in mehreren Zellen) sein. Eine einzelne Array-Formel ermöglicht damit Berechnungen, für die ansonsten mehrere Formeln und Hilfsspalten nötig gewesen wären.
Eine Array-Formel ist in Excel an den sie umschließenden geschweiften Klammern zu erkennen. Diese dürfen jedoch nicht über die Tastatur eingetippt werden. Stattdessen muss eine Array-Formel immer mit der Tastenkombination STRG+Umschalt+Eingabe abgeschlossen werden (STRG+Taste drücken und gedrückt halten, dann die Umschalt-Taste dazunehmen und ebenfalls gedrückt halten und schließlich mit der Eingabetaste alles übernehmen).
Beende die Eingabe einer Array-Formel immer mit STRG+Umschalt+Eingabe
Das war jetzt alles noch ziemlich abstrakt. Die folgenden Beispiele sollen das ein wenig greifbarer machen.
Noch ein Hinweis an die Excel-Profis:
Ich weiß, dass sich viele der folgenden Beispiele auch mit Standard-Funktionen umsetzen lassen. Aber darum geht es hier nicht. Ich möchte vielmehr ein grundsätzliches Verständnis für Array-Formeln vermitteln, um den noch nicht so erfahrenen Excel-Anwendern die Scheu davor zu nehmen und vielleicht die Experimentierfreude ein wenig zu fördern. Denn manche Probleme lassen sich eben doch nur mit den Array-Funktionen lösen.
So, genug der Vorrede. Jetzt geht’s los (die Beispieldatei kannst du dir hier herunterladen).
Gesamtsumme ermitteln (Variante 1)
Für 5 verkaufte Artikel soll der gesamte erzielte Umsatz berechnet werden. Dazu sind zwei Berechnungsschritte notwendig:
1. Multipliziere pro Artikel die Menge mit dem Einzelpreis
2. Addiere sämtliche Artikelumsätze zu einem Gesamtumsatz
Der klassische Weg in zwei Schritten
Eine Array-Formel vereinigt diese beiden Schritte in einer einzigen Ergebniszelle. Links im Bild die eben gezeigte klassische Variante, rechts die bessere Variante mit einer Array-Formel:
Der elegantere Weg über eine Array-Formel
Der innere Teil der Formel (blau markiert) führt zunächst die Multiplikation der beiden Spalten durch (A4:A8*B4:B8), der äußere Teile addiert mit der SUMME-Funktion die errechneten Einzelergebnisse.
Dies ist deshalb möglich, weil beiden angegebenen Bereiche die gleiche Anzahl an Spalten und Zeilen umfasst. Eine Array-Formel wäre nicht möglich, wenn ein Bereich weniger Zeilen hätte:
Matrizenmultiplikation. Was geht und was geht nicht.
Gesamtsumme ermitteln (Variante 2)
Die Bereiche in Array-Formeln müssen aber nicht zwingend den gleichen Aufbau haben. Möglich ist z.B. auch folgende Variante:
Matrizenmultiplikation, Teil 2
Hier wird der erste Bereich zunächst mit der ersten Spalte des zweiten Bereichs multipliziert und dann noch mit der zweiten Spalte. Die Einzelergebnisse werden dann wieder summiert.
Was dahinter steckt, sind die Regeln der Matrizenmultiplikation und ich möchte dich hier nicht mit allen Varianten langweilen. Aber ein Verständnis für das grundsätzliche Prinzip ist nicht schädlich, wenn man Array-Formeln durchdringen möchte. Wer es genauer wissen will, kann gerne auf Wikipedia ein wenig schmökern.
Rechnen mit Bedingungen
Bisher haben wir nur an der Oberfläche gekratzt und die Beispiele waren zugegebenermaßen auch noch nicht der Knaller. Das könnte sich jetzt ändern. Eine der großen Stärken von Array-Formeln ist, dass man mit ihnen Berechnungen anstellen kann, für die bestimmte Bedingungen zutreffen müssen. Beispiel:
Bilde die Summe der 5 größten Werte einer Liste
Die Summe der 5 größten Werte. Aber wie?
Wie würde man ohne Array-Formel an das Problem herangehen?
Der erste Schritt wäre, die 5 größten Werte zu bestimmen, um sie dann in einem zweiten Schritt zu summieren. Dazu kann man die KGRÖSSTE-Funktion verwenden:
Klassiker: Die KGRÖSSTE-Funktion
Der Wert für k wurde dabei fest in die Formel eingetragen.
Wie packt man so etwas in eine Array-Formel? Die Schwierigkeit ist hier, dass sich der Wert für k immer ändern muss, nämlich von 1 bis 5. Dafür gibt es einen kleinen, aber feinen Trick: Die ZEILE-Funktion.
ZEILE macht nichts anderes, als die Zeilennummer der angegebenen Zelladresse zurückzuliefern. So ergibt =ZEILE(B5) den Wert 5, ZEILE(A3) den Wert 3 und so weiter. Man kann statt einer Zelladresse auch eine Zeilennummer angeben:
=ZEILE(1:1) gibt naheliegenderweise den Wert 1, =ZEILE(7:20) ergibt den Wert 7, also die erste Zeilennummer im angegebenen Bereich.
Was im Normalfall relativ sinnfrei erscheint, erweist sich innerhalb einer Array-Funktion als genial. Gehen wir es Schritt für Schritt an.
Markiere die ersten 5 Zellen in Spalte C und gib dann in die erste folgende Formel ein:
=KGRÖSSTE(B3:B12;ZEILE(1:5))
Schließe dann die Formel mit STRG+Umschalt+Eingabe ab.
Array-Formel: Die Zwischenlösung
Was genau passiert hier? In jeder der 5 Zellen steht die gleiche Formel. Da wir sie über die spezielle Tastenkombination aber als Array-Formel eingeben haben, verwendet Excel abhängig von der Zellenposition jeweils die nächste Zeilennummer in der ZEILE-Funktion. Und damit erhalten wir genau das gewünschte Ergebnis für die KGRÖSSTE-Funktion.
Aber wir wollen ja die Summe daraus bilden und das Ganze auch noch ohne Zwischenberechnungen. Dazu fehlt nur ein letzter kleiner Schritt:
{=SUMME(KGRÖSSTE(B3:B12;ZEILE(1:5)))}
Array-Formal: Die finale Lösung
Das soll es für diese kleine Einführung in die Welt der Array-Formeln gewesen sein. Wie eingangs erwähnt, wären die gleichen Ergebnisse auch anders zu erzielen (Stichwort SUMMENPRODUKT). Aber ich hoffe, du hast jetzt zumindest eine bessere Vorstellung, wozu Array-Formeln in der Lage sind. Denn auch SUMMENPRODUKT ist nichts anderes als eine Array-Funktion, lediglich die geschweiften Klammern sind nicht notwendig.
In einem der folgenden Artikel werde ich das Thema wieder aufgreifen und dir einige weitere, teils verblüffende Beispiele zeigen, wozu man diese Spezialformeln einsetzen kann.
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.
Hallo Martin,
vielen Dank für die Einführung in die Matrixformeln (egal ob hierfür nun die rote oder die blaue Zellenfarbe gewählt werden musste). Gerade das einfache Beispiel mit der Kombination von ZEILE() in der Matrix hilft hier sehr weiter und ist mir hier wesentlich einleuchtender als eine etwas umfangreichere Berechnung der EAN Prüfziffer per Matrixformel. Hier hatten sich Gerhard Pundt und ich gemeinsam um eine Berechnung der Prüfziffer ebenfalls per Matrixformel bemüht. 🙂
Ich freue mich auf jeden Fall schon auf die folgenden Artikel gerade da ich hier hoffe meinen inneren Widerstand gegen die Matrix abbauen zu können und die Matrixformeln künftig ebenso gerne einzusetzen wie mittlerweile die INDIREKT() Funktion, zu der ich ebenfalls eine halbe Ewigkeit brauchte um diese zu mögen. 🙂
Viele Grüße und schon vorab Danke für die Serie
Andreas
Hallo Andreas,
mir geht es da sehr ähnlich. Es gibt einfach Funktionen, die brauchen länger, bis man sich damit anfreunden kann 🙂
Schöne Grüße,
Martin
Guten Tag Martin
Du hast wieder eine interessante Info mit der Arrey-Formel platziert. Besten Dank dafür.
Ich habe diese nachgemacht, einmal auf deinem Arbeitsblatt, einmal mit einem selbstgemachten Arbeitsblatt.
Die Formel stimmt, jedoch nimmt es bei meiner Berechnung auf deinem Arbeitsblatt als fünfte Grösse nicht die 53, sondern nach der 69 direkt die 39 und dann die 21. Warum das?
Danke für deine Hilfe.
Freundliche Grüsse
Renato
Hallo Renato,
in meinem Beispiel wäre die 53 nicht die fünftgrößte, sondern die viertgrößte Zahl. Die Reihenfolge ist 95, 87, 69, 53, 39.
Du hast vermutlich irgendwo einen Wurm mit den Zellbezügen drin. Für die fünf größten Werte muss die ZEILE-Funktion immer ZEILE(1:5) lauten.
(und das abschließende STRG+Umschalt+Eingabe nicht vergessen)
Schöne Grüße,
Martin
Guten Tag Martin
Besten Dank für deine Rückmeldung.
Ich habe es heute im Büro nochmals versucht, jedoch erhalte ich immer das selbe Resultat.
95, 87, 69, 39, 21.
Die Berechnung habe ich direkt ab deinem Excel-File gemacht.
Komischerweise stimmt die Summe der 5 Grössten mit 343 ;-).
Ich wollte dir ein jpg senden, damit du sehen kannst, dass ich alles korrekt eingegeben und berechnet habe.
Ich werde noch ein wenig mit anderen Berechnungen „experimentieren“. Vielleicht sehe ich, was nicht passt.
Trotzdem vielen Dank für deine Bemühungen.
Freundliche Grüsse
Renato
Guten Tag Martin
Übung mach den Meister ;-).
Es hat mir keine Ruhe gelassen und ich habe geübt und geübt, bis ich den Fehler (endlich) gefunden habe. Ich habe die Zellen nicht markiert!! Dieses Unterlassen wird mir hier nie mehr passieren ;-). Nun passt auch bei mir die Reihenfolge.
Herzlichen Dank und ich freue mich auf deine nächsten Tipps
Renato
Hallo Renato,
das freut mich, dass Du den Fehler doch noch gefunden hast. Manchmal hilft einfach nur eine gewisse Hartnäckigkeit.
Schöne Grüße,
Martin
Hi,
Erstmal großes Lob an deinen Blog den ich erst gestern entdeckt habe und der mich jetzt schon um viel Wissen bereichert hat! 🙂
Ich habe dein Beispiel nachgespielt und dann die Formel =SUMME(KGRÖSSTE(H8:H22;ZEILE(1:5))) einmal mit und einmal ohne Array eingegeben. Jetzt spucken mir die beiden Formeln unterschiedliche Zahlen aus. Kannst du mir vielleicht erklären woran das liegen könnte? Der einzige unterschied zwischen den beiden Formeln sind eben die Array-Klammern. Was genau machen diese Klammern eigentlich in der Formel? Weil theoretisch würde die Formel ja auch ohne den Klammern funktionieren oder?
Ich glaube, dass das meinen Knoten lösen würde was diese Thema angeht 🙂
Danke und liebe Grüße
Angela
Hallo Angela,
die Unterschiede kommen eben genau daher, dass die Array-Version (also die mit den geschweiften Klammern), die Ergebnisse anders berechnet. Es finden sozusagen im Hintergrund lauter einzelne Zwischenrechnungen statt, im die fünf größten Werte zu ermitteln und sie dann mit SUMME zu addieren.
In der Version ohne Klammern ignoriert Excel in der KGRÖSSTE-Funktion, dass man mit ZEILE eigentlich 5 einzelne Werte verwenden möchte. Stattdessen nimmt Excel den ersten Wert 1 und liefert damit nur das größte Ergebnis. Wenn Du statt ZEILE(1:5) jetzt ZEILE(3:5) schreibst, liefert es nur den drittgrößten Wert.
Wie Du siehst, funktioniert diese Rechnung also nur in der Array-Formel.
Vielleicht hat das ja ein wenig weitergeholfen.
Schöne Grüße,
Martin
Hallo Martin,
vielen Dank für deinen Einführungsartikel zum Thema Array-Funktionen und ganz generell für die extrem übersichtliche und strukturierte Darstellung deiner Hilfethemen auf deiner Seite.
Auch wenn der Artikel schon ein wenig im Netz steht, stelle ich mir die Frage, ob es mit ähnlichen Tools auch möglich ist, formelbasiert nicht die k-größten, sondern beispielsweise die unteren 20% einer Wertereihe zu summieren. Die 20% sollen dabei aber nicht auf die Anzahl der Werte, sondern auf die Gesamtsumme der Werte bezogen sein.
Noch einmal besten Dank und viele Grüße,
Jonas
Hallo Jonas,
grundsätzlich gibt es ja auch die KKLEINSTE-Funktion. Für k kann allerdings immer nur eine ganze Zahl stehen, kein Prozentsatz. Du musst also vorher diesen Prozentsatz bezogen auf deine Liste in eine absolute Zahl umwandeln.
Dann aber lässt sich auch eine Summe bezogen auf die kleinsten Gesamtwerte berechnen. Bezogen auf das erste Beispiel im Artikel könnte das so aussehen (die kleinsten 20% bedeuten hier also für k = 1 bzw. k = 2):
{=SUMME(KKLEINSTE(A4:A8*B4:B8;ZEILE(1:2)))}
Schöne Grüße,
Martin
Danke, hat mir sehr geholfen!
Hallo Gerd,
sehr gern geschehen!
Schöne Grüße,
Martin
Also im Prinzip verstehe ich das Konzept, nur wann genau ein Array durchgegangen wir und wann nicht, erscheint mir nicht auf Anhieb schlüssig.
Beim Beispiel: {=SUMME(KGRÖSSTE(B3:B12;ZEILE(1:5)))}
geht die Berechnung für die Zeile schleifenartig von 1 bis 5 durch. B3:B12 allerdings wird als Bereich angesehen und nicht für jede Zelle einzeln ausgeführt.
Kann ich das so verstehen, dass dann wenn eigentlich nur eine einzelne Zelle vorgesehen ist ein Bereich eben durchgegangen wird und da wo ein Bereich vorgesehen ist, dieser auch als Bereich angesehen wird?
Hallo Ralph,
es ist in der Tat nicht so leicht, Array-Formeln zu verstehen. Ich stolpere selbst immer wieder noch drüber. Das Beispiel mit SUMME und KGRÖSSTE: Da KGRÖSSTE eigentlich immer nur einen Wert für k erlaubt, wir aber 5 Werte haben wollen, ist hier ein Array notwendig. Damit wird, wie du schon vermutest, die Schleife 5x sozusagen Zeile für Zeile durchgegangen. Ohne Arrayfunktion würde SUMME(KGRÖSSTE()) nur einmal ausgeführt werden.
Schöne Grüße,
Martin
Hallo Martin,
ich weiß gar nicht, wie oft deine Seite mir schon die Augen geöffnet hat. Zuletzt war es das Dreamteam INDEX und VERGLEICH, wie du gleich sehen wirst. Zunächst vielen Dank dafür! Jetzt bin ich allerdings an einem Punkt angekommen, wo ich Hilfe brauchen könnte, denn meine Matrixformel spuckt zu hohe Ergebnisse aus. Hier die Formel:
{=MAX(WENN(INDEX(m;$W$2:$AX$2;AY$2)=“x“;$W4:$AX4;0))}
Das x kennzeichnet in einer Hilfstabelle (Matrix m), ob ein Mitarbeiter (jeder hat eine Spalte) eine bestimmte Rolle (diese haben je eine Zeile) hat. x = er hat die Rolle. Jetzt steht in meiner eigentlichen Tabelle nochmal die Auflistung dieser Rollen und zwar in W:AX. In Zeile 2 verweise ich auf die Hilfstabelle. Darunter befinden sich viele Zeilen mit Werten zwischen 0 und 4, es sind Skills und die Zahlen geben an, wie hoch die Anforderungen an die jeweiligen Rollen sind. Über AY2 kommt der Mitarbeiter ins Spiel für den in der Hilfstabelle steht, welche Rollen er hat.
Was ich will ist, dass der Zahlenwert aus dem Bereich W bis AX nur dann im Maximum in Betracht gezogen wird, wenn der Mitarbeiter die Rolle überhaupt hat. Sprich, nur wenn das x gilt, soll in diesem Beispiel aus W4 bis AX4 die Zahl berücksichtigt werden. Leider gibt mir die Matrixformel immer das Gesamtmaximum für den Bereich W4:AX4 aus, in diesem Fall 4. Die 4 ist die in der Zeile höchste Anforderung an alle Rollen. Allerdings hat der in AY2 genannte Mitarbeiter die Rolle mit der Anforderung 4 nicht, sondern es müsste eine 2 das Ergebnis sein.
In diesem ganz konkreten Beispiel dürften nur die beiden ersten Elemente der Matrix W4:AX4 im Maximum berücksichtigt werden. Was hab ich da falsch gemacht, sodass immer das Gesamtmaximum gezogen wird? Das MAX läuft ja offenbar durch das gesamte Array und versteht nicht, dass ich es einschränken wollte.
Ich hoffe, ich konnte das verständlich erklären und wäre für einen Stupser in die richtige Richtung sehr dankbar.
Liebe Grüße,
Nina
Hallo Nina,
danke für das schöne Feedback. Aber was dein Problem angeht: Das sprengt etwas den Rahmen, da es wirklich sehr speziell ist und ohne den konkreten Tabellenaufbau nur schwer nachvollziehbar ist. Daher kann ich hier leider keinen Stupser geben 🙁
Schöne Grüße,
Martin
Hallo Martin,
ich habe ein Problem, das ich mit Arrays lösen zu können hoffe – aber leider nicht schaffe ;-).
Es gibt eine Tabelle mit Produkten, die in einem oder mehreren Ländern hergestellt werden (Tabelle Produkt), in der die Länderlisten schon über die Array-Formel „Textverketten()“ (mit Strg.+Umschalten) je in eine Zelle geschrieben wurden:
Prod. Produktionsländer Vertriebsländer
A DE,NL DE,NL
B DK,SE,NL DK,SE,NL
C DE,UK DE,UK
D UK UK
Nun will ich die Vertriebsländer mit den Produktionsländern vergleichen, um zu ermitteln:
– in wie vielen Ländern das Produkt hergestellt und vertriebe wurde
– ob das Produkt nur in den Herstellungsländern vertrieben wurde
– in wie vielen Ländern außerhalb der Produktionsländer das Produkt vertriebe wurde u. ä. m.
Ich suche daher eine Möglichkeit, mit der Element für Element von Vertriebsländer in Produktionsländer suchen bzw. zählen kann. Eine erste, sehr einfache VBA-Funktion, der ich eine Zelle der Länderliste übergebe, liefert allerdings nur „#Wert“:
Function AnzahlArrayElemente(myArray)
AnzahlArrayElemente = UBound(myArray) – LBound(myArray) + 1
End Function
Scheinbar handelt es sich bei den Landesliste nicht um einen Array (trotz Abschluss mit Strg.+Umschalten).
Meine Fragen:
– Lässt sich en Array überhaupt in einer Zelle ablegen und wenn ja, wie müssen die einzelnen Elemente voneinander getrennt werden?
– Und ist das in diesem Fall überhaupt sinnvoll?
– Muss auf das Ablegen der Länderlisten in Zellen besser ganz verzichten und komplett alles in VBA erledigen?
Nach dem Du mir vor ein paar Wochen schon mal sehr kompetent weitergeholfen hast, hoffe ich, nochmal auf ein Heureka-Erlebnis 🙂
Hallo Dirk,
Fragen über Fragen, zu denen ich auch keine konkreten Antworten geben kann. Und insbesondere zu VBA gibt es bei mir leider generell überhaupt keine Tipps.
Mir stellt sich allerdings schon eine Frage:
Wenn die Daten anscheinend schon in einer separaten Tabelle getrennt vorliegen, warum versuchst du dann nicht diese Quelltabelle auszuwerten und plagst dich stattdessen mit den verketteten Zellen herum? Aber vielleicht stehe ich ja auch gerade auf dem Schlauch…
Schöne Grüße,
Martin
Hallo Martin,
sorry, erst jetzt gesehen, dass Du geantwortet hast, vielen Dank.
Es können mehrere Landeskürzel in einer Zelle vorkommen, das ist oben schlecht zu erkennen, weil meine Tabulierung im Kommentarfeld nicht übernommen wurde. In der ersten Zeile heißt es also
Produkt=“A“, Produktionsländer= „DE,NL“, Vertriebsländer=“DE,NL“
Von der Logik her will ich die Frage beantworten: wie viele der Vertriebsländer sind in den Produktionsländern enthalten, im Beispiel also 2 von 2. Um es plastischer zu machen, es geht um Filme und um die Frage, ob ein Film in weiteren Ländern erschienen ist, als dort wo er koproduziert wurde (Berlinale lässt grüßen ;-). Und es kommen an die 100 Länder vor. Die Billiglösung wäre vielleicht, für jedes Vertriebs- und jedes Produktionsland eine Spalte (insgesamt also an die 200) aufzumachen und unter „DE“ eine 1 reinzusetzen, wenn Deutschland vorkam etc., um dann die jeweils korrespondierenden Spalten zu vergleichen. Fand ich aber nicht so ästhetisch und dachte, das geht vielleicht eleganter.
Update: Die Lösung für alle, die ein ähnliches Problem haben, war wie folgt:
1. eine Spalte mit einer Liste aller ALLEN Länder anlegen, ergal ob Vertriebs- oder Produktionsland (DE, AE … VN, ZA)
2. mit „Daten/Duplikate entfernen“ doppelte entfernen
3. die Liste einmal für die Vertriebs- und einmal für die Produktionsländer im Kopf der Originaltabelle platzieren in identischer Reihenfolge
4. mit der Finden-Funktion in jeder Zelle darunter nachschauen, ob das Land in der Kopfzelle in der Länderliste vorkommt, wenn ja eine 1 reinsetzen, ansonsten leer lassen
5. über SUMMENPRODUKT(BereichVertriebsländer; BereichProduktionsländer) wird dann die Anzahl Länder gezählt, die beide Ländergruppen gemein haben
Vielen Dank für die vielen Anregungen und Lösungen auf Deiner Seite, die ich immer gern besuche!
Hallo Dirk,
vielen Dank für das Lob! Es tut mir sehr leid, ich komme momentan nicht mit dem Beantworten der Kommentare nach. Aber du hast ja offensichtlich schon eine Lösung gefunden und es freut mich, dass du die anderen Leser daran teilhaben lässt.
Schöne Grüße,
Martin
Kleine Frage zum Teil „Gesamtsumme ermitteln (Variante 2)“:¨
In Wikipedia ist die Regel zur Matrizenmultiplikation wie folgt definiert:“Um zwei Matrizen miteinander multiplizieren zu können, muss die Spaltenzahl der ersten Matrix mit der Zeilenzahl der zweiten Matrix/Vektor übereinstimmen“.
Jedoch (!) wird das Beispiel aufgeführt, wo wir eine 5×1 Matrix mit einer 5×2 Matrix multiplizieren (verletzt die oben aufgeführte Regel) und erhalten eine 1×2 Matrix bzw. die Zahlen (81,98).
Wo liegt mein Überlegungsfehler? Ziemlich verwirrend, wenn man versucht die Mathe Regeln hier anzuwenden.
Hallo Cedric,
ich denke, du hast schon Recht: es ist tatsächlich keine echte Matrizenmultiplikation. Excel multipliziert hier nur spaltenweise und addiert dann die Spaltenergebnisse einfach. Insofern ist mein Verweis auf die Matrizenmultiplikation nicht korrekt.
Schöne Grüße,
Martin
Könnte die vermeintlche Komplexität der Anwendung im Allgemeinen nicht daran liegen, dass MS Office eine nicht wirklich durchdachte Suite gewesen ist ?
Klar, es wird immer mal “ Hardliner “ geben, wie in diesem Fall, die offensichtlich der Meinung sind Tabellenkalkulation wäre wünschenswertes Wissen – aber den Mut sich gegen schlechte Anwendungen zu stellen wäre evtl. mal angebracht gewesen.
Wenn Privatpersonen erst eigenständige „Blogs“ errichten müssen dann ist eine Anwendung nicht „komplex“ sondern schlichtweg schlecht entwickelt worden.
Da unser Bildungssystem leider ähnlich rückständig ist und einem selbst in der Uni Excel noch vermittelt wird, musste ich dennoch auf diesen Blog zurückgreifen.
Grüße, ein SysAdmin.
Hallo D,
danke für deinen Kommentar. Man kann sicherlich deine Meinung teilen – muss es aber nicht. Trotzdem freut es mich, dass du auf meiner Seite gelandet bist und ich hoffe, du findest ein paar für dich nützliche Infos.
Schöne Grüße,
Martin
Hallo, gibt es eine Möglichkeit ohne VBA, dass man ein Matrixergebnis in Text umwandeln kann?
Wenn meine Formel z.B. als Ergebnis {2;4;6;8;10} liefert und ich möchte dann aber in der entsprechenden Zelle eben 2;4,5;8;10 stehen haben, nicht nur die 2, geht das?
Hallo Ralph,
das lässt sich vermutlich mit entsprechenden Text-Funktionen schon umwandeln. Hängt von der genauen Formel ab, die momentan das Ergebnis zurückliefert und natürlich von der eingesetzten Excel-Version. Aber dann steht in der Zelle eben auch die Text-Funktion drin. Wenn du den reinen Text brauchst, geht das nicht ohne VBA.
Schöne Grüße,
Martin
Textfunktion wäre wohl nicht das Problem denke ich, aber genau damit habe ich auch keine Lösung gefunden, übergebe ich den Textfunktionen eine Matrix, verarbeitet diese nur meinen ersten Wert, also im Beispiel die 2.
Noch eine Frage zu Matrix. Wie kann ich zwei Matrizen zusammenführen (so was wie verketten), also aus {1;2;3} und {4;5;6} soll {1;2;3;4;5;6} werden?
Hallo Ralph,
mit welcher Excel-Version arbeitest du denn? Und wie genau lauten die Formeln, welche deine beiden einzelnen Matrizen berechnen?
Schöne Grüße,
Martin
Hallo Martin!
Ich habe auch eine Frage zu den Matrixformeln. Ich habe eine lange Liste mit Einträgen jeweils in den Spalten. Ich möchte nun, das Excel diese Einträge in einer kurzen Liste ohne Zwischenräume zusammenfasst. Dafür habe ich mir folgende Matrixformel gebaut, die mir jeweils die nächste mit einer Zahl gefüllten Zelle zeigen soll:
=MIN(WENN(ISTZAHL(D6:D15);ZEILE(D6:D15)))
In dieser Spalte steht die erste Zahl auch ganz oben und im Formel-Generator gibt er als Ergebnis die 6 aus, was korrekt ist. In der Zelle steht aber immer eine Null. Die Zahlenformatierung ist korrekt.
Kannst Du mir helfen, woran das liegen könnte?
Vielen Dank und viele Grüße
Oliver
Hi Oliver,
die Formel ist korrekt und bei mir funktioniert sich auch einwandfrei. Meine erster Verdächtiger wäre auch das Zahlenformat gewesen, aber das hast du ja schon überprüft. Daher habe ich jetzt auch keine Erklärung, warum in der Zelle Null angezeigt wird…
Vielleicht hat ja ein anderer Leser noch eine Idee.
Schöne Grüße,
Martin
Tagchen,
heute den tollen Blog entdeckt und direkt mal gemerkt 😀
Wieso da immer eine 0 angezeigt wird klingt für mich nach einem Zirkelbezug? Wäre das möglich?
Antwort ist zwar spät, aber vielleicht hilft sie ja dennoch :>
Liebe Grüße
Hallo Martin
Zu deiner oben gezeigte Array-Formel habe ich letzthin gerade auch eine schöne Alternative im www gefunden.
Diese ist zwar weniger geeignet um mehrere (viele) Werte mit KGRÖSSTE zu ermitteln, dafür kamnn man aber auch einfach
bestimmte Werte auswählen und es müssen keine geschweiften Klammern aussen nach einer erneuten Formelbearbeitung
gesetzt werden (Ctrl/Shift/Enter). Dabei müssen aber min. die Anzahl der gesuchten Werte im Bereich stehen, sonst ergibt
es ein nerviges #ZAHL!
In deinem Beispiel würde das so aussehen:
=SUMME(KGRÖSSTE(B3:B12;{1;2;3;4;5})) > ergibt 343
=SUMME(KGRÖSSTE(B3:B12;{1;3;5})) > ergibt 203
> Im Bereich B3:B12 müssen min 5 resp. für 2. Formel 3 Werte vorhanden sein
Dieser Klammerntyp hat anscheinend noch einiges mehr drauf, wie z.B.:
=SUMME(ZÄHLENWENN(B3:B12;{„>90″;“13″;“=31“})), Ergebnis korrekt 3
Viel Spass für alle „Excelianer“ beim austüften
Adrian
Hallo Adrian,
vielen Dank für deine wunderbare Ergänzung. Grundsätzlich kann man in vielen Excel-Funktionen Werte direkt in geschweiften Klammern übergeben, die für sich genommen ein Array darstellen. Dass man sich in diesem Beispiel die Strg+Umschalt+Eingabe-Kombination sparen kann, war mir allerdings auch neu. Ebenso die schöne Variante mit den Kriterien in ZÄHLENWENN.
Wieder was gelernt 🙂
Schöne Grüße,
Martin
Danke,
Gut erklärt
BG U.Bruehl
Hallo Martin.
als Rentner versuche ich einem Rentner-Kollegen mit Hilfe einer Excel-DB die Verwaltung seiner Sterbeunterstüzungskasse zu vereinfachen. Um stets „Gewehr bei Fuß“ stehen zu können, haben wir die Excel-Arbeitsmappe zu MagentaCLOUD verlagert, um beide zeitlich unabhängig auf aktuelle Daten zugreifen zu können.
Jetzt zeigt sich folgendes Problem: während ich z. B. im Tabellenblatt „Beitragszahlungen“ einen nicht mehr relevanten Eintrag als komplette Zeile ohne Einschränkung löschen kann, funktioniert das bei meinem Kollegen nicht. Bei ihm erscheint die Fehlermeldung:
„Teile einer Matrix können nicht gelöscht werden.“
Im Laufe der Entwicklung der Arbeitsmappe habe ich an KEINER Stelle eine Matrixformel verwendet und nun stehe ich vor dem Problem, wie ich weiter helfen kann.
Vielleicht noch ein Hinweis:
– mein Kollege hat sich von einem Fachmann Excel 2019 installieren lassen, da seine Version zu alt war, um z. B. ganz einfach mal ein Makro einfügen zu können.
– Meine Excel-Version wird als Excel 2016 angezeigt und gehört zu einem Office 365 Paket.
Aus deinem Artikel „Ab in die Matrix: Eine kleine Einführung in Array-Formeln“ habe ich die beiden ersten Tabellen mal nachgebildet und versucht, über den Hinweis „Bearbeiten“ > „Suchen u. Auswählen“ > „Gehe zu ..“ > „Inhalte“ > „Aktuelles Array“ die Matrixformel aufzuspüren. Das gelingt ausschließlich in dem Fall, wenn nur der Tabellenbereich markiert wird, in dem die Array-Formel unten rechts liegt. Markierte Zellen darüber hinaus führen zur o. e. Fehlermeldung.
Ich müsste meinen Partner wohl bitten, einmal über STRG + a den gesamten Tabellenbereich zu markieren und dann die gerade beschriebenen Schritte durchführen zu lassen.
Ist meine Überlegung richtig? Könnte dadurch eine (unerklärliche) Matrixformel gefunden werden?
LG Klaus Bojert
Hallo Klaus,
mich irritiert gerade deine Aussage „Excel 2016“ und „Office 365-Paket“, das passt irgendwie nicht zusammen. Falls du wirklich ein Office 365-Paket im Einsatz hast, dann besteht die Möglichkeit, dass du Matrixformeln nutzt, ohne dir dessen bewusst zu sein oder diese mit Strg+Umschalt+Eingabe eingetippt zu haben.
Leider kenne ich keine einfache Möglichkeit, gezielt nach Matrixformeln zu suchen. Die von dir genannte Funktion über „Inhalte auswählen“ hat nur den Zweck, alle Zellen zu markieren, die zu dem aktuellen Array gehören. Dazu muss die aktive Zelle aber eigentlich schon auf einer Array-Zelle stehen. Daher wird dich das nicht weiterbringen.
Es bleibt noch die Option über „Suchen & Auwählen | Formeln“. Damit werden sämtliche Zellen im aktiven Blatt markiert, die eine Formel enthalten. Jetzt kann man einfach mit der Eingabetaste von Formelzelle zu Formelzelle springen und dabei in der Bearbeitungszeile prüfen, ob doch irgendwo die geschweiften Klammern auftauchen.
Das ist natürlich keine sehr elegante Lösung, aber eine bessere kenne ich leider nicht.
Schöne Grüße,
Martin
Moin Klaus,
Moin Martin,
könnte hier vielleicht eine Bedingte Formatierung mit der Formel:
=UND(SUCHEN(„{„;FORMELTEXT(A1:A1));SUCHEN(„}“;FORMELTEXT(A1:A1)))
über den kompletten Bereich helfen, um die Matrixformeln farbig hervor zu heben?
Gruß Marcel
Hallo Marcel,
die angegebene Formel wird von einer Fehlermeldung begleitet.
Auch dann, wenn ich die zweiten Anführungszeichen nach oben setze.
LG Klaus
Moin Klaus,
so wie es aussieht, müssen alle 4 Anführungszeichen nochmal von Hand gesetzt werden.
LG Marcel
Ja, dann passt es.
In meinem Excel-Arbeitsblatt wird erwartungsgemäß kein Treffer angezeigt.
Dann bin ich mal gespannt, ob das auf dem Rechner meines Kumpels auch so aussieht oder tatsächlich ungewollte Matrixformeln auftauchen.
Ich habe in dem kleinen Beispiel, das ich von Martin nachvollzogen habe, mal testweise deine Formel für die Bedingte Formatierung eingesetzt und – die einzige Matrix-Formel wurde erfolgreich rot umrandet!
Das könnte mein Kollege dann auch mal testen.
Danke nochmals, Klaus
Danke, Klaus
Hi Marcel,
eine clevere Idee mit der bedingten Formatierung, da wäre ich nicht draufgekommen. Funktioniert bei mir einwandfrei.
Vielen Dank,
Martin
Moin Martin,
Vielen Dank für das Lob 🙂
Mir ist noch aufgefallen, dass die Formel auch kürzer funktioniert:
=TEIL(FORMELTEXT(A1);1;1)=“{“
Gruß Marcel
Hi Marcel,
sogar noch besser, du bist mein Held!
Schöne Grüße,
Martin
Hallo Martin,
danke für deine Antwort. Deine Irritation könnte ich vielleicht durch einen ScreenShot auflösen. Dazu habe ich die Liste aller Programme den „Microsoft-Teil“ ausgewählt und den Mauszeiger über das Excel-Icon in der Taskleiste bewegt, so dass ich „Excel 2016“ lesen kann. Die Liste startet alphabetisch mit Microsoft 365 – de -de. Dann folgen Microsoft Edge, Microsoft Edge Update, Microsoft Fotos, Microsoft OneDrive, …
Die empfohlene Option über „Suchen & Auswählen | Formeln“ auf unabsichtliche Matrixformeln werde ich mal beschreiten, würde mich aber enorm wundern, einen Treffer zu landen. Als weitere Fehler-Recherche habe ich gestern meinem Kollegen per Brief einen winzigen Datenspeicher mit meiner Excel-Arbeitsmappe zugeschickt in der Hoffnung, dass auf seinem PC bzw. Laptop kein Matrixbereich gemeldet wird.
Liebe Grüße
Klaus
Hallo Klaus,
ja, bin gespannt, was herauskommt. Was die Excel-Version angeht: Die Versionsnummer findest du zuverlässig direkt in Excel. Im Menü „Datei“ auf die Schaltfläche „Konto“ klicken. Dort steht dann auf der rechten Seite entweder Excel 2016 oder etwas in der Art „Microsoft 365 Apps“.
Schöne Grüße,
Martin
Guten Tag mein Name ist Hartmut von Tryller
Ich habe ein Problem mit Exel in folgender Weise.
Das von mir erstellte Excel Programm erzeugt für jedes Jahr eine Ergebniszeile.
Bislang musste diese Ergebnis Zeile kopiert werden und mit der Funktion Inhalte einfügen in eine Tabelle eingefügt werden.
Diese Möglichkeit funktioniert zwar, aber abhängig vom Bediener treten Bedienungsfehler auf da die Ergebniszeile um Zirkel Beziehung zu vermeiden, mit Inhalte einfügen durchgeführt werden muss.
Ein zweites Problem ist die Tatsache dass das Arbeitsblatt aufgrund des manuellen Kopiervorgangs nicht geschützt werden kann.
Was ich nun benötige, ist eine automatisierte Abfolge die das jährlich anfallende Ergebnis dass in einer Zeile aufgeführt wird, automatisch in eine Matrix an der entsprechenden Jahreszahl eingetragen wird.
Es wäre schön wenn Sie mir (77 Jahre) einen Lösungsweg Aufzeichnung könnten.
Vielen Dank im Voraus
mit freundlichen Grüßen
Hartmut von Tryller
Guten Morgen Herr von Tryller,
aus der Ferne ist das natürlich schwer zu beurteilen. Vor allem dann, wenn es um irgendwelche Automatisierungen geht, wird es unter Umständen komplexer und hängt von den genauen Gegebenheiten ab. Sie sprechen davon, dass die Ergebniszeile in eine andere Tabelle kopiert werden soll. Ich gehe davon aus, dass es sich dabei um eine andere Datei handelt und nicht um ein Tabellenblatt innerhalb ihrer Datei, richtig?
Ich würde versuchen, in dieser zweiten Datei über Power Query die benötigten Daten aus der ersten Datei auszulesen und dann dort weiterzuverarbeiten. Dann muss nichts manuell kopiert und eingefügt werden und Sie umgehen die Probleme mit den Zirkelbezügen.
Einen besseren Tipp kann ich Ihnen hier nicht geben. Sollten Sie konkrete Unterstützung bei der Umsetzung benötigen, dann können wir das gerne im Rahmen eines Auftrags lösen.
Schöne Grüße,
Martin
Hallo,
gibt es die Möglichkeit ein mehrdimensionales Array ohne VBA zu verarbeiten.
Konkret möchte ich als Beispiel in 10 Zeilen mit jeweils 5 Werten (in Spalten) mit einer Formel ein Array erzeugen, das die 10 Summen der jeweils 5 Werte enthält.
Wie kann man den Bereich A1 bis E10 in der Form von „A1 bis E1“ bis „A10 bis E10“ angeben? Oder geht das nicht.
Grüße
Lisa
Hallo Lisa,
im Moment kann ich mir noch nicht so richtig vorstellen, wie das von dir gewünschte Ergebnis genau aussehen soll bzw. was genau bezweckt werden soll.
Sollen die fünf Summen als einzelne Werte in einer einzigen Zelle auftauchen? Ich stehe leider etwas auf dem Schlauch…
Schöne Grüße,
Martin
Vielen Dank!
Bin bald 68, als E-Ing. math. halbgebildet und hab so vieles vergessen.
Hier war der Array Einstieg nicht nur möglich, sondern hat auch Freude gemacht.
Werde hobbymäßig wiederkommen. Chapeau!
Hallo Harald,
vielen Dank für das tolle Feedback, das freut mich sehr! Dann wünsche ich auch zukünftig noch viel Freude hier auf dem Blog.
Schöne Grüße,
Martin