Rechercher et récupérer toute la ligne
Pour rechercher et récupérer une ligne entière de données en faisant correspondre une valeur spécifique, vous pouvez utiliser les fonctions INDEX et EQUIV pour créer une formule matricielle.
Rechercher et récupérer une ligne entière sur la base d'une valeur spécifique
Additionner une ligne entière sur la base d'une valeur spécifique
Analyse approfondie d'une ligne entière sur la base d'une valeur spécifique
Rechercher et récupérer une ligne entière sur la base d'une valeur spécifique
Pour obtenir la liste des ventes réalisées par Jimmy selon le tableau ci-dessus, vous pouvez d'abord utiliser la fonction EQUIV pour retourner la position des ventes réalisées par Jimmy, qui sera ensuite transmise à INDEX pour récupérer les valeurs à cette position.
Syntaxe générique
=INDEX(plage_retour,EQUIV(valeur_recherche,plage_recherche,0),0)
√ Remarque : Il s'agit d'une formule matricielle qui nécessite d'être validée avec Ctrl + Maj + Entrée.
- plage_retour: La plage qui contient toute la ligne que vous souhaitez retourner. Ici, cela fait référence à la plage des ventes.
- valeur_recherche: La valeur utilisée par la formule combinée pour trouver les informations de vente correspondantes. Ici, cela fait référence au vendeur donné.
- plage_recherche: La plage de cellules où faire correspondre la valeur_recherche. Ici, cela fait référence à la plage des noms.
- type_correspondance 0: Force EQUIV à trouver la première valeur exactement égale à la valeur_recherche.
Pour obtenir la liste des ventes réalisées par Jimmy, veuillez copier ou entrer la formule ci-dessous dans la cellule I6, appuyez sur Ctrl + Maj + Entrée, puis double-cliquez sur la cellule et appuyez sur F9 pour obtenir le résultat :
=INDEX(C5:F11,EQUIV("Jimmy",B5:B11,0),0)
Ou, utilisez une référence de cellule pour rendre la formule dynamique :
=INDEX(C5:F11,EQUIV(I5,B5:B11,0),0)
Explication de la formule
=INDEX(C5:F11,EQUIV(I5,B5:B11,0),0)
- EQUIV(I5,B5:B11,0): Le type_correspondance 0 force la fonction EQUIV à retourner la position de Jimmy, la valeur dans I5, dans la plage B5:B11, qui est 7 puisque Jimmy est à la 7ème position.
- INDEX(C5:F11C5:F11,EQUIV(I5,B5:B11,0),0) = INDEX(C5:F11C5:F11,7,0): La fonction INDEX retourne toutes les valeurs de la 7ème ligne de la plage C5:F11 sous forme de tableau comme ceci : {6678,3654,3278,8398}. Notez qu'il faut double-cliquer sur la cellule où vous avez saisi la formule, puis appuyer sur F9 pour rendre le tableau visible dans Excel.
Additionner une ligne entière sur la base d'une valeur spécifique
Maintenant que nous avons toutes les informations de vente réalisées par Jimmy, pour obtenir le volume de ventes annuel réalisé par Jimmy, nous pouvons simplement ajouter la fonction SOMME à la formule pour additionner toutes les valeurs de vente de la liste.
Syntaxe générique
=SOMME(INDEX(plage_retour,EQUIV(valeur_recherche,plage_recherche,0),0))
Dans cet exemple, pour obtenir le volume de ventes annuel réalisé par Jimmy, veuillez copier ou entrer la formule ci-dessous dans la cellule I7, et appuyez sur Entrée pour obtenir le résultat :
=SOMME(INDEX(C5:F11,EQUIV(I5,B5:B11,0),0))
Explication de la formule
=SOMME(INDEX(C5:F11,EQUIV(I5,B5:B11,0),0))
- EQUIV(I5,B5:B11,0): Le type_correspondance 0 force la fonction EQUIV à retourner la position de Jimmy, la valeur dans I5, dans la plage B5:B11, qui est 7 puisque Jimmy est à la 7ème position.
- INDEX(INDEX(C5:F11C5:F11,,EQUIV(I5,B5:B11,0),0),0) = INDEX(INDEX(C5:F11C5:F11,,7,0),0): La fonction INDEX retourne toutes les valeurs de la 7ème ligne de la plage C5:F11 sous forme de tableau comme ceci : {6678,3654,3278,8398}. Notez qu'il faut double-cliquer sur la cellule où vous avez saisi la formule, puis appuyer sur F9 pour rendre le tableau visible dans Excel.
- SOMME(INDEX(C5:F11,EQUIV(I5,B5:B11,0)),0) = SOMME({6678,3654,3278,8398}): La fonction SOMME additionne toutes les valeurs du tableau, puis obtient le volume de ventes annuel réalisé par Jimmy, $22,008.
Analyse approfondie d'une ligne entière sur la base d'une valeur spécifique
Pour un traitement supplémentaire des ventes réalisées par Jimmy, vous pouvez simplement ajouter d'autres fonctions telles que SOMME, MOYENNE, MAX, MIN, GRAND, etc. à la formule.
Par exemple, pour obtenir la moyenne des ventes trimestrielles de Jimmy, vous pouvez utiliser la formule suivante :
=MOYENNE(INDEX(C5:F11,EQUIV(I5,B5:B11,0),0))
Pour connaître les ventes les plus basses réalisées par Jimmy, utilisez l'une des formules ci-dessous :
=MIN(INDEX(C5:F11,EQUIV(I5,B5:B11,0),0))
OU
=PETITE.VALEUR(INDEX(C5:F11,EQUIV(I5,B5:B11,0),0),1)
Fonctions connexes
La fonction INDEX d'Excel renvoie la valeur affichée en fonction d'une position donnée dans une plage ou un tableau.
La fonction EQUIV d'Excel recherche une valeur spécifique dans une plage de cellules et renvoie la position relative de cette valeur.
Formules connexes
Rechercher et récupérer une colonne entière
Pour rechercher et récupérer une colonne entière en faisant correspondre une valeur spécifique, une formule INDEX et EQUIV vous rendra service.
Correspondance exacte avec INDEX et EQUIV
Si vous devez trouver des informations répertoriées dans Excel sur un produit, un film ou une personne spécifique, etc., vous devriez bien utiliser la combinaison des fonctions INDEX et EQUIV.
Correspondance approximative avec INDEX et EQUIV
Il arrive que nous devions trouver des correspondances approximatives dans Excel pour évaluer la performance des employés, noter les scores des étudiants, calculer les frais de port en fonction du poids, etc. Dans ce tutoriel, nous parlerons de la manière d'utiliser les fonctions INDEX et EQUIV pour récupérer les résultats dont nous avons besoin.
Vous savez peut-être que vous pouvez combiner les fonctions INDEX et EQUIV, ou utiliser la fonction RECHERCHEV pour rechercher des valeurs dans Excel. Cependant, ces recherches ne sont pas sensibles à la casse. Ainsi, pour effectuer une correspondance sensible à la casse, vous devriez tirer parti des fonctions EXACT et CHOISIR.
Les meilleurs outils de productivité pour Office
Kutools pour Excel - Vous aide à vous démarquer de la foule
Kutools pour Excel compte plus de 300 fonctionnalités, garantissant que ce dont vous avez besoin n'est qu'à un clic...
Office Tab - Active la lecture et l'édition par onglets dans Microsoft Office (y compris Excel)
- Une seconde pour basculer entre des dizaines de documents ouverts !
- Réduit des centaines de clics de souris pour vous chaque jour, dites adieu à la main de la souris.
- Augmente votre productivité de 50 % lors de la consultation et de l'édition de plusieurs documents.
- Apporte des onglets efficaces à Office (y compris Excel), tout comme Chrome, Edge et Firefox.