Anzeige
Archiv - Navigation
1540to1544
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

Formel mit Indirekt per VBA lösen

Formel mit Indirekt per VBA lösen
10.02.2017 17:32:30
erichm
Hallo,
ich habe eine umfangreiche Datei bei der Ergebnisse mit der Formel INDIREKT ermittelt werden.
Da diese Formel in vielen Zellen verwendet werden muss, hat sich der Ressourcenbedarf der Datei relativ schnell auf über 100 MB erhöht.
Jetzt möchte ich diese Formel per VBA lösen. Also immer wenn die Datei mit neuen "Grunddaten" versorgt wird und neu zu rechnen ist, soll dann das Makro mit der Formel ablaufen und nur der errechnete Wert in der Zelle stehen, nicht die Formel.
Die Datei hat vier Tabellen und in der Tabelle "senkrecht" ist die betreffende Formel.
Die Datei habe ich hochgeladen und in der Tabelle den Hinweis angebracht.
Ich habe auch in der Tabelle "2fach" eine Formel in vielen Zeilen und Spalten (mit ZÄHLENWENN) - sollte es hier von Vorteil (Schnelligkeit, Ressource) sein, ebenfalls auf eine Makrolösung umzustellen, wäre ich für eine Lösung dankbar - ist aber nicht zwingend notwendig.
Ich hoffe meine Beschreibung mit der Datei ist ausreichend.

Die Datei https://www.herber.de/bbs/user/111369.xlsm wurde aus Datenschutzgründen gelöscht


DANKE!
mfg

18
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Formel mit Indirekt per VBA lösen
10.02.2017 17:46:27
Daniel
Hi
wenn du in der Tabelle "senkrecht" in der Spalte A statt des Spaltenbuchstabens die Spaltennummer hinschreibst (A=1, B=2, C=3 usw) kannst du statt dem Zeitaufwendigen INDIREKT die Funktion INDEX verwenden, welche wesentlich resourcenschonender ist.
also in Zelle F3 dann
statt: =INDIREKT("2fach!"&$A3&2)
dieses: =INDEX('2fach'!$1:$1048576;SPALTE(B$1);$A3)
in A3 muss aber wiegesagt die 3 stehen.
auch die anderen INDIREKT-Funktionen solltest du durch INDEX ersetzen.
INDIREKT brauchst du eigentlich nur dann, wenn der Tabellenblattname "berechnet" wird.
Solange nur Zeilen- und Spalten teil der Berechnung ist, ist Index besser.
der Nachteil von INDIREKT ist, dass alle Formeln mit Indirekt bei jeder Änderung in Excel neu berechnet werden, egal was oder wo du änderst.
Normale Formeln werden nur dann neu berechnet, wenn sich im Zellbereich, den die Formel verwendet, eine Änderung ergeben hat.
Gruß Daniel
Anzeige
AW: Formel mit Indirekt per VBA lösen
10.02.2017 23:00:35
onur
Hi
Wenn du erklärst, was genau die Datei machen soll, und welche Zellen/Blätter wirklich benötigt werden oder nur hilfsmittel sind kann man alles per vba machen.
Gruß
Onur
AW: Formel mit Indirekt per VBA lösen
11.02.2017 07:57:32
erichm
Hallo onur,
danke für das Angebot. Die Struktur der Anforderung ist recht einfach:
1. Die Tabelle "Grunddaten" wird in unregelmäßigen Zeitabständen mit komplett neuen Daten in den Spalten C bis maximal ALO (= 1001 Spalten); Zeilen 2 bis maximal 6001 befüllt. Die Spalten A und B werden dann einfach soweit nach unten aufsteigend durchnummeriert, per Datenbefüllung. Derzeit nutzen wir wegen der Ressourden nur einen kleineren Teilbereich.
2. In der Tabelle 2 fach wird dann in dem Bereich C2 bis maximal ALO6001 berechnet bzw. verglichen:
a) pro Spalte
b) immer 2 aufeinanderfolgende Zeilen ab der Zeile 2
c) aus der Tabelle Grunddaten
d) wenn beide Zeilen den gleichen Inhalt haben ist das Ergebnis 1 ansonsten 0 (siehe zählenwenn-Formel in Tabelle 2fach)
3. In der Tabelle summiert wird dann lediglich pro Spalte die Summe aus den jeweiligen Spalten der Tabelle 2fach gezogen
4. Die Tabelle senkrecht ist dann die "Ergebnistabelle". Hier werden die notwendigen Auswertungsinformationen aus den anderen Tabellen gezogen:
a) Spalte B, Zeilen 3 bis max. 1003 sind die Überschriften aus der Tabelle Grunddaten (C1 bis max. ALO1); diese Überschriften ändern sich aber bei der Datenbefüllung nicht!
b) Spalte C, Zeilen 3 bis max. 1003 sind die Daten aus der Tabelle Grunddaten (C2 bis max. ALO2), diese Daten ändern sich immer wieder.
c) Spalte D, Zeilen 3 bis max. 1003 sind die Daten aus der Tabelle summiert, C2 bis max. ALO2
d) Spalte E, Zeilen 3 bis max. 1003 sind die Summe pro Zeile aus den Spalten F bis T in der gleichen Tabelle (in der Beispieldatei haben wir F bis U summiert, ist aber nicht erforderlich)
e) Spalten F bis T: hier werden die Ergebnisse aus der Tabelle 2fach quasi "senkrecht" aufgelistet:
ea) Spalte F, Zeilen 3 bis max. 1003 sind die Inhalte aus der Tabelle 2fach, C2 bis max. ALO2
eb) Spalte G, Zeilen 3 bis max. 1003 sind die Inhalte aus der Tabelle 2fach, C3 bis max. ALO3
usw. bis Spalte T
f) die Spalten W bis AE sind dann wie dargestellt Summen pro Zeile
Also zusammenfassend wären Ressourcenersparnisse aus meiner Laiensicht sicherlich primär gegeben, wenn die Formeln der Tabelle 2fach sowie die Formeln der Spalten F bis T der Tabelle senkrecht per VBA gelöst werden.
Besten Dank falls diese Lösungsmöglichkeit aufbereitet wird. Ich lasse den Thread deswegen noch offen (eine Formellösung hätte ich ja bereits).
mfg
Anzeige
AW: Formel mit Indirekt per VBA lösen
11.02.2017 12:44:04
onur
Hi erichm,
Noch mal zu der einen Frage:
Brauchst du ALLES tatsächlich genau so, wie im Beispiel, oder sind einige/mehrere der Zeilen/Spalten/Blätter einfach nur Hilfsmittel, um alles mit Formeln lösen zu können (Hilfszeilen, Hilfsspalten, so wie die Spalte A in "Senkrecht", wo nur A-ZZ drin steht)?
Denn DAS alles kann man sich ja dann ersparen.
Gruß
Onur
AW: Formel mit Indirekt per VBA lösen
11.02.2017 13:03:24
erichm
Hallo onur,
Hilfszeilen oder Hilfsspalten in der Form, dass das nur "Zwischenlösungen" sind gibt es nicht.
In der Tabelle senkrecht als "Ergebnistabelle" geht es in den Spalten C und D darum, dass sofort ersichtlich ist auf welcher Basis die Summen in der Spalte E zustande gekommen sind.
Deswegen werden diese Spalten für eine schnelle Auswertung / Entscheidung benötigt.
mfg
Anzeige
AW: Formel mit Indirekt per VBA lösen
11.02.2017 07:16:53
erichm
Hallo Daniel,
besten Dank!! Das ist schon eine gewaltige Optimierung und eröffnet uns die Möglichkeit, den Datenumfang wieder etwas zu erweitern. Den hatten wir wegen der Explosion der Ressourcen vorübergehend reduziert.
mfg
per VBA lösen
11.02.2017 16:55:17
Michael
Hi,
ich möchte mich Onur anschließen und das alles mit minimalen Formeln und stattdessen reinem VBA angehen wollen.
Ich verstehe die Ausführungen zu "senkrecht" aber nicht ganz:
1. dort geht es um die Spalten F-U, das entspricht insofern den ersten 17 Zeilen aus "2fach".
Bedeutet das, daß
a) hier tatsächlich nur die ersten 17 Zeilen wichtig sind (bzw. die Unterscheidung, wieviele 1 insgesamt bzw. in 1-17) oder
b) ist das nur zu Darstellungszwecken verkürzt, d.h. eigentlich müßten hier ab Spalte F 6000 Spalten mit Werten gefüllt werden?
2. Und wozu ist die "kumulative Addition" in W-AE gut? Warum hier nur 10 und nicht 17?
3. L1-U1 ist Müll?
4. Spalte C enthält immer den Wert aus der 2. Zeile der Grunddaten: was sagt das denn aus? Das ist ja eben nicht die doppelte Zahl?
Schöne Grüße,
Michael
Anzeige
AW: per VBA lösen
11.02.2017 17:41:47
erichm
Hallo Michael,
danke für das Interesse.
1. dort geht es um die Spalten F-U, das entspricht insofern den ersten 17 Zeilen aus "2fach".
Bedeutet das, daß
a) hier tatsächlich nur die ersten 17 Zeilen wichtig sind =JA; der Eintrag endet allerdings in Spalte T
oder
b) ist das nur zu Darstellungszwecken verkürzt, d.h. eigentlich müßten hier ab Spalte F 6000 Spalten mit Werten gefüllt werden? = Stimmt, nur zur Darstellung (für eine Entscheidung) verkürzt
2. Und wozu ist die "kumulative Addition" in W-AE gut? Warum hier nur 10 und nicht 17? das ist wiederum zur Vorbereitung einer Entscheidung relevant, deswegen nur der Teilbereich summiert
3. L1-U1 ist Müll? = Stimmt, habe bei der Musterdatei übersehen, dass ich nicht analog F1 bis K1 die Formel fortgesetzt habe; U1 muss aber nicht mehr befüllt werden
4. Spalte C enthält immer den Wert aus der 2. Zeile der Grunddaten: was sagt das denn aus? = dient wiederum ausschließlich zur Darstellung und Vorbereitung einer Entscheidung Das ist ja eben nicht die doppelte Zahl? das ist auch nicht notwendig, es ist eben nur die Übertragung
Ich hoffe ich konnte das klären.
mfg
Anzeige
Du konntest,
11.02.2017 17:59:27
Michael
Erich,
aber nicht ganz: soll es jetzt bis U gehen oder nicht?
Ich hatte mich auch vertan: es sind ja nur 16: Spalte F der 1. bis Spalte U der 16.
Ich gehe mal davon aus, daß U gefüllt werden soll.
Schau bitte in einer halben Stunde nochmal rein, ich mach's grad fertig (na, wer weiß).
Gruß,
M.
bitte testen
11.02.2017 18:37:03
Michael
Hi,
hier: https://www.herber.de/bbs/user/111382.xlsm
Die Buttons sind im Inhaltsverzeichnis; "opti" wirft nur das Ergebnis in "senkrecht" aus.
Viel Spaß beim Spielen,
Michael
EINFACH GIGANTISCH
11.02.2017 19:02:25
erichm
Hallo Michael,
ist ja Wahnsinn, in der kurzen Zeit so ein Tool!!
Habe gleich einen ersten Härtetest mit
700 Spalten
5.500 Zeilen
in den Grunddaten gewagt.
Mit Bravour bestanden!!
Den Unterschied zwischen
Zwischenschritt
Optimierung
habe ich noch nicht ganz verstanden?
DANKE auch für die Erläuterungen in VBA; vielleicht kann ich da mal was ähnliches umstricken (ein klein wenig verstehe ich VBA, aber eben nur ein klein wenig.....)
mfg
Anzeige
AW: bitte testen
11.02.2017 19:04:13
erichm
....und noch vergessen:
trotz der großen Datenmenge hat die jetzt nicht mal 30 MB!!
mfg
Ergänzungsversuch
12.02.2017 08:32:49
erichm
Hallo Michael,
jetzt habe ich entdeckt, dass in den Tabellen 2fach und senkrecht die Daten vor einer Neubefüllung gelöscht werden sollten, da bei Verkürzung der Spalten und Zeilen in den Grunddaten in den beiden Tabellen die dann überflüssigen Spalten und Zeilen nicht eliminiert werden.
In der Tabelle 2fach habe ich dies durch löschen und neu anlegen des Sheets gelöst (clear hat irgendwie nicht funktioniert).
Bei der Tabelle senkrecht habe ich mich nicht getraut, weil ich den Code des erstellens noch nicht ganz durchblickt habe.
Zusätzlich habe ich eine Erweiterung eingebaut, dass auch 3fach gerechnet wird (also vergleiche 3 Zeilen statt 2 Zeilen). Die Anlage des Codes und einer neuen Tabelle ist kein Problem - aber das Ergebnis?
In der Anlage meine überarbeitete Datei; die Ergänzungen habe ich im Code mit '''----- jeweils gekennzeichnet.
https://www.herber.de/bbs/user/111390.xlsm
Besten Dank für eine Überprüfung / Korrektur oder Hinweise.
mfg
Anzeige
erst Mal kurze Erklärung
12.02.2017 16:20:00
Michael
Hi Erich,
das Löschen von 2fach geht nicht mit Sheets(xx).clear, weil .clear eine Methode des Range-Objekts ist und nicht des Blatt-Objekts. Richtig heißt es: Sheets(xx).cells.clear bzw. .clearcontents, falls evtl. Formatierungen vorhanden sind.
Ansonsten: der Programmierer zerlegt Probleme in einzelne Häppchen, also habe ich in der Reihenfolge programmiert:
a) Sub GD_to_2fach() aufgerufen durch "Grunddaten in 2fach"
Das Ding liest die Grunddaten in ein Array, rechnet sie durch und schreibt sie in 2fach; nebenbei wird die Summe gleich mitgerechnet und unter die Daten geschrieben.
b) Sub zwischen() ("Zwischenschritt")
= Erweiterung von a) mit Berechnungen fürs Blatt "senkrecht"
c) Sub opti() ("Optimierung")
= wie b, nur daß die Ausgabe(n) in "2fach" auskommentiert sind - die "quick & dirty"-Lösung.
Man muß natürlich alle Zwischenergebnisse rechnen, aber für meinen Geschmack ist die *Ausgabe* von "2fach" völlig unnötig: das sieht sich kein Mensch freiwillig an - und die Maschine braucht's nicht.
Weitere Optimierungen sind in c) machbar: weil es aus a) entstanden ist, sind überflüssige Dinge vorhanden.
Deine Änderungen habe ich wieder entfernt, weil das alles mit 1 Zeile erledigt ist (für 2fach, 3fach und auch für senkrecht).
In 3fach habe ich Or durch And ersetzt und an den Schleifengrenzen gearbeitet. Ach so, Deine neue Variable habe ich rausgworfen. Deshalb hat es auch nicht funktioniert: die hatte (nach dem Dim) den Wert 0, dadurch wurde die Schleife nicht ausgeführt (for i = 1 to 0 macht rein gar nix). Es ist noch ne Änderung drin, aber ich habe die Datei schon wieder zu.
Interessant wäre zu wissen, ob Du die beiden 2fach und 3fach wirklich benötigst: "opti" braucht sie nämlich nicht (wobei opti3 dann noch aus opti2 zu erzeugen wäre).
Aber: wenn Du schon 2fache und 3fache haben willst, was ist dann mit 4fachen? 5fachen?
Eigentlich müßte man die gar nicht getrennt berechnen, es würde genügen, aus den 2fachen mehrfach untereinanderstehende 1-er zu summieren.
Nun, dazu müßte ich mehr wissen - abgesehen davon wird es langsam etwas arg umfangreich.
Die Datei: https://www.herber.de/bbs/user/111404.xlsm
Schöne Grüße,
Michael
Anzeige
AW: erst Mal kurze Erklärung
13.02.2017 07:28:17
erichm
Hallo Michael,
besten Dank für die Erläuterungen - jetzt habe ich es begriffen.
Stimmt natürlich, mit der VBA-Lösung benötige ich die Zwischentabellen 2fach und 3fach nicht.
Für das Projekt ist nur noch das 4fache relevant - 5- und mehrfach nicht mehr. 4fach hatten wir bisher außen vor gelassen wegen der Ressourcenprobleme auf Formelbasis.
Aber jetzt haben wir 2-, 3- und 4fach komplett; den "opti-Code" haben wir entsprechend erweitert und er läuft problemlos, ebenso stimmen die Berechnungen; haben wir bereits getestet.
Insofern jetzt unser Endergebnis:
Sub opti()
Dim a, asum, b, c&() ' a als Array via Zuweisung, asum, b & c als Array via redim
Dim aSp&, aZl&  ' Spalten/Zeilen des Arrays a zum Zwischenspeichern von ubound
Dim i&, j&, z&, s& ' & = as long; Laufvariablen
Dim t0 As Single
t0 = Timer
'''--------------2fach-----------------------------------------
a = Sheets("Grunddaten").Range("A1").CurrentRegion
aSp = UBound(a, 2)
aZl = UBound(a)
If aZl 
Also nochmals allerbesten Dank für die geduldige und äußerst kompetente Unterstützung. Ist immer wieder faszinierend welche Möglichkeiten EXCEL und dann insbesondere per VBA bietet.
mfg
Anzeige
freut mich, wenn's läuft
13.02.2017 12:41:31
Michael
Hi Erich,
das habt Ihr dann ja super hinbekommen!
Laß mich noch zwei Hinweise anbringen:
a) der Code für 2-4 könnte "irgendwie" noch zusammengefaßt werden, aber wenn Ihr zufrieden seid, mag ich mir nicht den Kopf zerbrechen...
b) der Code könnte bei sehr großen Erweiterungen an gewisse Speichergrenzen stoßen.
Im Moment habt Ihr ja 1024*6000*benötigte Bytes pro Eintrag (bin mir nicht schlüssig, ob Excel automatisch integer, Long oder was auch immer verwendet; also min. 2 Bytes, wahrscheinlicher 4 Bytes).
Falls der Fall eintritt, wäre das Mittel der Wahl, die Spalten eben immer z.B. 100er-weise einzulesen und die errechneten Zwischenergebnisse entsprechend 100-Zeilen-weise abzuspeichern.
Das ist etwas zusätzliche Rechnerei, dürfte zeitlich aber nicht groß ins Gewicht fallen.
Na dann: happy Exceling,
Michael
AW: freut mich, wenn's läuft
14.02.2017 07:27:32
erichm
Hallo Michael,
das passt jetzt alles so; danke für den Speicherhinweis!
Mit den Erkenntnissen können wir jetzt noch die eine oder andere umfangreiche Projektdatei für alternative VBA-Lösungen prüfen. Insbesondere der Verzicht auf "Zwischenspeicherungen" die mit Formeln notwendig sind ist genial. Ich habe schon mal gegoogelt und werde mich mit diesen VBA-Möglichkeiten mal beschäftigen - auf den ersten Blick muss ich mich da aber erst mal tief reinhängen............
Aber wird auf jeden Fall interessant sein und Spaß machen!
Danke nochmal.
mfg
gern geschehen
16.02.2017 16:29:16
Michael
Hallo Erich,
diese Arrays erfordern eine gewisse Übung, bis sie von der Hand gehen.
Es gibt eine Reihe guter Texte, u.a. http://www.online-excel.de/excel/singsel_vba.php?f=152
Ich wünsche weiterhin viel Spaß!
Grüße,
Michael

30 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige