Calcul de série avec NA, VALEUR, Vide

[Foie4]

Bonjour à tous,

J'aimerais calculer par exemple des série groupées de "1" sur une ligne. Ce qui complique la donne c'est qu'il peut y avoir des résultats comme NA, VALEUR, des cellules vides, des zéros.

Pouvez vous m'aider ?

Merci

 

[Chris24]

Bonjour

A tester

=SOMME(SI(ESTERREUR(F4:Z4);0;F4:Z4))

Formule matricielle à valider par Ctrl+Maj+Entrer

 

[Foie4]

Bonsoir le forum, Chris24,
Je ne sais pas si j'ai bien expliqué ma demande : je ne veux pas compter le nombre de "1" mais indiquer combien de "1" sont à la suite
(4 puis 3 puis 2 puis 1 puis 1).
Merci

 

[Foie4]

Bonjour le forum,
Je me permet de relancer le sujet car j'ai essayé de mon côté de longue heure mais je ne trouve pas.
merci de votre aide

 

[JHA]

Bonjour à tous,

Un essai par formule avec une ligne supplémentaire. Les lignes 5 ou 6 peuvent être masquées.
Un essai avec agregat et un essai avec somme.si

JHA

 

[Foie4]

Bonsoir JHA,

C'est parfait, ça marche super.
merci

 

[job75]

Bonjour Foie4, Chris24, JHA, le forum,

On peut utiliser cette fonction VBA, à placer dans un module standard :

Function CompteSerie(v, r As Range)
Dim flag As Boolean, n&, a&()
v = CStr(v)
For Each r In r
    If CStr(r) = v Then
        If Not flag Then flag = True: n = n + 1: ReDim Preserve a(1 To n)
        a(n) = a(n) + 1
    Else
        flag = False
    End If
Next
CompteSerie = a 'vecteur ligne (ou valeur d'erreur si n = 0)
End Function

Formule en F7 à tirer vers la droite :

=SIERREUR(GRANDE.VALEUR(CompteSerie($G1;$F4:$Z4);COLONNES($F7:F7));"")

Fichier .xlsm joint.

Edit : fichier (2) pour faire la même chose qu'au post #11 (affichage dans l'ordre de survenance).

Joyeuses fêtes de Pâques.

 

[job75]

Re,

Une solution par formule matricielle sans ligne auxiliaire :

=SIERREUR(GRANDE.VALEUR(SIERREUR(GRANDE.VALEUR(ColFin;COLONNE($F4:$Z4)-5)-GRANDE.VALEUR(ColDeb;COLONNE($F4:$Z4)-5);"");COLONNES($F7:F7));"")

Nota : la valeur cible en G1 ne doit pas être une valeur d'erreur alors que c'est possible avec le VBA.

Fichier xlsx joint.

A+

 

[job75]

Re,

Il paraît plus astucieux de présenter les résultats dans l'ordre de survenance des séries.

La formule en F7 est bien plus simple et n'est pas matricielle :

=SIERREUR(PETITE.VALEUR(ColFin;COLONNES($F7:F7))-PETITE.VALEUR(ColDeb;COLONNES($F7:F7));"")

Fichier (2).

Si l'on veut étudier les séries de cellules vides il faut ajouter des valeurs d'erreur en E4 et AA4, fichier (2 bis).

A+

 

[Foie4]

Bonjour job75,
Pas mal l'idée de classer les séries dans l'ordre de leur survenue. Je pense que je vais utiliser cette réponse.
Merci beaucoup.

 

[job75]

Bonjour Foie4, le forum,

Dans ce fichier (3) les séries de valeurs d'erreur sont aussi traitées grâce à la fonction TYPE.ERREUR.

En E4 et AA4 il faut mettre un caractère qu'on n'utilisera jamais en G1, par exemple l'espace insécable CAR(160).

En parallèle j'ai ajouté un fichier (2) au post #7.

Edit : j'ai remplacé ""&xxx=""&yyy par EXACT(xxx;yyy) ainsi la casse est respectée et 0 et vide sont distingués.

A+

 

[job75]

Bonjour le forum,

Chez moi sur Win 10 - Excel 2013 la solution par formules est 3 fois plus rapide :

- fichier (2) du post #7 => recalcul en 2,4 millisecondes

- fichier (3) du post #11 => recalcul en 0,8 milliseconde.

Bonne journée.

 

[job75]

Re,

En VBA on peut aller bien plus vite.

Il faut d'abord modifier la fonction :

Function CompteSerie(v, r As Range)
Dim a(), flag As Boolean, n&
v = CStr(v)
ReDim a(1 To r.Count)
For Each r In r
    If CStr(r) = v Then
        If Not flag Then flag = True: n = n + 1
        a(n) = a(n) + 1 'nombre
    Else
        flag = False
    End If
Next
For n = n + 1 To UBound(a): a(n) = "": Next 'texte vide
CompteSerie = a 'vecteur ligne
End Function

Ensuite dans la feuille de calcul sélectionner la plage F7:Z7, entrer =CompteSerie($G1;$F4:$Z4) dans la barre de formule et valider en bloc matriciellement par Ctrl+Maj+Entrée.

Le recalcul se fait maintenant en 0,4 milliseconde, c'est donc 2 fois plus rapide que par formules.

Fichier (3).

Ce problème était complexe, vaste et intéressant, je mets un "J'aime" au post #1.

A+

 

[Foie4]

Bonsoir job75,
J'ai bien étudié toutes vos propositions et je vous en remercie. Il est plus facile pour moi d'utiliser les fichiers sans VBA.
J'ai essayé d'adapter vos formules (de AA3 à AF3) pour une partie de mon dossier d'origine avec plusieurs lignes à calculer et je n'y arrive pas.
Je voulais faire comme vous : classer les "séries" de 1 du plus grand au plus petit et aussi dans l'ordre de leur survenue. Sans résultat,
malheureusement pour moi.
Merci

 

[job75]

Re,

Il est plus facile pour moi d'utiliser les fichiers sans VBA.

Ah bon ? La solution VBA est pourtant beaucoup plus facile à comprendre et à mettre en œuvre !

Par formules il faut appliquer les méthodes des posts #8 et #9, ci-joint votre fichier (nouveau nom pour ne pas confondre).

Pas besoin d'une cellule Cible puisque vous traitez toujours des séries de 1.

Attention pour définir les noms ColDeb et ColFin : les références de lignes sont relatives (pas de signe $) pour que ces noms puissent être utilisés sur toutes les lignes.

Bonne nuit.