Choix de formule pour somme compliquée

Bonjour,

Après avoir passé quelques heures à essayer de trouver une formule pouvant répondre à mon problème je me suis dis qu'il vallait mieux venir sur un site comme celui-ci pour y trouver ma réponse.

Explication:

Sur une feuille Excel, j'ai deux tableaux (que je ne peux pas joindre malheureusement), dans le premier j'ai deux entrées: - vertical correspondant au Mois

- horizontal correspondant à des secteurs

Dans un premier temps, les cases de résultats de ces deux entrées contiennent des noms de personnes qui reviennent plusieurs fois dans le tableaux mais toujours une fois par mois et par secteur. Une fois que la personne a réalisé son travail, un pourcentage est entré à la place de son nom. Voilà pour le premier tableau.

Le second tableau comporte . deux entrées horizontales: - la première: objectif fixé

- la seconde: objectif atteint

. une entrée verticale correspondant au nom de chaque personne nommée dans le premier tableau.

C'est ce second tableau qui me pose problème car je dois automatisé son remplissage à partir de ce que je rempli dans le premier tableau.

Pour la ligne objectif atteint dès que le nom de la personne concerné est remplacée par un pourcentage, la case doit ajouter 1. Exemple: si sur 3 mois il y a 2 pourcentage dans la case objectif atteint pour la personne concernée, on aura 2 etc... le maximum sera donc 12 si son nom apparaît chaque mois. C'est dont ceci dans un premier temps que je n'arrive pas à faire.

Ensuite, pour la ligne objectif fixé, on doit ajouter 1 dès que le nom de la personne concerné est présent dans le mois actuel. Par exemple, monsieur X à son nom en Février, Mars et Avril, son objectif fixé sera de 2 car en janvier il n'avait pas son nom et nous ne sommes pas encore au mois d'Avril. Je dois donc trouver un moyen pour que dès que le mois suivant arrive et que je commence à renseigner mon premier tableau, mes objectifs augmentent, s'ils le doivent, de 1 automatiquement. J'ai donc utilisé la formule SOMME SI mais je ne pense pas que ce soit la bonne car dans la case résultat on ne peux pas augmenter seulement de 1 à chaque fois. J'ai également essayé de trouver un système avec les couleurs car quand on a le nom de la personne c'est rouge et quand on a mis le pourcentage c'est vert, je voulais donc trouver une formule qui pourrait ajouter 1 dans la case objectif fixé lorsque l'on colore la case en rouge ou en vert.

Sinon j'avais un autre soucis moins embêtant je pense, lorsque l'on renseigne une case faisant référence à une autre, peut-on faire un tire global pour un tableau entier afin d'éviter de faire ça case par case?

Voilà, je vous remercie d'avance pour les personnes qui tenteront de m'aider et j'espère avoir été assez claire.

Bonne journée

Sylvain

hello

mais si, on a besoin de ton fichier. Tu le vides des données confidnetielles, tu le zippes et ça ira

à te lire

Voici le document purgé et simplifié en espérant que cela vous aide.

https://www.excel-pratique.com/~files/doc/Sylvain_audit_2008.xls

Bonjour Sylvain et Salut Jmd,

Je retourne ton tableau avec une copie du tableau initial pour la saisie des objectifs. Car, si on saisit les objectifs sur le même tableau, on perd les références des personnes qui réalisent l'objectif. Pour calculer les statistiques, ce n'est pas pratique. C'est pourquoi j'ai opté pour un tableau supplémentaire. Jete un coup d'oeil si cette solution te va.

Fichier Joint

Voici le fonctionnement du tableau.

J'ai nommé les plages dynamiquement (en fonction du mois en cours calculé par la fonction AUJOURDHUI() ) du 1er tableau (objectif) et 2è tableau(réalisation).

Par conséquent, la plage comprend la première cellule jusqu'à la dernier cellule du mois en cours-1. Par exemple, la plage du tableau objectif(nommée "objectif") s'étend de C3 à D20 car nous sommes en mars. Si nous étions en avril, elle sera de C3 à E20.

J'ai également mis un format conditionnel dans ces cellules pour être remplies en rouge(si nous sommes en mars, on prend les cellules de janvier et février etc.) et pour être remplies en vert s'il y une saisie d'objectif.

Et enfin, je compte les nombres de chaque individu dans le tableau objectif afin de calculer le nombre d'objectif prévu pour chacun. Pour la réalisation, je calcule le nombre d'objectif (nb de références) de chaque individu qui reste dans le tableau de réalisation et je le retranche de ce que j'ai calculé initialement avec le tableau objectif.

Cdlt,

Tout d'abord, merci pour la rapidité des réponses.

J'ai regardé et analysé le fichier que tu m'as mis mais j'ai plusieurs remarques:

• pour que mon objectif fixé augmente je suis obligé d'augmenter la plage? idem pour objectif réalisé?
• comment as tu fait pour faire le changement de couleur quand on passe de x à un nombre?
• lorsque j'augmente mon objectif fixé, mon objectif réalisé augmente tout seul.
• peut-on remplir le premier tableau et faire que l'autre soit rempli également sans perturber les calculs pour les objectifs réalisés.

Merci

Sylvain

Re,

1) Les plages du tableau objectif et du tableau réalisation sont liées au mois en cours. Ce sont des plages DYNAMIQUES. Pourquoi veux-tu les augmenter ? Pour un test ? Dans ce cas tu peux changer le mois en double-cliquant sur l'heure et tester. Si besoin est utilise la touche F9 pour recalculer.

2) C'est un format conditionnel qui fait cela. C'est à dire dans la cellule C23, il y a un format conditonnel qui est géré par cette formule : =C23<>C3 met la couleur verte.(cf Format/Mise en forme conditionnel/Condition 1)

3) C'est normal, car il n'y a pas de liaison entre le tableau objectif et réalisation. Si tu veux la liaison, la saisie des objectifs ne pourra pas effectuer dans le tableau réalisation. Il faut savoir ce que tu veux.

Si tu veux préparer et paramétrer ton vrai tableau, on pourra t'aider. En tout cas pour arriver à ça il faut appliquer une logique tout en respectant les contraintes. Dans mon précédent post, je t'ai tout de même donné la méthode pour y arriver. Si tu veux plus d'explications, n'hésite pas à demander.

Cdlt,

Re,

Je ne veux pas modifier la plage mais je ne sais pas comment elle fonctionne! Du coup je pensais que pour passer au mois suivant il fallait agrandir la plage en incluant le nouveau mois.

Si je comprends bien (ce qui n'est pas sûr) le mois de mars sera comptabiliser quand nous serons le 31 mars? car moi il faudrait que le mois soit comptabiliser vers le 15 et j'ai trifouillé avec le F9 mais j'ai pas réussi.

Ok pour la couleur.

Et pour la dernière réponse, je voulais juste savoir s'il y avait un moyen de faire une seule manip pour renseigner les deux tableaux.

Mais par rapport au fait que l'objectif réalisé se rempli tout seul quand on augmente l'objectif fixé ceci n'est pas bon car si le mois suivant le bonhomme n'est pas inscrit le résultat sera faux voilà ce que je voulait dire tout à l'heure.

Merci encore

Sylvain

Re,

1) Non. Quand nous sommes le 1er Avril le mois de mars sera inclus dans les plages de deux tableaux. En tous cas, c'est ce qui est prévu actuellement car l'interpretation de ta première demande s'y prète à cette solution. On peut bien sûr prévoir l'inclusion d'un mois à la fin de ce mois. Il suffit de changer le règle de jeu.

2) Je repète que nous ne pouvons pas garder une liaison permanente si nous saisissons sur le tableau de destination. Par contre, rien ne t'empêche de saisir sur 2 cellules les mêmes données en même temps. Pour ça utilise cette méthode.

Sélectionne tes cellules à saisir en même temps (Astuce => la touche CTRL enfoncée permet de sélectionner les cellules ou blocs de cellules non adjacentes). Sur la cellule active saisi la donnée et valide par les touches CTRL + ENTREE.

En ce qui concerne ta dernière remarque, je ne saisis pas bien le sens.

Cdlt,

Ok ok

Une dernière question et après je serais prêt à attaquer mon fichier.

Je voudrais juste savoir comment tu créer ta plage dynamique qui évolue avec les dates car je vais en avoir besoin pour le refaire dans mon vrai fichier excel.

Et enfin avec ton outil condition dans format peut on faire évoluer la couleur d'une case quand le pourcentage dans une autre case varie?

Par exemple, dans la case ou il y a mon pourcentage je marque 10% et mon autre case devient rouge ou si je mets 70% dans la même case "pourcentage" mon autre case devient verte?

Voilà merci et bonne soirée

Sylvain

Re,

J'ai créé deux plages dynamiques de la façon suivante :

Dans le menu Insertion/Nom/Définir

Nom dans le classeur : objectif

Fait référence à : =planning!$C$3:DECALER(planning!$C$3;;;18;MOIS(AUJOURDHUI())-1)

cliquer sur Ajouter

Nom dans le classeur : realisation

Fait référence à : =planning!$C$23:DECALER(planning!$C$23;;;18;MOIS(AUJOURDHUI())-1)

cliquer sur Ajouter

Une précision le chiffre 18 correspond aux 18 lignes du tableau.

Pour la mise en forme d'une cellule par rapport à une cellule de pourcentage suivre la méthode suivante :

A1 = cellule de %

A2 = cellule qui changera la couleur selon le % de la cellule A1

Sélectionner la cellule A2

Menu Format/Mise en forme conditionnel/Condition 1 = La formule est et dans la partie formule => =$A$1=10% cliquer sur le bouton Format et dans l'onglet motif choisir la couleur Rouge.

De la même façon créer une Condition 2 avec la formule =$A$1=70% et mettre un format motif de couleur Verte.

Cdlt,

Bonjour, ce matin j'ai attaqué mon fichier excel et il y a juste une chose que je n'arrive pas à faire. Je voudrais que ma ligne objectif réalisé se remplisse automatiquement en fonction des personnes mais en utilisant une formule qui n'implique pas la ligne objectif fixé car je laisse tombé la mise automatique de cette ligne là.

Du coup, en utilisant la formule NB.SI je mets la plage dynamique que j'ai crée et comme critère je mets le nom de la personne et j'obtiens donc le nombre de fois ou le nom de la personne apparaît dans ma plage. Je voudrais dons si c'est possible une formule ou un calcul qui puisse m'obtenir l'inverse de ce que j'obtiens cad que dans ma plage dès que je remplace un nom par un chiffre l'objectif réalisé passe à 1 voilà merci pour toutes ces infos.

Sylvain