INTRODUCTION
_________
CONVENTIONS :
Le tuto et les documents XL utilisent la convention suivante :
EN ou ONDE => Noir/Gris
pour les données 'Education Nationale'
CO =>Rouge/Bordeau
pour les données 'Concerto Ville'
Les Ecoles de Pierrefitte sont précédées de
EE (Ecole Elementaires) & EM (Ecole Maternelle)
STOCKAGE:
S:\Comparaison Base Eleve Concerto
(Dossier racine - répertoire de travail)
La procédure utilise les fichiers suivants :
EN.xlsx + CO.xlsx
(Extractions des données sources sources)
_BASE ELEVE v(xx) 2025.xlsm
(Fichiers de Comparaison)
SOMMAIRE
La procédure de ce Tuto permet de traiter et rendre comparable les fichiers ONDE et CONCERTO.
Une comparaison de ONDE / CONCERTO est réalisée plusieures fois par an (voir plus bas) pour réduire les écarts entre les données de CONCERTO et celles de ONDE (Education Nationale).
Des macros EXCEL effectuent automatiquement un travail d'harmonisation sur les fichiers pour les rendre comparables et prépare un document Excel qui identifie et repère en masse les différences en les 2 bases (Différence de statut d'inscription ou d'école)
Le repérage de ces écart est présenté dans plusieurs onglets pour guider les agents dans les modifications à apporter dans Concerto.
Chaque onglet permet de traiter une problématique (préinscription, radiation, différences sur une école ou encore enfants 'absents' (càd un enfant uniquement trouvé dans 1 seule des 2 sources Onde/Concerto...)
Avertissement sur la préparation des fichiers :
Cette procédure utilise la dernière forme des Fichiers ONDE (mars 2024, adaptée en 2025).
Le classeur traite les source via la fonction Power Query (successions de petites opérations standardisées dans une interface visuelle pour nettoyer les sources - bien plus facile que des macros)
Planning d'utilisation de l'outil :
Fin février => avant bascule scolaire ( extraction vers 1er jour des vacances)
Rôle à PRECISER
Mi-Juin => vérification de ONDE => identifier école absentes (relances) + Relancer sur "plan d'organisation pédagogique" (nombre et intitulé des classes)
Début-juillet => les directeurs d'école ont jusqu'aux vacances d'été, parfois quelques jours en plus, pour alimenter Onde (extractions vers mi-juillet)
Fin aout jusqu'à Toussaint => plusieurs mises à jour sont effectuées entre la rentrée et mi-octobre.
Fréquence 'normale' de mise à jour : Mensuelle
Limitations de l'outil
Les anomalies repérées par ce traitement ne peut se faire QUE si les enfants sont identifiés dans les 2 fichiers
(des différences d'état civil peuvent empêcher cette identification MAIS l'onglet "absent" du classeur parvient cependant proposer une possibilité de concordance sur près de 70% de ces cas (normalement non identifiables...')
Des résultats peuvent êtres proposés à tort à l'utilisateur : cela peut être dû à la présence de doublons et/ou à la méthode de traitement des situations dans Concerto.
1 vigilance est à maintenir sur les noms d'écoles utilisés dans ONDE et CONCERTO (exemples GS Petit-Pianos = 2 écoles dans Concerto, 1 seule dans ONDE, 1 nouvelle école devra être intégrée...)
Après traitement dans Concerto des cas indiqués par le fichier, seule 1 nouvelle mise à jour de l'extractions CONCERTO permet de refléter les corrections réalisées dans les documents XL.
Durée d'une mise à jour
L'extraction et le traitement des fichiers se réalise habituellement en 1h
(sauf modification impactante dans les sources)
En cas de modification dans les sources, 1 temps d'adaptation peut être nécessaire (24 à 48 heures selon les problématiques posées)
Pour info : Les 13 colonnes INDISPENSABLES ONDE
les extractions ONDE se font sur le portail https://externet.ac-creteil.fr
(Login + 1 mot de passe généré par clé sont indispensables pour y accéder => Voir avec Services Enfance ou Vie Scolaire)
_______________
Choisir :Cliquer enfin sur le bouton Rechercher...
1 lien cliquable apparait sous le formulaire, avec télécharger le fichier (CSV)
Sur ONDE faire 1 extraction par Etat d'inscription pour Pierrefitte
(à minima 'Admission Définitives' + 'Radiations + 'Admissions acceptées' )
Sur ONDE faire 1 extraction par Etat d'inscription pour Saint-Denis
________________________
Regrouper tous les fichiers en 1 seul fichier.
Les colonnes DOIVENT correspondre => toujours bien vérifier pour ne pas créer de décalage !
=> les 2 colonnes ci dessous suivantes sont de bons repères
- 'Date de Radiation' => à créer si absente avant 'Nom d'usage' ( car souvent absente AVANT la rentrée !)
(attention à ne pas créer de décalage !- vérifier chaque fichier d'état :-)
- 'Sexe'
Saint-Denis & Pierrefitte ne doivent faire qu'1 seul fichier.
Note : les colonnes après la colonne 'Sexe' sont inutilisées et peuvent être ignorées)
CONSEILS pour la création de la colonne 'Date de radiation' :
________________________
Une fois assemblés, repérer et supprimer les 'lignes parasites' de chaque fichier
(à la fin de chaque fichier, des lignes vides et d'entêtes sont souvent ajoutées dans les dernières lignes... elles doivent être supprimées)
Supprimer lignes blanche (si présentes)
Ne conserver Que la 1ère ligne d'Entête - supprimer les autres lignes d'en-têtes (si présentes)
(NOTE : il peut y en avoir plusieurs dans chaque extraction... bien vérifier jusqu'en bas du fichier)
________________________
Nommer la feuille précisément "ActiveSheet" (nom de l'onglet Excel)
Supprimer les autres feuilles du classeur EN.xlsx
=> Le classeur EN.xlsx ne doit contenir qu'1 seule feuille de calcul nommée "ActiveSheet"
enregistrer le fichier dans S:\Comparaison Base Eleve Concerto, sous le nom EN.xslx
NOTE : conserver une trace des extractions brutes (avec date) dans dossier "backup & autres"
Pour info : Les 10 colonnes INDISPENSABLES dans Concerto
menu Famille / Inscription
choisir modèle "_EXTRACTION Base Elève & Concerto -2025-2026"
En cas de doute sur le modèle, vérifier que la Rechercher s'effectue sur :
Multi-Activité
Inscription scolaire (élémentaire + maternelle) (tout statuts)
année scolaire en cours*
(* attention à la bascule scolaire qui a lieu en Mars)
tous lieux
Valider OK
Dans les outils, cliquer sur l'icone EXCEL (ou Clic-droit sur les résultat et demander Export/Excel )
Dans Excel :
Nommer la feuille précisément "ActiveSheet" (nom de l'onglet Excel)
enregistrer le fichier dans S:\Comparaison Base Eleve Concerto, sous le nom CO.xslx
NOTE : conserver une trace des extractions brutes (avec date) dans dossier "backup & autres"
Valider / Répondre aux avertissements à l'ouverture du classseur
INTRO permet de vérifier la comptabilité des sources avec avant actualisation.
Il suffit opur celà d'ouvrir en parrallele les fichiers CO et EN... en cliquant sur leur lien
Ouvrir les fichiers CO et EN pour vérifier que les sources sont compatibles avec le traitement
(noms de la fuille et des colonnes... => F5 et F7 doivent passer en vert )
Tous les les voyant de étape 1 (ligne 3 de la feuille) doivent passer à OK , en Vert
(le détail des tests est détaillé en ligne 49 'Tests Colonnes' - Noms feuilles + noms colonnes)
En cas d'échec vérifier les colonnes et les fichiers fournis.
Contacter Olivier si le classeur doit être adapté ou diagnostiqué
La partie suivante coche en VERT les colonnes qui ont passé le test :-)
cliquer sur l'onglet "Données"
cliquer sur le bouton "Actualiser tout"
Le traitement prend plusieurs minutes...
(XL actualise et traite les sources Concerto et Onde, puis actualise les onglets de traitement)
Vérifier que le traitement se soit correctement déroulé avec 2 ou 3 exemples pratiques
FORCER ou VERIFIER l'actualisation d'un TCD ou d'une source (CONCERTO ou ONDE) via clic droit / Actualiser
Vérifier que les dernières lignes de la source CONCERTO , ont bien leurs formules (certaines formules ne sont pas automatiquement répercutées
=>étendre simplement les formules vers le bas pour résoudre (Sémectionner EtatEN à fin + tirer)
Les Formules de la feuille Effectif doivent être manuellement adaptées selon période de l'année / état de remplissage de EN...
=> ajuster manuellement les formules si besoin pour 'viser la bonne colonne finale des TCD
En cas d'erreur lors de la mise à jour...
En cas d'erreur :
Le classeur ne peut comparer que ce qui est comparable :
IDENTIFIANT ENFANT : en l'absence d'identifiant unique, 1 clé est fabriquée avec 2 lettres du NOM + 2 lettre prénom + Date de naissance
=> si une clé n'est pas trouvée dans l'autre base un enfant est indiqué "abs"
=> l'école comme la ville peut faire des erreurs de saisie (exemple 1 enfant NOm Prénom né le 16/07/2020 dans onde et 15/07/2020 aura 2 clés distinctes NOpr16072020 différent de NOpr15072020)
SAISIE DE L'ECOLE : dans le cas où 1 école n'a pas encore saisi ses saisies : les enfans Concerto sont identifiés commes "absent" dans onde
A la rentrée de nombreux enfants CP + MS sont encore absents de ONDE !
Cet onglet est plutôt destinée à l'utilisateur qui met à jour le classeur.
Il permet de vérifier que les fichiers sont conformes aux éxigeances du classeur Excel (colonnes indispensables, etc...)
Il propose néanmoins une rubrique AIDE.
INTRO => onglet rappel des consignes / information et Aide sur :
- La vérification des sources (Etape 1 - vérification prérequis avant MAJ du classeur - Ouvrir CO et EN pour vérifier :-)
- l'Actualisation du classeur (Etape 2 - traitement des fichiers - Ne lancer que si tous les tests sont VERTS)
- consignes de traitement (Etape 3 - résumés des onglets présentés ci-dessous)
- détail des tests de vérification des noms de feuilles XL, noms de colonnes et d'écoles (Test & Vérifs - noms attendus)
- résumé de l'aide + lien vers le support
Etc...
(MAJ juillet 2024) Un onglet indique maintenant les enfants avec 2 inscriptions scolaires dans Concerto.
Ces enfants sont bien sûr à corriger en 1er
1 - Pré => enfants en Pre-inscription dans Concerto s'affichent en regard de l'inscription ONDE trouvée (à traiter en masse)
=> Choisir TOUTES les école à gauche
_______________
CAS 1 (même école => modifier Etat)
_______________
CAS 2 (Au sein d'un même Groupe scolaire => modifier Etat ET Lieux)
_______________
CAS 3 (hors du groupe)
__________
CAS 4 "absents"
2 - Rad =>enfants identifiés comme RADIES dans ONDE et qui ne le seraient PAS dans Concerto... (à traiter en masse)
=> Choisir TOUTES les écoles à gauche
_______________
CAS 1 'école identique)
_______________
CAS 2 (école différente)
Ecarts s'utilise de manière Autonome pour FINALISER UN ETABLISSEMENT (Choisir 1 ecole à gauche, c'est tout)
il peut être néccessaire d'utiliser les filtres Etat (inscrit// Pré) des 2 tableaux pour affiner l'affichage
L'onglet affiche simplement les enfants avec des différences (d'état et/ou de lieux ) trouvés sur 1 même ecole dans chacune des 2 sources...
Les Noms en JAUNE sont des enfants "Absents" (non reconnus par excel) qui pourraient néanmoins "matcher"
Les autres noms sont donnés pour une verification manuelle
Il s'agit donc d'un onglet à traiter :
après avoir traité en masse les Pré-ionscrirts + Radiés + avoir avoir procédé à la mise à jour du fichier CO
(les pré inscrits en masse ne disparaitront de l'outil qu'après nouvelle extraction / Mise à jours CO !)
cet onglet permet de traiter les derniers cas
Recherche par Ecole OU par personne
=> Saisir 1 nom d'école
=> OU 1 chaine de caractère (voir exemple sur feuille)
les enfants en Jaune sont des enfants "Absents" (non tropuvés dans l'autre base")
CEPENDANT => Excel affiche en jaune des correspondances probables :-)
Note le recherche est puissante, mais lente (revers de la médaille) => astuce taper rapidement votre saisie
CONCERTO => Les données Concerto utilisées dans le classeur avec quelques outils de sélection rapides en haut
ONDE => Les données ONDE utilisées dans le classeur avec quelques outils de sélection rapides en haut
_______________________________
Effectifs => comparaison des effectifs ONDE et CONCERTO chaque source (inscrits seulement)
Attention, si ONDE ne contient PAS toutes les ecoles,
les ecoles ne seront pas sur la même ligne (décalage !)
_______________________________
1 onglet supplémentaire est disponible : Classes Onde =>il affiche simplement les intitulés des classes dans la source Onde
Il est possible de paramétrer l'affichage pour mettre en surbrillance (jaune) des différences importantes avec l'autre source (Sur capacité ou Sous capacité...)
Modifier les % dans les cellules en Jaune (par rapport à 100%)
Dans l'attente d'une mise à jour du classeur, vous pouvez suivre les volumétries d'état d'inscription de Concerto en utilisant le rapport "Suivi par Ecole Etat Administratif"
(Rappel : Menu Familles / Documents... Choisir rapport et demander aperçu avant impression)
__________
Le rapport "Anomalie restauration" permet de détecter les enfants avec une Anomalie de Restauration (couplage Inscription Scolaire + restauration en anomalie)
___________
Le rapport "Fichier Scolaire Anomalies" permet de détecter les enfants avec 2 inscriptions scolaires (avec Pré/insc) dans Concerto.
Le classeur propose aussi un onglet "0- Doublons" qui indique en face la correspondance Onde, si trouvée... :-)
__________
DEPRECIE DEPUIS GRATUITE TOTALE de la Restauration - Le rapport "Anomalie restauration Niveau" fait ressortir les anomalie de Niveau scolaire au regard de l'inscription Restauration (Mater / Elem / gratuite)
On distingue 2 type d'opération à réaliser :
- Modifier simplement l'état d'une inscription (1 "pré inscription => à passer en "Inscription" sur une même école)
- Modifier le lieux d'inscription (Groupe scolaires !) ( 1 gpe scolaire => l'une des 2 écoles du groupe OU 1 ecole sur autre)
OU
- les 2 types d'opérations (ex. changer et d'école et d'état) ==> les 2 manips sont à enchainer
En masse sur un groupe scolaire, l'inscription se fait en en 2 temps :
1 modifier le lieux (Lieux inscription)
1 Modifier l'état de la classe (Etat + Classe) (voir plus bas)
1) Afficher la liste des inscrits (Famille / Inscription)
=> préférer le classement alphabétique par nom
=> Ajouter la colonne Etat pour plus de souplesse
=> Astuce : prenez Le groupe scolaire + ses 2 écoles puis grouper les pour enchainer les 2 étapes :-)
2) Sélectionner les enfants à inscrire (Ctrl + Clic) + Cliquer sur "Opération" (engrenage)
=> Choisir "Modifier les lieux et les classes groupes"
=> Valider
3) (verifier les noms sélectionnés, en jaune)
=> Choisir L'école de destination"
=> Choisir classe 'scolaire'
=> choisir ""Ignorer..." (2eme choix, milieu)
_________
Une fois le lieux Modifié...
=> (Ctrl + clic sur les enfants - Assurez vous que tous les enfants soient en sélectionnés (en jaune)
Demander de nouveau Opération + choisir cette fois Modifier l'état de la classe
4) => Modifiel l'état vers "Inscription"
5) => modifier l'état vers "Scolaire"
Enregistrer :-)
IMPORTANT :
Ouvrir l'inscription...
1) sur 1er ecran changer l'état pour "Inscription"
2) dans "lieux", choisir l'école (attention, 1 seule coche !)
(MAJ juillet 2024) Un nouvel Onglet "OUTIL Auber" a fait son apparition. Il permet d'anticiper les problématiques avec les inscrits de Auber. Il recence les enfants de ONDE domiciliés à Auber qui ont absent de Concerto.
La procédure pour obtenir ces listes d'enfants à inscrire n'est pas documentée à ce jour (Appeler le service concerné à Auberviliers)
Préparer les extractions ONDE (1 extraction par fichier, les regrouper en 1 seul fichier, appeler la feuille 'CSVExtractionMairie'
Préparer l'extraction CONCERTO, nommer la feuille CSVExtractionMairie)
____________________________
1. ouvrir fichier "_Prepa 2024 v11.xls"
ouvrir _Prepa 2023 v11.xls (ou version ultérieure)
dans S:\Comparaison Base Eleve Concerto
cliquer sur le bouton "Activer le contenu"(pour autoriser les macros)
2. lancer macro "Préparer Fichiers"
cliquer sur le bouton "Préparer le classeur" (onglet "PREPA" du classeur)
Le traitement prend plusieurs minutes (5 à 9 min)- des informations s'affichent en pied de fenêtre - l'écran peut ne pas bouger un certain temps...
Un message apparait en fin de macro si tout s'est correctement déroulé.
En cas de problèmes de colonnes, la Macro encadre l'utilisateur ( voir onglet 'Important') et réclame son intervention
=> voir 'En cas d'anomalie' plus bas...
3. fermer (sans enregistrer si erreurs...)
vérifier les feuilles EN et CO. Si tout est OK enregistrer le classeur
___________________
la macro crée 1 fichier 'ENprepa.xlsx' (avec seulement les colonnes utiles).
=> en cas de problème avec les colonnes de ONDE (présence des colonnes ou nom des colonnes), c'est sur ce fichier que l'utilisateur doit intervenir.
2 fichiers (CO2.xlsx et EN2.xlsx), sont fabriqués dans le dossier, ils seront utilisés pour mettre à jour les document des utilisateurs à l'étape 4
___________________
EN CAS D'ANOMALIE
Le fichier ONDE évolue constamment :
le nombre et l'ordre des colonnes varie selon la période de l'année... (en juillet la colonne 'Date de radiation' est souvent absente...)
ONDE renomme 'souvent' le nom de ses colonnes...
(parfois avec accent, parfois sans...)
SI les données ONDE ne sont pas conformes, la macro empêche la préparation des fichiers.
Le classeur '_PREPA 2024 v11' vous avertit si les 13 colonnes indispensables ne sont pas présentes dans le fichier 'ENprepa.xlsx'...
En cas de Nouvelles Colonnes dans ONDE (EN.xlsx) qui bloque un traitement correct...
Explication : La feuille EN du fichier Prépa2023 v11.xlsx importe le fichier EN... On supprime une grande partie de ses colonnes pour n'en garder que certaines...
Puis des formules sont ajoutées pour les exploiter et préparer le travail...
MAIS en cas de changement trop important, la macro où les formules ne trouvent plus leurs repères...
Une réparation possible consiste alors à Recréer l'onglet EN dans le fichier Prépa2023 v11 :
Supprimer et recréer 1 onglet nommé "EN"
Recréer l'import sur la feuille EN (via Ruban Données / Connexion entrante / Rechercher pour créer une connexion de données)
>>> Nommer la table de données crée "TAB_EN" (c'est le nom utilisé dans les formules plus bas...)
Rajouter/Vérifier 1 colonne "ID" AVANT le tableau (ID sera donc toujours en colonne A)
Rajouter APRES (en fin de tableau) 4 colonnes nommées "ecoleEN" + "etatEN" + "DATEmvt" + abs
Supprimer les colonnes inutiles pour ne garder que les colonnes utiles... (après la colonne sexe , on peut -jusqu'à présent- tout supprimer...)
>>> ne doivent rester QUE les colonnes qui correspondent aux colonnes suivantes (elles seront renommées avec le nom suivant)
Dans la colonne A 'ID' coller (sur la ligne 2... XL recopiera seul jusqu'en bas) :
formule ID >>> =CONCATENER(MAJUSCULE(GAUCHE([@nom];2));MINUSCULE(GAUCHE([@prenom];2));"_";TEXTE([@naissance];"JJMMAA"))
Dans les colonnes O à R coller (toujours sur le 2ème ligne) :
formule ecoleEN >>> =SIERREUR(RECHERCHEV([@code];TAB_Ecole;4;FAUX);" Erreur / Vide")
formule etatEN >>> =RECHERCHEV([@etat];TABetatinscription;2;FAUX)
formule DATEmvt >>> =MAX(TAB_EN[@[DATEinscription]:[DATEdefinitive]])
formule abs >>> =SI(NB.SI(TAB_CO[ID];[@ID])>0;"";"abs")
A ce stade il ne reste qu'à vérifier que les formules de la feuille CO aillent bien trouver les données EN (c'est à dire pointer vers TAB_EN)...
vérifier les formules colonnes O à Q
colonne O abs >>> =SI(NB.SI(TAB_EN[ID];[@ID])>0;"";"abs")
colonne P ecoleEN >>> =SIERREUR(SI([@ecoleCO]=RECHERCHEV([@ID];TAB_EN;15;FAUX);"";RECHERCHEV([@ID];TAB_EN;15;FAUX));"abs")
colonne Q etatEN >>> =SIERREUR(SI([@etatCO]=RECHERCHEV([@ID];TAB_EN;16;FAUX);"";RECHERCHEV([@ID];TAB_EN;16;FAUX));"abs")
IMPORTANT : Le classeur est en Lecture seule (protection contre erreurs):
faire clic-droit / propriété sur l'icole du fichier
décocher "Lecture Seule" (et valider)
Etape 4 - Actualisation des fichiers "_BASE ELEVE - v11.xlsm"
IMPORTANT : le fichier est en lecture seule !
Effectuer un clic-droit / propriété sur l'icone du fichier
et décocher "Lecture seule"...
1. ouvrir fichier "_ BASE ELEVEv11.xlsm"
ouvrir 1 fichier "_BASE ELEVE v11.xlsm"
cliquer sur le bouton "Activer le contenu"(pour autoriser les macros)
afficher onglet "INFO"
2. lancer macro "Actualiser classeur & TCD" (feuille INFO)
cliquer sur "Actualiser classeur & TCD" (l'opération prend quelques minutes...)
La mise à jour prend plusieurs minutes.
Patienter jusqu'au message "Mise à jour terminé"
3. vérifier + enregistrer le classeur
vérifier le fichier mis à jour :-)
Si le fichier semble bon, enregistrer le classeur
Retourner cocher "Lecture Seule" (clic-droit icone + cocher 'lecture seule')
Bon courage :-)
Sur ONDE faire une extraction des inscrits
nommer l'onglet Excel ' CSVExtractionMairie '
____________________________
(si présentes) Supprimer 2 colonnes "Niveau déclaré à l'inscription" + "Cycle déclaré à l'inscription"
____________________________
dans les dernières lignes du fichier :
supprimer lignes blanche (si présentes)
supprimer les lignes d'en-têtes répétées (si présentes)
____________________________
enregistrer sous le fichier dans S:\Comparaison Base Eleve Concerto, sous le nom EN.xslx
(conserver et dater les extractions brutes dans dossier "backup & autres")
____________________________
Rechercher avec :
Multi-Activité
Inscription scolaire (élémentaire + maternelle)
année scolaire en cours
tous lieux (4)
Valider OK
Clic-droit sur la liste => Export Excel
(ou cliquer sur le bouton Excel dans la barre d'outils)
enregistrer fichier dans S:\Comparaison Base Eleve Concerto, sous le nom CO.xslx
(penser à conserver et dater les extractions brutes dans dossier "backup & autres")
____________________________
Rajouter 1 ligne 'radiation' si besoin...
S'il n'y a aucun statut 'radiation' dans le fichier, il faut insérer une ligne :
insérer une nouvelle ligne avec 1 nom + prénom (bidon, type 'test' ou 'zzz')
Remplir 'Radiation' en colonnes Etat Inscription (Col E) + Statut dérogation (col J)
____________________________
____________________________
____________________________
Enregistrer le document
utiliser ensuite la commande excel "enregistrer-sous" pour créer 1 doc XL par utilisateur
(vous pouvez écraser les anciens)