Monsieur Excel
Pour tout savoir faire sur Excel !

27 janvier 2014

Un élément dans une matrice

Avec la formule =equiv(valeur;vecteur;0), il est aisé de trouver dans un vecteur horizontal ou vertical la position d’une valeur cherchée.

Mais cela ne marche pas si l’on cherche à identifier la position d’une valeur cherchée dans une matrice !

Avec l’exemple ci-dessous, dans les deux cellules à fond jaune, nous obtenons la position de la valeur cherchée. 

En B8, cette position est représentée par un nombre où les centaines identifient la bonne ligne et les unités la bonne colonne, à l’intérieur de la matrice concernée, bien sûr.

En B9, on indique de façon plus explicite le numéro de ligne et le numéro de colonne concernés.


Formule de B8 : =sierreur(100+equiv(B6;B2:D2;0);0)+sierreur(200+equiv(B6; B3:D3;0);0)+sierreur(300+equiv(B6;B4:D4;0);0)

Formule de B9 : =sierreur("Lig 1 - Col "&equiv(B6;B2:D2;0);"")&sierreur("Lig 2 - Col "&equiv(B6;B3:D3;0);"")&sierreur("Lig 3 - Col "&equiv(B6;B4:D4;0);"")


22 janvier 2014

Mes formations en mars...

Vous trouverez ci-dessous la liste des formations que je propose pour le mois de mars 2014. Chacune de ces formations est limitée à huit participants, avec un ordinateur par personne.
•  Modélisation avec Excel (2 jours) : les jeudis 20 et 27 mars 2014.
•  Découverte de Visual Basic (2 jours) : lundi 24 et mardi 25 mars 2014.
•  Création de tableau de bord sous Excel : le mercredi 19 mars 2014.

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  « Découverte de Visual Basic », vous ne saurez pas tout sur le VBA, ce qui n'est pas possible en si peu de temps. Mais vous en saurez assez pour développer vos propres macros et savoir où trouver ce que vous ne savez pas encore.

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.

Les cinq formations peuvent être animées en intra dans votre entreprise et – le cas échéant – personnalisées 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.

Cliquez ici pour en savoir plus sur ces formations

17 janvier 2014

Originaux vs. Doublons en mieux !

Dans l’article précédent, nous avons vu comment obtenir automatiquement à la fois la liste des doublons, et celle des valeurs originales, obtenues à partir de deux listes de référence.

La solution proposée par Brian Canes fonctionne parfaitement bien mais je dois avouer que mon sens de l’esthétique – ainsi que mon souci d’efficacité ! – sont heurtés par la nécessité de créer trois colonnes de calculs intermédiaires.

J’ai donc imaginé une solution ne faisant appel qu’à une seule colonne de calculs intermédiaires. Voici donc ma solution, dans laquelle j’ai masqué les colonnes B à D, désormais superflues.

Le principe en est le suivant : j’incrémente un compteur à partir de +1 pour les doublons, et je décrémente un compteur à partir de -1 pour les originaux. Simple, mais efficace !

Les formules originales de ce modèle sont donc les suivantes…

H2 : 0

H3, copiée jusqu’en H7 : =si(nb.si($A$1:$A$15;A3)=1;min($H$2:H2)-1;si(estna( equiv($A$1:A2;0));0;max($H$2:H2)+1)

H11, copiée jusqu’en H15 : =si(nb.si($A$1:$A$15;A11)=1;min($H$2:H15)-1;0)

I3, copiée jusqu’en I15 :
=si(ligne()<=ligne($I$2)+max(H:H);inde(A:A;equiv(ligne()-2;H:H;0));"")

J3, copiée jusqu’en J15 :
=si(ligne()<=ligne($I$2)-min(H:H);index(A:A;equiv(-ligne()+2;H:H;0));"")

Une cerise sur le gâteau : le format conditionnel


Dans les deux blocs de résultats, j’ai utilisé un format conditionnel qui met un fond bleu à toutes les cellules affichant un résultat et les encadre. C’est simple, efficace, et cela rend le résultat plus agréable à lire…

Comme vous pouvez le constater en bas de la copie d’écran, ce format utilise une formule simple…

12 janvier 2014

Les originaux et les doublons

Dans la copie d’écran ci-dessous, nous voyons en colonne A deux listes. Le but du jeu dest d’obtenir, comme nous le voyons dans les colonnes F et G, la liste des valeurs doublées à gauche et celle des valeurs originales à droite.

Imaginez que vous ne voyez pas les colonnes intermédiaires de B à D et essayez donc de trouver une solution efficace à ce problème…


La solution présentée ci-dessus a été trouvée par Brian Canes, un expert d’Excel. Voici la liste des formules originales de son modèle :

B3, recopiée jusqu’en B7, puis dans B11:B15 :
=si(estnum(equiv(A3;$A$11:$A$15;0));"Doublé";"Unique")

C3, recopiée jusqu’en D7 : =si($B3=C$2;max(C$2:C2)+1;"")

D3, recopiée jusqu’en D7 : =si($B3=D$2;max(D$2:D2)+1;"")

D11, recopiée jusqu’en D15 : =si($B11=D$2;max(D$2:D15)+1;"")

F3, recopiée jusqu’en G15 :
=sierreur(index($A$3:$A$15;equiv(ligne(A1);C$3:C$15;0));"")

Remarque – Cette solution utilise en F:G la fonction sierreur(), introduite avec Excel 2007. Elle serait un peu plus lourde si vous souhaitiez la faire fonctionner avec les versions antérieures d’Excel…

07 janvier 2014

Le bug étrange de l’insertion


Le cap du million !

Notre blog « Monsieur Excel » vient de passer le cap du million de visites, depuis sa création en octobre 2005. Ce qui est sympathique aussi, c’est que – depuis le début – le nombre de visites a toujours été en augmentation régulière.

L’an dernier, nous avons passé pour la première fois le cap des 20.000 visites en un mois et aussi celui des 1.000 visites en un jour.

A ma connaissance, vous ne trouverez sur aucun autre blog en langue française une aussi grande masse d'information sur Excel. Cet article est le 715ème publié, chacun faisant en moyenne une page et demie, et vous avez accès à la totalité des articles publiés depuis le début du blog.

________________________________________________________

Le bug étrange de l’insertion

Jerry Latham a découvert un moyen extraordinaire de faire gonfler un modèle Excel de façon totalement abusive. L’expérience est facile à reproduire…

Créez un document Excel et, en A1:A15, entrez le texte « aaaa ». J’ai pris ce texte pour exemple, pour sa simplicité, mais vous pouvez entrer en A1:A15  n’importe quel autre ensemble de textes et/ou de valeurs.

Sélectionnez A1:A3, faites un clic droit, puis « Insérer… », puis « Décaler les cellules vers la droite » et « OK ».

Les trois cellules se déplacent d’une colonne à droite et, quand vous faites [Ctrl]-[Fin] pour aller en bas et à droite de la feuille, vous atterrissez en B15. Tout va bien !

Rassurez-vous, après, cela va aller de plus en plus mal…

A présent, sélectionnez A5:A7, faites un clic droit, puis « Insérer… », puis « Décaler les cellules vers la droite » et « OK ».

Les trois cellules se déplacent d’une colonne à droite, en colonne B. Mais maintenant, quand vous faites [Ctrl]-[Fin] pour aller en bas et à droite de la feuille, vous atterrissez en C15 au lieu de B15. On se demande bien pourquoi…

A présent, sélectionnez A8:A10, faites un clic droit, puis « Insérer… », puis « Décaler les cellules vers la droite » et « OK ».

Les trois cellules se déplacent d’une colonne à droite, en colonne B. Mais maintenant, quand vous faites [Ctrl]-[Fin] pour aller en bas et à droite de la feuille, vous atterrissez en D15 au lieu de B15, comme nous le voyons dans la copie d’écran ci-dessous. De pire en pire !


En guise de conclusion…

Excel nous offre ici un splendide bug, avec un gonflement tout à fait original et superflu de la taille du fichier. Ce problème persiste quand vous réalisez les insertions par macro au lieu de les réaliser manuellement.

Il ne vous reste alors que deux possibilités pour éviter ce problème : soit vous remplacez les insertions par des couper/coller, soit vous continuez comme précédemment en augmentant la « taille utilisée » d’une colonne à chaque opération.

Dans ce dernier cas, pour ramener votre modèle à sa taille véritable : supprimez toutes les colonnes superflues à droite, fermez le classeur en l’enregistrant, puis rouvrez-le. Le problème sera alors résolu et [Ctrl]-[Fin] vous amènera bien en B15 comme si rien ne s’était passé…





02 janvier 2014

Dernier graphe en ligne coloré

Une excellente année 2014 !

Tout d’abord, je tiens à vous souhaiter une excellente année 2014 !

Pour la part, elle a débuté avec une bonne nouvelle : Microsoft m’a une fois encore attribué le titre de « MVP Excel », en reconnaissance de mon action autour d’Excel, en particulier via ce blog.

-------------------------------------------------------------------------------------

Dans le dernier article, nous avons vu comment le Frankens team résolvait le problème de la création d’un graphe en ligne coloré.

De fait, ils ne cherchaient pas à faire exactement le même graphe que nous avec des segments en vert (hausse), bleu (stabilité) et rouge (décroissance).

Dans leur cas, le bleu était remplacé par du jaune pour indiquer une croissance faible ou une décroissance faible. Le seuil en absolu en dessous duquel on utilisait le jaune était identifié par la valeur en B2.

Nous avons aussi légèrement modifié la définition de « Zone » pour ne pas avoir un point en trop à droite : =decaler( Graphe!$A$2;;;nbval(decaler( Graphe!$A$2;;;100))-1)

Nous voyons l’effet de tout cela sur la copie d’écran ci-dessous.


Voici en prime une petite macro qui nous a permis de modifier les bornes de l’axe des ordonnées afin de représenter le graphe sur la plus grande surface possible.

Sub Axe_Y()
    ActiveSheet.ChartObjects("Graphique  1").Activate
    ActiveChart.Axes(xlValue).MinimumScale = Range("D2").Value - 1
    ActiveChart.Axes(xlValue).MaximumScale = Application.Max(Range("A:A")) + 1
End Sub