Index/equiv/recherche ? suivi compliqué
Salut à tous,
Je viens vous demander un petit coup de main par rapport à un suivi excel que j’essaye de réaliser ! Ce suivi permettra à mes collègues d’aller plus vite dans leur travail, même si perso je suis en train de prendre un temps fou pour le réaliser. J’ai pas mal épluché internet en quête de solutions mais j’arrive à des résultats et des formules que je pourrais laaaargement simplifier je pense…
Bref, si jamais l’un de vous se sent de m’aider et de m’apprendre quelques astuces je suis plus que preneur ! Je vous mets en pièce jointe le fichier excel. En gros, j’ai deux problèmes qui se posent, et je vais essayer d’être le plus clair possible, et désolé d’avance pour le gros pavé !
1. Dans l’onglet « Fiche 1 » :
La colonne A sera à remplir par mes collègues. Elle désignera des dates qui, normalement, sont systématiquement espacées d’au moins un mois. Elles sont toujours dans l’ordre chronologique.
N’importe quelle cellule de la colonne B (ex B6) doit renvoyer un chiffre en fonction de la cellule A adjacente (ici donc, A6). Ce chiffre en B désigne l’échéance correspondante à la date en A.
Ma première date, ici en A5, renverra forcément à la première échéance : donc B5 sera forcément égal à 1.
Imaginons, un mois plus tard, une deuxième date apparaitra et correspondra donc à la deuxième échéance. La cellule B correspondante sera donc égale à 2. Et 5 mois plus tard, cette cellule sera égale à 5, etc.
Mais voila le hic (ou plutôt les conditions) :
- Une cellule en A ne sera pas forcément rempli : la cellule en B adjacente devra donc correspondre au nombre de la cellule B juste au-dessus.
- Si par contre la cellule en A contient la même date que la cellule A juste au-dessus ; alors la cellule en B adjacente devra donc correspondre au nombre de la cellule B juste au-dessus.
- Si la cellule en A contient une date mais que la cellule juste au-dessus n’en contient pas ; ou si la cellule en A contient une date différente de celle juste au-dessus qui en contient une ; alors j’ai inséré une formule DATEDIF qui calcule le nombre de mois d’écart en rajoutant +1.
Voila la formule en B7 : =SI(A7="";B6;SI(A7=A6;B6;DATEDIF($A$5;A7;"M")+1))
Ca le fait ? Je crois qu’elle marche mais peut-être qu’elle pourrait être simplifiée.
2. Dans l’onglet « Feuille 2 » : C’est là où ça se gâte
Comme vous l’avez vu, dans l’onglet « Fiche 1 », la colonne A peut contenir plusieurs fois la même date. Ces dates apparaissent systématiquement dans l’ordre chronologique. Même s’il peut exister des trous entre deux mêmes dates, c’est le nœud du problème je crois.
Ma question : dans l’onglet « Feuille 2 », colonne A, comment faire pour que la cellule B6 par exemple, qui est associé au numéro 1 de la colonne A6, renvoie automatiquement la date qui correspond au chiffre 1 dans l’onglet « Fiche 1 » (soit 16/02/17 dans notre exemple). Tout en sachant que pour la seule date du 16/02, il peut y avoir tout autant 0 action que 10 actions réalisées ? Tout en sachant que pour la date du 16/03/17 par exemple, il n’y aurait aucune action ?
Une partie de la solution serait-elle que cette fameuse colonne A du premier onglet soit systématiquement remplie ?
Ensuite, dans l’onglet « Feuille 2 » colonnes C, D ou E ; j’aimerai que des dates apparaissent automatiquement. Si dans l’onglet « Fiche 1 », pour l’échéance numéro 1, j’ai une action réalisée qui est une VF1 (en E5), et qu’elle a abouti (oui en F5), alors j’aimerai que la date correspondante à cette action (19/02/17 en C5) apparaisse dans l’onglet « Feuille 2 » cellule C6.
De même, si j’ai une action dans l’onglet « Fiche 1 », pour l’échéance numéro 3, j’ai une action réalisée qui est une VF2 (en E8), et qu’elle a abouti (oui en F8), alors j’aimerai que la date correspondante à cette action (19/04/17 en C8) apparaisse dans l’onglet « Feuille 2 » cellule D8.
J’ai réussi à pondre une formule de dingue en C6, mais qui a rencontré des bugs, que je dois valider avec CTRL MAJ ENTREE. Elle m’a l’air bien trop compliquée. Comment feriez-vous ? Utiliseriez-vous une autre formule ?
Trois conditions sont donc à respecter (onglet « Fiche 1 » : un chiffre en colonne B, une action bien définie en colonne E (colonne qui peut contenir bien d’autres actions..), et un « oui » colonne F (qui peut contenir « non »).
Tout en sachant qu’une VF1, VF2 et LR1 n’existe qu’une seule fois pour une même échéance dans le premier onglet (heureusement !).
Voilà, j’espère ne pas vous avoir trop embrouillé !
NB : le troisième onglet ilo contient des listes de noms, qui serviront de liste déroulante dans le premier onglet (colonne E et F en l’occurrence).
NB 2 : Dans l’onglet « Feuille 2 », les colonnes concernées par ma dernière question vont de C6 à J41 !
Un grand grand merci d’avance et bonne soirée !
Mamien
Bonsoir,
Si je comprends bien :
sur Fiche 1 en B6, je pense qu'il vaudrait mieux calculer l'écart en mois systématiquement à partir de la date origine en A5, par ailleurs tu peux éventuellement utiliser une autre fonction que DATEDIF (fonction de compatibilité que Microsoft déconseille... et il est vrai qu'elle peut parfois poser quelque problème..) :
=SI(A6<>"";FRACTION.ANNEE($A$5;A6)*12+1;B5)à tirer sur le reste de la colonne.
Sur fiche 2 : en B7 :
=SIERREUR(SI(MAX('Fiche 1'!$A$5:$A$40)>=MOIS.DECALER(B6;1);MOIS.DECALER(B6;1);"");"")à étendre sur la colonne.
Elle inscrit la date d'échéance, si celle-ci est antérieure ou égale à la date la plus récente de Fiche 1 col.A.
en C6 :
=SOMME.SI.ENS('Fiche 1'!$C$5:$C$40;'Fiche 1'!$B$5:$B$40;$A6;'Fiche 1'!$E$5:$E$40;C$5;'Fiche 1'!$F$5:$F$40;"Oui")à étendre sur toutes les lignes des colonnes C, D, E.
Cordialement.
Bonjour MFerrand,
Merci beaucoup pour ta réponse. Je regarde ça dans la journée et reviens vers toi.
Bonne journée !
Mamien
Re-bonjour !
C'est exactement ce que je cherchais! Merci beaucoup, la formule somme.si.ens est assez géniale.
J'ai une dernière petite question si ça ne te dérange pas : dans la fiche 1 on a la date du 16/2 et du 16/4. On a pas celle du 16/3.
Quelle formule tu mettrais à partir de B7 dans feuille 2 pour faire en sorte de ne pas faire apparaître la date du 16/3 (et toutes celles qui à l'avenir ne seront pas renseignées dans fiche 1 ?)
Merci encore !
Mamien
Bonjour,
Je pensais que tu voulais les indiquer tous...
En B6 :
=SIERREUR(INDEX('Fiche 1'!$A$5:$A$40;EQUIV(A6;'Fiche 1'!$B$5:$B$40;0));"")à tirer sur la colonne.
NB- J'ai oublié de te signaler : sur les colonnes C, D, E, l'élimination des 0 (qui se matérialiseraient sous la forme 00/01/1900) est obtenue par format de cellule personnalisé : jj/mm/aaaa;;
(soit tu vas dans Format de cellule > Personnalisé et tu ajoutes 2 points-virgules au format existant déjà)
Cordialement.
Salut!
J'essaie de voir ce que ça donne en les indiquant tous ou pas
Je vais refaire des essais et je te tiens au courant si jamais j'ai d'autres pépins.
A tt!