Joran vergleicht 13 verschiedene Modelle der Studentenfinanzhilfe (nach Provinzen / Territorien in Kanada). Jedes Modell verfügt über ein eigenes Arbeitsblatt, das in eine Zusammenfassungsseite eingespeist wird. Joran möchte in der Lage sein, die Komplexität jedes einzelnen Arbeitsblatts (Provinz / Gebiet) konkret zu vergleichen. Bei Verwendung des Werkzeugs „Formel auswerten“ wird jeder Schritt einer bestimmten Formel isoliert. Er möchte wissen, ob es eine Möglichkeit gibt, die Anzahl der in einer gesamten Tabelle enthaltenen Bewertungsschritte zu zählen. Dies würde Joran eine etwas objektive Maßnahme bieten, die in der gesamten Arbeitsmappe vergleichbar wäre.

Leider scheint Excel das Tool „Formel auswerten“ in VBA nicht als Objekt verfügbar zu machen. Das bedeutet, dass zum Auswerten eines Arbeitsblatts auf diese Weise ein Makro entwickelt werden muss, um alle Zellen zu durchsuchen, Formeln herauszuziehen und die „Funktionsphrasen“ in jeder Formel zu zählen. Dies könnte in kurzer Zeit ziemlich komplex werden.

Obwohl dies möglich ist (abgesehen von der Komplexität), führt es möglicherweise nicht zu den gewünschten Ergebnissen. Betrachten Sie als Beispiel die folgende Formel:

=A1+A2+A3

Diese Formel enthält entweder zwei oder drei Bewertungen, je nachdem, wie Sie die Bewertungen zählen. Betrachten Sie nun die folgende Formel:

=SUM(A1:A3)

Diese Formel entspricht funktional der vorherigen, erfordert jedoch nur eine Bewertung. Keine der beiden Formeln ist komplexer als die andere, aber wenn Sie nur Bewertungen zählen, wird die erste zwei- oder dreimal so komplex angezeigt wie die zweite. Einige Funktionen sind, obwohl sie als einzelne Auswertung gezählt werden, viel komplexer als die einfache Addition, und dies wird auch beim einfachen Zählen von Phrasen ignoriert.

Wenn Ihre Formeln irgendeine Art von Entscheidungsstruktur enthalten (normalerweise unter Verwendung der IF-Funktion), hängt die Komplexität der Formel nicht direkt von der Anzahl der Phrasen oder Bewertungen in der Formel ab. Der Grund dafür ist, dass das, was Excel tatsächlich berechnet, zum Zeitpunkt der Berechnung auf der Grundlage der zu diesem Zeitpunkt vorliegenden Bedingungen bestimmt wird.

Das Erkennen, dass das Zählen von Phrasen nicht der beste Weg ist, um die Komplexität des Modells zu bestimmen, führt natürlich zu der Frage, wie man die Bestimmung vornehmen kann. Was kann in einem Arbeitsblatt untersucht werden, um die Komplexität objektiv zu bestimmen? Es gibt keine einfache Möglichkeit, eine solche Entscheidung zu treffen.

Dies wird zum Teil von Steve erklärt, einem ExcelTips-Abonnenten, der als Doktor der Entwicklungschemie arbeitet. Im Rahmen seiner Arbeit entwirft und analysiert er Experimente, um logistische und geschäftliche Modelle zu erstellen. Hier sind seine Kommentare:

=== [begin Steve's comments on model complexity]===

Eine einfachere Bewertung der Komplexität des Modells ist die Anzahl der Begriffe, die im Modell verwendet werden, um die Ergebnisse zu erhalten. Bei der Bewertung von Modellen habe ich meiner Erfahrung nach festgestellt, dass Sie ein Gleichgewicht zwischen einfachen und Ergebnissen finden möchten. Man fügt Elemente hinzu, die den Ergebnissen echte Substanz verleihen, und eliminiert diejenigen, die dies nicht tun.

Ein Maß für die Anpassung, das in Modellen verwendet wird, wird als Bestimmungskoeffizient bezeichnet (auch bekannt als R ^ 2, R-Quadrat). Es ist ein Maß dafür, wie gut das Modell zu den tatsächlichen Daten passt. Dies ist das Verhältnis der „Summe der Quadrate“ der Regression, dh des Quadrats des Wertes des Modells, zum tatsächlichen Wert für jeden einzelnen Wert, geteilt durch die Summe der Quadrate insgesamt, dh des Quadrats der Differenzen jedes Punkts zum Mittelwert der Punkte.

Aber wenn man Komplexität hinzufügt (indem man zusätzliche Terme hinzufügt), wird R ^ 2 höher, wenn die Freiheitsgrade sinken. Ein angepasstes R ^ 2 ist ein guter Vergleich:

Adjusted R^2 = 1 - (1-R^2) * (n-1) / (n-k-1)

wobei R ^ 2 wie oben beschrieben war, ist n die Anzahl der Punkte und k die Anzahl der im Modell verwendeten Terme.

Typischerweise strebt man das maximal eingestellte R ^ 2 an.

=== [end Steve's comments on model complexity]===

Wenn man sich nicht mit statistischen Methoden und Mathematik auskennt, kann man sich leicht in den Details der Bestimmung der Komplexität auf diese Weise verlieren.

Wenn es Ihnen nicht gefällt (oder Ihrem Training), einen solchen Weg zu gehen, gibt es eine andere Möglichkeit, die Komplexität zu bestimmen: Wie lange dauert es, mit jedem Modell zu einem Ergebnis zu gelangen? Wenn Sie das Arbeitsblatt jedes Modells in eine eigene Arbeitsmappe einfügen, können Sie mithilfe eines Makros messen, wie lange es dauert, eine Neuberechnung der Arbeitsmappe durchzuführen. Da Excel für die Neuberechnung jeder Arbeitsmappe dieselben internen Methoden verwendet, können Sie das Timing für jede Arbeitsmappe problemlos vergleichen. Für eine allgemeine Anzeige der Komplexität ist die Arbeitsmappe, deren Neuberechnung am längsten dauert, die komplexeste.

ExcelTips ist Ihre Quelle für kostengünstige Microsoft Excel-Schulungen.

Dieser Tipp (13096) gilt für Microsoft Excel 2007, 2010, 2013, 2016, 2019 und Excel in Office 365.