Microsoft 365 Formule pour calculer la consommation électrique de bateau

R

Romvin

XLDnaute Nouveau
Bonjour à toutes et à tous,

Pour mon stage on m'a demandé de créer une formule, ça fait quelques jours que je suis dessus et je commence à perdre espoir.

Je dispose d'un jeu de donnée. Ce jeu de donnée excel correspond aux heures d'arrivé et heures de départ des bateaux dans un port.

Il y a donc 4 colones importantes :

-date d'entrée
-heure d'entrée
-date de sortie
-heure de sortie

IL y a 4 tarification électrique différentes des bateaux en fonction de l'heure et de la saison :
Prix de fourniture proposés en €HT/MWh :

HPH = 918,42
HCH= 265,97
HPE =243,80
HCE = 57,30

HPH (Heures Pleines Hiver)
HCH (Heures Creuses Hiver)
HPE (Heures Pleines Eté)
HCE (Heures Creuses Eté)

Hiver du 01/01/2022 au 31/03/2022 et du 01/11/2022 au 31/12/2022
Eté du 01/04/2022 au 31/10/2022

Heures Pleines de 7h31 à 21h29
Heures Creuses de 21h30 à 7h30

Je souhaiterai établir une formule permettant de facturer les bateaux en fonction de leur date et heure d'entrée et par leur date et heure de sortie. Un bateau peut avoir plusieurs tarification différente pendant sa durée d'escale.
Je ne sais pas s'il est possible de créer une formule directement pour ça ou alors dans un premier temps établir les heures creuses et heures pleines pour un bateau et ensuite facturer les heures par prix de l'électricité.
Voila merci d'avance pour ceux qui se pencheront sur mon problème.
 

Pièces jointes

  • Temps d'escale.xlsx
    151.5 KB · Affichages: 19
Trier par date Trier par votes
Cousinhub

Cousinhub

XLDnaute Barbatruc
Bonjour,
Dans la pièce jointe, une solution par le biais de Power Query (en natif dans ta version)
Ce n'est certes pas la plus concise, mais les contrôles effectués dans l'onglet "T_Final" me donnent le même nombre de minutes de connexion par navire.
Je sais que ta demande concernait la résolution par le biais de formules, mais mon travail peut "peut-être" aider les "formulistes", afin de vérifier si on trouve les mêmes résultats (Attention, je ne confirme pas que mes simples contrôles induisent de fait les vrais résultats...)
Comme ces données datent de l'année dernière, je suppose que tes tuteurs de stage disposent des bons résultats, donc vous pourrez les comparer.
Bonne apm, et bon W-E
Edit, pour mettre à jour, ruban "Données", "Actualiser tout"
Edit2, fichier V2 erroné, voir fichier V3
 
Dernière édition:
Vote positif 0 Vote négatif
N

njhub

XLDnaute Occasionnel
Bonjour,

Voyez dans le fichier joint, s'il fonctionne chez vous...
RomvinEscaleGlobal.png

Comme vous pourrez le remarquer dans la formule en R12, le tarif a été modulé par un coefficient, 18% entre les deux dernières parenthèses, pour ajuster la tarification de la puissance disponible à quai par la puissance réellement utilisée...

Sinon, les formules utilisées sont :
Heures Creuses
21h30 à 07h30 HCH = 265,97 €HT/MWh
Temps HCH (min)
Code: 
=SI(INDIRECT("O"&LIGNE();1)="NON";"";SOMME(SI(H_Debconx<7,5/24;7,5/24-H_Debconx;0);SI(H_Debconx>21,5/24;24/24-H_Debconx;0);SI(H_Finconx>21,5/24;24/24-H_Finconx;0);SI(ET(S_Dt>E_Dt;(Finconx-Debconx)<24/24;H_Finconx<21,5/24);7,5/24;0);SI(H_Finconx<7,5/24;7,5/24-H_Finconx;0);SI((Finconx-Debconx)>24/24;(S_Dt-(E_Dt))*10/24;0))*CHOISIR(MOIS(INDIRECT("I"&LIGNE();1));265,97;265,97;265,97;0;0;0;0;0;0;0;265,97;265,97))

21h30 à 07h30 HCE = 57,30 €HT/MWh
Temps HCE (min)
Code: 
=SI(INDIRECT("O"&LIGNE();1)="NON";"";SOMME(SI(H_Debconx<7,5/24;7,5/24-H_Debconx;0);SI(H_Debconx>21,5/24;24/24-H_Debconx;0);SI(H_Finconx>21,5/24;24/24-H_Finconx;0);SI(ET(S_Dt>E_Dt;H_Finconx<21,5/24);7,5/24;0);SI(H_Finconx<7,5/24;7,5/24-H_Finconx;0);SI((Finconx-Debconx)>24/24;(S_Dt-(E_Dt))*10/24))*CHOISIR(MOIS(INDIRECT("I"&LIGNE();1));0;0;0;57,3;57,3;57,3;57,3;57,3;57,3;57,3;0;0))


Heures Pleines
07h31 à 21h29 HPH = 918,42 €HT/MWh
Temps HPH (min)
Code: 
=SI(INDIRECT("O"&LIGNE();1)="NON";"";SOMME((S_Dt-(E_Dt+1))*14/24;H_Finconx-7,5/24;21,5/24-H_Debconx)*CHOISIR(MOIS(INDIRECT("I"&LIGNE();1));918,42;918,42;918,42;0;0;0;0;0;0;0;918,42;918,42))

07h31 à 21h29 HPE = 243,80 €HT/MWh
Temps HPE (min)
Code: 
=SI(INDIRECT("O"&LIGNE();1)="NON";"";SOMME((S_Dt-(E_Dt+1))*14/24;H_Finconx-7,5/24;21,5/24-H_Debconx)*CHOISIR(MOIS(INDIRECT("I"&LIGNE();1));0;0;0;243,80;243,80;243,80;243,80;243,80;243,80;243,80;0;0))

Les formules contiennent des expressions nommées qui correspondent à :
E_Dt
Code: 
SI(INDIRECT("O"&LIGNE();1)="NON";0;INDIRECT("I"&LIGNE();1))
H_Debconx
Code: 
SI(INDIRECT("O"&LIGNE();1)="NON";0;INDIRECT("J"&LIGNE();1)+"00:57:30")
Debconx
Code: 
E_Dt+H_Debconx

S_Dt
Code: 
SI(INDIRECT("O"&LIGNE();1)="NON";0;INDIRECT("K"&LIGNE();1))
H_Finconx
Code: 
SI(INDIRECT("O"&LIGNE();1)="NON";0;INDIRECT("L"&LIGNE();1)-"00:57:30")
Finconx
Code: 
S_Dt+H_Finconx

RomvinEscaleFonction.png
 

Pièces jointes

  • Romvin_Temps d'escale.xlsx
    212.6 KB · Affichages: 7
Vote positif 0 Vote négatif
R

Romvin

XLDnaute Nouveau
Cousinhub à dit:
Bonjour,
Dans la pièce jointe, une solution par le biais de Power Query (en natif dans ta version)
Ce n'est certes pas la plus concise, mais les contrôles effectués dans l'onglet "T_Final" me donnent le même nombre de minutes de connexion par navire.
Je sais que ta demande concernait la résolution par le biais de formules, mais mon travail peut "peut-être" aider les "formulistes", afin de vérifier si on trouve les mêmes résultats (Attention, je ne confirme pas que mes simples contrôles induisent de fait les vrais résultats...)
Comme ces données datent de l'année dernière, je suppose que tes tuteurs de stage disposent des bons résultats, donc vous pourrez les comparer.
Bonne apm, et bon W-E
Edit, pour mettre à jour, ruban "Données", "Actualiser tout"
Cliquez pour agrandir...
Super Cousinhub,
Je vais comparer tes résultats lundi quand je serais de retour au travail. Je ne sais pas quel est les périodicités des acquittement pour les croisiéristes (trimestrielle, semestrielle ou annuelle) dans ce cas ça serait une solution.
Merci beaucoup.
 
Vote positif 0 Vote négatif
R

Romvin

XLDnaute Nouveau
njhub à dit:
Bonjour,

Voyez dans le fichier joint, s'il fonctionne chez vous...
Regarde la pièce jointe 1170504
Comme vous pourrez le remarquer dans la formule en R12, le tarif a été modulé par un coefficient, 18% entre les deux dernières parenthèses, pour ajuster la tarification de la puissance disponible à quai par la puissance réellement utilisée...

Sinon, les formules utilisées sont :
Heures Creuses
21h30 à 07h30 HCH = 265,97 €HT/MWh
Temps HCH (min)
Code: 
=SI(INDIRECT("O"&LIGNE();1)="NON";"";SOMME(SI(H_Debconx<7,5/24;7,5/24-H_Debconx;0);SI(H_Debconx>21,5/24;24/24-H_Debconx;0);SI(H_Finconx>21,5/24;24/24-H_Finconx;0);SI(ET(S_Dt>E_Dt;(Finconx-Debconx)<24/24;H_Finconx<21,5/24);7,5/24;0);SI(H_Finconx<7,5/24;7,5/24-H_Finconx;0);SI((Finconx-Debconx)>24/24;(S_Dt-(E_Dt))*10/24;0))*CHOISIR(MOIS(INDIRECT("I"&LIGNE();1));265,97;265,97;265,97;0;0;0;0;0;0;0;265,97;265,97))

21h30 à 07h30 HCE = 57,30 €HT/MWh
Temps HCE (min)
Code: 
=SI(INDIRECT("O"&LIGNE();1)="NON";"";SOMME(SI(H_Debconx<7,5/24;7,5/24-H_Debconx;0);SI(H_Debconx>21,5/24;24/24-H_Debconx;0);SI(H_Finconx>21,5/24;24/24-H_Finconx;0);SI(ET(S_Dt>E_Dt;H_Finconx<21,5/24);7,5/24;0);SI(H_Finconx<7,5/24;7,5/24-H_Finconx;0);SI((Finconx-Debconx)>24/24;(S_Dt-(E_Dt))*10/24))*CHOISIR(MOIS(INDIRECT("I"&LIGNE();1));0;0;0;57,3;57,3;57,3;57,3;57,3;57,3;57,3;0;0))


Heures Pleines
07h31 à 21h29 HPH = 918,42 €HT/MWh
Temps HPH (min)
Code: 
=SI(INDIRECT("O"&LIGNE();1)="NON";"";SOMME((S_Dt-(E_Dt+1))*14/24;H_Finconx-7,5/24;21,5/24-H_Debconx)*CHOISIR(MOIS(INDIRECT("I"&LIGNE();1));918,42;918,42;918,42;0;0;0;0;0;0;0;918,42;918,42))

07h31 à 21h29 HPE = 243,80 €HT/MWh
Temps HPE (min)
Code: 
=SI(INDIRECT("O"&LIGNE();1)="NON";"";SOMME((S_Dt-(E_Dt+1))*14/24;H_Finconx-7,5/24;21,5/24-H_Debconx)*CHOISIR(MOIS(INDIRECT("I"&LIGNE();1));0;0;0;243,80;243,80;243,80;243,80;243,80;243,80;243,80;0;0))

Les formules contiennent des expressions nommées qui correspondent à :
E_Dt
Code: 
SI(INDIRECT("O"&LIGNE();1)="NON";0;INDIRECT("I"&LIGNE();1))
H_Debconx
Code: 
SI(INDIRECT("O"&LIGNE();1)="NON";0;INDIRECT("J"&LIGNE();1)+"00:57:30")
Debconx
Code: 
E_Dt+H_Debconx

S_Dt
Code: 
SI(INDIRECT("O"&LIGNE();1)="NON";0;INDIRECT("K"&LIGNE();1))
H_Finconx
Code: 
SI(INDIRECT("O"&LIGNE();1)="NON";0;INDIRECT("L"&LIGNE();1)-"00:57:30")
Finconx
Code: 
S_Dt+H_Finconx

Regarde la pièce jointe 1170503
Cliquez pour agrandir...
 
Vote positif 0 Vote négatif
R

Romvin

XLDnaute Nouveau
njhub à dit:
Bonjour,

Voyez dans le fichier joint, s'il fonctionne chez vous...
Regarde la pièce jointe 1170504
Comme vous pourrez le remarquer dans la formule en R12, le tarif a été modulé par un coefficient, 18% entre les deux dernières parenthèses, pour ajuster la tarification de la puissance disponible à quai par la puissance réellement utilisée...

Sinon, les formules utilisées sont :
Heures Creuses
21h30 à 07h30 HCH = 265,97 €HT/MWh
Temps HCH (min)
Code: 
=SI(INDIRECT("O"&LIGNE();1)="NON";"";SOMME(SI(H_Debconx<7,5/24;7,5/24-H_Debconx;0);SI(H_Debconx>21,5/24;24/24-H_Debconx;0);SI(H_Finconx>21,5/24;24/24-H_Finconx;0);SI(ET(S_Dt>E_Dt;(Finconx-Debconx)<24/24;H_Finconx<21,5/24);7,5/24;0);SI(H_Finconx<7,5/24;7,5/24-H_Finconx;0);SI((Finconx-Debconx)>24/24;(S_Dt-(E_Dt))*10/24;0))*CHOISIR(MOIS(INDIRECT("I"&LIGNE();1));265,97;265,97;265,97;0;0;0;0;0;0;0;265,97;265,97))

21h30 à 07h30 HCE = 57,30 €HT/MWh
Temps HCE (min)
Code: 
=SI(INDIRECT("O"&LIGNE();1)="NON";"";SOMME(SI(H_Debconx<7,5/24;7,5/24-H_Debconx;0);SI(H_Debconx>21,5/24;24/24-H_Debconx;0);SI(H_Finconx>21,5/24;24/24-H_Finconx;0);SI(ET(S_Dt>E_Dt;H_Finconx<21,5/24);7,5/24;0);SI(H_Finconx<7,5/24;7,5/24-H_Finconx;0);SI((Finconx-Debconx)>24/24;(S_Dt-(E_Dt))*10/24))*CHOISIR(MOIS(INDIRECT("I"&LIGNE();1));0;0;0;57,3;57,3;57,3;57,3;57,3;57,3;57,3;0;0))


Heures Pleines
07h31 à 21h29 HPH = 918,42 €HT/MWh
Temps HPH (min)
Code: 
=SI(INDIRECT("O"&LIGNE();1)="NON";"";SOMME((S_Dt-(E_Dt+1))*14/24;H_Finconx-7,5/24;21,5/24-H_Debconx)*CHOISIR(MOIS(INDIRECT("I"&LIGNE();1));918,42;918,42;918,42;0;0;0;0;0;0;0;918,42;918,42))

07h31 à 21h29 HPE = 243,80 €HT/MWh
Temps HPE (min)
Code: 
=SI(INDIRECT("O"&LIGNE();1)="NON";"";SOMME((S_Dt-(E_Dt+1))*14/24;H_Finconx-7,5/24;21,5/24-H_Debconx)*CHOISIR(MOIS(INDIRECT("I"&LIGNE();1));0;0;0;243,80;243,80;243,80;243,80;243,80;243,80;243,80;0;0))

Les formules contiennent des expressions nommées qui correspondent à :
E_Dt
Code: 
SI(INDIRECT("O"&LIGNE();1)="NON";0;INDIRECT("I"&LIGNE();1))
H_Debconx
Code: 
SI(INDIRECT("O"&LIGNE();1)="NON";0;INDIRECT("J"&LIGNE();1)+"00:57:30")
Debconx
Code: 
E_Dt+H_Debconx

S_Dt
Code: 
SI(INDIRECT("O"&LIGNE();1)="NON";0;INDIRECT("K"&LIGNE();1))
H_Finconx
Code: 
SI(INDIRECT("O"&LIGNE();1)="NON";0;INDIRECT("L"&LIGNE();1)-"00:57:30")
Finconx
Code: 
S_Dt+H_Finconx

Regarde la pièce jointe 1170503
Cliquez pour agrandir...

Bonjour,
J'ai l'impression que vous avez répondu à mon problème !! C'est génial.
Cependant le fichier ne fonctionne pas chez moi. En dehors de ce problème je ne comprends pas les dernières formules, ni leur intérêts. Pouvez-vous m'indiquer une documentation ou autre format pour comprendre ce que vous appelez " expressions nommées"?
Je vous remercie.
 
Vote positif 0 Vote négatif
N

njhub

XLDnaute Occasionnel
Romvin à dit:
Bonjour,
J'ai l'impression que vous avez répondu à mon problème !! C'est génial.
Cependant le fichier ne fonctionne pas chez moi. En dehors de ce problème je ne comprends pas les dernières formules, ni leur intérêts. Pouvez-vous m'indiquer une documentation ou autre format pour comprendre ce que vous appelez " expressions nommées"?
Je vous remercie.
Cliquez pour agrandir...
Bonjour,

Les expressions nommées, sont des éléments calculés qui sont réutilisés plusieurs fois dans les formules, au lieu de les recalculer à chaque utilisation, elles facilitent la conception et la lecture des formules...

A chacune d'entre elle correspond une formule décrite dans ma première réponse, si vous remplacez l'"expression" par sa formule de calcul, les autres formules s'en trouvent allongées, deviennent, incompréhensibles et difficiles à corriger.

Par exemple :

Fin de connexion = date de sortie + heure de fin de connexion

Finconx=S_Dt+H_Finconx

deviendrait :

Finconx=SI(INDIRECT("O"&LIGNE();1)="NON";0;INDIRECT("K"&LIGNE();1))+SI(INDIRECT("O"&LIGNE();1)="NON";0;INDIRECT("L"&LIGNE();1)-"00:57:30")

;)
 
Dernière édition:
Vote positif 0 Vote négatif
Cousinhub

Cousinhub

XLDnaute Barbatruc
Hello,
Tiens, njhub, comme tu passes par là...
Est-ce qu'il te serait possible de donner uniquement les temps de connexion (sans faire de calculs de coûts)?
(J'ai essayé, mais mes essais ne sont pas concluants)
C'est juste pour comparer tes valeurs avec celles que j'obtiens (sans compter les HP qu'à priori tu ne calcules pas, mais en faisant HP + HPH sur mon fichier, on devrait obtenir les mêmes HPH que toi)
Bonne apm (et bon courage aux mahorais)
 
Vote positif 0 Vote négatif
N

njhub

XLDnaute Occasionnel
Bonjour Cousinhub,

C'est le contenu de la colonne Q

Code: 
=SI(O2="NON";"";Finconx-Debconx)

Finconx=SI(INDIRECT("O"&LIGNE();1)="NON";0;INDIRECT("K"&LIGNE();1))+SI(INDIRECT("O"&LIGNE();1)="NON";0;INDIRECT("L"&LIGNE();1)-"00:57:30")

Debconx=SI(INDIRECT("O"&LIGNE();1)="NON";0;INDIRECT("I"&LIGNE();1))+SI(INDIRECT("O"&LIGNE();1)="NON";0;INDIRECT("J"&LIGNE();1)+"00:57:30")

Au final :
Code: 
=SOMME((SI(INDIRECT("O"&LIGNE();1)="NON";0;INDIRECT("K"&LIGNE();1))+SI(INDIRECT("O"&LIGNE();1)="NON";0;INDIRECT("L"&LIGNE();1)-"00:57:30"));-(SI(INDIRECT("O"&LIGNE();1)="NON";0;INDIRECT("I"&LIGNE();1))+SI(INDIRECT("O"&LIGNE();1)="NON";0;INDIRECT("J"&LIGNE();1)+"00:57:30")))
 
Vote positif 0 Vote négatif
Cousinhub

Cousinhub

XLDnaute Barbatruc
Re-,
Oui, pour le temps de connexion, mais ce qui m'intéresserait, ce serait les temps calculés par HPH, HCH, HPE et HCE (et non leurs coûts)
Merci par avance
PS, pour mes calculs, je mettais les 25mn de temps de navigation avant la connexion (donc 25 mn + 45 mn avant, et 45 mn après)
 
Vote positif 0 Vote négatif
N

njhub

XLDnaute Occasionnel
Bonjour Cousinhub,

C'est le contenu des colonnes R, S, T et U sans multiplier par la fonction CHOISIR() qui sélectionne le tarif en fonction du mois.

Heures Creuses
21h30 à 07h30 HCH = 265,97 €HT/MWh
Temps HCH (min)

Code:


=SI(INDIRECT("O"&LIGNE();1)="NON";"";SOMME(SI(H_Debconx<7,5/24;7,5/24-H_Debconx;0);SI(H_Debconx>21,5/24;24/24-H_Debconx;0);SI(H_Finconx>21,5/24;24/24-H_Finconx;0);SI(ET(S_Dt>E_Dt;(Finconx-Debconx)<24/24;H_Finconx<21,5/24);7,5/24;0);SI(H_Finconx<7,5/24;7,5/24-H_Finconx;0);SI((Finconx-Debconx)>24/24;(S_Dt-(E_Dt))*10/24;0))*CHOISIR(MOIS(INDIRECT("I"&LIGNE();1));265,97;265,97;265,97;0;0;0;0;0;0;0;265,97;265,97))


21h30 à 07h30 HCE = 57,30 €HT/MWh
Temps HCE (min)

Code:


=SI(INDIRECT("O"&LIGNE();1)="NON";"";SOMME(SI(H_Debconx<7,5/24;7,5/24-H_Debconx;0);SI(H_Debconx>21,5/24;24/24-H_Debconx;0);SI(H_Finconx>21,5/24;24/24-H_Finconx;0);SI(ET(S_Dt>E_Dt;H_Finconx<21,5/24);7,5/24;0);SI(H_Finconx<7,5/24;7,5/24-H_Finconx;0);SI((Finconx-Debconx)>24/24;(S_Dt-(E_Dt))*10/24))*CHOISIR(MOIS(INDIRECT("I"&LIGNE();1));0;0;0;57,3;57,3;57,3;57,3;57,3;57,3;57,3;0;0))


Heures Pleines
07h31 à 21h29 HPH = 918,42 €HT/MWh
Temps HPH (min)

Code:


=SI(INDIRECT("O"&LIGNE();1)="NON";"";SOMME((S_Dt-(E_Dt+1))*14/24;H_Finconx-7,5/24;21,5/24-H_Debconx)*CHOISIR(MOIS(INDIRECT("I"&LIGNE();1));918,42;918,42;918,42;0;0;0;0;0;0;0;918,42;918,42))


07h31 à 21h29 HPE = 243,80 €HT/MWh
Temps HPE (min)

Code:


=SI(INDIRECT("O"&LIGNE();1)="NON";"";SOMME((S_Dt-(E_Dt+1))*14/24;H_Finconx-7,5/24;21,5/24-H_Debconx)*CHOISIR(MOIS(INDIRECT("I"&LIGNE();1));0;0;0;243,80;243,80;243,80;243,80;243,80;243,80;243,80;0;0))

Du coup je pense qu'il y a une erreur, les formules HC étant différentes...

Dans la formule HTE il faut probablement remplacer :
SI(ET(S_Dt>E_Dt;H_Finconx<21,5/24);7,5/24;0);
par:
SI(ET(S_Dt>E_Dt;(Finconx-Debconx)<24/24;H_Finconx<21,5/24);7,5/24;0);
 
Dernière édition:
Vote positif 0 Vote négatif
Cousinhub

Cousinhub

XLDnaute Barbatruc
Re-,
C'est bien ce que j'avais essayé, mais j'ai des incohérences...🧐
En remplaçant les prix par 1 (donc en maintenant la fonction CHOISIR), on obtient le nb de minutes (du moins, si j'ai bien compris tes formules)
Et je trouve un différentiel de 10 837,5 minutes en plus pour toi...
Et de grosses différences entre les totaux horaires, surtout en hiver...
Hi, mon ami, regarde ma réponse dans le #21 (des @ se sont invités...)
 
Vote positif 0 Vote négatif

Discussions similaires

V
  • Question
ARRONDIR à la demi-heure.
Réponses
8
Affichages
2 K
Vyronas
V

Membres actuellement en ligne

2 membres et 27 invités.

Partager cette page

Tutos vidéos

Power Query : Ajouter des requêtes (Excel et Power BI)
Créez votre première fonction LAMBDA dans Excel en 3 étapes!
LAMBDA, l’arme secrète de Microsoft pour Excel !
Office Insider - Découvrez les nouveautés Excel avant tout le monde

Statistiques des forums

Discussions
306 164
Messages
2 033 642
Membres
227 000
dernier inscrit
fabiop