Formule Excel: extraire toutes les correspondances avec la colonne d'aide -

Table des matières

Formule générique

=IF(rowcheck,INDEX(data,MATCH(rownum,helper,0),column),"")

Sommaire

Une façon d'extraire plusieurs correspondances dans Excel consiste à utiliser INDEX et MATCH avec une colonne d'aide qui marque les données correspondantes. Cela évite la complexité d'une formule matricielle plus avancée. Dans l'exemple illustré, la formule en H6 est:

=IF($G6<=ct,INDEX(data,MATCH($G6,helper,0),1),"")

où ct (G3), data (B3: E52) et helper (E3: E52) sont des plages nommées.

Explication

Le défi avec les formules de recherche qui récupèrent plus d'une correspondance est la gestion des doublons (c'est-à-dire plusieurs correspondances). Les formules de recherche telles que RECHERCHEV et INDEX + MATCH peuvent facilement trouver la première correspondance, mais il est beaucoup plus difficile de rechercher «toutes les correspondances» lorsque les critères trouvent plus d'une correspondance.

Cette formule traite ce défi en utilisant une colonne d'assistance qui renvoie une valeur numérique qui peut être utilisée pour extraire facilement plusieurs correspondances. La formule dans la colonne d'assistance ressemble à ceci:

=SUM(E2,AND(C3=$I$3,D3=$J$3))

La colonne d'assistance teste chaque ligne des données pour voir si le service de la colonne C correspond à la valeur de I3 et le bâtiment de la colonne D correspond à la valeur de J3. Les deux tests logiques doivent renvoyer TRUE pour que AND renvoie TRUE.

Pour chaque ligne, le résultat de la fonction ET est ajouté à la «valeur ci-dessus» dans la colonne d'assistance pour générer un décompte. L'effet pratique de cette formule est un compteur incrémentiel qui ne change que lorsqu'une (nouvelle) correspondance est trouvée. Ensuite, la valeur reste la même jusqu'à ce que la correspondance suivante soit trouvée. Cela fonctionne car les résultats TRUE / FALSE retournés par AND sont forcés à des valeurs 1/0 dans le cadre de l'opération de somme. Les résultats FAUX n'ajoutent rien et les résultats VRAI ajoutent 1.

De retour dans la zone d'extraction, la formule de recherche pour le nom dans la colonne H ressemble à ceci:

=IF($G6<=ct,INDEX(data,MATCH($G6,helper,0),1),"")

En travaillant de l'intérieur vers l'extérieur, la partie INDEX + MATCH de la formule recherche le nom de la première correspondance trouvée, en utilisant le numéro de ligne de la colonne G comme valeur de correspondance:

INDEX(data,MATCH($G6,helper,0),1)

INDEX reçoit les 3 colonnes de données en tant que tableau (plage nommée "data"), et MATCH est configuré pour correspondre au numéro de ligne dans la colonne d'assistance (la plage nommée "helper") en mode de correspondance exacte (3ème argument mis à zéro) .

C'est là que l'intelligence de la formule devient évidente. La colonne d'assistance contient évidemment des doublons, mais cela n'a pas d'importance, car MATCH ne correspondra qu'à la première valeur. De par sa conception, chaque «première valeur» correspond à la ligne correcte dans la table de données.

Les formules des colonnes I et J sont les mêmes que H, à l'exception du numéro de colonne, qui est augmenté dans chaque cas de un.

L'instruction IF qui encapsule la formule INDEX / MATCH exécute une fonction simple - elle vérifie chaque numéro de ligne dans la zone d'extraction pour voir si le numéro de ligne est inférieur ou égal à la valeur de G3 (plage nommée "ct"), qui est le nombre total de tous les enregistrements correspondants. Si tel est le cas, la logique INDEX / MATCH est exécutée. Sinon, IF génère une chaîne vide ("").

La formule en G3 (plage nommée "ct") est simple:

=MAX(helper)

Étant donné que la valeur maximale dans la colonne d'assistance est la même que le nombre total de correspondances, la fonction MAX est tout ce dont nous avons besoin.

Remarque: la zone d'extraction doit être configurée manuellement pour gérer autant de données que nécessaire (c'est-à-dire 5 lignes, 10 lignes, 20 lignes, etc.). Dans cet exemple, il est limité à 5 lignes uniquement pour garder la feuille de calcul compacte.

J'ai appris cette technique dans le livre de Mike Girvin Control + Shift + Enter.