Enrera
 Mòdul 3
Introducció al full de càlcul
  Pràctica
1
2
3
4
5
   
Exercicis
Exercicis
 
 

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

  • La funció BUSCARV.
  • La funció RESIDUO.
  • 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. El resultat hauria de ser semblant a:
 
 

nnn

Consideracions prèvies

La principal novetat d'aquesta pràctica és la utilització de la funció BUSCARV. Aquesta funció, juntament amb BUSCARH, 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 =BUSCARV(7;A3:D7;2) . El resultat serà 28. Vegem per què és així. En executar-se la fórmula va a buscar el primer element del parèntesi (7) a la primera columna (A) del rang especificat (A3:D7) i quan el troba va a buscar 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: 
 

Fórmula Resultat
BUSCARV(6;A3:D7;2)
17
BUSCARV(6;A3:D7;3)
32
BUSCARV(15;A3:D7;2)
1
BUSCARV(8;A3:D7;4)
15
BUSCARV(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 a la primera columna del rang especificat (7 i 9). 

Com a resum podríem dir que BUSCARV 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ó BUSCARV que ja trobareu en pràctiques posteriors.

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

Ajuda Busqueu informació sobre les funcions BUSCARV i BUSCARH.

   
Desenvolupament de la pràctica
   
Pràctica

Seleccioneu un nou full del llibre MODUL3 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 buscar 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  =RESIDUO(  ;  ) i ara la fareu servir.

  • Entreu a la cel·la D2 la fórmula   =RESIDUO(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  =BUSCARV(D2;G3:H25;2). El que fa aquesta fórmula és buscar 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 Formato | Celdas | Protección de celdas i desactiveu l'opció Protegido.
  • Protegiu ara el full com heu fet en la pràctica 2 del mòdul 2. 
  • Deseu el llibre.
   
   
 
Amunt