Càlcul 03

Eines de text i dades

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.
AclarimentLa 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.

Pràctica 3.1

Desenvolupament de la pràctica

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. Es poden 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.

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.

Obre el fitxer del següent enllaç: Dades Mòdul 3.

Fes una còpia del fitxer. Fitxer - Fes una còpia.
Canvia el nom per FCM3 Nom i Cognoms.
Ara ja tens les dades necessàries al llibre de treball

Dades mòdul 3

Fes una còpia del fitxer. Fitxer - Fes una còpia.
Canvia el nom per FCM3 Nom i Cognoms.
Ara ja tens les dades necessàries al llibre de treball

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.
Podeu tancar el llibre Dades mòdul 3, perquè ara treballareu amb la còpia incorporada al llibre FCM3 Nom i Cognoms.

Immobilització de files i columnes

Seleccioneu el full Alumnes1 del llibre FCM3 Nom i Cognoms.

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 Mostra - Inmovilitza- 1 fila.
Seleccioneu la cel·la C1. A continuació seleccioneu Mostra - Inmovilitza- Fins la columna actual (C).
Us apareixeran en pantalla una línia Vertical i una línia horitzontal en gris 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 seleccionar Mostra - Inmovilitza - Cap fila i després Mostra - Inmovilitza- Cap columna.

Filtrat de dades de 1 ESO A

De la llista Alumnes1 heu de seleccionar l'alumnat de 1 ESO A, seguiu aquestes instruccions:

Seleccioneu una cel·la qualsevol de la llista.
Accediu a Dades - Crea un Filtre . 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, per fer-ho heu de desmarcar Batx.

Fixeu-vos com la llista ha canviat d'aspecte: la fletxa del botó que heu premut abans és un embut de color verd, 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, la que volgueu que tingui dades, sense incloure la cel·la de la capçalera on hi ha el nom, Al requadre inferior dret de la pantalla apareix Recompte i un número, veureu com indica el nombre de files visibles. En aquest cas són 269. Seleccioneu amb el ratolí Recompte 269, feu Ctrl + C i enganxeu-lo a sota de la columna Etapa, a la fila 460.
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, desmarcant les altres. Procediu com en el paràgraf anterior i per comptar les files filtrades i veureu que ara ja només són 52. És possible que no surti Recompte directament, llavors haurem de seleccionar amb la fletxa del requadre la operació que volem que ens mostri. Escriu a sota de la Columna Nivell, a la fila 460, el número d'alumnes d'ESO.
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 ?? Escriu a sota de la columna Grup, a la fila 460, el número d'alumnes del Grup A de primer d'ESO.

Ordenació de dades

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

Seleccioneu una cel·la de la columna A amb el Nom d'un alumne i accediu a Dades | Ordena les dades per la columna A. Fixeu-vos com ha quedat ordenada tota la llista. El menú que ha sortit us permet triar els criteris per ordenar la llista.
Premeu el botó del desplegable de la columna A, apareixen vàries opcions d'ordenació:

Trieu l'opció Ordena per A->Z (Ascendent). D'aquesta manera s'ordenarà la llista pel primer cognom de l'A a la Z.
- - Si dos alumnes tenen el primer cognom igual, els ordenarà a partir del segon cognom automàticament.
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

Crea un full nou FCM3 Nom i Cognoms i canvia el nom per 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.
Ú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.
Entreu el rètol a la fila 1 com es veu a la figura:

Desfem el filtre

Aneu a Dades - Desactiva el filtre. Veureu que apareixen les files da dades que estaven amagades i es mostren totes les dades que teniem a l'inici. La fila 460 manté les dades que hem introduït.

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.
Torneu a activar ara Dades- Crea un Filtre.
Premeu el botó de filtrat de la columna DATANAIXEMENT i trieu l'opció Filtra per condició. Apareix un desplegable. 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ó La Data és anterior. Data exacta Entreu directament 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. S'han amagat unes 70 files.

Premeu el botó de filtrat de la columna SEXE i trieu l'1, desmarqueu el 2. D'aquesta manera queden visibles només les files dels nois, que compleixen la condició anterior. Ens queden 37 files.
Ordeneu la llista com heu fet abans pel primer cognom, com hem 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 (Nois nascuts abans de 1985) que es veu a la figura:

Pràctica 3.2

En aquesta pràctica veureu com es calcula la lletra del NIF. Per fer-ho necessitareu fer servir:§ La funció VLOOKUP / HLOOKUP.§ 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ó VLOOKUP Aquesta funció, juntament amb HLOOKUP, serveixen per consultar taules. A continuació veureu com funciona.

Crea un nou full de càlcul del llibre de treball anomena el full ConsultaV
Introduïu els nombres següents en el rang A3:D7:

Entreu a la cel·la A9 la fórmula =VLOOKUP(7;A3:D7;2) . El resultat serà 28. Vegem per què és així. En executar-se la fórmula va a cercar el primer element del parèntesi (7) a la primera columna (A) del rang especificat (A3:D7) i quan el troba va a cercar 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.

Copiarem la fórmula de A9 a A10:A14 i feu les modificacions que teniu a la taula inferior, n'hi prou en seleccionar la cel·la A10 (i les que hi ha a sota) i col·locar el cursor en la barra de fórmules (part superior 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 VLOOKUP 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ó VLOOKUP que ja trobareu en pràctiques posteriors.

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

Pràctica 3.3

Desenvolupament de la pràctica

Crea un nou full del llibre i anomena'l NIF.

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, hi ha 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 =VLOOKUP(D2;G3:H25;2) . El que fa aquesta fórmula és cercar 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ó Amaga les columnes D i E.
- - 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ó Deixa d'amagar les columnes.
Modifiqueu les mides de les columnes i els detalls estètics com heu fet en mòduls anteriors.

El resultat hauria de ser semblant a:

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, amb el botó dret seleccioneu Protegeix l'interval.
A la dreta apareix la finestra Intervals i fulls protegits, seleccioneu Full.
Marqueu Excepte determinades ce·les. Si heu seleccionat la C2, apareixerà a la casella, si no apareix escriu C2.
Fes clic a Defineix els premisos.
Marca Mostra un advertiment quan s'editi aquest interval.

Pràctica 3.4

En aquesta pràctica seguireu treballant amb la consulta de taules. En aquest cas la taula estarà en un full diferent del de la fórmula.§ Ús de VLOOKUP amb una taula externa al full.§ Escriptura de fórmules per direccionament de cursor.§ Ús de la combinació de tecles Majúscules + F4.Haureu de construir un full que tradueixi les notes entrades numèricament a qualificacions literals. A més, calcularà la mitjana de les notes entrades i farà un recompte de les diferents qualificacions.

Desenvolupament de la pràctica

Crea dos nous fulls . Anomeneu-los Notes i Taula de notes, respectivament.
Seleccioneu el full Notes.
Entreu els rètols i dades de B2:C18.
Entreu els rètols de D2, B15 i de F2:F7.

El resultat serà semblant a:

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

Taula de notes

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) =VLOOKUP(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 =VLOOKUP(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) =VLOOKUP(
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: =VLOOKUP(C3
A continuació escriviu ; (el punt i coma).
Seguidament, amb el ratolí 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.
Sense fer clic enlloc, premeu la de tecla F4. Aquestes tecles col·loca els $ a l'última referència entrada. Per ara tenim escrit =VLOOKUP(C3;'Taula de notes'!$B$3:$D$7;2) .

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:C18).
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:

A continuació podeu acabar la pràctica:

Entreu a la cel·la C20 la fórmula =AVERAGE(C3:C18) per calcular la mitjana de les notes introduïdes.
En la cel·la D20 volem que surti la qualificació literal corresponent a aquesta mitjana, però sense abreviatures. Per això, seguint les instruccions anteriors, entreu-hi la fórmula =VLOOKUP(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 =COUNTIF($D$3:$D$18;"I"). D'aquesta manera comptarà quantes vegades la lletra I apareix en el rang D3:D18. Podeu introduir-la marcant amb el ratolí 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, de manera que per les cel·la del rang C3:C18 i per la cel·la C20 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.
Acabeu els detalls estètics com heu fet en mòduls anteriors. teniu una mostra del resultat final:

També podeu protegir el full:

Protegiu el full de modificacions accidentals, c, deixant desbloquejades les cel·les del rang C3:C18.

Pràctica 3.5

Les funcions de text que fareu servir en aquesta pràctica són LEN( ) , que ens diu el número de caracters d'una cel·la, LEFT () i RIGTH( ). Vegeu com funciona:

Crea un nou full del llibre i anomena'l Text.
Entreu a la cel·la A3 d'un full nou la paraula Informàtica.
Entreu a B3 la fórmula =LEN(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 =LEFT(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 =RIGHT(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 =RIGHT(A3;LEN(A3)-5). Fixeu-vos que escriu els últims caràcters de la paraula. El nombre de caràcters que escriu ve donat per LEN(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.

Pràctica 3.6

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.

Desenvolupament de la pràctica

Crea un nou full del llibre i anomena'l Esplugues de Llobregat.
Entreu a la cel·la A3 el rètol Centre; a la B3, Adreça, i a la C3, Població.. Poseu-los en negreta.
Modifiqueu l'amplada de les columnes de manera convenient.
Activeu el full Centres que teniu al mateix llibre FCM3NomiCognoms.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 buca el btext esborra en blau i fes clic.
  - Al quadre de cerca amb, la lupa, escriu el nom Esplugues de Llobregat. Per anar més de pressa, podeu entrar la lletra e de la inicial del nom de la població. i selecciona per que quedi marcada amb el tic verd. Fes D'acord.

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 | 2 Columnes a l'esquerra.

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 | Crea un filtre. Ves a Centre al desplegable Ordena per A->Z.
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 =RIGHT(A4;Len(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 abans, 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 LEN(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 =RIGHT(A13;LEN(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.

Aneu a la capçalera de la columna Tipus ,activeu el filtre Ordena per A->Z . 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 | Enganxa amb opcions especials. En aquesta opció podeu triar allò que voleu que es copiï. Cal seleccionar la opció Enganxa només valors.

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 | Suprimeix la columna A.
Ordeneu la nova llista segons els criteris que creieu més oportuns.
Desactiveu el filtre. Aneu a Dades - Desactiva el filtre.
Entreu a A1 el rètol Centres d'Esplugues de Llobregat. Centreu-lo al rang A1:D1 combinant les cel·les i doneu-li una mida de lletra més gran i negreta. Afegiu les línies de les vores a les cel·les necessàries.

El resultat ha de ser semblant a:

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
Acabeu els detalls estètics.

Pràctica 3.6

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ó VLOOKUP per a dades no ordenadesL'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.

Desenvolupament de la pràctica

Seleccioneu un nou full del llibre FCM3NomiCognoms. 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 Alumcad, Alumnes2 i Alumcurs, que ja teniu incorporats al llibre FCM3NomiCognoms.

Seleccioneu el full Alumcad. 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 | Crea un Filtre.
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 =VLOOKUP($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.
    - Per fer servir un rang d'un altre full farem servir el signe d'admiració després del nom del full (Alumnes2!)

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 =VLOOKUP($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. =VLOOKUP($A5;Alumnes2!$A$2:$E$260;5;0). 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 =VLOOKUP(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).
El resultat serà:

Exercicis

Després d’haver practicat amb diferents qüestions relacionades amb llistes i taules us proposem uns exercicis.

Feu cada exercici a un full nou, amb el noms E3.1, 3.2 ...

E3.1: 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 pel cognom.

A sota fes el recompte d'alumnes de cada sexe.

El resultat ha de ser semblant a:

E3.2: 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.

La taula és la mateixa que surt en la pràctica 3. La podeu copiar i modificar.

Les columnes que serveixen per fe rels càlculs s'han d'amagar. 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.

El resultat hauria de ser semblant a:

E3.3: 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:

Podreu amagar després les columnes on es fan els càlculs abans de mostrar el NIF Final.

E3.4: 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. A més ha de comptar el nombre de resultats per a cada nota. 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 AMB LETRA en el rang C3:C18, el full les ha de traduir (amb VLOOKUP) en el rang D3:D18 a partir de la Taula de notes.

A continuació es farà el recompte de resultats de cada nota a la dreta de la taula.

Pistes:=VLOOKUP(C3;F$3:G$7;2;0) =COUNTIF($C$3:$C$18;"I")

E3.5: Llistes combinades

Seguint les indicacions de la pràctica 5, us proposem elaborar una llista, a partir dels fulls Alumnes1, on figuri l'alumnat de 1r de batxillerat A. En aquesta llista han de sortir els dos cognoms, el nom, el DNI ordenats pel primer cognom.

Es pot fer filtrant

Recordeu d'enviar els exercicis al Classroom, amb les activitars i els exercicis a cada full amb el seu nom.

Recordeu que s'entrega des de el Drive.

Google Sites

Report abuse