Opérations plus complexes de transformation de données
Objectif
On souhaite créer une colonne dans laquelle on affiche l'écart entre l'IPS d'une voie d'un lycée (GT ou PRO) et l'IPS moyen académique de cette même voie.
Une étape consistera donc à créer deux requêtes contenant respectivement la moyenne académique des IPS des lycées voie GT et la moyenne académique des IPS des lycées voie PRO.
Définition : Moyenne pondérée
Ces moyennes sont des moyennes pondérées par les effectifs dans ces deux voies.
Soit \(N = \begin{pmatrix} n_1 \\ n_2 \\ n_3 \end{pmatrix}\) la grandeur « colonne » des effectifs et soit \(A= \begin{pmatrix} a_1 \\ a_2 \\ a_3 \end{pmatrix}\) la grandeur « colonne » des IPS. Alors la moyenne pondérée des IPS est définie par :
\(\mathrm{IPS_{moyen}}=\frac{\sum{n_i\cdot a_i}}{\sum{n_i}}\)
D'un point de vue mathématique, le numérateur de l'expression ci-dessus est le produit scalaire des deux grandeurs vectorielles \(\overrightarrow{n}\) et \(\overrightarrow{a}\) associées à \(N\) et \(A\) :
\(\begin{align}\overrightarrow{n}\cdot \overrightarrow{A}&=~^t N~A\\&=\begin{pmatrix} n_1 \quad n_2 \quad n_3 \end{pmatrix}\;\begin{pmatrix} a_1 \\ a_2 \\ a_3 \end{pmatrix}\\&=n_1\cdot a_1+n_2\cdot a_2+n_3\cdot a_3\end{align}\).
Créer une référence à une requête
Pour des facilités de lecture, renommer la requête « fr-en-ips-lycees-ap2022 » en « ips_lyc ».
Faire un clic droit sur la requête ainsi renommée (volet de gauche) et cliquer sur « Référence ».
Renommer la requête ainsi créée « IPS_moy_GT ».
Isoler les colonnes d'intérêt
Conseil :
Dans le menu « Accueil », cliquer sur « Choisir les colonnes » \((\times 2)\).
Cocher uniquement les colonnes « Effectifs voie GT » et « IPS voie GT ».
Ajouter une colonne
Méthode : Méthode 1
Sélectionner les deux colonnes de la requêtes.
Dans le menu « Ajouter une colonne », cliquer sur le bouton « Standard », puis « Multiplier ».
Renommer la nouvelle colonne calculée : « eff_x_IPS ».
Cette méthode est adaptée aux opérations simples sur les colonnes existantes.
Complément : Méthode 2
Dans le menu « Ajouter une colonne », cliquer sur « Colonne personnalisée ».
Donner un nom à la nouvelle colonne : « eff_x_IPS ».
Dans le cadre de la formule, double-cliquer sur les colonnes à multiplier pour obtenir cette formule :
=[Effectifs voie GT]*[IPS voie GT]
Cette méthode est plus générale que la première. En connaissant la syntaxe de certaines fonctions, on peut élaborer des calculs complexes.
Transformer une colonne... en nombre
Fondamental : Calcul de la moyenne pondérée
Renommer l'étape précédente « Produit_Eff_IPS ».
Sélectionner la colonne « eff_x_IPS ».
Dans le menu « Transformer », cliquer dans le sous-menu « Statistiques » sur « Somme ».
Le résultat obtenu dans la barre de formule est le suivant :
=List.Sum(Produit_Eff_IPS[eff_x_IPS])
Attention : Modification du code de l'étape
Pour obtenir la moyenne pondérée des IPS des lycées de la voie générale et technologique, on doit diviser ce résultat par la somme des effectifs des élèves en voie GT.
Modifier donc « à la main » cette étape de la façon suivante :
= List.Sum(Produit_Eff_IPS[eff_x_IPS])/List.Sum(Produit_Eff_IPS[Effectifs voie GT])
Arrondir à une décimale le résultat obtenu.
Éditeur avancé
Syntaxe : Code de la requête
La requête « IPS_moy_GT » renvoie un nombre décimal qui correspond à l'IPS moyen des élèves de la voie générale et technologique de l'académie de Besançon (année scolaire 2022/2023).
letSource = ips_lyc,
Select_Eff_IPS = Table.SelectColumns(Source,{"Effectifs voie GT", "IPS voie GT"}),
Produit_Eff_IPS = Table.AddColumn(Select_Eff_IPS, "eff_x_IPS", each [Effectifs voie GT] * [IPS voie GT], type number),
Calcul_moyenne = List.Sum(Produit_Eff_IPS[eff_x_IPS])/List.Sum(Produit_Eff_IPS[Effectifs voie GT]),
Arrondi_IPS = Number.Round(Calcul_moyenne, 1)
in Arrondi_IPSFinalisation de la tâche
Complément : Voie PRO
Reprendre les étapes précédentes pour déterminer l'IPS moyen des élèves de la voie professionnelle.
Remarque : Astuce
Il est possible de dupliquer la requête réalisant le calcul pour la voie GT et de la modifier dans le mode d'édition avancée.
Comparaison des IPS
À partir d'une nouvelle requête créée en référence à la requête « ips_lyc » :
Créer, pour chacune des voies, une colonne qui calcule l'écart à la moyenne de l'IPS de la voie.
Fermer et charger la requête dans un tableau. Les deux requêtes de type nombre, « IPS_moy_GT » et « IPS_moy_PRO », ne seront pas chargées : créer la connexion uniquement.
Dans Excel, utiliser la mise en forme conditionnelle pour créer des nuances de couleurs.