regrouper données sur critères autre feuille

[Gael]

Bonjour à tous,

Je trouve ce forum tellement efficace pour les autres que je commence à prendre moi aussi l'habitude de me faire aider de temps en temps.

Je n'arrive pas à calculer le pourcentage mensuel de ventes par famille d'articles dans l'exemple joint.

Je dispose d'un tableau 'Ventes' contenant les quantités vendues par article et par mois et d'un tableau 'Articles' qui contient les catégories et familles de chaque article.

L'objectif est de compléter le tableau 'Répartition' en regroupant les quantités vendues par famille d'articles et en calculant le % mensuel afin de définir une saisonnalité qui nous permettra d'affiner les prévisions.

Ca à l'air relativement simple mais je bute sur les formules de regroupement puisque les familles ne se trouvent pas dans la même feuille que les quantités ce qui complique un peu les choses.

A force d'avoir le nez dans le guidon, je ne trouve plus d'idée et je suis preneur de toutes les solutions en formules ou VBA.

Merci d'avance de votre aide.

@+

Gael [file name=Ventes.zip size=11813]http://www.excel-downloads.com/components/com_simpleboard/uploaded/files/Ventes.zip[/file]

 

[Jocelyn]

Bonjour le Forum,
Bonjour Gael,

je ne sais pas si c'est ce que tu veux mais voila un petit essai j'ai un peu changé la présentation de la feuille ventes.

Jocelyn [file name=gael_20051006173235.zip size=14262]http://www.excel-downloads.com/components/com_simpleboard/uploaded/files/gael_20051006173235.zip[/file]

 

[Hervé]

Bonjour gael, jocelyn

très vite fait, une proposition VBA.

salut
[file name=Ventes2_20051006173923.zip size=19858]http://www.excel-downloads.com/components/com_simpleboard/uploaded/files/Ventes2_20051006173923.zip[/file]

 

[Monique]

Bonjour,

Par formules.
En laissant aux feuilles la même présentation
SommeProd avec NB.SI(Designation feuille1;Désignation feuille2)
[file name=VentesGael.zip size=14019]http://www.excel-downloads.com/components/com_simpleboard/uploaded/files/VentesGael.zip[/file]

 

[Hervé]

Bonsoir tout le monde

Je suis pas sur que ce nouveau fichier te soit utile, Gael, mais je ne pouvais pas laisser mon précédent code aussi peu 'optimisé' sans intervenir.

Alors je répare lol

salut
[file name=Ventes3_20051007235341.zip size=21625]http://www.excel-downloads.com/components/com_simpleboard/uploaded/files/Ventes3_20051007235341.zip[/file]

Message édité par: Hervé, à: 08/10/2005 00:05

 

[Gael]

Bonsoir à tous,

Je n'ai pas eu le temps de me connecter sur le forum ces 2 derniers jours, sauf pour prendre rapidement des nouvelles de José.

Sans avoir regardé encore ton 2ème fichier, Hervé, j'allais te dire que j'étais impressionné par le résultat du premier. J'ai fait une erreur de débutant en vous envoyant un exemple un peu trop simplifié de mon problème mais j'ai réussi à modifier facilement ton code pour l'adapter à la nouvelle demande et ça marche très bien, ça fait plusieurs fois que je le dis mais je vais vraiment finir par basculer vers le coté obscur (VBA bien sûr).

Monique, je vais renvoyer Lundi un exemple un peu plus proche de la réalité car j'ai du mal à trouver les formules adéquates. Merci en tous cas pour ce premier essai avec des résultats impeccables et toutes mes excuses pour changer les règles du jeu.

Merci aussi à toi Jocelyn, tes calculs sont parfaits mais comme les tableaux proviennent de requêtes sur le système central, je ne souhaite pas modifier leur présentation.

Bon week-end à tous et à Lundi.

Gael

 

[Gael]

Bonjour Monique, Bonjour Hervé, Bonjour Jocelyn,

Comme promis mais avec un peu de retard, je vous envoie ci-joint un exemple plus précis dans lequel j'ai intégré les formules de Monique ainsi que la macro d'Hervé.

J'avais pratiquement supprimé les difficultés en faisant un exemple trop simple, les problèmes sont en fait les suivants:

* Le tableau Articles contient certaines références dans une deuxième colonne dans le but de cumuler les résultats car les 2 codes correspondent au même article. J'avais pensé adapter par la suite les formules proposées, c'est pourquoi je n'ai pas mentionné ce point dans mon premier exemple.

* En conséquence, les tableaux Ventes et Articles n'ont pas la même taille

* Les données de chaque tableau ne sont pas triées dans le même ordre

* Les références du tableau Ventes n'existent pas toujours dans Articles, même dans les 2 colonnes

* Et pour couronner le tout les références du tableau Ventes sont au format texte alors que celles du tableau Articles sont numériques.

Hervé, j'ai mis 2 macros dans l'exemple, la première (Calc_repart est celle que tu as réalisée, un peu modifiée pour intégrer le cumul des codes, la possibilités de ventes à 0, les formats texte et numérique).

Dans la deuxième (calc_repart2, j'ai essayé de simplifier le code pour la recherche de l'article et le cumul des ventes, le résultat est correct, mais je me demande s'il n'y a pas une commande VBA pour faire directement la recherche sans passer par une boucle, une instruction du style:

search tablovente (j,1) tabloarticles

Monique, même si la macro fonctionne, je suis toujours preneur d'un résultat en formules, ne serait-ce que parce que je ne suis pas arrivé à l'obtenir.


Merci à tous de votre aide.

Gael
[file name=Ventes_20051011155535.zip size=30700]http://www.excel-downloads.com/components/com_simpleboard/uploaded/files/Ventes_20051011155535.zip[/file]

 

[Monique]

Bonjour,

C'est du Somme() en matriciel,
matriciel parce que Transpose()
Le recalcul est lent

SOMME((CatA=$A5)*(FamA=$B5)*(RefA&DesA=TRANSPOSE(RefV&DesV))*TRANSPOSE(DECALER(RefV;0;EQUIV(C$1;Ms;0)+1)))
Celle-ci revient au même :
SOMME((TRANSPOSE(CatA&FamA)=$A5&$B5)*(TRANSPOSE(RefA&DesA)=RefV&DesV)*DECALER(RefV;;EQUIV(C$1;Ms;0)+1))

Je n'arrive pas aux pourcentages de façon directe,
car pas moyen d'avoir le total annuel avec cette formule.

Il y a 2 séries vérifiées, ça semble fonctionner

Les plages ont toutes la même longueur, celle-ci :
=MAX(NBVAL(Ventes!$A:$A);NBVAL(Articles!$A:$A))-1
[file name=VentesGaelV1.zip size=27458]http://www.excel-downloads.com/components/com_simpleboard/uploaded/files/VentesGaelV1.zip[/file]

 

[Gael]

Bonjour Monique, Bonjour Hervé, Bonjour Jocelyn,

Merci monique pour tes formules qui marchent très bien. Je n'ai pas non plus réussi à récupérer le total mais je n'étais déjà pas arrivé à ce stade.

J'ai modifié la formule pour cumuler les anciennes références et supprimé l'utilisation des désignations car le code article est un identifiant unique et la désignation n'apporte rien.

la formule devient:

{=SOMME((TRANSPOSE(CatA&FamA)=$A5&$B5)*((TRANSPOSE(RefA)=CNUM(RefV))+(TRANSPOSE(ARefA)=CNUM(RefV)))*DECALER(RefV;;EQUIV(C$1;Ms;0)+1))}

avec ArefA=DECALER(Articles!$B$2;;;LongMaxi) en fait identique à RefA mais sur la colonne B.

Les résultats sont justes.

@+

Gael

 

[Hervé]

Bonjour tout le monde

Réponse tardive gael, car j'avais un peu perdu ce fil de vue.

Merci de m'avoir rafraichi la mémoire

Concernant ta question : peut on eviter de boucler sur un tableau dynamique. J'ai bien peur que non.

search tablovente (j,1) tabloarticles

Ce type de syntaxe (modifié, bien sur) pourrait fonctionner si tu utilisait des variables 'objet' (set plage=range(....)) et que tu n'avais qu'un élément à rechercher.

De plus faire une boucle dans un tableau dynamique est très rapide. presque instantané (Ti dans un post parlait de la vitesse d'un souffle).

En espérant t'avoir été utile.

salut