Automatisation d'un fichier complexe
Bonjour à tous,
Je travaille dans un service logistique et je mon manager m’a demandé améliorer l’utilisation d’un fichier pour mes collègues gestionnaire de transports. Ce fichier mentionne : Le dépôt, le transporteur, le numéro de contrat, la date de livraison, la date de remontée de livraison, le type de frais (péage, heure de nuit, samedi, jour férié, etc), la valeur temporelle (1h, 2h, 45 minutes, etc) et la valeur monétaire (le tarif). Il y a des types de frais qui donnent une valeur temporaire (comme les nuits), et d’autre une valeur monétaire (comme les péages).
Contexte : Jusqu’à maintenant, les gestionnaires doivent filtrer le fichier par contrat, puis par type de frais, et ensuite faire la somme de la valeur total ou monétaire par type de frais pour intégrer les infos sur SAP et cela prend un temps fou.
Ce que je souhaite faire c’est créer une feuille automatisée où sont mentionnées les infos nécessaires (en colonne) pour le gestionnaire :
- Dépôt,
- N° de contrat
- Transporteur
- Le type de frais
- Valeur temporelle total
- Valeur monétaire total
et où il lui suffera de saisir le numéro de contrat PUIS de sélectionner le type de frais dans une liste déroulante pour qu’il ait la somme de la valeur temporelle ou la somme de la valeur monétaire ainsi que le nom du dépôt, du transport.
Info utile :
- Le fichier de base possède déjà une recherche V : lorsque l’on tape le n° du contrat, le transporteur et le dépôt s’affiche automatiquement
- Un même contrat peut avoir plusieurs types de frais la même journée, la même date de remontée et la même date de livraiso
- Vous trouverez en pièce jointe (PJ1 et PJ2) de mon deuxième message une illustration du fichier utilisé (Feuille1) et ce que je souhaiterai (Feuille2) .
J’espère avoir été clair et que vous pourrez m’aider. Un ami geek d’Excel m’a dit que selon lui c’est impossible, je commence à perdre espoir. Je travail sur ce fichier toute la journée mais je ne trouve aucune solution.
Merci d’avance !
Bonjour et
Des fichiers au format .pdf n'aideront pas plus qu'une capture d'écran (tes fichiers ne peuvent pas s'afficher, par ailleurs !?)
Donc, joins plutôt un extrait représentatif de ton fichier (en supprimant les données confidentielles) avec tes deux fichiers dans 2 feuilles du même classeur.
Tes explications paraissent claires, mais elles prendront tout leur sens pour nous si on peut voir, tester, etc.
Bonjour,
Merci pour ta réponse !
J'ai joint un fichier avec deux feuilles. Toutefois, ce n'est qu'une illustration pour que vous ayez un visuel de ce que je souhaite et un visuel du fichier qui est utilisé.
Il est impossible que je vous joigne le vrai fichier car il est automatisé / lié à plein d'autres en interne.
Cordialement,
Manèle
Re-bonjour,
Tu devrais renseigner -dans ton profil- la version d'Excel que tu utilises (2007, 2013, 2019, 365, etc.) : de manière que la solution proposée soit compatible.
La somme des "valeurs temporelles", pour un contrat et un type de frais pourrait être obtenue en M6 par :
=SOMME.SI.ENS('Fichier utilisé'!$G$2:$G$9;'Fichier utilisé'!$C$2:$C$9;D6;'Fichier utilisé'!$F$2:$F$9;J6)Pour ce qui est du dépôt et du transporteur, il ne saurait y avoir qu'un seul résultat ? Pour le contrat "R-FRG023" et les "heures de nuit", j'obtiens avec la formule proposée 1:32 en colonne M ... mais ne peut-il y avoir, avec ces mêmes critères, deux transporteurs et/ou dépôts différents ?
Bonjour à tous,
essai avec un TCD (Tableau croisé dynamique) et 2 segments pour choisir Contrat et Type de frais.
Suite à la remarque pertinente de U.Milité j'ai remis un sous-total par contrat pour avoir son global.
Après modification des données, un TCD doit être Actualisé par un clic-droit dessus.
Pour éviter un oubli je t'ai ajouté une macro qui le fait automatiquement à l'activation de la feuille.
A voir si tu as le droit d'avoir des macros (?)
eric
Bonjour à tous et merci pour votre contribution !
@Umilité Pour répondre à ta première question : Oui, un contrat = un seul dépôt et un seul transporteur donc un seul résultat possible. En fait, il faudrait que le nom du dépôt et du transporteur soient recherchés dans le fichier de base qui lui même fait une recherche V via une base de données pour obtenir le nom du dépôt et du transporteur en tapant le n° de contrat. Penses-tu que c'est possible ?
@Eric : C'est vrai que le TCD peut être une bonne alternative. Pour la macro, c'est ok. Merci pour cette superbe idée que je garde dans un coin de ma tête !
J'attends quand même de voir s'il est possible de mettre en place mon idée de base
Bonjour,
ne te contente pas de le garder dans un coin de ta tête, les TCD sont hyper puissants.
Ca vaut le coup d'y consacrer 1/2h pour comprendre la philosophie.
Tableau Croisé Dynamique (TCD) : http://www.mdf-xlpages.com/modules/smartsection/item.php?itemid=109
Manque-t'il des infos ?
J'ai peut-être raté qq chose dans les explication, complète si besoin.
Ceci dit, je viens de penser qu'on peut faire plus simple avec Sous-total() pour sommer les lignes filtrées.
Comme tu y tiens j'ai mis 1 ligne de code pour faciliter le filtrage.
Filtre basé sur 'Contient'. Tu peux saisir qu'un partie du contrat, utiliser les jokers * (remplace n'importe quelle chaine de caractères) et ? (remplace un caractère)
eric
Bonjour,
Salut Eriiic
La "Vers. 2208 (2023)" que tu as renseignée ... c'est Microsoft 365 ?
En fait, il faudrait que le nom du dépôt et du transporteur soient recherchés dans le fichier de base qui lui même fait une recherche V via une base de données pour obtenir le nom du dépôt et du transporteur en tapant le n° de contrat. Penses-tu que c'est possible ?
J'ai un peu de mal à comprendre : tu as utilisé une RECHERCHEV et tu me demandes ... si c'est possible d'en utiliser une !?
Dans le fichier que tu as déposé hier, en A2 de la feuille 1, tu avais
=SIERREUR(RECHERCHEV(C2;[PARC2021]Feuil2!A:H;8;FAUX);"")Dans ta seconde feuille, tu peux donc inscrire en A6 :
=SIERREUR(RECHERCHEV(D6;[PARC2021]Feuil2!A:H;8;FAUX);"")... ou alors quelque chose m'échappe ?
Précise aussi ce qui manquerait pour "mettre en place mon idée de base " ... on n'a pas apporté de réponse complète ?
[Edit :] Pas rafraîchi
oui. D'autant plus que la feuille 'Fichier souhaité' ne reflète pas vraiment la demande qui était, pour moi, assez bien expliquée (?!?)
Bonjour à tous les deux !
C'est très compliqué de se faire comprendre lorsqu'il n'est pas possible de joindre les fichiers exact. Je m'excuse si ce n'est pas clair.
@Umilité : Dans ma pièce jointe, la feuille 1 est le fichier source. Il y a une recherche V déjà mise en place par mon chef oui, mais ce n'est pas sur ce fichier que je travail :). Je demandais s'il était possible de faire une recherche V dans la feuille 2 (donc fichier sur lequel je travail pour avoir ce que je souhaite) à partir d'une recherche V déjà réalisé (feuille 1). Autrement dit, la recherche V d'une recherche V.
@Eric : Je ne me connecte jamais sur le forum. D'autant plus, que j'étais en vacances. Mais je n'oublie jamais la politesse
Je me suis connectée spécialement pour te remercier pour ton aide concernant le tableau croisé dynamique. Je l'ai proposé à une collègue et ça lui a beaucoup plu.
Je continue de travailler dessus et sur le fichier initial également.
Merci encore à vous deux.
Bonjour,
Pour utiliser RECHERCHEV, l'ordre des colonnes de la table_matrice est déterminant et, dans ton exemple, ça ne fonctionnera pas : la fonction cherche une valeur dans la première colonne (à gauche) de la table_matrice et renvoie donc une information qui ne peut figurer qu'à droite de cette première colonne. Tu me suis ?
Si tu disposes d'Excel 365 (tu n'as pas précisé) tu peux utiliser :
=RECHERCHEX(D6;'Fichier utilisé'!$C$2:$C$9;'Fichier utilisé'!$A$2:$A$9)Dans le cas contraire,
=INDEX('Fichier utilisé'!$A$2:$A$9;EQUIV(D6;'Fichier utilisé'!$C$2:$C$9;0))Les 2 sont à insérer en A6 (précédées d'un SIERREUR)
Teste dans ton "vrai" fichier et reviens nous dire
Bonjour,
as-tu regardé cette autre proposition ?
eric
Bonjour,
ne te contente pas de le garder dans un coin de ta tête, les TCD sont hyper puissants.
Ca vaut le coup d'y consacrer 1/2h pour comprendre la philosophie.
Tableau Croisé Dynamique (TCD) : http://www.mdf-xlpages.com/modules/smartsection/item.php?itemid=109Manque-t'il des infos ?
J'ai peut-être raté qq chose dans les explication, complète si besoin.Ceci dit, je viens de penser qu'on peut faire plus simple avec Sous-total() pour sommer les lignes filtrées.
Comme tu y tiens j'ai mis 1 ligne de code pour faciliter le filtrage.
Filtre basé sur 'Contient'. Tu peux saisir qu'un partie du contrat, utiliser les jokers * (remplace n'importe quelle chaine de caractères) et ? (remplace un caractère)eric
Bonjour !
J'ai pris le temps la semaine dernière de bien travailler le fichier avec le TCD que tu m'a proposé ERIIC et ton idée a définitivement révolutionné le service. Mon chef est plus que ravi et mes collègues sont aux anges ! Ca peut paraître minime mais je t'assure que c'est énorme pour nous. Nous gagnons un temps considérable grâce à ce TCD et aux Segments.
Umilité je voulais te remercier pour tes formules recherche V que j'ai également exploité. C'est magique. Je n'aurais jamais trouvé tout cela sans vous.
Merci infiniment à vous 2 !
Très bonne journée !