XL 2013 [RESOLU]Combiner des fonctions Somme.SI + NB.SI + Dates

[Shindaraa]

Bonjour a tous,

Ceci est mon premier message sur le forum, et surement pas le dernier pour les jours/semaines a venir.
Je viens en effet de démarrer un nouveau job en Jordanie pour une ONG française en tant qu'administrateur RH et Financier. Alors que j'avais plutôt l'habitude de travailler sur des logiciels optimisés et crées spécifiquement pour la gestion des RH, dans cette structure, on fait tout sur Excel, et une partie de mon travail consiste a créer/modifier, les outils existants afin de les rendre plus efficace pour le staff local qui s'en servira. N'étant pas un génie sur ce software; et vu la complexité de certains outils sur lesquels je vais devoir travailler, je ferai surement appel à vos lumières dans les prochaines semaines

Je travaille donc actuellement sur un doc Excel permettant de suivre et de contrôler les congés et les jours de repos du personnel expatriés(nommés RR comme vous pourrez le lire dans le doc ci-joint).
Comme vous pouvez le voir, il y'a des colonnes groupées à remplir selon la date et la nature des congés qui ont pu être pris par l'employé. Et deux colonnes nommées "Paid Leave FU" et "R&R FU", qui grâce à une fonction NB.SI comptabilise le nombre de jours de congés effectifs. Il y'a également deux colonnes qui renseignent les dates de début et de fin de contrat, dates qui sont susceptibles d’être modifiées assez souvent.

J'aurais donc aimé savoir s'il vous semble possible d'avoir, pour les colonnes "Paid Leave FU" et "R&R FU", une formule qui combine la fonction NB.SI que j'ai déjà crée, tout en ayant pour condition que ne soit pris en compte que les périodes correspondant au contrat de travail en cours.
Étant donne que les cases qui renseignent les congés effectifs(les cases coloriés dans les colonnes groupées) ne contiennent pas de données Dates; cela me parait difficilement faisable; je reste donc bien évidemment ouvert à toutes idées de votre part, au niveau formule que bidouillage de la présentation etc... qui pourraient me permettre d'arriver à mes fins.

Voila, désolé pour ce long roman mais je voulais m'assurer que ma requête soit la plus claire possible. N'hésitez pas à me demander si vous avez besoin d'infos supplémentaires.
Un immense merci d'avance pour le temps que vous pourrez accorder à m'aider

 

[Modeste]

Re : Combiner des fonctions Somme.SI + NB.SI + Dates

Bonjour Shindaraa et bienvenue dans le monde merveilleux d'Excel,

Commençons par une bonne nouvelle: une solution est envisageable en modifiant légèrement ton fichier: au stade actuel, rien ne permet de déterminer que la mention "RR" figurant dans la cellule BQ10 concerne le 22/02/2014! En ajoutant de vraies dates en ligne 8, au lieu de n'avoir que des chiffres de 1 à 28, 29, 30 ou 31, il devient possible de "comptabiliser" avec une formule du genre

=SOMMEPROD(($O$8:$ARG$8>=I10)*($O$8:$ARG$8<=J10)*($O$10:$ARG$10="PL"))

Par contre, au rayon des moins bonnes nouvelles, ce type de formule est ce qu'on appelle des formules matricielles et celles-ci sont gourmandes en resources (le temps de calcul s'allonge proportionnellement au "volume" à traiter et au nombre de formules matricielles utilisées ... et trois années consécutives, ça fait déjà plus de 1.100 colonnes ... on ne sait pas combien de lignes tu vas avoir au total!?

D'autre part, au niveau de "l'ergonomie", il me semble que j'aurais du mal à utiliser ce type de fichier (en raison, là aussi du nombre de colonnes). Et puis, si un jour quelqu'un a la bonne idée d'indiquer des "RR" dans les colonnes noires, tu risques de chercher longtemps d'où pourrait provenir l'erreur!

Enfin (en tout cas à ce stade ) tu ne précises pas comment tu indiquerais, pour une même personne les dates de début et fin d'un nouveau contrat? Une nouvelle ligne dans cette même feuille ... ou alors tu modifies les dates dans la même ligne?

 

[Shindaraa]

Re : Combiner des fonctions Somme.SI + NB.SI + Dates

Salut Modeste,

D'abord je tenais a te remercier pour avoir pris du temps pour m'aider,
et désolé de pas avoir été plus réactif mais j’étais en week-end (vendredi-samedi dans les pays "musulmans").

Je me suis attelé a une autre tache aujourd'hui, donc je test ta formule ce soir et demain matin et je reviens te faire un feed-back. En tout cas, la solution que tu me proposes me parait être plutôt proche de ce que je recherche.

Pour revenir sur tes autres recommandations et questions,

Au niveau de l'ergonomie, j'ai du mal a trouver une meilleure présentation en prenant en compte les infos que j'ai besoin d'afficher ( jour par jour pour avoir un bon suivi des congés pris et prévus ) et celles qui sont a prendre en compte dans les calculs (un contrat d'expat pouvant s’étaler sur 2 ans, il me faut l’année en cours, l’année précédente, et l’année suivante).
Sinon je ne devrais pas avoir plus d'une trentaine de ligne. Est ce que ça te semble jouable niveau ressources? (je fais un test demain avec le tableau rempli et ta formule magique)
Je pensais éventuellement passer par un tableau récapitulatif qui pourrait simplifier tout ça mais ça me paraissait pas mal d'avoir toutes les infos sur une seule feuille. Si tu as une idée de présentation différente, je suis évidemment a l’écoute.

Pour ce qui est des colonnes noires et de la possibilité que quelqu'un y rentre des infos qui seront évidemment difficilement détectable, c'est très bien vu et j'te remercie d'y avoir pense haha. Je pense que je peux y remédier soit en fusionnant ces colonnes en (quasi-)intégralité, ou alors en la protégeant non?

Enfin, le but de cet outil est d'avoir un suivi sur le contrat en cours, donc on viendra remplacer les dates directement sur la même ligne que le contrat précédent.

Encore un immense merci pour ton aide,
Je te tiens au courant.

Bonne soirée

 

[gosselien]

Re : Combiner des fonctions Somme.SI + NB.SI + Dates

Bonjour,

et pourquoi ne pas mettre ce qui serait le + volumineux (tes dates) en ligne et le reste donc en colonne et mettre autre chose que du noir ?

 

[Modeste]

Re : Combiner des fonctions Somme.SI + NB.SI + Dates

Salut Shindaraa, gosselien,

Enfin, le but de cet outil est d'avoir un suivi sur le contrat en cours, donc on viendra remplacer les dates directement sur la même ligne que le contrat précédent.

Euh ... qu'adviendra-t-il des infos relatives au contrat précédent? Elles vont disparaître instantanément!? ... Tu es sûr de ton coup?

 

[Shindaraa]

Re : Combiner des fonctions Somme.SI + NB.SI + Dates

Salut les gars,

Du coup j'ai pu faire tester le fichier par un de mes collègues jordaniens, il a pas eu l'air d'avoir trop de problème niveau ergonomie donc je pense que je vais laisser comme ça pour l'instant. Pour ce qui est de la formule matricielle, elle a pas l'air de donner lieu a trop de soucis niveau consommation de ressources, donc ça s’avère être une très belle solution pour moi. Encore un immense merci Modeste pour ton aide.
Du coup pour éviter tout problème j'ai rajouter des lignes en VBA pour protéger dès ouverture les parties du docs comme les formules donnée par Modeste et les fameuses colonnes noires, en laissant la possibilité de grouper et dégrouper ces mêmes colonnes.

@modeste : pour ce qui est de remplacer les dates du contrat, et des infos relatives au précédent qui resterait, il me semble que c'est pas un problème puisque grâce a ta formule, ces anciennes infos ne seront de toute façon pas prise en compte dans le calcul sur les nouvelles dates qui pourraient être saisies.

Du coup, j'ai juste une dernière requête, je sais pas si je dois recréer un topic pour ça, mais je voulais mettre une mise en forme conditionnelle sur la colonne G avec la condition G"x">H"x" alors G apparait en rouge ( la même que j’ai pu mettre dans la colonne I) . Mais celle ci ne fonctionne pas comme vous pouvez le voir. Pourtant la règle apparait bien dans le menu de gestion des MFC, mais ne s'applique pas. Avez vous déjà eu ce genre de bug ?
Le mot de passe pour débloquer la feuille est : hrxpat

 

[Modeste]

Re : Combiner des fonctions Somme.SI + NB.SI + Dates

Bonjour Shindaraa, le fil et le forum,

Dans ta formule en colonne H, pour le Valeur si faux de ton second SI, tu as mis des guillemets autour du 0. Si tu les enlèves et que tu recopies ta formule vers le bas, ta MFC fonctionne en G12.

Quand on regarde les MFC pour l'ensemble de la feuille de calcul, on a l'impression que tes MFC se multiplient (pas encore à l'infini, mais à terme, tu risques d'avoir une profusion de MFC ... ce qui peut devenir difficile à gérer). Une proposition:
- sélectionne G11:G59
- MFC --> Effacer les règles --> des cellules sélectionnées
- MFC --> Nouvelle règle --> Appliquer la mise en forme uniquement aux cellules qui contiennent
- Valeur de la cellule >> Supérieure à >> =$H11 (pas de '$' devant le 11)
... tu ne crées ainsi qu'une seule MFC pour toute la plage sélectionnée

Attention au fait que si on n'active pas les macros ... ta feuille n'est pas protégée

 

[Shindaraa]

Re : Combiner des fonctions Somme.SI + NB.SI + Dates

Aie aie aie tout ça pour une histoire de guillemets...
En effet, j'avais multiplie les MFC en les appliquant a chaque cellule individuellement a la recherche d'une solution pour ce "bug", qui n'en était pas un.

Tu es un génie Modeste, merci beaucoup pour ton aide et le temps que tu y as accorde, et merci aussi pour tes mises en gardes sur les "faiblesses" de mon outil.

Sujet clos =)

 

[gosselien]

Re : [RESOLU]Combiner des fonctions Somme.SI + NB.SI + Dates

Sympa ces remerciements