Macro VBA pour définir les critères de filtre dans Microsoft Excel

Si le filtre automatique est disponible dans le tableau, des critères individuels peuvent être définis pour les colonnes, en fonction desquels certaines lignes peuvent ensuite être filtrées.

Assurez-vous que les données que vous souhaitez filtrer ne contiennent aucune ligne vide. Le filtrage des données ne fonctionne que jusqu’à la première occurrence d’une ligne vide.

 -Déterminer la pénurie

Dans le premier exemple, toutes les lignes qui ont un inventaire <100 doivent être affichées et doivent donc être réorganisées prochainement. La macro pour ce contrôle d’inventaire est présentée dans l’extrait 6.76.

Sub StockSmaller 100()

 Selection.AutoFilter _

  Field:=2, Criteria1:="<100", Operator:=xlAnd

End Sub

La syntaxe complète de la méthode AutoFilter est:

Ausdruck.AutoFilter(Field, Criteria1, Operator, Criteria2, VisibleDropDown)

Avec la méthode de filtre automatique, vous avez donné à la colonne B (Champ: = 2) le critère d’affichage des lignes (Critère1: = “<100”). Avec l’opérateur argument, vous pouvez choisir parmi plusieurs constantes, que vous pouvez prendre dans le tableau .

Tableau : Les constantes d’opérateur de la méthode de filtrage automatique

L’argument Criteria2 est un deuxième critère possible. Il est utilisé conjointement avec le critère ia1 et l’argument Opérateur pour créer des critères composites.

Le dernier argument, VisibleDropDown, est défini sur True par défaut, ce qui signifie que les flèches déroulantes du champ filtré sont affichées. La définition de l’argument sur False masquera les flèches déroulantes du filtre automatique pour le champ filtré.

Tableau : Le nombre d’enregistrements filtrés est affiché dans la barre d’état.

-Effectuer des contrôles d’inventaire et de prix

Dans l’exemple suivant, deux critères de filtre sont définis: un critère de filtre pour la colonne B (stock> = 500) et l’autre critère de filtre pour la colonne C (prix> 500).

Sub sousstockGr500uPrGr100 ()

  With.selection

   .AutoFilter Field: = 2, Criteria1: = "> = 500", _

  Operator: = xlAnd

   .AutoFilter Field: = 3, Criteria1: = "> 500", _

  Operator: = xlAnd

  End With

End Sub

Afficher les lignes dans une certaine plage de valeurs

Très souvent, les limites de valeur de / à sont également utilisées dans la pratique. Par exemple, dans la figure 6.23, vous pouvez afficher tous les articles dont le prix est compris entre 500 et 750 DM. La macro pour cette tâche est présentée dans l’extrait 6.78.

Sub PrixEntre500750$()

  Selection.AutoFilter _

  Field: = 3, Criteria1: = "> = 500", Operator: = xlAnd, _

            Criteria2: = "<= 750"

End Sub

Si vous le souhaitez, vous pouvez conserver la macro de l’extrait flexible en entrant les positions des coins dans des cellules individuelles et en y faisant référence dans la macro.

La macro pour mapper cette fonctionnalité se trouve dans l’extrait .

Sub Gammedeprixpartirdescellules ()

  Sheets ("Feuil2"). Activate

  Selection.AutoFilter _

  Field: = 3, Criteria1: = Range ("C2"). Value, _

  Operator: = xlAnd, Criteria2: = Range ("C3"). Value

End Sub

 

-Déterminer les valeurs extrêmes

Un autre exemple de filtrage dynamique des données est le filtrage des dix premiers. Avec l’aide de ce filtre, vous avez la possibilité de filtrer les n-plus petites ou n-plus grandes entrées d’une liste.

Dans le tableau  ci-dessous , Si  On constate que l’identifiant du filtre (B pour l’article le moins cher et T pour l’article le plus cher) et le nombre d’ensembles à afficher sont conçus pour être flexibles. La macro de ce contrôle est présentée .

Sub prixevaluation ()

Dim op As Integer

Dim i As Integer

Select Case Range ("B2"). Value

  Case "T"

    op = 3

  Case "B"

    op = 4

  Case Else

  MsgBox "This letter is not allowed!": Exit Sub

  End Select

  i = Range ("C2"). Value

  Selection.AutoFilter _

    Field: = 3, Criteria1: = i, Operator: = op

End Sub

Dans la première étape, vous évaluez la cellule B2 à l’aide d’une structure de sélection de cas. La plaque d’immatriculation respective est là. La constante d’opérateur xlTop10Items du tableau suivant correspond à la valeur numérique 3 et la constante xlBottom10Items correspond à la valeur 4. Ensuite, à partir de l’entrée de la cellule C2, déterminez le nombre de jeux d’articles à afficher. Vous transmettez les deux informations à la méthode AutoFilter.

 –Contrôles et évaluations des filtres

Dans les pages suivantes, vous apprendrez à déterminer et à lire les données filtrées. Il y a u. une. les questions d’intérêt suivantes:

► Dans quelles colonnes un critère de filtre est-il défini?

► Combien de lignes sont filtrées?

► Combien de lignes sont filtrées?

► Quelle est la valeur la plus élevée dans la zone filtrée?

► Quel est le nom de la première ligne de la zone filtrée?

► Quel est le nom de la dernière ligne de la zone filtrée?

-Dans quelles colonnes un critère de filtre est-il défini?

Lors de la programmation des filtres, il est important de savoir dans quelles colonnes un ou plusieurs critères de filtre ont déjà été définis. Vous pouvez le faire avec la macro de l’extrait 6.81.

Sub Estcriteredefiltre ()

Dim w As Worksheet

Dim b As Boolean

Dim i As Integer

Set w = Worksheets ("Feuil3")

For i = 1 To ActiveSheet.Columns.Count

On Error GoTo ends

  If w.AutoFilterMode Then

      b = w.AutoFilter.Filters (i) .On

      Debug.Print "Filter in column" & i & ":"; b

  End If

Next i

The End:

End Sub

 

Déterminez d’abord la fréquence à laquelle votre boucle For Next doit être exécutée.

Idéalement, cette boucle est parcourue jusqu’à ce que la dernière colonne occupée soit atteinte. Cependant, comme cette solution n’est pas toujours la plus sécurisée dans les tables fréquemment éditées, puisque les données sont constamment supprimées et écrites, il peut arriver que la zone utilisée ne puisse pas toujours être déterminée correctement. Pour cette raison, ajoutez l’instruction On Error pour être du bon côté et, en cas d’erreur, branchez directement à la fin de la macro.

Vous enregistrez l’état du filtre automatique dans une variable de type booléen. La propriété Filters renvoie la valeur false si aucun critère de filtre n’est défini dans la colonne respective, ou la valeur true si un critère de filtre a été défini pour la colonne. À l’aide de la méthode Print, vous sortez l’index de colonne et l’état du filtre dans la fenêtre directe de votre environnement de développement (voir Figure ).

 –Combien de lignes sont filtrées?

Habituellement, le nombre de lignes filtrées est affiché dans la barre d’état. Toutefois, si vous souhaitez déterminer vous-même ce nombre filtré de lignes, démarrez la macro à partir du listing 6.82.

Sub combiendelignessontvisibles ()

Dim area As Range

Dim i As Integer

  Sheets ("Table 4"). Activate

  Set Range = Range ("A1"). CurrentRegion

  i = Intersect (area.SpecialCells (xlVisible), _

  Area.Columns (1)). Count - 1

MsgBox "You have" & i & "lines filtered", _

   vbInformation, "filter result"

End Sub

Tableau : Un critère de filtre est défini dans la colonne C.

Fenêtre d’édition de fichier. Entrez la question ici

Count propriété de laquelle vous devez soustraire la valeur 1, car la ligne d’en-tête ne doit pas être comptée comme faisant partie du résultat des données filtrées. Enfin, vous affichez le nombre de lignes filtrées dans une fenêtre de message, que vous fournissez avec les informations de symbole. Vous utilisez la constante vbInformation pour cela.

Combien de cellules sont filtrées?

Si vous le souhaitez, vous pouvez connaître le nombre de lignes filtrées ainsi que le nombre de cellules filtrées. Vous pouvez par exemple afficher ces informations dans la barre d’état. Vous pouvez consulter la macro requise pour cela dans l’extrait 6.83.

Sub NumberCellsInFiltering ()

Dim l As Long

Dim i As Integer

Sheets ("Feuil9"). Activate

Range ("A1"). Select

  Application.DisplayStatusBar = True

  l = Application.WorksheetFunction.Subtotal _

   (3, Range (ActiveSheet.UsedRange.Address))

  i = ActiveSheet.UsedRange.Columns.Count

  Application.StatusBar = _

   "Vous avez" & l - i & "cellules filtrés!"

End Sub

Pour utiliser la barre d’état pour la programmation, affichez-la d’abord à l’aide de la propriété DisplayStatusBar, que vous définissez sur la valeur True. Utilisez ensuite la propriété WorksheetFunction en conjonction avec la fonction SubTotal pour déterminer les cellules qui ont été filtrées. Vous transmettez l’argument 3 à la fonction SubTotal, ce qui signifie qu’avec cette fonction le résultat partiel est formé à partir de la fonction de table , qui compte toutes les cellules utilisées.

Index des fonctions la description
1 La fonction de table MOYENNE renvoie la valeur moyenne d’un

Arguments. Il y a 1 à 30 arguments numériques ou Les références de cellule sont possibles dont vous voulez calculer la valeur moyenne. pour un

2 La fonction de table NUMBER calcule le nombre de valeurs numériques qu’une liste d’arguments contient. De 1 à 30 arguments numériques ou références de cellules sont possibles, dont vous souhaitez déterminer le nombre.
3 3 La fonction de table ANZAHL2 calcule   combien de valeurs contient une liste d’arguments. Peu importe que les valeurs soient numériques ou textuelles. Cette fonction peut avoir 1 à 30 arguments ou références de cellule, dont vous souhaitez déterminer le nombre. Cependant, les cellules vides ne sont pas comptées.
4 4 La fonction de table MAX renvoie la plus grande valeur d’une liste d’arguments. La liste d’arguments peut comprendre de 1 à 30 nombres ou références de cellules, dont vous souhaitez déterminer la plus grande.
5  

5 La fonction de table MIN renvoie la plus petite valeur d’une liste d’arguments. La liste d’arguments peut être composée de 1 à 30 nombres ou références de cellules, dont vous souhaitez déterminer le plus petit nombre.

6 6 La fonction de table PRODUCT détermine le résultat de la multiplication d’un ou plusieurs arguments. Les nombres, les valeurs de vérité et les nombres sous forme de texte sont pris en compte lors de la formation du total.
7 7 La fonction de table STDEV estime l’écart type sur la base d’un échantillon. La fonction peut prendre de 1 à 30 arguments numériques représentant un échantillon tiré d’une population. Au lieu des arguments séparés par des points-virgules, vous pouvez spécifier une matrice ou une référence à une matrice.
8 8 La fonction de table STDEVN calcule l’écart type en fonction de la population. On suppose que toutes  les valeurs sont données comme arguments .La fonction peut prendre de 1 à 30 arguments  numériques représentant un échantillon tiré d’une population. Au lieu des arguments séparés par des points-virgules, vous pouvez spécifier une matrice ou une référence à une matrice.

 

9  La fonction de table SOMME additionne les arguments spécifiés. Vous pouvez saisir entre 1 et 30 arguments ou références de cellule. Les nombres, les valeurs de vérité et les nombres sous forme de texte sont pris en compte lors de la formation du total.
10 La fonction de table VARIANCE suppose que les arguments qui lui sont passés représentent un échantillon tiré d’une population. Les arguments possibles sont de 1 à 30 arguments numériques ou références de cellule qui représentent un échantillon prélevé dans une population.

 

11 La fonction de table VARIANCES suppose que les arguments qui lui sont passés correspondent à une population. 1 à 30 arguments numériques ou références de cellule qui correspondent à une population sont possibles comme arguments.

 

 

S’abonner
Notifier de
0 Commentaires
Inline Feedbacks
Voir tous les commentaires

Initiation à Excel

Fonctions Excel

Excel VBA

Macros VBA Utiles

Plus d'outils

Sur Facebook

Sur YouTube

0
Nous aimerions avoir votre avis, veuillez laisser un commentaire.x