{"text_format":"1","workbook":"xlsx","n_sheets":2,"sheets":["Menu","Mode d'emploi"],"not_rendered":{"charts":0,"images":10,"macros":1,"external_links":0,"defined_names":11,"comments":0,"pivot_tables":0},"unopened":[],"bounds":{"document_bytes":16777216,"sheet_cells":100000,"rows_first":2000,"rows_last":500,"rows_between":2000,"halvings":0,"cell_chars":16384,"cell_head_chars":4096},"cells_cut":0,"cells_raw":0,"read":"whole","date_system":"1900"}

{"sheet":"Menu","index":0,"n_rows":102,"n_cols":18,"rows_kept":102,"cut":false,"hidden":false,"merged":[[0,0,0,7],[11,4,11,5]]}
["Analyse de l'ensemble des paramètres pour une ou plusieurs communes",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"dept_num","departements"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"LEFT(département,SEARCH(\" - \",département)-1)","v":{"$error":"#VALUE!"}},"01 - Ain"]
[null,null,null,null,null,null,null,"Communes à analyser",null,null,null,null,null,null,null,null,null,"02 - Aisne"]
[null,"Département",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"03 - Allier"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"04 - Alpes-de-Haute-Provence"]
[null,null,null,null,null,null,null,null," ",null,null,null,null,null,null,null,null,"05 - Hautes-Alpes"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"06 - Alpes-Maritimes"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"07 - Ardèche"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"08 - Ardennes"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"09 - Ariège"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"10 - Aube"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"11 - Aude"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"12 - Aveyron"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"13 - Bouches-du-Rhône"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"14 - Calvados"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"15 - Cantal"]
[" ",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"16 - Charente"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"17 - Charente-Maritime"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"18 - Cher"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"19 - Corrèze"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"2A - Corse-du-Sud"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"2B - Haute-Corse"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"21 - Côte-d'Or"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"22 - Côtes-d'Armor"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"23 - Creuse"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"24 - Dordogne"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"25 - Doubs"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"26 - Drôme"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"27 - Eure"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"28 - Eure-et-Loir"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"29 - Finistère"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"30 - Gard"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"31 - Haute-Garonne"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"32 - Gers"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"33 - Gironde"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"34 - Hérault"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"35 - Ille-et-Vilaine"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"36 - Indre"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"37 - Indre-et-Loire"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"38 - Isère"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"39 - Jura"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"40 - Landes"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"41 - Loir-et-Cher"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"42 - Loire"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"43 - Haute-Loire"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"44 - Loire-Atlantique"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"45 - Loiret"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"46 - Lot"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"47 - Lot-et-Garonne"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"48 - Lozère"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"49 - Maine-et-Loire"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"50 - Manche"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"51 - Marne"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"52 - Haute-Marne"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"53 - Mayenne"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"54 - Meurthe-et-Moselle"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"55 - Meuse"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"56 - Morbihan"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"57 - Moselle"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"58 - Nièvre"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"59 - Nord"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"60 - Oise"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"61 - Orne"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"62 - Pas-de-Calais"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"63 - Puy-de-Dôme"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"64 - Pyrénées-atlantiques"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"65 - Hautes-Pyrénées"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"66 - Pyrénées-Orientales"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"67 - Bas-Rhin"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"68 - Haut-Rhin"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"69 - Rhône"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"70 - Haute-Saône"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"71 - Saône-et-Loire"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"72 - Sarthe"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"73 - Savoie"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"74 - Haute-Savoie"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"75 - Paris"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"76 - Seine-Maritime"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"77 - Seine-et-Marne"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"78 - Yvelines"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"79 - Deux-Sèvres"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"80 - Somme"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"81 - Tarn"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"82 - Tarn-et-Garonne"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"83 - Var"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"84 - Vaucluse"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"85 - Vendée"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"86 - Vienne"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"87 - Haute-Vienne"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"88 - Vosges"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"89 - Yonne"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"90 - Territoire de Belfort"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"91 - Essonne"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"92 - Hauts-de-Seine"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"93 - Seine-Saint-Denis"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"94 - Val-de-Marne"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"95 - Val-d'Oise"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"971 - Guadeloupe"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"972 - Martinique"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"973 - Guyane"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"974 - La Réunion"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"976 - Mayotte"]

{"sheet":"Mode d'emploi","index":1,"n_rows":85,"n_cols":27,"rows_kept":85,"cut":false,"hidden":false,"merged":[[84,1,85,11],[0,1,0,10],[77,1,77,10],[20,1,20,10],[3,1,3,10],[4,1,4,10],[2,1,2,10],[56,1,56,9],[18,2,18,10],[17,2,17,10],[5,1,5,12],[83,1,83,10],[64,1,64,9],[55,1,55,9],[32,1,32,10],[40,1,40,10],[82,1,82,11],[45,1,45,10],[33,1,33,12],[59,1,59,11],[46,1,46,10],[78,1,78,10],[58,1,58,10],[51,1,51,10]]}
[null,"Mode d'emploi"]
[]
[null,"Télécharger le fichier \"Résultats contrôle sanitaire eau.xlsm\" dans un nouveau dossier"]
[null,"Télécharger le fichier dis-202x.zip dans le même dossier à partir de l'URL"]
[null,"https://www.data.gouv.fr/fr/datasets/resultats-du-controle-sanitaire-de-leau-distribuee-commune-par-commune/"]
[null,"Un fichier par année mis à jour chaque début de mois avec les données du mois n-2 (par exemple au 1/10, les données de janvier à août)"]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,null,"Une documentation avec descriptif des fichiers est disponible  "]
[null,null,"https://www.data.gouv.fr/fr/datasets/r/36afc708-42dc-4a89-b039-7fde6bcc83d8"]
[]
[null,"Extraire les 3 fichiers de dis-202x.zip et les renommer comme ci-dessous dans le dossier"]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,"Ouvrir le fichier \"Résultats contrôle sanitaire eau.xlsm\""]
[null,"Si un message d'avertissement indiquant que les macros ont été désactivées s'affiche, cliquer sur Activer le contenu"]
[]
[]
[]
[]
[]
[]
[null,"A partir de l'onglet 'Menu', choisir un département à l'aide de la liste déroulante"]
[]
[]
[]
[]
[null,"Cliquer sur le bouton Phase 1"]
[null,"Un fichier RESULT_n°dept est créé dans le dossier (contient tous les résultats pour le département)"]
[]
[]
[]
[]
[null,"3 onglets sont créés dans le fichier Excel : RESULT_n°dept - PLV_DEPT  -  UDI_DEPT"]
[]
[]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"JOUY-LE-MOUTIER"]
[null,"Saisir une ou plusieurs communes du département à l'aide des listes déroulantes en dessous du libellé \"Communes à analyser\"",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"MENUCOURT"]
[null,"Nota : Si vous suivez régulièrement les résultats de plusieurs communes, saisissez-les une première fois et recopiez les noms en cliquant sur bouton 'Recopier mes communes' dans l'onglet Menu. Pour une nouvelle utilisation, il suffit de cliquer sur le bouton 'Mes communes' pour les recopier automatiquement sous 'Communes à analyser'. "]
[]
[null,"Cliquer sur le bouton Phase 2",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"NEUVILLE-SUR-OISE"]
[null,"3 nouveaux onglets RESULT_COMMUNES  -  UDI_COMMUNES  -  PLV_COMMUNES sont créés.",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"OSNY"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"PONTOISE"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"PUISEUX-PONTOISE"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"SAINT-OUEN-L'AUMONE"]
[]
[null,"Exploiter les résultats à partir de l'onglet RESULT_COMMUNES à l'aide des filtres"]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,"Nota : Le commentaire de conformité (colonne conclusionprel) porte sur le prélèvement et non pas sur le seul paramètre non conforme. Le regroupement opéré permet de disposer de toutes les informations des prélèvements d'une commune mais, de ce fait, le commentaire se retrouve pour chaque paramètre analysé. Parfois, le commentaire ne contient pas le nom du paramètre (libmajparametre) en défaut. Il faut alors regarder la valeur des paramètres dans la colonne rqana et la comparer aux valeurs des colonnes limitqual et refqual. "]
[null,"Afin de faciliter la recherche de non-conformité, une colonne 'Conformité' synthétise les 4 indications de conformité. Elle prend soit la valeur C (conforme), soit la valeur NC (non-conforme) si une seule des 4 plvconformite... prend la valeur N. En utilisant un filtre, on visualise uniquement les prélèvements non-conformes.  "]
[]
[]
[]
[null,"Pour analyser d'autres communes du même département : effacer les noms des communes, choisir le nom des nouvelles communes dans les listes déroulantes, cliquer sur le bouton Phase 2"]
[null,"Le temps de traitement est compris entre 5 et 20 minutes en fonction de la puissance du PC, du mois de l'année, du nombre de lignes qui varie selon le département, du nombre de communes à analyser..."]
[null,"Le bouton Réinitialiser supprime tous les onglets sauf Menu et Mode d'emploi. Il efface les noms des communes à analyser mais pas les noms des communes recopiés. A chaque nouveau téléchargement des données, il est nécéssaire de réinitialiser."]