Calc 03

Objectius

Saber què és, què significa i, si és el cas, saber practicar, adquirir seguretat i destresa en els aspectes següents:

§ Ordenació i filtrat de llistes.

§ Immobilització de parts d'una llista.

§ Consulta de taules.

§ Ús de les funcions de text.

§ Impressió de fulls de càlcul.

Aclariment

La majoria de dades amb les quals treballareu en aquest mòdul s'han obtingut d'un centre d'ensenyament imaginari anomenat La Rosa dels Vents i, posteriorment, han estat modificades.. Totes les dades dels alumnes que hi apareixen són totalment inventades, i qualsevol semblança amb la realitat és una simple coincidència.

Desenvolupament de la pràctica

Còpia de les dades necessàries al llibre de treball

§ Creeu un nou llibre i deseu-lo amb el nom de

En aquesta primera pràctica d'aquest mòdul treballareu per primer cop amb uns fulls de càlcul que estan estructurats en forma de llistes, de manera que cada columna té un nom i en cada fila hi ha les dades referents a un mateix individu. LibreofficeOffice Calc permet incorporar dades dins d'un full de càlcul amb l'estructura de taula de bases de dades organitzada en camps (cada columna) i registres (cada fila). El programa reconeix el caràcter singular de la primera fila que conté els noms dels camps. Disposa, també, d'eines específiques per a la realització de les accions més bàsiques sobre aquestes taules (ordenació, filtrat...) agrupades al menú Dades. Aquestes dades estructurades en registres i camps les anomenarem Llistes. Aquesta feina es concreta en:

§ Immobilització de files i columnes d'un full estructurat en forma de llista.

§ Filtrats d'una llista.

§ Ordenació d'una llista.

§ Creació de subllistes a partir de determinats criteris.

Les dades necessàries per poder treballar aquests objectius les trobareu en el llibre Dades modul3.ods.

l podeu recuperar i desar al vostre ordinador.

Concretament, a partir d'aquest llibre, haureu d'elaborar una llista de l'alumnat de 1 ESO A i una altra dels nois nascuts abans de l'1 de gener de 1985.

MODUL3NomiCognoms.ods.

§ Recupereu el llibre Dades modul3.ods.

§ Fixeu-vos que conté cinc fulls: alumnes1, alumcad, alumnes2, alumcurs i centres. Cadascun d'aquests fulls està estructurat en forma de llista (base de dades), de manera que cada columna o camp té un títol. Els quatre primers fulls contenen diverses dades d'alumnes d'un centre imaginari i el cinquè conté dades de centres d'ensenyament de tot Catalunya. A continuació copiareu aquests fulls sobre el llibre MODUL3NomiCognoms.ods.

§ Accediu al llibre Dades i seleccioneu l'etiqueta alumnes1, premeu la tecla Control i, sense deixar-la de prémer, aneu seleccionant les altres etiquetes. D'aquesta manera queden els cinc fulls seleccionats. Deixeu anar la tecla Control.

§ Amb la fletxa del cursor a sobre de les etiquetes seleccionades, premeu el botó dret del ratolí i accediu a l'opció Mou/copia Full del menú que ha sortit.

§ Del desplegable Al document seleccioneu MODUL3NomiCognoms.ods.

Del desplegable Insereix davant de trieu l'opció desplaçar a la última Posició. Premeu D’acord. D'aquesta manera els cinc fulls del llibre dades s'hauran copiat al final del llibre MODUL1NomiCognoms.ods. Recordeu que per accedir als diferents llibres actius ho podeu fer a través del menú Finestra.

§ Podeu tancar el llibre Dades.ods, perquè ara treballareu amb la còpia incorporada al llibre MODUL3NomiCognoms.ods.

Immobilització de files i columnes

§ Seleccioneu el full alumnes1 del llibre MODUL3NomiCognoms.ods.

Si moveu la barra de la part inferior dreta de la pantalla podeu veure la totalitat de les columnes, però quan veieu la columna GRUP perdeu de vista la primera columna NOM. Això és degut a què la llista té massa columnes per veure-les simultàniament per pantalla. A més, si moveu la barra de la dreta de la pantalla per accedir a les últimes files de la llista, perdeu de vista els títols de les columnes. Una manera d'evitar aquest problema consisteix en immobilitzar les primeres columnes i la primera fila.

§ Seleccioneu la cel·la A1. A continuació seleccioneu la D2. Assegureu-vos que la cel·la A1 és visible.

§ Activeu l'opció Finestra | Congela. Us apareixeran en pantalla una línia Vertical i una línia horitzontal que es creuen en el vèrtex superior esquerre de la cel·la seleccionada (la D2). D'aquesta manera, si ara accediu a les últimes columnes o files, sempre quedaran visibles les tres primeres columnes (Nom i cognoms de l'alumnat) i la primera fila amb els títols. Comproveu-ho. La cel·la D2 ha marcat el límit entre les parts mòbils i les parts fixes de la llista. Si volguéssiu tornar a la situació inicial hauríeu de desactivar l'opció activada anteriorment.

Filtrat de dades de 1 ESO A

De la llista alumnes1 heu de seleccionar l'alumnat de 1 ESO A:

§ Seleccioneu una cel·la qualsevol de la llista.

§ Accediu a Dades | Filtre | Filtre automàtic. Fixeu-vos com al costat dels títols de cada columna ha aparegut un botó que ens permetrà filtrar les dades que ens interessen.

§ Premeu el botó de la columna ETAPA. Del menú que apareix, podeu triar, entre d'altres, les opcions BATX o ESO. Trieu l'opció ESO.

§ Fixeu-vos com la llista ha canviat d'aspecte: la fletxa del botó que heu premut abans és de color blau, així com els números de les files. Ara només són visibles els registres (files) d'alumnes d'ESO. Els altres no s'han perdut i quan sigui oportú els podreu tornar a fer visibles.

§ Seleccioneu tota una columna, sense incloure la cel·la de la capçalera on hi ha el nom, i cliqueu amb el botó dret del ratolí en el requadre inferior dret de la pantalla. Trieu l'opció ComptaA i veureu com indica el nombre de files visibles. En aquest cas són 269.

§ Ara volem reduir més la llista i filtrar només els alumnes de 1r. Premeu el botó de la columna NIVELL i trieu l'opció 1. Procediu com en el paràgraf anterior i per comptar les files filtrades i veureu que ara ja només són 52.

§ Premeu el botó de la columna GRUP. Seleccioneu l'opció A. La llista ha deixat visible només l'alumnat que compleix els tres requisits que hem imposat: alumnat de 1 d'ESO i del grup A. El nombre de files que han superat el filtre és ara de 26.

Ordenació de dades

Les dades d'una llista es poden ordenar per diversos criteris.

§ Seleccioneu una cel·la qualsevol de la llista i accediu a Dades | Ordena. Fixeu-vos com ha quedat seleccionada tota la llista. El menú que ha sortit us permet triar els criteris per ordenar la llista. No feu servir per ordenar les dades els botons que apareixen a la barra d'eines, perquè els efectes no sempre són els mateixos.

§ Si premeu el botó del desplegable Ordenar per trobareu els títols de totes les columnes de la llista. Trieu COGNOM1 i l'opció Ascendent. D'aquesta manera s'ordenarà la llista pel primer cognom de l'A a la Z. No premeu encara D’acord.

§ Premeu a continuació el botó del desplegable central, tornen a sortir tots els títols de les columnes. Trieu COGNOM2 i Ascendent. Així, si dos alumnes tenen el primer cognom igual, els ordenarà a partir del segon cognom.

§ Trieu del tercer desplegable NOM i Ascendent. D'aquesta manera, les persones amb els dos cognoms iguals quedaran ordenats pel nom. Premeu D’acord. Fixeu-vos com ha quedat ordenada la llista.

§ Proveu de fer altres ordenacions i comproveu els resultats.

§ Deixeu la llista ordenada alfabèticament seguint els criteris indicats anteriorment.

Còpia de la llista filtrada i ordenada a un full nou

§ Anomeneu el primer full del llibre MODUL3NomiCognoms.ods amb el nom de Llistes.

§ Torneu al full alumnes1 on hi ha la llista filtrada i ordenada i seleccioneu les dades visibles de les tres primeres columnes (A, B i C).

§ Inicieu el procés de copiar-les amb Ctrl+C o accedint a Edita | Copia.

§ Seleccioneu la cel·la B3 del full Llistes.

§ Enganxeu-les amb Ctrl+V o Edita | Enganxa.

§ Modifiqueu la mida de les columnes al vostre criteri.

§ Entreu el rètol a la fila 1 com es veu a la figura.

§ Deseu el llibre.

Repetició del procés per a la segona llista

Ara cal fer un nou filtrat de la llista inicial de manera que quedin seleccionats tots els nois nascuts abans de l'1 de gener de 1985.

§ Torneu al full alumnes1.

§ Desactiveu Dades | Filtre | Filtre automàtic. D'aquesta manera tornen a ser visibles totes les files de la llista.

§ Torneu a activar ara Dades | Filtre | Filtre automàtic.

§ Premeu el botó de filtrat de la columna DATANAIXEMENT i trieu l'opció Predeterminat. Aquesta opció ens permet fer un filtrat més complet. Apareix un menú amb diversos desplegables. El que queda per sota de Nom del camp deixa visible el títol del camp que heu seleccionat.

§ Del primer desplegable que està per sota de Condició trieu l'opció <. Entreu directament en el desplegable de la seva dreta (sense prémer el seu botó) la data 1/01/85. Premeu D’acord. Fixeu-vos com han quedat filtrades les files que corresponen a nois i noies que han nascuts abans de la data entrada.

§ Premeu el botó de filtrat de la columna SEXE i trieu l'1. D'aquesta manera queden visibles nomes les files dels nois, que compleixen la condició anterior.

§ Ordeneu la llista com heu fet abans.

§ Copieu del full alumnes1 al full Llistes (a partir de la cel·la G3, com es veu a la figura), primer les tres primeres columnes i després les quatre últimes, com heu fet abans.

§ Entreu el rètol de la fila 1 que es veu a la figura.

§ Deseu el llibre.

Últims detalls de presentació

§ Entreu a la cel·la A3 del full Llistes NUM.

§ Entreu a les cel·les A4 i A5 els nombres 1 i 2, respectivament.

§ Seleccioneu el rang A4:A5. Deixeu anar el botó del ratolí.

§ Col·loqueu el cursor sobre el quadret negre del vèrtex inferior dret de la selecció feta. Fixeu-vos com el cursor ha canviat de la forma de creu amb braços amples a la d'una creu de braços prims i negres. Premeu el botó esquerre del ratolí i estireu el requadre seleccionat, com si anéssiu a fer una còpia, a la resta de la columna (fins a A29). Fixeu-vos com s'han escrit tots els nombres, de l'1 fins al 26.

§ Repetiu el procés per a l'altra llista.

§ Acabeu els detalls estètics que surten a la figura.

Els resultats seran semblants a:

§ Deseu el llibre.

En aquesta pràctica veureu com es calcula la lletra del NIF. Per fer-ho necessitareu fer servir:

§ La funció CONSULTAV.

§ La funció MOD.

§ El símbol & per unir caràcters.

§ Ocultació de columnes

Heu de construir un full de càlcul tal que si hi entrem un DNI qualsevol, escrigui el NIF corresponent. Per això haurà de calcular la lletra i enganxar-la al final del DNI.

Consideracions prèvies

La principal novetat d'aquesta pràctica és la utilització de la funció CONSULTAV. Aquesta funció, juntament amb CONSULTAH, serveixen per consultar taules. A continuació veureu com funciona.

Considereu un nou full de càlcul del llibre de treball i introduïu els nombres següents en el rang A3:D7:

Entreu a la cel·la A9 la fórmula =CONSULTAV(7;A3:D7;2) . El resultat serà 28. Vegem per què és així. En executar-se la fórmula va a Cerca el primer element del parèntesi (7) a la primera columna (A) del rang especificat (A3:D7) i quan el troba va a Cerca el contingut de la cel·la que ocupa la mateixa fila i la columna indicada (en aquest cas, 2). El contingut que troba és 28, i aquest és el resultat. Si en comptes de ser un 2 el tercer argument hagués estat un 3, aleshores el resultat hagués estat 13. A continuació proveu més casos en el mateix full. Modifiqueu la fórmula de A9 i comproveu els resultats. Per fer aquestes modificacions, n'hi prou en seleccionar la cel·la A9 i col·locar el cursor en la barra de fórmules (part superior dreta de la pantalla , on apareix la fórmula de la cel·la seleccionada) i prémer el botó esquerre del ratolí. A partir d'aquest moment podeu modificar la fórmula en la barra de fórmules sense haver de repetir-la tota. En acabar heu de prémer la tecla Intro o Retorn:

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

Com a resum podríem 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.

Els elements de la primera columna (A en l'exemple) poden ser numèrics o no, i cal, en general, que estiguin ordenats. En el cas que no hi estiguin, cal fer servir una petita variació de la funció CONSULTAV que ja trobareu en pràctiques posteriors.

La funció CONSULTAH és similar per Cerca elements a la primera fila en comptes de a la primera columna.

Desenvolupament de la pràctica

Seleccioneu un nou full del llibre MODUL3NomiCognoms.ods i anomeneu-lo NIF.

§ Fixeu-vos com a la figura no es veuen les columnes D i E. Estan amagades. Aviat sabreu com amagar columnes, però fins aleshores les farem servir estant visibles.

§ Entreu els rètols del rang B2:B3 i la taula G2:H25.

§ Entreu un nombre de DNI com el de la figura en la cel·la C2.

Per calcular quina lletra completa el NIF s'ha de calcular el residu de la divisió del DNI entre 23. Aquest residu serà un nombre comprès entre el 0 i el 22. A continuació cal Cerca a la taula de la figura el nombre que ha sortit. La lletra que queda a la seva dreta és la lletra que buscàvem. Per exemple, si dividim el nombre 12345678 (un possible DNI) entre 23, surt 14 de residu. Busquem el 14 a la taula i veiem que a la dreta té la lletra Z. Doncs, aquesta és la lletra del NIF.

Afortunadament, l'Excel té una funció que calcula directament residus de divisions sense haver de fer-les. Aquesta funció és =MOD( ; ) i ara la fareu servir.

§ Entreu a la cel·la D2 la fórmula =MOD(C2;23). El que fa és calcular el residu que resulta de dividir el contingut de la cel·la C2 (el DNI) entre 23.

§ Entreu a la cel·la E2 la fórmula =CONSULTAV(D2;G3:H25;2). El que fa aquesta fórmula és Cerca el valor que ha sortit en la cel·la D2 en la primera columna de G3:H25. Quan el troba retorna la lletra que està en la mateixa fila i en la segona columna de la taula. Fixeu-vos com amb el DNI de la figura ha de sortir la lletra Z.

§ Proveu d'entrar d'altres números de DNI per comprovar si funciona correctament.

A continuació enganxareu la lletra al DNI.

§ Entreu a la cel·la C3 la fórmula =C2&E2. D'aquesta manera el contingut de la cel·la C2 quedarà unit al de la E2. Aquesta és la funció del símbol &.

Les columnes D i E no cal que quedin visibles. Per amagar-les:

§ Seleccioneu les columnes D i E. Recordeu que per seleccionar tota una columna cal col·locar el cursor a sobre de la lletra de la columna corresponent i prémer el botó esquerre del ratolí.

§ Premeu el botó dret del ratolí i trieu l'opció Ocultar. Ja no són visibles, però no han quedat esborrades. Podeu comprovar que les fórmules que contenen segueixen funcionant perfectament.

§ Si voleu que tornin a ser visibles cal que seleccioneu les columnes anterior i posterior, simultàniament, i prement el botó dret del ratolí accediu a l'opció Mostrar.

§ Modifiqueu les mides de les columnes i els detalls estètics com heu fet en mòduls anteriors.

En aquest cas és convenient protegir el full de càlcul per evitar possibles modificacions accidentals. Tot i així cal deixar la cel·la C2 desbloquejada per poder anar variant el DNI:

§ Seleccioneu la cel·la C2, accediu a Format | Cel·les | Protecció de Cel·les i desactiveu l'opció Protegit.

§ Protegiu ara el full com heu fet en la pràctica 2 del mòdul 2.

El resultat hauria de ser semblant a:

§ Seleccioneu el full Taula de notes i entreu tota la taula de la figura en el rang B2:D7.

§ Torneu al full Notes. Cal entrar en el rang D3:D13 les fórmules corresponents que permetin traduir cadascuna de les notes de la columna C en qualificacions literals. A la cel·la D3 hi ha d'anar la fórmula (no l'entreu encara) =CONSULTAV(C3;'Taula de notes'.$B$3:$D$7;2). Analitzem el seu significat:

§ Recull la nota que hi ha a la cel·la C3.

§ Busca aquesta nota a la primera columna de la taula de conversió, que està indicada com 'Taula de notes'.$B$3:$D$7 (és a dir, el nom del full i el rang on està la taula). Els símbols de $ (referències absolutes) són necessaris per tal de poder copiar la fórmula a la resta de la columna i d'aquesta manera no canviar el rang de la taula.

§ Torna el que ha trobat a la segona columna de la fila corresponent i ho escriu a la cel·la on està la fórmula (D3).

Més endavant entendreu millor el seu significat, quan aneu provant diferents valors. Ara entrareu la fórmula a D3.

· Entreu a la cel·la D3 la fórmula =CONSULTAV(C3;'Taula de notes'.$B$3:$D$7;2). Aquesta fórmula la podeu escriure tal com us diem textualment o per direccionament del cursor. Aquest mètode és molt més còmode. Per fer-ho, seguiu les instruccions següents:

§ Seleccioneu la cel·la D3 i escriviu textualment (fixeu-vos com queda escrit a la barra de fórmules, a la part superior de la pantalla)=CONSULTAV(

§ A continuació, sense prémer cap altra tecla, moveu el ratolí fins a posar el cursor sobre de la cel·la C3. Observeu que a la barra de fórmules s'escriu automàticament C3 i que el contorn de la cel·la C3 ha canviat de color. És a dir, que el que ara hi ha escrit és: =CONSULTAV(C3

§ A continuació escriviu ;

§ Seguidament, amb el cursor, activeu el full Taula de notes.

§ Seleccioneu el rang B3:D7. Observeu que a l'àrea de fórmules s'escriu automàticament l'expressió del rang indicat.

§ Premeu la combinació de tecles Majúscula+F4. Aquestes tecles col·loca els $ a l'última referència entrada. Per ara tenim escrit =CONSULTAV(C3;$'Taula de notes'!$B$3:$D$7. El $ que apareix davant de 'Taula de notes', en aquest cas, no té cap funció específica. Si no hi fos no passaria res.

§ Finalitzeu l'expressió de la fórmula escrivint directament la resta que falta, és a dir: ;2). Premeu Intro.

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

a. Evita les errades mecanogràfiques.

b. És molt més còmode i ràpid d'escriure.

§ Copieu la fórmula entrada a C3, a la resta de la columna (és a dir a C4:C13).

§ Observeu com han quedat escrites aquestes fórmules. S'ha mantingut el rang de la taula gràcies als $ i ha anat canviant la referència de la columna C.

A continuació, per a una millor comprensió de les fórmules introduïdes, entrareu diferents notes en la columna C i observareu els resultats:

§ Entreu un 5 a C3. La fórmula entrada a D3 consulta la taula del full Taula de notes i troba que la segona fila primera columna hi ha un 5. A la seva dreta té la lletra S. Aquesta és la lletra que situa en la cel·la D3.

§ Entreu un 7 a C4. La fórmula que hi ha a D4, consulta la taula de l'altre full i troba el 7. Com que a la seva dreta hi ha la lletra N, l'escriu a la cel·la D4.

§ Entreu un 3,5 a C5. La fórmula que hi ha a la D5 busca la nota 3,5 a la primera columna (C) de la taula del full Taula de notes. Com que no la troba es queda en la fila on apareix el nombre immediatament inferior. En aquest cas és el 0. Com que a la dreta del 0 hi ha la lletra I, aquesta és la que escriu.

§ Entreu un 8 a C6. Com que no troba aquest nombre a la taula, es fixa amb la immediatament inferior (el 7) i retorna la lletra que troba a la seva dreta (N).

En definitiva, busca a la taula el nombre exacte introduït. Si no el troba es queda en la fila corresponent a l'immediatament inferior i retorna la lletra de la segona columna. Per aquesta raó és imprescindible que la taula de consulta tingui els valors de la primera columna ordenats de més petit a més gran.

La taula que s'haurà de consultar serà aquesta:

Taula de notes

A continuació podeu acabar la pràctica:

§ Entreu a la cel·la C15 la fórmula MITJANA(C3:C13) per calcular la mitjana de les notes introduïdes.

§ En la cel·la D15 volem que surti la qualificació literal corresponent a aquesta mitjana, però sense abreviatures. Per això, seguint les instruccions anteriors, entreu-hi la fórmula CONSULTAV(C15;'Taula de notes'!$B$3:$D$7;3). Fixeu-vos com heu escrit un 3 en comptes d'un 2. D'aquesta manera, en comptes d'escriure els valors amb les inicials (columna 2) retornarà els que apareixen a la tercera columna de la taula.

§ Entreu a la cel·la G3 la fórmula =COMPTASI($D$3:$D$13;"I"). D'aquesta manera comptarà quantes vegades la lletra I apareix en el rang D3:D13. Podeu introduir-la per direccionament de cursor, com heu fet abans. Cal entrar la mateixa fórmula a la resta de cel·les de la columna, però variant la lletra I per les lletres S, B, N i E respectivament.

§ Modifiqueu les mides de les columnes per tal que quedin com a la figura.

§ Feu servir el format condicional, com vàreu fer en la pràctica 7 del mòdul 1, de manera que per les cel·la del rang C3:C13 i per la cel·la C15 si surt un nombre inferior a 5 quedi escrit en color Vermell. El mateix podeu fer per les cel·les corresponents de la columna D, de manera que si surt la lletra I, quedi escrita en Vermell.

§ Per evitar entrades errònies podeu fer servir la validació de dades, com a la pràctica 7 del mòdul 1, de manera que només es puguin entrar valors decimals compresos entre el 0 i el 10.

§ Acabeu els detalls estètics com heu fet en mòduls anteriors.

§ Deseu el llibre.

Millora optativa

El full, així com el teniu, ja ha de funcionar. Tot i així, el podeu millorar, si voleu, per evitar que si s'esborra alguna nota de C3:C13 no surti algun símbol d'error a la columna D.

§ Modifiqueu la fórmula de la cel·la D3 de manera que quedi així =SI(C3="";"";CONSULTAV(C3;'Taula de notes'!$B$3:$D$7;2)) . D'aquesta manera, si a la cel·la C3 no hi ha res, no escriurà res i en cas contrari farà el procés abans comentat.

§ Copieu aquesta fórmula a D4:D13.

També podeu protegir el full:

§ Protegiu el full de modificacions accidentals, com a la pràctica 2, deixant desbloquejades les cel·les del rang C3:C13.

En aquesta pràctica veureu la possibilitat de separar rètols situats a les cel·les d'una columna en dues parts, de manera que quedin en dues columnes diferents. Aquesta situació es planteja quan en una cel·la s'ha introduït, per exemple, CEIP Can Vidalet i es vol que, sense haver de tornar escriure el nom, quedi separat en una cel·la CEIP i en una altra Can Vidalet. Evidentment, aquesta situació serà important de resoldre quan es plantegi en una llista de molts centres amb la mateixa situació. Per això heu de conèixer:

§ Funcions de text per modificar llistes

§ Més possibilitats dels filtratges de llistes

§ Inserció de columnes en llistes

Aquesta pràctica consisteix a elaborar una llista dels centres de la població d'Esplugues de Llobregat a partir de la llista de centres que heu incorporat al llibre de treball, de manera que el tipus de centre i el nom del centres quedin separats, i no com estan en l'original. El resultat ha de ser:

Qüestions prèvies

Les funcions de text que fareu servir en aquesta pràctica són LONG( ), ESQUERRA( ) i DRETA( ). Vegeu com funciona:

§ Entreu a la cel·la A3 d'un full nou la paraula Informàtica.

§ Entreu a B3 la fórmula =LONG(A3). Fixeu-vos com apareix un 11. Aquesta funció compta el nombre de caràcters que té la paraula de la cel·la A3.

§ Entreu a C3 la fórmula =ESQUERRA(A3;5). Fixeu-vos com escriu els 5 primers caràcters de la paraula A3. Podeu provar què passa si en comptes del 5 poseu altres números. Ja sabeu que per fer petites modificacions de fórmules no cal tornar-les a escriure. N'hi ha prou amb seleccionar la cel·la on hi ha la fórmula, accedir a la barra de fórmules, on apareix la fórmula entrada, fer les modificacions pertinents i acabar amb un Intro.

§ Entreu a D3 la fórmula =DRETA(A3;4). En aquest cas, escriu els 4 últims caràcters. Feu més proves amb altres números.

§ Entreu ara a E3 la fórmula =DRETA(A3;LONG(A3)-5). Fixeu-vos que escriu els últims caràcters de la paraula. El nombre de caràcters que escriu ve donat per LONG(A3)-5, és a dir, tots els que té la paraula menys 5.

La varietat de funcions d'aquest tipus és prou gran. Si accediu a Insereix | Funció i escolliu Categoria | Text trobareu totes les funcions de text disponibles. Fins i tot, des d'aquesta opció de menú podeu entrar les diferents funcions. L'ús d'aquestes funcions us anirà molt bé per complir el principal objectiu d'aquesta pràctica.

Desenvolupament de la pràctica

§ Seleccioneu un nou full del llibre modul3NomiCognoms.ods. Anomeneu-lo Esplugues de Llobregat.

§ Entreu a la cel·la A3 el rètol Centre; a la B3, Adreça, i a la C3, Població. Fixeu-vos que us heu deixat el rètol Tipus, que apareixerà durant el procés de resolució de la pràctica. És a dir, que els rètols que heu entrat estan tots correguts una columna a l'esquerra respecte als de la figura, tret del primer. Poseu-los en negreta.

§ Modifiqueu l'amplada de les columnes de manera convenient.

§ Activeu el full Centres que teniu al mateix llibre Modul3NomiCognoms.ods (si heu seguit les indicacions de la pràctica 1).

§ Filtreu la llista del full centre de manera que només quedin visibles els centres de la població d'Esplugues de Llobregat (pràctica 1). Per fer-ho:

§ Seleccioneu una cel·la qualsevol de l'interior de llista.

§ Accediu a Dades | Filtre | Filtre automàtic .

§ Premeu el botó de filtratge de la columna Població i trieu el nom Esplugues de Llobregat. Per anar més de pressa, podeu entrar la lletra e de la inicial del nom de la població.

En aquest moment ja teniu filtrats els 16 centres d'Esplugues de Llobregat. Fixeu-vos que a la columna Nom podeu trobar els diferents noms dels centres. El tipus de centre (CEIP, IES o cap) està incorporat a la mateixa columna. El que aconseguireu a continuació és separar el tipus de centre del nom. Aquesta feina la fareu al full Esplugues de Llobregat, per tant, el primer pas consistirà a copiar les dades que us interessin del full Centres al full Esplugues de Llobregat.

§ Seleccioneu les tres primeres columnes del full Centres per copiar-les a Esplugues de Llobregat. Assegureu-vos que seleccioneu les files de tots els centres filtrats. La primera fila, la dels títols, no l'heu de seleccionar. Accediu a Edita | Copia (o premeu Ctrl + C) per iniciar la còpia.

§ Obriu el full Esplugues de Llobregat i seleccioneu la cel·la A4. Accediu a Edita | Enganxa(o premeu Ctrl + V).

§ Acabeu de donar l'amplada més convenient a les columnes.

§ Inseriu dues noves columnes entre la A i la B. Per fer-ho:

§ Seleccioneu les columnes B i C des de la barra grisa on hi ha les lletres.

§ Accediu al menú Insereix | Columnes.

§ Entreu els títols de Tipus a B3 i de Nom a C3.

§ Seleccioneu una cel·la qualsevol de la llista i accediu a Dades | Ordenar i trieu l'opció Centre al desplegable Ordenar per. Accepteu.

§ Fixeu-vos que han quedat els CEIP i els IES agrupats. Entreu la paraula CEIP a la primera cel·la que queda sota del títol Tipus i que correspon al primer CEIP ( en aquest cas és la cel·la B4) . Copieu-la a la resta de cel·les de la columna corresponents als CEIP.

§ Entreu la fórmula =DRETA(A4;LONG(A4)-5) a la cel·la buida que queda sota el títol Nom i que correspon al primer CEIP ( en aquest cas és la cel·la C4).

Tal com heu comprovat a les qüestions prèvies d'aquesta pràctica, aquesta fórmula (o la corresponent al vostre cas) escriu els últims caràcters del contingut d'A4. El nombre de caràcters que escriu ve donat per LONG(A4)-5, és a dir, el resultat de restar el total de caràcters menys els cinc primers (espai en blanc inclòs).

§ Copieu el contingut d'aquest cel·la a la resta de cel·les de la columna corresponents als CEIP. En el nostre cas, el destí de la còpia és el rang C5:C11.

§ Entreu la paraula IES a la primera cel·la de la columna B que correspon a un IES. Podeu comprovar que és la B13 . Copieu-la a la resta de cel·les de la columna corresponents als IES (rang B14:B16).

§ Entreu la fórmula =DRETA(A13;LONG(A13)-4) a la cel·la C13.

§ Copieu aquesta fórmula a la resta de cel·les la columna corresponents als IES (rang C14:C16). En aquest moment, també teniu separats els noms dels IES.

§ Seleccioneu una cel·la de la llista, accediu a Dades | Ordenar i trieu l'ordenació respecte al tipus de centre. D'aquesta manera us quedaran tots els centres CEIP i IES consecutius.

§ Seleccioneu el rang A16:A19 i copieu-lo sobre C16:C19. D'aquesta manera la columna C ja queda completa.

Ara sobra la columna A. Si l'eliminéssiu immediatament, es produiria un error, ja que les dades de la columna C depenen del la columna A.

§ Seleccioneu C4:C19 i accediu a Edita | Copiar.

§ Sense moure la selecció feta, accediu a Edita | Enganxament especial. En aquesta opció podeu triar allò que voleu que es copiï. Cal que desactiveu els apartats Enganxa tot i Fórmules. Accepteu i contesteu afirmativament si us surt una finestra de confirmació de l'acció.

Amb aquesta acció heu aconseguit que les cel·les de la columna C quedin lliures de les fórmules i, per tant, només continguin els noms. Ja podeu Suprimir la columna A que sobra.

§ Seleccioneu la columna A i accediu a Edita | Suprimir Cel·les.

§ Ordeneu la nova llista segons els criteris que creieu més oportuns.

§ Entreu a A1 el rètol Centres d'Esplugues de Llobregat. Centreu-lo al rang A1:D1 i doneu-li una mida de lletra més gran i negreta.

§ Acabeu els detalls estètics.

§ Deseu el llibre.

En aquesta pràctica ens plantegem una situació que consisteix en combinar diferents llistes que tenen una columna en comú per elaborar una nova llista amb una informació que estava repartida i separada entre aquestes llistes inicials. Per aconseguir-ho, a part de fer servir filtrats i ordenacions diverses, cal conèixer:

§ L'ús de la funció CONSULTAV per a dades no ordenades

L'exemple que ens servirà per practicar aquesta situació consisteix en fer una llista de l'alumnat d'un grup determinat, amb els seus telèfons. Aquesta informació l'hem de treure de diferents llistes, de tal manera que cap d'elles té totes les columnes necessàries. El resultat serà:

Desenvolupament de la pràctica

§ Seleccioneu un nou full del llibre MODUL3NomiCognoms.ods. Anomeneu-lo Telèfons.

§ Entreu els rètols de les files 2 i 4 . Modifiqueu-los per tal que tinguin l'aspecte que es veu a la figura.

§ Modifiqueu les mides de files i columnes de la manera habitual.

A part d'aquest full, fareu servir els fulls alumacad, alumnes2 i alumcurs, que ja teniu incorporats al llibre MODUL3NomiCognoms.ods des de la pràctica 1.

§ Seleccioneu el full alumacad. Fixeu-vos com la columna C conté exclusivament l'any 2004. Això significa que aquesta llista està formada per dades de l'alumnat matriculat per al curs 2004-05. Observeu també que no apareixen els noms ni els telèfons, però sí l'etapa, el nivell i el grup.

§ Volem que d'aquesta llista quedin filtrats els números de matrícula (primera columna) de l'alumnat de 3 d'ESO B. Per això seleccioneu una cel·la qualsevol de la llista i accediu a Dades | Filtre | Filtre automàtic.

§ Amb els botons de filtrat de les columnes etapa, nivell i grupclasse feu els filtrats corresponents escollint ESO, 3 i B, respectivament. Al final us han d’haver quedat 15 files.

§ Seleccioneu totes les cel·les filtrades de la primera columna (el títol no), que representen tots els números de matrícula de l'alumnat de 3 ESO B. Copieu-les al full Telèfons a partir de la cel·la A5.

§ Hem d'aconseguir que a la cel·la B5 surti el primer cognom de l'alumne amb el número de matrícula que apareix a la cel·la A5. Podeu comprovar que el full alumnes2 conté el número de matrícula de cada alumne i els corresponents nom i cognoms, per tant serà útil per al nostre objectiu.

§ Entreu a B5 del full Telèfons la fórmula =CONSULTAV($A5;Alumnes2.$A$2:$E$260;4;0). Per entrar-la podeu fer servir el direccionament del cursor que heu treballat en la pràctica 3. Fixeu-vos que anirà a Cerca el contingut de la cel·la A5 a la primera columna del rang A2:E260 del full alumnes2 i retornarà el contingut de la quarta columna d'aquest rang. El 0 posat al final de la fórmula ens indica que el rang de la taula que consultem (A2:E260) no té perquè estar ordenat, en contra del que exigíem en l'ús de la fórmula sense introduir aquest 0. Com sempre, els $ són necessaris per fer correctament les còpies posteriors a d'altres cel·les.

§ Copieu la fórmula anterior sobre les cel·les C5 i D5. Com podeu observar, surt el mateix cognom repetit. Això no ha sortit bé. El problema és que cal modificar lleugerament la fórmula que heu copiat.

§ Seleccioneu la cel·la C5. A la zona de fórmules (part superior de la pantalla) surt la fórmula que heu copiat abans =CONSULTAV($A5;Alumnes2.$A$2:$E$260;4;0). Situeu el cursor a davant del 4 d'aquesta fórmula i premeu el botó esquerre del ratolí. Ara ja podeu modificar el nombre 4 per un 5, que és la columna de la taula que consultem (alumnes2) que conté el segon cognom. Premeu Intro o Retorn i ja apareix el segon cognom correcte.

§ Feu el mateix amb la fórmula de la cel·la D5, canviant el 4 inicial per un 3, que és la columna de la taula que consultem que conté el nom.

§ Cal entrar ara una fórmula que ens mostri els telèfons. El full alumncurs conté els números de matrícula i els telèfons. Entreu, doncs, a E5 del full Telèfons la fórmula =CONSULTAV(A5;Alumcurs.$B$2:$K$260;10;0) de la manera habitual. Us apareixerà el número de telèfon corresponent a l'alumne que ocupa aquesta fila.

§ Seleccioneu el rang B5:E5 i copieu el contingut d'aquest rang sobre B6:E19. Ja teniu la llista completa.

§ Per ordenar alfabèticament aquesta llista, seleccioneu el rang A5:E19 i accediu a Dades | Ordenar . Trieu del desplegable Ordenar per la Columna B que és la del primer cognom. Accepteu.

§ Seleccioneu A5:A19 i premeu el desplegable del botó de la barra d'eines que té una A subratllada i trieu el color Vermell. Si trieu el color blanc, que és el color de fons actual, podeu fer que els números de la matrícula quedin invisibles (però no esborrats).

§ Deseu el llibre.

Després d’haver practicat amb diferents qüestions relacionades amb llistes i taules us proposem uns exercicis que, una vegada resolts, heu d'enviar al vostre tutor o tutora de la manera habitual.

Com que fareu servir els fulls alumnes1, alumacad, alumcurs i alumnes2 de DadesModul3.ods, heu de copiar-los prèviament al llibre M3ExercicisNomiCognoms.ods, com heu fet en la pràctica 1.

E31: Majors d'edat

En aquest primer exercici, similar a la pràctica 1, heu d'elaborar dues llistes dels majors d'edat a 1/01/2005 , una per als nois i una altra per a les noies, a partir del full alumnes1, que heu de tenir copiat al llibre. Per a cada llista hi ha d’haver els cognoms, el nom i la data de naixement, i han d'estar ordenades alfabèticament.

E32: Lletra del NIF

Us proposem fer un full de càlcul que esbrini si una lletra determinada és la corresponent al NIF, havent entrat el DNI prèviament. El resultat hauria de ser semblant a:

***

La taula és la mateixa que surt en la pràctica 2. Fixeu-vos que la columna D està amagada. Les cel·les d'aquesta columna us poden servir per fer els càlculs necessaris per esbrinar quina hauria de ser la lletra correcta. Una vegada fet el full, si introduïu un DNI i una lletra en les cel·les C2 i C3, respectivament, en la cel·la E3 haurà de sortir un rètol que digui si la lletra introduïda és correcta o no.

E33: Llistes amb NIF

En aquest exercici us plantegem la situació següent: en una llista apareixen uns DNI sense la lletra del NIF. Heu d'aconseguir que en una altra columna quedin escrits, automàticament, els NIF complerts. Els DNI originals els podeu copiar del full Alumnes1. Els resultat ha de ser semblant a:

****

Fixeu-vos com la taula de consulta de la lletra del NIF està amagada.

E34: Més notes

En aquest exercici us proposem que dissenyeu un full de càlcul que permeti entrar les qualificacions en forma literal, les tradueixi a valors numèrics seguint un determinat criteri, calculi la mitjana i la torni a traduir a forma literal. A més ha de comptar el nombre d'insuficients per a cada alumne. Està força relacionat amb la pràctica 3. El resultat ha de ser semblant a:

****

***

Aclariments

Una vegada dissenyat el full, si s'entren les qualificacions literals en el rang C3:M9, el full les ha de traduir (amb CONSULTAV) en el rang C11:M17 a partir de la Taula 1. A continuació es calcularan les mitjanes per a cada alumne en les cel·les de O11:O17. Aquestes mitjanes han de quedar traduïdes a forma literal en el rang O3:O9 a partir de la Taula 2. Aquestes dues taules han d'estar en un altre full del mateix llibre d'exercicis. Finalment, s'hauran de comptar, automàticament, el nombre d'insuficients que treu cada alumne (P3:P9).

E35: Llistes combinades

Seguint les indicacions de la pràctica 5, us proposem elaborar una llista, a partir dels fulls alumacad, alumcurs i alumnes2, on figuri l'alumnat de 1r de batxillerat A. En aquesta llista han de sortir els dos cognoms, el nom, el DNI, l'adreça i el telèfon. Han de quedar ordenats alfabèticament.