Microsoft 365 Compter nombre de lignes utilisées

edgy63

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
 
Trier par date Trier par votes
AtTheOne

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: 4
Vote positif 0 Vote négatif
chris

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))
 
Vote positif 0 Vote négatif
edgy63

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
Vote positif 0 Vote négatif
AtTheOne

AtTheOne

XLDnaute Accro
Supporter XLD
re,
edgy63 à dit:
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.
Cliquez pour agrandir...
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
 
Vote positif 0 Vote négatif
AtTheOne

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
Vote positif 0 Vote négatif
edgy63

edgy63

XLDnaute Nouveau
mapomme à dit:
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 ;),
Cliquez pour agrandir...

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é.

AtTheOne à dit:
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
Cliquez pour agrandir...

Merci, c'est très intéressant. Un peu compliqué pour moi mais je vais regarder ça
 
Vote positif 0 Vote négatif
AtTheOne

AtTheOne

XLDnaute Accro
Supporter XLD
bonjour à toutes & à tous, bonjour @edgy63
edgy63 à dit:
Un peu compliqué pour moi mais je vais regarder ça
Cliquez pour agrandir...
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:
Vote positif 0 Vote négatif
edgy63

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
Vote positif 0 Vote négatif
job75

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:
Vote positif 0 Vote négatif

Discussions similaires

L
  • Question
Microsoft 365 compter des lignes sélectionner a la souris
Réponses
10
Affichages
421
LEPATOCHE
L
Sellig
  • Question
XL 2019 Envoyer mails, n’envoie pas toutes les lignes
2
Réponses
21
Affichages
555
Phil69970
Phil69970
Akortys
  • Question
Microsoft 365 RESOLU - Macro - Recherche de lignes identiques et ressortir les lignes uniques
Réponses
3
Affichages
419
AtTheOne
AtTheOne
S
  • Résolu(e)
XL 2010 compter nombre d'occurence dans une liste
Réponses
3
Affichages
245
Sirberthoult
S
N
  • Question
XL 2019 insertion de lignes et sous-total
Réponses
2
Affichages
173
nasser
N
C
  • Résolu(e)
XL 2019 Remplacement de valeurs tres long avec grosse base de données
2
Réponses
19
Affichages
448
carlos
C
C
  • Résolu(e)
Power Query POWER QUERY Supprimer des lignes suivant critères
Réponses
3
Affichages
249
alexga78
A
F
  • Question
XL 2016 Compter les valeurs uniques dans une liste
Réponses
3
Affichages
308
F22Raptor
F
K
  • Résolu(e)
Microsoft 365 Création d'une occurrence avec lignes vides
Réponses
8
Affichages
579
AtTheOne
AtTheOne
D
  • Question
Microsoft 365 Épurer les lignes vides d'une cellule puis fractionner la cellule en plusieurs lignes
2
Réponses
20
Affichages
617
fanch55
fanch55

Membres actuellement en ligne

6 membres et 55 invités.

Partager cette page

Tutos vidéos

Power Query : Ajouter des requêtes (Excel et Power BI)
Créez votre première fonction LAMBDA dans Excel en 3 étapes!
LAMBDA, l’arme secrète de Microsoft pour Excel !
Office Insider - Découvrez les nouveautés Excel avant tout le monde

Statistiques des forums

Discussions
313 769
Messages
2 102 234
Membres
108 181
dernier inscrit
Chr1sD