Monsieur Excel
Pour tout savoir faire sur Excel !

28 février 2015

Recherche sur >255 caractères

Les fonctions de recherche, aussi bien recherche() – et ses cousins rechercheH() et rechercheV() – que la fonction equiv() – sont tout à fait incapables de trouver un résultat quand la chaîne recherchée contient plus de 255 caractères.

Pour illustrer cela, nous avons mis en scène l’exemple suivant.


En A1, un texte contenant l’alphabet.
En A2, recopiée vers le bas, la formule =A1&$A$1.
En B1, recopiée vers le bas, la formule =nbcar(A1).

Formule de C1 : =equiv(A1;A:A;0)
Formule de D1 : =recherchev(A1;$A$1:$B$14;2;faux)

Manifestement, quand on copie ces deux formules vers le bas, on constate qu’elles ne fonctionnent plus quand le texte recherché comporte plus de 255 caractères !

Formule de E1, trouvée dans ExcelWays :
=sommeprod(($B$1:$B$14=B1)*ligne($B$1:$B$14))

Formule de F1, inspirée d’une formule proposée par Shane Devenshire :
=equiv(vrai;$A$1:$A$14=A1;0)
Cette dernière formule est matricielle, donc validée avec [Ctrl]-[Maj]-[Entrée].

Et voilà ! Vous avez désormais deux façons de faire des recherches sur des chaînes de plus de 255 caractères !

22 février 2015

Mes formations en mars 2015

Vous trouverez ci-dessous la liste des formations que je propose pour le mois de mars 2015. Chaque formation est limitée à huit participants.

·         Découverte de Visual Basic (2 jours) : lundi 23 et mardi 24 mars.
·         Création de tableau de bord sous Excel : le jeudi 26 mars.

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 « 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. Cette formation aura lieu dans les locaux de Logma, 12 rue d’Anjou, 78000 Versailles. A 600 m. de la gare Rive Gauche et à 1.500 m. de la gare des Chantiers.

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. Cette formation aura lieu dans les locaux de Finance 3.1, au 9 avenue de l’Opéra à Paris.

Toutes mes formations (cf. catalogue sur le site) 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 plus de 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.


16 février 2015

Cours de bourse (c)

Dans les deux articles précédents, nous avons vu comment représenter des cours de bourse sous la forme d’histogrammes. Aujourd’hui, nous voyons ce que se passe quand nous voulons représenter ces cours sous la forme de graphe en ligne. Pour les axes des X, nous utiliserons les étiquettes à trois niveaux présentées dans l’article précédent.

Dans le graphe n°1, nous utilisons les données originales, en fond jaune. Le graphe ne pose aucun problème. La seule difficulté d’interprétation éventuelle tient à ce qu’un lecteur un peu rapide peut ne pas remarquer que les jours de week-ends sont manquants.


Pour le graphe n°2, avec les données en fond bleu, nous avons inséré des lignes pour les jours des weekends et nous avons laissé en blanc les cellules correspondant aux cours de ces jours. Le résultat est un graphe où le cours tombe à 0 les jours de weekend, ce qui n’est pas très heureux !

Remarque – Si nous avions mis =na() pour les cours de weekend, ce n’aurait pas été mieux : Excel aurait alors interpolé les cours entre le vendredi et le lundi…

Le graphe n°3 est une reprise du graphe n°2 dans laquelle nous avons réalisé la séquence d’opérations suivante :

1 ) Commande « Sélectionner les données »
2 ) Bouton « Cellules masquées et cellules vides »
3 ) Bouton radio « Intervalles » pour « Afficher toute cellule vide... ».
4 ) Validation par « OK »

Maintenant, le graphe est parfaitement clair : on voit bien qu’il n’y a pas de cotation le weekend, et les valeurs de ces jours ne sont ni mises à 0 ni interpolées !


11 février 2015

Cours de bourse (b)

Nous reprenons l’exemple précédent, avec les cours de bourse représentés par un histogramme de facture tout à fait classique. Cet histogramme est celui du haut, dans la copie d’écran ci-dessous.

On trouve souvent de tels graphes dans les entreprises, avec des étiquettes de l’axe des X gênantes. Soit on les affiche à la verticale et elles sont difficiles à lire, soit on les met en oblique et ce n’est pas terrible non plus, soit enfin on les met à l’horizontale et elles prennent beaucoup de place…

Une solution efficace consiste à engendrer le résultat que nous pouvons observer dans le second graphe de la copie d’écran ci-dessous. L’axe des X comprend à présent trois parties : jour de la semaine, quantième et mois. Pour parvenir à cette fin, il a suffi de définir A2:C15 comme l’axe des X.


La colonne C comporte les mêmes dates que celles que nous avions en colonne A dans l’article prédécent, mais formatées au format « jjj ».

Les formules de A2 et B2 sont les mêmes : =C2. La seule différence tient au format, « mmm aaa » pour le premier et « j » pour le second.

La formule de A3, reproduite vers le bas, est : =si(mois(C3)<>mois(C2);C3;"")
La formule de C2 a tout simplement été reproduite vers le bas.

Remarque – Quand vous obtenez le graphe, il se peut que le jour de la semaine soit proposé en vertical par défaut. Pour le rétablir, faites un clic droit dans les étiquettes de l’axe des X, activez la commande « Mise en forme de l’axe », puis sélectionnez « Alignement » et demandez un alignement horizontal du texte.                 

05 février 2015

Cours de bourse (a)

Nous avons en colonne A une série de dates et, en colonne B, le cours de bourse correspondant. Ici, nous avons utilisé des cours récents du CAC40, décalés dans le temps pour l’intérêt pédagogique de l’exemple.

Quand nous sélectionnons A1:B14 et demandons  un histogramme, nous obtenons le graphe du haut. Que constatons-nous ? Certes, on voit bien qu’il y a des trous correspondants aux week-ends, périodes dans lesquelles il n’y a pas de cotation. Ceci dit, on pourrait souhaiter ne pas voir les jours de week-end apparaître,dans la mesure où – pour commencer ! – ils ne correspondent à aucune donnée dans la feuille de calcul.


Comment faire pour obtenir le résultat représenté dans le graphique du bas ? Ce n’est en fait pas très compliqué…

Faites un clic droit dans les étiquettes de l’axe des X et activez la commande « Mise en forme de l’axe », puis – dans le bloc « Type d’axe », prenez l’option « Texte sur les axes ». Et vous obtenez le résultat du second graphe !

Remarque – Cette commande – dont le nom est certes peu explicite ! – indique à Excel que nous souhaitons considérer les étiquettes de l’axe des X comme des textes et non des valeurs. Excel n’a alors plus aucune raison pour intercaler les dates manquantes correspondant aux jours de WE. Le résultat est un graphe plus lisible mais où, en revanche, on pourrait ne pas remarquer qu’il y a des jours manquants.


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)));"")