Calculer durée entre deux dates selon conditions
Bonjour à tous,
Tout d'abord, désolé s'il y a doublon mais je ne pense pas avoir trouver un problème similaire sur le forum.
Vu le manque de précision de mon titre, j'espère être plus clair dans l'explication ci-dessous.
Je travaille sur une base de données assez large (2.970 députés européens). Chacun de ceux-ci a un identifiant unique mais les députés apparaissent à plusieurs reprises. Par exemple, chaque député est entré plusieurs fois dans la base de données (avec le même identifiant) afin de connaitre les différents commissions dans lesquelles il a siégé. Pour chacune des commissions où il siégé, il y a sa date d'entrée et sa date de sortie qui sont codées.
Ce que je souhaite à présent connaître, c'est la la durée totale de son mandat européen (qui correspond tout simplement au nombre de mois entre sa plus ancienne date d'entrée et sa plus récente date de sortie).
Vu l'importance de la banque de données (2.970 avec au total 16.659 lignes puisqu'ils apparaissent chacun à plusieurs reprises), j'aimerais trouver un moyen efficace et rapide pour calculer cette durée. J'imagine qu'il faudrait demander à Excel d'isoler pour chaque identifiant la plus ancienne date et la plus récente, ainsi je pourrais calculer le nombre de mois passés au parlement. Pour plus de précision, j'ai sorti un extrait de la banque de données en pièces jointes.
Merci d'avance pour vos réflexions et pistes de solution!
Bonjour,
Je me lance, j'ai fait une combinaison de plusieurs formules, tu me rediras... Je pense que le mieux serait de faire l'affichage de tes résultats sur un onglet différent.
Je veux bien que tu envois un fichier avec un dizaine de noms pour être sûr.
A me redire,
Et vive l'europe ... tiens c'est la seconde fois que j'écris çà cette semaine
Cordialement,
Leakim
Bonjour,
Un très vif merci pour cette réponse rapide, c'est clairement dans cette direction que je souhaite aller...Je suis bluffé
Toutefois, il y a peut-être un problème car mes identificateurs ne sont pas continus (ils ne suivent pas 1,2, 3 mais passent parfois de 5 à 59). La raison est que ce sont des identifiants recupérés d'une base commune et qui sont maintenus pour la comparaison.
Je pense que c'est ce qui explique que j'ai bcp de "# N/A" quand je souhaite l'appliquer à ma base de données complète.
Y a-t-il une solution à ce problème?
Au passage, est-ce que c'est possible de faire afficher la durée en MOIS (vu que c'est pour des traitements statistiques, j'ai besoin d'une donnée numérique)?
Merci déjà pour ton temps!
Une solution un peu bricolage peut-être, ne serait-il pas de refaire une numérotation continue?
Je pourrais par exemple, tirer une numérotation continue à côté de la liste des Identificateurs (en faisant un tableau croisé je peux obtenir tous les identificateurs existants). Ensuite, je fais une formuler remplacer l'ancien identificateur par le nouveau identificateur issue de la suite continue (j'imagine que ça existe dans Excel
Qu'en penses-tu?Des idées dans ce sens? Mais ça a l'air un peu compliqué quand j'essaye...
J'ai tout de même le listing des nouveaux identificateurs si ça peut aider
Re,
Content d'être sur la bonne piste !!!
Je me doutais bien qu'il y aurait des embûches
De fait, il faut si c'est pas une suite linéaire faire un tri préalable des identifiants. Une fois fait, tu reprends cette liste et tu appliques les formules... après peut-être un ou deux ajustements.
Je crois que je suis capable de faire çà, mais il faudrait un peu plus de données pour vérifier sur un grand volume.
Leakm
EDIT: Tu as posté un fichier entre temps... J'y regarde
Ok,
C'est quoi le principe ancien et nouveau ID ?
Leakim
Bonjour,
Il n'y avait pas vraiment de principe ancien/nouveau identificateur, je pensais à voix haute pour essayer des solutions
Je te joins une dataset avec plus de députés pour que tu vois mieux le souci (je ne peux partager toutes les données pour des raisons professionnelles...)
Est-ce que ça te permet d'y voir plus clair?
En attendant, je vais m'atteler à bien cerner tes formules
Re,
J'ai laissé tomber le tri par formule... Je suis passé par une macro...
Sur la feuil2 tu tires vers le bas les cellules A10:F10 sur autant de lignes que tu veux.
Sur la feuil1 tu colles ta base de noms comme tu l'as fait jusqu'à présent, tu cliques sur le bouton "TRI" au niveau de la colonne Y
le reste ce passe sur la feuil2 !
A me redire,
Leakim
Re Leakim,
Et bien....cela marche presque, il y a juste un problème avec les dates de fin.
En fait, tu as pris chaque fois la plus récente "startEU" comme date de début de carrière et la plus veille "startEU" comme date de fin.
S'il faut bien prendre la plus récente "startEU" comme date de début, c'est la plus veille date de de la collone "endstartEU" qu'il faut prendre comme date de fin pour chaque député.
Comme je n'ai pas encore bien saisi comme tu avais fait les tris avec la macro ainsi que la logique derrière les formules précédentes, je préfère suivre tes conseils.
Mea culpa, je n'avais pas encore vérifié la concordance des dates lors de ton premier essai, j'avais eu mon attention attirée par les #N/A en raison de la suite non linéaire des identifiants.
Sinon, tout le reste roule parfaitement ! (ET merci pour la conversation en moi, c'est vraiment que je pouvais facilement déduire par contre ça
EDIT: Au passage, je dois ajouter une bonne vingtaine de colonnes (environ 25) après la dernière colonne du fichier originale (les données ne sont pas encore codées). Est-ce que ça va poser problème pour le tri et/ou les formules.
Encore merci pour ton temps!
Bonjour,
Ci-joint un essai sans macro.
Le fichier fonctionne de cette manière :
-Tu colles toutes tes données dans l'onglet Feuil1 : attention à ne pas supprimer les colonnes : utilise la touche supp de ton clavier pour effacer les anciennes données. N'utilise pas 'supprimer' du menu déroulant, ni 'couper' ça peut poser des problèmes avec les noms définis.
-Dans l'onglet feuil2, les cellules jaunes sont des formules matricielles : à valider simultanément par CTRL + MAJ + ENTREE.
Pour dérouler les formules, tu selectionnes par exemple la plage A2:E2 et tu déroules vers le bas.
Peu importe le nombre de colonnes de ta base, à condition que les informations de la base aient la même colonne que précédemment.
cdt
Bonjour,
Ma petite contribution du matin.
Avec une approche TCD et champ calculé (qui évite l'ajout de colonnes et de formules)
Cdlt
@Jean-Eric :
Hello l'asticot, un ver s'est glissé dans la pomme.
Plus sérieusement, bonjour
Un problème dans tes champs calculés : le min et max prennent la somme des dates StartEU et EndEU.
Cordialement
Re,
@ ketamacanna
L'asticot a péché contre le bon sens
Mais la pomme est saine...
Cdlt
Bonjour à tous,
Merci beaucoup pour vos précieuses réponses, j'apprécie surtout d'avoir différentes méthodes, c'est toujours très pratiques pour solutionner de futurs problèmes similaires.
Pour l'instant, j'aime bien la solution des TCD car c'est ce que je connais le mieux (je ne comprends pas toujours la logique des formules proposées, ce qui est embêtant à corriger quand il y a des soucis).
Pour le TCD, j'ai toutefois quelques questions:
1) Comment fais-tu pour ne faire apparaître qu'une seule fois le nom du député? (Quand j'avais essayé, il apparaissait sur plusieurs lignes).
2) Pourquoi utiliser la data birthday dans le tableau?
3) Le calcul des mois n'est pas correct, par exemple JARZEMBOWSKI a fait 491 mois, ce qui est bcp trop par rapport à son mandat.
Pour les autres solutions, j'essaye le tout et vous dit quoi tout à l'heure!
Merci à vous
Bonjour à tous,
Merci beaucoup pour vos précieuses réponses, j'apprécie surtout d'avoir différentes méthodes, c'est toujours très pratiques pour solutionner de futurs problèmes similaires.
Pour l'instant, j'aime bien la solution des TCD car c'est ce que je connais le mieux (je ne comprends pas toujours la logique des formules proposées, ce qui est embêtant à corriger quand il y a des soucis).
Pour le TCD, j'ai toutefois quelques questions:
1) Comment fais-tu pour ne faire apparaître qu'une seule fois le nom du député? (Quand j'avais essayé, il apparaissait sur plusieurs lignes).
2) Pourquoi utiliser la data birthday dans le tableau?
3) Le calcul des mois n'est pas correct, par exemple JARZEMBOWSKI a fait 491 mois, ce qui est bcp trop par rapport à son mandat.
EDIT: Dans le dernier fichier renvoyé, c'est correct merci!
Pour les autres solutions, j'essaye le tout et vous dit quoi tout à l'heure!
Merci à vous
Re,
Voir fichier pour la question 1
Question 2: Savoir qu'un député est né en xxx, qu'il est âgé de xxx et qu'il a participé pendant x années à x comités....
Pour la question 3, j'ai merdé initialement, mais c'est corrigé.
Dans l'idée, c'était, j'ai un tableau de base avec un grand nombre d'infos. Je le réduis pour ne conserver que le nécessaire.
Cdlt
Re-Salut tout le monde,
Un tout grand pour vos réponse rapides et utiles. A priori, je vais utiliser les propositions sous fourme de TCD et les formules proposées pour deux raisons.
1° le TCD me permet en effet de sortir une série d'informations utiles (je peux calculer la durée dans chaque comité par exemple).
2° Etant donné que j'ai une autre base de données connexes (où les députés sont identifiables avec leurs IDs), je vais sans doute pouvoir utiliser les formules pour recouper les informations sur deux fichiers. Je dois encore tester cette possibilité.
Encore merci et bonne journée!
Bonsoir à tous,
Je n'ai pas les mêmes résultats.
J'ai calculé le nbre de jours en col N de la feuille1 puis en feuille2 la fonction SOMMEPROD col G(en jours) et K(en mois arrondi)
Cdt
Henri
Salut AFEH,
La raison est qu'un même député est souvent présent dans plusieurs commissions lors de la même législature.
Aussi, alors qu'il ne reste 5 ans dans le parlement au cours de son mandant, tu comptes plusieurs fois ces 5 années dans la formule SOMMEPROD.
Aussi, je pense que la meilleure stratégie est bien de calculer la durée entre sa plus vieille date d'entrée (startEU) et sa plus récente date de sortie (EndEU).
Merci tout de même pour la solution alternative, sans le problème des plusieurs commissions, ça aurait été tout aussi efficace!
Bonsoir à tous,
Je suis content que ce post puisse mobilisé tant de propositions... c'est riche
Je constate que dès qu'il y a un peu de données à trier cela nous inspire
Cordialement,
Leakim