Monsieur Excel
Pour tout savoir faire sur Excel !

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.


13 août 2014

Articles du blog : date et heure

Nous venons de clore un série d’articles sur les fonctions de date et d’heure d’Excel. Si vous souhaitez compléter ce sujet et – au passage – récupérer une ou deux macros intéressantes, voici une liste d’articles publiés dans le blog sur ce thème…

Le dernier jour du mois
Date : 27 décembre 2005

Nombre de jours ouvrés entre deux dates 
Date : 1er août 2007

Création d’une séquence de jours ouvrés 
Date : 5 août 2007

Une macro pour éliminer les jours fériés 
Date : 9 août 2007

Arrondi au quart d’heure supérieur 
Date : 04 novembre 2007

Une formule pour jours ouvrables
Date : 7 mars 2008

Jours ouvrables : formule simplifiée
Date : 11 mars 2008

Calcul de la date et de l’heure d’après du texte
Date : 08 juillet 2008

Insertion automatique de l’heure « minuit »
Date : 15 février 2009

Récupérez l’heure et la date au pied levé
Date : 1er avril 2011

Un bug dans la mise à jour de dates
Date : 27 juillet 2013

08 août 2014

La fête du travail aux USA

Pour mettre en pratique les fonctions de date et heure, voici un petit exemple pratique, le calcul de la date de la Fête du Travail aux USA, leur 1er mai. Travailleurs comme ils le sont, les américains sont même obligés de travailler pour calculer cette date, qui tombe en effet le premier lundi de septembre.

Voici la formule que j’ai trouvée sur un site américain. Je ne vous dirai pas lequel, par délicatesse :

=date(A1;9;1)+si(joursem(date(A1;9;1);2)>1;7-joursem(date(A1;9;1);2)+1;1-joursem(date(A1;9;1);2))

Elle fonctionne parfaitement bien, mais je la trouvais franchement lourde…

 






Voici donc ma solution :
=date(A1;9;1)+mod(9-joursem(date(A1;9;1));7)

Dès que l’on travaille avec des dates et des jours de semaine, la fonction mod() se révèle très utile.

La formule =mod(a;b) vous donne le reste de la division de a par b.




03 août 2014

Fonctions date & heure d’Excel (c)

Suite aux deux articles précédents, et pour clore le sujet des fonctions de date et heure dans Excel, voici enfin trois fonctions qui ont été ajoutées à Excel par la suite. Ces fonctions n’étaient pas présentes dans l’Utilitaire d’analyse.

Ce sont les fonctions mois.decaler(), nb.jours.ouvres.intl() et serie.jour. ouvre.intl().


Voici la différence entre les syntaxes des deux fonctions calculant le nombre de jours ouvrés :

NB.JOURS.OUVRES(date_début;date_fin[;jours_fériés])
NB.JOURS.OUVRES.INTL(date_début;date_fin[;weekend[;jours_fériés]])

Dans la version internationale, le troisième argument indique quels jours de la semaine forment le « week-end » et le dernier argument indique les jours fériés éventuels.

Avec la formule =NB.JOURS.OUVRES.INTL(A27;A26;1;{"14/7/2014";"15/7/2014"}), on indiquerait à Excel que le week-end est formé par le samedi et le dimanche, et que le 14 et le 15 juillet sont fériés.

La formule mois.decaler(A26;-1) correspond en fait à =date(année(A26); mois(A26)-1; jour(A26)). Est-ce que cela valait le coup de créer une nouvelle fonction rien que pour cela ?

28 juillet 2014

Fonctions date & heure d’Excel (b)

Dans le dernier article, nous avons passé en revue les fonctions historiques de date et heure d’Excel. Aujourd’hui, nous nous intéressons aux fonctions de date et heure ajoutées par l’Utilitaire d’analyse

Le tableau ci-dessous illustre leur utilisation :


Leur syntaxe est la suivante :
=fin.mois(date_départ;mois)
=fraction.annee(date_début;date_fin[;base])
=nb.jours.ouvres(date_départ;date_fin[;jours_fériés])
=no.semaine(numéro_de_série[;méthode])
=serie.jour.ouvre(date_départ;nb_jours[;jours_fériés])

Nous avons vu dans le dernier article comment fonctionnait l’argument facultatif jours_fériés

Remarque 1 – La syntaxe dans l’aide d’Excel pour ces fonctions est aussi foireuse que d'habitude. Ainsi, pour la troisième fonction, elle nous donne =NB.JOURS.OUVRES(date_début, date_fin, [jours_fériés]) : des "," au lieu des ";" et un crochet mal placé !

Remarque 2 – L’argument facultatif base fonctionne de la façon suivante : rien ou 0 pour le comptage 30/360 US, 1 pour le comptage réel, 2 pour le réel/360, 3 pour le réel/365 et 4 pour le 30/360 européen.