ADMIN - Comparaison CAF
ancienne procédure 2022 archivée
ATTENTION : Vous consultez l'ancienne procédure !
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, absents de la source CAF.
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, puis identifie les résultats (Comparaison CAF v# (autonome) .xlsm).
3 macros automatisent les différents traitements pour identifier les enfants et créer un fichier de suivi (Suivi CAF - date##.xlsx).
Pour contourner l'absence d'identificateur commun dans ces 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 fichier unique et indépendant, qui sert de base au publipostage et de fichier de suivi (avec indicateurs statistiques).
Le temps de gestion est inférieur à 20 minutes (hors temps de gestion et de préparation des sources - approx. 10 minutes/fichier)
Limitations de l'outil
La clé d'identification échoue à identifier de manière unique un enfant dans les cas suivants :
erreurs sur l'état civil (Nom, Prénom ou Dates de naissances erronées dans l'une des sources)
certains cas de clés identiques (enfants nés même jours, homonymes... ces cas sont rares)
L'outil parvient à s'adapter à de nombreuses évolutions ou modifications des fichiers sources (structure ou noms de colonnes notamment). Certaines évolutions devront néanmoins réclamer une adaptation de l'outil (Ordre ou nom des colonnes, Formules et/ou Macro - les macros sont commentées :-)
Soyez vigilants sur la pertinence des Extractions (choix des dates, age des enfants, année de scolarisation, etc...).
Le fichier Excel effectue un 'travail logique', de masse, sur les informations fournies : Toute erreur ou omission d'information dans ces dernières implique une incidence immédiate sur les résultats proposés !
Il est impératif de Vérifier la pertinence des extraction et de la qualité des résultats.
Enregistrer les fichiers dans le même répertoire que COMPARAISON ##.xlsm
Il ne doit y avoir que 1 seule feuille de calcul dans chaque fichier d'extraction
Pour les 2 fichiers utilisés pour le publipostage
l'ordre des colonnes adresses utilisées pour le publipostage doit être le même
(opération à réaliser soit dans les fichiers, soit dans le classeur via l'outil intégré)
Repérer les colonnes avec Nom / Prénom / Date naissance de l'enfant
(voir les prérequis en préambule)
Renommer respectivement ces colonnes
"NOM" + "PRENOM" + "NAISSANCE" (Respecter majuscules)
Rappel :
1 fichier ONDE (public + privé en 1 seul fichier)
1 fichier VILLE enfants AVEC inscriptions
1 fichier VILLE enfants SANS inscription (adresses*)
1 fichier CAF (adresses*)
Dans le fichier Comparaison CAF v# (autonome).xlsx
Aller sur la feuille PARAM
Paramétrer les dates à prendre en compte dans le fichier CAF
(un état sera mis à jour à l'étape 2)
Dans le fichier COMPARAISON ##.xlsx
Aller sur la feuille PARAM
Sur la ligne 'NOM FICHIER', saisir les Noms des fichiers (avec extension) à utiliser (1)
=> astuce : copier/coller leur nom pour éviter les erreurs...
Cliquer sur le bouton ETAPE 1 pour lancer la vérification (2)
Si les colonnes 'Enfants' sont correctement nommées, tout s'affiche en vert... Vous pouvez passer à l'étape suivante
=> si du rouge apparaît, vérifier les fichiers !
Sur la ligne 'NOM FEUILLES de CALCUL' (dans les cellules jaunes), saisir le nom des feuilles de calcul à utiliser dans le classeur
=> astuce : copier/coller leur nom pour éviter les erreurs...
Cliquer sur le bouton 'ETAPE 2 - MAJ classeur' pour lancer la Mise à jour du classeur (2)
Vérifier l'ordre des colonnes qui seront utilisées pour le publipostage :
pour CORRIGER :
Numéroter manuellement de 1 à 6 chaque zone...
Puis cliquer sur "Corriger Ordre"
pour VALIDER :
Si l'ordre proposé est correct, cliquer sur "Ordre OK"
(Excel remplit 1 à 6, dans l'ordre, pour vous)
Vérifier (1) la mise à jour du classeur... (suivez les suggestions proposés... Voir aussi 'Ordonner les Adresses' plus bas))
UNIQUEMENT Si tout est correct : Cliquer sur le bouton 'ETAPE 3 - Extraction fichier Suivi' (2) (un lien sera créé)
Important :
Faire pointer le modèle de Fusion vers le dernier document de "suivi CAF" créé
Vérifier les résultats :
- les Villes (erreurs type "93200 Dakar")
- les domiciliés
- etc...
=> renseigner "non" dans la colonne "Envoi Courrier" pour exclure 1 adresse du publipostage
Filtrer le modèle de publipostage sur la colonne "Envoi Courrier" avec la valeur "oui"
ATTENTION :
Le Publipostage devra générer des lots de 500 pages max au format PDF
Une répartition par lots 'alphabétique' est préférable
bien vérifier le positionnement des adresses pour les enveloppes !
ajouter la Direction dans la fenêtre (en cas de retour d'un courrier)
TOUJOURS vérifier les publipostages réalisés (mise en page, débordements, etc...)
TOUJOURS vérifier les PDF après enregistrement
Le publipostage Word peut utilisez ses propres options de filtre et/ou de tri...
La macro :
assemble & formate les résultats (enfants seulement présents dans la CAF + 'Sans inscription' dans Concerto)
formate les résultats pour débuter le suivi dans un fichier indépendant.
nom du fichier : "Suivi CAF 2022-11-02 0000"
Si les modification des fichiers sources sont trop importantes pour êtres reconnues par XL, vous pouvez manuellement réorganiser l'ordre des colonnes dans les différentes feuilles...
L'utilisation de TABLES par XL permet l'usage suivant...
Exactement à la limite de la ligne d'entête XL et la 1ere cellule de la colonne ...
Faire un 1er clic (les données se sélectionnent...)
Faire un 2ème clic (la cellule de tête s'ajoute...)
Attrapez le bord de la colonne...
(curseur 'croix' !)...
Déplacer et déposer là ou désiré
(une forme "grise' indique où se fera l'insertion... )
Le classeur utilise la fonctionnalité Connexion de Données pour importer et gérer chaque fichier
sa mise en place est très simple : il suffit d'utiliser le bouton Rechercher de la fonctionnalité 'Connexions de données', et pointer vers le fichier à utiliser.
Cette fonctionnalité importe les données d'1 fichier Excel et permet sa mise à jour... (via clic droit/actualiser)
XL traite automatiquement ces données 'importées' dans un un tableau de type Table (filtres, assistance aux formules, etc...)
Les tableaux ainsi 'importés' peuvent s'actualiser par :
1 simple clic-droit sur n'importe quel cellule du tableau...
En cas de changement de source, vous pouvez :
1) Allez dans Données /Connexion
2) sélectionner la source et cliquer sur Propriété
3) Dans l'onglet Définitions, cliquer sur Rechercher...
La Fonctionnalité Tables équipe un tableau de nombreuses fonctionnalité (présentation auto, assistance aux formules, filtres, totaux, etc...). Un système de Noms remplace les références habituelles dans les formules...
XL nomme tout dans les tables : le tableau, les colonnes, les cellules... c'est déstabilisant au 1er abord, mais finalement très clair et pratique.
Exemples : (dans une table que nous avons nommée 'TABcaf')
= NBVAL(TABcaf[IDCAF])
Cette formule compte le nombre de valeur dans le tableau 'TABcaf', dans la colonne 'IDCAF' ...
=> noms de colonnes sont entre crochets, le nom du tableau avant :-)
= NB.SI([IDFAMCAF];[@IDFAMCAF])
Excel ajoute un '@' dans le nom de la colonne pour indiquer 'la valeur sur cette ligne '
Cette formule cherche donc à savoir combien de fois l'ID de cette ligne (@+nom colonne) est trouvée dans la colonne ID
(semblable à une formule NB.SI ( A2:A200 ; A2 )
Une fois la Connexion de données réalisée, des colonnes sont ajoutées dans les Tables, et des formules ajoutées... (la fonctionnalité Table assiste l'utilisateur à la création de ces formules => Sur une table non filtrée, réaliser la formule sur la 1ère ligne de données, et Excel remplira seul l'entièreté de la colonne => Modifier cette 1ère formule, et Excel mettra à jour toutes les autres, seul)
Excel se base sur les nom de colonnes pour gérer seul les formules crées. Ces noms ne doivent pas changer ! (si c'est le cas, renommer les colonnes dans les sources pour s'adapter à votre existant, SINON corriger les formules pour s'adapter à la nouvelle situation (voir ci-dessous).
Création de la clé ID :
NOTE : C'est cette formule qu'il faudrait adapter en cas de changement de noms de colonnes dans les extractions !
= CONCATENER(MAJUSCULE(GAUCHE([@NOM];paramid));MINUSCULE(GAUCHE([@PRENOM];paramid));"_";TEXTE([@[NAISSANCE]];"JJMMAA"))
la formule utilise les 2 caractères en partant de la Gauche de la colonne 'Nom'
=> GAUCHE ( [@NOM] ; paramid )
- [@Nom] est la référence à utiliser dans la Table (Rappel : XL nomme tout dans les tables :-)
- 'paramid' est le nom donné à la cellule qui contient le paramétrage de longueur de la clé (sur feuille 'Intro' avant les dates à paramétrer...)
la formule ajoute MAJUSCULE() ou MINUSCULE() certaines parties de la clé ID
=> MAJUSCULE(GAUCHE([@NOM];paramid)
La formule s'assure aussi de formater la date grace à TEXTE() (il sest possible de ne tester QUE sur l'année en mettant "aaaa")
=> TEXTE([@[NAISSANCE]];"JJMMAA")
Le tout est mis bout à bout avec CONCATENER()
=> CONCATENER (formuleNOM;formulePRENOM;formuleDATE)
Que faire pour adapter les formules de clés ID si les extraction ont changé ?
Si une colonne est renommée sur l'extraction, le formule ne la trouve plus... (exemple MAJUSCULE(GAUCHE(#REF!;paramid))Test correspondance entre les feuilles XL :
=NB.SI(CO!A:A;[@IDONDE])
La formule NB.SI () compte le nombre d'ID d'un Onglet trouvé sur l'autre Onglet
- CO!A:A = la zone de recherche est la feuille 'CO', Colonne A
- [@IDONDE]= on cherche l'ID fabriqué
La formule comptera 0, 1 ou plus...
Verdict des correspondances (feuille CAF) :
=SI(ET([@okonde]=0;[@okco]=0);"CAF only";SI(ET([@okonde]>0;[@okco]>0);"ok";SI(ET([@okonde]>0;[@okco]=0);"Onde only";"Concerto only")))
sur la feuille CAF une formule SI() compare les situations
si chacun des tests = 0, alors XL indique 'CAF only' => ET([@okonde]=0;[@okco]=0);"CAF only"
sinon, si chacun des tests > 0 (et donc présent dans chaque source) => ET([@okonde]>0;[@okco]>0);"ok"
si ce 2ème test est encore négatif, alors teste si dans Onde seulement => ET([@okonde]>0;[@okco]=0);"Onde only";
et si ce n'est pas le cas, le dernier scénario est alors "Concerto only"
Utilisation des dates paramétrées :
=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' ...
sur l'onglet CAF une formule SI() cherche donc à savoir si la date de naissance sur la ligne est comprise dans cette fourchette :-)