Discussion :

[sena1911]

Bonjour à tous,

J'expose mon cas :

j'ai un classeur Excel avec 4 feuilles

1/ Feuille 1 la base de données du personnel de mon entreprise (poste, date d'entrée, nom, prénom, service, site...)
2/ Feuille 2 l'ensemble des formations organisés pour les salariés
3/ Feuille 3 le coût pour chaque session de formation
4/ Feuille 4 la liste du personnel qui ne fait plus partie de l'entreprise

Mon besoin:
- lorsque je remplis dans la feuille 2 le nom du salarié dans une colonne et le prénom du salarié dans une autre colonne que s'affiche dans les colonnes service et site les données correspondante au salarié en fonction de ce qui est renseigné dans la feuille 1. La feuille 1 est remise à jour chaque mois en fonction des entrées sorties du personnel. Les salariés sorties figurent dans la feuille 4 du classeur.

- que pour chaque session de formation le coût soit ventilé sur chaque stagiaire (dans la colonne coût de la feuille 2) qui a suivi cette formation. Cette ventilation de coût doit se faire en fonction du nombre de stagiaire et de la date. Excel doit faire la différence entre une formation qui se déroule à deux dates différentes.

[riaolle]

Bonjour,
J'imagine qu'un Nom Prénom est unique. Il faudrait ajouter une colonne en début de tableau qui concatène le nom et le prénom. Ensuite avec RECHERCHEV, tu peux obtenir les valeurs des cellules des autres colonnes pour la même ligne.
Exemple :

Pour la deuxième question, j'ai du mal à voir ce que tu veux Est-ce qu'un tableau croisé dynamique fait l'affaire ?

[sena1911]

Bonjour Rialle,

Merci pour ta réponse.
J'avais songé à concatener les cellules mais je cherchais à automatiser le plus possible vu que l'on sera plusieurs à remplir ce tableau.

Concernant la deuxième question ce que je souhaite c'est que en fonction du coût d'une session de formation que ce coût se repartissent entre les stagiaires convoqués.
La difficulté c 'est que ce calcul automatique doit faire la différence entre deux dates différentes.

[riaolle]

Quelque chose comme ça :

En ce qui concerne, le remplissage du formulaire, je ne pense pas que tu puisses passer à côté de la concaténation du nom et du prénom. A moins que ta base de donnée soit triée en ordre alphabétique (Nom, puis Prénom). Alors, tu pourras t'amuser avec des EQUIV, INDEX ou DECALER.
Mais je trouve que c'est quand même beaucoup plus simple en concaténant Nom Prénom.
Si tu utilises un tableau, tu n'auras qu'à rentrer la formule une première fois, puis ce sera automatisé à chaque nouvelle entrée.

[sena1911]

le souci avec la fonction Concatener, c'est lorsque je remets à jour la feuille base de données, je dois à chaque fois refaire l'action de rajouter une colonne et de concatener nom prenom. Je recherche une fonction qui me permet d'automatiser le plus possible. De plus comme tu le dis si bien ma base de données est trié par ordre alphabétique donc je peux faire des index equiv. Je bloque sur cette fonction avec des messages d'erreurs.

Pour ce qui concerne la ventilation des côuts je souhaiterais une formule qui me permet d'aller chercher le coût en fonction de l'intitulé de formation puis que ce montant soit ventilé en fonction du nombre de stagiaires et de la date

[papouclo]

Bonjour,
J'ai été moins rapide que riaolle.
Les coûts de formation étant sous forme numérique dans un tableau nommé CoutFormation en feuille 3 d'après le premier message , en plage I2:J9 dans les exemples ci-dessous,
on peut écrire en G3 la formule :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=RECHERCHEV(A2;CoutFormation;2;FAUX)/NB.SI.ENS($A$2:$A$9;A2;$B$2:$B$9;B2)

et la recopier vers le bas dans la colonne G.

Tous les employés suivant une formation y participent pendant le même temps donc les données de la colonne C n'interviennent pas dans le calcul.
Si les durées peuvent être différentes pour une même formation mais pour des employés différents, cela compliquerait le calcul de répartition des coûts
comme dans le deuxième exemple.
La formule en G3 est alors :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=RECHERCHEV(A2;CoutFormation;2;FAUX)*C2/SOMME.SI.ENS($C$2:$C$9;$A$2:$A$9;A2;$B$2:$B$9;B2)

à recopier vers le bas.

La formule

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=RECHERCHEV(A2;CoutFormation;2;FAUX)/SOMME.SI.ENS($C$2:$C$9;$A$2:$A$9;A2;$B$2:$B$9;B2)

donne d'aileurs dans tous les cas le coût horaire d'une formation en fonction des durées de participation et du nombre de participants.
Cordialement
Claude

[papouclo]

Re,
Une proposition sans concaténation dans l'exemple ci-dessous :

La formule en J2 est :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=SI(SOMMEPROD(LIGNE($2:$9)*($C$2:$C$9=$H2)*($D$2:$D$9=$I2))>0;INDEX(E:E;SOMMEPROD(LIGNE($2:$9)*($C$2:$C$9=$H2)*($D$2:$D$9=$I2)));"")

Cordialement
Claude

[sena1911]

Exceptionnel, ca marche hyper bien, un grand merci. La ventilation des coûts de formation se fait parfaitement et le remplissage automatique du site, du service et du nom du manager également, génial!!!

Je me permets, d'anticiper sur les besoins futures, je m'explique:

La base de données du personnel est remise à jour chaque début de mois via un classeur Excel qui m'est envoyé. Dans ce classeur 2 feuillets:

- base de données du personnel
- effectifs qui ont quittées l'entreprise

je remets à jour mes données via une manip toute simple, je copie colle la nouvelle feuille que je colle sur l'ancienne base de données du personnel. La réactualisation se fait toute seule.
Ma question:
Les salariés qui ont suivi une formation mais qui ont quittés l'entreprise n'apparaitront plus dans la base de données. Comment faire en sorte que dans la formule on intègre le critère si on le trouve pas dans la base de données effectifs doit le chercher dans le feuillet sortie du personnel,

[riaolle]

Bonjour,
J'ai pas tout à fait compris la question, mais tu peux regarder du côté des fonctions suivantes :

• EQUIV pour trouver une correspondance
• ESTERREUR pour détecter s'il y a une erreur.

[sena1911]

Bonjour,

ces fonctions ne correspondent pas à ce que je recherche.
Ce que je voulais c 'était introduire dans la formule de recherche si on ne trouve pas le nom dans le feuillet "base de données effectifs" allez regarder dans le feuillet "sortie du personnel"

[riaolle]

Tu devrais pouvoir t'en sortir avec SIERREUR :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=SIERREUR(ta formule avec la première feuille; la formule avec la deuxième feuille)

SIERREUR renvoie son premier argument si celui-ci ne renvoie pas d'erreur (dans ton cas, s'il ne trouce pas de correspondance). Si le premier argument renvoie une erreur, alors SIERREUR renvoie le deuxième argument.

[sena1911]

je dois le mettre à la suite de la 1èere formule?

[riaolle]

Quelle est ta formule maintenant ?
Que renvoie la formule s'il n'y a pas de correspondance ? Je peux essayer de t'expliquer avec une exemple comment marche SIERREUR :
Imaginons que j'ai une liste
A
B
C
D
E
Je veux trouver la ligne correspondant à un élément de la liste. Par ex. je veux connaître la ligne de "E" :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=EQUIV("E";A:A;FAUX)

Ceci renverra 5. Maintenant, imaginons que je potentiellement chercher une lettre qui n'existe pas dans la liste :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=EQUIV("F";A:A;FAUX)

Ceci va renvoyer une erreur. J'essaie donc de récupérer l'erreur et je dis ce qu'il faut faire en cas d'erreur :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=SIERREUR(EQUIV("F";A:A;FAUX);"pas dans la liste")

Ceci va renvoyer pas dans la liste.
Maintenant, à toi d'adapter à ton cas.

Mais peut-être que ta formule ne renvoie pas une erreur, mais une valeur en particulier en cas de problème. Il suffit d'utiliser SI :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=SI(la_formule = la_valeur_en_cas_d'erreur; formule_si_erreur; formule_si_pas_d'erreur)

Ce qui veut dire : si la formule = la valeur en cas d'erreur, alors utiliser la formule du cas où il y a une erreur, sinon utiliser la formule "normale".

[sena1911]

Merci beaucoup pour ta formule, pour le moment je n'ai pas le besoin de verifier dans un autre fichier. Je vais avancer au fur et à mesure de mes besoins.
Merci pour votre aide à tous

[sena1911]

Bonjour à toutes et tous,

Suite aux derniers échanges sur ce post la formule marche à merveille, néanmoins je cherche à compléter ma formule afin qu'elle recherche les mêmes éléments chez les collaborateurs partis.
Dans mon classeur BD est la base de données et Sortie est le feuillet qui reprend les salariés qui ont quitté l'entreprise.
Je souhaiterais donc savoir pour dire à ma formule si tu ne trouves pas les éléments dans la base de données allez chercher ces mêmes éléments dans le feuillet sortie du meême classeur. Ci-dessous la formule modifié.

=SI(SOMMEPROD(LIGNE(BD!$1:$1048576)*(BD!$E:$E=Formation!$F99)*(BD!$C:$C=Formation!$G99))>0;INDEX(BD!S:S;SOMMEPROD(LIGNE(BD!$1:$1048576)*(BD!$E:$E=Formation!$F99)*(BD!$C:$C=Formation!$G99)));"")+si(ESTVIDE(q99);(SOMMEPROD(LIGNE(Sortie!$1:$1048576)*(Sortie!$E:$E=Formation!$F99)*(Sortie!$C:$C=Formation!$G99))>0;INDEX(Sortie!S:S;SOMMEPROD(LIGNE(Sortie!$1:$1048576)*(Sortie!$E:$E=Formation!$F99)*(Sortie!$C:$C=Formation!$G99)));"")

A noter que lorsque la formule ne trouve pas les éléments dans le feuillet BD, il mem met la cellule en vide

[sena1911]

A noter que lorsque les collaborateurs sont partis ca ne met pas erreur mais la cellule est vide
je cherche a introduire un complément de formule pour celle que j'ai déjà qui est si cellule vide allez chercher les infos dans collaborateurs partis

[danixdb]

Bonjour sena1911, le forum,

Voici une proposition notamment pour la recherchev (recherche du service en fonction du nom et prénom de la personne). Cela évite une concaténation du nom et du prénom.

Slts

[sena1911]

Bonjour,

Merci pour ce retour, néanmoins je n'ai pas de souci avec la recherche V elle s'opère assez bien. Je souhaiterais juste completer la recherche V pour que les données des personnes qui ont quittés l'entreprise (et qui sont dans une autre feuillet, le feuillet "sortie"). Lorsque la recherche v ne trouve pas les données des personnes parties, les cellules sont vides.
Du coup je cherchais une formule qui lorsque les cellules seraient vides iraient chercher les données correspondantes sur le feuillet sortie

[Pierre Fauconnier]

Salut

Tout dépend de ta version d'Excel (non mentionnée dans le tag de discussion...)

Idée de base, en modifiant RECHERCHEV pour un equiv ou autre qui détermine si la donnée est présente sur la feuille ou non
=si(estna(recherchev(...);Recherche dans autre feuille;maformule)

Perso, je subodore de gros problèmes de conception qui amènent une formule aussi longue et un besoin de rechercher une donnée sur deux feuilles... (Hé oui, c'est plus difficile de travailler avec Excel lorsque les bases de la conception d'un classeur et les règles capitales ne sont pas respectées... )

[sena1911]

J'ai choisi d'utiliser la recherche V car ma base de données du personnel est dans une autre feuille. En effet, comme j'utlise assez souvent ce document et que la base de données du personnel change chaque jour je ne peux pas tout mettre sur une feuille.
Ensuite il est vrai que sur le plan de la conception je te l'accorde, je peux certainement faire mieux car à ce jour ce fichier rame pas mal