Monsieur Excel
Pour tout savoir faire sur Excel !

29 octobre 2016

Trouver un mot depuis une liste

Le problème que nous avons aujourd’hui est assez particulier. Nous avons en colonne A une liste de textes et, en colonne E, une liste de mots recherchés. Nous voulons indiquer en colone B s’il est vrai que le texte à gauche contient au moins un mot de la liste et, en colonne C, quel est le premier mot de la liste trouvé dans ce texte.

Remarque 1 – La liste actuelle ne contient que des mots isolés, mais rien n’empêche d’y mettre des groupes de mots ou même des phrases entières.


La formule de B2 est :
=sommeprod(1*estnum(cherche(Couleur;A2)))>0

Dans cette formule :
cherche(Couleur;A2) donne {7;#VALEUR!;#VALEUR!;#VALEUR!;#VALEUR!}
estnum(cherche(Couleur;A2)) donne {VRAI;FAUX;FAUX;FAUX;FAUX}

La formule de C2 est matricielle, donc validée avec [Ctrl]-[Maj]-[Entrée].
La voici : =sierreur(index(Couleur;equiv(vrai;estnum(cherche(Couleur;A2));0));"")

En A6, deux couleurs de la liste sont présentes, mais c’est bien celle placée la plus haut dans la liste qui apparaît en C6.

Remarque 2 – Une fois de plus, nous constatons que nous parvenons à des résultats surprenants en utilisant des formules mettant en œuvre des vecteurs ! Il fallait penser à chercher un vecteur entier dans une cellule…

23 octobre 2016

Le ruban d’un add-in disparaît !

Vous avez peut-être découvert que le ruban d’un de vos add-ins, on dit compléments en français, a disparu depuis une mise à jour d’Excel en juillet dernier.

C’est un problème que vous pouvez avoir avec les versions 2010, 2013 et 2016 d’Excel. L’add-in, quand vous l’installez, se charge normalement. Mais Excel bloque, soit-disant pour des raisons de sécurité, des fichiers en provenance d’ailleurs. Quand on relance l’add-in, Excel bloque le chargement de ces fichiers.

Il y a deux façons de résoudre ce problème, une solution à long terme que je présenterai en premier car elle est à long terme, et une solution à court terme.

Solution à long terme

Il faut mettre le classeur de l’add-in dans le
C:\Utilisateurs\nom d’utilisateur\AppData\Roaming\Microsoft\AddIns\

Ou, avec le Mac :
 %appdata%\Microsoft\AddIns\

Tout add-in placé dans ce dossier apparaît dans le dialogue d’activation des add-ins sans que l’on ait besoin de l’installer. En outre, ce dossier peut être transformé en « Trusted Location » en suivant les instructions détaillées par mon collègue MVP Jon Peltier à l’adresse suivante :

C’est lui qui a identifié ce problème et publié l’article que j’ai traduit ici partiellement.

Solution à court terme

La solution ci-dessous vous permet d’installer l’add-in depuis n’importe quel dossier. Mais, si vous recevez un classeur de mise à jour, vous devrez le débloquer avant de l’ouvrir.
  • Trouvez l’add-in par l’Explorateur de fichiers.
  • Faites un clic droit sur le nom du fichier et sélectionnez les propriétés.
  • Si un message du type « Le fichier vient d’un autre ordinateur et peut être bloqué pour protéger cer ordinateur » apparaît, cliquez dans le bouton pour débloquer le fichier.
  • Validez par OK et redémarrez Excel.


18 octobre 2016

La somme des deux derniers

Une des dernières colles posées à la sagacité des MVP Excel était la suivante. Vous avez en colonne A des noms et en colonne B des montants. Vous désirez récupérer en colonne G, pour chaque personne, la somme des deux derniers montants qui lui sont associés.

La solution proposée ci-dessous est le résultat conjugué des contributions de Craig Hatmaker, Peter Bartholomew et Crispo Mwangi, avec un petit coup de main de votre serviteur.


Nous avons mis en colonne C la position de la ligne pour chacun des trois héros, avec la formule suivante en C2, recopiée ensuite vers le bas : =nb.si($A$2:A2;A2).

La formule la plus compliquée est celle de D2, recopiée elle aussi vers le bas. Cette formule doit indiquer le montant placé en colonne B si ce montant est un des deux derniers montants listés pour la personne dont le nom est en colonne A. La voici :

=si(ou(grande.valeur(($C$2:$C$14)*($A$2:$A$14=A2);{1;2})=C2);B2;"")

Cette formule est matricielle, donc validée avec [Ctrl]-[Maj]-[Entrée].

Son principe est le suivant : on récupère le montant en B2 si, pour les lignes où l’on a la même personne, la position est l’une des deux les plus grandes ; sinon, on ne met rien…

Quand on est en D7, la formule ($C$2:$C$14)*($A$2:$A$14=A7) s’évalue en {0;1;0;2;0;3;0;4;0;0;0;0;0}, c’est-à-dire la liste des positions pour Paul.

Pour terminer, la formule de G2, recopiée elle aussi vers le bas, est toute simple :

=somme.si(A:A;F2;D:D)

12 octobre 2016

Noms uniques et multiples

Reprenons l’exemple de notre dernier article. Nous vous avions montré comment calculer le nombre de modalités dans un vecteur, c’est-à-dire le nombre de noms différents.

Aujourd’hui, nous calculons le nombre de noms uniques, c’est-à-dire n’apparaissant qu’une fois dans la liste, et de noms multiples, c’est-à-dire apparaissant deux fois ou plus dans la liste.

Comme la fois précédentes, nous avons reproduit en colonne E le contenu des formules utilisées dans la colonne D.


Nombre de noms uniques

Il n’y a qu’une petite différence entre les formules de D8 et D9 : on peut multiplier au départ par 1* ou par --, ce qui revient exactementy au même et transforme à chaque fois une série de valeurs VRAI ou FAUX en 1 (pour VRAI) et 0 (pour Faux). On aurait donc pu aussi utiliser dans les formules de D2 à D4…
  
Nombre de noms multiples

La formule de D12 utilise, à l’instar de celles de D1 et D2, la fonction frequence().

equiv(A2:A14;A2:A14;0)  donne {1;2;3;4;5;1;3;8;3;10;11;1;4}
frequence(equiv(…);equiv(…)) a pour résultat {3;1;3;2;1;0;0;1;0;1;1;0;0;0}

Il y a trois cas où l’on obtient un nombre supérieur à 1, donc trois noms multiples.

La formule de D13 se contente de reprendre les formules du nombre de modalités et du nombre de noms uniques pour obtenir par soustraction le noms multiples.

06 octobre 2016

Calcul du nombre de modalités

Remarque – Même si le calcul du nombre de modalités ne présente pas pour vous un intérêt particulier, vous pourrez lire cet article avec intérêt si vous vous intéressez aux formules matricielles ou à l’une des fonctions suivantes : SommeProd, Equiv, Fréquence ou Nb.Si.

Excel propose 12 fonctions de bases de données nommées BD…(). Il manque la 13ème. Microsoft serait-elle donc une société superstitieuse ? En effet, une fonction très utile dans cette famille serait la fonction BdNbMod() qui compterait le nombre de modalités (c’est-à-dire de valeurs différentes) dans une colonne donnée.

Pour combler cette lacune, je vous propose dans cet article 6 façons différentes de combler cette lacune, avec 3 paires de solutions, chaque paire étant constituée par une formule matricielle, donc validée avec [Ctrl]-[Maj]-[Entrée], et une formule un chouïa plus longue, mais non matricielle.


La fonction Frequence()

Notre première solution (D1 et D2) utilise la fonction frequence(), une fonction maîtrisée par peu d’utilisateurs Excel.

La formule equiv(A2:A14;A2:A14;0) donne {1;2;3;4;5;1;3;8;3;10;11;1;4}, c’est-à-dire la place de la première occurrence de la valeur courante dans le vecteur.

La formule FREQUENCE(EQUIV(A2:A14;A2:A14;0);EQUIV(A2:A14;A2:A14;0))>0 nous donne ici le résultat {VRAI;VRAI;VRAI;VRAI;VRAI;FAUX;FAUX;VRAI;FAUX;
VRAI;VRAI;FAUX;FAUX;FAUX}, avec VRAI chaque fois que l’occurrence est la première. On calcule donc le nombre de fois où l’occurrence est la première, ce qui est le nombre d’occurrences différentes.

Pour cet exemple comme pour les deux suivants (D3:D4) et (D5:D6), la seconde formule utilise la fonction SommeProd() pour éviter d’avoir à saisir la formule comme matricielle.

La fonction Equiv()

Là, c’est tout simple, si vous savez comment fonctionne la fonction equiv(… ;0)…

Nous comptons tout simplement le nombre de fois où la position de la première occurrence de la valeur actuelle est bien celle de la ligne actuelle.

Nous avons vu ci-dessus comment la fonction ()Equiv() était évaluée. Le reste ne devrait pas vous poser de problème.

La fonction Nb.Si()

Cette troisième solution est la plus compacte et, comme la solution précédente, elle aussi très astucieuse.

La formule NB.SI(A2:A14;A2:A14) s’évalue {3;1;3;2;1;3;3;1;3;1;1;3;2}. En divisant 1 par ce vecteur, on obtient le vecteur {0,333;1;0,333;0,5;1;0,333;0,333;1;0,333;1;1;0,333;0,5}. Ce qui fait donc un total de 1 pour chaque modalité différente !

En guise de conclusion

La richesse d’Excel transparaît bien dans l’exercice de ce jour. Non seulement nous avons 6 formules différentes pour résoudre notre problème – et l’on pourrait aisément en trouver d’autres encore ! – mais nous avons aussi le choix entre des formules naturelles et des formules matricielles.


Ces dernières sont plus compactes mais un peu plus dangereuses : si un utilisateur les modifie puis les revalide en oubliant de les valider en formule matricielle, il cassera ce joli jouet !

01 octobre 2016

L’énigme des pèlerins (c)

Remarque 1  Désolé pour le retard. J'ai eu des ennuis avec le site et ai même perdu les deux derniers articles durant plusieurs jours, avant de parvenir à les récupérer. Voici donc la suite...

En fait, en cherchant un peu, on peut trouver une des solutions correspondant au cas où il y a 27 pèlerins. Voici une solution possible :

Reste à voir si l’on peut effectivement accommoder 3 pèlerins de plus, pour un total de 30 pèlerins.

Une autre question qui était posée au départ était : « Quel est le plus grand nombre de pèlerins que l’on peut loger en respectant la totalité des contraintes énoncées ? »

Nous allons répondre à ces deux questions dans cet article.

Le plus grand nombre de pèlerins

Pour trouver le plus grand nombre de pèlerins logeables avec toutes les contraintes, rien de tel que d’utiliser le solveur d’Excel. Voici comment nous l’avons paramétré pour résoudre le problème :


Quand on lance l’optimisation, Excel trouve immédiatement la réponse, soit un total de 30 pèlerins, ce qui est justement le nombre actuel de pèlerins plus les 3 arrivés sans préavis.

Nous répondons donc aux deux questions d’un coup : d’une part, nous avons trouvé le moyen de loger les 3 pèlerins supplémentaires, d’autre part nous avons identifié l’effectif maximal de pèlerins que nous pouvons loger en respectant toutes les contraintes !

Nous comprenons à présent pourquoi nous n’avons pas utilisé la somme (cf. formules dans l’article précédent) pour les lignes 8 et 14 et pour la colonne D. Cela nous gagne de la simplicité et du temps dans la formulation du problème : nous pouvons en effet écrire les contraintes de C à E, de la ligne 7 à la ligne 9, et de la ligne 13  à la ligne 15, sans être « pollués » par les cellules du milieu, là où se trouve l’escalier !

Remarque 2 – C’est pour cela que j’ai mis un fond noir à la cellule de l’escalier, ce qui cache la valeur de la cellule découlant de l’optimisation…