Filtrage horizontal des colonnes dans Excel

Si vous n'êtes pas tout à fait un utilisateur novice, vous devez déjà avoir remarqué que 99% de tout dans Excel est conçu pour fonctionner avec des tableaux verticaux, où les paramètres ou les attributs (champs) traversent les colonnes et où se trouvent les informations sur les objets ou les événements. dans les lignes. Tableaux croisés dynamiques, sous-totaux, copie de formules en un double clic, tout est spécifiquement adapté à ce format de données.

Cependant, il n'y a pas de règles sans exceptions et avec une fréquence assez régulière on me demande ce qu'il faut faire si un tableau à orientation sémantique horizontale, ou un tableau où lignes et colonnes ont le même poids de sens, tombait dans l'ouvrage :

Et si Excel sait toujours trier horizontalement (avec la commande Données – Trier – Options – Trier les colonnes), alors la situation avec le filtrage est pire - il n'y a tout simplement pas d'outils intégrés pour filtrer les colonnes, pas les lignes dans Excel. Donc, si vous êtes confronté à une telle tâche, vous devrez trouver des solutions de contournement plus ou moins complexes.

Méthode 1. Nouvelle fonction FILTRE

Si vous utilisez la nouvelle version d'Excel 2021 ou un abonnement Excel 365, vous pouvez profiter de la nouvelle fonctionnalité FILTRE (FILTRE), qui peut filtrer les données source non seulement par lignes, mais également par colonnes. Pour fonctionner, cette fonction nécessite une ligne de tableau horizontale unidimensionnelle auxiliaire, où chaque valeur (TRUE ou FALSE) détermine si nous affichons ou, au contraire, masquons la colonne suivante du tableau.

Ajoutons la ligne suivante au-dessus de notre tableau et écrivons-y le statut de chaque colonne :

• Disons que nous voulons toujours afficher les première et dernière colonnes (en-têtes et totaux), donc pour eux dans les première et dernière cellules du tableau, nous définissons la valeur = TRUE.
• Pour les colonnes restantes, le contenu des cellules correspondantes sera une formule qui vérifie la condition dont nous avons besoin à l'aide de fonctions И (ET) or OR (OR). Par exemple, que le total est compris entre 300 et 500.

Après cela, il ne reste plus qu'à utiliser la fonction FILTRE pour sélectionner les colonnes au-dessus desquelles notre tableau auxiliaire a une valeur TRUE :

De même, vous pouvez filtrer les colonnes par une liste donnée. Dans ce cas, la fonction vous aidera COUNTIF (NB.SI), qui vérifie le nombre d'occurrences du nom de colonne suivant à partir de l'en-tête de table dans la liste autorisée :

Méthode 2. Tableau croisé dynamique au lieu de l'habituel

Actuellement, Excel a intégré le filtrage horizontal par colonnes uniquement dans les tableaux croisés dynamiques, donc si nous parvenons à convertir notre tableau d'origine en tableau croisé dynamique, nous pouvons utiliser cette fonctionnalité intégrée. Pour ce faire, notre table source doit satisfaire les conditions suivantes :

• avoir une ligne d'en-tête "correcte" d'une ligne sans cellules vides et fusionnées - sinon cela ne fonctionnera pas pour créer un tableau croisé dynamique ;
• ne contiennent pas de doublons dans les étiquettes des lignes et des colonnes - ils se "réduiront" dans le résumé en une liste de valeurs uniques uniquement ;
• ne contenir que des nombres dans la plage de valeurs (à l'intersection des lignes et des colonnes), car le tableau croisé dynamique leur appliquera certainement une sorte de fonction d'agrégation (somme, moyenne, etc.) et cela ne fonctionnera pas avec le texte

Si toutes ces conditions sont remplies, alors pour construire un tableau croisé dynamique qui ressemble à notre tableau d'origine, il (l'original) devra être développé du tableau croisé en un tableau plat (normalisé). Et le moyen le plus simple de le faire est d'utiliser le complément Power Query, un puissant outil de transformation de données intégré à Excel depuis 2016. 

Ce sont:

1. Convertissons le tableau en une commande dynamique "intelligente" Accueil – Format sous forme de tableau (Accueil — Format sous forme de tableau).
2. Chargement dans Power Query avec la commande Données – À partir du tableau/plage (Données – À partir du tableau/plage).
3. Nous filtrons la ligne avec les totaux (le résumé aura ses propres totaux).
4. Faites un clic droit sur l'en-tête de la première colonne et sélectionnez Déplier les autres colonnes (Dépivoter les autres colonnes). Toutes les colonnes non sélectionnées sont converties en deux - le nom de l'employé et la valeur de son indicateur.
5. Filtrage de la colonne avec les totaux entrés dans la colonne Attribut.
6. Nous construisons un tableau croisé dynamique en fonction du tableau plat (normalisé) résultant avec la commande Accueil — Fermer et charger — Fermer et charger dans… (Accueil — Fermer et charger — Fermer et charger dans…).

Vous pouvez désormais utiliser la possibilité de filtrer les colonnes disponibles dans les tableaux croisés dynamiques - les coches habituelles devant les noms et les éléments Filtres de signatures (Filtres d'étiquettes) or Filtres par valeur (Filtres de valeur):

Et bien sûr, lors de la modification des données, vous devrez mettre à jour notre requête et le résumé avec un raccourci clavier Ctrl+autre+F5 ou équipe Données – Tout actualiser (Données — Actualiser tout).

Méthode 3. Macro dans VBA

Toutes les méthodes précédentes, comme vous pouvez facilement le voir, ne filtrent pas exactement - nous ne cachons pas les colonnes dans la liste d'origine, mais formons une nouvelle table avec un ensemble donné de colonnes à partir de l'original. S'il est nécessaire de filtrer (masquer) les colonnes dans les données source, une approche fondamentalement différente est nécessaire, à savoir une macro.

Supposons que nous voulions filtrer les colonnes à la volée où le nom du responsable dans l'en-tête du tableau satisfait le masque spécifié dans la cellule jaune A4, par exemple, commence par la lettre "A" (c'est-à-dire obtenir "Anna" et "Arthur " par conséquent). 

Comme dans la première méthode, nous implémentons d'abord une ligne de plage auxiliaire, où dans chaque cellule notre critère sera vérifié par une formule et les valeurs logiques VRAI ou FAUX seront affichées pour les colonnes visibles et masquées, respectivement :

Ajoutons ensuite une macro simple. Faites un clic droit sur l'onglet de la feuille et sélectionnez la commande Identifier (Code source). Copiez et collez le code VBA suivant dans la fenêtre qui s'ouvre :

Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$A$4" Then For Each cell In Range("D2:O2") If cell = True Then cell.EntireColumn.Hidden = False Else cell.EntireColumn.Hidden = Vrai Fin Si cellule suivante Fin Si Fin Sous  

Sa logique est la suivante :

• En général, il s'agit d'un gestionnaire d'événements Feuille de travail_Change, c'est-à-dire que cette macro s'exécutera automatiquement sur toute modification apportée à n'importe quelle cellule de la feuille actuelle.
• La référence à la cellule modifiée sera toujours dans la variable Target.
• Tout d'abord, nous vérifions que l'utilisateur a changé exactement la cellule avec le critère (A4) - cela est fait par l'opérateur if.
• Puis le cycle commence Pour chaque… pour itérer sur les cellules grises (D2:O2) avec des valeurs d'indicateur TRUE/FALSE pour chaque colonne.
• Si la valeur de la prochaine cellule grise est VRAI (vrai), alors la colonne n'est pas masquée, sinon on la masque (propriété caché).

•  Fonctions de tableau dynamique d'Office 365 : FILTER, SORT et UNIC
• Tableau croisé dynamique avec en-tête multiligne à l'aide de Power Query
• Que sont les macros, comment les créer et les utiliser