Avance/Retard livraison tableau croisé dynamique ?
Bonjour à tous,
Je suis mes avances et retards de livraisons de produits avec Excel.
Actuellement, je crée 2 tableaux croisés dynamiques à partir d'une extraction csv d'une base de données.
Le premier TCD présente le besoin (date demandée par mes clients) pour chaque produit par semaine.
Le second TCD présente la prévision de livraison pour chaque produit par semaine.
Pour obtenir ensuite le retard ou l'avance cumulée par semaine pour chaque produit, je fais une copie des 2 TCD dans une nouvelle feuille et crée manuellement un tableau "GAP cumul" qui présente l'avance ou le retard cumulé par semaine et par quantité de produit.
Est-ce que quelqu'un aurait une solution pour que le tableau retard_avance cumulé ce calcul automatiquement, sans être obligé de le refaire à chaque extraction de données ?
Merci par avance.
Pit.
Bonjour
Tu comptes le nombre de retards pas le nombre de semaine de retard ?
On effectuer des calculs dynamiques sur des TCD avec LIREDONNEESTABCROISDYNAMIQUE
Cependant les colonnes calculées dans la source me paraissent inutiles, les TCD sachant calculer seuls les années, mois, semaines à partir des dates.
De plus ton mode de calcul du numéro de semaine est faux : il faut utiliser 21 en second argument ou SEMAINE.ISO comme fonction.
Donc plutôt que tous ces calculs, il serait plus intéressant de calculer une colonne retard pour pourvoir faire tes 3 TCD sans compliquer...
Bonjour 78chris,
Merci pour ta réponse.
Oui je compte effectivement le nombre de retard et pas le nombre de semaine de retard.
J'ai fait un exemple hyper simpliste et rapide de mon problème.
Effectivement, les TCD calculs les années, mois, etc... à partir des dates, mais pas les semaines malheureusement.
Aurais-tu un suggestion pour le calcul de la colonne retard ?
Cordialement,
Pit.
RE
=SI((ANNEE(C2)&TEXTE(NO.SEMAINE(C2;21);"00")*1)-(ANNEE(B2)&TEXTE(NO.SEMAINE(B2;21);"00")*1)>0;1;0)Pour avoir à la fois les années et les semaines dans un TCD, il faut dans la source
soit dupliquer la date (on peut extraire l'année de l'original et la semaine de la copie)
soit calculer la semaine.
RE
=SI((ANNEE(C2)&TEXTE(NO.SEMAINE(C2;21);"00")*1)-(ANNEE(B2)&TEXTE(NO.SEMAINE(B2;21);"00")*1)>0;1;0)Pour avoir à la fois les années et les semaines dans un TCD, il faut dans la source
soit dupliquer la date (on peut extraire l'année de l'original et la semaine de la copie)
soit calculer la semaine.
Ok merci.
Mais je ne vois pas comment cette formule peut m'aider à faire une quelque chose de similaire à ce que je fais...
RE
cette formule donne 1 ou 0 selon le retard : c'est une formule à mettre dans une colonne de la source comme proposé dans mon poste de 12h51.
Après il est facile de faire un TCD des retards...
RE
En regardant de plus près tes calculs, je vois que tu comptes aussi les avances.
Le produit 3 est livré 6 semaines avant le besoin dans un cas, 1 semaine avant dans le 2ème et une semaine après dans le 3ème.
Ces avances impactent ton calcul de retard.
Si c'est le même client pourquoi pas sinon ce n'est pas logique.
Si tu maintiens ton mode de calcul, inutile d'ajouter RETARD à la source : utiliser la solution LIREDONNEESTABCROISDYNAMIQUE pour calculer l'écart.
RE
En regardant de plus près tes calculs, je vois que tu comptes aussi les avances.
Le produit 3 est livré 6 semaines avant le besoin dans un cas, 1 semaine avant dans le 2ème et une semaine après dans le 3ème.
Ces avances impactent ton calcul de retard.
Si c'est le même client pourquoi pas sinon ce n'est pas logique.
Si tu maintiens ton mode de calcul, inutile d'ajouter RETARD à la source : utiliser la solution LIREDONNEESTABCROISDYNAMIQUE pour calculer l'écart.
C'est effectivement le même client, d'où la méthode de calcul...
Je vais essayer de créer un tableau "fixe" avec des formules qui vont chercher des informations dans le tableau croisés dynamiques...
J'aurai vraiment aimé avoir une solution directe avec les tcd, mais je pense que ce que je veux faire ne le permet pas....
Re
As-tu vu le fichier joint à mon précédent post ? Tu as les formules ah-doc.
Non le TCD ne peut le faire ou peut-être avec des éléments calculés qui posent pas mal de problèmes... et pas évident à utiliser sur des champs temps qui vont forcément évoluer...
Re
As-tu vu le fichier joint à mon précédent post ? Tu as les formules ah-doc.
Non le TCD ne peut le faire ou peut-être avec des éléments calculés qui posent pas mal de problèmes... et pas évident à utiliser sur des champs temps qui vont forcément évoluer...
Non, je n'avais pas vu.
Je viens de télécharger.
Merci, c'est top.
Re
Possible en TCD avec PowerQuery (add on Microsoft sur 2010, intégré à partir de 2016)
Bonjour
Déjà j'ai mis la source sous forme de tableau et l'ai basculé dans PowerQuery (onglet Données à gauche A partir d'un tableau ou d'une plage)
Dans l'onglet Données tu peux afficher la liste des requêtes avec le bouton Requêtes et connexions
Tu vois alors 2 requêtes Tableau1 et Tableau1(2)
Clic droit sur Tableau1, Modifier : cela ouvre la fenêtre PowerQuery.
Quand une requête est sélectionnée (à gauche) son déroulé s'affiche à droite.
En cliquant sur chaque étape tu vois les modifications/ actions sur les données de tableau1.
Les 2 premières étapes sont automatiques à l'import du tableau dans PowerQuery.
Les suivantes permettent de supprimer de la requête les colonnes inutiles et d'ajouter une période combinant année et semaine pour Besoin d'une part et Livraison d'autre part, puis de décroiser les données ta source ayant les 2 infos sur la même ligne.
(On peut cliquer sur la petite roue dentée de la ligne pour voir comment cela se fait en mode assisté)
Ensuite j'ai
- dupliqué cette requête après l'étape Colonnes triées pour filtrer d'un côté les livraisons, de l'autre les besoins
- ajouté une valeur -1 aux livraisons et 1 aux besoins
- ai rassemblées les 2 requêtes ensuite
- ai ajouté une mise en forme plus lisible de la colonne semaine en colonne période.
En fait je vois ce matin que j'aurais pu plus simplement ne pas couper en 2 requêtes (étape 1 ci-dessus) mais ajouter la valeur 1 ou -1 avec un if ce qui évite 2 étapes et une requête.
Voir ci-joint
Au début j'ai utilisé la colonne semaine que j'avais ajoutée pour tester des calculs mais ayant finalement renoncé, j'aurais pu dès le début la présenter sous la forme année_semaine...
Bonjour
Déjà j'ai mis la source sous forme de tableau et l'ai basculé dans PowerQuery (onglet Données à gauche A partir d'un tableau ou d'une plage)
Dans l'onglet Données tu peux afficher la liste des requêtes avec le bouton Requêtes et connexions
Tu vois alors 2 requêtes Tableau1 et Tableau1(2)
Clic droit sur Tableau1, Modifier : cela ouvre la fenêtre PowerQuery.
Quand une requête est sélectionnée (à gauche) son déroulé s'affiche à droite.
En cliquant sur chaque étape tu vois les modifications/ actions sur les données de tableau1.
Les 2 premières étapes sont automatiques à l'import du tableau dans PowerQuery.
Les suivantes permettent de supprimer de la requête les colonnes inutiles et d'ajouter une période combinant année et semaine pour Besoin d'une part et Livraison d'autre part, puis de décroiser les données ta source ayant les 2 infos sur la même ligne.
(On peut cliquer sur la petite roue dentée de la ligne pour voir comment cela se fait en mode assisté)
Ensuite j'ai
- dupliqué cette requête après l'étape Colonnes triées pour filtrer d'un côté les livraisons, de l'autre les besoins
- ajouté une valeur -1 aux livraisons et 1 aux besoins
- ai rassemblées les 2 requêtes ensuite
- ai ajouté une mise en forme plus lisible de la colonne semaine en colonne période.
En fait je vois ce matin que j'aurais pu plus simplement ne pas couper en 2 requêtes (étape 1 ci-dessus) mais ajouter la valeur 1 ou -1 avec un if ce qui évite 2 étapes et une requête.
Voir ci-joint
Au début j'ai utilisé la colonne semaine que j'avais ajoutée pour tester des calculs mais ayant finalement renoncé, j'aurais pu dès le début la présenter sous la forme année_semaine...
Merci Chris
Je regarde cela en détail ce soir.
J'aime bien cette technique.
La seule chose que je ne comprends pas, c'est comment tu arrives à faire le step qui splitte la ligne en deux, une pour le besoin et l'autre pour la livraison...
Bonjour
Sélection des 2 colonnes Besoin et Livraison (ou figurent les semaines sous forme AnnéeSemaine) et dans l'onglet Tranformer : Supprimer le tableau croisé dynamique des colonnes