Monsieur Excel
Pour tout savoir faire sur Excel !

22 mai 2016

Remarques sur la volatilité

La volatilité dans les formules d’Excel est un sujet délicat, comme nous allons le voir avec les trois rubriques ci-dessous.

Déclarations erronées de Microsoft

La formule =cellule("Filename"), selon un article de Microsoft, ne serait pas volatile. Et pourtant elle l’est…

Notez d’ailleurs que dans l’aide d’Excel, Microsoft vous indique que l’argument à utiliser avec un Excel français est "Nom de fichier". Si vous utilisez cet argument au lieu de "Filename", vous obtenez l’erreur #VALEUR !

Un Somme.si() délicat

La formule =somme.si(A1:A4 ;">0";B1) est volatile, alors que la formule  =somme.si(A1:A4 ;">0";B1:B4) ne l’est pas. De plus, de fait, ces deux formules feront référence à B1:B4.

Il semblerait que ce comportement volatile ait été ajouté pour donner des résultats exacts quand le dernier argument est modifié… Cette situation a été découverte par Luke Wisbey.

Une fonction volatile impacte la formule qui la contient

De façon tout à fait surprenante, quand on utilise dans une formule une fonction volatile,  la formule elle-même devient volatile même si la fonction volatile n’est jamais exécutée !

Ainsi, avec la formule =si(2>1;100;maintenant()), on obtient toujours le résultat 100 et on n’exécute donc jamais la fonction maintenant(). Et pourtant, la cellule contenant cette formule est marquée comme volatile dans Excel !

Si maintenant vous entrez =maintenant() en A1, la formule =si(2>1;100;A1) continuera à toujours renvoyer 100, mais ne sera plus marquée comme volatile dans Excel !

Pour en savoir plus sur le calcul dans Excel et sur la volatilité

Une lecture tout à fait intéressante, complémentaire de celles que je vous ai déjà conseillées dans les quatre derniers articles :

17 mai 2016

La volatilité dans Excel

Dans le dernier article, nous avons évoqué le problème de la volatilité dans Excel en disant que, pour chaque cellule utilisant une fonction volatile, cette cellule, ainsi que toutes les cellules qui leur son dépendantes directement ou indirectement, sont calculées chaque fois que l’on modifie le contenu d’une cellule quelconque d’un classeur ouvert quelconque.

En fait, la situation réelle est pire encore que cela car ce n’est pas seulement la modification d’une cellule qui peut entraîner tous ces calculs. D’autres événements peuvent aussi provoquer cette vague de calculs :
  • insertion ou suppression de ligne(s) ou de colonne(s) ;
  • certaines utilisations du filtre automatique ;
  • double clic sur un séparateur de ligne ou de colonne ;
  • ajout, édition ou suppression d’un nom ;
  • modification du nom d’un onglet ;
  • déplacement d’un onglet ;
  • masquage ou démasquage de lignes (pas de colonnes !).
Cela peut entraîner des temps de calcul énormes, allant jusqu’à rendre le modèle – dans les pires cas – pratiquement inutilisable.

Le cas de maintenant() ou aujourdhui()

De nombreux utilisateurs ont recours à cette fonction pour vérifier comment une date saisie se situe par rapport à la date du jour. On pourrait éviter tout cela avec par exemple une macro qui, à l’ouverture du classeur, mette la date du jour dans une cellule donnée.

Le cas de indirect() ou decaler()

De la même façon, une partie des objectifs que l’on cherche à atteindre à l’aide des fonctions indirect() ou decaler() peut être atteinte à l’aide des fonctions index() et choisir().

Liste des fonctions volatiles 

Voici la liste des fonctions volatiles :
  •     maintenant()
  •     aujourdhui()
  •     alea() et alea.entre.bornes()
  •     decaler()
  •     indirect()
  •     info() (selon ses arguments)
  •    cellule() (selon ses arguments)
Je me suis inspiré pour cet article de l’excellente publication de Chandoo sur ce sujet :

12 mai 2016

La fonction indirect (c)

Ce qui m’a amené à vous reparler de la fonction indirect(), c’est un sondage lancé par mon collègue MVP Charles Williams, demandant aux experts Excel de répondre à un sondage pour identifier les fonctions Excel les plus dangereuses.

Son opinion est qu’il s’agit pour lui de la fonction indirect() :

Ses reproches sur la fonction indirect() sont les suivants :
  • elle est volatile
  • elle est « single-threaded »
  • elle renvoie facilement #REF !
  • elle exige que le classeur référencé soit ouvert
  • elle ne s’adapte pas aux modifications de structure
  • elle est difficile à « débugger »
Le problème de la volatilité

En fait, sans vous le dire, Excel construit un arbre de dépendance documentant comment les cellules d’un classeur dépendent les unes des autres. De grands modèles peuvent donc contenir de grandes chaînes de dépendance répertoriant des listes de milliers de cellules. C’est grâce à cela qu’Excel gère tous les calculs en connaissant ainsi l’ordre de priorité des calculs.

Le problème, c’est que les fonctions volatiles sont toutes recalculées chaque fois que vous modifiez le contenu d’une cellule quelconque d’un classeur ouvert quelconque.

Si donc un de vos classeurs contient des fonctions volatiles dans des cellules qui possèdent de grandes chaînes de dépendance, vous effectuez un grand nombre de recalculs superflus, ce qui peut ralentir votre modèle de façon très significative.

Nous reparlerons de la volatilité dans le prochain article…

Single-threaded

Ce terme peut faire un peu peur, et il est mis à toutes les sauces dans les traductions en français :

L’option « multi-threaded » a été introduite avec Excel 2007.
Elle vous permettra d’effectuer vos calculs plus rapidement si vous disposez d’un multi-processeur. 

Pour en savoir plus, lisez plutôt ce qu’en dit Microsoft :
https://support.microsoft.com/en-us/kb/2696967

05 mai 2016

La fonction indirect (b)

Le problème suivant a été fourni par un lecteur du blog. Une entreprise doit suivre les activités de ses clients, avec un onglet par client. Chacun de ces clients peut passer au maximum deux commandes par jour. L’objectif est de regrouper dans un onglet de récapitulation la liste des commandes des clients.

Nous voyons dans l’écran ci-dessous la présentation de la récapitulation avec – en insert – une photo de l’onglet Lerouge.


La formule de B5, reproduite vers le bas, est :
=B4+SI(ET(B4<>B3;NBCAR(C4&D4&E4)>3);0;1)

Grâce à cette formule, on ne reproduit le jour que si, pour celui-ci, au moins une des trois solonnes a été renseignée.

La formule miracle, saisie en C4 et reproduite dans les colonnes C à E, est la suivante :
=SI($A4<>$A3;index(indirect(C$2&"!B:B");equiv($A4;indirect(C$2&"!A:A");0));
index(indirect(C$2&"!B:B");equiv($A4;indirect(C$2&"!A:A");0)
+equiv($A4;decaler(indirect(C$2&"!A1");
equiv($A4;indirect(C$2&"!A:A");0);0):indirect(C$2&"!A2000");0)))

Si vous maîtrisez bien le fonctionnement des fonctions de recherche, vous n’aurez aucune difficulté à comprendre cette formule :)

Remarque 1 – Vous pouvez noter en particulier que cette formule utilise 7 fois la fonction indirect() que nous avons étudiée dans l'article précédent…

Remarque 2 – Pour éviter l’affichage de la ligne n quand aucune commande n’apparaît, on pourrait écrire une macro qui masque toutes les lignes pour lesquelles NBCAR(Cn&Dn&En)=0

Remarque 3 – Cet article et l’article précédent sont une actualisation (et amélioration) des articles déjà publiés dans ce blog les 14 et 18 avril 2011.

30 avril 2016

La fonction Indirect (a)

Il y a longtemps que nous n’avons pas parlé de la fonction indirect(), qui est pourtant une fonction particulièrement originale, et importante, d’Excel.

Supposez qu’en B1, vous ayez récupéré (cf. formule reproduite en B8) un montant de dépense provenant d’un autre classeur. Vous souhaitez rendre cette recherche paramétrable, en vous gardant la possibilité de modifier le nom du classeur, celui de la feuille, ou l’adresse de la cellule concernée.

Cela se fait très bien en utilisant la solution proposée en B2 (cf. formule en B9), grâce à la fonction indirect(). L’argument de la fonction sert uniquement à reconstituer, caractère par caractère, la formule que nous avions en B1 (cf. formule en B8).


Remarque 1 – Si le classeur référencé dans la formule n’est pas ouvert, le résultat de la formule est le message d’erreur #REF !

Remarque 2 – Dans le cas ci-dessus, qu’il y ait $B$129 ou B129 dans la cellule B6 revient strictement au même.

Syntaxe de la fonction

La syntaxe de la fonction est =indirect(référence[;A1]).
Les crochets dans la syntaxe indiquent que cet argument est facultatif.

Remarque 3 – Notez que, contrairement à Microsoft, nous mettons bien  dans nos syntaxes  le ";" à l'intérieur des crochets. Dans l'aide d'Excel, Microsoft les met toujours, à tort, en dehors...

Si l’argument facultatif A1 est FAUX, la référence doit être de type L1C1.

S’il est VRAI ou absent, la référence doit être de type A1, c’est-à-dire une ou plusieurs lettres pour la colonne et un nombre pour la ligne.

24 avril 2016

Découverte de la fonction N()

Une des fonctions les moins connues d’Excel est la fonction N(), qui est censée assurée la conversion en nombre de son argument.

En fait, dans une formule, sans rien vous demander, Excel assure lui-même la conversion chaque fois que nécessaire. Ainsi, quand A1 contient le texte « ’23 », et que l’on entre en B1 la formule « =10*A1 » , le résultat affiché est bien 230.

Historiquement, cette fonction a plutôt été conçue pour assurer une meilleure communication avec d’autres logiciels.


Le tableau ci-dessous indique les résultats obtenus avec divers arguments. J’ai fait exprès de choisir en B2:B10 des arguments cités en exemple dans l’aide de Microsoft.

On tombe sur un os avec B10, où j’ai repris l’exemple de l’aide de Microsoft qui annonce que l’on obtient alors la valeur de la date, comme cela avait été le cas en C3...

Le traducteur de l’aide n’a pas réalisé que les dates en français ne s’écrivaient pas comme en B10. C’est franchement désolant, et cela fait de multiples fois que je cite des exemples d’erreurs de ce genre dans l’aide d’Excel : Microsoft n’a hélas jamais voulu investir quoi que ce soit pour faire relire toute l’aide d’Excel par quelqu’un qui maîtrise le logiciel.

La fonction N() en guise de commentaire

Une application originale de la fonction N() est illustrée en B12. Comme on le constate en lisant la formule dans la barre de formule, on peut utiliser cette fonction – puisqu’elle évalue un texte à 0 ! – pour mettre un commentaire dans la formule elle-même !

Cela vous évite d’avoir à passer par la création de commentaires, d’avoir à supporter les triangles rouges, et de devoir gérer leur affichage ou non ! Mais cela ne fait hélas pas de la programmation très propre :(

Essayez de gagner un drone, votez pour Alzohis !

Dans le post précédent, je vous ai demandé de voter pour le projet Alzohis de diagnostic Alzheimer à partir dune prise de sang. Il faut absolument que cette start-up arrive en premier pour gagner ce concours. Elle a besoin de chaque vote possible ! Cela vous prendra moins d'une minute et vous ferez une bonne action !

http://entreprendre.bforbank.com/32/alzohis

Si vous voulez être plus sympa encore, demandez à vos amis - via Facebook ou autres - d'en faire autant. La date limite est le 25 avril au soir ! Il ne faut pus tarder...




19 avril 2016

Vous pouvez gagner un drone !

Il y a un peu plus d’un mois, le 16 mars, je vous avais présenté la société Alzohis, à laquelle je participe, qui fait du diagnostic Alzheimer à partir d’une simple prise de sang.

Comme promis, le site – qui n’était au départ qu’en anglais – est à présent disponible aussi en français :

Essayez de gagner un drone !

Alzohis participe, dans le cadre du réseau Entreprendre dont elle est lauréate, à un concours de start-ups animé par B for Bank. Vous pouvez participer en votant pour Alzohis dans la liste des 18 projets retenus pour ce concours :
http://entreprendre.bforbank.com/32/alzohis

Je vous encourage à voter pour trois raisons. 

La première est que cela vous donne une chance de gagner un drone ! 

La seconde est que si, comme moi, vous croyez dans ce projet, il faut tout faire pour lui permettre d’aboutir. 

La troisième est que cela représente une façon de me remercier pour tous les conseils sur Excel que je vous donne depuis octobre 2009 sur ce blog.

Attention ! La date limite pour les votes est le 25 avril !