résumer des feuilles: prix produits, remise selon clients, produits selon client

[firedavid]

Bonjour,
Voici le problème à résoudre :

Version Excel 2003 en Anglais

- 1 feuille avec une liste de produits avec le prix catalogue selon le produit
- 1 feuille avec une liste de clients et la remise appliquée selon le client
- 1 feuille avec la liste de produits référencés selon le client

Objectif :
- dans une nouvelle feuille :
Choisir le nom du client (à selectionner dans une liste) et afficher automatiquement la remise appliquée et afficher uniquement la liste des produits référencés chez le client avec le calcul du prix catalogue remisé

Merci d'avance pour votre aide.
David

 

[Dugenou]

Re : résumer des feuilles: prix produits, remise selon clients, produits selon client

Bonjour,
voici une possibilité. Pour la liste des produits par client j'ai utilisé une formule matricielle. A chaque modification il faut valider par ctrl+maj+enter.
Cordialement

 

[pierrejean]

Re : résumer des feuilles: prix produits, remise selon clients, produits selon client

Bonjour firedavid

Salut Dugenou

Puisque je l'ai Fait !!

 

[firedavid]

Re : résumer des feuilles: prix produits, remise selon clients, produits selon client

Bonjour,
Merci pour votre aide et vos conseils.
Le résultat est top , en effet c'est ce que je recherche.
Maintenant est ce qu'il y a une façon peut-être plus "simple" à tout hasard car je ne maîtrise pas du tout les formules matricielles.
Dans la partie "Referencement Produit Client" j'ai mis des 1 ou des 0 (à la base j'avais juste mis un "x") pour indiquer si le produit était référencé, quelle est la meilleure mise en forme ?

Vous pouvez modifier entièrement la mise en page des tableaux de chaque feuille si besoin si vous pensez que c'est plus simple.

Merci pour la formule simplifiée dans la partie "Liste Client c'est bien mieux et moins lourd en effet

Sincères salutations,

 

[firedavid]

Re : résumer des feuilles: prix produits, remise selon clients, produits selon client

superbe pierrejean, en revanche j'ai rien compris ! lol
apparemment il faut activer les macros et j'ai reussi à voir le code VB mais je suis novice alors pour refaire ce travail et l'adapter à mon vrai fichier ça va être compliqué
quelque chose de plus simple peut-être ?

Merci pour votre aide , et merci à tous pour votre solidarité )

 

[Dugenou]

Re : résumer des feuilles: prix produits, remise selon clients, produits selon client

Bonjour,
ta présentation avec des 1 et des 0 est très pratique. Rien à changer sauf si tu mettais des 2 au lieu de 1. cela permettrai de simplifier un tout petit peu la formule. Le tableau en dessous des produits par client n'est pas indispensable mais il permet de faire une formule plus simple dans la feuille résultat. Il est compréhensible que tu aies des difficultés à adapter cet exemple sur ton vrai fichier : n'hésites pas à demander des explication ou à faire part des dificultés que tu rencontres.
Cordialement

et salut Pierrejean !

 

[pierrejean]

Re : résumer des feuilles: prix produits, remise selon clients, produits selon client

Re

Tout depend de ce que tu veux comme adaptation
Il suffit pour l'instant de conserver les lignes et colonnes concernant les differentes données; et la macro s'adaptera aux nombres de produits et clients ainsi qu' aux contenus

 

[Dugenou]

Re : résumer des feuilles: prix produits, remise selon clients, produits selon client

Bonjour,

question complémentaire : adapter le cadre en fonction du nombre de ligne à chaque fois que l'on change de client,
c'est fait dans la PJ avec un format conditionnel.

J'ai légèrement modifié les formules pour avoir 0 quand pas de réponse et mis un format personnalisé pour masquer les 0
J'ai aussi modifié les formules pour utiliser tout le temps la référence produit plutôt que le produit : la table maitre est dans la feuille des prix par produits.

Cordialement

 

[firedavid]

Re : résumer des feuilles: prix produits, remise selon clients, produits selon client

Bonjour,

Oui tout à fait Dugenou cela serait très utile pour une belle mise en forme. Et je rajoute une tâche : par la suite d'intégrer un bouton "imprimer" pour générer un .pdf avec une mise en page type template à appliquer à chaque fois.

PJ (Pierre Jean) merci pour ton VB qui sera certainement utile pour les autres exceliens car pour moi c'est compliqué à adapter, pour le moment j'utilise la matrice de Dugenou

S'il y a d'autres suggestions je suis preneur

Solidairement,
David

 

[Dugenou]

Re : résumer des feuilles: prix produits, remise selon clients, produits selon client

david,

oups on s'est croisé : la réponse est au dessus !

J'espère que PierreJean repassera sur ce fil : je ne sais pas faire de bouton avec impression pdf.
mais tu peux rechercher sur le forum : c'est une question fréquente

ma suggestion de remplacer les 1 par des 2 était idiote et sans intérêt.
Cordialement

 

[firedavid]

Re : résumer des feuilles: prix produits, remise selon clients, produits selon client

Hello,

Merci j'ai quelques remarques / demandes :

- dans "Produits vs prix remises" en B3 il y a " stop " => est ce que je peux le modifier par "aucun" ou " - " ou autre , si oui quels modifs ça implique ?
- tjs dans cette feuille j'ai rajouté des colonnes avec de nouvelles données, j'ai remarqué que la cellule C3 "description" avait du coup un impact sur la feuille "Résultat souhaité" (voir pièce jointe), je n'arrive pas à modifier " 'Produits vs prix remises'!$B$3:$C$9,2,FALSE " sans que ça fasse une erreur,
- dans "Referencement Produit Client" , j'ai voulu remplacer "...+COUNTA(D$1518))),"stop") par " ... +COUNTA(D$1518))),"-") ou "aucun" , afin de remplacer le terme "stop"
- j'ai rajouté une feuille " Mise en page pour impression" , comment intégrer les éléments de "Résultat souhaité" en fonction du client avec tous les champs de "Produits vs prix remises" ? (comme dans l'exemple) , tout en faisant attention à la mise en forme avec les bordures selon le nombre de ref.

Encore merci pour ton aide précieuse,
Solidairement,
David

 

[Dugenou]

Re : résumer des feuilles: prix produits, remise selon clients, produits selon client

Bonjour,
Dans la feuille référencement produit client, pour éviter d'afficher une erreur quand il n'y a plus de produit on affiche "stop" mais ça peut être - ou aucun.

=SI(NB.SI(D$5:D$10;1)>=NBVAL(D$15:D15);INDEX($B$1:$B$10;PETITE.VALEUR(SI(D$5:D$10=1;LIGNE(D$5:D$10);0);NB.SI(D$5:D$10;0)+NBVAL(D$15:D15)));"-")

où on remplace "stop" par "-" : à valider par ctrl+maj+enter


Ensuite pour afficher le résultat souhaité on fait une recherche de la référence produit affichée ci dessus sur la feuille produit vs prix remisé et pour éviter à nouveau une erreur j'ai utilisé stop (mais ça peut être - ou aucun) dans la colonne des références et j'ai laissé vide dans la colonne des valeurs retournées.

Je comprends qu'il vaut mieux que cette valeur supplémentaire (stop ou- ou aucun) te gène quand elle se trouve dans les titres, donc je l'ai placée en fin de liste. Cela change la formule de recherche :
=RECHERCHEV(INDEX('Referencement Produit Client'!$D$16:$M$21;NBVAL($B$11:B11);EQUIV('Résultat souhaité'!$B$4;'Referencement Produit Client'!$D$4:$M$4;0));'Produits vs prix remises'!$B$3:$C$10;2;FAUX)

Pour la page d'impression : on peut tout faire directement dans cette feuille sans passer par la feuille résultat souhaité.
J'ai mis une liste déroulante en C9 et j'ai modifié les formules pour travailler seulement sur la référence produit (qui doit être unique) et ensuite toutes les autres données sont obtenues par recherchev.
un format conditionnel permet de gérer les bordure et l'affichage des données utiles.
les commentaires de la colonne G peuvent être effacés.
J'ai automatisé la date mais je ne sais pas si c'est une bonne idée : elle changera à chaque fois qu'on ouvre le fichier.

Pour ajouter des produits :

• dans la feuille référencement produits clients : insérer des lignes entre la 9 et la 10 et entre la 20 et la 21
• dans la feuille produits vs prix remisé : insérer des lignes entre la 8 et la 9

Pour ajouter des clients : même principe : insérer des colonnes avant la fin du tableau actuel dans la feuille ref produit client et des lignes dans la feuille liste client remise.

la feuille résultat souhaité peut être supprimée : j'y ai aussi changé la formule pour te montrer l'affichage des référence au lieu du produit.
Cordialement

 

[firedavid]

Re : résumer des feuilles: prix produits, remise selon clients, produits selon client

Hello,
Merci pour tes précisions, le fichier prend forme c'est top

Remarques :
- tu dis que cela change la formule de recherche la feuille " Résultat souhaité " :
=RECHERCHEV(INDEX('Referencement Produit Client'!$D$16:$M$21;NBVAL($B$11:B11);EQUIV('Résult at souhaité'!$B$4;'Referencement Produit Client'!$D$4:$M$4;0));'Produits vs prix remises'!$B$3:$C$10;2;FAUX)
sachant que j'ai excel en anglais ma formule actuelle dans le tableau est =INDEX('Referencement Produit Client'!$D$16:$M$21,COUNTA($B$11:B11),MATCH('Résultat souhaité'!$B$4,'Referencement Produit Client'!$D$4:$M$4,0)) => il n'y a pas ));'Produits vs prix remises'!$B$3:$C$10;2;FAUX) en version anglaise et quand j'essaie de l'adapter ça plante, donc actuellement en B16 il y a le trait d'union qui apparait ...

J'aimerais comprendre pour modifier malgré que cette feuille ne sera plus vraiment utilisée étant donné qu'il y a la nouvelle feuille "mise en page pour impression"

- dans la feuille "Mise en page pour impression" , j'ai remarqué qu'il y a une ligne en doublon A16 et A17, je n'arrive pas à modifier la ligne pour qu'elle disparaisse.

- Pourquoi dans cette meme feuille tu précises de ne rien écrire en A15 ? est ce que cette ligne peut être supprimée si besoin sans conséquences dans les formules ?
- quand je fais un Ctrl A , on voit en effet tous les 0 ecrit en blanc , question : est ce si j'ai une base de 100 produits mais que par exemple il y a le client 1 qui en a 10 , il va donc y apparaitre 90 ligne avec "0" caché, ... si j'imprime cela ne risque t il pas d'imprimer des feuilles blanches ?

- si j'insère un nouveau produit dans la feuille " Produits vs prix remises " entre la ligne 8 et 9, est ce qu'il y a un moyen pour que cela génère une nouvelle ligne automatique dans la feuille "Referencement Produit Client"

- si j'insère un nouveau client dans la feuille "Liste Client vs remise" entre la ligne 14 et 15, est ce qu'il y a un moyen pour créer automatiquement une nouvelle colonne avec ce client dans la feuille "Referencement Produit Client"

Merci !!!
David

 

[Dugenou]

Re : résumer des feuilles: prix produits, remise selon clients, produits selon client

Bonjour,
il n'y a rien à modifier dans la formule. Il faut seulement que ce qu'on écrit quand il n'y a pas de produit dans la feuille référencement produit client soit écrit aussi en B10 de la feuille produits vs prix remisés.

- dans la feuille "Mise en page pour impression" , j'ai remarqué qu'il y a une ligne en doublon A16 et A17, je n'arrive pas à modifier la ligne pour qu'elle disparaisse.

- Pourquoi dans cette meme feuille tu précises de ne rien écrire en A15 ? est ce que cette ligne peut être supprimée si besoin sans conséquences dans les formules ?

les deux questions sont liées c'est mieux en supprimant la ligne 15

Pour éviter les 0 masqués on peut faire quelque chose (voir PJ) mais il y aura toujours une formule et si tu veux que la zone d'impression soit limitée à la zone affichée il faut la définir avec une fonction DECALER (je ne sais pas si on peut : je regarde). Une autre solution dans ce cas est de revenir à la méthode proposée par PierreJean avec une macro qui ne remplit que les cases nécessaires.
Pour l'addition de produits ou de clients : on ne peut pas faire ce que tu veux par formule mais seulement avec des macros.

 

[Dugenou]

Re : résumer des feuilles: prix produits, remise selon clients, produits selon client

Bonsoir,

V6 avec zone d'impression adaptée au nombre de lignes affichées : yaisssss