sommeprod et fonction

[dave]

voici les données du problème:

J'utilise la fonction sommeprod dans la feuille 'Saisie absences et TS' pour obtenir des valeurs en fonction de données provenant d'une autre feuille intitulée 'absences'.
les valeurs sont situées

dans la feuille absences, je me sers des données d'une ligne: le nom du consultant, la date de début d'absence, le nombre de jour d'absence, le type d'absence.

Je récupère bien les valeurs comme souhaitées avec la fonction sommeprod en fonction de la date de début d'absence et le nom du consultant.
Evidemment pour le moment je n'insère dans la feuille 'Saisie absences et TS' que la date de début.


Comment faire pour ajouter après cette date le nombre de jour d'absence.
exemple:
une date d'absence le 10/10/2005, et un nombre de jour d'absence de 3.
Je dois donc ajouter deux autres journée, le 11/10/2005 et le 12/10/2005.

 

[Sylvie]

Re bonsoir Dave,

je te propose dans un premier temps cette ébauche de fichier toujours avec Sommeprod. J'ai complété ta feuille absences afin que les jours d'absence apparaissent et que ceux ci puissent etre reportés dans la feuille cible. Dans l'etat actuel des choses, j'ai juste reporté le mot 'Absent' (avec application d'une mise en forme conditionnelle) à la bonne date sans reporter le motif de l'absence associée d'une mise en forme conditionnelle suivant le motif de l'absence.
Je pense que cela est possible mais ce soir je ne peux pas faire plus.

Dis moi ce qu'il en est (J'ai du élaguer ton fichier car il avait beaucoup grossi avec SOMMEPROD)

Bonne soirée [file name=absencesDave.zip size=32192]http://www.excel-downloads.com/components/com_simpleboard/uploaded/files/absencesDave.zip[/file]

 

[dave]

ok,
Je comprend ton principe. Tu veux créer la suite de journée d'absence sur la ligne du consultant dans la feuille absence.
Et une fois que tu l'as la fonction sommeprod dans la feuille 'saisie absence' devient nettement plus simple.
Enfin si j'en crois ce que j'ai vu.

Je décortique le fichier que tu m'as rendu pour comprendre entièrement.

Par contre c'était quoi la suite dont tu parlais?

Dave.

 

[Sylvie]

Re bonjour Dave, le forum,

les colonnes que j'ai du rajouter sur la feuille 'absences' sont plutot destinées à être masquées
Par contre sur ta feuille 'Saisie absences et TS' que souhaites tu voir apparaitre ?
- le mot Absent suffit il comme je l'ai fait pour le moment ?
- ou le motif de l'absence (à recuperer sur la feuille absences) associée (ou non) avec le code couleur correspondant ?

 

[dave]

Re Bonjour Sylvie,

Concernant le mot 'absence', il faut plutôt mettre le type d'absence car ces données doivent ensuite servir à du calcul en fonction du type d'absence.

par contre la couleur n'est pas du tout nécessaire. je ne n'ai pas vu d'ailleurs où tu indiquais la couleur.

sinon quelques questions aussi:

- comment se fait-il que dès que je veux faire une modification pour la formule :
SERIE.JOUR.OUVRE(), je ne puisse pas et il s'affiche '#NOM?' à la place?

- autre problème: les valeurs dans la colonne 'fin abs' ne correspondent pas au résultat escompté alors que
la formule est identique sur toute la colonne.

il y a un jour de comptabiliser en trop à chaque foisquand on visionne la feuille 'saisie absence'.
En fait le nombre de jour d'absence comptabilise aussi la date de départ.
exemple: debabs = 14/10/2005, njabs = 2
je dois récupérer la date du 14 et du 15.

comment crées-tu les plages plageabs, tableau, date et absences

Sinon c'est bien ce que je demande.

 

[Dave]

Oups, je suis désolé. Ce n'était pas fait exprès

 

[Gael]

Bonjour Dave, Bonjour Sylvie,

Je n'avais pas répondu dans un premier temps car je n'avais pas bien compris la question.

J'ai fait un essai sans la fonction Sommeprod en utilisant les fonctions de recherche, la difficulté étant finalement lorsqu'il y a plusieurs jours d'absence de faire la recherche à partir du premier jour.

J'ai créé comme sylvie une colonne Datfin dans la table absence, puis une formule nommée 'Typabs' permettant de trouver le motif d'absence à partir du nom et de la date.

Typabs =INDEX(absences!$D$2:$D$25;EQUIV('Saisie absences et TS'!D$1&'Saisie absences et TS'!$B236;absences!$A$2:$A$25&absences!$B$2:$B$25;0))

Puis une fonction Datfin qui permet de trouver la première cellule d'une série en charchant le n° de ligne le plus élevé ayant une valeur différente du motif en cours +1.

Si la date est <= à la date de fin, on remet le même motif, sinon on fait une recherche 'Typabs' et on met le motif trouvé ou rien si elle n'aboutit pas.

Datfin =INDEX(absences!$F$2:$F$25;EQUIV('Saisie absences et TS'!D$1&INDIRECT('$B'&MAX(SI(('Saisie absences et TS'!D$1234<>'Saisie absences et TS'!D235);LIGNE('Saisie absences et TS'!D$1234)))+1);absences!$A$2:$A$25&absences!$B$2:$B$25;0))

la formule générale est donc:

=SI(D235='';SI(ESTNA(TypAbs);'';TypAbs);SI($B236<=Datfin;D235;SI(ESTNA(TypAbs);'';TypAbs)))

Pour les couleurs, Une MFC pour les week-end en bleu ciel avec une police de caractères de la même couleur pour ne pas voir apparaître les motifs les Samedis et dimanches.

une autre MFC pour les motifs si la cellule est <>''. je suis resté dans les bleus mais tu peux changer les couleurs.

Dis-moi si c'est OK ou si tu souhaites quelques modifs (je serai absent ce soir et jusqu'au 3 novembre).

@+

Gael

PS: J'ai supprimé quelques colonnes pour que le fichier tienne dans les 50Ko réglementaires. [file name=absences_V3.zip size=28283]http://www.excel-downloads.com/components/com_simpleboard/uploaded/files/absences_V3.zip[/file]

 

[Sylvie]

Re bonjour Dave, Gael, le forum

deux mots : 'Epatant' et 'Bravo' Gael

Il ne me reste plus qu'à étudier ta solution à tête reposée pour apprendre. Je vais en avoir besoin (de reposer ma tête ET d'éplucher ta solution)

A Bientot et merci

 

[Dave]

Je regarde pour voir si c'est ok et te tiens au courant.

Dave.

 

[Dave]

petit problème:
je ne peux rien voir comme données car j'ai l'indication '#NOM?' qui est afffiché à la place des valeurs.

Comment puis-je faire pour les visionner, j'ai le même soucis de visualisation sur un pc avec excel 2003 et un pc avec excel 2002.

 

[Gael]

Bonjour Dave,

Chez moi c'est XL2000 et ça marche bien.

Regarde si les macros complémentaires sont installées (outils macros complémentaires) et coche les différentes cases notamment utilitaires d'analyse.

@+

Gael

 

[Dave]

Excellent ça marche très bien.
Vraiment très fort.

Gael un grand merci à toi.

Et biensur à sylvie aussi.

 

[Gael]

Bonjour Dave, bonjour Sylvie,

Bon, je suis content que ça fonctionne avec les macros complémentaires car je n'avais pas d'autre idée.

Pour la formule nommée Datefin, c'est plus logique d'utiliser la colonne date plutôt qu'indirectement B et n° de ligne.

Dans ce cas, Datefin devient:

=INDEX(absences!$F$2:$F$25;EQUIV('Saisie absences et TS'!D$1&MAX(SI(('Saisie absences et TS'!D$1253<>'Saisie absences et TS'!D254);'Saisie absences et TS'!$B1:$B253))+1;absences!$A$2:$A$25&absences!$B$2:$B$25;0))

Attention: comme c'est une formule nommée qui varie en fonction de la cellule ou on se trouve, il faut en quelque sorte la synchroniser. Donc il faur être positionné sur la cellule 'D255' si tu veux faire un copier/coller de la formule ci-dessus dans insertion nom définir.

@+

Gael

 

[dave]

Bonjour gaël,

Comment fais-tu pour mettre des couleurs dans les cases, car je n'ai pas vu un seul code approprié pour celà dans tes formules?

En tout cas j'ai bien étudié tes formules; moi qui suit novice pour l'utilisation des formules complexes dans excel.
Je suis époustouflé par ces possibilités.


Dave.

 

[Sylvie]

Bonjour Dave, Gael, le forum

les couleurs auxquelles tu fais allusion sont le fruit d'une mise en forme conditionnelle.
Place toi par exemple en C228 et va dans Format / Mise en forme conditionnelle (MFC)

Pour faire simple, tu peux associer une couleur suivant une valeur ou suivant une ou plusieurs formules d'une cellule (dans la limite de 3).

N'hesite pas à utiliser le moteur de recherche du forum car de nombreux fils traitent de la MFC et te permettront d'approfondir cette fonction d'Excel. (Brigitte a également fait un petit mémo word sur la MFC)

Bonne fin de journée à tous

 

[Gael]

Bonjour Dave, bonjour Sylvie,

En réponse à ton message, Dave, quelques compléments d'informations:

le lien avec le nom du consultant dans l'onglet Absences se fait dans les 2 formules nommées 'Typabs' et 'Datfin' où la recherche se fait sur:

'Saisie absences et TS'!D$1&'Saisie absences et TS'!$B257

c'est à dire le nom du consultant en D1 associé à la date en colonne B, ce qui donne par exemple 'AUGEREAU38628' qui est recherché dans les 2 premières colonnes du tableau absences également concaténées:

absences!$A$2:$A$25&absences!$B$2:$B$25

Pour recopier les formules dans un autre fichier, il faut commencer par créer les 2 formules nommées 'Datfin' et 'Typabs' avec Insertion nom définir puis coller les formules dans la zone 'Fait référence à' et ajouter. Une fois les formules créées, vérifie bien que les onglets sont corrects en qu'il n'y a pas '#REF' à la place d'un nom de feuille et que les références des cellules utilisées sont correctes (ligne et colonne).

Typabs est facile à vérifier car si tu te positionnes sur une cellule correspondant à un nom de consultant et une date qui existent dans la feuille absence, tu fais insertion nom définir puis tu sélectionnes Typabs et tu cliques dans la zone 'fait référence à', XL doit t'afficher la feuille Absence positionnée sur la cellule contenant le motif correspondant à la date et au consultant choisi.

Bon courage.

@+

Gael