ESTVIDE, SIERREUR, RANG et fonctions statistiques
Quatre familles de fonctions pour fiabiliser un tableau. Les fonctions EST testent le contenu d’une cellule, SIERREUR remplace une erreur par un message propre, RANG classe les valeurs, et les fonctions statistiques résument une série. Chaque notion se manipule dans un simulateur, sur les résultats TOSA d’un groupe de stagiaires.
Les fonctions ESTVIDE, ESTNUM, ESTTEXTE, ESTFORMULE
Les fonctions EST (fonctions d’information) posent une question sur une cellule et répondent VRAI ou FAUX. Elles ne calculent rien : elles servent de condition dans un SI, une mise en forme conditionnelle ou une validation de données.
| Fonction | Renvoie VRAI si la cellule… | Exemple d’usage |
|---|---|---|
ESTVIDE(valeur) | est réellement vide (ni espace, ni formule) | =SI(ESTVIDE(B2);"Absent";B2) |
ESTNUM(valeur) | contient un nombre, une date ou une heure | Repérer les nombres stockés en texte |
ESTTEXTE(valeur) | contient du texte (y compris '42 ou un espace) | =SI(ESTTEXTE(B2);"Saisie à corriger";"") |
ESTFORMULE(référence) | contient une formule (Excel 2013 et plus) | Mise en forme conditionnelle qui colore les cellules calculées |
ESTNONTEXTE, ESTLOGIQUE | ne contient pas de texte / contient VRAI ou FAUX | Contrôles de saisie |
ESTERREUR, ESTERR, ESTNA | contient une erreur / une erreur sauf #N/A / exactement #N/A | Gérer les erreurs au cas par cas |
Choisissez une fonction : la colonne C affiche son résultat pour chaque cellule de la colonne B.
"" paraît vide, mais ESTVIDE répond FAUX. Pour tester « vide ou vide apparent », utilisez =B2="".ESTNUM répond VRAI. Un nombre aligné à gauche avec un triangle vert est souvent du texte : ESTTEXTE le confirme.La fonction SIERREUR()
=SIERREUR(valeur;valeur_si_erreur) calcule la valeur ; si le résultat est une erreur (#DIV/0!, #N/A, #VALEUR!, #REF!, #NOM?, #NOMBRE!, #NUL!), elle renvoie valeur_si_erreur à la place.
Sans SIERREUR, les divisions par zéro ou par une cellule vide et le texte « N.C. » produisent des erreurs, et la moyenne en D9 est elle-même en erreur.
"", que MOYENNE ignore.SI.NON.DISP(valeur;valeur_si_na) ne traite que #N/A (recherche infructueuse) et laisse voir les autres erreurs. RECHERCHEX a même son propre argument si_non_trouvé.La fonction RANG()
=RANG.EQ(nombre;référence;[ordre]) renvoie la position d’un nombre dans une liste. ordre omis ou 0 : décroissant (le plus grand est 1er) ; 1 : croissant. La référence doit être absolue pour recopier la formule : =RANG.EQ(B2;$B$2:$B$13).
| Fonction | En cas d’égalité | Exemple : trois scores à 720 |
|---|---|---|
RANG.EQ (et l’ancienne RANG) | Même rang pour tous, le rang suivant est sauté | 6, 6, 6 puis 9 |
RANG.MOYENNE | Moyenne des rangs occupés | 7, 7, 7 (moyenne de 6, 7 et 8) |
=RANG.EQ(B2;$B$2:$B$13)+NB.SI($B$2:B2;B2)-1 : le premier arrivé garde le meilleur rang.Les autres fonctions statistiques
Sur la même plage B2:B13, qui contient 10 scores, un texte « Abs » et une cellule vide, chaque fonction répond à une question différente. Modifiez le seuil et k pour voir les résultats changer.
ECARTYPE.STANDARD pour un échantillon (le cas courant), ECARTYPE.PEARSON pour une population entière.Exercice de synthèse : le bilan TOSA du groupe
Télécharger le fichier d’exercice (Énoncé et Corrigé)
- Feuille Contrôle : en C2:F12, appliquez ESTVIDE, ESTNUM, ESTTEXTE et ESTFORMULE à B2:B12. Le « 42 » en texte et le
=""sont les pièges. - Feuille Formations : en D2,
=SIERREUR(B2/C2;""), recopiez jusqu’en D8. En D9,=MOYENNE(D2:D8)→ 420 €. Refaites avec 0 : 240 €. - En G2,
=SIERREUR(RECHERCHEV(G1;A2:C8;2;FAUX);"Formation inconnue"). Tapez « Excell » puis « Excel » en G1. - Feuille Résultats : en C2,
=SIERREUR(RANG.EQ(B2;$B$2:$B$13);""). Chloé et Karim sont 1ers ex æquo, les trois 720 sont 6es. Avec RANG.MOYENNE : 1,5 et 7. - En G2:G13 : moyenne 773, médiane 750, mode 720, NB 10, NBVAL 11, NB.VIDE 1,
NB.SI(B2:B13;">=725")5, 3e meilleur score 880, écart type 110,58.