[NB.SI EXCEL] Recherche et Somme sur 2 critèr
Résolu/Fermé
A voir également:
- [NB.SI EXCEL] Recherche et Somme sur 2 critèr
- Liste déroulante excel - Guide
- Somme excel - Guide
- Somme si couleur excel - Guide
- Si et excel - Guide
- Word et excel gratuit - Guide
8 réponses
Raymond PENTIER
Messages postés
58388
Date d'inscription
lundi 13 août 2007
Statut
Contributeur
Dernière intervention
17 avril 2024
17 088
14 août 2007 à 06:38
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 !
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 !
Raymond PENTIER
Messages postés
58388
Date d'inscription
lundi 13 août 2007
Statut
Contributeur
Dernière intervention
17 avril 2024
17 088
17 août 2007 à 16:49
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
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
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 ?
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 ?
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
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.
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.
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 ?
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 ?
Raymond PENTIER
Messages postés
58388
Date d'inscription
lundi 13 août 2007
Statut
Contributeur
Dernière intervention
17 avril 2024
17 088
14 août 2007 à 18:10
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 ?
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 ?
Raymond PENTIER
Messages postés
58388
Date d'inscription
lundi 13 août 2007
Statut
Contributeur
Dernière intervention
17 avril 2024
17 088
24 août 2007 à 16:20
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 ?
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 ?
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
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
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
Vaucluse
Messages postés
26496
Date d'inscription
lundi 23 juillet 2007
Statut
Contributeur
Dernière intervention
1 avril 2022
6 394
25 juin 2011 à 17:32
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
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
Raymond PENTIER
Messages postés
58388
Date d'inscription
lundi 13 août 2007
Statut
Contributeur
Dernière intervention
17 avril 2024
17 088
Modifié par Raymond PENTIER le 26/06/2011 à 02:56
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
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
Raymond PENTIER
Messages postés
58388
Date d'inscription
lundi 13 août 2007
Statut
Contributeur
Dernière intervention
17 avril 2024
17 088
29 août 2011 à 01:00
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 ?
Cette information concerne-t-elle un post précis de cette discussion ?
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.
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.
michel_m
Messages postés
16603
Date d'inscription
lundi 12 septembre 2005
Statut
Contributeur
Dernière intervention
16 décembre 2023
3 303
5 déc. 2008 à 11:17
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)
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)
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
SINON JE PEUX RECOMMENCER
Raymond PENTIER
Messages postés
58388
Date d'inscription
lundi 13 août 2007
Statut
Contributeur
Dernière intervention
17 avril 2024
17 088
14 août 2007 à 06:25
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.
8 sept. 2010 à 14:43
on travaille sur des choses compliquées, on en oublie les bases ...
Merci encore.
Mme Szokalova
8 sept. 2010 à 19:16
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.
9 sept. 2010 à 04:33
Et pour ceux qui n'utilise pas encore Excel 2007
ou
Mytå
9 sept. 2010 à 19:11
reprend mon ancien post https://forums.commentcamarche.net/forum/affich-3389135-nb-si-excel-recherche-et-somme-sur-2-criter#9
18 juil. 2011 à 18:00
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é.