an Michael:Berechnungen für Spalten und Zeilen

Forumthread: an Michael:Berechnungen für Spalten und Zeilen

14.01.2016 11:29:49

Andi

Hallo Michael,
ist etwas schon länger her, aber ich hoffe das du dich noch an die Tabelle mit den Multiplikatoren und den Kurzzeichen erinnern kannst. Ich habe die Datei mit den Berechnungen nochmals durchgeschaut und die Kurzzeichen richtig zugeordnet und anhand eines Beispieldatei hochgeladen.
Ich habe erst einmal ausgehen von Tabelle1 die ja schon mit den Multiplikatoren berechnet sind in Tabelle2 die Kurzzeichen über die Wenn-Funktion das ganze so gelöst.
https://www.herber.de/bbs/user/102773.xlsm
=WENN($S6="E";F$10-F$6-F$7;
WENN($S6="P1";(F$21+F$20)-(F$45+F$46);
WENN($S6="C";F$40-F$57;
WENN($S6="R";F$59-F$35-F$41;
WENN($S6="H";(F$70+F$133+F$99)-F$122;
WENN($S6="D";F$15-F$9;
WENN($S6="M7";F$17-F$8;
WENN($S6="M5";F$53+F$54;
WENN($S6="M";(F$56+F$55)-(F$92+F$101+F$137);
WENN($S6="B";F$63-F$82;
WENN($S6="X";F$37+F$34;
WENN($S6="S";F$14+F$68;
WENN($S6="";F6;F6)))))))))))))
Frage:
Ist es möglich wenn neue Kurzzeichen (die in der Tabelle Kurzzeichen eingetragen werden) dazukommen, das über VBA automatisch diese mit in die Berechnung einbezogen werden.
Vielen Dank im Voraus!!
Andi

6
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

AW: an Michael:Berechnungen für Spalten und Zeilen

20.01.2016 00:03:27

Michael
AW: an Michael:Berechnungen für Spalten und Zeilen

20.01.2016 18:16:35

Andi
nur ganz kurz

21.01.2016 15:02:45

Michael
Aber jetzt...

21.01.2016 21:48:53

Michael
SUPER-MICHAEL VIELEN DANK!

21.01.2016 22:21:42

Andi
gerne, war mir ein Vergnügen,

23.01.2016 18:53:45

Michael

AW: an Michael:Berechnungen für Spalten und Zeilen

20.01.2016 00:03:27

Michael

Hallo Andi,
die Datei bringt bei mir einen XML-Fehler. Wahrscheinlich hattest Du irgendwelche exteren Bezüge drin, die jetzt natürlich weg sind.
Aber egal: ich mußte erst einmal suchen, wo die Formel denn nun eigentlich steht.
Nur, damit wir nicht aneinander vorbeireden. Die oben gepostete Formel steht ...
a) einmal im Blatt "Kurzzeichen", und zwar in Zelle A27 (mit führendem "'")
b) noch einmal im Blatt "Stromverbrauch", und zwar in F90
Also scheint a) so etwas wie eine Kopiervorlage zu sein, die Du dann nach b) kopierst, dort das "'" entfernst und schließlich auf den kompletten Bereich F90 bis Q166 kopierst.
Das Kopieren machst Du natürlich nur, wenn sich an den Formeln was ändert...
Habe ich das richtig interpretiert?
Wenn ich mal davon ausgehe, daß es so ist, gibt es dazu zu sagen:
- es gibt eine max. Verschachtelungstiefe bei WENN; die war mal bei 8, ich weiß nicht, wie es aktuell ist.
- in den allermeisten Fällen trifft keines der Kriterien zu, d.h. die Formel übernimmt nur den obigen Wert (also F6)
Zusammen also wäre es besser und würde es Excel weniger ausbremsen, wenn man die Formeln nur dahin übernehmen würde, wo sie auch eingesetzt werden.
Also, mal sehen.
So. Im Blatt mit den Kürzeln habe ich in Spalte C eine Formel eingefügt, die die zu der jeweiligen Formel gehörige Zeilennummer heraussucht. Diese wird dann im Makro verwendet, um die Formel in die jeweilige Zeile zu kopieren.
Das Makro sieht so aus:

Sub FormelKopieren()
Dim Kurz_A27 As String ' Formel im Blatt "Kurzzeichen" in A27
Dim wohin As Variant   ' das wird dann ein sogenanntes "Array"
Dim maxZeilen As Long
Const sv_Anfang = 90   ' 1. Kurzzeichen in "Tabelle2" des StromVerbrauchs
Dim sv_Ende As Long, i As Long, j As Long
Dim wsv As Worksheet
Dim t1 As Single
Set wsv = tabStromverbrauch
maxZeilen = Sheets("Kurzzeichen").Range("A" & Rows.Count).End(xlUp).Row
Kurz_A27 = Replace(Sheets("Kurzzeichen").Range("A" & maxZeilen), Chr(10), "")
maxZeilen = maxZeilen - 1
' -1, weil die Formel, die derzeit in A27 steht, nicht dabei
' sein soll - in der Liste "wohin" mit den Kurzzeichen
wohin = Sheets("Kurzzeichen").Range("A2:C" & maxZeilen)
' alle vorhandenen Kurzzeichen einlesen ...
'... und leere Zeilen raussortieren:
j = 0
For i = 1 To UBound(wohin, 1)
If wohin(i, 3)  "" Then
j = j + 1
wohin(j, 3) = wohin(i, 3)
End If
Next
MsgBox "Zeilen gesamt: " & i & " Zeilen mit Nummern: " & j
t1 = Timer              ' wie lange wird es wohl dauern?
' Jetzt geht's zur Sache: **************************
' 1. Kopieren aller Daten
wsv.Range("F6:Q82").Copy wsv.Range("F90")
' 2. *einmaliges* Einfügen der Formel in F90:
wsv.Range("F90").FormulaLocal = Kurz_A27
' 3. anschließendes Kopieren, aber nur in die benötigten Zeilen:
For i = 1 To j
wsv.Range("F90").Copy wsv.Range("F" & wohin(i, 3)).Resize(, 12)
Next
MsgBox "erledigt in " & (Timer - t1) * 1000 & " Millisekunden"
End Sub

und läuft gut fix.
Du hattest den Stromverbrauch ja schon kopiert; ich habe eine weitere Kopie angelegt und *vor* dem Test des Makros im Bereich F90 bis Q166 eine Verifizierung eingebaut (hier für F90),

='Stromverbrauch (2)'!F90=Stromverbrauch!F90

die die Werte vergleicht.
Sie stimmen auch *nach* dem Makroaufruf noch überein: paßt!
Ich bin nicht ganz glücklich mit der Lösung, denn Du mußt die Formel in A27 noch händisch anpassen, und eigentlich würden Teilformeln, die man zu jedem einzelnen Kürzel hinterlegt, genügen...
Aber Du solltest damit einen guten Schritt weiterkommen.
Die Datei: https://www.herber.de/bbs/user/102895.xlsm
Schöne Grüße,
Michael

AW: an Michael:Berechnungen für Spalten und Zeilen

20.01.2016 18:16:35

Andi

Hallo Michael,
bedanke mich ganz herzlich für deine Ausarbeitung.
- es gibt eine max. Verschachtelungstiefe bei WENN; die war mal bei 8, ich weiß nicht, wie es aktuell ist.
In der neuesten Versionen bis zu 64 mal!
a) einmal im Blatt "Kurzzeichen", und zwar in Zelle A27 (mit führendem "'")
Habe die Wenn-Funktion mit den Blatt "Kurzzeichen" eigentlich nur zur Überprüfung hergenommen, aber so ist es auch eine gute Lösung.
Dim Kurz_A27 As String ' Formel im Blatt "Kurzzeichen" in A27 'bei neuen Kurzeichen verschieben sich die Zeilen nach unten!
Da in den Blatt "Stromzähler" neue Anlagen und in den Blatt "Kurzzeichen" neue Berechnugen dazukommen,müsste man das im VBA-Code immer anpassen, oder kann man das auch so Programmieren das es automatisch erweiter wird?
' Jetzt geht's zur Sache: **************************
' 1. Kopieren aller Daten
wsv.Range("F6:Q82").Copy wsv.Range("F90") bei neuen Anlagen werden neue Zeilen eingefügt und die Zeilen würden entsprechend nach unten verschoben.
Ansonsten wenn es zu viel arbeit macht, ist diese Lösung für mich auf jedenfall eine Erleichterung.
Da ich vorher für jede Anlage ein einzelenes Blatt hatte also 77 Blätter in einer Arbeitsmappe ist diese Lösung auf jedenfall übersichtlicher und nachvollziehbarer.
Vielen Dank
Gruß
Andi

nur ganz kurz

21.01.2016 15:02:45

Michael

Hi Andi,
nur ganz kurz, weil ich jetzt dann einen Termin habe:
Ich habe schon noch ein, zwei Ideen, das wirklich variabel oder zumindest "halb-variabel" zu gestalten.
Einer der Ansätze dabei wäre, die Formeln wirklich nur jeweils pro Buchstaben zu gestalten, ohne die verschachtelten WENNs. - Das würde ich dann aber gerne so formulieren, daß "unten" definitiv nur auf Werte "oben" zugegriffen wird. Das geht schon, wenn man die Formeln zu H und M (habe jetzt nicht im Kopf, welche Nr. jeweils) etwas umstellt.
Zumindest die Position der "händischen" WENN in A27 wird von meinem Makro bereits unabhängig von der Zeile ermittelt: das Makros springt "von ganz unten" in den ersten Eintrag in Spalte A, eben genau zur Formel - Du mußt also nur dabei bleiben, die Formel immer ganz unten hinzuschreiben, dann ist dieser Teil des Makros immer funktionsfähig.
Daß die erste Datenzeile in der "unteren" Tabelle2 die Zeile 90 ist, ist weniger variabel - wenn Du das ändern möchtest, mußt Du auch die Formel selbst ändern. Nein, Quatsch, zumindest dann nicht, wenn Du wie eben gesagt, nur auf die obere Tabelle verweist.
Da muß ich nochmal nachdenken...
Sollte der Thread mittlerweile erneut rausfliegen, mache die Frage bitte nochmal auf.
Schöne Grüße,
Michael

Aber jetzt...

21.01.2016 21:48:53

Michael

Hi Andi,
ich habe einige Änderungen vorgenommen.
Die *neue* Programmlogik (alles im Blatt "Stromverbrauch") funktioniert in etwa so:
1. Button "Formeln in Spalte U erzeugen"
- sucht in Spalte S nach dem händisch nach Bedarf einzugebenden Wert KurzEnde (derzeit in Zeile 83) und merkt sich die Zeilennummer -1, das ist dann die unterste Zeile des Datenbereichs der oberen Tabelle.
Das heißt auch, daß Du die Tabelle beliebig erweitern kannst, Du mußt nur dieses Kennzeichen setzen.
- kopiert die Formeln *als Werte* von R nach U
- liest alle Zeilenkennzeichen in Spalte S von Zeile 6 ("hart kodiert") bis zur eben ermittelten, untersten Datenzeile ein und
- ersetzt alle in den Formeln der Spalte U enthaltenen Kennzeichen durch den Buchstaben "F" und die Zeilennummer des Kennzeichens. Dadurch haben wir hier die zeilenweise (und im Prinzip aus dem Kennzeichenblatt abgeleiteten) Formeln ohne das "=" zur weiteren Verarbeitung zur Verfügung.
2. Button "Formeln kopieren"
- sucht zuerst nach dem Kennzeichen "BeginnTab2" in Spalte S, unter dem die zweite Tabelle beginnt (jetzt in Zeile 88) und addiert 2 hinzu, überspringt also die Überschrift und gibt die erste Datenzeile an.
- liest alle Formeln in Spalte U ein und kopiert sie von "F" bis "Q" in die richtige Zeile
Damit ist das ganze Ding völlig variabel - Du kannst sowohl so viele Kennzeichen eingeben, wie Du willst als auch Datenzeilen verwenden, wie benötigt.
Allerdings: das Ersetzen der Kennzeichen durch "F"&Zeilennummer ist etwas tückisch: replace findet bei der Suche nach "E" eben auch "E1" usw., so daß ich alle Kennzeichen ohne Ziffer durch Kennzeichen & "0" ersetzt habe.
Weitere Feinheiten (u.a. umgestellte Formeln) siehe Datei: https://www.herber.de/bbs/user/102947.xlsm
Schöne Grüße,
Michael

SUPER-MICHAEL VIELEN DANK!

21.01.2016 22:21:42

Andi

Hallo Michael,
ich bin ja ganz baff, SUPER einfach SUPER, das ist mehr als ich erwartet habe.
Habe jetz wirglich eine solide Arbeitsmappe mit der ich sehr gut arbeieten kann.
Das Problem mit diesen Kennzeichen und dieser Berechnungsgrundlage war und ist immer ein Problem gewesen, da immer andere Zahlen dabei herrauskamen aufgrund dessen, da mehrere diese Datei öffnen und monatlich Ihre Verbräuche für die Anlagen eintragen.
Ich wollte es eben so zusammenfassen,das der Überblick nicht verloren geht.
Da hast du mir wirglich SUPER geholfen.
Vielen Dank
Gruß
Andi

gerne, war mir ein Vergnügen,

23.01.2016 18:53:45

Michael

Andi,
weil die Geschichte mich durchaus gereizt hatte.
Im Kopf hatte ich schon länger, wie ich es gern gehabt hätte, aber die Umsetzung hat dann doch einiges Nachdenken erfordert.
Ich wünsche weiterhin happy Exceling,
Gruß,
Michael

Beliebteste Forumthreads (12 Monate)

an Michael:Berechnungen für Spalten und Zeilen

Forumthread: an Michael:Berechnungen für Spalten und Zeilen

6 Beiträge zum Forumthread Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

VBA Beispiele zu diesem und ähnlichen Themen

Beliebteste Forumthreads (12 Monate)

6
Beiträge zum Forumthread

Beiträge zu diesem Forumthread