Validation des données

L’objectif de cette leçon de la formation Excel est de vous montrer comment utiliser la validation des données pour exiger aux utilisateurs la saisie des données selon le type que vous définissiez, par exemple : des nombres, des dates, une longueur précise du texte à saisir .. etc
validation des données

Ajouter une validation de données

Tenez par exemple cette fiche Client qu’on doit remplir par les données d’un client qui vient à votre bureau (…) pour s’inscrire.
exemple validation des données

Vous avez donc des informations variées à demander à votre client. Mais vous voulez empêcher l’utilisateur de ce fichier Excel de saisir des données non valides.
Commencez donc par définir une validation de données pour le Nom et le prénom :

Validation de données selon la longueur du texte

  • Vous voulez par exemple que l’utilisateur tape un Nom ou un Prénom avec au moins trois caractères.
  • Sélectionnez les deux cellules en face des cellules Nom et Prénom.
  • Cliquez sur l’onglet Données, et dans le groupe Outils de données cliquez sur Validations de données
bouton validation de données

  • Dans la boite de dialogue qui s’affiche et sous Autoriser sélectionnez Longueur du texte.
  • Dans la liste Données sélectionnez Supérieur ou égal à et tapez donc 3 dans la zone Minimum.
Longueur texte

  • Cliquez ensuite sur l’onglet Alerte d’erreur
  • Sous Style, sélectionnez Arrêt pour obliger l’utilisateur de saisir une donnée valide.
  • Dans la zone Titre, écrivez le titre du message d’erreur qui va apparaître, par exemple (Erreur)
  • Et dans la zone Message d’erreur, tapez le message que vous voulez afficher pour l’utilisateur ; par exemple : « Vous devez saisir au moins deux caractères ! »
Alerte erreur

  • Enfin cliquez sur OK

Ceci va donc alerter l’utilisateur, s’il a oublié par faute de frappe, d’écrire le nom complet.

Validation de données de type date

Pour les données de type date, vous avez deux cellules qui affiche la date : Date et Date de naissance.
  • Dans la première cellule Date, vous voulez que la date saisie soit la date du jour, alors pour cela sélectionnez la cellule de saisie Date et affichez la boite de dialogue Validation de données.
  • Dans la liste Autoriser sélectionnez Date
  • Et dans la liste Données sélectionnez égale à
  • Dans la zone Date tapez la formule =Aujourdhui()
validation des données date


Note : La formule =Aujourdhui() est déjà saisie aussi dans la cellule Date, et à chaque ouverture de votre classeur elle est mise à jour.

Formule aujoudhui

  • Cliquez maintenant sur l’onglet Alerte d’erreur puis saisissez le message et le titre à afficher au cas où l’utilisateur entre une date non valide. (Voir en haut)
  • Validez par OK et faites un test.
Message d'erreur


En ce qui concerne la date de naissance, vous allez définir un intervalle de date supérieur à l’année 1900 et inférieur à l’année 2000 comme exemple.
  • Sélectionnez la cellule où vous allez saisir la date de naissance puis affichez la boite de dialogue Validation de données. (Voir la procédure en haut).
  • Sélectionnez Date dans la liste Autoriser, puis sélectionnez Comprise entre dans la liste Données.
  • Dans la zone Date de début, écrivez 01/01/1900, et dans la zone Date de fin tapez 31/12/2000.
  • Saisissez le message d’erreur sous l’onglet Alerte d’erreur et cliquez enfin sur OK.
validation de données date comprise entre

  • Essayez de saisir une date qui n’appartient pas à l'intervalle défini puis tapez Entrée.

Validation de données de type nombre entier

Vous allez interdire dans la cellule qui va afficher le nombre d’enfants, à l’utilisateur, de tapez un nombre supérieur à 10 par exemple !!
  • Alors, sélectionnez la cellule cible et affichez la boite de dialogue Validation de données.
  • Sélectionnez Nombre Entier dans la liste Autoriser et dans la liste Données choisissez Inférieur ou égal à
  • Dans la zone Maximum tapez 10
  • Définissez le message d’alerte sous l’onglet Alerte d’erreur.
validation des données nombre entier

  • Puis valider vos opérations.
  • Faites un test.

La même chose pour le numéro de téléphone, définissez une validation de données de type Nombre Entier, sauf que dans la zone Maximum tapez le grand nombre quand peut donner pour un numéro de téléphone, par exemple 9999999999, et ceci selon le format de numéro de téléphone utilisé dans votre pays, par exemple pour le pays de Canada on a le format (012) 345 6789 (càd 10 chiffres) et pour la France 01 23 45 67 89.
  • Saisissez ensuite, le message d’erreur puis validez par OK.


validation des données nombre entier telephone


Note : Dans cet exemple nous avons défini un masque de saisi pour les numéros de téléphone de Canada en procédant ainsi :

  • Sélectionnez la cellule où vous allez saisir le numéro de téléphone puis cliquez sur l’onglet Accueil puis dans le groupe Nombre cliquez sur la flèche du coin inférieur.
Afficher fenêtre Nombre

  • Dans la fenêtre qui s’affiche, cliquez sur Spécial sous Catégorie puis sélectionnez numéro de téléphone(Canada).
Format de nombre téléphone

  • Cliquez enfin sur OK.
  • Tapez donc un numéro de téléphone comme 1234567890 puis validez par Entrée.

Validation de données de type Liste

Vous voulez faciliter la saisie des noms des villes pour l’utilisateur, et au lieu de tapez à chaque fois le nom de la ville, vous allez lui affichez une liste déroulante qui va lui permettre de sélectionner le nom de la ville désiré.
Villes

  • Sélectionnez maintenant la cellule qui va afficher la liste des noms des villes, puis affichez la boite de dialogue Validation de données.
  • Sous Autoriser, sélectionnez Liste.
  • Cliquez dans la zone Source puis sélectionnez les noms des villes du tableau que vous venez de créer.
Validation de données liste

  • Cliquez sur l’onglet Alerte d’erreur et sélectionnez Avertissement cette fois dans la liste Style, en effet, vous allez permettre à l’utilisateur  de saisir quand même un nom de ville qui n’appartient pas à la liste sélectionnée.
Message avertissement

  • Dans la zone message, tapez par exemple : « Ce nom de ville n’existe pas dans la liste affichée ! »
  • Validez et faites un essai alors.

Modifier ou supprimer une validation de données

Commencez par rechercher les cellules pour lesquelles vous avez définies les validations de données.
  • Cliquez  donc sur Rechercher et sélectionner qui se trouve dans le groupe Edition sous l’onglet Accueil .
  • Dans la liste qui s’affiche cliquez sur Validation Données.
Recherche de validation de données

  • Excel sélectionnera pour vous toutes les cellules recherchées, vous pouvez donc commencer à modifier les validations activées en sélectionnant chaque cellule puis en affichant la boite de dialogue Validations de données.

Supprimer une validation de données

  • Sélectionnez la cellule avec une validation de données activée, puis affichez la boite de dialogue Validation de données.
  • Cliquez sur Effacer tout puis sur OK.
supprimer validation de données

  • Répétez les mêmes étapes pour chaque cellule.

Vous pouvez aussi rechercher toutes les validations actives puis affichez la boite de dialogue Validation de données.
Vous remarquez que Excel affiche une autre boite de dialogue, cliquez sur OK pour effacer les réglages courants comme c’est demandé.

Supprimer toutes les validations de données


Cliquez enfin sur OK dans la boite de dialogue Validation de données
Faites une nouvelle recherche pour s’assurer que les validations sont supprimées.

validation des données supprimées

Comment protéger une feuille et comment protéger un classeur ?

Dans cette leçon de la formation Excel vous allez apprendre comment protéger vos données sauvegardées sur un fichier Excel, en protégeant soit le classeur entier, soit la feuille ou des feuilles spécifiques ou soit des cellules particulières.
protéger classeur et feuille excel

Comment protéger un classeur ?

Si vous voulez interdire l’utilisation de votre classeur même la lecture des données qu’il contient, vous pouvez donc lui définir un mot de passe très fort qui combine entre chiffres, symboles et lettres, avec une longueur qui dépasse les huit caractères.

Pour ce faire donc :

Cliquez sur le menu Fichier puis sur Informations, ensuite cliquez sur le bouton Protéger le classeur.

Autorisation Excel


Dans la liste qui s’affiche cliquez sur chiffrer avec mot de passe

Chifrer unclasseur


Dans la boite du dialogue qui s’affiche tapez votre mot de passe et cliquez sur OK.

Mot de passe Classeur


Retapez votre mot de passe puis validez par OK.

Note : Vous devez conserver votre mot de passe dans un lieu sûr!

Et si vous aimeriez permettre à des utilisateurs de lire votre classeur sans lui apporter de modification, suivez ces étapes :

  • Cliquez sur le menu Fichier puis sur Enregistrer sous
  • Dans la boite de dialogue affichée sélectionnez Outils puis Options générales...
Options générales

  • Dans la zone Mot de passe pour la modification, tapez votre mot de passe
Verrouiller classeur par mot de passe

  • Puis retapez-le dans la nouvelle boite qui s’affiche.
  • Ensuite enregistrez votre classeur.

Lorsque l’utilisateur ouvre votre classeur, une boite de dialogue s’affiche lui demandant de saisir un mot de passe ou bien de cliquer sur Lecture seule.

Classeur à lecture seule


L’utilisateur ne modifiera rien ; Même s’il essaye de le faire, il ne pourra enregistrer ses modifications que dans un nouveau classeur, et votre classeur reste intact.

Note : Vous pouvez définir aussi un mot de passe pour la lecture de votre classeur qui sera différent du mot de passe pour la modification. Et lors de l’ouverture du classeur le mot de passe de lecture sera le premier demandé.

Protéger la structure de votre classeur

Vous pouvez empêcher les utilisateurs de votre classeur de masquer ou d’afficher les feuilles de votre classeur, de déplacer ou de supprimer ces feuilles ou d’insérer ou de copier des feuilles.
Cependant ils peuvent modifier vos données tant que les feuilles de votre classeur ne sont pas protégées.

Pour cela, suivez ces deux démarches :

1. Cliquez sur le menu Fichier puis sur Informations, cliquez après sur le bouton Protéger le classeur.
Dans la liste qui s’affiche cliquez sur Protéger la structure du classeur.

Protéger la structure de classeur


2. Cliquez sur l’onglet Révision puis sur Protéger le classeur.

Révision - proteger le classeur


Dans la boite de dialogue affichée l’option Structure est cochée par défaut, tapez donc vote mot de passe et validez par OK.

Proteger structure classeur


Note : l’option Fenêtre empêche l’utilisateur de votre classeur de modifier le dimensionnement de la fenêtre du classeur Excel.

Comment protéger une feuille et une cellule ?

Pour protéger les cellules contre des utilisations maladroites ou contre toute modification, Excel verrouille par défaut les cellules.

  • Sélectionnez donc la cellule ou les cellules que vous voulez protéger et cliquez sur elles avec le bouton droit de la souris puis choisissez Format de cellule.
  • Cliquez maintenant sur Protection et remarquez que l’option Verrouillée est cochée par défaut.
Cellule verrouillée

  • Cette restriction ne fonctionnera que lorsque la feuille soit protégée.

Protéger la feuille Excel

Vous avez 4 méthodes pour verrouiller votre feuille ou vos feuilles de calcul.
1. Cliquez avec le bouton droit de la souris sur cette ou ces feuilles puis cliquez sur Protéger la feuille…

Proteger la feuille


2.
Dans l’onglet Accueil et dans le groupe Cellules cliquez sur Format puis sur Protéger la feuille…

Format puis Protéger


3.
Dans l’onglet Révision cliquez sur Protéger la feuille…

Révision et proteger la feuille


4.
Cliquez sur le menu Fichier puis sur Informations et cliquez après sur le  bouton Protéger le classeur, et dans la liste affichée cliquez sur Protéger la feuille active…

Fichier puis proteger la feuille

  • Dans la boite de dialogue affichée cochez les options à autoriser aux utilisateurs de votre feuille de calcul, et décochez celles que vous voulez interdire.
  • Après, tapez votre mot de passe et confirmez le dans la deuxième boite.
verrrouiller feuille mot de passe

  • Essayez maintenant de saisir du texte dans une cellule… Excel vous affiche donc un message vous informant que la cellule est verrouillée.
message cellule verrouillée


Déprotégez votre classeur ou feuille Excel

Pour annuler la protection de votre classeur, ouvrez-le puis :
  • Si vous avez défini un mot de passe de lecture ou d’ouverture du classeur, cliquez sur le menu Fichier puis sur Informations et cliquez sur le bouton Protéger le Classeur et dans la boite qui s’affiche supprimer le mot de passe et valider par OK.
Déproteger classeur

  • Si vous avez protégé la structure du classeur, cliquez sur l’onglet Révision et cliquez sur protéger le classeur.

Note : Remarquez que le bouton Protéger le classeur est activé ce qui signifie que votre classeur est verrouillé.
  • Entrez donc le mot de passe et validez par OK.

Pour déprotéger votre feuille de calcul, suivez la même procédure pour verrouiller la feuille Excel décrite en haut.
Mais au lieu de trouver Protéger la feuille vous allez trouver à sa place Ôter la protection de la feuille….

Oter la protection de la feuille


Entrez votre mot de passe et validez par OK.


Déplacer des colonnes ou des lignes contenant des données sans problème

Aujourd’hui dans cette formation Excel vous allez apprendre à déplacer des colonnes ou des lignes sans rien écraser ou disperser.
déplacer colonne et ligne Excel

Déplacer une colonne ou des colonnes

La méthode la plus simple et qui vous fait gagner du temps est la suivante :
  • Sélectionnez toute la colonne à déplacer en cliquant sur son entête.
  • Appuyez sur la touche Maj (Shift) et maintenez-la enfoncée tout en exécutant ces opérations :
  • Amenez le pointeur de la souris jusqu’au bord droit ou gauche de la colonne sélectionnée, et remarquez la transformation du pointeur en une croix à quatre flèches.
  • Cliquez et faites glisser la souris vers le nouvel emplacement. Vous remarquez une ligne grise verticale qui suit votre mouvement de déplacement de la souris.
  • Lorsque cette ligne grise arrive à l’emplacement voulu relâchez le bouton de la souris et libérez la touche Shift.
Déplacer colonne Excel


Pour déplacer plus d’une colonne il suffit de les sélectionner et de procéder de la même façon pour les déplacer.

Déplacer des colonnes


Déplacer une ou plusieurs lignes

  • Vous allez suivre la même méthode appliquée dans le déplacement des colonnes.
  • Sélectionnez donc votre ligne ou vos lignes à déplacer.
  • Maintenez la touche shift enfoncée et amenez le pointeur de la souris sur la limite de ou des lignes sélectionnées puis cliquez et faites glisser la souris vers le nouvel emplacement.
  • Relâchez maintenant le bouton de la souris et la touche Shift.
Déplacer lignes Excel


Déplacer des colonnes ou des lignes en effectuant une copie

Suivez la même méthode décrite ci-dessus mais au lieu de maintenir la touche Shift enfoncée, appuyez sur les deux touches: Ctrl et Shift et maintenez-les enfoncées durant toute l’opération de déplacement de vos lignes ou colonnes.

Déplacer et copier une colonne


Est-ce que les calculs resteront intacts après le déplacement ?

Si vous avez utilisé des formules dans vos colonnes ou lignes que vous voulez déplacer, Excel ne changera rien de vos résultats, en effet Excel changera les références des cellules utilisées dans ces formules par les nouvelles références qu’elles prennent après le déplacement.

Dans le tableau suivant nous avons appliqué une formule simple qui consiste à faire la somme des nombres se trouvant dans les cellules des colonnes A et B.
Par exemple nous avons écrit dans la cellule C2 ceci =A2+B2

Somme de deux nombres


Nous avons aussi calculé la moyenne de cette somme dans la colonne D en écrivant dans la cellule D2 ceci =C2/2

Moyenne calculée


Maintenant nous allons déplacer la colonne D et la déposer entre la colonne A et B.
Et voici le résultat:

Après déplacement




Comment interdire l’impression d’un classeur Excel ou d’une feuille Excel?

L’article suivant de la formation Excel va vous montrer comment interdire quelqu’un qui utilise votre classeur Excel de l’imprimer, ou bien d’imprimer quelques feuilles seulement et non tout le classeur.
Interdire l'impression d'un classeur excel

Interdire l’impression d’un fichier Excel

Pour interdire l’impression de la totalité des feuilles d’un classeur Excel vous avez deux choix : soit vous allez faire afficher un message d’interdiction d’impression à l’utilisateur de votre fichier Excel quand il veut l’imprimer ou bien vous pouvez protéger l’impression par un mot de passe.

Bloquer l’impression d’un fichier Excel

Vous allez donc créer une macro ou bien du code VBA.
Pour ce faire affichez tout d’abord la fenêtre VBA en tapant Alt+F11.
Vous pouvez aussi afficher la fenêtre VBA à partir de l’onglet Développeur
Cet onglet n’est pas affiché par défaut dans le ruban Excel comme vous pouvez le remarquez, cependant vous pouvez l’ajouter à ce ruban en suivant cette démarche :
  • Cliquez sur le menu Fichier puis sur Options.
  • Choisissez personnaliser le ruban et dans la zone Onglets principaux cochez Développeur.
Cochez Onglet Développeur

  • Cliquez maintenant sur Développeur puis sur le bouton Visual Basic dans le groupe Code.
Visual basic

  • La fenêtre VBA est affichée alors.
Fenêtre VBA


  • Double cliquez sur ThisWorkBook
  • Déroulez la liste Général et choisissez  Workbook
Thisworkbook

  • Dans la liste à droite de la liste Général sélectionnez BeforePrint
Afficher beforeprint

  • Ecrivez maintenant ce code :

Msgbox « Vous ne pouvez pas imprimer ce ficher ! »,vbcritical
Cancel=True

insérer code


  • Tapez Alt+F11 ou bien Cliquez sur le bouton Affichage Microsoft Excel pour revenir à l’affichage normal de votre fichier Excel
Affichage Excel

  • Essayez maintenant d’imprimer votre fichier Excel !
Message interdisant l'impression


Quand vous aurez terminé sauvegardez votre travail en cliquant sur le bouton Enregistrer
Vous remarquez que Excel vous demande d’enregistrer votre fichier Excel sous un autre type qui accepte l’utilisation des Macros
Cliquez alors sur Non et dans la fenêtre qui s’affiche sélectionnez le type de fichier avec le nom Classeur Excel prenant en charge les macros

Type de fichier avec macro


Puis cliquez sur OK.

Fermez et rouvrez votre fichier Excel pour tester  encore une fois si l’impression est interdite.

Protéger l’impression par un mot de passe

Vous allez suivre la même démarche décrite précédemment jusqu’à ce que vous arriviez à l’étape où vous allez entrer du code.
Et à la place de (msgbox « « Vous ne pouvez pas imprimer ……) tapez le code suivant:

Motdepasse= "123456789" (Modifiez ce code avec votre mot de passe personnel)
X=inputBox("Veuillez entrer votre mot de passe !")
If X <> Motdepasse then
MsgBox ("Veuillez entrer un mot de passe valide !") , VBCritical
Cancel=True
End if

Interdire impression par mot de passe


Ensuite essayez d’imprimer votre classeur Excel

Message Mot de passe


Empêcher l'impression d'une feuille Excel

De la même façon vous allez empêcher l’impression d’une feuille Excel ou quelques feuilles de votre classeur Excel.
En arrivant à l’étape d’écriture du code comme vous l’avez vue en haut, tapez ces lignes en supposant que vous allez interdire l’impression de la feuille nommée « liste1 »

If ActiveSheet.Name = "liste1"  Then 
Msgbox "Vous ne pouvez pas imprimer cette feuille !! ",vbcritical
Cancel=True
End if

Code pour empêcher l'impression d'une feuille excel


Note : Vous devez écrire correctement; entre les guillemets; le nom de votre feuille et sans espace aussi avant ou après ce nom.

Faites un essai d’impression de votre feuille interdite et des autres feuilles qui ne sont pas interdites d’impression.

message interdire impression feuille