Formule générique
=FILTER(list1,COUNTIF(list2,list1))
Sommaire
Pour comparer deux listes et extraire des valeurs communes, vous pouvez utiliser une formule basée sur les fonctions FILTER et COUNTIF. Dans l'exemple illustré, la formule en F5 est:
=FILTER(list1,COUNTIF(list2,list1))
où list1 (B5: B15) et list2 (D5: D13) sont des plages nommées. Le résultat, les valeurs qui apparaissent dans les deux listes, se répand dans la plage F5: F11.
Explication
La fonction FILTER accepte un tableau de valeurs et un argument "inclure" qui filtre le tableau en fonction d'une expression logique ou d'une valeur.
Dans ce cas, le tableau est fourni sous la forme de la plage nommée "list1", qui contient toutes les valeurs de B5: B15. L' argument include est fourni par la fonction COUNTIF, qui est imbriquée dans FILTER:
=FILTER(list1,COUNTIF(list2,list1))
COUNTIF est configuré avec list2 comme plage et list1 comme critère . Comme nous donnons à COUNTIF onze valeurs de critères, COUNTIF renvoie onze résultats dans un tableau comme celui-ci:
(1;1;0;1;0;1;0;1;0;1;1)
Notez que les 1 correspondent aux éléments de list2 qui apparaissent dans list1.
Ce tableau est livré directement à la fonction FILTER en tant qu'argument "include":
=FILTER(list1,(1;1;0;1;0;1;0;1;0;1;1))
La fonction FILTER filtre la liste1 en utilisant les valeurs fournies par COUNTIF. Les valeurs associées à zéro sont supprimées; les autres valeurs sont conservées.
Le résultat final est un tableau de valeurs qui existent dans les deux listes, qui se répand dans la plage F5: F11.
Logique étendue
Dans la formule ci-dessus, nous utilisons les résultats bruts de COUNTIF comme filtre. Cela fonctionne car Excel évalue toute valeur différente de zéro comme TRUE et zéro comme FALSE. Si COUNTIF renvoie un nombre supérieur à 1, le filtre fonctionnera toujours correctement.
Pour forcer explicitement les résultats TRUE et FALSE, vous pouvez utiliser "> 0" comme ceci:
=FILTER(list1,COUNTIF(list2,list1)>0)
Supprimer les doublons ou trier
Pour supprimer les doublons, imbriquez simplement la formule dans la fonction UNIQUE:
=UNIQUE(FILTER(list1,COUNTIF(list2,list1)))
Pour trier les résultats, imbriquez dans la fonction TRIER:
=SORT(UNIQUE(FILTER(list1,COUNTIF(list2,list1))))
Valeurs de liste manquantes dans list2
Pour afficher les valeurs de list1 absentes de list2, vous pouvez inverser la logique comme ceci:
=FILTER(list1,COUNTIF(list2,list1)=0)
