Comment ignorer une cellule vide d'une série à utiliser dans une formule

Bonjour,

Je m'appelle Axel, je suis en stage de fin d'étude en biologie. Dans ce cadre, je réalise un suivi de reproduction (nombre de fraies) chaque semaine d'une espèce de poisson, et j'ai pas mal de données à traiter, parfois aussi d'années précédentes. Afin de compléter les données manquantes de certaines semaines, j'utilise des courbes de tendances basées sur les semaines précédentes. Je me tourne vers ce forum car je n'ai trouvé aucune réponse à mes questions sur d'autres.

Afin d'être le plus précis possible, j'utilise une polynomiale à 6 degrés, qui représente généralement le mieux les tendances des relevés des semaines pour lesquelles j'ai de la matière. Sur la moyenne de 5 années, je calcule les coefficients b6/b5/b4... avec la formule INDEX(DROITEREG) que j'ai trouvé sur internet et qui fonctionne bien, ça me donne une équation polynomiale à 6 degrés, qui me permet d'obtenir un Y (nombre de fraies) à partir d'un X (semaine considérée), et de modéliser la reproduction en fonction du temps. Voici la formule que j'ai testé sur la moyenne des 5 années de relevés :

=INDEX(DROITEREG(C108:C133;B108:B133^{1.2.3.4.5.6});1)

Ou C108:C133 correspond aux Y, soient les moyennes brutes calculées sur 5 ans (malgré des trous), et B108: B133 les X (la semaine, de la semaine 48 d'une année à la semaine 21 d'une année suivante, soient 26 X). Je précise que la formule affichée automatiquement par Excel n'est pas précise et ne suffit pas, d'où le recours à la formule DROITEREG.

Seulement, la formule au-dessus fonctionne bien que lorsque j'utilise en Y une moyenne brute, calculée via les 5 années de relevés, l'objectif final étant de modéliser avec précision pour chaque année, malgré les trous à certaines semaines. Chaque année (ici 21/22, en rouge, voir fichier joint) ne ressemblant pas à la moyenne (en vert), la courbe change, donc les coefficients aussi, et je pensais pouvoir appliquer ma formule. Eh non ! Car ça ne fonctionne pas quand il y a un Y vide (donc une cellule vide) dans la plage de cellules servant de Y.

L'idée serait que, pour l'exemple en fichier joint, les X soient la colonne B, et les Y la colonne G, qui correspond aux relevés de l'année 2021/2022. Les Y de la colonne C correspondent à ce que j'ai décris ci-dessus, càd la moyenne. J'ai essayé une multitude de formules conditionnes (avec des SI EST VIDE etc), mais je ne trouve aucune qui arrive à me donner une valeur au final. Voici ce qui est le plus prometteur :

=INDEX(DROITEREG((SI(ESTVIDE(C149:C174); ""; C149:C174));B149:B174^{1.2.3.4.5.6});1)

Cette formule "fonctionne" quand je l'applique sur la colonne C (donc les Y de la moyenne, sans trou), me donnant les valeurs de coefficients en bas du fichier (qui sont les mêmes que en utilisant la première formule), mais quand je déplace sur la colonne des Y que je veux (donc la colonne G de l'Excel), ça ne marche pas, j'ai un #VALEUR !. Si je mets des 0 ou des NA à la place des vides, il me modifie la courbe donc je tronque les résultats.

EN RÉSUMÉ : Il me faudrait un moyen d'obtenir les coefficients de la courbe en rouge pour pouvoir obtenir l'équation précise de la courbe, pour calculer le nombre de nids en fonction de la semaine quand je n'ai pas la data. Pour ça, il me faut un moyen d'ignorer les cellules vides dans la formule DROITEREG, et je ne trouve pas.

Un grand merci par avance pour la lecture de ce pavé, et le temps accordé, belle journée,

Cordialement

Axel M

Bonjour et bienvenu sur le forum

Il est où le fichier joint...

A+ François

Bonjour,

Voici un fichier tiré de mon Excel natif qui correspond à l'image du premier message. Dites-moi si ça ne suffit pas.

Bonne journée,

Cordialement

bonjour,

les formules de regressions à partir de E35 + la graphique (en supposant que vous avez Excel 365)

une autre question, vue du point de vue statistique, je me demande le sens ou le non-sens d'aller à la puissance 6 de X. Je me rappèle qu'il y avait de tests pour çà, mais il y a beaucoup de poussière dans ma mémoire ... .

Je pense que ces tests vous diront d'arrêter à la puissance 4 ou 5 et que le 6 est pour la vitrine.

Bonjour,

Merci beaucoup de votre réponse.

Votre solution fonctionne bien, à condition de remplacer manuellement des coefficients dans une nouvelle formule, et d'étirer les X correspondants. Sinon, comme votre "Y calculé" en colonne D utilise le Y de base, il me met #N/A quand la case est vide et le problème redevient le même. Est-ce voulu ?

Malgré tout, en remplaçant manuellement les a6/a5 calculés avec votre méthode dans une formule d'équation polynomiale classique, j'obtiens les Y calculés manquants de la courbe pour les X correspondants. Je vais essayer d'appliquer ça à d'autres années en reprenant votre formule.

A propos des tests pour déterminer le degré d'équation, je ne connais pas, je vais me renseigner. J'imagine que la méthodo sera la même si je dois adapter, donc mon souci semble réglé ! Un grand merci.

Bonne journée,

Cordialement,

Axel MURET

re,

je ne comprends pas ce changement "manuelle". Voir Feuille "Exemple", j'ai nommée ms données "Mon_X" et 'MonY" et dans I1 je mets le nombre de dégrees. Alors avec cette formule, je reçois tous les coefficients du régression dans les cellules à partir de H3. Ceux qui m'intéressent (la première ligne) sont nommées "Coeff". J'ai besoin de ce #NA dans cette colonne dans la formule pour ma graphique. Si vous ne la créez pas, vous pouvez changer cela dans la formule.

=SI(C2<>"";SOMMEPROD(B2^SEQUENCE(;$I$1+1;$I$1;-1);Coeff);NA())
=SI(C2<>"";SOMMEPROD(B2^SEQUENCE(;$I$1+1;$I$1;-1);Coeff);)

Puis pour vérifier ma curve, je fais cette calculation dans la colonne D, mais je ne touche rien. Vous pouvez modifier ce nombre de dégrees ou supprimer des autres valeurs Y dans la colonne C , si vous voulez.

On peut aussi utiliser une MFC pour masquer ce "NA()" dans la colonne D