Menu

Calculer l’occurrence de deux données (pas somme)

Manonbibli 17 Messages postés mardi 31 juillet 2018Date d'inscription 20 août 2018 Dernière intervention - 31 juil. 2018 à 11:36 - Dernière réponse : Manonbibli 17 Messages postés mardi 31 juillet 2018Date d'inscription 20 août 2018 Dernière intervention
- 20 août 2018 à 15:51
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

32 réponses

Meilleure réponse
Raymond PENTIER 46663 Messages postés lundi 13 août 2007Date d'inscriptionContributeurStatut 22 août 2018 Dernière intervention - 31 juil. 2018 à 16:13
1
Merci
Bonjour.

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

Merci Raymond PENTIER 1

Avec quelques mots c'est encore mieux Ajouter un commentaire

CCM a aidé 25032 internautes ce mois-ci

Manonbibli 17 Messages postés mardi 31 juillet 2018Date d'inscription 20 août 2018 Dernière intervention - 31 juil. 2018 à 16:59
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 46663 Messages postés lundi 13 août 2007Date d'inscriptionContributeurStatut 22 août 2018 Dernière intervention - 31 juil. 2018 à 18:33
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 17 Messages postés mardi 31 juillet 2018Date d'inscription 20 août 2018 Dernière intervention - 1 août 2018 à 11:23
D'accord merci. Et avec ce tableau ? https://www.cjoint.com/c/HHbjxntdTow
Commenter la réponse de Raymond PENTIER
eriiic 21918 Messages postés mardi 11 septembre 2007Date d'inscriptionContributeurStatut 21 août 2018 Dernière intervention - 31 juil. 2018 à 12:06
0
Merci
Bonjour,

Merci de déposer le fichier xls sur cjoint.com et coller ici le lien fourni.
eric
Manonbibli 17 Messages postés mardi 31 juillet 2018Date d'inscription 20 août 2018 Dernière intervention - 31 juil. 2018 à 12:37
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
eriiic 21918 Messages postés mardi 11 septembre 2007Date d'inscriptionContributeurStatut 21 août 2018 Dernière intervention - Modifié par eriiic le 1/08/2018 à 14:47
0
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 17 Messages postés mardi 31 juillet 2018Date d'inscription 20 août 2018 Dernière intervention - 17 août 2018 à 11:32
Bonjour Eric,

Quel travail ! Je te remercie. Mais je vais encore avoir besoin d'indications supplémentaires pour prendre en main ce super tableau... c'est un pan d'Excel que je ne maîtrise pas encore.
En effet, j'ai lu et relu les annotations dont tu m'as fait part et je n'arrive pas à comprendre la manière dont je dois travailler pour compléter les chiffres manquants dans les différents tableaux de données. Sic. Ce n'est plus un système de formules apparentes dans les cellules mais seul apparaît le résultat... et je ne comprends pas comment il est généré dans chaque cellule. Il faut que je rajoute des lignes dans le premier tableau, "technique" ?
Tableau "technique" que j'ai encore bien du mal à saisir...notamment la colonne A "type tableau" et la colonne B "pl. Données" (pourquoi n'y a-t-il que 5 références de secteurs à l'intérieur ?)

J'ai complété la colonne TKN dans Regroupements mais elle correspond à TONKIN (oui désolée...). J'ai donc plutôt modifié l'intitulé dans S63. Pas d'impact ? On peut donc bien supprimer la colonne TKN de regroupements ?

Petite question : Je vois que dans "données", tu as ajusté la taille du tableau. Cela a un impact ? Puis-je le laisser plus grand (pour laisser les lignes accessibles aux collaborateurs qui vont les remplir) ?

https://www.cjoint.com/c/HHrjFymGlvw

Merci à toi pour tout ce temps investi!
Manon
eriiic 21918 Messages postés mardi 11 septembre 2007Date d'inscriptionContributeurStatut 21 août 2018 Dernière intervention - 17 août 2018 à 14:10
C'est un programme en VBA.
J'ai abandonné l'idée dune fonction personnalisée matricielle qui aurait été un peu complexe à utiliser.
Par contre on peut remplacer le bouton par un recalcul automatique à l'activation de la feuille.

Pour voir le code il faut aller dans l'éditeur (VBE) : Alt+F11, double-cliquer sur le module1 dans le projet à gauche.

J'ai mis plus d'explication dans le fichier joit. Regarde si ça t'éclaire.
Pour A, comme j'avais du mal à voir où je mettais les pieds, je l'avais prévu pour indiquer les différents types de restitution voulues. Vu que j'ai réussi à le déterminer automatiquement avec les paramètre fournis, pour l'instant c'est 1 partout.

J'ai complété la colonne TKN dans Regroupements mais elle correspond à TONKIN (oui désolée...). J'ai donc plutôt modifié l'intitulé dans S63. Pas d'impact ? On peut donc bien supprimer la colonne TKN de regroupements ?
Oui.
Dans la mesure où chaque secteur est bien marqué dans un libellé de regroupement c'est bon.
Par contre bien faire attention à l'orthographe. Si un secteur est absent je ne peux pas savoir si c'est volontairement ou non.

Petite question : Je vois que dans "données", tu as ajusté la taille du tableau. Cela a un impact ? Puis-je le laisser plus grand (pour laisser les lignes accessibles aux collaborateurs qui vont les remplir) ?
Non, surtout pas. Aucun intérêt avec un tableau structuré comme tu as mis ta plage.
Tu perdrais un de ses avantage qui est de se retailler automatiquement et de travailler sur le strict nécessaire.
Il suffit d'ajouter une ligne pour qu'il s'étende automatiquement. En appliquant formats et formules en plus.
Le tout est de ne jamais sauter une ligne, comme pour toute BdD.
Met-toi en A52, saisi une date et tu verras ton tableau s'agrandir.
C'est pourquoi j'utilises son nom pour pl. Données. Je suis sûr de toutes les avoir, sans à avoir 100 lignes de plus à traiter pour rien.
D'ailleurs je ne me sers dans Données que de A:G.
Toutes les colonnes au-delà peuvent être supprimées si elles ne te servent pas. Toutes les clés et compteurs qu'on avait dû ajouter par exemple.

Explications complémentaires : https://mon-partage.fr/f/zRLWvXbv/
eric
Manonbibli 17 Messages postés mardi 31 juillet 2018Date d'inscription 20 août 2018 Dernière intervention - 17 août 2018 à 16:20
Ah oui, c'est beaucoup plus clair ! Quel travail ! J'espère un jour pouvoir me servir de VBE et des macros de cette manière. :)
Merci pour les explications sur le tableau structuré.

Je teste lundi avec plus de données puis travaillerai ensuite sur la protection des feuilles. :)

Par contre, pour le tableau 3 "bilan par secteur", je ne pense pas avoir besoin de cette méthode, comme il s'agit seulement de calculer le nombre d'accueils de classe effectués. J'ai l'impression que les résultats ne sortent pas alors qu'ils peuvent sortir facilement par des formules (comme il y avait précédemment).
La chose complexe de ce tableau est qu'il obtient des résultats par secteur (et que l'information indiquant que le secteur a participé est dans "Données" à la fois en B et en C).

Un bon week-end.
https://tenor.com/view/merci-gif-9265482
Manon
eriiic 21918 Messages postés mardi 11 septembre 2007Date d'inscriptionContributeurStatut 21 août 2018 Dernière intervention - 17 août 2018 à 18:53
puis travaillerai ensuite sur la protection des feuilles. :)
Il vaut mieux les protéger par macro à l'ouverture en ajoutant le paramètre UserInterfaceOnly:=True
Seul l'utilisateur sera bloqué.
Sinon les macros seront bloquées aussi et il faudra à chaque fois déprotéger et reprotéger la feuille utilisée.
Ca peut te donner l'occasion de faire ta 1ère macro ;-)
eric
Manonbibli 17 Messages postés mardi 31 juillet 2018Date d'inscription 20 août 2018 Dernière intervention - 20 août 2018 à 15:51
Bonjour Eric,

J'ai complété le tableau avec trois fois plus de données. Si tu as encore le courage de retourner sur ce tableau, j'ai repéré deux soucis qui faussent les résultats mais ne sait comment les corriger (tout le reste a l'air de marcher ! :) ).

J'ai donc surligné en rouge les cellules à problèmes et inséré en commentaires le résultat normalement à obtenir.

1) Dans les deux tableaux "bilans par quartier", les chiffres n'apparaissent pas dans la partie "hors Villeurbanne" (lignes 87,88,89).

2) J'ai l'impression qu'il y a un souci dans les différents tableaux dans le calcul du nombre d'enfants accueillis.
Exemple le plus flagrant en D35 puisqu'il s'agit d'une somme d'enfants accueillis (avec un nombre de classes correspondant au nombre d'accueils de classe) mais le résultat n'est pas le bon.

Serait-il possible que quand il y a trois fois la même classe accueillie, avec un nombre d'enfants différents, ne soit pris en compte que le plus grand nombre (dans totaux avec clefs, il s'agit d'une moyenne et dans les résultats sortis par macro, cela prend en compte le plus petit).
Exemple simple en I64. Ou encore en H32 ou F22.

https://www.cjoint.com/c/HHunQWoaMkw

Sinon, j'ai réussi à créer ma première macro ! (Bon, j'ai mis du temps et merci youtube). Je peux verrouiller l'onglet Bilans en laissant accessible le bouton maj. :)

Merci d'avance et une bonne fin de journée,
Manon
Commenter la réponse de eriiic