{"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":[[20,15,20,16],[17,12,17,13],[0,15,0,18],[0,19,0,22],[12,15,12,16],[16,15,16,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$11,6,TRUE))","v":"B-"},{"$f":"IF(D2=\"\",\"\",VLOOKUP(D2,$O$3:$X$11,10,FALSE))","v":2.7},{"$f":"IF(C2=\"\",\"\",TRUNC(C2,0))","v":70},"Arondies","Total",{"$f":"SUM(I3:I101)","v":1501},{"$f":"SUM(J3:J101)","v":111441},{"$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,"A-",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,{"$f":"ROUND($N$12*S3,0)","v":1},{"$f":"S9","v":6.5500000000000003E-2},0,{"$f":"ROUNDDOWN($Q$34+Q3*$Q$35,1)","v":61.1},{"$f":"IF(ISERROR(VLOOKUP(O3,$M$3:$N$11,2,FALSE)),0,VLOOKUP(O3,$M$3:$N$11,2,FALSE))","v":2},{"$f":"V3/$N$12","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 < 61.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;61.1)"}]
["A01",null,80,"A-",3.7,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,"C",{"$f":"Q3","v":-1},-0.75,{"$f":"ROUND($N$12*S4,0)","v":3},{"$f":"S8","v":0.121},{"$f":"U3","v":61.1},{"$f":"ROUNDDOWN($Q$34+Q4*$Q$35,1)","v":64.8},1,4.7619047619047616E-2,2,"C","C  : 61.1 <= note < 64.8",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":"C:[61.1;64.8)"}]
["A01",null,72,"B-",2.7,72,64,1,64,4096,1,true,"C+",1,"C+",{"$f":"Q4","v":-0.75},-0.5,4,{"$f":"S7","v":0.17469999999999997},{"$f":"U4","v":64.8},68.5,1,4.7619047619047616E-2,2.2999999999999998,"C+","C+ : 64.8 <= note < 68.5",null,"C+:[64.8;68.5)"]
["A01",null,80,"A-",3.7,80,67,1,67,4489,1,true,"B-",6,"B-",-0.5,-0.25,4,{"$f":"(0.5987-0.5)*2","v":0.19740000000000002},68.5,72.2,6,0.2857142857142857,2.7,"B-","B- : 68.5 <= note < 72.2",null,"B-:[68.5;72.2)"]
["A01",null,33,"E",0,33,69,1,69,4761,1,true,"B",2,"B",-0.25,0,4,{"$f":"0.7734-0.5987","v":0.17469999999999997},72.2,75.900000000000006,2,9.5238095238095233E-2,3,"B","B  : 72.2 <= note < 75.9",null,"B:[72.2;75.9)"]
["A01",null,83,"A-",3.7,83,70,2,140,9800,2,true,"B+",2,"B+",0,0.25,3,{"$f":"0.8944-0.7734","v":0.121},75.900000000000006,79.599999999999994,2,9.5238095238095233E-2,3.3,"B+","B+ : 75.9 <= note < 79.6",null,"B+:[75.9;79.6)"]
["A02",null,70,"B-",2.7,70,71,1,71,5041,1,true,"A-",5,"A-",0.25,0.5,1,{"$f":"0.9599-0.8944","v":6.5500000000000003E-2},79.599999999999994,83.3,5,0.23809523809523808,3.7,"A-","A- : 79.6 <= note < 83.3",null,"A-:[79.6;83.3)"]
["A02",null,71,"B-",2.7,71,72,2,144,10368,2,true,"A+",2,"A",0.5,0.75,1,{"$f":"0.9878-0.9599","v":2.7900000000000036E-2},83.3,87,0,0,4,"A","A  : 83.3 <= note < 87.0",null,"A:[83.3;87.0)"]
["A02",null,72,"B-",2.7,72,73,1,73,5329,1,true,null,null,"A+",0.75,10,0,{"$f":"0.997-0.9878","v":9.199999999999986E-3},87,223.9,2,{"$f":"V11/$N$12","v":9.5238095238095233E-2},4.3,"A+",{"$f":"CONCATENATE(Y11,IF(LEN(Y11)=1,\"  : \",\" : \"),IF(Y11<>\"E\",CONCATENATE(FIXED(T11,1),\" <= \"),\"          \"),\"note\",IF(Y11<>\"A+\",CONCATENATE(\" < \",FIXED(U11,1)),\"\"))","v":"A+ : 87.0 <= note"},null,"A+:[87.0;100]"]
["A02",null,69,"B-",2.7,69,74,1,74,5476,1,true,"NB total",{"$f":"SUM(N3:N11)","v":21}]
["A02",null,32,"E",0,32,76,1,76,5776,1,true,"Choisir",null,null,"A- Clipping",null,null,null,"E",0,{"$f":"W3","v":9.5238095238095233E-2},0.05]
["A02",null,76,"B+",3.3,76,79,1,79,6241,1,true,"Méthode",{"$f":"LOOKUP(P33,P29:P31,Q29:Q31)","v":"C"},null,"Moy. est.",{"$f":"I2/K2","v":71.476190476190482},null,null,"C,C+",0.1,{"$f":"SUM(W4:W5)","v":9.5238095238095233E-2},0.2]
["A02",null,64,"C",2,64,80,3,240,19200,3,true,"A - B - C",null,null,"Écart est.",{"$f":"SQRT((J2-I2*I2/K2)/(K2-1))","v":14.41394827109854},null,null,"B-,B,B+",0.4,{"$f":"SUM(W6:W8)","v":0.47619047619047616},0.5]
["A02",null,80,"A-",3.7,80,82,1,82,6724,1,true,null,null,null,null,null,null,null,"A-",0.15,{"$f":"W9","v":0.23809523809523808},0.3]
["A02",null,67,"C+",2.2999999999999998,67,83,1,83,6889,1,true,null,null,null,"B- Seuils",null,null,null,"A",0.1,{"$f":"W10","v":0},0.2]
["A03",null,null,"","","",87,2,174,15138,2,true,"Statistiques",null,null,"Min. A+",{"$f":"T29/10","v":87},null,null,"A+",0.04,{"$f":"W11","v":9.5238095238095233E-2},7.0000000000000007E-2]
["A03",null,79,"B+",3.3,79,null,null,0,0,0,true,"Moyenne",{"$f":"AVERAGE(C:C)","v":71.476190476190482},null,"Max. E",{"$f":"T30/10","v":64}]
["A03",null,87,"A+",4.3,87,null,null,0,0,0,true,"Écart",{"$f":"STDEV(C:C)","v":14.41394827109854}]
["A03",null,87,"A+",4.3,87,null,null,0,0,0,true,"Médiane",{"$f":"MEDIAN(C:C)","v":73},null,"C-Max et Largeur"]
["A03",null,74,"B",3,74,null,null,0,0,0,true,"Moyenne",{"$f":"AVERAGE(E:E)","v":2.8666666666666663},null,"Min. A+",{"$f":"T33/10","v":87}]
["A03",null,73,{"$f":"IF(C23=\"\",\"\",VLOOKUP(C23,$T$3:$Y$14,6,TRUE))","v":"B"},{"$f":"IF(D23=\"\",\"\",VLOOKUP(D23,$O$3:$X$14,10,FALSE))","v":3},73,null,null,0,0,0,true,"Écart",{"$f":"STDEVP(E:E)","v":1.1055415967851356},null,"Largeur",{"$f":"T34/10","v":3.7}]
[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,null,null,null,null,null,null,null,0,0,0,true,null,null,null,1,"A",null,"Min A+ * 10",870,"Moy.",{"$f":"Q19-Q3*V30","v":77.142857142857139}]
[null,null,null,null,null,null,null,null,0,0,0,true,null,null,null,2,"B",null,"Max E * 10",640,"Écart",{"$f":"(Q18-Q19)/(P11-Q3)","v":13.142857142857142}]
[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,null,null,null,null,"Méthode C"]
[null,null,null,null,null,null,null,null,0,0,0,true,null,null,2,3,null,null,"Min A+ * 10",870,"Moy.",{"$f":"Q22-Q10*V34","v":75.900000000000006}]
[null,null,null,null,null,null,null,null,0,0,0,true,null,null,null,"Moy.",{"$f":"IF(N14=\"A\",Q14,IF(N14=\"B\",V29,V33))","v":75.900000000000006},null,"Écart * 10",37,"Écart",{"$f":"Q23/(Q10-P10)","v":14.8}]
[null,null,null,null,null,null,null,null,0,0,0,true,null,null,null,"Écart",{"$f":"IF(N14=\"A\",Q15,IF(N14=\"B\",V30,V34))","v":14.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,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,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,{"$f":"IF(L68,H68*G68,\"\")","v":""},{"$f":"IF(L68,G68*G68*H68,\"\")","v":""},{"$f":"IF(L68,H68,\"\")","v":""},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]

{"sheet":"Analyse par groupe","index":2,"n_rows":29,"n_cols":13,"rows_kept":29,"cut":false,"hidden":false,"merged":[]}
[]
[]
["NB Lettre","Groupe"]
["Lettre","A01","A02","A03","Total"]
["E",1,1,null,2]
["C+",null,1,null,1]
["B-",null,1,null,1]
["B",1,3,null,4]
["B+",1,1,2,4]
["A-",null,1,null,1]
["A",3,1,1,5]
["A+",1,null,2,3]
[null,null,null,1,1]
["Total",7,9,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$13,COLUMN(B21),FALSE)),0,VLOOKUP($A21,$A$5:$M$13,COLUMN(B21),FALSE))","v":1},{"$f":"IF(ISERROR(VLOOKUP($A21,$A$5:$M$13,COLUMN(C21),FALSE)),0,VLOOKUP($A21,$A$5:$M$13,COLUMN(C21),FALSE))","v":1},0,2,0,0,0,0,0,0,0,0]
["C",{"$f":"IF(ISERROR(VLOOKUP($A22,$A$5:$M$13,COLUMN(B22),FALSE)),0,VLOOKUP($A22,$A$5:$M$13,COLUMN(B22),FALSE))","v":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]
["B-",0,1,0,1,0,0,0,0,0,0,0,0]
["B",1,3,0,4,0,0,0,0,0,0,0,0]
["B+",1,1,2,4,0,0,0,0,0,0,0,0]
["A-",0,1,0,1,0,0,0,0,0,0,0,0]
["A",3,1,1,5,0,0,0,0,0,0,0,0]
["A+",1,0,2,3,0,0,0,0,0,0,0,0]