la fonction FILTRE d’Excel appliquée aux tableaux dynamiques

Cours et tutoriel excel / cours excel gratuit / tutoriel excel gratuit

Bienvenue dans ce cours Excel qui vous montre comment appliquer la fonction FILTRE dynamique

La fonction Excel FILTER est une fonction matricielle dynamique qui permet, à travers le paramétrage de critères spécifiques, de filtrer les données d’un tableau Excel et d’obtenir l’extraction du résultat au sein d’une matrice dynamique qui se développera dans la feuille de travail.

La fonction de tableau dynamique FILTER, définie dans une cellule de feuille de calcul renvoie donc un ensemble de valeurs (tableau) dans des cellules adjacentes. Ce type de comportement est appelé expansion.

Par définition, les fonctions Excel pouvant renvoyer des tableaux de différentes tailles sont appelées fonctions de tableau dynamique.
:: La fonction FILTRE n’est pas disponible dans la version 2019 d’Excel mais uniquement dans 365 à partir de la date de juillet 2020.

Syntaxe de la fonction de filtre.

La syntaxe de la fonction FILTRE est la suivante
= FILTER (matrice ; inclure ; [if_empty])

Dans lequel:
matrice : représente la plage de données à filtrer.
include : définit une matrice booléenne représentant les critères à filtrer et à appliquer à la plage de données. Vous pouvez utiliser un critère unique ou plusieurs critères de filtre.
if_empty : (argument facultatif) est utilisé pour définir une chaîne de texte à renvoyer s’il n’y a pas de résultat. Cet argument, étant facultatif, peut être omis ou “” peut être saisi pour renvoyer une chaîne vide.

Dans l’exemple illustré dans la figure ci-dessous, un tableau d’une trentaine de données représentées en en-tête (Vendeur, Région, Secteur et Chiffre d’affaires) est attendu. Le critère de recherche (Rossi) a été saisi dans la cellule F2.
Dans la cellule F6, la fonction est insérée :
= FILTRE (A2 : D34 ; A2 : A34 = F2 ; “il n’y a pas de données”)
Dans lequel, l’argument du tableau est représenté par la plage de données A2 : D34
l’argument include est décrit en comparant la matrice A2 : A34 avec le contenu de la cellule F2
Enfin, si le critère ne renvoie aucun enregistrement dans l’argument if_empty entre guillemets doubles, le texte “il n’y a pas de données” a été inséré

Une fois la fonction FILTER insérée à partir de la cellule F6, le résultat sera développé dans les colonnes de droite et dans toutes les lignes sous-jacentes.

Appliquez la fonction de filtrage en attendant deux critères de recherche ou plus

Exemple. Dans les quatre premières colonnes de la feuille de travail, il y a un tableau organisé dans les champs suivants : vendeur, région, secteur et chiffre d’affaires. Dans les cellules F2 et G2 sont insérés respectivement les critères Vendor Rossi et Veneto Region.
Enfin, la fonction est insérée dans la cellule F6 : = FILTER (A2 : D34 ; (A2 : A34 = F2) * (B2 : B34 = G2) ; “il n’y a pas de données”)

Dans lequel, l’argument du tableau est représenté par la plage de données A2 : D34
l’argument include est décrit en comparant la matrice A2 : A34 avec le critère contenu dans la cellule F2 et multiplié par la matrice B2 : B34 comparée au critère contenu dans la cellule G2, soit (A2 : A34 = F2) * (B2 : B34 = G2)
Enfin, si les critères n’ont renvoyé aucun enregistrement dans l’argument if_empty, le texte “il n’y a pas de données” a été inséré

Parce que les deux matrices sont multipliées l’une par l’autre dans le deuxième argument de la fonction FILTER

:: Approfondissement sur la Fonction = FILTRE (B3 : E35 ; (B3 : B35 = G3) * (C3 : C35 = H3) ; “aucune donnée disponible”)
Dans le deuxième argument sur les deux matrices colonnes du tableau (colonnes A et B) les critères comparatifs Rossi et Veneto sont vérifiés.

Ces comparaisons renvoient, selon la correspondance exacte ou non, la valeur TRUE ou FALSE.
En logique mathématique, VRAI correspond au chiffre 1 et FAUX au chiffre 0.
Dans la Fonction proposée, les résultats des deux matrices sont multipliés entre elles, donc seule la correspondance de deux nombres 1 renverra encore le nombre 1 (VRAI) et dans toutes les autres multiplications (FALS0 * FALSE ; TRUE * FALSE ; FALSE * TRUE ) le résultat sera nul et donc FAUX ! (0 * 0 ; 1 * 0 ; 0 * 1). [Voir image ci-dessous]

le code d’erreur #EXPANSION !

Pendant le processus d’expansion des données, s’il n’y a pas assez de cellules libres sur la feuille de calcul pour extraire toutes les valeurs correspondant aux critères définis, le code d’erreur #EXPANSION apparaîtra dans la cellule où la fonction FILTRE a été insérée.

CONCLUSION

Après avoir défini les critères de recherche sur un ou plusieurs tableaux de colonnes dans la fonction Excel FILTER, la fonction développe le résultat final à partir de la cellule dans laquelle la fonction elle-même a été insérée.
Cela signifie qu’Excel crée dynamiquement une plage de tableau correctement dimensionnée dès que la fonction est entrée en appuyant sur ENTRÉE.
Le résultat de la fonction FILTRE est dynamique donc si les données source du tableau Excel devaient être modifiées ou si les critères étaient modifiés, le résultat sera automatiquement mis à jour.
Si la fonction FILTER renvoie le code d’erreur #EXPANSION! cela signifie que le résultat de la formule ne peut pas s’étendre sur la feuille de calcul car certaines cellules voisines contiennent des données.
Si une cellule de l’expansion des données est sélectionnée, la même fonction insérée dans la première cellule apparaît sur la barre de formule, avec la différence substantielle que ces fonctions, cependant, NE PEUVENT PAS être modifiées et apparaîtront écrites en gris clair.

Merci à www.valterborsato.it 🙂