Morrn, Fml-Cracks;
für diejenigen, die das Thema interessiert und bisher (in meiner Beitragsreihe) eine (normal-)praktische Anwen­dung vermisst haben, reiche ich hiermit ein etwas komplexeres TabellenBsp nach. Die konstruierte Aufgabe desselben besteht darin, vor­kal­ku­lierte Preise bestimmter Produkt­gruppen mit einer Gruppe vor­be­stimm­ter Fak­toren so zu multi­pli­zieren, dass sich ein optimaler Preis ergibt, der im Gruppen­Durch­schnitt unter dem Gruppen­Durch­schnitt vglbarer Markt­Preise liegt. Um zu erreichen, dass der größere Teil dieser Preise unter dem Vgls­Preis liegt, wurde noch ein weiteres Krite­rium ein­ge­führt, das den vor­be­stimm­ten opti­malen Faktor so ver­ändert, dass dieses Ziel möglichst vor­teil­haft erreicht wird.
Es gibt sicher andere und bessere Ver­fahren, um so etwas zu erreichen, aber mir ging's hier­bei in 1.Linie um die Demon­stration einer speziellen Fktions­weise der Xl-Fkt INDEX in einer pluralen Matrix­Fml, die darauf hinaus­läuft, dass Xl, in welcher Form auch immer, einen 4dimen­siona­len Tensor bildet, der aller­dings so nicht voll­ständig auf einen Zell­Bereich abge­bildet wdn kann (was hier auch nicht erfor­der­lich ist). Dazu wird die 1.Matrix per INDEX in Einzel­werte [Skalare = Tenso­ren(0,0)] zer­legt, denen in der Multi­pli­kation jeweils eine Kopie der 2.Matrix ggüber­gestellt wird. In dieser Form wird die plurale Matrix­Fml nur 1× durch­laufen und zeigt (und gibt weiter!) nur die 1.Werte jeder der ein­zelnen Ergebnis­Matrizen. Durch ein weiteres INDEX wird dieses Ergeb­nis erneut ver­viel­fältigt (der Ergeb­nis-Tensor quasi „ent­faltet“), so dass alle seine Werte an eine andere Fkt als Argu­ment weiter­gege­ben wdn können. Dies geschieht in einem Rechen­Durch­lauf pro Zelle, wobei von Xl auto­ma­tisch der jeweils pas­sende Teil des Ergeb­nis-Ten­sors (eine Sekun­där­Matrix, vgl vor­herige Bei­träge) aus­ge­wählt wird.
Derartige Wiederholungen realisiert INDEX über sein 4.Argument, wobei egal ist, ob das 1.Argu­ment ein Bereichs­Bezug oder ein Datenfeld ist. Es müssen nur soviel 1en in diesem Argu­ment ver­wendet (bzw erzeugt) wdn, wie Wieder­holun­gen erfor­der­lich sind, also für jeden Ergeb­nis­wert eine. Die Posi­tio­nierung der Ergeb­nis­werte folgt dabei der 1.Matrix.
In diesem Zusammenhang muss darauf hingewiesen wdn, dass diese Berechnungen nicht auf eine andere Kal­kula­tions­Software übertragen wdn können. Deren FmlText-Inter­preter arbeiten idR anders als der von Xl und auch einige Fktt (auch gleich­namige!) wie bspw INDEX und MTRANS arbei­ten anders und können des­halb hier andere Ergeb­nisse liefern. Das trifft ins­beson­dere auf alles zu, was irgend­wie mit Matrix­Fmln (und Matrix­Kon­stan­ten erst recht!) zu tun hat.
Gleichzeitig wird mit dem nachfolgenden Bsp gezeigt, wie man Leerzellen aus der Berechnung aus­schalten, und dass man erfor­der­liche Hilfs­zellen (INDEX in dieser Form benötigt sie unbe­dingt!) uU sinn­voll in die Gesamt­Dar­stel­lung inte­grieren kann, was einem Ver­stecken der­selben in Aus­blen­dungen allemal vor­zu­ziehen wäre:

	A	B	C	D	E	F	G	H	I	J
1	Faktor- primär angenommene Multiplikationsfaktoren  ZellFormeln: * Gruppe Faktor1 Faktor2 Faktor3 Faktor4  C9:F11,C12:F14: {=RUNDEN(MITTELWERT(INDEX(WENNFEHLER(INDEX(B3:E5;ZEILE(1:3); FGruppe1 85% 90% 95% 100%                                  SPALTE(A:D))*INDEX((1/C28:E32)^-1;;;1^B3:E5);"");;;1^B3:E5));2)} FGruppe2 102% 105% 107% 110%  G9:J11,G12:J14: {=SUMME(INDEX(--(INDEX(B3:E5;ZEILE(1:3);SPALTE(A:D))*INDEX FGruppe3 112% 114% 117% 120%                                  (C28:E32;;;1^B3:E5)>C18);;;1^B3:E5))}           * Formel des jeweils 2. genannten Zielbereichs in Analogie zur gezeigten des 1. gebildet. Produkt- Faktor- Mittelwert faktorierter Produktpreise Anzahl faktorierter Produktpreise > Vglswert Gruppe Gruppe Faktor1 Faktor2 Faktor3 Faktor4 Faktor1 Faktor2 Faktor3 Faktor4 X1 FGruppe1 90,46 95,79 101,11 106,43 0 0 0 0 X1 FGruppe2 108,56 111,75 113,88 117,07 0 1 2 4 X1 FGruppe3 119,20 121,33 124,52 127,71 6 7 10 11 X2 FGruppe1 197,34 208,95 220,56 232,17 0 0 1 4 X2 FGruppe2 236,81 243,78 248,42 255,38 4 5 5 5 X2 FGruppe3 260,03 264,67 271,64 278,60 5 5 6 6           C19,D19: {=MAX(C9:F11*(C9:F11<C18))} Vergleichswert u. Faktorermittlung   C20,D20:=SUMMENPRODUKT(--(C9:F11=C19);B3:E5) Produktgruppe X1 X2   C21,D21: {=SUMMENPRODUKT(--(G9:J11=MAX(WENNFEHLER(1/G9:J11;-1))^-1);C9:F11)}  Ø Markt-Bruttopreise 122,54 232,66   C22,D22: {=SUMMENPRODUKT(--(G9:J11=MAX(WENNFEHLER(1/G9:J11;-1))^-1);B3:E5)}  maxkritischer Ø-Preis 121,33 232,17   C23,D23:=(C20-C22)*RECHTS(B23;3)+C22  maxkritischer Faktor 114% 100%   C24,D24:=MITTELWERT(F28:H32)  minkritischer Ø-Preis 111,75 220,56   I24,J24:=ZÄHLENWENN(F28:H32;"<"&C18)&" : "&ZÄHLENWENN(F28:H32;"≥"&C18)  minkritischer Faktor 105% 95%   F28:H32,F33:H35: {=WENNFEHLER(RUNDEN((1/(C28:E32*C23))^-1;2);"")}  opt Faktor Δ 65% 110,9% 98,3%    Anzahlrelation kleinerer zu größeren X1 X2  Ø optim B-ProdPreise 117,98 228,11    optimierten Brutto-Produktpreisen 9 : 5 3 : 3                     Produkt- Produkt kalkulierte Produktpreise optimierte Brutto-Produktpreise     Gruppe Bezeichn ProdVar1 ProdVar2 ProdVar3 ProdVar1 ProdVar2 ProdVar3     X1 Alpha 106 97 91 117,50 107,52 100,87     X1 Beta 117 113 112 129,69 125,26 124,15     X1 Gamma 109 116   120,83 128,59       X1 Delta 105 110 104 116,39 121,94 115,28     X1 Epsilon 92 107 111 101,98 118,61 123,04     X2 Vau 227 238 245 223,03 233,84 240,71     X2 Zeta 235 244   230,89 239,73       X2 Eta 204     200,43
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35

Die Tabelle kann ohne oberen und linken Rand kopiert wdn, sollte dann aber auch an dem­entsprd Stelle in ein leeres Blatt eingefügt wdn!
Quasi als Nachtrag zur 4.Fortsetzung dieses Themas stelle ich nachfolgend die neue Version1.1 der UDF TensorProd zV. Die vor­he­rige hat nur Ganz­zahlen berück­sich­tigt. Aller­dings wird sie für das TabBsp nicht benötigt, würde die Lösung gar erschweren. Aber, wer gern alle Werte des gebildeten 4Tensors in der üblichen Form als größere Matrix anzei­gen möchte, kann sie dafür mit folgder plu­ralen Matrix­Fml nutzen: {=TensEx(TensorProd(B3:E5;C28:E32;2))}
Ihre 180 Ergebniswerte benötigen 5×3 Zeilen und 3×4 Spalten. Die hier­bei auch _ erfor­der­liche UDF TensEx ist eben­falls in zurück­lie­genden Bei­trägen zu finden: Rem Ermittelt Kronecker-Tensor-Produkt von 2 Matrizen als 2/2d-Array, ' was einem 4-Tensor gleichkommt; Arg1-2: jedes Element v.Arg1 wird ' mit allen Elementen von Arg2 multipliziert u.die Ergebnismatrizen ' in einer Matrix gleicher Größe wie Arg1 elementweise gespeichert; ' Arg3: fehlt/0 Ausgabe der 1.Subebene (xlSimulation) um F-Werte zu ' vmeiden, ±1/2 Rückgabe aller Werte als Texte in Matrixkonstanten- ' form (Sekundärmatrizen), ±2 erzeugt US-Notation, die v.UDF TensEx ' benötigt wird (m. dieser können die Texte zu 2d-Matrizen üblicher ' Form expandiert bzw auf d.Subebenen d. 4d-Modells extendiert wdn. ' Vs1.1 - LSr:CyWorXxl -cd:20180414 -1pub:20180414h -lupd:20180511t Function TensorProd(ByVal Matrix1, ByVal Matrix2, Optional ByVal alsMxKText _ As VbTriState) Dim cx1 As Long, cx2 As Long, ix As Long, rx1 As Long, rx2 As Long, _ zlErg() As String, el, erg, it, zwErg As Variant, _ MxCSep, MxDSep, MxRSep, txMxForm As Variant On Error GoTo fx With Application MxDSep = Array(".", .International(xlDecimalSeparator)) MxCSep = Array(",", .International(xlColumnSeparator)) MxRSep = Array(";", .International(xlRowSeparator)) txMxForm = Array("{#}", .International(xlLeftBrace) & "#" & _ .International(xlRightBrace)) End With alsMxKText = -Abs(alsMxKText): erg = Matrix1: zwErg = Matrix2 Matrix1 = erg: Matrix2 = zwErg ReDim erg(UBound(erg, 1) - LBound(erg, 1), UBound(erg, 2) - LBound(erg, 2)) ReDim zwErg(UBound(zwErg, 1) - LBound(zwErg, 1), UBound(zwErg, 2) - _ LBound(zwErg, 2)) For Each el In Matrix1 For Each it In Matrix2 If alsMxKText = vbUseDefault Then zwErg(rx2, cx2) = Replace(CStr(el * it), MxDSep(1), MxDSep(0)) Else: zwErg(rx2, cx2) = el * it End If rx2 = (rx2 + 1) Mod (UBound(zwErg, 1) + 1) cx2 = (cx2 - CInt(rx2 = 0)) Mod (UBound(zwErg, 2) + 1) Next it If CBool(alsMxKText) Then ReDim zlErg(LBound(zwErg, 1) To UBound(zwErg, 1)) For ix = LBound(zwErg, 1) To UBound(zwErg, 1) zlErg(ix) = Join(WorksheetFunction.Index(zwErg, ix + 1, 0), _ MxCSep(alsMxKText + 2)) Next ix erg(rx1, cx1) = Replace(txMxForm(alsMxKText + 2), "#", _ Join(zlErg, MxRSep(alsMxKText + 2))) Else: erg(rx1, cx1) = zwErg End If rx1 = (rx1 + 1) Mod (UBound(erg, 1) + 1) cx1 = (cx1 - CInt(rx1 = 0)) Mod (UBound(erg, 2) + 1) Next el If alsMxKText = vbFalse Then With Application.Caller 'Simulation der Xl-Ausgabe m.INDEX Debug.Print .Rows.Count - 1; .Columns.Count - 1 ReDim zwErg(.Rows.Count - 1, .Columns.Count - 1) For rx1 = 0 To .Rows.Count - 1 For cx1 = 0 To .Columns.Count - 1 zwErg(rx1, cx1) = erg(rx1, cx1)(0, 0) Next cx1 Next rx1 End With TensorProd = zwErg Else: TensorProd = erg End If fx: If CBool(Err.Number) Then TensorProd = CVErr(Err.Number) End Function Sachlich-fachliche Kommentare und Anmerkungen zur vorgestellten Arbeit sind durchaus erwünscht; es können auch alternative Lösungen vorgestellt wdn.
Ansonsten wünsche ich allen das übliche FroPf, obwohl wir es hier bei uns wohl wetter­mäßig mit am besten getroffen zu haben scheinen…
Gruß, Luc :-?