{"text_format":"1","workbook":"xlsx","n_sheets":4,"sheets":["Instructions","Analyse Notes","Analyse par groupe","Graphique par groupe"],"not_rendered":{"charts":4,"images":0,"macros":1,"external_links":0,"defined_names":0,"comments":0,"pivot_tables":3},"unopened":[{"sheet":"Graphique par groupe","index":3,"reason":"not_a_worksheet"}],"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":"Instructions","index":0,"n_rows":11,"n_cols":3,"rows_kept":11,"cut":false,"hidden":false,"merged":[[3,0,4,0],[3,2,4,2]]}
["Étape","Couleur","Description"]
[1,null,"On copie les numéros de groupe, matricules et les notes des étudiants dans les colonnes A, B et C de la feuille de travail \"Analyse Notes\". Il faut faire attention de faire un collage spécial avec valeur seulement. On présume que vous avez calculé la note globale de tous les étudiants de tous les groupes. Vous pouvez aussi inclure une nouvelle feuille dans laquelle vous faite le calcul des notes globales et faire les liens appropriés avec des formules. Laissez la note vide pour les abandons."]
[]
[2,null,"On recopie les formules contenue dans les colonnes D, E et F de la feuille de travail \"Analyse Notes\" pour chaque étudiant."]
[]
[]
[3,null,"On actualise les données avec le bouton \"Actualiser tout\" de la feuille \"Analyse Notes\"."]
[]
[4,null,"On sélectionne la méthode à utiliser : A (clipping), B (seuils) ou C (Max et largeur). Un tableau au centre de la feuille permet de faire cette sélection avec la souris. On manipule les données correspondant à la méthode choisie. Les graphiques de cette page se modifient automatiquement. Cependant, dans le cas de statistiques par groupe, il faut appuyer sur le bouton \"Actualiser tout\". De plus, on doit ajuster le graphique par groupe en limitant la zone de provenance des données sources aux seules colonnes contenant des données de groupe."]
[]
[5,null,"Une fois l'analyse terminée, on copie les matricules et les lettres obtenues (colonnes B et D de la feuille \"Analyse Notes\") pour remettre dans la feuille de calcul ayant servie au calcul de la note globale. Ici encore on peut utiliser des formules pour rapatrier ces résultats dans une autre feuille de calcul."]

{"sheet":"Analyse Notes","index":1,"n_rows":101,"n_cols":28,"rows_kept":101,"cut":false,"hidden":false,"merged":[[23,15,23,16],[20,12,20,13],[0,15,0,18],[0,19,0,22],[15,15,15,16],[19,15,19,16]]}
["Groupe","Matricule","Note","Lettre","Points","Arondies","NB Arondies",null,"Sigma","Sigma2","Nombre",null,"NB Lettre",null,null,"Idéal",null,null,null,"Final"]
["A01",null,70,{"$f":"IF(C2=\"\",\"\",VLOOKUP(C2,$T$3:$Y$14,6,TRUE))","v":"C+"},{"$f":"IF(D2=\"\",\"\",VLOOKUP(D2,$O$3:$X$14,10,FALSE))","v":2.2999999999999998},{"$f":"IF(C2=\"\",\"\",TRUNC(C2,0))","v":70},"Arondies","Total",{"$f":"SUM(I3:I101)","v":1500},{"$f":"SUM(J3:J101)","v":111300},{"$f":"SUM(K3:K101)","v":21},null,"Lettre","Total",null,"min","max","NB","%","min","max","NB","%",null,null,"Grille de conversion ",null,"Grille de conversion abrégée"]
["A01",null,82,{"$f":"IF(C3=\"\",\"\",VLOOKUP(C3,$T$3:$Y$14,6,TRUE))","v":"A-"},{"$f":"IF(D3=\"\",\"\",VLOOKUP(D3,$O$3:$X$14,10,FALSE))","v":3.7},82,32,1,{"$f":"IF(L3,H3*G3,\"\")","v":32},{"$f":"IF(L3,G3*G3*H3,\"\")","v":1024},{"$f":"IF(L3,H3,\"\")","v":1},true,"E",2,"E",-10,-1.375,{"$f":"ROUND($N$15*S3,0)","v":0},{"$f":"1-0.997","v":3.0000000000000027E-3},0,{"$f":"ROUNDDOWN($Q$36+Q3*$Q$37,1)","v":58.1},{"$f":"IF(ISERROR(VLOOKUP(O3,$M$3:$N$14,2,FALSE)),0,VLOOKUP(O3,$M$3:$N$14,2,FALSE))","v":2},{"$f":"V3/$N$15","v":9.5238095238095233E-2},0,"E",{"$f":"CONCATENATE(Y3,IF(LEN(Y3)=1,\"  : \",\" : \"),IF(Y3<>\"E\",CONCATENATE(FIXED(T3,1),\" <= \"),\"          \"),\"note\",IF(Y3<>\"A+\",CONCATENATE(\" < \",FIXED(U3,1)),\"\"))","v":"E  :           note < 58.1"},null,{"$f":"CONCATENATE(Y3,IF(LEN(Y3)=1,\":\",\":\"),IF(Y3<>\"E\",CONCATENATE(\"[\",FIXED(T3,1),\";\"),\"[0;\"),IF(Y3<>\"A+\",CONCATENATE(FIXED(U3,1),\")\"),\"100]\"))","v":"E:[0;58.1)"}]
["A01",null,80,"B+",3.3,80,33,1,{"$f":"IF(L4,H4*G4,\"\")","v":33},{"$f":"IF(L4,G4*G4*H4,\"\")","v":1089},{"$f":"IF(L4,H4,\"\")","v":1},true,"C-",1,"D",{"$f":"Q3","v":-1.375},-1.125,{"$f":"ROUND($N$15*S4,0)","v":0},{"$f":"S14","v":9.199999999999986E-3},{"$f":"U3","v":58.1},{"$f":"ROUNDDOWN($Q$36+Q4*$Q$37,1)","v":60.9},0,0,1,"D",{"$f":"CONCATENATE(Y4,IF(LEN(Y4)=1,\"  : \",\" : \"),IF(Y4<>\"E\",CONCATENATE(FIXED(T4,1),\" <= \"),\"          \"),\"note\",IF(Y4<>\"A+\",CONCATENATE(\" < \",FIXED(U4,1)),\"\"))","v":"D  : 58.1 <= note < 60.9"},null,{"$f":"CONCATENATE(Y4,IF(LEN(Y4)=1,\":\",\":\"),IF(Y4<>\"E\",CONCATENATE(\"[\",FIXED(T4,1),\";\"),\"[0;\"),IF(Y4<>\"A+\",CONCATENATE(FIXED(U4,1),\")\"),\"100]\"))","v":"D:[58.1;60.9)"}]
["A01",null,72,"C+",2.2999999999999998,72,64,1,64,4096,1,true,"C",1,"D+",{"$f":"Q4","v":-1.125},-0.875,1,{"$f":"S13","v":2.7900000000000036E-2},{"$f":"U4","v":60.9},63.7,0,0,1.3,"D+","D+ : 60.9 <= note < 63.7",null,"D+:[60.9;63.7)"]
["A01",null,80,"B+",3.3,80,67,1,67,4489,1,true,"C+",4,"C-",-0.875,-0.625,1,{"$f":"S12","v":6.5500000000000003E-2},63.7,66.5,1,4.7619047619047616E-2,1.7,"C-","C- : 63.7 <= note < 66.5",null,"C-:[63.7;66.5)"]
["A01",null,33,"E",0,33,69,1,69,4761,1,true,"B-",3,"C",-0.625,-0.375,3,{"$f":"S11","v":0.121},66.5,69.3,1,4.7619047619047616E-2,2,"C","C  : 66.5 <= note < 69.3",null,"C:[66.5;69.3)"]
["A01",null,83,"A-",3.7,83,70,3,210,14700,3,true,"B",2,"C+",-0.375,-0.125,4,{"$f":"S10","v":0.17469999999999997},69.3,72.099999999999994,4,0.19047619047619047,2.2999999999999998,"C+","C+ : 69.3 <= note < 72.1",null,"C+:[69.3;72.1)"]
["A01",null,70,"C+",2.2999999999999998,70,72,2,144,10368,2,true,"B+",1,"B-",-0.125,0.125,4,{"$f":"(0.5987-0.5)*2","v":0.19740000000000002},72.099999999999994,74.900000000000006,3,0.14285714285714285,2.7,"B-","B- : 72.1 <= note < 74.9",null,"B-:[72.1;74.9)"]
["A02",null,null,"","","",73,1,73,5329,1,true,"A-",4,"B",0.125,0.375,4,{"$f":"0.7734-0.5987","v":0.17469999999999997},74.900000000000006,77.7,2,9.5238095238095233E-2,3,"B","B  : 74.9 <= note < 77.7",null,"B:[74.9;77.7)"]
["A02",null,72,"C+",2.2999999999999998,72,74,1,74,5476,1,true,"A",1,"B+",0.375,0.625,3,{"$f":"0.8944-0.7734","v":0.121},77.7,80.5,1,4.7619047619047616E-2,3.3,"B+","B+ : 77.7 <= note < 80.5",null,"B+:[77.7;80.5)"]
["A02",null,69,"C",2,69,76,1,76,5776,1,true,"A+",2,"A-",0.625,0.875,1,{"$f":"0.9599-0.8944","v":6.5500000000000003E-2},80.5,83.3,4,0.19047619047619047,3.7,"A-","A- : 80.5 <= note < 83.3",null,"A-:[80.5;83.3)"]
["A02",null,32,"E",0,32,79,1,79,6241,1,true,null,null,"A",0.875,1.125,1,{"$f":"0.9878-0.9599","v":2.7900000000000036E-2},83.3,86.1,1,4.7619047619047616E-2,4,"A","A  : 83.3 <= note < 86.1",null,"A:[83.3;86.1)"]
["A02",null,76,"B",3,76,80,3,240,19200,3,true,null,null,"A+",1.125,10,0,{"$f":"0.997-0.9878","v":9.199999999999986E-3},86.1,185.5,{"$f":"IF(ISERROR(VLOOKUP(O14,$M$3:$N$14,2,FALSE)),0,VLOOKUP(O14,$M$3:$N$14,2,FALSE))","v":2},{"$f":"V14/$N$15","v":9.5238095238095233E-2},4.3,"A+",{"$f":"CONCATENATE(Y14,IF(LEN(Y14)=1,\"  : \",\" : \"),IF(Y14<>\"E\",CONCATENATE(FIXED(T14,1),\" <= \"),\"          \"),\"note\",IF(Y14<>\"A+\",CONCATENATE(\" < \",FIXED(U14,1)),\"\"))","v":"A+ : 86.1 <= note"},null,"A+:[86.1;100]"]
["A02",null,64,"C-",1.7,64,82,1,82,6724,1,true,"NB total",{"$f":"SUM(N3:N14)","v":21}]
["A02",null,80,"B+",3.3,80,83,1,83,6889,1,true,"Choisir",null,null,"A- Clipping",null,null,null,"E",0.04,{"$f":"W3","v":9.5238095238095233E-2},0.08]
["A02",null,67,"C",2,67,87,2,174,15138,2,true,"Méthode",{"$f":"LOOKUP(P35,P32:P34,Q32:Q34)","v":"C"},null,"Moy. est.",{"$f":"I2/K2","v":71.428571428571431},null,null,"D,D+,C-",0.04,{"$f":"SUM(W4:W6)","v":4.7619047619047616E-2},0.1]
["A03",null,70,"C+",2.2999999999999998,70,null,null,{"$f":"IF(L18,H18*G18,\"\")","v":0},{"$f":"IF(L18,G18*G18*H18,\"\")","v":0},{"$f":"IF(L18,H18,\"\")","v":0},true,"A - B - C",null,null,"Écart est.",{"$f":"SQRT((J2-I2*I2/K2)/(K2-1))","v":14.417251570848819},null,null,"C,C+",0.2,{"$f":"SUM(W7:W8)","v":0.23809523809523808},0.3]
["A03",null,79,"B+",3.3,79,null,null,0,0,0,true,null,null,null,null,null,null,null,"B-,B,B+",0.3,{"$f":"SUM(W9:W11)","v":0.2857142857142857},0.4]
["A03",null,87,"A+",4.3,87,null,null,0,0,0,true,null,null,null,"B- Seuils",null,null,null,"A-,A",0.2,{"$f":"SUM(W12:W13)","v":0.23809523809523808},0.3]
["A03",null,87,"A+",4.3,87,null,null,0,0,0,true,"Statistiques",null,null,"Min. A+",{"$f":"T32/10","v":84.8},null,null,"A+",0.04,{"$f":"W14","v":9.5238095238095233E-2},7.0000000000000007E-2]
["A03",null,74,"B-",2.7,74,null,null,0,0,0,true,"Moyenne",{"$f":"AVERAGE(C:C)","v":71.428571428571431},null,"Max. E",{"$f":"T33/10","v":61.4}]
["A03",null,73,"B-",2.7,73,null,null,0,0,0,true,"Écart",{"$f":"STDEV(C:C)","v":14.417251570848819}]
[null,null,null,null,null,null,null,null,0,0,0,true,"Médiane",{"$f":"MEDIAN(C:C)","v":73},null,"C-Max et Largeur"]
[null,null,null,null,null,null,null,null,0,0,0,true,"Moyenne",{"$f":"AVERAGE(E:E)","v":2.6095238095238096},null,"Min. A+",{"$f":"T36/10","v":86.1}]
[null,null,null,null,null,null,null,null,0,0,0,true,"Écart",{"$f":"STDEVP(E:E)","v":1.1092274282257648},null,"Largeur",{"$f":"T37/10","v":2.8}]
[null,null,null,null,null,null,null,null,0,0,0,true]
[null,null,null,null,null,null,null,null,0,0,0,true]
[null,null,null,null,null,null,null,null,0,0,0,true]
[null,null,null,null,null,null,null,null,0,0,0,true]
[null,null,null,null,null,null,null,null,0,0,0,true,null,null,null,"Sélection","Lettre",null,"Méthode B",null,"Moy.",{"$f":"Q22-Q3*V32","v":74.27}]
[null,null,null,null,null,null,null,null,0,0,0,true,null,null,null,1,"A",null,"Min A+ * 10",848,"Écart",{"$f":"(Q21-Q22)/(P14-Q3)","v":9.36}]
[null,null,null,null,null,null,null,null,0,0,0,true,null,null,null,2,"B",null,"Max E * 10",614]
[null,null,null,null,null,null,null,null,0,0,0,true,null,null,null,3,"C"]
[null,null,null,null,null,null,null,null,0,0,0,true,null,null,1,3,null,null,"Méthode C"]
[null,null,null,null,null,null,null,null,0,0,0,true,null,null,null,"Moy.",{"$f":"IF(N17=\"A\",Q17,IF(N17=\"B\",V31,V36))","v":73.5},null,"Min A+ * 10",861,"Moy.",{"$f":"Q25-Q13*V37","v":73.5}]
[null,null,null,null,null,null,null,null,0,0,0,true,null,null,null,"Écart",{"$f":"IF(N17=\"A\",Q18,IF(N17=\"B\",V32,V37))","v":11.2},null,"Écart * 10",28,"Écart",{"$f":"Q26/(Q13-P13)","v":11.2}]
[null,null,null,null,null,null,null,null,0,0,0,true,null,null,null,null,null,null,null,30]
[null,null,null,null,null,null,null,null,0,0,0,true]
[null,null,null,null,null,null,null,null,0,0,0,true]
[null,null,null,null,null,null,null,null,{"$f":"IF(L41,H41*G41,\"\")","v":0},{"$f":"IF(L41,G41*G41*H41,\"\")","v":0},{"$f":"IF(L41,H41,\"\")","v":0},true]
[null,null,null,null,null,null,null,null,0,0,0,true]
[null,null,null,null,null,null,null,null,0,0,0,true]
[null,null,null,null,null,null,null,null,0,0,0,true]
[null,null,null,null,null,null,null,null,"","","",false]
[null,null,null,null,null,null,null,null,"","","",false]
[null,null,null,null,null,null,null,null,"","","",false]
[null,null,null,null,null,null,null,null,"","","",false]
[null,null,null,null,null,null,null,null,"","","",false]
[null,null,null,null,null,null,null,null,"","","",false]
[null,null,null,null,null,null,null,null,"","","",false]
[null,null,null,null,null,null,null,null,"","","",false]
[null,null,null,null,null,null,null,null,"","","",false]
[null,null,null,null,null,null,null,null,"","","",false]
[null,null,null,null,null,null,null,null,"","","",false]
[null,null,null,null,null,null,null,null,"","","",false]
[null,null,null,null,null,null,null,null,"","","",false]
[null,null,null,null,null,null,null,null,"","","",false]
[null,null,null,null,null,null,null,null,"","","",false]
[null,null,null,null,null,null,null,null,"","","",false]
[null,null,null,null,null,null,null,null,"","","",false]
[null,null,null,null,null,null,null,null,"","","",false]
[null,null,null,null,null,null,null,null,"","","",false]
[null,null,null,null,null,null,null,null,"","","",false]
[null,null,null,null,null,null,null,null,"","","",false]
[null,null,null,null,null,null,null,null,"","","",false]
[null,null,null,null,null,null,null,null,"","","",false]
[null,null,null,null,null,null,null,null,"","","",false]
[null,null,null,null,null,null,null,null,"","","",false]
[null,null,null,null,null,null,null,null,"","","",false]
[null,null,null,null,null,null,null,null,"","","",false]
[null,null,null,null,null,null,null,null,"","","",false]
[null,null,null,null,null,null,null,null,"","","",false]
[null,null,null,null,null,null,null,null,"","","",false]
[null,null,null,null,null,null,null,null,"","","",false]
[null,null,null,null,null,null,null,null,"","","",false]
[null,null,null,null,null,null,null,null,"","","",false]
[null,null,null,null,null,null,null,null,"","","",false]
[null,null,null,null,null,null,null,null,"","","",false]
[null,null,null,null,null,null,null,null,"","","",false]
[null,null,null,null,null,null,null,null,"","","",false]
[null,null,null,null,null,null,null,null,"","","",false]
[null,null,null,null,null,null,null,null,"","","",false]
[null,null,null,null,null,null,null,null,"","","",false]
[null,null,null,null,null,null,null,null,"","","",false]
[null,null,null,null,null,null,null,null,"","","",false]
[null,null,null,null,null,null,null,null,"","","",false]
[null,null,null,null,null,null,null,null,"","","",false]
[null,null,null,null,null,null,null,null,"","","",false]
[null,null,null,null,null,null,null,null,"","","",false]
[null,null,null,null,null,null,null,null,"","","",false]
[null,null,null,null,null,null,null,null,"","","",false]
[null,null,null,null,null,null,null,null,"","","",false]
[null,null,null,null,null,null,null,null,"","","",false]
[null,null,null,null,null,null,null,null,"","","",false]
[null,null,null,null,null,null,null,null,"","","",false]
[null,null,null,null,null,null,null,null,"","","",false]
[null,null,null,null,null,null,null,null,"","","",false]
[null,null,null,null,null,null,null,null,"","","",false]
[null,null,null,null,null,null,null,null,"","","",false]
[null,null,null,null,null,null,null,null,{"$f":"IF(L101,H101*G101,\"\")","v":""},{"$f":"IF(L101,G101*G101*H101,\"\")","v":""},{"$f":"IF(L101,H101,\"\")","v":""},false]

{"sheet":"Analyse par groupe","index":2,"n_rows":32,"n_cols":13,"rows_kept":32,"cut":false,"hidden":false,"merged":[]}
[]
[]
["NB Lettre","Groupe"]
["Lettre","A01","A02","A03","Total"]
["E",1,1,null,2]
["C",null,1,null,1]
["C+",null,2,null,2]
["B-",3,1,2,6]
["B",null,1,1,2]
["B+",2,1,1,4]
["A-",2,null,null,2]
["A",null,null,2,2]
[null,null,1,null,1]
["Total",8,8,6,22]
[]
[]
[]
[]
[]
[null,{"$f":"B4","v":"A01"},{"$f":"C4","v":"A02"},"A03","Total",0,0,0,0,0,0,0,0]
["E",{"$f":"IF(ISERROR(VLOOKUP($A21,$A$5:$M$16,COLUMN(B21),FALSE)),0,VLOOKUP($A21,$A$5:$M$16,COLUMN(B21),FALSE))","v":1},{"$f":"IF(ISERROR(VLOOKUP($A21,$A$5:$M$16,COLUMN(C21),FALSE)),0,VLOOKUP($A21,$A$5:$M$16,COLUMN(C21),FALSE))","v":1},0,2,0,0,0,0,0,0,0,0]
["D",{"$f":"IF(ISERROR(VLOOKUP($A22,$A$5:$M$16,COLUMN(B22),FALSE)),0,VLOOKUP($A22,$A$5:$M$16,COLUMN(B22),FALSE))","v":0},0,0,0,0,0,0,0,0,0,0,0]
["D+",0,0,0,0,0,0,0,0,0,0,0,0]
["C-",0,0,0,0,0,0,0,0,0,0,0,0]
["C",0,1,0,1,0,0,0,0,0,0,0,0]
["C+",0,2,0,2,0,0,0,0,0,0,0,0]
["B-",3,1,2,6,0,0,0,0,0,0,0,0]
["B",0,1,1,2,0,0,0,0,0,0,0,0]
["B+",2,1,1,4,0,0,0,0,0,0,0,0]
["A-",2,0,0,2,0,0,0,0,0,0,0,0]
["A",0,0,2,2,0,0,0,0,0,0,0,0]
["A+",0,0,0,0,0,0,0,0,0,0,0,0]