Recherche de données en fonction du mois en cours sur EXCEL Résolu/Fermé

Question

Bonjour à tous,

Après avoir écumer les forums excel, je n'ai toujours pas trouver de réponses à ma problématique.

J'aimerais mettre en place une formule qui me permettrait d'afficher dans une cellule une donnée (tiré dans un autre tableau du classeur) en fonction du mois en cours.
C'est à dire, afficher la valeur pour le mois d'avril, car nous sommes en Avril. Et que la mise à jour du mois se fasse automatiquement.

Je vous remercie par avance,

Cdt,

Mike-31 · Accepted Answer

Re,

sais tu faire les formules matricielles qu'il faut confirmer avec les touche Ctrl, Shift et Entrée

je te prépare les formules pour les 3 régions et les deux matières route et fer en début d'après midi

Mike-31 · Answer

Bonjour,

si j'ai bien compris, cela ne me semble pas compliqué mais il faudrait avoir un exemple de fichier pour voir quelle syntaxe utiliser.
pour joindre un exemple de fichier sert toi de ce lien
Puis parcourir/créer le lien et colle dans une réponse le lien généré
https://www.cjoint.com/

Mike-31 · Answer

Re,

Si j'ai bien compris onglet BDD-TDB
en C8 tu veux 48 638	
et en D8 67 090
après idem pour Route et le total des deux

Mike-31 · Answer

Re, je suis de retour, une formule matricielle se confirme en cliquant en même temps sur trois touches du clavier Ctrl, Shift et Entrée, si tu fais bien la formule se placera entre ces accolades {} en C8 colle cette formule et confirme la en matricielle comme toutes les formule que je te donne ci après =INDEX('Vol. par Bassin'!6:6;MAX((COLONNE(1:1)-5)*('Vol. par Bassin'!6:6<>0))) en D8cette formule =INDEX('Vol. par Bassin'!6:6;MAX((COLONNE(1:1)-2)*('Vol. par Bassin'!6:6<>0))) en C9 =INDEX('Vol. par Bassin'!7:7;MAX((COLONNE(1:1)-5)*('Vol. par Bassin'!7:7<>0))) en D9 =INDEX('Vol. par Bassin'!7:7;MAX((COLONNE(1:1)-2)*('Vol. par Bassin'!7:7<>0))) dans le même ordre pour NE fer =INDEX('Vol. par Bassin'!6:6;MAX((COLONNE(1:1)-4)*('Vol. par Bassin'!6:6<>0))) =INDEX('Vol. par Bassin'!6:6;MAX((COLONNE(1:1)-1)*('Vol. par Bassin'!6:6<>0))) route =INDEX('Vol. par Bassin'!7:7;MAX((COLONNE(2:2)-4)*('Vol. par Bassin'!7:7<>0))) =INDEX('Vol. par Bassin'!7:7;MAX((COLONNE(2:2)-1)*('Vol. par Bassin'!7:7<>0))) pour NE fer =INDEX('Vol. par Bassin'!6:6;MAX((COLONNE(1:1)-3)*('Vol. par Bassin'!6:6<>0))) =INDEX('Vol. par Bassin'!6:6;MAX((COLONNE(1:1))*('Vol. par Bassin'!6:6<>0))) route =INDEX('Vol. par Bassin'!7:7;MAX((COLONNE(2:2)-3)*('Vol. par Bassin'!7:7<>0))) =INDEX('Vol. par Bassin'!7:7;MAX((COLONNE(2:2))*('Vol. par Bassin'!7:7<>0)))

Mike-31 · Answer

Re,

par rapport à ton tableau ou tu as le mois précédent du dernier mois renseigné, en C8 et le dernier mois connu en D8 
=((D8-C8)/C8)
et tu formates la cellule en pourcentage 

par contre si les valeurs baissent pur calculer le pourcentage de baisse il faut inverser les valeurs
=((C8-D8)/D8)
A+
Mike-31

Une période d'échec est un moment rêvé pour semer les graines du savoir.

Mike-31 · Answer

Re, c'est exactement les mêmes formules matricielles mais en remontant de 2 mois alors pour la première formule pour rechercher la valeur du mois précédent pour SUD Fer =INDEX('Vol. par Bassin'!6:6;MAX((COLONNE(1:1)-5)*('Vol. par Bassin'!6:6<>0))) et pour rechercher la valeur du deuxième mois précédent soit janvier =INDEX('Vol. par Bassin'!6:6;MAX((COLONNE(1:1)-8)*('Vol. par Bassin'!6:6<>0))) ensuite additionner les deux valeurs divisé par deux, ou directement =(INDEX('Vol. par Bassin'!6:6;MAX((COLONNE(1:1)-8)*('Vol. par Bassin'!6:6<>0)))+INDEX('Vol. par Bassin'!6:6;MAX((COLONNE(1:1)-5)*('Vol. par Bassin'!6:6<>0))))/2 et idem en adaptant pour Route et les régions NE et NO par contre cette formule sera en erreur #VALEUR! tant que le mois de mars n'est pas renseigne puisqu'on remonte de deux mois, pour éviter cet affichage complète la formule avec une gestion d'erreur et confirme en matricielle =SIERREUR((INDEX('Vol. par Bassin'!6:6;MAX((COLONNE(1:1)-8)*('Vol. par Bassin'!6:6<>0)))+INDEX('Vol. par Bassin'!6:6;MAX((COLONNE(1:1)-5)*('Vol. par Bassin'!6:6<>0))))/2;"") A+ Mike-31 Une période d'échec est un moment rêvé pour semer les graines du savoir.

Mike-31 · Answer

Re,

ta moyenne se fera toujours sur les deux mois précédents il serait peut être intéressant qu'elle se fasse du premier mois au mois précédent en fonction du mois dans l'année exemple en Août de janvier à juillet !

Mike-31 · Answer

Re,

je te regarde ça dans la soirée

Mike-31 · Answer

Re,
en partant sur le principe que les régions SUD, NE et NO sont toutes les trois renseignées chaque mois, le principe de base pour calculer une moyenne est la division d'une somme par le nombre de montant qui compose cette somme.
la formule matricielle pour sommer les montants Sud des mois précédents le dernier mois enregistré
=SOMMEPROD((MOD(COLONNE(C6:INDIRECT(ADRESSE(LIGNE();MAX(SI(C6:AL6>0;COLONNE(C6:AL6)-3 ));4)))-2;3)=1)*(C6:INDIRECT(ADRESSE(LIGNE();MAX(SI(C6:AL6>0;COLONNE(C6:AL6)-3 ));4))))
pour calculer le nombre de versement
=SOMMEPROD((MOD(COLONNE(C6:INDIRECT(ADRESSE(LIGNE();MAX(SI(C6:AL6>0;COLONNE(C6:AL6)-3 ));4)))-2;3)=1)*(C6:INDIRECT(ADRESSE(LIGNE();MAX(SI(C6:AL6>0;COLONNE(C6:AL6)-3 ));4))>0))

pour écourter les formules, je te conseille de coller ces formules dans ton onglet Vol. par Bassin sur la ligne 6 et dans trois colonne que tu pourras masquer et appeler chaque résultat dans l'onglet de ton choix
ce qui donne pour calculer la moyenne Fer pour le SUD 
=SOMMEPROD((MOD(COLONNE(C6:INDIRECT(ADRESSE(LIGNE();MAX(SI(C6:AL6>0;COLONNE(C6:AL6)-3 ));4)))-2;3)=1)*(C6:INDIRECT(ADRESSE(LIGNE();MAX(SI(C6:AL6>0;COLONNE(C6:AL6)-3 ));4))))/SOMMEPROD((MOD(COLONNE(C6:INDIRECT(ADRESSE(LIGNE();MAX(SI(C6:AL6>0;COLONNE(C6:AL6)-3 ));4)))-2;3)=1)*(C6:INDIRECT(ADRESSE(LIGNE();MAX(SI(C6:AL6>0;COLONNE(C6:AL6)-3 ));4))>0))

pour le NE
=SOMMEPROD((MOD(COLONNE(D6:INDIRECT(ADRESSE(LIGNE();MAX(SI(C6:AL6>0;COLONNE(C6:AL6)-3 ));4)))-1;3)=0)*(D6:INDIRECT(ADRESSE(LIGNE();MAX(SI(C6:AL6>0;COLONNE(C6:AL6)-3 ));4))))/SOMMEPROD((MOD(COLONNE(D6:INDIRECT(ADRESSE(LIGNE();MAX(SI(C6:AL6>0;COLONNE(C6:AL6)-3 ));4)))-1;3)=0)*(D6:INDIRECT(ADRESSE(LIGNE();MAX(SI(C6:AL6>0;COLONNE(C6:AL6)-3 ));4))>0))

pour le NO
=SOMMEPROD((MOD(COLONNE(E6:INDIRECT(ADRESSE(LIGNE();MAX(SI(C6:AL6>0;COLONNE(C6:AL6)-3 ));4)))-1;3)=1)*(E6:INDIRECT(ADRESSE(LIGNE();MAX(SI(C6:AL6>0;COLONNE(C6:AL6)-3 ));4))))/SOMMEPROD((MOD(COLONNE(E6:INDIRECT(ADRESSE(LIGNE();MAX(SI(C6:AL6>0;COLONNE(C6:AL6)-3 ));4)))-1;3)=1)*(E6:INDIRECT(ADRESSE(LIGNE();MAX(SI(C6:AL6>0;COLONNE(C6:AL6)-3 ));4))>0))

pour les "ROUTE" de chaque département il suffit d'incrémenter les formules.
Si tu rencontres des problèmes, je te retournerai ton fichier

Mike-31 · Answer

Re,

tu les as bien déclarées en matricielle et collé sur ta feuille "Vol. par Bassin"
ensuite pour les avoir sur ta feuille "BDD-TDB" il suffit de faire =ta cellule concernée 

regarde ton fichier sur lequel j'ai supprimé les liens feuille "Vol. par Bassin" pour éviter que tes formules soient en erreur
 https://www.cjoint.com/c/FDggQN3zjOj 
A+
Mike-31

Une période d'échec est un moment rêvé pour semer les graines du savoir.

Mike-31 · Answer

Re,

colle dans un post la formule du début qui ne fonctionne plus 
 
A+
Mike-31

Une période d'échec est un moment rêvé pour semer les graines du savoir.

Mike-31 · Answer

Re,

a oui bien sur parce que j'ai mis tes nouvelles formule sur la ligne, alors on va contourner le problème en décalant l'emplacement de tes nouvelles formules exemple la première en AO13, ce qui va changer dans les nouvelles formule est l'emplacement de la ligne, explication dans les dernières formules tu verras LIGNE() qui renvoient le numéro de ligne soit pour une cellule en AN6 renvoie 6 si je décale les formules sur la ligne 2 (AO12) la syntaxe LIGNE() renvoie 12 et comme je veux récupérer les informations de la ligne 6 je modifie la syntaxe LIGNE() par LIGNE()-6
  https://www.cjoint.com/c/FDgh6hsgBij

Mike-31 · Answer

Re,

sur ta feuille BDD-TDB en H8 tu veux d'après ton dernier fichier 67090 
 et en H9 21115
mais en I8 que veux tu le cumul des mois précédents (janvier et février) soit 115728
c'est cela !

en H14 et I14 quelle somme doit il y avoir
idem H22, I22, H28 et I28

Mike-31 · Answer

Re,

regarde comme cela

 https://www.cjoint.com/c/FDgjLlDYaIj

 feuille Vol. par Bassin ligne de 28 à 31 peuvent être masquées pour protéger les formules

Mike-31 · Answer

Re,

comme il n'a que deux lignes de recherche, j'ai un peu simplifié les formules en supprimant LIGNE()-24 par le numéro de ligne soit 6 ou 7

 https://www.cjoint.com/c/FDgl3aoRosj

Mike-31 · Answer

Re,

tu peux revenir sans problème si tu rencontres un problème, la copie des formules ne doit pas poser de problème si tu respectes le numéro de ligne comme les bornes des plages.

n'oublie pas de mettre le statut de la discussion en résolu

Mike-31 · Answer

Re, alors si j'ai bien compris pour sud fer, toujours en matricielle =INDEX(6:6;MAX((COLONNE(1:1)-5)*(6:6<>0))) NE =INDEX(6:6;MAX((COLONNE(1:1)-4)*(6:6<>0))) NO =INDEX(6:6;MAX((COLONNE(1:1)-3)*(6:6<>0))) et pour route incrémente d'une cellule vers le bas

Mike-31 · Answer

Re, cette formule te donne la dernière valeur d'une ligne différente de 0 =INDEX(6:6;MAX((COLONNE(1:1))*(6:6<>0))) on pourrait également écrire =INDEX(6:6;MAX((COLONNE(1:1))*(6:6>0))) donc pour avoir la valeur SUD du mois précédent il faut ajouter -5 puisque par rapport à la dernière valeur de la ligne, SUD est décalée de 5 colonnes à gauche =INDEX(6:6;MAX((COLONNE(1:1)-5)*(6:6<>0))) NE de -4 et NO de -3 A+ Mike-31 Une période d'échec est un moment rêvé pour semer les graines du savoir.

Mike-31 · Answer

Re,

ce qui pose problème dans l'onglet "Budget-Réel" sont les deux colonnes "total" AB:AC
serait possible de les placer en début de tableau exemple en D et E ou il va falloir passer par des formules à rallonge du VBA

Mike-31 · Answer

Re,

cela ne changera rien, il y a très peu de possibilités par formule pour isoler la dernière valeur d'une ligne ou colonne, hors les totaux que tu as en fin de ligne même décalé seront toujours sur la ligne, c'est pour cela que dans l'onglet "Vol. par Bassin" on a déplacé les formules sous les tableaux 
le déplacement en début de tableau ne prendra que 2 secondes si cela ne dérange pas la hiérarchie
A+
Mike-31

Une période d'échec est un moment rêvé pour semer les graines du savoir.

Mike-31 · Answer

Re,
pour ne pas changer la structure de l'onglet "Budget-REEL" j'ai dû repenser les formules et ajoutant sous chaque tableau un ligne somme qui sert de déclencher de calcul qui ne doit contenir que les sommes de chaque catégorie et rester vide au delà du tableau et peut être masquée
il ne te reste plus qu'a rapatrier les valeurs souhaitées sur l'onglet de ton choix
  https://www.cjoint.com/c/FDjhtdxib7j

Mike-31 · Answer

Re,

chaque calcul est indépendant, donc tu peux supprimer les colonnes que tu souhaites

Mike-31 · Answer

Re,

la ligne total est une ligne qui sert au déclenchement des formules, exemple ligne 18, la première somme positive est en I18 ce qui déclenche les calculs des mois précédent soit janvier et février Budget et Réel
mais effectivement le 0 de janvier n'est pas pris en compte et donc la moyenne est fausse, je regarde comment contourner le problème

Mike-31 · Answer

Re,

regarde ton fichier voir si cela correspond à tes attentes, il peut y avoir un décalage avec les résultats qui vient de l'affichage des données que tu rapatries de ton fichier "Suivi des opérateurs transports - 2016.xls"
exemple E11 affiche 18 mais en fait tu as des décimales 18,1121, en G11 16,0901 etc et lorsque tu additionnes ces valeurs, les décimales s'additionnent ce qui peut influer sur des résultats
 https://www.cjoint.com/c/FDutzCG7bEj

Mike-31 · Answer

R5e,

pour fonctionner comme tu veux il faut un élément déclencheur, sous chaque tableau il y a deux lignes que j'ai masquées, exemple lignes 18 et 19, 34 et 35 etc ...
ensuite en AE7, AF7 comme dans chaque mode de calcul j'ai rajouté l'affichage automatique du mois concerné, ces données ne servent à rien dans les calculs mais permettent à l'utilisateur de savoir d'un seul coup d'œil quel est le dernier mois concerné par les calculs au dessous. c'est simplement un petit complément qui je pense intéressant

Mike-31 · Answer

Re,

alors peut être comme cela
 https://www.cjoint.com/c/FDvrxfpRIzj

Mike-31 · Answer

Re,

teste le fichier et relève la cellule qui te semble donner un résultat erroné que j'aille directement sur la formule, mais le résultat semble être bon et change lorsque tu saisis de nouvelles données ou change de mois par catégorie

Mike-31 · Answer

Re,

es ce le réel qui déclenche le calcul, parce que OFI as des valeurs Budget jusqu'en Décembre.
Si c'est la colonne Réel qui sert de référence ce qui était le cas dans les propositions précédentes tous les calculs pourront présenter la même erreur.
J'attends ta réponse pour modifier

Mike-31 · Answer

Re,
alors comme cela
 https://www.cjoint.com/c/FDwjB2wA2Jj

Mike-31 · Answer

Re,
Es tu sur que  "Cumul mois précèdent" le budget devrait être égal à 0
si tu additionnes les  mois précédent au dernier mois connu soit pour la premier module Janvier et Février le cumul me semble juste

Mike-31 · Answer

Re, exact, en AK55 colle cette formule que tu confirme en matricielle =SOMMEPROD((MOD(COLONNE(D55:INDIRECT(ADRESSE(LIGNE();MAX(SI($60:$60<>"";COLONNE($60:$60)-1 ));4)))-1;2)=1)*(D55:INDIRECT(ADRESSE(LIGNE();MAX(SI($60:$60<>"";COLONNE($60:$60)-1 ));4)))) en Al =SOMMEPROD((MOD(COLONNE(D55:INDIRECT(ADRESSE(LIGNE();MAX(SI($60:$60<>"";COLONNE($60:$60)-1 ));4)))-1;2)=0)*(D55:INDIRECT(ADRESSE(LIGNE();MAX(SI($60:$60<>"";COLONNE($60:$60)-1 ));4)))) en AN55 =SIERREUR(SOMMEPROD((MOD(COLONNE(D55:INDIRECT(ADRESSE(LIGNE();MAX(SI($60:$60<>"";COLONNE($60:$60)-1 ));4)))-1;2)=1)*(D55:INDIRECT(ADRESSE(LIGNE();MAX(SI($60:$60<>"";COLONNE($60:$60)-1 ));4))))/SOMMEPROD((MOD(COLONNE(D55:INDIRECT(ADRESSE(LIGNE();MAX(SI($60:$60<>"";COLONNE($60:$60)-1 ));4)))-1;2)=1)*(D55:INDIRECT(ADRESSE(LIGNE();MAX(SI($60:$60<>"";COLONNE($60:$60)-1 ));4))<>""));"") en AO55 =SIERREUR(SOMMEPROD((MOD(COLONNE(D55:INDIRECT(ADRESSE(LIGNE();MAX(SI($60:$60<>"";COLONNE($60:$60)-1 ));4)))-1;2)=0)*(D55:INDIRECT(ADRESSE(LIGNE();MAX(SI($60:$60<>"";COLONNE($60:$60)-1 ));4))))/SOMMEPROD((MOD(COLONNE(D55:INDIRECT(ADRESSE(LIGNE();MAX(SI($60:$60<>"";COLONNE($60:$60)-1 ));4)))-1;2)=0)*(D55:INDIRECT(ADRESSE(LIGNE();MAX(SI($60:$60<>"";COLONNE($60:$60)-1 ));4))<>""));"") incrémente les quatre formules vers le bas jusqu’à la ligne 59 A+ Mike-31 Une période d'échec est un moment rêvé pour semer les graines du savoir.

Recherche de données en fonction du mois en cours sur EXCEL

31 réponses

Discussions similaires

Newsletters