Formule générique
=FILTER(list1,COUNTIF(list2,list1))
Sommaire
Pour filtrer les données afin d'extraire les valeurs correspondantes dans deux listes, vous pouvez utiliser la fonction FILTER et la fonction COUNTIF ou COUNTIFS. Dans l'exemple illustré, la formule en F5 est:
=FILTER(list1,COUNTIF(list2,list1))
où list1 (B5: B16) et list2 (D5: D14) sont des plages nommées. Le résultat renvoyé par FILTER inclut uniquement les valeurs de list1 qui apparaissent dans list2 .
Remarque: FILTER est une nouvelle fonction de tableau dynamique dans Excel 365.
Explication
Cette formule s'appuie sur la fonction FILTER pour récupérer des données sur la base d'un test logique construit avec la fonction COUNTIF:
=FILTER(list1,COUNTIF(list2,list1))
fonctionnant de l'intérieur vers l'extérieur, la fonction COUNTIF est utilisée pour créer le filtre réel:
COUNTIF(list2,list1)
Notez que nous utilisons list2 comme argument de plage et list1 comme argument de critère. En d'autres termes, nous demandons à COUNTIF de compter toutes les valeurs de list1 qui apparaissent dans list2. Parce que nous donnons plusieurs valeurs à COUNTIF pour les critères, nous récupérons un tableau avec plusieurs résultats:
(1;1;0;1;0;1;0;0;1;0;1;1)
Notez que le tableau contient 12 comptes, un pour chaque valeur de list1 . Une valeur nulle indique une valeur dans list1 qui ne se trouve pas dans list2 . Tout autre nombre positif indique une valeur dans list1 qui se trouve dans list2 . Ce tableau est renvoyé directement à la fonction FILTER comme argument d'inclusion:
=FILTER(list1,(1;1;0;1;0;1;0;0;1;0;1;1))
La fonction de filtre utilise le tableau comme filtre. Toute valeur de list1 associée à un zéro est supprimée, tandis que toute valeur associée à un nombre positif survit.
Le résultat est un tableau de 7 valeurs correspondantes qui se répandent dans la plage F5: F11. Si les données changent, FILTER recalculera et renverra une nouvelle liste de valeurs correspondantes en fonction des nouvelles données.
Valeurs non concordantes
Pour extraire les valeurs non correspondantes de list1 (c'est-à-dire les valeurs de list1 qui n'apparaissent pas dans list2 ), vous pouvez ajouter la fonction NOT à la formule comme ceci:
=FILTER(list1,NOT(COUNTIF(list2,list1)))
La fonction NOT inverse effectivement le résultat de COUNTIF - tout nombre différent de zéro devient FALSE et toute valeur zéro devient VRAI. Le résultat est une liste des valeurs de list1 qui ne sont pas présentes dans list2 .
Avec INDEX
Il est possible de créer une formule pour extraire les valeurs correspondantes sans la fonction FILTER, mais la formule est plus complexe. Une option consiste à utiliser la fonction INDEX dans une formule comme celle-ci:
La formule dans G5, copiée vers le bas est:
=IFERROR(INDEX(list1,SMALL(IF(COUNTIF(list2,list1),ROW(list1)-ROW(INDEX(list1,1,1))+1),ROWS($F$5:F5))),"")
Remarque: il s'agit d'une formule matricielle et doit être saisie avec Ctrl + Maj + Entrée, sauf dans Excel 365.
Le cœur de cette formule est la fonction INDEX, qui reçoit list1 comme argument de tableau. La plupart des formules restantes calculent simplement le numéro de ligne à utiliser pour les valeurs correspondantes. Cette expression génère une liste de numéros de ligne relatifs:
ROW(list1)-ROW(INDEX(list1,1,1))+1
qui renvoie un tableau de 12 nombres représentant les lignes de list1 :
(1;2;3;4;5;6;7;8;9;10;11;12)
Ceux-ci sont filtrés avec la fonction IF et la même logique utilisée ci-dessus dans FILTER, basée sur la fonction COUNTIF:
COUNTIF(list2,list1) // find matching values
Le tableau résultant ressemble à ceci:
(1;2;FALSE;4;FALSE;6;FALSE;FALSE;9;FALSE;11;12) // result from IF
Ce tableau est livré directement à la fonction SMALL, qui est utilisée pour récupérer le numéro de ligne correspondant suivant lorsque la formule est copiée dans la colonne. La valeur k pour SMALL (pensez nième) est calculée avec une plage en expansion:
ROWS($G$5:G5) // incrementing value for k
La fonction IFERROR est utilisée pour intercepter les erreurs qui se produisent lorsque la formule est copiée et manque de valeurs correspondantes. Pour un autre exemple de cette idée, voyez cette formule.
