Calcul de série avec NA, VALEUR, Vide

Foie4

XLDnaute Junior
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
 

Pièces jointes

  • Calcul série avec NA, VALEUR, Vide.xlsx
    10 KB · Affichages: 28

JHA

XLDnaute Barbatruc
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
 

Pièces jointes

  • Calcul série avec NA, VALEUR, Vide.xlsx
    11.7 KB · Affichages: 45
Dernière édition:

job75

XLDnaute Barbatruc
Bonjour Foie4, Chris24, JHA, le forum,

On peut utiliser cette fonction VBA, à placer dans un module standard :
Code:
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 :
Code:
=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.
 

Pièces jointes

  • Calcul série avec NA, VALEUR, Vide(1).xlsm
    23.7 KB · Affichages: 37
  • Calcul série avec NA, VALEUR, Vide(2).xlsm
    23.8 KB · Affichages: 26
Dernière édition:

job75

XLDnaute Barbatruc
Re,

Une solution par formule matricielle sans ligne auxiliaire :
Code:
=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+
 

Pièces jointes

  • Calcul série avec NA, VALEUR, Vide(1).xlsx
    17.7 KB · Affichages: 29

job75

XLDnaute Barbatruc
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 :
Code:
=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+
 

Pièces jointes

  • Calcul série avec NA, VALEUR, Vide(2).xlsx
    17.5 KB · Affichages: 33
  • Calcul série avec NA, VALEUR, Vide(2 bis).xlsx
    17.7 KB · Affichages: 38

job75

XLDnaute Barbatruc
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+
 

Pièces jointes

  • Calcul série avec NA, VALEUR, Vide(3).xlsx
    17.7 KB · Affichages: 35
Dernière édition:

job75

XLDnaute Barbatruc
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

XLDnaute Barbatruc
Re,

En VBA on peut aller bien plus vite.

Il faut d'abord modifier la fonction :
Code:
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+
 

Pièces jointes

  • Calcul série avec NA, VALEUR, Vide(3).xlsm
    23.9 KB · Affichages: 28

Foie4

XLDnaute Junior
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
 

Pièces jointes

  • Calcul série avec NA, VALEUR, Vide(3).xlsx
    15 KB · Affichages: 29

job75

XLDnaute Barbatruc
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.
 

Pièces jointes

  • Fichier final(1).xlsx
    25.6 KB · Affichages: 28

Discussions similaires

Réponses
4
Affichages
411
Réponses
7
Affichages
566

Membres actuellement en ligne

Statistiques des forums

Discussions
314 450
Messages
2 109 726
Membres
110 552
dernier inscrit
jasson