Les dernières fonctions d’info

Dans les rubriques précédentes, nous venons de passer en revue la plupart des fonctions d’information d’Excel. Nous terminons cette présentation avec les six dernières fonctions, dont le rôle est résumé dans le tableau suivant :

Remarque 1 – En ce qui concerne la fonction EstRef(), ce qui compte comme argument c’est ce qui se trouve entre parenthèses et non le contenu de l’adresse indiquée, ce qui explique la différence entre les résultats en B6 et B7. Notons par ailleurs que cette fonction ne fonctionne pas bien : elle fournit parfois des résultats faux ; mais ce n’est pas bien grave car – pour ma part – je ne vois pas bien son utilité.

Remarque 2 – La fonction info(), avec l’argument en colonne E, fournit divers renseignements. Notons une fois de plus l’incohérence de l’aide d’Excel, où l’argument « répertoire » est indiqué, alors qu’il faut l’écrire sans accent pour obtenir le bon résultat…

Les tests d’erreur dans la feuille

Excel possède trois fonctions pour tester la présence d’erreurs dans une cellule : EstErr(), EstErreur() et EstNA(). Cette dernière est la plus simple et renvoie VRAI quand la cellule vaut #NA! et FAUX autrement.

Pour les deux autres, nous constatons une fois de plus la très mauvaise qualité de l’aide d’Excel puisque, comme nous le voyons en bas de la copie d’écran ci-dessous, les noms des erreurs sont affichés en anglais pour la fonction EstErr() et en français pour EstErreur(). Encore du travail bâclé !

Dans le tableau, nous avons provoqué des erreurs dans la colonne A, en indiquant dans la colonne B la formule obtenue pour engendrer ces erreurs. En colonne F, nous avons montré, dans certains cas, une seconde formule possible pour obtenir l’erreur concernée.

En E1, nous avons entré la formule =type.erreur($A2), que nous avons ensuite reproduite vers le bas.

Remarque 1 – Le seul cas particulier est l’erreur #REF! que l’on ne peut pas provoquer par une formule directe. Pour obtenir cette erreur, nous avons fait référence à une cellule dont nous avons ensuite détruit la colonne.

Remarque 2 – On ne voit pas bien l’intérêt de la fonction EstErreur() qui ne se distingue de EstErr() que par sa capacité à déceler l’erreur #NA! alors que celle-ci peut être décelée grâce à la fonction EstNA().

Les fonctions d'information d'Excel

Aujourd’hui, nous vous présentons la famille des fonctions d’information d'Excel.

Cette famille comporte quinze membres. Nous avons déjà étudié la fonction cellule() en détail dans la rubrique du 10 décembre 2006. Dans la dernière rubrique, nous avons présenté les fonctions EstVide(), EstNum() et EstTexte().

Remarque 1 – Les majuscules ne servent à rien, mais je les ai mises ici pour rendre les noms de ces fonctions plus explicites. En général, je ne tape jamais de majuscule pour un nom de fonction : ainsi, quand je fais une faute de frappe, je m’en aperçois tout de suite car le nom de la fonction ne passe pas en majuscules lors de la validation de la formule.

Vous avez ci-dessus la liste des fonctions avec leur syntaxe (selon Excel) et la date de leur présentation, sauf en ce qui concerne les fonctions na() dont le seul but est de provoquer l’erreur #NA ! et la fonction EstNonTexte() qui est le pendant de la fonction EstTexte() déjà vue il y a quatre jours.

Remarque 2 – La famille de fonctions « Informations » n’est pas constituée de façon très cohérente. Ainsi, on y trouve la fonction na() qui n’informe sur rien, et on n’y trouve pas une fonction telle que nbcar() qui – elle – fournit une information sur la cellule concernée.

Quelques fonctions d’information

Dans le petit modèle ci-dessous, dont les formules sont ensuite reproduites, nous avons entré des valeurs en A2 et A6, une chaîne de caractères vide en A3, rien du tout en A4 et un texte en A5.

Dans les colonnes suivantes, nous affichons le résultat – pour chaque entrée – des fonctions EstVide, EstNum, EstTexte et NbCar. Enfin, en F8 et F9, nous avons calculé le total de deux façons différentes.

Remarque 1 – Il est intéressant de noter que la cellule contenant un texte vide n’est quand même pas vide, qu’elle contient du texte même s’il n’y en a pas, et que son contenu non vide représente 0 caractère. Cela peut surprendre un peu, mais reste cohérent.

Remarque 2 – En ce qui concerne la sommation, on vérifie bien que la fonction somme() ignore les textes, alors que la somme terme à terme (en F9) aboutit à une erreur si l’un des termes n’est pas numérique.

Que faire alors pour additionner des cellules individuelles (par exemple en des endroits différents de la feuille) quand certaines des cellules peuvent contenir du texte ?

Il suffit tout simplement d’utiliser la fonction somme() : dans cette feuille, par exemple, la formule =somme(A2;A3;A4;A5;A6) aboutit bien au total de 123 !

Remarque 3 – La couleur (bleu ou rouge) du bloc C2:E6 provient d'un format conditionnel...

Réglez la vitesse de votre film

Dans la dernière rubrique, nous avons vu comment faire du cinéma avec Excel.

Ceci dit, il y a de grandes variétés dans la vitesse des processeurs des PC et le même film peut défiler normalement sur une machine, et beaucoup trop vite sur une autre.

C’est pourquoi, quand je développe des modèles avec animation, j’ajoute en général un curseur grâce auquel l’utilisateur modifie la vitesse du défilement. Voici donc la présentation finale de notre modèle « Cinéma » :

La macro définitive est listée ci-dessous. On remarquera que le « frein » est obtenu en réalisant – de zéro à un million de fois selon la vitesse choisie – une opération totalement inutile calculant k*(k+1)/2.

Faites du cinéma avec Excel

Le modèle que nous avons mis au point il y a quatre jours permet, avec un ascenseur horizontal, de modifier le point de départ d’une série graphique.

Pour montrer cette évolution en continu, en un mot pour faire du cinéma avec Excel, il suffit d’écrire une macro qui modifiera la valeur de C1 entre ses limites actuelles 0 et 350.

C’est ce que fait la macro ci-dessus, dont nous avons superposé le texte au classeur Excel.

Cette macro est toute simple : elle utilise une boucle pour modifier les valeurs de C1. Sa seule particularité est la présence de l’instruction :

Application.ScreenUpdating = True

Cette instruction force Excel à rafraîchir l’écran à chaque itération.

C’est ce qu’il est censé faire mais, comme vous le constaterez aisément en transformant cette instruction en commentaire, le mouvement du film est plus saccadé si l’on ne force pas ce rafraîchissement.

Zoom sur une partie de graphe

On dispose parfois de données numériques sur de longues périodes et l’on souhaite afficher un graphe à partir d’un certain point dans cette chronique.

Cela est utile par exemple pour la comparaison de cours d’actions quand on veut comparer deux séries, par exemple le CAC 40 et le cours d’une action, en choisissant le point de départ à une date donnée.

Dans l’exemple ci-dessous, nous avons entré des dates consécutives en colonne A. En B1, histoire d’avoir une jolie courbe, nous avons entré la formule =sin(pi()*(ligne()-1)/22,5)*(1-cos(pi()*(ligne()-1)/17)) que nous avons tirée vers le bas jusqu’en A456 (décembre 2007).

En D1, nous avons mis la formule =decaler(B1;$C$1;0), reproduite ensuite jusqu’en D100. Le graphe représente les valeurs D1:D100.

Enfin, en utilisant l’outil « Barre de défilement » de la barre d’outils Formulaires, nous avons créé un ascenseur horizontal lié à C1 et prenant les valeurs de 1 à 350.

Remarque – Si vous utilisez Excel 7, lisez mon message du 21 novembre 2007 pour découvrir comment y faire apparaître la barre d’outils « Formulaires ».

Grâce à l’ascenseur, nous sélectionnons le point de départ entre janvier 1970 et mars 1999, et le graphe représente immédiatement les 100 valeurs débutant à cette date.

Titre dynamique dans un graphe

Dans les deux dernières rubriques, nous avons appris consécutivement à créer un nom dynamique, puis un graphe élastique. Il ne nous reste plus qu’à donner un titre dynamique à ce graphe pour couronner cette œuvre !

La façon la plus rapide d’associer un texte à un graphe est de cliquer dans la zone de graphique pour la sélectionner, puis de taper le texte dans la zone de formule et enfin de valider par [Entrée]. Le texte est alors ancré dans le graphique et apparaît en plein milieu, prêt à être logé au bon endroit.

Faisons donc cela puis tapons la formule ="Il y a "&nb(A:A)&" valeurs pour un total de "&somme(A:A) et validons par [Entrée]. Nous obtenons le résultat suivant :

Excel nous dit que la formule contient une erreur ! Copions donc cette formule refusée par Excel, cliquons en B2 et collons-y la formule.

Sélectionnons à nouveau la zone de graphique puis tapons – dans la barre de formule – le signe « = » puis cliquons en B2 et validons enfin par [Entrée].

Et le tour est joué ! Le texte apparaît enfin au milieu du graphique. Il ne reste plus qu’à déplacer ce titre à l’endroit désiré.

Remarque – Pour améliorer la présentation, la toute dernière étape consiste à glisser la cellule B2 en C2, afin de cacher la poussière sous le tapis !