Trouver l'écart entre plusieurs MIN et MAX

Bonjour à tous,

Parce qu'une démonstration est parfois bien plus efficace que des paroles farfelues, voici mon problème :

36classeur1.xlsx (9.70 Ko)
forum

Etant débutant sur Excel, et n'y connaissant absolument rien en macro/VBA etc., j'aimerai si possible trouver une formule qui me permet d'obtenir les Δ réels comme je l'ai illustré sur la photo/fichier. Le but étant, par la suite, de calculer des rendements [énergie consommée /quantité (chaud ou froid) produite]. Pour les Δ de quantité chaud/froid, rien de très compliqué étant donné que les données restent figées si ma consigne passe à 0. Cependant, je n'arrive pas à obtenir ce que je souhaite pour l'énergie consommée, qui elle varie continuellement qu'on soit en chaud ou en froid...

D'avance un grand grand merci à tous pour votre aide !

PS: ceci est un tableau très très (très) simplifié de mon vrai tableau, qui contient plus de 10.000 lignes (chacune représentant un moment de la journée) et dont les changements de consigne ne sont pas du tout aussi régulier. Donc si possible, j'aimerai obtenir une formule très générale qui s'applique à des tableaux bien plus aléatoires. Merciiiiiii :)

Bonsoir LaJoya,

Mettre ton tableau sous forme de tableau pour plus de facilité.

Un essai avec ce que j'ai compris.

Cordialement.

29classeur1-1.xlsx (12.29 Ko)

Bonjour Zebulon2,

Merci pour ta réponse ! Cependant, plusieurs problèmes se dressent devant moi Tout d'abord, je ne sais pour quelle raison, une partie de mon explication s'est volatilisée dans les limbes d'Internet... Zut. J'y expliquais qu'en fait, je dois calculer les rendements d'une pompe à chaleur, jour après jour. Et pour cela, j'ai besoin de savoir, en fonction de la consigne (chaud/froid) le delta Δ global sur la journée, en chaud/froid, qui concerne l'énergie consommée. Donc, en sommant les Δ par "section" si je puis dire. Par exemple, en sommant tous les Δ lorsque la pompe se lance en froid, puis se stop, puis se relance plus tard dans la journée, se stop etc. comme dans le fichier que j'ai envoyé. Je pense que ça tu l'avais compris !

Néanmoins, voici les problèmes que je rencontre :

1) mon Excel ne reconnait pas les formules MIN SI ENS/MAX SI ENS et m'affiche # # # # # # lorsque j'ouvre le fichier que tu m'as envoyé. Pourtant, j'ai bien Excel 2016 ! Peut-être existe-t-il une façon de détourner ce problème, en utilisant une autre formule ?

2) en fait, tous les jours, les données de la pompe sont enregistrés dans une feuille Excel qui porte comme nom de fichier la date du jour d'enregistrement. Le but de mon travail est donc de copier les feuilles d'enregistrement dans un classeur "COP" (opération faite manuellement). Ensuite, en feuille 1 de ce classeur COP, je dois construire un seul tableau unique qui permet de calculer les COP, en fonction de la date choisie. Le problème est le suivant : l'exemple que j'ai envoyé est très simplifié par rapport à la réalité. En effet, ce n'est pas un simple tableau de 15 lignes que je créé moi-même, mais ca peut aller jusque 10.000 lignes et +, et ce n'est pas moi qui le créé... Donc, je n'ai pas vraiment la possibilité de transformer chacune des feuilles d'enregistrement de données en Tableau, comme tu me le conseilles...

3) enfin, j'aimerai obtenir une formule générale, qui me permet de calculer ces Δ car dans l'exemple que tu m'as donné, tu répètes les opérations plusieurs fois. Certes, cela fonctionne... mais comme il se peut que la pompe change de mode (chaud/froid) 100x par jour, je ne sais jamais réellement à quelle ligne le changement s'opèrera...

Donc, pour résumer, j'aimerai si possible obtenir une formule différente de MIN SI ENS/MAX SI ENS qui me permet de calculer les Δ en mode chaud/froid, d'une feuille de donnée que je ne peux pas transformer en Tableau au préalable, et dont le nombre max de ligne varie d'un jour à l'autre de 1 à 10.000, avec des changements de mode qui s'opère de manière totalement quelconque...

Je sais que ça à l'air compliqué et pardonne moi si mon explication n'est pas très claire, je fais au mieux

Dans tous les cas, merci de ton aide !!!!

1) mon Excel ne reconnait pas les formules MIN SI ENS/MAX SI ENS et m'affiche # # # # # # lorsque j'ouvre le fichier que tu m'as envoyé. Pourtant, j'ai bien Excel 2016 ! Peut-être existe-t-il une façon de détourner ce problème, en utilisant une autre formule ?

Bonjour,

indique ta version d'excel dans ton profil de façon à avoir une réponse qui t'est adaptée

hello zebulon

Le but de mon travail est donc de copier les feuilles d'enregistrement dans un classeur "COP" (opération faite manuellement).

Cela n'est pas le sujet, mais sache que cela peut aussi s'automatiser facilement pour te faciliter la tâche ... peut-être un autre post.

Bonjour Steelson,

Merci pour ton conseil, j'ai modifié mon profil ! Je suis également preneur d'explications pour automatiser la copie des feuilles dans mon Classeur principal, si ca ne te dérange pas ! Sauf si tu penses que c'est mieux d'en faire un autre post plus adapté?

On peut partir sur un calcul via TCD (mais je n'ai pas la notion de date dans ton fichier)

23classeur1.xlsx (14.13 Ko)

Peut-être serait-t-il plus judicieux de ma part de mettre mon fichier réel, histoire que tu te rendes compte exactement de ce que je recherche je te fais ça tout de suite !

Et aussi les fichiers de base que tu dois assembler (cela devrait faire l'objet d'un autre post, mais il est probable qu'on y trouve aussi une solution telle que suggérée par zebulon que je salue

Normalement ta version excel 2016 accepte les max.si.ens et min.si.ens

Voici exactement sur quoi je travaille :

21classeur-cop.xlsx (25.45 Ko)

Je vais expliquer comment cela fonctionne, cela vous aidera à mieux appréhender le problème :

Via un logiciel, un utilisateur rentre les paramètres qu'il veut surveiller concernant la pompe à chaleur (PAC). Il a le choix entre 1 à 72 paramètres différents. (ce qui impliquera que les données ne se trouveront pas tjs dans la même colonne, vous comprendrez pas la suite). Ensuite, il appuie sur "Enregistrer", et les données s'enregistrent dans un fichier "X." jusqu'à ce qu'il décide d'appuyer sur "Terminer" pour terminer l'enregistrement. Il peut répéter ces opérations plusieurs fois par jour, en changeant les paramètres comme bon lui semble entre chaque enregistrement. A chaque enregistrement, une nouvelle feuille est créée dans le fichier "X".

L' objectif étant de déterminer, au terme de la journée, la consommation totale de la PAC et donc son COP (coefficient de performance) en mode chaud et en mode froid.

Quand l'enregistrement est terminé, l'utilisateur se rend sur le Classeur COP afin d'y observer ses résultats.

Dans ce classeur, voici comment il faut procéder :

1) Entrer la date choisie pour visualiser le COP de cette date.
2) Ouvrir le dossier s'y référant (ici "X", accessible via un lien hypertexte qui se crée) et renommer la feuille par la date en format aa.mm.jj. ATTENTION ! S'il existe plusieurs feuilles pour la même date, renommer les suivantes : aa.mm.jj(2), aa.mm.jj(3), etc.
3) Ouvrir la/les feuille(s) via le/les lien(s) créé(s) et l'/les insérer dans ce classeur : clic droit sur la nouvelle feuille ouverte -> déplacer ou copier -> liste déroulante -> Classeur COP -> cocher "en dernier" -> ok. (ici, c'est la feuille "20.10.20" qui a été copiée dans le Classeur COP).
La feuille se trouve désormais dans le Classeur COP.
4) De là, le COP se calcule tout seul en fonction des données se trouvant dans la nouvelle feuille collée, grâce à des formules qui s'y rapportent.

Pour l'instant, je ne travaille qu'avec une seule feuille de données, par date !

Etant donné que d'un enregistrement à l'autre le nombre de paramètre varie, l'emplacement de ceux-ci varie également dans le feuille 20.10.20. Par exemple, la colonne du paramètre"1.10.1.1007 | Générateur de chaleur consigne" ne se situera pas toujours en colonne H, mais parfois en E, ou en I, ou en Z, etc. J'ai donc réussi a créer une formule qui indique précisément la lettre de la colonne ou ce paramètre se situe, peu importe qu'il soit en A, B ou Z. J'en ai fais de même pour les 3 autres paramètres qui m'intéressent pour le calcul du COP, à savoir la quantité de froid/chaleur et l'importation d'énergie. Jusque là, tout va bien (bien que mes formules pourraient sans doute être simplifiées).

C'est maintenant que les choses se corsent...

Je dois donc, en fonction de la consigne de la colonne H de la feuille 20.10.20 (52= mode chaud, 18= mode froid, 0 = à l'arrêt), déterminer les Δ pour calculer à terme les COP. Pour ce qui est des Δ quantité de froid/chaud produit, je n'ai pas de problème car les données s'y référant n'évoluent que lorsque la consigne leur correspond. Donc, les valeurs MIN et MAX sont les bonnes.

C'est pour le Δ de l'importation d'énergie que je coince... En effet, je procède comme ceci :

J'extrais la valeur MIN et MAX en mode chaud ou froid (en fonction de la consigne toujours) puis je calcule les Δ. Vous allez me dire, "ok mais il est où le problème alors?".

Le problème est que l'importation d'énergie (qui est en fait la consommation électrique) évolue continuellement dans le temps, peu importe que l'on se trouve en mode froid (consigne 18) ou en mode chaud (consigne 52), contrairement aux quantité de chaud/froid produits qui n'évoluent que lorsque la consigne leur correspond.

===>>> Ce que j'aimerai donc avoir, c'est une méthode/formule, qui me permet de calculer les Δ d'importation d'énergie uniquement entre les sections ou la consigne est correcte, en ayant toujours bien en tête que le nombre total de ligne varie (en fonction de quand on stop l'enregistrement) et qu'il peut y avoir une consigne en chaud pendant 3h, comme pendant 10min et pareil pour le froid...

Dans mon exemple, j'aimerai donc qu'il calcule le Δ d'importation d'énergie en froid (consigne 18) comme ceci : P26-P17+P72-P60+P124-P111, ce qui donnerai comme résultat 0.66 au lieu des 1.22 que j'obtiens. Et cela, quel que soit le nombre de ligne total, ainsi que l'alternance chaud/froid.

Voila, j'espère avoir été assez clair en tout cas j'ai fais tout mon possible pour l'être !

Je ne sais pas si c'est compliqué à réaliser ou pas étant donné que je suis novice en Excel, mais je vous remercie pour le temps que vous prenez à essayer de m'aider, c'est vraiment chouette et ca m'aiderai fortement! Merci

Houlà, il y a du "monde", non ce n'est pas compliqué à réaliser, je regarderai ce soir mais tu auras peut-être une réponse par zebulon aussi d'ici là car il était le premier à intervenir.

edit : je viens de relire, c'est très intéressant et très clair (et déjà bien pensé) - yapluquà

D'accord, merci beaucoup ! D'ici la je continue d'optimiser mon tableau :)

bonjour à tous,

solution via une fonction personnalisée. fait la somme des différences entre les valeurs associées à la dernière ligne et àla première ligne d' intervalles identifiés par une valeur (consigne).

=IEA(plageconsigne,consigne,plagevaleur)

où plage consigne est la plage dans laquelle retrouver la consigne

où consigne est la consigne à recherche dans la plagesonsigne

où plage valeur contient les valeurs à utiliser pour faire la somme des différences

voir exemple d'utilisationd dans classeur joint. (il faudra adapter les formules pour y inclure l'utilisation de indirect)

18classeur-cop.xlsm (33.53 Ko)

Bonjour acide sulfurique,

Merci de ta réponse ! Effectivement, dans ce que tu m'as envoyé, ça à l'air de fonctionner. Cependant, je ne la comprends pas très bien étant donné que les arguments qui y sont mis me semble trop simplistes. Je ne vois pas vraiment comment sont effectués les calculs de soustraction pour mes Δ, bien que le résultat soit celui que je veux.

De plus, je vois que tu délimites mes tableaux de la ligne 10 à la ligne 130. Or, je ne sais pas à l'avance combien de ligne j'aurai... il se peut que j'en ai 5 comme il se peut que j'en ai 3.000 J'ai donc essayé de transformer tes H10:H130 et P10:P130 simplement par H:H et P:P mais ça me renvoie #NOM? quoi que je fasse, dès que j'essaie de modifier ta formule, ca me renvoie la même erreur. Même si je copie/colle la formule telle quelle dans une autre cellule, en veillant à bien la valider par ctrl + maj + enter, ca me renvoie cette erreur... que suis-je censé faire pour parvenir à mes fins? J'ai l'impression que tu as utilisé une macro pour réussir à obtenir la valeur voulue. Si oui, peux-tu m'aiguiller sur le comment de celle-ci et surtout comment je peux la modifier à ma guise? (pour y ajouter des choses, des contraintes ou même pour reproduire la macro sur un autre fichier).

Enfin, je me suis rendu compte aujourd'hui en optimisant mon tableau qu'il n'y avait en fait pas qu'un seul critère de décision mais un second (qui n'apparait malheureusement pas dans mon tableau à la date du 20.10.20). En effet, que ce soit pour le mode chaud ou pour le mode froid, le second critère qui m'intéresse est la position des vannes de la PAC. En d'autre termes, les vannes sont soit en position 1, soit en 2 .... Je sais, ca complexifie encore le problème

Voici un petit tableau pour mieux comprendre ce que j'essaie d'expliquer :

ChaudFroid
Postion vanne11
Position vanne00

Donc, pour résumer, le premier critère est la consigne, qui est soit de 0, soit 18, soit 52. Une fois que l'on a le premier critère, il faut que je regarde le second, à savoir dans quelle position se trouvent les vannes (1 ou 2). Je pense (j'espère...) pouvoir m'en sortir avec les MIN et MAX conditionnels, pour ce qui est de l'insertion de ce second critère (de la même manière que j'ai fais dans le tableau que j'ai envoyé). Cependant, je n'ai absolument aucune idée de comment l'incorporer dans ta fonction personnalisée, ni comment en ressortir les Δ chaud position 1, chaud position 2, froid position 1 et froid position 2 (tjs en ayant en tête que froid = consigne 18 et chaud = consigne 52).

On touche au but, merci beaucoup d'essayer de m'aider !

Re-

Bonjour h2so4

je passerais volontiers par un TCD (donc sans macro) qui est

  • très pratique pour faire le calcul des min et des max
  • pratique si tu veux ajouter un paramètre de filtrage
  • assez simple pour tenir compte de nouvelles données (on peut en une ligne de code le faire an auto) - on pourrait le faire sur des colonnes entières mais il ne faudrait pas qu'il y ait ce cartouche en en-tête !
    13classeur-cop.xlsx (30.83 Ko)

bonsoir,

j'ai adapté la fonction personnalisée (pour y inclure la gestion des vannes, j'aurais besoin d'un fichier qui contient l'info, ainsi que les calculs souhaités)

Function IEA(feuille, colonneconsigne, consigne, colonnevaleur)
'fonction faisant la somme des différences des valeurs min et max sur les intervalles définis par une suite de lignes contenant la consigne.
' feuille = nom de la feuille contenant les données
'colonneconsigne = lettre identifiant la colonne contenant les consignes
' consigne = valeur de la consigne pour laquelle faire le calcul
' colonnevaleur = lettre identifiant la colonne contenant les valeurs

    s = 0 'somme des différences
    With Sheets(feuille)
    For i = 1 To .Cells(Rows.Count, colonneconsigne).End(xlUp).Row ' on boucle sur toutes les lignes utiles
        If .Cells(i, colonneconsigne) = consigne And fr = 0 Then 'début d'un intervalle pour cette consigne ?
            fr = i 'oui on mémorise la ligne de début de l'intervalle
        ElseIf .Cells(i, colonneconsigne) <> consigne And fr <> 0 Then 'fin d'un intervalle ?
            s = s + .Cells(i - 1, colonnevaleur) - .Cells(fr, colonnevaleur) 'oui on additionne la différence entre dernière valeur et première valeur
            fr = 0 'fin de l'intervalle
        End If
    Next i
    End With
    IEA = s 'renvoyer la somme des différences
End Function

voir dans le clsseur joint exemple d'appel de la fonction

4classeur-cop.xlsm (35.64 Ko)

et comme pour h2so4, il serait bon d'avoir le fichier avec les vannes

tu auras alors 2 solutions différentes, cela enrichit toujours tes possibilités (perso, je ne te donnerai pas de préférence, c'est toi le "client" qui choisira)

Bonsoir à tous,

Hello Steelson, h2so4,

Un essai avec power query en attendant le fichier avec les vannes.

Bonne soirée, cordialement.

13classeur-cop-z.xlsx (43.52 Ko)

Bonjour à tous,

Merci pour vos nombreuses réponses qui me paraissent toutes très intéressantes ! Je suis désolé de vous répondre si tardivement mais avec ce fameux COVID, les dispositions prises avec mon école et mon lieu de stage pour effectuer du télétravail je n'ai pas vraiment eu le temps de m'y plonger sérieusement. De plus, il fallait que j'aille dans l'entreprise dans laquelle je travaille afin d'avoir des données supplémentaires concernant ces fameuses vannes.

Maintenant que c'est chose faite, je peux me plonger à 100% dans les résolutions que vous m'avez fournies. Je reviens vers vous au plus vite !

Encore merci de votre aide !

ce fameux COVID

cette fameuse COVID (c'est féminin)

on attend de tes nouvelles ...

Rechercher des sujets similaires à "trouver ecart entre min max"