D51. Mòdul 4. Pràctica 2. Curs de full de càlcul avançat

Mòdul 4

Curs de full de càlcul avançat

Pràctica

Solucions

Exercicis

Introducció

L'objectiu d'aquesta pràctica és poder conèixer, per una data determinada del calendari, quants dies de l'any han transcorregut. Per fer-ho, haureu de treballar amb:

Ús de la funció CONSULTAV per a la consulta de taules.
Efectes en l'ús del paràmetre FALS() en la funció CONSULTAV.
Alineació d'un rètol en una selecció de cel·les.

El full de càlcul utilitzat serà:

Consideracions prèvies

La principal novetat d'aquesta pràctica és la utilització de la funció CONSULTAV. Aquesta funció, juntament amb CONSULTAH i CONSULTA, serveixen per consultar taules.

Suposeu que teniu un petit full de càlcul, anomenat Taules, com el següent:

En la cel·la A9 heu introduït la fórmula =CONSULTAV(7;A3:D7;2) i el resultat serà 28. Vegeu per què és així. En executar-se la fórmula, va a buscar el primer element del parèntesi (7) en la primera columna (A) del rang especificat (A3:D7), i, quan el troba, es desplaça fins a la columna indicada pel tercer argument del parèntesi (2). En desplaçar-se a la segona columna cap a la dreta, es troba amb el 28, i aquest és el resultat. Si en comptes de ser un 2, el tercer argument hagués estat un 3, el resultat, aleshores, hagués estat 13. A continuació, teniu més casos en el mateix full:

Fórmula	Resultat
CONSULTAV(6;A3:D7;2)	17
CONSULTAV(6;A3:D7;3)	32
CONSULTAV(15;A3:D7;2)	1
CONSULTAV(8;A3:D7;4)	15
CONSULTAV(13;A3:D7;3)	17

Com podeu veure, en els dos darrers casos, si no troba el valor demanat (8 i 13) considera el valor immediatament inferior trobat en la primera columna del rang especificat (7 i 9).

Es pot dir que CONSULTAV busca en la primera columna d'una matriu i es desplaça a través de la fila per retornar el valor d'una cel·la. S'ha de tenir en compte que els valors de la primera columna han d'estar ordenats.

La funció CONSULTAH tindrà una aplicació similar, però fent la primera recerca en horitzontal. A continuació teniu, a tall d'exemple, alguns casos en què s'ha utilitzat aquesta darrera funció amb el mateix full:

Fórmula	Resultat
CONSULTAH(5;A3:D7;2)	6
CONSULTAH(15;A3:D7;2)	17
CONSULTAH(15;A3:D7;4)	12
CONSULTAH(25;A3:D7;5)	32
CONSULTAH(30;A3:D7;4)	26

Així doncs, es pot dir que CONSULTAH busca en la primera fila d'una matriu i es desplaça cap avall, en la columna, per retornar el valor d'una cel·la. S'ha de tenir en compte que els valors de la primera fila han d'estar ordenats.

Observeu ara el que passa quan la primera fila o la primera columna no estan ordenades:

Considereu, en el mateix full de Taules, la taula següent:

Amb els casos següents i els seus resultats:

Fórmula	Resultat
=CONSULTAV(5;F3:I7;2)	#N/A
=CONSULTAV(5;F3:I7;2;FALS())	15
=CONSULTAV(12;F3:I7;2)	17
=CONSULTAV(12;F3:I7;2;FALS())	#N/A

Observeu que els resultats en color vermell són clarament falsos: si la primera columna (o fila) està desordenada, és imprescindible fer servir el paràmetre FALS(), d'altra manera es pot obtenir un resultat incorrecte.

Si, en canvi, la primera columna (o fila) sí que està ordenada, tenim dues possibilitats: si el valor no existeix, en el cas de no utilitzar el paràmetre FALS(), donarà el resultat corresponent al major valor més petit que el buscat; però, si s'utilitza el paràmetre FALS(), donarà error avisant que no hi és.

Resumint:

	Columna ordenada		Columna desordenada
	Sense FALS()	Amb FALS()	Amb FALS()
Troba el valor	Retorna valor correcte	Retorna valor correcte	Retorna valor correcte
No troba el valor	Retorna valor inferior	Retorna error	Retorna error

Busqueu informació sobre les funcions CONSULTAV, CONSULTAH, CONSULTA.

En general, és preferible utilitzar la funció CONSULTAH o CONSULTAV en comptes de CONSULTA. El sistema inclou aquesta forma CONSULTA per compatibilitat amb altres programes per a fulls de càlcul.

Desenvolupament de la pràctica

Seleccioneu el full següent i anomeneu-lo Dies de l_any.
Amplieu l'altura de la fila 2 a 0,7 cm.
Entreu en la cel·la B2 la paraula CONSULTA en negreta.
Seleccioneu el rang B2:C2 i activeu Format | Fusiona les Cel·les. Alineeu el text centrat.
Entreu els rètols de les cel·les B3, B4 i B5.
Modifiqueu l'amplada de les columnes A, B, E, F i G, respectivament, a 1, 2, 1,5, 1,5 i 1,5, aproximadament.
Entreu en la cel·la E2 la paraula "TAULA" en negreta. Seleccioneu el rang E2:G2 i activeu Format | Fusiona les Cel·les. Alineeu el text centrat.
Genereu la successió de valors E3:E14.
Entreu el nombre de dies de cada mes sobre el rang F3:F14 (suposeu anys que no siguin de traspàs).
Entreu al rang G3:G14 les fórmules precises per obtenir els dies acumulats dels mesos anteriors en un de determinat.
Entreu les dades numèriques en el rang C3:C4.
Introduïu en la cel·la C5 la fórmula

=C3 + CONSULTAV(C4; E3:G14;3).

La funció CONSULTAV consultarà el rang E3:G14. Buscarà el valor que trobi en la cel·la C4 de la primera columna (E) i tornarà la dada situada a la tercera (G). Finalment, sumarà el valor de C3. En l'exemple, el 2 de febrer és el dia número 33 de l'any.
Feu servir la validació de dades del menú Data | Validesa per evitar que es puguin introduir en les cel·les C3 i C4 nombres que no siguin naturals.
Desactiveu la visió de la quadrícula.
Seleccioneu el rang B2:C5 i afegiu-hi una vora blava.
Amb el mateix rang seleccionat, pinteu-lo de color groc clar.
Seleccioneu el rang E3:G14 i afegiu-hi una quadrícula blava.
Seleccioneu el rang E2:G14 i afegiu-hi una vora blava.
Amb el mateix rang seleccionat, pinteu-lo de color groc clar.
Protegiu les cel·les que creieu convenients com vau fer en la pràctica 7 del mòdul 2.
Canvieu les dades de les cel·les C3 i C4 i comproveu el funcionament del full de càlcul.
Deseu el llibre.

Ampliacions, aclariments i comentaris

Genereu la successió de valors E3:E14

Entreu els valors 1 i 2 en les cel·les E3 i E4, respectivament.
Seleccioneu les cel·les E3 i E4.
Estireu la selecció fins a la cel·la E14.
Una alternativa seria:
Entreu l'1 en la cel·la E3.
Seleccioneu el rang E3:E14.
Activeu Edita | Emplena | Sèries, amb Tipus: lineal, i Incremento: 1

Entreu en el rang G3:G14 les fórmules precises per obtenir els dies acumulats dels mesos anteriors a un de determinat

Entreu 0 en el rang G3.
Entreu la fórmula =G3+F3 en la cel·la G4.
Copieu aquesta fórmula sobre el rang G5:G14.

Seleccioneu el rang B2:C5 i afegiu una vora blava

Activeu Format | Cel·les | Vores i poseu-hi un contorn de color blau.

Amb el mateix rang seleccionat, pinteu-lo de color groc clar

Activeu Format | Cel·les | Fons, i escolliu el groc clar.

Seleccioneu el rang E3:G14 i afegiu-hi una quadrícula blava

Activeu Format | Cel·les | Vores i poseu-hi una quadrícula de color blau.