Microsoft Excel

Herbers Excel/VBA-Archiv

(Matrix)Formel | Herbers Excel-Forum


Betrifft: (Matrix)Formel von: Bertram
Geschrieben am: 14.02.2012 11:34:37

Hallo zusammen,

hab mal wieder ein Problem und seh den Wald vor lauter Bäumen wahrscheinlich nicht. Ich habe folgende Formel:

=(SUMPRODUCT((OFFSET(INDIRECT(ADDRESS(Zeile;18;;;"Sheet1"));;;;15)=G13) *(OFFSET(INDIRECT(ADDRESS(Zeile;33;;;"Sheet1"));;;;15))*(INDIRECT(ADDRESS(Zeile; ROUNDDOWN(COLUMN(AG:AU)/3;0)+1;;;"Sheet1")))*(VLOOKUP(G13;Rohstoffe;3;0)))*$G$7*$G$8*2) /10^8

'Zeile' ist ein benannter Bereich.

Das Ergebnis der Formel ist #VALUE

Führe ich alle Schritte in der Formel einzeln aus (markieren -> F9), dann kommt das richtige Ergebnis raus

(OFFSET(INDIRECT(ADDRESS(Zeile;18;;;"Sheet1"));;;;15)=G13)*(OFFSET(INDIRECT(ADDRESS(Zeile;33;;;"Sheet1"));;;;15)) = {0\15\0\100\0\0\0\0\0\100\0\0\0\0\0}

(INDIRECT(ADDRESS(Zeile;ROUNDDOWN(COLUMN(AG:AU)/3;0)+1;;;"Sheet1"))) = {10\10\10\8\8\8\6\6\6\13\13\13\18\18\18}

{0\15\0\100\0\0\0\0\0\100\0\0\0\0\0}*{10\10\10\8\8\8\6\6\6\13\13\13\18\18\18}={0\150\0\800\0\0\0\0\0\1300\0\0\0\0\0}

{0\150\0\800\0\0\0\0\0\1300\0\0\0\0\0}*(VLOOKUP(G13;Rohstoffe;3;0))) ={0\139,5\0\744\0\0\0\0\0\1209\0\0\0\0\0}

(SUMPRODUCT({0\139,5\0\744\0\0\0\0\0\1209\0\0\0\0\0})*$G$7*$G$8*2)/10^8=23,0175

Warum funzt das nicht in einem Rutsch mit der Formel?

Hier das Bespiel ('Zeile'=2)als Datei: https://www.herber.de/bbs/user/78918.xls

Danke für eure Tips

Gruß
Bertram

  

Betrifft: AW: (Matrix)Formel von: silex1
Geschrieben am: 14.02.2012 12:49:49

Hallo,

WENNFEHLER() ist uninteressant für XL03, daher kommt es eh zu Fehler #NAME? wenn die Datei geöffnet wird.
Könntest Du mal in Worte fassen, was Du eigentlich mit dieser Formel erreichenwillst?

VG René


  

Betrifft: AW: (Matrix)Formel von: Bertram
Geschrieben am: 14.02.2012 13:35:53

Sorry, hatte übersehen, dass es die Fkt ja noch nicht gibt in den niedrigeren Versionen. Kann man für den Fall aber auch weglassen.

Eigentlich möchte ich die Formel so flexibel wie möglich halten um aus einer Zeile des Sheet1 einen Wert zu berechnen. Das ganze mit möglichst wenig Hilfspalten. Mit mehr Hilfspalten krieg ich das auch hin, aber mich würde eben interessieren, warum mein Konstrukt in Einzelschritten funktioniert, im Ganzen aber nicht.

Gruß
Bertram


  

Betrifft: ich bin raus... von: silex1
Geschrieben am: 14.02.2012 14:07:14

...denn dass Du was berechnen willst, ist schon logisch. Auch ist ein #BEZUG Fehler, der mühseelig ist zu korrigieren.

Ich stelle aber den Beitrag auf offen, sodass andere helfen können!

VG René


  

Betrifft: DeiN()e FunktioN() 'N() bisschen korrigiereN() von: NoNet
Geschrieben am: 14.02.2012 14:24:00

Hallo Bertram,

es fehlt eigentlich N()ur eine kleine Korrektur : ein einfaches N ;-) :

=(SUMPRODUCT((OFFSET(INDIRECT(ADDRESS(Zeile;18;;;"Sheet1"));;;;15)=G13) *(OFFSET(INDIRECT(ADDRESS(Zeile;33;;;"Sheet1"));;;;15)) *N(INDIRECT(ADDRESS(Zeile;ROUNDDOWN(COLUMN(AG:AU)/3;0)+1;;;"Sheet1"))) *(VLOOKUP(G13;Rohstoffe;3;0)))*$G$7*$G$8*2) /10^8


Gruß, N()oN()et ;-)


  

Betrifft: AW: DeiN()e FunktioN() 'N() bisschen korrigiereN() von: Bertram
Geschrieben am: 14.02.2012 15:38:50

Hallo NoNet,

danke. Das ist genau was ich suchte. Nur ein Problem: Wozu brauch ich das N()? Was es generell tut weiß ich schon, aber in dem Fall????? Wenn ich's verstehe kann ich mir das nächste Mal das Rumprobieren sparen:-)

Danke und Gruß
Bertram


  

Betrifft: N() liefert u.a. eine Werte-MATRIX als Resultat von: NoNet
Geschrieben am: 14.02.2012 16:12:21

Hallo Bertram,

das Problem tritt häufig in Verbindung mit MATRIZEN auf : Einige Funktionen liefern als Ergebnis nur einen einzelnen Wert als Resultat und keine entsprechende Matrix.

Die Funktion N() wandelt eigentlich Zellinhalte in numerische Werte, hat aber den positiven Nebeneffekt, dass sie auch eine MATRIX als Ergebnis liefert - sie ist hier also nur ein Hilfskonstrukt (Quasi "Mittel zum Zweck").

Schau dir mal dieses Beispiel an : In den Zellen B3:E3 sollen die Werte aus dem gelben Bereich summiert werden, indem der Bereich per BEREICH.VERSCHIEBEN() angegeben wird :

OHNE die Funktion N() wird das Ergebnis aus BEREICH.VERSCHIEBEN() nicht als MATRIX interpretiert, sondern nur als einzelner Wert der Zelle A3 und daher nur diese erste Zelle "addiert" (bei SUMME als MATRIX) bzw. es erscheint ein Fehler #WERT! bei SUMMENPRODUKT()).
Bei Verwendung der Funktion N() wird das Resultat aus BEREICH.VERSCHIEBEN() jeweils als MATRIX interpretiert, daher ist das Ergebnis in diesen beiden Fällen korrekt :

ABCDE
1
2
3#WERT!
4
5
6
7

Funktionen im Tabellenblatt :
Zelle Formel 
D3   =SUMMENPRODUKT((BEREICH.VERSCHIEBEN(A2;ZEILE($1:$5);0))*1) 
E3   =SUMMENPRODUKT(N(BEREICH.VERSCHIEBEN(A2;ZEILE($1:$5);0))*1) 

MATRIX-Funktionen (mit Strg+Shift+ENTER bestätigen)
ZelleFormel
B3   {=SUMME(BEREICH.VERSCHIEBEN(A2;ZEILE($1:$5);0)
C3   {=SUMME(N(BEREICH.VERSCHIEBEN(A2;ZEILE(1:5);0))

Tabelle eingefügt mit Syntaxhighlighter 4.15

Gruß, NoNet


  

Betrifft: AW: N() liefert u.a. eine Werte-MATRIX als Resultat von: Bertram
Geschrieben am: 14.02.2012 16:35:09

Hallo NoNet,

vielen Dank für die tolle Erklärung. Das meine Matrix bzw. der (fälschlich) zurückgegebene Wert mein Problem ist dachte ich mir schon, allerdings wusste ich noch nicht, dass N() eine Matrix zurückgibt. Das ist sehr hilfreich. Falls das in der Hilfe steht, hab ich es ständig überlesen:-)

Nochmals Danke und Gruß

Bertram


  

Betrifft: Das, was hier N leistet, war offensichtl so ... von: Luc:-?
Geschrieben am: 15.02.2012 01:22:47

…ursprgl gar nicht vorgesehen, Bertram,
und ist deshalb auch nicht in der xlHilfe zu finden, sondern ein Ergebnis der „xlForschung”. ;-)
Einige Fktt liefern - wie NoNet schreibt - (scheinbar) nur einen Wert. Die allgemeine Matrix-Steuerung von Xl sorgt dann wohl dafür, dass die Werte auch zellabhängig gewechselt wdn. Allerdings ist nicht einzusehen, warum eine Fkt wie bspw BEREICH.VERSCHIEBEN oder INDIREKT nur einen Wert zurückgeben soll, wenn sie doch einen ganzen Zellbereich behandelt! Das im FmlAssi nur 1 Wert angezeigt wird, ist dafür kein Beweis, das kann an der Pgmierung desselben liegen wie auch in anderem Zusammenhang zu bemerken ist.
Es wird eher so sein, dass das (in Zusammenarbeit mit der allgemeinen Matrixsteuerung) zurückgegebene Datenfeld in diesen Fällen eine Form hat, die von den meisten Fktt nicht verarbeitet wdn kann. Solche Datenfelder kann man mit VBA auch leicht selber erzeugen. In bestimmten Fällen kann N mit diesen Datenfeldern umgehen und sie dabei in normale DFelder transformieren, aber nicht in allen! IdR sind das dann Vektoren (bei INDIREKT auch als Einzelwerte über mehrere Blätter → 3D-Vektor; bei Vektoren klappt's dann nicht mehr → 3D-Matrix, von Matrizen ganz zu schweigen, aber die könnten ja ohnehin nicht ohne Tensorprodukt bzw Vglbares 2dimensional dargestellt wdn).
Gruß Luc :-?


  

Betrifft: Hier noch ein Bsp für N-Einsatz, ... von: Luc:-?
Geschrieben am: 15.02.2012 02:00:29

…das du in diesem Thread gg Ende finden kannst.
Luc :-?


  

Betrifft: AW: Hier noch ein Bsp für N-Einsatz, ... von: Bertram
Geschrieben am: 15.02.2012 15:46:35

Hallo Luc,

danke noch für deine Erläuterungen und den Link. Normalerweise würde ich das Ganze eh mit VBA machen, aber ich wollte es diesmal mit Formeln lösen:-)

Gruß
Bertram


  

Betrifft: Bitte sehr! Gruß owT von: Luc:-?
Geschrieben am: 16.02.2012 12:23:58

:-?


Beiträge aus den Excel-Beispielen zum Thema "(Matrix)Formel"