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ó CONTAR.SI
  

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 comptant un 60% l'examen i un 40% el treball. Un dels fulls recollirà la mitjana, la mediana, la moda i la desviació estàndard de cada una de les qualificacions, i també es farà constar la qualificació acadèmica literal a més de escriure en vermell les notes insuficients. 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 per deixar-la com es veu a  la figura. Deixeu l'alçada de la fila 1 a 5. 
• Amplieu l'amplada de la columna B, per tal que hi càpiguen els noms de l'alumnat. 
• Entreu els rètols de la fila 2 i els de la columna B. 
• Inserteu un full de càlcul al final del llibre i anomeneu-lo Notes conversió. 
• Desactiveu la visió de la quadrícula del full Notes conversió. 
• Disminuïu l'amplada de les columnes A i D i l'alçada de la fila 1 del full Notes conversió. 
• Entreu el contingut del rang B2:C7 del full Notes conversió. És la taula que servirà per traduir les notes.
• Entreu a la cel·la E4 del full Notes la fórmula adequada: =C4*0,6+D4*0,4 


Amb aquesta fórmula calculem 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 de la cel·la C16 fins a la C19 les fórmules respectives: 

=PROMEDIO(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 a la cel·la F4 la fórmula =BUSCARV(E4;'Notes conversió'!B$3:C$7;2). Aquesta fórmula la podeu escriure tal com us ho diem textualment o per direccionament del cursor. 

Això és, 
• Escriviu textualment en l'àrea de fórmules: =BUSCARV( 
• A continuació poseu el cursor sobre de la cel·la E4. Observeu que a l'àrea de fórmules s'escriu automàticament E4 i que el contorn de la cel·la E4 és de traç discontinu i movible. És a dir, que el que ara hi ha és: =BUSCARV(E4 
• A continuació escriviu ; 
• Seguidament, amb el cursor, activeu el full Notes conversió. Observeu que a l'àrea de fórmules s'escriu automàticament l'expressió: 'Notes conversió'! 
• Seleccioneu el rang B3:C7. Observeu que a l'àrea de fórmules s'escriu automàticament l'expressió del rang indicat. Fixeu les files d'aquest rang, és a dir, que l'expressió de la fórmula fins ara és: 

=BUSCARV(E4;'Notes conversió'!B$3:C$7 
• Finalitzeu l'expressió de la fórmula escrivint directament la resta que falta, és a dir: ;2)

 
• Els avantatges d'escriure les fórmules per direccionament del cursor són dos:

 
• Evita les errades mecanogràfiques. 
• És molt més còmode i ràpid d'escriure.

 
• Copieu la fórmula anterior sobre el rang F5:F14 i sobre la cel·la F16. 
• 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 es generen.

• Seleccioneu el rang C4:E16 i activeu Formato | Formato condicional...
• En la casella de l'esquerra hi ha d'haver actiu Valor de la celda. 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.
• Premeu el botó Formato... i accediu a Fuente.
• Trieu el color vermell i premeu Aceptar dos cops.
• Seleccioneu ara la cel·la F4 i activeu Formato | Formato condicional...
• Trieu de la casella de l'esquerra Fórmula. A 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.
• Premeu el botó Formato... i accediu a Fuente.
• Trieu el color vermell i premeu Aceptar dos cops.
• Accediu a Edición | copiar .
• Seleccioneu el rang F5:F16.
• Activeu Edición | Pegado especial | Formatos.
• Proveu de canviar notes i veureu com canvien els colors.

• Seleccioneu el rang B2:F20 i activeu Formato | Celdas... | Bordes | Color: blau | Estilo: línia gruixuda | Contorno. 
• Seleccioneu el mateix el rang anterior i activeu Formato | Celdas... | Tramas | Color: groc clar. 
• Seleccioneu el rang C2:E2 i activeu Formato | Celdas... | Fuentes | Color: Roig fosc. 
• Seleccioneu el rang C3:E3 i activeu Formato | Celdas... | Bordes | Color: blau | Estilo: línia gruixuda | Botó línia superior. 
• Seleccioneu el rang C3:C19 i activeu Formato | Celdas... | Bordes | Color: blau | Estilo: línia gruixuda | Botó línia esquerra. 
• Seleccioneu el rang C19:E19 i activeu Formato | Celdas... | Bordes | Color: blau | Estilo: línia prima | Botó línia inferior. 
• Seleccioneu el rang E3:E19 i activeu Formato | Celdas... | Bordes | Color: blau | Estilo: línia prima | Botó línia dreta. 
• Seleccioneu el rang C16:F19 i activeu la lletra cursiva. Poseu en negreta el contingut del rang B2:B19. 
• Seleccioneu el rang B2:C7 del full de càlcul Notes conversió i activeu Formato | Celdas... | Bordes | Color: blau | Contorno. 
• Amb el mateix rang anterior seleccionat activeu Formato | Celdas... | Tramas | Color: groc clar. 
• Poseu en negreta el contingut de la cel·la B2 del full de càlcul Notes conversió.

 

• Intercaleu a la fila 20,7 files. Per a fer-ho, procediu de la manera següent: intercaleu una fila i feu tantes vegades com convingui, en el nostre cas 6, Ctrl+Y. Aquesta combinació de tecles repeteix l'última acció realitzada. 
• Escriviu a la cel·la B21 el text: RESUM DE LES QUALIFICACIONS en negreta i amb 9 caràcters inicials en blanc. 
• Seleccioneu el rang B21:F21 i feu que el text introduït a la cel·la B21 estigui centrada en la selecció. 

  Fase primera

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

=CONTAR.SI(F$4:F$14;'Notes conversió'!C3). No oblideu de fer-ho per direccionament del cursor. 
• Introduïu a 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ó CONTAR.SI

Fase segona

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

=CONTAR.SI(F$4:F$14;'Notes conversió'!C3)&" "&'Notes conversió'!C3. 
• Esborreu el contingut del rang D22:D26. 
• Seleccioneu la cel·la 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 diferents qualificacions.

  Fase tercera

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

=CONTAR.SI(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.

  Fase quarta

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

=SI(CONTAR.SI(F$4:F$14;'Notes conversió'!C3)=0; "Cap alumne ha obtingut "&'Notes conversió'!C3; SI(CONTAR.SI(F$4:F$14;'Notes conversió'!C3)<2; CONTAR.SI(F$4:F$14;'Notes conversió'!C3)&" alumne ha obtingut "&'Notes conversió'!C3;CONTAR.SI(F$4:F$14;'Notes conversió'!C3)&" alumnes han obtingut "&'Notes conversió'!C3)) 

Observeu que aquí s'utilitza el condicional compost, la funció CONTAR.SI, el direccionament del cursor per escriure les diferents parts de la fórmula, i la selecció de cada una de les parts que forma el condicional, copiar-lo i enganxar-lo (com ara, CONTAR.SI(F$4:F$14;'Notes conversió'!C3)). La resta de la fórmula es reajusta manualment.
 

• Seleccioneu la cel·la C22 i arrossegueu la selecció fins a la C26. 
• Verifiqueu que aquesta part funciona correctament en fer variar el nombre de les diferents qualificacions. 
• Desactiveu la visió de la quadrícula.
• Seleccioneu el rang C22:E26 i feu que tingui el contorn de color blau. 
• Seleccioneu el rang C4:D14 i a partir de  Datos | Validación... , feu que només puguin introduir-se els nombres (decimals) entre el 0 i el 10.
• Protegiu les cel·les que creieu convenients dels dos fulls (Notes i Notes conversió). 
• Deseu els fulls.