Excel pour les null ne pas afficher les zeros (bwz blank when zero)

Soyez le premier à donner votre avis sur cette source.

Snippet vu 4 634 fois - Téléchargée 22 fois

Contenu du snippet

Memento sur les cellules a zéro.

Traitement des Cellules quand une formule de calcul conditionnée ou non retourne la valeur zéro.
Soit la formule retourne un zéro que l'on ne veux pas voir affiché
Soit la formule retourne une chaine vide qui perturbe les calculs.

Ci dessous différentes solutions aux problemes rencontrés.

Source / Exemple :


'
' ----------------------------------------------------------------------
' BWZ : Blank When Zéro  EXCEL POUR LES NULL  (Zéro est arrivé)
' ----------------------------------------------------------------------
' La fonction SI retourne une valeur de type numérique ou Alpha qui peut être gênante
'
' Syntaxe N°1 Exemple de condition qui retourne le nombre zéro
'    retourne la valeur numérique zéro avec les syntaxes suivantes (dans le cas ou A1 est égal a zéro ou estVide) 
'       A1 = 0
'       B1 = SI(A1<>0;"Pas nul";)
'       B1 = SI(A1<>0;"Pas nul";0)
'       B1 = SI(A1=0;;"Pas nul")
'       B1 = SI(A1=0;0;"Pas nul")
'    Avec cette syntaxe
'       C1 = B1 + 22      --> 22 sans problème
'       Mais la valeur 0 retournée en B1 n'est pas forcément souhaitée à l'affichage.
'       Pour ne pas afficher les zéros dans certaines cellules d'une feuille : 
'          utiliser un mask d'édition personnalisé avec la condition d'affichage [<>0] 
'          Format Cell Personnalisé :  [<>0]# ##0.00;""
'       Ou bien si aucune valeur à zéro n'est à afficher dans la feuille ou dans le classeur
'          Options Excel / Options avancées / Décocher la case "Afficher un zéro dans les cellules qui ont une valeur nulle"
'
' Syntaxe N°2 Exemple de condition qui retourne une chaine vide ""
'    retourne une chaine alpha vide avec les syntaxes suivantes (dans le cas ou A1 est égal a zéro ou estVide) 
'       A1 = 0
'       B1 = SI(A1<>0;"Pas nul";"")
'       B1 = SI(A1=0;"";"Pas nul")
'       Avec cette syntaxe
'          La valeur "" retournée est gênante quand la cellule entre dans une formule arithmétique 
'          C1 = B1 + 22         --> #VALEUR car la cellule B1 contient de l'alpha
'          Pour contourner le problème il faut faire
'          C1 = SOMME(B1) + 22  --> 22  
'          Il n'y a pas de 0 affiché puisque la cellule est de type texte et contient ""
' 
' NOTA 1 Le Mask d'affichage ne change ni le type ni la valeur des cellules.
' NOTA 2 Quelque soit la syntaxe utilisée, la cellule B1 n'est plus vide car elle contient soit 0 soit une chaine vide
'       et la fonction ESTVIDE(B1) retourne toujours FAUX car la cellule n'a plus le type vbEmpty
' NOTA 3 si on exporte le fichier au format .TXT ou .CSV les données sont exportées formatées comme elles sont affichées.

A voir également

Ajouter un commentaire

Commentaires

Cette source me fait penser à un problème que je n'ai jamais réussi à résoudre sans passer par des macros.
Je souhaite tracer dans un graphique (en "nuage de points") une courbe éventuellement discontinue, en ne reliant que les valeurs positives d'un tableau. Or une formule est toujours considérée comme une donnée dans les graphiques.
Ainsi si j'ai une série de valeurs dans la colonne A et une série de formules dans la colonne B de type : =SI(A1>0;A1;0) ou =SI(A1>0;A1;""), chaque fois que la cellule de la colonne A sera négative ou nulle, la courbe, tracée à partir de la colonne B, descendra à zéro, sans "sauter" la valeur qui ne m'intéresse pas et que je voudrais occulter. Seule une cellule vide permet d'interrompre une courbe.
Il me manque donc une formule du type =SI(A1>0;A1;estvide) ! Ce que je contourne grâce à une macro qui efface les cellules négatives mais cela prend beaucoup de temps...
Merci à tous ceux qui pourront me proposer des idées... à bientôt !
BILLOTmi
Messages postés
13
Date d'inscription
jeudi 27 novembre 2008
Statut
Membre
Dernière intervention
25 octobre 2018
-
Effectivement c'est un probleme dont je n'est pas non plus trouvé la solution sans passer par une macro.

il faudrait pouvoir affecter a une cellule le type vbEmpty quand le résultat est nul,
mais Une cellule recevant le résultat d'une formule de calcul
prend toujours le type numérique ou alpha quelque soit le type de la valeur retournée

J'avais essayé de faire fonction qui retourne un résultat de type vbEmpty dans une cellule
D1=ZeroIsEmpty()

Public Function ZeroIsEmpty()
Dim w As Variant
Debug.Print "w type "; VarType(w)
ZeroIsEmpty = w
Debug.Print "ZeroIsEmpty type "; VarType(ZeroIsEmpty)
End Function

bien que la fonction retourne le type vbEmpty.
le résultat de l'affectation dans la cellule est quand meme un zéro de type numerique.

Donc pas d'autre solution que de faire une macro
qui examine les cellules une a une et fait un clearcontents quand c'est zéro
Si votre macro dure longtemps il faut peut etre l'optimiser
et bloquer l'affichage écran ainsi que les recalcul durant l'opération
Application.ScreenUpdating = False ' Pas de rafraichissement écran
Application.Calculation = xlCalculationManual ' Pas de recalcul automatique
...
...
Application.Calculation = xlCalculationAutomatic ' Recalcul automatique
Application.ScreenUpdating = True ' False ' Rafraichissement écran
Merci beaucoup pour cette prise en compte de ma question... même si vos conclusions sont identiques aux miennes.
J'ai effectivement essayé de limiter autant que possible les temps de calcul (via ScreenUpdating et Calculation), mais mon ambition sort sûrement un peu du domaine prévu d'Excel : je veux dessiner le globe terrestre (avec ses parallèles, ses méridiens, et surtout avec les continents principaux) comme en 3D, en précisant les coordonnées (latitude et longitude) du point que survolerait la station spatiale qui donnerait la même vue.
Cette première étape marche sans problème, elle fait appel à quelques belles formules trigonométriques qui permettent de passer de coordonnées sphériques (latitude et longitude) en coordonnées cartésiennes (l'abscisse et l'ordonnée dans le plan de l'écran, et la hauteur qui est soit positive quand le point dessiné est en avant plan de l'écran, il faut donc le dessiner, soit négative quand le point est en arrière plan, il ne faut pas le dessiner : d'où mon problème).
Cela se complique lorsque je souhaite faire défiler le globe. Dans ce cas, le calcul des formules et le rinçage des cellules correspondant aux points à effacer (près de 1000) prend de l'ordre d'une seconde, ce qui fait un défilement trop saccadé.
Mais bon, rien n'est vital...
Encore une fois, mille mercis pour l'intérêt que vous m'avez témoigné... j'en suis très touché.
Très bonne soirée...
BILLOTmi
Messages postés
13
Date d'inscription
jeudi 27 novembre 2008
Statut
Membre
Dernière intervention
25 octobre 2018
-
Bonjour, c'est un bel exemple de la puissance d'excel dont en général on n'utilise qu'une tres faible partie des possibilités.
Je constate que vous maitrisez pas mal le sujet, c'est déja tres bien d'avoir réussi à afficher le globe vu de la station spatiale.
Le seule optimisation supplémentaire que je vois c'est de ne pas charger les cellules par des formules excel mais de calculer la valeur des cellules avec VBA qui peut charger le resultat d'un calcul fusse t'il trigonométrique ou mettre une cellule a Blank.
Mais malgré cette optimisation, on sera tres loin des 25 images par seconde surtout qu'il faut ajouter le temps de calcul du graphique.
Meme les outils d'animation 3D n'y arrivent pas, ils calculent des images qui sont stockées pour etre ensuite affichées a la vitesse voulue.
Merci encore pour votre intérêt... très bonne continuation !

Vous n'êtes pas encore membre ?

inscrivez-vous, c'est gratuit et ça prend moins d'une minute !

Les membres obtiennent plus de réponses que les utilisateurs anonymes.

Le fait d'être membre vous permet d'avoir un suivi détaillé de vos demandes et codes sources.

Le fait d'être membre vous permet d'avoir des options supplémentaires.