Graphe en “secteurs de secteur”

Nous nous intéressons aujourd’hui à un graphe d’un type particulier, baptisé par Excel « secteurs de secteur », ce qui ne nous avance pas vraiment beaucoup quand on ne sait pas bien de quoi il s’agit…

Il s’agit d’analyser la répartition de « gâteaux » salés ou sucrés en fonction de leurs composants. Nous débutons en sélectionnant le bloc C4:D16 et en insérant le camembert baptisé « secteurs de secteur », le seul d’ailleurs avec deux camemberts, un principal et un secondaire. Nous obtenons alors le graphe ci-dessous :

Pour obtenir le résultat reproduit à la fin de cet article, il nous faut à présent effectuer les étapes suivantes :

• mettre la légende en bas du graphe ;
• ajouter des étiquettes de données :
• pour celles-ci, cocher « Nom de catégorie » et « Pourcentage »
• puis, en « Mise en forme », leur ajouter un cadre ;
• remplacer l’étiquette « Autre » par « Sucré » ;
• replacer les étiquettes chevauchant les autres étiquettes ;
• ajouter le titre « Gâteaux salés et sucrés » et l’encadrer.

Et voilà ! Vous venez de créer probablement votre premier camembert en  « secteurs de secteur ».

Remarque 1 – Nous sommes en France, il n’est pas surprenant donc que le gâteau (« pie », en anglais) devienne un camembert en traversant la Manche, d’autant que la Normandie, la région de la ville éponyme, est une des premières régions françaises que découvrent les anglais en débarquant…

Remarque 2 – Même avec les améliorations que nous lui avons fait subir, ce camembert n’est peut-être pas la meilleure façon d’illustrer graphiquement nos données. D’ici mon prochain article, dans lequel vous trouverez une réponse à ce sujet, essayez d’imaginer ce que vous pourriez faire pour l’améliorer…

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 :

http://www.decisionmodels.com/calcsecretsi.htm

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 :

http://chandoo.org/wp/2014/03/03/handle-volatile-functions-like-they-are-dynamite/

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() :

https://fastexcel.wordpress.com/2016/04/25/indirect-excels-most-evil-function/

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 :

http://www.linguee.fr/anglais-francais/traduction/single-threaded+software.html

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

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.