Live-Forum - Die aktuellen Beiträge
Datum
Titel
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
1776to1780
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

Werte aus Matrix in Tabelle schreiben

Werte aus Matrix in Tabelle schreiben
13.08.2020 14:20:49
Jaroslav
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

26
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Werte aus Matrix in Tabelle schreiben
13.08.2020 14:30:14
Jaroslav
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
AW: mit N(), ZÄHLENWENN() und SVERWEIS() ...
13.08.2020 14:43:50
neopa
Hallo Jaro,
... in M3: =N(ZÄHLENWENN($K$3:$K3;$K3)&lt=SVERWEIS($K3;$A:$E;SPALTE(B1);0)) und diese Formel nach rechts und unten ziehend kopieren.
Gruß Werner
.. , - ...
AW: mit N(), ZÄHLENWENN() und SVERWEIS() ...
13.08.2020 15:01:23
Jaroslav
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.
Anzeige
AW: also bei mir schon; und ja B1 stimmt owT
13.08.2020 15:12:31
neopa
Gruß Werner
.. , - ...
Informiere dich über ZEILE() u.SPALTE()! owT
14.08.2020 01:00:38
Luc:?
:-?
AW: Informiere dich über ZEILE() u.SPALTE()! owT
17.08.2020 08:26:09
Jaroslav
Ok sorry funktioniert doch. So ganz verstanden habe ich die Formel zwar nicht, aber trotzdem vielen vielen Dank:-)
AW: und was ist für Dich noch nicht klar? owT
17.08.2020 10:08:09
neopa
Gruß Werner
.. , - ...
AW: und was ist für Dich noch nicht klar? owT
17.08.2020 11:07:43
Jaroslav
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.
Anzeige
AW: die Wandlung des WAHRheitswertes ...
17.08.2020 13:37:44
neopa
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
.. , - ...
AW: die Wandlung des WAHRheitswertes ...
17.08.2020 14:00:22
Jaroslav
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
Anzeige
AW: Deine Vorgabeergebniswerte für 2021 ...
17.08.2020 14:22:26
neopa
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
.. , - ...
Anzeige
AW: Deine Vorgabeergebniswerte für 2021 ...
17.08.2020 15:35:34
Jaroslav
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
Anzeige
AW: dafür dann ...
17.08.2020 20:07:16
neopa
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)&lt=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)&lt=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)&lt=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
.. , - ...
Anzeige
AW: dafür dann ...
18.08.2020 08:16:50
Jaroslav
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?
AW: hier ist der Einsatz von INDIREKT() ...
18.08.2020 08:49:08
INDIREKT()
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
.. , - ...
Anzeige
AW: hier ist der Einsatz von INDIREKT() ...
18.08.2020 09:13:00
INDIREKT()
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
AW:letzteres wäre auf jeden Fall hilfreich(er) owT
18.08.2020 09:33:24
neopa
Gruß Werner
.. , - ...
Anzeige
AW: AW:letzteres wäre auf jeden Fall hilfreich(er) owT
18.08.2020 09:41:48
Jaroslav
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
AW: aus dieser neuen Datei kann ich entnehmen ...
18.08.2020 10:42:52
neopa
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
.. , - ...
Anzeige
AW: aus dieser neuen Datei kann ich entnehmen ...
18.08.2020 11:24:19
Jaroslav
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)

AW:ohne Datei (mitListe) nicht nachvollziehbar ...
18.08.2020 12:01:26
neopa
Hallo Jaroslav,
... weiß allerdings nicht wann ich dazukomme, dies danach zu prüfen.
Gruß Werner
.. , - ...
AW: AW:ohne Datei (mitListe) nicht nachvollziehbar ...
18.08.2020 12:57:02
Jaroslav
Kannst du mir deine angepasste Liste bitte einmal hochladen?
Dann kann ich selbst nachgucken?
VG
Jaro
AW: AW:ohne Datei (mitListe) nicht nachvollziehbar ...
18.08.2020 13:35:45
Jaroslav
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
AW: da ist noch zu klären ...
18.08.2020 15:50:32
neopa
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
.. , - ...
AW: da ist noch zu klären ...
19.08.2020 10:06:24
Jaroslav
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) ?
AW: zumindest teilweise ist das nicht der Fall ...
19.08.2020 11:34:57
neopa
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
.. , - ...

311 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige