Monsieur Excel
Pour tout savoir faire sur Excel !

21 janvier 2015

Utilisation du tri horizontal

Quand on reçoit, pour importation dans Excel, un fichier provenant d’une source extérieure, ERP ou autre, les colonnes qui nous intéressent ne sont pas forcément dans le bon ordre. Et, souvent, il y a des colonnes qui ne servent à rien…

Dans l’exemple ci-dessous, nous avons reçu la liste reproduite à partir
de la ligne 2. Nous entrons en ligne 1 le numéro d’ordre dans lequel nous souhaitons ranger les colonnes, avec « 10 » pour celles qui ne nous intéressent pas.


Il suffit alors de cliquer n’importe où dans la base, d’appeler la commande « Trier » de l’onglet « Données », ce qui sélectionne la base, puis de cliquer dans le bouton « Options », de demander l’orientation « De la gauche vers la droite », de valider, puis – dans le champ « Trier par » de choisir « Ligne 1 » et de valider.

Remarque – Après le tri, les largeurs de colonnes n’ayant pas été modifiées, le résultat peut se révéler assez inesthétique. Il est alors nécessaire de redimensionner les colonnes par un double clic sur l’un quelconque des séparateurs de colonnes.


16 janvier 2015

Consolidation sur plusieurs onglets

Nous avons déjà publié, il y a quelques mois, trois articles relatifs à la consolidation dans Excel.

Dans le premier article, en particulier, nous montrions comment consolider automatiquement tous les onglets placés entre deux onglets « limites » baptisés « A » et « Z ».

« Une consolidation à géométrie variable », le 6 mai 2014
« Consolidation sans utiliser les onglets A et Z », le 11 mai 2014
« Consolidation avec identification des titres », le 18 mai 2014

Une de nos lecteurs, Alexis Joulié, ne souhaitait pas consolider en déplaçant les onglets de son modèle. Il nous a donc concoté une autre approche de consolidation. 

Dans ce modèle, chaque pays possède un onglet dont un exemple est donné encadré en bleu, avec des unités pour le premier pays, des dizaines pour le second, et ainsi de suite… afin de bien vérifier que les consolidations sont bonnes.


Le résultat de la consolidation, en C13, est obtenu par la formule :
=sommeprod(somme(indirect("'"&PaysChoisis&"'!B"&$C$3)))

Cela fonctionne car "'"&PaysChoisis&"'!B"&$C$3 a pour résultat {"'Pays 1'!B2";"'Pays 4'!B2";"'Pays 3'!B2"} et indirect(…) vaut {5;9000;600}.


11 janvier 2015

Un histogramme très « tendance »

Notre objectif aujourd’hui est de créer l’histogramme reproduit ci-dessous, à partir des données affichées en A1:C11. Cet histogramme affiche en haut de chaque barre la tendance de la série.

Travail de préparation

Tout d’abord, nous ajoutons en G1 et H1 les symboles qui seront utilisés dans le graphe. Puis nous entrons des formules en D2 et E2 qui sont ensuite reproduites vers le bas.


En D2, la formule =max(B2;C2)
En E2 : =si(C2=B2;"===";si(C2>B2;$G$1;$H$1)&texte(abs((C2-B2)/B2);"0%"))

Création de l’histogramme

Sélectionnons A1:D11 et créons un histogramme simple, ce qui nous affiche les trois séries.

Sélectionnons la troisième série et ajoutons-lui des étiquettes de données, en décochant « Valeur » et en cochant « Nom de catégorie ».

Ensuite, via la commande « Mettre en forme une série de données », associons-la à l’axe secondaire, puis donnons-lui l’option « Aucun remplissage ».

Nous nous approchons de l’objectif mais, pour le moment, les étiquettes sont encore les noms des produits.

Modification des étiquettes

Pour modifier les étiquettes, cliquons dans le graphe et appelons la commande « Sélectionner les données ». Sélectionnons la série « Max » dans le bloc de gauche et, dans le bloc de droite, cliquons dans le bouton « Modifier ». Il suffit alors de remplacer =Feuil1!$A$2:$A$11 par =Feuil1!$E$2:$E$11. Et le tour est joué !

Peaufinage final

Il reste quelques étapes à accomplir pour obtenir le même résultat que dans la copie d’écran…

1 – Modifier les couleurs des séries en bleu intense et en rouge vif.
2 – Oter de la légende la référence à la série « Max »
3 – Mettre la légende en bas de l’écran
4 – Mettre en forme les étiquettes en leur donnant un remplissage blanc
5 – Faire varier l’axe gauche de 0 à 110 et effacer l’axe droit
6 – Sélectionner le quadrillage horizontal et le mettre en bleu

Et, pour conclure…

Mes remerciements vont à Mynda Treacy chez qui j’ai trouvé l’idée de cet article :

Par ailleurs, je vous annonce que Microsoft m’a renouvelé comme « MVP Excel » pour l’année 2015, ce qui fait donc la septième année…


06 janvier 2015

Mes formations en fin janvier

Vous trouverez ci-dessous la liste des formations que je propose pour la dernière quinzaine de janvier 2015. Chacune de ces formations est limitée à huit participants, chaque participant venant avec son ordinateur.

·         Modélisation avec Excel (2 jours) : les mardis 20 et 27 janvier 2015.
·         Modélisation du risque : le jeudi 22 janvier 2015.
·         Création de tableau de bord sous Excel : le jeudi 29 janvier 2015.

Pour avoir une description détaillée de ces formations :

Les autres séminaires habituels ne sont pas proposés en inter lors de cette session. Toutes les formations proposées sont organisées en association avec la société EuroDécision.

Avec la formation « Modélisation avec Excel », je garantis de transformer en deux jours tout utilisateur moyen d'Excel en « power user », c'est-à-dire à l'amener au niveau des 5% des meilleurs utilisateurs d'Excel.

Avec la formation « Modélisation du risque », vous découvrirez comment utiliser Excel pour faire des simulations probabilistes (dites de Monte Carlo). Nous utiliserons à cet effet l’add-in Crystal Ball, le meilleur produit au monde dans cette catégorie.

La formation « Création de tableau de bord sous Excel » est inédite. Elle vous permettra, en une seule journée, de totalement maîtriser la création d’un tableau de bord personnalisé, comme vous pouvez le voir dans l’article
« Notre premier tableau de bord » du 22 mai 2013.

Chacune de mes formations peut être animée en intra dans votre entreprise et – le cas échéant – personnalisée grâce à l’analyse et à l’amélioration des modèles propres à votre entreprise.

Ne loupez pas cette occasion de découvrir tout cela de la bouche même de l’auteur de ce blog, qui partagera avec vous l’expérience qu'il acquise en développant plus de 1.000 modèles dans plus de 100 entreprises en 10 pays.

Quelques-unes de mes références de formation intra : Aéroports de Paris, Aérospatiale, Arianespace, Bouygues, Caisse des dépôts, CASE-Poclain, CCIP, Cegelec, CNES, CNET, EADS, EdF, Elf, Ernst & Young, Euroconsult, Finacor, France Telecom, Gaz de France, GIAT, IFP, Isochem, Lafarge, La Poste, Lilly France, Marsh, RTE, Sanofi, SIRIS, Texas Instruments, Tir Groupé, Total, Wabco, Walt Disney.


01 janvier 2015

Comment concaténer à répétition…

Tout d’abord, je souhaite à tous
une heureuse année 2015 !

Vous avez en colonne A le début de la liste d’acteurs que nous avons déjà utilisée dans l’article « La liste déroulante "interactive" » du  10 novembre 2014. Vous souhaites concaténer ces dix noms avec la formule =concatener(A1:A10) et vous constatez en C1, qui utilise la formule reproduite en B1, que seul le premier nom apparaît.

Vous essayez en C2 ensuite la formule =A1:A10&";" qui, elle, affiche le second nom suivi d’un « ; ».


L’astuce consiste ensuite à suivre les deux étapes décrites en B4 et B9.

Merci à Brian Canes chez qui j’ai trouvé l’idée de cet article !


26 décembre 2014

Masquer les erreurs à l’impression

Pour reprendre l’exemple publié il y a quelques jours, cela ne vous pose pas toujours de problème d’avoir certaines erreurs affichées à l’écran, tant que cela ne perdure pas au moment où vous imprimez votre rapport.

Vous disposez alors d’une seconde solution, encore plus simple à mettre en œuvre que la création d’un format conditionnel !

Cette solution est assez peu connue, c’est pourquoi je me permets de vous la présenter. Il suffit pour cela de garder la table telle qu’elle était au départ, avec les messages d’erreur originaux.

Vous activez la commande « Imprimer les titres » de l’onglet « Mise en page », et vous déroulez le menu de la commande « Erreurs de cellule comme : ». Il suffit alors de sélectionner l’option « vide » pour que toutes les cellules contenant des erreurs soient affichées vides à l’impression…




20 décembre 2014

Masquage des erreurs à l’écran

Il arrive qu’un tableau, même bien conçu, comporte des erreurs. C’est le cas du tableau ci-dessous où nous répertorions le salaire moyen d’un groupe de personnes, par sexe, dans plusieurs pays. La dernière colonne affiche le salaire moyen, indépendamment du sexe.

Certains pays n’ayant pas de représentants des deux sexes, cela engendre automatiquement des erreurs de type #DIV/0!, comme le montre le premier tableau ci-dessous. C’est aussi bien entendu le cas pour tout pays ne comptant aucune personne, comme l’Angleterre.


Une solution simple consiste à sélectionner le bloc L11:N21 et à définir un format conditionnel spécifiant que, si la cellule comporte une erreur, la police de caractères doit être de couleur blanche. Nous voyons comment cela est défini dans la seconde copie d’écran.

La dernière copie d’écran, entourée d’un trait bleu, illustre le résultat obtenu.

Remarque – Si vous sélectionnez tout le bloc, vous verrez alors apparaître le contenu les cellules masquées en blanc sur un fond bleuté.