Lilapuce
 

exercices (mardi 30 mars 2010)

Validation & RECHERCHEV (Excel)

Une fois n’est pas coutume, voici un exo presque sans image. Rien que du texte (le cauchemar).

Je me contenterai donc de lister, ci-dessous, les consignes correspondant aux différentes manipulations effectuées en atelier. Vous devriez, normalement, retrouver vos repères assez facile ment en vous aidant de votre mémoire et de vos documents de travail.

Récupérez, comme d’habitude, les fichiers joints.

Ouvrez un nouveau classeur. Il nous faut quatre feuilles, que vous renommez ainsi :

- Formulaire

- Usage

- Code postal

- Fréquentation

Enregistrez votre classeur en tant que « Grille de fréquentation.xls ».

Laissez de côté, pour l’instant de feuille « Formulaire ». Nous y reviendrons.

Feuille « Code postal »

Ouvrez le fichier joint « CP_Villes_Arrondt.xls », sélectionnez les données et copiez.


Remarque importante : vous constatez que les arrondissements de Paris ont été placés avant les communes séquano-dionysiennes. Autrement-dit, ce qui commence par « 75 » précède « 93 ». Le respect de cet ordre numérique - croissant - est important pour que nous puissions effectuer la petite manip que nous verrons plus loin.

Fermez ce classeur.

Retournez sur « Grille de fréquentation.xls » :

- Cliquez sur l’onglet « Code postal »,

- Placez-vous en « A1 »

- Collez.


Sélectionnez le tableau (« A1:B40 »).

Cliquez sur la zone nom (qui indique « A1 ») afin de mettre en surbrillance.

Tapez « codepostal » ; important : sans l’espace entre « code » et « postal ». Validez par « Entrée ». Vous venez de renommer votre plage de cellule.

Feuille « Usage »

Cliquez sur la feuille « Usage ». Placez-vous en « A1 » et tapez les données suivantes (« Usage » en A1, « Emploi » en A2, etc.) :

Usage

Emploi
Loisirs
Etudes
Logement
Voyage
Santé
Autre

Sélectionner « A2 :A8 » (sans l’intitulé « Usage ») et renommer la plage de cellules en tant que « usage » en utilisant la même méthode que précédemment.

Feuille « Fréquentation »

Cliquez sur la feuille « Fréquentation ». Placez-vous en « A1 » et tapez les données suivantes (« Fréquentation » en A1, « Première visite » en A2, etc.) :

Fréquentation

Première visite
Inférieur à 1 mois
Depuis 3 mois
Entre 3 et 6 mois
Entre 6 et 9 mois
Entre 9 et 12 mois
Plus de 12 mois

Sélectionner « A2 :A8 » (sans le l’intitulé « Fréquentation ») et renommer la plage de cellules en tant que « frequentation » ; important : sans accent sur le « e ».

Feuille « Formulaire »

Petite précision d’usage : en fait, nous n’allons pas vraiment aborder ici le « formulaire », au sens où on l’entend généralement sur Excel. Admettons juste que cette feuille de calcul nous serve à recueillir des données, ce qui constitue une procédure assimilable à un « formulaire ».

Cliquez sur la feuille « Formulaire ». Remplissez de « A1 » à « H1 » les données suivantes :

Sélectionnez ce groupe de cellules, passez-le en gras et mettez un fond de couleur. Adaptez la largeur des colonnes en fonction du contenu (par exemple élargissez la colonne « Ville » pour qu’elle puisse contenir « le Pré-Saint-Gervais »).

Dans la colonne « Date » (« A »), sélectionnez « A2 : A45 » et faîtes CTRL + (touche) «  ; » (CTRL maintenue et pression brève sur le point-virgule). Vous venez d’insérer la date du jour ; et contrairement à la fonction « =AUJOURDHUI() » la date ne s’actualisera pas le jour suivant.

En colonne « B » (« Sexe »), sélectionnez « B2 :B45 », passez par le menu « Données/Validation ».

Dans la boîte de dialogue, pour « Autoriser », sélectionnez « Liste » et dans le champ « Source » tapez : « homme;femme » (sans les guillemets, mais avec le point virgule et sans espace).

Vous venez de créer une liste de sélection pour la colonne « B ». Il suffit de se placer, sur n’importe quelle cellule de la colonne et de sélectionner une option de la liste.

En colonne C (« Usage »), sélectionnez « C2 :C45 », passez par le menu « Données/Validation ».

Dans la boîte de dialogue, pour « Autoriser », sélectionnez « Liste » et dans le champ « Source » tapez : « =usage » (sans les guillemets, mais avec le signe égal et sans espace).

Vous venez de créer une liste de sélection pour la colonne « C » avec la plage de cellules que vous aviez précédemment nommée « usage » (sur la feuille « Usage »). Il suffit de se placer, sur n’importe quelle cellule de la colonne et de sélectionner une option de la liste.

En colonne « D » (« Code postal ») vous ne tapez rien (pour l’instant).

En colonne « E » (« Ville »), placez vous en « E2 » et tapez le code suivant :

=RECHERCHEV(D2;codepostal;2;FAUX)

Vous pouvez également faire appel à l’assistant fonction, catégorie « Recherche & Matrices », sélectionnez « RECHERCHEV », puis indiquez les valeur suivantes

- Valeur recherchée : D2 (clic simple sur la cellule)

- Table_matrice : codepostal (à saisir)

- No_index_col : 2 (à saisir)

- Valeur proche : FAUX (à saisir)

Après validation, il est normal que le tableau renvoie un message d’erreur en « E2 » (#N/A) car on demande d’afficher un résultat à partir d’une cellule vide (« D2 »).

Nous verrons, dans un second temps, comment masquer cette erreur. En attendant, testons notre formule :

Placez-vous en « D2 » et tapez « 93260 ».

Normalement le message d’erreur s’efface et le nom de la ville devrait s’inscrire tout seul en « E2 », grâce à la formule que nous avons placée.

Petit rappel de la formule :

=RECHERCHEV(D2;codepostal;2;FAUX)

Nous demandons :

- Est-ce que le code postal tapé en « D2 » se trouve également dans le groupe de cellules « codepostal » ? (D’où le message d’erreur si « D2 » est vide ou s’il ne contient pas un code postal parisien ou séquano-dyonisien).

- Tu as trouvé le même code postal ? Alors tu restes sur la même ligne et tu m’affiches ce qui est placé en deuxième colonne. La valeur « 2 » indique le « rang de colonne », c’est à dire le nom de la ville.

- Tu dois trouver exactement le même code postal, et non une correspondance « au plus proche ». Par exemple je ne veux pas que tu m’affiches « le Pré-Saint-Gervais » si je tape « 95350 » au lieu de « 93350 ». Afin d’obtenir une correspondance exacte et non « au plus proche », on ajoute l’argument « FAUX », à la fin de la formule.

Dans cette colonne « E », nous allons donc pouvoir récupérer le nom de la ville (ou de l’arrondissement parisien) à partir des codes postaux saisis en « D ».

Avant de généraliser la formule (placée en « E2 ») à toute la colonne « E », il est temps de masquer le message d’erreur (qui apparaîtra obligatoirement si aucun code postal n’est saisi) :

Placez-vous en « E2 » et cliquez sur le menu « Format/Mise en forme conditionnelle ».

Sélectionnez l’option « La formule est » et dans le champ tapez le code suivant :

=ESTERREUR(E2)

Cliquez sur le bouton « Format » et sélectionnez l’option pour mettre le texte en blanc.

Validez les options de mise en forme conditionnelle.

Faites un essai : placez-vous en « D2 » et supprimez le code postal. Le code d’erreur (#N/A) ne devrait désormais plus apparaître en « E2 ».

Tapez à nouveau un code postal du 9cube ou de Paris. Normalement la ville, ou l’arrondissement correspondant, devrait apparaître en « E2 ».

Placez-vous en « E2 » puis étirez la formule jusqu’en « E45 ».
Pour masquer les messages d’erreur, placez-vous en « E2 », cliquez sur le pinceau et appliquez le pinceau de « E3 » à « E45 ».

Pour masquer les petites marques d’erreurs (angles verts) : passez par le menu « Outils/ Options... ». Cliquez sur l’onglet « Vérification des erreurs »et décochez la case « Activer la vérification des erreurs d’arrière-plan ».

En colonne F (« Fréquentation »), sélectionnez « F2:F45 », passez par le menu « Données/Validation ».

Dans la boîte de dialogue, pour « Autoriser », sélectionnez « Liste » et dans le champ « Source » tapez : « =frequentation » (sans les guillemets, mais avec le signe égal).

Vous venez de créer une liste de sélection pour la colonne « F » avec la plage de cellules que vous aviez précédemment nommée « frequentation ».

En colonne « G » (Date de naissance), sélectionnez le groupe « G2:G45 » et, via le menu « Format/Cellule », définissez un format de cellule « Date » de type « JJ/MM/AA » (par exemple 20/12/57). Ne tapez rien pour l’instant en colonne « G ».

En colonne « H » (Âge), placez-vous en H2 et tapez le code suivant :

=ENT((A2-G2)/365)

Petite explication :

- On demande un chiffre entier, sans décimale (ENT)

- On soustrait la date du jour (« A2 ») de la date de naissance (« G2 ») et on divise par le nombre de jour d’une année. Cela nous donne (en gros) l’âge du capitaine.

Il est normal qu’une donnée fantaisiste s’affiche en colonne « H » (par exemple « 110 ») : nous demandons de calculer une formule à partir d’une cellule vide (« G2 »).

Nous verrons plus tard comment corriger cela, en attendant, testons notre formule :

Tapez votre date de naissance en G2…

Vous êtes démasqué. Tirez la formule stockée en « H2 » jusqu’en « H45 ».

Terminons ce prototype par un petit tour de ripolinade, histoire de masquer la misère ; je veux parler de ces données incohérentes qui hurlent en colonne « H » :

- Placez-vous en « G2 » et tapez sur la touche apostrophe (’) de votre clavier.

Après validation, vous remarquerez que ce caractère (d’échappement) permet d’indiquer que la cellule contient une valeur « non-nulle » sans, pour autant que s’affiche ce caractère (ce qui pourrait faire croire que la cellule « G2 » est vide).

Par contre, la valeur fantaisiste qui s’affichait avant, en « H2 », quand « G2 » était vide, a disparue mais ceci a été remplacé par un message d’erreur (#VALEUR !) ; ce qui n’est guère mieux.

Qu’à cela ne tienne :

- Tirez la cellule « G2 » jusqu’en « G45 », afin de dupliquer l’apostrophe (et le message d’erreur en colonne « H »).

- Placez-vous en « H2 ». Cliquez sur le menu « Format/Mise en forme conditionnelle ».

Sélectionnez l’option « La formule est » et dans le champ tapez le code suivant :

=ESTERREUR(H2)

Cliquez sur le bouton « Format » et sélectionnez l’option pour mettre le texte en blanc.

Validez la mise en forme conditionnelle.

Cliquez sur « H2 », prenez l’outil « pinceau » et appliquez cet outil de « G3 » à « G45 ».

Essayez de taper à nouveau des dates de naissance en colonne « G ».

Voilà quelques bribes qui devraient nous permettre de construire la première étape d’une grille de fréquentation d’un lieu public, comparable au Kiosque…

 

Code postaux (à copier)

 
 

Document « finalisé »

 

 

Autres exercices
de l'atelier Tableur

 

L'atelier «Tableur»