Excel - Matrixformel

[nordstern]

hi,

ich habe gerade ein Excelproblem. Normalerweise würde mir mein Vater helfen, der ist aber grad auf erstmal unbestimmte Zeit im Krankenhaus (nur Beobachtung).

Ich erweiter gerade meine Excelkentnisse. Hintergrund ist neben einer zukünftigen beruflichen Nutzung auch persönliches Interesse weil ich oft merke das ich Tabellen nicht so umsetzen kann wie ich das gerne hätte.

Nach langem Knobeln bin ich auf die Idee einer Matrixformel gestoßen. Leider will die nicht so wie ich will. Nachdem ich die Excelhilfe gelesen habe, die Officehilfe im Internet und etwas gegoogelt habe kam ich zu dem Schluss, das ich damit nichts anfangen kann weil das alles auf Einsteigerniveau ist. Kann mir hier vielleicht jemand helfen?

Also der Grundgedanke war es 3 Tabellen mit einander zu verknüpfen. In Tabelle 1 stehen die Daten, in Tabelle 2 was mit den Daten getan werden soll und in Tabelle 3 soll das Ergebnis stehen. Als Beispiel habe ich einfach mal die Heeresstruktur aus HoI3 herrangezogen weil mir auf die schnelle nichts besseres eingefallen ist. Hier mal bildlich:
tabelle 1: Hier stehen in den Zeilen die Brigaden, in den Spalten welche und wieviele Truppentypen in der Brigade vertreten sind
Inf lArt sArt SP lP ...
Inf 3000 60 20 0 3 ...
Art 0 180 60 0 0
Mech
SFL
...

In Tabelle 2 steht dann aus was sich die unterschiedlichen Divisionen zusammensetzen:
Inf Mech
Inf 3 Mech 3
Art 2 SFL 2
...

Nun soll die Formal in Tabelle 3 folgendes tun:
Wenn am Anfang der Zeile Inf steht soll er in tabelle 2 die Inf-Division Inf und die Brigadevorgaben benutzen. Er muss hierbei erkennen wenn ich z.b. Art durch SFL ersetze oder eine weitere Brigade hinzufüge wie z.b. Mech in einer Inf-Division (Ich lass drunter 2 Felder frei). Und dann nimmt er die Werte und verrechnet sie mit der Zeile der Brigade. Also am Beispiel der Inf-Division soll er folgendes machen:
Inf lArt sArt SP lP
Inf 3*3000+1*0 3*60+180*1 3*20+1*60 3*0+1*0 3*3+1*0

Generel geht das recht einfach so. Die Schwierigkeit die ich mir nun stelle ist was wenn die Formal spezifisch auf Änderungen in der Hierarchie (später bei Korps, Armeen, HG, etc) und Divisionsstruktur reagieren soll. Wenn sie also selber erkennen soll das sich die Brigadeart geändert hat. Bei der einfachen Methode muss man dann alles wieder umschreiben in den Formeln, weil die Zeilen dann die falschen sind.

Mein versuch sag wie folgt aus:
=Summe(((Tabelle 1 = MTRANS(Bereich.Verschieben (1.Spalte Tabelle 2;0;Vergleich (Zeilenwert Tabelle 3; Spaltenbeschriftung Tabelle 2;0)-1)))*Tabelle 1* MTRANS ( Bereich.Verschieben (Zahl des Zeilenwert 1.Spalte Tabelle2;0;Vergleich(Zeilenwert Tabelle 3;Spaltenbeschriftung Tabelle 2;0)-1)))

nur leider funktioniert die irgendwie nicht. Und ich sollte diese Rechnung spätestens nächste Woche können für mein Praxissemester. Die Formal gibt #NV aus. Hier mal das Orginal:
=SUMME(((B2:L8=MTRANS(BEREICH.VERSCHIEBEN(O12:O14;0;VERGLEICH(A15;O11:V11;0)-1)))*B2:L8*MTRANS(BEREICH.VERSCHIEBEN(P12:P14;0;VERGLEICH(A15;O11:V11;0)-1))))
Die gschweiften Klammern einer Matrix verschwinden ja beim kopieren, da das System sie selbst erstellt nachdem ich STRG - SHIFT - ENTER gedrückt habe.


Danke für eure Mühe.

nordstern

[Horgan]

Ich bin durchaus sehr fit in Excel, was die Formeln und Programmierung betrifft, gern auch im Austausch mit externen Datenquellen etc., aber aus deinem Text werde ich so nicht schlau - müsste man sehen können. Was genau willst Du eigentlich konkret machen? Die Kampfstärke einer Division berechnen, welche aus variablen Einheiten besteht oder schlicht Werte durch die Gegend schieben?

Die Frage ist, ob dein Ansatz richtig ist.
- MTRANS transponiert einen Bereich - aus XY wird YX (um mal bildlich in Koordinatenachsen zu sprechen),
- VERGLEICH liefert Dir die Position, aber nicht Wert eines gesuchten Elements
- VERSCHIEBEN und BEREICH sind mir in der Form auch nicht geläufig

Wenn Du für variable Selektionskriterien Berechnungsgrundlagen aus 1+n Matrizen ermitteln und damit Berechnungen durchführen willst, dann sollte SVERWEIS ausreichend sein. Wenn es dabei für deine Übersicht Not tut, kannst Du innerhalb der Matrizen auch Bereiche benennen - musst Du aber nicht.

Aus deiner Transponiererei und dem Verschieben werde ich im Kontext nicht schlau - hier fehlt eindeutig mehr Input von Dir.

[nordstern]

mTrans habe ich genutzt da ich die betreffende Tabelle anders als ich es für Matrizen hätte tun sollen formatiert habe. Wenn ich die Tabelle aber spiegel brauch ist das nicht mehr.

Ich nutze die Vergleichsfunktion nicht zum vergleichen von Matrizen sondern zur Posotionsbestimmung... ich erklärs dir mal:


B2:B8*MTRANS(BEREICH.VERSCHIEBEN(P12:P14;0;VERGLEICH(A15;O11:U11;0)-1)))

Ich fang mit den zweiten Teil einfach mal an, weil der die eigentliche Rechnung vornimmt. Als erstes hier den Vergleich. Er sucht die Position der Divisionsbezeichnung... hier ist Infanterie die 1. Durch das -1 nachträglich verschiebt die Bereich.Verschiebenfunktion den Datenbereich um 0 Spalten nach links, uninteressant. Wenn ich hier aber eine Mech drinstehen habe steht Mech an der 5.Position. Damit gibt er mir eine 5 aus. Wenn er nun aber diese Tabelle um 5 verschieben würde, währe der Mecheintrag auf Position 0 und damit außerhalb des Datenbereiches. Durch die -1 verschiebt er die Tabelle aber "nur" um 4 und damit auf Position 1. Diese Werte multipliziere ich nun mit der Brigadematrix. Dadurch erhalte ich erstmal noch keinen Filter. Aber nun greift der 1.Teil der Matrix:


A2:A8=MTRANS(BEREICH.VERSCHIEBEN(O12:O14;0;VERGLEICH(A15;O11:U11;0)-1)))

Hier erstelle ich nun den eigentlichen Filter: In der Spalte A2:A8 stehen die Brigadetypen, nun nutze ich die Vergleichsfunktion. Die Formel steht in B15, der Divisionstyp steht in A15. O11 bis U11 stehen die Überschriften für die Divisionsnamen, darunter ihre Zusammensetzung. Die Vergleichsfunktion dient dazu das er die feststellt an welcher Position sich die gesuchte Division befindet. Am Beispiel der Infanterie gibt die Formel hier die 1 für die 1.Position aus. und verschiebt nun die gesamte Tabelle der Divisionsstruktur um 1-1 Felder nach links, also bleibt alles beim alten. Weshalb ich die Formel aber eigentlich nutze ist was anderes. Die Vergleichsformel gibt True oder False als Wert aus. Und False wird als 0 definiert und True als 1 von Excel. Damit habe ich eine Matrix die überall wo nicht in beiden Feldern Inf steht eine 0 und nur an einer Stelle eine 1. Und diese wird nun multipliziert mit der Matrix des 2.Teils und damit fliegen alle Brigaden die nicht Wahr sind raus und es bleibt nur noch die Infanteriebrigade oder eben Artillerie sind, weil in der Infanteriespalte "nur" die Inf und Art angegeben ist.

Soweit zumindest der Plan...


Eine Division besteht aus Brigaden, jeder Brigade sind Einheitentypen zugeordnet wie: Infanterie, leichte Artillerie, schwere Artillerie, Jagdpanzer, etc und zwar mehrere dieser Werte. So besitzt z.b. eine Infanteriedivision neben Infanterie auch leichte Artillerie, LKWs, PaKs, Panzerwagen und leichte Panzer. Und damit geht es nicht mehr mit Sverweis. Weil er im Falle von Inf nicht auf eine Zahl sondern auf eine Zeile zugreifen muss und die mit einer anderen Zeile verrechnen soll. Ich benutze oben unterschiedliche Namen. In der Excel sind die aber gleich, habe das hier nur zu Verständniszwecken anders genannt.

[Karbe]

Excel stößt sehr schnell an Grenzen.
Über kurz oder lang solltest du darüber nachdenken, dich mit einem relationalen Datenbank-System zu beschäftigen - damit stünden dir wesentlich mehr Möglichkeiten zur Verfügung.

Grüße

[nordstern]

ich weis.. aber es müsste doch mit Matrix-Rechnung gehen. Wenn ich "manuell" rechne tu ich im Grunde auch nichts anderes wie Matrixrechnen.

Und ich finde den Fehler nicht. Vielleicht fällt mir am Sonntag noch n zweiter Rechenweg ein. Wenn jemand ne Idee hat kann er sie gerne hier posten, ich probiers aus.
Das Problem an Matrixrechnungen ist, das man die nur sehr schwer "nachvollziehen" kann und damit Fehler nur recht umständlich findet.