Menu

Calculer l’occurrence de deux données (pas somme) [Résolu]

Messages postés
25
Date d'inscription
mardi 31 juillet 2018
Dernière intervention
17 septembre 2018
-
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.
Afficher la suite 

Votre réponse

3 réponses

Meilleure réponse
Messages postés
47702
Date d'inscription
lundi 13 août 2007
Statut
Contributeur
Dernière intervention
17 décembre 2018
2
Merci
Bonjour.

Premier obstacle majeur, empêchant toute recherche automatique :
les intitulés en J2:N2 sont différents de ceux en colonne C !

Dire « Merci » 2

Heureux de vous avoir aidé ! Vous nous appréciez ? Donnez votre avis sur nous ! Evaluez CommentCaMarche

CCM 36952 internautes nous ont dit merci ce mois-ci

Manonbibli
Messages postés
25
Date d'inscription
mardi 31 juillet 2018
Dernière intervention
17 septembre 2018
-
Bonjour,

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.
Raymond PENTIER
Messages postés
47702
Date d'inscription
lundi 13 août 2007
Statut
Contributeur
Dernière intervention
17 décembre 2018
-
J'avais compris.
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 !
Manonbibli
Messages postés
25
Date d'inscription
mardi 31 juillet 2018
Dernière intervention
17 septembre 2018
-
D'accord merci. Et avec ce tableau ? https://www.cjoint.com/c/HHbjxntdTow
Commenter la réponse de Raymond PENTIER
Messages postés
22255
Date d'inscription
mardi 11 septembre 2007
Statut
Contributeur
Dernière intervention
17 décembre 2018
1
Merci
Bonjour,

Merci de déposer le fichier xls sur cjoint.com et coller ici le lien fourni.
eric
Manonbibli
Messages postés
25
Date d'inscription
mardi 31 juillet 2018
Dernière intervention
17 septembre 2018
-
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.
Commenter la réponse de eriiic
Messages postés
22255
Date d'inscription
mardi 11 septembre 2007
Statut
Contributeur
Dernière intervention
17 décembre 2018
1
Merci
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

En essayant continuellement, on finit par réussir. 
Donc plus ça rate, plus on a de chances que ça marche.(les Shadoks)
En plus du merci (si si, ça se fait !!!), penser à mettre en résolu. Merci
Manonbibli
Messages postés
25
Date d'inscription
mardi 31 juillet 2018
Dernière intervention
17 septembre 2018
-
Merci Eric.
Feuille tmp masquée.
Et tu as bien raison, du coup j'ai testé de possibles ajouts. Aucun souci pour rajouter des niveaux de classe ou des établissements scolaires, ils apparaissent bien pris en compte dans les tableaux bilans. Cela marche sans souci.

1) J'ai aussi essayé de créer un nouveau tableau bilan, afin d'être sûre d'avoir bien compris la logique... et cela ne marche pas ! Grrr. Si tu as encore la motivation pour regarder d'où peut venir le blocage... je t'en remercierais ! Cela me gêne de te demander davantage donc n'hésite pas à me dire si tu satures avec ce tableau !
Il doit me manquer un truc que je n'ai pas bien compris.
J'ai par exemple créé deux nouveaux tableaux (regroupés en un), similaires aux tableaux Bilans par quartier. Sauf que dans Champ lig, au lieu de sélectionner Reg_Quart, j'ai sélectionné "Etablissement scolaire". L'idée étant d'avoir toujours les mêmes données analysées mais par établissement scolaire et non plus par quartier. Pas besoin de regroupement donc puisqu'on indique le champ directement.

Cela m'indique alors :
Erreur d'exécution 9 - L'indice n'appartient pas à la sélection

J'ai pourtant revérifié les plages sélectionnées qui sont les bonnes, ainsi que les intitulés pour qu'ils correspondent à ceux dans Données... je n'ai pas nommé tous les établissements scolaires (seulement une partie, celle dont les données m'intéressent), mais je doute que ce soit cela qui plante le calcul.
Et bizarrement, à chaque fois que je retourne dans "Données", cela filtre automatiquement la colonne "niveau de classes" avec les niveaux maternelles...
https://www.cjoint.com/c/HInnaeNY52w

Et deux questions annexes pour être sûre de ne pas "casser" le travail fait :
2)) Dans l'onglet Bilans, est-il possible d'insérer des lignes supplémentaires entre les lignes 1 et 8 (avant la touche maj) ? si besoin futur de créer d'autres tableaux bilans.
3) Dans l'onglet "Regroupements", je pourrais hypothétiquement créer un nouveau regroupement sans toucher aux lignes de code ? Y a-t-il une manip spéciale à faire pour que l'intitulé du nouveau regroupement englobe tous les éléments en-dessous ? (car je ne comprends pas bien comment actuellement Reg-Quart sait qu'il travaille sur T3:U54).

Merci !
Manon
eriiic
Messages postés
22255
Date d'inscription
mardi 11 septembre 2007
Statut
Contributeur
Dernière intervention
17 décembre 2018
-
Bonjour,

je regarde ça ce we. A force de refaire, possible que j'ai omis un truc :-)
A+
eric
eriiic
Messages postés
22255
Date d'inscription
mardi 11 septembre 2007
Statut
Contributeur
Dernière intervention
17 décembre 2018
-
Bonjour,

n'hésite pas à me dire si tu satures avec ce tableau !
C'est trop tard, j'ai saturé depuis longtemps. Là je suis passé à la phase 'blasé' donc tout passe ;-)

1) déjà, une bonne nouvelle : tu as parfaitement compris comment définir les paramètres d'un tableau :-)
C'est bien ce que je pressentais, j'ai zappé le fait que tu pouvais indiquer un nom de champ à la place d'un regroupement, vu que le cas ne se présentait pas.
Corrigé pour lignes et colonnes.
Je te laisse contrôler plus à fond mais à-priori c'est bon avec tes 3 lignes de données (plus facile de contrôler :-) )
https://www.cjoint.com/c/HIpo6K8VdAT

2) Dans l'onglet Bilans, est-il possible d'insérer des lignes supplémentaires entre les lignes 1 et 8 (avant la touche maj) ? si besoin futur de créer d'autres tableaux bilans.
Oui, j'avais anticipé ce besoin.
Tu sélectionnes x lignes à partir de la dernière ligne et tu fais juste Insérer. Je te l'ai mis à 15 lignes.
A partir du moment où tu as bien saisis tes plages sous la forme =C20:G31 et non simplement C20:G31 tes paramètres déjà créés s'adapteront automatiquement.
Maintenant tu peux voir l'avantage et la raison d'avoir des #VALEUR! ou des valeurs sans sens (perturbants au début) au lieu de lire simplement la référence choisie.

3) Dans l'onglet "Regroupements", je pourrais hypothétiquement créer un nouveau regroupement sans toucher aux lignes de code ? Y a-t-il une manip spéciale à faire pour que l'intitulé du nouveau regroupement englobe tous les éléments en-dessous ? (car je ne comprends pas bien comment actuellement Reg-Quart sait qu'il travaille sur T3:U54).
Oui, tu peux, à condition de respecter scrupuleusement les régles que la macro retrouve ses petits.

Regarde la ligne 1
Quand tu indiques un champ ligne, je le recherche en ligne 1.
Si trouvé (reg_Quart en T1) c'est que c'est un regroupement, sinon c'est un nom de champ à trouver directement dans Données.
Si c'est un regroupement, 2 lignes en-dessous je dois trouver le nom du champ lié de Données (Etablissement scolaire).
Là je sélectionne la zone en cours qui est T3:U54 (d'où la nécessité d'avoir des lignes et colonnes vides autour) pour récupérer les données du regroupement.
Ces données sont récupérées dans des dictionaries dans la Sub init(), ce qui permet de les avoir en permanence sous le coude, sans aller faire de multiples lectures de la feuille Regroupements.

2 cas :
- si plus de 2 colonnes : un item peut appartenir à plusieurs groupes. Les groupes doivent être listés et on coche l'appartenance.
- si 2 colonnes : un item ne peut appartenir qu'à un seul groupe qu'on indique en 2nde colonne. Ce qui évite d'avoir 15 colonnes si 15 groupes.

Le brouillard s'est dissipé ?
eric
Manonbibli
Messages postés
25
Date d'inscription
mardi 31 juillet 2018
Dernière intervention
17 septembre 2018
-
Bonjour Eric,

La phase blasée, zut ! Et dire que c'est moi qui t'ai infligé tout cela !^^ En tout cas merci, j'ai découvert de nouveaux pans d'Excel qu'il me tarde d'approfondir de mon côté. Merci pour la correction concernant l'indication du nom de champs. Ça marche nickel. Merci aussi pour toutes ces explications, très claires et que je garde précieusement. Le brouillard s'est dissipé, oui.
Je ne pense pas avoir besoin dans l'immédiat de créer de nouveaux champs et si cela arrive un jour, je creuserai du côté des dictionaries dans la Sub init(), chose qui ne me parle pas du tout pour le moment ! :p

En bref, un vrai bonheur d'avoir bénéficié de ton aide, de ta patience et de ton investissement si important (un mois et demi d'échanges quand-même) ! Je crois que maintenant, une chatte peut retrouver ses petits dans ce fichier Excel ! ;)
En espérant ne plus avoir à t’embêter, je te souhaite une bonne continuation, sur le forum et en dehors.
Manon
eriiic
Messages postés
22255
Date d'inscription
mardi 11 septembre 2007
Statut
Contributeur
Dernière intervention
17 décembre 2018
-
Bonjour,

Oufff !!! ;-)
Si demain tu dois créer de nouveaux regroupement tu n'auras rien à modifier dans la macro. Elle les prendra en compte automatiquement.
Que ça ne t'empêche pas de regarder de plus près les dictionaries, ça te servira bien un jour :-)
Bonne continuation à toi aussi.
eric
Commenter la réponse de eriiic