XL 2016 Tableau croisé dynamique, régression linéaire/polynomiale

[BARNS]

Bonjour tout le monde,

Je suis en train de travailler sur un fichier et ne connaissant pas toute les fonctionnalités des tableau croisé dynamique je ne sais pas si ce que je souhaite faire est possible.

Donc j'ai les abaques / données de canons hydrauliques (j'en ai mis que deux sur le fichier pour que ce soit plus simple) dont je souhaite pouvoir extraire une lecture rapide par choix successif et entrée de valeur.

Je souhaite donc pouvoir choisir
1) mon canon
2) la buse

Puis je souhaite pouvoir entrée la pression exact que j'ai pour obtenir une valeur de portée et de débit.

Il faudrait que par les choix (canon, buse) fait je puisse isoler une partie du tableau pour aisément faire une régression polynomiale ou linéaire afin d'obtenir une portée et un débit.

Est ce que tout ceci est possible? Quelques pistes?

Merci pour tout,

 

[Dugenou]

Bonjour,
Une possibilité en pj à base de formules matricielles.
J'ai modifié les listes de choix des canons et buses. Une zone nommée pour chaque série de buses : peut être fastidieux si vous avez beaucoup de canons : d'autres solutions sont possibles.
Je ne comprends pas pourquoi (et comment) faire une régression alors qu'il n'y a qu'un résultat possible. A moins que la pression antrée ne soit pas affichée dans le tableau...

Cordialement

 

[Dugenou]

Re,
Version 2 avec régression linéaire pour traiter les valeurs de pression qui ne sont pas dans la table.

Cordialement

 

[sylvanu]

Bonjour Barns, Dugenou,
Un essai en PJ avec deux fonctions perso :

Function Portée(Canon, Buse, Pression)
Application.Volatile
With Sheets("Calcul")
    For L = 2 To .Range("A65500").End(xlUp).Row
        If .Cells(L, 1) = Canon And .Cells(L, 2) = Buse And .Cells(L, 3) > Pression Then
            Exit For
        End If
    Next L
    Portée1 = Cells(L, 4): Portée2 = Cells(L - 1, 4)
    Pression1 = Cells(L, 3): Pression2 = Cells(L - 1, 3)
    CoefA = (Portée1 - Portée2) / (Pression1 - Pression2)
    CoefB = Portée1 - CoefA * Pression1
    Portée = CoefA * Pression + CoefB
End With
End Function

Function Débit(Canon, Buse, Pression)
Application.Volatile
With Sheets("Calcul")
    For L = 2 To .Range("A65500").End(xlUp).Row
        If .Cells(L, 1) = Canon And .Cells(L, 2) = Buse And .Cells(L, 3) > Pression Then
            Exit For
        End If
    Next L
    Débit1 = Cells(L, 5): Débit2 = Cells(L - 1, 5)
    Pression1 = Cells(L, 3): Pression2 = Cells(L - 1, 3)
    CoefA = (Débit1 - Débit2) / (Pression1 - Pression2)
    CoefB = Débit1 - CoefA * Pression1
    Débit = CoefA * Pression + CoefB
End With
End Function

Le recours au VBA me semble plus simple que par formule.

 

[BARNS]

Bon franchement je ne m'attendais pas à autant de réactivité c'est très bienvenue mais c'est à la fois frustrant que vous donniez des solution en 20 minutes quand je cherche depuis des heures

Dugenou,
Merci! Oui effectivement les valeurs de pression à donner sont exact et pas forcément dans la table, un grand merci pour le fichier. Par contre j'ai tenté de compléter avec une partie du reste de mes données mais je rencontre des messages d'erreurs...


=INDEX(Tableau1[PRESSION];EQUIV($U$6&$V$6&$W$6;Tableau1[CANON]&Tableau1[BUSES]&Tableau1[PRESSION];1))

Sylvanu,
Merci aussi pour le retour. Du coup,
on est d'accord que
For L = 2 To .Range("A65500").End(xlUp).Row
c'est pour répéter une boucle autant de fois qu'il pourrait y avoir de ligne dans le tableau

Du coup la partie qui suit c'est pour nommer les entités
If .Cells(L, 1) = Canon And .Cells(L, 2) = Buse And

Mais du coup pourquoi :

.Cells(L, 3) > Pression

et pas
.Cells(L, 3) = Pression
?

Et puis je ne comprends pas le reste..
Next L Portée1 = Cells(L, 4): Portée2 = Cells(L - 1, 4) Pression1 = Cells(L, 3): Pression2 = Cells(L - 1, 3)

Donc dans le tableur :

=Portée(U6;V6;W6)
je suppose que tu appelles le code avec les paramètres canon buses pression

Voilà si vous avez le temps je suis preneur de quelques explications! Sinon un grand merci!

 

[sylvanu]

For L = 2 To .Range("A65500").End(xlUp).Row
c'est pour répéter une boucle autant de fois qu'il pourrait y avoir de ligne dans le tableau

Exact

Du coup la partie qui suit c'est pour nommer les entités
If .Cells(L, 1) = Canon And .Cells(L, 2) = Buse And

Non, en fait je cherche la ligne où se trouve Canon et Buse ( d'où le AND )

.Cells(L, 3) > Pression

Si on met =Pression on va chercher la cellule où il y a pression. Mais justement Pression n'est pas dans le tableau, plus exactement il n'y a que peu de chances qu'il y soit.
Donc je cherche la première valeur de Pression dans le tableau qui soit supérieur à la pression demandée. Cela me sert de Y2, et donc la valeur inférieur est à la ligne L-1 qui me donne Y1.
Ce qui me permettra de calculer CoefA=(Y2-Y1)/(X2-X1)

Portée1 = Cells(L, 4): Portée2 = Cells(L - 1, 4)
Pression1 = Cells(L, 3): Pression2 = Cells(L - 1, 3)

Ensuite j'extrait les valeurs X1,x2,Y1,Y2 pour calculer CoefA et CoefB puis faire y=ax+b.

Peut être plus explicite :

=Portée(U6;V6;W6)
je suppose que tu appelles le code avec les paramètres canon buses pression

Oui c'est la syntaxe que j'ai adoptée : =Portée(Canon, Buse, Pression)
Ca fait donc en cellule X6 : =Portée(U6;V6;W6) car U6 donne le type de canon, V6 le diamètre de la buse et W6 la pression que vous avez mesuré.

En espérant avoir été plus clair.

 

[BARNS]

Oui c'est nettement plus clair!

En m'essayant au code : si je veux que le message "Pression hors de la plage de fonctionnement du canon et de la buse choisi" quand la pression est égal à 0 ou soit inférieur soit supérieur au plage de fonctionnement le code serait alors le suivant :

Avec P la pression donée, Pmin et Pmax les pressions minimum et maximum de fonctionnement pour le canon et la buse choisi.

If P<Pmin ou P>Pmax ou Pmin = 0 ou P=<0 Then MsgBox "Pression hors de la plage de fonctionnement du Canon et de la buse choisi", vbOKOnly Else 'la suite du code

J'ai juste du mal pour définir les variable:
Pmin = Application.WorksheetFunction.Min(.Range(.Cells(canon), .Cells(buse)))
Pmax = Application.WorksheetFunction.Max(.Range(.Cells(canon), .Cells(buse)))
P=pression?

merci!

 

[sylvanu]

Bonjour,
Je suppose que Pmin et Pmax sont les valeurs min max d'une plage ?
Par ex Skipper12 Pmin=2, Pmax=5 ?

 

[BARNS]

Oui tout à fait!

 

[sylvanu]

Voilà.
Je traite les trois cas : P vide, P< Pmin , P > Pmax.

 

[BARNS]

Okay,

Sylvanu,

D'abord merci encore, tout cela est très propre, après m'être cassé la tête j'ai encore des points obscures.. mais je suppose qu'avec le temps je vais comprendre.

Je pensais pouvoir finaliser le fichier tout seul mais finalement comme je ne comprends pas l'intégralité de ton code (et vba en général) j'avance très très lentement voir pas du tout.

Si tu peux m'aider sur un dernier point ça serait génial après je clôturerais la discussion.

Le dernier point est l'ajout d'un suppresseur. Il y a trois type de suppresseur. Un suppresseur va rajouter de la pression, et donc le débit sera modifié.

La valeur de pression additionnelle donné par le suppresseur dépend du débit. Donc plus le calcul est répété plus nous gagnons en précision. Il faut donc faire des "itérations".

Par exemple pour un supresseur 3 CV la pression additionnelle = -0.0022 * Débit² + 0.0091 * Débit + 3.0337

et donc un nouveau débit (débit1) sera calculé avec la pression à l'entrée + la pression additionnelle

Une nouvelle pression additionnelle peut être calculé Padd2 =-0.0022 * Débit2² + 0.0091 * Débit2 + 3.0337 ... x10

etc.

J'ai essayer de faire ce que je pouvais avec une boucle for (10 itération) mais j'ai du mal à utiliser la liste des suppresseurs comme variable, le débit devient une variable changeante du coup je sais pas trop ou positionner le code vu que j'ai besoin qu'a chaque boucle le débit se recalcule... je suis un peu perdu.

Et puis les suppresseurs ont des plages de fonctionnement de débit à respecter, j'ai utilisé des fonction if qui je pense devraient fonctionner.

Je te laisse regarder tout ça si tu as le temps bien sûre.

Merci pour tout,

PB

 

[sylvanu]

Bonsoir Barns,
J'ai supprimé deux posts car bourrés de sottises. En PJ un essai après moult réflexions.

J'ai créé la fonction Surpression avec la syntaxe :

Surpression(Canon, Buse, pression, surpresseur)

Ca donne des choses, ça se plante pas. A vous de vérifier.

Par contre, avec les coefficients donnés et 3 de pression, un 3CV donne plus de surpression qu'un 4CV. Est ce normal ?

Faites un feedback . Je suis curieux de voir si je suis à coté de la plaque ou non.

 

[sylvanu]

Bonjour,
Un doute m'assaille.
Quand vous faites : (surpresseur = "3CV" And DebitEnCours < 10.8), le débit utilisé doit être le débit d'entrée ou le débit de sortie ?
Dans la PJ3 j'utilise le débit d'entrée et j'ai plein d'avertissements, dans la PJ4 ci jointe j'utilise le débit de sortie, en recalculant avant les tests :

DebitEnCours = débit(Canon, Buse, pression + Pfinale) ' Calcul de débit final

Vous avez le choix dans la version.

 

[BARNS]

Oui effectivement suRpresseur! La correction automatique me le change en "suppreseur" je ne sais pas pourquoi.

Alors c'est vrai tu as plus de pression avec la 3CV mais la plage de fonctionnement du débit est inférieur. La puissance électrique ou les chevaux sont liés a la capacité du suppresseur (pompe) à ajouter une plage de pression pour une plage de débit.

Je suis désolé j'ai mis du temps à répondre mais je voulais tout reprendre bien pour m'assurer du fonctionnement.

Oui, c'est bien le débit finale avec la nouvelle pression qui doit être pris en compte.

Les surpresseurs sont surtout utilisés quand la pression initiale est inférieur à la pression de fonctionnement du canon, donc même si P<Pmin, la pression additionnel que pourrait attribuer le surpresseur doit être prise en compte autrement dit :

Si Pini + Padd >Pmin, le calcul doit s'effectuer, si Pini + Padd <Pmin alors oui, aucune valeur ne doit être affichées.

Du coup j'ai supprimé le doublon de cette condition au niveau de fonction débit & portée pour le mettre dans la fonction surpression. J'ai l'impression d'avoir solutionner une partie du problème mais cette fois même quand P< Pmin le résultat du calcul s'affiche. J'ai le même problème avec P>Pmax
Par exemple avec skipper (canon) 10 (buse) 1 (pression) sans surpresseur

Aussi j'ai un message d'erreur concernant la plage de fonctionnement du suppresseur mais il semblerait que ce soit Qini qui soit prit en compte et non Qfin
Par exemple avec skipper (canon) 10 (buse) 2 (pression) 3cv (surpresseur)

En tout cas votre code est très propre! Nous nous approchons de la solution finale

Merci pour tout!

 

[sylvanu]

Bonjour Barns,
J'y vais à tâtons, je n'ai pas bien tout saisis. Donc les modifs de la PJ pour essai :

1- Tous les messages d'erreurs sont calculés sur Pression finale et Débit final.
2- J'ai corrigé un joli bug. Si P< Pmin buse alors on prend Pmin buse et ... Pmax de la buse d'avant.
3- J'ai supprimé les #Valeurs quand P<Pmin ou P>Pmax avec des SiErreur, dans la feuille.
4- J'ai mis des MFC lors de ces conditions.

Réponses aux questions dans le code :
1- nous ne devons pas fermer la fonction if avant for?
Non, Exit For saute directement après Next, une sorte de Goto qui sort de la boucle
2-'quelle est la différence entre "next l" et "exit for"
Rien à voir. Next L permet de continuer la boucle avec la valeur suivante de L. Exit For sort de la boucle. ... C'est vrai qu'après la dernière valeur de L on se retrouvera à la même ligne de code.
3- L pour Ligne.
Oui, on aurait pu l'appeler comme on veut. Ligne par ex, c'eût été plus explicite.
4-'Pourquoi ne pas avoir commencé par Sub?'
Parce que les variables Public doivent être déclarées au tout début.