Microsoft Excel

Herbers Excel/VBA-Archiv

Werte aus Matrix in Tabelle schreiben

Betrifft: Werte aus Matrix in Tabelle schreiben von: Jaroslav
Geschrieben am: 13.08.2020 14:20:49

Hallo Zusammen,

ich möchte per Makro Werte aus einer Matrix spaltenweise in eine Tabelle zurückschreiben.

Als Matrix habe ich bspw.

---------- 2021 2022 2023 2024
Anlage a 4 --- 3 --- 2 --- --- 0
Anlage b 1 --- 2 --- 3 ------ 2
Anlage c 6 --- 9 --- 6--- --- 14

Nun möchte ich die Werte in den jeweiligen Jahren in eine Tabelle auf einem anderen Blatt speichern die wie unten aufgebaut ist. Er soll z.B.s jeweils die Maßnahmen der Anlage a als "1" in die Tabelle unten schreiben und das so lange bis die 4 Maßnahmen eingetragen sind und dann weiter zur nächsten Anlage gehen.

Anlage
----------- 2021 --- 2022 2023 2024
Anlage a Teil 1 1 --- 1 1----
Anlage a Teil 2 1 --- 1 1
Anlage a Teil 3 1 --- 1 -
Anlage a Teil 4 1 --- - -
Anlage b Teil 1 1 --- 1 --- 1 --- 1
Anlage b Teil 2 --- 1 --- 1 --- 1
Anlage b Teil 3 --- --- 1---
Anlage c Teil 1 1 --- 1 --- --- 1

usw.
Ich hoffe man kann erkennen was ich vor habe.
Kann mir da bitte jemand helfen?

VG

Jaro


Betrifft: AW: Werte aus Matrix in Tabelle schreiben
von: Jaroslav
Geschrieben am: 13.08.2020 14:30:14

Anbei mal das Beispiel als Excel.
Hoffe das hilft.
Ich würde auf eine DoWhile oder For Schleife mit IF Bedingung und einem Hilfszähler tippen aber ich hab grad überhaupt keinen Ansatz
https://www.herber.de/bbs/user/139613.xlsx

Betrifft: AW: mit N(), ZÄHLENWENN() und SVERWEIS() ...
von: neopa C
Geschrieben am: 13.08.2020 14:43:50

Hallo Jaro,

... in M3: =N(ZÄHLENWENN($K$3:$K3;$K3)<=SVERWEIS($K3;$A:$E;SPALTE(B1);0)) und diese Formel nach rechts und unten ziehend kopieren.

Gruß Werner
.. , - ...

Betrifft: AW: mit N(), ZÄHLENWENN() und SVERWEIS() ...
von: Jaroslav
Geschrieben am: 13.08.2020 15:01:23

Das klappt leider nicht. Bist du sicher, dass die B1 hinten stimmt?
Wenn ich es runter ziehe, dann nimmt er irgendwann den Wert aus Spalte B17 oder so und da ist ja garnichts.
Er soll ja den Wert 4 aus der Zelle B3 nehmen und dann eine 1 in die Tabelle (M3) schreiben dann wäre der Wert in B3 ja nur noch 3 und dann soll er die nächste, also M4 eine 1 reinschreiben so lange bis die 4 Werte aufgebraucht sind.

Betrifft: AW: also bei mir schon; und ja B1 stimmt owT
von: neopa C
Geschrieben am: 13.08.2020 15:12:31

Gruß Werner
.. , - ...

Betrifft: Informiere dich über ZEILE() u.SPALTE()! owT
von: Luc:?
Geschrieben am: 14.08.2020 01:00:38

:-?

Betrifft: AW: Informiere dich über ZEILE() u.SPALTE()! owT
von: Jaroslav
Geschrieben am: 17.08.2020 08:26:09

Ok sorry funktioniert doch. So ganz verstanden habe ich die Formel zwar nicht, aber trotzdem vielen vielen Dank:-)

Betrifft: AW: und was ist für Dich noch nicht klar? owT
von: neopa C
Geschrieben am: 17.08.2020 10:08:09

Gruß Werner
.. , - ...

Betrifft: AW: und was ist für Dich noch nicht klar? owT
von: Jaroslav
Geschrieben am: 17.08.2020 11:07:43

So wie ich die Formel verstehe: Wenn die Formel "Wahr" ist gibt er eine 1 raus. Hierzu vergleicht er den Namen der Anlage und guckt in der Matrix wieviel Werte er hat. Aber was macht das kleinergleich vor dem SVerweis und wie zählt er denn runter wieviele Maßnahmen er schon genutzt hat? Er soll ja eine 1 reinschreiben solange bis er die Anzahl der Maßnahmen "verbraucht" hat.

Habe jetzt aber noch ein Problem:
Ich habe nicht nur verschiedenen Anlagen sondern auch verschiedene Maßnahmen in den einzelnen Anlagen. Ich habe jede Matrix für jeweils eine Maßnahme auf einem Tabellenblatt.
Er soll jetzt die 1 die Gesamttabelle in ein Auswertungsblatt schreiben.

Kann ich die Zählenwenn Funktion daher auch noch um andere Bereiche bzw.Kriterien erweitern?
Also so etwas wie: Wenn der Anlagenname gleich ist und der Maßnahmenname gleich ist dann soll er eine 1 in die Zelle schreiben, so lange bis die Anzahl der Maßnahmen die in der Matrix stehen aufgebraucht ist.

Betrifft: AW: die Wandlung des WAHRheitswertes ...
von: neopa C
Geschrieben am: 17.08.2020 13:37:44

Hallo Jaroslav,

... in eine 1 für WAHR bzw. eine 0 für FALSCH erfolgt durch die Funktion N().

Zu Deiner Folgefrage. Wahrscheinlich reicht Dir dafür anstelle ZÄHLENWENN() die Funktion ZÄHLENWENNS(). Aber genau kann ich Dir erst sagen, wenn Du eine entsprechende Beispieltabelle hier einstellst.

Gruß Werner
.. , - ...

Betrifft: AW: die Wandlung des WAHRheitswertes ...
von: Jaroslav
Geschrieben am: 17.08.2020 14:00:22

Hallo Werner,

vielen dank für die Hilfe.
Anbei nochmal eine Beispieltabelle. Habe jetzt erstmal nur die Werte von der Anlage a angepasst.
Wenn es für die funktioniert dann glaube ich kann ich das entsprechend anwenden.
https://www.herber.de/bbs/user/139662.xlsx

Betrifft: AW: Deine Vorgabeergebniswerte für 2021 ...
von: neopa C
Geschrieben am: 17.08.2020 14:22:26

Hallo Jaroslav,

... kann ich voll nachvollziehen nicht aber die für die Folgejahre. Diese bedürfen noch Deiner näheren Erläuterung. Momentan würde sich nämlich mein Ergebnis noch wie folgt ergeben.

Arbeitsblatt mit dem Namen 'Auswertung'
 KLMNOP
2   202120222023
3Anlage aTeil 1Maßnahme 1111
4Anlage aTeil 2Maßnahme 1111
5Anlage aTeil 3Maßnahme 2111
6Anlage aTeil 4Maßnahme 3111
7Anlage aTeil 5Maßnahme 1000
8Anlage aTeil 6Maßnahme 3011
9Anlage aTeil 7Maßnahme 2011
10Anlage aTeil 8Maßnahme 2000
11Anlage aTeil 9Maßnahme 2000
12Anlage aTeil 10Maßnahme 2000
13Anlage aTeil 11Maßnahme 3000
14Anlage aTeil 12Maßnahme 3000
15Anlage aTeil 13Maßnahme 1000
16Anlage aTeil 14Maßnahme 1000
17Anlage aTeil 15Maßnahme 1000
18Anlage aTeil 16Maßnahme 1000
19Anlage aTeil 17Maßnahme 3000
Verwendete Systemkomponenten: [Windows (32-bit) NT 6.02] MS Excel 2010
Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg


Gruß Werner
.. , - ...

Betrifft: AW: Deine Vorgabeergebniswerte für 2021 ...
von: Jaroslav
Geschrieben am: 17.08.2020 15:35:34

Ich glaube ich weiß was du meinst.
Allgemein ist es so, dass jede Zeile in der Auswerteliste genau einer Maßnahme in einem bestimmten Jahr entspricht.
Demnach wäre für 2021 in Anlage a 2xMaßnahme1 , 1x Maßnahme 2 und 1x Maßnahme 3

Für 2022 dann 2x M1 2xM2 und 2xM3.

Insgesamt darf also ein Zeile = eine Maßnahme nicht mehreren Jahren zugeordnet werden.
Es muss geguckt werden: ob Anlagenname, Maßnahmenname und Jahr übereinstimmen und dann soll er da eine 1 reinschreiben, so dass man in der Auswertungstabelle eine Übersicht hat. Es würde auch ein "x" gehen oder so. Es dient nur der Markierung, so dass man sieht welche Maßnahme in welchem Jahr dran ist

Betrifft: AW: dafür dann ...
von: neopa C
Geschrieben am: 17.08.2020 20:07:16

Hallo Jaroslav,

... fällt mir momentan nur ein, mit jedem Jahr die Formel entsprechend zu erweitern.
Die Formelzellen mit dem benutzerdefinierten Zahlenformat: "x";;"-" zu versehen und die Formeln N3:P3 entsprechend nach unten zu kopieren:

Arbeitsblatt mit dem Namen 'Auswertung'
 KLMNOP
2   202120222023
3Anlage aTeil 1Maßnahme 1x--
4Anlage aTeil 2Maßnahme 1x--
5Anlage aTeil 3Maßnahme 2x--
6Anlage aTeil 4Maßnahme 3x--
7Anlage aTeil 5Maßnahme 1-x-
8Anlage aTeil 6Maßnahme 3-x-
9Anlage aTeil 7Maßnahme 2-x-
10Anlage aTeil 8Maßnahme 2-x-
11Anlage aTeil 9Maßnahme 2--x
12Anlage aTeil 10Maßnahme 2--x
13Anlage aTeil 11Maßnahme 3-x-
14Anlage aTeil 12Maßnahme 3--x
15Anlage aTeil 13Maßnahme 1-x-
16Anlage aTeil 14Maßnahme 1--x
17Anlage aTeil 15Maßnahme 1--x
18Anlage aTeil 16Maßnahme 1---
19Anlage aTeil 17Maßnahme 3--x

ZelleFormel
N3=WENNFEHLER(N(ZÄHLENWENNS(K$3:K3;K3;M$3:M3;M3)<=SVERWEIS(K3;INDIREKT("'"&WECHSELN(M3;"Maßnahme";"Matrix")&"'!B:F");SPALTE(B1);0));"")
O3=WENN(SUMME($N3:N3)=1;0;WENNFEHLER(N(ZÄHLENWENNS(K$3:K3;K3;M$3:M3;M3;N$3:N3;0)<=SVERWEIS(K3;INDIREKT("'"&WECHSELN(M3;"Maßnahme";"Matrix")&"'!B:F");SPALTE(C1);0));""))
P3=WENN(SUMME($N3:O3)=1;0;WENNFEHLER(N(ZÄHLENWENNS(K$3:K3;K3;M$3:M3;M3;N$3:N3;0;O$3:O3;0)<=SVERWEIS(K3;INDIREKT("'"&WECHSELN(M3;"Maßnahme";"Matrix")&"'!B:F");SPALTE(D1);0));""))
Verwendete Systemkomponenten: [Windows (32-bit) NT 6.02] MS Excel 2010
Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg


Gruß Werner
.. , - ...

Betrifft: AW: dafür dann ...
von: Jaroslav
Geschrieben am: 18.08.2020 08:16:50

Das sieht ja schonmal sehr gut aus danke.
KAnsnt du mir bitte noch erklären was das Wennfehler das indirekt und Wechseln in der Formal bewirken, also warum diese ergänzt werden müssen?
Denn du beziehst dich ja auf die Matrix auf der ersten Seite aber die eigentliche Matritzen sind ja auf den Tabellenblättern 2-4.
Kann ich da auch direkt drauf verweisen?

Betrifft: AW: hier ist der Einsatz von INDIREKT() ...
von: neopa C
Geschrieben am: 18.08.2020 08:49:08

Hallo Jaroslav,

... unumgänglich, um die jeweiligen korrekten Datenwerte aus den verschiedenen Tabellenblättern zuzuordnen. Mehr zu INDIREKT() sieh mal hier: https://www.online-excel.de/excel/singsel.php?f=24.
Das WECHSELN() hab ich eingesetzt weil Deine jeweiligen Tabellenblätter ja mit "Matrix" beginnend benannt sind und ich so das Wort "Maßnahme" in "Matrix" gewandelt habe. Und zu WENNFEHLER(9 sieh mal hier: https://www.online-excel.de/excel/singsel.php?f=182

Gruß Werner
.. , - ...

Betrifft: AW: hier ist der Einsatz von INDIREKT() ...
von: Jaroslav
Geschrieben am: 18.08.2020 09:13:00

Mmh ok. Nun ist es ja so, dass ich die Auswertung in Wirklichkeit mit meinen echten Kundendaten machen möchte.
Hier sind die Maßnahmen natürlich eindeutig benannt. Die Matritzen unten heißen aber wirklich MAtrix 1 usw und enthalten die Anzahl der Maßnahmen je Maßnahmenklasse und Anlage.

Muss ich demnach in der Formel das WEchseln in die Bezeichnung MAtrix umstellen und dann dafür die echten NAmen angeben?
Ich glaube ich muss mal in der echten TAbelle die Kundendaten verallgemeinern und diese dann hochladen....Vielleicht könntest du dann da einmal gucken

Betrifft: AW:letzteres wäre auf jeden Fall hilfreich(er) owT
von: neopa C
Geschrieben am: 18.08.2020 09:33:24

Gruß Werner
.. , - ...

Betrifft: AW: AW:letzteres wäre auf jeden Fall hilfreich(er) owT
von: Jaroslav
Geschrieben am: 18.08.2020 09:41:48

Hallo WErner,

anbei die anonymisiete echte DAtei.
Ich habe mal alles bis auf drei Anlagen gelöscht.
Es soll wirklich nur der Inhalt der einzelnen Matritzen in die Daten_Lebenslauf - ab Spalte AC also "Neue Daten geschrieben werden".
In Wirklichkeit sind es ein paar mehr Anlagen aber wenn die Formel für die 10 Maßnahmen in den drei Anlagen funktioniert kann ich sie ja runter ziehen

https://www.herber.de/bbs/user/139681.xlsm

Betrifft: AW: aus dieser neuen Datei kann ich entnehmen ...
von: neopa C
Geschrieben am: 18.08.2020 10:42:52

Hallo Jaroslav,

... das Du bisher eine hilfreiche Spalte mir "unterschlagen" hast, die die Auswertung vereinfacht, d.h. nicht je Jahr eine erweiterte Formel notwendig wird. Dazu musst Du lediglich die Spalte "Jahr(neu)" in ZÄHLENWENNS() der Formel analog der bisherigen (für N3) integrieren.

Und bzgl. der korrekten Zuordnung Deiner jeweiligen Maßnahmenklasse zu dem jeweiligen "Matrix #"-Tabellenblatt rate ich zu einer zusätzlichen Liste, in der Du diese Zuordnung erfasst. Über diese kannst Du dann mittels SVERWEIS() den jeweils zugehörigen Tabellenblattnamen ermitteln und diesen dem INDIREKT()-Formelteil analog meiner Formel (dann natürlich ohne WECHSELN() dafür eben mit dem SVERWEIS(-Formelteil) übergeben und gelangst damit zu dem von Dir angestrebten.

Gruß Werner
.. , - ...

Betrifft: AW: aus dieser neuen Datei kann ich entnehmen ...
von: Jaroslav
Geschrieben am: 18.08.2020 11:24:19

Hey WErner,

tut mir Leid, dass ich dir da was unterschlagen habe....
Hier mal die Formel so wie ich dich verstanden habe. Habe eine Liste auf ein neues Tabellenblatt und da dann die Liste mit Zuordnung Maßnahme zu Matrix.
Da ist aber irgendwo noch ne Macke drin...
=WENNFEHLER(N(ZÄHLENWENNS(K$3:K3;K3;M$3:M3;M3;AD$2:AD2;AD2)<=SVERWEIS(K3; INDIREKT("'"&SVERWEIS(Liste!C2;Liste!B2:C12;Liste!B:B;B:F");SPALTE(B1);0));"") 


Betrifft: AW:ohne Datei (mitListe) nicht nachvollziehbar ...
von: neopa C
Geschrieben am: 18.08.2020 12:01:26

Hallo Jaroslav,

... weiß allerdings nicht wann ich dazukomme, dies danach zu prüfen.

Gruß Werner
.. , - ...

Betrifft: AW: AW:ohne Datei (mitListe) nicht nachvollziehbar ...
von: Jaroslav
Geschrieben am: 18.08.2020 12:57:02

Kannst du mir deine angepasste Liste bitte einmal hochladen?
Dann kann ich selbst nachgucken?

VG

Jaro

Betrifft: AW: AW:ohne Datei (mitListe) nicht nachvollziehbar ...
von: Jaroslav
Geschrieben am: 18.08.2020 13:35:45

Ansonsten hier nochmal meine Liste mit der Formel für 2021 und 2022.
Da stimmt halt was nicht, ich weiß aber nicht wo es hakt.
Vielleicht findest du ja schnell den Fehler in meiner Formel in AD3.

https://www.herber.de/bbs/user/139689.xlsm

Betrifft: AW: da ist noch zu klären ...
von: neopa C
Geschrieben am: 18.08.2020 15:50:32

Hallo Jaro,

... zunächst müssen Deine Texte in Liste "Maßnahmenklasse" exakt so geschrieben sein, wie in Deiner neuen Tabelle "Liste". Das ist bei einigen noch nicht der Fall.

Dann stehen in Deinen Matrix-Tabellen auch 0-Werte. Für diese darf dann natürlich kein Ergebniswert ermittelt werden somit muss die Formel erweitert werden.

Für was genau steht die Jahreszahl in Spalte AR? Steht diese nicht dafür, dass lediglich in der Spalte ein Ergebniswert ermittelt werden darf, oder?

Gruß Werner
.. , - ...

Betrifft: AW: da ist noch zu klären ...
von: Jaroslav
Geschrieben am: 19.08.2020 10:06:24

Morgen Werner,

die Schreibweise in der Liste stimmt mit denen in der Maßnahmenklasse überein.
Es sind lediglich mehr Maßnahmen vorhanden als betrachtet werden.
Die Spalte AR ist für die Betrachtungen irrelevant. Dort kann der neue Jahreswert aus der Matrix angezeigt werde - Dh. er könnte auch anstatt der "1" in die Tabelle zu schreiben die Jahreszahl in die Spalte AR schreiben die zur jeweiligen Maßnahme in der Matrix gehört.
Wie kriege ich denn die 0 raus? Mit einer Vorabfrage Wenn(Formel...ungleich null) ?

Betrifft: AW: zumindest teilweise ist das nicht der Fall ...
von: neopa C
Geschrieben am: 19.08.2020 11:34:57

Hallo Jaro,,

... das die Daten in der Liste mit den in "Maßnahmenklasse" übereinstimmen. Das kannst Du leicht feststellen, wenn Du mal in AV3 folgende Formel: =SVERWEIS(E3;Liste!B:C;2;0) schreibst und diese nach unten kopierst. Dann ergeben sich bei einigen Werten ein #NV

Wenn die Jahreszahlen in AR irrelevant sind, dann bleibt für die Auswertung doch nur der "weg" über die jeweilige Formelerweiterung je Jahr.

Und ja, die teilweise vorhandenen 0-Wert-Vorgabe in Deinen "Matrix"-Tabellenblättern müssen dann mit einer "Vorabfrage" abgefangen werden.

Gruß Werner
.. , - ...