Note utilisateur: 5 / 5

Etoiles activesEtoiles activesEtoiles activesEtoiles activesEtoiles actives
 

 

 

S'il y a bien quelque chose de moche dans un tableau Excel, c'est l'erreur #DIV/0!

Cette erreur s'affiche lorsqu'un nombre est divisé par "0". Outre l'aspect peu esthétique, et pas franchement pro, cette erreur peut vous ennuyer pour réaliser des sommes ou des graphiques.

Voici comment y remédier

 

L'erreur #DIV!0! : inesthétique, mais pas que !

J'ai un tableau très simple qui me permet de contrôler ma consommation électrique par jour :

  • dans la colonne A, j'indique le début de la période qui m'est facturée par EDF, puis la fin de la période en colonne B
  • dans la colonne C, la formule =SOMME(B2-A2) me permet de calculer le nombre de jour compris dans cette période
  • dans la colonne D, j'indique le nombre de kilowatts consommés
  • dans la colonne E, la formule =SOMME(D2/C2) divise la consommation totale par le nombre de jour, et me donne donc la consommation par jour
  • dans la cellule E14, j'aimerai que la formule =MOYENNE(E2:E13) m'indique ma consommation journalière moyenne

 

Mais actuellement, la moyenne de ma consommation ne peut être calculée, car des erreur #DIV/0! sont présentes dans les cellules à partir desquelles je veut calculer une moyenne.

En effet, puisque je ne connais pas mes dates de factures à l'avance, je ne peux les renseigner dans le tableau et la formule divise donc une consommation à 0 avec 0 jours pour les mois où je n'ai pas encore reçu mes factures : ce qui créé l'erreur #DIV/0! :)

C'est là tout le problème de l'erreur #DIV/0! : en présence de cette erreur, impossible de faire une somme, une moyenne, et encore moins d'alimenter un graphique.

 

 

En finir avec l'erreur #DIV/0!

Voici un autre tableau de suivi EDF complet :

  • en colonne A et B, le début et la fin de période de facturation
  • en colonne C, le nombre de jour, calculé par la formule =SOMME(B4-A4)
  • en colonne D, le nombre de kW consommés en heures pleines
  • en colonne E, le nombre de kW consommés en heures creuses
  • en colonne F, le nombre total de kW consommés grâce à la formule =SOMME(D4+E4)
  • en colonne G, la consommation journalière, calculée grâce à la formule =SOMME(F4/C4)
  • en colonne I, le pourcentage de consommation en heures pleines =SOMME(D4/F4), avec une cellule au format pourcentage
  • en colonne J, le pourcentage de consommation en heures creuses =SOMME( E4/F4), avec une cellule au format pourcentage

 

 

On peut voir pourquoi l'erreur #DIV/0! apparaît : dans la cellule G10, ma formule divise en effet la consommation totale de 0, avec un nombre de jour de 0

 

 

Allez, on supprime les formules existantes : pour mettre un terme à l'erreur #DIV/0!, nous allons devoir utiliser une fonction

 

 

Placez-vous en G4, puis, dans le ruban, cliquez sur la liste déroulante de la somme automatique, et sélectionnez Autres fonctions...

 

 

Dans le champ de recherche, tapez "si" puis cliquez sur OK afin de trouver la fonction du même nom. Mettez la en surbrillance, puis cliquez sur le OK situé en bas de la fenêtre

 

 

Nous allons d'abord créer le test logique : lors de ce test, Excel vérifie si la condition est respectée ou non :

  • si elle est respectée, il appliquera ce que nous aurons déterminé dans le champ Valeur_si_vrai
  • si elle n'est pas respectée, il appliquera ce que nous aurons déterminé dans le champ Valeur_si_faux

Cliquez sur le petit sélecteur, situé à droite du champ Test_logique

 

 

L'idée est la suivante : si le nombre de jour est différent de 0 (autrement dit si nous n'avons bien des dates entrées en colonne A et B), nous indiquerons à la formule SI qu'il peut effectuer la division.

Si le nombre de jour n'est pas différent de 0 (si nous n'avons pas entré de dates dans les colonnes A et B), nous lui demanderont d'indiquer un simple "0", ce qui nous permettra :

  • de ne plus avoir l'erreur #DIV/o!
  • d'avoir un 0 à la place qui ne génera ni les moyennes, ni les sommes, ni les graphiques

 

Dans l'argument de la fonction, saisissez C4<>0, puis validez en appuyant sur la touche ENTREE de votre clavier

dans une fonction "<>" veut dire "différent"

 

 

Bien notre test logique est prêt.

Notez que, dans mon exemple, puisque des dates sont bien présentes dans les cellules A4 et B4, il nous dit que le test logique est vrai : le contenu de la cellule C4 est bien différent de 0

Cliquez sur le sélecteur situé à droite du champ de la Valeur_si_vrai

 

 

Lorsque le test logique est validé, nous réalisons la divisions que nous souhaitons faire : on l'entre donc simplement dans les arguments de la fonction : F4/C4

Validez ensuite avec la touche ENTREE de votre clavier

 

 

Passons à la Valeur_si_faux : pas besoin du sélecteur, saisissez simplement le nombre 0 dans le champ, puis cliquez sur le bouton Ok en bas de la fenêtre

 

 

On résume :

  • le test logique vérifie si la celule C4 est différente de 0
  • si c'est vrai, il réalise la division F4/C4
  • si c'est faux, il renvoie le chiffre 0

 

 

Il ne reste plus qu'à vous positionner en bas à droite de la cellule C4, puis à tirer afin de duppliquer votre fonction à toute la colonne C

Vous remarquez avec bonheur qu'il n'y a plus aucune erreur #DIV/0!

 

 

Je créé les mêmes fonctions dans mes colonnes I et J, afin de banir également les erreurs #DIV/0! de ces colonnes...

 

 

Et voici un tableau de pro, qui pourra être alimenté au fil de l'eau, sans erreurs #DIV/0!

 

 

Si je reprends mon petit tableau du départ et que je créé les formules SI selon le même principe, j'observe que ma moyenne se calcule correctement :

 

 

Conclusion

Bannir les erreurs #DIV/0!, outre l'aspect esthétique, permet de produire un tableau fini et sans messages d'erreurs qui peuvent dérouter les utilisateurs.

Procéder de cette manière permet de garantir que tous les calculs basés sur des cellules pouvant faire l'objet d'une #DIV/0! puissent toujours s'éxécuter.

 

1000 caractères restants


Gravatar
Héjja Etienne
Un grand merci c'est exactement ce que je cherchais pour résoudre ce problème.