Discussion :

[roxadi]

Bonjour à tous,

Je fais partie d'une association de parents qui organise chaque année une vente de livres d'occcasion ; à cette fin, j'ai créé un fichier du genre dépôt-vente, super basique puisque je suis loin d'être une pro. Je ne savais même pas quel titre donner à cette requête !
Il est composé d'une page "Ventes", une page "Achats" et une page "Base de données". Sur cette dernière je renseigne tous les livres mis en vente sur 2 colonnes : référence et prix. Lors de la vente, je renseigne chaque référence dans des petits tableaux de la page achats, et le prix et le total par client s'affiche automatiquement. Jusque là, à la fin de la vente (qui ne dure qu'un jour), je mettais en couleur manuellement chaque livre de la base de données vendu, et comme le premier chiffre de la référence correspondait toujours au numéro des vendeurs, je faisais des copier-coller sur la page des ventes pour savoir qui avait vendu pour combien.
Comme dit, basique.
Je voudrais demander de l'aide pour savoir comment paramétrer ce fichier afin que lorsque qu'une référence est notée dans la page achat, la cellule de la base de donnée qui lui correspond soit colorisée (en vert par exemple).
J'ai essayé une mise en forme conditionnelle des cellules de la base de données en faisant un "trouve" sur la page des achats, mais cela ne marche que sur la première cellule de cette dernière... Cela me permettrait de gagner beaucoup de temps (on vend chaque année plus de 1000 livres).
Comme je le disais, je ne suis pas très experte, et j'espère que mes explications ont été claires...
Merci pour votre aide.

[gFZT82]

Bonjour,

Tu peux appliquer une mise en forme conditionnelle sur la colonne "Référence" de la feuille "Base de données" en utilisant une formule du type
=NB.SI(MaPlage;A1)>0

MaPlage étant le nom défini pour la plage de cellules qui contient les références dans la feuille "Achats" (=Achats!$1:$1048576 par exemple pour la totalité de la feuille).

A+

[Menhir]

@Roxadi : Pourrais-tu mettre en pièce jointe ton fichier pour avoir une meilleure vision de la structure de chaque page ?


@gFZT82 : je pense qu'il y a une ")" mal placée dans ta formule.

[roxadi]

Merci, merci, merci, ça marche ; j'ai juste dû mettre COUNTIF (j'ai oublié de préciser que mon Excel est en anglais).
Je n'ai plus qu'à organiser le fichier au mieux... Je l'ai mis en pièce jointe : s'il vous plaît, ne rigolez pas, je ne connais pas le quart de la moitié du tiers des fonctions de ce logiciel qui pourtant, je le sais, peut quasiment tout faire...

VenteLivres_test2.xlsx

[Menhir]

Il y a deux choses que je ne comprends pas dans ton système.

Quelle est la relation entre les onglets Achat et Vente ?
Est-ce que chaque transaction est inscrite deux fois (une fois en Achat et une fois en Vente) ?

Est-ce que la base de données Livres est remplie au préalable pour savoir quels sont tous les livres à la vente et leur prix ou bien est-ce qu'elle est remplie à postériori en fonction des transactions réalisées ?

[roxadi]

Il n'y a pas de relation entre les acheteurs et les vendeurs ; le premier jour de la vente, chaque parent apporte ses livres (ils sont identifiés dans le fichier comme vendeurs). Le lendemain, les livres sont vendus : chaque client est identifié comme acheteur. Donc un vendeur qui a le numéro 12 par exemple dans le fichier peut être l'acheteur numéro 38. Un acheteur paie tous ses achats même s'il a des livres en dépôt, et on le rétribue le lendemain, après les comptes, s'il a vendu des livres.
Je remplis la base de données avec tous les livres mis en vente au fur et à mesure qu'ils nous sont déposés.
Jusque là, quand les livres étaient vendus, je colorais les cellules correspondantes dans la BD afin de les repérer et après la vente, je faisais des copier coller vers les feuilles "Vente" pour obtenir le montant des ventes de chacun. Beaucoup de bricolage mais ça marchait assez bien.
Voici le fichier d'une ancienne vente pour illustrer à quoi il ressemble après : VenteLivres2010.xls
Ce qui m'amène à essayer d'améliorer ce fichier c'est le problème du temps que cela prend de tout recopier... et du risque d'erreur (ce qui nous est déjà arrivé)
L'idéal serait que quand un livre est vendu, donc que sa référence apparaisse dans une feuille Achats, elle soit automatiquement reportée dans la colonne du vendeur correspondant ; mais pour cela il faudrait entrer je pense dans la programmation, et là, c'est du chinois pour moi... J'aimerais beaucoup apprendre mais je n'en ai pas le temps pour le moment.
J'espère que mes explications ont été claires...

[Menhir]

Je pense que ton problème est plutôt conceptuel. Il faudrait repenser l'organisation de ton fichier mais je pense que tout ça peut se faire sans programmation.
Laisse-moi un peu de temps et je te prépare un truc.

Qu'est-ce que tu dirait d'un fichier sur trois onglets :

Un onglet "Personnes" dans lequel, pour chaque personne, serait indiqué son nom, son numéro, la somme de ses ventes et la somme de ses achats (ces deux dernières colonnes calculées automatiquement d'après les données de l'onglet transaction).

Un onglet "Livres" dans lequel seraient répertoriés tous les livres avec leur prix et s'il a été vendu (cette dernière colonne renseignée automatiquement d'après les données de l'onglet transaction).

Un onglet "Transaction" dans lequel on indiquerait pour chaque vente le nom (ou le numéro) du vendeur et de l'acheteur et la référence du livre et son prix (cette dernière colonne renseignée automatiquement d'après les données de l'onglet Livre).

Le tout bien sûr avec les filtres qui vont bien et qui te permettrait, par exemple, d'avoir la liste des livres vendu ou non-vendu, le bilan des achats (ou des ventes) d'une personne, qui est en positif ou négatif dans son bilan achat-vente, etc.

Le seul truc qui n'est pas encore clair pour moi c'est la façon de renseigner les personnes dans la transaction.
Serait-il mieux de donner leur numéro ou de faire des listes déroulantes dans chaque cellule proposant la liste de tous les noms ?

[roxadi]

Alors, ça, c'est super sympa !
Juste une précision : un vendeur ne viendra pas forcément acheter des livres (si son enfant quitte l'école par exemple) comme un acheteur n'aura pas forcément mis de livres en vente. C'est pour cela que nous ne reprenons pas les mêmes numéros pour les uns et les autres.
En fait, un vendeur arrive chez nous avec une liste sur laquelle sont notés les titres de ses livres à vendre. Nous attribuons un numéro à cette liste (par ordre d'arrivée en général). La référence du livre est composée du numéro de la liste suivi du numéro de la ligne où le titre est noté. Cette référence est notée sur la fiche et dans le livre, ainsi que son prix. Ensuite je rentre les références dans le fichier, comme expliqué avant.
Le plus important pour nous, c'est de pouvoir rapidement faire un total lorsqu'un acheteur se présente (d'où l'onglet BD qui renseigne les prix), et surtout savoir exactement quels livres un vendeur aura vendu, afin de lui verser son argent et lui rendre les invendus. Il y a en moyenne chaque année 1300 livres qui transitent dans cette vente qui ne dure que 2 jours (un pour les dépôts et un pour la vente), c'est pour cela que je cherche à avoir un outil plus performant. Pour la petite histoire, avant que je ne commence à me servir de ce fichier, les références des livres étaient notés sur des post-it, que nous reprenions lors des achats, et collions sur les fiches des vendeurs avant de faire les totaux avec une calculatrice...
Par conséquent, le fichier que tu proposes serait pour moi la 8ème merveille du monde !!!
Pour le dernier point que tu évoques, je sais selon l'expérience que j'ai de cette vente que les numéros des fiches vendeurs sont plus fiables pour nous que les noms (parce que chaque année, il y a des oublis, des gens qui reviennent des heures plus tard et on se retrouve avec 2 ou 3 fiches différentes qui portent le même nom...).

[Menhir]

Juste une précision : un vendeur ne viendra pas forcément acheter des livres (si son enfant quitte l'école par exemple) comme un acheteur n'aura pas forcément mis de livres en vente. C'est pour cela que nous ne reprenons pas les mêmes numéros pour les uns et les autres.

Je ne vois pas trop l'importance que ça pourrait avoir.
Enfin... si, je le comprends quand je vois la façon dont ton fichier est conçu avec un onglet vente, un onglet achat et une colonne pour chaque personne, ce qui est énorme. Déjà, je pense que tu aurais du le faire dans l'autre sens, mais c'est un détail.

En fait, pour moi, il y aura un seul onglet "personne" avec une personne par ligne. Donc, peu importe qu'il y ait des vendeurs qui ne soient pas acheteur et réciproquement. De toute façon, le nombre de personnes influera assez peu sur l'ergonomie de la partie Transaction où seront spécifiées les achats/vente.

Le plus important pour nous, c'est de pouvoir rapidement faire un total lorsqu'un acheteur se présente (d'où l'onglet BD qui renseigne les prix)

Le prix de chaque livre sera indiqué dans l'onglet Livre, avec ses autres renseignements (code, titre, vendu?, propriétaire).

et surtout savoir exactement quels livres un vendeur aura vendu, afin de lui verser son argent et lui rendre les invendus.

Dans le fichier que je suis en train de faire, il y aura trois solution pour ça.
Soit regarder dans le fichier des Personnes où sera indiqué pour chaque personne le total des ventes et des achats (en €).
Soit Activer le filtre Vendeur (juste un bouton) sur l'onglet des transactions pour connaitre toutes les transactions ayant eu ce vendeur.
Soit Activer le filtre vendeur (et éventuellement le filtre Vendu?) dans le fichier livre pour connaitre le status de tous les livres de ce vendeur.

chaque année 1300 livres qui transitent dans cette vente

Si on prévoit un dimensionnement à 300 personnes, 2000 livres, 2000 transactions, ça pourra aller ?

2 ou 3 fiches différentes qui portent le même nom...

Aie !!! Ca, ça risque de poser problème.
J'avais prévu de mettre mon et prénom des personnes. Il faudrait vraiment que cette référence soit unique parce que sinon les fonctions de recherche vont balancer des erreurs. S'il y a des fiches qui ont même nom, même prénom, il faudra ajouter un numéro ou une lettre dans la désignation pour la rendre unique.

Je te mets en pièce jointe un brouillon de ce que j'imagine.
Il ne contient pas encore les formules de recherche et de calcul donc ne t'inquiète pas si ça ne marche pas.
C'est juste pour donner une idée de l'ergonomie.
La philosophie globale c'est que tu renseignes ce qui est en fond vert, tu ne touches pas à ce qui est en fond rouge (ça se calcule tout seul).

L'idée (tel que j'ai compris ton process), c'est que :
Quand une nouvelle personne arrive avec sa liste, tu commences par inscrire son nom (et seulement son nom) dans l'onglet Personne. La colonne ID représentera l'identificateur numérique de la personne.
Dans l'onglet livre, dans une nouvelle ligne, tu sélectionnes la personne dans une liste déroulante implantée dans chaque cellule de la première colonne.
Tu donnes un numéro (N°) à chaque livre (le numéro doit être unique pour la personne mais peut être le même que celui d'une autre liste. Un code est ensuite créé qui concatène l'ID de la personne et le N° du livre.
C'est aussi là qu'on indique le prix.

Tout ça, c'est le boulot pour la première journée.

Pour le lendemain, c'est dans l'onglet Transaction que ça se passe.
Pour chaque transaction, tu sélectionnes l'acheteur (si ce n'est pas un vendeur, il faudra le rajouter la liste des personnes), le vendeur (son ID sera retrouvée automatiquement) et le N° du livre pour ce vendeur. Le reste se renseignera tout seul.
Il est impossible d'indiquer un vendeur ou un acheteur qui ne soit pas dans la liste des personnes (j'ai mis un verrouillage par la vérification de données qui gère aussi les listes déroulantes).
Cependant si tu ne veux pas indiquer d'acheteur, rien ne t'empêche de laisser "Nom" dans cette case. Je te conseillerai plutôt de créer un acheteur fictif "Nobody", histoire d'avoir une balance des achats et des vente dont on puisse vérifier l'équilibre.

Dans les onglets Livres et Transaction, j'ai mis des filtres, ce qui te permet de faire des tris ou de n'afficher que les lignes d'une certaine personnes (par exemple) simplement en cliquant sur l'une des flèches du titre et en sélectionnant une option.

A chaque transaction, le montant des achats et des ventes d'une personne se remet à jour ainsi que le status "Vendu?" des livres.

Voilà en gros comment c'est supposé marcher.
Tu me dis ce que tu en penses.

Je te joints donc une première version SANS FORMULES de ce que j'ai déjà fait pour que tu puisses commencer à jouer avec : saisir des truc, voir les listes déroulantes, etc.

Ne commence pas à le remplir "pour de vrai" parce que rien ne dis qu'il sera possible de transférer facilement les données vers le fichier définitif.

Je pense que je pourrais te donner la version définitive à la fin de la semaine.

[roxadi]

Bon, une fois de plus, je me sens petite. "Le faire dans l'autre sens", je ne vois même pas ce que tu veux dire... Je suis plutôt littéraire, même si j'adore travailler sur l'ordi et apprendre à en faire toujours plus.
Je ne pourrai pas, par manque de temps, renseigner les titres des livres, mais je suppose que c'est un détail insignifiant. Et c'est mieux de laisser cette colonne, au cas où un jour j'arrive à les rentrer.
300 personnes, 2000 livres c'est plus que suffisant. Pour les transactions, on engrange +/- 1300 livres, on en vend +/- les 2/3, donc 2000 transactions devraient suffire.
Pour les doublons des noms des personnes, je peux arranger ça en mettant un Paul Durand_2 par exemple si nécessaire... Ok.
Tout est parfait, mais il y a juste une chose qui me pose problème pour le 2ème jour : l'acheteur va se présenter avec des livres qui comporteront uniquement une référence (type 8-15). Tu me dis qu'il faudra renseigner le vendeur, mais je n'aurai que son ID (premier numéro du code indiqué sur le livre) ; serait-ce possible de renseigner le code du livre et que la recherche se fasse là-dessus ? Où au moins sur l'ID ?
Un grand merci.

[Menhir]

"Le faire dans l'autre sens", je ne vois même pas ce que tu veux dire...

Mettre les colonnes en lignes et réciproquement. D'une manière générale, quand on fait un tableau Excel, on essaye de mettre la plus grande dimension dans le sens des lignes.

Dans ton cas, tu as environ une centaine de personnes pour une dizaine de livres par personnes. Il aurait donc été préférable de mettre une personne par ligne et un livre par colonne. Ca aurait été plus pratique pour chercher des personnes. En plus, étant donné la taille réduite des codes de livres, ça t'aurait permis de faire des colonnes toute petite et de voir toute les colonnes sur un écran.

Je ne pourrai pas, par manque de temps, renseigner les titres des livres, mais je suppose que c'est un détail insignifiant.

Le champ est présent mais n'a aucune utilité fonctionnelle. Donc, si tu souhaites le remplir, tu peux le faire. Si tu ne le souhaites pas, ça n'est pas gênant.

300 personnes, 2000 livres c'est plus que suffisant. Pour les transactions, on engrange +/- 1300 livres, on en vend +/- les 2/3, donc 2000 transactions devraient suffire.

Il me semble quand même logique de prévoir autant de transactions que de livres.
Si ça commence à être lent à l'usage, il ne sera pas compliqué de réduire la voilure.

Pour les doublons des noms des personnes, je peux arranger ça en mettant un Paul Durand_2 par exemple si nécessaire... Ok.

En fait, depuis mon dernier message, j'ai changé mon fusil d'épaule. Je suis plutôt parti sur une gestion des personnes par leur numéro.
Les listes déroulantes de noms, c'était sympa mais le problème c'était de récupérer ensuite le code de la personne. La fonction RECHERCHE de Excel ne fonctionne que si la plage de recherche est triée par ordre alphabétique. Donc, ça imposait que tu fasses un tri après avoir introduit chaque nom (ou paquet de noms) et ça pouvait donner des renseignements erronés si c'était pas fait.
Ou alors, il aurait fallu créer une fonction par macro. J'aurais pu le faire mais je préfère éviter.

Donc, je suis parti, pour les onglets Livre et Transaction, sur un renseignement des personnes par leur code avec, en sécurité, un affichage du nom (automatique) pour voir si c'est le bon.

Bref, avoir deux noms identiques ne pose plus de problème.

Tout est parfait, mais il y a juste une chose qui me pose problème pour le 2ème jour : l'acheteur va se présenter avec des livres qui comporteront uniquement une référence (type 8-15). Tu me dis qu'il faudra renseigner le vendeur, mais je n'aurai que son ID (premier numéro du code indiqué sur le livre) ; serait-ce possible de renseigner le code du livre et que la recherche se fasse là-dessus ? Où au moins sur l'ID ?

En fait, il n'y a même pas besoin de faire de recherche. Il suffira de procéder comme tu le fait actuellement, avec un code de livre qui soit composé de l'ID vendeur et du N° de livre.

Par exemple, quelqu'un vient demander d'acheter le livre 18-4 : tu tapes 18 en vendeur et 4 en livre pour la transaction.
Le fait d'être passé en renseignement des personnes par numéro et non par nom est donc une bonne idée en plus d'être techniquement plus facile.


J'ai terminé une version. Il est fonctionnel mais il faudrait lui faire passer l'épreuve du feu. Quand on est concepteur, on lui fait faire ce qu'on sait qu'il fait bien. Pour tester efficacement, il faut le mettre dans les mains d'un "candide" qui va lui faire faire des trucs imprévus et potentiellement "destructeurs".

Je te mets le fichier en pièce jointe.

Il y a les trois même onglets que le précédent.
Comme le précédent, tu ne touches qu'à ce qui est en vert (ou en vert gris). Le reste se calcul tout seul.
Tout ça est fait sans code VBA, juste avec des fonctions Excel, des Mise en formes conditionnelles et des tris automatiques.

Ca commence par l'onglet Personnes.
Là, tu n'as à renseigner que les noms. Tu peux le faire comme tu veux : majuscules, minuscules, avec/sans prénom, pseudo, surnoms, etc.
Seule chose : Evite de trop le tripatouiller avec des tris ou des filtres. La liste des ID doit rester dans l'ordre (sinon les recherches ne marcheront pas). Si tu dois faire un tri, ne le fait que sur la colonne 1, sans toucher à la ligne 4 et avant d'avoir commencé à renseigner les autres tableaux.
C'est pour ça que c'est le seul tableau pour lequel je n'ai pas mis de filtres automatique.

La première ligne "Nobody" (ID 0) te permet juste d'utiliser une personne "générique". Par exemple tu peux l'utiliser si tu n'as pas le temps de donner le nom d'un acheteur le second jour. "0", c'est facile à retenir comme identifiant.

Ce tableau indique la somme des achats et des ventes pour chacun. J'ai ajouté une mise en forme automatique qui passe en grisé les valeurs nulles, ce qui permet de repérer plus facilement celles sur lesquelles il y a eu du mouvement.
La dernière colonne fait le différence des deux. J'ai bien compris que ça ne t'est pas vraiment utile mais comme ça ne coûte qu'une soustraction, autant le mettre, "au cas où".

Dans l'onglet Livre, il y une ligne par livre.
Quand tu inscris un livre, tu commences par donner l'ID du vendeur. Automatiquement, le nom correspondant s'affiche (ce qui limite les erreurs de frappe ou de mémoire). Evite de donner le numéro d'une personne qui n'est pas inscrite dans la liste des personnes.

Tu donnes un numéro de livre pour ce vendeur. Le numéro doit être unique pour ce vendeur mais peut être le même que celui du livre d'un autre vendeur. Rien n'oblige à avoir une suite continue pour un vendeur (par exemple, s'il a trois livres, tu peux leur donner comme numéro 5, 10 et 100).
L'ID et le numéro sont groupés en un Code formé de "Code-N°" indiqué dans la colonne suivante.

Au début, j'avais mis une numérotation automatique qui donnait un nouveau numéro chaque fois qu'on ajoutait une ligne, mais j'ai abandonné parce que ça limitait les possibilités de tri. A la place, j'ai fais une vérification de doublons qui t'évite de mettre deux fois le même numéro pour un vendeur. Si ça arrive, la case Code se met en rouge vif.
Rien ne t'oblige, non plus, à grouper les livres par vendeur. De toutes façon, tu peux très bien les regrouper à postériori en utilisant les filtres (clique sur les flèches qui se trouvent dans les entêtes de colonnes).

La colonne Titre peut être renseignée ou non, peu importe. Rien ne t'empêche de l'utiliser éventuellement pour autre chose comme indiquer l'état du livre ou autre chose.

Pour chaque livre, le prix doit être renseigné. Au pire, il faut mettre un 0 mais ne pas laisser vide.

La dernière colonne scrute les transaction. Si ce livre a été vendu, elle indique "Vendu" sur fond jaune.
S'il est indiqué "Survendu" sur fond rouge, ça veut dire que le livre se trouve sur plus d'une ligne de transaction. C'est une sécurité supplémentaire.

Venons-en justement à l'onglet Transaction, celui qui du second jour.
Pour une transaction, tu donnes simplement le numéro de l'acheteur (ou 0 si tu ne veux pas renseigner un acheteur qui n'est pas vendeur), le numéro du vendeur (là, c'est obligatoire) qui est la première partie du code puis le numéro du livre.

Ca vérifie si ce code (ID + N°) existe. Si ce n'est pas le cas, la colonne Code se met en rouge vif et indique Erreur. Ca veut dire que ce code n'a pas été trouvé dans la liste des livres.
Si le code est trouvé, la dernière colonne indique le prix.

Sur tout ça, il y a des filtres (les flèches dans les entêtes de colonnes) qui te permettent, par exemple, de faire une liste de tous les achats d'une personne donné ou de trier les transactions par code de livre.

En y réfléchissant, je me dis qu'on aurait pu se contenter de deux onglets en combinant Livre et Transaction. Il aurait suffit de rajouter une colonne Acheteur à Livre. Il aurait suffit de renseigner ce champ pour indiquer que le Livre avait été vendu.

Il est toujours possible d'améliorer.

Dis-moi ce que tu penses du résultat.

[roxadi]

Cela semble parfait, et tu as anticipé je pense toutes les erreurs que nous avons rencontrées par le passé. Je vais le tester mais... Je ne l'ai pas trouvé en pièce jointe...
En tout cas, merci pour tout cela, c'est trop top !

[Menhir]

Je ne l'ai pas trouvé en pièce jointe...

Je vais réessayer...

En tout cas, merci pour tout cela, c'est trop top !

En cas de réponse satisfaisante, un clic que les est toujours apprécié (d'autant plus que c'est trop rare).

[roxadi]

OK, cette fois je l'ai. Je viens de le tester vite fait et c'est pil poil ce qu'il me fallait.
Je vais refaire des essais dans la journée avec des données des anciens fichiers, pour me mettre un peu plus dans les conditions de la vente, mais franchement, pour avoir jonglé avec ces livres depuis 5 ans, je peux déjà te dire qu'il est parfait pour nous.
Je te confirme ce soir.

[Menhir]

Je viens de faire une nouvelle version.

Comme je l'avais dit, j'ai viré l'onglet Transaction.

Pour l'onglet Personnes, rien ne change (excepté les formules qui sont dans les colonnes rouges). Le principe et les précautions sont les mêmes.

L'onglet Livres est, en gros, le même, excepté les deux colonnes supplémentaires indiquant le vendeur.

Pour l'utiliser, je conseille, à la fin de la première journée, de le trier sur la colonne Code (en utilisant le filtre automatique), histoire que les livres soient plus facile à repérer pour le lendemain.

Quand une vente a lieu, il suffit juste d'indiquer le numéro de l'acheteur ou "0" (Nobody) si l'on ne souhaite pas avoir le cumul de ses achats.
Une case vide indique que le livre n'est pas vendu.
J'ai laissé la colonne "Vendu?" par précaution (pour être sûr du status du livre) bien qu'elle ne soit plus indispensable.

C'est plus simple et plus fiable : une vente = une case à remplir.
Moins de risque d'erreur sur le code du livre vendu.