All posts in Excel

Les filtres automatiques Excel

Les filtres automatiques sont la colonne vertébrale de l’utilisation des feuilles Excel de données. Ils permettent d’interroger efficacement une grande quantité d’informations présentées sur une feuille de données.

Exemple, il est possible en quelques clics d’isoler les clients de notre exemple Figure 29 : résultat tri respectant les critères suivants :

  • homme,
  • né avant le 01/01/1960,
  • habitant à Paris.

 

POSITIONNER LES FILTRES AUTOMATIQUES SUR UNE FEUILLE DE DONNEES

Une feuille de données doit impérativement contenir les noms des colonnes en première ligne (si possible avec une mise en forme distincte des lignes de données, pour améliorer l’ergonomie visuelle du document). Pour positionner les filtres automatiques, il suffit de sélectionner les cellules de la première ligne sur lesquelles on veut pouvoir faire une sélection (ou la première ligne entière, les filtres seront alors positionnés sur toutes les colonnes renseignées).

Le filtre automatique est accessible de deux manières : par le menu « Accueil » du ruban et par le menu « Données ».

 

 

Lorsqu’un filtre automatique est activé, il est reconnaissable de deux manières dans l’affichage des données de la feuille :

  • le triangle de filtre devient un petit triangle couplé à un entonnoir ,
  • seules les lignes correspondantes au critère du filtre sont affichées. Leur numéro de ligne est bleu.

 

 

CHOIX POSSIBLES DE LA FENETRE DE FILTRE AUTOMATIQUE

  • Trier de A à Z

    Cette action permet de trier la colonne, dans le sens croissant.

  • Trier de Z à A

    Cette action permet de trier la colonne, dans le sens décroissant.

  • Trier par couleur

    Cette action permet de trier la colonne en affichant en premier la couleur sélectionnée.

  • Filtrer par couleur

    Cette action permet de filtrer les colonnes par couleurs. Seules les colonnes de la couleur sélectionnées sont affichées, comme dans l’exemple ci-dessous.

  • Filtrer par liste de données

    Par défaut, la case à cocher « (Sélectionner tout) » est cochée, ainsi que toutes les valeurs disponibles trouvées dans la colonne (dans la limite de 10.000).

    Si on veut afficher toutes les valeurs sauf une ou plusieurs valeurs, on décoche la ou les valeurs à enlever.

    Si on ne veut afficher qu’une ou quelques valeurs, on décoche tout d’abord la case à cocher « (Sélectionner tout) » pour décocher l’ensemble des valeurs, puis on sélectionne la ou les valeurs à afficher.

 

UTILISER LES FILTRES TEXTUELS

Les filtres textuels sont les plus puissants. Ils permettent d’établir des critères « approchés » et ainsi d’interroger finement une grande quantité de données.

Dans le cas des filtres textuels, l’interface d’Excel propose la fenêtre suivante (avec la première recherche positionnée selon le choix utilisateur). Il est possible de positionner deux critères seulement.

Un clic sur la liste déroulante affiche toutes les valeurs possibles de la colonne. Il est également possible d’entrer manuellement des valeurs, notamment pour les règles de type « commence par ».

Les règles de sélection possibles sont les suivantes :

  • est égal à,
  • est différent de,
  • est supérieur à,
  • est supérieur ou égal à,
  • est inférieur à,
  • est inférieur ou égal à,
  • commence par,
  • ne commence pas par,
  • se termine par,
  • ne se termine pas par,
  • contient,
  • ne contient pas.

 

Dans notre feuille de données exemple, un moyen sûr d’afficher tous nos clients Parisiens serait de positionner le filtre suivant :

 

COMBINER LES FILTRES SUR PLUSIEURS COLONNES

La combinaison de filtres nous permet d’isoler les lignes correspondantes aux critères suivants :

  • homme,
  • né avant le 01/01/1960,
  • habitant à Paris.

 

Il suffit pour cela de positionner les filtres adéquats sur chaque colonne concernée.

La combinaison de filtres sur plusieurs colonnes est une méthode puissante d’interrogation de feuilles de données Excel. C’est quelque peu l’équivalent du langage SQL en base de données.

SUPPRIMER TOUS LES CRITERES DE FILTRE AUTOMATIQUE

Sur certaines feuilles Excel, une multitude de critères peuvent être appliqués sur autant de colonnes. Il peut alors être fastidieux de supprimer tous les critères un par un. Pour supprimer tous les critères choisis en une seule action, il faut cliquer sur le bouton « Effacer » du ruban, menu « Données », groupe « Trier et filtrer » :

LES LIMITES DES FILTRES AUTOMATIQUES

Attention, les filtres automatiques fonctionnent correctement avec des feuilles de données. Mais ils posent problèmes avec des feuilles contenant des cellules fusionnées.

Si on filtre sur agence1 nous obtenons le résultat erroné suivant :

Enfin, les données doivent toutes être renseignées (dans le cas où l’on ne fusionne pas de cellule), il ne doit pas subsister de blancs signifiant « répétition de la valeur », comme sur l’exemple ci-dessous. En effet, un filtre positionné sur ces valeurs ne sélectionne pas la valeur à blanc !

Top

Calculer les temps et les durées dans Excel

Une question qui revient souvent :

« J’ai procédé à un calcul, et je trouve 6,5 minutes. Je veux tout bêtement que ça m’affiche 6 minutes et 30 secondes, ou 00:06:30, mais il n’y a rien qui marche, c’est toujours pareil avec Excel !! ».

C’est assez normal de penser cela, car l’exercice est beaucoup moins trivial qu’il n’y paraît. Il faut avant tout savoir que ce n’est pas Excel qui est en cause, le problème est commun à tous les systèmes informatiques, bases de données et langages ! La gestion du temps est toujours un problème !

Pourquoi ? Parce que le temps est stocké d’une façon (6,5) complètement différente de l’affichage (6 minutes et 30 secondes). Et ça c’est commun à tous les langages, toutes les bases de données…

Dans quasiment tous les systèmes informatiques langages bases de données, Excel compris, l’unité de base est le jour. Aucun système informatique ne compte le temps avec des heures et des minutes et des secondes ! Le temps est stocké en numérique, en décimal :

1 = 1 jour

1/24 = 1heure

1/24/60 = 1 minute

Donc, si nous avons calculé un temps en fractionnel et obtenu 6,5 minutes, il faut le convertir en temps « universel », autrement dit en jour !

Effectuons dans une cellule le calcul suivant :

= 6,5 x (1/24/60)

Nous obtiendrons comme résultat :

0,00451388888888889

Et mettons ce résultat au format heure. Oh, miracle, nous obtenons le temps affiché correctement !

calcul-temps-durées-excel

Pour résumer, oui, la gestion du temps sous Excel c’est compliqué, mais c’est commun à tous les systèmes informatique, la faute aux grégoriens ou autres romains qui ont fractionné le temps ainsi (1 jour = 24h, 1h = 60 min etc.)

Top

Le tri Excel

Une feuille Excel contenant des données peut être triée. Le tri est une notion essentielle d’une feuille de données. Toutefois, les données doivent être présentées correctement afin d’utiliser efficacement le tri : la première ligne doit contenir les entêtes de données.

Le tri est disponible dans deux menus du ruban : le menu « Accueil » et le menu « Données ».

Trois options de tris sont disponibles :

  1. Tri de A à Z,
  2. Tri de Z à A,
  3. Tri personnalisé.

En fonction du type contenu de la colonne à trier, le bouton « Tri de A à Z » peut avoir un autre nom : pour une colonne contenant des nombres, le bouton sera nommé « Tri du plus petit au plus grand ». Pour une colonne contenant des dates, le bouton sera nommé « Tri du plus ancien au plus récent ».

Le tri d’une feuille telle que la feuille exemple ci-dessous peut porter sur un seul critère, aussi bien que sur plusieurs critères.

TRI SUR UN SEUL CRITERE

Le tri sur un seul critère est un tri sur une seule colonne. On sélectionne n’importe quelle cellule de la colonne à trier. Si on veut trier par l’âge (du plus petit au plus grand), on peut sélectionner la cellule G1, G2, etc. Et on clique simplement sur le bouton « Tri de A à Z » (pour l’âge, le bouton sera nommé « Tri du plus petit au plus grand »).

Si on sélectionne toute la colonne, une boite de dialogue de confirmation est proposée, pour étendre le tri à la sélection.

Il est donc possible de ne trier qu’une seule colonne sans trier le « tableau de données » entier.

TRI SUR PLUSIEURS CRITERES

Pour effectuer un tri sur plusieurs critères, cliquer sur le bouton « Tri Personnalisé ».

Il faut bien vérifier que la case à cocher « Mes données ont des en-têtes » est sélectionnée, de manière à avoir le nom de la colonne affichée dans la colonne à trier, et à ne pas intégrer la première ligne dans le tri.

Comme vous pouvez le constater, le tri Excel est facile à utiliser !

Top

Utiliser la recherche dans Excel

La recherche de texte est une action qui peut s’effectuer sur toute feuille Excel. La recherche est accessible par deux méthodes :

  • le raccourci standard de Windows « CTRL + F »,
  • le bouton « Rechercher et sélectionner » du menu « Accueil » du ruban, groupe « Edition ».

LA RECHERCHE SIMPLE

Sur les données présentées ci-dessous, nous pouvons effectuer une recherche de la chaine de caractères « PARIS ». Chaque clic sur le bouton suivant sélectionne la cellule « suivante » contenant la chaine de caractères cherchée :

Paramètres par défaut de la recherche :

  • la recherche est effectuée ligne à ligne. C’est-à-dire que une occurrence de la chaine de caractère « PARIS » est recherchée à la ligne 1, puis la ligne 2 etc. Par exemple, la chaine « PARIS » en A8 serait trouvée avant la chaine « PARIS » située en B1,
  • la recherche n’est pas sensible à la casse (minuscule / majuscule). Si on cherche « PARIS », on trouvera également « Paris » ou « paris ».
  • La recherche ne porte pas sur le contenu entier d’une cellule. Par exemple, si une cellule C41 contient « découvrez nos paris sportifs en ligne » et que l’on cherche « PARIS », la cellule C4 validera la recherche.

Ces paramètres par défaut peuvent être modifiés, en cliquant sur le bouton « Options », c’est la recherche avancée.

Bouton « Rechercher tout » : la recherche présente tous les résultats dans une liste. Quand on clique sur une occurrence de la liste, la cellule est sélectionnée.

RECHERCHE AVANCEE

  • Dans : « Feuille / Classeur »

Si « Feuille » est sélectionné, la recherche ne porte que sur la feuille en cours. Si on sélectionne Classeur, la recherche porte sur toutes les feuilles sur classeur, ce qui est très utile lorsque nos données sont « éclatées » sur plusieurs feuilles.

  • Sens : « Par ligne / Par colonne »

Modifie le sens de la recherche

  • Regarder dans : « Formules / Valeurs / Commentaires »

« Formules » : la valeur est cherchée dans les formules pour les cellules contenant des formules, dans les valeurs affichées pour les cellules sans formules.

« Valeurs » : la valeur est cherchée dans les valeurs affichées pour les cellules avec ou sans formules.

« Commentaires » : la valeur est cherchée dans les commentaires saisis dans la feuille (menu « Révision », « Nouveau Commentaire ».

  • Respecter la casse

La valeur cherchée doit respecter exactement la casse. Exemple, si on cherche « PARIS », une cellule contenant « paris » ne sera pas sélectionnée.

  • Totalité du contenu de la cellule

Le texte de la cellule doit correspondre exactement à la valeur cherchée. Exemple, si on cherche « PARIS », « PARISOT », « PARIS SPORTIFS » ne seront pas sélectionnés. Seules les cellules contenant uniquement « PARIS » ou « paris » pourront être sélectionnées.

  • Format

La recherche peut être ciblée sur un format (couleur, police, etc.). Le format peut être choisi à partir de la cellule (auquel cas on sélectionne une cellule pour choisir un format à rechercher), ou bien en définissant le format dans la boite de dialogue des formats.

Top

Importer un flux RSS dans Excel

Il est possible d’importer un flux RSS dans Excel. Un flux RSS n’est ni plus ni moins qu’un fichier XML mis à disposition sur le Web. Nous avons vu qu’il est possible dans Excel d’importer des données Web, ainsi que des données XML. Il est donc logique qu’Excel puisse lire un flux RSS.

Tout d’abord, sélectionner le flux RSS désiré. Pour notre exemple, nous allons encore une fois rechercher des taux de change mis à disposition en flux RSS. Une rapide recherche nous fournit un flux RSS adéquat. Nous procédons alors à l’import de données Web dans Excel, menu « Données – Données externes – A partir du Web » et nous collons l’adresse du flux RSS.

Attention, sur certains flux RSS, il sera nécessaire de cliquer sur l’option « Masquer les icônes » entourée en rouge sur la capture d’écran ci-dessus. Nous validons avec le bouton « Importer » et nous obtenons un tableau Excel facilement exploitable.

L’utilisation des flux RSS dans Excel offre une fonctionnalité proche de certains Web Services !

Top

Ecrire un tri en VBA Excel

En VBA, la méthode « Sort » s’applique à des objets « Range » (à des plages de cellules). Nous avons fréquemment besoin d’écrire des sort sur des plages en VBA Excel. Comme souvent dans ce cas, nous n’avons qu’à faire appel à l’enregistreur de macros, faire un tri et adapter le code. Malheureusement, dans ce cas, le code est disons… Trop riche… Voici en effet le code généré pour effectuer un tri sur une plage A3:F75 :

Range("A3:F3").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveWorkbook.Worksheets("Echeancier").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Echeancier").Sort.SortFields.Add Key:=Range( _
"A4:A75"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Echeancier").Sort
.SetRange Range("A3:F75")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

Franchement, ça ne donne pas envie d’écrire un tri… Voici donc la marche à suivre :

 1/ Déclarer un Objet Range,

2/ Affecter l’objet Range, dont la première ligne contient les titres,

3/ Affecter les clés de tri,

4/ Appliquer la méthode Sort à l’objet Range sur les clés désirées.

 Voici un exemple :

Dim MonEcheancier As Excel.Range
Set MonEcheancier = Range(MaFeuilleEcheancier.Cells(1, 1), MaFeuilleEcheancier.Cells(1, 1).End(xlDown).Offset(0, 5))
MonEcheancier.Sort Key1:=MaFeuilleEcheancier.Cells(1, 1), Order1:=xlAscending, Header:=xlYes

Si nous avions plusieurs clés, nous n’aurions qu’à écrire MaRange.Sort Key1:=xxx Key2:=yyy etc. Nous avons également positionné l’option Header à oui, car nous avons une en-tête qui ne sera pas triée.

Pour certains, l’affection du Range pourrait sembler confuse… Une Range simple (rectangulaire) est délimitée par 2 cellules, en haut à gauche et en bas à droite. La syntaxe de Range est donc Range (Cell1, Cell2).

Une Cellule est identifiée par son numéro de ligne et son numéro de colonne. On écrit Cells(1,5) pour la 5ème colonne de la 1ère ligne. On peut préfixer Cells par la feuille qu’on adresse, ici MaFeuilleEcheancier, qu’on aura au préalable déclarée avec :

 Dim MonProjet As Excel.Workbook
Dim MaFeuilleEcheancier As Excel.Worksheet
Set MonProjet = Excel.ActiveWorkbook
Set MaFeuilleEcheancier = MonProjet.Worksheets("Echeancier")

Enfin, .End(XlDown) permet d’adresser directement la dernière cellule non vide vers le bas (XlToRight pour aller vers la droite) et .Offset permet de se décaler de 0 lignes et 5 colonnes dans l’exemple.

Top
1 2 3 7 Page 1 of 7

Contactez-nous contact@expert-data.fr

EXPERT data, solutions pour TPE/PME et grands comptes