Création tableau de suivi excel

[Fermé]
Signaler
Messages postés
6
Date d'inscription
lundi 7 octobre 2013
Statut
Membre
Dernière intervention
8 janvier 2017
-
Messages postés
6
Date d'inscription
lundi 7 octobre 2013
Statut
Membre
Dernière intervention
8 janvier 2017
-
Bonjour à tous,

Je suis en cours de création d'une petite boutique web via ebay ( et peut être d'autres cyber market à terme).

Pour assurer le suivi de mes ventes, voir si je suis rentable, surveiller mes stocks etc... j'aurai besoin d'un gros coup de main pour la création d'un tableau excel. (version 2010)
Je ne suis pas du tout un expert sous excel mais je sais qu'on peut faire plein de supers trucs si on utilise les bonnes formules.
Sachant qu'il y a des grands spécialistes ici, si quelqu'un aurait la gentillesse de m'aider en insérant les bonnes formules dans mes colonnes ça serait génial.
J'essai de le créer depuis plus d'un mois et je galère tellement que je baisse les bras. Help please!

Je vais essayer d'expliquer le plus clairement possible ce que je souhaite avoir comme résultat et vous poster ce que j'ai déjà fait pour mieux se rendre compte. Si certains experts, voient qu'en quelques minutes ils peuvent insérer les bonnes formules, ça serait génial. Si c'est trop long, je vais continuer de chercher, faut pas que ça vous prennent des heures.
J'ai regardé des softs déjà tout fait, mais je n'ai pas encore trouvé quelque chose de simple qui correspondent bien à ce que je souhaite.

Voici mon cahier des charges:

J'ai 2 onglet, un pour l'inventaire et données de départ et l'autre qui servira pour la saisie et pour le suivi des ventes.

C'est surtout l'onglet suivi des ventes qui va être le plus compliqué

Le tableau concerné est disponible ici

https://mega.nz/#!tNFCQIhA!s9yrXXwv0xPGR...2dRrnwEO9g

Colonne A:
J'aimerai que la date du jour s'insère automatiquement quand je sélectionne un produit dans la colonne B

Colonne B:
C'est le menu déroulant me permettant de choisir la référence du produit vendu se référant à la colonne A (produits) de l'onglet inventaire. Le résultat actuel me convient mais je ne sais pas si la méthode que j'ai utilisé pour mes tableaux ne créer pas un bug pour d'autres formules plus loin.

Colonne C:
Là je saisi manuellement la quantité vendue.

Colonne D:
Le prix de vente qui sera saisi manuellement

Colonne E:
Commission du site vendeur ( 7.5%) de la colonne D ( normalement ma formule insérée fonctionne)

Colonne F:
Commission Paypal= 3.4% de la colonne D + 0.25€ ( formule correcte déjà insérée)

Colonne G et H:
Les choses se compliquent un peu à ce niveau. Je souhaiterai qu'il m'affiche directement l'affranchissement que je dois mettre selon le poids de la commande. Dans l'onglet inventaire, J'ai inséré le poids de chaque article en G et le poids du conditionnement en H. Sachant que sauf cas exceptionnel ( que je pourrais corriger manuellement) le conditionnement prévu ne changera pas même si les quantités vendues à un même client augmente. Donc en G le calcul doit donc donner =poids unitaire du produit sélectionné en colonne B * quantité indiqué en colonne C + poids du conditionnement indiqué en colonne H de l'onglet inventaire.
Ensuite en fonction du résultat indiqué en colonne G de l'onglet suivi la colonne H se calcul automatique avec les tarifs postaux classiques. Exemple: Si G inférieur ou égal à 20g alors 0.70€ ( le prix a augmenté au 1er janvier mais c'est un exemple), si G supérieur à 20g et inférieur à 100g alors 1.40€, si G supérieur à 100g et inférieur à 250€ alors 2.10€ etc...)
J'avais déjà fait une formule qui marchait ( on la voit en H de suivi) mais je ne sais pas si la méthode était bonne.

Colonne I:
Marge Nette
Le calcul me semble moins complexe même si je n'ai pas trouvé comment le faire en référence avec l'onglet inventaire.
Le résultat doit donner: Prix de vente colonne D de l'onglet suiv - (prix d'achat colonne E de l' onglet inventaire * quantité vendue colonne C dans suivi) - frais ebay (E) - Frais Paypal (F)- Frais d'envois (H)
Evidemment le calcul se fait en fonction du produit sélectionné en B de l'onglet suivi.

Colonne J:

Là j'ai beaucoup de mal et pourtant je n'ai pas l'impression que c'est compliqué. J'ai lu plein de formules et d'exemples mais chaque fois sur mon tableau ça plante.
Je souhaite que chaque fois que j'ai une vente, la quantité vendue se déduise du stock restant. Le stock de départ est indiqué dans l'onglet inventaire en colonne B. Je suppose que la formule doit être = stock de départ - somme des quantités vendues évidemment toujours en fonction du produit sélectionné en B de l'onglet suivi.

Colonne K:
Très simple, menu déroulant des sites de ventes. Déjà rempli

Colonne L:
Je souhaite savoir à partir de quand j'ai rentabilisé l'achat de départ sur le produit et à partir de quand je commence réellement à gagner de l'argent sur celui ci.
Cette colonne doit prendre l'investissement de départ situé en colonne F de l'onglet inventaire, je dois le mettre ce nombre en négatif ( car c'est une dette pour le moment) et ajouter à ce nombre négatif, la somme des marges nettes réalisées sur le produit ( colonne I de l'onglet suivi) + prix d'achat du produit * quantités vendues. Je rajoute le prix d'achat car il est déjà déduit de ma marge nette, si je dis une bêtise n'hésitez pas à me corriger.

Enfin, j'aimerai avoir une ligne des totaux globaux et avoir la possibilité de faire des sous totaux selon les filtres que j'utilise.
Par exemple, je sélectionne un produit, un mois de vente et il me fait alors apparaitre sur une ligne le nombre de produits en question vendu sur le mois indiqué, ( pas le nombre de cellules, je crois qu'il y a une formule spécifique), le chiffre réalisé et l'amortissement de celui ci.
Je pensais mettre le total en dernière ligne et un sous total au dessus du tableau. Comme ça quand je peux le voir les variations des sous totaux à chaque fois que je modifie un filtre.
Voilà après cette longue tirade ( merci à ceux qui auront eu le courage de lire jusqu'au bout mais j'ai essayé d'être le plus précis possible), j'attends vos idées, suggestions, conseils ou encore mieux si ce n'est pas compliqué un correctif de mon tableau.

Un grand merci d'avance à ceux qui prendront le temps de m'aider.

Amitiés à tous
A voir également:

9 réponses

Messages postés
54540
Date d'inscription
lundi 13 août 2007
Statut
Contributeur
Dernière intervention
2 septembre 2021
16 472
Colonne B: ... je ne sais pas si la méthode que j'ai utilisé pour mes tableaux ne créer pas un bug pour d'autres formules 

Non ; mais ta plage "produit" s'arrête à la ligne 25 ! il faut la prolonger jusqu'à la ligne 99 ou 999 ... De plus cette colonne A n'étant pas triée alphabétiquement, la recherche d'un produit dans la liste déroulante n'est pas très facile ...
3
Merci

Quelques mots de remerciements seront grandement appréciés. Ajouter un commentaire

CCM 65492 internautes nous ont dit merci ce mois-ci

Messages postés
6
Date d'inscription
lundi 7 octobre 2013
Statut
Membre
Dernière intervention
8 janvier 2017

Est ce que si j'ajoute des produits par la suite, mon tableau sera t il limité avec le nombre de produits du départ? Est ce pour cela que tu me conseilles de prolonger le nombre de lignes de la colonne produits?
Pour l'ordre alphabétique, en effet ça semble si évident et pourtant je n'y ai pas pensé. Faut il que je rentre mes produits moi même dans le bon ordre ou y a t il un bouton trier l'ordre quelque part?
Merci d'hors et déjà de tes premières réponses.
Messages postés
54540
Date d'inscription
lundi 13 août 2007
Statut
Contributeur
Dernière intervention
2 septembre 2021
16 472
Bonjour .

Ton lien sur Mega est inexploitable pour nous.
Il vaut mieux envoyer ton fichier avec CJoint.com :
 1) Tu vas dans https://www.cjoint.com/ 
2) Tu cliques sur [Parcourir] pour sélectionner ton fichier (15 Mo maxi)
3) Tu défiles vers le bas pour cliquer sur le bouton bleu [Créer le lien Cjoint]
4) Au bout de quelques secondes la deuxième page s'affiche, avec le lien en gras ; tu fais un clic-droit dessus et tu choisis "Copier le lien"
5) Tu reviens dans ta discussion sur CCM, et dans ton message de réponse
tu fais "Coller".
=>Voir la fiche https://www.commentcamarche.net/faq/29493-utiliser-cjoint-pour-heberger-des-fichiers
Messages postés
54540
Date d'inscription
lundi 13 août 2007
Statut
Contributeur
Dernière intervention
2 septembre 2021
16 472
Colonne A: J'aimerai que la date du jour s'insère automatiquement quand je sélectionne un produit dans la colonne B 

Mauvaise idée ! Que se passera t-il si le lendemain d'une saisie tu dois corriger l'orthographe de la référence ou le prix de vente ? Alors qu'il est si facile d'insérer la date de création avec le raccourci-clavier [ ctrl + ; ]
Messages postés
6
Date d'inscription
lundi 7 octobre 2013
Statut
Membre
Dernière intervention
8 janvier 2017

ok je n'avais pas pensé à ce détail. Autant l'insérer manuellement en effet
Messages postés
54540
Date d'inscription
lundi 13 août 2007
Statut
Contributeur
Dernière intervention
2 septembre 2021
16 472
Colonne G et H: en G le calcul doit donner =poids unitaire du produit sélectionné en colonne B * quantité indiqué en colonne C + poids du conditionnement indiqué en colonne H de l'onglet inventaire

Le poids unitaire est donné en P7 par la formule
=SI(ESTVIDE(suivi[Référence]);"";RECHERCHEV(suivi[Référence];'Inventaire départ'!A5:G25;8;FAUX))
ta formule avec référence à la colonne 5 donne le prix, pas le poids !
Messages postés
6
Date d'inscription
lundi 7 octobre 2013
Statut
Membre
Dernière intervention
8 janvier 2017

En fait, j'ai commencé en essayant directement la formule en H
J'ai inséré la colonne G en me disant qu'il serait peut être plus simple de calculer en 2 temps car ma formule en H est gigantesque avec les supérieurs/ inférieurs ou égal
Si je dois rentrer tous les poids et tarifs postaux dans une même formule, la moindre erreur sera fatale et plus difficile à retrouver. ( en tout cas pour moi ;) )
Messages postés
54540
Date d'inscription
lundi 13 août 2007
Statut
Contributeur
Dernière intervention
2 septembre 2021
16 472
Colonne F: =SI(ESTVIDE(B7);"";(ARRONDI(D7*3,4%;2))+0,25) 

Evite les caractères superflus et les calculs inutiles :
=ARRONDI(D7*3,4%;2)+0,25 marche aussi bien !
Et à part les formules utilisant la fonction RECHERCHEV, où il est nécessaire, le contrôle SI(ESTVIDE) ne sert à rien, si tu as décoché l'option avancée "Afficher un zéro dans les cellules qui ont une valeur nulle".

Colonne G et H: J'avais déjà fait une formule qui marchait (... en H de suivi)

Oui, sauf qu'elle t'affiche des textes et pas des valeurs numériques !
Il ne faut pas mettre les tarifs entre guillemets ...
Remplacer en H7 la formule
=SI(Q7<=20;"0,70";SI(Q7<=100;"1,40";SI(Q7<=250;"2,80";
SI(Q7<=500;"4,20";SI(Q7<=3000;"5,60";"")))))
par
=SI(Q7<=20;0,7;SI(Q7<=100;1,4;SI(Q7<=250;2,8;
SI(Q7<=500;4,2;SI(Q7<=3000;5,6;"")))))

Définir le nom "invent" pour la plage
='Inventaire départ'!$A$5:$J$25
;
en G7 saisir la formule
=SI(ESTVIDE([@Référence]);"";RECHERCHEV([@Référence];invent;8;FAUX)*[@Quantité]+
RECHERCHEV([@Référence];invent;9;FAUX))
C'est bien, la retraite ! Surtout aux Antilles ... :-) 
Raymond (INSA, AFPA, CF/R)
Messages postés
54540
Date d'inscription
lundi 13 août 2007
Statut
Contributeur
Dernière intervention
2 septembre 2021
16 472
Colonne I: P.V. col D onglet suiv - (P.A. col E onglet inventaire * Qté col C dans suivi) - ebay (E) - Paypal (F)- envois (H)

Formule en I7 :
=SI([@Référence]="";"";[@[Prix de vente]]
-RECHERCHEV([@Référence];invent;5;FAUX)*[@Quantité]
-[@[Frais Ebay]]-[@[Frais Paypal]]-[@[Frais d''envoi]])
On obtient 6,56 € en I7 ; énorme pour 2 articles achetés 0,70 € ...
Mais on obtient -0,20 € en I8 ; là on perd de l'argent !
C'est bien, la retraite ! Surtout aux Antilles ... :-) 
Raymond (INSA, AFPA, CF/R)
Messages postés
54540
Date d'inscription
lundi 13 août 2007
Statut
Contributeur
Dernière intervention
2 septembre 2021
16 472
Colonne J: ... chaque fois que j'ai une vente, la quantité vendue se déduise du stock restant

En J7 la formule est
=SI([@Référence]="";"";RECHERCHEV([@Référence];invent;2;FAUX)-SOMME.SI(B$7:B7;[@Référence];C$7:C7))

Colonne L: Je souhaite savoir à partir de quand j'ai rentabilisé l'achat de départ sur le produit ... Cette colonne doit prendre l'investissement de départ

En L7 la formule partira de
SI([@Référence]="";"";RECHERCHEV([@Référence];invent;6;FAUX))
et se fera sur le modèle des précédentes ...
C'est bien, la retraite ! Surtout aux Antilles ... :-) 
Raymond (INSA, AFPA, CF/R)
Messages postés
54540
Date d'inscription
lundi 13 août 2007
Statut
Contributeur
Dernière intervention
2 septembre 2021
16 472
et pour la dernière interrogation
voir les variations des sous totaux à chaque fois que je modifie un filtre
, je crois qu'il vaut mieux attendre que tu aies complété ton fichier.
L'idéal, à mon avis, serait que tu utilises un tableau croisé dynamique.
Si tu ne sais pas encore ce que c'est, je te suggère de t'y mettre, en utilisant l'Aide d'Excel qui est bien faite, ou les tutos de CCM et d'Internet.

Très cordialement.
Messages postés
6
Date d'inscription
lundi 7 octobre 2013
Statut
Membre
Dernière intervention
8 janvier 2017

Merci Raymond pour toutes ces informations. C'est très sympa.
Je ne suis pas sur mon pc mais je vais travailler sur tout ça en suivant tes conseils et essayer de comprendre les formules que tu m'as donné ainsi que les tableaux dynamique ( nouvelle bestiole que je ne connais pas).
Merci d'avoir pris le temps de me répondre et bonne journée sous le soleil des Antilles ;)
Amitiés
Messages postés
6
Date d'inscription
lundi 7 octobre 2013
Statut
Membre
Dernière intervention
8 janvier 2017

Bonjour Raymond,

Merci de l'information, je ne savais pas que les liens Mega posaient problème.

Voici le lien cjoint.

https://www.cjoint.com/c/GAhq1YnPmVD

Amitiés