Introducció

En aquesta pràctica treballareu:

• Càlcul de paràmetres estadístics (mitjana, mediana i desviació estàndard).
• Aprofundiment en l'ús de la consulta de taules.
• Escriptura de fórmules per direccionament de cursor.
• Tractament de variacions del format condicional.
• Ús de la funció COMPTASI.
  

L'objectiu és construir dos fulls de càlcul que permetin enregistrar les notes obtingudes per un grup d'alumnes. L'exemple plantejat és, per raons de brevetat, molt simplificat. Es disposa de les notes obtingudes per 10 alumnes en un examen i un treball. A partir d'aquestes dades s'obtindrà la nota global tenint en compte els percentatges següents: 60 % l'examen i 40 % el treball. Un dels fulls recollirà la mitjana, la mediana, la moda i la desviació estàndard de cada una de les qualificacions; també s'hi farà constar la qualificació acadèmica literal, a més d'escriure en vermell les notes insuficients. La qualificació literal s'exemplifica en dos models: un per a les qualificacions insuficient, suficient, bé, notable i excel·lent, i un altre per a I, S, B, N, E.

L'altre full contindrà els valors de conversió numèrica en expressió literal. Els resultats seran similars als següents: 

• Seleccioneu un nou full de càlcul i anomeneu-lo Notes. 
• Mitjançant el cursor, disminuïu l'amplada de la columna A i l'altura de la fila 1 per deixar-la com es veu a la figura.
• Amplieu l'amplada de la columna B perquè hi càpiguen els noms de l'alumnat. Els podeu copiar d'aquí:

Afonso, Elena Boumanne, S. Carmona, Manuel Conreras, L. David Florejachs, Marina Flores, José Gil, Rosa Morales, Jordi Moreno, Vicenç Soufianne, M. Zambrano, Pedro

• Entreu els rètols de la fila 2 i els de la columna B.
• Entreu les notes corresponents al rang C4:D14. 
• Inseriu un full de càlcul al final del llibre i anomeneu-lo Notes conversió. 
• Desactiveu la visió de la quadrícula dels fulls. 
• Ajusteu les columnes i les files del full Notes conversió tal com es veu a la figura. 
• Entreu el contingut del rang B2:D7 del full Notes conversió. És la taula que servirà per traduir les notes.
• Entreu en la cel·la E4 del full Notes la fórmula adequada: =C4*0,6+D4*0,4. 

Amb aquesta fórmula, calculeu la nota global donant el 60 % d'importància a l'examen i el 40 % al treball.


• Copieu-la sobre el rang E5:E14. 
• Entreu, des de la cel·la C16 fins a la C19, les fórmules respectives: 

=MITJANA(C4:C14), =MEDIANA(C4:C14), =MODA(C4:C14) i =DESVESTP(C4:C14) que calculen la mitjana aritmètica, la mediana, la moda i la desviació estàndard de les notes de l'examen. Val a dir que la funció MODA pot donar error si totes les dades són diferents. 
• Copieu-les sobre el rang D16:E19. 
• Fixeu el nombre de decimals a 2 en el rang C16:E19. 
• Entreu en la cel·la F4 la fórmula =CONSULTAV(E4;'Notes conversió'.B$3:D$7;2). Aquesta fórmula la podeu escriure tal com se us diu textualment, o per direccionament del cursor, com ja heu comprovat a la pràctica 2 del mòdul 3. 

És a dir: 

• Escriviu textualment en l'àrea de fórmules: =CONSULTAV( 
• A continuació, poseu el cursor sobre la cel·la E4. Observeu que en l'àrea de fórmules s'escriu automàticament E4 i que el contorn de la cel·la E4 és de color vermell. És a dir, que el que ara hi ha és: =CONSULTAV(E4. 
• A continuació escriviu ; 
• Seguidament, amb el cursor, activeu el full Notes conversió. Observeu que s'obre aquest full.  
• Seleccioneu el rang B3:D7. Observeu que en l'àrea de fórmules s'escriu automàticament l'expressió del rang indicat, amb el nom del full inclòs. Fixeu les files d'aquest rang, escrivint $ davant del 3 i del 7 fins arribar a: 

=CONSULTAV(E4;'Notes conversió'.B$3:D$7. (Fixeu-vos al punt del final.)
• Finalitzeu l'expressió de la fórmula escrivint directament la resta que falta, és a dir: ;2) i tornareu al full Notes quan feu Retorn en acabar d'escriure la fórmula.

• Copieu la fórmula anterior sobre el rang F5:F14 i sobre la cel·la F16. 
• Entreu en la cel·la G4 la fórmula =CONSULTAV(E4;'Notes conversió'.B$3:D$7;3). Aquesta fórmula, igual que abans, la podeu escriure tal com se us ha dit textualment, o per direccionament del cursor. 
• Copieu la fórmula anterior sobre el rang G5:G14 i sobre la cel·la G16. 
• Els avantatges d'escriure les fórmules per direccionament del cursor són dues:
1. Evita les errades mecanogràfiques. 
2. És molt més còmode i ràpid d'escriure.
• Comproveu el bon funcionament del full de càlcul Notes sobretot en els casos de notes globals no enteres. Varieu algunes notes tant del treball com de l'examen i observeu les modificacions que s'hi generen.

A continuació, introduïu el format condicional perquè surtin en vermell les notes insuficients. En primer lloc, heu de crear un estil de cel·la amb aquest color:

• Obriu Format | Estils i formatació.
• Situeu el cursor sobre l'estil Per defecte, que estarà seleccionat, i, amb el botó dret del ratolí, escolliu l'opció Nou.
• El nom de l'estil pot ser Vermell; amb les pestanyes Efectes de lletra i Tipus de lletra, seleccioneu les opcions de color (vermell o vermell clar) amb negreta.
• Després de clicar a D'acord, veureu el nou estil Vermell a la finestra d'estils.

• Seleccioneu el rang C4:E16 i activeu Format | Formatació condicional...
• En la casella de l'esquerra hi ha d'haver actiu El valor de la celda és. En la del centre s'ha de triar menor que. En la de la dreta s'ha d'entrar un 5. D'aquesta manera, totes les notes que siguin inferiors a 5 es veuran amb el format que tot seguit triareu.
• Trieu l'estil Vermell i premeu D'acord.

• Seleccioneu ara la cel·la F4 i activeu Format | Formatació condicional...
• Trieu, de la casella de l'esquerra, La fórmula és. En la de la dreta, entreu  =E4<5. D'aquesta manera, si la nota global és inferior a 5, escriurà la part literal amb el format que ara triareu.
• Trieu l'estil Vermell i premeu D'acord.
• Accediu a Edita | Copia.
• Seleccioneu el rang F5:F16.
• Activeu Edita | Enganxament especial i deixeu només activat Formats.

• Seleccioneu ara la cel·la G4 i activeu Format | Formatació condicional...
• Trieu, de la casella de l'esquerra, La fórmula és. En la de la dreta entreu  =E4<5. D'aquesta manera, si la nota global és inferior a 5, escriurà la part literal amb el format que ara triareu.
• Trieu l'estil Vermell i premeu D'acord.
• Accediu a Edita | Copia.
• Seleccioneu el rang G5:G16.
• Activeu Edita | Enganxament especial i deixeu només activat Formats..

Proveu de canviar notes i veureu com canvien els colors.

• Seleccioneu el rang B2:G20 i activeu Format | Cel·les | Vores amb Color: blau, Estil: línia gruixuda de 2,5 punts, Arranjament de línies: Vora exterior. 
• Activeu Format | Cel·les | Fons i seleccioneu el color groc clar. 
• Modifiqueu el color del rang C2:E2.
• Modifiqueu les vores del rang C3:F3.
• Modifiqueu les vores del rang C3:C19.
• Modifiqueu les vores del rang C19:F19. 
• Modifiqueu les vores del rang F3:F19.
• Modifiqueu les vores de la la cel·la F3.

Per tal de fer totes les modificacions de les vores, fareu servir el quadre de disseny de vores, marcant les línies i els seus atributs:

• Seleccioneu el rang C16:E19 i activeu la lletra cursiva.
• Poseu en negreta el contingut del rang B2:B19. 
• Modifiqueu les vores del rang B2:D7 del full de càlcul Notes conversió.
• Amb el mateix rang anterior seleccionat, modifiqueu el seu color.
• Poseu en negreta el contingut de la cel·la B2 del full de càlcul Notes conversió.

Ara interessa complementar el full de càlcul Notes amb la comptabilitat d'alumnes que han tret una determinada qualificació. 

1-Creació de lloc:

• Intercaleu a la fila 20, 7 files més. Per fer-ho, activeu Insereix | Files tantes vegades com convingui. 
• Escriviu a la cel·la C21 el text: "RESUM DE LES QUALIFICACIONS", en negreta i color blau. 
• Seleccioneu el rang C21:F21 i feu que el text introduït en la cel·la C21 sigui centrat en la selecció, activant prèviament Format | Fusiona les cel·les. 

  2-Fase primera

• Introduïu en la cel·la C22 la fórmula: 

=COMPTASI(F$4:F$14;'Notes conversió'.C3). No oblideu de fer-ho per direccionament del cursor. 
• Introduïu en la cel·la D22 la fórmula: 

='Notes conversió'.C3. També aquí ho podeu fer per direccionament del cursor. 
• Seleccioneu el rang C22:D22 i arrossegueu la selecció, per copiar el seu contingut, fins al D26. 
• Verifiqueu que aquesta part funciona correctament en fer variar el nombre de les diferentes qualificacions. 

Busqueu informació sobre la funció COMPTASI

3-Fase segona

• Introduïu en la cel·la C22 la fórmula: 

=COMPTASI(F$4:F$14;'Notes conversió'.C3)&" "&'Notes conversió'.C3. 
• Esborreu el contingut del rang D22:D26. 
• Seleccioneu la cel·la C22 i arrossegueu la selecció, per copiar el seu contingut, fins al C26. 
• Verifiqueu que aquesta part funciona correctament en fer variar el nombre de les diferents qualificacions.

  4-Fase tercera

• Introduïu en la cel·la C22 la fórmula: 

=COMPTASI(F$4:F$14;'Notes conversió'.C3)&" alumnes han obtingut "&'Notes conversió'.C3. 
• Seleccioneu la cel·la C22 i arrossegueu la selecció, per copiar el seu contingut, fins a la C26. 
• Verifiqueu que aquesta part funciona correctament en fer variar el nombre de les diferents qualificacions.

  5-Fase quarta

• Introduïu en la cel·la C22 la fórmula: 

=SI(COMPTASI(F$4:F$14;'Notes conversió'.C3)=0; "Cap alumne ha obtingut "&'Notes conversió'.C3; SI(COMPTASI(F$4:F$14;'Notes conversió'.C3)<2;"1 alumne ha obtingut "&'Notes conversió'.C3;COMPTASI(F$4:F$14;'Notes conversió'.C3)&" alumnes han obtingut "&'Notes conversió'.C3)).  
• Seleccioneu la cel·la C22 i arrossegueu la selecció fins a la C26. 
• Introduïu en la cel·la F22 la fórmula: 
  =SI(COMPTASI(G$4:G$14;'Notes conversió'.D3)=0;"Cap amb "&'Notes conversió'.D3;COMPTASI(G$4:G$14;'Notes conversió'.D3)&" amb "&'Notes conversió'.D3).
• Seleccioneu la cel·la F22 i arrossegueu la selecció fins a la F26. 
• Verifiqueu que aquesta part funciona correctament en fer variar el nombre de les diferents qualificacions. 
• Desactiveu la visió de la quadrícula si no s'havia fet.
• Seleccioneu el rang C22:F26 i feu que tingui el contorn de color blau. 
• Seleccioneu el rang C4:D14 i, a partir de Data | Validesa, feu que només puguin introduir-se els números (decimals) entre el 0 i el 10.
• Protegiu les cel·les que creieu convenients dels dos fulls (Notes i Notes conversió). 
• Deseu el llibre.