Bereich invers durchlaufen

In VBA verwendet man zum Durchlaufen von Aufzählungen (z. B. Workbooks, Worksheets,…) gewöhnlich die For Each…Next-Anweisung.
Will man dies in umgekehrter Reihenfolge tun, dann geht das mittels For *.Count To 1 Step -1…Next, wenn für die jeweilige Aufzählung die Eigenschaft Count vorliegt und auf die einzelnen Elemente per Index zugegriffen werden kann.
Ein Zellbereich bietet das mit Cells.Count zwar auch an, aber die Items beziehen sich nicht direkt auf den gesamten Bereich, sondern zuerst auf Areas, die sich schließlich auf den Bereich selbst beziehen. Da ein Bereich aus mehreren Areas bestehen kann, müssen also diese und die darin enthaltenen Items abgearbeitet werden.

Die Lösung sind 2 verschachtelte, invers laufende “For…Next”-Schleifen, die Areas und Cells abarbeiten und einer Collection übergeben. Anstatt der Aufzählung könnte auch ein temporärer Name mit den Bezügen gefüttert werden, was aber einen Eingriff im Tabellenblatt oder in der Arbeitsmappe bedeuten würde.

Die Benutzerdefinierte Funktion RANGEINVERSION(Bereich) erwartet einen Bereich, der sich aus einer Selektion, einem Namen oder aus VBA-Code heraus ergeben kann.
Das Tabellenblatt RANGEINVERSION der folgenden Arbeitsmappe bietet dazu einen Test mittels des Namens Testrange an:

UND für Matrixformeln

Der Funktion UND(Wahrheitswert1;[Wahrheitswert2];…) können bis zu 255 (30 in Excel 97 – 2003) Bedingungen übergeben werden, die entweder als WAHR oder als FALSCH bewertet werden können. Diese Argumente dürfen auch als Arrays (Matrizen) vorliegen, was einen verleiten könnte, UND auch in einer Matrixformel zu verwenden, was aber daran scheitert, dass immer nur ein Wert zurückgegeben wird!

Abhilfe

Anstatt UND(Wahrheitsarray1;Wahrheitsarray2;…) nehme man bei Matrixformeln das Produkt Wahrheitsarray1*Wahrheitsarray2*…

Alternative

Die Benutzerdefinierte Funktion AND4AF(Wahrheitswert1;[Wahrheitswert2];…) (Abkürzung für “AND for Array Formulas”) funktioniert wie das normale UND (allerdings werden beliebig viele Argumente entgegengenommen), wenn sie von einer “gewöhnlichen” Formel aufgerufen wird, in allen anderen Fällen (Aufruf durch Matrixformel, VBA,…) werden die Wahrheitswerte wie folgt abgearbeitet:

  • 2-dimensionale Matrix führt zum Fehler #WERT!
  • 1-dimensionale Matrizen können gemischt als Spalten- und Zeilenvektoren vorliegen
  • Der größte Vektor bestimmt den Ergebnisumfang
  • Fehlende Werte eines kleineren Vektors werden ignoriert
  • Ein Einzelwert wird zu einem Vektor mit einer Komponente
  • Dabei werden wie beim normalen UND Text und fast alle Zahlen ignoriert, denn 0 entspricht FALSCH

Die folgende Datei zeigt die Verwendung der Benutzerdefinierten Funktion AND4AF im Vergleich zum normalen UND anschaulich für die verschiedensten Fälle:

Signifikante Stellen

Soll in Excel eine Zahl abweichend vom “Standard” in bestimmter Art und Weise anzeigt werden, dann kann man mit Zahlenformaten, insbesondere der Kategorie “Benutzerdefiniert”, einiges erreichen.
Selbst Signifikante Stellen lassen sich mit der Kategorie “Wissenschaft” realisieren. Wenn mit diesem Wert noch weitergerechnet werden soll, dann dürfte man die gewöhnungsbedürftige Darstellung in Kauf nehmen, da eine Rundung erst möglichst spät innerhalb des Rechnungsgangs durchgeführt werden sollte.
Bei einem Endergebnis, das ohnehin auf mehrere unsichere Werte aufbaut, kann allerdings eine gerundet Darstellung anderer Art erwünscht sein!

So wie ich wurden offensichtlich viele auf Excelformeln fündig. Übrigens eine äußerst empfehlenswerte Seite! Ich habe alle 3 Bücher erstanden und kann sie jedem Wissbegierigen nur ans Herz legen!
Der leider schon verstorbene Klaus Kühnlein hat die Aufgabe eindrucksvoll mittels Formel umgesetzt, das prinzipielle Ziel erfüllend, ohne VBA auszukommen.

Das erste, was mich bei Formeln stört, ist das mehrfache Vorkommen ein und desselben Terms, besonders wenn wegen dessen Länge ein Zwischenergebnis quasi erzwungen wird. Dies deswegen, weil ansonsten mehrere identische Berechnungen erfolgen.
Für mich stehen Aufwand zu Nutzen im Vordergrund und nicht das hehre Ziel, VBA zu vermeiden, selbst wenn auch ich gerne ohne auskomme. Deswegen habe ich auf Basis der vorgefundenen Formel eine Benutzerdefinierte Funktion programmiert, die natürlich auch mit Matrixformeln zurecht kommt.
Die folgende Datei zeigt die Umsetzung via Formel und Matrixformel mit und ohne VBA:

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:

Zwischenwerte

Nackte Zahlenreihen kleide ich einerseits wegen “Ein Bild sagt mehr als 1000 Worte” in Diagramme, andererseits hat sich deswegen schon so manches unplausible “Eck” insbesondere in Punkt (XY)-Diagrammen als Fehler entpuppt. Damit solche “Ecken” nicht von zu wenig Punkten herrühren, sorge ich für ausreichend viele Zwischenwerte, nicht zuletzt auch wegen der teilweise fragwürdigen Windungen zufolge der Option “Linie glätten”.
Diese Zwischenwerte will ich allerdings nicht auf dem Tabellenblatt haben, da dadurch nur die Übersichtlichkeit in Mitleidenschaft gezogen wird und es das Diagramm nicht erzwingt. Ich verwende dazu schon länger Namen und verzichte auf sonstige Tricks.

Bisher habe ich mich dazu 2er Methoden bedient:

  • Wie in Vektoren beschrieben, nutzte ich am häufigsten Namen mit Werten zwischen einem Start- und einem Endwert. Nachteilig war, dass etwaige weitere vorgegebene Werte im errechneten Vektor keine Entsprechung mehr fanden, im Diagramm also nicht auftauchten. Darüber hinaus liefert diese Methode natürlich nur monoton steigende oder fallende Zahlenreihen.
  • Obigen Nachteil behob ich in Kubischer Spline und dessen Weiterentwicklung Parametrische Spline-Interpolation durch eine benutzerdefinierte Funktion, die den aufrufenden Namen entsprechend befüllte.
    Die Nutzung von VBA ist nie meine erste Wahl, weil es aus Sicherheitsgründen und wegen geringerer Transparenz bei vielen Anwendern Argwohn hervorruft oder gar auf Ablehnung stößt!

Nunmehr habe ich eine Methode gefunden, die die Vorteile beider Ansätze vereint, ohne deren Nachteile zu haben:

  • Der fürs Diagramm benötigte Vektor liegt als Name “x_1” vor
  • Er enthält die ursprünglichen Werte mit dazwischen gleichmäßig angeordneten Zwischenwerten
  • Er ergibt sich als Matrizenmultiplikation aus dem Vektor der Ursprungswerte “x_0” mit einer Matrix, benötigt also kein VBA
  • Diese Matrix liegt ebenfalls als Name “Matrix” vor. Die Formel lautet:
    “=((ZEILE(M)>=(n_ZW+1)*SPALTE(M)-2*n_ZW)*(ZEILE(M)<=(n_ZW+1)*SPALTE(M)))*ABS(2*(n_ZW+1)*(ZEILE(M)>((n_ZW+1)*(SPALTE(M)-1)+1))-(ZEILE(M)-(n_ZW+1)*(SPALTE(M)-1)+n_ZW))/(n_ZW+1)”
    Geht’s (noch) einfacher? 😉
  • Wegen der Begrenzung auf 255 Zeichen, der Übersichtlichkeit und vor allem der darin mehrfach benutzten Funktionen ZEILE und SPALTE wird eine weitere Matrix mit kurzem Namen “M” herangezogen
  • Diese Matrix bezieht sich mittels nicht volatiler Funktion INDEX (Bezugsversion) auf ein “very hidden” Tabellenblatt “X”, um “bezugsgefährdenden” Änderungen zu entgehen

Restriktion für Excel 97-2003:

  • Der finale Vektor darf nicht mehr als 5.461 Elemente umfassen. Bei gegebener Ausgangsanzahl “n_0” ergibt sich also die höchstzulässige Anzahl der Zwischenwerte “n_ZW” als “=GANZZAHL((5461-n_0)/(n_0-1))”.

Die folgende Datei zeigt die Umsetzung obig beschriebener Erzeugung von Zwischenwerten:

Matrizen

Mit Excel kann man auch Matrizen-Rechnung betreiben. Für diejenigen, die das ohnehin häufig brauchen, ist das banal, es gibt aber viele Unwissende, die das zu ihrem Vorteil nutzen könnten. Ein ähnliches Schicksal teilen die Arrayformeln.
Was ich schon für Vektoren gezeigt habe, gilt auch hier, nämlich dass erst das Benutzen von Namen zur oftmals benötigten Flexibilität führt, weil in vielen Fällen die Matrixgröße nicht bekannt ist.
Ein gutes Beispiel dafür ist die Spline-Interpolation Kubischer Spline: Das in Version 1.10 hinzugefügte Tabellenblatt “Spline (Namen)” enthält gegenüber “Spline” weder Vektoren noch Matrix, sondern bedient sich derer über Namen. Im Namensmanager kann man den Aufbau der tridiagonalen Matrix nachvollzeihen. Die Aufteilung auf mehre Namen ist der Beschränkung auf 255 Zeichen geschuldet.
Namen definiert man über FORMELN > Namensmanager. Die benötigten Funktionen findet man in Version 2013 fast alle in der Kategorie “Math. & Trigonom.” (MDET, MEINHEIT, MINV, MMULT), nur MTRANS ist in “Matrix” eingeordnet.

Einheitsmatrix
Die Funktion MEINHEIT gibt es erst seit Version 2010. Aber auch in 97-2003 lässt sie sich sehr einfach als Name definieren.

Drehmatrizen
Als Techniker braucht man häufig Transformationen, wie z. B. Drehungen um die Koordinatenachsen.

Euler-Winkel
Bei mehreren aufeinander folgenden Drehungen sollte man sich an Konventionen halten!

Multiplikation
Nur die Zeilenanzahl der rechten Matrix muss der Spaltenanzahl der linken entsprechen, alles weitere kann variabel gestaltet werden.

Die folgende Datei enthält Beispiele zum oben angeführten:

Vektoren

Einspaltige und einzeilige Bereiche im Tabellenblatt von Excel sind prinzipiell Vektoren. Sie stellen häufig Verläufe dar, deren Umfänge oftmals unbekannt oder überhaupt dynamisch sind. Hier kommen vorteilhaft Namen ins Spiel, mit denen man ganz einfach variable Zahlenfolgen erzeugen kann, die tatsächlich Spalten- und Zeilenvektoren sind, die ich hier vorstellen möchte.

In Excel alles im Tabellenblatt umzusetzen, damit es für jeden auf den ersten Blick nachvollziehbar ist, mag ein wünschenswert einfacher Ansatz sein. Um aber beispielsweise Diagramme mit Daten zu versorgen, müssen keine Zellen beschickt werden. Ein Name bedient die Abszisse, ein zweiter die Ordinate, passend für den entsprechenden Diagrammtyp. Ich selbst benötige am häufigsten das Punkt (XY)-Diagramm, wofür ich damit den Start- und Endwert und eine taugliche Anzahl Zwischenwerte der Abszisse sehr flexibel festlegen kann! Die Werte der Ordinate ergeben sich dann in Abhängigkeit davon in Art und Umfang als dazu passender Vektor.

Für die Erstellung von Vektoren stehen 3 mir bekannte Formeln zur Verfügung: BEREICH.VERSCHIEBEN, INDIREKT und INDEX.
Dass BEREICH.VERSCHIEBEN und INDIREKT volatile Funktionen sind, dürfte den meisten Usern unbekannt und aus Performancegründen meist unerheblich sein. Sie werden im Zuge einer Neuberechnung immer mit berechnet, auch wenn zum Verursacher keine Abhängigkeit besteht.
Auf INDEX, ohne diesen Nachteil, bin ich erst durch den bemerkenswerten Beitrag Index als Bezug – ein wahres Monster von Peter Haserodt gestoßen!
Für die Vektorerstellung sind noch die Funktionen ZEILE oder SPALTE erforderlich, die zufolge eines übergebenen Bereichs ja erst Werte für den Vektor zurückgeben. Gegen die damit einhergehende Bezugsabhängigkeit ist INDIREKT gefeit, da der Bezug hier als “toter” String vorliegt. Etwaiges Löschen oder/und Einfügen von Zeilen oder Spalten lässt INDIREKT kalt.
Man bräuchte also einen Zwitter aus INDIREKT und INDEX, um perfekt zu funktionieren! Das lässt sich folgendermaßen lösen: Man nehme INDEX und für den Bezug ein eigenes Tabellenblatt, das man versteckt, aber nicht wie üblich durch “Ausblenden”. Man starte den VBE (Visual Basic Editor) mittels Alt+F11, selektiere links im entsprechenden VBA-Projekt das betreffende Tabellenblatt und setze in den Eigenschaften “Visible” auf “2 – xlSheetVeryHidden”.

Das folgende Worksheet zeigt alle 4 Varianten der Spalten- und Zeilenvektoren als Namen und deren Abbildung im Tabellenblatt:

Anmerkung: Für maximale Zeilen und Spalten wurden 65.535 anstatt 65.536 und IU anstatt IV verwendet, um die automatische Umwandlung in ganze Zeilen- und Spaltenbezüge zu vermeiden, damit eventuelle Fehlermeldungen ausbleiben.