Excel - Formule avancée
Bonjour,
Je me suis crée un fichier excel pour vérifier les coûts de location des entreprises, dans ce fichier sous forme de tableau j'ai plusieurs données, la tâche en question, une date de début, une date de fin et un taux de location journalier. Ce qui me permet de savoir combien va me coûter les différentes tâches.
Je vous joins un exemple de mon tableau pour me comprendre.
A l'heure d'aujourd'hui j'ai réussi à faire mes formules (certes peut-être pas optimisées) pour le coût de location au mois, le coût du premier mois (car j'ai une date de début par exemple : 21/12/2016, et donc je paye uniquement du 21/12/2016 au 31) et pareil pour la date de fin (date de fin par exemple le 15/12/2016, je paye donc la location du 1/12/16 au 15).
Aujourd'hui je voudrais rajouter une colonne dans mon tableau et choisir par exemple le mois de août 2016 et qu'il me calcul le coût de location du mois d'août 2016 en tenant compte des tâches qui ont commencées ce mois ci, celles qui ont terminées et celles où je paye le mois complet. Compliqué à expliqué à l'écrit ...
J'espère avoir été clair dans mes explications.
En espérant que quelqu'un pourra m'aider.
Je vous remercie d'avance.
Cordialement,
Bonjour,
Une précision : pour les tâches qui sont à cheval sur deux mois comment se fait le calcul ?
faut-il faire une règle de trois pour ne tenir compte que des jours du mois recherché ?
Bonjour,
Je n'ai pas trop compris, les tâches à cheval sur deux mois ?
Par exemple si le mois recherché est septembre 2016 et que ma tâche :
Date début : 12/09/16
Date de fin : 22/10/16
- Je prends uniquement les 19 jours restant du mois de septembre
OU
Date début : 12/09/16
Date de fin : 22/09/16
- Je prends uniquement les 11 jours entre le 12 et le 22
En sachant que je compte la journée du 12 et la journée du 22.
Bonjour
Formule en M6 à incrémenter vers le bas
=SOMMEPROD((ANNEE(LIGNE(INDIRECT([@[Date de début]]&":"&[@[Date de fin]])))=ANNEE(($M$2)))*(MOIS(LIGNE(INDIRECT([@[Date de début]]&":"&[@[Date de fin]])))=MOIS($M$2)))*[@[Points de location]]Cordialement
Bonjour Amadéus,
Merci beaucoup pour votre aide. D'après quelque test tout à l'air de fonctionner !
Cela faisait plus d'un mois que je cherchais une solution...
Dernière question, pourriez-vous m'expliquer votre formule, car je vous cache pas que je ne l'a comprends pas vraiment :/
Juste un détail.
La formule fonctionne uniquement si j'ai une date de début et une date de fin.
J'aimerai quelle fonctionne aussi comme pour cet exemple :
Mois recherché : juillet 2016
Date de début : 20/05/16
Date de fin : Non connu
Donc nombre de jours à calculé '31' car le mois de juillet 2016 est supérieur à la date de début.
Car toutes mes tâches n'ont pas forcément une date de fin.
Merci encore ! Je vais regarder de mon côté si je peux pas modifier cette formule.
Bonjour
Pour la nouvelle formule, si la date de fin n'est pas indiquée, le calcul se fait donc entre Date de début et aujourd'hui et si le mois recherché y est inclus.
=SI([@[Date de fin]]>0;SOMMEPROD((ANNEE(LIGNE(INDIRECT([@[Date de début]]&":"&[@[Date de fin]])))=ANNEE(($M$2)))*(MOIS(LIGNE(INDIRECT([@[Date de début]]&":"&[@[Date de fin]])))=MOIS($M$2)))*[@[Points de location]];SOMMEPROD((ANNEE(LIGNE(INDIRECT([@[Date de début]]&":"&AUJOURDHUI())))=ANNEE(($M$2)))*(MOIS(LIGNE(INDIRECT([@[Date de début]]&":"&AUJOURDHUI())))=MOIS($M$2)))*[@[Points de location]])Pour l'explication:
La formule
=SOMMEPROD((ANNEE(LIGNE(INDIRECT([@[Date de début]]&":"&[@[Date de fin]])))=ANNEE(($M$2)))*(MOIS(LIGNE(INDIRECT([@[Date de début]]&":"&[@[Date de fin]])))=MOIS($M$2)))*[@[Points de location]]simule une matrice de tous les jours entre Date de début et Date de fin et somme les jours de cette matrice qui répondent aux critères recherchés (ici l'année et le mois)
Cette matrice est représentée dans la fonction par
LIGNE(INDIRECT([@[Date de début]]&":"&[@[Date de fin]]))Pour visualiser cette matrice , tu cliques sur le menu FORMULES > Dans Vérification des formules> Evaluer la formule.
Chaque clic sur le bouton évaluer te montre pas à pas le fonctionnement de la formule.
Cordialement
Un gros merci !
Je ne connais rien sur les matrices, je vais me pencher dessus.
Tout fonctionne maintenant je te remercie.
Bonne continuation à toi et de bonne fête de fin d'année !