Utilisation des fonctions "EQUIV" et "INDEX"

Pour cela nous allons utiliser la fonction EQUIV et INDEX : Voici la formule complète de la cellule V3 capable de calculer la note offensive de l’élève grâce à son rapport offensif (Cellule AK3) et sa comparaison au barème : =INDEX(AB4:AB21;EQUIV(AK3;Z4:Z21;-1)) Dans cette formule, EQUIV recherche la colonne où se trouve la performance recherchée. Dans ce cas précis, on compare le rapport trouvé en AK3 à la colonne Z (De la cellule Z4 à Z21) où se trouvent les rapports offensifs. Le programme va rechercher la valeur la plus approchante à AK3. On note -1 à la fin pour indiquer le type de recherche que l’on souhaite : • si on note 1, la fonction EQUIV trouve la valeur la plus élevée qui est inférieure ou égale à celle de la valeur_cherchée ; • si on note 0, la fonction EQUIV trouve la première valeur exactement équivalente à celle de la valeur_cherchée ; • si on note -1, la fonction EQUIV trouve la plus petite valeur qui est supérieure ou égale à celle de la valeur_cherchée. La fonction INDEX restitue la valeur contenue dans la cellule définie par les numéros de ligne et de colonne. Dans ce cas elle va restituer la note indiquée dans la colonne AB (De la cellule AB4 à AB21) en fonction de la recherche d’EQUIV. On utilise le même procédé pour la note défensive. La formule sera alors : =INDEX(AB4:AB21;EQUIV(AL3;AD4:AD21;1)) La fonction EQUIV se réfère au rapport défensif calculé en AL3 puis compare au barème dans la colonne AD. 1 en bout de formule indique un mode de recherche vers la valeur la plus élevée. La fonction Index va alors y attribuer une note en se référant à la colonne AB. ATTENTION Pour les 2 colonnes V et W, le fait de cliquer et glisser la formule pour l’adapter à l’ensemble de la colonne va poser un problème. On peut s’apercevoir dans la formule =INDEX(AB4:AB21;EQUIV(AL3;AD4:AD21;1)) que les parties AB4 :AB21 et AD4 :AD21 resteront les mêmes dans toutes les cellules puisque elles font référence au barème qui est le même pour tous les élèves. Par contre le nom AL3 qui fait référence à la perf de l’élève en ligne 3 lui changera pour l’élève de la ligne 4 et deviendra AL4, puis AL5 pour la ligne 5 etc…. Or si vous cliquez et glissez la formule, l’ensemble des chiffres de la formule va se modifier même ceux qui devaient rester fixes (barème). Exemple pour un « clic et glisse » de la ligne 3 à la ligne 4 on aura en ligne 4 : =INDEX(AB5:AB22;EQUIV(AL4;AD5:AD22;1)) alors que nous devrions avoir : =INDEX(AB4:AB21;EQUIV(AL4;AD4:AD21;1)). Seul AL3 aurait du changer en AL4. Dans ce cas préférez la solution copier coller et modifiez manuellement dans chaque cellule le chiffre après AL pour la note défensive et faites la même chose dans la colonne V pour la note offensive en modifiant le chiffre après AK. Astuces Un symbole d’erreur #N/A survient lorsqu’une valeur n’est pas disponible pour une fonction ou une formule, par exemple si aucune valeur n’a encore été inscrite. Comme pour le message d’erreur #DIV/0 !, nous allons rajouter au début de la formule : =SI(AH3=0 ; » » ;INDEX(AB4 :AB21 ;EQUIV(AK3 ;Z4 :Z21 ;-1))). Par conséquent si dans la colonne AH3 la valeur est égale à 0, on précise qu’on ne veut rien voir apparaître. Un didacticiel spécialisé dans l'utilisation des fonctions EQUIV et INDEX est également à votre disposition sur le site EPS académique dans la rubrique TICE et EPS, " les outils sur tableur " (Tutoriel pour réaliser une feuille de calcul avec recherche automatique du barème). Vous pouvez y accéder directement en cliquant sur le lien ci dessus.

 

Académie de Poitiers Courriel :  webEPS

Dernière mise à jour : 20-Oct-2006