XL 2016 Compter Le Nbre des Zéros Consecutifs à Condition > 9

Foufoudora

XLDnaute Occasionnel
Bonjour
Je cherche à compter le nombre des zéros consécutifs quand le nombre est supérieur à 9.
Si le nombre est Inférieur à 9 de rien faire.
Il faut que les Zéros soient consécutifs pour faire le calcul.
Voir fichier ci-joint.
Merci pour votre aide
Cordialement
 

Fichiers joints

Foufoudora

XLDnaute Occasionnel
Bonsoir
Merci pour ta réponse
Pour la Ligne 5 en AK5 je dois trouver 31 - 16(Zéro)= 15
Pour la ligne 6 je dois trouver rien car je n'ai pas des 9 Zéros consécutifs.
Bien Cordialement
 

mapomme

XLDnaute Barbatruc
Supporter XLD
Bonsoir à tous,

Je n'ai pas bien compris si les séquences de 0 doivent au moins avoir 9 zéros (comme le message #3 le laisserait croire) ou au moins 10 zéros (comme le message #1 le laisserait penser).
La formule du fichier joint considère que les séquences de zéros doivent avoir 10 zéros ou plus pour être pris en compte.

La formule est une formule matricielle à valider par la combinaison des 3 touches Ctrl+Maj+Entrée et non par la seule touche entrée.

La formule en AH5 à tirer vers le bas:
VB:
=NBVAL(B5:AF5)-SOMME(SI(FREQUENCE(SI(B5:AF5=0;COLONNE(B5:AF5));SI(B5:AF5<>0;COLONNE(B5:AF5)))>9;FREQUENCE(SI(B5:AF5=0;COLONNE(B5:AF5));SI(B5:AF5<>0;COLONNE(B5:AF5)));0))
nota: si les séquences de zéros à prendre en compter doivent comporter au moins 9 zéros (au lieu de 10 au moins), remplacer dans la formule >9 par >8.

 

Fichiers joints

Dernière édition:
  • J'aime
Reactions: JHA

Foufoudora

XLDnaute Occasionnel
Bonjour Mapomme et le forum
Je te remercie, et permettez-moi de te rendre compte plus tard car je ne peux pas vérifier actuellement la formule.
Bien cordialement
 

Boo2bafoot

XLDnaute Nouveau
Bonsoir Foufoudora et MaPomme,

J'apporte également ma contribution sur le fichier transmis par MaPomme.
Si j'en crois les explications de Foufoudora, en 2nde ligne, le résultat doit être rien ou "" car il n'y a pas 9 zéros consécutifs et du coup sur la 3ème ligne, ayant 9 zéros consécutifs sur les 9 premières cellules, le résultat devrait être 7 (les sept fois 1 présents sur la ligne).
Il s'agit d'une interprétation littérale, j'en conviens.
J'utilise ici une concaténation des cellules et je cherche les 9 zéros consécutifs via CHERCHE("000000000"), éventuellement à modifier si il s'agit de 10 zéros consécutifs à trouver. Si la fonction CHERCHE renvoie une erreur, alors on fait "rien"... Sinon, on somme les 1 présents sur la ligne.
Il ne s'agit ici pas d'une formule matricielle.

La formule en AH5, à tirer vers le bas également,
Code:
=SI(ESTERREUR(CHERCHE("000000000";B5&C5&D5&E5&F5&G5&H5&I5&J5&K5&L5&M5&N5&O5&P5&Q5&R5&S5&T5&U5&V5&W5&X5&Y5&Z5&AA5&AB5&AC5&AD5&AE5&AF5));"rien";SOMME(B5:AF5))
Dites nous en plus demain :)
Bonne soirée
 

Fichiers joints

job75

XLDnaute Barbatruc
Bonjour le forum,

Avec cette fonction VBA à placer dans un module standard (Alt+F11) :
Code:
Function MaxZerosConsecutifs(r As Range)
Dim n
For Each r In r
  If CStr(r) = "0" Then
    n = n + 1
    If n > MaxZerosConsecutifs Then MaxZerosConsecutifs = n
  Else
    n = 0
  End If
Next
End Function
Fichier joint.

Bonne journée.
 

Fichiers joints

CISCO

XLDnaute Barbatruc
Bonjour à tous, bonjour Job75, Mapomme, JHA, boo2bafoot

J'espérai trouver plus simple avec une formule, avec FREQUENCE peut-être... mais ce n'est pas le cas.

@ plus

P.S : Modification du fichier pour y ajouter quelques $ dans la définition des noms débutdes0 et findes0
 

Fichiers joints

Dernière édition:

CISCO

XLDnaute Barbatruc
Bonjour

Une solution en pièce jointe, avec une formule matricielle (à valider avec Ctrl+maj+entrer) plus courte.

@ plus

P.S1 : Dans mes premiers essais avec FREQUENCE, j'avais un mauvais résultat lorsqu'il y avait beaucoup de 1 consécutifs. J'ai résolu ce problème en les remplaçant par du texte (ici ""), qui n'est pas pris en compte par la fonction FREQUENCE.
P.S2 : Cf. dans les formules et la MFC s'il faut faire avec un > ou un >=.
P.S 3 : Cette méthode ne fonctionne pas si il y a un 0 tout seul dans les colonnes I, J, K... Méthode à modifier, donc.
 

Fichiers joints

Dernière édition:

Foufoudora

XLDnaute Occasionnel
Bonjour Le Fil et le Forum
D'abord un grand merci pour votre implication.
Désolé de vous répondre un peu tardivement (travail de nuit oblige :( ).
Mapomme : Quand j'ai des cases vides je devrais pas avoir des résultats négatifs.
Boo2bafoot : Quand je fais le calcul : AF4 - Nbre des Zéros consécutifs = 31 - 10 = 21. votre résultat est de 12.
Cisco : Quand j'ai des cases vides le calcul ne se fait correctement.
Job75 : permettez - moi d'avoir un peu de temps pour l'essayer.
Bien Cordialement
 

job75

XLDnaute Barbatruc
Re,

Une solution par formule matricielle en AP11 de ce fichier (2) :
Code:
=SI(OU(NON(NB.SI(DECALER(B11;;COLONNE(B11:AF11)-2;;10);"<>0")));SOMME(B11:AF11);"")
à valider par Ctrl+Maj+Entrée.

Attention, il faut 9 colonnes vides (masquées) avant la colonne AP pour éviter une référence circulaire.

Noter le 10 dans la formule car il faut au moins 10 zéros consécutifs, le titre du fil c'est [...] > 9.

A+
 

Fichiers joints

job75

XLDnaute Barbatruc
Re,

Les 9 colonnes vides à la suite du tableau ne sont pas indispensables, on peut utiliser cette formule :
Code:
=SI(OU(NON(NB.SI(DECALER(B11;;COLONNE(B11:W11)-2;;10);"<>0")));SOMME(B11:AF11);"")
qu'on peut compliquer ainsi (mais cela a-t-il un intérêt ?) :
Code:
=SI(OU(NON(NB.SI(DECALER(B11;;COLONNE(DECALER(B11;;;;COLONNES(B11:AF11)-9))-2;;10);"<>0")));SOMME(B11:AF11);"")
Fichier (2 bis).

A+
 

Fichiers joints

Foufoudora

XLDnaute Occasionnel
Bonjour le Fil et le Forum
Merci pour ta réponse Job75.
En faite, pour la fonction VBA et la formule matricielle, je dois avoir un résultat = 10 car j'ai un groupe de Zéros consécutifs matérialisé par des Zéros en rouge. Les Zéros en fond bleu ne devraient pas être comptabilisés. (voir Fichier joint)
Sauf si j'ai un deuxième groupe de 10 Zéros pour être comptabilisés.
Bien Cordialement
 

Fichiers joints

CISCO

XLDnaute Barbatruc
Bonjour

Bonjour Le Fil et le Forum
...
Mapomme : Quand j'ai des cases vides je devrais pas avoir des résultats négatifs.
...
Cisco : Quand j'ai des cases vides le calcul ne se fait pas correctement.
...
Bien Cordialement
Sans exemple avec des cellules vides dans ton fichier mis en pièce jointe, il était assez logique que nous ne traitions pas cette possibilité. Un test du style SI(B5:AF5=0 ne permettant pas de différencier les cellules vides des cellules contenant un 0, le résultat est parfois faux.

N'importe comment, la méthode que je proposais dans mon précédent post ne convient pas car elle plante* quand on met un 0 tout seul (donc précédé et suivi d'un 1) dans les colonnes I, J, K... J'essaye de la modifier en conséquence.

@ Plus

* Elle ramène un 2 car ce 0 est à la fois le début d'une série de 0 et la fin d'une série de 0 (là où je ne veux qu'un 1), et fait ensuite le produit 2 * n° donné dans la ligne 4 alors qu'il me faut le produit 1 * n° donné dans la ligne 4.
 
Dernière édition:

job75

XLDnaute Barbatruc
Bonjour à tous,
En faite, pour la fonction VBA et la formule matricielle, je dois avoir un résultat = 10
Vous n'êtes guère cohérent Foufoudora car dans le fichier du post #1 vous indiquez comme résultat 15, c'est à dire le nombre de 1.

Maintenant si vous voulez afficher le nombre maximum de zéros consécutifs je vais revoir la question.

Par VBA c'est bien sûr très facile mais par formule Excel ce sera une formule très lourde !!!

A+
 

Foufoudora

XLDnaute Occasionnel
Bonjour Le Fil et le Forum
D'abord je tiens à présenter mes excuses pour le manque des explications et l'incohérence des souhaits.
Mais c'est en essayant d'appliquer et utiliser le fichier au fur et à mesure que les besoins et la perfection se dessinent.
Toute proposition VBA ou autre sera la bienvenue.
Encore désolé.
Bien Cordialement
 

CISCO

XLDnaute Barbatruc
Rebonjour à tous, bonjour Job75, boo2bafoot, Mapomme, Calvus

Pour différencier les cellules vides des cellules contenant un zéro, j'ai mis le test SI(""&B5:AF5="0" au lieu de SI(B5:AF5=0. Il m'a aussi fallu rajouter un ou deux autres tests pour prendre en compte d'autres cas particuliers (0 dans la première cellule, 0 seul après et avant un 1...). Cf. en pièce jointe.

@ plus

P.S : La formule dans la colonne AI est de la forme ((MAX(...)+1)>9)*(AF$4- (MAX(...)+1)). Donc toute la partie MAX(...)+1 est répétée deux fois à l'identique, ce qui fait que la formule est longue.
 

Fichiers joints

Dernière édition:

job75

XLDnaute Barbatruc
Re,

Donc voyez ce fichier (2) pour afficher le nombre maximum de zéros consécutifs (au lieu du nombre de 1).

Solution VBA

La fonction est modifiée :
Code:
Function MaxZerosConsecutifs(r As Range, nref%)
Dim n%, a(1 To 3)
For Each r In r
  If CStr(r) = "0" Then
    n = n + 1
    If n > a(1) Then a(1) = n: a(3) = r.Column 'a(3) dernière colonne des 0
  Else
    n = 0
  End If
Next
a(2) = a(3) - a(1) + 1 '1ère colonne des 0
If a(1) > nref Then MaxZerosConsecutifs = a Else MaxZerosConsecutifs = ""
End Function
Elle renvoie maintenant une matrice (vecteur ligne) ce qui permet de l'utiliser dans la MFC sur B5:AF6.

Solution par formule Excel

J'ai défini le nom maxi (B11 étant sélectionnée) par :
Code:
=MAX(SI(NON(NB.SI(DECALER(B11;;COLONNE(B11:AF11)-2;;TRANSPOSE(COLONNE(B11:AF11)-1));"<>0"));NB.SI(DECALER(B11;;COLONNE(B11:AF11)-2;;TRANSPOSE(COLONNE(B11:AF11)-1));"0")))
Avec cette solution il est impératif qu'il y ait au moins 30 colonnes vides (masquées) à droite du tableau.

Le calcul de la formule en B11 prend beaucoup de temps : 24 fois plus que le calcul en B5.

A+
 

Fichiers joints

pierrejean

XLDnaute Barbatruc
Bonjour à tous

Un essai avec une fonction personnalisée pour le décompte des 0 successifs
A partir du fichier d'origine et sans avoir suivi les divers posts
La formule est adaptable pour >9 ou >10 ou écrire le max de 0
 

Fichiers joints

Discussions similaires


Haut Bas