Les messages d'erreur Excel

Comprendre les messages d’erreur dans les formules Excel

Il nous est tous arrivés après avoir écrit une fonction dans Excel apparaître des erreurs du genre: #VALEUR!, #DIV/0!, #REF!, #N/A! ou encore #####. Il m'arrive encore aujourd’hui de générer ces types d'erreurs. Les explications mentionnées ci-après devraient vous aider à résoudre vos problèmes. Pour cela, il faut bien comprendre l’explication de chaque message d’erreur.

1. - #VALEUR! : Ce type d’erreur indique en général que vous êtes en train de faire une opération avec une donnée non-numérique. Par exemple, la formule  = 5 + "Ventes" retournera #VALEUR! comme erreur. Cette erreur arrive aussi lorsque vous entrez une formule matricielle et oubliez de valider avec Ctrl+Shift+Entrée.

2. -  #DIV/0 : Ce type d’erreur lorsque vous diviser un nombre par zéro. Ou lorsque la valeur du dénominateur est évaluée à 0.

3. -  #REF! : Cette erreur apparaît lorsque la référence d’une cellule n’est pas valide. Par exemple, si dans une formule, vous faites référence à une cellule qui a été supprimée. Il peut aussi apparaître lorsque vous faites référence à une cellule dans un autre fichier qui a été supprimé.

4. -  #N/A! : Cette erreur apparaît lorsqu’une valeur n’est pas valable dans une formule. En général elle survient lorsqu’en utilisant une fonction de recherche telle que RECHERCHEV, EQUIV, RECHERCHE, la valeur est introuvable. On peut aussi retrouver cette erreur lorsque dans une formule matricielle, l’une des matrices n’a pas la même taille que les autres ou encore lorsque dans une fonction vous oubliez un argument indispensable.

Il est aussi important de noter que lorsque dans une cellule il y a l’erreur #N/A! alors tous les calculs se référant à cette cellule auront comme résultat #N/A!.

Il faut aussi savoir qu’il y a une fonction qui permet d’insérer cette erreur, il suffit d’écrire  :  =NA() et vous aurez comme résultat l’erreur.

5. - ###### : Ceci n’est pas vraiment une erreur. En général c’est lorsqu’il la largeur de la colonne n’est pas assez grande pour représenter la valeur de la cellule. Il suffit alors juste d’augmenter la taille de la colonne pour avoir le bon résultat. Des fois aussi Excel fait apparaître cette valeur pour vous dire que vous êtes en train de soustraire une date inférieure à une autre date.

Garder visible les entêtes d'un tableau

Tutoriel Excel

Garder visible les entêtes d’un tableau qu’on fait défiler

Lorsque vous avez un tableau ou une liste assez longue avec des entêtes au-dessus, le fait de défiler le tableau vers le bas peut vous faire perdre de vue ces entêtes.

Il y a quelques astuces qu’on peut utiliser pour éviter cette situation.

Prenons le cas du tableau ci-dessous :

L’objectif est de pouvoir atteindre les cellules après la ligne 134 et de toujours voir les entêtes. 

A) Méthode n° 1 : Figer les volets

• Se mettre sur la cellule A118
• Dans le ruban, Onglet Affichage/Groupe Fenêtre/Figer les volets/Figer les volets

Dès que c’est fait, alors tous les cellules au-dessus de la ligne 117 et à gauche de la colonne A seront toujours visibles et une ligne noire apparaît.

Il est important de noter que pour annuler cette opération Ctrl+Z ne marche pas, il suffit de repartir sur Affichage/Groupe Fenêtre/Figer les volets/Libérer les volets

B) Méthode n° 2 : Mettre sous forme de tableau

• Se mettre sur une des cellules du rapport
• Dans le ruban, Onglet Accueil/Groupe Style/ Mettre sous forme de tableau / choisir le modèle qu’on préfère

Lorsque le rapport est sous forme de tableau et que l’on fait défiler le rapport vers le bas, alors les titres des colonnes sont remplacés par les entêtes.

Inverser un texte d'une cellule

Tutoriel Excel

Comment inverser un texte

Exemple :  Changer Jean-Paul BONNETTO pour BONNETTO Jean-Paul

Il vous est certainement déjà arrivé de recevoir un fichier dans lequel le nom et le prénom sont dans le mauvais ordre et vous avez l’obligation de les inverser.

Regardons l’exemple ci-dessous :

Pour réussir à inverser les noms nous allons utiliser une formule avec plusieurs fonctions textes imbriquées. Il faut d’abord insérer une colonne entre les colonnes B et C

Pour se faire : Clic droit sur la lettre C de la colonne C, puis Insertion ou Ctrl +

Nous allons utiliser les fonctions : 

• CHERCHE() : elle permet de retrouver la position d’un caractère ou d’un ensemble de caractère dans un texte
• GAUCHE() : elle permet d’extraire un ensemble de caractères d’un texte en partant de sa gauche. Elle a 2 arguments : le 1er précise dans quel texte on extrait les caractères et le second le nombre de caractère à extraire.
• DROITE() : elle permet d’extraire un ensemble de caractères d’un texte en partant de sa droite. Elle a 2 arguments : le 1er précise dans quel texte on extrait les caractères et le second le nombre de caractère à extraire.
• CONCATENER() ou & : permet de mettre ensemble plusieurs caractères

L'objectif initial est d’inverser l’ordre de 2 groupes de caractères séparés par un espace. On va donc commencer par chercher où se trouve l’espace dans le texte.

Pour retrouver la position de l’espace on écrira : = CHERCHE(" ";B108;1)

Quelques explications au sujet des éléments de cette fonction :

-  " "  : signifie l’espace en question. En effet le 1er argument de cette fonction est le caractère recherché et ici nous recherchons la position de l’espace.

- B108 : cet argument précise dans quel texte nous devons chercher le caractère en question et dans ce cas, il s’agit du texte présent dans la cellule B108

- 1 : cet argument précise à partir du quantième caractère il faut chercher dans le texte. On cherche à partir du 1er caractère.

Le résultat de cette fonction est 10. En effet l’espace dans B108 est le 10ème caractère.

Maintenant que nous savons où se trouve l’espace nous l’utiliserons comme repère pour inverser notre texte. Nous allons d’une part extraire le 2nd nom et ensuite extraire le 1er nom pour finalement les concaténer.

- Extraction de Jean-Paul : =GAUCHE(B108;NBCAR(B108)-CHERCHE(" ";B108;1)+1)

- Extraction de Bonnetto : =DROITE(B108;NBCAR(B108)-CHERCHE(" ";B108;1))

- Et pour finir:

 =DROITE(B108;NBCAR(B108)-CHERCHE(" ";B108;1))& " " &GAUCHE(B108;NBCAR(B108)-CHERCHE(" ";B108;1)+1)

Cette formule peut être simplement incrémentée et on aura le résultat :

Il suffira enfin de supprimer la colonne B après avoir copier et coller les valeurs de la colonne C bien évidemment.

Calculer un pourcentage cumulé

Tutoriel Excel

Calculer le pourcentage cumulé du total automatiquement

Lorsque l’on veut mettre par exemple la règle de 20/80, il peut s’avérer utile de savoir-faire facilement le pourcentage cumulé. Il s’agit de faire le pourcentage sur les montants cumulés de plusieurs éléments, cela oblige d’écrire plusieurs formules. Voici une méthode qui permet de réaliser ce calcul avec une seule formule qu'il nous restera à incrémenter.  

Prenons l’exemple ci-dessous :

Dans la colonne D, on veut avoir le pourcentage cumulé. Deux méthodes peuvent être utilisées : 

A) Méthode n° 1: deux formules et une incrémentation 

• Se positionner sur D101 et écrire : =C101/$C$109 (ne pas oublier les dollars avec la touche F4) et bien sûr mettre en format de pourcentage
• Se positionner sur D102 et écrire : =C102/$C$109+D101
• Copier cette formule jusqu’à la cellule D108

B) Méthode n° 2 : une formule et une incrémentation 

• Se positionner sur D101 et écrire : =Somme(C101:C$101)/$C$109)
• Copier cette formule jusqu’à la cellule D108

Faire un rapport dynamique

Tutoriel Excel

Comment faire un rapport dynamique mensuel grâce à une zone de liste déroulante

Objectif :  Vous apprendre une technique utilisée dans la mise en place de tableaux de bord dans Excel. On utilise une zone de liste déroulante pour sélectionner le mois à représenter dans le tableau de bord.

Dans une feuille de votre classeur Excel, vous avez ce rapport brut :

L’objectif est de pouvoir avoir un rapport dynamique comme celui-ci-dessous :

Pour le faire il faut connaitre :

• Les fonctions : GRANDE.VALEUR(), INDEX(), DECALER()
• L'imbrication de ces fonctions
• La mise en forme conditionnelle en barre de données
• L'insertion d'une Zone de liste déroulante
• Les références relatives et absolues.

1.     Insérer notre liste déroulante avec ses données

Pour insérer la zone de liste déroulante avec les données des mois à l’intérieur, il faudra d’abord créer les données ensuite insérer la liste déroulante et lui « montrer » où récupérer ses données.

• Sur une des feuilles du classeur, insérer les données  Janvier, Février…Décembre par exemple entre B15:B26
• Nommer cette colonne, par exemple «lesmois».  Pour nommer une colonne il suffit de la sélectionner, ensuite se mettre sur Zone de noms et écrire le nouveau nom.

• Pour insérer la zone de liste déroulante, dans le ruban Développeur/Contrôles/Insérer/ cliquer sur le contrôle Zone de liste déroulante 

•  Avec la souris, glisser le contrôle sur la feuille, je clique et je ne lâche pas pour former la liste déroulante.
• Cliquer sur la zone de liste déroulante avec le bouton droit de la souris et cliquer sur Format de contrôle
• Dans la nouvelle boite de dialogue, remplir comme ci-dessous :

A ce niveau la liste déroulante est prête et à chaque fois qu'un élément est choisi, la valeur de la cellule A1 change aussi.

2.     Retrouver les valeurs du mois choisi dans la liste déroulante

Une petite difficulté ici est de retrouver les valeurs du mois mais aussi de les classer du plus grand ou plus petit. Pour y arriver nous allons utiliser la fonction DECALER().

• En E8 par exemple, insérer le mois choisi dans la liste déroulante, pour cela on va écrire la formule : INDEX(Feuil1 !$B$3:$M$3;A1).
• Il faut noter ici que la feuille Feuil1 est celle sur laquelle nous avons nos données initiales.

Nous allons retrouver maintenant les valeurs classées par ordre décroissant :

• En E9 par exemple, écrire :=GRANDE.VALEUR(DECALER(Feuil1!$A$4:$A$7;;$A$1);1)

Il faut savoir que la fonction GRANDE.VALEUR retrouve la kième plus grande valeur d'une place de cellules. Dans notre cas, la plage de cellules est :
        DECALER(Feuil1!$A$4 :$A$7;;$A$1)  et la kième valeur est 1

• Pour pouvoir incrémenter cette formule on pourra remplacer le 1 par cette formule :
  
      LIGNE()-8. La fonction LIGNE() permet de retrouver le numéro de ligne de la cellule active, ici c’est 9.
• On aura pour finir en H16 :
  =GRANDE.VALEUR(DECALER(Feuil1!$A$4:$A$7 ;;Feuil2!$A$1);LIGNE()-8)

Cette formule peut maintenant être incrémentée (ou copier) jusqu’à la cellule H19.

3.     Nous allons maintenant retrouver les noms pour chaque vente

• En G16 on écrira :
  = INDEX(Feuil1!$A$4:$A$7;EQUIV(H16;DECALER(Feuil1 !$A$4:$A$7;;$A$1);0))
• On incrémentera la formule jusqu’en G19 et le résultat sera :

4. Nous allons rajouter les mises en forme conditionnelles en barre de données :

• En I16, écrire =H16
• Incrémenter la formule jusqu’en I19
• Sélectionner I16 :I19
• Dans le ruban, Accueil/Style/Mise en forme conditionnelle/Barre de données/Autres règles…
• Remplir la nouvelle boite de dialogue comme ci-dessous et valider :

Voici le résultat :

Sécuriser et Masquer les formules d'une feuille

Tutoriel Excel 

Quatre étapes pour sécuriser et masquer les formules d’une feuille 

On peut avoir besoin de protéger les cellules qui ont des formules pour éviter de mauvaises manipulations ou tout simplement pour ne pas partager son talent.

1. Déverrouiller toutes les cellules du classeur : 

Par défaut toutes les cellules d’un nouveau classeur sont verrouillées, il faut donc d’abord les déverrouiller.

• Sélectionner toutes les cellules du classeur en cliquant sur l’angle entre la colonne A et la ligne 1 
• Cliquer sur le bouton droit de la souris et choisir Format de cellule ou utiliser le raccourci clavier Ctrl + 1 
• Dans la boite de dialogue, dans l’onglet Protection, décocher Verrouillée et Masquée et cliquer sur OK 

2. Sélectionner uniquement les cellules qui ont des formules :

•  Cliquer sur une cellule quelconque dans la feuille
• Appuyer sur le raccourci clavier Ctrl+T
• Dans la boite de dialogue «Sélectionner les cellules », choisir Formules et OK 

3. Verrouiller et masquer les formules

Maintenant que les formules sont sélectionnées, on va masquer.

• Juste après être quitté de la boite de dialogue, cliquer avec le bouton droit sur l’une des cellules sélectionnées (une cellule qui contient une formule)
• Choisir Format de cellule
• Dans l’onglet Protection cocher Verrouiller (pour empêcher l’accès) et Masquée (pour masquer la formule) 
• Valider 

4. Protéger la feuille 

La protection de la feuille aura un impact uniquement sur les cellules verrouillées :

• Dans le ruban, Révision/Modifications/Protéger la feuille
• Dans la boite de dialogue, cliquer sur OK, si vous ne voulez pas de mot de passe sinon insérer un mot de passe et valider.