De nouvelles fonctions ont été introduites dans Excel. Les fonctions FILTRE et TRIER en font partie. Elles devraient vite se rendre utiles…
La fonction FILTRE filtre les données d’une plage de cellules en fonction de critères… et renvoie toutes les lignes correspondantes dans une nouvelle plage de cellules. Cette fonction repose sur le même principe de fonctionnement que la fonction RECHERCHEX sauf que cette dernière ne restitue qu’une seule ligne. D’où l’intérêt de la fonction FILTRE qui, en fournissant toutes les lignes de données correspondant aux critères définis par l’utilisateur, se comporte de fait comme une requête SQL.
Tout comme RECHERCHEX, la fonction FILTRE utilise le principe de la propagation des données (spilling) c’est-à-dire que lorsque cette fonction renvoie plusieurs résultats, ils sont restitués dans les cellules environnantes (grande nouveauté de la nouvelle génération de fonctions Excel).
Syntaxe :
FILTRE(plage_données;critères;[retour_absence_résultat]
Arguments obligatoires :
- Plage_données : plage de cellules contenant les données à filtrer
- Critères : plage de cellules + opérateur de comparaison + données comparée (même principe que RECHERCHEX) ; exemple : D4:D21>10000.
Arguments facultatifs :
- Retour_absence_résultat : permet d’afficher un message ou d’effectuer un calcul alternatif en cas d’absence de résultat (afin d’éviter l’affichage d’un message d’erreur Excel)
Exemple : FILTRE APPLIQUE SUR MONTANTS (colonne D) > 10 000 € :
=FILTRE(A4:D21;D4:D21>10000)
Restitution :
Le lecteur attentif observera au passage que le résultat tient sur quatre colonnes et cinq lignes (principe de la propagation des données) alors que la formule de calcul ne tient que dans une seule cellule.
La fonction FILTRER trie les données d’une plage de cellules.
Syntaxe :
TRIER(plage_cellules;[index_colonne];[ordre];[par_colonne])
Associée à la fonction FILTRE, la fonction TRIER permet de trier des données préalablement filtrées.
Argument obligatoire :
- Plage_cellules : plage de cellules à trier
Arguments facultatifs
- Index_colonne : numéro de colonne (ou de ligne) à trier
- Ordre : ordre de tri = 1 : ascendant (par défaut), -1 : descendant
- Par_colonne : tri en ligne (FAUX) ou colonne (VRAI)
Exemple : FILTRE APPLIQUE SUR MONTANTS > 10 000 € puis TRI PAR MONTANT
=TRIER(FILTRE(A4:D21;D4:D21>10000);4)
Restitution :
Les données restituées sont les mêmes que dans l’exemple précédent sauf qu’ici elles sont triées par montant (index = 4).
Filtres multicritères :
La fonction FILTRE accepte traiter plusieurs critères simultanément (au même titre que RECHERCHEX). Le filtrage multicritères nécessite de faire appel à l’arithmétique booléenne.
Exemple : FILTRE APPLIQUE SUR MONTANTS > 10 000 € ET (*) COMPTE COMMENCANT PAR 6152x puis TRI PAR MONTANT :
=TRIER(FILTRE(A4:D21;(D4:D21>10000)*(GAUCHE(B4:B21;4)=”6152″));4)
Restitution :
Autre exemple plus complexe : FILTRE APPLIQUE SUR :
- MONTANTS > 10 000 € ET (*) COMPTE COMMENCANT PAR 6152x
OU (+) - COMPTE COMMENCANT PAR 6156 (quel que soit le montant)
Puis TRI PAR MONTANT
=TRIER(FILTRE(A4:D21;((D4:D21>10000)*(GAUCHE(B4:B21;4)=”6152″))+(GAUCHE(B4:B21;4)=”6156″));4)
Le position des parenthèses revêt une importance particulière puisqu’elles organisent l’expression des différents critères et qu’en fonction de leur position, ces critères pourront avoir une autre signification (à l’image de la position des virgules dans une phrase…).
Restitution :
Ce dernier exemple démontre la puissance de la fonction filtre. Elle peut avantageusement se substituer à des requêtes SQL… A titre de comparaison, la requête SQL aurait pu être rédigée ainsi :
SELECT CodeEntité, CompteNum, CompteLib, Solde FROM _BG WHERE (Solde>10000 AND Left(CompteNum,4)="6152") OR (Left(CompteNum,4)="6156") ORDER BY Solde
Le classeur Excel contenant les exemples exposés ci-avant est proposé en libre téléchargement ci-après.
Découvrir les autres nouveautés d’Excel 2021 (site de l’éditeur)
___
Approfondir le sujet : Analyse de données et automatisation / Tout savoir sur Excel / Découvrir la série d’articles Maîtriser Excel
Derniers articles parBenoît RIVIERE (voir tous)
- Excel : collecter des données juridiques sur les entreprises avec l’API SIRENE – 1ère partie : base SIREN (entreprises) - lundi 15 avril 2024
- Obtenir les données financières de sociétés au format Excel - mardi 2 avril 2024
- Robotic Process Automation (RPA) et Python : piloter des applications web - dimanche 24 mars 2024
- Initiation à la programmation en Basic : nouveau compilateur FreeBASIC - lundi 18 mars 2024
- Retrogaming : (re)jouer aux jeux vidéo d’antan - lundi 11 mars 2024
Pingback: Excel : fiabiliser la saisie de données avec la validation de données - Audit & Systèmes d'Information
Pingback: Excel 2021 : les fonctions ASSEMB.V et ASSEMB.H - Audit & Systèmes d'Information