Au tableur : du fichier au tableau croisé

Énoncé

Un fichier de données a une ligne par candidat : la voie en colonne A, le résultat en colonne B. En voici un extrait fictif de douze lignes (lignes 2 à 13). | Ligne | A (voie) | B (résultat) | |:---:|:---:|:---:| | 2 | Générale | Admis | | 3 | Professionnelle | Admis | | 4 | Générale | Admis | | 5 | Technologique | Non admis | | 6 | Générale | Admis | | 7 | Professionnelle | Non admis | | 8 | Technologique | Admis | | 9 | Générale | Non admis | | 10 | Professionnelle | Admis | | 11 | Générale | Admis | | 12 | Technologique | Admis | | 13 | Professionnelle | Admis | On construit le tableau croisé dans les colonnes D à G : les voies en D2, D3, D4, les résultats « Admis » et « Non admis » en E1 et F1, les totaux en colonne G et en ligne 5. a) Quelle formule écrire en E2 pour compter les candidats de la voie générale admis ? Quelle formule en F4 ? b) Compléter, en comptant à la main, le tableau croisé que le tableur doit afficher. Cela permet de vérifier les formules. c) Quelles formules écrire en G2 et en E5 ? d) Un élève écrit en E2 la formule « =NB.SI(A2:A13;"Générale") ». Que compte-t-elle ? e) Comment obtenir, en H2, la fréquence des admis parmi les candidats de la voie générale ?

Indice : NB.SI compte selon UNE condition ; NB.SI.ENS compte les lignes qui vérifient TOUTES les conditions données, par paires « plage ; critère ».

Correction

  1. Étape 1 : Un tableau croisé compte les individus qui vérifient deux conditions à la fois : une sur la voie, une sur le résultat. Il faut donc une fonction qui compte selon plusieurs critères. La fonction NB.SI.ENS : « =NB.SI.ENS(plage1;critère1;plage2;critère2) » compte les lignes où la plage 1 vérifie le critère 1 et la plage 2 le critère 2.
  2. Étape 2 : a) En E2 (voie générale, admis) : « =NB.SI.ENS(A2:A13;"Générale";B2:B13;"Admis") ». En F4 (voie professionnelle, non admis) : « =NB.SI.ENS(A2:A13;"Professionnelle";B2:B13;"Non admis") ». Les critères sont du texte, donc entre guillemets, et doivent être écrits exactement comme dans le fichier : « Generale » sans accent ne trouverait rien.
  3. Étape 3 : b) Voie générale : lignes 2, 4, 6, 9 et 11, dont une seule non admise (ligne 9). Voie technologique : lignes 5, 8 et 12, dont une non admise (ligne 5). Voie professionnelle : lignes 3, 7, 10 et 13, dont une non admise (ligne 7). | | Admis | Non admis | Total | |---|:---:|:---:|:---:| | Générale | 4 | 1 | 5 | | Technologique | 2 | 1 | 3 | | Professionnelle | 3 | 1 | 4 | | Total | 9 | 3 | 12 | Contrôle : 5 + 3 + 4 = 12 et 9 + 3 = 12, le nombre de lignes du fichier.
  4. Étape 4 : c) En G2, le total de la ligne « Générale » : « =SOMME(E2:F2) ». En E5, le total de la colonne « Admis » : « =SOMME(E2:E4) ». On recopie ensuite ces formules vers le bas ou vers la droite : le tableur décale les références automatiquement.
  5. Étape 5 : d) « =NB.SI(A2:A13;"Générale") » ne porte qu'une condition, sur la voie : elle compte tous les candidats de la voie générale, admis ou non, soit 5. C'est le total de la ligne, pas la case : il manque la condition sur le résultat.
  6. Étape 6 : e) On divise la case par le total de sa ligne : « =E2/G2 », qui affiche 0,8, soit 80 % des candidats de la voie générale admis. Pour recopier cette formule vers le bas, la colonne G doit rester fixe dans chaque ligne : c'est le cas, puisque la recopie vers le bas ne change que le numéro de ligne.

    4/5 = 0,8

  7. Étape 7 : Une condition de moins, et on lit la marge : NB.SI avec la seule condition sur la voie rend 5 au lieu de 4 : on a compté la ligne entière. Le tableau semble plausible, mais toutes ses cases sont des totaux. Vérifier une formule sur un petit fichier : Avant de lancer une formule sur un fichier de mille lignes, teste-la sur une dizaine de lignes que tu peux compter à la main. Si les deux résultats coïncident, la formule est juste ; et le double contrôle des marges fait le reste.