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

Résolu/Fermé

Valeur

Happy79_2 Messages postés 73 Date d'inscription vendredi 19 septembre 2014 Statut Membre Dernière intervention 6 décembre 2023 - 21 nov. 2014 à 19:41
Happy79_2 Messages postés 73 Date d'inscription vendredi 19 septembre 2014 Statut Membre Dernière intervention 6 décembre 2023 - 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.

Posez votre question

A voir également:

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

12 réponses

Réponse 1 / 12

ccm81 Messages postés 10853 Date d'inscription lundi 18 octobre 2010 Statut Membre Dernière intervention 24 avril 2024 2 404
24 nov. 2014 à 18:18

@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 lundi 23 juillet 2007 Statut Contributeur Dernière intervention 1 avril 2022 6 395
Modifié par Vaucluse le 24/11/2014 à 19:06

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 vendredi 19 septembre 2014 Statut Membre Dernière intervention 6 décembre 2023 1
24 nov. 2014 à 20:17

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 vendredi 19 septembre 2014 Statut Membre Dernière intervention 6 décembre 2023 1 > Happy79_2 Messages postés 73 Date d'inscription vendredi 19 septembre 2014 Statut Membre Dernière intervention 6 décembre 2023
24 nov. 2014 à 20:21

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 vendredi 19 septembre 2014 Statut Membre Dernière intervention 6 décembre 2023 1
21 nov. 2014 à 21:03

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 18313 Date d'inscription dimanche 17 février 2008 Statut Contributeur Dernière intervention 21 avril 2024 5 073
21 nov. 2014 à 22:50

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 vendredi 19 septembre 2014 Statut Membre Dernière intervention 6 décembre 2023 1
24 nov. 2014 à 15:38

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 lundi 23 juillet 2007 Statut Contributeur Dernière intervention 1 avril 2022 6 395
24 nov. 2014 à 15:55

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 vendredi 19 septembre 2014 Statut Membre Dernière intervention 6 décembre 2023 1
24 nov. 2014 à 16:32

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

Réponse 7 / 12

michel_m Messages postés 16603 Date d'inscription lundi 12 septembre 2005 Statut Contributeur Dernière intervention 16 décembre 2023 3 303
24 nov. 2014 à 15:59

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

Réponse 8 / 12

Happy79_2 Messages postés 73 Date d'inscription vendredi 19 septembre 2014 Statut Membre Dernière intervention 6 décembre 2023 1
24 nov. 2014 à 16:05

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 16603 Date d'inscription lundi 12 septembre 2005 Statut Contributeur Dernière intervention 16 décembre 2023 3 303
24 nov. 2014 à 16:42

EXCUSEZ MOI d'avoir OSER proposer une solution

Happy79_2 Messages postés 73 Date d'inscription vendredi 19 septembre 2014 Statut Membre Dernière intervention 6 décembre 2023 1 > michel_m Messages postés 16603 Date d'inscription lundi 12 septembre 2005 Statut Contributeur Dernière intervention 16 décembre 2023
24 nov. 2014 à 16:45

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

Vaucluse Messages postés 26496 Date d'inscription lundi 23 juillet 2007 Statut Contributeur Dernière intervention 1 avril 2022 6 395
Modifié par Vaucluse le 24/11/2014 à 17:59

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 10853 Date d'inscription lundi 18 octobre 2010 Statut Membre Dernière intervention 24 avril 2024 2 404
24 nov. 2014 à 16:47

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 vendredi 19 septembre 2014 Statut Membre Dernière intervention 6 décembre 2023 1
24 nov. 2014 à 16:49

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 10853 Date d'inscription lundi 18 octobre 2010 Statut Membre Dernière intervention 24 avril 2024 2 404
24 nov. 2014 à 17:05

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 vendredi 19 septembre 2014 Statut Membre Dernière intervention 6 décembre 2023 1
24 nov. 2014 à 19:09

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 10853 Date d'inscription lundi 18 octobre 2010 Statut Membre Dernière intervention 24 avril 2024 2 404
24 nov. 2014 à 19:14

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 mardi 20 janvier 2009 Statut Contributeur Dernière intervention 20 décembre 2016 942
24 nov. 2014 à 19:31

Salut le Forum

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

Exemple 2, 1

Mytå

ccm81 Messages postés 10853 Date d'inscription lundi 18 octobre 2010 Statut Membre Dernière intervention 24 avril 2024 2 404
Modifié par ccm81 le 24/11/2014 à 20:05

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 vendredi 19 septembre 2014 Statut Membre Dernière intervention 6 décembre 2023 1
24 nov. 2014 à 19:35

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.

Posez votre question

Discussions similaires

Modifier un filtre automatiquement selon la valeur d'une cellule

valou_ne - 20 nov. 2014 à 16:27
Gyrus - 21 nov. 2014 à 14:36

3 réponses

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

Cmoi - 24 nov. 2014 à 15:59
Cmoi - 24 nov. 2014 à 16:18

3 réponses

Conditions comprise entre 2 valeurs

Bourlamaque - 30 oct. 2014 à 04:02
Bourlamaque - 2 nov. 2014 à 16:09

7 réponses

insérer contenu d'une cellule dans pied de page

Floki72450 - 17 oct. 2014 à 12:05
Floki72450 - 17 oct. 2014 à 17:36

6 réponses

Extraire une chaîne de caractère après un tiret

Oliv94 - 2 janv. 2015 à 10:44
AlpSG - 15 sept. 2015 à 18:20

10 réponses

Liste déroulante en fonction d'une autre cellule

Somik - 22 août 2014 à 11:51
tyranausor - 22 août 2014 à 20:00

4 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

Discussions similaires

Newsletters