Live-Forum - Die aktuellen Beiträge
Datum
Titel
29.03.2024 13:14:12
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
1076to1080
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

MATRIX-Funktion mit INDEX()

MATRIX-Funktion mit INDEX()
05.06.2009 17:33:22
NoNet
Hallo Excel-Profis,
so kurz vor dem Wochenende möchte ich euch nochmal eine kleine "Denksport-Aufgabe" stellen, damit ihr bis Montag nicht einrostet :
Bekanntlich kann man mit MATRIX-Funktionen schöne Dinge anstellen/Berechnungen durchführen.
Leider gibt es aber auch einige Funktionen, die sich nicht als MATRIX-Funktion eingeben lassen - eine davon ist INDEX().
Ich meinte aber, dass man auch diese "überlisten" kann, ähnlich wie das bei anderen Funktionen durch Einklammerung in N() möglich ist. Nur leider kann ich mich nicht mehr daran erinnern, wie man in INDEX() auch mit ZEILE(1:3) alle Elemente der Matrix durchlaufen kann.
Kann mir bitte jemand auf die Sprünge helfen ?
Hier ein kleines Beispiel : Die gelb markierten Zellen sollen per MATRIX-Funktion summiert werden :
ABCDE
1
2
3
4
5
6
7

Array-Formeln der Tabelle
ZelleFormel
D4   {=SUMME(INDEX(A4:A6;ZEILE($1:$3))
D5   {=SUMME(BEREICH.VERSCHIEBEN(A4;ZEILE($1:$3)-1;0)
D6   {=SUMME(INDIREKT(WAHL(ZEILE($1:$3);"A4";"A5";"A6"))
D7   {=SUMME(INDIREKT(INDEX({"A4";"A5";"A6"};(ZEILE($1:$3))))
E4   {=SUMME(N(INDEX(A4:A6;ZEILE($1:$3)))
E5   {=SUMME(N(BEREICH.VERSCHIEBEN(A4;ZEILE($1:$3)-1;0))
E6   {=SUMME(N(INDIREKT(WAHL(ZEILE($1:$3);"A4";"A5";"A6")))
E7   {=SUMME(N(INDIREKT(INDEX({"A4";"A5";"A6"};(ZEILE($1:$3)))))

Bitte zum Vergleich die Ergebnisse der Spalten D (ohne N()) und E (mit N()) beachten !
PS: Und nicht dass mir nun ein Schlaumeier mitteilt, dass man A4:A6 auch ohne Matrix (=SUMME(A4:A6)) summieren kann
Schönes WE wünscht
NoNet

11
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
Na, du hast wohl Langeweile,...
05.06.2009 18:07:07
Luc:-?
...NoNet,
dass du erneut in WFs Revier "wilderst"... ;-)
Nebenbei, dein Ansatz ist falsch. INDEX fkt durchaus auch als "Felderzeuger", aber nicht so, denn das liefert stets nur 1 Wert, nämlich den 1., 2. oder 3.! Du musst schon die volle Indexierung verwenden...
=SUMME(INDEX(A4:A6;;SPALTE(A:C)))
Hast du den "feinen" Unterschied bemerkt...? ;-)
Schönes WE dito!
Gruß Luc :-?
DANKE ! - Nööö, keine Langeweile,...
05.06.2009 18:36:27
NoNet
Hey Luc,
erstmal vielen Dank, Deine Lösung funktioniert einwandfrei !
Ich "wildere" gerne in allen möglichen (Excel-)Revieren - vor allem weil ich nicht der Meinung bin, dass einige davon bestimmten Personen vorbehalten sind (schliesslich habe ich auch 100% Excel gekauft und nicht nur 95% - obwohl ich selbst wohl allenfalls 30% nutze ;-) - WF wird mir das sicherlich verzeihen...
Was mich etwas an Deiner Lösung irritiert :
A4:A6 entspricht einer Matrix aus 3 Zeilen und 1 Spalte, also {10;20;30}
Weshalb muss man dann in der INDEX()-Funktion das Argument SPALTE inkrementieren und nicht ZEILE ?
Ich meine damit übrigens wirklich das 3.Argument dieser Funktion, und nicht die von Dir verwendete Funktion SPALTE(), denn es funktioniert ja auch mit {=SUMME(INDEX(A4:A6;;ZEILE(1:3)))} und entsprechend auch mit {=SUMME(INDEX(MTRANS(A4:A6);ZEILE(1:3);))}
Der Bereich A4:A6 entspricht doch der Matrix {10;20;30} und nicht {10.20.30} (das wäre z.B. A4:C4), also müsste man m.E. doch die Zeilennummer (2. Argument der INDEX()-Funktion) variieren, also {1;2;3} und nicht die Spaltennummer (3.Argument), also wie in Deinem Bsp. {1.2.3} !?!?
SUMME() kann ja sowohl vertikale wie auch horizontale Matrizen addieren, die Ausrichtung spielt für diese Funktion also keine Rolle.
PS: Ich hatte übrigens bereits vor meiner Fragestellung folgende Funktion getestet, die aber auch (warum auch immer) das falsche Ergebnis liefert :
{=SUMME(INDEX(A4:A6;1;SPALTE(A:C)))}
Gruß, ein nicht gelangweilter NoNet
Anzeige
Ja, das hast du richtig erkannt,...
05.06.2009 19:11:23
Luc:-?
...NoNet;
das hat nur was mit Arg3 zu tun, weil...
Arg2 eine Doppelfunktion hat! Wenn nur Arg2 existiert, wird der Bereich einfach nur "durchgezählt"; folglich hast du keine Chance, die Fkt hier zu was Anderem zu "überreden"...
Fazit: Arg2 muss immer vorhdn sein, wenn ein Feld generiert wdn soll. Arg2=1 fkt nicht so, weil es die Auswahl auf Zeile 1 beschränkt. Das wäre auch mit jeder anderen Angabe außer 0 an dieser Stelle so. Ein nicht angegebenes Arg2 wird wohl als 0 interpretiert, so wie das auch bei einigen anderen Fktt der Fall ist.
Übrigens führt auch ein Vertauschen von Argg2+3 nicht zum gewünschten Ergebnis. Arg3=0 wird einfach ignoriert. Ich habe selber mal so eine "Komfort"-Argg-Eingabe für mehrere udF pgmiert, deshalb kann ich mir vorstellen, was da intern läuft... ;-)
Ansonsten zählen nur die Zahlenwerte in den Argg. Die Ausrichtung spielt keine Rolle, weil anscheinend auch nur die "Umgebung" in Form von SUMME diese Wirkung aus INDEX "herauskitzelt". Du weißt ja, dass Xl Formeln optimiert, bevor sie berechnet wdn. Das wird dann wohl die eigentliche Ursache für so manchen "unmöglichen" Berechnungseffekt sein.
Gruß Luc :-?
PS: Freitag und Sonnabend ist abends hier aber wirklich nicht viel los. Da kann man immer gut die wirklich Interessierten von der Masse der "Büroknechte" unterscheiden. Deshalb werde ich jetzt mal auch an meinem AddIn "weiterstricken"... ;-)
Anzeige
Nachtrag: Genau genommen wird hier...
05.06.2009 19:46:22
Luc:-?
...auch Arg3 nicht benötigt, NoNet;
Arg2 muss nur 0 sein, das ist das Entscheidende! Also geht auch...
=SUMME(INDEX(A4:A6;))
Möglicherweise wird das ja von Xl zu =SUMME(A4:A6) optimiert, womit dann allerdings kein Nachweis der Feldgenerierung durch INDEX erbracht wäre. Alle Versuche mit {} ohne SUMME haben nämlich kein Feld ergeben! Man müsste also eine andere "Testumgebung" erdenken, um hier ein endgültiges Urteil fällen zu können. Leider!
Gruß Luc :-?
Nachtrag2: Muss mich teilweise korrigieren,...
05.06.2009 21:00:30
Luc:-?
...NoNet;
=INDEX(A4:A6;) liefert doch ein Feld, nämlich eine Zeile oder Spalte, je nach Ausrichtung von Arg1, weil 0 wohl soviel wie keine Einzelzellen- bzw -elementauswahl bedeutet. Auswahl einer Matrix auf diese Weise liefert dagg #BEZUG!. Demnach wäre hiermit doch der Nachweis der Feldgenerierungsfähigkeit (in Vektorform) von INDEX gelungen! q.e.d.
Gruß + sWE, Luc :-?
Anzeige
Nachweis für INDEX()...
05.06.2009 21:43:54
NoNet
Hey Luc,
nochmals Danke für Deinen Beitrag und für das Ausprobieren.
Wie das "intern" funktioniert, kann ich mir auch sehr gut vorstellen, da auch ich schon selbst solche UDFs erstellt habe.
Interessant finde ich Deine Erkenntnis, dass Argument 2 und 3 überhaupt nicht vorhanden sein müssen (respektive : Argument 2 nur mit DEFAULT-Wert).
Dass es sich dann auch tatsächlich um eine Indizierung der Matrix handelt und nicht nur um die auf SUMME(Matrix) reduzierte Funktion, zeigt folgendes Beispiel (ebenfalls "q.e.d" ;-) :
ABC
1
2
3
4
5
6

MATRIX-Funktionen in der Tabelle : (Eingabe ohne {  } - mit Strg+SHIFT+ENTER bestätigen !)
ZelleFormel
C1   {=SUMME(N(INDIREKT(INDEX({"A1"."A3"."A5"};)))

Nur durch die Indizierung der Matrix {"A1"."A3"."A5"} konnte die SUMME()-Funktion hier ihre Argumente erhalten,womit der Nachweis ebenfalls erbracht wäre !
Hier erkennst Du auch, weshalb es manchmal nicht genügt, eine SUMME() über einen gewöhnlichen Bereich zu bilden : wenn man entweder nicht zusammenhängende Bereiche auswerten möchte, oder sogar Bereiche auf unterschiedlichen Tabellenblätter, die sich möglicherweise dynamisch ergeben oder deren Tabellenblatt-Anzahl sich dynamisch ergibt.
Ein Anwendungsfall kannst Du z.B. auch in folgendem Beitrag finden (Im Gegensatz zu Deinem Kommentar halte ich DBSUMME() hier nicht für geeignet, da diese Funktion m.E. nicht Tabellenblattübergreifend eingesetzt werden kann, was im Beitrag jedoch gefordert war) :
https://www.herber.de/forum/messages/1079482.html
Eine abgewandelte Variante : Es sollen die Werte der Spalte A (bzw.A1:A999) aller Tabellnblätter summiert werden, in deren gleichen Spalte der Spalte B der Wert "XY" steht (Bedingtes Summieren über Tabellenblätter hinweg). Die Lösung hierfür :
- Definition des Bereichnamens TabName mit Bezug auf : =ARBEITSMAPPE.ZUORDNEN(1+0*JETZT())
=> Bereichsname enthält nun eine Matrix aus allen Tabellenblattnamen der aktuellen Mappe !
- Matrix-Funktion : {=SUMME(WENN(T(INDIREKT(ADRESSE(ZEILE(1:999);2;;;INDEX(TabName;))))="Schulz";N(INDIREKT(ADRESSE(ZEILE(1:999);1;;;INDEX(TabName;))))))}
Gruß, NoNet
Anzeige
Nachtrag : Doch noch ein Problem bzgl. INDEX()
05.06.2009 23:50:51
NoNet
Hm.....,
jetzt dachte ich, ich hätte INDEX() im Matrix-Kontext verstanden, da taucht doch glatt das nächste Problem auf :
Aus der Matrix {10;20;40} möchte ich nur die ersten beiden Elemente summieren:
Aber : {=SUMME(INDEX({10;20;40};;ZEILE(1:2)))} ergibt leider 70 - es werden also unabhängig vom Index-Zähler Zeile(1:2) immer ALLE Matrix-Elemente durchlaufen :-(
Mit der Alternative BEREICH.VERSCHIEBEN() (als MATRIX-Variante) ist das möglich :
EF
1
2
3

MATRIX-Funktionen in der Tabelle : (Eingabe ohne {  } - mit Strg+SHIFT+ENTER bestätigen !)
ZelleFormel
F1   {=SUMME(N(BEREICH.VERSCHIEBEN(E1;ZEILE(1:2)-1;0))

Wie bekomme ich das mit INDEX() hin ?
Gruß, NoNet
Anzeige
Habe dieses Posting eben erst gesehen,...
06.06.2009 01:36:23
Luc:-?
...NoNet,
und heute Nacht gar nicht mehr mit dir gerechnet... ;-)
Tja, das wird wohl nichts. Nach dem, was ich festgestellt hatte, ist Arg3 irrelevant, wenn Arg2=0 ist. Das geht dann einfach nicht, weil INDEX in allen anderen Fällen ein Einzelelement zurückgibt. Du musst also die überflüssigen Elemente eliminieren, bspw so...
{=SUMME(INDEX({10;20;40};)*({1;2;3}<3))}
'Ne andere Möglichkeit sehe ich z.Z. nicht!
Gruß Luc :-?
PS: Bin morgen (heute) den ganzen Tag und abends unterwegs; m.kl.Schw. feiert 'nen Runden...
Na ja, dann war's das ja wohl,...
06.06.2009 01:17:00
Luc:-?
...NoNet... ;-)
Wofür so etwas nützlich sein kann, ist mir schon klar. Nur ist das viell doch nur mehr oder weniger Nebeneffekt und deshalb kaum "büroalltagstauglich", weshalb MS ja wohl auch seit Jahren die Entwicklungsschwerpktt anders zu setzen scheint. Ellenlangen Fmln aus solchen o.ä. Konstruktionen ziehe ich dann schon mal 'nen Eigenbau vor...
Was meinen Vorschlag DBSUMME betrifft —­ beschäftige mich mit dieser FkttGruppe noch nicht lange, eigentlich erst seitdem ich gelesen hatte, dass diese auch in/für xlTabb ohne DB-Bezug eingesetzt wdn können. Nachteilig ist evtl, dass für die Kriterienlistung Hilfszellen benötigt wdn. Andererseits wird's so aber auch sehr übersichtlich. Na, jetzt muss ich wohl tatsächlich ausprobieren, ob du hier recht hast... ;-)
Gutnacht! Luc :-?
Anzeige
Letzte Korrektur und Schluss
08.06.2009 03:50:07
Luc:-?
Was ist los, NoNet,
WE im Wahl-Kampfeinsatz...?
So, habe inzwischen noch etwas probiert. Dabei fiel mir Arg4 wieder ein und damit die xlHilfe. Dort ist das alles sehr ausführlich erklärt, nur wird das oft übersehen bzw als schwer verständlich empfunden. Dabei haben die Pgmierer aber auch an Mehrfachauswahlen (nichtzusammenhängende Bereiche) gedacht. Nur haben sie einen etwas anderen Mechanismus installiert als der MatrixFml-"Jünger" vermutet. Dabei stellt sich dann Arg4 als das eigentlich matrixuntaugliche Element heraus...
Userbild
Also muss man entweder die implementierten Mechanismen so akzeptieren und evtl Alternativen für den Arg4-Einsatz schaffen oder gleich eine udF schreiben... ;-)
Gruß + sWo, Luc :-?
Anzeige
Letzter Nachtrag: Anmerkung
12.06.2009 01:28:48
Luc:-?
Die SummenFmln ohne Teilbereiche sind natürlich nicht verwendbar, wenn es sich tatsächlich um Teilbereiche handelt und dazwischen auch Zahlen stehen. Diese würden dann mitsummiert.
Luc :-?

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige