NB.SI et Plusieurs valeurs dans une même cellule

Résolu

Valeur

Happy79_2 Messages postés 73 Date d'inscription Statut Membre Dernière intervention -
Happy79_2 Messages postés 73 Date d'inscription Statut Membre Dernière intervention - 24 nov. 2014 à 20:21

Bonjour,

J'aimerais connaître le moyen de cumuler une valeur x dans une colonne dans laquelle une cellule peut contenir plus d'une valeur.

Exemple :
B2 : 1
B3 : 1,5, 7
B4 : 2,5
B5 : 1

ETC.

Comment puis-je connaître le nombre de 1 dans la colonne B (par exemple)?

=NB.SI(J4:J501;"1") serait la formule pour compter les élément contenant uniquement 1, mais je veux inclure tout ce qui contient 1.

Merci.

Afficher la suite

Répondre (12) Posez votre question

Répondre (12)

A voir également:

Excel plusieurs valeurs dans une cellule
Aller à la ligne dans une cellule excel - Guide
Excel cellule couleur si condition texte - Guide
Liste déroulante excel - Guide
Formule excel pour additionner plusieurs cellules - Guide
Proteger cellule excel - Guide

12 réponses

Réponse 1 / 12

ccm81 Messages postés 10909 Date d'inscription Statut Membre Dernière intervention 2 433

@Vaucluse et Michel
Cordiales salutations à tous les deux

... sauf si la liste de vos valeurs est en format texte et pas en format numérique, ce qui expliquerait par ailleurs le fonctionnement de NB.SI(...;"*..*")

Si le contenu de la cellule est du genre 1,10,11,20,30 elle est forcément au format texte non?

Réponse 2 / 12

Vaucluse Messages postés 26496 Date d'inscription Statut Contributeur Dernière intervention 6 438

Exact CCM,(bonsoir)

On pourrait imaginer si il n'y a pas de risque de devoir compter plusieurs 1 dans la, même cellule:

=NB.SI(champ;1)+NB.SI(Champ;"*1,*")+NB.SI(Champ;"*,1")

qui totalisera le nombre de cellules avec 1 isolé + les 1 suivi d'un virgule + un éventuel 1 final précédé d'une virgule
Mais ne comptera pas le 1, s'il se présente plusieurs fois dans une cellule

donc en fait en si il y a risque de voir plusieurs fois 1, dans quelques textes, je crois qu'il n'y a que VBA pour résoudre le problème.

Bonne soirée
bien cordialement

Errare humanum est, perseverare diabolicum

Happy79_2 Messages postés 73 Date d'inscription Statut Membre Dernière intervention 1

Je vais assayé ça! En effet ça semble très pertinent. Je vous reviens dès que j'ai du temps.

On est dans la bonne voie! :)

Happy79_2 Messages postés 73 Date d'inscription Statut Membre Dernière intervention 1 > Happy79_2 Messages postés 73 Date d'inscription Statut Membre Dernière intervention

C'est parfait! Il me restera à régler mon ajout de condition.

Préférez vous que je post une nouvelle question ou on règle ça ici?

Réponse 3 / 12

Happy79_2 Messages postés 73 Date d'inscription Statut Membre Dernière intervention 1

Bon, finalement je crois avoir trouver la réponse alors je mes ma formule pour le bénéfice des autres :

=NB.SI($J$4:$J$501;"*1*")+NB.SI($J$4:$J$501;"1")-NB.SI($J$4:$J$501;"*10*")

Le dernier sert à éliminer un élément qui contien 1, mais qui n'est pas un 1.

Toutefois, si vous avez quelque chose de plus efficace pour quelqu'un qui aurait plus de variable comme allant jusqu'à 100. Ne gênez-vous surtout pas!

Réponse 4 / 12

Mike-31 Messages postés 18405 Date d'inscription Statut Contributeur Dernière intervention 5 135

Bonsoir,

si j'ai bien compris, tu peux utiliser cette formule

=SOMMEPROD((NBCAR(J4:J501)-NBCAR(SUBSTITUE(J4:J501;1;""))))

Vous n’avez pas trouvé la réponse que vous recherchez ?

Posez votre question

Réponse 5 / 12

Happy79_2 Messages postés 73 Date d'inscription Statut Membre Dernière intervention 1

C'est presque ça!

Toutefois, cette formule n'exclut pas les nombres comme 10, 11, 21, 100, etc. qui contiennent 1.

Votre formule équivaut à =NB.SI($J$4:$J$501;"*1*")+NB.SI($J$4:$J$501;"1").

Y a t'il mieux?

Réponse 6 / 12

Vaucluse Messages postés 26496 Date d'inscription Statut Contributeur Dernière intervention 6 438

Bonjour

difficile de s'y retrouver dans votre et vos solutions

NB.SI(Champ;"*1*") ne fonctionne que si les valeurs sont textes et pas numériques, sauf erreur de ma part dans mes tests
et ensuite vous voulez compter les nombres qui débutent par 1 mais en éliminant 10,100 ect...
s'il s'agit donc de compter uniquement des nombres entre 1 et 2 en incluant 1, mieux vaut:

=NB.SI.ENS(J4:J504;">=1";J4:J540;"<2")

crdlmnt

Happy79_2 Messages postés 73 Date d'inscription Statut Membre Dernière intervention 1

Est-ce que c'est formule convient pour Excel2003? (code d'erreur #NOM?)

Réponse 7 / 12

michel_m Messages postés 16602 Date d'inscription Statut Contributeur Dernière intervention 3 314

Bonjour
=SOMMEPROD((ENT(B2:B16)=1)*1)

Réponse 8 / 12

Happy79_2 Messages postés 73 Date d'inscription Statut Membre Dernière intervention 1

Désolé si ma solutions impliste vous mélange.

Je recommence en modifiant mon exemple :

Exemple :
j4 : 1
j5: 1,5, 7
j6 : 2,5
j7 : 10
j8 : 2, 10

Je veux compter le nombre de valeur «1» tout en excluant les autres nombres contenant «1» comme 10 ou 11 ou 100.

J'ai copier votre formule =NB.SI.ENS(J4:J504;">=1";J4:J540;"<2"), mais elle me fait le code d'erreur #NOM?
(et ce même si j'ai 540 au deux plages)

michel_m Messages postés 16602 Date d'inscription Statut Contributeur Dernière intervention 3 314

EXCUSEZ MOI d'avoir OSER proposer une solution

Happy79_2 Messages postés 73 Date d'inscription Statut Membre Dernière intervention 1 > michel_m Messages postés 16602 Date d'inscription Statut Contributeur Dernière intervention

Merci, mais elle ne convient pas. Vous n'avez pas à vous excuser.

Vaucluse Messages postés 26496 Date d'inscription Statut Contributeur Dernière intervention 6 438

Si la formule renvoie #NOM c'est que votre excel est antérieur à 2007, et dans ce cas la formule de Michel ne peut que fonctionner, sauf si votre question est encore mal posée (désolé).
Elle calcule le nombre de valeurs de votre tableau qui ont 1 comme valeur de l'entier, et ça c'est incontournable...
... sauf si la liste de vos valeurs est en format texte et pas en format numérique, ce qui expliquerait par ailleurs le fonctionnement de NB.SI(...;"*..*")
Si vous ne dites pas tout il n'y aura pas de solution.
Encore une tentative et je passe à autre chose:
=NB.SI(J4:J540;1)+NB.SI(J4:J540;"1,*")

Réponse 9 / 12

ccm81 Messages postés 10909 Date d'inscription Statut Membre Dernière intervention 2 433

Bonjour à tous

Peut être via vba
https://www.cjoint.com/?3Kyq0Kmc4FC

Cdlmnt

Réponse 10 / 12

Happy79_2 Messages postés 73 Date d'inscription Statut Membre Dernière intervention 1

Vous ne savez pas à quel point j'aimerais utiliser ce site, mais il est bloqué à mon travail...

Réponse 11 / 12

ccm81 Messages postés 10909 Date d'inscription Statut Membre Dernière intervention 2 433

Alt-F11 pour acceder à l'editeur vba
puis Insertion Module
Dans Module 1 copier ceci

Public Function NbOV(plage As Range, s As String) As Long
Dim n As Long, cel As Range, ts, k As Long
n = 0
For Each cel In plage
  ts = Split(cel.Value, ",")
  For k = 0 To UBound(ts)
    If ts(k) = s Then n = n + 1
  Next k
Next cel
NbOV = n
End Function

Dans la feuille la fonction
= NbOV(A1:A4;1) pour compter le nombre de 1 "purs" (séparés par une virgule du reste de la cellule) dans la plage A1:A4

Cdlmnt

Happy79_2 Messages postés 73 Date d'inscription Statut Membre Dernière intervention 1

J'y comprend rien en programmation, j'ai créé le module; j'ai copier la formule dedans; j,ai copié l'autre formule dans une cellule en modifiant la plage pour j4 à j559, soit =NbOV(j4:j559;1), mais j'ai une fois de plus un message d'erreur du type Erreur de compilation : Attendu : fin d'instruction.

ccm81 Messages postés 10909 Date d'inscription Statut Membre Dernière intervention 2 433

Erreur de compilation : Attendu : fin d'instruction.
il y a une erreur de syntaxe quelque part dans ton code
tu as du mal copier (fais un copier/coller ça sera plus sûr)

Mytå Messages postés 2973 Date d'inscription Statut Contributeur Dernière intervention 953

Salut le Forum

Si la saisie contient un espace, elle n'est pas compté.

Exemple 2, 1

Mytå

ccm81 Messages postés 10909 Date d'inscription Statut Membre Dernière intervention 2 433

Exact
correction ligne soulignée

For k = 0 To UBound(ts)
    If Trim(ts(k)) = s Then n = n + 1
Next k

Réponse 12 / 12

Happy79_2 Messages postés 73 Date d'inscription Statut Membre Dernière intervention 1

Bon raisonnement, mais mes cellules sont au format standard.

Pour ma part, je croyais que les ** signifiaient "Contient", mais j'en comprends mainetenant que, parce qu'il y a une virgule, il le prend comme du texte et exclut les valeurs numériques comme 1 (uniquement). C'est pourquoi il a fallut que je j'additionne "1" à *1*; pour ensuite soustraire 10.

=NB.SI($J$4:$J$501;"*1*")+NB.SI($J$4:$J$501;"1")-NB.SI($J$4:$J$501;"*10*")

Lorsque j'en aurai l'occasion je transmettrai un fichier à partir de chez-moi.

J'aurai à ce moment une question plus complexe, c'est à dire àjouter une condition au calcul.

En pssant, merci pour le temps qui vous passez à essayer de trouver cette mozus de formule.

Répondre Posez votre question

Votre réponse

Discussions similaires

Modifier un filtre automatiquement selon la valeur d'une cellule

valou_ne -
Gyrus -

3 réponses

Excel:fonction "si" cellule vide => ... / si renseignée => ...

Cmoi -
Cmoi -

3 réponses

Donner une valeur a une lettre

titeufv1 -
Mike-31 -

5 réponses

Afficher remplir une colonne en fonction d'une autre colonne

painghou -
redaiwa -

3 réponses

variation entre deux valeur

tatarita02 -
tatarita02 -

3 réponses

valeur comprise entre A et B

michel -
PapyLuc51 -

3 réponses

Devenez membre en quelques clics

Connectez-vous simplement avec ceux qui partagent vos intérêts
Suivez vos discussions facilement et obtenez plus de réponses
Mettez en avant votre expertise et aidez les autres membres
Profitez de nombreuses fonctionnalités supplémentaires en vous inscrivant

S'inscrire