Monsieur Excel
Pour tout savoir faire sur Excel !

30 septembre 2014

La validation par liste

Une commande particulièrement utile d’Excel est la validation par liste, à laquelle on parvient par la commande « Validation des données » du bloc « Outils de données » du ruban « Données ».

Il suffit pour l’utiliser de choisir l’option « Liste » et d’indiquer la source. Dans notre exemple, nous avons ainsi défini la cellule C2 en indiquant la source A2:A1201.

Quand on sélectionne la cellule C2, on obtient alors une liste déroulante dans laquelle on peut sélectionner l’option voulue. Cette liste (cf. copie d’écran ci-dessous) affiche huit valeurs et – s’il y en a d'autres qui ne sont pas encore visibles – un ascenseur.


Limite de la liste déroulante

Jusqu’à la version 2007 incluse (à moins que ce ne soit 2003), il y avait un bug qui limitait la liste aux 1.000 premières valeurs de la liste. Le même bug apparaissait dans les listes déroulantes des tables et des bases de données.

Ce bug a heureusement été résolu depuis, et cette limite a enfin disparu.

Où sont les listes déroulantes ?

Il est parfois utile de retrouver dans une feuille de calcul, ou à l’intérieur d’une sélection, quelles sont les cellules comportant une liste déroulante.

Pour cela, utilisez le raccourci  [Ctrl]-t qui lance la commande « Atteindre », activez la commande « Cellules… », cochez l’option « Validation des données » et validez. Vous avez même la possibilité de ne sélectionner que les cellules dont la validation des données est identique, ce qui peut se révéler bien pratique.

Une liste déroulante dynamique

Supposons qu’une liste déroulante affiche tous les pays dans lesquels se trouvent vos clients. Vous souhaitez bien évidemment pouvoir l’actualiser quand un nouveau client vient d’un pays qui n’est pas encore dans votre liste.

Créons une liste de pays par exemple à partir de la cellule E1.

Définissons ensuite le nom dynamique « Pays » avec la formule : =decaler(Noms!$E$1;;;NBVAL(Noms!$E:$E))

Nous pouvons vérifier que ce nom correspond bien à la liste des pays en utilisant [Ctrl]-t et le nom « Pays » : nous constatons que la liste des pays est bien atteinte…

Il ne reste plus qu’à définir la zone de validation par liste avec « =pays » pour obtenir une liste déroulante dynamique des pays. Ajoutez un pays puis déroulez la liste : vous constaterez qu’il a été aussitôt intégré à la liste :)



24 septembre 2014

Nombre de modalités - Autre

Nous avons déjà publié il y a quelques jours un article intitulé « Calcul du nombre de modalités » où nous montrions comment calculer le nombre de valeurs différentes dans une colonne.

Cette fonction est en effet une fonction qui manque cruellement à la série des fonction BD…().

Aujourd’hui, nous rouvrons le sujet pour vous montrer deux nouvelles façons de définir cette fonction.

Formule de D1 (cf. article précédent) :
=somme(si(equiv(A2:A15;A:A;0)=ligne(A2:A15);1;0))
Formule de D2 (cf. article précédent) :
=somme(1*(equiv(A2:A15;A:A;0)=ligne(A2:A15)))
Formule de D3 : =sommeprod(1/nb.si(A2:A15;A2:A15))

Les deux premières formules sont matricielles, donc validées avec [Ctrl]-[Maj]-[Entrée].


La première formule est assez compréhensible : quand le numéro de la ligne est le numéro de la première ligne où l’on trouve la valeur considérée, c’est la première occurrence de cette valeur et cela compte donc pour 1.

La seconde formule montre que l’on peut très bien remplacer le « si », dans une telle situation, par un simple produit par « 1 », qui a la vertu de transformer les « VRAI » en « 1 ».

La troisième formule présente le double avantage d’être plus compacte et de ne pas être une formule matricielle. Nous y notons une particularité : le second argument, au lieu d’être un élément unique, comme c’est normalement prévu dans le mode d’emploi de la fonction, est en fait un vecteur.

Quand nous évaluons la partie nb.si(A2:A15;A2:A15) de la formule, nous obtenons le vecteur {2;1;4;3;1;2;4;1;4;1;1;4;3;3}. On va donc compter 1/ 2 pour chaque élément apparaissant deux fois, 1/3 pour chaque élément apparaissant trois fois,… Au total, nous aurons donc le nombre de modalités.

Remarque 1 – Ce n’est pas la première fois que nous découvrons des ressources inespérées d’Excel en « détournant » le mode d’emploi officiel d’une fonction…

Remarque 2 – Il y a enfin, assez logiquement, une formule encore plus courte, mais de nouveau matricielle, avec =somme(1/nb.si(A2:A15;A2:A15)). Ce n'est pas surprenant car, comme nous l'avons déjà expliqué à plusieurs reprises, la fonction sommeprod() sert souvent à « éviter » une formule matricielle puisqu'elle génère naturellement une boucle...



18 septembre 2014

Calcul du nombre de modalités

Les fonctions de base de données, les fonctions BD…() d’Excel, sont nombreuses. On peut ainsi calculer le max, le min, la moyenne, l’écart-type, la variance... des fiches d’une base qui correspondent à des critères spécifiques. Il en manque cependant une, celle qui calculerait le nombre de « modalités » d’une colonne, c’est-à-dire le nombre d’éléments différents qu’elle contient.

Nous vous proposons deux solutions, toutes les deux sous la forme de formules matricielles, donc validées avec la combinaison [Ctrl]-[Maj]-[Entrée].


La formule entrée en D1 est la suivante :
=somme(si(equiv(A2:A14;A:A;0)=ligne(A2:A14);1;0))

Le principe en est en fait assez simple : les valeurs originales sont celles dont le numéro de ligne est celui de la première ligne où l’on trouve cette valeur dans la colonne.

Remarque – La formule précédente peut être simplifiée en ôtant le si(). En effet, quand on multiplie VRAI par 1, on obtient le résultat 1.

Voici donc la formule simplifiée, entrée dans la cellule D2 :
= somme(1*(equiv(A2:A14;A:A;0)=ligne(A2:A14)))

13 septembre 2014

Formatage astucieux de la table

Avec la table que nous avons construite dans l’article précédent : si l’on permute les deux champs en B35 et B34, le salaire moyen est affiché en décimal, et l’âge moyen en € ! Ce n’est pas d’un très bel effet, comme nous le voyons ci-dessous…


En fait, il y a une parade si l’on tient compte du fait que – selon toute vraisemblance – les âges des personnes dans la base seront de moins de 100 ans, et les salaires de plus de 100 €.

Remarque 1 – Si vous envisagez d’embaucher des centenaires, il vous suffira de remplacer dans ce qui suit 100 par 150...

La solution passe par l’utilisation de formats personnalisés. Si vous ne savez pas comment fonctionnent ces formats, je vous conseille de lire l’article « Les formats personnalisés d'Excel » du 20 octobre 2005. Cet article est important, ce qui explique pourquoi il a été écrit dans les trois premiers mois de ce blog.

Le format qui nous intéresse ici est le second type de format personnalisé :
[condition 1]format1;[condition 2]format2;format3

La solution ici est donc d’utiliser le format : [>100]# ##0,00 €;0,00

Comme nous le montre la seconde partie de la copie d’écran ci-dessus, notre problème est résolu de façon tout à fait esthétique : les moyennes inférieures à 100 sont affichées en nombre à deux décimales, et les autres en euros (aussi avec deux décimales).

Remarque 2 – Avec un format de ce type, on peut par exemple afficher une valeur en nombre à deux décimales si elle est supérieure à 1 en valeur absolue, et en pourcentage dans le cas contraire...



08 septembre 2014

Les fonctions de base de données

Quand vous cliquez sur le symbole fx de la barre de formule, vous accédez à la liste des fonctions d’Excel.  Vous pouvez alors sélectionner une famille de fonctions . Dans la famille « Bases de données », il y a douze fonctions : BDecartype, BDecartypeP, BDlire, BDmax, BDmin, BDmoyenne, BDnb, BDproduit, BDsomme, BDvar et BDvarP.

Cete famille de fonctions possède une particularité partagée par aucune autre famille de fonctions : toutes les fonctions de la famille ont exactement la même syntaxe :

=BDfonction(base de données;champ;critères)

Remarque 1 – Une particularité du second argument, le champ, est que celui-ci peut être identifié par son nom ou par sa position dans la liste des champs.

Remarque 2 – Quand le nom de la fonction se termine par un « P », on indique par là que l’échantillon utilisé représente la totalité de la population. Pour les spécialistes, cela signifie que la variance est calculée en divisant par n au lieu de n-1.

Le tableau ci-dessous, dans les lignes 32:35, montre l’utilisation d’une de ces fonctions pour calculer l’âge et le salaire moyen par sexe.


Tout cela s’obtient avec la formule suivante, entrée en C34 et reproduite dans le reste du tableau : =BDmoyenne($A$5:$F$30;$B34;C$32:C$33). Notez bien l’utilisation des « $ » : nous avons tout bloqué pour la base, la colonne seulement pour le champ et les lignes seulement pour les critères.

Remarque 3 – Contrairement à ce qui se passe dans un TCD (tableau croisé dynamique), les résultats de ce tableau s’actualisent automatiquement.

Si donc nous entrons « 4 » en B35, nous aurons deux lignes avec l’âge moyen. Mais la seconde sera formulée en euros :(



02 septembre 2014

Notes sur le filtrage et l’extraction

Dans les trois articles précédents, nous avons vu en détail comment fonctionnaient les zones de critères, le filtrage sur place et l’extraction dans Excel. Voici quelques compléments et remarques d’intérêt général sur ces deux opérations.

Utilisation des jokers

Dans lez zones de critères, vous pouvez utiliser les deux jokers : « ? » pour un caractère et « * » pour un ensemble de caractères.

Ainsi, en entrant « ????? » dans la zone « Prénom », vous ne garderez que les prénoms composés d’exactement cinq caractères. Nous aurions aussi pu utiliser à cet effet un critère personnalisé utilisant la formule =nbcar(B6)=5, à condition alors de ne pas oublier de donner à la cellule au-dessus un nom différent de tous les noms des champs de la base..

Avec « Dupon? », vous garderez bien « Dupont » et « Dupond », mais ni « Dupontier », ni « du Pont ».

Recherche d’un joker

On peut alors se poser la question suivante : qu’arrive-t-il si je cherche dans une colonne un point d’interrogation ou un astérisque ?

Heureusement pour vous, Excel a même prévu ce cas de figure ! Il suffit de faire précéder le joker du tilde – le « ~ » espagnol – pour que ce joker soit pris à la lettre. Ainsi, pour identifier toute cellule dans laquelle un astérisque est présent, il suffit d’utiliser « *~** », ce qui signifie « n’importe quoi, suivi d’un astérisque, et encore suivi de n’importe quoi ! ».

L’extraction destructive

Dans le dernier article, nous avons vu comment l’extraction détruit tout ce qui se trouve en dessous de la zone d’extraction. En ce qui me concerne, à part la solution par macro que je vous ai décrite, j’utilise aussi volontiers la solution qui consiste à extraire sans précaution quand je sais tout simplement qu’il n’y a rien au dessous.

Extraction sans doublon

L’extraction sans doublon, étrangement, est disponible quoi que l’on fasse : filtrage sur place ou extraction. Pour ma part, je trouve cela un peu ridicule. Quand on effectue un filtrage sur place, on utilise tous les champs de la base : il ne peut pas y avoir deux lignes totalement identiques, sauf si vous gérez mal votre base. Il me semblerait plus intelligent de n’activer cette commande que lorsqu’une extraction est demandée.

D’autant que la commande « Supprimer les doublons » du bloc « Outils des données » permet depuis Excel 2007 de résoudre le cas où, par fusion de deux bases, on aurait effectivement des lignes identiques.

Liste des pays utilisés

L’extraction sans doublon est très pratique pour obtenir rapidement la liste des modalités d’une colonne. Supposons par exemple que nous souhaitions obtenir la liste des pays utilisés dans la colonne F. Il suffit pour cela d’entrer « Pays » en H5 et d’y extraire la liste des pays : sans zone de critères et en excluant les doublons.