Valeurs de recherche et de retour dans une ligne/colonne entière dans Excel

RECHERCHEV est l’une des fonctions les plus utilisées dans Excel. Il recherche une valeur dans une plage et renvoie une valeur correspondante dans un numéro de colonne spécifié.

Maintenant, je suis tombé sur un problème où je devais rechercher une ligne entière et renvoyer les valeurs dans toutes les colonnes de cette ligne (au lieu de renvoyer une seule valeur).

Voici donc ce que je devais faire. Dans l’ensemble de données ci-dessous, j’avais les noms des représentants des ventes et les ventes qu’ils ont réalisées au cours des 4 trimestres de 2012. J’avais une liste déroulante avec leurs noms et je voulais extraire les ventes maximales de ce représentant des ventes au cours de ces quatre trimestres.

Rechercher une ligne/colonne entière

Je pourrais proposer 2 manières différentes de le faire – En utilisant INDEX ou VLOOKUP.

Rechercher une ligne/colonne entière à l’aide de la formule INDEX

Voici la formule que j’ai créée pour ce faire en utilisant Index

=LARGE(INDEX($B$4:$F$13,MATCH(H3,$B$4:$B$13,0),0),1)
Comment ça fonctionne:

Examinons d’abord la fonction INDEX qui est enveloppée à l’intérieur de la fonction LARGE.

=INDICE($C$4:$F$13,MATCH(H3,$B$4:$B$13,0),0)

Analysons de près les arguments de la fonction INDEX :

  • Tableau – $B$4 : $F$1
  • Numéro de ligne – MATCH(H3,$B$4:$B$13,0)
  • Numéro de colonne – 0

Notez que j’ai utilisé le numéro de colonne comme 0.

L’astuce ici est que lorsque vous utilisez le numéro de colonne comme 0, il renvoie toutes les valeurs dans toutes les colonnes. Donc, si je sélectionne John dans la liste déroulante, la formule d’indexation renverra les 4 valeurs de vente pour John {91064,71690,67574,25427}.

Maintenant, je peux utiliser la fonction Large pour extraire la plus grande valeur

Pro Tip - Use Column/Row number as 0 in Index formula to return all the values in Columns/Rows.

Rechercher une ligne/colonne entière à l’aide de la formule RECHERCHEV

Alors que la formule d’index est soignée, propre et robuste, la méthode VLOOKUP est un peu complexe. Cela finit également par rendre la fonction volatile. Cependant, il y a une astuce incroyable que je voudrais partager dans cette section. Voici la formule :

=LARGE(VLOOKUP(H3,B4:F13, ROW(INDIRECT("2:"&COUNTA($B$4:$F$4))), FALSE),1) 
Comment ça fonctionne
  • LIGNE(INDIRECT(“2 :”&COUNTA($B$4:$F$4))) – Cette formule renvoie un tableau {2;3;4;5}. Notez que puisqu’il utilise INDIRECT, cela rend cette formule volatile.
  • RECHERCHEV(H3,B4:F13,LIGNE(INDIRECT(“2:”&COUNTA($B$4:$F$4))),FAUX) – Voici la meilleure partie. Lorsque vous les assemblez, cela devient RECHERCHEV(H3,B4:F13,{2;3;4;5},FALSE). Notez maintenant qu’au lieu d’un seul numéro de colonne, je lui ai donné un tableau de numéros de colonne. Et RECHERCHEV recherche docilement les valeurs dans toutes ces colonnes et renvoie un tableau.
  • Maintenant, utilisez simplement GRAND fonction pour extraire la plus grande valeur.
Lis  Comment ajouter des mois à ce jour dans Excel (formule facile)

N’oubliez pas d’utiliser Contrôle + Maj + Entrée d’utiliser cette formule.

Pro Tip - In VLOOKUP, instead of using a single column number, if you use an array of column numbers, it will return an array of lookup values.

Vous aimerez peut-être aussi les tutoriels Excel suivants :

Pour protéger votre fils, votre mari ou votre espion, vous pouvez télécharger cette application

X
0 Shares
Tweet
Share
Share
Pin