Comment savoir quel produit a été le plus vendu?
Solved
Snoop06
Posted messages
3
Registration date
Status
Membre
Last intervention
-
Snoop06 Posted messages 3 Registration date Status Membre Last intervention -
Snoop06 Posted messages 3 Registration date Status Membre Last intervention -
Bonjour, je suis actuellement en stage en entreprise pour ma 1ere année de BTS Assistant de Gestion et mon responsable de stage ma demander de réaliser un graphiquer où sont indiqués les produits les mieux vendu durant l'année 2012 et 2013.
Jai a ma disposition une base de donnée que j'ai exporter du logiciel adcount vers excel.
Jai donc un tableau avec le n° de pièce, L'année; le nom du client, la marque, le numero d'article, le nombre et le prix de vente.
Je suis bloqué, je ne sais pas comment lui dire quel est le produit le plus vendu et qui sont les meilleurs clients pour ensuite faire un graphique de pareto..
Merci d'avance pour votre aide
Jai a ma disposition une base de donnée que j'ai exporter du logiciel adcount vers excel.
Jai donc un tableau avec le n° de pièce, L'année; le nom du client, la marque, le numero d'article, le nombre et le prix de vente.
Je suis bloqué, je ne sais pas comment lui dire quel est le produit le plus vendu et qui sont les meilleurs clients pour ensuite faire un graphique de pareto..
Merci d'avance pour votre aide
3 answers
Hello
In the database (Sheet1), you can create an out-of-field column that will return the rank of each amount in the set with this formula:
=RANK(Cell amount;amount field)
Then you can recreate a table on Sheet2 to reorganize the data according to the rank
For example, starting in A2:
=INDEX(complete field Sheet1;MATCH(ROW()-1;Rank field;0);COLUMN())
will return the data of rank 1 in the 1st column of the Sheet1 field! (2nd column in B, etc...)
If you have ties in the amounts, you will need to complicate things
The rank detection formula, if for the example, the amount is in column C.
Start in row 2
=RANK(C2;$C$2:$C$1000)+COUNTIF($C$1:C1;C2)/1000
Note that in the COUNTIF code, only C1 is locked
And the data extraction formula:
=IF(ISERROR(SMALL(Rank field;ROW()-1);"";INDEX(data field;MATCH(SMALL(Rank field;ROW()-1);rank field;0);COLUMN()))
Be careful to lock the correct cells with the dollar sign before dragging the formulas
If you have a problem, create a small dummy example of your data and place it on:
https://www.cjoint.com/ and come back here to post the link provided by the site
Moreover, if you do not have duplicates, you can directly edit the sorted data without going through the RANK column with the formula in A2 (if amount in Sheet1!C2:C1000)
=IF(ISERROR(LARGE(Sheet1!$C$2:$C$1000;ROW()-1);"";INDEX(Sheet1!$A$2:$X$1000;MATCH(LARGE(Sheet1!$C$2:$C$1000;ROW()-1);Sheet1!$C$2:$C$1000;0);COLUMN()))
And to finish simply:
you can also copy your data sheet and sort by the amount column from largest to smallest, it will be much faster!
Best regards
--
To err is human, to persist is devilish.
In the database (Sheet1), you can create an out-of-field column that will return the rank of each amount in the set with this formula:
=RANK(Cell amount;amount field)
Then you can recreate a table on Sheet2 to reorganize the data according to the rank
For example, starting in A2:
=INDEX(complete field Sheet1;MATCH(ROW()-1;Rank field;0);COLUMN())
will return the data of rank 1 in the 1st column of the Sheet1 field! (2nd column in B, etc...)
If you have ties in the amounts, you will need to complicate things
The rank detection formula, if for the example, the amount is in column C.
Start in row 2
=RANK(C2;$C$2:$C$1000)+COUNTIF($C$1:C1;C2)/1000
Note that in the COUNTIF code, only C1 is locked
And the data extraction formula:
=IF(ISERROR(SMALL(Rank field;ROW()-1);"";INDEX(data field;MATCH(SMALL(Rank field;ROW()-1);rank field;0);COLUMN()))
Be careful to lock the correct cells with the dollar sign before dragging the formulas
If you have a problem, create a small dummy example of your data and place it on:
https://www.cjoint.com/ and come back here to post the link provided by the site
Moreover, if you do not have duplicates, you can directly edit the sorted data without going through the RANK column with the formula in A2 (if amount in Sheet1!C2:C1000)
=IF(ISERROR(LARGE(Sheet1!$C$2:$C$1000;ROW()-1);"";INDEX(Sheet1!$A$2:$X$1000;MATCH(LARGE(Sheet1!$C$2:$C$1000;ROW()-1);Sheet1!$C$2:$C$1000;0);COLUMN()))
And to finish simply:
you can also copy your data sheet and sort by the amount column from largest to smallest, it will be much faster!
Best regards
--
To err is human, to persist is devilish.
Re
here is a solution according to your model:
https://www.cjoint.com/c/CEvp3q0foEb
in the database sheet, I added:
_ a cumulative column for the number of articles
_ a cumulative column for the amounts (which is the product of the number of articles by its price)
_ a column for the range of the number sold (named R_Q)
_ a column for the rank of the amounts sold (named R_M)
the complete field is named CHAMP
you have two editing sheets for the example:
_ one class by number of products,
_ the other class by amount.
The whole thing is functional up to line 1000
Hoping that this is satisfactory.
best regards
Errare humanum est, perseverare diabolicum
here is a solution according to your model:
https://www.cjoint.com/c/CEvp3q0foEb
in the database sheet, I added:
_ a cumulative column for the number of articles
_ a cumulative column for the amounts (which is the product of the number of articles by its price)
_ a column for the range of the number sold (named R_Q)
_ a column for the rank of the amounts sold (named R_M)
the complete field is named CHAMP
you have two editing sheets for the example:
_ one class by number of products,
_ the other class by amount.
The whole thing is functional up to line 1000
Hoping that this is satisfactory.
best regards
Errare humanum est, perseverare diabolicum
I'm sending you a "dummy" example just in case I can't manage: https://www.cjoint.com/?3EvlZ35KJyR
Thanks!!