Régler la référence circulaire

Cet article fait la suite de notre première présentation des références circulaires, le 27 avril.

Quand on active, en cochant « Itération » dans la commande Outils – Options – Calcul, les références circulaires, on a accès à deux réglages, le nombre maximal d’itérations et l’écart maximal.

Le nombre maximal d’itérations est le nombre d’itérations qui vont être effectuées lors d’un calcul de la feuille. Fixons-le à « 1 » pour l’instant. Cette commande n’a donc de sens que si l’on a sélectionné « Sur ordre » dans le bloc « Mode de calcul », comme dans la copie d’écran ci-dessous :

Effaçons à présent la formule de la cellule B4, lançons un calcul avec [F9] pour que la cellule B3 retrouve sa valeur de 0, puis ré-entrons la formule de somme en B4.

Le prix de vente est maintenant de 100 euros puisque seule la cellule B4 a été calculée, quand sa formule a été validée :

Lançons un calcul avec [F9] et nous constatons que B3 prend la valeur 30,00 € et B4 la valeur 130,00 €.

Lançons un nouveau calcul avec [F9], et nous obtenons 39,00 € en B3 et 139,00 € en B4.

Lançons un nouveau calcul avec [F9], et nous obtenons 41,70 € en B3 et 141,70 € en B4.

Encore cinq calculs et le résultat final de 42,86 € en B3 et 142,86 € en B4 est obtenu...

En fait, Excel arrête le calcul dès que soit le nombre maximal d’itérations a été atteint, soit l’écart maximal entre le énième calcul et le (n+1)ème calcul – pour les valeurs de toutes les cellules du classeur – est inférieur à l’écart maximal défini.

Créer une référence circulaire

Désolé, mais j’étais chargé ces temps-ci, entre les développements Excel et les audits et améliorations de modèles existants ; je vous retrouve donc après un sevrage de cinq jours...

Réalisons un petit modèle dans lequel nous entrons un prix d’achat en B2, nous déclarons en B3 que nous allons faire une marge de 30% du prix de vente et, en B4, nous demandons la somme grâce à l’outil de sommation.

Et patatras ! Excel nous annonce qu’il ne peut pas calculer la formule car il y a une référence circulaire :

Il s’agit d’une sécurité normale car, le plus souvent, les références circulaires sont tout à fait involontaires, il s’agit d’erreurs de conception qu’Excel signale avec raison.

Il se peut cependant que – c’est d’ailleurs le cas ici – qu’il ne s’agisse pas d’une erreur mais que nous souhaitions effectivement calculer un résultat par convergence. C’est par exemple indispensable si vous construisez un modèle de calcul d’ISF (Impôt Sur la Fortune) : en effet, dans ce cas, on peut déduire, pour calculer la base imposable, le montant de l’ISF à payer qui dépend de cette même base imposable ! Encore une invention d’énarque...

Pour valider le calcul en références circulaires, il vous suffit d’appeler la commande Outils – Options – Calcul, et de cocher « Itération » :

Le résultat final de 142,86 € apparaît alors aussitôt dans la cellule B4 ! Voilà, ce n’est pas plus compliqué que cela...

Nous verrons bientôt comment tirer profit des deux réglages suivants, le nombre maximal d’itérations et l’écart maximal.

Audit : Spreadsheet Detective

Le 18 avril, nous vous avons montré qu’il était important de valider vos modèles par un audit sérieux, et nous avons déclaré qu’il y avait à présent de bons outils d’audit. Spreadsheet Detective est très probablement le plus réputé d’entre eux.

Cet add-in d’Excel installe un menu conséquent (cf. ci-contre) ainsi qu’une barre d’outils spécialisée reprenant les commandes les plus fréquemment utilisées de ce menu.

Parmi les très nombreuses fonctionnalités de cet excellent add-in, notons-en deux dont les écrans ci-dessous reproduisent les résultats.

Tout d’abord, une commande pour lister toutes les formules originales d’une feuille de calcul, qui affiche derrière chaque référence ce à quoi elle correspond.

Ensuite, une commande pour ombrer les cellules différemment selon qu’il s’agit d’une constante, d’une formule originale ou d’une formule recopiée. Dans ce dernier cas, le sens du hachurage indique la direction de la recopie.

Ce dernier outil est précieux, par exemple pour identifier très rapidement dans un bloc les formules qui ont été écrasées par des constantes, ou modifiées de façon isolée.

Je vous laisse découvrir les autres outils, tels que la mise automatique en commentaire de toutes les formules ou encore l’analyse des liens entre les feuilles d’un classeur...

Pour en savoir plus, consultez : http://www.spreadsheetdetective.com/

La fiabilité de vos modèles Excel...

Pour la plupart des lecteurs de ce blog, comme pour moi, Excel est probablement un outil d’usage quotidien, l’outil de référence sur lequel reposent de nombreux modèles d’aide à la décision.

Il convient de ne pas oublier cependant que les modèles Excel sont développés par des êtres humains, donc des entités éminemment faillibles… C’est une des particularités de la langue française, d’ailleurs, que « faillible » et « fiable » soient des mots si proches...

Je vous conseille à cet égard un article tout à fait intéressant, disponible dans son intégralité sur Internet, et qui fait référence dans ce domaine : « What We Know About Spreadsheet Errors » de Raymond R. Panko :

http://panko.cba.hawaii.edu/ssr/Mypapers/whatknow.htm

Selon lui, le taux d’erreur dans des modèles analysés par des auditeurs professionnels – il y a un certain temps déjà – était de l’ordre de 30%, mais il cite des cas plus extrêmes comme un audit de Coopers et Lybrand en Angleterre, selon lequel 90% des modèles sur tableur de plus de 150 lignes qu’ils avaient audités contenaient des erreurs. Un auditeur en particulier, en analysant quatre modèle de grande taille, avait relevé 128 erreurs...

Et, depuis 1995, date à partir de laquelle des méthodes d’audit plus évoluées ont été utilisées, 94% des 88 modèles analysés au travers de 7 études différentes auraient contenu des erreurs ! Ce taux, bien supérieur aux 30% cités plus haut, serait la preuve de l’amélioration des outils d’audit.

Il y a même un groupe d’études spécialisé sur ce sujet, EuSpRIG, le
« European Spreadsheet Risks Interest Group », qui fournit une liste de 89 types d’erreurs réalisées sur des modèles de tableur :

http://www.eusprig.org/stories.htm

Pour ma part, j’ai été appelé récemment à auditer – pour une grande banque – un classeur Excel particulièrement complexe, avec 30 feuilles de calcul comportant de multiples références croisées, correspondant à un montage financier de 250 millions d’euros. Et j’ai effectivement découvert plusieurs erreurs dans ce modèle...

Un argument "0" pour Index()

La fonction Index(), que nous avons présentée le 12 avril, possède une particularité : si l’argument de ligne est « 0 », toute la colonne est renvoyée comme résultat ; de même, si quand l’argument de colonne est « 0 », c’est toute la ligne qui est renvoyée.

Ainsi, dans le tableau ci-dessus, nous avons sélectionné le bloc G1:G10, saisi la formule =index(A1:C10;0;E2) puis validé cela en formule matricielle avec la combinaisons [Ctrl]-[Alt]-[Entrée], pour obtenir la liste des prénoms.

Il suffit de taper 2 ou 3 en E2 pour que cette liste soit remplacée automatiquement par celle des noms ou des salaires :

Certes, me direz-vous, pourquoi ne pas avoir entré en G1 une formule toute simple, non matricielle, telle que =index(A1:C1;E$2) que nous aurions alors pu benoîtement tirer vers le bas jusqu’en G10 ?

De fait, cela marche très bien et c’est même une meilleure solution si l’on ne souhaite obtenir que le résultat actuel. Pourquoi faire du matriciel quand on peut s’en passer ?

Cet exemple nous a simplement servi à montrer que l’index avec un argument à « 0 » renvoie une ligne ou une colonne entière. Il y a des cas – que nous verrons dans des rubriques ultérieures – où il faut utiliser cette solution car il n’y a pas comme ici de solution plus simple disponible.

Introduction à la fonction Index()

Nous avons récemment présenté la fonction Recherche() et ses cousines RechercheH() et RechercheV() de façon assez détaillée. Il est temps à présent de vous faire apprécier la fonction Index().

Pour illustrer sa mise en œuvre, nous avons créé en A1:E15 un bloc de 75 valeurs aléatoires comprises entre 0 et 99, engendrées grâce à la formule =ent(100*alea()).

En H1:H3, nous avons entré la formule =ent(1+3*alea()) qui engendre des valeurs aléatoires comprises entre 1 et 3.

Enfin, nous avons sélectionné A1:B3 puis, en maintenant la touche [Ctrl] enfoncée, les blocs B8:D9 et D14:E15, et nous avons entré le nom « Toto » dans la zone « Nom » - à gauche de la barre de formule – puis validé par [Entrée] pour confirmer la création de ce nom. Nous avons enfin donné un fond jaune à Toto.

Nous avons reproduit en colonne I les formules saisies en colonne H. Voici les trois syntaxes de la fonction Index, plus précises que celles proposées par l’aide d’Excel :
=index(vecteur;position)
=index(matrice;n°ligne;n°colonne)
=index({matrices};n°ligne;n°colonne;n°matrice)
… où {matrices} représente un ensemble de matrices.

Remarque 1 – Quand l’adressage est impossible, #REF! est renvoyé comme résultat. Ici, en effet, il n’y a pas 3 lignes et 3 colonnes dans la matrice n°2 !

Remarque 2 – Microsoft ne connaît apparemment pas la première syntaxe, avec un vecteur en premier argument. En effet, quand on demande à Excel de proposer les arguments avec la combinaison [Ctrl]-A, on n’obtient que les deux dernières syntaxes :

Un surprenant DateDiff() en VBA

Mon dernier message « en finissait » avec la fonction DateDif() de la feuille de calcul. Une fois de plus, Bill Gates parvient à nous surprendre avec – en VBA – une fonction qui lui ressemble, mais qui n'est pas la même, une sorte de Canada Dry...

En effet, le VBA vous offre la fonction DateDiff() , avec un second « f » pour faire la différence. Cette fonction n’est pas tout à fait la même, mais il y a évidemment un air de famille avec DateDif().

Dans cette fonction, c’est le premier argument qui indique la « forme », mais avec des modalités différentes. Nous vous laissons consulter l’aide pour en voir le détail. Au passage, attribuons un carton rouge à Microsoft qui donne un exemple d’application foireux :

En effet, le premier argument en « d » pour « day » a été abusivement traduit dans cet exemple en « j » pour « jour », ce qui entraîne un plantage de la macro.

Si vous entrez votre date de naissance en A1, la macro ci-dessous affichera le nombre de jours auxquels vous avez survécu :

Sub Age()
MsgBox Format(DateDiff("d", Range("A1"), Now), "#,##0") & " jours"
End Sub

Voici le résultat :

Pour en finir avec DateDif()...

Pour en terminer avec la fonction DateDif(), nous avons constitué un petit tableau illustrant sa mise en œuvre et détaillant le résultat obtenu avec les divers arguments « forme » :

Notez au passage que, pour ses calculs, DateDif() sait gérer les années bissextiles, comme on le voit dans le tableau suivant où la formule de H1 a été recopiée vers le bas. :

Vous voilà donc outillés pour tirer le maximum de cette fonction si bien cachée...

Découvrez la fonction DateDif()

J’ai reçu plusieurs mails de lecteurs suite à mes messages du 16 et du 22 janvier, tous pour me parler de la fonction DateDif. Certes, ma solution du 16 janvier était une démonstration des possibilités des fonctions de date dans Excel, mais il est vrai que – si l’on ne s’intéresse qu’au résultat – la fonction DateDif fait l’affaire de façon bien plus simple.

La fonction DateDif() est une de ces fonctions mystérieuses dont Excel a le secret. Elle existe depuis la version 5 d’Excel, mais n’a été documentée que dans la version 2000, pas dans les versions 97, ni 98, ni 2002, ni 2003, allez donc savoir pourquoi !

Avec Excel 2003, si vous entrez dans une cellule =datedif puis que vous faites [Ctrl]-a pour obtenir l’assistant fonction (cf. notre rubrique du 30 novembre), vous obtenez l’écran suivant qui, ensuite, mène à un écran vide quand on clique dans « Aide sur cette fonction ».

La fonction est donc reconnue, sinon l’assistant n’apparaîtrait pas. Mais alors, carton rouge pour Microsoft qui affirme qu’il n’y a pas d’argument (!) et dont le bouton d’aide mène au Triangle des Bermudes…

La syntaxe de la fonction est =datedif(date_1;date_2;mode) avec date_1<
date_2. Pour l’argument « mode », voyez la table ci-dessous :

Si vous avez entré les deux dates dont vous souhaitez calculer l’écart en A1 et A2, la formule à utiliser est : =datedif(A1;A2;"y")&si(datedif(A1;A2;"y")>1;" ans, ";" an, ")&datedif(A1;A2;"ym") &" mois et "&datedif(A1;A2;"md")&si(datedif(
A1;A2;"md")>1;" jours";" jour").