Régression linéaire pour prédire un temps

Question

Je travaille sur un projet Excel et j'aimerais obtenir de l'aide pour automatiser une estimation du temps de déchargement de containers. Mon fichier Excel contient deux feuilles : "Historique prod" et "En cours" la feuille Historique prod contient les colonnes suivant : Col A : nb de commandes. Col B : total colis. Col C à F : détail des types de colis dans le container. Col G : Temps déchargement Col H : j'ai ajouté cette colonne Temps décimal pensant que ça pouvait être utile. La feuille "En cours" contient la même structure mais c'est sur cette feuille qu'il faut calculer les temps de déchargement en se basant sur les déchargements passés. Je précise que toutes les colonnes doivent être prises en compte dans le calcul. Par exemple, deux containers peuvent avoir le même nombre de colis par type, mais un nombre de commandes différent, et donc un temps de traitement différent. Ma question est la suivante : Quelle est la meilleure méthode pour estimer ce temps de traitement, et comment la mettre en place dans Excel ? J’ai essayé plusieurs méthodes (formules Excel, macros VBA, scripts Python, même via des IA), mais je n’ai pas réussi à obtenir un résultat correct. Je rencontre souvent des erreurs dans les formules ou des résultats incohérents. Merci d’avance pour votre aide. le fichier en pièce jointe Cordialement...

Doux Rêveur · Answer

Je ne comprends pas bien ce qu'on doit obtenir. En quoi les temps indiqués dans la première feuille sont-ils utiles ? Ces temps sont donc les temps réels, constatés et pas calculés ...

saboh12617 · Answer

Sans parler du fait que les données dans historique de production de sont pas exploitables (il faut corriger les nombreux chiffres stockés en lettre ou avec des caractères bizarres), je pense que la question manque de précision. On peut faire des statistiques, mais à mon avis les données fournies, même si elles sont nombreuses, ne sont pas suffisantes pour suivre la consigne telle que vous l'avez donnée. Je m'explique : Dans la feuille en cours, prenons l'exemple de la 1e ligne. Le tableau de données contient 7 lignes avec le meme nombre de commandes (50). Si l'on veut n'ajouter ne serait-ce qu'un 2e filtre, par exemple sur le nombre total de colis, on n'a plus aucune correspondance. Selon moi une solution serait donc de séparer les paramètres de chaque colonne, et faire une approximation par colonne. Soit 1 approx/paramètre. Et ensuite on pondère ces approximations en donnant +/- d'importance à certaines colonnes (ou bien importance égale), et on en sort une estimation. Cependant cette approche n'est valide que si les paramètres n'ont aucune corrélation entre eux, ce dont je doute. Ci-joint une proposition d'illustration du principe, avec les nombres du tableau de base corrigés (en nombres). EDIT : comme je le disais, résultats peu fiables. Par exemple en c/c les paramètres de la 1e ligne de la BDD on calcule un temps de décharge de 49min au lieu des 25 réelles.....

saboh12617 · Answer

Re,

J'ai étudié un peu les données et effectivement la méthode proposée avec les moyennes ne peut pas fonctionner car les données ne suivent pas un parcours linéaire par paramètre. Par exemple quand on trace le temps de décharge par le nombre de commandes on voit bien qu'il n'y a aucun lien direct.

Je ne suis moi non plus pas trop un expert en régression linéaire ni en IA, cependant si tu sais utiliser python je pense tu pourrais essayer de faire tourner le script suivant. Je te joins le CSV correspondant à ce message. Je n'ai pas installé sklearn mais tu peux tester. Selon moi le plus gros soucis sera sur la colonne 6 (nb colis 99) que je supprimerai car elle est quasiment complètement vide (95% de 0) et ça risque de biaiser ton approximation.

import pandas as pd
from sklearn.model_selection import train_test_split
from sklearn.linear_model import LinearRegression
from sklearn.metrics import mean_squared_error
import numpy as np

# Charger les données
df = pd.read_csv("data.csv", sep=';')

# Convertir le temps en secondes
df['Temps déchargement'] = pd.to_timedelta(df['Temps déchargement']).dt.total_seconds()

# Séparer les variables explicatives et la cible
X = df.iloc[:, :-1]
y = df.iloc[:, -1]

# Diviser en jeu d'entraînement et de test
X_train, X_test, y_train, y_test = train_test_split(X, y, test_size=0.2, random_state=42)

# Entraîner le modèle
model = LinearRegression()
model.fit(X_train, y_train)

# Évaluer le modèle
y_pred = model.predict(X_test)
mse = mean_squared_error(y_test, y_pred)
print(f"Erreur quadratique moyenne (MSE) : {mse}")

# Fonction de conversion en heures, minutes, secondes
def convert_seconds_to_hms(seconds):
    hours = int(seconds // 3600)
    minutes = int((seconds % 3600) // 60)
    seconds = int(seconds % 60)
    return hours, minutes, seconds

# Fonction de prédiction personnalisée
def predict_loading_time(values):
    prediction = model.predict([values])[0]
    return convert_seconds_to_hms(prediction)

# Exemple de simulation
custom_values = [16, 160, 4, 80, 70, 1]
predicted_time = predict_loading_time(custom_values)
print(f"Temps de déchargement estimé : {predicted_time[0]} heures, {predicted_time[1]} minutes et {predicted_time[2]} secondes")

11data.csv (44.04 Ko)

saboh12617 · Answer

Pour l'utiliser dans Excel tu peux faire ceci en G3 puis tirer vers le bas. Dans le tableau Historique prod, passe le format de la colonne du temps en nombre à virgules (très important).

import pandas as pd
from sklearn.model_selection import train_test_split
from sklearn.linear_model import LinearRegression
from sklearn.metrics import mean_squared_error
import numpy as np

# Charger les données
df = xl("tblHisto[#All]", headers=True)

# Séparer les variables explicatives et la cible
X = df.iloc[:, :-1]
y = df.iloc[:, -1]

# Diviser en jeu d'entraînement et de test
X_train, X_test, y_train, y_test = train_test_split(X, y, test_size=0.2, random_state=42)

# Entraîner le modèle
model = LinearRegression()
model.fit(X_train, y_train)

# Évaluer le modèle
y_pred = model.predict(X_test)
#mse = mean_squared_error(y_test, y_pred)
#print(f"Erreur quadratique moyenne (MSE) : {mse}")

# Fonction de prédiction personnalisée
def predict_loading_time(values):
    prediction = model.predict([values])[0]
    return prediction

# Exemple de simulation
custom_values = xl("A3:F3")
custom_values = custom_values.values.flatten()

predicted_time = predict_loading_time(custom_values)

Cependant les résultats sont toujours élevés... J'imagine qu'il faut jouer sur cette instruction

train_test_split(X, y, test_size=0.2, random_state=42)

mais je n'y comprends pas grand chose...

saboh12617 · Answer

J'ai également testé une autre régression qui aurait du être plus précise, mais toujours le même problème.

J'attends de voir si quelqu'un a d'autres propositions, moi je m'arrête ici. Bon courage !

BsAlv · Answer

Bonjour, ce fichier il vient d'où ? il contient le charactère 160 (les valeurs &gt;1000) ...pffffffff , j'ai des problèmes avec ! (un fichier sans ces charactères, c'est possible ?) la colonne Tout, c'est la somme de70, 77, 90 et 99 et encore d'autres ...

saboh12617 · Answer

Salut Bart, mon 1e fichier joint est nettoyé...

BsAlv · Answer

Re, R2 = 0,06 = peanuts...

saboh12617 · Answer

Oui j'ai les mêmes résultats que Bart... L'ordinateur a vraiment du mal à trouver un lien entre les temps de déchargement et les données du tableau. Peut etre avez-vous d'autres paramètres plus impactants/linéaires ?

Pour l'erreur python je ne sais pas, je vous mets ci-joint mon fichier, qui chez moi fonctionne (vous n'avez pas besoin d'installer des dépendances, toutes les exécutions python pour excel se passent dans le cloud).

8optimisation-dechargement.xlsx (127.73 Ko)