Macro pour supprimer les 0 et les 0 %

[christopher68]

Bonsoir,

Pour vous expliqué mon problème, sur une colonne de 20 cellules exemple sur le fichier joint J2:J21, dans celle-ci il y a des valeurs nulles 0% et d'autres valeurs supérieures a 0%, le but et de signaler par remplissage en rouge les 5 plus petites valeurs sans prendre en compte les valeurs nulles 0%. Idem pour les valeur 0 dans la colonne K2:K21
J’ai essayé la méthode Fichier/Options/Avancées et décocher l'option d'affichage à zéro mais malheureusement quand je fais une mise en forme conditionnelle/ règles des valeurs plus/moins élevées / les 10 valeurs les moins élevées / je coche 5 les valeurs moins élevées remplissage rouge, les cellules vides sont quand même en rouge.
Ci joint le fichier

Merci

Cordialement

 

[CHRIS1945]

Re : Macro pour supprimer les 0 et les 0 %

Bonsoir,
Ce n'est pas évident à réaliser.
En fait, je devrais savoir pourquoi tu as des 0 ?
Cela parait idiot, mais il n'y a pas de formule et il y a 0 donc soit quelqu'un à encoder 0 soit c'est une copie provenant d'un autre tableau.
Or, si on met vide ("") dans la cellule au lieu de 0, la mise en forme conditionnelle fonctionne : elle ne tient pas compte des vides et tu auras le résultat escompté (sauf que, comme il y a des doublons, le test est perturbé et ne met pas exactement le nombre d'éléments demandés ainsi en demandant les 5 plus petits, on obtient 8 éléments dont des doublons... On n'a donc pas vraiment les 5 chiffres les plus petits, mais, il est possible de réajuster en augmentant de 5 à 6 ou 7... je sais c'est pas terrible... mais cela marche.
Par contre, si tu obtiens ces 0 par copie d'un autre tableau, de deux chose l'une : ou tu peux empêcher que l'autre tableau génère des 0 et met plutôt un vide soit, si c'est impossible : tu dédoubles ton fichier actuel et tu remplis les cellules du 2ème tableau dans la colonne J par la formule : =si('A2'!J2=0;"";'A2'!J2) ainsi que dans la colonne k. pour toutes les autres cellules tu fais simplement par exemple dans cellule B2 tu mets la formules ='A2'!B2.
Et tu fais la mise en forme conditionnelle sur la copie.
Une fois mis en place, ce n'est pas lourd et tout sera automatique.

Pas terrible comme solution mais je ne vois rien d'autre. Peut être quelqu'un d'autre ...
Merci de me dire si cela t'a aidé.
A+
Chris

 

[jpb388]

Re : Macro pour supprimer les 0 et les 0 %

Bonjour à tous

cette macro n'est valable que si les données sont entrées une par une

 

[mapomme]

Re : Macro pour supprimer les 0 et les 0 %

Bonjour christopher68 et bienvenue sur XLD , CHRIS1945, jpb388,

Pas de macro! Un essai avec une MFC basée sur une formule unique.

La formule (si on l'applique à partir de la cellule J1):

=SIERREUR(1/J1>=GRANDE.VALEUR(SIERREUR(1/DECALER($A$1;(2+21*ENT((LIGNES($A$1:J1)-2)/21))-1;COLONNES($A$1:J1)-1;21;1);"");5);FAUX)

Les hypothèses sont:

• Le premier tableau commence à la ligne 2
  
• les tableaux se suivent avec un pas de 21 lignes

Procédure:

• copier la formule ci-dessus
• effacer les MFC des colonnes J à K
• sélectionner la zone J1 : K252
• appliquer une MFC par formule
• coller la formule & définir la mise en forme souhaitée
• valider

NB : il peut y avoir plus de cinq cellules colorées par la MFC par le jeu des ex æquo.

Rem : si, par exemple, vous désirez appliquer la MFC à la colonne H sur la zone H2:H252, il faudra remplacer dans la formule J1 par H2

 

[CHRIS1945]

Re : Macro pour supprimer les 0 et les 0 %

Bonjour mapomme,
Impressionnant ! Pourrais-tu expliquer la formule magique ?
Merci d'avance
A+
Chris

 

[christopher68]

Re : Macro pour supprimer les 0 et les 0 %

Bonjour,

Un grand merci pour vos réponses à CHRIS1945, jpb388 et mapomme.
J'ai trouvé mon bonheur avec la formule de mapomme.

Je vous souhaite un bon dimanche a tous.
Cordialement

 

[coline741]

Re : Macro pour supprimer les 0 et les 0 %

Bonjour christopher68

Cette petite macro xls4 va bien sous les conditions suivantes :

- suivant ton tableau tu sélectionnes J1:J252 ;

- tu lances CTRL+Maj+Q

Et voilà !

Cependant si la cellule J1 est égale à 0 ou 0% elle effacera tout !

Il suffit de le savoir. Ici dans J1 est porté "total"


sup_zero CTRL+Maj+Q

=LIGNES(SELECTION())
=POUR.CELLULE("cellulact";;FAUX)
=SI(CELLULE.ACTIVE()=0;EFFACER();"")
=SELECTIONNER(DECALER(CELLULE.ACTIVE();1;0))
=SUIVANT()
=RETOUR()

Voilà l'Ami, Bon Dimanche

 

[christopher68]

Re : Macro pour supprimer les 0 et les 0 %

Bonjour,
Merci coline741
Bon dimanche a toi aussi

 

[job75]

Re : Macro pour supprimer les 0 et les 0 %

Bonjour à tous,

On peut définir la MFC sur J2:J21 par :

=(J2>0)*(J2<=PETITE.VALEUR(SI(J$2:J$21>0;J$2:J$21);5))

Mais s'il peut y avoir moins de 5 valeurs > 0 dans la plage il faut les prendre toutes :

=(J2>0)*(J2<=PETITE.VALEUR(SI(J$2:J$21>0;J$2:J$21);MIN(5;NB(SI(J$2:J$21>0;J$2:J$21)))))

Pour faire la même chose sur la plage K2:K21, faire un Copier-Collage spécial-Format de J2:J21 et remettre la colonne K au format standard.

A+

 

[job75]

Re : Macro pour supprimer les 0 et les 0 %

Re,

Pour les 12 tableaux de la feuille :

- soit on crée 12 MFC avec l'une de mes formules précédentes en modifiant l'adresse de la plage

- soit on utilise la méthode du fichier joint avec les noms définis ligvide ligdeb ligfin plage1 plage2.

Notez que les tableaux peuvent avoir une hauteur différente de 21.

A+

 

[job75]

Re : Macro pour supprimer les 0 et les 0 %

Re,

Une solution VBA par double-clic dans une colonne :

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Application.Count(Target.EntireColumn) = 0 Then Exit Sub
Dim a As Range, t, i&, n As Byte, x
Cancel = True
Application.ScreenUpdating = False
With Target.EntireColumn
  .Cells(1) = IIf(.Cells(1), "", True)
  For Each a In .SpecialCells(xlCellTypeConstants, 1).Areas
    a.Interior.ColorIndex = xlNone 'RAZ
    If .Cells(1) Then
      t = a.Resize(a.Count + 1) 'au moins 2 valeurs
      For i = 1 To UBound(t) - 1
        If t(i, 1) <= 0 Then t(i, 1) = Empty
      Next i
      n = Application.Min(Application.Max(Application.Count(t), 1), 5)
      x = Application.Small(t, n)
      For i = 1 To UBound(t) - 1
        If t(i, 1) <> "" Then If t(i, 1) <= x Then a(i).Interior.ColorIndex = 44
      Next i
    End If
  Next a
End With
End Sub

Fichier joint.

Bonne fin de soirée.

 

[christopher68]

Re : Macro pour supprimer les 0 et les 0 %

Bonsoir un GRAND Merci pour vos reposes
Excellent forum
Bon weekend a tous