Microsoft Excel

Herbers Excel/VBA-Archiv

Zeilenwerte von links nach rechts auslesen

Betrifft: Zeilenwerte von links nach rechts auslesen von: Doc Martin
Geschrieben am: 29.09.2014 18:20:06

Hallo Zusammen,

für Eure Hilfe wäre ich total dankbar. Auch in einem Post vor 4 Wochen, konnte das Thema nicht gelöst werden und nirgendwo finde ich einen Lösungsansatz - dabei müssten doch noch mehr Leute vor so einer Aufgabe stehen:

Ich suche eine Formel (kein Makro / VBA), die Zeilenwerte, die größer Null sind, nacheinander (von links nach rechts) ausliest und in eine Spalte (Zellen untereinander) schreibt:

Hier ein erklärendes Beispiel: In Zeilen ist eine zeitliche Projektion zukünftiger Kreditbedarfe aufgetragen. Die notwendigen Kreditbeträge sind über die Spalten einem Tagesdatum oder Kalenderwoche zugeordnet.
Bei 20 Kalenderwochen, ist die Zeile also 20 Spalten breit. In dieser Zeit, habe ich vielleicht nur 3 Abrufe (Kreditbetrag grösser 0). In diesem Fall habe ich also eine 20 spaltenbreite Zeile in der nur 3 Zellinhalte wichtig sind.
Das ist in der Darstellung extrem unübersichtlich.

Vereinfacht sieht die Quelltabelle so aus: In Zeile 1 steht das Datum, die Zeilen 2 und 3 zeigen die notwendigen Kreditbeträge für Kredit 1 bzw. Kredit 2 (Werte grösser Null):

        A         B         C            D
1               15.9    30.9   15.10    ...
2   Kredit 1:   0€   100€   50€ ...
3   Kredit 2:   50€   0€   200€ ...

Ich suche eine Formel, die aus der Quellzeile nacheinander Werte grösser Null ausliest und in eine vertikale Reihenfolge schreibt - dies in der Zuordnung zur ausgelesenen Zeile (die steht für Kredit 1, oder Kredit 2) und jeweils auch mit dem entsprechenden Datum / Kalenderwoche.

       A                    B           
1  Kredit 1     Datum
2  100€          30.9
3   50€           15.10
4
5  Kredit 2     Datum
6  100€          30.9
7   50€           15.10

Anbei ist eine Beispiel-xls beigefügt

Herzlichen Dank vorab für Eure Hilfe!
Doc Martin

  

Betrifft: AW: Zeilenwerte von links nach rechts auslesen von: Doc Martin
Geschrieben am: 29.09.2014 18:22:42

die beigefügte Datei ist hier zu finden:
https://www.herber.de/bbs/user/92883.xlsx


  

Betrifft: Na, da hast du dir ja eine tolle DateiStruktur ... von: Luc:-?
Geschrieben am: 29.09.2014 21:09:47

…ausgedacht, Doc M.!
Da wirst du dich in Geduld üben dürfen, da das hochkomplexe Fmln erfordert, denn immerhin hängt ja alles voneinander ab. Besonders brisant wird das bei der Neuanordnung von Datum und KreditNr, denn Letztere soll ja wohl ebenfalls aufsteigend sein und hängt dazu noch vom Datum ab (wg ggf leer). Andersherum hängt auch das Datum von der KreditNr ab. Außerdem können zu einem Datum auch mehrere KreditNrn gehören, die die Nicht-Leer-Bedingung erfüllen!
Ich habe erstmal andersherum angefangen, wodurch sich ein 1.Ansatz ergibt:

 ABCD
15GmbH 1 externe AbrufeDatumBetrag
16 Kredit 23. Sep. 1460 025 €
17 Kredit 324. Sep. 1433 334 €
18 Kredit 21. Okt. 1430 681 €
19 Kredit 222. Okt. 1410 985 €
20 Kredit 229. Okt. 1458 271 €
21 Kredit 212. Nov. 1430 012 €

Dabei habe ich schon mal festgestellt, dass du für Kredit3 ein falsches 1.Datum in der Zieltabelle angegeben hast.
Die bisherigen Fmln lauten (bezogen auf den gezeigten TestBereich):
B16[:B21]: {=INDEX(Quelltabelle!C$6:C$9;KKLEINSTE(WENN(INDEX(Quelltabelle!L$6:AB$9;1;VERGLEICH(C16;Quelltabelle!L$5:AB$5;0)):INDEX(Quelltabelle!L$6:AB$9;4;VERGLEICH(C16;Quelltabelle!L$5:AB$5;0))>0;ZEILE($1:$4);"");ZEILE($1:$4)))}
C16:C21: {=WENNFEHLER(KKLEINSTE(MTRANS(Quelltabelle!L5:AB5);ZEILE(1:17));"")}
D16[:D21]:=INDEX(Quelltabelle!L$6:AB$9;VERGLEICH(B16;Quelltabelle!C$6:C$9;0);VERGLEICH(C16;Quelltabelle!L$5:AB$5;0))
Damit hast du schon mal einen Eindruck, wie komplex das wird!
Gruß, Luc :-?

Besser informiert mit …


  

Betrifft: AW: Na, da hast du dir ja eine tolle DateiStruktur ... von: Doc Martin
Geschrieben am: 29.09.2014 23:52:46

Hallo Luc:-?,
vielen Dank! Ja, aus der Umsetzungssicht sieht das wirklich "toll" aus, aus Anwendersicht eines Kreditgebers ist die Anforderung "ganz normal"...
Absolut korrekt, bei Kredit3 ist ein falsches erstes Datum.

Bis auf die Bestimmung des Datums (Zellen c16:c21), die nach Deinem Ansatz die Bestimmende für die Zuordnung von Kreditnr und Betrag ist, konnte ich Deine Formel verstehen. Wie Du ja eingangs schreibst, kann es sein bzw. ist es auch so, daß zu einem Datum mehrere Kreditabrufe notwendig sind. Dann wird´s schwierig bzw. funktioniert dieser Ansatz nicht - ebenso die Sortierung nach Krediten, die (wieder aus Anwendersicht) sehr sinnvoll wäre.

Ohne konkrete Umsetzungsidee, würde ich eher in der Quelltabelle die Kreditbeträge separat je Zeile (die ja für eine Kreditnr stehen) von links nach rechts "auslesen" und dann aus der Kombination von Betrag und Kreditnr, das zugehörige Datum suchen lassen...

Gruß, Doc. M.




  

Betrifft: Soso, ganz normal!? Da sind aber noch andere ... von: Luc:-?
Geschrieben am: 30.09.2014 03:31:17

…Hürden eingebaut, Doc;
nämlich die von dir mit gezeichneten Balken versehenen Kreditierungen, die in der Zieltabelle grau dargestellt sind. Mit Fmln wird das Ganze wg seiner Komplexität wohl kaum ohne Hilfszellen lösbar sein. In solchen Fällen verwendet man idR eher ein VBA-Pgm oder wenigstens ein paar qualifizierte UDFs, mit denen ZellFmln einfacher wdn könnten.
So, das Folgende ist mir nun mit StandardFmln, einer Hilfsmatrix und -spalte gelungen:

 ABCDEFGHI
1GmbH 1 externe AbrufeDatumBetrag Hilfsmatrix   
22Kredit 23. Sep. 1460 025 €  2  
32Kredit 21. Okt. 1430 681 €   3 
42Kredit 222. Okt. 1410 985 €  2 4
52Kredit 229. Okt. 1458 271 €  2  
62Kredit 212. Nov. 1430 012 €  234
73Kredit 324. Sep. 1433 334 €  2  
83Kredit 329. Okt. 1466 666 € GmbH 1 bei GmbH 2DatumBetrag
94Kredit 41. Okt. 1418 669 € SelberKredit 41. Okt. 1418 669 €
104Kredit 429. Okt. 143 975 € lösen!Kredit 429. Okt. 143 975 €
11Formeln        
12A2:A10: {=KKLEINSTE((F2:F7;G2:G7;H2:H7;I2:I7);ZEILE(INDIREKT("Z1:Z"&ANZAHL(F2:I7);0)))} 
13B2[:B10]:=INDEX(Quelltabelle!$C$6:$C$9;A2) 
14C2[:C10]: {=INDEX(KKLEINSTE(WENN(INDEX(Quelltabelle!$L$6:$AB$9;A2;0)*(Quelltabelle!$L$5:$AB$5>0)≠0;Quelltabelle!$L$5:$AB$5;"");SPALTE(A:R));ZÄHLENWENN($A$2:$A2;A2))}
15D2[:D10]:=INDEX(Quelltabelle!L$6:AB$9;VERGLEICH(B2;Quelltabelle!C$6:C$9;0);VERGLEICH(C2;Quelltabelle!L$5:AB$5;0)) 
16F2:I7: {=INDEX(WENN(INDEX(Quelltabelle!$L$6:$AB$9;ZEILE(1:4);VERGLEICH(KKLEINSTE(Quelltabelle!$L$5:$AB$5;SPALTE($A:$R));Quelltabelle!$L$5:$AB$5;0))=0;"";Quelltabelle!$B$6:$B$9);SPALTE(A:D);ZEILE(1:6))}
Hinweis zu Fmln: Vor jeder Fml ist der StandortBereich angegeben, wobei eckige Klammern bedeuten, dass die dargestellte Fml nur in der 1.Zelle die dargestellte Form hat, in den FolgeZellen sind relative Adressen angepasst; {=…} → Fml ist eine sog MatrixFml (ohne [] in der StandortAdresse → mehrzellig über den ganzen Bereich, sonst einzellig (nur in der jeweiligen Zelle)
Habe auf Grund der ohnehin schon großen Komplexität die Fmln nicht allzusehr universaliert und hoffe (auch deshalb), dass du dann mit dem Rest selbst klar kommst. Aber sicher bleibt so auch noch etwas Handarbeit übrig, um die Ergebnisse an den richtigen Stellen auszugeben… ;-]
Morrn, Luc :-?


  

Betrifft: Wat nu, ... von: Luc:-?
Geschrieben am: 01.10.2014 13:56:37

Martin;
Sprache verschlagen? ;-]
Luc :-?


  

Betrifft: AW: Wat nu, ... von: Doc M
Geschrieben am: 01.10.2014 22:26:11

n'Abend Luc-?

ja, mir hat´s die Sprache verschlagen.
Einmal, weil Du solche Lösungen um 3:31 entwickelst,
zum anderen ,weil ich eben erst von einem langen Tag nach Hause komme. Dies wird morgen auch genauso sein.
Sorry, daß ich daher erst so spät reagiere. Ich fürchte, daß ich den Feiertag brauche, Deine
Lösung zu verstehen und meine Hausaufgabe zu machen ;-0.
Was ich jetzt sofort schonmal umsetzte, ist nicht mehr behaupten Excel gut zu können. Puh,
der Anfänger meldet sich aber zurück ;-p
Der Doc


  

Betrifft: Na, denn iss ja jut...! ;-] owT von: Luc:-?
Geschrieben am: 01.10.2014 22:38:33

:-?


 

Beiträge aus den Excel-Beispielen zum Thema "Zeilenwerte von links nach rechts auslesen"