Examinez cette figure:
Supposons que vous souhaitiez produire un rapport à partir de cela comme si vous aviez filtré sur la région. Autrement dit, si vous filtrez sur Nord, vous verrez:
Mais que faire si vous vouliez une version basée sur une formule de la même chose?
Voici le résultat que vous recherchez dans les colonnes I: K:
Clairement, c'est le même rapport, mais il n'y a pas d'éléments filtrés ici. Si vous vouliez un nouveau rapport sur l'Est, ce serait bien de simplement changer la valeur de G1 en Est:
Voici comment procéder. Tout d'abord, cela ne se fait pas à l'aide de RECHERCHEV. J'ai donc menti sur le titre de cette technique!
La colonne F n'était pas affichée auparavant et elle peut être masquée (ou déplacée ailleurs pour ne pas interférer avec le rapport).
Ce qui est indiqué dans la colonne F, ce sont les numéros de ligne où se trouve G1 dans la colonne A; autrement dit, quelles lignes contiennent la valeur «Nord»? Cette technique consiste à utiliser au- dessus de la cellule, il doit commencer au moins la ligne 2. Il correspond à la valeur « Nord » contre la colonne A, mais au lieu de toute la colonne, utilisez une fonction OFFSET: OFFSET($A$1,F1,0,1000,1).
Puisque F1 est 0, OFFSET(A1,0,0,1000,1)c'est A1: A1000. (Le 1000 est arbitraire, mais assez grand pour faire le travail - vous pouvez en faire n'importe quel autre nombre).
La valeur 2 dans F2 est l'endroit où se trouve le premier «Nord». Vous souhaitez également rajouter la valeur de F1 à la fin, mais c'est zéro, jusqu'à présent.
La «magie» prend vie dans la cellule F3. Vous savez déjà que le premier nord se trouve dans la ligne 2. Vous voulez donc commencer à rechercher deux lignes sous A1. Vous pouvez le faire en spécifiant 2 comme deuxième argument de la fonction OFFSET.
La formule en F3 pointera automatiquement 2 qui a été calculé dans la cellule F2: Lorsque vous copiez la formule vers le bas, vous verrez =OFFSET($A$1,F2,0,1000,1)qui est ce OFFSET($A$1,2,0,1000,1)qui est A3: A1000. Vous faites donc correspondre le nord à cette nouvelle plage et il trouve le nord dans la troisième cellule de cette nouvelle plage, donc le MATCH donne 3.
En rajoutant la valeur de la cellule ci-dessus, F2, vous verrez le 3 plus le 2 ou le 5, qui est la ligne qui contient le deuxième nord.
Cette formule est suffisamment remplie pour obtenir toutes les valeurs.
Cela vous donnera les numéros de ligne où se trouvent tous les enregistrements du Nord.
Comment traduisez-vous ces numéros de ligne en résultats dans les colonnes I à K? Tout est fait avec une seule formule. Entrez dans cette formule I2: =IFERROR(INDEX(A:A,$F2),””). Copiez à droite, puis copiez vers le bas.
Pourquoi utiliser IFERROR? Où est l'erreur? Remarquez la cellule F6 - elle contient # N / A (c'est pourquoi vous voudriez masquer la colonne F) car il n'y a plus de Nord après la ligne 15. Donc, si la colonne F est une erreur, retournez un blanc. Sinon, prenez la valeur de la colonne A (et lorsqu'elle est remplie à droite, B & C).
Le $ F2 est une référence absolue à la colonne F donc le remplissage à droite fait toujours référence à la colonne F.
Cet article invité provient d'Excel MVP Bob Umlas. C'est l'une de ses techniques préférées de son livre, Excel Outside the Box.
Excel en dehors de la boîte »
