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
 

Pièces jointes

  • Nombre de Zéro.xlsx
    8.8 KB · Affichages: 53

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.

 

Pièces jointes

  • Foufoudora- Nombre de Zéro- v1.xlsx
    8.9 KB · Affichages: 49
Dernière édition:

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
 

Pièces jointes

  • Foufoudora- Nombre de Zéro- v2.xlsx
    9.1 KB · Affichages: 39

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.
 

Pièces jointes

  • MaxZerosConsecutifs(1).xlsm
    20.8 KB · Affichages: 45

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
 

Pièces jointes

  • Nombre de Zéro.xlsx
    15.1 KB · Affichages: 41
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.
 

Pièces jointes

  • Nombre de Zéro.xlsx
    15.6 KB · Affichages: 33
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+
 

Pièces jointes

  • MaxZerosConsecutifs(2).xlsm
    22.2 KB · Affichages: 51

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+
 

Pièces jointes

  • MaxZerosConsecutifs(2 bis).xlsm
    22.3 KB · Affichages: 40

job75

XLDnaute Barbatruc
Re,

Pour ceux qui sont motivés, on peut mesurer la durée du calcul de chaque formule.

Dans le fichier joint double-clic sur AH5 AH11 AJ11.

La macro je l'ai créée en 2010.

A+
 

Pièces jointes

  • MaxZerosConsecutifs Durée du calcul(1).xlsm
    25.1 KB · Affichages: 44

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
 

Pièces jointes

  • MaxZerosConsecutifs(2 bis) .xlsm
    16.1 KB · Affichages: 40

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:

Discussions similaires

Statistiques des forums

Discussions
311 705
Messages
2 081 733
Membres
101 807
dernier inscrit
foued