Microsoft 365 Compter nombre de lignes utilisées

edgy63

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

XLDnaute Accro
Supporter XLD
Bonjour à toutes & à tous, bonjour @edgy63
avec un tableau structuré ("tb_1", c'est facultatif mais ainsi on cerne les lignes à tester)
la formule
VB:
=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
 

Pièces jointes

  • Compter nombre de lignes utilisées.xlsx
    10.6 KB · Affichages: 5

chris

XLDnaute Barbatruc
Bonjour
VB:
=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
Code:
=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))
 

edgy63

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

Pièces jointes

  • Compter nombre de lignes utilisées.xlsx
    10.4 KB · Affichages: 5

AtTheOne

XLDnaute Accro
Supporter XLD
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
 

AtTheOne

XLDnaute Accro
Supporter XLD
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) :
VB:
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 :
VB:
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
 

Pièces jointes

  • Compter nombre de lignes utilisées b.xlsm
    48.6 KB · Affichages: 4

edgy63

XLDnaute Nouveau
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) :
VB:
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 :
VB:
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
 

AtTheOne

XLDnaute Accro
Supporter XLD
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)
Enrichi (BBcode):
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"

Enrichi (BBcode):
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
 
Dernière édition:

edgy63

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

Pièces jointes

  • Compter nombre de lignes utilisées C.xlsm
    119.2 KB · Affichages: 2

job75

XLDnaute Barbatruc
Bonjour edgy63, le forum,

En ajoutant une 4ème colonne au tableau, formule en D2 :
Code:
=REPT("x";B2&C2<>"")
Et formule en I4 à tirer sur I4:K6 :
Code:
=NB.SI.ENS($A:$A;$H4;B:B;"><")
Je laisse à AtTheOne le soin de modifier le code VBA.

A+
 

Pièces jointes

  • Compter nombre de lignes utilisées C.xlsm
    143.4 KB · Affichages: 8
Dernière édition:

Discussions similaires

Membres actuellement en ligne

Aucun membre en ligne actuellement.

Statistiques des forums

Discussions
314 708
Messages
2 112 096
Membres
111 416
dernier inscrit
philipperoy83