Le "bug" de régression résolu

Le lecteur qui m'a fourni la meilleure explication est François Sermier, qui a souvent écrit des articles pour La lettre d'Excel que j'ai publiée durant 15 ans. Voici donc ses explications...

En fait, il s'agit d'un problème de "type d'axe" (à trouver dans les options du graphique). Pour un graphique de type courbe, on a le choix entre un axe de "Catégories" ou un axe "Chronologique", le choix par défaut étant l'option "Automatique" (donc s'adaptant aux données fournies). Pour retrouver les valeurs correspondant aux entiers en colonne A, il suffit de forcer l'axe en type "Catégorie" :

C'est encore un sujet sur lequel Microsoft est plutôt discret. Si j'ai bonne mémoire, l'option "chronologique" a été introduite avec Excel 97. A l'époque, je trouvais l'idée intéressante mais déplorais le choix de proposer cette option dans les graphiques de type Courbe (dont la logique normale est de mettre en abscisses le n° d'ordre du point de la série) et non dans le graphe en Nuage de points, qui est un vrai diagramme cartésien.

Le problème soulevé est une conséquence directe de ce choix de la part de Microsoft. Par contre, le côté rigolo, c'est que la pente est bien calculée sur la base de l'ordre des points (donc +1 entre chaque point en abscisses) mais que l'origine des abscisses est quand même renvoyée à janvier 1900. Et que l'abscisse est mesurée en mois (soit 1.272 pour janvier 2006, en attribuant l'origine à janvier 1901).

Du coup, l'origine sur l'axe des abscisses (déc. 2005, le point 0 de la courbe) correspond en fait à un mois numéro 1.271. Mais le terme constant de l'équation, lui, est bien calculé avec le 0 des dates (jan. 1900).

Merci, François. Encore un mystère d'Excel résolu !

Etrange droite de régression

Je vous avais promis une surprise suite à l’article précédent introduisant la droite de régression. Mettons à présent des dates dans la colonne A et – oh surprise ! – nous constatons que la formule de la droite de régression dans le graphe n’a plus rien à voir avec la pente et la constante indiquées en ligne 16 :

Pire encore, nous avons entré en D2 la formule =10,533*$A2-13305, recopiée ensuite vers le bas, ce qui ne fait qu’appliquer la formule indiquée dans le graphe, et nous constatons que cela nous donne des valeurs de y supérieures à 300.000, qui n’ont franchement rien à voir avec les valeurs attendues, situées entre 90 et 215 !!!

Pour occuper vos longues soirées d’hiver avant le réveillon, voici donc un challenge...

Pour quelle raison Excel se moque-t-il de nous avec cette droite de régression ?
Quel sens tout cela peut-il avoir ?

Vous aurez la réponse dans le prochain message, en guise d’étrennes !

Obtenir la droite de régression

Prenons une série de valeurs obtenues pour les onze premiers mois de cette année. Nous les représentons graphiquement et essayons d’obtenir la droite de régression permettant d’illustrer la tendance de ces valeurs.

Pour cela, nous traçons la courbe des valeurs et faisons un clic droit sur la courbe, puis sélectionnons la commande « Ajouter une courbe de tendance ». Excel nous propose par défaut une régression linéaire, ce qui nous convient parfaitement ici. Activons l’onglet « Options » et cochons « Ajouter l’équation sur le graphique » et enfin validons par « OK ».

Après quelques retouches de formatage pour mettre la droite de régression en rouge, formater et déplacer l’équation sur le graphique, nous obtenons le résultat suivant :

La pente de la droite de régression et sa constante peuvent être récupérées par des formules, comme nous l’avons fait en ligne 16. Pour cela, nous avons sélectionné A16 :B16, puis entré la formule =droitereg(B2:B12;A2:A12), que nous avons validée en formule matricielle avec la combinaison [Ctrl]-[Maj]-[Entrée].

En colonne C, nous avons entré les valeurs « prévues » par la droite de régression, en appliquant la formule =$A$16*$A2+$B$16 en C2.

Dans le prochain message, nous découvrirons une particularité assez étonnante de cette droite de régression.

Recherche de cellules spéciales

La commande « Atteindre » du menu Edition possède une fonctionnalité peu connue d’Excel, accessible à travers le bouton « Cellules ».

Cette fonctionnalité est particulièrement utile pour sélectionner rapidement – dans une feuille de calcul – toutes les cellules possédant une particularité donnée. Voici qui apparaît quand cette commande est invoquée :

On peut donc ainsi sélectionner par exemple toutes les cellules contenant un format conditionnel ou une validation de données, ou – comme nous le faisons avec la sélection suivante – toutes les cellules contenant des formules aboutissant à des erreurs :

Remarque – Notez que certaines options sont totalement superflues pour les bons utilisateurs d’Excel. Ainsi, il est tout à fait inutile de passer par l’option
« Zone en cours » alors que le même résultat est atteint plus rapidement à l’aide du raccourci [Ctrl]-*.

De même, les antécédents ou dépendants sont atteints plus directement par les outils correspondants de la barre d’outils « Audit » , si vous avez pris la peine de les incorporer à vos deux barres d’outils.

Nom de classeur, chemin et feuille

Nous venons d’apprendre à utiliser les fonctions orphelines Cellule() et Info(). Voici un exemple où nous les utilisons pour récupérer séparément le nom du classeur, le nom de la feuille, le nom du chemin du classeur et enfin le nom du chemin « actif », c’est-à-dire celui dont on voit la liste des fichiers quand on passe par la commande « Ouvrir » du menu Fichier.

Pour A1 et B6, nous avons mis les formules en commentaire, il est donc superflu de les décrire ici.

Remarque - Notez la présence du second argument dans la formule de la cellule A1 : si nous l’avions omis, A1 ferait référence à la feuille et au classeur de la dernière cellule modifiée, et donc pas nécessairement à cette feuille ni à ce classeur !

Voici donc les formules utilisées dans les autres cellules :

B3 : =stxt(A1;cherche("[";A1)+1;cherche("]";A1)-cherche("[";A1)-1)
B4 : =droite(A1;nbcar(A1)-cherche("]";A1))
B5 : =gauche(A1;cherche("[";A1)-1)

Ces formules sont assez simples car elles font référence à la cellule A1. Si l’on voulait tout calculer à chaque fois en une seule cellule, les formules deviendraient sensiblement plus lourdes :

C3 : =stxt(cellule("filename";$A$1);cherche("[";cellule("filename";$A$1))+1;cherche("]";cellule("filename";$A$1))-cherche("[";cellule("filename";$A$1))-1)

C4 : =droite(cellule("filename";$A$1);nbcar(cellule("filename";$A$1))-cherche("]";cellule("filename";$A$1)))

C5 : =gauche(cellule("filename";$A$1);cherche("[";cellule("filename";$A$1))-1)

Remarque - Il est intéressant de noter que, si l'on voulait récupérer les noms du classeur, de la feuille, ou le chemin, il faudrait normalement passer par des macros en VBA. Ici, nous obtenons le même résultat directement avec de simples (si l'on peut dire) formules...

Découverte de la fonction Cellule()

Nous vous avons présenté la fonction Info() il y a quatre jours, la première fonction « orpheline » d’Excel présentée dans ce blog. Aujourd’hui, nous étudions une autre fonction orpheline, la fonction Cellule(). Voici l'aide associée à cette cellule :

Quand le second argument est absent, les renseignements récupérés par la fonction sont relatifs à la dernière cellule modifiée.

Nous avons testé cette formule en entrant la formule =pi() en B1 et la formule =cellule(A2;$B$1) en B2, formule recopiée dans les colonnes B et D.

Nous constatons une fois de plus que le travail de Microsoft a été bâclé : les arguments de A8 à A10 engendrent des erreurs car, comme pour la fonction Info(), chaque lettre accentuée doit être remplacées par son équivalent sans accent !

En ce qui concerne A2, bien que l’aide ait utilisé une orthographe bâtarde (un « e » à la fin, comme en français, et « dd », comme en anglais), il faut bien entrer « adresse » pour éviter l’erreur de notre exemple.

Le statut un peu particulier des fonctions Info() et Cellule() tient à ce qu’elles sont en fait des fonctions du langage macro d’Excel 4 et non des fonctions
« naturelles » de la feuille de calcul. C’est probablement pour cette raison qu'elles elles sont si mal suivies par Microsoft.

Remarque 1 – L’argument « nomfichier » est mal nommé car on récupère en fait à la fois le chemin complet du fichier, le nom du fichier, et le nom de la feuille.

Remarque 2 – Comme pour la fonction Info(), les arguments en anglais fonctionnent très bien et garantissent la transportabilité de vos modèles.

Remarque 3 – Je tiens à souligner au passage que je trouve navrant que la plupart des livres en français sur Excel que j’ai vus à ce jour reproduisent bêtement les arguments indiqués dans l’aide d’Excel, même quand ils ne marchent pas : les auteurs de ces livres n’ont manifestement pas pris le temps de tester ces fonctions.

Découverte de la fonction Info()

La fonction =Info() est une de ces fonctions quasiment ignorées de tous sauf des professionnels du développement Excel. Elles est intéressante car elle permet de récupérer en direct des informations telles que le système d’exploitation utilisé, la mémoire disponible, le mode de recalcul utilisé ou même le classeur actif.

On parle en médecine de maladies orphelines quand peu de patients à travers le monde en souffrent. On pourrait parler de fonctions orphelines quand peu de gens les connaissent et encore moins les utilisent. La fonction Info() est une fonction orpheline !

L’aide en ligne d’Excel nous en dit ceci :

Comme pour de nombreuses fonctions orphelines d’Excel, l’aide en ligne n’a jamais été relue chez Microsoft par quelqu’un de sérieux. Nous pouvons le voir ci-dessous dans lequel nous avons entré les arguments utilisés dans les colonnes A et D, le résultat étant affiché respectivement dans les colonnes B et E. Nous avons entré en B1 la formule =info(A1), formule qui a ensuite été recopiée dans les colonnes D et E.

Remarque 1 – Chaque fois que l’argument – tel que décrit dans l’aide d’Excel – comporte au moins une lettre accentuée, il engendre une erreur. Merci, Bill !

Remarque 2 – Le problème précédent est résolu en ôtant tout bêtement les accents. Ainsi, il faut entrer « repertoire » en A1 pour obtenir en B1 le même résultat qu’en E1.

Remarque 3 – Même dans la version française, les arguments en anglais fonctionnent parfaitement, avec ou sans accent tonique...

Remarque 4 – Mon conseil est de toujours utiliser les arguments en anglais. En effet, votre modèle est alors transportable dans le monde entier, ce qui ne serait pas le cas autrement car, à l’ouverture du classeur dans un autre environnement linguistique, les chaînes de caractères n’ont aucune raison d’être traduites en même temps que les noms de fonctions qui, elles, le sont naturellement.

Remarque 5 – Des essais réalisés cette journée avec plusieurs personnes équipées d’Excel 2003 ont montré que – dans certains cas ??? – la fonction Info() n’est plus reconnue mais est remplacée par la fonction Informations(), avec quand même – faisons confiance à Microsoft ! – les mêmes bugs qu’avant concernant les lettres accentuées dans les arguments !

Si vous avez du neuf à ce sujet, nous attendons vos commentaires sur cette page...

Trouver le code le proche

Le problème que nous résolvons aujourd’hui est un problème un peu particulier. Vous avez en colonne A des codes, en B des noms, en E une liste de codes de référence. Vous voulez obtenir en colonne C le code de référence le plus proche de celui de la colonne A :

Quelle formule devez-vous entrer en C2, pour la recopier ensuite en C3:C6 ?

Une fois encore, c’est une formule matricielle qui nous permet de réaliser le miracle attendu ! La formule à entrer et à valider, comme toute formule matricielle, avec la combinaison Ctrl-Maj-Entrée, est la suivante :

=index($E$1:$E$9;equiv(petite.valeur(abs($E$1:$E$9-A2);1);abs($E$1:$E$9-A2);0))

Cette formule, il faut l’avouer, n’est pas complètement évidente ! Commentons-la donc pas à pas...

La formule petite.valeur(abs($E$1:$E$9-A2);1) trouve le plus petit écart absolu entre la valeur en A2 et les valeurs de la colonne E.

La formule equiv(...;abs($E$1:$E$9-A2);0) trouve la position dans $E$1:$E$9 où se situe la valeur avec le plus petit écart.

La formule index($E$1:$E$9;...) renvoie la valeur de $E$1:$E$9 qui a le plus petit écart avec A2.

Ainsi que je l'ai déjà dit, les possibilités des formules matricielles sont quasiment illimitées !