Het bepalen van de complexiteit van een werkblad

Joran is bezig met een vergelijking van 13 verschillende modellen van de studiefinanciering (door provincies / gebieden in Canada). Elk model heeft zijn eigen werkblad die zich voedt in een overzichtspagina. Joran zou graag in staat zijn om de complexiteit van elke individuele werkblad (provincie / grondgebied) te vergelijken op een concrete manier. Bij gebruik van de 'Evalueren Formula' tool, elke stap van een bepaalde formule is geïsoleerd. Hij zou graag willen weten of er een manier is om het aantal 'stappen' evaluatie opgenomen in een hele spreadsheet tellen. Dit zou Joran te voorzien van een enigszins objectieve maatstaf die vergelijkbaar zijn tussen de werkmap zou zijn.

Helaas blijkt dat Excel niet het evalueren Formula 'instrument dat beschikbaar is als een object binnen VBA. Dat betekent dat op een werkblad op deze manier te evalueren zou vereisen de ontwikkeling van een macro om te kijken door alle cellen, trek formules, en tel de "functionele zinnen" in elke formule. Dit kan vrij complex in korte tijd uitgegroeid.

Hoewel dit kan worden gedaan (complexiteit terzijde), kan het niet de gewenste resultaten opleveren. Als voorbeeld, overweeg dan de volgende formule:

= A1 + A2 + A3

Deze formule heeft twee of drie evaluaties, afhankelijk van hoe je telt evaluaties. Beschouw nu de volgende formule:

= SOM (A1: A3)

Deze formule is functioneel gelijkwaardig aan de vorige, maar slechts één evaluatie vereist. Noch formule is complexer dan de andere, maar indien u alleen tellen evaluaties, de eerste zou blijken twee of drie keer zo complex als de tweede. Sommige functies, ook al geteld als een enkele evaluatie, zijn veel complexer dan eenvoudige toevoeging en dit wordt ook genegeerd als gewoon tellen zinnen.

Verder, als je formules omvatten elk type beslissingsstructuur (meestal met behulp van de IF-functie), dan is de complexiteit van de formule is niet direct gekoppeld aan het aantal zinnen of evaluaties in de formule. De reden is dat wat Excel feitelijk berekend wordt bepaald op het moment van berekening op basis van de op dat moment aanwezige omstandigheden.

In het besef dat het tellen van zinnen is niet de beste manier om de complexiteit van het model natuurlijk bepalen leidt tot de vraag hoe men de bepaling kan maken. Wat kan binnen een werkblad worden onderzocht om objectief vast te stellen complexiteit. Er is geen eenvoudige manier om een ​​dergelijke beslissing te nemen. Dit is deels te verklaren door Steve, een ExcelTips abonnee die is gepromoveerd developmet chemicus. Als onderdeel van zijn werk dat hij ontwerpt experimenten en analyseert ze om logistieke en zakelijke modellen te maken. Hier is zijn commentaar:

=== [Beginnen opmerkingen Steve's van het model complexiteit] ===

Een eenvoudige evaluatie van de complexiteit van het model is het aantal termen in het model om de resultaten te krijgen. Bij de evaluatie van modellen, in mijn ervaring heb ik geconstateerd dat u een balans van eenvoudige vs resultaten vinden. Men voegt elementen die echt invulling te geven aan de resultaten, en elminates die dat niet doen.

Een maatregel van fit gebruikt in modellen wordt genoemd de determinatiecoëfficiënt (ook bekend als R ^ 2, R-kwadraat). Het is een maat voor hoe goed het model past bij de feitelijke gegevens. Het is de verhouding van de "som van kwadraten" van de regressie, betekent het kwadraat van de waarde van het model ves de werkelijke voor elke afzonderlijke waarde gedeeld door de som van de kwadraten Totaal betekent het kwadraat van gedifferentieerde elk punt het gemiddelde van de punten.

Maar als men voegt complexiteit (door het toevoegen van extra termen) de R ^ 2 te krijgen hoger als de vrijheidsgraden naar beneden gaan. Een aangepast R ^ 2 is een goede vergelijking:

Aangepaste R ^ 2 = 1 - (1-R ^ 2) * (n-1) / (NK-1)

waarin R ^ 2 was zoals hierboven beschreven, n is het aantal punten en k het aantal termen in het model.

Typisch een streeft naar de maximale aangepast R ^ 2.

=== [End opmerkingen Steve's van het model complexiteit] ===

Tenzij men zeer vertrouwd in het statistische methoden en wiskunde, het is gemakkelijk om te verdwalen in de details van de complexiteit te bepalen op deze manier.

Als naar beneden dergelijk parcours is niet naar wens (of uw opleiding), dan is er een andere manier om de complexiteit te bepalen: Hoe lang duurt het om te komen tot een resultaat met behulp van elk model? Als je werkblad elk model zetten in zijn eigen werkboek, kunt u een macro gebruiken om te meten hoe lang het duurt om een ​​herberekening uit te voeren op de werkmap. Omdat Excel maakt gebruik van dezelfde interne methoden om herberekening uit te voeren op elke werkmap, kunt u gemakkelijk timing vergelijken op elk van de werkboeken. Voor een algemene, de zetel-van-de-broek indicatie van de complexiteit, de werkmap die het langst duurt om opnieuw te berekenen is degene die het meest complex.

ExcelTips is uw bron voor kosteneffectieve Microsoft Excel training. Deze tip (13.096) is van toepassing op Microsoft Excel 2007, 2010 en 2013.