Anwenden einer Berechnungsformel aus einem anderen Dokument

Forumthread: Anwenden einer Berechnungsformel aus einem anderen Dokument

04.10.2024 15:43:35

ijoyoujo

Hallo Forum, bitte um Hilfe!

Ich habe eine Anzahl von xlsx Dokumenten (etwa 1000), welche individuell unterschiedlich, in gewissen Zellbereichen jedoch "standardisiert" sind. Die standardisierten Zellbereiche enthalten (Referenzen auf) fixe Textbausteine, die in einem "Master" xlsx Dokument hinterlegt sind, sowie Zahlenwerte.

Meine Frage bezieht sich auf die Zahlenwerte. Sie sind das Ergebnis von Berechnungen mit Werten aus anderen Zellen des jeweiligen Dokuments. Die Formeln für diese Berechnungen sind in allen 1000 Dokumenten gleich. Jedoch kann es im Laufe der Zeit zu Änderungen in den Formeln kommen. Ich möchte daher vermeiden, die Berechnungsformeln 1000 mal in allen Dokumenten zu hinterlegen, damit ich bei einer etwaigen Änderung nicht alle Dokumente ändern muss.

Kann ich einen Bezug zu einer Formel erstellen, welche sich (äquivalent zu den Textbausteinen) in einem Formelkatalog im "Master" xlsx Dokument befindet, sodaß die Berechnung anhand dieser Formel, jedoch mit den Werten aus dem individuellen Dokument, erfolgt?

Beispiel für eine Formel in Zelle AA12:
=WENN(ODER(X10>'[master.xlsx]values'!$A$38,X11,X16,UND(X17,X18)),X13-X10-X11,"")

AA ist in allen Dokumenten die Berechnungs-Spalte. Die Beispielsformel steht identisch in allen 1000 Dokumenten in Zelle AA12. Spalte AA enthält eine Vielzahl ähnlicher Formeln, mitunter komplexer als das Beispiel.

X ist in allen Dokumenten die Datenquellen-Spalte. Die Zellwerte sind pro Dokument unterschiedlich, aber die referenzierten Zellpositionen sind immer gleich.

Wenn ich die Formel aus AA12 aber in meinem "Master" xlsx Dokument hinterlegen könnte, und im individuellen Dokument eine Art "Referenz" auf diese Formel erstellen könnte, so wäre das viel einfacher und eleganter.

Ich hoffe, ich habe das Problem soweit verständlich beschrieben. Gibt es hierfür eine einfache Lösung?

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

AW: Anwenden einer Berechnungsformel aus einem anderen Dokument

05.10.2024 10:36:30

Oberschlumpf

Hi,

na ja, ich denk mal, so rein als Formellösung könntest du so vorgehen:

- erstell dein "Masterblatt"
- schreib ins Masterblatt jede Formel, die sich ändern könnte; z Bsp so:
Zelle Formel
A1 =Formel1
A2 =Formel2
A3 =Formel3
usw

- nun öffne Datei 1 von 1000
- trag dort, wo in Datei1 von 1000 auch noch =Formel1 drin steht, das hier als Formel ein:
=Masterblatt!A1 (natürlich die richtige Schreibweise beachten; ich weiß jetzt ohne testen nicht genau, wie eine Formel aus einer Datei in einer anderen Datei eingetragen wird)
- trag dort, wo in Datei1 von 1000 auch noch =Formel2 drin steht, das hier als Formel ein: =Masterblatt!A2
- trag dort, wo in Datei1 von 1000 auch noch =Formel3 drin steht, das hier als Formel ein: =Masterblatt!A3
usw.

Und das ganze wiederholst du nun in allen anderen 999 Dateien = somit hast du in 1000 Dateien "nur eine Formelverknüpfung" aus dem Masterblatt
Und wenn im Masterblatt sich eine der vielen Formeln ändern sollte, geschieht DAS nun auch in deinen 1000 Dateien...sollte zumindest so sein

Ich aber, als nicht Formel-Experte, würde eine VBA-Lösung vorziehen.

- neue Datei erstellen, in der die VBA-Lösung steht
- in der neuen VBA-Datei auch erst mal in einer Tabelle alle Formeln zusammentragen, die für dich (1000 Dateien) wichtig sind
- nun per VBA + For/Next-Schleife nacheinander alle 1000 Dateien...
...öffnen
...Formeln oder nur Werte in die 1000 Dateien übertragen
...Datei speichern und schließen

Hier gilt natürlich:
Wenn Änderung von Formel(n) erforderlich, dann werden die Formeln nur in der VBA-Datei geändert + die VBA-Lösung muss nach jeder Formeländerung erneut gestartet werden, damit auch die 1000 Dateien "wissen", welche Formeln sich geändert haben.

Hilfts?
Nein?
Dann weiß ich auch nicht weiter.

Ciao
Thorsten

AW: Anwenden einer Berechnungsformel aus einem anderen Dokument

05.10.2024 10:46:11

Saffi

Ich könnte mir vorstellen, dass man mittels Makro die jeweiligen Formeln vorher ausliest und dann bevor in den Datenblättern berechnen, erst die Formel aktualisiert. Egal wie, da müsste man dann nochmal in allen "1000" Dateien diese Routine einpflegen. Die Frage wäre, wieviel Formeln pro Datei in den Spalten AA sind.^^ Sonst sitzt man vor jedem Run eine Stunde vorm Rechner und kann nichts tun.

Theoretisch müsste ein Master als Grundbaustein angelegt werden, aus dem immer wieder eine neue Datendatei gebildet wird. damit hat man aber dann keinen Einfluss auf bestehende Datentabellen mehr. Ich denke das ist wohl ein sehr komplexes Problem. Hier ist wohl eine Datenbank mit mySQL um einges effizienter. Denn da sammelst du Datensätze und hast ein Tabellenblatt mit den vordefinierten Formeln. Daraus erstellst du dann mittels Abfrage eben dann immer mit aktualisierten Formeln Deine Tabellenansicht. Soweit die Theorie.

AW: Anwenden einer Berechnungsformel aus einem anderen Dokument

09.10.2024 19:41:14

ijoyoujo

Danke für die Antworten.

=Masterblatt!A1 (ohne Anführungszeichen) kann nicht funktionieren, da Excel ja natürlich nicht die FORMEL aus Masterblatt!A1 nimmt, sondern den WERT.
"=Masterblatt!A1" gibt den Text zwischen den Anführungszeichen als Text zurück, also {=Masterblatt!A1} ohne die geschweiften Klammern.
MySQL kann ich nicht.

Folgende Lösung wäre eventuell gangbar, was hält ihr davon?

Im Masterblatt stehen in Spalte AA die Formeln. (Es sind ca. 60 Formeln.)

Im Masterblatt Spalte AB konvertiere ich mit =FORMELTEXT() die Formel in Text.
{z.B. master.AB12=FORMELTEXT(AA12)}

Im Masterblatt Spalte AC konvertiere ich den deutschen Formeltext in einen äquivalenten englischen Formeltext, mit folgender VBA Funktion:

Function fxdeuF2engT(handover As Range)

    Application.Volatile

    fxdeuF2engT = Application.Caller.Parent.Evaluate(handover.Formula)

End Function

{z.B. master.AC12=fxdeuF2engT(AB12)}

In meinen "1000" individuellen Dateien wende ich dann die englischen Texte aus dem Masterblatt als Formeln in der Spalte AA an, mit folgender VBA Funktion:

Function fxT2F(handover As String)

    Application.Volatile

    fxT2F = Application.Caller.Parent.Evaluate(handover)

End Function

{z.B. child.AA12=fxT2F('[master.xlsm]formulae'!$AC$12)}

Folgende Einschränkungen gelten:

1) "Evaluate" kann offenbar nur englisch-sprachige Texte in Formeln rückwandeln.
Daher der Zwischenschritt mit der englischen Syntax. Mit der deutschen Syntax (d.h. nur =FORMELTEXT() im master) erhalte ich bloß #WERT! Fehler zurück.

2) Die Formeln im Master dürfen keine Referenzen zu Zellen in anderen Tabellenblättern enthalten. Denn alles wird zu Text konvertiert, und somit entstehen aus relativen Bezügen absolute Bezüge, die dann in der "child" Datei nicht mehr funktionieren.

3) Einige Formeln enthalten verschachtelte WENN Formeln. Diese Zellen werden beim Öffnen der child Datei nicht vollständig automatisch aufgelöst, es muss mehrfach "F9" gedrückt werden (4x in meinem Fall). Ditto, wenn ich in der child Datei den Wert in einer jener Zellen ändere, mit der die rückgewandelte Formel rechnet, sofern es sich um eine jener verschachtelten WENN Funktionen handelt.

4) Logischerweise müssen die Zellpositionen, auf die sich die Formeln im master beziehen, in allen children identisch sein.

5) Alle Dateien müssen im Format .xlsm gespeichert werden.

AW: Anwenden einer Berechnungsformel aus einem anderen Dokument

09.10.2024 19:58:16

Oberschlumpf

Zitat:
"=Masterblatt!A1 (ohne Anführungszeichen) kann nicht funktionieren, da Excel ja natürlich nicht die FORMEL aus Masterblatt!A1 nimmt, sondern den WERT. "

ähh...ja..das ist richtig...aber du hast doch - immer! - in Masterblatt die Formeln! = diese werden doch immer aktualisiert = und in die einzelnen Blätter stehen halt nur die Werte - die aber AUCH aktualisiert werden.

Nun denn, wenn es wichtig ist, dass in den einzelnen Blättern die Formeln STEHEN MÜSSEN, dann weiß ich nich weiter.

AW: Anwenden einer Berechnungsformel aus einem anderen Dokument

16.10.2024 16:39:23

ijoyoujo

Danke für die Antwort.

Ich habe mir mittlerweile eine gangbare Lösung erarbeitet, und diese in zwei Antworten auf meine eigene Frage beschrieben.

Dein obiger Beitrag geht genau an den Kern des Problems: Eine Zelle in Excel hat entweder einen Text oder einen numerischen Wert. Falls es sich um einen numerischen Wert handelt, kann dieser direkt als solcher eingegeben oder anhand einer bestimmten Formel errechnet sein. Handelt es sich nun also um eine Berechnungsformel, so steht diese immer in der betroffenen Zelle selbst. So weit, so klar.

Ich wollte Excel nun so "umfunktionieren", dass ich Zugriff auf die Berechnungsformeln bestimmter Zellen erlange. Der Wert der Zelle ist quasi egal, ich brauche nur die Formel, die Rechenmethode, damit ich die identische Methode in einer anderen Zelle anwenden kann, wobei sich diese andere Zelle in einer anderen Excel-Datei befindet.

Beispiel:
Blatt M(Master)
B1=1
B2=2
Formel A1=B1+B2
Ergebnis A1==3

Blatt C(Child)
B1=11
B2=22
Formel A1=Master!A1
Ergebnis A1==3

---> Excel hat alles richtig gemacht, '3' ist tatsächlich der Wert aus Master!A1.

Das wollte ich aber nicht. Ich wollte die Rechenmethode aus Master!A1. Also, im Beispiel, eine Addition der Werte aus B1 und B2. Die Rechenmethode, die Formel, sie kommt aus dem Master Dokument, die Berechnung aber findet im Child Dokument statt, anhand der im Child Dokument in B1 und B2 befindlichen Werte. Im Beispielfall steht in A1 daher das Resultat '33'.

Es MÜSSEN also, wenn man so will, in den einzelnen Child Blättern die Formeln stehen. Bzw., um genau zu sein, es muss eine Referenz auf die Original-Formel stehen. Ein 1000-faches Propagieren jedweder Änderung der Original-Formel in alle Child-Dokumente wird somit vermieden. Sondern, beim Öffnen des Child-Dokuments werden alle Formeln aus dem Master Dokument aktuell ausgelesen und die entsprechenden Berchnungen durchgeführt. Formeländerungen werden dadurch für das Child Dokument transparent.

Die von mir gefundene Lösung tut dies, indem sie (vereinfacht gesprochen) die FORMEL aus dem Master in einen TEXTSTRING umwandelt, welcher dann im Child wieder in eine funktionierende FORMEL rückgewandelt wird. Dies funktioniert mit den Einschränkungen und dem Workaround wie in meinen beiden Beiträgen beschrieben.

AW: Anwenden einer Berechnungsformel aus einem anderen Dokument

14.10.2024 13:50:13

ijoyoujo

Zitat:
3) Einige Formeln enthalten verschachtelte WENN Formeln. Diese Zellen werden beim Öffnen der child Datei nicht vollständig automatisch aufgelöst, es muss mehrfach "F9" gedrückt werden (4x in meinem Fall). Ditto, wenn ich in der child Datei den Wert in einer jener Zellen ändere, mit der die rückgewandelte Formel rechnet, sofern es sich um eine jener verschachtelten WENN Funktionen handelt.

Korrektur:

Wie sich nun herausstellt, ist es nicht die Tatsache, dass es in diesen Zellen verschachtelte WENN Formeln gibt, die eine Neuberechnung mit F9 erforderlich macht. Sondern: durch Verwendung meiner VBA Funktion, welche einen Formeltext (aus einem anderen Excel-Dokument, dem "Master") in eine Formel rückwandelt, stellt Excel intern keinen 'dependency tree' zwischen den Zellen im aktuellen Tabellenblatt her. Die einzige 'dependence' einer individuellen Zelle mit rückgewandelter Formel ist die Originalzelle im Master Dokument. (Technisch korrekt ist dies als 'precedence' zu bezeichnen.)

Daher kommt es bei Zellen, die zur Berechnung Werte aus Zellen benötigen, die ihrerseits selbst anhand von rückgewandelte Formeltexten berechnet werden (wo also eine 'dependence' zwischen 2 oder mehreren rückgewandelten Zellen existiert), zu Fehlverhalten.

Dieses Fehlverhalten besteht darin, dass die erwartete Berechnung in den voneinander abhängigen Zellen nicht durchgeführt wird. Beim Öffnen der Datei ergeben sich #NAME? Fehler. Bei Änderung von Werten im bereits geöffneten Dokument, welche eine Änderung von Zellen mit rückgewandelten Formeln verursachen sollte, erfolgt keine solche Änderung (der bereits bestehende Wert bleibt unverändert).

Mit F9 kann man aber die Neuberechnung triggern.

Es wird jedoch pro F9-Druck immer nur eine (1) Neuberechnung durchgeführt. Da in meinem Dokument jedoch mehrere voneinander abhängige Zellen mit rückgewandelten Formeln existieren, muss ich mehrfach F9 drücken.

Beispiel:

Zelle AA11 bis AA15 enthalten jeweils meine VBA Funktionen (Rückwandlung von Text in Formel, mit 'evaluate', wie zuvor beschrieben)

AA11 benötigt ausschließlich Werte aus anderen Zellen im aktuellen Datenblatt
AA12 benötigt den Wert aus AA11
AA13 benötigt den Wert aus AA12
AA14 benötigt den Wert aus AA13
AA15 benötigt den Wert aus AA14

Öffnet man nun die Datei, ergib sich folgende Situation:

AA11 zeigt den erwarteten, berechneten Wert
AA12 bis AA15 zeigen #NAME? Fehler

Druck auf F9 triggert ein erneutes Berechnen des Tabellenblatts, inclusive Ausführen der VBA Funktionen. Zum Zeitpunkt des Drückens ist aber nur der Wert in AA11 bekannt, und somit kann nur die Zelle AA12 aufgelöst werden. Sie erhält nun einen Wert. Da kein 'dependency tree' existiert, werden die Zellen AA13, AA14 und AA15 nicht aktualisiert, und liefern erneut #NAME? zurück.

So kommt es, dass ich im Beispielfall 4x F9 drücken muss, um alle aktuellen Werte zu erhalten.

Analog der Fall, wenn Zellwerte im Datenblatt geändert werden, die den Wert in AA11 ändern. Um AA12 bis AA15 zu aktualisieren, muss 4x F9 gedrückt werden.

Workaround:

Um zu vermeiden, jedes Mal wiederholt F9 drücken zu müssen, habe ich folgende Sub Routine geschrieben, welche die Neuberechnung des aktiven Tabellenblatts mehrfach wiederholt:

' trigger recalculation by calling recalctimes subroutine 

Public Sub TriggerRC()

    On Error Resume Next

    Call recalctimes(9)

End Sub





' recalctimes subroutine: recalculates active sheet rct times

Sub recalctimes(rct As Integer)

    On Error Resume Next

    

    Dim i As Integer

    i = 1

            

            With ActiveSheet



        For i = 1 To rct

            

            .EnableCalculation = False

            .EnableCalculation = True

            .Calculate

            

            i = i + 1

        Next i

            

            End With

    

End Sub

Diese Subroutine rufe ich durch Drücken einer Schaltfläche im child Dokument auf.
{Entwicklertools: Einfügen Schaltfläche. Dann: Makro zuweisen - 'child.xlsm'!TriggerRC }.
Alle voneinander abhängigen Zellen werden somit mit einem einzigen Mausklick aufgelöst und erhalten die erwarteten Werte.

Beliebteste Forumthreads (12 Monate)

Anwenden einer Berechnungsformel aus einem anderen Dokument

Forumthread: Anwenden einer Berechnungsformel aus einem anderen Dokument

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

VBA Beispiele zu diesem und ähnlichen Themen

Beliebteste Forumthreads (12 Monate)

6
Beiträge zum Forumthread

Beiträge zu diesem Forumthread