Additionner les termes de plusieurs additions séparément / des chiffres avec texte

[jbdumaine]

Bonjour à toutes et à tous,

J'espère que tous les grands spécialistes d'excel ne sont pas tous partis en vacances et qu'il en restera quelques-uns pour m'éclairer un peu. Voilà les données du problème...
Je suis chef d'établissement adjoint et j'ai besoin de recenser les détails des services réalisés par les professeurs.
Cependant, les collègues n'assurent pas l'intégralité de leur service dans leur discipline pour une classe donnée et peuvent faire autre chose (accompagnement personnalisé, soutien, théâtre, chorale, informatique, etc.).
Aussi dans une cellule, pour un professeur et une classe donnés, je dois rentrer les chiffres sous la forme d'une addition. Par exemple pour un prof X qui donne 5 h de cours et 1 h d'ATP (soutien personnalisé) à la classe de 6e1 et la même chose aux 6e4 , je vais rentrer les données ainsi :
| |Prof X |Prof Y |TOTAL
|6e1 |5h + 1h ATP | |
|6e2 | | |
|6e3 | | |
|6e4 |5h + 1h ATP | |
|6e5 | | |
|Total Cours| 10h | |
|Total ATP | 2h | |
|Divers |2h info | 2h théâtre| 4 h
|TOTAL service|12 h |
1ère question : Quelle formule me permettrait d'obtenir l'addition des termes de gauche de l'addition pour avoir le Total Cours, dans une cellule et l'addition des termes de droite de l'addition pour avoir le Total ATP ?
Pour ne rien arranger, il y aura dans certaines cellules, une seule valeur, celle des heures de cours assurés, puisque tous les profs ne font pas de l'ATP, et des cellules vides puisque 1 professeur n'a pas la charge de toutes les classes (cela arrive parfois, mais c'est exceptionnel !).

Par ailleurs, des profs rendent des services particuliers qui sont recensés à la ligne Divers (club musique, chorale, théâtre, informatique, coordination, etc.)
2nde question : Quelle formule me permettrait d'obtenir le total des services divers sachant que je suis obligé de préciser la nature des services dans chaque cellule, comme dans mon schéma ci-dessus.

Pour être plus clair, je vous envoie mon fichier exemple, tout beau tout propre qui attend vos formules pour vraiment être utile. Je vous remercie à l'avance de toutes vos contributions et, si ce n'est déjà fait, d'excellentes vacances, ensoleillées si possible.

Bien cordialement,

JB DUMAINE

 

[Gurgeh]

Re : Additionner les termes de plusieurs additions séparément / des chiffres avec tex

Bonjour jbdumaine,

Je te propose la formule suivante, qui est certes un peu bourrine, mais qui fonctionne. Il faut toutefois que le texte dans la case concernée respecte une certaine forme du type xh + yh ATP où x et y sont les valeurs que l'on veut récupérer.

Pour récupérer les heures ATP de la cellule C4 :

=SI(ESTERREUR(CHERCHE("h ATP";C4));0;CNUM(STXT(C4;CHERCHE("+";C4)+1;(CHERCHE("h ATP";C4)-CHERCHE("+";C4)-1))))

Pour récupérer les heures de cours de la cellule C4 :

=SI(ESTERREUR(CHERCHE("h";C4));0;CNUM(GAUCHE(C4;CHERCHE("h";C4)-1)))

Dis moi si cela correspond à ton besoin.

Toutefois, je pense qu'on pourrait faire ça de manière plus élégante en saisissant les différentes heures sous forme de tableau type base de données, et en créant ensuite les tableaux croisés dynamiques que l'on veut, je peux détailler cette proposition si ça t'intéresse.

Gurgeh

 

[JBARBE]

Re : Additionner les termes de plusieurs additions séparément / des chiffres avec tex

Bonjour,

J'ai éliminé le texte dans les cellules et changé la présentation du tableau !

 

[jbdumaine]

Re : Additionner les termes de plusieurs additions séparément / des chiffres avec tex

Chers Contributeurs,

Merci d'abord pour vos réponses. En ce qui concerne la proposition de JBARBE, la modification du tableau ne répond pas à mes besoins puisque, ce qui m'intéresse, c'est d'établir le recensement des heures de services par classe et par professeur en trois catégories : heures de cours, ATP, et divers. Ce tableau doit pouvoir me permettre de connaître la ventilation des heures de service et quelques précisions concernant la nature des services effectués.

La proposition de GURGEH est une première étape puisque les formules qu'il me propose permettent d'extraire d'une cellule (C4) les heures de cours, d'une part et les heures d'ATP, d'autre part. Mais il faudrait encore les additionner sur la colonne, pour les heures de cours (en C26) et les heures d'ATP (en C27) et également additionner C26+C27+C28 en C29. Pour finir, il faudrait soustraire C29 à C3 pour déterminer le nombre d'HSA. Tous ces éléments me permettent d'établir et de contrôler le service par prof.
En ce qui concerne les lignes, il faudrait faire de même en ce qui concerne les heures par classe (les additionner) dans les colonnes F G et H, puisque mon tableau exemple va s'étoffer (j'ai une 50aine de profs à rentrer).
Voilà, j'espère avoir été clair.

Si cela peut vous simplifier la tâche, pour l'élaboration des formules, je ne suis pas arrêté sur un mode de saisie en particulier. On peut très bien imaginer, pour la saisie des heures de cours et d'ATP, une entrée du type "5 + 1 ATP".

Pour ce qui est des tableaux, type base de données, et des TCD, si cela peut amener la solution, je veux bien connaître les détail. Comme je suis une chevrette en matière d'Excel, toute connaissance supplémentaire est bonne à prendre. Merci GURGEH.


Merci du temps que vous m'avez tous consacré et de celui que vous allez peut-être encore utiliser.

Bien cordialement,

JB DUMAINE

 

[Gurgeh]

Re : Additionner les termes de plusieurs additions séparément / des chiffres avec tex

Bonjour jbdumaine,

Je détaille ci-dessous ma proposition de travailler plutôt avec une base de données sur laquelle tu peux faire les calculs et recherches que tu veux.

Les données sont rentrées sous forme d'un tableau avec des colonnes qui représentent chacune une information !
* Nom du professeur
* Nom de la classe (pour les heures de cours) ou de l'activité (pour les heures divers)
* Type d'heures (Cours, ATP, Divers, Service)
* Nombre d'heures
Chaque ligne contient une information : tel professeur a fait tant d'heures de tel type dans telle classe
On rentre les informations à la suite les unes des autres, pas d'ordre particulier.
Dans le fichier joint, cette base se trouve dans l'onglet "Base"

Une fois cette base constituée, l'utilisation de la fonction SOMME.SI.ENS te permettra d'obtenir des informations du type "Combien d'heures d'ATP au total ont été effectuées par tel professeur ?" ou "Combien d'heures de cours au total ont été effectuées par tous les professeurs pour telle classe ?", etc.

Pour la syntaxe de la fonction, je te laisse consulter l'aide d'Excel, j'ai mis quelques exemples sur la feuille "Formules" et un exemple de tableau de synthèse (sur lequel je n'ai pas fait d'effort pour la mise en forme) sur la feuille "Exple tableau".

A ta disposition pour toute question complémentaire !!

Gurgeh