Discussion :

[casavba]

Bonjour,

Je sollicite vos réflexions sur un sujet particulier relatif à l'application d'une table de critères à une table de données.
Je souhaite en effet marquer dans une colonne [Z] de la table des données, les lignes qui correspondent aux critères repris dans la table de critères (cf. ci-dessous)



explication des critères :

Critère 1 : si Champ1 = 0 alors colonne [Z] = "X"
Critère 2 : si Champ1 est Null alors colonne [Z] = "X"
Critère 3 : si Champ2 <0 et Champ3 >2 alors colonne [Z] = "X"
...etc

Ces critères doivent s'appliquer selon l'ordre indiqué.

Cette table de critères est dynamique (tant au niveau colonnes que lignes).

Les intitulés des colonnes de la table des critères correspondent aux champs existant dans la table des données.

La table des données contient plusieurs champs (disons que c'est une plage de données [A1:Z2000] qui se situent dans la feuil1 du classeur)
La table des critères se situe dans la feuil2 du classeur

Quelle est la meilleure solution optimisée qui permet d'automatiser ce traitement?

Merci pour votre aide

[Promethee25]

Bonjour,

Etant donné que tu sembles vouloir utiliser du VBA, pourquoi ne pas reconstruire une formule Excel sur la base de tes critères (en construisant une string contenant la formule) puis l'exécuter avec Evaluate?

[casavba]

Bonsoir,
Merci pour votre proposition.
Etant donné que la table de critères est dynamique tant au niveau colonnes que lignes, comment peut-on optimiser la construction du string de la formule? Pourrais-tu illustrer ta proposition?

Je tiens à préciser que je ne souhaite pas nécessairement passer par du VBA, si une autre solution optimale est possible. Donc je suis ouvert à toutes les propositions possibles (vba ou non ou mix des deux)

[Promethee25]

Bonjour,

Utiliser (comme toujours) un tableau structuré et passer par un "For each" pour parcourir toutes les lignes et un For each imbriqué pour parcourir toutes les colonnes.
Il faut que les entêtes de colonne aient idéalement exactement le bon nom.

Dans la concaténation progressive construite via ces boucles, tu ajoutes les IF et les AND qui vont bien au fur et à mesure (Evaluate veut une formule en anglais).

[tototiti2008]

Bonjour,

Bonjour,
Merci pour cette proposition. La formule matricielle dans l'onglet critères affiche {=TABLE(;I2)} qui n'est pas reconnue dans ma version Excel. Et je ne vois nulle part la fonction BDNBVAL dans votre exemple.
Pourriez-vous, je vous prie, nous expliquer cette solution intéressante? cela permettra aux membres de forum d'en bénéficier aussi.
Merci

Dans le classeur Test Criteres, Feuille Criteres, la fonction BDNBVAL est en cellule H5

Pour l'explication en détail :
Les fonctions de base de données (BDSOMME, BDMAX, BDMIN... etc) peuvent utiliser naturellement les zones de critères comme dans la plage nommée Crit du classeur exemple, mais elles ne peuvent pas s'appliquer à une seule ligne d'une base de données (liste)
En effet, elles s'écrivent toutes sur le modèle =BDNBVAL(Liste;NumeroColonne;ZonedeCriteres) et doivent donc recevoir la liste entière (avec la ligne de titre)

L'idée était donc de se faire une fausse liste qui n'irait chercher que les données d'une seule ligne
C'est ce que j'ai construit de I1 à X2
J'ai la ligne de titre (car obligatoire) puis la ligne du dessous qui va lire les données en fonction du numéro de ligne tapé en I2 avec des fonctions INDEX dans la feuille Données

Puis maintenant il faudrait en quelque sorte faire "varier" le contenu de I2, pour obtenir le résultat pour la ligne voulue

C'est là qu'intervient l'outil table de données (Onglet Données - analyse scénarios - Table de données)
Les table de données permettent de simuler la variation d'un paramètre (ou jusqu'à 2 mais ici 1 suffit) dans une formule et de nous donner le résultat
Elles créent ces formules matricielles =TABLE(), pas très modifiable après coup et plutôt gourmandes en terme de ressources (au point que dans les options de calcul il y a "automatique sauf les table de données")

Pour ma table de données il faut :
1) écrire ma formule, ici je l'avait fait en H5
=BDNBVAL($J$1:$X$2;1;Crit)
Signifie compte moi les lignes de la colonne 1 de la liste de J1 à X2 qui correspondent aux critères de la plage Crit
Attention BDNBVAL ne compte pas les cellules vides, je pars du principe que la colonne 1 est toujours remplie

2) En dessous, dans la colonne précédente (G), je dois taper les valeurs que va prendre mon paramètre (ici tous les numéros de lignes de la base de données)
3) Sélectionner de G5 à H28 (de la gauche de la formule jusqu'au niveau du dernier paramètre en colonne H)
4) Onglet Données - Analyse scénarios - Table de données
Cellule d'entrée en colonne : I2
(ça veut dire que c'est la valeur de I2 qui sera remplacée dans la formule BDNBVAL par les paramètres de la table)
OK

Après j'avais juste fait un lien dans la feuille Données, qui aurait sans doute été plus propre avec une RECHERCHEV ou X

en espérant que ça clarifie les choses

[Thumb down]

Bonjour,
Je n'ai pas compris la finalité du truc, mais selon moi tu réinventes le filtre élaboré qui ce trouve être natif dans excel.

[casavba]

Bonjour,
L'objectif étant d'avoir une routine agile qui me permettra à travers les critères repris dans la table de paramétrage de flagger les lignes de la base de données qui répondent à ces dits critères.
Le filtre avancé n'est pas la solution recherchée.

[tototiti2008]

Bonsoir,

Le soucis c'est que ça demande de recoder des outils qui existent dans Excel dans les filtres avancés ou les fonctions de base de données, aucune ne correspondant exactement à ton besoin ici
Ce n'est pas si simple car, comme indiqué par Prométhée

Evaluate veut une formule en anglais

Ce qui sous-entend de traduire les critères en anglais (décimales point, dates format américain...)

c'est embêtant car parfois la valeur sera précédée d'un opérateur de comparaison (=,>,<,>=,<=,<>) et donc qu'il faut découper le critère en opérateur/valeur

enfin il faudra détecter l'utilisation de caractères génériques (*, ?) et essayer de le traduire par des critères qu'on puisse intégrer dans un AND (ET) ce qui demandera encore une analyse fine

Edit : Bref en résumé ça serait peut-être plus simple de ne pas passer par Evaluate et d'essayer de recoder les tests directement en VBA

[Thumb down]

Voilà avec un filtre élaboré

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
 
Sub FiltrerEtFlaguer()
    Dim wsData As Worksheet, wsCrit As Worksheet
    Dim lastRowData As Long, i As Long
 
    Set wsData = Worksheets("Feuil1")
    Set wsCrit = Worksheets("Feuil2")
 
    ' Supprimer les anciens filtres
    wsData.AutoFilterMode = False
 
    ' Dernière ligne des données
    lastRowData = wsData.Cells(wsData.Rows.Count, 1).End(xlUp).Row
 
    ' Appliquer le filtre avancé directement sur la même feuille
    wsData.Range("A1:Z" & lastRowData).AdvancedFilter _
        Action:=xlFilterInPlace, _
        CriteriaRange:=wsCrit.Range("A1").CurrentRegion, _
        Unique:=False
 
    ' Flaguer les lignes visibles
    For i = 2 To lastRowData
        If Not wsData.Rows(i).Hidden Then
            wsData.Cells(i, 26).Value = "X"
        Else
            wsData.Cells(i, 26).Value = "" ' Optionnel : nettoyer
        End If
    Next i
 
    ' Nettoyer le filtre
    wsData.ShowAllData
 
    MsgBox "Filtrage terminé et lignes flaguées."
End Sub

Notes qu'il faut une valeur valide dans champ1.
=0 si la valeur a filtrer doit être égale à zéro
= Sans rien d'autre si la valeur doit être égale à null.

Dans un filtre élaboré ou filtre avancé les colonnes représente des AND et le lignes de OR

Colonne 1 = champ1
Colonne2 = champ1
Champ1|champ1
>1 |<10
Filtre champ1>1 and champ1<10

[Raccourcix]

Bonjour

Voici une solution par formule avec BYROW et MAKEARRAY

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
=SIERREUR(
 EQUIV(
   VRAI;
   BYROW(
     MAKEARRAY(
       LIGNES(Critères);
       COLONNES(Critères);
       LAMBDA(i;j;SI(
           ESTVIDE(INDEX(Critères;i;j));
           VRAI;
           NB.SI(INDEX(Données[@];j);INDEX(Critères;i;j))=1)));
     ET);
   0);
 "")

MAKEARRAY pour créer un tableau du même nombre de lignes et de colonnes que la table "Critères"
la valeur de chaque cellule de ce tableau est VRAI si la condition est vide ou si la valeur de ma données rempli la condition (test avec NB.SI = 1) l'intérêt du NB.SI est qu'il autorise les comparateurs >, <

ensuite par ligne du MAKEARRAY on teste que toutes les colonnes de ma ligne étudiée remplissent la condition avec le BYROW(... ; ET)
le EQUIV(VRAI;....;0) renverra la position du 1er VRAI et donc l'index de la première condition qui est vérifiée



si on veut simplement un X si n'importe quelle condition est valide on modifie légèrement la formule avec un OU

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
=SI(
  OU(
   BYROW(
     MAKEARRAY(
       LIGNES(Critères);
       COLONNES(Critères);
       LAMBDA(i;j;SI(
           ESTVIDE(INDEX(Critères;i;j));
           VRAI;
           NB.SI(INDEX(Données[@];j);INDEX(Critères;i;j))=1)));
     ET));
 "X";
 "")

Stéphane

[casavba]

Bonjour,
La solution proposée par Thumb down que je remercie au passage, fonctionne correctement. Je viens de la tester avec quelques amendements et celle-ci flagge correctement les lignes répondant aux critères spécifiées.

En revanche, la solution proposée par Stéphane est aussi intéressante dans sa construction mais ne fonctionne pas correctement. Excel affiche une erreur de fonction et ne flagge aucune ligne dans l'exemple fourni.

[Thumb down]

Bonjour,
La solution proposée par Thumb down que je remercie au passage, fonctionne correctement...

Bonsoir,
A ton service, ravie d'avoir été utile.

[Raccourcix]

Bonjour,
...

En revanche, la solution proposée par Stéphane est aussi intéressante dans sa construction mais ne fonctionne pas correctement. Excel affiche une erreur de fonction et ne flagge aucune ligne dans l'exemple fourni.

Bonsoir
Quelle est la version d'excel utilisée ?
Et quelle erreur ? #NOM ?
Stephane

[casavba]

Bonsoir
Quelle est la version d'excel utilisée ?
Et quelle erreur ? #NOM ?
Stephane

Bonjour,

Excel 365.
L'erreur est : #NOM?

[tototiti2008]

Bonjour,

un essai avec une fonction BDNBVAL et une table de données (outil de simulation)

[casavba]

Bonjour,

un essai avec une fonction BDNBVAL et une table de données (outil de simulation)

Bonjour,
Merci pour cette proposition. La formule matricielle dans l'onglet critères affiche {=TABLE(;I2)} qui n'est pas reconnue dans ma version Excel. Et je ne vois nulle part la fonction BDNBVAL dans votre exemple.
Pourriez-vous, je vous prie, nous expliquer cette solution intéressante? cela permettra aux membres de forum d'en bénéficier aussi.
Merci