Office: Funktion erstellen ??? oder eine bessere Idee ???

Hallo

Mal eine Frage zum Erstellen eigener Funktionen in Excel.

Am besten ich schildere das Problemchen an einem Beispiel. Ich möchte die Sonnenposition berechnen und habe dazu ein sehr genaues Model. Dieses Model benötigt Tabellen zur Berechnung (siehe unten). Da ich aber nun stündlich (für ein Jahr) die Position der Sonne bestimme möchte brauche ich nun diese Tabellen in jeder Stundenberechnung.

Ich dachte eine Art Funktion, wo die Tabellen hinterlegt sind, könnte das Problem lösen, leider weiß ich nicht ob das überhaupt möglich ist? Habt Ihr Erfahrungen im selber schreiben von Funktionen, wäre es möglich so etwas zu erstellen oder habt Ihr eine andere (bessere) Idee?

Schöne Grüße
Tauchmann

Die Summe der Tabelle wird aus einem übergebenen Wert (Arbeitsblatt 1) mit den Werten in der Tabelle berechnet.

			Σ R1	100378,567146
Row	A	B	C	R1i
0	103019	1,10749	6283,07585	101068,260437
1	1721	1,0644	12566,1517	52,289269
2	702	3,142	0	-701,999942
3	32	1,02	18849,23	-30,970151
4	31	2,84	5507,55	5,152712
5	25	1,32	5223,69	-16,333070
6	18	1,42	1577,34	7,853276
7	10	5,91	10977,08	-9,131780
8	9	1,42	6275,96	8,963082
9	9	0,27	5486,78	-5,516687

 

Hallo,

wie wäre es, wenn Du mal Tante Google bemühen würdest, da kannst Du Dich vor Lösungen gar nicht schnell genug in Sicherheit bringen, auch ohne Tabellen.


Gruß

Edgar

 

Hallo Edgar,

danke für deine Antwort.
Dann war Tante Google bei mir nicht sehr hilfsbereit (.

Ich würde gerne wissen ob ich es nur mit einer eigenen geschrieben Funktion erreichen kann, dass jede Zeile in der Tabelle, mit einem zuvor errechneten Wert, berechnet und anschließend alle Zeilen in einer Summe berechnet werden? Würde es in Excel noch eine andere Möglichkeit geben eine solche Tabelle für viele Variablen darzustellen?

Was meinst du mit „… auch ohne Tabellen“?
Wäre es möglich einen Link zu posten, der das ungefähr so beschreibt wie du das lösen würdest? Oder einen Link der mich in die richtige Richtung (Herangehensweise) führt?

Einen schönen Abend
Tauchmann

 

Hallo,

Tante google sagt:

da gibt es Formeln, Anfragen in Excel-Foren mit Lösungen (auch Funktionen), alles was das Herz begehrt. Sieh mal nach, was Du damit anfangen kannst. Meist brauchst Du dann keine Tabelle mit Werten, sondern nur Formeln (es sei denn, Du willst ein Diagramm zeichnen).

 

Hallo Edgar,

Danke für die Antwort.
Die zur Berechnung der Sonnenposition nötigen Formeln habe ich bereits. Das Problem im Internet ist das es viele Möglichkeiten zur Berechnung gibt. Ich habe auch schon eine fertig, aber leider sind die Formeln, die nicht auf diese Tabellen zugreifen, nicht sehr korrekt und haben bei niedrigen Sonnenständen beachtliche Abweichungen (mehrere Grad !!!). Das Model was ich gerne versuchen möchte greift auf Tabellen zurück die es erlauben eine Abweichung von nur 0.0003 Grad bis 0.01 Grad zu erhalten.

Da ich aber für die 8760 Stunden im Jahr die Position mit dem sehr genauen Modell berechnen möchte, müsste ich mir eigene Funktionen, die diese Tabellen beschreiben, erstellen. Das ist was ich mir so vorstelle. Nun habe ich leider keine Ahnung vom erstellen eigener Formeln und wollte nur wissen wie man so etwas am besten angeht oder ob es eine anderen (besseren) Lösungsweg in Excel gibt?

Hast du Vorschläge, gerne auch Links, die mir weiterhelfen könnten?

Schöne Grüße
Tauchmann

 

Hallo Tauchmann,

abgesehen davon das ich von Sonnen-Positions-Berechnung absolut keinen Plan habe,
ich verstehe auch nicht wirklich was Du nun genau möchtest.

Mal schreibst Du von "eigene Funktion erstellen", dann davon dass Du "keine Ahnung vom erstellen eigener Formeln" hast, und irgendwie willst Du irgendwelche Tabellen benutzen (oder willst Du sie erstmal erstellen?).

Vielleicht fügst Du mal eine Beispieldatei an oder versuchst auf anderem Wege uns zu verdeutlichen was genau Du möchtest, wo genau Du Hilfe brauchst.

Möglicherweise erhöht dies Deine Chance auf eine zielführende Antwort.

 

Also die Google-Suche nach "Sonnenstand berechnen" gibt mir gleich als erstes einen Wikipedia-Treffer zurück. Dort fand ich dann ab der Überschrift "Genaue Berechnung des Sonnenstandes für einen Zeitpunkt" einen Haufen Formeln. Diese hab ich mal versucht, in Excel nachzubauen.

Bis zu einem gewissen Punkt bin ich gekommen, aber dann hakt's wohl irgendwie mit der unterschiedlichen Behandlung von Grad (Wiki) und Bogenmaß (Excel). Die Ekliptikalkoordinaten passen noch zu den von Wiki errechneten, aber bei den Äquatorialkoordinaten (Rektaszension) läuft's aus dem Ruder.

Nun hab ich von dem ganzen Kladderadatsch irgendwie auch sowas von überhaupt keine Ahnung, aber vielleicht hilft Dir das im Anhang von mir verzapfte ja schon mal irgendwie weiter.

Gruß, Michael

 

Hallo Michael,

Das ist der springende Punkt. Diesen Artikel kann jeder finden, auch Tauchmann. Das hätte er genauso nachbauen können und dann hätte er fragen können. Deswegen habe ich auch nichts gebastelt.
Bei Excel wird im Bogenmaß gerechnet, nach folgender Beziehung:

Oder auch mit der Funktion BOGENMAß

Gruß

Edgar

 

Hallo Leute,

ich möchte die Sonnenposition sehr !!! genau berechnen. Da die Formeln in Wikipedia (oder andere) nur ungenügend genau sind, habe ich mich für eine Berechnungsmethode entschieden die sehr genau ist (hier der Link zum Paper http://www.nrel.gov/docs/fy08osti/34302.pdf ). Das Berechnungsmodel ist für einen bestimmten Augenblick z.B. 15/July/2011 -11:00:00 ausgelegt. Das Model greift auf feste Tabellenwerte zurück die z.B. mit dem „Julian Ephemeris Millennium” Wert multipliziert werden. Da sich mit jeder Zeitänderung, also z.B. 15/July/2011 -12:00:00, der „Julian Ephemeris Millennium” Wert ändert, ändern sich die Summe der berechneten Werte aus den Tabellen auch.

Die Tabellenwerte werden etwas beschreiben was nicht veröffentlicht werden soll (oder wie die darauf gekommen sind) und genau solche Sachen machen das Modell halt sehr genau und schwierig. Zum Beispiel hat die NASA ein Model was den Einfluss andere Planeten und sonsiges Zeugs beachtet.

Meine Absicht ist es, dass ich für jede Stunde im Jahr (8760 Stunden) die neuen Summenwerte der Tabellen benötige, um die genaue Position zu bestimmen.

Deshalb denke ich mir, dass ich die Tabellen, mit den festen Werten, als eigenständige Funktion erstellen müsste, um immer wieder darauf zuzugreifen.

Leider habe ich absolut keine Ahnung vom erstellen eigener Funktionen und ob es überhaupt möglich ist Tabellen (siehe ersten Beitrag) in einer eigenen Funktion darzustellen?

Kann man eine Tabellen in einer Funktion darstellen?

Schöne Grüße
Tauchmann

 

Hallo!

Ich habe mir die PDF-Datei etwas angeschaut. Dort wird ein C-Programm abgedruckt, das mehrere mehrdimensionale Arrays mit Konstanten enthält. Ich nehme an, Du willst wissen, ob man solche Arrays auch in VBA eingeben kann?
Antwort: Man kann es.
Das Problem ist nur, will man die Konstanten auf die bequeme Art wie in C in diese Arrays eingeben, ist die im Hintergrund gespeicherte Datenstrukur nicht so effizient wie in C.
Will man bei der Datenstruktur die gleiche Effizienz wie in C, dann ist die Eingabe der Konstanten im Programm leider etwas umständlich.
Effizienz heißt in diesem Zusammenhang: Größe der Datenstruktur und Schnelligkeit beim Zugriff auf und dem Verarbeiten der Daten.

1) Die bequeme Art der Konstantenfestlegung im Code: zB.

Code:

Public Matrix As Variant
Matrix = Array(Array(103019, 1.10749, 6283.07585, 101068.260437),
               Array(1721, 1.0644, 12566.1517, 52.289269),
               Array(702, 3.142, 0 , -701.999942))

2) Die effizientere Art der Konstantenfestlegung im Code: zB.

Code:

Public Matrix(1 to 3, 1 to 4) As Double
Matrix(1,1)=103019: Matrix(1,2)=1.10749: Matrix(1,3)=6283.07585: Matrix(1,4)=101068.260437
Matrix(2,1)=1721: Matrix(2,2)=1.0644: Matrix(2,3)=12566.1517: Matrix(2,4)=52.289269
Matrix(3,1)=702: Matrix(3,2)=3.142: Matrix(3,3)=0: Matrix(3,4)=-701.999942

Beidesmal soll eine 3x4-Matrix namens "Matrix" mit doppeltgenauen Gleitkommazahlen als Elementen angelegt werden.

Bei der 2. Variante wird das Ziel voll und ganz erreicht, doch ist die Eingabe der Konstanten im Code etwas umständlich.

Bei der 1. Variante wird diese Matrix in ein VARIANT-Array hineinverpackt. Die Eingabe im Code ist fast so bequem wie in C. In diesem Array brauchen (im Gegensatz zu C) nicht einmal Dimensionsangaben im Vorhinein festgelegt werden. Daher scheint die faktische 3x4-Dimensionierung nicht in der Deklariation auf, sondern nur in der Anzahl und Schachtelung der ARRAY()-Funktionen. Daher ist es leicht einzusehen, dass der Zugriff auf die einzelnen Array-Elemente (im Hintergrund) viel umständlicher ist als bei der 1. Variante. Zudem muss jedes Array-Element in einem VARIANT-Datenfeld verpackt werden, das zusätzlich den Datentyp-Code enthält (der nicht notwendigerweise DOUBLE sein muss). Aus diesen Strukturen müssen zur Runtime die eigentlichen Nutzdaten herausgeholt werden, woraus man sofort sieht, dass diese Methode bei sehr vielen Daten bzw. bei rechenintensiven Problemen nicht gerade optimal ist.

Der Zugriff auf die einzelnen Array-Elemente im Code ist bei beiden Varianten völlig identisch (was die Syntax anbelangt), zB:

Code:

Matrix(2,3)=2*Matrix(1,3)+4.5*Matrix(1,1)

Man ist auch nicht beschränkt auf 2 Dimensionen, man kann bis zu 60 Dimensionen festlegen (sofern der Speicher dafür ausreicht).

 

Hallo Exl121150

Vielen Dank für deine Antwort.

Ich glaub ich verwirre mich nun selber ?!?!?!?!?.

Was ich in der Funktion genau machen möchte siehe unten.

Im obigen Beispiel ergibt sich die Summe R1 aus den Untersummen der Row’s. Das stimmt, da ich das mal ausprobiert (ins Arbeitsblatt eingeschrieben habe) und mit den Vergleichsergebnissen aus dem Paper verglichen habe.

Formel: Lx = Ax*cos(Bx + Cx * JMC)
(JMC wird vorher berechnet und wäre die einzige Variable die in die Funktion übergeben werden müsste.)

L1 = A1*cos(B1 + C1 * JMC)
L2 = A2*cos(B2 + C2 * JMC)
Usw.
L5 = A5*cos(B5 + C5 * JMC)

Summe R1 = L1 + L2 + … + L5

Muss ich das nun als Matrix machen oder einfach anders?

Ich hoffe das war soweit verständlich?

Schöne Grüße
Tauchmann

 

Hallo!

Das von Dir im 1. Posting verwendete Zahlenmaterial kann mit den Formeln im letzten Posting nicht übereinstimmen. Denn es gibt keinen einzelnen Faktor JMC, der zu Deinen Ax, Bx, Cx und Deinen Formeln für Lx (oder R1i oder wie immer diese Größen auch letztendlich heißen) passen würde. Das Einzige, das passt, ist die Summe für R1i (bzw. Lx).
Die Formel zur Ermittlung von JMC aus Deinen Zahlen muss für jede Zeile x (bzw. i) lauten:
JMC = (ARCCOS(Lx/Ax)-Bx)/Cx
was 10x das gleiche Resultat für JMC ergeben müsste, würden Deine Formeln zu Deinem Zahlenmaterial passen.
Aber selbst wenn man annimmt, dass die Bx (und eventuell Cx) im Gradmaß angegeben sind und daher vor Anwendung des COS(x) ins Bogenmaß (Radianten) konvertiert werden müssen, kommt man mit Deinen Daten und Deinen Formeln bezüglich JMC auf keinen grünen Zweig. Formel:
JMC = (ARCCOS(Lx/Ax)-Bx*PI/180)/Cx
was für alle x für JMC stets den gleichen Wert ergeben müsste - was es aber nicht tut!

Nichts desto trotz habe ich Dir in VBA eine Funktion geschrieben, die Deine Formel umsetzt:

Code:

Option Explicit
Type ABC_Zeile
  A As Double
  B As Double
  C As Double
End Type

Public Function SummeR1(JMC As Double) As Double
  Dim ABC(0 To 9) As ABC_Zeile
  Dim I As Integer, S As Double
  
  With ABC(0): .A = 103019#: .B = 1.10749: .C = 6283.07585: End With
  With ABC(1): .A = 1721#: .B = 1.0644: .C = 12566.1517: End With
  With ABC(2): .A = 702#: .B = 3.142: .C = 0#: End With
  With ABC(3): .A = 32#: .B = 1.02: .C = 18849.23: End With
  With ABC(4): .A = 31#: .B = 2.84: .C = 5507.55: End With
  With ABC(5): .A = 25#: .B = 1.32: .C = 5223.69: End With
  With ABC(6): .A = 18#: .B = 1.42: .C = 1577.34: End With
  With ABC(7): .A = 10#: .B = 5.91: .C = 10977.08: End With
  With ABC(8): .A = 9#: .B = 1.42: .C = 6275.96: End With
  With ABC(9): .A = 9#: .B = 0.27: .C = 5486.78: End With
  
  S = 0#
  For I = 0 To 9
    With ABC(I)
      S = S + .A * Cos(.B + .C * JMC)
    End With
  Next I
  SummeR1 = S
  
End Function

Falls jedoch die Bx und Cx Größen im Gradmaß darstellen, ist folgende Version zu nehmen:

Code:

Option Explicit
Type ABC_Zeile
  A As Double
  B As Double
  C As Double
End Type
Public Function SummeR1(JMC As Double) As Double

  Dim ABC(0 To 9) As ABC_Zeile
  Dim I As Integer, S As Double
  
  With ABC(0): .A = 103019#: .B = 1.10749: .C = 6283.07585: End With
  With ABC(1): .A = 1721#: .B = 1.0644: .C = 12566.1517: End With
  With ABC(2): .A = 702#: .B = 3.142: .C = 0#: End With
  With ABC(3): .A = 32#: .B = 1.02: .C = 18849.23: End With
  With ABC(4): .A = 31#: .B = 2.84: .C = 5507.55: End With
  With ABC(5): .A = 25#: .B = 1.32: .C = 5223.69: End With
  With ABC(6): .A = 18#: .B = 1.42: .C = 1577.34: End With
  With ABC(7): .A = 10#: .B = 5.91: .C = 10977.08: End With
  With ABC(8): .A = 9#: .B = 1.42: .C = 6275.96: End With
  With ABC(9): .A = 9#: .B = 0.27: .C = 5486.78: End With
  
  S = 0#
  For I = 0 To 9
    With ABC(I)
      S = S + .A * Cos(Radiant(.B + .C * JMC))
    End With
  Next I
  SummeR1 = S
  
End Function
Public Function Radiant(Grad As Double) As Double
  Const Pi As Double = 3.14159265358979
  Radiant = Grad * Pi / 180#
End Function

Ich habe in beiden Fällen den Weg über einen benutzerdef. Datentypen "ABC_Zeile" gewählt, der 3 Double-Zahlen namens "A", "B" und "C" enthält. Dieser Datentyp repräsentiert je 1 Zeile Deiner Datenmatrix.
Aufgerufen wird die Funktion, der jeweils ein Wert für den Parameter JMC zu übergeben ist, wie folgt, zB:
SummeR1(1.45)

 

Hallo,

vielen super Dank, funktioniert alles super. ))))))))))))))))))
Nun heißt es 6 solcher Tabellen zu erstellen mit teilweise mehr als 50 ABC Werten.

Nochmals vielen vielen Dank
Schöne Grüße
Tauchmann

 

Hallöchen,

nachdem ich den ersten Code von Exl121150 erfolgreich verwendet habe, habe ich mal etwas im Code verändert um den Ablauf besser zu verstehen, dabei bin ich auf folgende Schwierigkeiten gestoßen.

Exl121150 hat im Code 1 das S als „As Double“ definiert, dem S eine Null zugewiesen (S = 0#), das S in der Formel verwendet und lässt aus den S Werten die Summe bilden die auch stimmen. Nun habe ich der Übersichtshalber neben dem S ein X As double definiert, dem X die Null zugewiesen und dieses X dann in der Formel (hinter dem Gleichheitszeichen) verwendet. Leider wird mir dann nur der S –Wert der letzten Zeile als Ergebnis wiedergegeben.

Kann mir einer erklären warum dies passiert und wieso Exl121150 in der Formel ein S nach dem Gleichheitszeichen eingestellt hat (in der original Formel ist diese nicht mit angegeben, aber wie ich herausgefunden haben notwendig um die Berechnung durchzuführen)?

Was beutet das Raute-Zeichen hinter der vollen Zahl? (laut Google ist das eine signifikante Ziffer, was bedeutet das?)

Was bedeuten und machen die Doppelpunkte in den With-Anweisungen?

Schöne Grüße
Tauchmann

Code:

Option Explicit
Type ABC_L3_Row
    A_L3 As Double
    B_L3 As Double
    C_L3 As Double
End Type

Public Function HeliLong(JD As Double) As Double

Dim ABC_L3(0 To 6) As ABC_L3_Row
Dim Delta_T As Byte, JDE As Double, JC As Double, JCE As Double, JME As Double
Dim I As Integer, S As Double, X As Double

Delta_T = 67                            'seconds
JDE = JD + (Delta_T / 86400)            'JDE = Julian Ephemeris Day
JC = (JD - 2451545) / 36525             'JC = Julian Century
JCE = (JDE - 2451545) / 36525           'JCE = Julian Ephemeris Century
JME = JCE / 10                          'JME = Julian Ephemeris Millennium

    With ABC_L3(0): .A_L3 = 289#: .B_L3 = 5.844: .C_L3 = 6283.076: End With
    With ABC_L3(1): .A_L3 = 35#: .B_L3 = 0#: .C_L3 = 0: End With
    With ABC_L3(2): .A_L3 = 17#: .B_L3 = 5.49: .C_L3 = 12566.15: End With
    With ABC_L3(3): .A_L3 = 3#: .B_L3 = 5.2: .C_L3 = 155.42: End With
    With ABC_L3(4): .A_L3 = 1#: .B_L3 = 4.72: .C_L3 = 3.52: End With
    With ABC_L3(5): .A_L3 = 1#: .B_L3 = 5.3: .C_L3 = 18849.23: End With
    With ABC_L3(6): .A_L3 = 1#: .B_L3 = 5.97: .C_L3 = 242.73: End With

X = 0#
For I = 0 To 6
    With ABC_L3(I)
        S = X + .A_L3 * Cos(.B_L3 + .C_L3 * JME)
    End With
Next I
HeliLong = S

End Function

 

Hallo!

In der Programmiersprache BASIC - VBA ist nur eine Weiterentwicklung davon - gibt es seit Anfang an die Konvention, dass man den Datentyp einer Variablen oder Konstanten an einem speziellen Zeichen, das man der Variablen bzw. Konstanten anfügt, ablesen kann:
Folgende Zeichen stehen dafür zur Verfügung:
% bedeutet, es handelt sich um eine 16-bit-Ganzzahl
& beduetet, es handelt sich um eine 32-bit-Ganzzahl
! bedeutet, es handelt sich um eine 32-bit-Gleitkommazahl
# bedeutet, es handelt sich um eine 64-bit-Gleitkommazahl
@ bedeutet, es handelt sich um um eine Währungszahl (mit max. 4 dezimalen Nachkommastellen)
$ bedeutet, es handelt sich um eine Variable, die Textzeichenfolgen enthält.

Da man der Null (0) nicht ansieht, ob sie eine kurze oder lange Ganzzahl oder ob sie gar eine kurze oder lange Gleitkommazahl sein soll, kann man zB. der 0 ein # anfügen, um damit dem Compiler/Interpreter mitzuteilen, dass es sich um eine doppeltgenaue Gleitkommazahl (=DOUBLE) handelt.

Das Google-Zitat hat in diesem Zusammenhang überhaupt nichts verloren - da wurden 2 verschiedene Dinge miteinander vermischt. Der Google-Hinweis dürfte sich auf die Formatierung von Zahlen beziehen. Dort wird das Zeichen # verwendet, um in der Formatmaske die Position einer Ziffer anzudeuten.

Dieser Programmabschnitt kann so nicht funktionieren.
1) Du weist zuerst der DOUBLE-Variablen X eine 0 (vom Typ DOUBLE) zu.
2) Du führst die Schleife FOR I ... NEXT I 7x aus, indem Du die Variable I von 0 bis 6 jeweils um 1 je Durchlauf hochzählen lässt.
3) Diese I-Werte (0, 1, 2, ..., 5, 6) wirken sich innnerhalb der Schleife nur auf den Array-Index der benutzerdef. Array-Variablen ABC_L3(I) aus.
4) Innerhalb des Blocks WITH ABC_L3(I) ... END WITH wird auf die Subdatenfelder der benutzerdef. Variablen zugegriffen: das sind die Variablen, denen ein Punkt (.) vorangestellt ist (.A_L3 bzw. .B_L3 bzw. .C_L3)
5) Die Werte/Ausdrücke rechts vom Zuweisungsoperator (=) sind ganz normale mathematische Formeln, die berechnet werden aufgrund ihrer momentanen Werte. Das sich ergebende Resultat wird dann der Variablen, die links vom Zuweisungsoperator (=) steht, zugewiesen. Beim Zeichen = handelt es sich hier nicht um ein Gleichheitszeichen im mathematischen Sinn, sondern um einen Wertzuweisungsoperator: der Wert rechts vom Zeichen = wird der Variablen, die links davon steht, zugewiesen.
6) Da Du zu anfangs die Variable X auf 0 gestellt hast (X = 0#), den Wert von X seither nicht verändert hast, hat sie diesen Wert auch noch im folgenden Ausdruck X+.A_L3*.... Dieser Ausdruck wird berechnet und der Variablen S, die recht von = steht, zugewiesen.
7) Dieser Vorgang wiederholt sich ingesamt 7x. Der sich schlussendlich ergebende Wert von S wird der Funktion (HeliLong = S) zugewiesen, die diesen Wert als Funktionswert im aufrufenden Formelausdruck zurückgibt.
Somit gibt die Funktion einen Wert zurück, der aufgrund der Subfelder von ABC_L3(6) ermittelt wurde.

Also: in BASIC wird das Zeichen als Wertzuweisungsoperator verwendet. In anderen Programmiersprachen werden dafür auch die Zeichen := verwendet.
In logischen Vergleichsausdrücken jedoch wird das = im mathematisch-logischen Sinn verwendet: ein Test, ob die Ausdrücke links und rechts vom = gleich sind (zB. IF A=B THEN ...)

Ein Programmkonstrukt in BASIC oder FORTRAN wie das Folgende:
S = S + Ausdruck
kann in gewissen anderen Programmiersprachen (ALGOL, PASCAL) so aussehen:
S := S + Ausdruck
und in wieder anderen Programmiersprachen (zB. C):
S += Ausdruck
und bewirkt, dass der Wert von Ausdruck auf den Wert von S draufaddiert wird.