Microsoft 365 Compter nombre de lignes utilisées

[edgy63]

Bonjour à tous,

Je cherche à compter le nombre de lignes max utilisées sur plusieurs colonnes.
Par exemple si on a 2 colonnes :
Dans la colonne A, seules les lignes 1, 2 et 3 sont utilisées
Dans la colonne B, seules les lignes 3, 4 et 6 sont utilisées.

Je cherche une formule qui permette de trouver en résultat qu'au total 5 lignes ont été utilisés (les lignes 1, 2, 3, 4 et 6; la ligne 5 est vide)
Merci

 

[AtTheOne]

Bonjour à toutes & à tous, bonjour @edgy63
avec un tableau structuré ("tb_1", c'est facultatif mais ainsi on cerne les lignes à tester)
la formule

=SOMMEPROD(--(tb_1[Col1]&tb_1[Col2]<>""))

te renvoie le nombre de lignes contenant une valeur en Col1 et/ou en Col2
Voir fichier joint
À bientôt

 

[chris]

Bonjour

=LET(Li;SI(A:B<>"";LIGNE(A:B);0);Nb;MAX(Li);R;UNIQUE(ASSEMB.V(PRENDRE(Li;Nb;1);PRENDRE(Li;Nb;-1)));NBVAL(FILTRE(R;R>0)))

ou si on veut les numéros de ligne utilisés

=LET(Li;SI(A:B<>"";LIGNE(A:B);0);Nb;MAX(Li);R;UNIQUE(ASSEMB.V(PRENDRE(Li;Nb;1);PRENDRE(Li;Nb;-1)));FILTRE(R;R>0))

 

[mapomme]

Bonjour à tous ,

Une autre pour le nombre de lignes non vides : :
=SOMMEPROD(N((((A1:A999<>"")+(B1:B999<>""))>0)))

Pour les numéros de ligne (O3665) :
=LET(Y;(((A1:A999<>"")+(B1:B999<>""))>0)*LIGNE(A1:A999);FILTRE(Y;Y>0))

 

[edgy63]

Super, merci pour vos réponses. Très bien cette formule SOMMEPROD.
Je préfère la version sans tableau parce que les résultats vont arriver par une macro et le nombre de ligne sera différent à chaque fois.
Maintenant j'aimerai associer cette formule à la formule NB.SI.ENS (ou autre si je me trompe) mais je ne sais pas comment faire.
Je vous joint un fichier à partir du 1er exemple pour montrer ce que j'aimerai.
C'est à dire compter le nombre de ligne utilisées lorsque les valeurs dans une autres colonnes sont égales à X.
Merci encore

 

[AtTheOne]

re,

Je préfère la version sans tableau parce que les résultats vont arriver par une macro et le nombre de ligne sera différent à chaque fois.

Cela n'est pas un problème, la macro peut écrire dans le tableau, on le vide puis on colle les valeurs et il s'ajuste automatiquement, l'avantage c'est justement que l'on n'a plus à se soucier du nombre de lignes dans les formules.
Si tu as un exemple d'un ensemble de données à charger dans le tableau je veux bien te faire la macro qui va bien avec ...
À bientôt

 

[mapomme]

Re,

Voyez cette formule :
=SOMMEPROD(N(((A1:A999<>"")+(B1:B999<>""))*(C1:C999="X")>0))

nota : avec les dernières versions d'Excel (dont O365), la formule suivante suffit :
=SOMME(N(((A1:A999<>"")+(B1:B999<>""))*(C1:C999="X")>0))

Bonjour @chris , @AtTheOne ,

 

[AtTheOne]

Re,
Pour faire suite à mon précédent post (#6) voici en même temps que la réponse à ta question un exemple de macro pour importer des données dans un tableau structuré :

Une macro pour générer un nouveau jeu de données au hasard (entre 30 et 3000 lignes) :

Sub NouveauJeu()
     sh_NJD.Cells.Clear
     nbl = WorksheetFunction.RandBetween(30, 3000)
     ReDim tb(1 To nbl, 1 To 3)
     For i = 1 To nbl
          tb(i, 1) = WorksheetFunction.RandBetween(1, 3)
          If WorksheetFunction.RandBetween(1, 2) = 1 Then tb(i, 2) = "x"
          If WorksheetFunction.RandBetween(1, 2) = 1 Then tb(i, 3) = "x"
     Next
     sh_NJD.Cells(1).Resize(nbl, 3).Value = tb
End Sub

Une macro pour importer ce nouveau jeu dans le TS :

Sub Importer_Nouveau_Jeu()
     Dim tb()
     tb = sh_NJD.UsedRange.Value2
     With [Tb_1]
          .ClearContents
          .ListObject.Resize .ListObject.Range.Resize(UBound(tb, 1) + 1)
          [Tb_1].Value = tb
     End With
End Sub

J'ai supposé qu'il fallait remplacer les anciennes données mais c'est encore plus simple d'ajouter le nouveau jeu en fin de TS (pas besoin d'effacer les données ni de redimensionner le TS

Jette un œil sur le fichier joint (il y a un bouton sur la feuille "Nouveau jeu de données" et un bouton sur la feuille "Données"

À bientôt

 

[chris]

Bonjour à tous

Je préfère la version sans tableau

Aucune des formules ne se référait à un tableau...

EDIT : quand j'ai posté je en voyais pas lé réponse de AtTheOne

Je me doutais qu'on pouvais simplifier mes formules : bravo à mapomme

 

[edgy63]

Re,

Voyez cette formule :
=SOMMEPROD(N(((A1:A999<>"")+(B1:B999<>""))*(C1:C999="X")>0))

nota : avec les dernières versions d'Excel (dont O365), la formule suivante suffit :
=SOMME(N(((A1:A999<>"")+(B1:B999<>""))*(C1:C999="X")>0))

Bonjour @chris , @AtTheOne ,

Merci mais désolé je me suis mal exprimé. Quand je dis X il s'agissait en fait de valeurs différentes et qui peuvent contenir des lettres (1, 2, 2bis,3...) comme dans mon fichier en exemple. Je n'aurai pas dû dire X étant donné qu'il y a aussi des crois dans les case désolé.

Re,
Pour faire suite à mon précédent post (#6) voici en même temps que la réponse à ta question un exemple de macro pour importer des données dans un tableau structuré :

Une macro pour générer un nouveau jeu de données au hasard (entre 30 et 3000 lignes) :

Sub NouveauJeu()
     sh_NJD.Cells.Clear
     nbl = WorksheetFunction.RandBetween(30, 3000)
     ReDim tb(1 To nbl, 1 To 3)
     For i = 1 To nbl
          tb(i, 1) = WorksheetFunction.RandBetween(1, 3)
          If WorksheetFunction.RandBetween(1, 2) = 1 Then tb(i, 2) = "x"
          If WorksheetFunction.RandBetween(1, 2) = 1 Then tb(i, 3) = "x"
     Next
     sh_NJD.Cells(1).Resize(nbl, 3).Value = tb
End Sub

Une macro pour importer ce nouveau jeu dans le TS :

Sub Importer_Nouveau_Jeu()
     Dim tb()
     tb = sh_NJD.UsedRange.Value2
     With [Tb_1]
          .ClearContents
          .ListObject.Resize .ListObject.Range.Resize(UBound(tb, 1) + 1)
          [Tb_1].Value = tb
     End With
End Sub

J'ai supposé qu'il fallait remplacer les anciennes données mais c'est encore plus simple d'ajouter le nouveau jeu en fin de TS (pas besoin d'effacer les données ni de redimensionner le TS

Jette un œil sur le fichier joint (il y a un bouton sur la feuille "Nouveau jeu de données" et un bouton sur la feuille "Données"

À bientôt

Merci, c'est très intéressant. Un peu compliqué pour moi mais je vais regarder ça

 

[mapomme]

Re,

En L4 vous mettez la formule suivante puis vous la recopiez vers le bas :
=SOMMEPROD(N(((B1:B999<>"")+(C1:C999<>""))*(A1:A999=K4)>0))

 

[AtTheOne]

bonjour à toutes & à tous, bonjour @edgy63

Un peu compliqué pour moi mais je vais regarder ça

Du coup je commente un peu pour démystifier tout cela :

La première macro c'est juste pour générer un jeu de données aléatoires dans une feuille (qui ne contiendra rien que ces données).

J'ai fait cela car je ne connais pas la forme des données que tu récupères par macro.

Cette feuille a pour nom VBA "sh_NDJ" (c'est son nom d'objet modifié dans la fenêtre propriétés de l'éditeur VBA, pas son nom dans l'onglet)

Sub NouveauJeu()
     'On vide complétement la feuille
    sh_NJD.Cells.Clear

     'On tire au sort un nombre de lignes "nbl" (entre 30 et 3000)
    nbl = WorksheetFunction.RandBetween(30, 3000)
  
    'On dimensionne un tableau "tb" pour recueillir les données que l'on va générer :
    ReDim tb(1 To nbl, 1 To 3)                                     
  
    'On boucle sur toutes les lignes du tableau "tb"
    For i = 1 To nbl
        tb(i, 1) = WorksheetFunction.RandBetween(1, 3)                     'Dans 1ère col un chiffre entre 1 et 3
        If WorksheetFunction.RandBetween(1, 2) = 1 Then tb(i, 2) = "x"     'Dans la 2ème col un x ou rien
        If WorksheetFunction.RandBetween(1, 2) = 1 Then tb(i, 3) = "x"     'Dans la 3ème col un x ou rien
    Next

   'On colle les valeurs générées dans le tableau "tb" sur la feuille "sh_NJD" à partir de la première cellule (A1) sur "nbl" lignes et 3 colonnes
    sh_NJD.Cells(1).Resize(nbl, 3).Value = tb

'C'est tout !
End Sub

La seconde macro sert à importer un jeu de données contenu dans la feuille "sh_NJD" dans le tableau structuré nommé "Tb_1"

Sub Importer_Nouveau_Jeu()
     Dim tb()

     'On charge dans le tableau VB "tb" les données contenues dans la feuille "sh_NJD"
     tb = sh_NJD.UsedRange.Value2

   
     With [Tb_1]
          .ClearContents                    'On vide le tableau structuré "Tb_1" de ses données
          .ListObject.Resize .ListObject.Range.Resize(UBound(tb, 1) + 1) 'On le redimensionne pour recevoir les données du tableau VB "tb"
          [Tb_1].Value = tb 'On colle le contenu du tableau VB "tb" dans le tableau structuré "Tb_1"
     End With

'C'est tout !
End Sub

À bientôt

 

[edgy63]

Bonjour,
Parfait, merci @mapomme @AtTheOne, exactement ce que je voulais.

Il reste une dernière étape et c'est vrai que ce serait mieux de le faire avec un tableau et une macro.
Parce que je suis obligé de descendre jusqu'à 3000 pour être sur de tout avoir.

J'ai repris ton fichier auquel j'ai ajouté une formule pour savoir si les deux colonnes sont différentes (donc 0 ou 1) =SI(C2=B2;0;1)
et une autre formule pour compter le nombre de différences totales par valeurs =NB.SI.ENS($A$2:$A$3000;K4;Q$2:Q$3000;1)

Il doit y avoir un moyen de faire ceci en une seule étape ?

Enfin je divise ce nombre par le résultat de votre formule.
Ensuite il ne me restera plus qu'à modifier tout ça pour que ça fonctionne avec plusieurs colonnes en comparant à chaque fois 2 colonnes adjacentes

 

[job75]

Bonjour edgy63, le forum,

En ajoutant une 4ème colonne au tableau, formule en D2 :

=REPT("x";B2&C2<>"")

Et formule en I4 à tirer sur I4:K6 :

=NB.SI.ENS($A:$A;$H4;B:B;"><")

Je laisse à AtTheOne le soin de modifier le code VBA.

A+

 

[AtTheOne]

Bonjour à toutes & à tous, bonjour @edgy63, @job75

Je laisse à AtTheOne le soin de modifier le code VBA.

Je regarderai cela demain soir, je ne suis pas chez moi...
À bientôt