CONVENTION : Le tuto et les documents XL utilisent la convention suivante :
EN ou Onde pour les données 'Education Nationale' => Noir/Gris
CO pour Concerto Ville =>Rouge/Bordeau
INFOS : stockage & noms
S:\Comparaison Base Eleve Concerto
(Dossier racine - répertoire de travail)
La procédure utilise les fichiers suivants :
_Prepa 2024 v(xx).xlsm
(Fichier de Préparation des sources)
_BASE ELEVE v(xx).xlsm
(Fichiers de Comparaison)
2 Extractions sont nécessaires :
EN.xlsx + CO.xlsx
1 fichier ENprepa.xlsx est créé pendant le traitement. (C'est ce fichier qui peut être appelé à être adapté manuellement par l'utilisateur)
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).
La procédure de ce Tuto permet de traiter et rendre comparable les fichiers ONDE et CONCERTO.
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 2023).
La Macro de préparation ne peut opérer QUE si le fichier ONDE n'a pas été modifié dans sa structure par l'Education Nationale.
En cas de changement ONDE trop important (renommages de colonnes clés OU modification conséquente de la structure du fichier) .
Si la macro échoue >>>> Contacter Olivier DEPAULE pour effectuer les changements nécessaires
un test grandeur nature peut être planifié avant chaque campagne pour anticiper ces changements et éviter des retards.
Planning d'utilisation de l'outil :
Fin février => avant bascule scolaire ( extraction vers 1er jour des vacances)
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 et rentrée => plusieurs mises à jour sont effectuées entre la rentrée et mi-octobre.
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...')
Les Dérogations empêchent le repérage correcte des écarts. (50% d'échec de la macro sur ces situations)
une vigilanceest à maintenir sur les noms d'écoles utilisés dans ONDE et CONCERTO (exemples GS Petit-Pianos = 2 ecoles 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éalisable en moins de 2h
En cas d'échec de la macro un temps d'adaptation peut être nécessaire (24 à 48 heures selon les problématiques posées)
Procéder aux extractions Onde (1 par statut) , et les regrouper en 1 seul fichier (par copier coller) en surveillant ces 3 points
Vérifier les colonnes (spécialement Onde - elles doivent êtres identiques d'1 extraction à l'autre)
Nommer le feuille exactement " ActiveSheet "
Supprimer les lignes d'en têtes ou lignes blanches des extractions (ne garder que l'entête du haut)
Sur ONDE faire 1 extraction par Etat d'inscription
(à minima 'Admission Définitives' + 'Radiations + 'Admissions acceptées' )
SURVEILLER LES COLONNE :
La colonne "Date de radiation" doit être créée si absente (c'est notamment le cas au moment de la rentrée...),
entre "Date admission définitive" et "Nom d'usage"...)
Les colonnes DOIVENT correspondre
=> toujours bien vérifier pour ne pas créer de décalage
=> les 2 colonnes suivantes sont de bons repères
- Date de Radiation
- Sexe
Note : les colonnes après la colonne 'Sexe' sont inutilisées et peuvent être ignorées)
VERIFIER/SUPPRIMER LIGNES BLANCHES & EN TÊTES
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)
supprimer les lignes d'en-têtes répétées (si présentes), ne garder que celle du haut
(NOTE : il peut y en avoir plusieurs... bien vérifier jusqu'en bas de chaque fichier)
NOMMER LA FEUILLE "ActiveSheet"
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"
menu Famille / Inscription
choisir modèle "_Base Elève & Concerto 20xx-20xx"
cliquer sur Rechercher avec :
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"
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)
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...
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'...
créée une copie 'ENprepa.xlsx'
qui ne conservera QUE les colonnes utiles
(c'est ce fichier qui doit est à modifier si nécessaire)
vérifie présence des 13 colonnes clés de 'ENprepa.xlsx'
importe 'ENprepa' & 'CO' dans 'Prepa 2024' pour :
homogénéiser les 2 sources (noms, formats...)
appliquer des formules pour les comparer
exporter des fichiers EN2 et CO2 (allégés)
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")
Il s’agit d’une mise à jour partielle des données (données Concerto seulement), afin d’intégrer les dernières modifications réalisées dans Concerto dans le classeur.
Commencer la procédure précédente à l’étape Etape 2 - Extraction 'CO' (Concerto)
A l’étape 3 - Préparation des sources (‘_Prepa 2023 v11.xlsm’)
=> utiliser le bouton MAJ avancement Concerto
Dans l'attente d'une mise à jour d'avancement, je vous rappelle que vous pouvez suivre à tout moment les volumétries d'état d'inscription de Concerto en utilisant le rapport ci dessous
=> (Menu famille/ Documents) ENS-SCO - Suivi par Ecoles des états Administratifs 2023-24
____________________________________
Finaliser avec l’étape Etape 4 - Actualisation des classeurs Excel
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)
IMPORTANT : le fichier est en lecture seule !
Effectuer un clic-droit / propriété sur l'icone du fichier
et décocher "Lecture seule"...
ouvrir 1 fichier "_BASE ELEVE v11.xlsm"
cliquer sur le bouton "Activer le contenu"(pour autoriser les macros)
afficher onglet "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é"
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 :-)
Le fichier Excel identifie pour vous les écarts ONDE/CONCERTO sur lesquels intervenir :
' 1 PRE concerto ' => les enfants en Pre-inscription à corriger dans Concerto (en masse)
' 2 RAD onde ' => les enfants Radiés dans Onde, à Radier dans Concerto (en masse)
' 3 ECARTS Onde=> identifie les écarts qu'ONDE détecte avec Concerto (en masse)
'Doublons & MAJ' => repère les doubles inscriptions Concerto + Invite à faire la MAJ post PRE & RAD
' 4 Ecarts Ecole & Statut Inscr. ' => indique pour 1 école choisie les différences identifiées entre nos données CONCERTO et Onde (différences d'Etat inscription et/ou d'école différente)
' 5 Erreurs Etat Civil' => affiche les enfants qui n'ont PAS pu être comparé dans les 2 sources en raison d'erreurs d'état civil... (Nom, Patronyme, ou Date de naissance...)
- Le classeur met en couleur des concordances possibles
2 autres onglets vous ont utiles
'Ecoles ' => Cet onglet récapitulatif affiche école par école les écarts entre ;
- les nombre d'enfants 'Inscrit dans Onde
- le nombre d'enfant 'inscrits' + 'Préinscrits' dans Concerto
Il permet de mesurer les écarts entre les 2 fichiers
'Consultation libre ' => cet onglet est libre, vous pouvez paramétrer librement les boutons d'école, niveau scolaire, d'état d'inscription pour afficher et comparer les données Onde & Concerto en vis à vis.
Ignorer les autres onglets.
Réalisez dans l’ordre les tâches ci-dessous
Transformer les pré-inscriptions de CONCERTO en Inscriptions => Onglet "1 PRE concerto"
Affiche les Pré-inscrits de Concerto
Si des données Onde différentes sont trouvées, elles s’affichent en bout de ligne (colonnes grises)
(Le classeur n’affiche rien lorsque les données sont identiques)
Ajuster les informations Concerto en fonction des infos Onde en bout de ligne
Ouvrir l’inscription Concerto
Modifier l’état de l’inscription avec le statut Onde (etatEN)
Si la correction a lieu APRES les inscriptions en masse
=> Vérifier les inscriptions périscolaires & extrascolaires (créer les nouvelles, clôturer les autres avec 1 date de fin, gestion éventuelle des pointages…)
Une gestion en masse peut être faite pour passer les Préinscrits en Inscrits.
Ouvrir l’inscription Concerto
Modifier l’état avec le statut indiqué Onde (etatEN)
Modifier le lieu avec l’école indiquée dans Onde (ecoleEN)
=> Vérifier les inscriptions périscolaires & extrascolaires (créer les nouvelles, clôturer les autres avec 1 date de fin, gestion éventuelle des pointages…)
Une gestion en masse peut être faite pour modifier le lieu d’inscription.
Une seconde gestion en masse pour passer les Préinscrits en Inscrits.
Vérifier les 2 sources pour vérifier si un traitement peut être apporté.
En l’absence d’éléments permettant le traitement de la ligne :
=> contacter l’école et attendre confirmation…
Modifier les inscriptions de l’enfant selon la situation observée
Ex. Déménagement = Radiation ancienne école + Inscription nouvelle école, etc…
Créer/modifier/vérifier les inscriptions périscolaire & extrascolaires en conséquence
Indique les enfants Radiés de ONDE trouvés dans Concerto avec des écarts...
Répercuter les radiations de ONDE dans Concerto
Si des données Concerto différentes sont trouvées, elles s’affichent en couleur en bout de ligne (colonnes Rouges)
Si les dates Onde sont plus récentes :
Modifier l’état de l’inscription Concerto pour « radiation »
Indiquer la ‘DATEradiation’ ONDE comme date de fin d’inscription dans Concerto
Entrer le motif de modification : « En attente de réponse » (Le motif sera précisé ultérieurement)
Interroger systématiquement l’école pour obtenir motif de radiation
Créer/modifier/vérifier les inscriptions périscolaire et extrascolaire en conséquence
Préciser le motif de Radiation selon la réponse de l’école
Gestion enfant par enfant (motif, gestions dossier famille, inscriptions péri, etc…)
Vérifier les 2 sources pour vérifier si un traitement peut être apporté.
En l’absence d’éléments permettant le traitement de la ligne :
=> contacter l’école et attendre confirmation…
Modifier les inscriptions de l’enfant selon la situation observée
Ex. Déménagement = Radiation ancienne école + Inscription nouvelle école, etc…
Créer/modifier/vérifier les inscriptions périscolaire & extrascolaires en conséquence
Gestion enfant par enfant (motif, gestions dossier famille, inscriptions péri, etc…)
Indique les enfants de ONDE trouvés dans Concerto avec des écarts d'Ecole ou de statut d'inscription...
Si des données Concerto différentes sont trouvées, elles s’affichent en bout de ligne (colonnes Rouges)
Si un ID x2 apparait en bout de ligne cela 2 ID identiques peu
A traiter comme sur les 2 premiers onglets.
Vérifier les 2 sources pour vérifier si un traitement peut être apporté.
En l’absence d’éléments permettant le traitement de la ligne :
=> contacter l’école et attendre confirmation…
Modifier les inscriptions de l’enfant selon la situation observée
Ex. Déménagement = Radiation ancienne école + motif + Inscription nouvelle école, etc…
Créer/modifier/vérifier les inscriptions périscolaire & extrascolaires en conséquence
A l’issue des opérations précédentes, la feuille « Doublons & MAJ » indique les problématiques de double inscriptions trouvées dans Concerto.
Après correction des cas indiqués, une mise à jour des données Concerto peut être réalisée pour intégrer les corrections réalisées dans le classeur.
NOTE : sans mise à jour des données Concerto, le classeur ne répercutera PAS vos modifications
Affiche les enfants sur 1 école CONCERTO et affiche les différences trouvées dans Onde...
Afficher les données de Concerto sur 1 école en choisissant 1 école dans les boutons de droite
Si des données Onde différentes sont trouvées, elles s’affichent en bout de ligne (colonnes grises)
Répercuter ces changements dans Concerto
Le tableau est complété (à droite) par des Enfant ONDE trouvés sur cette même école... :-)
Ouvrir l’inscription Concerto
Modifier l’état avec le statut indiqué Onde (etatEN)
=> Vérifier les inscriptions périscolaires & extrascolaires (créer les nouvelles, clôturer les autres avec 1 date de fin, gestion éventuelle des pointages…)
(Rappel : le tableau de droite complète ces données avec des enfants ONDE qui devraient être sur cette école…)
Vérifier les 2 sources pour vérifier si un traitement peut être apporté.
En l’absence d’éléments permettant le traitement de la ligne :
=> contacter l’école et attendre confirmation…
Modifier les inscriptions de l’enfant selon la situation observée
Ex. Déménagement = Radiation ancienne école + Inscription nouvelle ecole, etc…
Créer/modifier/vérifier les inscriptions périscolaire & extrascolaires en conséquence
Gestion enfant par enfant (motif, gestions dossier famille, inscriptions péri, etc…)
Cet onglet regroupe les enfants qui n'ont pas pu être comparés en raison :
Erreurs sur les noms prénoms (différences de saisie, familles recomposées etc...)
Erreurs sur la date de naissance
NOTE : Par extension, en plus des cas d'erreurs, cet onglet affiche tous les enfants trouvés dans 1 seule des 2 sources d’information Onde OU Concerto
Le classeur indique en couleur des probabilités de concordances
Si correctes, corriger les concordances identifiées + Contacter les écoles pour corriger ces différences
(si aucune correction n’est apportée, les prochaines mises à jour continueront d’afficher ces enfants)
Trouver les enfants dans Concerto et apporter les modifications nécessaires dans Concerto
(modifier l’état administratif si même école, interroger l’école si radiation… etc…)
Rectification de l’erreur :
Seule une demande d’acte d’état civil à la famille permet de savoir si l’erreur est corrigeable dans Concerto…
Si l’erreur est dans Onde signaler à l’école pur demander correction
(Aucune solution ne peut être apportée, pour l’instant, sans effectif supplémentaire)
Si 1 enfant reste sur fond blanc = le classeur ne parvient PAS à trouver de proposition de concordance.
Effectuer les recherche(s) manuelles de l’enfant dans concerto (exemples : par date de naissance dans concerto, Recherches phonétiques, en utilisant le nom des parents disponibles dans Onde)
Si aucun résultat n’est trouvé dans Concerto : Interroger l’école sur l’enfant :
Dans le cas d’un enfant uniquement dans ONDE (absent de Concerto) : l’école doit demander à la famille de se présenter en Mairie (ou la Mairie contacte la famille à l’aide des informations de Onde)
Les enfants uniquement dans Concerto doivent être traités manuellement selon les éléments du dossier famille…
2 onglets supplémentaires (onglets bleus) permettent :
une consultation librement paramétrable pour l'utilisateurs (niveau, état, ecole...)
les statistiques des Inscrits école par école
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)