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

Calculer l’occurrence de deux données (pas somme) Calculer occurrence entre 2 valeurs » Forum - Excel Comment trouver le pourcentage entre 2 nombre [Résolu] (Résolu) » Forum - Bureautique Calculer la moyenne mensuelle de données par jour sur excel [Résolu] (Résolu) » Forum - Excel Récupérer deux données d'un fichier json (Résolu) » Forum - Javascript Calculer l 'ecart de 2 valeurs changeantes du positif au negatif [Résolu] (Résolu) » Forum - Excel

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.

Forum

Calculer l’occurrence de deux données (pas somme) Calculer occurrence entre 2 valeurs » Forum - Excel Comment trouver le pourcentage entre 2 nombre [Résolu] (Résolu) » Forum - Bureautique Calculer la moyenne mensuelle de données par jour sur excel [Résolu] (Résolu) » Forum - Excel Récupérer deux données d'un fichier json (Résolu) » Forum - Javascript Calculer l 'ecart de 2 valeurs changeantes du positif au negatif [Résolu] (Résolu) » Forum - Excel

Web: www.shapebootstrap.net

51 réponses

Marsh

NOVEMBER 9, 2013 AT 9:15 PM

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 2

Avec quelques mots c'est encore mieux Ajouter un commentaire

CCM a aidé 26420 internautes ce mois-ci

Reply
réponses:
  • Manonbibli

    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

    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

    D'accord merci. Et avec ce tableau ? https://www.cjoint.com/c/HHbjxntdTow

Marsh

NOVEMBER 9, 2013 AT 9:15 PM

Bonjour,

Merci de déposer le fichier xls sur cjoint.com et coller ici le lien fourni.
eric

Reply
réponses:
  • Manonbibli

    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.

Marsh

NOVEMBER 9, 2013 AT 9:15 PM

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

Reply
réponses:
  • auteur

  • Manonbibli

    Un grand merci Eric ! Je ne connaissais pas le système de champs clés et champs compteur donc je vais prendre le temps de me renseigner, puis regarder de plus près les formules que tu as créées.
    Bonne soirée.
    Manon

  • eriiic

    le principe global est d'attribuer une note égale à l'inverse du nombre de présence de la clé.
    Par exemple "passerelle_DUPONT Fred" est présent 3 fois, chacune de ses lignes reçoit 1/3.
    La somme de "passerelle_DUPONT Fred"fera 1 qq soit son nombre de lignes.

  • Manonbibli

    Ingénieux !!! Je me penche sur mon tableau (encore plus complexe puisque va s'ajouter, si j'y arrive, la donnée "niveau scolaire" - ex : "combien d'enfants de maternelle accueillis sur le réseau") dans les jours à venir et reviendrai vers toi pour te parler du résultat ! :)

  • Manonbibli

    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

  • eriiic

    Bonjour,

    impossible de répondre à main levée, il faut le fichier avec des explications précises.
    Met tous les différents compteurs que tu as besoin. Certains pouvant être éventuellement regroupés à la conception. Revenir dessus ensuite est plutôt complexe.
    eric

  • Manonbibli

    https://www.cjoint.com/c/HHdn5ISnKfR
    Pour plus de simplicité, je mets donc en accès le document sur lequel je travaille, avec les bons codes utilisés. J'ai donc résumé dans la partie "bilan" le fonctionnement, en espérant que ce soit compréhensible.
    Les cellules en jaune sont celles avec déjà les bonnes formules.
    Celles en rouge celles dont je cherche la formule pour pouvoir la reproduire dans les autres cellules (qui marquent toutes REF car j'ai supprimé un onglet servant l'année précédente pour tout regrouper dans le même et n'ai pas encore repris ces formules).
    Je viens d'ailleurs de me rendre compte que la formule SOMME-SI donc je parle juste avant a en fait l'air de ne pas fonctionner...
    Merci à toi si tu as le temps de jeter un coup d'oeil !
    Manon

  • eriiic

    C'est le grand foutoir ton truc. Il faudrait essayer d'y trouver un semblant d'ordre pour y arriver, une chatte n'y retrouverait pas ses petits.
    Ca risque de prendre un peu de temps mais il faut bien arriver à faire le ménage là dedans sinon au premier grain de sable tout se coincera..

    Je pense qu'il faut abandonner les formules plutôt dures à mettre au point (et quasi impossible à faire évoluer) et s'orienter vers des fonctions personnalisées.
    Est-ce que :
    1) "Réseau des médiathèques*", "Accueils menés seulement par le Rize (hors médiathèque), "FDL",
    et "MLIS", "RIZE", "TONKIN", "PAC-BUS", "FDL"
    peuvent être définis par une liste précise de Secteurs organisateurs (présent dans Données) ?

    2) idem pour Maternelle Elémentaire Ecole (Mat+Element) Collège Lycée
    qui seraient une sélection parmi les Niveaux de classe (présent dans Données) ?

    3) on est bien d'accord qu'une classe est définie de façon unique par un Enseignant référent, qui ne peut donc pas en avoir 2

    4) quid des quartiers évoqués à moment donné ?

    En résumé peux-tu remplir les tableaux dans la feuille Regroupements ?
    https://www.cjoint.com/c/HHdrUQACdTM
    On doit pouvoir, à partir des Données, classer chaque ligne dans un regroupement de tes Bilans.
    Si on y arrive tes formules se résumeront à qq chose comme : =nbClasse(A3;C2)

    Et il faut que les objets portent le même nom partout. RIZE MEDIATHEQUE ou RIZE MED mais pas un coup l'un, un coup l'autre.
    Et si tu pouvais me rappeler le distinguo entre 'Classes accueillies' et 'Accueils de classe effectués' stp ?
    J'ai un peu la flemme de tout relire ou d'éplucher mes formules pour retrouver...
    eric

  • Manonbibli

    Bonjour Eric.

    J'avais opté pour un système de listes ; à voir effectivement vers quoi peut mener la feuille Regroupements. Je l'ai complétée, ce qui répond il me semble aux questions 1 et 2. Je ne visualise pas encore vraiment ce que tu appelle "formule personnalisée". Je bloque toujours avec la façon d'avoir le nombre de classes accueillies (et non d'accueils).
    3) Je te confirme qu'une classe est définie de façon unique par un enseignant. C'est donc bien la colonne F qui peut permettre la distinction entre nombre d'accueils de classe et nombre de classes accueillies. Il en va de même avec les enfants, si la classe du même enseignant vient trois trois, c'est que ce sont les mêmes enfants (on n'a donc pas touché de nouveaux enfants, donc on ne comptabilise qu'une fois et non 3).
    4) J'ai complété aussi dans la feuille "regroupements" les quartiers et leurs établissements scolaires associés.

    Nouvelle version ici
    https://www.cjoint.com/c/HHem1DmrQSP

    Concernant le distinguo entre "nombre de classes accueillies" et "nombre d'accueils de classe", en espérant être claire :
    - Nombre d’accueils de classe effectués : La même classe (avec le même enseignant référent) vient trois fois à la bibliothèque et sera comptabilisé trois fois (soit sur excel, compter trois lignes, car trois accueils organisés par les bibliothécaires).

    - Nombre de classes accueillies : La même classe (avec le même enseignant référent) vient trois fois à la bibliothèque mais n’est compté qu’une fois (car pas de nouvelle classe touchée).

    Nombre d'enfants accueillis : Relatif au nombre de classes accueillies. Ex : 25 enfants ayant été accueillis trois fois compteront pour 25.
    Si la même classe des mêmes 25 élèves vient trois fois, on compte donc dans "nombre d'enfants accueillis" 25 et non 3 fois 25.

    Concernant les noms des objets, j'ai juste trouvé 1 différence, dans les titres du tableau "bilan par secteur". J'avais précisé en toute lettre afin que les collègues comprennent bien. Mais dans le doute, j'ai tout indiqué avec le même nom raccourci qui sert aux formules.

    En espérant avoir été claire ! Et merci du coup de main !
    Manon

  • eriiic

    Bonjour,

    j'ai jeté un oeil vite fait.
    Les totaux en H ne sont pas =somme(C:G) ?
    Si tu as le courage, avoir les résultats du 1er tableau me permettrai de voir au fur et à mesure si je suis dans les rails ou pas.
    eric

  • Manonbibli

    Bonjour Eric,
    J'ai modifié les totaux en H. Ils sont la somme (E:G). E étant lui-meme la somme de C et D.
    J'ai laissé les totaux surlignés en jaune et les ai décalé en colonne I. Ceux-ci sont normalement justes et calculés avec le système de clefs.
    https://www.cjoint.com/c/HHfiToYToPP

    Une bonne journée,
    Manon

  • eriiic

    Bonjour,

    Totaux avec clé est bien ce que je dois obtenir dans Totaux ?
    VBA est très rigide. Il ne faut plus changer ta demande et modifier ou insérer des colonnes

    J'aurais voulu le détail des colonnes précédentes.
    Et si tu pouvais mettre un peu plus de lignes de données pour faire ressortir les erreurs tout de suite si j'ai pris une mauvaise piste.
    Ca fera moins de débogage laborieux ensuite, et moins de travail pour toi à expliquer ce qui est faux, pourquoi, et comment l'obtenir.
    Je ne te cache pas que c'est assez lourd et qu'il faudra un peu de temps.
    eric

  • Manonbibli

    Bonjour Eric,
    J'ai complété le détail des colonnes précédentes du tableau (pardon, je n'avais pas compris la première fois). Je pourrai faire dès que nécessaire le même travail pour le tableau 2.
    Dans la colonne H Totaux, on obtiendra le même résultat en additionnant les trois cellules de gauche qu'en utilisant les clefs. Donc peu importe, on peut effectivement garder la colonne H et supprimer les données présentes de I3 à I33 (mais cela m'a permis de vérifier mes sommes pour remplir manuellement les cellules bilans :) )
    Contrairement à la ligne 12 TOTAL où le référentiel n'est pas le même.

    Pour info, les calculs dans les bilans "nombre d'accueils de classe effectués" sont justes. Les formules fonctionnent car il s'agit de simples sommes.

    Et petit détail concernant le calcul du nombre d'enfants, j'ai indiqué pour chaque passage de la même classe le même nombre d'enfants. En général, c'est le cas et je me suis dis que cela simplifierait les calculs et leur vérification. Dans la pratique, il se peut qu'une classe revienne et qu'il y ait 5 enfants de moins. Idéalement, le calcul du nombre d'enfants se fait alors sur le plus grand nombre (et non pas sur la moyenne).
    Ex : 1er accueil de la classe de Mr UN : 25 élèves
    2ème accueil de la classe de Mr UN : 25 élèves
    3ème accueil de la classe de Mr UN : 20 élèves
    Soit 2 accueils de classe, 1 classe accueillie et 25 élèves touchés.
    Si cela complique le calcul, on changera nos pratiques pour indiquer toujours le même nombre d'enfants.

    Quand tu m'indiques que le tableau sera rigide, il sera bien possible de modifier facilement les listes servant aux listes fermées dans Données ? Je pense notamment à la liste "établissements scolaires" qui évolue année après année.

    Nouvelle version : https://www.cjoint.com/c/HHgn1Hqwyww

    En te remerciant du temps que tu consacres à ce tableau !!
    Bonne fin de journée,
    Manon

  • eriiic

    Bonjour,

    non, ce n'est pas nécessaire pour le tableau 2. Je ferai les tests principaux sur le 1er et des plus légers sur les autres.

    Par contre j'ai vu ensuite que tu avais différentes structures de tableaux.
    Les lignes 47:48 sont-elles correctes ?
    Et si tu pouvais faire lignes 60:65 colonnes C et Q stp

    petit détail concernant le calcul du nombre d'enfants
    si seulement c'était la seule difficulté :-) Non, ça ne posera pas de problème.
    Si tu vois d'autres détails ne tarde pas trop...

    Quand tu m'indiques que le tableau sera rigide, il sera bien possible de modifier facilement les listes servant aux listes fermées dans Données ? Je pense notamment à la liste "établissements scolaires" qui évolue année après année.
    oui, tu pourras. Il faudra juste mettre à jour les regroupements.
    Bon, j'ai quand même bossé dessus et après avoir exploré différentes pistes je crains que les fonctions personnalisées ne restent encore lourdes à utiliser vu ta demande et les tableaux qui sont variés.
    Ca sera plutôt une zone de définition des tableaux où tu pourras mettre facilement leurs caractéristiques, et un bouton pour rafraichir.
    J'espère que tu n'en as pas besoin pour dans 3 jours, je ne veux pas risquer une surchauffe du cerveau ;-)
    eric

    PS : quand tu supprimes des lignes d'un tableau structuré n'utilise pas la touche Suppr.
    Sélectionnne les lignes (une colonne suffit) et clic-droit Supprimer / Lignes de tableau.
    Sinon tu perds l'avantage de son adaptation à la taille des données. Et travailler sur 300 lignes au lieu de 50 est très pénalisant pour excel.

  • Manonbibli

    Bonjour Eric.
    Prends le temps qu'il te faudra, je voudrais éviter toute surchauffe de cerveau, surtout par ces fortes chaleurs ! ^^ Idéalement bien sûr, j'en aurais besoin vers le 23 août, afin que les collègues puissent y accéder au moment de la pré-rentrée, date de départ des inscriptions des enseignants. :) Sinon, je m'adapterai.

    Voici la nouvelle version https://www.cjoint.com/c/HHhiwHJuOLw

    Effectivement, le tableau "Bilan par secteur", l 36:48 a une structure différente... car je compliquerais encore la donne en demandant le nombre de classes et d'enfants accueillis par secteur, sachant que ces données se trouvent dans deux colonnes dans la feuille Données, B et C (car des accueils au sein de l'établissement MLIS sont effectués par deux secteurs distincts de manière simultanée). Bref, c'est bien comme cela !

    Je ne vois pas d'autres détails. J'ai rajouté une colonne en J dans Données afin de ne pas avoir à le faire après, vu que cela peut compliquer la donne. J'ai revérifié les chiffres des tableaux bilans : ok donc pour les lignes 47;48 et celles du tableau "bilan par quartier". J'ai fait les lignes 60 à 65 (entièrement même). N'hésite pas s'il y a besoin de plus.

    Merci pour le conseil pratique en "PS" ; j'ignorais ce détail !
    Un grand merci.
    Manon

  • eriiic

    Bonjour,

    Je n'ai pas pu m'y remettre pour cause d'occupations personnelles prioritaires qui me prennent pas mal de temps.
    Et ton truc ce n'est pas le genre où l'on peut avancer avec 1/4h-1/2h dispo par ci par là...
    Alors j'essaie de redémarrer dessus mais un peu dur de se ré-approprier l'ensemble.
    Quoiqu'il en soit j'essaie de te fournir un truc pour début semaine prochaine (sauf accident ou gros blocage dans les calculs que je n'ai pas vu venir), que tu aies le temps de tester. Et qu'il en reste pour déboguer si besoin.
    Désolé du contre temps
    eric

  • Manonbibli

    Bonjour Éric. Il n’y a pas de souci, vraiment. Déjà un grand merci pour tout le temps que tu as consacré à ce tableau !
    Bonne journée à toi.
    Manon

  • eriiic

    Bonjour,

    des news.... c'est l'horreur ton truc ;-)
    J'ai recommencé à zéro 4 fois parce qu'on du mal à savoir par quel bout c'est mieux de le prendre.
    Et c'est la 4e fois que je me suis aperçu que tu avais inversé les explications entre Classes accueillies et Accueils de classe effectués par rapport à l'ordre des résultats sur le tableau. Tu m'étonnes que j'avais des écarts.
    Voilà ce qui arrive quand on lit en diagonale...
    Pour l'instant c'est ok pour les tableaux 1 et 2.
    Demain je verrai pour 3 et peut-être 4. Mais bon, on sature vite avec tes tableaux, et je viens de voir que le 5 avait 2 regroupements différents sur les colonnes. C'était trop simple jusqu'à maintenant ? ;-)
    En résumé : patience
    eric

  • Manonbibli

    Bonjour Éric.
    Oups, désolée pour les explications inversées.
    Je n’aurais pas cru en posant ma demande au tout début que les résultats que je souhaitais obtenir étaient si compliqués à mettre en place ! :s
    Si c’est impossible d’ici la fin de semaine prochaine, tant pis, je peux encore le rendre opérationnel début septembre! Aucun souci ! Et dans tous les cas, je m’adapterai aux améliorations que tu auras pu apportées, qui sont déjà des plus par rapport à ma version initiale.

    Un grand merci en tout cas de passer tout ce temps sur mon tableau.
    Bonne journée,
    Manon

  • eriiic

    Bonjour,

    Bonne nouvelle, après un accouchement laborieux le bébé est né.
    Tes 4 premiers tableaux sortent correctement. Le 5e, vu que ce sont des formules simples, autant le garder comme ça.
    Par contre il faut que je nettoie le code et que je te fasse un modop. Mais là je sature un peu, demain sans doute.
    J'ai dû nettoyer le classeur à différents endroits car il commençait à boguer sans raison. Il affichait le sablier comme s'il recalculait, et basculait tout seul entre les fenêtres du classeur et l'éditeur VBA et repartait avec le sablier.... Impossible de travailler dessus dans ces conditions.
    Du coup tu auras certaines de tes formules à reprendre.

    Peut-être est-ce dû à la maj d'Office faite aujourd'hui (?).
    Ah au fait ! Evite les validations de données sur colonnes entières. Pas nécessaire comme tu as mis un tableau structuré.
    eric

  • eriiic

    Suite... : https://mon-partage.fr/f/VRYQsjmH/
    Si tu as d'anciens résultats pour contrôler de façon plus large.
    C'est plus facile à déboguer quand c'est frais ;-)
    eric

  • Manonbibli

    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

    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

    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

    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

    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

  • eriiic

    Bonjour,

    Je regarde ça ce soir.
    A-priori toutes les erreurs concerne les enfants accueillis, donc si je trouve la faute j'espère qu'elle sera unique et que tout se corrigera.
    Merci d'avoir bien décortiqué, je vais chercher avec D35. C'est agréable de travailler pour toi, tu anticipes et tu fais bien ta part ;-)
    Je ne te cache pas que ça risque d'être long. Tes modes de calcul ne sont pas instinctifs.

    Pour Hors Villeurbanne ça ne m'étonne pas outre mesure. Il m'a enquiquiné tout le long, j'étais obligé d'ajouter des traitements spéciaux rien que pour lui.
    Il présente 2 cas particuliers à lui tout seul : Etablissement et quartier ont même libellé, et c'est un quartier de 1 étab.
    Ca parait anodin mais ça fait bien iech... Le diable est dans les détails :-)
    Est-ce qu'on peut envisager de renommer le quartier ? "Hors Villeurbanne (Quartier)" par exemple.

    Et si tu peux jeter un oeil de temps stp. Si jamais j'ai une question que je ne reste pas bloqué trop longtemps...
    eric

    PS : peut-on supprimer les clés Données!O:V où tu t'en sers encore ?

    PS2 : vu l'ano. En fait je prend le nombre d'élève maxi du prof, mais sur toute l'année, sans tenir compte du filtre choisi.
    D'ailleurs c'est bizarre, j'aurais même dû annoncer 346 et non 345 puisque que c'est QUARENTEHUIT (avec un A stp ;-) )avec 25 ou 23 éléves. Je contrôlerai ça aussi
    Que faut-il faire ?
    1) rester avec l'effectif max annuel ? soit 25
    2) calculer l'effectif max selon les filtres actifs ? soit 23
    .

  • eriiic

    PS3 : j'ai trouvé pour le delta de 1. Un copié-collé que j'ai oublié d'adapter.
    Tous les autres se sont corrigés.
    Reste à savoir si tu veux 346 ou 344 en D35

    Demain je verrais pour Hors Villeurbanne

  • Manonbibli

    Bonjour Eric.

    C'est bien normal de faire ma part, tu m'apportes déjà une aide considérable ! :o

    Aucun problème pour renommer "Hors Villeurbanne (Quartier)", bien sûr. Si cela peut éviter des galères Excel.

    Tu penses que les clefs Données alourdissent le tableau ? Dans ce cas là, oui, on peut les supprimer. Elles ont été bien utiles pour trouver les erreurs mais maintenant que les chiffres sont bons, on peut effectivement s'en passer.

    QuarAnte ! Oups..

    Pour le nombre d'enfants accueillis, il faudrait idéalement calculer l'effectif max selon les filtres actifs (soit 23).
    Donc 344 en D35.

    Merci et bonne journée,
    Manon

  • eriiic

    Bonjour,

    ça ne se présente pas bien. La structure actuelle du programme n'est pas vraiment adaptée pour ce type de modif.
    Il va sans doute falloir que je remette tout à plat pour ne pas avoir un truc trop horrible.
    Reste à trouver la motivation et le temps... ;-)
    eric

  • Manonbibli

    Aïe. Pas cool... Prends le temps qu’il te faudra sans souci. Je comprends que tu n’aies pas que ça à faire et je ne voudrais pas que tu rêves uniquement de ce tableau Excel ! ^^
    Un grand merci.
    Manon

  • eriiic

    Bonjour,

    pfiou, pas faciles les contrôles. 142 lignes balayées 15 fois avec des filtres différents à chaque fois ça occupe...
    Bon, je viens de passer un bug qui m'a occupé la semaine mais j'ai encore une divergence.

    Par exemple 'Réseau des médiathèques', 'Lycée', 'Enfants accueillis' : tu trouves 56, moi 69.

    22/09/2017	RIZE MED		Lycée Marie Curie	1° pro	SEPT	28
    06/11/2017 RIZE MED Lycée Marie Curie 1° pro SEPT 15

    J'avais cru comprendre que tu voulais le max élèves et tu prends le min.
    Si tu pouvais m'éclairer stp.
    eric

  • Manonbibli

    Bonjour Eric.
    Effectivement cela ne doit pas être évident.... je ne sais comment te remercier du temps que tu consacres à ce tableau !
    Je te confirme qu'il s'agit bien du maximum d'élèves qui doit apparaître dans la cellule, soit bien 69 (30+28+11).

    Les données qui apparaissent actuellement dans les tableaux bilans sont celles générées automatiquement par les formules. J'avais juste indiqué dans quelques cellules que j'avais surlignées en rouge et en commentaires le chiffre qui était censé apparaître, afin que tu t'en serves de modèle, mais effectivement, le problème de calcul concernant le nombre d'enfants accueillis apparaît dans toutes les cellules des bilans concernant "enfants accueillis" (je n'ai pas tout repris car je pensais que quelques exemples permettraient de comprendre l'erreur et de la résoudre pour l'ensemble des tableaux).

    En espérant t'avoir aidé,
    Je me tiens à ta disposition si besoin,
    Bonne soirée,
    Manon

  • eriiic

    Ah ok.
    Du coup je ne sais pas si les écarts qui restent sont normaux ou pas.
    Ca tombe bien, je sature au niveau des contrôles. A toi de jouer ;-)
    C'est un peu long pour avoir le résultat. Il faudrait que j'en reprenne une partie mais refaire une 6 fois ne me tente guère. Au moindre bogue je repars pour 1/2h de tests et contrôles :-s

    Cette version n'est pas la définitive, mais te permet de commencer les contrôles
    Encore un truc à régler, je nettoie un peu trop fort le dernier tableau et supprime tes formules. Je modifierai plus tard pour ne nettoyer qu'une colonne sur 3.
    On va déjà déboguer.
    https://www.cjoint.com/c/HIhvyMVf1wM
    eric

  • Manonbibli

    Bonjour Éric. Ça marche, je m’en occupe mardi, je ne pourrai pas avant malheureusement.
    Bonne journée !

  • eriiic

    Bonjour,

    la version qui récure un peu moins dans les coins.
    Tu peux remettre tes formules dans le 4ème tableau.
    https://www.cjoint.com/c/HIkwhjbYKRM
    eric

  • Manonbibli

    Bonjour Eric,

    Ça y est, j'ai complété les modèles des 3 tableaux avec les données censées sortir et... tout est juste ! Ouaaaah. :)

    J'ai supprimé les colonnes "clefs" des données et des deux tableaux bilans. Elles ne servent définitivement plus.

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

    Ça veut dire que tout est bon ?! :)

    Bonne fin de journée
    Manon

  • eriiic

    Et bien c'est super :-)
    Il reste bien qq lignes de code mise en commentaire au cas où il faille déboguer qu'on pourrait supprimer, mais vu qu'elles sont inactives on ne va pas faire une version juste pour ça.
    Donc oui, j'avais ajouté le dernier bout manquant et tout est complet :-)
    Tu peux aussi masquer la feuille 'tmp'.
    Bon courage.
    eric

  • Manonbibli

    Bonjour Eric,
    Un grand merci d'avoir pris autant de temps durant ce mois et demi pour travailler sur mon tableau. Ce qui était une "simple" question Excel de ma part pour améliorer mon tableau de travail s'est transformé en chantier de fonds pour lui refaire une beauté ! :) Quel travail !

    Il va être mis en service dès la semaine prochaine !

    Peux-tu me préciser à quoi correspond la feuille "tmp" et pourquoi elle est indispensable ?

    Et on est bien d'accord que dans l'onglet "Regroupements", je pourrai rajouter si besoin l'année prochaine des niveaux de classe ou des établissements scolaires, ils seront pris en compte sans encombre ?

    Encore merci à toi,

    Bonne journée,

    Manon

  • eriiic

    Bonjour,

    pour simplifier et avoir moins de lignes de programmes je filtre ta bdd sur le champ (ou regroupement, par exemple Maternelle) de la colonne du tableau, que j'extrais dans tmp. Puis je passe à la suivante.
    Sur cette bdd restreinte je filtre successivement sur les lignes du tableau (ex Réseau des médiathèques).
    Ce qui me permet d'avoir ton max élève et les autres chiffres avec moins de prises de tête.

    Tu peux mettre cette feuille en xlSheetVeryHidden. Comme ça elle n'apparaîtra pas dans 'Afficher...', ça fera comme si elle n'existait pas pour les utilisateurs.

    Et on est bien d'accord que dans l'onglet "Regroupements", je pourrai rajouter si besoin l'année prochaine des niveaux de classe ou des établissements scolaires, ils seront pris en compte sans encombre ?
    Théoriquement oui, tu pourras même en créer de nouveaux si besoin.
    Le tout est de bien respecter l'équivalence des libellés lignes/colonnes de tes tableaux, et du libellé du champ lié dans ta bdd.
    Quand tu auras 5 min, par sécurité tu peux te faire un test bidon pour voir s'il réagit bien comme tu l'entends.
    Autant battre le fer tant qu'il est chaud ;-) Parce que s'il faut faire une adaptation dans un an...
    eric

  • Manonbibli

    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

    Bonjour,

    je regarde ça ce we. A force de refaire, possible que j'ai omis un truc :-)
    A+
    eric

  • eriiic

    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

    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

    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

Leave a Replay

Make sure you enter the(*)required information where indicate.HTML code is not allowed