Joran fait une comparaison de 13 modèles différents d’aide financière aux étudiants (par provinces / territoires au Canada). Chaque modèle a sa propre feuille de calcul qui alimente une page de résumé. Joran aimerait pouvoir comparer la complexité de chaque feuille de travail individuelle (province / territoire) de manière concrète. Lors de l’utilisation de l’outil «Evaluer la formule», chaque étape d’une formule donnée est isolée. Il aimerait savoir s’il existe un moyen de compter le nombre d’étapes d’évaluation contenues dans une feuille de calcul entière. Cela fournirait à Joran une mesure quelque peu objective qui serait comparable dans tout le cahier.

Malheureusement, il semble qu’Excel ne rend pas l’outil ‘Evaluate Formula’ disponible, en tant qu’objet, dans VBA. Cela signifie que pour évaluer une feuille de calcul de cette manière, il faudrait développer une macro pour parcourir toutes les cellules, extraire des formules et compter les «phrases fonctionnelles» dans chaque formule. Cela pourrait devenir assez complexe en peu de temps.

Même si cela peut être fait (complexité mise à part), cela peut ne pas produire les résultats escomptés. À titre d’exemple, considérons la formule suivante:

=A1+A2+A3

Cette formule comporte deux ou trois évaluations, selon la façon dont vous comptez les évaluations. Considérons maintenant la formule suivante:

=SUM(A1:A3)

Cette formule est fonctionnellement équivalente à la précédente, mais elle ne nécessite qu’une seule évaluation. Aucune des deux formules n’est plus complexe que l’autre, mais si vous ne comptez que les évaluations, la première apparaîtra comme deux ou trois fois plus complexe que la seconde. Certaines fonctions, bien que comptées comme une seule évaluation, sont beaucoup plus complexes qu’une simple addition et cela est également ignoré lors du simple comptage des phrases.

De plus, si vos formules incluent un type de structure de prise de décision (utilisant généralement la fonction IF), la complexité de la formule n’est pas directement liée au nombre de phrases ou d’évaluations dans la formule. La raison est que ce qu’Excel calcule réellement est déterminé au moment du calcul en fonction des conditions présentes à ce moment-là.

Reconnaître que compter les phrases n’est pas la meilleure façon de déterminer la complexité du modèle conduit naturellement à la question de savoir comment on peut faire la détermination. Ce qui peut être examiné dans une feuille de calcul pour déterminer objectivement la complexité. Il n’y a pas de moyen facile de faire une telle détermination.

Ceci est expliqué, en partie, par Steve, un abonné ExcelTips qui est un chimiste en développement de doctorat. Dans le cadre de son travail, il conçoit des expériences et les analyse pour créer des modèles logistiques et commerciaux. Voici ses commentaires:

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

Une évaluation plus simple de la complexité du modèle est le nombre de termes utilisés dans le modèle pour obtenir les résultats. Lors de l’évaluation des modèles, j’ai constaté, d’après mon expérience, que vous souhaitiez trouver un équilibre entre les résultats simples et les résultats. On ajoute des éléments qui apportent une réelle substance aux résultats et on élimine ceux qui ne le font pas.

Une mesure d’ajustement utilisée dans les modèles est appelée le coefficient de détermination (également appelé R ^ 2, R-carré). C’est une mesure de l’adéquation du modèle aux données réelles. C’est le rapport de la «somme des carrés» de la régression, c’est-à-dire le carré de la valeur du modèle par rapport au réel pour chaque valeur individuelle, divisé par la somme des carrés du total, c’est-à-dire le carré des différences de chaque point par rapport au moyenne des points.

Mais à mesure que l’on ajoute de la complexité (en ajoutant des termes supplémentaires), le R ^ 2 augmente à mesure que les degrés de liberté diminuent. Un R ^ 2 ajusté est une bonne comparaison:

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

où R ^ 2 était comme décrit ci-dessus, n est le nombre de points et k est le nombre de termes utilisés dans le modèle.

En règle générale, on s’efforce d’atteindre le R ^ 2 ajusté maximum.

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

À moins d’être très familiarisé avec les méthodes statistiques et les mathématiques, il est facile de se perdre dans les détails de la détermination de la complexité de cette manière.

Si suivre une telle piste ne vous convient pas (ou ne vous plaît pas), il existe une autre façon de déterminer la complexité: combien de temps faut-il pour arriver à un résultat en utilisant chaque modèle? Si vous placez la feuille de calcul de chaque modèle dans son propre classeur, vous pouvez utiliser une macro pour mesurer le temps nécessaire pour effectuer un recalcul sur le classeur. Étant donné qu’Excel utilise les mêmes méthodes internes pour effectuer un recalcul sur chaque classeur, vous pouvez facilement comparer le minutage sur chacun des classeurs. Pour une indication générale de complexité, le classeur qui prend le plus de temps à recalculer est celui qui est le plus complexe.

ExcelTips est votre source pour une formation Microsoft Excel rentable.

Cette astuce (13096) s’applique à Microsoft Excel 2007, 2010, 2013, 2016, 2019 et Excel dans Office 365.