Monsieur Excel
Pour tout savoir faire sur Excel !

31 janvier 2015

Extraction dynamique perso

Cet article prolonge l’article précédent, dans lequel nous avons mis en place un filtre permettant de réaliser une extraction dynamique depuis une base. Si vous ne l’avez pas encore lu, commencez donc par lire cet article car nous continuerons sur le même thème, en enrichissant le modèle précédent.

Dans l’article précédent, la cellule en I2 contenait la chaîne à rechercher dans le champ « Prénom ».

Aujourd’hui, nous souhaitons utiliser deux paramètres : en I1, le nom du champ sur lequel la sélection doit avoir lieu ; en I2, la chaîne de caractères à chercher dans ce champ.


Pour la cellule I1, nous avons utilisé la commande « Validation des données » en demandant une validation par liste à partir de B2:G2.

En J1, nous trouvons le numéro du champ sélectionné grâce à la formule : =equiv(I1;B2:G2;0).

La formule de I3, reproduite vers le bas, est : =si(esterreur(cherche($I$2;decaler(A3;0;$J$1)));"";ligne()).

La formule de K3, reproduite dans le bloc de K à M, est :
=sierreur(index(decaler($A:$A;0;equiv(K$2;$B$2:$G$2;0));petite.valeur($I$3:$I$27;ligne()-ligne($K$2)));"")


26 janvier 2015

Filtre pour extraction dynamique

Nous avons une base dans les colonnes B:G. A droite, dans les colonnes K à M, nous souhaitons extraire – en temps réel ! – le prénom, le nom et le pays de tous ceux dont le prénom contient la chaîne de caractères en I2.


Pour cela, nous entrons en I3, recopiée vers le bas, la formule :
=si(esterreur(cherche($I$2;C3));"";ligne())

Cette formule affiche le numéro de la ligne quand le nom contient la clef demandée.

Ensuite, en K2, nous entrons la formule :
=sierreur(index(C:C;petite.valeur($I$3:$I$27;ligne()-ligne($K$2)));"")

Dans les formules de L2 et M2, il suffit de changer le nom de la colonne indexée.

Dès que l’on modifie la clef en I2, si le mode de calcul est bien automatique, la nouvelle extraction s’opère en temps réel !

Remarque – On pourrait même n’utiliser qu’une formule unique pour le bloc de K à M, mais elle serait sensiblement plus lourde :
=sierreur(index(decaler($A:$A;0;equiv(K$2;$B$2:$G$2;0));petite.valeur($I$3:$I$27;ligne()-ligne($K$2)));"")


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 !