Aktualisieren von Zellen

Laika · 19. Mai 2012 um 13:11

Hallo,

wohl eine alte Sache: Zellen, die einen Bezug zu einer anderen haben, werden nicht aktualisiert - z.B. über eine Function: In B1 steht

= funktion("A1")

wobei „funktion“ etwas ist, das in VBA irgendwas macht, berechnet. Das ist so, wenn

ich in der Quellzelle etwas ändere,
ich die Zelle B1 aktiviere und F9 drücke.
Nix passiert. Erst, wenn ich die Zelle B1 lösche und „Rückgängig“ mache, wird’s aktualisiert.

Wer kann mir einen Tipp geben?

Danke
Laika
der Office 2003 nutzt

Reinhard_fb8cb7 · 19. Mai 2012 um 14:39

wohl eine alte Sache: Zellen, die einen Bezug zu einer anderen
haben, werden nicht aktualisiert - z.B. über eine Function: In
B1 steht

= funktion(„A1“)

wobei „funktion“ etwas ist, das in
VBA irgendwas macht, berechnet. Das ist so, wenn

ich in der Quellzelle etwas ändere,

ich die Zelle B1 aktiviere und F9 drücke.
Nix passiert. Erst, wenn ich die Zelle B1 lösche und
„Rückgängig“ mache, wird’s aktualisiert.

Hallo Laika,

der fachjargon für eine selbstgeschriebene Funktion ist
BDF (benutzerdefinierte Funktion) und UDF (wie in deutsch, halt User anstelle Benutzer) falls du mal über diese Begriffe im Internet
stolperst.

Da ist in der Tat ein Problem mit dem automatischen Aktualisieren.
Was für ein Hintergrund waram das geschieht ist wußte ich mal teilweise, hab’s aber vergessen

Hier einige Dinge die du tun kannst:

- Zu Anfang des Codes der Funktion einfügen: Application.Volatile 
- die Formel so schreiben: =Funktion("A1")+Jetzt()\*0
- wie eben, nur halt bei Text: =Funktion("A1")&Links(Jetzt();0)
- in eine freie Zelle schreiben =B1

Ist das was mir spontan einfiel, wenn das nicht hilft, zeige mal kleine Mappe.

Gruß
Reinhard

Laika · 19. Mai 2012 um 15:26

Danke Reinhard, funktioniert!

Hier einige Dinge die du tun kannst:

Zu Anfang des Codes der Funktion einfügen:
Application.Volatile

Das allein bringt es noch nicht …

die Formel so schreiben: =Funktion(„A1“)+Jetzt()*0

… aber damit alleine funktioniert es, d.h. „Volatile“ ist (hier) nicht erforderlich.
Ich meine, es in einem schon älteren Buch („VBA-Programmierung mit Excel 7“, Michael Kofler) gelesen zu haben, finde es aber nicht wieder.
Also alles ok, die Jetzt-Function veranlasst Excel wohl zu einer sofortigen Aktualisierung

Gruß auch
Laika

Laika · 19. Mai 2012 um 15:36

Noch was …
Ich benutze das zu Folgendem:
Aus einer Punkt-Kurve wird eine quadratische Regression bestimmt. Im Diagramm wird die Formel angezeigt, z.B.

y = 0,00073x2 + 0,0568x - 22,38

die paste 'n copy ich in eine Zelle, in drei anderen Zellen werden mittels (Feld-) Function daraus die Koeffizienten extrahiert:

= poly\_koeffizienten()

wobei die Formel per Zellenname übergeben wird.
Es ergibt sich dann in drei aufeinander folgenden Zellen

0,00073 0,0568 -23,38

Diese Werte brauche ich dann für weitere Berchnungen.
Ziemlich umständlich, gell? Habe aber noch nix Besseres gefunden.
Weißt Du was? Oder Thomas, der hier vielleicht mitliest?

Laika

Reinhard_fb8cb7 · 19. Mai 2012 um 15:57

Hallo Laika,

Danke Reinhard, funktioniert!

sehr schön.

Application.Volatile

Das allein bringt es noch nicht …

Möglich, aber das Verhalten von Excel in so Fällen ist komplex.
Gelegentlich reicht es, dann wieder nicht.
Liegt an der jeweiligen Tabelle oder ob grad Samstag ist *grins*

Ich hab ja nur aufgezählt was mir spontan dazu einfällt.
Habe vllt. vergessen zu schreiben daß du bei Tests auch mehreres
dessen was ich schrieb zusammen testen solltest.

Noch eines zur reinen Info, dieses .Volatile in einer Funktion,
wenn es denn funktioniert, wird dann immer ausgeführt.
Auch wenn sich irgendwas in einer der 500 tabellen was ändert.
Wenn du nun diese Funktion 100.000 mal benutzt kann das schon die Performance heuntersetzen.
Aber das ist ungetestes Halbwissen.

Ich meine, es in einem schon älteren Buch („VBA-Programmierung
mit Excel 7“, Michael Kofler) gelesen zu haben, finde es aber
nicht wieder.

„schon älter“? Völlig wurscht. Michael Kofler ist gut.
Allerdings, „Excel 7“? Sagt mir jetzt nix.
Excel 97 ist Vers. 8.0, Excel 2000 ist Vers. 9.0 usw.
(Excel für Mac zählt anders als Excel für Win)
Ach, grad nachgeschaut Vers. 7.0 ist Excel 95, das hatte ich nie.

Also alles ok, die Jetzt-Function veranlasst Excel wohl zu
einer sofortigen Aktualisierung

Sehe ich auch so aber ich kann dir gleich sagen das funktioniert nicht immer wie ich schon erfahren mußte.
Freue dich daß es funktioniert und neues problem ist neue Herausforderung *gg*

Gruß
Reinhard

Reinhard_fb8cb7 · 19. Mai 2012 um 16:02

Hallo Laika,

Aus einer Punkt-Kurve wird eine quadratische Regression
bestimmt.

aha, wo das in Mathe dran kam war ich wie bei vielem grad auf’m Klo

Im Diagramm wird die Formel angezeigt, z.B.

y =
0,00073x2 + 0,0568x - 22,38

die paste 'n copy ich in eine
Zelle, in drei anderen Zellen werden mittels (Feld-) Function
daraus die Koeffizienten extrahiert:

= poly_koeffizienten()

wobei die Formel per Zellenname übergeben wird.
Es ergibt sich dann in drei aufeinander folgenden Zelle
n

0,00073 0,0568 -23,38

Diese Werte brauche ich dann
für weitere Berchnungen.

Interessant.

Ziemlich umständlich, gell?

K.A. Mir unklar wie du da eine Formel aus einem Diagramm kopierst
und mit Paste dann Werte in drei Zellen erscheinen.
Beispielmappe die ich kapiere?

Gruß
Reinhard

Thomas_Ramel · 19. Mai 2012 um 21:25

Grüezi Laika

Zu Anfang des Codes der Funktion einfügen:
Application.Volatile

Das allein bringt es noch nicht …

die Formel so schreiben: =Funktion(„A1“)+Jetzt()*0

… aber damit alleine funktioniert es, d.h. „Volatile“ ist
(hier) nicht erforderlich.

Das ist Hemd wie Hose, sprich, beides dasselbe und löst nicht das Problem sondern bekämpft bloss das Symptom (dazu oben mehr).

Mit freundlichen Grüssen

Thomas Ramel

MVP für MS-Excel -

Thomas_Ramel · 19. Mai 2012 um 21:28

Grüezi Laika

wohl eine alte Sache: Zellen, die einen Bezug zu einer anderen
haben, werden nicht aktualisiert - z.B. über eine Function: In
B1 steht

= funktion(„A1“)

wobei „funktion“ etwas ist, das in
VBA irgendwas macht, berechnet. Das ist so, wenn

ich in der Quellzelle etwas ändere,

ich die Zelle B1 aktiviere und F9 drücke.
Nix passiert. Erst, wenn ich die Zelle B1 lösche und
„Rückgängig“ mache, wird’s aktualisiert.

Wer kann mir einen Tipp geben?

Funktion so umprogrammieren, dass alle Bereiche auf die sie zugreift als Parameter übergeben werden.

Du hast offenbar unreferenzierte Zugriffe auf Bereiche im Tabellenblatt drin, die nur dann ausgewertet und berechnet werden (können) wenn eine komplette Neuberechnung des Tabellenblattes erfolgt.

Sind alle relevanten Bereiche als Parameter übergeben reagiert die Funktion sauber und ohne Zicken, vor allem ohne Application.Voaltile und andere Tricks.

…dazu unten mehr…

Mit freundlichen Grüssen

Thomas Ramel

MVP für MS-Excel -

Thomas_Ramel · 19. Mai 2012 um 21:33

Grüezi Laika

Aus einer Punkt-Kurve wird eine quadratische Regression
bestimmt. Im Diagramm wird die Formel angezeigt, z.B.

y =
0,00073x2 + 0,0568x - 22,38

die paste 'n copy ich in eine
Zelle, in drei anderen Zellen werden mittels (Feld-) Function
daraus die Koeffizienten extrahiert:

= poly_koeffizienten()

wobei die Formel per Zellenname übergeben wird.
Es ergibt sich dann in drei aufeinander folgenden Zelle
n

0,00073 0,0568 -23,38

Diese Werte brauche ich dann
für weitere Berchnungen.
Ziemlich umständlich, gell?

Ja, allerdings - vor allem geht das so nicht automatisch weil immer Hand (oder Maus) -Arbeit fürs Copy/Paste der Formel aus dem Diagramm notwendig ist.
Des weiteren ist die Genauigkeit der Koeffizienten so auch immer von den eingestellten NAchkommastellen der Diagramm-Formel abhängig.

Weißt Du was? Oder Thomas, der hier vielleicht mitliest?

Ja, berechne die Koeffizienten direkt aus den Daten im Tabellenblatt mit der Funktion RGP() - diese kann dir, richtig angewandt, direkt in einzelne Zellen die Koeffizienten deiner Trendlinie liefern.
Damit läuft das Ganze dann automatisch sobald neue Daten im Tabellenblatt stehen und auch die Genauigkeit der Werte hat die höchst mögliche Güte.

Worum genau geht es denn in dieser Mappe?
Kannst Du sie (oder zumindest den relevanten Teil) auf einen WebSpace hochladen und dann hier den Link nennen?
‚Wir‘ bauen das Ganze dann entsprechend um…

Mit freundlichen Grüssen

Thomas Ramel

MVP für MS-Excel -

Laika · 20. Mai 2012 um 10:22

Moin Reinhard,

aha, wo das in Mathe dran kam war ich wie bei vielem grad
auf’m Klo

Ich nicht, aber bei uns kam das nicht an der Schule. Erst als ich dann „ein paar Silvester an der Uni oxidiert habe“ ) habe ich was drüber gehört. Aber das ist laaaaange her, heute weiss ich nur noch, was das ist und wozu das gut ist. Eine Regression selber aus den Werten berechnen … da müsste ich mich erstmal wieder in die Grundlagen einarbeiten. Aber wozu? Es gibt ja Excel!

K.A. Mir unklar wie du da eine Formel aus einem Diagramm
kopierst und mit Paste dann Werte in drei Zellen erscheinen.

Zum Punktehaufen kannst Du eine „Trendlinie“ erzeugen - linear, polynomisch, exponentiell - je nachdem, wie der Punktehaufen aussieht und wie Du glaubst, eine gute Annäherung mit geringen Abweichungen zu bekommen. Dann kannst Du in den Optionen angeben, dass die Formel angezeigt werden soll. Und das ist dann z.B. eine quadratische wie ich sie nannte. Die kann man pasten und in eine Zelle kopieren.

Beispielmappe die ich kapiere?

Hmm … da ist nix drin erklärt, sondern nur angewendet.
Schau mal in die Antwort an Thomas.

Gruß
Laika

Laika · 20. Mai 2012 um 11:18

Moin Thomas,

Ja, allerdings - vor allem geht das so nicht automatisch weil
immer Hand (oder Maus) -Arbeit fürs Copy/Paste der Formel aus
dem Diagramm notwendig ist.

Ja, das ist ja das (etwas) Lästige, das ich umgehen möchte.

Des weiteren ist die Genauigkeit der Koeffizienten so auch
immer von den eingestellten NAchkommastellen der
Diagramm-Formel abhängig.

Das stört eher weniger, solange ich in Excel bleibe, da lasse ich einfach genügend Nachkommastellen drin. Auch streuen die Messwerte so, dass das nicht stört, auch bei Bestimmtheitsmaßen von 0,99, die ich i.d.R. erreiche.
Weiterhin habe ich Ungenauigkeiten bei der Weiterverarbeitung in meinem Microcomputer (MC), der kann nämlich nur eingeschränkte Berechnungen (siehe unten).

Ja, berechne die Koeffizienten direkt aus den Daten im
Tabellenblatt mit der Funktion RGP() - diese kann dir, richtig
angewandt, direkt in einzelne Zellen die Koeffizienten deiner
Trendlinie liefern.

RGP ist doch aber ein linearer Trend!?

Worum genau geht es denn in dieser Mappe?

Nur mal kurz:

Mein MC soll die Aussentemperatur für meine Heizung messen.
Dazu hat er einen NTC (Thermowiderstand) als Sensor.
Der liefert eine Spannung 0 … 2,55 V an den MC.
Über eine Analogeingabe wird diese Spg. in einen „Rohwert“ 0 … 255 umgewandelt.
Jetzt muss ich den Rohwerten die tatsächlichen Aussentemperaturen im festgelegten Messbereich -25 … 40° zuordnen, d.h. ich muss das Ganze eichen.
Das geschieht über die Werte, die ein normales Thermometer neben dem Sensor liefert.
Damit habe ich einen Haufen zusammen gehörende Wertepaare Rohwert/Fertigwert.
Die trage ich in eine Tabelle/Diagramm ein, gebe die Trendlinie/Regression und die Gleichung dazu aus.
Da die Kennlinie des NTC stark gekrümmt ist, muss ich eine nichtlineare Anpassung machen - eine quadratische (Polynom 2. Grades) ist ausreichend, muss ausreichend sein.
Die Koeffizienten will ich einzeln haben, ich benötige sie für die Weiterbearbeitung im MC.
Dieser kann nur 16Bit-Integerrechnung, d.h. Zahlenbereich -32767 … 32768. Das ist dann Fummelei, die hier nicht näher ausgeführt werden soll - Zerlegen der Koeffizienten in integerverarbeitbare Zahlen, so dass der zulässige Zahlenbereich eingehalten wird … Rentnerspass könnte man sagen )
Das Umformen und Zerlegen der Koeffizienten mache ich in Excel, erzeuge mir ein paar Zeilen, die ich einfach und formatrichtig in mein MC-Programm (ein BASIC-Dialekt) kopieren kann.
Das kommt natürlich nicht sehr oft vor, nur wenn ich was umbaue.

Kannst Du sie (oder zumindest den relevanten Teil) auf einen
WebSpace hochladen und dann hier den Link nennen?
‚Wir‘ bauen das Ganze dann entsprechend um…

Das ist nett von Euch, habe nur keinen Webspace, bin sehr zurückhaltend, was Veröffentlichungen im Netzt angeht. Da muss man Name, Adresse usw. angeben, dann wird man mit Werbung bombardiert - das mag ich nicht.
Ich habe das auch für andere Zwecke bisher nicht benötigt, woraus sich auch gleich ergibt: Das relativ seltene Kopieren der Formel ist weniger Aufwand als das Einrichten des Webspace, hochladen, hin und her … nichts für ungut.

Gruss und schönen Sonntag, geht gleich in die Besenwirtschaft
Laika

Thomas_Ramel · 20. Mai 2012 um 12:03

Grüezi Laika

Ja, allerdings - vor allem geht das so nicht automatisch weil
immer Hand (oder Maus) -Arbeit fürs Copy/Paste der Formel aus
dem Diagramm notwendig ist.

Ja, das ist ja das (etwas) Lästige, das ich umgehen möchte.

Wie vorgeschlagen geht das mit RPG() wunderbar.

Des weiteren ist die Genauigkeit der Koeffizienten so auch
immer von den eingestellten NAchkommastellen der
Diagramm-Formel abhängig.

Das stört eher weniger, solange ich in Excel bleibe, da lasse
ich einfach genügend Nachkommastellen drin.

Ich meinte die Anzahl Nachkommastellen, die im Diagramm in der Formel eingestellt sind - diese kann/sollt man auf 15-20 erhöhen um korrekte(er) Koeffizienten zu erhalten.

Auch streuen die
Messwerte so, dass das nicht stört, auch bei
Bestimmtheitsmaßen von 0,99, die ich i.d.R. erreiche.

Das ist ja schon relativ gut, die Trendlinie im Diagramm würde ich drin lassen als optische Kontrolle. Polynome (vor allem höheren Grades) können schon mal bessere Bestimmtheitsmasse zeigen, passen dann allerdings nicht mehr wirklich zum eigentlichen Kurven-Verlauf.
Das kommt bei dir aber IMO weniger zum tragen, denke ich.

Ja, berechne die Koeffizienten direkt aus den Daten im
Tabellenblatt mit der Funktion RGP() - diese kann dir, richtig
angewandt, direkt in einzelne Zellen die Koeffizienten deiner
Trendlinie liefern.

RGP ist doch aber ein linearer Trend!?

Ich schrieb ja: richtig angewandt

Du kannst mit RGP() auch problemlos polynomische Koeffizienten berechnen lassen, dafür einfach die X-Werte potenzieren und Du erhältst die Koeffizienten als einfache Matrix.

Worum genau geht es denn in dieser Mappe?

Nur mal kurz:

Ich kürze mal ab:

Damit habe ich einen Haufen zusammen gehörende Wertepaare
Rohwert/Fertigwert.

Die trage ich in eine Tabelle/Diagramm ein, gebe die
Trendlinie/Regression und die Gleichung dazu aus.

In welchen Tabellenbereich trägst du diese Wertepaare ein?
Mit diesem Ansatz (und vielleicht ein paar Beispielwerten) könnten wir den Spiess umdrehen und die Daten selber aufbauen um dir hier dann nur noch die Formeln zu nennen.

Da die Kennlinie des NTC stark gekrümmt ist, muss ich eine
nichtlineare Anpassung machen - eine quadratische (Polynom 2.
Grades) ist ausreichend, muss ausreichend sein.

OK, das ist klar und wie gesagt mit RGP() auch kein Problem.

Die Koeffizienten will ich einzeln haben, ich benötige sie
für die Weiterbearbeitung im MC.

Die hast Du dann so in 3 eigenen Zellen stehen (für eine Potenz 2. Grades).

Dieser kann nur 16Bit-Integerrechnung, d.h. Zahlenbereich
-32767 … 32768. Das ist dann Fummelei, die hier nicht näher
ausgeführt werden soll - Zerlegen der Koeffizienten in
integerverarbeitbare Zahlen, so dass der zulässige
Zahlenbereich eingehalten wird … Rentnerspass könnte man
sagen )

Dieser Spass kann dir dann bleiben, nur das Rumkopieren aus dem Diagramm kannst Du deutlich einfacher haben.

Kannst Du sie (oder zumindest den relevanten Teil) auf einen
WebSpace hochladen und dann hier den Link nennen?
‚Wir‘ bauen das Ganze dann entsprechend um…

Das ist nett von Euch, habe nur keinen Webspace, bin sehr
zurückhaltend, was Veröffentlichungen im Netzt angeht. Da muss
man Name, Adresse usw. angeben, dann wird man mit Werbung
bombardiert - das mag ich nicht.

OK, auch das ist kein Prpblem.

Nenne bitte die Adressen der Zellen in die Du deine Werte einträgst und bitte auch eine Datenreihe hier in einem Beitrag, das sollte schon ausreichend sein um dir das Prinzip mit RGP() anhand eines Polynoms 2-Grades zu zeigen.

Mit freundlichen Grüssen

Thomas Ramel

MVP für MS-Excel -

Thomas_Ramel · 20. Mai 2012 um 12:45

Grüezi Laika nochmals

Du kannst mit RGP() auch problemlos polynomische Koeffizienten
berechnen lassen, dafür einfach die X-Werte potenzieren und Du
erhältst die Koeffizienten als einfache Matrix.

Hier mal ein kleines Beispiel, das dies veranschaulichen soll.
Die Werte in Spalte A sind einfach von 1-10 gewählt.
In Spalte B werden die WErte für den Versuch hier mit der Funktion ZUFALLSBEREICH() ermittelt um die Dynamik zu zeigen.

Markiere den Bereich D2:F6 , gib die unten stehende Formel ein und schliesse als Matrixformal ab.

Markiere nun noch den Bereich A2:B11 und füge ein XY-Punkte-Diagramm hinzu in dem Du dann auch gleich die Trendlinie anzeigen lässt.

Vergleiche dann die Koeffizienten aus dem Diagramm mit den Zellen D2:F2.

Tabellenblatt: [Mappe1]!Tabelle1
 │ A │ B │ C │ D │ E │ F │
───┼─────────┼─────────┼───┼────────────┼────────────┼────────────┤
 1 │ X-Werte │ Y-Werte │ │ K1 │ K2 │ K3 │
───┼─────────┼─────────┼───┼────────────┼────────────┼────────────┤
 2 │ 1 │ 8 │ │ -2.25 │ 22.9924242 │ 0.16666667 │
───┼─────────┼─────────┼───┼────────────┼────────────┼────────────┤
 3 │ 2 │ 35 │ │ 1.0588924 │ 11.9518888 │ 28.6175344 │
───┼─────────┼─────────┼───┼────────────┼────────────┼────────────┤
 4 │ 3 │ 78 │ │ 0.41400572 │ 24.3314949 │ #NV │
───┼─────────┼─────────┼───┼────────────┼────────────┼────────────┤
 5 │ 4 │ 63 │ │ 2.47275459 │ 7 │ #NV │
───┼─────────┼─────────┼───┼────────────┼────────────┼────────────┤
 6 │ 5 │ 38 │ │ 2927.84848 │ 4144.15152 │ #NV │
───┼─────────┼─────────┼───┼────────────┼────────────┼────────────┤
 7 │ 6 │ 48 │ │ │ │ │
───┼─────────┼─────────┼───┼────────────┼────────────┼────────────┤
 8 │ 7 │ 84 │ │ │ │ │
───┼─────────┼─────────┼───┼────────────┼────────────┼────────────┤
 9 │ 8 │ 15 │ │ │ │ │
───┼─────────┼─────────┼───┼────────────┼────────────┼────────────┤
10 │ 9 │ 5 │ │ │ │ │
───┼─────────┼─────────┼───┼────────────┼────────────┼────────────┤
11 │ 10 │ 26 │ │ │ │ │
───┴─────────┴─────────┴───┴────────────┴────────────┴────────────┘
Benutzte Formeln:
B2 : =ZUFALLSBEREICH(1;100)
B3 : =ZUFALLSBEREICH(1;100)
B4 : =ZUFALLSBEREICH(1;100)
B5 : =ZUFALLSBEREICH(1;100)
B6 : =ZUFALLSBEREICH(1;100)
B7 : =ZUFALLSBEREICH(1;100)
B8 : =ZUFALLSBEREICH(1;100)
B9 : =ZUFALLSBEREICH(1;100)
B10: =ZUFALLSBEREICH(1;100)
B11: =ZUFALLSBEREICH(1;100)

Benutzte Matrixformeln:
D2:F6 : {=RGP($B$2:blush:B$11;$A$2:blush:A$11^{1\2};WAHR;WAHR)}

(Matrixformeln nicht mit "Enter" sondern mit "Strg+Shift+Enter" eingeben.
Die Spezialklammern nicht manuell eingeben, sie werden von Excel erzeugt.)


A1:F11
haben das Zahlenformat: Standard

Tabellendarstellung erreicht mit dem Code in FAQ:2363

Mit freundlichen Grüssen

Thomas Ramel

MVP für MS-Excel -

Laika · 21. Mai 2012 um 10:24

Guten Morgen Thomas,

Hier mal ein kleines Beispiel, das dies veranschaulichen soll.

Du gibts Dir sehr viel Mühe, ich werde schon wieder müde des daran Arbeitens, es ist ja nur eine kleine Unschönheit (das extra Kopieren müssen). Ich muss nämlich langsam mal wieder was anderes machen. Trotzdem … weiter geht’s …

Markiere den Bereich D2:F6 , gib die unten stehende Formel ein
und schliesse als Matrixformal ab.

Das funktioniert nicht bei mir: Der Teil {1\2} wird als Fehler abgewiesen.

Benutzte Formeln:
B2 : =ZUFALLSBEREICH(1;100)

Diese Funktion mag mein Excel auch nicht.

Benutzte Matrixformeln:
D2:F6 : {=RGP($B$2:blush:B$11;$A$2:blush:A$11^{1\2};WAHR;WAHR)}

Wie gesagt, die wird mit Fehler abgewiesen. Das „Strg+Shift+Enter“ kenne ich natürlich, habe ich schon oft benutzt.

Hier meine Zahlen/Wertepaare:

Links sind die „Rohwerte“ (Rubrikenachse) in Spalte A, rechts die jeweils zugehörigen „Fertigwerte“ in °C (Größenachse) in Spalte B. Dafür mache ich ein Punktdiagramm mit Trendlinie. Als Gleichung dazu ergibt sich

 y = 0,000770x2 + 0,044298x - 21,522096

mit

 R2 = 0,998799

als Bestimmtheitsmaß, also sehr gut. Diese Formel kopiere ich in eine Zelle namens

 TA\_Gleichung

TA wie Außentemperatur. In drei Zellen (darunter) trage ich meine Function zum Extrahieren der Koeffizienten ein:

 = poly\_koeffizienten() + JETZT()\*0

Alle Zellen sind als Standard formatiert.
Die Function sieht so aus:

Function poly\_koeffizienten()

'Polynom-Koeffizienten aus der Trendgleichung 2. Grades erzeugen.
'Die Poly-Gleichung muss aus dem Diagramm in die Tabelle kopiert werden.
'
'Poly-Gleichung: y = koeffizient\_1 \* x2 + koeffizient\_2 \* x - 24,9998855
' i\_koeff = 0 1 2

 Dim ergebnis\_feld!(3)
 Dim i1%, z1$, i\_koeff%, z\_koeff$
 '-------------------------------------------------------------------------

 i\_koeff = 0
 z\_koeff = ""
 poly\_koeffizienten = "#?"
 For i1 = 4 To Len([TA\_Gleichung]) 'Start beim ersten Koeff. hinter dem Gleichheitszeichen
 z1 = Mid([TA\_Gleichung], i1, 1)
 Select Case z1
 Case "x"
 'x = TA erreicht, der Koeff. ist fertig
 ergebnis\_feld(i\_koeff) = z\_koeff
 i\_koeff = i\_koeff + 1
 i1 = i1 + 1
 z\_koeff = ""
 Case Else
 'Ziffer oder Dez.komma gefunden
 z\_koeff = z\_koeff & z1
 End Select
 Next i1
 ergebnis\_feld(i\_koeff) = z\_koeff 'den letzten Koeff. eintragen,
 'da er kein "x" hat, wird er oben nicht erfasst!
 poly\_koeffizienten = ergebnis\_feld

End Function

Die gewonnenen Koeffizienten verarbeite ich dann weiter. Ich hoffe, Dir gefällt das.

Gruss
Laika

Thomas_Ramel · 21. Mai 2012 um 10:52

Grüezi Laika

Benutzte Matrixformeln:
D2:F6 : {=RGP($B$2:blush:B$11;$A$2:blush:A$11^{1\2};WAHR;WAHR)}

Wie gesagt, die wird mit Fehler abgewiesen. Das
„Strg+Shift+Enter“ kenne ich natürlich, habe ich schon oft
benutzt.

Sorry, mein Fehler - verwende den Punkt als Matrixtrenner anstelle des Backlash:

=RGP($B$2:blush:B$11;$A$2:blush:A$11^{1.2};WAHR;WAHR)

Ich habs gerade nochmal geprüft, das passt bestens.

Mit freundlichen Grüssen

Thomas Ramel

MVP für MS-Excel -

Laika · 21. Mai 2012 um 12:33

Guten Tag Thomas,

schnelle Reaktion auch am Tage, wenn Du (wahrscheinlich) arbeiten musst - ich als Rentner …

Sorry, mein Fehler - verwende den Punkt als Matrixtrenner
anstelle des Backlash:

=RGP($B$2:blush:B$11;$A$2:blush:A$11^{1.2};WAHR;WAHR)

Ja, das geht. Ich hatte schon anderes probiert - normalen Schrägen, geschweifte Klammer ersetzen, …

Aber: Die Tabelle muss lückenlos sein! Wenn da leere Zellen drin sind, kommt #WERT! Ich muss also den Wertebereich immer anpassen - mehr Arbeit, als die Gleichung zu kopieren … müssten wir noch was erfinden.
Wir hatten das schon mal vor einer Weile, als ich eine Gleichung 3. Grades machen wollte. War dann irgendwie abgerissen.

Gruss
Laika

Thomas_Ramel · 21. Mai 2012 um 12:45

Grüezi Laika

Sorry, mein Fehler - verwende den Punkt als Matrixtrenner
anstelle des Backlash:

=RGP($B$2:blush:B$11;$A$2:blush:A$11^{1.2};WAHR;WAHR)

Ja, das geht. Ich hatte schon anderes probiert - normalen
Schrägen, geschweifte Klammer ersetzen, …

Aber: Die Tabelle muss lückenlos sein! Wenn da leere Zellen
drin sind, kommt #WERT! Ich muss also den Wertebereich immer
anpassen - mehr Arbeit, als die Gleichung zu kopieren …
müssten wir noch was erfinden.

Wenn Du ‚garantieren‘ kannst (und warum sollte das nicht machbar sein?), dass die Werte immer lückenlos untereinander stehen, dann kannst Du sehr einfach zwei Bereichsnamen verwenden, welche sich auf die Anzahl der Datensätze anpassen und diese dann in der Formel verwenden.

Mit freundlichen Grüssen

Thomas Ramel

MVP für MS-Excel -

Laika · 21. Mai 2012 um 22:34

Guten Abend Thomas,

jetzt lasse ich mich ja „komplett an Deinem Tropf hängen.“

Wenn Du ‚garantieren‘ kannst (und warum sollte das nicht
machbar sein?), dass die Werte immer lückenlos untereinander
stehen, …

Das geht schon.

… dann kannst Du sehr einfach zwei Bereichsnamen
verwenden, welche sich auf die Anzahl der Datensätze anpassen
und diese dann in der Formel verwenden.

Hilf mir mal, habe in der Hilfe nix gefunden. Ich würde wieder eine „Billiglösung“ mit Abzählen der Zellen und definieren eines Namens zu den belegten Zellen machen - ist wohl auch noch nicht ganz durchdacht, ob das geht …

Gruessle
Laika

Thomas_Ramel · 21. Mai 2012 um 22:51

Grüezi Laika

jetzt lasse ich mich ja „komplett an Deinem Tropf hängen.“

Na, dann lass mal ‚hängen‘

Wenn Du ‚garantieren‘ kannst (und warum sollte das nicht
machbar sein?), dass die Werte immer lückenlos untereinander
stehen, …

Das geht schon.

Gut, das hatte ich mir schon so gedacht.
In xl2007 wäre das übrigens noch um einiges einfacher, aber hier eine Lösung, die in xl2003 eigentlich klappen müsste.

… dann kannst Du sehr einfach zwei Bereichsnamen
verwenden, welche sich auf die Anzahl der Datensätze anpassen
und diese dann in der Formel verwenden.

Hilf mir mal, habe in der Hilfe nix gefunden. Ich würde wieder
eine „Billiglösung“ mit Abzählen der Zellen und definieren
eines Namens zu den belegten Zellen machen - ist wohl auch
noch nicht ganz durchdacht, ob das geht …

Schau dir die Mappe und auch die Definition der beiden Bereichsnamen im Namensmanager näher an.

Füge einfach neue oder andere und mehr oder weniger Werte hinzu und beobachte die Veränderungen der Trendlinien-Formel im Diagramm einerseits und die Veränderungen der Koeffizienten im RGP()-Bereich andererseits. Ich habe die Werte an beiden Orten mit derselben Anzahl Nachkommastellen formatiert, sodass Übereinstimmungen sehr schnell sichtbar werden.

Als weitere Gegenprüfung könnte man nun mit den Koeffizienten eine weitere Spalte in der Liste ausfüllen und diese dann ebenfalls im Diagramm darstellen lassen.

http://users.quick-line.ch/ramel/Demo-Daten/tr_Trend…

Mit freundlichen Grüssen

Thomas Ramel

MVP für MS-Excel -

Reinhard_fb8cb7 · 22. Mai 2012 um 00:11

Wenn Du ‚garantieren‘ kannst (und warum sollte das nicht
machbar sein?), dass die Werte immer lückenlos untereinander
stehen, …

Das geht schon.

Hallo Laika,

zu dem Punkt.
Du kannst schon eine Liste erzeugen die automatisch lückenlos ist:
http://excelformeln.de/formeln.html?welcher=160

Da sehe ich aber ungetestet ein potentielles Problem.
Diese lückenlose Liste hat ja oben die Treffer aufgelistet aber
untendrunter haben ja weitere Zellen Formeln.
Müssen sie ja haben, damit wenn in der Ursprungsspalte neue
Einträge reinkommen diese auch angezeigt werden.

Jetzt müßte man herausfinden ob da die Formel für dynamischen
Zellbereich nicht durch die Formeln irritiert wird und zuviel anzeigt,
wenn sie sich auf diese lückenlose Liste beziehen.

Viel besser ist, du stellst das lückenlose manuell sicher.

Gruß
Reinhard