SOMME.SI, MOYENNE.SI...

1.1. Exercice

Récupérez les données du fichier repartition-age-effectif.csv et enregistrez une version au format ods (si vous utilisez Open ou Libre Office Calc) ou au format xlsx si vous utilisez Microsoft Excel.

1.2. Exercice complémentaire

Récupérez de nouveau les données du fichier repartition-age-effectif.csv en vous trompant volontairement de séparateur mais en utilisant un caractère non présent dans le fichier (par exemple la tabulation). Les données vont se placer dans une seule colonne. Utilisez ensuite l’outil « Convertir » (avec Microsoft Excel) ou « Texte en colonnes… » (avec Open Office ou Libre Office Calc).

1.3. Nettoyage des données

Les données ouvertes ne sont pas toujours cohérentes.

Par exemple, en plaçant un filtre, observez que dans la colonne « college », le collège « Exécution sédentaire » est orthographié « Exécution sédentaire » et « Exécution sédentaires ».

Avec un recherche/remplacement, remplacez toutes les occurrences de « Exécution sédentaires » par « Exécution sédentaire ».

Observez que les catégories d’âge ne sont pas toujours respectées dans nos données d'étude. Il existe des catégories d’âge « De 45 à 49 ans » et « 50 ans et plus » qui ne sont utilisées que pour le collège « Exécution roulant conduite ». Difficile ici de reconstituer les classes « De 45 à 54 ans » et « 55 ans et plus » pour avoir un jeu de données plus cohérents.

Exercice

Remplissez les différentes parties du tableau de bord.

L’ensemble des formules doivent pouvoir continuer à fonctionner et donner les mêmes résultats quel que soit le tri réalisé par la suite sur les données. Ci-suivent pour chaque zone des indications et suggestions complémentaires. Les nouvelles fonctions ne sont pas expliquées : utilisez l’assistant fonction ou l’aide en ligne (et consultez votre enseignant).

Conseil : dans la feuille des données, un volet a été placé. Utilisez-le. Si vous ne savez plus comment on le place. Révisez.

Consigne générale pour le format des cellules : affichez les nombres en utilisant le séparateur des milliers et sans chiffre après la virgule

Explications et suggestions

1. Tableau des effectifs par année (C4:C12) : utilisez la fonction SOMME.SI.
2. Rang (D4:D12) : calculez le rang de l'année dans le classement par ordre croissant d'effectif ; utilisez la fonction RANG.
3. Statistiques (moyenne, médiane, effectif min, effectif max, nb années – cellules C14:C17 et C19) : fonctions classiques
4. Nb années ≥ moyenne, Nb années > médiane (cellules C20:C21) :
   • Utilisez la fonction NB.SI.
   • Pour la comparaison avec la moyenne, il faut utiliser la condition : ">="&C14 (la condition est obtenue en concaténant l’opérateur >= et la valeur de C14 qui est la moyenne des effectifs de l’année (on pourrait aussi utiliser ">="&MOYENNE(C4:C12)). Cette condition est à adapter pour l’étude (vérification) de la médiane.
5. Année avec le plus petit effectif
   • Utilisez la fonction EQUIV pour déterminer le numéro de ligne à laquelle l’effectif minimum (contenu en C16) se trouve dans la plage C4:C12
   • Ajoutez 2 à cette fonction pour déterminer le numéro de ligne dans le classeur
   • Concaténer la chaîne "B" devant : vous aurez une formule de la forme "B"&(EQUIV(...)+1) qui vous donnera la référence de la cellule qui contient l’année cherchée (pour rappel, l'opérateur & est l'opérateur de concaténation ; on peut utiliser la fonction CONCATENER à la place de cet opérateur).
   • Utilisez la fonction INDIRECT pour retrouver l’année
6. Année avec le plus grand effectif : démarche similaire
7. Suggestion : ajouter un formatage conditionnel pour faire apparaître dans le tableau B4:D11 la ligne (année + effectif) correspondant à l’effectif minimal et la ligne correspondant à l’effectif maximal
8. Effectifs moyens par collège (G3:G8) : utilisez la fonction MOYENNE.SI.
9. Effectifs moyens par tranche d’âge (G12:G19) : idem.
10. Effectifs moyens par tranche d’âge et par collège (I3:O10) : utilisez la fonction MOYENNE.SI.ENS. Remarque : comme pour certaines tranches d'âge et certains collèges il n'y a pas de données, une division par 0 peut se produire. Dans un tel cas, n'affichez rien (le calcul ne doit rien afficher).
11. Effectifs pour une année (saisie en I24) par tranche d’âge et par collège (J26:O33) : utilisez la fonction SOMME.SI.ENS
12. Totaux marginaux de la table précédente (celulles J34:O34, P26:P33, P34) : utilisez la fonction SOMME.

Remarque

• Pour toutes ces fonctions, pour les conditions, il est possible d’utiliser comme critère une valeur ou un critère du type « >=valeur », « >valeur », « <>valeur », « <= valeur », « < valeur » où valeur est un nombre (potentiellement ajouté à l’opérateur par concaténation).