Calculer l’occurrence de deux données (pas somme)
Résolu
Manonbibli
Messages postés
25
Date d'inscription
Statut
Membre
Dernière intervention
-
eriiic Messages postés 24603 Date d'inscription Statut Contributeur Dernière intervention -
eriiic Messages postés 24603 Date d'inscription Statut Contributeur Dernière intervention -
Bonjour à tous,
(Excel 2010)
Je travaille pour un réseau de médiathèques d’une ville et suis en train d’élaborer un tableau excel permettant de recenser différentes informations relatives aux accueils de classe effectués.
En voici la version simplifiée, me centrant sur les formules que je n'arrive pas à trouver (cellules en jaune).
https://www.cjoint.com/c/HGFkIBw6E4w
Il y a 6 niveaux d’organismes accueillant ces classes :
- 4 médiathèques (A, B, C et D), constituant le réseau de lecture publique appelé « Réseau »
- Un établissement culturel (qu’on nommera « passerelle ») constitué à la fois d’une des 4 médiathèques (la A) et des archives municipales (E)
- Une fête du livre (F)
J’aurais besoin d’obtenir :
- Le nombre d’accueils de classe effectués (si une classe vient 3 fois, elle est comptée 3 fois). CHIFFRE DEJA OBTENU FACILEMENT
- 1) Le nombre de classes accueillies (une classe venue 3 fois est comptée 1 fois).
- 2) Le nombre d’enfants accueillis (afin de ne pas compter 3 fois le même enfant, il ne suffit pas de faire la somme de la colonne « nombre d’enfants accueillis »).
Sachant que, en colonne, nous complétons ces données :
Etablissement scolaire Niveau de classe Enseignant référent Nombre enfants
+ Organisme accueillant (parmi A B C D E ou F)
(sachant que dans une autre colonne, A et E s’inscrivent automatiquement comme appartenant à l’établissement Passerelle).
J’ai réussi à obtenir les chiffres voulus pour l’ensemble des 6 organismes avec la création d’une colonne J « Classe accueillie pour la… fois sur l’ensemble des organismes » que remplissent manuellement les collaborateurs., puis avec la formule =NB.SI.ENS('Feuille 1'!B:B;"Organisme accueillant";'Feuille 1'!L:L;"Niveau de la classe";'Feuille 1'!J:J;"=1")
Mais du coup, cela marche uniquement pour avoir le nombre de classes et d’enfants accueillis pour le Total (car une classe peut être accueillie pour la 2ème fois au Total, mais pour la première fois dans l'organisme) ; les chiffres sont faux quand je veux obtenir les données uniquement :
- Par organisme
- Pour le réseau de médiathèques (composé de A B C et D)
- Pour l’établissement passerelle (composé de A et E)
- Pour le total (A B C D E et F).
Connaissez-vous un autre moyen d’arriver au résultat voulu ?
Je pensais créer sur Excel plusieurs colonnes masquées qui diraient automatiquement pour la combientième fois vient une classe, par organisme, pour le réseau, pour l’établissement passerelle…, en combinant les colonnes « enseignant référent » et « organisme accueillant » (si elles sont remplies deux fois de la même manière, c’est que la classe de l’enseignant est venue deux fois).
Y a-t-il moyen de croiser deux cellules et savoir, non le nombre total d’occurrence, mais sur la même ligne, pour la combientième fois, la classe est accueillie ?
Si vous avez d’autres idées, je suis preneuse !
Un grand merci à vous.
(Excel 2010)
Je travaille pour un réseau de médiathèques d’une ville et suis en train d’élaborer un tableau excel permettant de recenser différentes informations relatives aux accueils de classe effectués.
En voici la version simplifiée, me centrant sur les formules que je n'arrive pas à trouver (cellules en jaune).
https://www.cjoint.com/c/HGFkIBw6E4w
Il y a 6 niveaux d’organismes accueillant ces classes :
- 4 médiathèques (A, B, C et D), constituant le réseau de lecture publique appelé « Réseau »
- Un établissement culturel (qu’on nommera « passerelle ») constitué à la fois d’une des 4 médiathèques (la A) et des archives municipales (E)
- Une fête du livre (F)
J’aurais besoin d’obtenir :
- Le nombre d’accueils de classe effectués (si une classe vient 3 fois, elle est comptée 3 fois). CHIFFRE DEJA OBTENU FACILEMENT
- 1) Le nombre de classes accueillies (une classe venue 3 fois est comptée 1 fois).
- 2) Le nombre d’enfants accueillis (afin de ne pas compter 3 fois le même enfant, il ne suffit pas de faire la somme de la colonne « nombre d’enfants accueillis »).
Sachant que, en colonne, nous complétons ces données :
Etablissement scolaire Niveau de classe Enseignant référent Nombre enfants
+ Organisme accueillant (parmi A B C D E ou F)
(sachant que dans une autre colonne, A et E s’inscrivent automatiquement comme appartenant à l’établissement Passerelle).
J’ai réussi à obtenir les chiffres voulus pour l’ensemble des 6 organismes avec la création d’une colonne J « Classe accueillie pour la… fois sur l’ensemble des organismes » que remplissent manuellement les collaborateurs., puis avec la formule =NB.SI.ENS('Feuille 1'!B:B;"Organisme accueillant";'Feuille 1'!L:L;"Niveau de la classe";'Feuille 1'!J:J;"=1")
Mais du coup, cela marche uniquement pour avoir le nombre de classes et d’enfants accueillis pour le Total (car une classe peut être accueillie pour la 2ème fois au Total, mais pour la première fois dans l'organisme) ; les chiffres sont faux quand je veux obtenir les données uniquement :
- Par organisme
- Pour le réseau de médiathèques (composé de A B C et D)
- Pour l’établissement passerelle (composé de A et E)
- Pour le total (A B C D E et F).
Connaissez-vous un autre moyen d’arriver au résultat voulu ?
Je pensais créer sur Excel plusieurs colonnes masquées qui diraient automatiquement pour la combientième fois vient une classe, par organisme, pour le réseau, pour l’établissement passerelle…, en combinant les colonnes « enseignant référent » et « organisme accueillant » (si elles sont remplies deux fois de la même manière, c’est que la classe de l’enseignant est venue deux fois).
Y a-t-il moyen de croiser deux cellules et savoir, non le nombre total d’occurrence, mais sur la même ligne, pour la combientième fois, la classe est accueillie ?
Si vous avez d’autres idées, je suis preneuse !
Un grand merci à vous.
A voir également:
- Calculer l’occurrence de deux données (pas somme)
- Fuite données maif - Guide
- Comment calculer la moyenne sur excel - Guide
- Formule somme excel colonne - Guide
- Somme si couleur - Guide
- Nombre de jours entre deux dates excel - Guide
3 réponses
Bonjour.
Premier obstacle majeur, empêchant toute recherche automatique :
les intitulés en J2:N2 sont différents de ceux en colonne C !
Premier obstacle majeur, empêchant toute recherche automatique :
les intitulés en J2:N2 sont différents de ceux en colonne C !
Bonjour,
Merci de déposer le fichier xls sur cjoint.com et coller ici le lien fourni.
eric
Merci de déposer le fichier xls sur cjoint.com et coller ici le lien fourni.
eric
Merci Eric ! J'ai rajouté le lien à l'article initial.
Il s'agit de celui-ci : https://www.cjoint.com/c/HGFkIBw6E4w
J'ai surligné en jaune les cellules dont je n'arrive pas à trouver la formule.
Il s'agit de celui-ci : https://www.cjoint.com/c/HGFkIBw6E4w
J'ai surligné en jaune les cellules dont je n'arrive pas à trouver la formule.
Bonjour,
un peu complexe, j'ai fait selon ce que j'ai compris.
Ca passe par l'ajout de champs clés et de champs compteur pour alléger les formules finales. Tu pourras masquer ces colonnes.
Si je suis à peu près sûr pour le 2nd tableau, je le suis moins sur le premier.
Il faudrait que tu contrôles attentivement en ajoutant plus de lignes.
https://www.cjoint.com/c/HHbmU0XwSmM
eric
un peu complexe, j'ai fait selon ce que j'ai compris.
Ca passe par l'ajout de champs clés et de champs compteur pour alléger les formules finales. Tu pourras masquer ces colonnes.
Si je suis à peu près sûr pour le 2nd tableau, je le suis moins sur le premier.
Il faudrait que tu contrôles attentivement en ajoutant plus de lignes.
https://www.cjoint.com/c/HHbmU0XwSmM
eric
Bonjour Eric,
Le système que tu m’as proposé a fonctionné à la perfection. Mes totaux fonctionnent bien. Un grand merci !
Je suis maintenant en train de détailler mon tableau, pour pouvoir avoir le nombre de classes et enfants accueillis, par niveau scolaire (maternelle, élémentaire, collège lycée), puis par quartier de la ville (pas fini !) mais je n’arrive pas tout à fait à approfondir mes formules, en ajoutant des conditions aux formules que tu as trouvées.
Concrètement, la formule pour avoir le nombre de classes accueillies dans la bibliothèque 1 (bib1) est celle-ci (celle que tu avais proposée):
=SOMME.SI(T_sept_dec[Secteur organisateur];"bib1";T_sept_dec[cptEtablissement])
Et je dois maintenant rajouter : SI dans la colonne K « groupe niveau» est indiqué maternelle, pour avoir le nombre de maternelles accueillis uniquement.
J’ai trouvé la bonne formule qui est :
=SOMME.SI.ENS(T_sept_dec[cptEtablissement];T_sept_dec[Secteur organisateur];"bib1";T_sept_dec[Groupe niveau];"Maternelle")
Cependant, je n’arrive pas à combiner les deux pans de formules pour la 2ème formule que tu avais construite, permettant d’avoir les données à l’échelle du réseau, celle utilisant SOMMEPROD (je crois que j’ai du mal à cerner cette formule, malgré le fait qu’elle fonctionne, je ne vois pas pourquoi on doit multiplier les arguments).
=SOMMEPROD((GAUCHE(T_sept_dec[CléRéseau];2)="Ré")*T_sept_dec[cptréseau])
--> formule qui donne le nombre de classes accueillies à l’échelle du réseau.
J’aimerais rajouter si :
T_sept_dec[Groupe niveau];"Maternelle"
Une idée ?
Cordialement,
Manon
Le système que tu m’as proposé a fonctionné à la perfection. Mes totaux fonctionnent bien. Un grand merci !
Je suis maintenant en train de détailler mon tableau, pour pouvoir avoir le nombre de classes et enfants accueillis, par niveau scolaire (maternelle, élémentaire, collège lycée), puis par quartier de la ville (pas fini !) mais je n’arrive pas tout à fait à approfondir mes formules, en ajoutant des conditions aux formules que tu as trouvées.
Concrètement, la formule pour avoir le nombre de classes accueillies dans la bibliothèque 1 (bib1) est celle-ci (celle que tu avais proposée):
=SOMME.SI(T_sept_dec[Secteur organisateur];"bib1";T_sept_dec[cptEtablissement])
Et je dois maintenant rajouter : SI dans la colonne K « groupe niveau» est indiqué maternelle, pour avoir le nombre de maternelles accueillis uniquement.
J’ai trouvé la bonne formule qui est :
=SOMME.SI.ENS(T_sept_dec[cptEtablissement];T_sept_dec[Secteur organisateur];"bib1";T_sept_dec[Groupe niveau];"Maternelle")
Cependant, je n’arrive pas à combiner les deux pans de formules pour la 2ème formule que tu avais construite, permettant d’avoir les données à l’échelle du réseau, celle utilisant SOMMEPROD (je crois que j’ai du mal à cerner cette formule, malgré le fait qu’elle fonctionne, je ne vois pas pourquoi on doit multiplier les arguments).
=SOMMEPROD((GAUCHE(T_sept_dec[CléRéseau];2)="Ré")*T_sept_dec[cptréseau])
--> formule qui donne le nombre de classes accueillies à l’échelle du réseau.
J’aimerais rajouter si :
T_sept_dec[Groupe niveau];"Maternelle"
Une idée ?
Cordialement,
Manon
C’est que j’ai grossièrement repris mon tableau de travail en supprimant les données à laisser anonymes ou qui ne concernent pas le problème. Je n’ai pas de soucis avec le calcul concernant les niveaux d’âge des enfants (d’où les informations manquantes :) ) et les intitulés ne correspondant pas). Je coince par contre sur les occurrences d’une même classe accueillie et sur la formule dans les bilans afin d’ avoir, non pas le nombre d’accueil effectués, mais le nombre de classes et d’enfants accueillis.
Mais la formule de recherche, avec NB.SI ou SOMME.SI ou RECHERCHEV ou INDEX aura toujours besoin de comparer la classe concernée en ligne 2 et celle concernée en colonne C ... Alors si les noms diffèrent, c'est foutu !