Formule générique
=IF(F5>ct,"",INDEX(data,AGGREGATE(15,6,(ROW(data)-ROW($B$5)+1)/ISNUMBER(SEARCH(search,data)),F5)))
Sommaire
Pour extraire toutes les correspondances basées sur une correspondance partielle, vous pouvez utiliser une formule matricielle basée sur les fonctions INDEX et AGGREGATE, avec la prise en charge de ISNUMBER et SEARCH. Dans l'exemple illustré, la formule en G5 est:
=IF(F5>ct,"",INDEX(data,AGGREGATE(15,6,(ROW(data)-ROW($B$5)+1)/ISNUMBER(SEARCH(search,data)),F5)))
avec les plages nommées suivantes: "search" = D5, "ct" = D8, "data" = B5: B55.
Remarque: il s'agit d'une formule matricielle, mais elle ne nécessite pas de contrôle + shift + entrée, car AGGREGATE peut gérer les tableaux de manière native.
Explication
Le cœur de cette formule est la fonction INDEX, avec AGGREGATE utilisé pour déterminer la "nième correspondance" pour chaque ligne de la zone d'extraction:
INDEX(data,nth_match_formula)
Presque tout le travail consiste à déterminer et à signaler quelles lignes de «données» correspondent à la chaîne de recherche et à signaler la position de pour chaque valeur correspondante à INDEX. Ceci est fait avec la fonction AGGREGATE configurée comme ceci:
AGGREGATE(15,6,(ROW(data)-ROW($B$5)+1)/ISNUMBER(SEARCH(search,data)),F5)
Le premier argument, 15, indique à AGGREGATE de se comporter comme SMALL et de renvoyer la nième plus petite valeur. Le deuxième argument, 6, est une option pour ignorer les erreurs. Le troisième argument est une expression qui génère un tableau de résultats correspondants (décrits ci-dessous). Le quatrième argument, F5, agit comme "k" dans SMALL pour spécifier la "nième" valeur.
AGGREGATE opère sur des tableaux, et l'expression ci-dessous construit un tableau pour le troisième argument dans AGGREGATE:
(ROW(data)-ROW($B$5)+1)/ISNUMBER(SEARCH(search,data))
Ici, la fonction ROW est utilisée pour générer un tableau de numéros de ligne relatifs, et ISNUMBER et SEARCH sont utilisés ensemble pour faire correspondre la chaîne de recherche aux valeurs des données, ce qui génère un tableau de valeurs TRUE et FALSE.
L'astuce consiste à diviser les numéros de ligne par les résultats de la recherche. Dans une opération mathématique comme celle-ci, TRUE se comporte comme 1 et FALSE se comporte comme zéro. Le résultat est que les numéros de ligne associés à une correspondance positive sont divisés par 1 et survivent à l'opération, tandis que les numéros de ligne associés à des valeurs non correspondantes sont détruits et deviennent des erreurs # DIV / 0. Étant donné que AGGREGATE est défini pour ignorer les erreurs, il ignore les erreurs # DIV / 0 et renvoie le "nième" plus petit nombre dans les valeurs restantes, en utilisant le nombre dans la colonne F pour "nième".
Gérer la performance
Comme toutes les formules matricielles, cette formule est «coûteuse» en termes de ressources avec un grand ensemble de données. Pour minimiser les impacts sur les performances, toute la formule INDEX et MATCH est enveloppée dans IF comme ceci:
=IF(F5>ct,"",formula)
où la plage nommée "ct" (D8) contient cette formule:
=COUNTIF(data,"*"&search&"*")
Cette vérification arrête la partie INDEX et AGGREGATE de la formule de s'exécuter une fois que toutes les valeurs correspondantes ont été extraites.
Array formule avec SMALL
Si votre version d'Excel ne dispose pas de la fonction AGGREGATE, vous pouvez utiliser une formule alternative basée sur SMALL et IF:
=IF(F5>ct,"",INDEX(data,SMALL(IF(ISNUMBER(SEARCH(search,data)),ROW(data)-ROW($B$5)+1),F5)))
Remarque: il s'agit d'une formule matricielle et doit être saisie avec Ctrl + Maj + Entrée.
