Monsieur Excel
Pour tout savoir faire sur Excel !

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.


28 août 2014

Utiliser une zone de critères (c)

Filtrer la liste sur place

Revenons à la première zone de critères, celle en A1:B3.

Pour opérer le filtrage sur place, il faut utiliser la commande « Avancé » du bloc « Trier et filtrer » de l’onglet « Données », comme on le voit dans la copie d’écran ci-dessous.


Remarque 1 – Notez que l’adresse du bloc « Zone de critères » identifie naturellement la feuille où elle se trouve, ce qui signifie que la zone de critères peut se trouver sur un autre onglet.

Copier vers un autre emplacement

Avant de tester la commande « Copier vers un autre emplacement », entrons en A38:F38 le texte « Important ».

Puis entrons « Nom », « Prénom » et « Age » dans le bloc B32:C32. Nous indiquons ainsi que ce sont uniquement ces trois champs qui nous intéressent pour l’extraction de la base.

Pour copier vers un autre emplacement, dans le dialogue, nous cochons à présent la seconde option et nous sélectionnons $C$32:$E$32 pour le bloc « Copier dans : ».

Remarque 2 – Notons au passage que les champs extraits – comme c’est ici le cas – peuvent être dans un ordre différent que celui qu’ils avaient dans la base.

Et nous obtenons le résultat ci-dessous ! Nous constatons que l’extraction s’est bien passée, sinon que les cellules C38:D38 ont été détruites ainsi que – pire encore ! – toutes les cellules placées au-dessous, et ce jusqu’à la ligne 1.048.576 !


Nous venons de détruire plus d’un million de lignes sur trois colonnes ! Immédiatement, nous essayons de rétablir la situation antérieure avec  [Ctrl]-z , mais nous constatons alors que cette annulation est inopérante. Imaginez ce qui peut arriver si vous n’aviez pas de double de la base, si vous aviez réglé Excel sur un enregistrement automatique toutes les 10 minutes, et que cet enregistrement survienne juste à ce moment !

Excel vient de faire une destruction irréparable sans nous avoir averti du danger !

Certes, on aurait pu choisir $C$32:$E$37 comme zone de réception, mais ce n’est pas naturel de devoir réserver à l’avance toute la surface de réception. Bien entendu, si la zone de réception est alors insuffisante, Excel vous prévient du danger, mais pourquoi ne le fait-il pas dans le premier cas ? Il serait facile pour lui de vérifier si la zone de réception est suffisante avant de tout casser…

Remarque 3 – Un autre problème, et non des moindres, est que la zone de réception doit obligatoirement se trouver sur le même onglet que la base, ce qui est contraire à tous les principes de sécurité que l’on doit normalement respecter dans la gestion de bases de données.

Pour cette raison, pour mes clients, j’utilise souvent dans ce cas une macro qui filtre sur place, sélectionne ce qui reste de la base, la copie, la colle sur l’onglet voulu et détruit enfin les colonnes superflues.


23 août 2014

Utiliser une zone de critères (b)

Les critères que nous avons présentés dans l’article précédent sont des critères normaux, avec le nom du champ concerné dans la première ligne et les valeurs acceptables en dessous.

Il existe une seconde catégorie de critères, les critères calculés. Un critère calculé possède deux particularités :

  • Le nom du champ, en première ligne, est différent de tous les noms des champs de la base ;
  • La condition exprimée, à partir de la seconde ligne, doit être formulée en fonction des valeurs associées à la première fiche de la base, c’est-à-dire aux données placées dans la deuxième ligne de la base.
En ce qui nous concerne, la formule exprimant la condition doit donc faire référence aux informations relatives à Adamopoulos.

Un premier critère calculé

En F1:F2, nous avons entré notre premier critère calculé, dont l’objectif est de réaliser la même sélection que la zone de critères A1:B3.

Nous notons tout d’abord que le nom du champ est bien différent de tous les noms de champs de la base : la première condition est donc bien satisfaite !

La formule de F2 est =ou(et(A6="m";D6=26);et(A6="f";D6=25)). Cette formule correspond bien à ce que nous recherchons, les garçons de 26 ans et les filles de 25 ans !

Nous constatons que la cellule F2 affiche FAUX. C’est tout à fait normal car Georges ne satisfait à aucune de nos deux conditions. Si l’on tapait 26 pour son âge, la cellule afficherait VRAI.

Il suffit d’utiliser la commande « Avancé », en indiquant cette fois-ci F1:F2 comme zone de critères, pour constater que nous obtenons bien le même résultat que celui obtenu avec la zone A1:B3.

Remarque 1 – Les deux zones de critères sont donc équivalentes. La plus ancienne est plus lisible mais moins compacte. Je conseille pour ma part de toujours utiliser des critères normaux quand cela est possible : avec Excel en entreprise, la lisibilité des modèles est souvent plus importante que la compacité.


Un second critère calculé

Supposons à présent que nous souhaitions sélectionner toutes les personnes dont le salaire est inférieur à 60 fois l’âge.

Ca cas est intéressant : En effet, si l’on ne connaît pas les critères calculés, la seule solution à ce problème consiste à ajouter une colonne dans la base, dans laquelle on calcule le résultat de ce test, puis à utiliser ce résultat comme condition dans la zone de critères.

Nous avons résolu ce problème avec la zone de critères en H1:H2. La formule de H2 est =E6<60 span="">.

Un troisième critère calculé

Si maintenant nous modifions la formule de H2 pour la remplacer par =E6>E7, quelles seront les personnes sélectionnées ? Essayez de répondre avant de lire la solution ci-dessous…

La réponse est que les gagnantes seront toutes les personnes gagnant plus que la personne qui les suit dans la base. Si l’on avait utilisé E$7 à la place de E7, ce seraient toutes les personnes gagnant plus qu’Alper Atamtürk…


18 août 2014

Utiliser une zone de critères (a)

De nombreux utilisateurs d’Excel, confrontés à une base de données, ne savent plus que créer – ou parfois même seulement utiliser – un TCD (tableau croisé dynamique). C’est bien malheureux car on peut faire plein de choses sur une base de données en utilisant les zones de critères, par exemple réduire la partie affichée de la base à ce qui nous intéresse, ou réaliser une extraction de la base.

Comprenons-,nous bien : quand Microsoft parle de « base de données » dans Excel, il s’agit d’un terme tout à fait abusif. Excel ne sait que gérer des tables. Une véritable base de données vous permet de mettre en relation plusieurs tables, ce qu’Excel n’est pas capable de faire. C’est d’ailleurs la raison pour laquelle Microsoft a essayé de remplacer le terme « base de données » par celui de liste, mais ce remplacement n’a pas été uniforme. Ne serait-ce que dans le nom des fonctions spécifiques qui commencent toutes par le « BD » de « base de données ».

Qu’est-ce qu’une zone de critères ?

Au minimum, une zone de critères est constituée de deux cellules superposées. Dans l’exemple ci-dessous, les blocs A1:A2, A1:A3, B1:B2, B1:B3, A1:B2 et A1:B3 représentent six zones de critères possibles.

Dans une zone de critères, comme dans une liste Excel, la première ligne est réservée aux noms des champs concernés. En-dessous, on trouve les conditions à appliquer à ces champs. La condition peut être formulée, comme dans notre exemple, par les valeurs possibles. Elle peut aussi contenir des expressions telles que « >25 » ou « <>25 ».

Remarque 1 – Attention cependant car, par exemple en A2, on peut entrer indifféremment m, M, ="m" ou ="M", mais "m""M"=m ou =M ne marcheront pas !


Quand une zone de critères contient plusieurs lignes ou plusieurs colonnes, chaque nouvelle colonne représente un « ET » et chaque nouvelle ligne un « OU ». Dans notre exemple, la zone de critères en A1:B3 signifie littéralement « ((Sexe = « M ») ET (Age = 26)) OU ((Sexe = « F ») ET (Age = 25)) ».

Le « OU » dans Excel est additif : A OU B signifie donc que soit A est VRAI, soit B est VRAI, soit A et B sont tous les deux VRAIs.

Que fait-on avec une zone de critères ?

La première utilisation d’une zone de critères consiste à réduire la partie affichée de la base à ce qui correspond aux critères demandé. Pour cela, plaçons le curseur n’importe où dans la base, appelons la commande « Avancé » du bloc « Trier et filtrer » de l’onglet « Données », identifions A1:B3 comme zone de critères et validons par « OK ».

Et nous obtenons aussitôt le résultat reproduit en bas de la copie d’écran. Il suffit d’utiliser ensuite la commande « Effacer » du bloc « Trier et filtrer » pour réafficher la base entière.

Remarque 2 – Sans zone de critères, vous ne pourriez pas atteindre ce résultat avec seulement des filtres. En effet, avec les filtres, vous pouvez sélectionnez les garçons de 26 ans, ou les filles de 25 ans, mais pas les deux à la fois.

Remarque 3 – Quand on filtre la liste sur place, cela remplace le filtre antérieur s’il y en avait un. Il n’est donc pas nécessaire de redéployer la base avant d’effectuer un nouveau filtrage si l’on a entre deux modifié le contenu de la zone de critères.

La seconde utilisation d’une zone de critères consiste à extraire une partie de la base dans une autre partie de la feuille, ce qui revient à créer une mini-base correspondant aux critères explicités dans la zone de critères, en se limitant aux champs choisis pour l’extraction. Nous verrons l’extraction dans un prochain article.