[NB.SI EXCEL] Recherche et Somme sur 2 critèr

Résolu/Fermé
Livity - 13 août 2007 à 17:12
 CF - 8 août 2012 à 11:02
Bonjour,

J'essaye vainement de trouver la formule capable de satisfaire à mes attentes sous Excel, peut-être quelqu'un pourra t'il m'aiguiller...

J'ai un tableau en Feuil1, un autre en Feuil2, dans le 2ème je souhaite voir apparaître le nb d'occurences trouvé en réponse à une recherche multicritères.

Ex :
A B
1 X
1 Y
2 X
1 X
2 Y

Je souhaite comptabiliser le nb de cellules de la colonne A contenant 1 et ayant une valeur à X dans la colonne B (dans l'exemple = 2).

Si vous m'avez compris (c'est pas gagné) et que vous connaissez le moyen de m'aider je vous en serai éternellement reconnaissant :)

Cordialement.

8 réponses

Raymond PENTIER
Messages postés
56097
Date d'inscription
lundi 13 août 2007
Statut
Contributeur
Dernière intervention
21 mai 2022
17 209
14 août 2007 à 06:38
Je ne sais pas si tu as reçu ma réponse initiale, car c'est la première fois que j'utilise CommntCaMarche.net !

Aussi je te la redonne à tout hasard :

Il suffit, en colonne C, de concaténer (&) les variables des colonnes A et B.

Ainsi en tapant {=A1&B1 } dans la cellule {C1} on obtient comme résultat {1X}.

La recherche du nombre d'occurences se fera donc en bas de colonne C, avec la fonction NB.SI.



Fais savoir si ça t'a donné satisfaction !
10
Mme Szokalova
8 sept. 2010 à 14:43
vous, vous m'avez sauvé la peau ! une astuce aussi simple !
on travaille sur des choses compliquées, on en oublie les bases ...

Merci encore.

Mme Szokalova
0
Raymond PENTIER
Messages postés
56097
Date d'inscription
lundi 13 août 2007
Statut
Contributeur
Dernière intervention
21 mai 2022
17 209
8 sept. 2010 à 19:16
Bonjour.

Depuis 2007, l'eau a coulé sous les ponts, ma barbe a blanchi, et Excel 2007 s'est répandu.

Avec cette version on dispose d'une nouvelle fonction, bien adaptée à ce cas de figure : =NB.SI.ENS( A1:A5;1; B1:B5;"x" )

Cordialement.
0
Mytå
Messages postés
2967
Date d'inscription
mardi 20 janvier 2009
Statut
Contributeur
Dernière intervention
20 décembre 2016
904
9 sept. 2010 à 04:33
Salut le forum

Et pour ceux qui n'utilise pas encore Excel 2007

=SOMMEPROD((A1:A5=1)*(B1:B5="X"))
ou

=SOMMEPROD(--(A1:A5=1);--(B1:B5="X"))
Mytå
0
Raymond PENTIER
Messages postés
56097
Date d'inscription
lundi 13 août 2007
Statut
Contributeur
Dernière intervention
21 mai 2022
17 209
9 sept. 2010 à 19:11
0
Merci beaucoup Raymond!!

Votre formule sous le Excel 2007 m'a beaucoup aidée pour un problème similaire!! Merci de votre aide à tous, c'est très apprécié.
0
Raymond PENTIER
Messages postés
56097
Date d'inscription
lundi 13 août 2007
Statut
Contributeur
Dernière intervention
21 mai 2022
17 209
17 août 2007 à 16:49
Livity,

Je n'ai plus de tes nouvelles !?...

Il existe une fonction qui répondrait parfaitement à tes besoins, mais elle n'est disponible que dans la version 2007 d'Excel (sauf si Microsoft peut te fournir une macro équivalente qui fonctionnerait sur les versions antérieures).


NB.SI.ENS
Compte le nombre de cellules à l'intérieur d'une plage qui répondent à plusieurs critères.

Syntaxe : NB.SI.ENS(plage1, critère1,plage2, critère2…)

Plage1, plage2, … représentent 1 à 127 plages dans lesquelles les critères associés doivent être évalués. Les cellules de chaque plage doivent être des nombres ou des noms, des matrices ou des références contenant des nombres. Les valeurs vides et textuelles seront ignorées.

Critère1, critère2 représentent 1 à 127 critères, sous forme de nombre, d'expression, de référence de cellule ou de texte, qui déterminent les cellules à compter.

Remarques : Chaque cellule d'une plage est additionnée seulement si tous les critères correspondants spécifiés sont vrais pour la cellule. Si le critère correspond à une cellule vide, NB.SI.ENS la considère comme une valeur de 0. Vous pouvez utiliser les caractères génériques [le point d'interrogation (?) et l'astérisque (*)] dans l'argument critère. Le point d'interrogation correspond à un caractère quelconque et l'astérisque à une séquence de caractères. Si vous recherchez un point d'interrogation ou un astérisque, tapez un tilde (~) devant ce caractère.

Amicalement, Raymond
4
Bonjour, le sujet semble clos depuis longtemps, cependant il correspond tout à fait à ce que je recherche !

je travaille sous Excel 2003, il ne m'est donc pas possible d'utiliser la fonction NB.SI.ENS (disponible sous Excel 2007), dommage...

je pense que toutes vos réponses n'étaient pas adaptées hormis la dernière de Raymond.

en effet, les fichiers à gérer peuvent être très lourds et les formules afférentes aux recherches multicritères doivent être les plus simples possibles pour ne pas surcharger ces fichiers.

En gros, pour résumer, voilà ce que je voudrais faire (en évitant la macro) :

J’ai un tableau de données comprenant une colonne X avec des nombres et une colonne Y avec des chiffres.

Je voudrais comptabiliser le nombre de lignes ayant en commun des arguments en X et des arguments en Y que je définis. J’ai essayé de créer une formule du type :

=NB.SI(matriceXY;SI(EQUIV("argument recherché en X";colonne X;0);SI(EQUIV("argument recherché en Y";colonne Y;0);1;0);0))
ou
=NB.SI(matriceXY;ET(RECHERCHE(colonne X;"argument recherché en X");RECHERCHE(colonne Y;"argument recherché en Y")))

Le problème est que les fonctions EQUIV et RECHERCHE de Excel renvoient vers des positions. La fonction NB.SI est donc inefficace...

si quelqu'un a une solution ?
0
Selecteur
Messages postés
44
Date d'inscription
samedi 12 février 2005
Statut
Membre
Dernière intervention
29 septembre 2013
6
13 août 2007 à 17:56
Bonjour Livity, ALMAGRO, le forum,

Désolé Almagro mais je n'avais pas vu ton message. Dans mon message j'ai oublié l'essentiel : il faut valider par les touches ctrl + maj + Entrée (les trois en même temps). Il s'agit d'une formule matricielle.

Selecteur.
1
Bonjour à tous,

Effectivement seul Raymond a compris ma demande mais je vous remercie tous de m'avoir répondu.

Toutefois, Raymond, saches que la solution que tu me proposes est pour moi trop lourde à mettre en oeuvre puisqu'il s'agit de sortir des statistiques sur + de 5000 lignes et sur 5 critères différents (2 en simultané max.)...

J'ai bricolé aussi de mon côté et je n'ai rien trouvé de très léger, pour parvenir à mes fins c'est du rafistolage. Personne n'aurait donc dans un recoin de son cerveau conserver la trace d'une formule magique permettant de faire ce calcul en une seule et unique cellule ?
1
Raymond PENTIER
Messages postés
56097
Date d'inscription
lundi 13 août 2007
Statut
Contributeur
Dernière intervention
21 mai 2022
17 209
14 août 2007 à 18:10
Je me doutais bien que le problème était plus corsé qu'il n'y paraissait dans ta question initiale !

Si c'est possible (j'ignore comment tu as structuré ton fichier et organisé tes saisies), la solution idéale est de gérer ton fichier comme une base de données.

Pour mémoire,
* la première ligne du fichier contient les "champs", c'est-à-dire le nom des colonnes (dont en pratique la liste des critères),
* la deuxième ligne et les suivantes contiennent les "enregistrements", c'est-à dire les données relatives à chaque élément.

Attention,
* pas de ligne vide, pas de colonne vide (sous prétexte de présentation et d'espacement)
* dans la première colonne, qui sert de clé de recherche, il ne faut pas de doublon (chaque saisie doit être unique) ; au besoin tu insères une première colonne avec un N° d'ordre qui permettra de distinguer des enregistrements ayant la même désignation initiale.

Une fois ces précautions (obligatoires) prises, ton curseur étant dans une cellule renseignée (B2 fera très bien l'affaire), tu vas dans "Trier et filtrer" et tu actives "Filtre automatique".

Tu disposes dès lors d'une "Base de Données" grâce à laquelle tu peux effectuer des tris simples ou combinés, des filtres simples ou combinés, et même des sous-totaux.
Mais il sera toujours temps de te donner quelques conseils pour bien les utiliser.

Avant tout, es-tu en mesure d'organiser ton fichier sous forme de base de données comme indiqué ci-dessus ?
0
Raymond PENTIER
Messages postés
56097
Date d'inscription
lundi 13 août 2007
Statut
Contributeur
Dernière intervention
21 mai 2022
17 209
24 août 2007 à 16:20
Bonjour, Livity.

Grâce à UsulArrakis, je crois savoir maintenant comment transmettre un fichier :
il faut utiliser le service http://cjoint.com/.
Alors essaie de m'envoyer ton fameux fichier avec recherche multicritère (s'il est trop "lourd", supprime 4500 lignes sur les 5000 existantes). J'essaierai de trouver une astuce.
A bientôt ?
0

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

Posez votre question
Selecteur
Messages postés
44
Date d'inscription
samedi 12 février 2005
Statut
Membre
Dernière intervention
29 septembre 2013
6
13 août 2007 à 17:44
Bonjour Livity, le forum,

Pour connaitre le nombre de 1 et X, essaie ça :
Avec

A B
1 X
2 Y
2 Y
1 X
1 X
2 X


=SOMME((A1:A6=1)*(B1:B6="X"))

à adapter pour le nombre de 2 et Y

Selecteur
0
Bonjour,
La formule =somme((A1:A6=1)*(B1:B6="X")) ne fonctionne pas.
0
Vaucluse
Messages postés
26495
Date d'inscription
lundi 23 juillet 2007
Statut
Contributeur
Dernière intervention
8 avril 2022
6 253
25 juin 2011 à 17:32
bonjour
pour la faire fonctionner:
=SOMMEPROD((A1:A6=1)*(B1:B6="X"))
on espère que depuis Aout 2007,Selecteur a fait des progrès!
crdlmnt
0
Raymond PENTIER
Messages postés
56097
Date d'inscription
lundi 13 août 2007
Statut
Contributeur
Dernière intervention
21 mai 2022
17 209
Modifié par Raymond PENTIER le 26/06/2011 à 02:56
En effet,
le 25/06/2011 notre sympathique ami(e) de la Réunion n'a pas vu que l'erreur commise au post #2
le 13/08/2007 par Sélecteur avait été corrigée au commentaire #15
le 09/09/2010 par notre membre Mytå ...
https://forums.commentcamarche.net/forum/affich-3389135-nb-si-excel-recherche-et-somme-sur-2-criter#15
0
Il existe aussi un outil de recherche multi-format : http://curseur.biz/produits/easy-data-search
0
Raymond PENTIER
Messages postés
56097
Date d'inscription
lundi 13 août 2007
Statut
Contributeur
Dernière intervention
21 mai 2022
17 209
29 août 2011 à 01:00
Et en quoi cet outil (payant) présente-t-il un intérêt pour résoudre le problème posé ?
Cette information concerne-t-elle un post précis de cette discussion ?
0
Bonjour Livity, Raymond PENTIER, le forum,


Livity, j’avoue ne pas comprendre puisque Raymond et moi-même te proposons deux solutions qui te donnent ce que tu souhaites c'est-à-dire le nombre de « 1X » par exemple. De plus tu ajoutes dans ton dernier message qu’il y a 5 critères, élément important que tu aurais pu indiquer dans ta demande initiale. Concernant ce nombre 5 : est-ce que tu veux dire par exemple que dans la colonne A on a le critère « 1 » puis le critère « 2 » et ainsi de suite jusqu’au critère « 5 » ET dans la colonne B le critère « A » puis le critère « B » et ainsi de suite jusqu’au critère « E » ?

Si c’est ça alors tu as 25 (5 x 5) formules identiques à écrire, ce qui n’est rien puisque tu fais un copier coller et tu adaptes avec les solutions possibles (1A, 1B,…).

Peux-tu nous dire en quoi ce que propose Raymond PENTIER est trop lourd à mettre en œuvre ? (comprends pas ! il s’agit d’une formule parmi les 25)

Sinon, peux-tu nous dire, svp, ce qui ne convient pas, ainsi nous pourrons chercher la meilleure solution pour toi ?

Selecteur.
0
michel_m
Messages postés
16568
Date d'inscription
lundi 12 septembre 2005
Statut
Contributeur
Dernière intervention
14 mai 2022
3 280
5 déc. 2008 à 11:17
Bonjour,

Si j'ai bien compris: dans une matrice de 2 colonnes, tu veux savoir le nombre de fois où les données sont égales sur la m^me ligne?
dans ce cas:
=SOMMEPROD((A1:A35=B1:B35)*1)
0
BONJOUR TOUT EST SIMPLE SUR EXCEL IL FAUT COPIER LES RESULTATS ET LES EMMENER DANS LA CELLULE OU VOUS DESIREZ TOUT RETROUVER EXEMPLE VOUS AVEZ UN RESULTAT EN CELLULE A2 UN AUTRE EN B6 VOUS FAITES DANS LA COLONNE OU VOUS VOULEZ LES RETROUVER =A2+B6 en espèrant que vous aurez compris bonsoir ALMAGRO
SINON JE PEUX RECOMMENCER
-2
Raymond PENTIER
Messages postés
56097
Date d'inscription
lundi 13 août 2007
Statut
Contributeur
Dernière intervention
21 mai 2022
17 209
14 août 2007 à 06:25
Je m'excuse, mais ce n'est pas le problème posé par Livity. Il s'agit pour EXCEL de compter le nombre d'occurences d'une valeur dans un liste.
0