Procédure téléchargeable (à venir)
Contexte
La commune est en charge d’établir la liste des enfants scolarisables, dans un tableau unique pour transmettre à la DSDEN et à la préfecture de la Seine-Saint-Denis.
Repérage des enfants scolarisables
La Ville de Saint-Denis a développé un outil sous Excel qui facilite le recoupement de plusieurs sources hétérogènes (notamment dénuées d'identificateurs permettant un croisement fiable) afin d'établir cette liste.
Les 4 sources utilisées sont :
1 fichier CAF contenant plus + de 21 200 enfants
2 fichiers Ville (Concerto) contenant :
- + de 13 400 enfants AVEC inscription scolaire
- + de 4 100 enfants SANS inscription scolaire
1 fichiers Education Nationale ONDE contenant
- + 12 600 enfants scolarisés dans le publique
- + de 780 enfants scolarisés dans le privé
L'ensemble de fichiers est traité pour repérer :
dans un 1er temps : les enfants uniquement présents dans le fichier CAF sans aucune information de scolarisation dans ONDE ou CONCERTO
Il ajoute dans un 2nd temps les enfants dénués d'information de scolarisation identifiés dans Concerto.
Sur les 51600 lignes analysées, l'outil identifie autour de 6200 enfants non scolarisés. Un courrier de demande de justification de situation est envoyé pour chacun d'eux.
Fonctionnement de l'outil...
L'outil agrège les 4 différents fichiers dans 1 document unique, pour identifier et vérifier la présence d'1 enfant dans chaque source.
Pour contourner l'absence d'identificateur commun dans les différentes sources, l'outil créée une clé d'identification 'unique' pour chaque enfant de chaque source. Il exploite ensuite cette clé pour détecter ou non sa présence dans les différents fichiers.
Les résultat identifiés sont assemblés dans un tableau unique qui servira de base au publipostage et de fichier de suivi (avec indicateurs statistiques).
Le temps de gestion est inférieur à 5 minutes
(hors temps de gestion et de préparation des sources - approx. 5 minutes/fichier)
Les macros utilisées dans l'outil initial (2022) sont été supprimées au profit de la fonctionnalité PowerQuery de Excel ( plus rapide et plus facile à entretenir).
Limitations de l'outil
Répertoire de travail (juin 2023) :
S:\Fichier Scolaire\CAF
________________
Fichier de traitement :
_OUTILS TRAITEMENT v2025.xlsx
________________
________________
CONSEILS : Vigilance & Vérifications
Repérer les Colonnes avec Nom / Prénom / Date naissance de l'enfant
Nommer ces colonnes 'NOM' + 'PRENOM' + 'NAISSANCE' (Respecter MAJ)
Chaque fichier source ne doit contenir qu'1 seule feuille, nommée ActiveSheet
Enregistrer chaque fichiers sous les noms suivants dans S:\Fichier Scolaire\CAF
s_ONDE.xlsx (fichier Onde)
s_CO.xlsx (Inscrits Concerto)
s_COautre.xlsx (Concerto sans école)
s_CAF.xlsx (fichier CAF)
En résumé...
Sur la feuille INTRO du classeur (l'étape 2 , ligne 28),
paramétrer les dates de naissance à utiliser dans le classeur (cellules jaunes)
Actualiser les données…
Aller dans le ruban DONNEES
Cliquer sur ACTUALISER TOUT
Cliquer sur 1 TCD pour voir apparaitre le ruban "Analyse du tableau croisé dynamique" (1)
Aller dans le ruban
ANALYSE DU TABLEAU CROISE DYNAMIQUE
Cliquer sur ACTUALISER TOUT
Les résultats sont disponibles sur 2 feuilles (couleur Orange)...
Tableau_de_bord
Suivi
feuille 'Suivi'
(résultats identifiés + colonnes de suivi)
feuille 'Tableau de bord'
(stats)
Créer une copie du classeur pour commercer votre suivi, puis SUPPRIMER l'actualisation des résultats de la feuille 'SUIVI',
(afin d'éviter toute mise à jour accidentelle => en cas de sources modifiées ou différentes, 1 mise à jour pourrait créer des décalages de lignes)
'Enregistrer sous' 1 copie de ce classeur pour créer 1 fichier de Suivi CAF indépendant (exemple : de nom 'Suivi Caf 2024-05 25')
Sur la copie créée (vous pouvez rester sur la feuille INTRO) :
Aller dans le ruban DONNEES
Cliquer sur REQUETES ET CONNEXION pour afficher le panneau latéral
dans le panneau droit (dans 'Requetes'), survoler sans cliquer 'résultats' pour faire apparaitre une fenêtre...
... et SUPPRIMER la capacité de MAJ
Après confirmation, l'itelm 'résultat' disparaitra du volet latéral... Vous pouvez maintenant saisir les informations de suivi sans risque de créer de décalage d'enregistrement :-)
Si vous le souhaitez, vous pouvez supprimer de votre fichier de suivi CAF les 4 feuilles de données sources (qui restent consultables dans _OUTILS TRAITEMENT v2025.xlsx)
Utiliser le modèle de publipostage avec le dernier fichier de "Suivi CAF" crée…
Vous devez créer des lots de 500 pages max, au format PDF.
1.
Ouvrir le Modèle de publipostage (en cas de message à l'ouverture, cliquer 'non').
Dans l'onglet Publipostage, utiliser "Sélection des Destinataire" / "Utiliser la liste existante" pour faire pointer le modèle vers le fichier de Suivi CAF créé .
2.
Utiliser ensuite "Modifier la liste des Destinataire" (bouton immédiatement à droite) :
- dans la fenêtre qui s'ouvre, cliquer sur "Filtre" (1)
- paramétrer le champ "Envoi courrier"
- choisir l'item de Comparaison "égal à"
- et saisir dans "Comparer à" la valeur "Oui" (2) .
3.
Vérifier les résultats en utilisant "Aperçu des résultats".
(vérifier les résultats collectés et la mise en page des courriers créés)
4.
Utiliser "Terminer & Fusionner" (1) (en bout de ruban) en créant autant de des lots de 500 enregistrements max. que nécessaire (avec les champs "De " et "à" (2)).
Les lots créés devront être enregistrés au format PDF
Privilégier une gestion par lots 'alphabétique' (500 max)
Conseils publipostage :
Toujours procéder à une impression de test sur papier à entête pour vérifier la mise en page.
Vérifier le positionnement des adresses pour les enveloppes à fenêtre !
"Direction Vie Scolaire" doit être visible dans la fenêtre de l'enveloppe (en cas de retour courrier)
Le Publipostage doit générer des lots de 500 pages max au format PDF (vérifier le nombre total de pages du document pour vous en assurer…)
Privilégier une gestion par lots 'alphabétique' des lots (toujours dans la limite de 500 pages)
TOUJOURS vérifier les PDF après enregistrement (ouvrir, parcourir, fermer)
Déposer les fichiers sur le répertoire commun Impression de la direction
Ci-dessous quelques éléments des techniques qu'utilise le classeur _OUTILS TRAITEMENT v2025.xlsx
Des modifications dans les sources peuvent compromettre le déroulement correct des traitements et de la mise à jour…
AVANT toute autre tentative, commencer à vérifier que vous avez correctement réalisé le traitement des sources à l'ETAPE 1
Nom des fichiers (noms normés, avec extension '.xlsx' de fichier Excel postérieur 2010)
Nom des colonnes (NOM + PRENOM + NAISSANCE)
Nom de la feuille ( 'ActiveSheet' ) + 1 seule feuille par fichier
etc…
SI le traitement échoue à l'ETAPE 2, (actualisation du classeur) alors il faut adapter ce classeur aux évolutions ou changement des sources…
Si les sources sont correctement traitées , les principales sources d'échec de traitement sont dues à :
1 colonne 'clé' absente ou renommée...
1 changement du chemin ou nom de Répertoire
etc...
Le classeur utilise la fonctionnalité PowerQuery de Excel :
PowerQuery est une fonctionnalité de traitement des données (via des instructions simplifiées dans une interface compréhensible)
PowerQuery permet de piloter Excel pour effectuer et enchainer diverses opérations sur les fichiers sources (choisir des colonnes, les renommer, remplacer des valeurs de texte dans les fichiers, créer des nouvelles colonnes, etc...)
PowerQuery est plus rapide que la précédente technologie utilisée (l'outil utilisait des macros)
PowerQuery est modifiable par un néophyte Excel... Il est en effet bien plus facile d'intervenir dans PowerQuery pour le modifier ou ajouter des fonctionnalités, que d'intervenir sur le langage VBA des macros...
Pour Accéder à PowerQuery du classeur :
1) Cliquer sur le ruban DONNEES
2) cliquer sur REQUETES et CONNEXION
Dans la barre latéral qui apparait, cliquer sur une requête...
Faire 1 clic droit/ modifier
(ou 1 clic sur 1 tableau de la feuille qui l'utilise, pour afficher en haut 1 Ruban 'REQUETE' et son bouton modifier...)
La fenêtre PowerQuery s'ouvre avec :
à gauche, les requêtes du classeur
à droite les différents étapes de traitement appliqués (ordre ou renommage de colonnes, remplacements, etc...)
une fonctionnalité 'Editeur avancé'
L' 'Editeur avancé' permettra de remédier facilement à de nombreuses modification...
Apparaissent en rouge, les éléments que vous pouvez Modifier directement...
il faudra être prudent (ne pas effacer de parenthèse ou de ponctuation nécessaire à PowerQuery)
Dans l'image ci-dessus, voici quelques exemple d'interventions possibles... :
la ligne 1 laisse voir le chemin ou se trouve la source qu'Excel doit traiter...
=> Il est donc possible de copier coller une nouvelle adresse de répertoire...
la ligne 2 laisse voir le nom de la feuille qui sera utilisée dans le classeur indiqué avant...
=> changer pour un autre nom si nécessaire (ou adapter le fichier source comme indiqué ici)
la ligne 5 et 6 indiquent les colonnes qui seront conservées par Excel dans votre fichier source...
=> vous pouvez répercuter un changement de nom de colonne clé (exemple, le fichier est livré sans accent ...)
=> ou ajouter/supprimer 1 colonne (attention, il faudra répercuter ces modifs sur les autres lignes)
la ligne 7 indique le type d'une colonne (date, texte, etc...)
=> si vous avez changé le nom à l'étape précédente, n'oubliez pas de répercuter ici :-)
la ligne 10 montre un exemple d'une opération de remplacement d'accent dans une colonne...
la ligne 25 indique l'ordre des colonnes de votre source...
=> modifier l'ordre ici si besoin (toutes les colonnes précédemment utilisées doivent y figurer)
l'éditeur indique enfin si tout est correct pour Excel
=> si vous avez par erreur "abimé" ces lignes lors des modifs (effacer par erreur une parenthèse ou une virgule dont Excel a besoin), la fenêtre l'indiquera.
______________
Une particularité du classeur est de traiter les 4 fichiers sources par ces requetes PowerQuery...
puis d'utiliser le résultat du traitement comme nouvelle base à traiter :
les 2 feuilles 'caf' et 'ville_sans' sont utilisées comme nouvelles sources pour être "fusionnées" et fournir notre tableau de résultats sur la feuille 'suivi' :-)
1 fois les fichiers sources sources traitées, quelques formules permettent de repérer les enfants d'une feuille à l'autre...
Test correspondance entre les feuilles XL :
Formules utilisées dans les colonnes nommées 'feuille1" (2, 3 ou 4)
=NB.SI(caf[ID];[@ID])
La formule NB.SI () compte le nombre d'ID trouvé dans la Colonne ID d'un autre tableau
- caf[ID] = la zone où chercher => ici, c'est la colonne la colonne 'ID' du tableau nommé 'caf'
- [@ID]= on cherche la valeur utilisée sur cette ligne (le '@') dans la colonne ID
La formule comptera 0, 1 ou plus...
Verdict des correspondances (resultat) :
Formules utilisées dans les colonnes 'résultat' (feuilles caf + ville_sans))
=SI( ET([@feuille1]=0;[@feuille2]=0;[@feuille3]=0) ; sheet4&" only";
SI( ET([@feuille1]>0;[@feuille2]>0);"ok"; SI( ET([@feuille1]>0;[@feuille2]=0);sheet1&" only";
SI( ET([@feuille1]=0;[@feuille2]=0;[@feuille3]>0);sheet3&" only";sheet2&" only"))))
La formule semble très complexe mais n'est qu'une succession rigoureuse et structurée de scénarios de test imbriqués (des SI (test ; alors ; Sinon )
Si scénario 1 ALORS dans caf SINON
SI scénario 2 ALORS dans Onde+Ville SINON
SI scénario 3 ALORS dans Onde SINON
SI scénario 4 ALORS dans Ville_Sans SINON forcément le dernier scénario dans Ville
Chaque test (scénario) doit remplir plusieurs conditions (des ET) :
Exemple scénario 1 => Et ( pas dans Onde+ pas dans Ville + pas dans ville_sans )
les résultats de chaque test utilise une cellule nommée et ajoute du texte ( sheet4&" only" = cellule nommée 'sheet4' + chaine de texte " only")
Utilisation des dates paramétrées :
Formules utilisées dans les colonnes 'annee' (feuilles caf + ville_sans))
=SI( ET([@[NAISSANCE]]>=dateid;[@[NAISSANCE]]<=dateid2) ; "ok" ; "no")
Sur la feuille 'INTRO' les cellules contenant les dates de naissance à exploiter sont nommées 'dateid' et 'dateid2' ...
la formule SI() cherche donc à savoir si la date de naissance sur la ligne est comprise dans cette fourchette :-)