Microsoft Excel

Herbers Excel/VBA-Archiv

Problem mit Kopieren aus Matrix

Betrifft: Problem mit Kopieren aus Matrix von: UKi
Geschrieben am: 10.05.2008 22:05:54

Hallo,

habe ein für meine Verhältnisse unlösbares Excelproblem und bitte herzlich um VBA-Hilfe. Hier bedarf es ggf. der Unterstützung von wirklich guten VBA-User.

Hier das Einzelproblem. Es geht im weitesten Sinn um das Kopieren aus einem dynamischen Bereich einer Matrix.

https://www.herber.de/bbs/user/52282.xls

Am Ende des Tages soll der VBA-Prozeß einen immer keilförmigen Teil der oberen Matrix aufnehmen und an einer vordefinierten Stelle in der unteren Matrix ablegen sowie den Rest der unteren Matrix Ausnullen.

Vorinformation:
1. Die oberen zwei Reihen sind gegeben und können eingelesen werden. Es sind Zerfallsprozesse.
2. Die multiplikative, verschobene Verknüpfung der oberen Matrix ist auch bereits gegeben.
3. Die (mindestens) zwei notwendigen Variablen 1 und 2 sind auch gegeben. Habe ich bereits im Modell.

Arbeitsschritte:
- Lesen Startvariable 1. und 2. (Hier am 31.01.2009 und 5 Jahre bzw. 60 Perioden) aus. Max. zwischen 1 bis 120.
- Kopieren des passenden Keilabschnittes aus oberen Bereich
- Steuere den "richtigen" Startpunkte in der unteren Matrix an
- Kopiere den Keiles zwischen 1 bis Endvariable (60); Schneide nach 5 Jahren vertikal ab und nach 36 - Monaten horizontal
- Nulle alle anderen Felder aus in der unteren Matrix, die nicht belegt wurden

Der untere Bereich mit den Daten ist bereits vorhanden als leerer Rahmen, aber bitte nicht direkt mit den Kalerndedaten arbeiten, weil sich diese auch verändern (können). Die Position der ersten Variable bestimmt den Beginn des Keiles bzw. des relative Positionierung in der unteren Matrix.

Hoffe, ich konnte das Problem halbwegs erklären. Bitte ganz herzlich um Eure Hilfe!!!!!! Wenn ich dies bei jeder Veränderung per Hand machen soll im Excel (max. 36x 36) Zellen, dann ist es aus mit mir....

UKi



  

Betrifft: AW: Problem mit Kopieren aus Matrix von: UKi
Geschrieben am: 11.05.2008 10:57:06

Hallo,

noch eine Ergänzung, die vielleicht eine Lösung erleichtert.

Die genaue Position, in die der relevante Zellebereich ("Keil") reinkopiert werden soll, ergibt sich immer aus der Variable 1.
Ist die Variable 1 =13, dann beginnt der belegte Bereich ("Keil") 13 Zellen nach recht 13 Zellen nach unten. Ende des "Keils" ist dann nach rechts die Variable 2 (im Beispiel 60) und nach unten wird immer nach der 36 Zeile im Rahmen abgeschnitten.

Sorry, daß ich hier kein Sub liefern kann, aber baue auf Eure Hilfe trotz des schönen Wetters...

Danke,

UKi


  

Betrifft: geht aber ganz ohne VBA, mit Formel(n) ... von: neopa
Geschrieben am: 11.05.2008 13:27:40

Hallo Udo,

... sowie benutzerdefinierten Zahlenformat: 0,00%;; (um die Nullwerte auszublenden).

Genau genommen reicht eine Formel in C53 aus, die in der Zielmatrix entsprechend weit nach unten und rechts kopiert wird. Alles weitere ist eine Pfingstzugabe ;-) , so die Variable 2 (ab welcher Spalte begonnen werden soll und die Ermittlung der zutreffenden Periodenanzahl in Zeile 51 sowie die Formel in C13, die Du für die Datenmatrix entsprechend weit nach unten und rechts hättest kopieren können, natürlich auch mit benutzerdefinierten Zahlenformat:0,00%;; ohne auf die Abtreppung zu achten.

Für die entsprechende Hintergrundfarbe (oder eben keine) bietet sich bedingte Formatierung an ...
Ich hoffe, Dir ist damit auch ohne VBA das Pfingsten gerettet. Ich klinke mich erst mal wieder aus.

 BCDEFG
2 Eingaben    
31. Variable :3    
42. Variable :31.03.2008    
5Jahre :5    
6      
71. Variable 12345
8Erste Verfallskurve300%222,9%187,4%165,7%150,6%
9Zweite Verfallskurve100,0%99,8%99,6%99,4%99,2%
10      
11      
12 12345
131300,00%222,47%186,61%164,63%149,31%
142 222,47%186,61%164,63%149,31%
153  186,61%164,63%149,31%
164   164,63%149,31%
175    149,31%
186     

Formeln der Tabelle
ZelleFormel
G7=F7+1
D9=C9-0,0021
E9=D9-0,0021
F9=E9-0,0021
G9=F9-0,0021
C13=C$8*C$9*($B13<=C$12)

Datengültigkeit der Tabelle
ZelleZulassenDatenEingabe 1Eingabe 2
C3Liste =Var_1 
C4Liste =Var_2 
C5Ganze ZahlZellwert ist zwischen 19
Namen in Formeln
ZelleNameBezieht sich auf
C3Var_1=Sheet1!$B$13:$B$47
C4Var_2=Sheet1!$C$52:$DR$52
Namen verstehen


 BCDEFG
511. Variable  123
52 31.01.0829.02.0831.03.0830.04.0831.05.08
531     
542     
553  300,00%222,47%186,61%
564   222,47%186,61%
575    186,61%
586     

Formeln der Tabelle
ZelleFormel
C51=(VERGLEICH(C52;Var_2;0)>=VERGLEICH($C$4;Var_2;0))*(MAX($B51:B51)<$C$5*12)*(SPALTE()+1-SPALTE($B$52)-VERGLEICH($C$4;Var_2;0))
C53=WENN((ZEILE()-ZEILE($B$52)<$C$3)+(SPALTE()-SPALTE($B$52)<VERGLEICH($C$4;Var_2;0))+((SPALTE()-SPALTE($B$52))>VERGLEICH($C$4;Var_2;0)+$C$5*12-1); "";INDEX(Daten_Matrix;ZEILE()-ZEILE($B$52)+1-$C$3;SPALTE()-SPALTE($B$52)+1-VERGLEICH($C$4;Var_2;0)))
Namen in Formeln
ZelleNameBezieht sich auf
C51Var_2=Sheet1!$C$52:$DR$52
C53Daten_Matrix=Sheet1!$C$13:$DR$48
C53Var_2=Sheet1!$C$52:$DR$52
Namen verstehen


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4


Gruß Werner,
.. , - ...


  

Betrifft: AW: geht aber ganz ohne VBA, mit Formel(n) ... von: UKi
Geschrieben am: 11.05.2008 13:49:03

Hallo Werner,
danke herzlich für den Vorschlag. Freut mich, daß Du mein Problem auch optisch nachvollziehen konntest.
Ich habe die Frage (noch) auf offen gestellt, weil mir vor dem Nachbau Deiner Lösung in meinem (englischen) Excel noch ein paar Punkte unklar sind:


1. Schneidet die Formel im Ergebnis den Keil nach 36 Monaten (unten) und nach Ende der 5 Jahre ab?
2. Was wird in den gelben Zellen stehen? "0"
3. Wäre ein VBA für das selbst Ergebnis arg schwierig?

Ich hatte VBA im Sinn, weil es möglichst keine manuellen Arbeitsschritte geben soll, wie z.B. rauskopieren oder abschneiden.

Wäre für kurzes Feedback dankbar.

UKi


  

Betrifft: dreimal JA als Antwort auf Deine Fragen ... von: neopa
Geschrieben am: 11.05.2008 13:55:59

Hallo Udo,

... wobei das JA für Frage 3 nur für mich gilt. Für die vielen VBA'ler hier im Forum, sollte es absolut kein Problem sein.

So jetzt muss mich aber wirklich ausklinken, sonst bekomme ich Ärger @home.

Gruß Werner,
.. , - ...


  

Betrifft: AW: dreimal JA als Antwort auf Deine Fragen ... von: UKi
Geschrieben am: 11.05.2008 14:04:37

Hallo,

die Excel-Lösung von Werner habt Ihr gesehen. Hat Jemand als Alternative ggf. noch ein VBA an der Hand als Lösung?

Danke.

UKi


  

Betrifft: Matrixwert-Übertragung - VBA-Lösung von: fcs
Geschrieben am: 11.05.2008 22:58:34

Hallo UKi,

hab hier mal ein wenig für dich gebastelt.
Zwei Makros sind eingebaut.
Nr. 1 Matrix_Aufbereiten
Mit diesem Makro werden die in der Zielmatrix vorhandenen Daten gelöscht und die Formatierung zurückgesetzt. Zusätzlich werden die Inhalte der beiden Zeilen uber den Datumsangaben gelöscht.

Dieses Makro startest du zuerst.
Dann trägst du in der Zelle über dem Datum ab dem die Werte eingetragen werden sollen eine 1 ein.

Anschließend startest du das 2. Makro - Matix_Daten_Eintragen
Diese Makro liest die Eingabedaten aus und kopiert die Daten aus der Datenmatrix in die Zielmatrix.
Zum Schluss wird in der Zielmatrix noch die Zellfarbe der Zellen mit Werten formatiert.

Im Makro 2 hab ich alternativ die Möglichkeit eingerichtet, das die Werte in der Matrix berechnet werden. Das ist aber deutlich langsamer als das Kopieren.

Im Makro sind verschiedene Informationen wie Zelladressen und Spalten/Zeilennummer, die das Makro benötigt, als Konstanten deklariert. Wenn du am Tabellenaufbau etwas änderst muss du hier ggf. Werte anpassen.

Prüfe in deiner Tabelle noch mal die Formeln ab Zelle V32 nach rechts und nach unten.
Die Formel ist : =C$8*U$9
müsste sie nicht =C$8*V$9 sein?
Ich hab in meiner Tabelle eine Formel eingebaut, die die Funktion INDIREKT verwendet, um die Prozentwerte der Verfallswerte aus der 1. Reihe zu lesen. Diese Formel kann man problemlos im Bereich der Berechnungsmatrix kopieren. Die zellbezüge werden korrekt berechnet.

Gruß
Franz

https://www.herber.de/bbs/user/52293.xls


  

Betrifft: AW: Matrixwert-Übertragung - VBA-Lösung von: UKi
Geschrieben am: 11.05.2008 23:32:49

Franz,

lieben Dank für die VBA-Lösung. Schon saugeil das Forum hier. Ich baue die Sache zeitnah ein und gebe Dir bei Interesse Feedback, ob es gut klappt.

UKi


  

Betrifft: AW: geht aber ganz ohne VBA, mit Formel(n) ... von: UKi
Geschrieben am: 12.05.2008 11:51:32

Werner,

sorry, aber ich schaffe es nicht, Deine Lösung lauffähig nachzubauen. Hätte ich vorher nicht gedacht.

1) Ich bin ungeübt mit den Definition von Feldern und kann daher nicht händische Deine Lösung so sicher nachbauen, daß es läuft. Ich verstehe die Mechanik nicht, daher kann ich die Formel auch nicht nachbauen.
2) Ich kann nicht erkennen, wo genau die zwei Inputfelder sind, die ich automatisch oder ggf. händisch steuere, damit sich der Keil aktualisiert.
3). Ist nicht C51 und C53 gleich belegt bzw. definiert?

Kannst Du mir bitte zeitnah Deine Excellösung als Link geben, damit ich eine lauffähige Version habe? Ich stelle die Frage nach dem Nachbau der Lösung gleich im Forum, weil ich nicht weiß, wann Du reagieren kannst.

Danke für Dein Verständnis.

UKi


  

Betrifft: AW: geht aber ganz ohne VBA, mit Formel(n) ... von: UKi
Geschrieben am: 12.05.2008 11:55:59

Hallo Forum,

kann Jemand von Euch die Lösung von Werner / noa (Vgl. Diskussion) nachbauen als Sheet und mir als Link zu Verfügung stellen???

Ich bin offensichtlich nicht fit genug bei der Zelldefinition und kann daher die Lösung von Werner / noa nicht lauffähig nachbauen bzw. die Logik seiner Hauptformel nachvollziehen!

Eigentlich war Pfingsten schon gerettet, aber nun sitze ich wieder hier und hoffe auf schnelle Hilfe. Die Lösung liegt sehr wahrscheinlich schon vor, nur bekomme ich sie nicht zum Laufen.

Danke!!!

UKi


  

Betrifft: anbei die gesuchte Tabelle ... von: neopa
Geschrieben am: 12.05.2008 17:07:01

Hallo Udo,

... "entwickelt" aus Deiner Ursprungstabelle mit meinen Formelauswertungen. Ich hoffe das hilft Dir etwas weiter.

Gruß Werner,
.. , - ...

p.S. Wer oder was ist noa ;-) ?


  

Betrifft: AW: anbei die gesuchte Tabelle ... von: UKi
Geschrieben am: 12.05.2008 17:20:35

Hallo Werner,

danke für die Antwort, aber ich verstehe sie nicht. Ich hatte gehofft, Du würdest mir den File als Link zur Verfügung stellen. Ich kann Deinen Lösungsvorschlag, der optisch richtig liegt, einfach nicht nachbauen aus besagten Gründen.

Hattest Du den Link vergessen? Verstehe auch Deine Antwort nicht. Wie gesagt, ich würde gern Deine Lösung für mein Ausgangsproblem zum Laufen bringen in meinem (englischen) Excel.

UKi,

PS In meinem Browser wird noa als Antwortname angezeigt, daher hatte ich beide Namen reingenommen.


  

Betrifft: da gab es offensichtlich ein kleines Problem ... von: neopa
Geschrieben am: 12.05.2008 18:27:06

Hallo Udo,

.. mit dem hochladen. Hier nun ein erneuter Versuch: https://www.herber.de/bbs/user/52296.zip
Beachte: Das sheet der Arbeitsmappe wird beim Öffnen in zwei Fenstern dargestellt. Im 1.Fenster ist der Datenbereich mit der Eingabesteuerung und im 2. der Ergebnisbereich abgebildet.

Gruß Werner
.. , - ...


  

Betrifft: AW: da gab es offensichtlich ein kleines Problem . von: UKi
Geschrieben am: 12.05.2008 20:37:19

Hallo Werner,

sauber. File konnte ich öffnen und verwenden. In der Inputmatrix war noch ein grundsätzlicher Verknüfungsfehler bei den Ketten (2. Reihe wandert; 1. Reihe fängt immer bei 1 an), aber dies war auch für mich packbar. Respekt und Dank.

Damit ich auch noch was lerne dabei:
1. Zellformel kann ich nachvollziehen, aber wie wird die farbliche Formatierung aktiviert? Makros sind nicht angezeigt. Läuft dies über eine Formatierung der Zelle? Wo und wie?
2 . Wo steuerst Du die zweifache Ansicht wie oben von Dir angekündigt an?


Also dicken Dank. Die Lösung ist schon fast zu gut....

UKi


  

Betrifft: AW: da gab es offensichtlich ein kleines Problem . von: fcs
Geschrieben am: 12.05.2008 22:14:49

Hallo Udo,

hab mir Werners Lösung angesehen. Saubere Lösung.

In der Datei, so wie in der ZIP-Datei gespeichert, ist das Tabellenblatt so eingerichtet, dass es in 2 Fenstern dargestellt wird. Diese sind mit :1 und :2 hinter dem Dateinamen gekennzeichnet. Mit Fenster anordnen -- untereinander kannst du den oberen und unteren Bereich der Tabelle gut sehen und unabhängig von einander scrollen. Mit "Neues Fenster" im Meneü Fenster erreichst du diese Darstellung.

Im Menü Fenster gibt es auch die Funktion "Fenster Teilen".
Hebe zunächst die Fixierung auf, dann positionieren den Cursor etwa in der Zelle A20 und wähle Fenster teilen. Auch da hat man einen besseren Überblick.

Die Formatierung im unteren Matrix-Bereich wird mit bedingter Formatierung im Menü Format erreicht.
Alle Zellen mit Wert 0 werden farbig dargestellt, Zusätzlich wird ein besonderes benutzerdefiniertes Zahlenformat verwendet, dass die Nullwerte als "Nichts" darstellt.

Gruß
Franz

Gruß
Franz


  

Betrifft: Franz (fcs) hat Deine Fragen ... von: neopa
Geschrieben am: 13.05.2008 08:16:01

Guten Morgen Udo,

... sicherlich zu Deiner Zufriedenheit beantwortet. Sollte noch was unklar sein, melde Dich noch mal.
Kann aber sein, dass ich es aber nicht gleich mitbekomme, weil ich mehr im Excelforum von Spotlight.de zu Hause bin, das nur mal 14 Tage Pause gemacht hat.

Gruß Werner,
.. , - ...


 

Beiträge aus den Excel-Beispielen zum Thema "Problem mit Kopieren aus Matrix"