{"text_format":"1","workbook":"xlsx","n_sheets":5,"sheets":["Collectes","Suivi","Tarifs","NotesSuiviPluriAnnuel","SuiviPluriAnnuel"],"not_rendered":{"charts":0,"images":0,"macros":1,"external_links":0,"defined_names":10,"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":"Collectes","index":0,"n_rows":27,"n_cols":6,"rows_kept":27,"cut":false,"hidden":false,"merged":[]}
["num Collecte","Date","idTypMatériel","Kg collectés","Kg Recyclés","Kg réemployés"]
[1,"2019-01-16",1,150,122,0]
[null,null,2,6,4,2]
[2,"2019-02-18",1,135,101,0]
[null,null,3,35,29,6]
[null,null,7,40,20,0]
[3,"2019-03-14",1,180,138,0]
[null,null,2,7,2,2]
[null,null,4,8,4,0]
[4,"2019-04-13",1,234,172,0]
[5,"2019-05-11",5,3,1,1]
[null,null,7,43,6,0]
[6,"2019-06-13",1,276,203,0]
[null,null,2,6,2,2]
[null,null,6,2,1,0]
[7,"2019-07-16",1,200,149,0]
[null,null,2,5,2,1]
[8,"2019-08-18",3,23,19,4]
[9,"2019-09-14",1,100,89,0]
[null,null,2,7,2,2]
[10,"2019-10-14",1,230,179,0]
[null,null,2,6,4,2]
[11,"2019-11-08",1,122,92,0]
[null,null,3,30,25,5]
[null,null,7,45,22,0]
[12,"2019-12-14",1,180,130,0]
[null,null,2,7,2,2]

{"sheet":"Suivi","index":1,"n_rows":30,"n_cols":11,"rows_kept":30,"cut":false,"hidden":false,"merged":[[2,10,5,14],[29,1,29,2],[20,3,20,5],[21,1,21,2],[22,1,22,2],[23,1,23,2],[24,1,24,2],[25,1,25,2],[26,1,26,2],[27,1,27,2],[28,1,28,2],[20,6,20,8],[10,1,10,2],[11,1,11,2],[12,1,12,2],[13,1,13,2],[14,1,14,2],[15,1,15,2],[16,1,16,2],[17,1,17,2],[8,7,8,8],[4,0,4,1],[4,3,4,4],[9,1,9,2],[8,3,8,4],[8,5,8,6]]}
["Client","Salmin Immobilier"," il faudrait avoir une table des clients… "]
["Année","Type abonnement","Formule","Nombre de collectes","Poids moyen\npar collecte","Coût global annuel du tri","Gain annuel du tri","Total annuel à régler","Economie réalisée grâce au tri"]
[{"$f":"YEAR(MAX(Collectes!B2:B101))","v":2019},4,{"$f":"VLOOKUP(B3,TabAbonnements,2,0)","v":"Total Recycle"},{"$f":"MAX(numCollecte)","v":12},{"$f":"ROUND(D19/D3,2)","v":173.33},{"$f":"VLOOKUP(TypA,TabAbonnements,3,0)+E19","v":1900.5},{"$f":"SUM(G19,I19)","v":329.55},{"$f":"F3-G3","v":1570.95},{"$f":"G3/F3","v":0.17340173638516179},null,"Deux indicateurs de performance synthétisent ces résultats (Performance Recyclage et Performance Réemploi). Ils s’obtiennent en rapportant le nombre de cas « OK », au nombre de cas où l’objectif a été mesuré"]
[]
["Performance Recyclage",null,{"$f":"COUNTIF(F23:F30,\"OK\")/COUNT(D23:D30)","v":0.5714285714285714},"Performance Réemploi",null,{"$f":"COUNTIF(I23:I30,\"OK\")/COUNT(G23:G30)","v":0.42857142857142855}]
[]
[]
[]
[null,null,null,"Collecte",null,"Recyclage",null,"Réemploi"]
["Code","Matériels",null,"Kilos collectés","Coût collecte","Kilos recyclés","Gain recyclage","Kilos réemployés","Gain réemploi"]
[1,"Papiers et cartons",null,{"$f":"SUMIFS(Collectes!$D$2:$D$27,Collectes!$C$2:$C$27,Suivi!A11)","v":1807},{"$f":"D11*Tarifs!E4","v":903.5},{"$f":"SUMIFS(Collectes!$E$2:$E$27,Collectes!$C$2:$C$27,Suivi!A11)","v":1375},{"$f":"F11*Tarifs!F4","v":275},{"$f":"SUMIFS(Collectes!$F$2:$F$27,Collectes!$C$2:$C$27,Suivi!A11)","v":0},{"$f":"H11*Tarifs!G4","v":0}]
[2,"Cartouches d'encres",null,{"$f":"SUMIFS(Collectes!$D$2:$D$27,Collectes!$C$2:$C$27,Suivi!A12)","v":44},{"$f":"D12*Tarifs!E5","v":88},{"$f":"SUMIFS(Collectes!$E$2:$E$27,Collectes!$C$2:$C$27,Suivi!A12)","v":18},{"$f":"F12*Tarifs!F5","v":9},{"$f":"SUMIFS(Collectes!$F$2:$F$27,Collectes!$C$2:$C$27,Suivi!A12)","v":13},{"$f":"H12*Tarifs!G5","v":13}]
[3,"Matériels informatiques",null,{"$f":"SUMIFS(Collectes!$D$2:$D$27,Collectes!$C$2:$C$27,Suivi!A13)","v":88},{"$f":"D13*Tarifs!E6","v":88},{"$f":"SUMIFS(Collectes!$E$2:$E$27,Collectes!$C$2:$C$27,Suivi!A13)","v":73},{"$f":"F13*Tarifs!F6","v":14.600000000000001},{"$f":"SUMIFS(Collectes!$F$2:$F$27,Collectes!$C$2:$C$27,Suivi!A13)","v":15},{"$f":"H13*Tarifs!G6","v":3.75}]
[4,"Autres supports de données",null,{"$f":"SUMIFS(Collectes!$D$2:$D$27,Collectes!$C$2:$C$27,Suivi!A14)","v":8},{"$f":"D14*Tarifs!E7","v":40},{"$f":"SUMIFS(Collectes!$E$2:$E$27,Collectes!$C$2:$C$27,Suivi!A14)","v":4},{"$f":"F14*Tarifs!F7","v":4},{"$f":"SUMIFS(Collectes!$F$2:$F$27,Collectes!$C$2:$C$27,Suivi!A14)","v":0},{"$f":"H14*Tarifs!G7","v":0}]
[5,"Piles et accus",null,{"$f":"SUMIFS(Collectes!$D$2:$D$27,Collectes!$C$2:$C$27,Suivi!A15)","v":3},{"$f":"D15*Tarifs!E8","v":15},{"$f":"SUMIFS(Collectes!$E$2:$E$27,Collectes!$C$2:$C$27,Suivi!A15)","v":1},{"$f":"F15*Tarifs!F8","v":1.25},{"$f":"SUMIFS(Collectes!$F$2:$F$27,Collectes!$C$2:$C$27,Suivi!A15)","v":1},{"$f":"H15*Tarifs!G8","v":1.5}]
[6,"Lampes, néons et ampoules",null,{"$f":"SUMIFS(Collectes!$D$2:$D$27,Collectes!$C$2:$C$27,Suivi!A16)","v":2},{"$f":"D16*Tarifs!E9","v":10},{"$f":"SUMIFS(Collectes!$E$2:$E$27,Collectes!$C$2:$C$27,Suivi!A16)","v":1},{"$f":"F16*Tarifs!F9","v":0.25},{"$f":"SUMIFS(Collectes!$F$2:$F$27,Collectes!$C$2:$C$27,Suivi!A16)","v":0},{"$f":"H16*Tarifs!G9","v":0}]
[7,"Autres petits déchets recyclables de bureau",null,{"$f":"SUMIFS(Collectes!$D$2:$D$27,Collectes!$C$2:$C$27,Suivi!A17)","v":128},{"$f":"D17*Tarifs!E10","v":256},{"$f":"SUMIFS(Collectes!$E$2:$E$27,Collectes!$C$2:$C$27,Suivi!A17)","v":48},{"$f":"F17*Tarifs!F10","v":7.1999999999999993},{"$f":"SUMIFS(Collectes!$F$2:$F$27,Collectes!$C$2:$C$27,Suivi!A17)","v":0},{"$f":"H17*Tarifs!G10","v":0}]
[8,"Mobilier de bureau",null,{"$f":"SUMIFS(Collectes!$D$2:$D$27,Collectes!$C$2:$C$27,Suivi!A18)","v":0},{"$f":"D18*Tarifs!E11","v":0},{"$f":"SUMIFS(Collectes!$E$2:$E$27,Collectes!$C$2:$C$27,Suivi!A18)","v":0},{"$f":"F18*Tarifs!F11","v":0},{"$f":"SUMIFS(Collectes!$F$2:$F$27,Collectes!$C$2:$C$27,Suivi!A18)","v":0},{"$f":"H18*Tarifs!G11","v":0}]
[null,null,"Total",{"$f":"SUM(D11:D18)","v":2080},{"$f":"SUM(E11:E18)","v":1400.5},1520,311.3,29,18.25]
[]
[null,null,null,"Recyclage",null,null,"Réemploi"]
["Code","Matériels",null,"%","Objectif","Niveau de votre entreprise","%","Objectif"," Niveau de votre entreprise"]
[1,"Papiers et cartons",null,{"$f":"F11/D11","v":0.76092971776425011},0.75,{"$f":"IF(D23>E23,\"OK\",\"\")","v":"OK"},{"$f":"H11/D11","v":0},0,{"$f":"IF(G23>H23,\"OK\",\"\")","v":""}]
[2,"Cartouches d'encres",null,0.40909090909090912,0.65,{"$f":"IF(D24>E24,\"OK\",\"\")","v":""},0.29545454545454547,0.25,{"$f":"IF(G24>H24,\"OK\",\"\")","v":"OK"}]
[3,"Matériels informatiques",null,0.82954545454545459,0.6,"OK",0.17045454545454544,0.1,"OK"]
[4,"Autres supports de données",null,0.5,0.35,"OK",0,0.15,""]
[5,"Piles et accus",null,0.33333333333333331,0.5,"",0.33333333333333331,0.05,"OK"]
[6,"Lampes, néons et ampoules",null,0.5,0.15,"OK",0,0.05,""]
[7,"Autres petits déchets recyclables de bureau",null,0.375,0.4,"",0,0,""]
[8,"Mobilier de bureau"]

{"sheet":"Tarifs","index":2,"n_rows":19,"n_cols":9,"rows_kept":19,"cut":false,"hidden":false,"merged":[]}
[]
[null,"Tarifs de matériels prélevés"]
[null,"Identifiant\n","Matériels\n","Contient des données","Prix du Kg collecté","Prix du kilo recyclé","Prix du Kilo réemployé","% moyen de recyclage","% moyen de réemploi"]
[null,1,"Papiers et cartons","O",0.5,0.2,0,0.75,0]
[null,2,"Cartouches d'encres","N",2,0.5,1,0.65,0.25]
[null,3,"Matériels informatiques\n","O",1,0.2,0.25,0.6,0.1]
[null,4,"autres supports de données","O",5,1,1.5,0.35,0.15]
[null,5,"Piles et accus","N",5,1.25,1.5,0.5,0.05]
[null,6,"Lampes, néons et ampoules","N",5,0.25,2,0.15,0.05]
[null,7,"Autres petits déchets recyclables de bureau","N",2,0.15,0,0.4,0]
[null,8,"Mobilier de bureau","N",1,0.3,0.5,0.2,0.15]
[]
[null,null,null,"contient des données devant donc être détruites=> traitement spécifique"]
[null,"Abonnements"]
[null,"Type abonnement","Formule","Tarif annuel"]
[null,1,"Recycle Papier",50]
[null,2,"Recycle Cartouche",60]
[null,3,"Recycle Data",300]
[null,4,"Total Recycle",500]

{"sheet":"NotesSuiviPluriAnnuel","index":3,"n_rows":85,"n_cols":7,"rows_kept":85,"cut":false,"hidden":false,"merged":[]}
["Remarques :",null,"il ne s'agit pas ici de critiques relatives à un sujet d'examen (qui est forcément limité compte tenu des temps impartis au traitement d'un dossier)"]
[null,null,"il s'agit de proposer des améliorations a minima"]
[]
["1) la macro à compléter se contente de mettre en archiveles données annuelles d'un client"]
["2) cela suppose qu'il y a un fichier par client (pb d'optimisation)"]
["3) une feuille d'archivage est normalement sécurisée (masquée et protégée)"]
["4) la structure de la table pluriannuelle doit systématiquement commencer en A1"]
["5) la macro demandée ne tient pas compte de ce que l'on peut appuyer plusieurs fois sur le bouton et donc peut générer des doublons (comme dans tout archivage)"]
[]
["il convient donc de la restructurer, et d'utilser pour simplifier le développement la programmation modulaire."]
[]
["Macro demandée dans l'énoncé",null,null,null,null,null,"il faut rajouter :"]
["Sub CopierData()",null,null,null,null,null,"1)"]
["'",null,null,null,null,null,"Sub EnleverDoublons()"]
["' CopierData Macro",null,null,null,null,null,"'"]
["'",null,null,null,null,null,"' EnleverDoublons Macro"]
["","ouvrir la feuille pluriannuel avant avec :",null,null,null,null,"'"]
["'"]
["    Sheets(\"SuiviPluriAnnuel\").Select",null,null,null,null,null,"'"]
["    Rows(\"2:2\").Select",null,null,null,null,null,"    Sheets(\"SuiviPluriAnnuel\").Select"]
["    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove",null,null,null,null,null,"    Range(\"A1:L1\").Select"]
["    Range(\"A2\").Select",null,null,null,null,null,"    Selection.Copy"]
["    Sheets(\"Suivi\").Select",null,null,null,null,null,"    Range(\"N1\").Select"]
["    Range(\"B1\").Select",null,null,null,null,null,"    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _"]
["    Selection.Copy",null,null,null,null,null,"        :=False, Transpose:=False"]
["    Sheets(\"SuiviPluriAnnuel\").Select",null,null,null,null,null,"    Application.CutCopyMode = False"]
["    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _",null,null,null,null,null,"    Application.CutCopyMode = False"]
["        :=False, Transpose:=False",null,null,null,null,null,"    Application.CutCopyMode = False"]
["    Sheets(\"Suivi\").Select",null,null,null,null,null,"    Columns(\"A:L\").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _"]
["    Range(\"A3\").Select",null,null,null,null,null,"        \"N1:Y1\"), Unique:=True"]
["    Application.CutCopyMode = False",null,null,null,null,null,"    Columns(\"A:M\").Select"]
["    Selection.Copy",null,null,null,null,null,"    Range(\"M1\").Activate"]
["    Sheets(\"SuiviPluriAnnuel\").Select",null,null,null,null,null,"    Selection.Delete Shift:=xlToLeft"]
["    Range(\"B2\").Select",null,null,null,null,null,"    Range(\"A1\").Select"]
["    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _",null,null,null,null,null,"End Sub"]
["        :=False, Transpose:=False"]
["    Sheets(\"Suivi\").Select",null,null,null,null,null,"2) "]
["    Range(\"B3\").Select",null,null,null,null,null,"Sub FermerArchive()"]
["    Application.CutCopyMode = False",null,null,null,null,null,"'"]
["    Selection.Copy",null,null,null,null,null,"' FermerArchive Macro"]
["    Sheets(\"SuiviPluriAnnuel\").Select",null,null,null,null,null,"'"]
["    Range(\"C2\").Select"]
["    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _",null,null,null,null,null,"'"]
["        :=False, Transpose:=False",null,null,null,null,null,"    Sheets(\"SuiviPluriAnnuel\").Select"]
["    Sheets(\"Suivi\").Select",null,null,null,null,null,"    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True"]
["    Range(\"C3\").Select",null,null,null,null,null,"    Sheets(\"SuiviPluriAnnuel\").Select"]
["    Application.CutCopyMode = False",null,null,null,null,null,"    ActiveWindow.SelectedSheets.Visible = False"]
["    Selection.Copy",null,null,null,null,null,"End Sub"]
["    Sheets(\"SuiviPluriAnnuel\").Select",null,null,null,null,null,"puis :"]
["    Range(\"D2\").Select",null,null,null,null,null,"3)"]
["    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _",null,null,null,null,null,"Sub OuvrirArchive()"]
["        :=False, Transpose:=False",null,null,null,null,null,"'"]
["    Sheets(\"Suivi\").Select",null,null,null,null,null,"' OuvrirArchive Macro"]
["    Range(\"D3\").Select",null,null,null,null,null,"'"]
["    Application.CutCopyMode = False"]
["    Selection.Copy",null,null,null,null,null,"'"]
["    Sheets(\"SuiviPluriAnnuel\").Select",null,null,null,null,null,"    Sheets(\"NotesSuiviPluriAnnuel\").Select"]
["    Range(\"E2\").Select",null,null,null,null,null,"    Sheets(\"SuiviPluriAnnuel\").Visible = True"]
["    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _",null,null,null,null,null,"    Sheets(\"SuiviPluriAnnuel\").Select"]
["        :=False, Transpose:=False",null,null,null,null,null,"    ActiveSheet.Unprotect"]
["    Sheets(\"Suivi\").Select",null,null,null,null,null,"End Sub"]
["    Range(\"E3:I3\").Select"]
["    Application.CutCopyMode = False",null,null,null,null,null,"enfin il faut écrire la macro composée de ces macros élémentaires :"]
["    Selection.Copy",null,null,null,null,null,"Sub Archiver()"]
["    Sheets(\"SuiviPluriAnnuel\").Select",null,null,null,null,null,"    OuvrirArchive"]
["    Range(\"F2\").Select",null,null,null,null,null,"    CopierData"]
["    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _",null,null,null,null,null,"    EnleverDoublons"]
["        :=False, Transpose:=False",null,null,null,null,null,"    FermerArchive"]
["    Sheets(\"Suivi\").Select",null,null,null,null,null,"End Sub"]
["    Range(\"C5\").Select"]
["    Application.CutCopyMode = False"]
["    Selection.Copy"]
["    Sheets(\"SuiviPluriAnnuel\").Select"]
["    Range(\"K2\").Select"]
["    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _"]
["        :=False, Transpose:=False"]
["    Sheets(\"Suivi\").Select"]
["    Range(\"F5\").Select"]
["    Application.CutCopyMode = False"]
["    Selection.Copy"]
["    Sheets(\"SuiviPluriAnnuel\").Select"]
["    Range(\"L2\").Select"]
["    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _"]
["        :=False, Transpose:=False"]
["End Sub"]

{"sheet":"SuiviPluriAnnuel","index":4,"n_rows":4,"n_cols":12,"rows_kept":4,"cut":false,"hidden":true,"merged":[]}
["Client","Année","Type abonnement","Formule","Nombre de collectes","Poids moyen","Coût global annuel du tri","Gain annuel du tri","Total annuel à régler","… réalisée grâce au tri","Performance Recyclage","Performance Réemploi"]
["Salmin Immobilier",2019,4,"Total Recycle",12,173.33,1900.5,329.55,1570.95,0.17340173638516179,0.5714285714285714,0.42857142857142855]
["Salmin Immobilier",2018,4,"Total Recycle",10,160.56,1200.3399999999999,223.5,976.84,0.19,0.5714285714285714,0.42857142857142855]
["Salmin Immobilier",2017,4,"Total Recycle",6,154.32,854.25,55.26,798.99,0.06,67,0.42857142857142855]