Monsieur Excel
Pour tout savoir faire sur Excel !

29 avril 2007

Un add-in pour des micrographes

Si vous souhaitez avoir rapidement une idée de la forme de la courbe qui correspond à une série de valeurs, vous pouvez utiliser un add-in à la fois peu connu et particulièrement original, MicroCharts.

Dans l’exemple ci-dessous, nous avons en E2:P6 les quantités vendues de cinq produits durant l’année 2006. Nous avons créé des courbes pour les trois premiers produits et des histogrammes pour les deux derniers.

La particularité de Microcharts est qu'il permet de créer rapidement des graphiques simples, chacun dans une cellule. Ce ne sont pas des graphiques Excel mais des représentations graphiques engendrées spécialement.

Voici par exemple comment nous avons renseigné les paramètres relatifs à la définition des trois courbes :

Nous terminerons cette présentation de Microcharts dans quatre jours…

25 avril 2007

Un camembert plus appétissant !

Dans mon dernier message, je disais que les camemberts d’Excel étaient loin de la perfection.

Un problème que je n’avais pas encore soulevé est le fait que, si plusieurs petites parts sont voisines, on ne parvient plus à lire leurs étiquettes :

Ici, les étiquettes des trois produits avec des petites valeurs se chevauchent, le résultat étant illisible. C’est pourquoi j’ai écrit une petite macro permettant de faire effectuer au camembert la rotation permettant d’afficher au mieux l'étiquette de la plus petite valeur. L’idée est d’afficher celle-ci à l’horizontale. Voici la macro, puis le résultat obtenu :

Sub Corrige_angle()
'
' Macro créée le 25/04/2007 par Hervé Thiriez
'
ActiveSheet.ChartObjects("Graphique 1").Activate
ActiveChart.ChartGroups(1).FirstSliceAngle = Range("Angle").Value
Range("A1").Select
End Sub


Je vous laisse le plaisir d'analyser les formules placées en commentaire ...

21 avril 2007

Un camembert parfois indigeste…

Les camemberts sont les graphiques les moins évolués d’Excel. Tout d’abord, ils possèdent un bug qui n’a jamais été résolu, le fait que non seulement Excel accepte de les dessiner quand ils possèdent une valeur négative mais aussi – pire encore – que, dans ce cas, Excel utilise la valeur absolue des valeurs négatives :


Quand une valeur au moins est négative, Excel devrait soit refuser de tracer le camembert et afficher un message approprié, soit le tracer en ignorant les valeurs négatives et en avertissant l’utilisateur de l’existence du problème.

En outre, il est très difficile de faire bien remplir à un camembert le rectangle de la zone de graphique.

De plus, dès que l’on veut isoler l’ensemble des quartiers en les écartant du centre, ils deviennent tout petits…

Enfin, les camemberts ne s’appliquent qu’à une série à la fois !

La seule façon de les rendre plus efficaces, c’est de leur associer des menus déroulants permettant par exemple de sélectionner la série à représenter. Nous verrons un jour comment réaliser des graphiques avec des menus déroulants.

17 avril 2007

Des précisions sur xlPrecision...

Pour aller un peu plus loin dans la présentation de l’add-in xlPrecision, disons tout d’abord que son auteur s’appelle Greg Lovern, que le prix de cet add-in varie de 2 $ pour la version à 25 chiffres significatifs à 85 $ pour la version à 32.767 chiffres significatifs. Vous pouvez consulter le tarif à l’adresse suivante : http://precisioncalc.com/Buy.html

Notez à ce sujet qu'une version gratuite est disponible, dans laquelle un message flash apparaît de façon régulière tant que vous n'avez pas acheté le produit.

Voici le tableau du 13 avril, enrichi de quelques exemples supplémentaires :

On voit en lignes 10 à 12 ce qui se passe quand on multiplie deux grands nombres avec Excel seul, puis avec xlPrecision, et enfin – toujours avec xlPrecision – en spécifiant un format avec séparateur des milliers.

Dans les lignes 14 et 15, on découvre la possibilité de sélectionner le nombre de chiffres significatifs de son choix.

Il y a donc plusieurs paramètres pour chacune des fonctions…

13 avril 2007

xlPrecision, un add-in bien précis !

Dans les messages du 1er avril, du 5 et du 9 avril, nous avons vu divers exemples de bugs de calcul d’Excel. Nous avons aussi dit qu’Excel disposait au mieux – quand tout se passait bien – de 15 chiffres significatifs valables.

Que peut-on faire si l’on souhaite réaliser des calculs plus précis ? Une solution est d’avoir recours à un add-in adéquat, ce qui est le cas avec xlPrecision. Cet add-in propose 73 fonctions permettant d’effectuer des calculs avec une précision pouvant aller jusqu’à 32.767 chiffres significatifs !

Bien entendu, ces nombres sont représentés sous forme de texte, sinon Excel reperdrait aussitôt la précision obtenue. Voici ce que cela donne avec en colonne A les formules ayant servi à obtenir le résultat des colonnes B et suivantes :


Il y a tant de décimales que cela continue jusqu’au milieu de la colonne H, les colonnes C à H ayant la même largeur.

On remarque en B4, que nous avons reformatée pour ajouter toutes les décimales affichées à l’écran, qu’Excel ne met plus que des « 0 » après le quinzième chiffre significatif.

Notons en revanche avec B6 que la re-multiplication par 7 du 1/7 de B3 nous nous donne pas « 1 » mais un zéro suivi d’une tripotée de « 9 » en décimales.

Remarque 1 – Les fonctions d’xlPrecision sont utilisables en VBA.

Remarque 2 – Il est possible de formater les résultats, par exemple pour grouper les chiffres de la partie entière par paquets de trois.

09 avril 2007

Excel et sa variance négative...

Aujourd’hui, contrairement aux deux derniers messages, je vous présente un bug de calcul qui – lui – est enfin résolu dans les versions récentes d’Excel.

Il est quand même intéressant de vous le présenter parce qu’il est toujours présent chez tous ceux qui utilisent encore une version XP ou antérieure (98, 97,…) d’Excel.

Définition de la variance

Rappelons que la variance d’une série de valeurs est la moyenne des carrés des écarts entre ces valeurs et la moyenne de la série. Par construction donc, une variance est positive ou nulle. Quand on prend une série de trois nombres consécutifs par exemple n-1, n et n+1, la moyenne est n et la variance est égale à (1^2+0^2+1^2 )/3 = 2/3.

La variance d’une série de valeurs (cf. ci-dessous) est calculée à l’aide de la fonction Var.P(), la fonction Var() étant réservée au calcul de la variance estimée d’une population dont la série serait un échantillon. Voici ce que vous obtenez avec un Excel récent :

Les variances négatives d’Excel

Si vous avez une version d’Excel XP ou antérieure, vous obtenez, avec la même formule, des variances farfelues, dont certaines sont négatives, ce qui est fort de café pour des moyennes de carrés ! Voici ce que j’obtiens avec Excel XP :

Et cela a duré pendant des années et des années, malgré nos remarques – et celles de bien d’autres utilisateurs – adressées à Microsoft… En fait, jusqu’aux versions récentes d’Excel, certaines fonctions statistiques d’Excel étaient non seulement peu précises (au maximum 6 chiffres significatifs, contre 15 au maximum pour Excel), mais elles avaient des bugs !

05 avril 2007

Tendance géométrique douteuse !

Puisque j’ai commencé à vous traumatiser il y a quatre jours en vous montrant la capacité d’Excel à « se planter », nous allons poursuivre sur la même voie aujourd’hui avec un autre exemple intéressant, qui présentera par ailleurs l’intérêt de nos faire découvrir la recopie incrémentée « avec tendance ».

Nous avons un tableau dans lequel nos avons entré les constante 1 en A1:C1, 10 en B2:C2 et 100 en C3. Puis nous avons saisi la formule =10*A1 en A2, formule reproduite ensuite vers le bas.

La colonne A nous renvoie des résultats tout à fait rassurants. Sélectionnons à présent B1:B2, puis cliquons – avec le bouton droit de la souris – sur le curseur de recopie incrémentée en bas de B2 et tirons jusqu’en B20. L’écran ci-dessous montre comme se présente alors notre document :

Nous constatons donc que la recopie incrémentée, avec le bouton droit, donne accès aux commandes « Tendance linéaire » et « Tendance géométrique ». Nous sélectionnons cette dernière car nous voulons obtenir une série géométrique, c’est-à-dire où chaque nouvelle valeur est le produit de la précédente par la même constante : 10 ici, puisqu’il y a un facteur 10 entre B1 et C1.

Quand nous lâchons enfin la souris, nous constatons qu’il y a des problèmes en bas de la colonne. Sélectionnons à présent C1:C3 et faisons exactement la même opération de recopie incrémentée avec tendance géométrique.

Et voici le résultat où, cette fois-ci, au lieu d’avoir des unités en trop, on en perd :

01 avril 2007

Comment avoir A=B et A-B=16 ?

Nous allons étudier aujourd’hui la conception originale d’Excel en matière d’algèbre. Prenons un modèle dans lequel nous avons entré la valeur 1 dans A1:A15 et 10^16 en A16. Nous calculons le total de la colonne en A18 dont la présentation ne diffère de celle de la cellule A16 que par son format numérique à 0 décimale et avec séparateur des milliers.

Les formules des cellules de la cellule A18 et de la ligne 20 sont reprises dans leurs commentaires.

Remarque 1 – Nous remarquons déjà en A18 que nous avons apparemment perdu – dans la somme – les quinze « 1 » du bloc A1:A15…

Maintenant, copions A1:A15 pour le coller en B2, et A18 pour le coller en B1. Voici le résultat, dans lequel nous constatons que A=B (en A20) alors que A-B=16 (en B20). Intéressant, n’est-ce pas !

Remarque 2 – Le plus drôle est que la différence entre A et B est de 16, alors que – selon les totaux de la ligne 18 – on a perdu 15 unités, pas 16…

Eh oui, tout cela vient de ce qu’Excel ne fait pas ses calculs en précision totale !

Remarque 3 – Ce message, malgré la date, n'est pas un poisson d'avril !