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

 

[job75]

Bonjour Foie4, le forum,

Le fichier précédent se recalcule en 8,0 millisecondes (solution par formules).

Avec la méthode du post #13 le fichier joint se recalcule en 6,6 millisecondes (solution VBA).

Le code de la fonction utilise la macro de tri Quick sort :

Function CompteSerie(v, r As Range, Optional classer As Boolean)
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
If classer Then If n > 1 Then tri a, 1, n
For n = n + 1 To UBound(a): a(n) = "": Next 'texte vide
CompteSerie = a 'vecteur ligne
End Function

Sub tri(a, gauc, droi)  ' Quick sort
Dim ref, g, d, temp
ref = a((gauc + droi) \ 2)
g = gauc: d = droi
Do
    Do While a(g) > ref: g = g + 1: Loop
    Do While ref > a(d): d = d - 1: Loop
    If g <= d Then
      temp = a(g): a(g) = a(d): a(d) = temp
      g = g + 1: d = d - 1
    End If
Loop While g <= d
If g < droi Then Call tri(a, g, droi)
If gauc < d Then Call tri(a, gauc, d)
End Sub

Bonne journée.

 

[Foie4]

Bonsoir job75,
Le fichier final (1) m'intéresse mais je n'arrive pas à l'adapter avec un nouveau classeur.
Que faut il faire, je ne comprends pas ColDeb et ColFin...
Merci
Bonne soirée

 

[job75]

Re,

je ne comprends pas ColDeb et ColFin...

Eh oui pas facile quand on ne s'y connaît pas en formules, alors qu'avec la fonction VBA on ne se casse pas la tête.

J'ai donné toutes les explications nécessaires dans les fichiers déposés, je ne peux pas faire plus*.

Bonne fin de soirée.

*Edit : je précise quand même comment créer les noms ColDeb et ColFin :

- sélectionnez la cellule AA3 et allez dans le Gestionnaire de noms

- créez ColDeb avec la formule :

=SI(ESTERREUR(LN(Feuil1!$C3:$X3=1))*ESTNUM(LN(Feuil1!$D3:$Y3=1));COLONNE(Feuil1!$D3:$Y3))

- créez ColFin avec la formule :

=SI(ESTNUM(LN(Feuil1!$D3:$Y3=1))*ESTERREUR(LN(Feuil1!$E3:$Z3=1));COLONNE(Feuil1!$D3:$Y3)+1)

Comme je l'ai dit la référence de la ligne 3 est relative (pas de $).

 

[Foie4]

Bonjour job75,
Je ne veux pas utiliser de VBA car le fichier est complexe et avec d'autres formules VBA.
Pour l'explication je comprends mieux, et je pouvais chercher longtemps car je ne connaissais pas cette méthode.
Merci beaucoup je vais tester ça ce soir sur mon fichier.
Bonne journée

 

[job75]

Bonjour Foie4, le forum,

Je ne veux pas utiliser de VBA car le fichier est complexe et avec d'autres formules VBA.

J'espère que vous vous rendrez compte que ces propos sont contradictoires !

Bonne journée.

 

[job75]

Re,

Pour finir, optimisation de la fonction VBA pour aller plus vite :

Function CompteSerie(v, r As Range, Optional classer As Boolean)
'r doit être une plage d'une ligne et d'au moins 2 colonnes
Dim t, a%(), i%, flag As Boolean, n%
v = CStr(v)
t = r 'matrice, plus rapide
ReDim a(1 To UBound(t, 2))
For i = 1 To UBound(a)
    If CStr(t(1, i)) = v Then
        If Not flag Then flag = True: n = n + 1
        a(n) = a(n) + 1
    Else
        flag = False
    End If
Next
If classer Then If n > 1 Then tri a, 1, n
CompteSerie = a 'vecteur ligne
End Function

Fichier (2), le recalcul s'effectue en 3,1 millisecondes.

Les tableaux AA3:AF26 et AH3:AM26 sont au format nombre personnalisé 0;; pour masquer les valeurs zéro.

Edit : testé sur les tableaux recopiés sur 24 000 lignes => 2,4 secondes, c'est cohérent.

A+

 

[Foie4]

Bonjour job75,
J'ai testé vos propositions et ça marche, je vais les utiliser dans 2 dossiers différents.
Merci beaucoup pour le temps que vous m'avez accordé.
A bientôt
Bonne journée