TEILERGEBNIS für RGP

Zur Berechnung des Wertverlusts von PKWs unterschiedlicher Erstzulassung wollte ich die Steigung eines linearen Trends mittels der Funktion RGP(Y_Werte;[X_Werte];[Konstante];[Stats]) verwenden. Als ich allerdings einen Filter setzte, musste ich feststellen, dass manuell ausgeblendete Zellen eingeschlossen bleiben. Also bemühte ich die Funktion TEILERGEBNIS(Funktion;Bezug1;[Bezug2];…), um zu erkennen, dass unter den angebotenen 11 Funktionen kein RGP zu finden ist. Dass Excel dies generell kann, sieht man aber daran, dass ein Diagramm auf ausgeblendete Zellen reagiert und eine etwaige lineare Trendlinie somit “gezwungen” ist, ihre Parameter demgemäß anzupassen!

Was liegt also näher, als eine Benutzerdefinierte Funktion SUBTOTAL_RGP(Y_Werte;[X_Werte];[Konstante];[Stats]) zu programmieren, die nichts anderes tun muss, als die sichtbaren Zellen herauszufiltern, um sie der ursprünglichen Arbeitsblattfunktion RGP (VBA: Worksheetfunction.LinEst) als Arrays zu übergeben.
Die Funktion akzeptiert Bezüge und Matrizen in Zeilen und Spalten, mit der momentanen Einschränkung gegenüber RGP, dass der Bereich der X_Werte nur eine Variablengruppe umfassen darf.

Die folgende Datei zeigt die Funktionalität mittels zweier Gruppierungen und Zufallszahlen, wobei die Formel in den Diagrammen als Kontrolle dienen mag:

Moment um allgemeine Achse

Momente sind an und für sich nichts Besonderes, solange Sonderfälle zu Vereinfachungen führen. Das Moment um eine allgemeine Achse aber brachte mich zum Grübeln, da ich natürlich eine für mich zukunftsträchtige Lösung in Excel haben wollte: Kraft oder/und Moment wirken an einem Angriffspunkt und erzeugen ein Moment um eine Achse.
Da mich Google im Stich ließ, überlegte ich einen Lösungsweg:

  • Für eine Kraft braucht es deren wirksame Komponente und ihren Hebelarm. Ersteres ist die Projektion der Kraft in eine Normalebene der Achse. Letzteres ist der Normalabstand dieser Projektion zur Achse.
  • Für ein Moment genügt dessen wirksame Komponente.

Nach längerer Herleitung, zeigte sich ein unsympathisches Konvolut, was zugegebenermaßen zum Teil “meiner Mathematik” geschuldet sein hätte können. Die Unwägbarkeiten eines kommutativen Skalarprodukts ließen mich dann aber einen anderen Weg einschlagen, nämlich ganz althergebracht den allgemeinen Fall zum Sonderfall zu machen!

  1. Verschiebung des Achsenpunktes in den Nullpunkt
  2. Drehung um die x-Achse, damit die Achse in der z-x-Ebene zu liegen kommt
  3. Drehung um die y-Achse, damit die Achse schließlich kollinear zur z-Achse ist

Und schon haben wir den quasi ebenen Sonderfall! Die sich durch die Transformationen ergebenden Kraftkomponenten \( F_x^{\prime\prime} \) und \( F_y^{\prime\prime} \) samt zugehöriger Wirkabstände \( y^{\prime\prime\prime} \) und \( x^{\prime\prime\prime} \) und die Momentkomponente \( M_z^{\prime\prime} \) ergeben das gesuchte Moment \( M \) .
Die folgende Datei zeigt dies einerseits aufwändig aber nachvollziehbar Schritt für Schritt, vereinfacht mit Drehmatrizen in Zellen, kompakt mit Hilfe von Namen und für mich optimal als Benutzerdefinierte Funktion:

Bemerkung: Nicht als 97-2003-Version, weil in den Diagrammen der kombinierte Typ “Doppelt” der Linie des Moments und generell der Endpfeiltyp “Pfeil” – ohne Warnung beim Speichern im Kompatibilitätsmodus – verworfen wurden.

INDEX2

Die Funktion INDEX gibt einen Wert oder den Bezug zu einem Wert aus einer Tabelle oder einem Bereich zurück. Sie ist in 2 Versionen vorhanden: Matrix- und Bezugsversion.
Sie lässt sich auch in Matrixformeln (Arrayformeln) verwenden. Das funktioniert auch, wenn sie in einem Namen verwendet wird, der sich auf dem Tabellenblatt wiederum in solch einer Formel wiederfindet.
Wenn man aber möglichst viel von dem, was nicht unbedingt auf dem Tabellenblatt sein muss, via Namen verbannt, wird Namen generieren, die wiederum Namen enthalten. Das mag schon alleine deswegen nötig sein, weil ein Name 255 Zeichen nicht überschreiten kann.
In meinem Fall sollte die INDEX-Funktion eine benutzerdefinierte Funktion mit einer Matrix an Werten füttern, übergab aber immer nur Einzelwerte. Was die Prozedur der Matrixformel verlässlich erledigt, das ist dem Namen selbst fremd, da er ja auch nicht als solche eingegeben werden kann (Eingabe mittels “STRG+UMSCHALT+EINGABE” bzw. “Ctrl-Shift-Enter”). Mehrere Werte, die die Matrixformel-Prozedur nach und nach einzeln übergibt, werden ignoriert und nur der erste davon genommen.

Das führte zur Entwicklung von INDEX2, einer benutzerdefinierten Funktion, die für die Parameter “Zeile” und “Spalte” auch einzeilige oder einspaltige Zellbereiche oder Matrizen annimmt.
INDEX2 verhält sich wie INDEX, mit dem Unterschied, dass bei einem ungültigen Verweis nicht #BEZUG! sondern #NV zurück geliefert wird. Grund dafür ist, dass eine etwaige Abfrage mit ISTFEHLER() oder ISTFEHL() um 4 bzw. 2 Zeichen länger ist als bei ISTNV(), was bei 255 Zeichen relevant sein kann. Überdies ist es in meinen Augen egal, warum kein Wert vorliegt.
Die Test- und Demonstrationsdatei enthält zur Verdeutlichung 2 Tabellenblätter “INDEX2” und “INDEX”, die diesen “kleinen Unterschied” linksseitig zeigen. Rechterseits allerdings zeigen sich die Stärken bei der Verwendung in einem Namen. Die Anzahl der Elemente ist das augenscheinlichste! Und dass der Name für den aufrufenden Bezug passend beschickt wird, darf natürlich erwartet werden:

3D-Punkt (XY)-Diagramm

Dass man ein Punkt (XY)-Diagramm zur Darstellung von Geometrie verwenden kann, wenn man für Verzerrungsfreiheit sorgt, habe ich mittels Punkt (XY)-Diagramm unverzerrt realisiert. In Belastungsverteilung tat ich damit erstmals den Schritt in die 3. Dimension, wobei alle 3 Euler-Winkel frei einstellbar sind, und 8 vordefinierte Ansichten zur Auswahl stehen.

Da ich räumliche Darstellungen wiederholt benötige, habe ich mir eine Vorlage generiert, die sich schnell in neue Projekte integrieren lässt. Die z-Achse ist dabei wie in der Axonometrie stets vertikal ausgerichtet, wodurch nur noch zwei Drehwinkel definiert werden müssen: Jener um die z-Achse und der Winkel um die Horizontale. Diese Winkel können als beliebiger Zahlenwert eingegeben oder schrittweise mittels Bildlaufleiste definiert werden. Natürlich gibt es auch wieder die 8 vordefinierten Ansichten (Isometrisch, Dimetrisch, Oben, Unten, Vorne, Hinten, Rechts und Links), die mittels Kombinationsfeld oder Listenfeld gewählt werden können. Je nach Geschmack kann eines der beiden Elemente gelöscht und der Code (gemäß dortiger Anleitung) angepasst werden.

Die benötigte Mathematik (Gesamt-Drehmatrix zufolge Multiplikation 2er Drehmatrizen, transformierte Punkte und deren Extremwerte für die Achsendefinition) ist via Namen komplett ausgelagert, was auch für die Daten des Diagramms (Achsen und Datenreihen) gilt. Auf dem Tabellenblatt befinden sich also nur benötigte Eingaben für Punkte und Winkel und die Ansichtsbezeichnungen für die Listen.

Die folgenden Datei enthält ein Punkt (XY)-Diagramm, das zur 3-dimensionalen Darstellung genutzt wird. Da es mir als Vorlage sehr dienlich ist, mag es auch für andere geeignet sein: